如何映射颜色名称与HEX值实现Excel条件格式？（pandas+xlsxwriter）

你的代码失效的核心原因是条件格式的文本匹配规则没写对：Excel中使用等于条件匹配文本时，需要将文本值用单引号包裹，否则会被当作公式解析。

以下是两种可行的实现方式：

方法一：基于xlsxwriter批量添加条件格式（推荐） #

先建立颜色名称与HEX值的映射字典，再循环为每个颜色添加对应的条件格式，代码简洁易维护：

import pandas as pd

df = pd.DataFrame({'ID': ['ID001', 'ID002', 'ID003', 'ID004', 'ID005', 'ID006'],
                   'Color': ['Red', 'Green', 'Blue', 'Green', 'Yellow', 'Purple']})

# 定义颜色映射字典（可根据需求调整HEX值）
color_map = {
    'Red': '#FF0000',
    'Green': '#00B050',
    'Blue': '#0070C0',
    'Yellow': '#FFC000',
    'Purple': '#7030A0'
}

with pd.ExcelWriter('TestColor_SpreadSheet.xlsx', engine='xlsxwriter') as writer:
    df.to_excel(writer, index=False, sheet_name='TestWorksheet')
    workbook = writer.book
    worksheet = writer.sheets['TestWorksheet']
    
    # 循环为每个颜色添加条件格式
    for color_name, hex_color in color_map.items():
        cell_format = workbook.add_format({'bg_color': hex_color})
        # 文本值必须用单引号包裹，确保Excel识别为文本匹配
        worksheet.conditional_format(
            f'B1:B{len(df)+1}',  # 动态计算单元格范围，适配数据行数变化
            {
                'type': 'cell',
                'criteria': '=',
                'value': f"'{color_name}'",
                'format': cell_format
            }
        )

方法二：直接为单元格设置格式（适合小数据集） #

如果数据量不大，也可以遍历DataFrame的每个单元格，直接指定背景色：

import pandas as pd

df = pd.DataFrame({'ID': ['ID001', 'ID002', 'ID003', 'ID004', 'ID005', 'ID006'],
                   'Color': ['Red', 'Green', 'Blue', 'Green', 'Yellow', 'Purple']})

color_map = {
    'Red': '#FF0000',
    'Green': '#00B050',
    'Blue': '#0070C0',
    'Yellow': '#FFC000',
    'Purple': '#7030A0'
}

with pd.ExcelWriter('TestColor_SpreadSheet.xlsx', engine='xlsxwriter') as writer:
    df.to_excel(writer, index=False, sheet_name='TestWorksheet')
    workbook = writer.book
    worksheet = writer.sheets['TestWorksheet']
    
    # 遍历Color列的每个单元格（Excel行号从1开始，表头占第1行）
    for row_num in range(len(df)):
        color_name = df.iloc[row_num]['Color']
        cell_format = workbook.add_format({'bg_color': color_map[color_name]})
        # 写入单元格时同时应用格式
        worksheet.write(row_num+1, 1, color_name, cell_format)

关键注意点 #

• 条件格式中，value参数必须用f"'{color_name}'"的形式：单引号包裹文本，避免Excel将其解析为公式。
• 动态计算单元格范围f'B1:B{len(df)+1}'可以适配数据行数变化，比固定写B1:B7更灵活。
• 颜色映射字典中的HEX值可根据需求调整，示例中使用了贴近Excel默认主题色的数值，效果更贴合目标样式。

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