Python + xlwings + pandas 批量处理 Excel 多工作表自动求和并生成汇总表
在办公自动化中，经常遇到一个 Excel 工作簿包含几十个结构相似的工作表（如按月份、部门拆分），需要分别对每个工作表的金额列求和，并集中展示结果。手动逐表添加 SUM 公式不仅重复低效，还容易遗漏或出错。本文将基于 pandas 和 xlwings 实现一套完整的批量求和脚本，自动遍历所有工作表、清洗金额数据、将合计结果写回原表末尾，并生成一张汇总表用于横向对比。pip install pandas xlwings
安装前确认环境中有 Excel 应用（xlwings 依赖 Excel 进程），建议先复制一份测试文件再运行正式数据。
完整脚本如下，已处理空表、缺少目标列、金额带货币符号等常见边界情况：
import pandas as pd
import xlwings as xw
def clean_to_number(s: pd.Series) -> pd.Series:
"""
将带货币符号、逗号、空格的文本金额转换为数值。
例如：￥12,345.67 -> 12345.67
"""
s = s.astype(str).str.strip()
s = s.str.replace(",", "", regex=False)
s = s.str.replace(r"[¥￥$ ]", "", regex=True)
s = s.str.replace(r"[^0-9\.\-]", "", regex=True)
return pd.to_numeric(s, errors="coerce")
def sum_all_sheets_in_workbook(
input_xlsx: str,
sum_col: str = "销售利润",
summary_sheet_name: str = "汇总",
start_cell: str = "A1",
save_as: str | None = None,
) -> None:
"""
对工作簿中所有工作表按指定列求和：
1. 在每个工作表末尾追加“合计”行
2. 生成或覆盖“汇总”工作表，列出每个工作表的合计和总计
save_as=None 则覆盖保存原文件，save_as=路径则另存为新文件
"""
app = xw.App(visible=False, add_book=False)
app.display_alerts = False
app.screen_updating = False
try:
wb = app.books.open(input_xlsx)
results = []
for sht in wb.sheets:
if sht.name == summary_sheet_name:
continue# 跳过汇总表，避免重复计算
rng = sht.range(start_cell).expand("table")
if rng.value is None:
print(f" {sht.name}: 空表")
continue
df = rng.options(pd.DataFrame).value
if df is None or df.empty:
print(f" {sht.name}: 无有效数据")
continue
if sum_col not in df.columns:
print(f" {sht.name}: 缺少列 '{sum_col}'")
continue
# 清洗金额列并求和
num = clean_to_number(df).fillna(0)
total = float(num.sum())
# 写回当前表末尾
last_cell = rng.last_cell
total_row = last_cell.row + 1
col_idx = df.columns.get_loc(sum_col) + 1# xlwings 列号从1开始
sht.range((total_row, 1)).value = "合计"
sht.range((total_row, col_idx)).value = total
try:
sht.range((total_row, 1), (total_row, col_idx)).api.Font.Bold = True
except Exception:
pass
results.append({"工作表": sht.name, f"{sum_col}合计": total})
print(f" {sht.name}: {sum_col} 合计 = {total}")
# 生成汇总工作表
summary_df = pd.DataFrame(results)
if summary_df.empty:
raise RuntimeError("未得到任何可汇总结果，请检查列名、数据区域或是否为空表。")
total_col = f"{sum_col}合计"
grand_total = float(summary_df.sum())
summary_df = summary_df.sort_values(by=total_col, ascending=False)
try:
sum_sht = wb.sheets
sum_sht.clear()
except Exception:
sum_sht = wb.sheets.add(summary_sheet_name, before=wb.sheets)
sum_sht.range("A1").options(index=False).value = summary_df
last = sum_sht.range("A1").expand("table").last_cell
total_row_s = last.row + 1
sum_sht.range((total_row_s, 1)).value = "总计"
sum_sht.range((total_row_s, 2)).value = grand_total
try:
sum_sht.range((1, 1), (total_row_s, 2)).api.Columns.AutoFit()
sum_sht.range((total_row_s, 1), (total_row_s, 2)).api.Font.Bold = True
except Exception:
pass
if save_as:
wb.save(save_as)
print(f" 已另存为：{save_as}")
else:
wb.save()
print(f" 已覆盖保存：{input_xlsx}")
wb.close()
finally:
app.quit()
if __name__ == "__main__":
sum_all_sheets_in_workbook(
input_xlsx="产品销售统计表.xlsx",
sum_col="销售利润",
summary_sheet_name="汇总",
start_cell="A1",
save_as="产品销售统计表_已求和.xlsx",
)
关键点解析：
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 就能扩展功能。办公自动化的价值在于让重复操作稳定、可验证。务必先备份再执行脚本。
Re: Python + xlwings + pandas 批量处理 Excel 多工作表自动求和并生成汇总表
感谢楼主分享的脚本，非常实用！xlwings 结合 pandas 确实是处理复杂 Excel 工作簿的好方案。代码里清理货币符号、跳过空表和汇总表、异常处理这些细节都考虑得很周全，对实际办公场景很有参考价值。我平时也遇到类似需求，不过有时金额列不止一个，或者不同表的列名略有差异——如果后续版本能支持对多个指定列同时求和，或者自动识别数值列，应该会更通用。另外，数据量特别大（比如几百个工作表）时，关掉屏幕更新虽然能提速，但 xlwings 底层还是逐表操作，如果有机会换成 openpyxl 或直接 pandas 写入，性能可能更好，不过那样就不能利用 Excel 公式和样式了。总之，楼主这份脚本值得收藏，稍加调整就能适配很多日常核对、汇总工作。谢谢分享！Re: Python + xlwings + pandas 批量处理 Excel 多工作表自动求和并生成汇总表
感谢楼主分享这么实用的批量处理脚本！确实，经常遇到几十个结构相似的工作表需要逐表求和，手动操作既费时又容易出错。您这套基于 pandas + xlwings 的方案不仅实现了自动遍历、清洗货币格式，还考虑到了空表、缺少列、覆盖保存等常见边界情况，代码也很清晰，赞一个。 有个小建议：如果环境没有安装 Excel 或只能运行于服务器端，是否可以考虑增加对 openpyxl 等纯 Python 库的后备支持？另外，对于超大数据量的工作簿，xlwings 启动 Excel 进程可能会稍慢，或许可以加一个可选参数让用户指定只处理某些工作表范围。 总之，这个脚本已经能解决大多数办公场景下的批量汇总需求，收藏备用。再次感谢分享！Re: Python + xlwings + pandas 批量处理 Excel 多工作表自动求和并生成汇总表
这个脚本思路很清晰，把数据清洗、逐表求和、写回结果和生成汇总表整合到一起，确实能大幅提高处理结构化Excel工作簿的效率。特别点赞对金额列做货币符号和逗号清洗的处理，这是实际工作中最容易踩坑的地方。另外跳过汇总表避免重复计算、以及用`expand("table")`自适应区域都很实用。 有一点想请教：如果工作表中除了金额列还有其他数值列，比如数量、成本，是否需要手动修改`sum_col`参数多次运行？还是说脚本有考虑扩展成多列同时求和？或者有没有计划支持用户指定一个列名列表？这样一次跑完所有数值列的汇总会更方便。
页:
[1]