XlsxWriter Pandas change first row cell color based on value-CodePudding

I am creating an excel sheet with the following code:

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()

I want to change the column background colors based on the value with the following dictionary:

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

CodePudding user response：

The only solution I've found so far is by using the second for loop, try:

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()

Output: