Python批量处理Excel工作簿与工作表：os+xlwings+pandas实战

脚本专家

在日常办公中，Excel 的重复操作（如合并几十个部门报表、统一重命名工作表、批量提取数据）是典型的“低效劳动”。手动处理不仅慢，而且容易出错。本文基于 Python 的 os、xlwings 和 pandas 三库分工协作，提供一套可复用的批量处理模板，并重点讲解路径管理、临时文件过滤、资源释放和结果验证等踩坑经验。

一、三大库的分工：各自负责什么

os：负责文件系统层面的批处理，如遍历目录、拼接路径、判断扩展名、创建输出文件夹。

2. import os
3. folder = r"C:\Temp\excel_batch"
4. for file_name in os.listdir(folder):
5.     if file_name.endswith(".xlsx"):
6.         full_path = os.path.join(folder, file_name)
7.         print(full_path)

复制代码

xlwings：模拟人工操作 Excel 应用程序，适合处理需要公式刷新、宏、打印、打开工作簿等场景。需注意本机必须安装 Excel。

2. import xlwings as xw
3. app = xw.App(visible=False)
4. wb = app.books.open(r"C:\Temp\excel_batch\demo.xlsx")
5. sheet = wb.sheets[0]
6. sheet.range("A1").value = "Python 批量处理测试"
7. wb.save()
8. wb.close()
9. app.quit()

复制代码

pandas：直接处理表格数据，读取、筛选、分组、合并、导出等。不依赖 Excel 程序，速度和稳定性更优。

2. import pandas as pd
3. df = pd.read_excel(r"C:\Temp\excel_batch\sales.xlsx")
4. result = df.groupby("产品", as_index=False)["销售额"].sum()
5. result.to_excel(r"C:\Temp\excel_batch\销售额汇总.xlsx", index=False)

复制代码

二、批处理通用模板（pathlib + pandas）

无论任务是批量新建、合并还是重命名，外层流程固定：确定输入/输出目录 → 遍历文件 → 过滤目标 → 逐个处理 → 保存输出 → 关闭资源。下面是用 pathlib 和 pandas 实现的安全模板：

2. from pathlib import Path
3. import pandas as pd
5. input_dir = Path(r"C:\Temp\excel_batch\input")
6. output_dir = Path(r"C:\Temp\excel_batch\output")
7. output_dir.mkdir(exist_ok=True)
9. for file_path in input_dir.glob("*.xlsx"):
10.     if file_path.name.startswith("~$"):
11.         continue
12.     print(f"正在处理：{file_path.name}")
13.     df = pd.read_excel(file_path)
14.     # 示例处理逻辑：删除完全空白行
15.     df = df.dropna(how="all")
16.     output_path = output_dir / f"{file_path.stem}_处理后.xlsx"
17.     df.to_excel(output_path, index=False)
19. print("批量处理完成")

复制代码

关键点：
- 使用 pathlib 管理路径，避免反斜杠转义问题。
- 跳过以 ~$ 开头的临时文件（Excel 打开时生成）。
- 先输出到新目录，不要直接覆盖原文件。

三、运行前必须做的四件事（排雷指南）

1. 统一目录结构：建议分为 input、output、backup 三个文件夹。先复制 3-5 个样本测试，再对整个文件夹执行。
2. 先备份：涉及改名、删除、覆盖等破坏性操作时，一定先复制原始文件到 backup。避免不可逆损失。
3. 关闭占用：Excel 文件被打开时脚本可能写入失败。若使用 xlwings，务必在 finally 中关闭工作簿并退出应用，防止后台残留进程。
4. 验证结果，不能只看“运行完成”：写一段脚本检查输出文件数量和基本字段。

2. from pathlib import Path
3. output_dir = Path(r"C:\Temp\excel_batch\output")
4. files = list(output_dir.glob("*.xlsx"))
5. print(f"输出文件数量：{len(files)}")
6. for file in files[:5]:
7.     print(file.name)

复制代码

四、常见踩坑点与防御写法

1. 中文路径和反斜杠：使用原始字符串 r"..." 或 pathlib.Path，避免转义。

2. 临时文件~$：遍历时用 startswith("~$") 跳过。

3. 字段名不一致：不同部门提交的 Excel 可能有“资产编号”和“资产编码”等差异。在做汇总前先校验必需字段：

2. required_columns = {"资产编号", "资产名称", "使用部门"}
3. missing = required_columns - set(df.columns)
4. if missing:
5.     print(f"字段缺失：{missing}")

复制代码

4. xlwings 资源释放：用 try...finally 确保 wb.close() 和 app.quit() 执行。

2. import xlwings as xw
3. app = xw.App(visible=False)
4. try:
5.     wb = app.books.open(r"C:\Temp\excel_batch\demo.xlsx")
6.     # 处理逻辑
7.     wb.save()
8.     wb.close()
9. finally:
10.     app.quit()

复制代码

五、可复盘的脚本：加入日志与计数

一段真正可用的办公自动化脚本应记录处理了多少文件、跳过多少、失败多少及原因。下面模板适合后续扩展成工具：

2. from pathlib import Path
3. import pandas as pd
5. input_dir = Path(r"C:\Temp\excel_batch\input")
6. output_dir = Path(r"C:\Temp\excel_batch\output")
7. output_dir.mkdir(exist_ok=True)
9. success_count = 0
10. fail_count = 0
12. for file_path in input_dir.glob("*.xlsx"):
13.     if file_path.name.startswith("~$"):
14.         continue
15.     try:
16.         df = pd.read_excel(file_path)
17.         df = df.dropna(how="all")
18.         output_path = output_dir / f"{file_path.stem}_处理后.xlsx"
19.         df.to_excel(output_path, index=False)
20.         print(f"[成功] {file_path.name} -> {output_path.name}")
21.         success_count += 1
22.     except Exception as e:
23.         print(f"[失败] {file_path.name}，原因：{e}")
24.         fail_count += 1
26. print(f"处理完成：成功 {success_count} 个，失败 {fail_count} 个")

复制代码

六、总结：自动化思维比代码更重要

Python 处理 Excel 的真正价值不在于记住某个 API，而在于把重复动作抽象成稳定流程。先识别重复任务，拆分为“文件层 → Excel层 → 数据层”，再选用合适的库实现。每次写脚本前备份、先小样本测试、加入输入校验和结果验证，才能让自动化真正可靠。积累可复用的遍历、读取、输出、日志模板，后续遇到新需求只需拼装改造，无需从零开始。

热心网友7

感谢分享！这套模板把 os、xlwings、pandas 的分工讲得很清楚，特别是排雷指南里的“先备份、关占用、验结果”三步，新手最容易在这些地方翻车。用 pathlib 代替字符串拼接也是好习惯。 提两个小补充： - 如果用 pandas 读取大量文件，可以加个 dtype 参数避免自动推断类型导致内存占用过高。 - xlwings 那部分如果只是读写值，可以先用 screen_updating=False 和计算模式手动关闭，速度会快很多。 另外“五、可复盘的”后面是不是被截断了？期待你补完，比如复盘实际项目中的参数校验逻辑或异常处理案例，这种实战经验太宝贵了。

热心网友7

感谢分享，非常实战的内容！特别是排雷指南里提到的“跳过~$临时文件”和“用pathlib代替os.path处理路径”，这两个坑刚入门时真的容易踩。我之前都是手写os.listdir然后if判断，代码里全是反斜杠转义，后来换成pathlib干净多了。 想问下楼主的通用模板里，如果不同Excel文件里的字段名不统一（比如有的叫“产品名称”有的叫“产品名”），一般用什么策略来统一呢？是进pandas前预先重命名，还是先人工统一格式再做批处理？

热心网友7

非常实用的一篇总结！你提到的路径管理、临时文件过滤和资源释放确实是实际批量处理中最容易踩坑的地方，特别是 xlwings 的 app.quit() 很多人会忘掉。另外建议在遍历文件时可以加上 `sorted()` 固定处理顺序，这样结果更可预期。感谢分享这些经过实践检验的技巧。