python - 无法将数据框写入 excel

Question

我正在尝试设置数据框的样式并尝试将结果写入 Excel 工作表。但是当我尝试将结果写入 excel 时，它不会保留样式。这是我尝试过的。

import pandas as pd
df1 = pd.DataFrame({'Data': ["Hello", "Hai", "Hello", "Hai", "Hello", "Hai", "Hello"],'Data1': [10, 20, 30, 20, 15, 30, 45], })
df2 = pd.DataFrame({'Data': ["Hello", "Hai", "Hello", "Hai", "Hello", "Hai", "Hello"],'Data1': [10, 20, 30, 20, 15, 30, 45], })
data_to_be_colored = "Hello"

df1 = df1.style.apply(lambda x: ['background:lightblue' if x == data_to_be_colored else 'background:lightgrey' for x in df1.Data], axis=0)

df3 = {'Test 1': df1, 'Test 2': df2}

writer = pd.ExcelWriter(r'Styled_Excel.xlsx')
for sheetname, df in df3.items():
    df.to_excel(writer, sheet_name=sheetname, index = False)
    worksheet = writer.sheets[sheetname]
writer.save()

接收错误：

AttributeError: 'Styler' 对象没有属性 'style'

有人能告诉我上面的代码有什么问题以及如何写到excel来保留样式吗？

标签： pythonpython-3.xpandas

Answer 1

我试图从这里重现答案。似乎问题是您需要设置background-color而不是background.

from IPython.display import HTML

def highlight(x):
    r = '#ADD8E6'
    g = '#B0B0B0'

    m1 = x["Data"] == "Hello"
    m2 = x["Data"] != "Hello"
    df1 = pd.DataFrame('', index=x.index, columns=x.columns)
    for col in x:
        df1[col] = np.where(m1, 'background-color: {}'.format(r), df1[col])
        df1[col] = np.where(m2, 'background-color: {}'.format(g), df1[col])

    return df1

df1.style.apply(highlight, axis=None).to_excel('df.xlsx', engine='openpyxl')

输出：

如果我尝试使用浅蓝色和浅灰色，但它不起作用，所以我尝试使用 HTML 颜色。

更新

如果您想为不同的列着色，请尝试以下操作：

def highlight(x):
    r = '#ADD8E6'
    g = '#B0B0B0'
    red  = "#FF0000"

    m1 = x["A"] == "Hello"
    m2 = x["A"] != "Hello"
    df1 = pd.DataFrame('', index=x.index, columns=x.columns)
    for col in x[["A","B"]]:
        df1[col] = np.where(m1, 'background-color: {}'.format(r), df1[col])
        df1[col] = np.where(m2, 'background-color: {}'.format(g), df1[col])

    for col in x[["C","D"]]:
        df1[col] = 'background-color: {}'.format(red)

    return df1

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

输出：