如何使用XlsxWriter保存DataFrame为Excel表格时保留多级索引表头

问题出在XlsxWriter的add_table方法默认不会复用已写入的多级表头，而是自动生成column1这类默认列名。需要手动构建对应多级索引的表头结构，并传递给add_table参数。

修正后的完整代码 #

import pandas as pd
import numpy as np

# 创建带多级列索引的DataFrame
columns = pd.MultiIndex.from_product([['A', 'B'], ['x', 'y', 'z']])
df = pd.DataFrame(np.arange(1, len(columns)*5+1).reshape(-1, len(columns)), index=range(5), columns=columns)

row_count = df.shape[0]
col_count = df.shape[1]

# 构建适配XlsxWriter的多级表头结构
header = []
# 第一级表头（上层索引）
header.append([col[0] for col in columns])
# 第二级表头（下层索引）
header.append([col[1] for col in columns])

with pd.ExcelWriter('final_template.xlsx', engine='xlsxwriter') as writer:
    df.to_excel(writer)
    worksheet = writer.sheets['Sheet1']
    
    # 定义表格范围：包含两级表头和所有数据行，跳过索引列
    worksheet.add_table(0, 1, row_count + 1, col_count, {
        'header': header,
        'autofilter': True,
        'style': 'Table Style Medium 2'  # 可选：添加表格样式增强可读性
    })
    worksheet.autofit()

关键说明 #

1. 构建表头结构：将多级列索引的每一层转换为列表，组成嵌套列表，对应Excel中的多行表头。
2. 调整表格范围：设置表格从第0行（第一级表头）开始，到最后一行数据结束，确保完整包含所有表头和数据。
3. 传递表头参数：在add_table中指定header参数为我们构建的多级表头，替代默认生成的列名。

如果需要将索引列也包含在表格内，只需调整表头和表格范围：

# 包含索引列的表头
header = []
header.append([''] + [col[0] for col in columns])
header.append(['Index'] + [col[1] for col in columns])

# 调整表格范围包含索引列
worksheet.add_table(0, 0, row_count + 1, col_count, {
    'header': header,
    'autofilter': True,
    'style': 'Table Style Medium 2'
})

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