Export styled dataframe to Excel (background color)

Question

I'm looking to generate an excel file from styled dataframes. Specifically I want to keep the background colors of cells.

I can generate an excel file with df.to_excel() or df.style.to_excel(), but the background color disappears along the way. Is there any way to keep the background color?

EDIT: The specific code I used:

df = pd.DataFrame([
  { 'color_A_in_red': True , 'A': 1 },
  { 'color_A_in_red': False , 'A': 2 },
  { 'color_A_in_red': True , 'A': 2 },
])

def highlight(x):
    c = f"background-color:red" 
    #condition
    m = x["color_A_in_red"]
    # DataFrame of styles
    df1 = pd.DataFrame('', index=x.index, columns=x.columns)
    # set columns by condition
    df1.loc[m, 'A'] = c
    return df1

df.style.apply(highlight, axis=None)
df.to_excel("test.xlsx")

I've never done this myself so making assumptions here. I'm not sure if pandas supports this natively. Also I think if you style a df the styles don't persist if you update the df (so you have to do it right before the write). Again, assumptions from conversations with others but I've never done it myself so sorry if that's not right/useful! — rayad, Mar 31 '23 at 23:28
Does this answer your question? [How can I color the empty rows in and export to an excel file with pandas?](https://stackoverflow.com/questions/70383088/how-can-i-color-the-empty-rows-in-and-export-to-an-excel-file-with-pandas) — Laurent, Apr 01 '23 at 07:20
@Laurent, thank you, but df.style.to_excel() (which was the solution of that question) was exactly what I used here, with no success. Specifically, I used df.style.apply() to color the cells. I will add some code to the question for clarity. — guybrush_threepwood, Apr 03 '23 at 07:04
Can you try `df.style.apply(highlight, axis=None).to_excel('styled.xlsx', engine='openpyxl')` ? — jezrael, Apr 03 '23 at 07:11
Ah, found it! df.style.apply does not act on the existing object but returns a new object! — guybrush_threepwood, Apr 03 '23 at 08:12

score 0 · Accepted Answer · answered Apr 03 '23 at 08:15

df.style.apply() does not change df directly but returns a modified copy. The following code works:

df = pd.DataFrame([
  { 'color_A_in_red': True , 'A': 1 },
  { 'color_A_in_red': False , 'A': 2 },
  { 'color_A_in_red': True , 'A': 2 },
])

def highlight(x):
    c = f"background-color:red" 
    #condition
    m = x["color_A_in_red"]
    # DataFrame of styles
    df1 = pd.DataFrame('', index=x.index, columns=x.columns)
    # set columns by condition
    df1.loc[m, 'A'] = c
    return df1


df = df.style.apply(highlight, axis=None)

df.to_excel("test.xlsx")

Export styled dataframe to Excel (background color)

1 Answers1