Excel处理效率低？用Python实现文件批量重命名与表格合并

一、准备工作与环境依赖

完成本篇涉及的任务不需要复杂的开发环境。推荐使用 Python 3.8 或更高版本。在底层文件系统交互方面，我们将直接使用 Python 内置的 pathlib 和 re 模块，无需额外安装路径操作相关的第三方工具；在表格读取与合并方面，则依赖广泛使用的 pandas 库及其读取 .xlsx 文件所依赖的 openpyxl 引擎。

在终端或命令行界面中执行以下命令完成环境安装：

pip install pandas openpyxl

为了让后续的操作讲解具备明确参照，我们假定当前工作目录下存在一个名为 raw_reports 的文件夹，其中存放着来自各个分店、命名风格各异的 Excel 报表：

• 1月销售数据(副本).xlsx（含有杂质后缀）
• 2024-02_华东_最终版.xlsx（分隔符与修饰词不规范）
• 3月报表_待合并.xlsx（日期位数不齐且包含临时标记）

二、任务一：多Excel文件批量重命名

许多人在合并表格时常常直接跳过重命名阶段，这往往为后续埋下隐患。实际业务中，不同人员提交的文件名常常含有“副本”、“最终版”、“最新修改”等无规律修饰词，月份表示形式也有“1月”、“01”、“2024-01”等多种写法。先进行规范化重命名，一方面能清洗掉文件名噪音，统一提取出形如 202401_销售日报.xlsx 的标准化格式；另一方面，还能为后续提取文件名中的地区、周期等业务维度建立稳定的规则支撑。

在批量重命名时，直接执行改名操作非常危险，一旦正则匹配有误或目标文件名与已有文件冲突，极易造成文件覆盖或丢失。因此，成熟的批量重命名代码必须包含 Dry-run（模拟试运行机制）：先将新旧路径打印在终端供人工比对，并在代码层面执行目标文件存在性检查，确认无误后再执行实际的物理重命名。

以下为完整的批量重命名脚本。该脚本使用 pathlib.Path 遍历目标目录，利用正则表达式提取年份、月份与核心业务词，并在重命名前进行冲突防范检查：

# -*- coding: utf-8 -*-
from pathlib import Path
import re

def batch_rename_excel_files(target_dir: str, dry_run: bool = True):
    """
    批量规范化重命名 Excel 文件
    :param target_dir: 存放 Excel 文件的目标文件夹路径
    :param dry_run: 是否为模拟试运行模式。默认 True 仅打印映射关系，不修改实际文件
    """
    folder = Path(target_dir)
    if not folder.exists() or not folder.is_dir():
        raise FileNotFoundError(f"指定的目录不存在: {target_dir}")

    # 检索所有 xlsx 文件，并过滤 Office 打开时产生的 ~$ 开头隐藏临时文件
    excel_files = [f for f in folder.glob("*.xlsx") if not f.name.startswith("~$")]
    
    print(f"[*] 共扫描到 {len(excel_files)} 个待处理 Excel 文件 (模式: {'Dry-Run 模拟模式' if dry_run else '正式重命名模式'})")
    
    rename_plan = []
    
    for file_path in excel_files:
        original_name = file_path.stem  # 不含后缀的文件名
        
        # 匹配示例规则：提取可能存在的年份、月份以及核心名称
        # 能够解析例如 "1月销售数据(副本)", "2024-02_华东_最终版", "3月报表_待合并"
        month_match = re.search(r"(\d{1,2})月|(\d{4})[-_](\d{1,2})", original_name)
        
        if month_match:
            if month_match.group(1):
                # 类似 "1月销售数据(副本)"
                year_str = "2024"
                month_num = int(month_match.group(1))
            else:
                # 类似 "2024-02_华东"
                year_str = month_match.group(2)
                month_num = int(month_match.group(3))
                
            formatted_date = f"{year_str}{month_num:02d}"
            clean_name = f"{formatted_date}_销售日报.xlsx"
        else:
            # 未能识别规则的文件，跳过改名以策安全
            print(f"[!] 无法识别命名规则，跳过处理: {file_path.name}")
            continue

        target_path = file_path.with_name(clean_name)
        
        if file_path == target_path:
            continue
            
        rename_plan.append((file_path, target_path))

    # 执行安全检查与输出预览
    for old_p, new_p in rename_plan:
        if new_p.exists():
            print(f"[警告] 目标路径已存在同名文件，已终止该项以防覆盖: {new_p.name}")
            continue

        if dry_run:
            print(f"[预览映射] {old_p.name}  ===>  {new_p.name}")
        else:
            old_p.rename(new_p)
            print(f"[成功重命名] {old_p.name}  ===>  {new_p.name}")

if __name__ == "__main__":
    # 第一次运行建议使用 dry_run=True 进行预检
    # 确认终端输出符合预期后，将 dry_run 设置为 False 执行落盘
    batch_rename_excel_files("raw_reports", dry_run=True)

三、任务二：多表格结构解析与合并原理

在批量更名使得文件名恢复秩序后，接下来需要解决正文内容合并的问题。传统手工复制粘贴最容易踩的坑有三个：

1. 列顺序漂移：比如分店 A 的表头顺序是“日期、门店、销售额”，分店 B 的表头顺序却是“门店、日期、销售额”。手工合并若直接按列位复制，会导致分店 B 的数据错位填入错误列；
2. 非关键列偶发增减：例如分店 C 在某月新增了一列“促销备注”，手工合并若不仔细看，很容易将备注数据误粘至其他数字列中；
3. 数据可追溯性丢失：一旦把成百上千行数据全部汇合在同一张大表里，若后续发现某行数据存在异常，人工很难判断这行数据究竟来自哪一个原始报表。

使用 Python 的 pandas 库进行纵向拼接（pd.concat）正是解决这三类痛点的最佳手段。pandas 在执行 pd.concat(axis=0) 时，并不依赖每列数据在物理表格中的排列次序，而是以列名（Column Name）作为哈希键进行精确对齐。如果遇到某张子表多出的列，pandas 会自动在合并总表中拓展该列，并在缺少该列的子表对应行中填入缺失值标记 NaN，确保每一列数据绝对安全、互不污染。

四、任务三：编写多表格自动化合并脚本

在明确了合并原理后，我们可以编写一个具备容错能力的合并处理脚本。一个可靠的工业级脚本，应当在循环遍历文件时包含以下几个关键设计：

• 过滤临时锁文件：当某位同事在局域网内打开某个 Excel 文件时，Office 会在同目录下生成名为 ~$xxx.xlsx 的隐藏临时锁定文件。这类文件不能作为常规 Excel 解析，必须显式跳过；
• 指定表索引：各部门对 Sheet 页的命名往往随意（有的叫“Sheet1”，有的叫“1月数据”，有的叫“工作表”），显式使用 sheet_name=0 读取首张工作表，能够避免因 Sheet 命名不一致引发的 KeyError；
• 强制数据类型（dtype）：像客户订单号、工号、身份证号等长字符串数字，如果不显式指定数据类型，pandas 可能会将其误推断为浮点型或整型，导致前导零丢失或超长数字被截断为科学记数法；
• 注入来源追踪列：在读取每个 DataFrame 后，主动添加一列 df["来源文件"] = file.name，合并后总表即可保留完整的数据溯源线索。

下面是实现多表格自动化合并的完整代码：

# -*- coding: utf-8 -*-
from pathlib import Path
import pandas as pd

def merge_excel_files(source_dir: str, output_file: str):
    """
    遍历指定目录中的所有 Excel 文件，按列名自动对齐并合并为一个总表
    :param source_dir: 包含待合并 Excel 文件的目录
    :param output_file: 导出的合并总表文件路径
    """
    folder = Path(source_dir)
    if not folder.exists():
        raise FileNotFoundError(f"目录不存在: {source_dir}")

    # 严格排除隐藏临时文件与输出目标本身（防止重复读取上一次合并的结果）
    output_target = Path(output_file).resolve()
    valid_files = [
        f for f in folder.glob("*.xlsx")
        if not f.name.startswith("~$") and f.resolve() != output_target
    ]

    if not valid_files:
        print("[!] 未找到任何有效的待合并 Excel 文件。")
        return

    print(f"[*] 找到 {len(valid_files)} 个待合并工作簿，开始逐一解析...")

    data_frames = []
    total_raw_rows = 0

    for file_path in valid_files:
        try:
            # sheet_name=0 读取第一个工作表
            # dtype 指定特定业务列为文本类型，防止前导零丢失
            df = pd.read_excel(
                file_path,
                sheet_name=0,
                dtype={"订单号": str, "工号": str, "电话": str}
            )

            # 空表防护检测
            if df.empty:
                print(f"[提示] 文件 {file_path.name} 为空表，已跳过。")
                continue

            # 动态注入数据来源列
            df["来源文件"] = file_path.name
            
            data_frames.append(df)
            total_raw_rows += len(df)
            print(f"  [+] 已加载: {file_path.name} (共 {len(df)} 行数据)")

        except Exception as err:
            print(f"[错误] 读取文件 {file_path.name} 失败: {err}")

    if not data_frames:
        print("[!] 无有效数据可供合并。")
        return

    # 纵向合并所有 DataFrame
    # ignore_index=True 重新从 0 生成连续行号
    # sort=False 保持列出现的原始顺序，不按字母重新排序列名
    print("[*] 正在执行跨表列名对齐与数据聚合...")
    combined_df = pd.concat(data_frames, ignore_index=True, sort=False)

    # 导出到总表，不保留 DataFrame 自带的数字索引列
    combined_df.to_excel(output_file, index=False, engine="openpyxl")
    
    print(f"[✓] 汇总完成！")
    print(f"    - 合并有效文件数: {len(data_frames)} 个")
    print(f"    - 原始总行数: {total_raw_rows} 行")
    print(f"    - 合并后总行数: {len(combined_df)} 行")
    print(f"    - 总列数: {len(combined_df.columns)} 列")
    print(f"    - 输出目标文件: {output_file}")

if __name__ == "__main__":
    merge_excel_files(
        source_dir="raw_reports",
        output_file="raw_reports/全年度销售汇总_总表.xlsx"
    )

五、进阶处理与高频避坑指南

在批量处理数以百计的真实业务报表时，极少会遇到“所有表格格式一模一样”的理想状况。根据在实际生产中的常见异常，以下三项关键配置是保障脚本长期平稳运行的护城河：

1. 路径拼接严禁使用字符串加号

在 Windows 与 macOS/Linux 跨平台环境中，路径分隔符分别是反斜杠（\）与正斜杠（/）。直接使用 dir_name + "/" + file_name 经常会引发由于转义字符（如 、 ）导致的找不到文件异常。务必统一采用 pathlib.Path 的路径重载运算符（如 Path(folder) / file_name），由 Python 底层根据当前操作系统自动匹配标准路径分割规范。

2. 敏感数字列显式指定 dtype 保护精度

Excel 在存储纯数字时，默认会尝试转换为双精度浮点数。这导致类似于员工工号 004521 被自动剥除开头的零变成 4521；18位身份证号或电商平台长订单号由于超出 15 位有效数字精度限制，后三位直接被截断置零（例如 520102199805123456 变成了 520102199805123000）。在调用 pd.read_excel 时，必须通过 dtype={"订单号": str} 显式指定识别为字符串类型，才能彻底保全原始数据的准确性。

3. 空格与全半角字符清洗

由于 pd.concat 依据列名进行字符串哈希匹配，某些子表的表头文字如果在输入时不小心带有前导或尾随空格（例如“销售额 ”与“销售额”），pandas 会认为这是两个截然不同的字段，从而在合并后拆分成两列展示。如果业务报表规范性较弱，可以在读取各表后添加一行列名去空格清洗代码：

# 自动剔除所有列名首尾的不可见空格与换行符
df.columns = df.columns.str.strip()

将文件批量重命名与跨表自动合并这两个环节以规范化流程固化为 Python 脚本后，原本耗费大半天且极易出错的 Excel 汇总整理工作，便可以被压缩在几秒内稳定完成。通过标准库 pathlib 建立规范的命名与防冲突预检机制，再配合 pandas 强大的基于列名对齐能力，即可在兼顾安全与速度的前提下，彻底告别低效的人工机械搬运。