Python 处理 Excel 办公自动化：pandas 数据加工 + openpyxl 排版实战

Python 处理 Excel 做办公自动化，真正能拉开效率差距的，不是谁会写更复杂的循环，而是能不能把“数据加工”和“表格排版”分开处理。很多人拿一批表格过来就直接用 pandas 读，读出来之后统计、筛选、分组都做完了，结果导出的 Excel 一打开，列宽是默认的，日期变成一串数字，有空值的单元格还残留着“NaN”文本，最后还是花十几分钟手动改格式。这篇内容围绕办公自动化里的 Excel 高级操作展开，适合已经能用 Python 写基础脚本、但还没把 Excel 场景跑顺的人。下面我会按实际落地的顺序拆：先判断需求属于哪一类，再准备环境和依赖，接着处理高频的数据清洗场景，然后处理格式、公式、批量文件，最后说大文件性能和报错排查。整个过程里会给出可复现的代码片段和判断标准，但不会把“能跑通”说成“一定适合生产”，有些边界要等你用真实数据验证过才能确认。

1. 先把需求分成两类：数据加工和表格排版

办公自动化里的 Excel 任务，表面看都是“用 Python 操作表格”，实际差别很大。如果不先分清需求类型，很容易选错库、写错流程，甚至花了半天做出来的东西跟手动做没区别。

1.1 数据加工类需求，核心是减少重复劳动

这类需求的特征是：输入是一张或很多张表，你要做的是清洗、筛选、拆分、合并、统计，最后得到一个结果表。比如从销售明细中统计每个部门每个月的总金额，或者把一列“姓名电话”拆成两列，再或者按多个条件筛出目标数据。

这类任务最适合 pandas。pandas 处理的是“表”这个概念，而不是某个 Excel 文件里的某个单元格。读进来之后，你可以按列名操作，可以按布尔条件筛选，可以分组聚合，可以合并多个表。判断这类需求是否做好的标准也很简单：输出结果的行数、列数、汇总值是否和手工核对一致，而不是看代码写得多漂亮。

1.2 表格排版类需求，核心是输出后能不能直接使用

还有一类需求，输入数据基本已经处理好了，但最终交付的 Excel 需要带标题、表头颜色、边框、合并单元格、固定列宽、冻结首行。如果数据本身还要从多张表里汇总，那其实是一个“先数据加工、后排版”的组合需求。

排版这类任务用 openpyxl 更合适。你可以精确控制某个单元格的字体、背景色、边框、对齐方式，也能控制整列的宽度、某个区域是否合并、筛选按钮是否开启。判断标准是：打开生成文件时不需要人工补格式，直接能转发给别人。

两类需求用一张表区分会更直观：

这里最常犯的错误是一上来就写 openpyxl 逐行逐列循环。数据有几千行时，这种写法又慢又难维护。正确顺序是先让 pandas 把数据处理干净，再一次性把结果交给 openpyxl 做样式调整。顺序反了，后面改需求会很痛苦。

2. 环境和依赖准备：先解决“读不到、导不进去”的问题

Excel 办公自动化最容易出问题的不是功能代码，而是环境。好几年前我带过一个小项目，脚本在别人电脑上跑得好好的，换一台机器就报错，原因不是代码改了，而是别人电脑上没有装依赖库。

2.1 Python 环境和虚拟环境要提前固定

我建议新建项目时先创建虚拟环境，别一股脑把库装到全局 Python 里。全局环境里的包版本一乱，今天这个脚本能用，明天安装另一个库之后可能就冲突了。常规操作是这样：

python -m venv venv

Windows 下激活虚拟环境：

venv\Scripts\activate

macOS 或 Linux 下激活：

source venv/bin/activate

激活后安装依赖：

pip install pandas openpyxl

安装完成后，我建议先确认版本能正常导入，不要直接跑完整脚本：

import pandas as pd import openpyxl print(pd.__version__) print(openpyxl.__version__)

这一步看着多余，但能避免“昨天还能用，今天突然报 module not found”这类问题。原始材料没有给出固定的版本号，所以这里不写死某个版本要求。你只要保证 pandas 和 openpyxl 都能 import 成功，就说明基础环境没问题。

2.2 pandas 和 openpyxl 的分工不同

很多新手会问，到底是学 pandas 还是 openpyxl。我的回答是：两个都要装，但脑子里要把分工理清。

pandas 底层依赖 openpyxl 或 xlrd 来读写 Excel 文件。你在 pandas 里调用pd.read_excel()时，实际上它要借助 openpyxl 去解析 .xlsx 文件。所以安装 pandas 后，单独安装 openpyxl 是合理的，两者不是替代关系，而是配合关系。

文件格式也要提前确认：

• .xlsx 是现代 Excel 默认格式，pandas + openpyxl 处理最稳。
• .xls 是老版本格式，openpyxl 不支持读取 .xls，需要额外用 xlrd 或先把文件另存为 .xlsx。
• .xlsm 带宏，读取数据通常可以，但写回宏会有很多限制，不建议直接当普通表格改。

读文件时还要注意路径。如果文件路径里包含中文或空格，用 pandas 通常没问题，但建议用 raw string 或 pathlib 来避免反斜杠转义问题。路径写错了很容易出现FileNotFoundError，而这类报错不是 Excel 库的问题，是路径本身没写对。

我一般会先这样确认文件是否存在：

from pathlib import Path file_path = Path("data/销售明细.xlsx") print(file_path.exists()) print(file_path.resolve())

能打印出True和完整绝对路径，再往下读文件。

3. 第一个稳定流程：读取、检查、清洗

办公自动化脚本想长期复用，第一步不是写出花哨的统计代码，而是把读取和检查做扎实。拿到一张 Excel，我总是先看几行，再看字段类型，最后才决定怎么处理。

3.1 先读入并观察数据，不直接清洗

用 pandas 读取 Excel 文件的基本写法：

import pandas as pd df = pd.read_excel("data/销售明细.xlsx", sheet_name="明细") print(df.head()) print(df.info())

sheet_name可以传 sheet 名称，也可以传索引。如果不确定文件里有几张 sheet，可以先用：

sheets = pd.read_excel("data/销售明细.xlsx", sheet_name=None) print(sheets.keys())

sheet_name=None会把所有 sheet 读成一个字典，key 是 sheet 名。这样能快速知道整个工作簿的结构。如果只想读某个 sheet，再单独用sheet_name指定。

df.info()会输出每列的名称、非空数量、数据类型。这一步非常关键，因为 Excel 里“看起来是数字”的列，读进来之后很可能是object类型，这是最常见的坑之一。

3.2 空值、重复值和类型转换要分开处理

空值处理前，先要搞清楚空值在哪里。比如合并单元格会导致某些行在 pandas 里显示为NaN，因为原始 Excel 中只有左上角单元格有值，其他合并区域是空的。这时直接dropna()会删掉大量有效数据。正确做法是先判断这个空值是不是由合并单元格造成的，如果是，可以先用前向填充把值补上。

类型转换也要按列来，不能一概而论。比如金额列可能带有千分位逗号，读进来后实际上是文本：

df["金额"] = df["金额"].astype(str).str.replace(",", "", regex=False) df["金额"] = pd.to_numeric(df["金额"], errors="coerce")

先转成字符串，再移除逗号，最后转数字。errors="coerce"的意思是遇到无法转换的内容时变成NaN，而不是直接抛错。这样你能通过统计NaN的数量，定位到原始数据里哪些行格式不正常。

我自己处理时，不会直接修改原文件，而是先复制一份再加清洗结果：

clean_df = df.copy() clean_df["日期"] = pd.to_datetime(clean_df["日期"], errors="coerce") clean_df = clean_df.dropna(subset=["姓名", "金额"])

dropna(subset=...)只检查指定列，不会因为某些无关列缺失就把整行删掉。判断清洗是否成功的标准是：清洗前后总行数、各列非空数量、金额总和是否有明显变化。如果金额总和突然少了一大截，多数是空值或类型转换把某些行弄丢了。

4. 高频办公场景：筛选、拆分、分组汇总

数据清洗干净之后，就可以进入真正的高频场景了。下面这几个需求几乎每天都会在表格工作里出现，用 Python 处理它们的核心不是代码难，而是清楚每个操作背后的判断标准。

4.1 多条件筛选：先写条件，再写数据

“找出部门是销售部且金额大于 1000 的记录”，这种需求写作上很简单，但新手常见的报错是把条件表达式写错。

推荐先构造布尔条件，再用loc筛选：

mask = (clean_df["部门"] == "销售部") & (clean_df["金额"] > 1000) result = clean_df.loc[mask]

这里要注意括号。在 pandas 表达式里，&是逐位与操作，运算优先级容易踩坑。如果漏掉括号，可能会得到错误结果，甚至报 ValueError。每次写这类条件时，先把 mask 单独打印出来看 True/False 的数量，再筛选数据。

如果条件里还包含“或”的关系，记得用|，同时也要加括号：

mask = ( (clean_df["部门"] == "销售部") & (clean_df["金额"] > 1000) ) | (clean_df["客户等级"] == "A")

过滤结果以后，别忘了统计一下行数，再预览前几行。行数是否符合业务预期，比代码逻辑“看起来对”更值得确认。

4.2 姓名和电话拆分：先看分隔符，再写正则

“姓名和电话分开”也是常见需求。很多人会直接写复杂正则，结果遇到几十个异常格式就懵了。我建议先看原始数据的实际格式，再决定拆分方式。

如果原始数据像“张三 13800138000”，一个空格分隔，可以先 split：

df[["姓名", "电话"]] = df["联系方式"].str.split(expand=True, n=1)

但如果分隔符可能是空格、全角逗号、半角逗号、竖线，那简单 split 就不可靠。这时可以用正则提取姓名和手机号：

pattern = r"^(?P<姓名>.*?)[\s,，|]+(?P<电话>1[3-9]\d{9})$" split_result = df["联系方式"].str.extract(pattern) df2 = pd.concat([df, split_result], axis=1)

这个正则的含义是：

• .*?非贪婪匹配姓名部分
• [\s,，|]+匹配一个或多个分隔符
• 1[3-9]\d{9}匹配中国内地手机号的常见格式

正则不是万能钥匙。如果原始数据里根本没有规律的起止位置，比如写成“张先生电话13800138000”，正则就很难一次提取完整。这时候更稳妥的做法是先把“电话”字段提取出来，再反推剩余部分作为姓名。判断标准是：提取后姓名列和电话列不能有错位，空值数量要能说清楚原因。

4.3 分组汇总和跨表合并

做月度汇总、部门汇总这类需求时，groupby很常用：

summary = ( clean_df.groupby(["部门", "月份"], as_index=False)["金额"] .agg(["sum", "count"]) .reset_index() )

as_index=False可以避免分组列变成索引，后续操作更直观。.agg(["sum", "count"])会同时得到汇总金额和记录数。汇总之后，列名通常会变得有点乱，比如金额下面出现两层列名，这时可以手动重命名再导出。

跨表合并也很常见，比如把“订单表”和“客户表”按客户编号关联：

merged = pd.merge(order_df, customer_df, on="客户编号", how="left")

how="left"的意思是以左边表为基础，能匹配到的客户信息拼进来，匹配不到的显示为NaN。合并后你应该检查一下行数是否等于左边表的行数，因为如果客户表里有重复客户编号，会导致行数膨胀。

5. 做一份能直接交付的 Excel：格式、合并单元格、冻结窗格

数据处理结束后，如果交付对象不是程序员，那你就不能只给一张“能看的 CSV”，而是要给他们一份打开后不尴尬的 Excel。这里我一般会采用“先让 pandas 写值，再用 openpyxl 调样式”的组合流程。

5.1 写入数据，再加载工作簿调样式

先正常导出数据：

summary.to_excel("output/销售汇总.xlsx", index=False, sheet_name="汇总")

然后加载这个文件调整样式：

from openpyxl import load_workbook from openpyxl.styles import Font, PatternFill, Alignment, Border, Side wb = load_workbook("output/销售汇总.xlsx") ws = wb.active

为什么不直接在 pandas 里用 ExcelWriter 写样式？因为 pandas 的Styler方案在导出 Excel 时能做的事情有限，而且不同版本表现有差异。用 openpyxl 加载再修改，代码更直白，也能看到每一步操作的对象到底是哪个单元格。

给标题设置字体和背景色：

title_font = Font(name="微软雅黑", size=14, bold=True) header_fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid") header_font = Font(name="微软雅黑", size=11, bold=True, color="FFFFFF") for cell in ws[1]: cell.font = header_font cell.fill = header_fill cell.alignment = Alignment(horizontal="center", vertical="center")

如果要在第一行上方再加一个合并标题，可以先插入一行再合并：

ws.insert_rows(1) ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=ws.max_column) ws.cell(row=1, column=1, value="销售汇总报表") ws.cell(row=1, column=1).font = title_font ws.cell(row=1, column=1).alignment = Alignment(horizontal="center", vertical="center")

合并单元格时一定要想清楚合并范围。合并后，只有合并区域左上角的单元格能正常写值，其他区域的内容会被清空。所以先确定好标题要跨多少列，再传end_column。

5.2 列宽、边框、冻结首行和筛选按钮

列宽如果不设置，Excel 打开后会按默认宽度显示，中文字符很容易被截断。可以这样设置：

width_mapping = { "A": 20, "B": 15, "C": 12, "D": 18, } for col, width in width_mapping.items(): ws.column_dimensions[col].width = width

如果字段很多，更通用的方式是按表头内容大致估算宽度，或直接设置一个统一宽度。这一步别追求完美，重点是让中文列名和主要数据不会被隐藏。

冻结首行：

ws.freeze_panes = "A2"

freeze_panes是“冻结窗格”的位置。A2表示第一行固定不动，向下滚动时表头始终可见。如果前面插入了标题行，那表头行号会变化，写完代码后记得打开文件确认一下。

给数据区域加边框：

thin_border = Border( left=Side(style="thin"), right=Side(style="thin"), top=Side(style="thin"), bottom=Side(style="thin"), ) for row in ws.iter_rows(min_row=2, max_row=ws.max_row, max_col=ws.max_column): for cell in row: cell.border = thin_border

如果数据量很大，给每一行加边框会拖慢脚本速度。我的经验是：对外交付的小报表可以逐格加边框，几百行以内问题不大；几千行以上的大表通常不需要加全边框，加个自动筛选和冻结首行就够了。

最后是自动筛选按钮，可以用ws.auto_filter.ref指定区域：

ws.auto_filter.ref = ws.dimensions

ws.dimensions是当前有数据的区域范围，它能自动包含所有列。

6. 公式、跨文件处理和批量任务设计

一个办公自动化流程能不能真正替代手工，要看它能不能处理批量文件，而不只是处理一个文件。

6.1 openpyxl 写公式的边界

如果你需要在某个单元格写 Excel 公式，openpyxl 可以直接赋值：

ws["F21"] = "=SUM(F2:F20)"

但是这里有一个必须知道的边界：openpyxl 只负责把公式写进文件，并不会帮你计算公式结果。也就是说，你用 pandas 读这个文件时，可能读不到 F21 的计算值，只会读到公式字符串或None。等用户用 Excel 打开文件时，Excel 通常会重新计算公式并显示结果。

如果下游脚本要用 pandas 再读这个 Excel，我不建议把计算逻辑交给 Excel 公式。更好的做法是用 pandas 先把汇总值算出来，直接写数值。需要公式只是为了“在 Excel 里能联动”，那再考虑写公式。判断标准是：下游还要不要读取这个文件。如果需要读，用数值更稳；如果只是给人看的，用公式可以。

6.2 批量处理文件夹下的多个 Excel

批量处理时，第一步不是写处理逻辑，而是确认“要处理哪些文件”。用 pathlib 遍历目录比较方便：

from pathlib import Path input_dir = Path("data") output_dir = Path("output") output_dir.mkdir(exist_ok=True) for file_path in input_dir.glob("*.xlsx"): print("开始处理:", file_path.name) try: df = pd.read_excel(file_path, sheet_name=0) # 这里放你的清洗和统计逻辑 result = df.groupby("部门", as_index=False)["金额"].sum() out_path = output_dir / f"{file_path.stem}_汇总.xlsx" result.to_excel(out_path, index=False) except Exception as e: print(f"处理失败: {file_path.name}, 错误: {e}")

这个流程里有三个容易被忽略的点。

第一，输出文件名一定要基于输入文件名生成，不能所有文件都写成汇总.xlsx，否则后面的文件会覆盖前面的。

第二，异常捕获不能只打印，还要带上文件名。如果你把异常捕获写在循环外面，一个文件出错就会中断整批任务。建议在每个文件级别捕获异常，这样单个文件失败不影响其他文件。

第三，处理完后要检查输出文件数量是否等于输入文件数量。数量对不上时，根据日志定位是哪些文件失败了，不要直接重跑全部任务。

批量任务的“成功”不是代码不报错，而是输出文件数量、文件名、行数、汇总值都符合预期。我在批量运行前会先处理一个文件，看输出结果对不对，确认无误后再放开整个文件夹。

6.3 跨文件数据合并

如果需求是把多个结构相同的 Excel 合成一张总表，可以先循环读取，再用pd.concat合并：

all_data = [] for file_path in input_dir.glob("*.xlsx"): df = pd.read_excel(file_path, sheet_name=0) all_data.append(df) combined = pd.concat(all_data, ignore_index=True) combined.to_excel(output_dir / "合并结果.xlsx", index=False)

pd.concat默认按列名对齐。如果每个文件的列名不完全一致，合并后会出现很多NaN列。这时先检查每个文件的列名是否一致，是更重要的前提。

7. 数据量变大时，不要直接撑爆内存

办公自动化场景里有个常见错觉：小文件能用，大文件也能用。实际不是这样。用 pandas 读取 Excel 时，文件本身可能只有 50MB，但读进内存后会膨胀好几倍。如果你在脚本里反复复制 DataFrame，内存占用还会更高。

7.1 先用小数据试跑，再决定是否全量处理

面对大文件，我一般先读前几十行确认结构：

df_sample = pd.read_excel("big_file.xlsx", sheet_name="明细", nrows=50) print(df_sample.head()) print(df_sample.columns)

nrows参数只读前面若干行，读取速度很快。确认列名、类型和样例数据没问题后，再全量读取。如果你的机器配置不高，全量读取时关注三个指标：内存占用、读取耗时、处理耗时。

打开任务管理器观察 Python 进程的内存，如果内存涨到接近物理内存上限，就要想办法减少数据体积或分批处理。

7.2 openpyxl 的 read_only 和 write_only 模式

如果你不需要用 pandas 做复杂统计，只是要把某个 Excel 里的内容遍历一遍，可以用 openpyxl 的只读模式：

from openpyxl import load_workbook wb = load_workbook("large_file.xlsx", read_only=True) ws = wb["明细"] for row in ws.iter_rows(values_only=True): # 每一行都是元组 pass wb.close()

read_only=True不会一次性把所有数据加载到内存，而是按行流式读取，内存占用明显更低。写文件时也可以用只写模式：

from openpyxl import Workbook wb = Workbook(write_only=True) ws = wb.create_sheet("结果") ws.append(["姓名", "金额"]) for row in some_iterable: ws.append(row) wb.save("large_output.xlsx")

write_only=True模式不支持反向修改已经写入的内容，适合一次性顺序写入大量行。判断是否使用这种模式，要看任务是不是“批量写入 + 不需要频繁定位单元格”。如果你的脚本需要反复改某个固定单元格，还是用普通模式方便。

7.3 超过 Excel 行数上限时要考虑其他方案

.xlsx 格式的行数上限大约是 104 万行左右，这个限制不是 Python 造成的，而是 Excel 文件格式本身的上限。如果你的数据量接近这个范围，不建议硬塞进 Excel。更合适的做法是把处理结果输出成 CSV，或者导入到数据库里再做查询分析。

低配置机器能跑通小文件，不代表适合批量跑大文件。如果你要处理的目标是几百 MB 的 Excel，先考虑把源数据按月份或按部门拆成多个文件，分批处理后再合并，会比一次性读完更稳。

8. 报错定位链路：按这个顺序排查，不要乱改参数

最后一部分专门说问题排查。办公自动化脚本出问题时，很多人的第一反应是去改代码参数，但实际有一半问题出在文件、路径、权限或数据格式上。我建议按“先看现象，再看输入，再看环境，再看代码”的顺序排查。

8.1 常见报错现象和排查方向

8.2 通用的排查顺序

第一步，看报错在哪个阶段。它是发生在读取文件时、清洗数据时、写入文件时，还是生成样式时。因为 Excel 文件的错误往往会在最后写入时才暴露，比如某些单元格里有非法字符。

第二步，看输入文件本身。用 Excel 打开文件检查表头、sheet 名称、合并单元格、单元格格式，不要只盯着代码看。很多“Python 读不到”的问题，其实是源文件第一行并不是表头，或者文件里存在多个 sheet，你默认读的 sheet 不是你以为的那个。

第三步，看环境和依赖。先确认 pandas 和 openpyxl 能正常 import，再确认文件路径没有因为目录结构变化而失效。如果代码之前能跑，现在不能跑，先想想是不是有人移动了文件或升级了依赖版本。

第四步，看你的数据操作逻辑。比如筛选条件里的括号是不是写错了，groupby之后是不是忘了重置索引，合并单元格时是不是写错了行列范围。这些逻辑错误不报错，但结果就是不对。

第五步，看库本身的功能边界。openpyxl 不计算公式、宏链路会破坏、.xls 旧格式读不了、合并单元格会挡住部分操作，这些都属于工具限制，不是你的代码 bug。遇到这些情况，要么换方案，要么换工具，不要硬调参数。

8.3 几个我实战中经常踩的细节

处理 Excel 时，我最常犯的一个错误是忘记处理“文件已经打开”的状态。脚本写入时报PermissionError，十有八九是你自己在 Excel 里开着同一个文件。排查时先关掉 Excel 再跑一次。

第二个容易被忽略的问题是 sheet 名称里可能有空格。比如 sheet 名字叫“销售明细 ”，末尾带一个看不见的空格，用sheet_name="销售明细"读取就会报错。可以用pd.ExcelFile先打印所有 sheet 名称，确认有没有看不见的字符。

第三个问题是 pandas 的空值处理。df.fillna("")是常用的补空操作，但如果某列本身是数字类型，强行填空字符串会把整列变成文本，后续统计就会出错。我的建议是：先想清楚每个空值在业务上应该怎么处理，是删除整行、填 0、填“未知”，还是保留 NaN，然后再动手。

第四个问题是格式和数据的顺序。不要先合并单元格再写数据，很容易覆盖内容。应该先写完所有数据，再统一调格式。格式操作和数据处理分开，代码维护起来会轻松很多。

这个方案真正落地时，最该盯住的不是功能列表，而是输入格式、资源占用和失败重试。先让单文件流程跑通，再做文件夹批量；先看日志，再改参数；先确认数据没有异常，再放心交付给业务方。办公自动化不是把代码写出来就结束，而是把重复劳动稳定地降低到一个可以接受的程度。