Python读取多格式Excel并实现跨表匹配合并的完整示例

目录

• 一、需求拆解
• 二、通用 Excel 读取函数（支持 XLS / XLSX）
• 三、跨表匹配的整体流程
• 四、完整示例代码（已脱敏）
• 五、关键技术点解释
• ① 通用读取函数封装的意义
• ② 使用set_index做匹配
• ③ 保持原表字段顺序不变
• 六、效果示例
• 七、总结

在数据处理中，经常会遇到这样一个需求：

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

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

• 主表是 XLS，信息表是 XLSX
• 两个表的表头不在第一行
• 信息表第一列是序号，真正数据在第二列开始
• 合并后需要把新字段插入到指定位置，而不是简单拼在末尾

本文就分享一次真实项目中的解决方案： 使用 Python + Pandas + xlrd + openpyxl，实现多格式 Excel 读取 + 灵活表头处理 + 按姓名匹配合并 + 自定义列顺序输出。

最终目标是：

1. 无论是 XLS 还是 XLSX，都能读取
2. 支持跳过表头行
3. 支持跳过第一列（常见为序号）
4. 按姓名进行匹配
5. 把匹配结果插入主表的指定列后（例如 I 列）
6. 输出一个新的 XLSX 文件

为了做到这点，我们需要写一个“通用 Excel 读取函数”。

核心难点在于：

• 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

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


# ===== 配置区（示例） ===== #
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 "姓名" not in df_main.columns or "姓名" not in 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: None for 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 只需修改文件名
• 结构更清晰、利于维护

df_info.set_index("姓名")

这样查找效率极高，相当于字典查询。

很多业务表格是“固定模板”，不能随意打乱列。 这里通过自定义 new_cols 保证了最终顺序完全可控。

最终输出的 Excel 将会：

• 保留主表原有字段顺序
• 在指定列（如 I 列）后插入信息表的字段
• 按姓名逐行匹配
• 如果信息表中没有该姓名，填充为空白

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

通过 Python 的数据处理能力，我们可以轻松实现：

• 跨格式 Excel 读取（XLS/XLSX）
• 灵活处理表头位置与列结构
• 按姓名精准匹配信息表
• 保持主表字段顺序
• 自动生成合并后的最终数据

在实际业务场景（学籍管理、人事数据、客户数据等）中，这类脚本非常实用，大幅提升效率。

如果你有更复杂的需求，比如：

• 多字段匹配
• 多表联查
• 模糊匹配（拼音/首字母）
• 学生重复名自动识别
• 批量处理多个文件夹

以上就是Python读取多格式Excel并实现跨表匹配合并的完整示例的详细内容，更多关于Python读取多格式Excel并合并的资料请关注风君子博客其它相关文章！

您可能感兴趣的文章:

• python 实现读取一个excel多个sheet表并合并的方法
• Python实现读取、修改和计算 Excel 公式的示例详解
• Python xlrd实现从读取Excel到高效数据提取的全面指南
• python读取Excel大文件的四种方法与优化