摘要

周五下午 5:30，产品经理甩来一个压缩包，说道：“这里有 1000 份不同地区的销售报表，下班前整合成一张总表，我得赶周报。”

此时你面临两个选择：

• A. 戴上痛苦面具，手动打开 Excel，Ctrl+C、Ctrl+V 直到手抽筋，最终加班到深夜。
• B. 淡定一笑，运行一段 Python 脚本，5 分钟后准时打卡，直奔峡谷开黑。

作为技术人员，答案当然是 B。本文不讲理论，直接奉上一套可立即投入使用的 Excel 合并与数据清洗脚本，高效解决批量处理难题。

为何不选 Excel 宏（VBA）？

有人可能会说：“VBA 不也能干这事？”

确实可以，但面对上千个文件时，VBA 显得力不从心：

• 慢：每次打开、读取、关闭 Excel 文件都伴随巨大系统开销，VBA 运行时界面频繁刷新，极易卡死。
• 卡：一旦数据量突破 10 万行，Excel 开始明显变慢；超过百万行？直接崩溃无误。
• 难维护：VBA 的语法陈旧，结构混乱，后期修改成本高，阅读体验极差。

Pandas

相比之下，Python 的数据处理生态才是应对此类批量任务的利器。它无需图形界面，直接在内存中操作二进制流，读写效率极高，速度远超传统方式。

场景一：简单合并（由浅入深）

1.1 需求说明

假设你有一个目录，

data_folder

存放了大量

.xlsx

格式的报表文件，每个文件结构一致（列名相同），目标是将所有文件纵向拼接，生成一份统一的汇总表。

1.2 基础实现（入门版）

先安装必要依赖：

pip install pandas openpyxl

以下是常见于网络的基础代码，功能可用，但健壮性不足：

import pandas as pd
import os

# 设置文件夹路径
folder_path = './sales_data'
file_list = []

# 遍历文件夹
for filename in os.listdir(folder_path):
    if filename.endswith(".xlsx"):
        file_path = os.path.join(folder_path, filename)
        # 读取文件
        df = pd.read_excel(file_path)
        file_list.append(df)

# 合并所有 DataFrame
result = pd.concat(file_list, ignore_index=True)

# 保存结果
result.to_excel("final_result.xlsx", index=False)
print("搞定！")

1.3 升级方案（生产级）

上述脚本在真实环境中容易出错：

• 遇到子文件夹会忽略或报错？
• 某个文件损坏是否导致整个流程中断？
• 执行过程黑屏无反馈，只能干等？

为此，我们引入更现代的工具增强稳定性与可观测性：使用 pathlib 替代 os 操作路径，搭配 tqdm 添加进度条反馈。

安装进度条库：

pip install tqdm

pathlib

tqdm

以下是可用于实际项目的完整脚本：

import pandas as pd
from pathlib import Path
from tqdm import tqdm  # 进度条库，执行过程清晰可见
import time

def merge_excels(folder_path, output_name="merged_output.xlsx"):
    print(f"???? 开始扫描文件夹：{folder_path}")
    
    # 递归查找所有 .xlsx 文件，包括子目录
    files = list(Path(folder_path).rglob("*.xlsx"))
    
    if not files:
        print("? 没找到 Excel 文件，检查路径是否正确？")
        return
    
    print(f"???? 共发现 {len(files)} 个文件，开始合并...")
    
    all_data = []
    error_files = []

    # 使用 tqdm 包裹循环，实时显示进度
    for file in tqdm(files, desc="Processing"):
        try:
            # 显式指定 openpyxl 引擎，提高兼容性和稳定性
            df = pd.read_excel(file, engine='openpyxl')
            # 添加来源标记，便于后续追踪数据归属
            df['source_file'] = file.name
            all_data.append(df)
        except Exception as e:
            # 出错仅记录，不影响整体流程
            error_files.append(str(file))
            print(f"\n?? 读取失败: {file.name}, 原因: {e}")

    # 合并有效数据
    if all_data:
        result = pd.concat(all_data, ignore_index=True)
        result.to_excel(output_name, index=False)
        print(f"? 成功合并 {len(all_data)} 个文件，结果已保存至：{output_name}")
    else:
        print("? 所有文件读取失败，未生成输出文件。")

    # 输出错误日志
    if error_files:
        print(f"? 以下 {len(error_files)} 个文件读取失败：")
        for f in error_files:
            print(f"  - {f}")

该版本具备以下优势：

• 支持递归遍历子目录
• 自动跳过损坏文件，避免中断
• 实时进度条反馈执行状态
• 记录错误文件列表，便于排查
• 附加源文件名字段，增强数据溯源能力

开箱即用，适合部署于日常办公自动化流程中。

print("???? 正在拼接数据表（这可能需要一点内存）...")
final_df = pd.concat(all_data, ignore_index=True)
print(f"???? 正在保存结果，共 {len(final_df)} 行数据...")
final_df.to_excel(output_name, index=False)
print(f"? 成功！文件已保存为 {output_name}")
else:
print("? 没有读取到任何有效数据。")
if error_files:
print(f"?? 注意！有 {len(error_files)} 个文件处理失败，请检查日志。")
# 你的文件夹路径放这里
merge_excels(r"C:\Users\Admin\Desktop\WeeklyReports")

代码核心亮点解析

该脚本在处理大量分散的 Excel 文件时表现出极强的鲁棒性与自动化能力，主要体现在以下几个方面：

1. 递归遍历文件结构

通过递归方式扫描指定目录及其所有子目录，无论目标文件嵌套多深，都能被完整检索并纳入处理流程。这种机制确保了数据采集的全面性。

rglob

2. 异常容错处理机制

在读取过程中若遇到损坏或格式异常的文件，程序不会立即中断，而是记录错误文件名，并继续处理其余正常文件。最终统一提示用户哪些文件未能成功加载，提升整体稳定性。

try-except

3. 数据来源追溯功能

合并后的数据会保留原始文件的信息标记，新增一列用于标识每条记录源自哪个文件。这一设计在后续排查问题或核对原始数据时极为关键。

source_file

file.name

4. 可视化执行进度反馈

通过打印状态信息模拟进度提示，让用户清晰掌握当前所处阶段，增强操作可控感——尤其是面对大规模数据时的心理安慰剂。

场景二：合并后数据清洗（避坑实战指南）

完成数据合并只是起点。实际业务中收集来的表格往往存在各种质量问题，例如列名含空格、重复行、缺失关键字段等。手动在 Excel 中处理不仅效率低下，还容易因数据量过大导致软件卡顿甚至崩溃。推荐全程使用 Python 自动化清洗。

def clean_data(df):
    print("???? 开始清洗数据...")
    # 1. 统一列名（去除首尾空格）
    # 常见陷阱：表面相同的列名因隐藏空格无法匹配
    df.columns = df.columns.str.strip()

    # 2. 删除完全重复的记录
    before = len(df)
    df.drop_duplicates(inplace=True)
    print(f"?? 删除了 {before - len(df)} 行重复数据")

    # 3. 填补关键字段的缺失值
    # 示例：将 '销售额' 列中的空值替换为 0
    if '销售额' in df.columns:
        df['销售额'] = df['销售额'].fillna(0)

    # 4. 过滤无效数据行
    # 示例：剔除 '姓名' 字段为空的整行数据
    if '姓名' in df.columns:
        df = df.dropna(subset=['姓名'])

    return df

# 合并完成后调用清洗函数
# final_df = clean_data(final_df)

资深开发者经验分享

关于列名不一致问题

当面对上千个 Excel 文件时，常见情况是各文件列顺序不同，或某些文件缺少部分字段。pd.concat() 能够根据列名自动对齐数据，对于缺失的列，默认填充 NaN 值，避免结构错乱。

pd.concat

NaN

注意：此机制依赖于列名严格一致。如 “User” 与 “Username” 将被视为两个独立字段，需提前标准化命名。

应对内存溢出（OOM）风险

若总数据量达到数 GB 级别，一次性加载至内存可能导致程序崩溃。

解决方案：避免将所有 DataFrame 存入列表。可改为流式处理：初始化一个空 CSV 文件，每读取一个 Excel 文件，便将其内容追加写入 CSV。

CSV 作为文本流格式，占用内存远小于 Excel，且支持逐块读写，更适合大数据场景。

all_data

mode='a'

Excel 文件潜在陷阱

部分看似“空白”的 Excel 实际包含大量非空单元格（如仅含空格或格式标记），Python 读取后会产生大量 NaN 或空字符串行。

建议在读取后立即执行清理步骤，利用 dropna(how='all') 或类似逻辑删除全为空值的行。

df.dropna(how='all')

结语：技术的本质是提效

真正的生产力工具，不是让人更忙，而是让人更闲。当你把这套自动化流程打包成可执行脚本（.py 或 .exe）交给非技术人员使用时，他们眼中流露的惊叹，以及随之而来的奶茶邀请，就是对你技术价值最直接的认可。

.exe

告别无意义的复制粘贴，把时间留给更有价值的事。代码拿走，早点下班。