How to style a pandas DataFrame into an excel file

Question

I am trying to build an application which writes data into an excel file. I am therefore trying to use the pandas library and I use the xlsxwriter engine. I read the documentation about the Styler object from the pandas library, but I can't find out a way to modify the style of table headers (or column headers, the keys of the dictionary I feed to the DataFrame constructor). In fact, I think the set_table_styles method isn't working very well in my case.

Here is the code I run:

import os
import pandas

writer = pandas.ExcelWriter(f"{os.getcwd()}\\Styled_excel.xlsx", engine="xlsxwriter", mode='w')

data_frame = pandas.DataFrame({"Pressure": [10, 20, 30, 50],
                               "Volume": [10, 8, 5, 2]})

styler = data_frame.style
styler.set_properties(**{'color': 'red', 'border-color': 'black', 'border-width': "1px"})
styler.set_table_styles([{'selector': 'th', 'props': [('font-weight', 'normal')]}])

styler.to_excel(excel_writer=writer, sheet_name="Feuil1", startrow=4, startcol=2, index=False)

writer.save()

Although the set_properties works as expected, set_table_styles doesn't seem to apply the style I want to, which is a normal font weight for the cells containing "Pressure" and "Volume".

On the same topic, I can't find anywhere in the documentation a way to programmatically change the width of a column, is it just not supported?

If you have any clue on what I might be doing wrong or why it isn't working, I thank you in advance for your answer.

Paul

EDIT: I did find an answer to the issues I had but if someone has an explanation as to why my previous code wasn't working as expected, I'll be glad to hear it (but I think it's due to the fact that the set_table_styles method can only be used when rendering an html page or when displaying the table in Jupyter Notebook)

Pauuuuuuul · Accepted Answer · 2022-07-08 09:28:58Z

3

I found a solution to both my issues on the xlsxwriter documentation (which I didn't know existed before I posted this question):

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

# Creation of format for the column headers
header_format = workbook.add_format({
    'bold': False,
    'border': 1
})

# Writing the column headers only and applying the style with the format created
for col_num, value in enumerate(data_frame.columns.values):
    worksheet.write_string(0, col_num, value, header_format)

# Widening a column to fully fit a string in it
worksheet.set_column('C:C', 10)

edited Jul 8, 2022 at 9:28

answered Nov 20, 2020 at 10:05

Pauuuuuuul

2753 silver badges13 bronze badges

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

Collectives™ on Stack Overflow

How to style a pandas DataFrame into an excel file

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related