xlsxwriter设置含XLOOKUP的条件格式需手动应用才生效

• 两张结构完全一致的Excel工作表分别存储历史周期、当前周期数据，以KEY列作为行数据唯一匹配标识，列包含Col_1、Col_2、KEY、Col_3及其他扩展列，示例结构如下：

• 需求：逐列校验同一KEY对应的历史值与当前值，存在差异时将当前工作表中对应差异单元格填充指定背景色，校验覆盖所有业务列。
• 初始实现：因KEY列不在首列，采用XLOOKUP编写匹配规则，通过for循环逐列批量应用条件格式（示例中KEY列为C列），代码如下：

dark_blue = writer.book.add_format({'bg_color': '#3A67B8'})
old_sheet = "\'" + "old_" + "sheet_name" + "\'"
for col in range(last_col):
    col_name = xl_col_to_name(col) 
    if col_name in unformatted_cols: # 跳过不需要设置格式的列
        continue
    else:
        apply_range = '{0}1:{0}1048576'.format(col_name)
        formula = "XLOOKUP(C1, {1}!C1:C1048576, {1}!{0}1:{0}1048576) <> XLOOKUP(C1, C1:C1048576, {0}1:{0}1048576)".format(col_name, old_sheet)
        active_sheet.conditional_format(apply_range, {'type': 'formula',
                                                      'criteria': formula,
                                                      'format': dark_blue})

• 生成的Excel文件打开后，上述批量设置的条件格式默认不生效；
• 手动进入「条件格式 > 管理规则 > 编辑规则」，不做任何修改直接点击确定应用，规则就可以正常触发；
• 其他逻辑更简单的条件格式规则均正常生效，无该问题。
• 公式存在自动转换情况：
  • Python端首轮循环写入的公式：

  =XLOOKUP(C1, 'old_sheet_name'!C1:C1048576, 'old_sheet_name'!A1:A1048576) <> XLOOKUP(C1, C1:C1048576, A1:A1048576)
  

  • Excel中查看同列规则的实际公式：

  =XLOOKUP(C1, 'old_sheet_name'!C:C, 'old_sheet_name'!A:A) <> XLOOKUP(C1, C:C, A:A)
  

问题核心原因是写入条件格式公式时，查找范围未加绝对引用符号$，大区域引用触发Excel隐式整列转换后，引用基准随单元格偏移导致初始计算失效；手动编辑规则确认时Excel会自动修正引用偏移，规则才恢复正常。
按以下两点修改即可：

1. 公式中所有固定查找范围加$设为绝对引用，仅和当前行绑定的基准单元格用相对引用，避免应用到整列时引用范围偏移；
2. 删掉冗余的第二个XLOOKUP——该段逻辑本质是取当前单元格的值，直接引用当前单元格即可，既减少公式解析出错概率，也能提升Excel计算性能。
   修改后可正常运行的代码如下：

dark_blue = writer.book.add_format({'bg_color': '#3A67B8'})
old_sheet = "'old_sheet_name'"
for col in range(last_col):
    col_name = xl_col_to_name(col) 
    if col_name in unformatted_cols:
        continue
    # 从第2行开始应用规则，跳过表头
    apply_range = f'{col_name}2:{col_name}1048576'
    # 查找范围用绝对引用，当前行匹配用相对引用
    formula = f'XLOOKUP($C2, {old_sheet}!$C:$C, {old_sheet}!${col_name}:${col_name}) <> ${col_name}2'
    active_sheet.conditional_format(apply_range, {
        'type': 'formula',
        'criteria': formula,
        'format': dark_blue
    })

重新生成的Excel文件打开后条件格式会直接生效，无需手动编辑规则。

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