目录
1 概述
官方文档：https://openpyxl.readthedocs.io
2 工作簿操作（新建/打开、保存）
from openpyxl import Workbook
from openpyxl import load_workbook
# 新建一个工作簿
wb = Workbook()
# 打开本地Excel文件，加r是为了解决转义的问题
# wb = load_workbook(r'C:\Users\Test\Desktop\test.xlsx')
# 保存工作簿，加r是为了解决转义的问题
wb.save(r'C:\Users\Test\Desktop\test.xlsx')
# 关闭工作簿，该方法在只读或只写模式下有用
wb.close()
3 工作表
3.1 工作表操作（创建、修改、移动、复制、删除）
from openpyxl import Workbook
# 新建一个工作簿
wb = Workbook()
# 获取默认的工作表
ws1 = wb.active
# 返回工作表名
print(ws1.title)
# 修改工作表名
ws1.title = 'test'
# 新建一个工作表create_sheet(self, title=None, index=None)
# title：工作表名称
# index：工作表位置，默认为None，表示在最后插入
wb.create_sheet('test1', 0)
wb.create_sheet()
# 通过表名获取工作表
ws2 = wb['test1']
# 将test工作表向右移动一位，-1为向左移动，+1为向右移动
wb.move_sheet(ws2, +1)
# 返回工作表的索引
print(wb.index(ws2))
# 复制工作表
wb.copy_worksheet(ws1)
# 删除工作表
del wb['test1']
# 返回所有工作表名
print(wb.sheetnames)
3.2 工作表信息
print(ws.max_row) # 最大行数，例如16
print(ws.max_column) # 最大列数，例如12
print(ws.dimensions) # 已启用的单元格范围，例如A1:L16
print(ws.encoding) # 编码类型，例如utf-8
print(ws.sheet_view) # 对象信息
4 单元格
4.1 获取和修改单个单元格
from openpyxl import Workbook
import datetime
# 新建一个工作簿
wb = Workbook()
# 获取默认的工作表
ws = wb.active
# 返回基于给定坐标的单元格对象
cell1 = ws['A1']
cell2 = ws.cell(1, 1)
# 直接修改某个单元格的值
ws['A1'] = 42
# 追加一行
ws.append([1, 2, 3])
ws['A2'] = datetime.datetime.now()
# 先获取单元格对象然后再进行修改
cell3 = ws['A6']
cell3.value = 66
print(cell3, cell3.value) # 输出：<Cell 'Sheet'.A6> 66
# 通过坐标修改某个单元格的值
cell4 = ws.cell(6, 1, 'python')
print(cell4, cell4.value) # 输出：<Cell 'Sheet'.A6> python
1.如果使用cell(row, column, value)获取，第一个参数是行，第二个参数是列，下标都是从1开始，例如，ws[‘A6’]等同于ws.cell(6, 1)，但如果指定了第三个参数value，则修改了该单元格的值。
2.只要访问了一个cell就会被创建，不管是否赋值。
根据上面的方式，我们可以通过循环来准备一些数据：
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
x = 1
for i in range(1, 11):
for j in range(1, 6):
ws.cell(i, j, x)
x += 1
wb.save('test.xlsx')
4.2 获取多个单元格
4.2.1 通过范围取值
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
x = 1
for i in range(1, 11):
for j in range(1, 6):
ws.cell(i, j, x)
x += 1
row_cells = ws[2] # 选取第2行（下标从1开始）
print(row_cells) # 输出：(A2, B2, C2, D2, E2)
col_cells = ws['B'] # 选取B列
print(col_cells) # 输出：(B1, B2, B3, B4, B5, B6, B7, B8, B9, B10)
row_range_cells = ws[2:6] # 选取2、3、4、5、6共5行
print(row_range_cells)
# 输出：
# ((A2, B2, C2, D2, E2),
# (A3, B3, C3, D3, E3),
# (A4, B4, C4, D4, E4),
# (A5, B5, C5, D5, E5),
# (A6, B6, C6, D6, E6))
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['B2:D6'] # 选取 C3到F6区域共16个元素
print(range_cells)
# 输出：
# ((B2, C2, D2),
# (B3, C3, D3),
# (B4, C4, D4),
# (B5, C5, D5),
# (B6, C6, D6))
1.以上输出应该类似<Cell ‘Sheet’.A2>、 <Cell ‘Sheet’.B2>，为了好看，简化为A2、B2的形式。
2.以上获取到的多个单元格，返回的是元组或元组套元组，可以通过遍历的方式访问或修改。
4.2.2 通过iter_rows()或iter_cols()迭代取值
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
x = 1
for i in range(1, 11):
for j in range(1, 6):
ws.cell(i, j, x)
x += 1
cells = ws.iter_rows(min_row=2, max_row=6, min_col=2, max_col=4)
for cell in cells:
print(cell)
# 输出：
# (B2, C2, D2)
# (B3, C3, D3)
# (B4, C4, D4)
# (B5, C5, D5)
# (B6, C6, D6)
cells = ws.iter_cols(min_col=2, max_col=4, min_row=2, max_row=6)
for cell in cells:
print(cell)
# 输出：
# (B2, B3, B4, B5, B6)
# (C2, C3, C4, C5, C6)
# (D2, D3, D4, D5, D6)
iter_rows()和iter_cols()都可以指定参数values_only=True，这样只返回值而不是返回cell对象。
也可以使用rows或columns属性遍历全部行或列，values属性取出所有值，它们都返回迭代器，但是注意只读模式下columns属性无效。
for row in ws.rows:
print(row)
for column in ws.columns:
print(column)
for row in ws.values:
for value in row:
print(value)
4.3 操作单元格
4.3.1 合并单元格
合并单元格，会保留最左上角的单元格的数据和样式，其他单元格会被清空，即使取消合并。即，合并之后只保留左上角第一个单元格的数据和样式。
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
x = 1
for i in range(1, 11):
for j in range(1, 6):
ws.cell(i, j, x)
x += 1
print(ws['A2'].value) # 输出：6
ws.merge_cells('A1:B2')
ws.unmerge_cells('A1:B2')
print(ws['A2'].value) # 输出：None
# 等同于下面的代码
# ws.merge_cells(start_row=1, start_column=1, end_row=2, end_column=2)
# ws.unmerge_cells(start_row=1, start_column=1, end_row=2, end_column=2)
wb.save('test.xlsx')
4.3.2 插入或删除行列
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
x = 1
for i in range(1, 11):
for j in range(1, 6):
ws.cell(i, j, x)
x += 1
ws.insert_rows(2, 2) # 在第2行后面插入2行，原来的第2行及后面的行都往后移动
ws.insert_cols(2, 2) # 在第2列（B列）后面插入2列，原来的第2列（B列）及后面的列都往后移动
ws.delete_rows(2, 2) # 从2行开始往后删除2行
ws.delete_cols(2, 2) # 从2列开始往后删除2列
wb.save('test.xlsx')
4.3.3 移动单元格
可以使用move_range()移动指定范围的单元格，但是注意，如果移动到的位置原来有数据会被覆盖掉，移动之后公式不会自动更新，可以通过设置translate=True来更新，默认是False。
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
x = 1
for i in range(1, 11):
for j in range(1, 6):
ws.cell(i, j, x)
x += 1
ws.move_range('C1:E2', rows=6, cols=-2, translate=True) # 移动单元格，向下移动6行，向左移动2列
wb.save('test.xlsx')
4.3.4 单元格坐标信息
print(cell.coordinate) # 单元格坐标，例如A2
print(cell.row) # 单元格所在行，例如2
print(cell.column) # 单元格所在列，例如1
print(cell.col_idx) # 单元格所在列，例如1
print(cell.column_letter) # 单元格所在列，例如A
5 设置样式（字体样式、对齐方式、行列宽高、边框、填充和渐变）
5.1 字体样式
from openpyxl import Workbook
from openpyxl.styles import Font
wb = Workbook()
ws = wb.active
# 默认字体样式
ws['A1'] = 'A1'
# 自定义字体样式
ws['B2'] = 'B2'
font = Font(
name='微软雅黑', # 字体
size=16, # 字体大小
color='FF0000', # 字体颜色，用16进制RGB表示
bold=True, # 是否加粗，True/False
italic=True, # 是否斜体，True/False
strike=True, # 是否使用删除线，True/False
shadow=True, # 是否使用阴影，True/False
underline='single' # 下划线, 可选 'single', 'double', 'singleAccounting', 'doubleAccounting'
)
ws['B2'].font = font
wb.save('test.xlsx')
5.2 对齐方式
from openpyxl import Workbook
from openpyxl.styles import Alignment
wb = Workbook()
ws = wb.active
# 默认对齐方式
ws['A1'] = 'A1'
# 自定义对齐方式
ws['B2'] = 'B2'
alignment = Alignment(
horizontal='center', # 水平对齐，可选 'general', 'left', 'center', 'right', 'fill', 'justify', 'centerContinuous', 'distributed'
vertical='center', # 垂直对齐，可选 'top', 'center', 'bottom', 'justify', 'distributed'
text_rotation=90, # 字体旋转，0~180整数
wrap_text=True, # 是否自动换行
shrink_to_fit=True, # 是否缩小字体填充
indent=10 # 缩进值，0~255
)
ws['B2'].alignment = alignment
wb.save('test.xlsx')
5.3 行高列宽
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
ws.row_dimensions[2].height = 20 # 设置第2行高度为20
ws.column_dimensions['B'].width = 20 # 设置B列宽度为20
wb.save('test.xlsx')
5.4 边框
from openpyxl import Workbook
from openpyxl.styles import Border, Side
wb = Workbook()
ws = wb.active
side = Side(
style='medium', # 边框样式，可选 'dashDot', 'dashDotDot', 'dashed', 'dotted', 'double', 'hair', 'medium', 'mediumDashDot', 'mediumDashDotDot', 'mediumDashed', 'slantDashDot', 'thick', 'thin'
color='FF0000', # 边框颜色，用16进制RGB表示
)
border = Border(
left=side, # 左
right=side, # 右
top=side, # 上
bottom=side, # 下
vertical=side, # 垂直线
horizontal=side, # 水平线
diagonal=side, # 对角线
diagonalUp=True, # 是否使用上对角线，True/False
diagonalDown=True # 是否使用下对角线，True/False
)
cells = ws['B2:C3']
i = 1
for row in cells:
for cell in row:
cell.border = border
cell.value = i
i += 1
wb.save('test.xlsx')
5.5 填充和渐变
from openpyxl import Workbook
from openpyxl.styles import PatternFill, GradientFill
wb = Workbook()
ws = wb.active
pattern_fill = PatternFill(
patternType='solid', # 填充类型，可选 'none', 'solid', 'darkDown', 'darkGray', 'darkGrid', 'darkHorizontal', 'darkTrellis', 'darkUp', 'darkVertical', 'gray0625', 'gray125', 'lightDown', 'lightGray', 'lightGrid', 'lightHorizontal', 'lightTrellis', 'lightUp', 'lightVertical', 'mediumGray'
fgColor='FFFF00', # 前景色，用16进制RGB表示
bgColor='FFFFFF', # 背景色，用16进制RGB表示
# fill_type=None, # 填充类型
# start_color=None, # 前景色，用16进制RGB表示
# end_color=None # 背景色，用16进制RGB表示
)
gradient_fill = GradientFill(
degree=60, # 渐变角度
stop=('000000', 'FFFFFF') # 渐变颜色，用16进制RGB表示
)
ws['B2'] = 'B2'
ws['D2'] = 'D2'
ws['B2'].fill = pattern_fill
ws['D2'].fill = gradient_fill
wb.save('test.xlsx')
6 使用、复制公式
6.1 使用公式
当前演示的 openpyxl 版本是 3.1.3，共支持 355 个公式，公式保存在一个 frozenset 类型的集合中，我们可以通过 python 的 in 语法判断是否支持某个公式，记住每个公式都是大写的。
from openpyxl.utils import FORMULAE
print(FORMULAE) # frozenset({'TDIST', 'WEEKNUM', 'SYD', 'OCT2DEC', 'DATEVALUE', 'IMSUM', ...
print(len(FORMULAE)) # 355
# 判断是否支持某个公式，公式名区分大小写
print('SUM' in FORMULAE) # True
print('PI' in FORMULAE) # True
print('sum' in FORMULAE) # False
使用公式很简单，只要记得公式名和用法，直接像在 Excel 那样输入即可。例如，下面的求平均值、求和：
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
table = [['姓名', '语文', '数学', '英语', '平均分', '总分'],
['张三', 82, 96, 88],
['李四', 92, 86, 66],
['王五', 62, 98, 80]]
for row in table:
ws.append(row)
ws['E2'] = '=AVERAGE(B2,D2)' # 求平均值
ws['F2'] = '=SUM(B2:D2)' # 求和
wb.save('test.xlsx')
6.2 复制公式
用过 Excel 的同学都知道，当某个单元格使用了公式，可以通过拖动填充柄的方式快速复制上一个公式进行填充，用 openpyxl 复制公式如下：
from openpyxl import Workbook
from openpyxl.formula.translate import Translator
wb = Workbook()
ws = wb.active
table = [['姓名', '语文', '数学', '英语', '平均分', '总分'],
['张三', 82, 96, 88],
['李四', 92, 86, 66],
['王五', 62, 98, 80]]
for row in table:
ws.append(row)
ws['E2'] = '=AVERAGE(B2,D2)' # 求平均值
ws['F2'] = '=SUM(B2:D2)' # 求和
# 复制公式：E3:E4使用上面E2的求平均值公式
for row in ws['E3:E4']:
for cell in row:
# ws['E3'] = Translator(formula='=AVERAGE(B2,D2)', origin='E2').translate_formula('E3')
cell.value = Translator(formula='=AVERAGE(B2,D2)', origin='E2').translate_formula(cell.coordinate)
# 复制公式：F3:F4使用上面F2的求和公式
for row in ws['F3:F4']:
for cell in row:
# ws['F3'] = Translator(formula='=SUM(B2:D2)', origin='F2').translate_formula('F3')
cell.value = Translator(formula='=SUM(B2:D2)', origin='F2').translate_formula(cell.coordinate)
wb.save('test.xlsx')
7 插入图表
Excel 支持的图表类型还挺多的，包括柱状图、折线图、饼图、雷达图等，2D 和 3D 都有，而且支持很多自定义配置，例如颜色、大小、位置等。因为内容较多，所以这里以折线图为例，其他图表类型大家可以参考官方文档：https://openpyxl.readthedocs.io/en/stable/charts/introduction.html
from openpyxl import Workbook
from openpyxl.chart import LineChart, Reference
# 新建一个工作簿
wb = Workbook()
# 获取默认的工作表
ws = wb.active
# 准备数据
table = [['姓名', '语文', '数学', '英语'],
['张三', 82, 96, 88],
['李四', 92, 86, 66],
['王五', 62, 98, 80]]
# 写入数据
for row in table:
ws.append(row)
# 新建一个折线图
chart = LineChart()
chart.title = '成绩折线图'
chart.style = 12
chart.x_axis.title = '科目'
chart.y_axis.title = '成绩'
# 选择数据范围
data = Reference(ws, min_col=1, min_row=2, max_col=4, max_row=4)
chart.add_data(data, from_rows=True, titles_from_data=True)
# 设置类别轴标签
categories = Reference(ws, min_col=2, min_row=1, max_col=4, max_row=1)
chart.set_categories(categories)
# 线条样式
s0 = chart.series[0]
s0.marker.symbol = 'star' # star为星形标记， 可选circle、dash、diamond、dot、picture、plus、square、star、triangle、x、auto
s0.marker.graphicalProperties.solidFill = 'FF0000' # 标记的填充颜色
s0.marker.graphicalProperties.line.solidFill = '0000FF' # 标记的线条颜色
# s0.marker.graphicalProperties.line.noFill = True # 设置为True则隐藏标记线条，但显示标记形状
s1 = chart.series[1]
s1.graphicalProperties.line.solidFill = 'FFFF00' # 线条颜色
s1.graphicalProperties.line.dashStyle = 'sysDot' # 虚线样式
s1.graphicalProperties.line.width = 80000 # 线条宽度
s2 = chart.series[2]
s2.smooth = True # 平滑曲线
# 插入折线图
ws.add_chart(chart, 'A8')
# 保存工作簿
wb.save('test.xlsx')
8 筛选和排序
8.1 筛选和排序
openpyxl 有提供对应的设置对表格进行筛选或排序，但是，只是添加筛选或排序选项，并不会真的对数据进行筛选或排序。
from openpyxl import load_workbook
# 打开本地Excel文件
wb = load_workbook('test.xlsx')
# 新建一个工作表
ws = wb.create_sheet('2024')
# 准备数据
table = [['姓名', '语文', '数学', '英语'],
['张三', 62, 96, 88],
['李四', 82, 86, 66],
['王五', None, 98, 80],
['赵六', 82, 92, 90]]
# 写入数据
for row in table:
ws.append(row)
# 选择数据范围
ws.auto_filter.ref = 'A1:D4'
# 添加筛选条件：选择第2列作为筛选条件（下标从0开始），并勾选需要筛选的数据项，blank=True表示包含空值。
ws.auto_filter.add_filter_column(1, ['82', '80'], blank=True)
# 添加排序条件：排序范围为B2:B4，descending=True表示降序。
ws.auto_filter.add_sort_condition('B2:B4', descending=True)
# 保存工作簿
wb.save('test.xlsx')
8.2 pandas 排序
虽然 openpyxl 不能真的实现排序，但是我们可以借助超级强大的 pandas 轻松实现排序。
import pandas as pd
# 读取上一步保存的Excel文件
df = pd.read_excel('test.xlsx', sheet_name='2024')
# 按“语文”和“数学”列降序排列，如果“语文”列数据相同，再按“数学”列降序排列。
df_sorted = df.sort_values(by=['语文', '数学'], ascending=False)
# 保存文件
df_sorted.to_excel('test_sorted.xlsx', sheet_name='2024', index=False)
9 只读、只写模式
说明：
前面我们使用的 normal 模式进行读写 Excel 文件，这是一种兼顾读写相对比较平衡的模式，但是，数据加载到内存占用的资源是比较大的，大概是文件的 50 倍，如果 Excel 文件本身就 10MB，加载之后程序需要占用 500MB 内存，这很不划算（大内存电脑请自动忽略），所以我们需要考虑是不是可以选择只读或只写模式以便提高性能。
9.1 只读模式
只读模式：
如果需要读取很大的 Excel 文件，但是又不改变和保存，例如只读取数值用于其他数据分析，这时候我们完全可以使用只读模式提高性能。
load_workbook()：
openpyxl.reader.excel.load_workbook(filename, read_only=False, keep_vba=False, data_only=False, keep_links=True, rich_text=False)
参数说明：
filename：要打开的文件对象。
read_only：是否只读，默认为 False。
keep_vba：是否使用 VBA 编程，默认为 False。
data_only：是否只加载数据值，即丢弃公式、排序等操作，默认为 False。
keep_links：是否保留超链接，默认为 True。
rich_text：是否保留单元格中的富文本格式，默认为 False。
from openpyxl import load_workbook
# 打开本地Excel文件，read_only=True表示只读
wb = load_workbook('test.xlsx', read_only=True)
# 获取默认的工作表
ws = wb.active
# 读取数据
for row in ws.rows:
for cell in row:
print(cell.value)
# 关闭工作簿（注意：读取完之后需要手动关闭避免内存泄露）
wb.close()
9.2 只写模式
只写模式：
如果文件是以写为主，可以在创建工作簿的时候指定为只写模式以便提高性能，不管文件有多大，都可以把内存保持在 10MB 以下。
Workbook()：
class openpyxl.workbook.workbook.Workbook(write_only=False, iso_dates=False)
参数说明：
write_only：是否只写，默认为 False。
iso_dates：是否将日期时间转换为时间戳字符串，默认为 False。
from openpyxl import Workbook
from openpyxl.cell import WriteOnlyCell
from openpyxl.styles import Font
from openpyxl.comments import Comment
# 新建一个工作簿，write_only=True表示只写
wb = Workbook(write_only=True)
# 新建一个工作表
ws = wb.create_sheet('2024')
# 准备数据
table = [['姓名', '语文', '数学', '英语'],
['张三', 82, 96, 88],
['李四', 92, 86, 66],
['王五', 62, 98, 80]]
# 写入数据（只能通过append写入数据）
for row in table:
ws.append(row)
# 如果需要设置字体、注释等操作，可以使用WriteOnlyCell
cell = WriteOnlyCell(ws, value='end')
cell.font = Font(name='微软雅黑', size=16)
cell.comment = Comment(text='这是注释', author='openpyxl')
ws.append([cell])
# 保存工作簿
wb.save('test.xlsx')
# 关闭工作簿（注意：写入完之后需要手动关闭避免内存泄露）
wb.close()
只写模式注意点：
1.需要通过 create_sheet() 新建工作表。
2.只能通过 append() 写入数据，不能通过单元格坐标写入数据（ws[‘A1’] = 'A1’或ws.cell(1, 1, ‘A1’)）。
3.只能调用一次 save()，之后不能再修改或保存，否则将引发 WorkbookAlreadySaved 异常。
扫一扫
1634
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
