如何将Pandas DataFrame导出为Excel并按STYLE列设置样式转置

问题分析 #

你需要将长格式数据转置为宽格式（日期为列标题、Unit为索引），并根据原始数据中STYLE列的定义，为对应METRIC的列设置Excel单元格格式。之前用pandas.Styler的set_properties、apply方法失败，是因为Pandas的Styler对Excel自定义格式的支持有限，推荐直接用openpyxl操作工作簿来实现。

解决方案步骤 #

1. 数据转置（生成目标结构）

先将原始长格式数据转置为宽格式，同时提取每个METRIC对应的格式规则：

import pandas as pd

# 模拟你的原始数据（替换为实际数据）
df = pd.DataFrame({
    "Unit": ["A", "A", "A", "B"],
    "METRIC": ["Sales", "Sales", "Cost", "Sales"],
    "DATE": ["2024-01-01", "2024-02-01", "2024-01-01", "2024-01-01"],
    "VALUE": [1000, 1500, 0.5, 800],  # Cost的值为小数（对应50%）
    "STYLE": ["$#,##0.00", "$#,##0.00", "0.00%", "$#,##0.00"]
})

# 提取每个METRIC对应的唯一格式规则
metric_style_map = df.drop_duplicates(subset=["METRIC"]).set_index("METRIC")["STYLE"].to_dict()

# 转置为宽格式：Unit为索引，(DATE, METRIC)为列，VALUE为单元格值
pivot_df = df.pivot(index="Unit", columns=["DATE", "METRIC"], values="VALUE")

# 调整列标题格式（可选，让标题更清晰）
pivot_df.columns = [f"{date} ({metric})" for date, metric in pivot_df.columns]

2. 导出并设置Excel格式

用openpyxl加载导出的无样式Excel，遍历列并应用对应格式：

from openpyxl import load_workbook

output_path = "formatted_output.xlsx"
# 先导出无样式的基础表格
pivot_df.to_excel(output_path, engine="openpyxl")

# 加载工作簿并设置格式
wb = load_workbook(output_path)
ws = wb.active

# 遍历所有数据列（跳过第一列的Unit索引）
for col in ws.iter_cols(min_col=2):
    # 从列标题提取对应的METRIC
    col_title = col[0].value
    metric = col_title.split("(")[-1].rstrip(")")
    target_format = metric_style_map.get(metric)
    
    if target_format:
        # 为该列所有数据单元格设置格式
        for cell in col[1:]:  # 跳过标题行
            if cell.value is not None:
                cell.number_format = target_format

# 保存最终带格式的Excel
wb.save(output_path)

关键说明 #

• 之所以放弃pandas.Styler：Styler的设计初衷是生成HTML报表，导出Excel时很多自定义格式规则无法被正确解析，而openpyxl是直接操作Excel文件的库，对格式支持更完整。
• 格式匹配逻辑：确保列标题能正确提取到METRIC，如果你的转置列标题格式不同，需要调整split或正则提取的逻辑。
• 百分比数据注意点：如果STYLE是百分比格式，原始VALUE必须是小数（比如50%对应0.5），否则Excel会显示错误的百分比值。

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