使用XlsxWriter为Pandas DataFrame设置整列条件格式及差值高亮

刚好我之前也处理过类似的Excel格式需求，给你详细拆解这两个问题的解决方案：

XlsxWriter本身就支持直接引用整列，不用非得写死具体的行号范围（比如B3:K12这种）。你只需要用列字母加冒号的格式来指定整列，比如B:B代表整个B列，D:D代表整个D列。

不过要注意一个细节：如果你DataFrame的第一行是表头，整列引用会把表头也包含进去。如果不想格式化表头，可以用B2:B这种写法，代表从B列第2行开始到最后一行的所有单元格。

给你个完整的代码示例：

import pandas as pd

# 创建示例DataFrame
data = {
    'A': ['项目1', '项目2', '项目3', '项目4'],
    'B': [100, 120, 90, 150],
    'C': [90, 110, 100, 140]
}
df = pd.DataFrame(data)

# 初始化ExcelWriter，指定引擎为XlsxWriter
with pd.ExcelWriter('output.xlsx', engine='xlsxwriter') as writer:
    df.to_excel(writer, sheet_name='Sheet1', index=False)
    worksheet = writer.sheets['Sheet1']
    
    # 为整个B列应用条件格式（包含表头）
    format1 = writer.book.add_format({'bg_color': '#FFC7CE'})
    worksheet.conditional_format('B:B', {
        'type': 'cell',
        'criteria': '>',
        'value': 100,
        'format': format1
    })
    
    # 为C列从第2行开始应用条件格式（跳过表头）
    format2 = writer.book.add_format({'bg_color': '#C6EFCE'})
    worksheet.conditional_format('C2:C', {
        'type': 'cell',
        'criteria': '<',
        'value': 100,
        'format': format2
    })

这里首先要明确“差值超过15%”的计算逻辑——通常是指相对差值，也就是两个值的差的绝对值，除以基准值（比如以C列为基准）大于15%。对应的Excel公式应该是：
=ABS(B1-C1)/C1>0.15

为什么用B1和C1？因为条件格式的公式是相对于你应用范围的第一个单元格的，当你把格式应用到B列时，XlsxWriter会自动把公式里的行号对应到每一行。

如果你的基准是B列，公式就改成=ABS(B1-C1)/B1>0.15，根据你的实际需求调整就行。

下面是实现这个需求的代码示例：

import pandas as pd

data = {
    'A': ['项目1', '项目2', '项目3', '项目4'],
    'B': [100, 130, 90, 160],
    'C': [90, 110, 100, 140]
}
df = pd.DataFrame(data)

with pd.ExcelWriter('output.xlsx', engine='xlsxwriter') as writer:
    df.to_excel(writer, sheet_name='Sheet1', index=False)
    worksheet = writer.sheets['Sheet1']
    
    # 创建红色字体格式
    red_format = writer.book.add_format({'font_color': '#FF0000'})
    
    # 应用条件格式到B列（从第2行开始，跳过表头）
    worksheet.conditional_format('B2:B', {
        'type': 'formula',
        'criteria': '=ABS(B1-C1)/C1>0.15',  # 以C列为基准的相对差值超过15%
        'format': red_format
    })

这里要注意：公式里的行号1对应你应用范围的第一行（也就是B2），因为条件格式的公式是相对引用，XlsxWriter会自动帮你把每一行的公式对应成B2-C2、B3-C3等等，不用手动修改行号。

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