openpyxl使用教程

---

前言

万字长文，欢迎指正。
Excel作为我们在日常工作中使用的工具，用途广泛且功能强大。本文简单介绍使用openpyxl操作Excel。
官方文档地址：https://openpyxl.readthedocs.io/en/stable/#

---

一、openpyxl是什么？

openpyxl是一个Python库，用于读取/写入 Excel 2010 xlsx/xlsm/xltx/xltm 文件。基于PHPExcel。

注意：openpyxl不支持xls文件！！！

默认情况下，openpyxl不防范XML攻击，建议使用defusedxml软件包进行防范。

defusedxml 是纯Python软件包，带有所有stdlib XML解析器的修改后的子类，可防止任何潜在的恶意操作。建议对解析不受信任的XML数据的任何服务器代码使用此包。该软件包还附带示例漏洞利用程序和有关更多XML漏洞利用程序的扩展文档，例如XPath注入。

二、简单操作使用

1 工作簿(WorkBook)

1.1 新建工作簿

基于openpyxl中的Workbook类

from openpyxl import Workbook

# 新建一个工作簿对象
wb = Workbook()

1.2 加载已有的工作簿

与新建的方式类似，使用openpyxl.load_workbook()加载已有的工作簿

from openpyxl import load_workbook

# 根据指定文件路径加载文件
wb = load_workbook('./test.xlsx')

1.3 保存工作簿

• 保存到文件

# 保存，save（必须要写文件名（绝对地址）默认 py 同级目录下，只支持 xlsx 格式）
wb.save('openpyxl_test1.xlsx')

此操作将在没有警告的情况下覆盖现有文件。

• 保存为文件流
  使用tempfile下的NamedTemporaryFile()

tempfile：临时文件模块。大量临时数据放在内存中会占用大量资源，可以使用临时文件来进行储存

# 保存为文件流
with NamedTemporaryFile('w+b', delete=False) as tmp:
    wb.save(tmp.name)
    tmp.seek(0)
    stream = tmp.read()  # 二进制文件流

系统	with NamedTemporaryFile(‘w+b’, delete=False) as tmp:	with NamedTemporaryFile(‘w+b’) as tmp:
Windows	正常运行，运行结束保留临时文件	不能运行，不保留临时文件
Linux	正常运行，运行结束保留临时文件	正常运行，运行结束不保留临时文件

2 工作表(WorkSheet)

一个工作簿应至少存在一个工作表。

注意：当工作簿创建好之后，默认会创建一个名为"Sheet"的工作表

2.1 新建工作表

格式：workbook.create_sheet([title][,index])

注意：如果不指定名称，则在创建时会自动根据顺序编号（Sheet、Sheet1、Sheet2、…）进行命名

# 创建一个新的sheet
ws1 = wb.create_sheet("Sheet_1")  # 在最后插入一个sheet(默认)
ws2 = wb.create_sheet("Sheet_2", 0)  # 在第一个位置插入一个sheet
ws3 = wb.create_sheet("Sheet_3", -1)  # 在倒数第二个位置插入一个sheet
ws4 = wb.create_sheet()  # 默认

2.2 获取所有工作表名称

格式：workbook.sheetnames
获取当前工作簿所有工作表的名称，并以列表的形式返回

wb.sheetnames	# ['Sheet_2', 'Sheet', 'Sheet_3', 'Sheet_1', 'Sheet1']

循环所有工作表：

# 循环工作表
for sheet in wb:
    print(sheet)  # 工作表对象
    print(sheet.title)  # 工作表名称

2.3 修改工作表名称

格式：worksheet.title = 工作表新名称

# 修改工作表名称
ws2.title = 'New Title'
ws4.title = '新的工作表名称'
print(wb.sheetnames)    # ['New Title', 'Sheet', 'Sheet_3', 'Sheet_1', '新的工作表名称']

2.4 获取当前活动工作表

格式：workbook.active
默认为第一个工作表。返回一个工作表对象

ws = wb.active  # <Worksheet "New Title">
print(ws.title)  # New Title    当前活动工作表标题

2.5 指定工作表

方式1：将工作表名作为键来获取工作表对象
格式：workbook[worksheet_name]

# 方式1: 将工作表名作为键来获取工作表对象
specify_ws = wb['Sheet_3']  # <Worksheet "Sheet_3">
print(specify_ws.title)  # Sheet_3

方式2：更改active索引值
格式：workbook.active = worksheet_index

当索引值超出已有工作表数量范围时为None

# 方式2: 更改active索引值
wb.active = 3
specify_ws2 = wb.active  # <Worksheet "Sheet_1">
print(wb.active.title)  # Sheet_1
wb.active = 10
print(wb.active)  # None

2.6 获取工作表的最大、最小行与最大、最小列

工作表最大行：worksheet.max_row，获取存在数据的最大一行索引
工作表最小行：worksheet.min_row，获取存在数据的最小一行索引
工作表最大列：worksheet.max_column，获取存在数据的最大一列索引
工作表最小列：worksheet.min_column，获取存在数据的最小一列索引

# 获取工作表最大、最小行
print(ws.max_row)  # 4
print(ws.min_row)  # 2
# 获取工作表最大、最小列
print(ws.max_column)  # 2
print(ws.min_column)  # 1

2.7 插入工作表行或列

插入行：worksheet.insert_rows(idx, [,amount]) idx:行索引，从哪一行插入；amount:插入几行，默认一行

# 插入工作表行
ws.insert_rows(idx=4, amount=2)

插入列：worksheet.insert_cols(idx, [,amount]) idx:列索引，从哪一列插入；amount:插入几行，默认一行

# 插入工作表列
ws.insert_cols(idx=2, amount=2)

2.8 删除工作表行或列

删除行：worksheet.delete_rows(idx[, amount]) idx:行索引，从哪一行删除；amount:删除几行，默认一行

# 删除工作表行
print("删除前A4的值：", ws['A4'].value)   # 删除前A4的值： 测试1
ws.delete_rows(idx=3, amount=1)
print("删除后A4的值：", ws['A4'].value)   # 删除后A4的值： 测试3

删除列：worksheet.delete_cols(idx[, amount]) idx:列索引，从哪一列删除；amount:删除几列，默认一列

# 删除工作表列
print("删除前B3的值：", ws['B3'].value)   # 删除前B3的值： 测试2
ws.delete_cols(idx=2, amount=1)
print("删除后B3的值：", ws['B3'].value)   # 删除后B3的值： None

注意：删除行或列后，后面的行或列会自动往前面填充，如上例子所示

2.9 修改工作表标题选项卡背景颜色

默认背景颜色为白色，可以更改为RRGGBB颜色代码
格式：worksheet.sheet_properties.tabColor = color_code

# 修改工作表标题选项卡背景颜色
ws4.sheet_properties.tabColor = '1072BA'

结果如图所示：

2.10 单个工作簿中创建工作表的副本

格式：workbook.copy_worksheet(from_worksheet)

copy_ws1 = wb.copy_worksheet(ws3)
copy_ws1.title = '这是copy的sheet'

效果图：

注意：仅复制单元格（包括值、样式、超链接和注释）和某些工作表属性（包括尺寸、格式和属性）。不会复制所有其他工作簿/工作表属性 - 例如图像、图表。
不能在工作簿之间复制工作表。如果工作簿以只读或只写 模式打开，则无法复制工作表。

3 单元格(cell)

3.1 选择单个单元格

方式1：将单元格作为工作表的键直接访问
格式：worksheet[cell]

# 方式1：将单元格作为工作表的键直接访问
a1 = ws['A1']  # 如果单元格不存在，将创建一个单元格
print(a1)  # <Cell 'Sheet_11'.A1>
# 也可以直接对单元格赋值
ws['A2'] = '测试'

方式2：使用worksheet.cell(row, column[, value])方法

# 方式2：使用worksheet.cell()方法
cell2 = ws.cell(row=4, column=2, value='测试2')  # column