xlsxwriter not applying format to header row of dataframe - Python Pandas

Question

I am trying to take a dataframe and create a spreadsheet from that dataframe using the xlsxwriter

I am trying to do some formatting to the header row, but the only formatting that seems to be working on that row is for the row height. The exact same formatting options work on the other rows of the dataframe.

Please see code below..

The red color (and the height) is applied to all rows except the header row (row 2) - the red color is applied to both row 0 and row 3, but only the height is applied to row 2

Any help would be much appreciated

import numpy as np
import pandas as pd
from pandas.io.data import DataReader
from pandas import DataFrame
from IPython import display

import xlsxwriter

WorkBookName="test.xlsx"



df3=pd.read_csv("https://raw.githubusercontent.com/wesm/pydata-book/master/ch08/tips.csv", sep=',')

writer = pd.ExcelWriter(WorkBookName, engine='xlsxwriter')
df3.to_excel(writer, sheet_name="sheet",index=False,startrow=2)


workbook  = writer.book
worksheet = writer.sheets["sheet"]

worksheet.write(0,0,"text string")
worksheet.write(0,1,"text string")
worksheet.write(0,2,"text string")
worksheet.write(0,3,"text string")


color_format = workbook.add_format({'color': 'red'})
worksheet.set_row(0,50,color_format)
worksheet.set_row(2,50,color_format)
worksheet.set_row(3,50,color_format)    


writer.save()

display.FileLink(WorkBookName)

Martymartyn

Answer 1

You are trying to change the formatting of the header so you should first reset the default header settings

pd.core.format.header_style = None

Then apply the formatting as required

format = workbook.add_format()
format.set_align('center')
format.set_align('vcenter')

worksheet.set_column('A:C',5, format)

here is complete working code

d=pd.DataFrame({'a':['a','a','b','b'],
               'b':['a','b','c','d'],
               'c':[1,2,3,4]})
d=d.groupby(['a','b']).sum()

pd.core.format.header_style = None

writer = pd.ExcelWriter('pandas_out.xlsx', engine='xlsxwriter')
workbook  = writer.book
d.to_excel(writer, sheet_name='Sheet1')

worksheet = writer.sheets['Sheet1']

format = workbook.add_format()
format.set_align('center')
format.set_align('vcenter')

worksheet.set_column('A:C',5, format)
writer.save()

Hedgepeth

What is your Pandas version? In Pandas 0.19.1 it's pd.formats.format.header_style instead of pd.core.format.header_style ... – Bridgers

yes @MaxU correct you should use this based on the correct pandas version – Hedgepeth

Awesome..Thank you! – Martymartyn

@Hedgepeth Bless you. I was beating my head against a wall on this one. To all, it is important to call pd.core.format.header_style = None before calling pd.ExcelWriter. – Irresponsive

Answer 2

In case you have 0.22, you must do pd.io.formats.excel.header_style = None. Check this git page out.

Ameliorate

Answer 3

As far as I've understood, Pandas sets the format of the index row. There are ways to reset it, but those solutions weren't very reliable. It was also quite hard to actually format it.

The accepted answer uses the same format for all cells, while I just wanted to format the index row.
I solved it by writing out the index columns with the desired format:

import pandas as pd

# The data that we're feeding to ExcelWriter
df = pd.DataFrame(
    {
        "Col A": ["a", "a", "b", "b"],
        "Col B": ["a", "b", "c", "d"],
        "Col C": [1, 2, 3, 4],
    }
)

# The Excel file we're creating
writer = pd.ExcelWriter("pandas_out.xlsx", engine="xlsxwriter")
df.to_excel(writer, sheet_name="Sheet1", index=False) # Prevents Pandas from outputting an index

# The variables we'll use to do our modifications
workbook = writer.book
worksheet = writer.sheets["Sheet1"]

worksheet.set_row(0, 30) # Set index row height to 30

# Find more info here: https://xlsxwriter.readthedocs.io/format.html#format-methods-and-format-properties
header_format = workbook.add_format(
    {
        "bold": True,
        "valign": "vcenter",
        "align": "center",
        "bg_color": "#d6d6d6",
        "border": True,
    }
)

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

# Set format of data
format1 = workbook.add_format({"align": "center"})
worksheet.set_column('A:Z', 10, format1) # Width of cell

writer.save()

Dragoon

Answer 4

In order to change the formatting of the header, you should first reset the default header settings.

Version 0.24 requires:

import pandas.io.formats.excel
pandas.io.formats.excel.ExcelFormatter.header_style = None

Steeple

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Knobby