Python批量合并多个Excel文件并汇总数据

一、核心思路与标准操作流水线

在开始编写脚本之前，明确批量处理的数据流至关重要。很多人在初次接触这一任务时，习惯直接在脚本中边读边写，或者在循环体内不断执行 DataFrame 拼接，这往往会在数据量稍大时引发严重的性能瓶颈甚至程序崩溃。

一个健壮且可复用的批量合并程序，由以下六个标准阶段构成：

1. 路径检索与安全过滤：通过标准库 pathlib.Path 批量匹配目标目录下的所有 .xlsx 或 .xls 文件，同时必须显式排除以 ~$ 开头的临时锁文件，防止 Office 正在打开该表格时导致程序抛出读取异常。
2. 结构化读取与数据类型保护：调用 pandas.read_excel() 逐个载入文件，并对工号、订单编号、身份证号等关键字段显式指定 dtype=str，避免前导零被自动转换为纯数值而遭到裁切。
3. 注入来源元数据：为每个读取后的 DataFrame 新增一列（例如 来源文件 或由文件名解析出的区域与日期信息），确保合并成几十万行明细后依然具备精确的数据溯源能力。
4. 内存列表暂存：将读入的子表依次追加（append）到 Python 临时列表中，由列表保存对象指针，零额外数据拷贝开销。
5. 一次性矢量拼接：在循环体外使用 pandas.concat() 一次性完成纵向（axis=0）拼接，并指定 ignore_index=True 重建全局唯一行索引。
6. 多维汇总与导出：基于合并后的全局明细，通过 groupby 与 pivot_table 计算管理层需要的汇总指标，并利用 pandas.ExcelWriter 将明细表与汇总表输出至同一文件的不同工作表中。

图 1：批量合并与汇总的标准执行流水线与流转阶段

二、完整可执行代码与关键步骤解析

完成批量合并所依赖的核心第三方库主要有两个：pandas（用于数据组织与分析）和 openpyxl（用于读写现代 .xlsx 格式）。在命令行或虚拟环境中确认安装：

pip install pandas openpyxl

假定待处理的表格均存放在本地目录 ./sales_data/ 下，每个表格记录着不同门店的销售流水（包含字段：订单编号、销售日期、门店名称、销售件数、销售金额）。下面是规范、健壮的完整合并与汇总代码：

from pathlib import Path
import pandas as pd

# 1. 定义源数据目录与目标输出路径
SOURCE_DIR = Path("./sales_data")
OUTPUT_FILE = Path("./sales_summary_master.xlsx")

# 2. 检索并过滤有效 Excel 文件（排除 ~$ 临时缓存文件）
excel_files = [
    f for f in SOURCE_DIR.glob("*.xlsx") 
    if not f.name.startswith("~$")
]

if not excel_files:
    print("未在指定目录下找到符合条件的 Excel 文件，程序退出。")
    exit()

print(f"共扫描到 {len(excel_files)} 个待处理的 Excel 文件，开始读取...")

# 3. 循环载入并收集到内存列表中
dfs = []
for file_path in excel_files:
    # 针对编号类列声明 str 类型，避免 00128 变成 128
    df = pd.read_excel(
        file_path,
        sheet_name=0,
        dtype={"订单编号": str, "门店编号": str}
    )
    
    # 去除列名两端的潜在隐形空格，避免对齐撕裂
    df.columns = df.columns.str.strip()
    
    # 注入来源文件名，方便事后审计核对
    df["数据来源"] = file_path.name
    
    dfs.append(df)

# 4. 一次性完成高效拼接
merged_df = pd.concat(dfs, ignore_index=True, sort=False)
print(f"合并完成！总计整合明细记录数：{len(merged_df)} 行。")

# 5. 执行数据汇总（示例：按门店统计总件数与销售总额）
store_summary = (
    merged_df.groupby("门店名称", as_index=False)
    .agg(
        订单总数=("订单编号", "count"),
        销量合计=("销售件数", "sum"),
        总销售额=("销售金额", "sum")
    )
    .sort_values(by="总销售额", ascending=False)
)

# 6. 使用 ExcelWriter 统一交付至单文件双 Sheet
with pd.ExcelWriter(OUTPUT_FILE, engine="openpyxl") as writer:
    merged_df.to_excel(writer, sheet_name="合并明细数据", index=False)
    store_summary.to_excel(writer, sheet_name="各门店业绩汇总", index=False)

print(f"汇总报告已成功生成至：{OUTPUT_FILE}")

在上述代码中，最核心的工程设计在于使用了 dfs.append(df) 配合循环外的 pd.concat(dfs)。这与许多初学者习惯写的 merged_df = pd.concat([merged_df, df]) 存在本质上的效率差异。

图 2：合并底层机制对比：循环内追加 vs 列表暂存一次性合并

三、常见认知混淆与边界辨析

在真实业务场景中，由于不同分支机构填报习惯各异，批量合并往往会遇到各种异构数据问题。以下是对几个最常见混淆说法的深入剖析：

1. 辨析：各个子表格的列顺序不一致，会导致合并错位吗？

结论：完全不会。在部分基于数组索引合并的工具中，列顺序必须严格一致，否则第一列的数字会直接贴入第二列。但 pandas 的 pd.concat(axis=0) 默认基于列标签（Column Name）自动对齐。例如，A 表列顺序为 [日期, 销售额, 门店]，B 表列顺序为 [门店, 日期, 销售额]，合并时 pandas 会自动匹配同名列，数据不会发生任何位移。

需要注意的边界：虽然列顺序无需一致，但列名必须字面完全相同。常见的“翻车”点是某张表的表头末尾多打了一个半角空格（如 "销售额 " 与 "销售额"）。pandas 会判定为两列，导致合并后出现两列销售额且互为缺失值（NaN）。因此，在读取后统一执行 df.columns = df.columns.str.strip() 是必不可少的防护动作。

2. 辨析：一个 Excel 文件内有多个工作表（Sheet）该如何处理？

pd.read_excel(file_path) 在缺省状态下仅读取工作簿的第一个 Sheet（索引为 0）。如果业务数据分散在同一文件内的不同 Sheet 中（例如按月份划分的 Sheet），可以通过传入 sheet_name=None 读取整个工作簿的所有工作表：

# 读取所有工作表，返回字典：{sheet_name: DataFrame}
sheets_dict = pd.read_excel(file_path, sheet_name=None)
for sheet_name, sheet_df in sheets_dict.items():
    sheet_df["工作表名"] = sheet_name
    sheet_df["来源文件"] = file_path.name
    dfs.append(sheet_df)

3. 辨析：数据类型自动推断为什么会导致前导零消失？

Excel 和 pandas 在读取未标注类型的文本字段时，如果发现整列全由数字字符组成（例如工号 "00128"），底层解析器会自动将其解析为整数（128）。这种推断看似省心，实则破坏了编码规范；更有甚者，如果某些子表的该列存在少量空值或文本，pandas 会将整列提升为浮点型（float64），原本的工号直接变成 128.0。因此，任何带有业务编码属性的字段，必须在读取时通过 dtype=Ellipsis 显式固定为字符串类型。

图 3：现实场景中的表头对齐、格式陷阱与容错机制

四、合并后的多维度数据统计与汇总

将全部历史明细合并为一个完整的 DataFrame 后，便拥有了全局分析视图。常见的汇总计算包括“多指标分组聚合”以及“行列交叉透视表”。

1. 多维度分组聚合（groupby）

假设我们希望同时按“大区”和“商品类别”汇总销售总额、总成交单量与平均客单价，可以使用 agg 传递自定义聚合字典：

summary_grouped = (
    merged_df.groupby(["所属大区", "商品类别"], as_index=False)
    .agg(
        总销售额=("销售金额", "sum"),
        订单总量=("订单编号", "count"),
        平均单价=("销售金额", "mean")
    )
    .round(2)
)

2. 生成交叉矩阵透视表（pivot_table）

若需要制作一张横向为月份、纵向为各大区的财务透视表，并附带行与列的汇总总计，pivot_table 能够一步到位：

pivot_summary = pd.pivot_table(
    merged_df,
    index="所属大区",
    columns="销售月份",
    values="销售金额",
    aggfunc="sum",
    fill_value=0,
    margins=True,
    margins_name="总计"
)

五、总结与排查决策清单

回顾整个批处理过程，Python 的高灵活性使得几行代码就能完成数据汇聚，但真正决定脚本在生产环境中能否稳定运行的，往往是对边界细节的把控。在将脚本投入日常自动化任务前，可参照以下架构与排查树进行核对：

图 4：批量合并与汇总全流程架构与排查决策树

• 锁文件已排除：检查是否过滤了 ~$*.xlsx，防止多人协同编辑时的文件锁中断脚本。
• 内存一次性拼接：严格使用 dfs = []; dfs.append(df) 收集，循环外统一 pd.concat。
• 索引重置：合并时必须指定 ignore_index=True，避免原表各自的行号产生大量重复索引。
• 列名去除空格：执行 df.columns = df.columns.str.strip()，防止同一指标分裂为两列。
• 关键编码类型保护：订单号、条形码、手机号等显式设定 dtype=str，防止前导零丢失。
• 来源追溯：为每一批数据保留 来源文件 字段，便于最终校对与异常排查。