Python跨表合并多格式Excel的自动化脚本（精准匹配与列位控制）

凌晨两点半

发布时间：2026-10-10 | 3人浏览过

原创

在企业管理与数据分析场景中，经常需要将两份不同来源的Excel表格进行对齐：一份为主数据底表，另一份包含待补充的详细信息。实际操作时常常面临格式不统一、表头错位、首列为冗馀序号列等痛点，导致人工筛选效率低下且容易出错。本文分享一套经过项目验证的Python自动化方案，利用Pandas配合底层解析库，一键完成跨格式读取、智能跳过异常行列、按姓名精准匹配并自定义输出列序，彻底替代繁琐的手工拼接。

目标与核心难点

该脚本旨在实现以下数据处理目标：

同时兼容传统的 .xls 与新版 .xlsx 格式，避免因库不支持导致的读取失败。
允许程序自动跳过顶部的非数据行（如标题说明），并剔除第一列多余的流水号或序号。
通过“姓名”这一共同字段进行跨表数据透视，将信息表的关联内容匹配至主表对应行。
严格控制最终输出的Excel列布局，确保新增字段被定点插入到主表指定的物理位置之后，而不是盲目追加在末尾。

环境准备与依赖安装

由于不同版本的Python对旧版Office格式的支持发生了变化，本方案采用组合策略：pandas 负责高层数据框运算，openpyxl 专攻 .xlsx，而 xlrd 专门用于解析遗留的 .xls 文件。

前置警告： 在执行自动化写盘操作前，请务必手动备份原始的两份Excel工作簿，防止代码逻辑意外覆盖重要数据。

请在终端执行以下命令安装依赖：

pip install pandas openpyxl xlrd

自动化合并执行步骤

第1步：构建兼容双格式的通用读取器

传统 pandas.read_excel 在面对复杂排版或非标准结构的 .xls 文件时极易崩溃。为此，我们封装了一个具备路径嗅探能力的函数，根据后缀名自动切换引擎，并通过参数控制数据清洗阈值。

import pandas as pd
import xlrd
import openpyxl

def read_excel_any(path, sheet_name=None, skip_header_rows=0, skip_first_column=False):
    """
    支持 XLS/XLSX 的通用读取函数
    - skip_header_rows: 跳过前 N 行
    - skip_first_column: 是否跳过第一列（序号）
    """
    if path.lower().endswith(".xls"):
        book = xlrd.open_workbook(path)
        sheet = book.sheet_by_name(sheet_name) if sheet_name else book.sheet_by_index(0)

        data = []
        for r in range(sheet.nrows):
            if r < skip_header_rows:
                continue
            row = sheet.row_values(r)
            if skip_first_column:
                row = row[1:]
            data.append(row)

        df = pd.DataFrame(data[1:], columns=data[0])
        return df

    else:  # XLSX
        df = pd.read_excel(
            path,
            sheet_name=sheet_name,
            engine="openpyxl",
            skiprows=skip_header_rows
        )
        if skip_first_column:
            df = df.iloc[:, 1:]
        return df

第2步：加载数据源并校验基础结构

配置好实际的文件路径与Sheet名称后，调用上述函数提取核心数据集。在继续运算前，必须验证两张表是否共含有作为桥梁的“姓名”列，否则后续映射无法展开，此时会抛出明确的 ValueError 阻断执行。

# ===== 配置区（按需修改） ===== #
main_file = "主表.xls"
main_sheet = "数据表"

info_file = "信息表.xlsx"
info_sheet = "全部信息"

info_skip_header = 1       # 跳过信息表的第1行表头
info_skip_first_col = True # 去掉信息表的第一列序号
# ======================== #

df_main = read_excel_any(main_file, sheet_name=main_sheet)
df_info = read_excel_any(
    info_file,
    sheet_name=info_sheet,
    skip_header_rows=info_skip_header,
    skip_first_column=info_skip_first_col
)

if "姓名" not in df_main.columns or "姓名" not in df_info.columns:
    raise ValueError("两份表格必须都包含“姓名”列！")

第3步：建立索引并执行逐行匹配

放弃低效的循环比对，利用 set_index 将信息表转化为哈希索引结构。随后在主表中遍历每一行的姓名，向索引请求对应的完整数据字典。对于匹配不上的空白记录，自动填入 None 以避免程序中断。

# 设置信息表的索引，大幅提升查询速度
df_info_index = df_info.set_index("姓名")

matched_data = []
for name in df_main["姓名"]:
    if name in df_info_index.index:
        matched_data.append(df_info_index.loc[name].to_dict())
    else:
        # 不存在该姓名则填空，维持DataFrame结构稳定
        matched_data.append({col: None for col in df_info.columns if col != "姓名"})

df_match = pd.DataFrame(matched_data)

第4步：重组列序列定点插入结果

许多业务场景下的Excel模板具有严格的列宽限制与打印格式。通过将原始列名切片、拼接新字段名、再拼回原列名，我们可以精确控制输出表格的排版顺序。最后调用 to_excel 生成全新的报告。

# 插入到第 8 列之后（索引 8 对应第 9 个位置之后）
insert_pos = 8
cols_main = df_main.columns.tolist()
new_cols = cols_main[:insert_pos + 1] + df_match.columns.tolist() + cols_main[insert_pos + 1:]

df_out = pd.concat([df_main, df_match], axis=1)[new_cols]
df_out.to_excel("输出结果.xlsx", index=False)
print("合并成功，已生成输出结果.xlsx")