Styling of column and row headers not reflected when saving to excel.

[saihari]

Following is the code I used to style the headers of both rows and columns and then saving the stylized data frame to an excel file.

df = pd.DataFrame([1000000.0, 1000000.0], index=pd.bdate_range('2017-08-01', '2017-08-02'))

df.index = df.index.strftime("%Y-%m-%d")
styler = df.style
styler.set_table_styles(
# select the table header with th and set it right align
    [dict(selector="th", props=[("text-align", "right"),('color','green')])]   
)
styler.to_excel('styled.xlsx', engine='openpyxl')

Problem description

I am trying to stylize the headers of a data frame and then save that data frame to an excel file, but I am unable to see the stylized effects inside the excel file.

Output of Jupyter Notebook

Output in excel file

Expected Output

Pandas Version: '0.24.1'

[gfyoung]

I was unaware that our Excel IO functionality could even support that level of formatting (or styling) for DataFrame. That being said, it would be nice if we could...

[WillAyd]

This is somewhat related to #22773

So right now there's not a great API to do this. This is controlled internally by the header_style property of the ExcelFormatter class:

pandas/pandas/io/formats/excel.py

Line 385 in 683c7b5

def header_style(self):

That's not part of the exposed API, nor is it an exposed attribute of the Styler object as that instantiates it locally:

pandas/pandas/io/formats/style.py

Line 179 in 683c7b5

formatter = ExcelFormatter(self, na_rep=na_rep, cols=columns,

So one option is to figure out a way to logically expose that, though I'm not sure if that would really jive with the HTML elements that you are styling above.

In any case investigation and PRs would certainly be welcome

[attack68]

closed as recorded in master tracker #42276