Python批量处理多个Excel工作簿：工作表分类汇总与数值清洗实战

脚本专家

问题背景：手工汇总Excel报表的低效与风险

在日常办公中，经常需要处理一个文件夹里的多个Excel文件（工作簿），每个文件又包含多张工作表。领导要求按“销售区域”汇总“销售利润”。如果手工操作：打开工作簿→切换工作表→分类汇总→复制结果→保存，然后重复。工作簿数量一多，不仅慢，而且容易漏表、选错区域、金额列被当成文本、临时文件被误处理等。Python自动化可以把重复动作抽象成稳定流程，避免人为错误。

适用场景
这类脚本适合以下场景：一个文件夹内有多个.xlsx工作簿；每个工作簿有多张工作表；表头结构相同或相似；需要按某个字段分组（如销售区域、客户名称、部门、产品类型）；需要对数值字段求和（如销售利润、销售额、数量、成本）；希望汇总结果写回原工作表右侧，方便查看。建议先在测试文件夹复制2～3个样例文件试运行，确认无误后再处理正式数据，避免批量误操作损坏原始文件。

核心原理：批处理流水线
手工汇总步骤拆开后就是固定流水线：扫描文件夹→打开工作簿→遍历工作表→读取数据→分组汇总→写回保存。三个库各有分工：os负责文件系统处理（扫描、拼接路径、判断扩展名）；xlwings负责操作Excel（打开工作簿、访问工作表、写回结果）；pandas负责数据处理（将表格转为DataFrame，用groupby()分类汇总）。这条流程搭好后，只需替换中间的数据处理逻辑，即可实现批量筛选、排序、拆分等需求。

操作前准备
首先安装依赖：

2. pip install pandas xlwings

复制代码

注意：xlwings在Windows上依赖本机已安装的Microsoft Excel。

建议目录结构：原始报表和输出结果分开存放。例如：项目目录下建“销售表”文件夹放原始文件，另建“输出结果”文件夹保存处理后的文件。

字段要求：每张工作表至少包含两个字段：销售区域、销售利润。如果字段名不同，需修改代码中的参数。

完整代码：批量处理多个工作簿中的所有工作表
以下代码考虑了真实办公中的常见问题：跳过~$临时文件、跳过空表、校验字段、清洗金额、另存输出、退出Excel。

2. import os
3. import pandas as pd
4. import xlwings as xw
6. def clean_to_number(series: pd.Series) -> pd.Series:
7.     """
8.     将可能带有货币符号、逗号、空格、文本前缀的金额列清洗为数值。
9.     例如：￥12,345.67 -> 12345.67，profit: 6543.21 -> 6543.21
10.     """
11.     series = series.astype(str).str.strip()
12.     series = series.str.replace(",", "", regex=False)
13.     series = series.str.replace(r"[¥￥$ ]", "", regex=True)
14.     series = series.str.replace(r"[^0-9\.\-]", "", regex=True)
15.     return pd.to_numeric(series, errors="coerce")
17. def summarize_one_sheet(
18.     df: pd.DataFrame,
19.     group_col: str = "销售区域",
20.     value_col: str = "销售利润"
21. ) -> pd.DataFrame:
22.     """
23.     对单张工作表数据进行分类汇总。按group_col分组，对value_col求和。
24.     """
25.     if group_col not in df.columns:
26.         raise KeyError(f"缺少分组列：{group_col}")
27.     if value_col not in df.columns:
28.         raise KeyError(f"缺少汇总列：{value_col}")
29.     temp = df.copy()
30.     # 先清洗成数值，避免字符串求和或排序错误
31.     temp[value_col] = clean_to_number(temp[value_col]).fillna(0)
32.     result = (
33.         temp.groupby(group_col, dropna=False)[value_col]
34.         .sum()
35.         .reset_index()
36.         .rename(columns={
37.             group_col: "销售区域",
38.             value_col: "销售利润汇总"
39.         })
40.         .sort_values("销售利润汇总", ascending=False)
41.     )
42.     return result
44. def batch_summary_workbooks(
45.     input_folder: str,
46.     output_folder: str,
47.     group_col: str = "销售区域",
48.     value_col: str = "销售利润",
49.     start_cell: str = "A1",
50.     write_cell: str = "J1"
51. ) -> None:
52.     """
53.     批量处理多个工作簿中的所有工作表。
54.     """
55.     os.makedirs(output_folder, exist_ok=True)
56.     app = xw.App(visible=False, add_book=False)
57.     app.display_alerts = False
58.     app.screen_updating = False
59.     try:
60.         for file_name in os.listdir(input_folder):
61.             # 跳过Excel临时文件和非xlsx文件
62.             if file_name.startswith("~$"):
63.                 continue
64.             if not file_name.lower().endswith(".xlsx"):
65.                 continue
66.             input_path = os.path.join(input_folder, file_name)
67.             output_path = os.path.join(output_folder, file_name)
68.             print(f"\n[OPEN] 正在处理工作簿：{input_path}")
69.             wb = app.books.open(input_path)
70.             success_count = 0
71.             skip_count = 0
72.             try:
73.                 for sht in wb.sheets:
74.                     try:
75.                         rng = sht.range(start_cell).expand("table")
76.                         if rng.value is None:
77.                             print(f" [SKIP] {sht.name}：空表")
78.                             skip_count += 1
79.                             continue
80.                         df = rng.options(pd.DataFrame, header=1, index=False).value
81.                         if df is None or df.empty:
82.                             print(f" [SKIP] {sht.name}：无有效数据")
83.                             skip_count += 1
84.                             continue
85.                         summary_df = summarize_one_sheet(
86.                             df,
87.                             group_col=group_col,
88.                             value_col=value_col
89.                         )
90.                         # 清理旧汇总区，避免上一次结果残留
91.                         sht.range(write_cell).resize(100, 3).clear_contents()
92.                         # 写回汇总结果，不写入DataFrame索引
93.                         sht.range(write_cell).options(index=False).value = summary_df
94.                         sht.autofit()
95.                         print(f" [OK] {sht.name}：已汇总到 {write_cell}")
96.                         success_count += 1
97.                     except Exception as e:
98.                         print(f" [SKIP] {sht.name}：{e}")
99.                         skip_count += 1
100.                 wb.save(output_path)
101.                 print(f"[DONE] 已保存：{output_path}，成功 {success_count} 张表，跳过 {skip_count} 张表")
102.             finally:
103.                 wb.close()
104.     finally:
105.         app.quit()
106.     print("\n[ALL DONE] 所有工作簿处理完成")
108. if __name__ == "__main__":
109.     batch_summary_workbooks(
110.         input_folder=r"销售表",
111.         output_folder=r"输出结果",
112.         group_col="销售区域",
113.         value_col="销售利润",
114.         start_cell="A1",
115.         write_cell="J1"
116.     )

复制代码

关键判断：必须先清洗数值再分类汇总
很多新手直接写 df.groupby("销售区域")["销售利润"].sum()，但真实Excel报表中销售利润列可能带有货币符号（￥、$）、逗号、空格、文本前缀（如"profit: "）等。如果不清洗，pandas会识别为字符串，求和结果异常甚至报错。代码中的clean_to_number函数先用正则去除符号和文本，再转为数值，确保分组求和结果准确。

运行效果验证
脚本运行后不能只看控制台无报错，需要验证三层：
1. 输出文件夹中生成了对应的Excel文件，数量与输入文件一致。
2. 打开任意输出文件，切换到不同工作表，查看J1位置是否出现汇总结果（两列：“销售区域”和“销售利润汇总”）。
3. 随机选一张表，用Excel透视表或筛选核对一两个区域的销售利润，与脚本结果一致才算可信。

常见问题与踩坑记录
- 为什么脚本会跳过某些工作表？常见原因：空表、表头不在A1、缺少指定字段。代码已做异常捕获，不会中断批处理。如果表头从A2开始，请修改start_cell参数。
- 为什么要跳过~$开头的文件？这些是Excel打开的临时锁定文件，不是数据文件，参与处理会导致打开失败或权限错误。
- 为什么输出到新文件夹而不是覆盖原文件？分类汇总属于批量写操作，写错可能影响多个文件。输出到新文件夹后可以对比检查，确认无误再替换原始文件。
- 为什么Excel有时会残留进程？脚本中途异常退出而未执行app.quit()会导致Excel进程残留。代码使用try/finally确保无论是否报错都调用app.quit()，这是使用xlwings的基本规范。

总结：从脚本到办公自动化套路
本文的核心不是记住某行代码，而是理解可复用的套路：先定位文件，再定位工作簿，再定位工作表，最后将数据读入DataFrame处理。这套思路可以扩展到按客户名称汇总销售额、按部门统计费用、按产品类型汇总订单数量，甚至将每个工作簿的汇总结果合并成总表。批量处理脚本一定要先用样例数据验证，再处理正式数据，尤其是涉及金额汇总、财务报表时。

热心网友3

这个脚本写得很实用，特别是对“~$”临时文件和字段校验的处理很周全，能在正式跑数据前避免不少坑。数值清洗那部分对带货币符号和逗号的金额也很友好，实际工作中很多报表确实不干净。 有一点想请教：输出结果写回原工作簿的右侧（比如J列），如果原工作表的数据列数超过J列，或者后面有其他公式，会不会有覆盖的风险？是否考虑过增加一个“新工作表写入结果”的选项？另外，`start_cell`参数好像没有在函数里用到，是不是留着给其他扩展场景预留的？ 整体流程清晰，注释也到位，对新手很友好。建议加一个对文件编码或单元格格式的说明，比如金额列清洗后保持数字格式，免得导出后还是文本。感谢分享。

热心网友3

您这套脚本思路很清晰，把日常手工汇总的痛点（临时文件、文本金额、空表等）都考虑进去了，特别是 `clean_to_number` 函数对货币符号和逗号的处理很实用。我平时也常用类似方式批量处理报表，不过有个小建议：如果源文件的`销售区域`列有合并单元格或空值，groupby(dropna=False)已经处理了空值，但合并单元格读进来可能会有前一行重复填充的问题，可以在读表时先 `ffill()` 一下。另外，xlwings 在 Excel 未安装的环境下会报错，您一般在正式跑之前会怎么快速校验环境？

热心网友3

感谢分享！这个脚本设计得很实用，尤其是对办公中常见的脏数据（货币符号、逗号、空格、文本前缀）做了清洗，还考虑了临时文件`~$`和空表的情况，很贴心。我平时也常被Excel汇总折磨，手动操作容易漏数据，有了这个流水线就稳多了。有个小问题想请教：如果不同工作簿的表头字段名不完全一致，比如有的叫“销售区域”，有的叫“区域”，需要手动修改`group_col`参数吗？还是说脚本里可以加个字段映射逻辑？另外，输出结果写回原工作表右侧（比如J列），如果原表右侧已有其他数据，会不会被覆盖？希望楼主能再多讲一下这部分细节。