如何为导出Excel的Pandas DataFrame设置含正负数字的差异化样式

需求说明 #

现有Pandas DataFrame需要导出至Excel，已实现基础样式（黑色背景、绿色文字、白色边框），需扩展以下样式规则：

• 字符串与日期类型列的文字设为白色
• 数字类型列中，正数文字为绿色，负数文字为红色

示例DataFrame：

StartDate   ExpiryDate  Commodity   Quantity  Price   Total
---------   ----------  ----------  -------   -----   -----
02/28/2024  12/28/2024  HO          10000     -3.89   -38900
02/28/2024  12/28/2024  WPI         10000      4.20    42000

实现方案 #

通过Styler.applymap方法结合自定义函数，针对不同数据类型和数值设置文字颜色，同时保留全局的背景色与边框样式。

步骤1：定义单元格样式函数 #

编写函数判断单元格的数据类型和数值，返回对应的CSS样式：

def format_cell_color(val):
    # 字符串或日期类型：文字白色
    if isinstance(val, str) or pd.api.types.is_datetime64_any_dtype(type(val)):
        return 'color: white'
    # 数字类型：正负区分颜色
    else:
        if val > 0:
            return 'color: green'
        elif val < 0:
            return 'color: red'
        else:
            return 'color: white'  # 零值可按需调整样式

步骤2：组合样式并导出Excel #

结合全局样式与单元格级样式，导出到Excel：

import pandas as pd

# 构造示例DataFrame（实际使用替换为你的数据）
data = {
    'StartDate': ['02/28/2024', '02/28/2024'],
    'ExpiryDate': ['12/28/2024', '12/28/2024'],
    'Commodity': ['HO', 'WPI'],
    'Quantity': [10000, 10000],
    'Price': [-3.89, 4.20],
    'Total': [-38900, 42000]
}
df = pd.DataFrame(data)

# 转换日期列（若原始数据为字符串格式）
df['StartDate'] = pd.to_datetime(df['StartDate'])
df['ExpiryDate'] = pd.to_datetime(df['ExpiryDate'])

# 创建ExcelWriter（需使用xlsxwriter引擎支持样式导出）
writer = pd.ExcelWriter('styled_output.xlsx', engine='xlsxwriter')

# 组合全局样式与单元格级样式
styled_df = df.style.set_properties(**{
    'background-color': 'black',
    'border-color': 'white',
    'border-style': 'solid',
    'border-width': '1px'
}).applymap(format_cell_color)

# 导出到Excel，可自定义起始行、浮点数格式等参数
styled_df.to_excel(
    writer,
    sheet_name='Sheet1',
    startrow=0,  # 替换为你的rowPos变量
    float_format="%0.5f",
    index=False  # 若不需要导出索引可设置为False
)

# 保存并关闭文件
writer.close()

关键说明 #

• 确保使用xlsxwriter作为Excel引擎，Pandas的样式功能依赖该引擎支持
• 若原始日期列已为datetime类型，可跳过日期转换步骤
• 零值的样式可根据需求调整（例如改为灰色）
• set_properties中的边框样式需补充border-style和border-width，否则边框可能无法在Excel中显示

内容的提问来源于stack exchange，提问作者iBeMeltin