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