XLSXWRITER PANDAS基于值更改第一行颜色

Question

我正在创建一个具有以下代码的Excel表：

writer = pd.ExcelWriter('data.xlsx', engine='xlsxwriter')
data = pd.DataFrame(data={'val1': [True, False], 
                          'val2': [False, True], 
                          'val3': [True, True], 
                          'val4': [True, False], 
                          'val5': [True, True],
                          'val6': [False, False]},
                           index=pd.Series([1, 2], name='index'))
data.to_excel(writer, index=False, sheet_name='Sheet1')
workbook  = writer.book
ws = writer.sheets['Sheet1']
writer.close()
writer.save()

我想使用以下字典来根据值更改列背景颜色：

colors = {'val3': '#FFFF00',
          'val4': '#DCE6F1',
          'val6': '#FDE9D9'}

Answer 1

到目前为止，我唯一发现的解决方案是使用第二个循环，尝试：

writer = pd.ExcelWriter('data.xlsx', engine='xlsxwriter')
data = pd.DataFrame(data={'val1': [True, False], 
                          'val2': [False, True], 
                          'val3': [True, True], 
                          'val4': [True, False], 
                          'val5': [True, True],
                          'val6': [False, False]},
                           index=pd.Series([1, 2], name='index'))
data.to_excel(writer, index=False, sheet_name='Sheet1')
workbook  = writer.book
ws = writer.sheets['Sheet1']
d = {
    'bold': True,
    'text_wrap': False,
    'align': 'center',
    'valign': 'center',
    'border': 1
}
colors = {'val3': '#FFFF00',
          'val4': '#DCE6F1',
          'val6': '#FDE9D9'}

for col_num, value in enumerate(data.columns.values):
    for key in colors:
        if key == value:
            d['fg_color'] = colors[key]
            header_format = workbook.add_format(d)
            ws.write(0, col_num, value, header_format)

writer.save()

输出：