Pandas+XlsxWriter导出Excel设置表头背景色异常排查

出现该异常是格式调用逻辑不符合XlsxWriter的API规则：

1. 若直接调用worksheet.write(0, 0, 自定义格式对象)，XlsxWriter会将第三个入参识别为待写入的单元格内容，格式对象被强制转换为布尔值False写入A1单元格，直接覆盖原有列名
2. 代码仅对(0,0)坐标的单个单元格做了写入操作，未覆盖首行所有表头列，因此其余表头单元格不会应用指定格式

前置初始化 #

创建Excel写入对象时提前定义统一的表头格式，所有工作表可复用该格式对象，无需重复创建：

import pandas as pd

# 初始化写入实例，指定XlsxWriter为引擎
writer = pd.ExcelWriter("多sheet统计结果.xlsx", engine="xlsxwriter")
workbook = writer.book

# 配置表头格式：加粗、#00FFFF背景、细边框
header_style = workbook.add_format({
    "bold": True,
    "bg_color": "#00FFFF",
    "border": 1
})

写入多Sheet并应用表头格式 #

两种实现方式按需选择即可：

方式一：写入时自动挂载表头格式（推荐） #

Pandas 1.3.0及以上版本的to_excel方法原生支持header_format参数，传入预定义格式后会自动为所有表头列匹配样式，不会修改原有列名内容：

# 此处替换为实际的数据源映射，key为工作表名，value为对应待写入的DataFrame
sheet_df_mapping = {
    "月度销售数据": sales_df,
    "用户留存统计": retention_df,
    "库存周转明细": stock_df
}

for sheet_name, df in sheet_df_mapping.items():
    df.to_excel(
        writer,
        sheet_name=sheet_name,
        index=False,  # 不需要写入行索引可保留该参数
        header_format=header_style
    )

方式二：写入后手动批量设置表头格式（适配自定义调整场景） #

如果需要在写入DataFrame后对表头做额外调整，可先正常写入数据，再逐列给表头单元格设置格式，注意传入原有列名避免内容被覆盖：

for sheet_name, df in sheet_df_mapping.items():
    # 先正常写入DataFrame
    df.to_excel(writer, sheet_name=sheet_name, index=False)
    current_ws = writer.sheets[sheet_name]
    # 遍历所有列，给首行表头设置格式
    for col_idx, col_name in enumerate(df.columns):
        current_ws.write(0, col_idx, col_name, header_style)

收尾保存 #

所有工作表写入完成后关闭写入流，生成最终文件：

writer.close()

• 调用XlsxWriter的write方法时，参数顺序固定为行号、列号、写入内容、格式对象，禁止跳过内容参数直接传格式，否则必然覆盖单元格值
• 若需要适配表头长度自动调整列宽，可在设置表头格式时同步调用current_ws.set_column(col_idx, col_idx, 列宽值)配置
• 格式对象属于workbook层级资源，全局创建一次即可跨工作表复用，重复创建会浪费内存

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