Python 处理 Excel 的 常用5 个核心场景,把重复劳动交给脚本

月底的报表日，最常见的工作画面：从系统导出一份 Excel，手工筛选、去重、加总、调格式，再复制粘贴进邮件模板，一套流程两小时，下个月原样再来一遍。这类活儿的共同特征是规则完全确定、重复频率高、错一步就得从头来——恰好是脚本最擅长接管的那一类。这篇文章按五个核心场景往下走：读表、写表、改表、批量合并、以及大文件与格式的坑，每个场景给可直接套用的代码骨架与避坑清单，读完你能把自己手头那份手工流程拆成脚本，也知道每一步该交给哪个库。

1. 读表，pandas 与 openpyxl 的分工

两个库的定位一句话说清：pandas 把表读成 DataFrame，为的是算数——筛选、透视、合并、统计都是它的主场；openpyxl 把表读成单元格对象，为的是「摸到」每一个格子的位置、样式、批注与合并区域。读数据做分析用 pandas，读结构做检查用 openpyxl，多数项目两个都装。分不清这个定位，是 Excel 自动化踩坑的头号来源：拿 pandas 去改模板格式，一保存格式全丢；拿 openpyxl 去做数据透视，代码写成小作文还慢得要命。先认场景，再认库，顺序不能反。

import pandas as pdfrom openpyxl import load_workbookdf = pd.read_excel("销售明细.xlsx", sheet_name=0, header=0)wb = load_workbook("销售明细.xlsx", read_only=True)print(wb.sheetnames)          # 所有工作表名ws = wb["10月"]print(ws.max_row, ws.max_column)   # 数据区的行列数wb.close()

多表与不规则表头的处理也归这一节。一个文件里有多个同构工作表时，pd.read_excel 传 sheet_name=None 会一次性读成「表名到 DataFrame」的字典，配合循环免掉重复代码；表头不在第一行的报表（前面塞了标题行和说明），用 header 与 usecols 指明数据区起点，比读进来再删行干净得多。不规则结构读干净了，后面所有场景都会顺。

文件里有公式列时，pandas 读到的是公式算好的值还是公式本身，取决于写文件时的缓存。用 openpyxl 读就是另一回事——load_workbook 默认拿公式字符串，想拿计算结果得传 data_only=True，而且前提是这份文件曾被 Excel 打开保存过。来源不明的程序生成文件，先打开检查再进管道，能省掉半夜排查的功夫。同一个文件，data_only 传与不传读出的内容可能完全不同，团队里最好把读法定死在一个公共函数里，全组走同一个入口。

2. 写表与格式，报表的「体面」也在脚本里

机器写出来的表，最常见的差评是「不好看」：表头不突出、列宽挤成一团、没有冻结首行。这些用 openpyxl 都能写进脚本，一次定义，以后每次导出都自带格式。pandas 的 to_excel 负责灌数据，样式交给 openpyxl 在同一份文件上加工。顺序上记住「数据先落、样式后加」——先铺样式再重灌数据等于白干，重灌一次就要重铺一次。pandas 与 openpyxl 的接力就发生在保存之后：to_excel 生成文件，load_workbook 重新打开做加工，最后 save 收尾，一次导出两个库各干各的活。

import pandas as pdfrom openpyxl import load_workbookfrom openpyxl.styles import Font, PatternFill, Alignmentdf.to_excel("月报.xlsx", index=False, sheet_name="汇总")wb = load_workbook("月报.xlsx")ws = wb["汇总"]head_fill = PatternFill("solid", fgColor="4472C4")for cell in ws[1]:                       # 第一行 = 表头    cell.font = Font(bold=True, color="FFFFFF")    cell.fill = head_fill    cell.alignment = Alignment(horizontal="center")ws.column_dimensions["A"].width = 14     # 调列宽ws.freeze_panes = "A2"                   # 冻结表头wb.save("月报.xlsx")

这段骨架覆盖了报表格式需求的八成：加粗表头、填充色、居中、列宽、冻结窗格。需要日期列统一格式或金额列加千分位时，思路相同——定位到目标列，逐格改 cell.number_format。把这套样式逻辑封装成一个函数，所有导出脚本共用，全组的报表从此长得一样。判断格式该不该脚本化的标准很简单：如果同一套调整你每个月都要手工来一遍，它就该进代码；只出现一次的临时加工，直接在 Excel 里点两下更快，不必为了脚本化而脚本化。

3. 改表，在既有文件上做增量修改

第三类场景最容易踩库的坑：老板发来的模板里已经有格式、图表和数据有效性，你要做的只是往里追加几行。这时千万不能用 pandas 重写整个文件——to_excel 会生成一个全新工作簿，模板里的格式、公式与多出来的工作表全部丢失。正确姿势是 openpyxl 打开原文件，原位追加。这种丢失往往是静默发生的：脚本不报错、数据看着也对，直到业务方打开文件才发现下拉框没了、图表成了白板，事故已经出门。宁可多写十行 openpyxl，也别拿整文件重写去赌。

from openpyxl import load_workbookfrom datetime import datewb = load_workbook("台账.xlsx")     # 注意：不要加 read_only=Truews = wb.activerow = ws.max_row + 1                # 定位到第一条空行ws.cell(row=row, column=1, value=date.today())ws.cell(row=row, column=2, value="华东区")ws.cell(row=row, column=3, value=12800)wb.save("台账.xlsx")

关键点有三个：打开时必须保持可写模式；ws.max_row 在表尾有空行时会虚高，稳妥的定位方式是从最后一列往前找非空单元格；保存前如果原表有公式引用了数据区，新增行不会自动扩展公式范围——需要时顺手把公式也重写一遍。改表场景的本质是「尊重既有文件」，凡涉及别人维护的模板，一律走这条路。增量修改还有一个纪律值得立成规矩：改别人模板前先复制一份加时间戳的副本，脚本只对副本动手——这不是胆小，是给「模板被业务方同步改了结构」这类突发情况留退路，回滚成本为零。

4. 批量合并：几十个表汇成一张总表

第四个场景是自动化收益最高的：目录下几十个分公司、几十个月的同构表，要汇成一张总表。手工做法是逐个打开复制粘贴，一小时起步还容易漏；脚本做法是 glob 扫目录加循环读入，再 concat 拼接，几十秒收工。合并脚本还有一层手工做不到的价值：可重复。下个月新表放进目录，重跑一遍就出总表，流程不再依赖某个人的记忆与手感，交接成本趋近于零——写一次，每月白捡一小时。

import globimport pandas as pdframes = []for path in glob.glob("分部报表/*.xlsx"):    df = pd.read_excel(path, sheet_name="明细")    df["来源文件"] = path.split("\\")[-1]     # 留溯源列，出错能定位    frames.append(df)total = pd.concat(frames, ignore_index=True)total.to_excel("总表.xlsx", index=False)

如果每个文件里有多个工作表，把「该读哪个表」也配置成规则，而不是写死表名。目录若有年份、月份两级层级，glob 开递归后配合路径拆列，来源信息还能记到更细的粒度。合并之后通常跟着两步清洗：一是按关键列去重，各分部数据有交叠时必须做，drop_duplicates 的子集参数要跟业务对齐；二是类型统一，同一个「金额」列在不同分部文件里可能是数字也可能是文本，concat 之前逐表归一，否则总表里同列两种类型，透视直接出错。

5. 大文件与格式的解决方法

第五个场景躲不掉：几十万行的宽表，比较好的方案是使用流式——openpyxl 的 read_only=True 模式不再把整个工作簿装进内存，而是逐行吐出，配合生成器写成批量入 pandas 或直接入库，内存占用能降一个量级；反过来，纯写入几十万行时用 Workbook(write_only=True)，只进不出，速度比普通模式快得多。write_only 有个限制要记住：只支持顺序追加，不能回头改已写出的行，动手前先把数据备齐。内存问题的规律一句话：读入方式决定内存下限，处理方式决定内存上限。流式把下限压下来，边读边处理把上限摁住，两头都管住，百万行的表也能在普通的办公机上跑完。

from openpyxl import load_workbookwb = load_workbook("大表.xlsx", read_only=True)ws = wb["明细"]for row in ws.iter_rows(values_only=True):   # 逐行产出元组，不建单元格对象    pass  # 逐行处理或攒批入库wb.close()

格式与数据类型的坑集中在三处：日期列读出来时而是字符串时而是 datetime，取决于源文件的单元格格式，读之前用 dtype 或读之后显式转换最稳；文本型数字（单元格左上角带绿三角的那种）会污染整列类型，清洗时先 pd.to_numeric(errors="coerce") 兜底；老式 .xls 文件 openpyxl 不支持，建议先转成 .xlsx 格式，别在旧格式上恋战。数据量再往上走，还有一条更彻底的路：让 Excel 退出存储层。流程改成「数据入库或存 parquet，分析在 pandas 完成，Excel 只承担对人的交付」——每张对外交付的表都由脚本从源数据现场生成，中间态不再以 Excel 形态落地。这一步迈过去，文件损坏、版本混乱、各自改本地副本三类事故一起消失。顺带回答一个高频问题：读不动的场景要不要上多进程？先试流式与分块，多数情况够用；真到瓶颈，卡点通常在磁盘 IO 而非计算，并行收益有限——把功夫花在少读数据上（usecols 裁列、分表、增量读），比加进程有效。

最后的建议，先分场景再选库

五个场景走完，选型问题的答案已经浮出来：先问自己要的是数据还是文件。要数据算数，pandas 起手，格式需求出现后再让 openpyxl 接力；要保住别人模板的格式与结构，openpyxl 原位改，pandas 不碰整文件；量大到内存告急，双方各自切流式模式；批量合并走 glob 加 concat，永远带溯源列。这套分工背下来，Excel 自动化的路就通了。