几十个 Excel 汇总成一张总表？Python 自动拼接还标来源

2026-10-02 · 喵窝资源铺

各门店/各区域/各个月份各发一个 Excel，月底要把它们拼成一张总表——手动复制粘贴几十次，还要防漏行、防格式错位。这篇给一个 本地运行 的 pandas 脚本：把一个文件夹里所有 xlsx 按 sheet 名纵向拼成一张总表，每个文件一个 sheet，并且自动加一列「来源文件」，出问题能追到是哪个文件的数据。本文代码实跑验证，真实输出见下。

完整脚本

先 pip install pandas openpyxl，脚本保存为 merge_excel.py，在 Excel 文件夹里运行 python merge_excel.py . 汇总.xlsx：

"""批量合并Excel：目录下所有xlsx按sheet名纵向拼接成总表。用法：python merge_excel.py [目录] [输出文件]"""
import os, sys, glob
import pandas as pd

FOLDER = sys.argv[1] if len(sys.argv) > 1 else "."
OUT = sys.argv[2] if len(sys.argv) > 2 else "汇总.xlsx"
files = [f for f in sorted(glob.glob(os.path.join(FOLDER, "*.xlsx")))
         if os.path.basename(f) != OUT]
if not files:
    sys.exit("没找到xlsx文件")

# 先按sheet名把各文件的数据攒起来，最后一次性写出
# （直接在ExcelWriter里逐个to_excel会因同名sheet互相覆盖，只剩最后一个文件）
grouped = {}
for f in files:
    xls = pd.ExcelFile(f)
    for sheet in xls.sheet_names:
        df = pd.read_excel(f, sheet_name=sheet)
        df["_来源文件"] = os.path.basename(f)
        grouped.setdefault(sheet[:31], []).append(df)

with pd.ExcelWriter(os.path.join(FOLDER, OUT)) as w:
    for sheet, dfs in grouped.items():
        pd.concat(dfs, ignore_index=True).to_excel(w, sheet_name=sheet, index=False)

print(f"合并了 {len(files)} 个Excel -> {OUT}")
for f in files:
    print(" +", os.path.basename(f))
for sheet, dfs in grouped.items():
    print(f"  sheet[{sheet}]: {sum(len(d) for d in dfs)} 行")

实跑结果

我造了 3 个测试 Excel（华东区/华南区/华北区，都是「销售」sheet，共 5 行数据），跑一遍的输出：

合并了 3 个Excel -> 汇总.xlsx
 + 华东区.xlsx
 + 华北区.xlsx
 + 华南区.xlsx
  sheet[销售]: 5 行

验证产物：汇总.xlsx 里「销售」sheet 共 5 行数据，和 3 个源文件加起来对得上，每行都带上了来源标注：

再跑一遍验证幂等：还是合并 3 个源文件、还是 5 行——汇总.xlsx 自己没有被并进去。

三个容易踩的坑（都是真踩过的）

• 同名 sheet 互相覆盖：第一版脚本直接在 ExcelWriter 里逐个 to_excel，三个文件都有「销售」sheet，结果写出来只剩最后一个文件的 2 行——前面全被覆盖了，而且不报任何错。实测抓到后改成「先按 sheet 名攒成组、最后 concat 一次性写」，5 行全部在。
• 输出文件会被自己合并：汇总.xlsx 放在同一个文件夹时，第二版里 glob 会把它捞进去再并一遍，行数翻倍。脚本里有 and os.path.basename(f) != OUT 这一句堵住——这也是 PDF 合并那篇（批量合并 PDF）同款坑。
• sheet 名最长 31 字符：Excel 的硬限制，超了会写入失败，所以脚本里 sheet[:31] 截断。另外如果各文件的列名不完全一致（比如「金额」vs「销售额」），concat 会出现 NaN 列——先统一列名再合并，这也是我们 四个常用自动化脚本 里提过的思路。

再往上是什么活？

脚本处理的是「每个文件一个 sheet、列名一致」的标准情况。如果你要的是「各文件列名不一样要映射」「合并后按条件去重」「合并完直接出透视表和图发给老板」——逻辑要再包一层，可以直接找我们代写（见下）。

相关阅读：批量合并 PDF ｜ 批量重命名文件 ｜ 批量压缩图片 ｜ Python 自动化能帮你做什么