pandas xlsxwriter, format table header - not sheet header

Question

I'm saving pandas DataFrame to_excel using xlsxwriter. I've managed to format all of my data (set column width, font size etc) except for changing header's font and I can't find the way to do it. Here's my example:

import pandas as pd
data = pd.DataFrame({'test_data': [1,2,3,4,5]})
writer = pd.ExcelWriter('test.xlsx', engine='xlsxwriter')

data.to_excel(writer, sheet_name='test', index=False)

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

font_fmt = workbook.add_format({'font_name': 'Arial', 'font_size': 10})
header_fmt = workbook.add_format({'font_name': 'Arial', 'font_size': 10, 'bold': True})

worksheet.set_column('A:A', None, font_fmt)
worksheet.set_row(0, None, header_fmt)

writer.save()

The penultimate line that tries to set format for the header does nothing.

rleelr · Accepted Answer · 2020-09-13 01:08:10Z

57

I think you need first reset default header style, then you can change it:

pd.core.format.header_style = None

All together:

import pandas as pd

data = pd.DataFrame({'test_data': [1,2,3,4,5]})
writer = pd.ExcelWriter('test.xlsx', engine='xlsxwriter')

pd.core.format.header_style = None

data.to_excel(writer, sheet_name='test', index=False)

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

font_fmt = workbook.add_format({'font_name': 'Arial', 'font_size': 10})
header_fmt = workbook.add_format({'font_name': 'Arial', 'font_size': 10, 'bold': True})

worksheet.set_column('A:A', None, font_fmt)
worksheet.set_row(0, None, header_fmt)

writer.save()

Explaining by jmcnamara, thank you:

In Excel a cell format overrides a row format overrides a column format.The pd.core.format.header_style is converted to a format and is applied to each cell in the header. As such the default cannot be overridden by set_row(). Setting pd.core.format.header_style to None means that the header cells don't have a user defined format and thus it can be overridden by set_row().

EDIT: In version 0.18.1 you have to change

pd.core.format.header_style = None

to:

pd.formats.format.header_style = None

EDIT: from version 0.20 this changed again

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

thanks krvkir.

EDIT: from version 0.24 this is now required

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

thanks Chris Vecchio.

edited Sep 13, 2020 at 1:08

rleelr

1,95418 silver badges27 bronze badges

answered Apr 18, 2016 at 12:50

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

rahlf23 Over a year ago

This answer should be updated to reflect the contribution by Chris Vecchio below: stackoverflow.com/a/58033197/8146556

Ironbeard · Accepted Answer · 2017-05-31 15:53:27Z

32

An update for anyone who comes across this post and is using Pandas 0.20.1.

It seems the required code is now

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

Apparently the excel submodule isn't imported automatically, so simply trying pandas.io.formats.excel.header_style = None alone will raise an AttributeError.

answered May 31, 2017 at 15:53

Ironbeard

4754 silver badges8 bronze badges

Comments

Chris Vecchio · Accepted Answer · 2019-09-20 17:59:50Z

25

Another option for Pandas 0.25 (probably also 0.24). Likely not the best way to do it, but it worked for me.

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

answered Sep 20, 2019 at 17:59

Chris Vecchio

3761 gold badge4 silver badges6 bronze badges

Comments

eliu · Accepted Answer · 2020-06-30 14:43:36Z

6

for pandas 0.24:

The below doesn't work anymore:

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

Instead, create a cell formatting object, and re-write the first row's content (your header) one cell at a time with the new cell formatting object.
Now, you are future proof.

Use the following pseudo code:

# [1] write df to excel as usual
writer = pd.ExcelWriter(path_output, engine='xlsxwriter')
df.to_excel(writer, sheet_name, index=False)

# [2] do formats for other rows and columns first

# [3] create a format object with your desired formatting for the header, let's name it: headercellformat

# [4] write to the 0th (header) row **one cell at a time**, with columnname and format
for columnnum, columnname in enumerate(list(df.columns)):
    worksheet.write(0, columnnum, columnname, headercellformat)

edited Jun 30, 2020 at 14:43

answered Apr 13, 2019 at 15:09

eliu

2,4892 gold badges19 silver badges31 bronze badges

1 Comment

naccode Over a year ago

This is the recommended way according to docs (xlsxwriter.readthedocs.io/example_pandas_header_format.html). AND, it prevents from formatting the whole row (up to XFD column)

Alec Hanefeld · Accepted Answer · 2017-05-12 14:52:14Z

5

In pandas 0.20 the solution of the accepted answer changed again.

The format that should be set to None can be found at:

pandas.io.formats.excel.header_style

answered May 12, 2017 at 14:52

Alec Hanefeld

511 silver badge1 bronze badge

Comments

divingTobi · Accepted Answer · 2019-10-01 16:11:22Z

1

If you do not want to set the header style for pandas entirely, you can alternatively also pass a header=False to ExcelWriter:

import pandas as pd
import numpy as np

df = pd.DataFrame(np.random.rand(3, 5), 
                  columns=pd.date_range('2019-01-01', periods=5, freq='M'))

file_path='output.xlsx'
writer = pd.ExcelWriter(file_path, engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1', header=False, index=False )
workbook = writer.book
fmt = workbook.add_format({'num_format': 'mm/yyyy', 'bold': True})
worksheet = writer.sheets['Sheet1']
worksheet.set_row(0, None, fmt)
writer.save()

answered Oct 1, 2019 at 16:11

divingTobi

2,3502 gold badges16 silver badges32 bronze badges

Comments

Sven · Accepted Answer · 2021-02-17 17:28:49Z

-1

unfortunately add_format is in not avaiable anymore

answered Feb 17, 2021 at 17:28

Sven

1011 silver badge10 bronze badges

Collectives™ on Stack Overflow

pandas xlsxwriter, format table header - not sheet header

7 Answers 7

1 Comment

Comments

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

1 Comment

Comments

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related