• 文档
• 教程

openpyxl是 Python 生态中最流行的 xlsx 格式 Excel 文件读写三方库之一，本教程（源自 Python-100-Days 第 25 天内容）将带你完整掌握用openpyxl加载工作簿、读写单元格、调整样式、写入公式以及插入统计图表的能力。学完本节，你可以独立实现办公自动化中的 Excel 数据导入导出、报表生成与表格样式美化等常见需求，并为后续在数据分析项目中使用 pandas 处理 Excel 数据打下基础。

openpyxl：读写 xlsx 的瑞士军刀

为什么选择 openpyxl

Excel 是微软面向 Windows 和 macOS 开发的一款电子表格软件，凭借直观的界面、出色的计算功能和图表工具，一直是个人计算机上最流行的数据处理软件。虽然市面上还有 Google Sheets、LibreOffice Calc、Numbers 等竞品，但它们基本都兼容 Excel 文件的读写，因此掌握 Python 操作 Excel 的能力，可以让日常办公自动化更轻松，也让商业项目中常见的 Excel 导入导出功能变得更加可控。

在本教程的前一章（24.Python读写Excel文件-1.md）中，我们讲解了基于xlrd和xlwt操作旧版xls格式文件的方法——前者只负责读、后者只负责写，操作相对割裂。而本章的主角openpyxl则同时支持读和写，且它在样式编辑、公式计算、数据透视和图表插入等方面的便捷性都更胜一筹，是处理 Office 2007 及以后版本（即xlsx格式）文件的首选方案。

需要强调的是：openpyxl不支持Office 2007 以前版本的 Excel 文件（xls格式），如果确需兼容旧格式，请回看 Day21-30/24.Python读写Excel文件-1.md 中的xlrd/xlwt方案。

安装 openpyxl

使用pip即可完成安装：

pip install openpyxl

在仓库的 Jupyter Notebook 数据分析示例中，也保留了同样的安装方式，例如 Day66-80/code/day04.ipynb 中的%pip install openpyxl，以及 Day66-80/code/day01.ipynb 中一次性安装numpy pandas matplotlib openpyxl的做法，说明openpyxl在数据分析链路中通常作为 Excel 文件的底层读写引擎出现。

读取 Excel 文件

假设当前文件夹下有一个名为“阿里巴巴2020年股票数据.xlsx”的 Excel 文件，可以通过如下代码加载并查看其内容：

import datetime import openpyxl # 加载一个工作簿 ---> Workbook wb = openpyxl.load_workbook('阿里巴巴2020年股票数据.xlsx') # 获取工作表的名字 print(wb.sheetnames) # 获取工作表 ---> Worksheet sheet = wb.worksheets[0] # 获得单元格的范围 print(sheet.dimensions) # 获得行数和列数 print(sheet.max_row, sheet.max_column) # 获取指定单元格的值 print(sheet.cell(3, 3).value) print(sheet['C3'].value) print(sheet['G255'].value) # 获取多个单元格（嵌套元组） print(sheet['A2:C5']) # 读取所有单元格的数据 for row_ch in range(2, sheet.max_row + 1): for col_ch in 'ABCDEFG': value = sheet[f'{col_ch}{row_ch}'].value if type(value) == datetime.datetime: print(value.strftime('%Y年%m月%d日'), end='\t') elif type(value) == int: print(f'{value:<10d}', end='\t') elif type(value) == float: print(f'{value:.4f}', end='\t') else: print(value, end='\t') print()

提示：上面代码中使用的示例文件“阿里巴巴2020年股票数据.xlsx”可通过教程配套的云盘链接获取。读者也可以在仓库中找到真实的 xlsx 文件自行练习，例如 Day66-80/code/res/2020年销售数据.xlsx 与 Day66-80/code/res/tips.xlsx，它们是 pandas 数据分析章节（Day66-80/75.深入浅出pandas-4.md）中反复使用的真实数据文件。

这段代码揭示了openpyxl读取 Excel 的完整对象层级：

• Workbook（工作簿）：openpyxl.load_workbook()返回工作簿对象，sheetnames属性给出所有工作表名称列表；
• Worksheet（工作表）：通过wb.worksheets[0]按索引或wb['表名']按名称获取；dimensions返回数据区域范围（如A1:G255），max_row/max_column返回行数与列数；
• Cell（单元格）：见下文的两种取值方式。

单元格的两种获取方式

openpyxl获取指定单元格有两种方式，务必区分清楚：

1. cell方法：sheet.cell(row, column)。需要注意，该方法的行索引和列索引都是从1开始的，这是为了照顾用惯了 Excel 的人的习惯，与xlrd中从 0 开始的索引规则完全不同；
2. 索引运算（坐标）：sheet['C3']、sheet['G255']，直接使用 Excel 风格的列字母 + 行号坐标定位单元格。

无论哪种方式，最终都通过单元格对象的value属性获取单元格的值。

切片获取多单元格

通过类似sheet['A2:C5']或sheet['A2':'C5']的切片操作可以一次获取多行多列，该操作返回嵌套的元组（外层元组对应行，内层元组对应行内的单元格），相当于取到了数据区域的子矩阵。这种切片能力在批量读取与数据预处理时非常实用。

数据类型的格式化处理

由于 Excel 单元格中可能存放日期、整数、浮点数、字符串等多种类型的数据，上面的读取循环中针对datetime.datetime、int、float分别做了格式化输出（日期转为“年月日”文本、浮点数保留 4 位小数、整数左对齐占位 10 位）。这种“按类型分流处理”的写法，正是真实业务中导出可读报表的常见套路，与本教程第 24 天中xlrd章节对日期和数值的格式化思路一脉相承。

写 Excel 文件

使用openpyxl写入数据同样遵循“工作簿 → 工作表 → 单元格 → 保存”的步骤。下面的代码生成一张包含 5 名学生 3 门课程成绩的表格：

import random import openpyxl # 第一步：创建工作簿（Workbook） wb = openpyxl.Workbook() # 第二步：添加工作表（Worksheet） sheet = wb.active sheet.title = '期末成绩' titles = ('姓名', '语文', '数学', '英语') for col_index, title in enumerate(titles): sheet.cell(1, col_index + 1, title) names = ('关羽', '张飞', '赵云', '马超', '黄忠') for row_index, name in enumerate(names): sheet.cell(row_index + 2, 1, name) for col_index in range(2, 5): sheet.cell(row_index + 2, col_index, random.randrange(50, 101)) # 第四步：保存工作簿 wb.save('考试成绩表.xlsx')

关键 API 说明：

• openpyxl.Workbook()：创建空白工作簿，默认自带一个名为Sheet的工作表；
• wb.active：获取当前激活的工作表，sheet.title = '期末成绩'用于重命名工作表；
• sheet.cell(row, col, value)：第三个参数即要写入的值，写入时同样遵循行列从 1 开始计数的规则；
• wb.save(文件名)：将工作簿持久化到磁盘。

对比第 24 天xlwt的写法（wb.add_sheet()添加工作表、sheet.write()写入单元格），openpyxl的wb.active一步到位获取可写工作表，代码更为简洁直观。

调整样式与公式计算

openpyxl最大的优势之一，就是可以通过单元格对象（Cell对象）的属性直接调整样式，包括字体（font）、对齐（alignment）、边框（border）等。下面代码在上一步生成的“考试成绩表.xlsx”基础上，追加“平均分”列并完成公式计算与样式美化：

import openpyxl from openpyxl.styles import Font, Alignment, Border, Side # 对齐方式 alignment = Alignment(horizontal='center', vertical='center') # 边框线条 side = Side(color='ff7f50', style='mediumDashed') wb = openpyxl.load_workbook('考试成绩表.xlsx') sheet = wb.worksheets[0] # 调整行高和列宽 sheet.row_dimensions[1].height = 30 sheet.column_dimensions['E'].width = 120 sheet['E1'] = '平均分' # 设置字体 sheet.cell(1, 5).font = Font(size=18, bold=True, color='ff1493', name='华文楷体') # 设置对齐方式 sheet.cell(1, 5).alignment = alignment # 设置单元格边框 sheet.cell(1, 5).border = Border(left=side, top=side, right=side, bottom=side) for i in range(2, 7): # 公式计算每个学生的平均分 sheet[f'E{i}'] = f'=average(B{i}:D{i})' sheet.cell(i, 5).font = Font(size=12, color='4169e1', italic=True) sheet.cell(i, 5).alignment = alignment wb.save('考试成绩表.xlsx')

样式对象解析

• Font：控制字体，常用参数有name（字体名称，如华文楷体，要求本机已安装该字体）、size（字号）、bold（加粗）、italic（斜体）、color（颜色，十六进制 RGB 字符串，如ff1493、4169e1）；
• Alignment：控制对齐，horizontal可取center/left/right，vertical可取center/top/bottom；
• Side与Border：Side定义单边线条（color+style，style支持mediumDashed等线型），Border将四条边（left/right/top/bottom）组合成一个完整边框；
• row_dimensions/column_dimensions：分别按行号与列字母设置行高（height）和列宽（width）。

公式计算：完全沿用 Excel 语法

做公式计算时，可以完全按照 Excel 中的操作方式——直接把以=开头的公式字符串写入单元格即可：

sheet[f'E{i}'] = f'=average(B{i}:D{i})'

例如i = 2时写入=average(B2:D2)，由 Excel/WPS 打开文件时自动计算语文、数学、英语三科的平均分。这种“公式即字符串”的设计大大降低了学习成本：你在 Excel 里怎么写公式，在openpyxl里就怎么写字面量。

注意：openpyxl写入公式后，单元格的值需要由 Excel 应用打开文件时才能计算得出；如果希望在 Python 端直接读取计算结果，需要额外借助公式求值引擎，这一点在纯写入场景下通常无需关心。

生成统计图表

openpyxl可以直接向 Excel 中插入统计图表，做法与在 Excel 中插入图表大体一致：创建图表对象 → 设置图表属性 → 绑定数据（Reference）→ 添加到工作表。下面的代码生成一张分组柱状图：

from openpyxl import Workbook from openpyxl.chart import BarChart, Reference wb = Workbook(write_only=True) sheet = wb.create_sheet() rows = [ ('类别', '销售A组', '销售B组'), ('手机', 40, 30), ('平板', 50, 60), ('笔记本', 80, 70), ('外围设备', 20, 10), ] # 向表单中添加行 for row in rows: sheet.append(row) # 创建图表对象 chart = BarChart() chart.type = 'col' chart.style = 10 # 设置图表的标题 chart.title = '销售统计图' # 设置图表纵轴的标题 chart.y_axis.title = '销量' # 设置图表横轴的标题 chart.x_axis.title = '商品类别' # 设置数据的范围 data = Reference(sheet, min_col=2, min_row=1, max_row=5, max_col=3) # 设置分类的范围 cats = Reference(sheet, min_col=1, min_row=2, max_row=5) # 给图表添加数据 chart.add_data(data, titles_from_data=True) # 给图表设置分类 chart.set_categories(cats) chart.shape = 4 # 将图表添加到表单指定的单元格中 sheet.add_chart(chart, 'A10') wb.save('demo.xlsx')

图表 API 要点

• Workbook(write_only=True)：只写模式创建的工作簿不可读、只可写，配合sheet.append(row)逐行追加数据，在大批量写入时内存占用更低、速度更快；
• BarChart：柱状图对象，type='col'表示垂直柱状图（'bar'则为水平条形图），style控制图表内置样式编号，title为图表标题；x_axis.title/y_axis.title设置横纵轴标题；shape控制柱形形状（数值对应不同样式）；
• Reference：定义图表引用的数据区域。data引用B1:C5（包含表头行，因为设置了titles_from_data=True，表头将被作为系列名称），cats引用A2:A5作为分类轴（横轴）标签；
• chart.add_data(data, titles_from_data=True)：绑定数值系列并声明首行为系列标题；chart.set_categories(cats)：绑定分类标签；
• sheet.add_chart(chart, 'A10')：将图表锚定到工作表的A10单元格位置。

运行上面的代码，打开生成的demo.xlsx即可看到如下效果：图表标题为“销售统计图”，横轴为“手机 / 平板 / 笔记本 / 外围设备”四类商品，纵轴为“销量”，蓝色与红色两组柱分别对应销售 A 组与销售 B 组的销售数据，与表格中的原始数据一一对应。

从 openpyxl 到 pandas：数据分析场景的进阶路径

本教程在总结部分明确指出：如果数据体量较大或处理方式较复杂，推荐使用 pandas 库。这一点在仓库后续章节得到了充分印证——在 Day66-80/73.深入浅出pandas-2.md 中，使用pd.read_excel('data/2022年股票数据.xlsx', sheet_name='AMZN', index_col='Date')即可按表单名加载指定数据；Day66-80/code/day05.ipynb 中则通过pd.read_excel('res/2020年销售数据.xlsx', sheet_name='data')读取真实销售数据文件。事实上，pandas 读取 xlsx 时默认正是借助openpyxl作为底层引擎，因此掌握本节内容，也就理解了 pandas Excel 能力的根基。

选择建议可以归纳为：

总结

通过本节的学习，你已掌握openpyxl操作 Excel 文件的完整能力：

1. 读：load_workbook加载工作簿，通过cell()/ 坐标索引 / 切片获取单元格数据，并对日期、数值等类型做格式化输出；
2. 写：Workbook()+wb.active+cell()三步完成建表与填数，save()落盘；
3. 样式与公式：Font/Alignment/Border/Side对象化设置样式，以=average(...)形式直接写入公式；
4. 图表：BarChart+Reference绑定数据区域，add_chart将图表插入工作表；
5. 选型：旧格式用xlrd/xlwt，新格式用openpyxl，复杂数据分析交给 pandas。

掌握了这些方法，日常办公中大量繁琐的 Excel 处理工作——例如把多个格式相同的 Excel 文件合并到一个文件、从多个文件或表单中提取指定数据——都可以交给 Python 脚本一键完成，这正是办公自动化与商业项目中 Excel 导入导出功能的实现基础。

• 文档
• 教程