如何在Python生成XLSX报表时，根据内容设置单元格条件格式？

完全可以实现，借助xlsxwriter的条件格式功能就能做到。你不需要在单元格值里加{bold}标记，直接匹配单元格的实际内容，给符合条件的单元格应用粗体格式即可。

原代码 #

import pandas as pd

df = pd.DataFrame({
    'aaa': [0, 0, 2, 3],
    'aaBaa': ["0","0","2","\{bold\}123"]
})

j_df = {}
j_df["dummy"] = df.copy()

if 1 == 1:
    writer = pd.ExcelWriter("dummy.xlsx", engine='xlsxwriter')
    for sheet_name, df_write in j_df.items():
        df_write.to_excel(writer, sheet_name=sheet_name, index=False)
        workbook  = writer.book
        worksheet = writer.sheets[sheet_name]
        (max_row, max_col) = df_write.shape
        worksheet.set_column(0,  max_col - 1, 12)
        worksheet.autofilter(0, 0, max_row, max_col - 1)
        worksheet.autofit()

        shadedFormat  = workbook.add_format({'bg_color': 'gray'})
        worksheet.conditional_format('B3:B5', {'format': shadedFormat})


        header_data = df_write.columns.to_list()
        header_format = workbook.add_format({'bold': True,
                                            'bottom': 2,
                                            'bg_color': '#F9DA04'})

        for col_num, data in enumerate(header_data):
            worksheet.write(0, col_num, data, header_format)
    writer.close()

修改后的代码（实现值为“123”的单元格粗体） #

import pandas as pd

# 去掉单元格值里的{bold}标记，直接存原始内容
df = pd.DataFrame({
    'aaa': [0, 0, 2, 3],
    'aaBaa': ["0","0","2","123"]
})

j_df = {}
j_df["dummy"] = df.copy()

writer = pd.ExcelWriter("dummy.xlsx", engine='xlsxwriter')
for sheet_name, df_write in j_df.items():
    df_write.to_excel(writer, sheet_name=sheet_name, index=False)
    workbook = writer.book
    worksheet = writer.sheets[sheet_name]
    max_row, max_col = df_write.shape
    
    worksheet.set_column(0, max_col - 1, 12)
    worksheet.autofilter(0, 0, max_row, max_col - 1)
    worksheet.autofit()

    shadedFormat = workbook.add_format({'bg_color': 'gray'})
    worksheet.conditional_format('B3:B5', {'format': shadedFormat})

    # 新增：创建粗体格式
    bold_format = workbook.add_format({'bold': True})
    # 应用条件格式：匹配值等于"123"的单元格，应用粗体
    # 范围设为整个数据区域（从第1行表头下的第2行开始，到最后一行最后一列）
    worksheet.conditional_format(1, 0, max_row, max_col - 1, {
        'type': 'cell',
        'criteria': 'equal to',
        'value': '"123"',
        'format': bold_format
    })

    header_data = df_write.columns.to_list()
    header_format = workbook.add_format({'bold': True,
                                        'bottom': 2,
                                        'bg_color': '#F9DA04'})

    for col_num, data in enumerate(header_data):
        worksheet.write(0, col_num, data, header_format)
writer.close()

关键改动说明 #

• 移除了单元格值中的{bold}标记，直接存储原始内容"123"
• 创建了粗体格式bold_format
• 使用conditional_format方法，指定规则为单元格值等于"123"，并将该格式应用到整个数据区域（参数1, 0, max_row, max_col - 1对应从第2行第1列到最后一行最后一列的范围）

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