如何用xlsxwriter导出带MultiIndex列的Pandas DataFrame至Excel并格式化

针对你遇到的MultiIndex列DataFrame导出Excel时的报错，以及需要同时使用add_table()和autofit()的需求，可通过以下步骤实现：

核心思路 #

Pandas目前不支持index=False时导出MultiIndex列，因此必须保留索引导出（index=True），再通过xlsxwriter的API手动适配表格结构和格式，同时实现自动列宽。

完整实现代码 #

import pandas as pd

DAYS = ['26-Aug','27-Aug','28-Aug']
SHIFTS = ['S1','S2','S3']
EMPLOYEES = ['John Doe','Jane Doe']

# 创建按日期分组的班次列索引
idx = pd.MultiIndex.from_product([DAYS, SHIFTS], names=['Days','Shifts'])
# 为每位员工创建DataFrame
df = pd.DataFrame('', EMPLOYEES, idx)
# 填充示例考勤数据
df.loc['John Doe', ('26-Aug', 'S1')] = '出勤'
df.loc['Jane Doe', ('27-Aug', 'S2')] = '请假'

# 使用xlsxwriter引擎初始化ExcelWriter
with pd.ExcelWriter('排班考勤表.xlsx', engine='xlsxwriter') as writer:
    # 导出DataFrame，必须保留索引（index=True），避免MultiIndex列导出报错
    df.to_excel(writer, sheet_name='考勤表', index=True)
    
    # 获取工作表对象
    worksheet = writer.sheets['考勤表']
    
    # 自动获取已使用单元格的范围（Excel的1-based格式）
    used_range = worksheet.used_range
    table_range = f'{used_range[0]}:{used_range[1]}'
    
    # 构建表格列配置：适配索引列和MultiIndex列
    column_settings = [{'header': '员工姓名'}]  # 索引列表头
    for day, shift in df.columns:
        # 将MultiIndex元组转为换行表头，与Pandas导出的格式匹配
        column_settings.append({'header': f'{day}\n{shift}'})
    
    # 定义表格样式（可自定义）
    table_options = {
        'style': 'Table Style Medium 9',
        'columns': column_settings,
        'autofilter': True,
        'banded_rows': True
    }
    
    # 添加表格到工作表
    worksheet.add_table(table_range, table_options)
    
    # 自动调整所有列宽
    worksheet.autofit()
    
    # （可选）给特定行/列添加自定义格式
    header_format = writer.book.add_format({'bg_color': '#D9E1F2', 'bold': True})
    # 给表头两行设置背景色
    worksheet.set_row(0, None, header_format)
    worksheet.set_row(1, None, header_format)
    # 手动调整索引列宽度
    worksheet.set_column(0, 0, 18)

关键说明 #

1. 避免导出报错：设置index=True导出，绕过Pandas对index=False+MultiIndex列的限制。
2. 自动识别表格范围：通过worksheet.used_range获取已填充数据的单元格范围，无需手动计算行数列数，适配动态数据。
3. 适配MultiIndex表头：将列的MultiIndex元组转为带换行的表头字符串，与Pandas自动合并的表头格式保持一致，确保表格显示正常。
4. 格式扩展：可通过xlsxwriter的add_format()、set_row()、set_column()方法给特定行/列添加背景色、字体样式等自定义格式。

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