CODEWITHSUNDEEP
pythonpandas
Munichong
Munichong

Reputation: 4031

How to change the background color of Excel cells/rows using Pandas?

How should I set the color of a group of cells in Excel using Pandas?

import pandas as pd
import numpy as np

np.random.seed(24)
df = pd.DataFrame({'A': np.linspace(1, 10, 10)})
df = pd.concat([df, pd.DataFrame(np.random.randn(10, 4), columns=list('BCDE'))],
               axis=1)

writer = pd.ExcelWriter('test.xlsx', engine='xlsxwriter')
workbook = writer.book
worksheet = workbook.add_worksheet('Summary')
writer.sheets['Summary'] = worksheet

format = workbook.add_format({'bg_color': '#21AD25'})
worksheet.set_row(0, cell_format= format)  # set the color of the first row to green

df.to_excel(writer, sheet_name='Summary', index=False)
writer.save()

Above code can change the color of the first row. However, the background color of the dataframe header is not changed.

Upvotes: 0

Views: 10517

Answers (1)

naccode
naccode

Reputation: 520

As per the documentation, "Pandas writes the dataframe header with a default cell format. Since it is a cell format it cannot be overridden using set_row(). If you wish to use your own format for the headings then the best approach is to turn off the automatic header from Pandas and write your own.". Solution is to include header=False and startrow=1 in the to_excel call to avoid copying the headers and leave the first row blank, and then iterate over the headers to paste column values and the format you like.

Full example using your code:

import pandas as pd
import numpy as np

np.random.seed(24)
df = pd.DataFrame({'A': np.linspace(1, 10, 10)})
df = pd.concat([df, pd.DataFrame(np.random.randn(10, 4), columns=list('BCDE'))],
               axis=1)

writer = pd.ExcelWriter('test.xlsx', engine='xlsxwriter')
workbook = writer.book
worksheet = workbook.add_worksheet('Summary')
writer.sheets['Summary'] = worksheet

# format = workbook.add_format({'bg_color': '#21AD25'})
# worksheet.set_row(0, cell_format= format)  # set the color of the first row to green

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

# Add a header format.
header_format = workbook.add_format({
    'bg_color': '#21AD25',  # your setting
    'bold': True,           # additional stuff...
    'text_wrap': True,
    'valign': 'top',
    'align': 'center',
    'border': 1})

# Write the column headers with the defined format.
for col_num, value in enumerate(df.columns.values):
    worksheet.write(0, col_num + 1, value, header_format)

writer.save()

Upvotes: 4

Related Questions