先说结论：如果你每天的工作里有一部分是和 Excel 报表打交道——填数据、调格式、汇总各个来源的表格、发周报——那 openpyxl 这个库确实值得花一个下午好好了解一下。它不是处理 Excel 的唯一方案，但它是“用 Python 操作 xlsx 文件”这条路上最稳、最常用、生态最成熟的选择之一，和 Pandas 搭配起来几乎是自动化办公的标配组合。这篇文章我会从最基础的单元格读写讲起，一路聊到样式、公式、图表、大数据量优化，最后用 Pandas 联动收尾，并附一个可以直接改改拿去用的自动化日报汇总脚本。
适合谁看？刚接触 Python、想摆脱手工复制粘贴报表的办公族；已经会用 Pandas 但总觉得“导出 Excel 后格式太丑”的同学；以及想系统了解 openpyxl 能力边界、准备用它做内部工具的开发者。下面的内容不会绕弯子，都是我自己在项目里反复用过的写法，也包含一些网上教程不太会提到的坑。
1. 选型逻辑：为啥是 openpyxl，而不是 xlrd、xlwt 或 xlsxwriter
1.1 Excel 处理库的江湖关系
先理清这些库之间的关系，不然新手很容易装错包。Python 处理 Excel 的库不少，但各自定位差异很大：
| 库名 | 支持格式 | 主要能力 | 适合场景 |
|---|---|---|---|
| openpyxl | .xlsx / .xlsm | 读写、样式、图表、公式、条件格式 | 日常办公自动化，功能最均衡 |
| xlrd | .xls（旧版） | 只读 | 读取老式 .xls 文件，新版不再支持 .xlsx |
| xlwt | .xls（旧版） | 只写 | 生成旧版 .xls 文件 |
| xlsxwriter | .xlsx | 只写，功能丰富 | 生成样式复杂的报表，不支持读取 |
| pandas | 依赖底层引擎 | 读取/写入 DataFrame | 数据处理、批量整理 |
现在的 .xlsx 文件本质上是一个 zip 压缩包，里面是一堆 XML 定义的工作表内容。openpyxl 做的事情就是把这些 XML 解析成我们熟悉的“工作簿 - 工作表 - 单元格”对象结构，然后让用户像操作普通 Python 对象一样去改内容。
所以如果格式是 .xls 老古董，openpyxl 处理不了，需要 xlrd/xlwt 补位；如果只需要写不需要读，xlsxwriter 也够用。但大多数人的办公场景是“既要读别人发来的文件，又要生成自己的报表”，这种时候 openpyxl 就是最省事的选择。
1.2 安装和最小对象模型
安装没什么门槛：
pip install openpyxlopenpyxl 的对象模型很直观：Workbook 是一个 Excel 文件，Worksheet 是其中一个 Sheet，Cell 是某个格子。日常操作就是“打开工作簿 -> 选工作表 -> 定位单元格 -> 读写值”。
先看一个最小示例：
from openpyxl import Workbook wb = Workbook() ws = wb.active ws.title = "数据报表" ws["A1"] = "你好，openpyxl" ws["A2"] = 42 wb.save("demo.xlsx")这段代码创建了一个工作簿，给 A1 写字符串、A2 写数字，然后保存成文件。这三行就是 openpyxl 的全部核心逻辑，后面所有复杂操作都是在这个基础上叠加的。
反过来读取文件：
from openpyxl import load_workbook wb = load_workbook("demo.xlsx") ws = wb.active print(ws["A1"].value) # 你好，openpyxl print(ws["A2"].value) # 42这里有个使用习惯建议：写代码前想清楚这个文件是给人看的报表，还是机器之间交换数据的中间产物。如果是前者，样式、列宽、冻结窗格这些必须处理；如果是后者，直接写值就够了，样式只会拖慢速度。
2. 日常高频操作：单元格、区域遍历和样式控制的正确姿势
2.1 单元格读写与区域遍历的几种惯用法
单元格定位有两种方式，一种是直接使用类似 Excel 坐标的字符串写法，一种是行列号：
# 方式1：坐标字符串，可读性好 ws["B2"] = "销售额" # 方式2：行列数字，适合循环 for row in range(2, 10): ws.cell(row=row, column=2, value=row * 100)区域遍历最常用的三个 API 是 iter_rows、iter_cols 和 ws.iter_rows(values_only=True)。比如要读取一个表格的所有内容：
for row in ws.iter_rows(min_row=1, max_row=ws.max_row, min_col=1, max_col=ws.max_column): for cell in row: print(cell.coordinate, cell.value)写入一整块二维数据时，直接用 append 最省事：
rows = [ ["产品", "销量", "单价"], ["A", 120, 35.5], ["B", 98, 42.0], ["C", 150, 28.8], ] for row in rows: ws.append(row)append 会在当前工作表底部追加，不需要自己维护行号。我自己的习惯是：需要精确控制某个位置的写入用坐标定位，批量导入数据用 append，遍历数据用 iter_rows。这三个操作掌握了，基本办公场景就覆盖了大半。
2.2 样式与格式：能改的和必须小心的
openpyxl 的样式系统包括字体、填充、边框、对齐、数字格式等。给表头加个样式是经典需求：
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side header_font = Font(name="微软雅黑", size=11, bold=True, color="FFFFFF") header_fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid") center_align = Alignment(horizontal="center", vertical="center") for cell in ws[1]: cell.font = header_font cell.fill = header_fill cell.alignment = center_align这里有个新手容易踩的坑：openpyxl 的样式对象是“复制后赋值”的机制。也就是说，你不能先创建一个 Font 对象，后面改了它的属性，期待之前引用它的单元格自动更新。每个单元格拿到的是一个独立的样式副本。所以批量设置样式时，样式对象要定义在循环外面，然后把同一个对象赋给不同单元格，这样是没问题的；但如果想要不同单元格用不同颜色，就得在循环里分别创建新对象。
数字格式是另一个高频需求。把日期显示成“2025-06-18”，把数字显示成千分位，写法如下：
cell = ws["C2"] cell.value = 1234567.891 cell.number_format = "#,##0.00" from datetime import date dcell = ws["A2"] dcell.value = date(2025, 6, 18) dcell.number_format = "YYYY-MM-DD"注意 number_format 使用的是 Excel 的格式代码，不是 Python 的格式化语法。这个习惯必须养成，不然日期写入后常会变成一坨数字序列。
2.3 合并单元格、冻结窗格、分组和打印设置
合并单元格是制作报表表头的必经之路：
ws.merge_cells("A1:D1") ws["A1"] = "季度销售汇总" ws.unmerge_cells("A1:D1")合并后，只有左上角的单元格能正常写入内容和样式，其他区域的单元格是“被合并状态”，读写要绕过。取消合并时把 merge_cells 换成 unmerge_cells 即可，注意取消后原被覆盖的单元格是空的。
冻结窗格适合表头固定场景。想让第一行冻结不动：
ws.freeze_panes = "A2"这里赋值的坐标表示“从这个单元格左上角开始冻结”，A2 意味着第一行固定。想让第一行和第一列都冻住就写 B2。
行和列的分组在 openpyxl 里对应的是 Excel 的“组合”功能，适合做明细折叠：
ws.row_dimensions.group(2, 10, hidden=False) ws.column_dimensions.group("B", "F", hidden=True)打印设置这块容易被忽略，但真要把报表打印出来时，默认打印区域可能乱七八糟。简单设置一下：
ws.print_area = "A1:H50" ws.page_setup.orientation = "landscape" ws.page_setup.paperSize = ws.PAPERSIZE_A4 ws.oddHeader.center.text = "季度报表"3. 报表进阶：公式、条件格式、图表和数据验证
3.1 公式的写入策略：openpyxl 不计算，但可以给你写公式
往单元格里写公式，其实就是一个字符串：
ws["D2"] = "=SUM(B2:C2)"需要注意的关键点是：openpyxl 只负责把公式文本写进文件，它本身不会去计算结果。如果之后你用 openpyxl 读取这个文件的公式值，默认读到的是公式字符串，不是数值。
所以写公式前你要想清楚：这个文件最终是给谁看的。如果是给 Excel/WPS 用户打开看的，公式写完保存，用户打开时软件会自动计算，没问题。如果要用程序读取计算结果，就需要在生成文件时用带公式的软件打开并保存一遍，或者干脆用 Python 自己把结果算出来再写。
读取已存在文件中的公式值时，load_workbook 有个 data_only 参数：
wb = load_workbook("report.xlsx", data_only=True)开启后读取到的是公式的“缓存值”。但这个缓存值只有在文件最后被 Excel 之类的软件保存过时才存在；如果文件是由 openpyxl 写的、从未被 Excel 打开过，data_only=True 读出来的公式位置是 None。
这点我在项目里吃过亏：脚本生成一批带 SUM 公式的报表，下游系统读取时全是 None，排查了半天才意识到 openpyxl 写的公式没有缓存结果。后来改成在脚本里直接计算 SUM 写入静态值，或者用一套本地计算逻辑，才彻底解决。
3.2 条件格式：规则引擎的正确打开方式
条件格式适合做“数据高亮”，比如把销量低于某个阈值的单元格标红。openpyxl 里用 Rule 对象来定义：
from openpyxl.formatting.rule import CellIsRule green_fill = PatternFill(start_color="C6EFCE", end_color="C6EFCE", fill_type="solid") red_fill = PatternFill(start_color="FFC7CE", end_color="FFC7CE", fill_type="solid") ws.conditional_formatting.add( "B2:B100", CellIsRule(operator="greaterThan", formula=["100"], fill=green_fill) ) ws.conditional_formatting.add( "B2:B100", CellIsRule(operator="lessThan", formula=["50"], fill=red_fill) )公式参数要写成列表形式，因为条件格式支持公式数组。实际项目里，我用得更多的其实是 FormulaRule，它支持任意表达式，灵活性大很多。比如高亮整行中符合条件的记录：
from openpyxl.formatting.rule import FormulaRule # B列为状态，值为“已完成”时A到D全部标绿 ws.conditional_formatting.add( "A1:D100", FormulaRule(formula=['$B1="已完成"'], fill=green_fill) )注意 FormulaRule 里的公式是相对引用思维，需要按 Excel 规则写行号。第一行对应公式里的 1，这就是为什么 $B1 里的“1”要跟着范围起点走。
3.3 图表：不用 VBA 也能出折线图和柱状图
openpyxl 的图表模块支持柱状图、条形图、折线图、饼图等常用类型。创建图表时不看工具栏里的图标是大坑，代码非常简单：
from openpyxl.chart import BarChart, Reference chart = BarChart() chart.type = "col" chart.title = "各产品销售对比" chart.y_axis.title = "销量" chart.x_axis.title = "产品" data = Reference(ws, min_col=2, min_row=1, max_row=5, max_col=2) # 数据区域 cats = Reference(ws, min_col=1, min_row=2, max_row=5) # 类别区域 chart.add_data(data, titles_from_data=True) chart.set_categories(cats) ws.add_chart(chart, "F2")关键点是 Reference 的坐标理解。add_data 的数据区域如果包含表头行，加 titles_from_data=True 可以自动把首行当系列名。图表插入后会生成一个独立的图表对象，位置在他的坐标，Excel 打开时图表是活着的。
折线图、饼图就是换个类名的问题，用法一致。说实话，日常自动化办公里图表能做到这一步，已经超过大半手工报表了。
3.4 下拉列表与数据验证
给报表加下拉选项用 DataValidation。比如给“状态”列加一个“已完成/进行中/未开始”的下拉：
from openpyxl.worksheet.datavalidation import DataValidation dv = DataValidation(type="list", formula1='"已完成,进行中,未开始"', allow_blank=True) dv.add("C2:C20") ws.add_data_validation(dv)注意公式里用逗号分隔选项，整个字符串要用英文双引号包裹。如果选项很多，公式里可以引用一个隐藏 Sheet 的区域。这个功能做数据录入工具时很实用，但注意 DataValidation 的列表选项公式长度不能超过 Excel 的限制，特别长的选项列表建议改成引用区域。
4. 别让几十万行拖垮内存：write_only 和 read_only 模式
4.1 openpyxl 藏不住的内存问题
openpyxl 的默认行为是把整个工作表对象构建在内存中操作。平时几千行、几百列完全没问题，但到了十万行以上的级别，内存占用开始明显飙升，甚至报 MemoryError。原因很简单：每个单元格都是一个完整对象，里面有值、样式、坐标等属性，一行几百列，几十万行的对象数量非常庞大。
这时候需要的是 write_only 模式。它牺牲了“能回头改数据、能读已有文件”的能力，换取了极速写入和几乎恒定的内存占用。
4.2 write_only 模式写入几十万行
from openpyxl import Workbook wb = Workbook(write_only=True) ws = wb.create_sheet("big_data") # 一次性写入表头 ws.append(["id", "name", "value", "create_time"]) # 模拟批量写入，来自数据库或文件 for i in range(1, 300001): ws.append([i, f"用户{i}", i * 1.5, "2025-01-01"]) wb.save("big_data.xlsx")write_only 模式下工作表没有行号的概念，只能用 append 顺序写入，也不能在写完后修改前面已写入的行。对“程序生成新文件”的场景完全够用，写入几十万行数据通常几秒钟就能完成。
4.3 read_only 模式配合高效读取
读取超大 Excel 文件时，用普通模式加载容易把机器搞崩。read_only 模式配合 iter_rows 可以边读边丢，内存占用很低：
wb = load_workbook("big_data.xlsx", read_only=True, data_only=True) ws = wb.active for row in ws.iter_rows(min_row=2, values_only=True): # 逐行处理，这里可以做一些过滤、统计 print(row) wb.close()read_only 模式下拿到的不是完整对象，读取性能也不错，但要注意它的限制：不能修改内容，对单元格样式不可读写。单纯做数据抽取完全够用。如果发现读取还是很慢，直接上 Pandas 更省心，Pandas 的 read_excel 底层引擎就是 openpyxl，后面会细说。
5. Pandas 联动：DataFrame 和 Excel 之间的桥
5.1 读取和写入的默认引擎其实是 openpyxl
Pandas 并没有自己实现 Excel 解析，读 xlsx 时默认用的是 openpyxl。也就是说，Pandas 读写 Excel 的每一行代码，底层都有 openpyxl 在跑。
日常最常见的读取方式：
import pandas as pd df = pd.read_excel("销售明细.xlsx", sheet_name="Sheet1", header=0)这一步背后就是 openpyxl 解析文件，再把数据转成 DataFrame。所以 Pandas 读不了的诡异文件，openpyxl 大概率也读不了，反过来同理。
写入时把 DataFrame 变成 Excel：
df.to_excel("output.xlsx", index=False)这里要重点提 index=False。不写的话，DataFrame 的行索引会被当作第一列写进表格，变成一列莫名其妙的“0、1、2、3”，这是新手最容易遇到的“表情失控”问题。
5.2 用 Pandas 写入时精确控制格式
Pandas 的 to_excel 决定数据，openpyxl 决定格式。两者可以混合使用：先用 Pandas 把数据写进文件，然后用 openpyxl 重新打开加样式。
import pandas as pd from openpyxl import load_workbook from openpyxl.styles import Font, PatternFill from openpyxl.utils.dataframe import dataframe_to_rows df = pd.DataFrame({ "产品": ["A", "B", "C"], "销量": [120, 98, 150], "单价": [35.5, 42.0, 28.8], }) df.to_excel("report.xlsx", index=False, sheet_name="汇总") wb = load_workbook("report.xlsx") ws = wb["汇总"] for cell in ws[1]: cell.font = Font(bold=True, color="FFFFFF") cell.fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid") wb.save("report.xlsx")这样既省去手动逐格写数据的体力活，又能拿到想要的外观。另一个更精细的姿势是用 dataframe_to_rows 把 DataFrame 转成行迭代器，手动写入，这样每个单元格都能单独控制：
ws = wb.create_sheet("数据") for r_idx, row in enumerate(dataframe_to_rows(df, index=False, header=True), start=1): for c_idx, value in enumerate(row, start=1): ws.cell(row=r_idx, column=c_idx, value=value)这种方式适合需要写合并单元格、公式、跨 Sheet 引用的复杂模板。
5.3 追加 Sheet 而不破坏原文件
实际工作中经常遇到“已有报表要追加一个新的 Sheet”。直接用 to_excel 会覆盖原文件，正确做法是借助 openpyxl 打开原工作簿，挂到 ExcelWriter 上：
from openpyxl import load_workbook import pandas as pd wb = load_workbook("report.xlsx") with pd.ExcelWriter("report.xlsx", engine="openpyxl", mode="a", if_sheet_exists="overlay") as writer: writer.book = wb writer.sheets = {ws.title: ws for ws in wb.worksheets} df2.to_excel(writer, sheet_name="月度明细", index=False)这里三行挂载很关键：writer.book 是原工作簿对象，writer.sheets 把工作表的名字和对象对应起来。不做这两行，Pandas 不知道工作簿里已经有哪些 Sheet，会直接覆盖。
6. 完整实战：一个自动化日报汇总小项目
6.1 需求与文件结构
我之前的项目里有个高频需求：每天从多个业务表格中提取关键指标，汇总成一张日报表，发出去给人看。在做完整流程前，我们先明确目录结构：
daily_report/ ├── data/ │ ├── 华东.xlsx │ ├── 华南.xlsx │ └── 华北.xlsx ├── scripts/ │ └── make_report.py └── output/每个分表的格式大概是这样：第一行是标题，第二行是表头，下面是具体数据：
| 日期 | 订单量 | 销售额 | 退款金额 |汇总逻辑是：读取每个文件，加总订单量、销售额、退款金额，最后输出带样式的日报表，再加上一张本月趋势折线图。
6.2 核心汇总逻辑实现
from openpyxl import load_workbook from openpyxl.styles import Font, PatternFill, Alignment, Border, Side from openpyxl.chart import LineChart, Reference from datetime import date import os, glob def extract_stats(file_path): wb = load_workbook(file_path, data_only=True) ws = wb.active total_orders = 0 total_sales = 0.0 total_refund = 0.0 for row in ws.iter_rows(min_row=3, values_only=True): if row[0] is None: continue total_orders += row[1] total_sales += row[2] total_refund += row[3] return total_orders, total_sales, total_refund file_list = glob.glob("data/*.xlsx") summary = [] for f in file_list: name = os.path.basename(f).split(".")[0] orders, sales, refunds = extract_stats(f) summary.append([name, orders, sales, refunds])代码里从第三行开始遍历，是为了跳过标题和表头。values_only=True 拿到的是一行元组，直接按下标取值，速度快，也不会产生大量 Cell 对象。
6.3 样式美化与趋势图输出
汇总数据拿到后，生成最终报表：
wb = Workbook() ws = wb.active ws.title = "日报汇总" ws.append(["区域", "订单量", "销售额", "退款金额"]) for row in summary: ws.append(row) # 添加汇总行 ws.append(["合计", sum(r[1] for r in summary), sum(r[2] for r in summary), sum(r[3] for r in summary)]) # 表头样式 header_style = Font(name="微软雅黑", size=11, bold=True, color="FFFFFF") header_fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid") for cell in ws[1]: cell.font = header_style cell.fill = header_fill cell.alignment = Alignment(horizontal="center", vertical="center") # 列宽 ws.column_dimensions["A"].width = 12 ws.column_dimensions["B"].width = 16 ws.column_dimensions["C"].width = 16 ws.column_dimensions["D"].width = 16 # 生成折线图 chart = LineChart() chart.title = "各区域订单量对比" chart.style = 13 data = Reference(ws, min_col=2, min_row=1, max_row=ws.max_row) cats = Reference(ws, min_col=1, min_row=2, max_row=ws.max_row - 1) chart.add_data(data, titles_from_data=True) chart.set_categories(cats) ws.add_chart(chart, "F2") wb.save("output/日报汇总.xlsx")这个脚本在真实项目中可以直接定时任务跑起来，每天早上自动生成。需要注意的细节是：合计行不能出现在图表的数据区域之外，所以图表数据区域用 ws.max_row 时，类别区域要排除合计行。
6.4 流程复盘与可以复用的封装
跑完这个流程后，我会再做一次复盘。第一，数据源表如果有合并单元格、空行，iter_rows 时要做元素为 None 的判断。第二，金额列建议用 number_format 设置千分位。第三，所有文件路径全部用 os 或 pathlib 处理，硬编码斜杠在 Windows 和 Linux 上都会埋雷。
如果这个任务要重复使用，可以把它封装成一个函数：
def generate_daily_report(data_path, output_path, report_title="日报汇总"): # 原逻辑全部收进来，数据路径和输出路径做成参数 pass建议也把“读取分表”和“生成汇总表”拆成两个模块，前者以后可以换成从数据库读，后者可以换成发邮件时附件转发，改动量都受控。
7. 我踩过的坑与排查思路
7.1 公式读出来是 None
这是最容易让人崩溃的问题。读别人发来的 Excel，代码写着 values_only=True，公式单元格对应的值却是 None。真相是：openpyxl 读公式单元格返回的是公式字符串，除非你同时满足两个条件——文件在 Excel/WPS 里保存过产生了缓存值，且 load_workbook 加 data_only=True。判断办法很简单：打印 ws["A1"].value，如果字符串以 = 开头，情况就是这样。
解决办法也直白：要么要求上游在导出时用 Excel 另存一遍，要么自己在 Python 里计算。openpyxl 官方不打算内置计算引擎，所以别纠结。
7.2 日期变成了数字序列
写入日期后打开 Excel 看到的是一串像 45562 的数字，几乎人人都会遇到。原因是 openpyxl 写入日期时如果没有设置 number_format，Excel 显示的是日期序列号。解决办法就是在写入 datetime 后立即设置单元格的数字格式，往回带 number_format="YYYY-MM-DD HH:MM:SS"。
多列日期时我是用循环统一设置的：
ws[f"C{row}"].number_format = "YYYY-MM-DD"7.3 文件路径导致 PermissionError
Windows 下最常见的报错是 PermissionError: [Errno 13]，十有八九是目标文件正在被 Excel 打开。openpyxl 的 save 本质上是创建一个新文件然后覆盖，如果原文件被占用，写入权限会被拒绝。排查思路：先关掉所有 Excel 窗口，确认没有进程占用；如果程序要反复读写同一个文件，数据库或缓存层才是正确解法，不要硬刚同一个文件。
7.4 写入报错：非法字符
有时候明明写的是简单字符串，保存却报 IllegalCharacterError。原因是字符串里带了一些 Excel 不支持的不可见控制字符，比如 \x00、\x0b。处理办法是写入前做一次清洗，把 ASCII 控制字符全部剔除：
import re def clean_excel_text(text): if not isinstance(text, str): return text return re.sub(r"[\x00-\x1f]", "", text)这个函数我现在所有写 Excel 的脚本都会带着，省得到处找 bug。
最后说点实在的。我用 openpyxl 将近四年，最大的体会是：这个库的功能比大多数人想象的多，但边界也清楚——它不适合当一个数据分析引擎，也不适合替代数据库。真正的定位是“让 Excel 在自动化流程里当一个合格的输入/输出角色”。把数据计算、清洗交给 Pandas，把格式、图表、呈现交给 openpyxl，两者配合才是自动化办公最高效的组合。希望这篇内容对你有用，也欢迎你拿着自己的场景去试一遍，跑通了你会回来感谢这个库的。
