why pandas dataframe style lost when saved with "to_excel"?

Question

Per this example the to_excel method should save the Excel file with background color. However, my saved Excel file does not have any color in it. I tried to write using both openpyxl and xlsxwriter engines. In both cases, the Excel file was saved, but the cell color/style was lost.

I can read the file back and reformat with openpyxl, but if this to_excel method is supposed to work, why doesn't it?

Here is the sample code.

 import pandas as pd # version 0.24.2
 dict = {'A': [1, 1, 1, 1, 1], 'B':[2, 1, 2, 1, 2], 'C':[1, 2, 1, 2, 1]}
 df = pd.DataFrame(dict)
 df_styled = df.style.apply(lambda x: ["background: #ffa31a" if x.iloc[0] < v else " " for v in x], axis=1)

 df_styled 
 ''' in my jupyter notebook, this displayed my dataframe with background color when condition is met, (all the 2s highlighted)'''

 '''Save the styled data frame to excel using to_excel'''
 df_styled.to_excel('example_file_openpyxl.xlsx', engine='openpyxl')
 df_styled.to_excel('example_file_xlsxwriter.xlsx', engine='xlsxwriter')

Answer 1

I stumbled across this myself and as far as I'm aware there isn't support for exporting to excel like this yet. I've adjusted your code to match the output to excel in the documentation.

This is the documentation output to excel method.

df.style.\
    applymap(color_negative_red).\
    apply(highlight_max).\
    to_excel('styled.xlsx', engine='openpyxl')

This is your code adjusted:

import pandas as pd
dict = {'A': [1, 1, 1, 1, 1], 'B':[2,1,2,1,2], 'C':[1,2,1,2,1]}
df = pd.DataFrame(dict)

def highlight(df, color = "yellow"):

    attr = 'background-color: {}'.format(color)
    df_bool = pd.DataFrame(df.apply(lambda x: [True if x.iloc[0] < v else False for v in x],axis=1).apply(pd.Series),
                      index=df.index)
    df_bool.columns =df.columns
    return pd.DataFrame(np.where(df_bool, attr, ""),
                       index= df.index, columns=df.columns)
df.style. \
    apply(highlight, axis=None).\
    to_excel("styled.xlsx", engine="openpyxl")

Inside the highlight function, I create a boolean dataframe based on the conditions applied in the list comprehension above. Then, I assign styling based on the result of this dataframe.