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)

Shijo · Accepted Answer · 2017-01-03 16:42:42Z

9

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()

answered Jan 3, 2017 at 16:42

Shijo

9,7913 gold badges23 silver badges31 bronze badges

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

4 Comments

MaxU - stand with Ukraine Over a year ago

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

Shijo Over a year ago

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

Stumbling Through Data Science Over a year ago

Awesome..Thank you!

DaveL17 Over a year ago

@Shijo 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.

callmeGuy · Accepted Answer · 2018-11-11 22:55:45Z

2

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

answered Nov 11, 2018 at 22:55

callmeGuy

1,0643 gold badges15 silver badges30 bronze badges

Comments

Coco · Accepted Answer · 2020-12-16 22:51:49Z

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()

SherylHohman · Accepted Answer · 2023-03-17 01:10:40Z

0

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

edited Mar 17, 2023 at 1:10

SherylHohman

18.2k18 gold badges94 silver badges100 bronze badges

answered Mar 10, 2023 at 13:21

fabio.zamborlin

12 bronze badges

1 Comment

Blue Robin Over a year ago

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.

Collectives™ on Stack Overflow

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

4 Answers 4

4 Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related