question: How to add format to already writed cells?

[PaleNeutron]

Question

I'm trying to generate excel from a pandas styler object which contains font color.

If I simply use my_styler.to_excel(writer, sheet_name='Sheet1') the color exist.

If I follow the document and set format to dataframe, percent exist.

# Add a percent number format.
percent_format = workbook.add_format({"num_format": "0%"})

# Apply the number format to Grade column.
worksheet.set_column(2, 2, None, percent_format)

But when I tried to set number format to workbook generated by my_styler.to_excel(writer, sheet_name='Sheet1') , it does not work.

test code:

import pandas as pd
import numpy as np
from io import BytesIO

weather_df = pd.DataFrame(np.random.rand(10,2)*5,
                          index=pd.date_range(start="2021-01-01", periods=10),
                          columns=["Tokyo", "Beijing"])

def make_pretty(styler):
    styler.set_caption("Weather Conditions")
    styler.format_index(lambda v: v.strftime("%A"))
    styler.background_gradient(axis=None, vmin=1, vmax=5, cmap="YlGnBu")
    return styler

my_styler = weather_df.loc["2021-01-04":"2021-01-08"].style.pipe(make_pretty)
my_styler

writer = pd.ExcelWriter("test.xlsx", engine='xlsxwriter')
my_styler.to_excel(writer, sheet_name='Sheet1')
percent_format = writer.book.add_format({'num_format': '0.00%'}) # this line not work
# Now apply the number format to the column 
writer.sheets['Sheet1'].set_column(0, 0, 30)
writer.sheets['Sheet1'].set_column(1, 2, 15, percent_format)
writer.close()

[jmcnamara]

How to add format to already writed cells?

Unfortunately you can't. XlsxWriter doesn't support overwriting/adding cell formats.

Instead you could omit the styler and use an Excel conditional format to get a similar effect:

import pandas as pd
import numpy as np
from io import BytesIO

weather_df = pd.DataFrame(np.random.rand(10,2)*5,
                          index=pd.date_range(start="2021-01-01", periods=10),
                          columns=["Tokyo", "Beijing"])

writer = pd.ExcelWriter("test.xlsx", engine='xlsxwriter')
weather_df.to_excel(writer, sheet_name='Sheet1')
percent_format = writer.book.add_format({'num_format': '0.00%'}) 

# Get the dimensions of the dataframe.
(max_row, max_col) = weather_df.shape

# Apply a conditional format to the required cell range.
writer.sheets['Sheet1'].conditional_format(1, 1, max_row, max_col, {"type": "3_color_scale"})

# Now apply the number format to the column 
writer.sheets['Sheet1'].set_column(0, 0, 30)
writer.sheets['Sheet1'].set_column(1, 2, 15, percent_format)
writer.close()

Output:

You can change the colors, if required, via the XlsxWriter APIs: https://xlsxwriter.readthedocs.io/working_with_conditional_formats.html#type-3-color-scale

[PaleNeutron]

Thanks a lot, conditional_format can not solve my real world case, my color condition is much complex and hard to convert it to excel condition description.

But I review the source and found another solution. I think it worth to be documented:

    def color_cells(x):
        for col in x.columns:
            df_styler[col] = np.where(x[col] < first_row[col], 'color: green;', 'color: red;')
            # other code
        percent_style = "number-format: 0.00%;"  # this line is vital
        df_styler = df_styler.map(lambda x: f"{x} {percent_style}")
        return df_styler

df_style = df.style.apply(color_cells, axis=None)

pandas create a dummy css property number-format just for xlsxwriter !

[jmcnamara]

Cool. Thanks for letting me know.