Python批量处理多个Excel工作簿：工作表分类汇总与数值清洗实战
问题背景：手工汇总Excel报表的低效与风险在日常办公中，经常需要处理一个文件夹里的多个Excel文件（工作簿），每个文件又包含多张工作表。领导要求按“销售区域”汇总“销售利润”。如果手工操作：打开工作簿→切换工作表→分类汇总→复制结果→保存，然后重复。工作簿数量一多，不仅慢，而且容易漏表、选错区域、金额列被当成文本、临时文件被误处理等。Python自动化可以把重复动作抽象成稳定流程，避免人为错误。
适用场景
这类脚本适合以下场景：一个文件夹内有多个.xlsx工作簿；每个工作簿有多张工作表；表头结构相同或相似；需要按某个字段分组（如销售区域、客户名称、部门、产品类型）；需要对数值字段求和（如销售利润、销售额、数量、成本）；希望汇总结果写回原工作表右侧，方便查看。建议先在测试文件夹复制2～3个样例文件试运行，确认无误后再处理正式数据，避免批量误操作损坏原始文件。
核心原理：批处理流水线
手工汇总步骤拆开后就是固定流水线：扫描文件夹→打开工作簿→遍历工作表→读取数据→分组汇总→写回保存。三个库各有分工：os负责文件系统处理（扫描、拼接路径、判断扩展名）；xlwings负责操作Excel（打开工作簿、访问工作表、写回结果）；pandas负责数据处理（将表格转为DataFrame，用groupby()分类汇总）。这条流程搭好后，只需替换中间的数据处理逻辑，即可实现批量筛选、排序、拆分等需求。
操作前准备
首先安装依赖：
pip install pandas xlwings
注意：xlwings在Windows上依赖本机已安装的Microsoft Excel。
建议目录结构：原始报表和输出结果分开存放。例如：项目目录下建“销售表”文件夹放原始文件，另建“输出结果”文件夹保存处理后的文件。
字段要求：每张工作表至少包含两个字段：销售区域、销售利润。如果字段名不同，需修改代码中的参数。
完整代码：批量处理多个工作簿中的所有工作表
以下代码考虑了真实办公中的常见问题：跳过~$临时文件、跳过空表、校验字段、清洗金额、另存输出、退出Excel。
import os
import pandas as pd
import xlwings as xw
def clean_to_number(series: pd.Series) -> pd.Series:
"""
将可能带有货币符号、逗号、空格、文本前缀的金额列清洗为数值。
例如：￥12,345.67 -> 12345.67，profit: 6543.21 -> 6543.21
"""
series = series.astype(str).str.strip()
series = series.str.replace(",", "", regex=False)
series = series.str.replace(r"[¥￥$ ]", "", regex=True)
series = series.str.replace(r"[^0-9\.\-]", "", regex=True)
return pd.to_numeric(series, errors="coerce")
def summarize_one_sheet(
df: pd.DataFrame,
group_col: str = "销售区域",
value_col: str = "销售利润"
) -> pd.DataFrame:
"""
对单张工作表数据进行分类汇总。按group_col分组，对value_col求和。
"""
if group_col not in df.columns:
raise KeyError(f"缺少分组列：{group_col}")
if value_col not in df.columns:
raise KeyError(f"缺少汇总列：{value_col}")
temp = df.copy()
# 先清洗成数值，避免字符串求和或排序错误
temp = clean_to_number(temp).fillna(0)
result = (
temp.groupby(group_col, dropna=False)
.sum()
.reset_index()
.rename(columns={
group_col: "销售区域",
value_col: "销售利润汇总"
})
.sort_values("销售利润汇总", ascending=False)
)
return result
def batch_summary_workbooks(
input_folder: str,
output_folder: str,
group_col: str = "销售区域",
value_col: str = "销售利润",
start_cell: str = "A1",
write_cell: str = "J1"
) -> None:
"""
批量处理多个工作簿中的所有工作表。
"""
os.makedirs(output_folder, exist_ok=True)
app = xw.App(visible=False, add_book=False)
app.display_alerts = False
app.screen_updating = False
try:
for file_name in os.listdir(input_folder):
# 跳过Excel临时文件和非xlsx文件
if file_name.startswith("~$"):
continue
if not file_name.lower().endswith(".xlsx"):
continue
input_path = os.path.join(input_folder, file_name)
output_path = os.path.join(output_folder, file_name)
print(f"\n 正在处理工作簿：{input_path}")
wb = app.books.open(input_path)
success_count = 0
skip_count = 0
try:
for sht in wb.sheets:
try:
rng = sht.range(start_cell).expand("table")
if rng.value is None:
print(f" {sht.name}：空表")
skip_count += 1
continue
df = rng.options(pd.DataFrame, header=1, index=False).value
if df is None or df.empty:
print(f" {sht.name}：无有效数据")
skip_count += 1
continue
summary_df = summarize_one_sheet(
df,
group_col=group_col,
value_col=value_col
)
# 清理旧汇总区，避免上一次结果残留
sht.range(write_cell).resize(100, 3).clear_contents()
# 写回汇总结果，不写入DataFrame索引
sht.range(write_cell).options(index=False).value = summary_df
sht.autofit()
print(f" {sht.name}：已汇总到 {write_cell}")
success_count += 1
except Exception as e:
print(f" {sht.name}：{e}")
skip_count += 1
wb.save(output_path)
print(f" 已保存：{output_path}，成功 {success_count} 张表，跳过 {skip_count} 张表")
finally:
wb.close()
finally:
app.quit()
print("\n 所有工作簿处理完成")
if __name__ == "__main__":
batch_summary_workbooks(
input_folder=r"销售表",
output_folder=r"输出结果",
group_col="销售区域",
value_col="销售利润",
start_cell="A1",
write_cell="J1"
)
关键判断：必须先清洗数值再分类汇总
很多新手直接写 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处理。这套思路可以扩展到按客户名称汇总销售额、按部门统计费用、按产品类型汇总订单数量，甚至将每个工作簿的汇总结果合并成总表。批量处理脚本一定要先用样例数据验证，再处理正式数据，尤其是涉及金额汇总、财务报表时。
Re: Python批量处理多个Excel工作簿：工作表分类汇总与数值清洗实战
这个脚本写得很实用，特别是对“~$”临时文件和字段校验的处理很周全，能在正式跑数据前避免不少坑。数值清洗那部分对带货币符号和逗号的金额也很友好，实际工作中很多报表确实不干净。 有一点想请教：输出结果写回原工作簿的右侧（比如J列），如果原工作表的数据列数超过J列，或者后面有其他公式，会不会有覆盖的风险？是否考虑过增加一个“新工作表写入结果”的选项？另外，`start_cell`参数好像没有在函数里用到，是不是留着给其他扩展场景预留的？ 整体流程清晰，注释也到位，对新手很友好。建议加一个对文件编码或单元格格式的说明，比如金额列清洗后保持数字格式，免得导出后还是文本。感谢分享。Re: Python批量处理多个Excel工作簿：工作表分类汇总与数值清洗实战
您这套脚本思路很清晰，把日常手工汇总的痛点（临时文件、文本金额、空表等）都考虑进去了，特别是 `clean_to_number` 函数对货币符号和逗号的处理很实用。我平时也常用类似方式批量处理报表，不过有个小建议：如果源文件的`销售区域`列有合并单元格或空值，groupby(dropna=False)已经处理了空值，但合并单元格读进来可能会有前一行重复填充的问题，可以在读表时先 `ffill()` 一下。另外，xlwings 在 Excel 未安装的环境下会报错，您一般在正式跑之前会怎么快速校验环境？Re: Python批量处理多个Excel工作簿：工作表分类汇总与数值清洗实战
感谢分享！这个脚本设计得很实用，尤其是对办公中常见的脏数据（货币符号、逗号、空格、文本前缀）做了清洗，还考虑了临时文件`~$`和空表的情况，很贴心。我平时也常被Excel汇总折磨，手动操作容易漏数据，有了这个流水线就稳多了。有个小问题想请教：如果不同工作簿的表头字段名不完全一致，比如有的叫“销售区域”，有的叫“区域”，需要手动修改`group_col`参数吗？还是说脚本里可以加个字段映射逻辑？另外，输出结果写回原工作表右侧（比如J列），如果原表右侧已有其他数据，会不会被覆盖？希望楼主能再多讲一下这部分细节。
页:
[1]