Xlsxwriter处理多层索引列格式时Excel文件报错求助

问题分析 #

你遇到的Excel打开报错，核心原因是重复写入表头单元格：Pandas在将带多层索引的DataFrame写入Excel时，已经自动把多层表头（比如Level 0和Level 1）写入对应行，而你的代码再次用sheet.write(0, i, value)覆盖这些单元格，导致内容冲突、格式混乱，最终损坏Excel文件。此外，代码没有正确处理多层索引对应的列范围，容易出现格式错位。

正确实现思路 #

1. 先让Pandas完整写入DataFrame（包括多层表头），再用XlsxWriter修改格式，避免重复写入单元格。
2. 针对多层索引的结构，按顶层索引（比如VALUE1、VALUE2）批量处理对应的列范围，统一设置数据格式和表头格式。
3. 对顶层索引对应的表头单元格进行合并（可选，但符合Excel多层表头的常规展示），避免重复内容。

完整示例代码 #

假设你的columns_format配置如下，需求是：

• VALUE1：绿色字体、加粗、5位小数，表头居中带边框
• VALUE2：6位小数，表头居中带边框

import pandas as pd

# 模拟带多层索引的pivot_table结果
data = {
    'Category': ['A', 'A', 'B', 'B'],
    'SubCat': ['X', 'Y', 'X', 'Y'],
    'VALUE1': [1.234567, 2.345678, 3.456789, 4.567890],
    'VALUE2': [9.87654321, 8.76543210, 7.65432109, 6.54321098]
}
df = pd.pivot_table(data, index=['Category', 'SubCat'], columns=[], aggfunc='sum')
# 构造多层索引（模拟pivot_table生成的结构）
df.columns = pd.MultiIndex.from_tuples([('VALUE1', 'Total'), ('VALUE2', 'Total')])

# 格式配置字典
columns_format = {
    'VALUE1': {
        'align': 'center',
        'number_format': '0.00000',
        'font_color': '#008000',  # 绿色
        'bold': True,
        'header_align': 'center',
        'width': 18
    },
    'VALUE2': {
        'align': 'right',
        'number_format': '0.000000',
        'font_color': '#000000',  # 黑色
        'header_align': 'center',
        'width': 18
    }
}

# 写入Excel并设置格式
with pd.ExcelWriter('formatted_output.xlsx', engine='xlsxwriter') as writer:
    # 写入DataFrame，默认包含表头
    df.to_excel(writer, sheet_name='PivotResult', startrow=0)
    
    book = writer.book
    sheet = writer.sheets['PivotResult']
    
    # 获取顶层索引的唯一值及对应列范围
    level0_values = df.columns.get_level_values(0).unique()
    level0_cols = df.columns.get_level_values(0)
    
    for level0_val in level0_values:
        # 找到当前顶层索引对应的所有列的位置
        col_positions = [i for i, val in enumerate(level0_cols) if val == level0_val]
        start_col = col_positions[0]
        end_col = col_positions[-1]
        fmt_config = columns_format[level0_val]
        
        # 1. 设置数据列的格式（表头行之后的所有行）
        data_format = book.add_format({
            'align': fmt_config['align'],
            'number_format': fmt_config['number_format'],
            'font_color': fmt_config['font_color'],
            'bold': fmt_config.get('bold', False)
        })
        sheet.set_column(start_col, end_col, fmt_config['width'], data_format)
        
        # 2. 设置顶层表头（第0行）的格式，合并对应列的单元格
        header0_format = book.add_format({
            'align': fmt_config['header_align'],
            'bold': True,
            'border': 2,
            'bg_color': '#f0f0f0'  # 可选：给表头加背景色
        })
        sheet.merge_range(0, start_col, 0, end_col, level0_val, header0_format)
        
        # 3. 设置第二层表头（第1行）的格式
        header1_format = book.add_format({
            'align': fmt_config['header_align'],
            'bold': True,
            'border': 2,
            'bg_color': '#f0f0f0'
        })
        for col in col_positions:
            level1_val = df.columns[col][1]
            sheet.write(1, col, level1_val, header1_format)

关键说明 #

• 避免重复写入：不再手动写入表头内容，而是基于Pandas已写入的表头修改格式，从根源上避免Excel文件损坏。
• 批量列处理：通过顶层索引对应的列范围，一次性为同一分组的列设置格式，效率更高且逻辑清晰。
• 合并表头单元格：用merge_range合并顶层索引对应的表头单元格，符合Excel多层表头的阅读习惯，也避免了重复内容。

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