Python: Pandas 风格的背景颜色在 Excel 文件中不可见

Question

我正在使用 xlsxwriter 作为引擎创建一个包含多个工作表的 excel 文件。

在每个工作表中，行颜色基于名为 colour 的列的值

但我的 excel 文件中看不到颜色。

import pandas as pd

def row_colour(row):
    return ['background-color:'+row.colour.lower()for i in row]


writer = pd.ExcelWriter('try.xlsx', engine='xlsxwriter')

cols = ['subject','colour']
df1 = pd.DataFrame([['Math','DarkRed'],['Science','Yellow']],columns=cols)

df2 = pd.DataFrame([['English','Orange'],['History','Green']],columns=cols)

df3 = pd.DataFrame([['Geography','DarkRed'],['Civic','Yellow']],columns=cols)

df1.style.apply(row_colour,axis=1)
df2.style.apply(row_colour,axis=1)
df3.style.apply(row_colour,axis=1)

df1.to_excel(writer, sheet_name='Sheet 1')
df2.to_excel(writer, sheet_name='Sheet 2')
df3.to_excel(writer, sheet_name='Sheet 3')

writer.save()

在输出中没有颜色可见:

接受的答案对于上述问题是正确的。

我通过删除颜色列改进了任务，因为它的唯一用途是为行着色。

代码:

import pandas as pd

def row_colour(table,color):
    print("table: \n "+str(table))
    print("table shape : "+str(table.shape))
    color_data = []

    for index,row in table.iterrows():
        color.iloc[index]
        if str(color.iloc[index]['colour']) == "DarkRed":
            c= 'background-color:red'
        else:
            c= 'background-color:'+str(color.iloc[index]['colour'])
        color_data.append([c for i in range(len(row))])


    return pd.DataFrame(color_data,index=table.index, columns=table.columns)


writer = pd.ExcelWriter('try.xlsx', engine='xlsxwriter')

cols = ['subject','colour']
df1 = pd.DataFrame([['Math','DarkRed'],['Science','Yellow']],columns=cols)

df2 = pd.DataFrame([['English','Orange'],['History','Green']],columns=cols)

df3 = pd.DataFrame([['Geography','DarkRed'],['Civic','Yellow']],columns=cols)

color = pd.DataFrame(columns=['colour'])
color['colour']=df1['colour']
df1 = df1.drop(['colour'],axis=1)
df1=df1.style.apply(row_colour,axis=None,color=color)

color = pd.DataFrame(columns=['colour'])
color['colour']=df2['colour']
df2=df2.drop(['colour'],axis=1)
df2=df2.style.apply(row_colour,axis=None,color=color)

color = pd.DataFrame(columns=['colour'])
color['colour']=df3['colour']
df3=df3.drop(['colour'],axis=1)
df3=df3.style.apply(row_colour,axis=None,color=color)

df1.to_excel(writer, sheet_name='Sheet 1')
df2.to_excel(writer, sheet_name='Sheet 2')
df3.to_excel(writer, sheet_name='Sheet 3')

writer.save()

Answer 1

功能没问题，你只需要重新分配df1，df2，df3。这应该有效:

import pandas as pd

def row_colour(row):
    return ['background-color:'+row.colour.lower()for i in row]


writer = pd.ExcelWriter('try.xlsx', engine='xlsxwriter')

cols = ['subject','colour']
df1 = pd.DataFrame([['Math','DarkRed'],['Science','Yellow']],columns=cols)
df2 = pd.DataFrame([['English','Orange'],['History','Green']],columns=cols)
df3 = pd.DataFrame([['Geography','DarkRed'],['Civic','Yellow']],columns=cols)

df1 = df1.style.apply(row_colour,axis=1)
df2 = df2.style.apply(row_colour,axis=1)
df3 = df3.style.apply(row_colour,axis=1)

df1.to_excel(writer, sheet_name='Sheet 1')
df2.to_excel(writer, sheet_name='Sheet 2')
df3.to_excel(writer, sheet_name='Sheet 3')

writer.save()

这里的to_excel是pandas.io.formats.style.Styler的一个方法，而不是原来的dataframe。