How to format a dataframe while writing to Excel

Question

I am trying to format a dataframe while writing to excel.

For Xlsxwriter: As we cant format the headers. I copy the header to first row and try to change the format of the first row(I set header=False when writing to excel). I need to format specific parts of my header. Like first 3 heading are bold, the next three are red etc. Is there any way to set specific header formats? Now I am able to do either for the full row or full column. I am ready to loop through the rows and cols but for that I think I need to use .write, the issue here is that for .write I need to insert a value too, but the value is already available from Dataframe.

How can I target to format specific cells like A1, A2, A3 for formatting it differently.

Answer 1

You can do custom formatting using xlsxwriter:

Example Dataframe:

    id  PER Team
0   1   12  OKC
1   2   14  OKC
2   3   11  GSW

Had custom formatter for headers:

writer = pd.ExcelWriter("file.xlsx", engine='xlsxwriter')

df.to_excel(writer, sheet_name='Sheet1', startrow=1, header=False, index=False)

workbook  = writer.book
worksheet = writer.sheets['Sheet1']

# add custom headers
header_format1 = workbook.add_format({
    'bold': True,
    'border': 1})

header_format2 = workbook.add_format({
    'bold': True,
    'fg_color': '#ffcccc',
    'border': 1})

#add columns required for each format
column_format1 = ["id"]
column_format2 = ["PER", "Team"]

for col_num, value in enumerate(df.columns.values):
    if value in column_format1:
        worksheet.write(0, col_num, value, header_format1)
    elif value in column_format2:
        worksheet.write(0, col_num, value, header_format2)

    column_len = df[value].astype(str).str.len().max()

    column_len = max(column_len, len(value)) + 3
    print(column_len)

    worksheet.set_column(col_num, col_num, column_len)

writer.save()

Output Excel format: