Python + xlwings + pandas 批量处理 Excel 多工作表自动求和并生成汇总表

脚本专家

在办公自动化中，经常遇到一个 Excel 工作簿包含几十个结构相似的工作表（如按月份、部门拆分），需要分别对每个工作表的金额列求和，并集中展示结果。手动逐表添加 SUM 公式不仅重复低效，还容易遗漏或出错。本文将基于 pandas 和 xlwings 实现一套完整的批量求和脚本，自动遍历所有工作表、清洗金额数据、将合计结果写回原表末尾，并生成一张汇总表用于横向对比。

2. pip install pandas xlwings

复制代码

安装前确认环境中有 Excel 应用（xlwings 依赖 Excel 进程），建议先复制一份测试文件再运行正式数据。

完整脚本如下，已处理空表、缺少目标列、金额带货币符号等常见边界情况：

2. import pandas as pd
3. import xlwings as xw
5. def clean_to_number(s: pd.Series) -> pd.Series:
6.     """
7.     将带货币符号、逗号、空格的文本金额转换为数值。
8.     例如：￥12,345.67 -> 12345.67
9.     """
10.     s = s.astype(str).str.strip()
11.     s = s.str.replace(",", "", regex=False)
12.     s = s.str.replace(r"[¥￥$ ]", "", regex=True)
13.     s = s.str.replace(r"[^0-9\.\-]", "", regex=True)
14.     return pd.to_numeric(s, errors="coerce")
16. def sum_all_sheets_in_workbook(
17.     input_xlsx: str,
18.     sum_col: str = "销售利润",
19.     summary_sheet_name: str = "汇总",
20.     start_cell: str = "A1",
21.     save_as: str | None = None,
22. ) -> None:
23.     """
24.     对工作簿中所有工作表按指定列求和：
25.     1. 在每个工作表末尾追加“合计”行
26.     2. 生成或覆盖“汇总”工作表，列出每个工作表的合计和总计
27.     save_as=None 则覆盖保存原文件，save_as=路径则另存为新文件
28.     """
29.     app = xw.App(visible=False, add_book=False)
30.     app.display_alerts = False
31.     app.screen_updating = False
32.     try:
33.         wb = app.books.open(input_xlsx)
34.         results = []
35.         for sht in wb.sheets:
36.             if sht.name == summary_sheet_name:
37.                 continue  # 跳过汇总表，避免重复计算
38.             rng = sht.range(start_cell).expand("table")
39.             if rng.value is None:
40.                 print(f"[SKIP] {sht.name}: 空表")
41.                 continue
42.             df = rng.options(pd.DataFrame).value
43.             if df is None or df.empty:
44.                 print(f"[SKIP] {sht.name}: 无有效数据")
45.                 continue
46.             if sum_col not in df.columns:
47.                 print(f"[SKIP] {sht.name}: 缺少列 '{sum_col}'")
48.                 continue
49.             # 清洗金额列并求和
50.             num = clean_to_number(df[sum_col]).fillna(0)
51.             total = float(num.sum())
52.             # 写回当前表末尾
53.             last_cell = rng.last_cell
54.             total_row = last_cell.row + 1
55.             col_idx = df.columns.get_loc(sum_col) + 1  # xlwings 列号从1开始
56.             sht.range((total_row, 1)).value = "合计"
57.             sht.range((total_row, col_idx)).value = total
58.             try:
59.                 sht.range((total_row, 1), (total_row, col_idx)).api.Font.Bold = True
60.             except Exception:
61.                 pass
62.             results.append({"工作表": sht.name, f"{sum_col}合计": total})
63.             print(f"[OK] {sht.name}: {sum_col} 合计 = {total}")
64.         # 生成汇总工作表
65.         summary_df = pd.DataFrame(results)
66.         if summary_df.empty:
67.             raise RuntimeError("未得到任何可汇总结果，请检查列名、数据区域或是否为空表。")
68.         total_col = f"{sum_col}合计"
69.         grand_total = float(summary_df[total_col].sum())
70.         summary_df = summary_df.sort_values(by=total_col, ascending=False)
71.         try:
72.             sum_sht = wb.sheets[summary_sheet_name]
73.             sum_sht.clear()
74.         except Exception:
75.             sum_sht = wb.sheets.add(summary_sheet_name, before=wb.sheets[0])
76.         sum_sht.range("A1").options(index=False).value = summary_df
77.         last = sum_sht.range("A1").expand("table").last_cell
78.         total_row_s = last.row + 1
79.         sum_sht.range((total_row_s, 1)).value = "总计"
80.         sum_sht.range((total_row_s, 2)).value = grand_total
81.         try:
82.             sum_sht.range((1, 1), (total_row_s, 2)).api.Columns.AutoFit()
83.             sum_sht.range((total_row_s, 1), (total_row_s, 2)).api.Font.Bold = True
84.         except Exception:
85.             pass
86.         if save_as:
87.             wb.save(save_as)
88.             print(f"[DONE] 已另存为：{save_as}")
89.         else:
90.             wb.save()
91.             print(f"[DONE] 已覆盖保存：{input_xlsx}")
92.         wb.close()
93.     finally:
94.         app.quit()
96. if __name__ == "__main__":
97.     sum_all_sheets_in_workbook(
98.         input_xlsx="产品销售统计表.xlsx",
99.         sum_col="销售利润",
100.         summary_sheet_name="汇总",
101.         start_cell="A1",
102.         save_as="产品销售统计表_已求和.xlsx",
103.     )

复制代码

关键点解析：

1. 金额清洗：Excel 中金额常混有货币符号、逗号、空格等，必须先用正则去除，再通过 pd.to_numeric 转数值。clean_to_number 函数是求和正确的前提。
2. 跳过汇总表：脚本运行时生成的“汇总”表不能再次被遍历求和，否则结果会线性膨胀。if sht.name == summary_sheet_name: continue 是安全判断。
3. 容错处理：空表、缺少目标列的 Sheet 会被跳过并打印日志，不会中断整个工作簿。
4. 写回方式：在原表最后一行下方追加“合计”行，不覆盖原始数据。
5. 另存为建议：首次使用时指定 save_as 为另一路径，避免原始文件被误改。

效果验证：
- 打开每个业务 Sheet，确认表尾出现“合计”行且金额列有值。
- 打开“汇总”Sheet，检查是否包含所有工作表及总计。
- 随机选择1-2个 Sheet 用 Excel 的 SUM 公式手工核对。
- 控制台输出的日志可作为运行记录。

常见踩坑：
- expand("table") 依赖连续区域，表格中间有空行会导致读取不全，建议统一模板或改用固定范围（如 A1:H2000）。
- 不要默认每张表都有目标列，缺少时跳过而非报错。
- 不要直接覆盖原文件，除非已充分测试。

总结：
本文提供的不仅是求和函数，而是一个批量计算框架：遍历、清洗、计算、写回、汇总。替换 sum() 为 max/min/mean 就能扩展功能。办公自动化的价值在于让重复操作稳定、可验证。务必先备份再执行脚本。

热心网友3

感谢楼主分享的脚本，非常实用！xlwings 结合 pandas 确实是处理复杂 Excel 工作簿的好方案。代码里清理货币符号、跳过空表和汇总表、异常处理这些细节都考虑得很周全，对实际办公场景很有参考价值。我平时也遇到类似需求，不过有时金额列不止一个，或者不同表的列名略有差异——如果后续版本能支持对多个指定列同时求和，或者自动识别数值列，应该会更通用。另外，数据量特别大（比如几百个工作表）时，关掉屏幕更新虽然能提速，但 xlwings 底层还是逐表操作，如果有机会换成 openpyxl 或直接 pandas 写入，性能可能更好，不过那样就不能利用 Excel 公式和样式了。总之，楼主这份脚本值得收藏，稍加调整就能适配很多日常核对、汇总工作。谢谢分享！

热心网友3

感谢楼主分享这么实用的批量处理脚本！确实，经常遇到几十个结构相似的工作表需要逐表求和，手动操作既费时又容易出错。您这套基于 pandas + xlwings 的方案不仅实现了自动遍历、清洗货币格式，还考虑到了空表、缺少列、覆盖保存等常见边界情况，代码也很清晰，赞一个。 有个小建议：如果环境没有安装 Excel 或只能运行于服务器端，是否可以考虑增加对 openpyxl 等纯 Python 库的后备支持？另外，对于超大数据量的工作簿，xlwings 启动 Excel 进程可能会稍慢，或许可以加一个可选参数让用户指定只处理某些工作表范围。 总之，这个脚本已经能解决大多数办公场景下的批量汇总需求，收藏备用。再次感谢分享！

热心网友3

这个脚本思路很清晰，把数据清洗、逐表求和、写回结果和生成汇总表整合到一起，确实能大幅提高处理结构化Excel工作簿的效率。特别点赞对金额列做货币符号和逗号清洗的处理，这是实际工作中最容易踩坑的地方。另外跳过汇总表避免重复计算、以及用`expand("table")`自适应区域都很实用。 有一点想请教：如果工作表中除了金额列还有其他数值列，比如数量、成本，是否需要手动修改`sum_col`参数多次运行？还是说脚本有考虑扩展成多列同时求和？或者有没有计划支持用户指定一个列名列表？这样一次跑完所有数值列的汇总会更方便。