xlsxwriter - Conditional formatting based on column name of the dataframe

Question

I have a dataframe as below. I want to apply conditional formatting on column "Data2" using the column name. I know how to define format for a specific column but I am not sure how to define it based on column name as shown below.

So basically I want to do the same formatting on column name(because the order of column might change)

df1 = pd.DataFrame({'Data1': [10, 20, 30],
                   'Data2': ["a", "b", "c"]})
writer = pd.ExcelWriter('pandas_filter.xlsx', engine='xlsxwriter', )
workbook  = writer.book

df1.to_excel(writer, sheet_name='Sheet1', index=False)
worksheet = writer.sheets['Sheet1']
blue = workbook.add_format({'bg_color':'#000080', 'font_color': 'white'})
red = workbook.add_format({'bg_color':'#E52935', 'font_color': 'white'})

l = ['B2:B500']
for columns in l:
    worksheet.conditional_format(columns, {'type': 'text',
                                              'criteria': 'containing',
                                               'value': 'a',
                                               'format': blue})
    worksheet.conditional_format(columns, {'type': 'text',
                                              'criteria': 'containing',
                                               'value': 'b',
                                               'format': red})

writer.save()

Answer 1

using xlsxwriter with xl_col_to_name we can get the column name using the index.

from  xlsxwriter.utility import xl_col_to_name

target_col = xl_col_to_name(df1.columns.get_loc("Data2")) 
l = [f'{target_col}2:{target_col}500']

for columns in l:

using opnpyxl with get_column_letter we can get the column name using the index.

from openpyxl.utils import get_column_letter

target_col = get_column_letter(df1.columns.get_loc("Data2") + 1) # add 1 because get_column_letter index start from 1

l = [f'{target_col}2:{target_col}500']

for columns in l:
   ...