如何用Pandas对比两个Excel文件并高亮差异单元格后保存

问题背景 #

现有两个Excel文件，内容如下：

文件1 #

文件2 #

需要实现：对比两个文件，高亮所有差异单元格（包括文件2新增的整行），并保存为新的Excel文件。

实现步骤 #

1. 安装依赖 #

需要pandas处理数据，openpyxl用于写入带格式的Excel文件：

pip install pandas openpyxl

2. 完整代码实现 #

import pandas as pd
from openpyxl.styles import PatternFill

# 读取两个Excel文件（替换为你的文件路径）
df1 = pd.read_excel("文件1.xlsx")
df2 = pd.read_excel("文件2.xlsx")

# 对齐两个DataFrame的行和列，缺失值填充为NaN，确保对比维度一致
aligned_df1, aligned_df2 = df1.align(df2, join="outer", axis=0, fill_value=pd.NA)

# 标记差异位置
# 1. 对应单元格值不同的位置
diff_cells = aligned_df1 != aligned_df2
# 2. 文件2新增的行（df1中不存在的行）
new_rows = aligned_df1.isna().all(axis=1)

# 写入Excel并设置高亮格式
with pd.ExcelWriter("对比结果.xlsx", engine="openpyxl") as writer:
    # 先写入文件2的原始内容
    df2.to_excel(writer, index=False, sheet_name="差异对比")
    workbook = writer.book
    worksheet = writer.sheets["差异对比"]
    
    # 定义高亮格式（黄色背景，可自定义十六进制颜色）
    highlight_fill = PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid")
    
    # 遍历所有数据单元格，应用高亮
    # Excel行从1开始，表头占第1行，数据从第2行开始；列从1开始
    for row_idx in range(len(df2)):
        excel_row = row_idx + 2
        for col_idx in range(len(df2.columns)):
            excel_col = col_idx + 1
            # 检查是否为差异单元格或新增行
            if new_rows.iloc[row_idx] or diff_cells.iloc[row_idx, col_idx]:
                worksheet.cell(row=excel_row, column=excel_col).fill = highlight_fill

代码说明 #

• 对齐DataFrame：通过align方法统一两个文件的行和列维度，缺失的行/列填充为NaN，确保每个单元格能精准对比。
• 标记差异：
  • diff_cells：布尔矩阵，标记对应位置值不同的单元格。
  • new_rows：布尔数组，标记文件2独有的行（整行在df1中无匹配）。
• 高亮格式：使用openpyxl的PatternFill设置背景色，可自行修改颜色值适配需求。
• 写入逻辑：先写入文件2的完整内容，再遍历单元格添加高亮，最终保留文件2的结构同时突出差异。

内容的提问来源于stack exchange，提问作者Manu Mayur K