多个 Excel 工作簿合并成一张总表：列序不同也能对齐（含 4 个实跑大坑）

2026-10-06 · 喵窝资源铺

每个部门 / 每个门店 / 每个月各交一份 Excel，月底要拼成一张总表——复制粘贴到手抽筋，而且只要有一份的列顺序不一样、中间夹了空行、表头抄了两遍，总表就悄悄错位。这篇给一份多工作簿多 Sheet 合并的脚本：列序不同按列名对齐、空行剔除、重复表头去重、来源文件和表名记进前两列可追溯。代码完整实跑过，真实输出贴在下面。

它做的判断顺序

  1. 扫目录下所有 .xlsx/.xls，排除 ~$ 锁文件和输出文件自己（见坑 1 和坑 4，这两个都不是锦上添花）；
  2. 以第一个文件的首行为全局表头；其余文件首行与它比对，列序不同就打警告并按列名对齐，不是硬按位置拼；
  3. 每张表自带的表头行（和全局表头相同）自动跳过，中间空行剔除并记录在案；
  4. 公式单元格用「缓存值 + 原公式」双读：缓存值丢了就标 @FORMULA(...) 转人工，绝不静默填空（见坑 2）；
  5. 输出总表前两列是来源文件 / 来源表名，出了问题能追到哪份原件哪张 Sheet。

脚本全文

import os, sys, glob
import openpyxl
from openpyxl.utils import get_column_letter

SRC = sys.argv[1]
OUT = os.path.abspath(sys.argv[2] if len(sys.argv) > 2 else os.path.join(SRC, '合并总表.xlsx'))

files = sorted(f for f in glob.glob(os.path.join(SRC, '*.xls*'))
               if not os.path.basename(f).startswith('~$')
               and os.path.abspath(f) != OUT)   # 坑4：输出文件自己别当输入
if not files:
    sys.exit('没找到任何 .xlsx/.xls 文件')

def is_blank(vals):
    return all(v is None or str(v).strip() == '' for v in vals)

rows, skipped, warns = [], [], []
global_header = None
for f in files:
    wbv = openpyxl.load_workbook(f, data_only=True)   # 读计算结果
    wbf = openpyxl.load_workbook(f, data_only=False)  # 读公式源码
    own_header = None
    for k, ws in enumerate(wbv.worksheets):
        wsf = wbf.worksheets[k]
        for i, raw in enumerate(ws.iter_rows(values_only=True), 1):
            vals = list(raw)
            if is_blank(vals):
                skipped.append((os.path.basename(f), ws.title, i, '空行')); continue
            if own_header is None:
                own_header = ['' if v is None else str(v).strip() for v in vals]
                if global_header is None:
                    global_header = own_header
                elif own_header != global_header:
                    warns.append(f'{os.path.basename(f)} 列序不同，按列名对齐')
                continue
            if ['' if v is None else str(v).strip() for v in vals] == own_header:
                skipped.append((os.path.basename(f), ws.title, i, '重复表头')); continue
            rec = []
            for ci, v in enumerate(vals, 1):
                fv = wsf.cell(row=i, column=ci).value
                if v is None and isinstance(fv, str) and fv.startswith('='):
                    rec.append(f'@FORMULA({fv})')   # 坑2：无缓存值的公式转人工
                    warns.append(f'{os.path.basename(f)}/{ws.title} {get_column_letter(ci)}{i} 公式无缓存值')
                else:
                    rec.append(v)
            m = dict(zip(own_header, rec))          # 按列名对齐，不按位置
            rows.append((os.path.basename(f), ws.title) + tuple(m.get(h) for h in global_header))

out = openpyxl.Workbook(); s = out.active
s.append(['来源文件', '来源表'] + list(global_header))
for r in rows: s.append(list(r))
out.save(OUT)

print(f'合并 {len(files)} 个文件 -> 数据行 {len(rows)}，跳过 {len(skipped)}')
for w in warns: print('  [!] ' + w)
for r in rows: print(' ', r)
for f, ws, i, why in skipped: print(f'  [跳] {f}/{ws} 第{i}行：{why}')

用法：python3 merge_workbooks.py 目录 [输出.xlsx]。合并前建议把输出文件放到源目录外面，双保险。

实跑输出

造了 4 份测试表：华东区（正常 + 1 空行 + 表头抄两遍）、华北区（列序不同：日期在姓名前面 + 多一列备注）、华南区（两个 Sheet）、公式区（金额列是公式）：

合并 4 个文件 -> 数据行 7，跳过 3
  [!] 公式区.xlsx/10月 C2 公式无缓存值
  [!] 华北区.xlsx 列序不同，按列名对齐
  ('公式区.xlsx', '10月', '周八', '10-07', '@FORMULA(=A2)')
  ('华东区.xlsx', '10月', '张三', '10-01', 120.5)
  ('华东区.xlsx', '10月', '李四', '10-02', 80)
  ('华北区.xlsx', '10月', '王五', '10-03', 200)
  ('华北区.xlsx', '10月', '赵六', '10-04', 99.9)
  ('华南区.xlsx', '10月', '孙七', '10-05', 60)
  ('华南区.xlsx', '加班表', '孙七', '10-06', 30)
  [跳] 华东区.xlsx/10月 第3行：空行
  [跳] 华东区.xlsx/10月 第5行：重复表头
  [跳] 华南区.xlsx/加班表 第1行：重复表头

逐行核对输出的总表文件（防「合并了但内容错位」）：

行核对结果
王五 / 赵六华北区列序是「日期,姓名」，按列名对齐后姓名/日期/金额各归其位✅
孙七 × 210月 和 加班表 两个 Sheet 都进总表，来源表名可区分✅
周八公式无缓存值 → @FORMULA 显式标记，不是静默变空✅ 转人工
空行 × 1 / 重复表头 × 2全部剔除且列明位置✅

连跑第二次：仍是 4 个文件 -> 7 行，行数不变——幂等通过（修复前是 14 行，见坑 1）。

4 个真坑（都是实跑踩出来的）

坑 1：输出文件落在源目录里，二次运行把自己再合并一遍，数据直接翻倍。实跑复现：初版没排除输出文件，合并总表.xlsx 就存在源目录里；第二次运行 glob 又扫到它，把上次的合并结果当输入再并一遍——7 行变 14 行，周八出现了两遍，而且不报任何错。跑 N 次就是 2^(N-1) 倍。修复：glob 时把 os.path.abspath(OUT) 排除掉。这和「归档脚本扫到自己产出的年目录」是同一族坑：任何"扫目录→写同目录"的脚本都要想清楚自己会不会吃自己。
坑 2：openpyxl 刚写的公式没有缓存值，data_only=True 读出来是 None，数据静默丢失。实跑复现：金额列写 =A2 的测试文件，data_only=True 读出来金额是 None——不是报错，是安安静静变成空。原因是 Excel 保存时才在文件里存「公式的最后一次计算结果」，openpyxl 生成的文件没这一层，data_only=True 自然拿不到。修复：公式版 + 数值版双开 workbook，数值为空而原格是 = 开头的公式时标 @FORMULA(...) 转人工。顺带提醒：用 Excel/WPS 正常保存过的文件没有这个问题（缓存值存在），坑主要出现在程序生成或某些工具转换过的表上——所以不能因为"我这边测了没问题"就不设防。
坑 3：列序不同的分表，表头行会被当数据混进总表。实跑复现：华北区表头是「日期,姓名,金额」，和全局的「姓名,日期,金额」不同，初版按「首行当表头、后续行全收」的逻辑跑完，华北区的表头行原样进了数据区（输出里能看到一行『日期 姓名 金额』），而它的数据列也整体错位——王五的姓名列里躺着的是日期。修复：每份文件的首行先与全局表头比对，不同就打警告并按列名对齐（用 dict(zip(own_header, rec)) 重排）。如果你们的分表连列名都不统一（比如「金额」vs「报销金额」），那就得先维护一张列名映射表，纯脚本兜不住，如实说明。
坑 4：~$ 开头的锁文件会被当成正经文件并进总表。实跑确认：Excel 开着文件时目录里会有 ~$华东区.xlsx 这种隐藏锁文件，不排除的话它会被并表（内容通常只有个用户名），总表里凭空多出一行来历不明的数据。修复：glob 后按 startswith('~$') 过滤。注意 Windows 资源管理器默认看不见它们，所以你眼睛检查目录时觉得"很干净"没用，得在代码里拦。

边界说明（如实）

不想自己折腾合并？把文件发给我。十几份部门表要月底拼总表、多个 Sheet 要归一、合并前要查重复报销，我可以替你写好脚本、用你的真实文件实测后交付（¥19.9 表格/文件批量处理 / ¥29.9 脚本定制）。
→ 去下单页看看 ｜ 虚拟物品交付后不退款；未收到或未达承诺免费补发/重做。

相关文章：值班表排班冲突检测 · 报销单按事由关键词归集科目 · 发票号批量提取查重 · 邮箱列清洗去重

← 更多实跑干货 ｜ 喵窝资源铺