如何通过Pandas DataFrame为Excel动态新增列并设置单元格格式？

1. 动态生成类别列 #

先对原始数据做结构转换，根据每个类别的子项数量自动生成对应列。假设你的数据包含类别、子项、值三个字段，可通过透视表实现动态列生成：

import pandas as pd

# 示例原始数据
data = {
    "类别": ["A", "A", "B", "B", "B"],
    "子项": ["a1", "a2", "b1", "b2", "b3"],
    "值": [10, 20, 30, 40, 50]
}
df = pd.DataFrame(data)

# 生成带类别前缀的唯一列名
df["动态列名"] = df["类别"] + "_" + df["子项"]
# 透视为宽表，空值填充0（可根据需求调整）
dynamic_df = df.pivot(index=df.groupby("类别").cumcount(), columns="动态列名", values="值").fillna(0)
# 去除自动生成的索引
dynamic_df = dynamic_df.reset_index(drop=True)

2. Excel格式定制（合并单元格+对齐） #

使用xlsxwriter作为导出引擎，直接操作Excel对象实现格式设置：

# 初始化Excel写入器
writer = pd.ExcelWriter("最终输出.xlsx", engine="xlsxwriter")
# 从第2行第2列开始写入数据（留出行列用于合并表头）
dynamic_df.to_excel(writer, sheet_name="数据汇总", startrow=1, startcol=1, index=False)

workbook = writer.book
worksheet = writer.sheets["数据汇总"]

# 定义格式模板
# 通用对齐+边框格式
align_border_format = workbook.add_format({
    "align": "center",
    "valign": "vcenter",
    "border": 1
})
# 表头加粗格式
header_format = workbook.add_format({
    "align": "center",
    "valign": "vcenter",
    "bold": True,
    "border": 1
})

# 合并左上角单元格（示例合并A1到B1，写入总标题）
worksheet.merge_range("A1:B1", "类别数据汇总", header_format)

# 应用格式到表头行（第2行）
worksheet.set_row(1, None, header_format)

# 获取数据行列数，批量应用格式到数据区域
row_count, col_count = dynamic_df.shape
# 数据区域从第3行第2列开始，到row_count+2行、col_count+1列结束
worksheet.conditional_format(2, 1, row_count+1, col_count, {"type": "all", "format": align_border_format})

# 自动调整列宽适配内容
for col_idx, col_name in enumerate(dynamic_df.columns, start=2):
    # 计算列内容最大长度
    max_len = max(len(col_name), dynamic_df[col_name].astype(str).str.len().max())
    worksheet.set_column(col_idx-1, col_idx-1, max_len + 2)  # xlsxwriter列索引从0开始

# 保存文件
writer.close()

关键说明 #

• 动态列生成：通过pivot结合分组计数索引，确保每个类别的子项都能展开为独立列，不管类别下有多少子项都能自动适配。
• 格式设置：必须用xlsxwriter或openpyxl引擎（示例用xlsxwriter），Pandas原生样式无法实现复杂的单元格合并和批量格式应用。
• 单元格合并：merge_range方法的参数是Excel单元格范围字符串（如"A1:B1"），可根据实际表头位置调整。

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