Excel数据处理

# _*_ coding:utf-8 _*_
# edit time: 2022/4/27 19:54
# name: correct.py
# Product: PyCharm
from openpyxl import load_workbook
workbook = load_workbook(filename="write_test.xlsx")
workbook.sheetnames                 # 获取表名

from openpyxl import load_workbook
workbook = load_workbook(filename="write_test.xlsx")
workbook.sheetnames
sheet = workbook['sheet1']
print(sheet)                    # 获取第一个表名

from openpyxl import load_workbook
workbook = load_workbook(filename="write_test.xlsx")
workbook.sheetnames
sheet = workbook["sheet1"]
print(sheet)
sheet.dimensions                # 获取表大小，行列

workbook = load_workbook(filename= "write_test.xlsx")
sheet = workbook.active
print(sheet)
cell1 = sheet["A1"]
cell2 = sheet["C11"]
print(cell1.value, cell2.value)
'''
workbook.active  打开激活的表格
sheet["A1] 获取A1格子的数据
cell.value 获取格子中的值
'''

workbook = load_workbook(filename= "write_test.xlsx")
sheet = workbook.active
print(sheet)
cell1 = sheet.cell(row = 1,column= 1)
cell2 = sheet.cell(row = 11,column= 3)
print(cell1.value,cell2.value)

workbook = load_workbook(filename= "write_test.xlsx")
sheet = workbook.active
print(sheet)
cell1 = sheet["A1"]
cell2 = sheet["C11"]
print(cell1.value, cell1.row, cell1.column, cell1.coordinate)
print(cell2.value, cell2.row, cell2.column, cell2.coordinate)
"""
.row 获取某个格子的行数
.columns 获取某个格子的列数
.coordinate 获取某个格子的坐标
"""

workbook = load_workbook(filename= "write_test.xlsx")
sheet = workbook.active
print(sheet)
cell = sheet["A1:C2"]
print(cell)
for i in cell:
    for j in i:
        print(j.value)
"""
sheet["A"]  获取A列的数据
sheet["A:C"]  获取A,B,C三列的数据
sheet[5]  获取第五行的数据
"""

workbook = load_workbook(filename= "write_test.xlsx")
sheet = workbook.active
print(sheet)
for i in sheet.iter_rows(min_row=2, max_row=5, min_col= 1, max_col=2):
    for j in i:
        print(j.value)
for i in sheet.iter_cols(min_row=2, max_row=5, min_col=1, max_col=2):
    for j in i:
        print(j.value)

workbook = load_workbook(filename= "write_test.xlsx")
sheet = workbook.active
print(sheet)
for i in sheet.rows:
    print(i)

# python向Excel中写入内容

workbook = load_workbook(filename="write_test.xlsx")
sheet = workbook.active
print(sheet)
sheet["A1"] = "halo"
# cell = sheet["A1"]
# cell.value = "halo"
workbook.save(filename="halo.xlsx")

workbook = load_workbook(filename="write_test.xlsx")
sheet = workbook.active
print(sheet)
data = ["tangseng","1","180cm"
"wukong","2","188cm"
"bajie","1","175cm"
"heshang","1","144cm"
      ]
for row in data:
    sheet.append(row)
workbook.save(filename="write_test.xlsx")

# IF (RIGHT(C2,2)="cm",C2,SUBSTITUTE(C2,"m","")*100&"cm")
workbook = load_workbook(filename= "write_test.xlsx")
sheet = workbook.active
print(sheet)
sheet["D1"] = "标准身高"
for i in range(2,16):
    sheet["D{}".format(i)] = 'IF (RIGHT(C2,2)="cm",C2,SUBSTITUTE(C2,"m","")*100&"cm")'.format(i,i,i)
workbook.save(filename="write_test.xlsx")

import openpyxl
from openpyxl.utils import FORMULAE
print(FORMULAE)

# .insert_cols()和 .insert_rows():插入空行和空列
# *.insert_cols(idx = 数字编号，amount = 要插入的列数)，插入的位置是在idx列数的左侧
# *.insert_rows(idx = 数字编号, amount = 要插入的行数)，插入的行数是在idx行数的下方

workbook = load_workbook(filename="write_test.xlsx")
sheet = workbook.active
print(sheet)
sheet.insert_cols(idx = 4,amount = 2)
sheet.insert_rows(idx = 5,amount = 4)
workbook.save(filename= "write_test.xlsx")

# *.delete_rows(idx = 数字编号, amount = 要删除的行数)
# *.delete_cols(idx = 数字编号, amount = 要删除的列数)

workbook = load_workbook(filename= "write_test.xlsx")
sheet = workbook.active
print(sheet)
sheet.delete_cols(idx = 1)
sheet.delete_rows(idx = 1)
workbook.save(filename= "write_test.xlsx")

# *.move_range("数据区域", rows= , cols= ):正整数为向下或向右，负整数为向左或向上
# 向左移动两列，向下移动两行：sheet.move_range("C1:D4", rows=2,cols=-1)

workbook = load_workbook(filename= "write_test.xlsx")
sheet = workbook.active
print(sheet)
workbook.create_sheet("new_sheet")
print(workbook.sheetnames)
workbook.save(filename= "write_test.xlsx")

import xlrd
wb = xlrd.open_workbook("工作簿名.xls")
wsobj = wb.sheets()  #  读取工作簿下的所有工作表名称
ws1 = wb.sheet_by_name('工作表名')  # 按指定名称读取工作表对象
ws2 = wb.sheet_by_index(1)   #  按指定序号读取工作表对象
ws3 = wb.sheets()[1] # 按指定序号读取工作表对象

import xlrd
ws = xlrd.open_workbook('工作簿名.xls').sheet_by_name('工作表名')
crow = ws.nrows   #  获取行数
ccol = ws.ncols   #  获取列数
row_data = ws.row_values(行号)  # 获取指定行数据
col_data = ws.col_values(列号)  # 获取指定列数据
cell_data_1 = ws.cell_value(行号，列号)  # 获取单元格数据
cell_data_2 = ws.cell(行号，列号).value  # 获取单元格数据

#  创建工作簿、工作表、写入单元格
import xlwt
nwb = xlwt.Workbook(encoding='utf-8')  # 新建工作簿
nws = wb.add_sheet('工作表名')  # 添加工作表
nws.write(1,2,'要写入的值')  # 写入单元格
nwb.save('工作簿名.xls')

# 修改工作簿、工作表、单元格
import xlrd
from xlutils.copy import copy
wb = xlrd.open_workbook('工作簿名.xls') #读取要修改的工作簿
nwb = copy(wb)  # 复制工作簿
nws1 = nwb.add_sheet('工作表名')
nws2 = nwb.add_sheet(序号)
nws3 = nwb.get_sheet('工作表名')
nws1.write(行号，列表，'值')
nwb.save('工作簿名.xls')

import openpyxl
wb = openpyxl.Workbook()
wb.save('工作簿.xlsx')