how do you style data frame and write to excel in pandas

Question

I need to change the background's color of the dataframe headers to orange:

My df looks like this:

Campus    Server      Port
AZ       server12    Eth1
AZ1      server12    Eth2
AZ2      server23    Gi2
NV       Server34    Eth2

I tried this:

df1=df.style.set_table_style(
[{
   'selector': 'th',
   'props':  [('background-color', '#FFA500')]
}])

When I try to write this df1 to Excel, I don't see the color of the header's background changing:

with pd.ExcelWriter('C:/documements') as writer:
  df1.to_excel(writer, index=False

Any ideas what I am doing wrong here?

Answer 1

Most of the CSS styles (if not all) applied with set_table_styles won't export to Excel (see here).

You could use apply_index, instead :

bg = ["background-color: #FFA500"]*len(df.columns)

st = df.style.apply_index(lambda _: bg, axis=1)

st.to_excel("output.xlsx", index=False)

Preview (output.xlsx) :