基于Python与Robot Framework对比两Excel文件的布局结构差异

核心校验范围 #

• 工作表层级：新增、删除的工作表
• 维度层级：各工作表总行数、总列数差异
• 表头层级：灰色填充标识的行表头、列表头文本差异
• 结构层级：数据区域新增、删除的行/列

完整Python实现代码 #

先安装依赖：pip install openpyxl

from openpyxl import load_workbook, Workbook

def is_gray_fill(cell):
    """判断单元格是否为灰色填充（表头标识）"""
    fill = cell.fill
    if not fill or fill.patternType is None:
        return False
    # 可根据实际模板的灰色RGB值扩展该集合
    gray_rgb_set = {"00D9D9D9", "00C0C0C0", "00E7E6E6", "FFD9D9D9", "FFC0C0C0", "FFE7E6E6"}
    return fill.fgColor.rgb in gray_rgb_set

def compare_excel_layout(base_path, target_path, diff_save_path):
    diff_list = []
    # 加载工作簿时保留样式读取权限
    wb_base = load_workbook(base_path, data_only=False)
    wb_target = load_workbook(target_path, data_only=False)

    base_sheets = set(wb_base.sheetnames)
    target_sheets = set(wb_target.sheetnames)

    # 校验工作表增减
    deleted_sheets = base_sheets - target_sheets
    for sheet in deleted_sheets:
        diff_list.append(["工作表级", sheet, "-", "存在", "已删除"])
    added_sheets = target_sheets - base_sheets
    for sheet in added_sheets:
        diff_list.append(["工作表级", sheet, "-", "不存在", "新增"])
    
    # 遍历共有工作表做结构校验
    common_sheets = base_sheets & target_sheets
    for sheet_name in common_sheets:
        sheet_base = wb_base[sheet_name]
        sheet_target = wb_target[sheet_name]

        # 校验总行数、总列数
        base_max_row = sheet_base.max_row
        target_max_row = sheet_target.max_row
        if base_max_row != target_max_row:
            diff_list.append(["维度级", sheet_name, "总行数", base_max_row, target_max_row])
        base_max_col = sheet_base.max_column
        target_max_col = sheet_target.max_column
        if base_max_col != target_max_col:
            diff_list.append(["维度级", sheet_name, "总列数", base_max_col, target_max_col])
        
        # 识别行表头范围：顶部连续存在灰色填充单元格的行
        header_rows = []
        for row in range(1, min(base_max_row, target_max_row) + 1):
            has_gray = False
            for col in range(1, min(base_max_col, target_max_col) + 1):
                if is_gray_fill(sheet_base.cell(row, col)) or is_gray_fill(sheet_target.cell(row, col)):
                    has_gray = True
                    break
            if has_gray:
                header_rows.append(row)
            elif header_rows:
                break
        # 识别列表头范围：左侧连续存在灰色填充单元格的列
        header_cols = []
        for col in range(1, min(base_max_col, target_max_col) + 1):
            has_gray = False
            for row in range(1, min(base_max_row, target_max_row) + 1):
                if is_gray_fill(sheet_base.cell(row, col)) or is_gray_fill(sheet_target.cell(row, col)):
                    has_gray = True
                    break
            if has_gray:
                header_cols.append(col)
            elif header_cols:
                break
        
        # 校验行表头、列表头文本
        for row in header_rows:
            for col in range(1, max(base_max_col, target_max_col) + 1):
                val_base = sheet_base.cell(row, col).value
                val_target = sheet_target.cell(row, col).value
                if val_base != val_target:
                    pos = f"第{row}行第{col}列（行表头）"
                    diff_list.append(["表头级", sheet_name, pos, val_base, val_target])
        for col in header_cols:
            for row in range(1, max(base_max_row, target_max_row) + 1):
                if row in header_rows:
                    continue
                val_base = sheet_base.cell(row, col).value
                val_target = sheet_target.cell(row, col).value
                if val_base != val_target:
                    pos = f"第{row}行第{col}列（列表头）"
                    diff_list.append(["表头级", sheet_name, pos, val_base, val_target])
        
        # 基于表头锚点校验行、列增减
        if header_rows and header_cols:
            anchor_row = max(header_rows)
            anchor_col = max(header_cols)
            base_data_row_count = base_max_row - anchor_row
            target_data_row_count = target_max_row - anchor_row
            if base_data_row_count > target_data_row_count:
                diff_list.append(["结构级", sheet_name, "数据行", f"{base_data_row_count}行", f"{target_data_row_count}行（删除{base_data_row_count - target_data_row_count}行）"])
            elif base_data_row_count < target_data_row_count:
                diff_list.append(["结构级", sheet_name, "数据行", f"{base_data_row_count}行", f"{target_data_row_count}行（新增{target_data_row_count - base_data_row_count}行）"])
            
            base_data_col_count = base_max_col - anchor_col
            target_data_col_count = target_max_col - anchor_col
            if base_data_col_count > target_data_col_count:
                diff_list.append(["结构级", sheet_name, "数据列", f"{base_data_col_count}列", f"{target_data_col_count}列（删除{base_data_col_count - target_data_col_count}列）"])
            elif base_data_col_count < target_data_col_count:
                diff_list.append(["结构级", sheet_name, "数据列", f"{base_data_col_count}列", f"{target_data_col_count}列（新增{target_data_col_count - base_data_col_count}列）"])

    # 导出差异到独立Excel
    if diff_list:
        wb_diff = Workbook()
        ws_diff = wb_diff.active
        ws_diff.title = "布局差异明细"
        ws_diff.append(["差异级别", "所属工作表", "位置", "基准文件值", "待校验文件值"])
        for item in diff_list:
            ws_diff.append(item)
        wb_diff.save(diff_save_path)
    return diff_list

if __name__ == "__main__":
    diffs = compare_excel_layout(
        base_path="AnnexureTemplates/Annex 8 (Large exposures).xlsx",
        target_path="CycleTemplates/FinalValidated.xlsx",
        diff_save_path="Excel布局差异结果.xlsx"
    )
    if diffs:
        print(f"检测到{len(diffs)}处布局差异，已导出到Excel布局差异结果.xlsx")
        for d in diffs:
            print(f"[{d[0]}] {d[1]} - {d[2]}: 基准值={d[3]}，待校验值={d[4]}")
    else:
        print("两份Excel布局完全一致，无差异")

Robot Framework 集成方式 #

将上述Python代码保存为ExcelLayoutCompare.py，作为自定义库直接导入RF即可，示例用例如下：

*** Settings ***
Library    ExcelLayoutCompare.py
Library    BuiltIn

*** Test Cases ***
两份Excel模板布局一致性校验
    [Documentation]    对比基准模板和待校验模板的布局差异，结果导出为独立Excel
    ${diff_list}=    Compare Excel Layout
    ...    base_path=AnnexureTemplates/Annex 8 (Large exposures).xlsx
    ...    target_path=CycleTemplates/FinalValidated.xlsx
    ...    diff_save_path=布局校验结果.xlsx
    Run Keyword If    len($diff_list)>0    Fail    检测到${len($diff_list)}处布局差异，请查看布局校验结果.xlsx
    Log    布局校验通过，无差异

适配说明 #

代码默认匹配常用灰色填充值作为表头标识，如果模板使用的灰色不在默认匹配范围内，可先运行以下代码读取实际表头单元格的色值，补充到gray_rgb_set集合内即可：

from openpyxl import load_workbook
wb = load_workbook("你的模板文件路径.xlsx")
# 替换为模板内灰色表头单元格的行、列号
target_cell = wb["工作表名称"].cell(row=1, column=1)
print("表头单元格填充色RGB：", target_cell.fill.fgColor.rgb)

内容的提问来源于stack exchange，提问作者Varun Singh