Python操作Excel:openpyxl vs pandas对比与实战

我一开始的答案是"都装上"。直到有一次，我用 pandas 生成了一张带条件着色的良率报表，兴冲冲发给主管，他回了句："你这表怎么一点格式都没有，红都没有红一下？"

那一刻我才想明白——它俩根本不是一个赛道的东西。

openpyxl 管的是"这张表长什么样"：单元格、颜色、边框、列宽、冻结、图表、公式。 pandas 管的是"这堆数据怎么算"：清洗、筛选、分组、聚合、透视。

选型口诀就一句：pandas 负责算，openpyxl 负责长得好。

今天用一份真实的良率明细把它跑通，从生成到分析到输出，最后给你一张直接抄的对照表和一组实测性能数据。

解决方案概览

整条链路分四段，每段只用该用的那个工具：

外加：12 条场景对照表 + 2 万行写入性能实测（结果有点反直觉）。

分步实操

第 1 步：先立数据真相源，别急着写 Excel

这是我被数据坑过之后养成的习惯：先把所有数字锁在一个函数里，用 assert 校验通过，再往表里填。

不信你回忆一下，有多少次报表发出去才发现"良品+不良+报废"对不上投入？

defbuild_rows():    rnd = random.Random(20260930)   # 种子固定，结果可复现    rows = [...]for i, r inenumerate(rows):        qty_in = rnd.choice([800, 1000, 1200, 1500])        defect = int(qty_in * defect_rate)        scrap  = rnd.randint(2, 15)        good   = qty_in - defect - scrap        # 倒推良品，保证等式成立        r["投入(pcs)"] = qty_in        r["良品(pcs)"] = good        r["不良(pcs)"] = defect        r["报废(pcs)"] = scrap        r["良率"] = good / qty_in               # 全精度保留，别在这步 roundreturn rowsdefassert_truth_source():for i, r inenumerate(ROWS):assert r["良品(pcs)"] + r["不良(pcs)"] + r["报废(pcs)"] == r["投入(pcs)"]assertabs(r["良率"] - r["良品(pcs)"] / r["投入(pcs)"]) < 1e-9

一个小细节值得单独说：良率不要在这一步 round(4)。我曾经这么干，结果自断言直接报错——0.9091 和 0.909090... 差了 5e-5，超过 1e-6 的容差。正确做法是真相源保持全精度，显示的四舍五入交给 Excel 的 number_format。

第 2 步：openpyxl 生成"给人看"的明细表

这一段 pandas 帮不上忙，全靠 openpyxl：

from openpyxl import Workbookfrom openpyxl.styles import PatternFill, Font, Border, Side, Alignmentfrom openpyxl.formatting.rule import CellIsRulews.append(HEADERS)for cell in ws[hdr_row]:    cell.fill = PatternFill("solid", fgColor="1F4E79")    cell.font = Font(color="FFFFFF", bold=True)for r in ROWS:    ws.append([...])# 数字格式：千分位和百分比是显示层的事row[5].number_format = "#,##0"# 投入row[9].number_format = "0.00%"# 良率# 条件着色：良率低于 90% 自动标红（重点！用 Excel 原生规则，改数据自动跟着变）ws.conditional_formatting.add(f"A{first}:J{last}",    CellIsRule(operator="lessThan", formula=["0.9"],               fill=PatternFill("solid", fgColor="FFC7CE"),               font=Font(color="9C0006")),)ws.freeze_panes = "A4"# 冻结表头ws.auto_filter.ref = f"A{hdr_row}:J{last}"# 加筛选箭头

这里有个必须区分清楚的概念：条件着色（conditional_formatting）是活的规则，写死填充色是死的。 主管拿到表改了个数，前者会自动变色后者不会。这是我用死颜色被问"为什么改了数不红"之后才改过来的。

第 3 步：pandas 一行读入，groupby 出结论

同样的文件换个工具读，画风完全不同：

df = pd.read_excel(DETAIL_XLSX, sheet_name="良率明细", header=2)  # 标题占了前两行df.columns = HEADERSg = df.groupby("机台", as_index=False).agg(    投入=("投入(pcs)", "sum"), 良品=("良品(pcs)", "sum"), 不良=("不良(pcs)", "sum"))g["良率"] = g["良品"] / g["投入"]

实测跑出来的结果，非常有意思：

总投入 35,700 pcs / 良品 32,589 / 不良 2,823 / 整体良率 91.29%  ✅ 达标按产品：PA-3302 90.90% | PA-4410 91.26% | PA-2201 91.62%   —— 全都达标按机台：L04 88.01% ❌ | L02 88.13% ❌ | L03 94.16% | L01 94.24%不良最多：L02（986 pcs）

整体 91.29% 看着达标，拆到机台一看，L04/L02 只有 88%。

这就是 pandas 的价值——它不只是省了几行循环，而是让你一眼看到平均数掩盖掉的东西。按 openpyxl 的写法，你得手写四个 dict 去累加，写完还得怀疑自己边界写错没；pandas 一行 groupby 出来，结果可以直接跟总数对账（代码里我也用 assert 拦了：分组口径加总必须等于明细加总）。

第 4 步：组合拳——pandas 写数，openpyxl 补妆

这是实际工作里用得最多的姿势：

# ① pandas 把算好的结果写进去with pd.ExcelWriter(SUMMARY_XLSX, engine="openpyxl") as writer:    prod.to_excel(writer, sheet_name="按产品", index=False)    mach.to_excel(writer, sheet_name="按机台", index=False)# ② openpyxl 回头给这张表补格式wb = load_workbook(SUMMARY_XLSX)for ws in wb.worksheets:for cell in ws[1]:        cell.fill = HDR_FILL        cell.font = HDR_FONT    ws.freeze_panes = "A2"for r inrange(2, ws.max_row + 1):        cell = ws[f"D{r}"]                      # 良率列        cell.number_format = "0.00%"if cell.value < 0.9:            cell.fill, cell.font = RED_FILL, RED_FONTwb.save(SUMMARY_XLSX)

顺序不能反：先 pandas 写值，再 openpyxl 补格式。反过来，你辛苦加的颜色会在 to_excel 重写时被冲得干干净净——这是我踩得最疼的一次，改了半小时的格式，一行 to_excel 全没了。

第 5 步：性能实测（结论反直觉）

我顺手做了个 2 万行的写入计时，结果跟很多人的直觉相反：

【写入 20,000 行耗时】  openpyxl（逐行 append）：1.30 s  pandas （整表 to_excel）：2.64 s  更快：openpyxl，约快 2.0 倍

为什么？ 因为 pandas.to_excel 底层就是调 openpyxl，还多扛了一层 DataFrame → 单元格的转换开销。

所以结论很明确：pandas 快在"算"，不在"写"。 纯要灌数据，老老实实用 openpyxl（海量行用 write_only=True 还能再快一截）。

附：12 条场景对照表（直接抄）

关键参数说明

• read_only=True：大文件只读模式，流式迭代，内存占用从"整表"降到"一行"。代价是拿不到部分样式信息。
• write_only=True：只写模式，不能回头改已写的单元格，但写海量行快得多。
• data_only=True：读公式的计算结果。注意前提是该文件被 Excel 打开并保存过，否则读出来是 None——Python 不会替 Excel 计算公式。
• header=2：标题占了前两行时必写。更稳的写法是用 ws.values 扫出真正的表头行再喂给 pandas，别硬编码。
• number_format：显示层格式化。0.00% / #,##0 / 0.000，永远不要在数据层 round。
• groupby(..., as_index=False)：加了才把分组键留成普通列，否则后面取列要 .reset_index()。
• ExcelWriter(engine="openpyxl")：想在一个工作簿里写多个 Sheet，必须用这个写法。

常见问题和避坑提醒

1. pandas 读到满屏 Unnamed: 0：多半是标题行/装饰行占了位置，header= 写错。先用 list(ws.values) 找出表头在哪一行再定参数，别猜。
2. to_excel 之后格式全没了：必然的，它重写整个文件。解决办法就是第 4 步的顺序——先写值再补妆。
3. 追加数据别用 to_excel：会把别人手工加的批注、颜色一起冲掉。用 ws.append。
4. data_only=True 读到 None：文件没被 Excel 打开计算过。要么先用 Excel 打开存一次，要么自己用 pandas 重算。
5. 良率 round 早了导致对账差一点点：真相源保持全精度，显示交给 number_format。这次我的自检就是被它拦下的。
6. 大表内存爆：read_only=True 流式读，或 pandas chunksize 分块；再大就别用 xlsx 了，换 parquet/csv。
7. .xls 老文件读不了：openpyxl 只支持 .xlsx/.xlsm。老格式用 xlrd，或者另存为新格式。
8. 日期读进来一会儿是 Timestamp 一会儿是字符串：读取时显式 parse_dates=["日期"]，写入时 date_format 统一。
9. 条件格式改了数不变色：确认用的是 conditional_formatting.add 的规则，不是直接设 cell.fill。

总结

回到最开始那个问题——openpyxl 和 pandas 选哪个？

别再二选一了，正确的问法是：这一步是在"算"，还是在"画"？

• 在算（清洗、筛选、分组、聚合）→ pandas
• 在画（表头、颜色、列宽、冻结、图表、公式）→ openpyxl
• 两件事都有 → pandas 写数，openpyxl 补妆

一句话收尾：pandas 负责算，openpyxl 负责长得好。

顺带说一句，这次跑出来的数据挺打脸的：整体良率 91.29% 妥妥达标，拆到机台才发现 L04/L02 只有 88%。报表好不好，很多时候就差这"拆一层"的一步。

领取资料

回复【Python Excel对比脚本】领取《openpyxl_vs_pandas实战对比.py》——单文件可直接运行，内置示例数据与完整注释：

• 真相源 dict + assert 勾稽（良品+不良+报废=投入）
• openpyxl 生成带条件着色的明细表（<90% 自动标红）
• pandas groupby 出产品/机台良率，抓 TOP 不良机台
• pandas 写数 + openpyxl 补妆的组合拳完整代码
• 12 条场景对照表 + 写入性能实测

三种运行模式：--selftest 跑勾稽断言 / --dry-run 只看计划不落盘 / --bench 20000 性能实测。覆盖前自动备份，不会把你做好的表冲掉。

第 44 篇 · 半导体工程师 AI 提效系列