python - 不了解 Pandas 样式器如何用于导出到 excel（.format、.set_properties）

Question

我在将 Pandas Dataframe 格式化导出到 Excel 后遇到一些问题。我阅读了https://pandas.pydata.org/pandas-docs/stable/style.html并尝试按照示例进行操作，但我唯一可以工作的是 .apply 和 .applymap。

我的代码如下。我想做三件事 - 将边框颜色设置为黑色，将数字设置为百分比，并使用 Pandas Styler 格式化函数将红色应用于负数，将其导出到 Excel 文件中。任何帮助或指导将不胜感激。

import pandas as pd
import numpy as np
import xlsxwriter

np.random.seed(24)
df = pd.DataFrame({'A': np.linspace(1, 10, 10)})
df = pd.concat([df, pd.DataFrame(np.random.randn(10, 4), columns=list('BCDE'))],
               axis=1)
df.iloc[0, 2] = np.nan

def color_negative_red(val):
    color = 'red' if val < 0 else 'black'
    return 'color: %s' %color

workbook = xlsxwriter.Workbook('example20.xlsx')
writer = pd.ExcelWriter(workbook.filename, engine = 'xlsxwriter')
workbook = writer.book

worksheet = workbook.add_worksheet('Sheet1')
writer.sheets['Sheet1'] = worksheet
worksheet.hide_gridlines(2)

df1 = df.style.set_properties(**{'border-color':'black'}) #this line of code doesn't work.
df2 = df1.style.format("{:.2%}").applymap(color_negative_red) #this line of code works only for the applymap(color_negative_red) section.
df2.to_excel(writer, sheet_name='Sheet1')

writer.save()

标签： pythonexcelpandasnumpyxlsxwriter