python 创建xlsx后缀的excle表格（复制、公式运算）
简单需求可以用with open 创建csv后缀的表格，但是csv后缀的表格 不支持 多个sheet 表。可以使用 openpyxl 库 创建xlsx后缀的表格
使用的是 openpyxl 库：
创建表格
from openpyxl import Workbook,load_workbook
# 加载表格
# wb = load_workbook(r'.\test.xlsx')
# 新建表格
wb = Workbook()
ws = wb.active
# 准备数据
rows = [
['月份','桃子','西瓜','龙眼'],
[1,25,28,62],
[2,25,25,72],
[3,245,28,642],
[4,25,28,42],
[5,25,42,62],
[6,30,28,62],
]
for row in rows:
# 逐行写入数据
ws.append(row)
# 保存表格
wb.save('字体测试.xlsx')赋值操作
# -*- coding:utf-8 -*- from openpyxl import Workbook,load_workbook from openpyxl.utils import FORMULAE from openpyxl.formula.translate import Translator wb = Workbook() # wb = load_workbook(r'.\test.xlsx') ws = wb.active ws.append(['最高价','最低价','求和','平均值']) ws.append([23,25]) ws.append([42,27]) ws.append([12,18]) ws.append([19,30]) # 赋值 ws['D4'] = 200 ws['D5'] = 100 ws['D6'] = 100 ws['E9'] = 100 ws['D13'] = 200 wb.save(r'.\test.xlsx')
公式运算
# -*- coding:utf-8 -*-
from openpyxl import Workbook,load_workbook
from openpyxl.utils import FORMULAE
from openpyxl.formula.translate import Translator
wb = Workbook()
# wb = load_workbook(r'.\test.xlsx')
ws = wb.active
ws.append(['最高价','最低价','求和','平均值'])
ws.append([23,25])
ws.append([42,27])
ws.append([12,18])
ws.append([19,30])
# 运行公式 求和
ws['c2'] = '=SUM(A2:B2)'
# ws['c3'] = '=SUM(A3:B3)'
# ws['c4'] = '=SUM(A4:B4)'
# ws['c5'] = '=SUM(A5:B5)'
# 另外一种运行公式的方法，类似于 excle 表格里的下拉
ws['c3'] = Translator(formula='=SUM(A2:B2)',origin='c2').translate_formula('c3')
ws['c4'] = Translator(formula='=SUM(A2:B2)',origin='c2').translate_formula('c4')
# 一种简单的方法批量运行公式
for cell in ws['c2:c5']:
cell[0].value = Translator(formula='=SUM(A2:B2)',origin='c2').translate_formula(cell[0].coordinate)
# 运行公式 求平均值
ws['d2'] = '=AVERAGE(A2:B2)'
# ws['d3'] = '=AVERAGE(A3:B3)'
# ws['d4'] = '=AVERAGE(A4:B4)'
# ws['d5'] = '=AVERAGE(A5:B5)'
# 另外一种运行公式的方法，类似于 excle 表格里的下拉
ws['d3'] = Translator(formula='=AVERAGE(A2:B2)',origin='d2').translate_formula('d3')
ws['d4'] = Translator(formula='=AVERAGE(A2:B2)',origin='d2').translate_formula('d4')
# 一种简单的方法批量运行公式
for cell in ws['d2:d5']:
cell[0].value = Translator(formula='=AVERAGE(A2:B2)',origin='d2').translate_formula(cell[0].coordinate)
wb.save('测试.xlsx')更多拓展：
https://blog.csdn.net/qq_39147299/category_11674179.html
创建新的工作簿
from openpyxl import Workbook
wb = Workbook()
ws = wb.active # 获取默认的工作表
print(ws.title) # 返回工作表名，Sheet
# ws.title = "default_sheet" # 修改工作表名
wb.save("./test.xlsx")
wb.close()打开已有工作簿
from openpyxl import load_workbook
wb2 = load_workbook('./test2.xlsx') # 打开已存在的工作簿
ws = wb2.active
ws.title = "default_2"
wb2.save("test2.xlsx")保存为文件
openpyxl只支持保存为xlsx类型
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
print(ws.title)
wb.save("./test.xlsx") # 保存到硬盘
wb.close() # 该方法在只读或只写模式下有用注意，通过保存路径和文件名相同会覆盖原先的文件，不会有提示
获取表格sheet名字：
wb = Workbook() ws = wb.active # 获取默认的工作表 print(ws.title) # 返回工作表名，Sheet
创建工作表
from openpyxl import Workbook
wb = Workbook()
ws1 = wb.create_sheet("my_sheet1", 1)
ws2 = wb.create_sheet("my_sheet2", 2)
wb.save("./test.xlsx") # 保存到硬盘create_sheet(title,index)，接收两个参数，表名和位置
title：表名
index：下标即位置，从0开始
工作表信息
print(ws.max_row) # 最大行数，例如14 print(ws.max_column) # 最大列数，例如20 print(ws.dimensions) # 已启用的单元格范围，例如A1:T14 print(ws.encoding) # 编码类型，例如utf-8 print(ws.sheet_view) # 对象信息
获取和修改表名、获取下标
from openpyxl import Workbook
wb = Workbook()
ws1 = wb.create_sheet("my_sheet1", 1)
ws2 = wb.create_sheet("my_sheet2", 2)
# 修改表名
print(ws1.title) # 输出：my_sheet1
ws1.title = "my_sheet111"
print(ws1.title) # 输出：my_sheet111
# 获取所有表名
names = wb.get_sheet_names() # 推荐使用下面的获取用法
sheet_names = wb.sheetnames
print(type(sheet_names), sheet_names) # 输出：<class 'list'> ['Sheet', 'my_sheet111', 'my_sheet2']
# 通过表名获取表
sheet = wb.get_sheet_by_name("Sheet") # 推荐使用下面的获取方法
sheet = wb["Sheet"]
# 获取表的下标位置（下标从0开始）
# index = wb.get_index(ws2) # 推荐下面的获取方法
index = wb.index(ws2)
print("get_index:", index)移动、复制、删除表
from openpyxl import Workbook
wb = Workbook()
ws1 = wb.create_sheet("my_sheet1", 1)
ws2 = wb.create_sheet("my_sheet2", 2)
ws1.title = "my_sheet111"
print(ws1.title)
print(wb.sheetnames) # 输出：['Sheet', 'my_sheet111', 'my_sheet2']
# 移动位置
wb.move_sheet("my_sheet111", -1) # 向前移动一个位置，正数往后，负数往前
print(wb.sheetnames) # 输出：['my_sheet111', 'Sheet', 'my_sheet2']
# 删除工作表
# wb.remove_sheet(ws1) # 方法1
del wb["my_sheet111"] # 方法2
print(wb.sheetnames) # 输出：['Sheet', 'my_sheet2']
# 复制工作表
cp_sheet = wb.copy_worksheet(ws1)
print(cp_sheet.title) # 输出：my_sheet111 Copy获取和修改单个单元格
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
cell = ws["a6"] # 通过坐标获取
cell2 = ws.cell(1, 2) # 通过行列下标获取
# 直接修改某个单元格的值
ws["a5"] = 666
ws['A3'] = datetime.datetime.now().strftime("%Y-%m-%d") # 修改为时间类型
ws.append([1, 2, 3]) # 在最下面新增一行追加一个或多个值
# 先获取单元格对象然后再进行修改
cell = ws["a6"]
cell.value = 777
print(cell, cell.value) # 输出：<Cell 'Sheet'.A6> 777
cell2 = ws.cell(6, 1) # 第6行第1列，即A6
print(cell2, cell2.value) # 输出：<Cell 'Sheet'.A6> 777
# 单元格坐标信息
print(c.coordinate) # 单元格坐标，例如A6
print(c.column_letter) # 单元格列名，例如A
print(c.col_idx) # 单元列下标，例如1
print(c.row) # 单元格所在行，例如6如果使用cell(row, column, value)获取，第一个参数是行，第二个参数是列，下标都是从1开始，例如，ws[“a6”]等同于ws.cell(6, 1)，但如果指定了第三个参数value，则修改了该单元格的值
2.只要访问了一个cell就会被创建，不管是否赋值
通过范围取值
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
i = 1
for x in range(1, 11):
for y in range(1, 21):
ws.cell(row=x, column=y, value=i)
i += 1
# wb.save("test.xlsx")
row_cells = ws[2] # 选取第2行（下标从1开始）
print(row_cells) # 输出：(A2, B2, C2, D2, E2, F2, G2, H2, I2, J2, K2, L2, M2, N2, O2, P2, Q2, R2, S2, T2)
col_cells = ws["b"] # 选取B列
print(col_cells) # 输出：(B1, B2, B3, B4, B5, B6, B7, B8, B9, B10)
row_range_cells = ws[2:5] # 选取2、3、4、5共4行
print(row_range_cells) # 输出：
# ((A2, B2, C2, D2, E2, F2, G2, H2, I2, J2, K2, L2, M2, N2, O2, P2, Q2, R2, S2, T2),
# (A3, B3, C3, D3, E3, F3, G3, H3, I3, J3, K3, L3, M3, N3, O3, P3, Q3, R3, S3, T3),
# (A4, B4, C4, D4, E4, F4, G4, H4, I4, J4, K4, L4, M4, N4, O4, P4, Q4, R4, S4, T4),
# (A5, B5, C5, D5, E5, F5, G5, H5, I5, J5, K5, L5, M5, N5, O5, P5, Q5, R5, S5, T5))
col_range_cells = ws["B:D"] # 选取B、C、D共3列
print(col_range_cells) # 输出：
# ((B1, B2, B3, B4, B5, B6, B7, B8, B9, B10),
# (C1, C2, C3, C4, C5, C6, C7, C8, C9, C10),
# (D1, D2, D3, D4, D5, D6, D7, D8, D9, D10))
range_cells = ws["c3:f6"] # 选取 C3到F6区域共16个元素
print(range_cells) # 输出：
# ((C3, D3, E3, F3),
# (C4, D4, E4, F4),
# (C5, D5, E5, F5),
# (C6, D6, E6, F6))上输出应该类似<Cell ‘Sheet’.A2>、 <Cell ‘Sheet’.B2>，为了好看，简化为A2、B2的形式
2.以上获取到的多个单元格，返回的是元组或元组套元组，可以通过遍历的方式访问或修改
通过iter_rows或iter_cols取值
iter_rows()与iter_cols()都可以指定最大最小的行列，下标从1开始
返回结果是生成器
...
# wb.save("test.xlsx")
cells = ws.iter_rows(min_row=1, max_row=3, min_col=2, max_col=5)
for cell in cells:
print(cell)
# 输出：
# (B1, C1, D1, E1)
# (B2, C2, D2, E2)
# (B3, C3, D3, E3)
cells = ws.iter_cols(min_row=1, max_row=3, min_col=2, max_col=5)
for cell in cells:
print(cell)
# 输出：
# (B1, B2, B3)
# (C1, C2, C3)
# (D1, D2, D3)
# (E1, E2, E3)iter_cols和iter_rows都可以指定参数values_only=True，这样只返回值而不是cell对象
也可以使用rows或columns属性遍历全部行或列，values属性取出所有值，它们都得到迭代器，但是注意只读模式下columns属性无效
for cell in ws.rows: print(cell) for cell in ws.columns: print(cell) for row in ws.values: for value in row: print(value)
合并单元格
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
i = 1
for x in range(1, 11):
for y in range(1, 21):
ws.cell(row=x, column=y, value=i)
i += 1
print(ws["C2"].value) # 输出：23
ws.merge_cells("A1:F3")
ws.unmerge_cells("A1:F3")
print(ws["C2"].value) # 输出：None
# 等同于下面的代码
# ws.merge_cells(start_row=1, start_column=1, end_row=3, end_column=6)
# ws.unmerge_cells(start_row=1, start_column=1, end_row=3, end_column=6)
wb.save("./test.xlsx")删除或插入行列
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
i = 1
for x in range(1, 11):
for y in range(1, 21):
ws.cell(row=x, column=y, value=i)
i += 1
ws.insert_cols(5) # 在第5列即E列插入1列，原来的E列及后面的列都往后移动
ws.insert_rows(2, 3) # 在第2行后面插入3行
ws.delete_cols(2, 3) # 从2列开始往后删除3列
ws.delete_rows(5, -3) # 从5行开始往前删除3行，即删除第4、3、2行
wb.save("./test.xlsx")移动单元格
可以使用move_range()合并指定范围的单元格，但是注意，如果移动到的位置原来有数据会被覆盖掉，移动之后公式会丢失，可以通过设置translate=True来更新，默认是False
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
i = 1
for x in range(1, 11):
for y in range(1, 21):
ws.cell(row=x, column=y, value=i)
i += 1
ws.move_range("B1:D3", rows=6, cols=-1, translate=False) # 移动单元格，向下移动6行，向左移动1列
wb.save("./test.xlsx")pandas排序
虽然openpyxl不能真的实现排序，但是我们可以借助超级强大的pandas轻松实现排序
import pandas as pd
# 读取上一步保存的Excel文件
df = pd.read_excel("./openpyxl/test.xlsx", sheet_name="Sheet")
df_value = df.sort_values(by=["桃子", "西瓜"], ascending=False) # 如果"桃子"数据相同再按照"西瓜"进行排列
# 保存文件
writer = pd.ExcelWriter('./openpyxl/sort_file.xlsx')
df_value.to_excel(writer, sheet_name='Sheet1', index=False)
writer.save()