Python使用pandas/openpyxl比对不同行数Excel并新增校验列

DevOps 小满

2026-8-26

需求说明

待校验的两个Excel表均包含Product ID、Color、Brand三列：
- 待比对表：同一Product ID可对应多条数据记录，存在部分与标准值不符的异常条目
- 参考表：每个Product ID唯一对应1条正确的Color、Brand标准记录，仅3行数据
目标输出：在待比对文件中新增Validation列，逐行匹配对应Product ID的参考标准值，若该行Color、Brand与标准值完全一致则标记Ok，否则标记Not Ok
现存问题：前期基于pandas、openpyxl库编写嵌套openpyxl循环比对Color列时，因两个文件行数不一致、各Product ID对应的待比对记录数不固定，循环计数器逻辑无法适配，目前仅能打印比对结果，无法实现待比对文件新增列存储校验结果的最终需求。

原有代码缺陷

原有尝试编写的代码如下：

for i in range(2, ref.max_row+1):  
   cell_obj_1 = ref.cell(row=i, column=1)
   for n range(2, Compare_file.max_row+1):
     cell_obj_2 = Compare_file.cell(row=j, column=1)
        while(cell_obj_1.value == cell_obj_2.value):
            if(Compare_file.cell(row=J+1, column=2).value==(ref.cell(row=I+1, column=2)).value):
                print('Ok')
            else:
                print('Not Ok')

其中ref为参考表对应的工作簿对象，Compare_file为待比对表对应的工作簿对象，代码存在以下问题：

变量定义混乱：内层循环声明的循环变量为n，实际取值时使用了未定义的j，同时混用了大写J/I和小写i/n，变量名拼写、大小写不匹配会直接触发运行错误
逻辑死循环：使用while做值相等判断时，循环内部没有递增行号的逻辑，匹配到相等的Product ID后会一直卡在当前判断分支无法退出
校验规则不全：仅判断了Color列的一致性，未同步校验Brand字段，不符合“两个字段完全一致才标记Ok”的要求
缺少写入逻辑：仅做了结果打印，没有将校验结果写入待比对表新增列的相关代码

实现方案

优先使用pandas做关联匹配实现，无需手动维护循环计数器，逻辑简单且运行效率更高，代码如下：

import pandas as pd

# 读取两个表格，将路径替换为本地实际文件路径
compare_df = pd.read_excel("待比对文件.xlsx")
ref_df = pd.read_excel("参考文件.xlsx")

# 重命名参考表的校验字段，避免合并后和待比对表字段重名
ref_df = ref_df.rename(columns={"Color": "std_Color", "Brand": "std_Brand"})

# 按Product ID关联，将标准值匹配到待比对表的每一行
merged_res = compare_df.merge(ref_df, on="Product ID", how="left")

# 逐行判断两个字段是否完全匹配，生成Validation列
merged_res["Validation"] = merged_res.apply(
    lambda row: "Ok" if (row["Color"] == row["std_Color"] and row["Brand"] == row["std_Brand"]) else "Not Ok",
    axis=1
)

# 删除临时关联的标准值列，保留原待比对表全部字段+新增的校验结果列
final_df = merged_res.drop(columns=["std_Color", "std_Brand"])

# 将结果写入新的Excel文件，index=False表示不导出pandas自动生成的行号列
final_df.to_excel("校验完成_待比对文件.xlsx", index=False)

如果需要纯openpyxl实现，不要写双层嵌套循环，先把参考表的标准值存为字典，遍历待比对表时直接查字典匹配即可，天然适配不同Product ID对应记录数不固定的场景，代码如下：

from openpyxl import load_workbook

# 加载两个工作簿，替换为本地实际文件路径
ref_wb = load_workbook("参考文件.xlsx")
ref_ws = ref_wb.active
compare_wb = load_workbook("待比对文件.xlsx")
compare_ws = compare_wb.active

# 读取参考表数据，存为字典：key为Product ID，value为(标准Color, 标准Brand)
std_map = {}
for row_idx in range(2, ref_ws.max_row + 1):
    pid = ref_ws.cell(row=row_idx, column=1).value
    std_color = ref_ws.cell(row=row_idx, column=2).value
    std_brand = ref_ws.cell(row=row_idx, column=3).value
    std_map[pid] = (std_color, std_brand)

# 给待比对表新增Validation列表头（第4列）
compare_ws.cell(row=1, column=4, value="Validation")

# 遍历待比对表逐行校验
for row_idx in range(2, compare_ws.max_row + 1):
    pid = compare_ws.cell(row=row_idx, column=1).value
    cur_color = compare_ws.cell(row=row_idx, column=2).value
    cur_brand = compare_ws.cell(row=row_idx, column=3).value
    # 从字典取对应Product ID的标准值
    target_color, target_brand = std_map.get(pid, (None, None))
    validate_res = "Ok" if (cur_color == target_color and cur_brand == target_brand) else "Not Ok"
    compare_ws.cell(row=row_idx, column=4, value=validate_res)

# 保存修改后的文件
compare_wb.save("校验完成_待比对文件_openpyxl版.xlsx")

内容的提问来源于stack exchange，提问作者Naiara Tabanez