我们有两份 Excel：一份是主数据表，另一份是学生/员工/客户的完整信息表。需要按姓名匹配，把完整信息补充到主表中。

听起来简单，但实际操作中常会踩坑，比如：

• 主表是 XLS，信息表是 XLSX

• 两个表的表头不在第一行

• 信息表第一列是序号，真正数据在第二列开始

• 合并后需要把新字段插入到指定位置，而不是简单拼在末尾

本文就分享一次真实项目中的解决方案：

使用 Python + Pandas + xlrd + openpyxl，实现多格式 Excel 读取 + 灵活表头处理 + 按姓名匹配合并 + 自定义列顺序输出。

一、需求拆解

最终目标是：

1. 无论是 XLS 还是 XLSX，都能读取

2. 支持跳过表头行

3. 支持跳过第一列（常见为序号）

4. 按姓名进行匹配

5. 把匹配结果插入主表的指定列后（例如 I 列）

6. 输出一个新的 XLSX 文件为了做到这点，我们需要写一个“通用 Excel 读取函数”。

二、通用 Excel 读取函数（支持 XLS / XLSX）

核心难点在于：

• pandas 无法直接读取带旧格式 .xls 的合并单元格或特殊格式

• xlrd 只能读取 .xls（新版不支持 .xlsx）

所以策略是：

• xls → xlrd

• xlsx → pandas(openpyxl)

并手动实现跳过行、跳过列等功能。

三、跨表匹配的整体流程

整个数据处理逻辑如下：

1. 读取主表（XLS/XLSX 均可）

2. 读取信息表（可以从第二行是真表头）

3. 把信息表按“姓名”设置为索引

4. 循环主表每一行，根据姓名取对应信息

5. 把匹配结果拼成新的 DataFrame

6. 插入到主表指定列之后

7. 输出最终的合并表这个流程能适配各种不同的 Excel 格式，健壮性很高。

四、完整示例代码（已脱敏）

以下是通用示例代码，你可以直接复用：

import pandas as pd

import xlrd

import openpyxl

defread_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 inrange(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

# ===== 配置区（示例） ===== #

main_file = "主表.xls"

main_sheet = "数据表"

info_file = "信息表.xlsx"

info_sheet = "全部信息"

info_skip_header = 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"姓名"notin df_main.columns or"姓名"notin df_info.columns:

    raise ValueError("两份表格必须都包含“姓名”列！")

# 设置信息表的索引

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:

        # 不存在则填空

        matched_data.append({col: Nonefor col in df_info.columns if col != "姓名"})

df_match = pd.DataFrame(matched_data)

# 匹配内容插入到指定位置（示例：插入到第 8 列之后）

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)

五、关键技术点解释

① 通用读取函数封装的意义很多项目里主表和信息表格式不同、位置不同、列不同。

封装通用函数之后：

• 脚本可复用性强

• 更换 Excel 只需修改文件名

• 结构更清晰、利于维护

② 使用 set_index 做匹配df_info.set_index("姓名")这样查找效率极高，相当于字典查询。

③ 保持原表字段顺序不变很多业务表格是“固定模板”，不能随意打乱列。

这里通过自定义 new_cols 保证了最终顺序完全可控。

六、效果示例

最终输出的 Excel 将会：

• 保留主表原有字段顺序

• 在指定列（如 I 列）后插入信息表的字段

• 按姓名逐行匹配

• 如果信息表中没有该姓名，填充为空白

整个流程自动化，不需要人工筛选、复制、粘贴。

更多相关技术内容咨询欢迎前往并持续关注好学星城论坛了解详情。

想高效系统的学习Python编程语言，推荐大家关注一个微信公众号：Python编程学习圈。每天分享行业资讯、技术干货供大家阅读，关注即可免费领取整套Python入门到进阶的学习资料以及教程，感兴趣的小伙伴赶紧行动起来吧。