Python批量读取文件夹下多个Excel文件并合并数据的实现方法

在日常数据处理与办公自动化中，按日期、店铺或业务员分散生成的Excel报表极为常见。使用Python实现批量读取与合并的核心路径非常直接：利用标准库pathlib或glob遍历目标文件夹获取全部文件路径，通过数据分析库pandas循环读取每个文件转化为DataFrame对象并暂存在列表中，最后通过pd.concat()方法进行一次性纵向合并并导出。本文将围绕这一标准方案展开具体实现步骤、边界处理与工程实践。

图1：Python批量读取并合并Excel文件的核心执行流程

一、准备工作与环境依赖

实现该方案需要Python环境安装好pandas以及对应的底层Excel解析引擎。由于不同版本的Excel文件格式存在技术分歧，通常推荐安装openpyxl（负责解析.xlsx文件）与xlrd（负责解析早期的.xls文件）。

可以在终端或命令行工具中执行以下安装命令：

pip install pandas openpyxl xlrd

二、标准完整实现代码

以下为最典型且健壮的批量读取与合并代码。代码采用面向对象的pathlib.Path处理文件路径，具备跨操作系统兼顾Windows和macOS/Linux路径分隔符的优势，并自动规避Excel软件打开时产生的临时文件（以~$开头的文件）。

import os
from pathlib import Path
import pandas as pd

def batch_merge_excel(folder_path, output_filepath):
    """
    批量读取指定目录下的所有Excel文件并合并为一个新文件
    :param folder_path: 目标文件夹路径
    :param output_filepath: 合并后输出的文件路径
    """
    target_dir = Path(folder_path)
    if not target_dir.exists():
        raise FileNotFoundError(f"指定目录不存在: {folder_path}")

    # 1. 获取目录下所有 .xlsx 和 .xls 文件，过滤临时文件
    excel_files = [
        f for f in target_dir.iterdir()
        if f.is_file() 
        and f.suffix.lower() in [".xlsx", ".xls"] 
        and not f.name.startswith("~$")
    ]

    if not excel_files:
        print("未在该文件夹中找到符合条件的Excel文件。")
        return None

    print(f"共发现 {len(excel_files)} 个待合并文件，开始读取...")

    # 2. 循环读取每个文件并保存至列表
    df_list = []
    for filepath in excel_files:
        try:
            # 默认读取第一个工作表 (sheet_name=0)
            df = pd.read_excel(filepath, engine=None)
            
            # 为数据追加一列来源文件名，便于追溯原始记录
            df["数据来源"] = filepath.name
            
            df_list.append(df)
            print(f"成功读取: {filepath.name}，行数: {len(df)}")
        except Exception as e:
            print(f"读取文件失败 [{filepath.name}]: {e}")

    if not df_list:
        print("有效数据为空，未执行合并。")
        return None

    # 3. 一次性拼接并重置全局索引
    merged_df = pd.concat(df_list, axis=0, ignore_index=True)
    
    # 4. 导出为新Excel文件
    output_dir = Path(output_filepath).parent
    output_dir.mkdir(parents=True, exist_ok=True)
    
    merged_df.to_excel(output_filepath, index=False)
    print(f"\n合并完成！总计合并行数: {len(merged_df)}，已保存至: {output_filepath}")
    return merged_df

if __name__ == "__main__":
    # 示例调用
    data_folder = "./sales_reports"
    output_file = "./sales_reports/merged_result.xlsx"
    batch_merge_excel(data_folder, output_file)

图2：pd.concat 字段对齐机制与索引重构示意

三、常见边界异常与排查策略

在实际生产环境中，不同部门提交的Excel往往并非完全规整。如果不进行前置预防，脚本容易在执行中途崩溃或产出不符合预期的脏数据。以下为四类最典型的实际问题与对应解决方案：

1. 表头行位置不一致（跳过首部说明文字）

部分Excel前几行包含“公司抬头”、“统计周期”等说明性文字，导致真正的表头落在第2行甚至第3行。使用read_excel时，可通过header参数指定有效表头所在行数（从0开始计数）：

# 指定第3行为表头（前2行为说明信息），自动跳过上方非数据行
df = pd.read_excel(filepath, header=2)

2. 包含多个Sheet（工作表）的数据合并

默认情况下pd.read_excel仅提取第一个Sheet。如果每个工作簿内含有多个同构工作表（例如按月份划分的12个Sheet），可向sheet_name传入None，此时将返回一个字典，键为Sheet名称，值为对应的DataFrame：

# 读取全部Sheet并展开合并
all_sheets = pd.read_excel(filepath, sheet_name=None)
for sheet_name, sheet_df in all_sheets.items():
    sheet_df["来源Sheet"] = sheet_name
    sheet_df["来源文件"] = filepath.name
    df_list.append(sheet_df)

3. 身份证号、工号等长数字变为科学计数法或丢失前导零

Excel默认会将纯数字的编号识别为浮点或整数，使得“00123”变为“123”，或18位身份证号被转换为1.10101E+17。通过向read_excel显式传递dtype参数，可强制以字符串格式载入指定列：

# 强制指定特定列为字符串类型，避免精度丢失与前导零截断
df = pd.read_excel(filepath, dtype={"员工编号": str, "身份证号": str})

图3：常规读取缺陷与防御性合并方案对照

四、关键API与技术选型建议

处理Excel批量读取有多种库可供挑选。对于常见的数据统计与分析场景，pandas是首选；但若目标是保留原工作簿的高级格式（如单元格背景色、边框、公式），则需要结合不同工具：

图4：批量合并操作工程化落地清单

五、总结

通过pathlib扫描路径、pandas.read_excel读取以及pandas.concat合并，构成了Python批量处理表格数据的经典架构。在实际落地时，切忌在循环体内部反复赋值拼接DataFrame，而应始终使用Python内置列表作为内存缓冲区，以获得毫秒级的执行性能。在此基础上，加入对临时文件的排除、列名空格清除以及长数字文本化保护，即可构建出一套高可用、适应各类复杂边界的批量合并工作流。