Django中Pandas DataFrame多重索引最后一列未合并问题

问题分析 #

你当前的代码里，设置多重索引后用df.to_excel(merge_cells=True)导出时，前两列desk、phone能按相同值合并，但最后一列email无法合并。这是因为Pandas默认的merge_cells=True仅对多重索引的上层列做合并处理，最后一层索引的重复值不会自动触发合并，需要手动指定合并规则。

解决方案：用Pandas Styler自定义单元格合并 #

通过Styler的set_table_styles方法，针对索引列的重复值设置合并规则，具体实现代码如下：

修改后的完整代码 #

import io
import pandas as pd
from django.http.response import HttpResponse
from rest_framework import status

# 创建测试DataFrame
test_data = {
    "desk": ["DESK1", "DESK2", "DESK3", "DESK4", "DESK5", "DESK6", "DESK7", "DESK8", "DESK9", "DESK10"],
    "phone": ["111-1111", "111-1111", "111-1111", "111-1111", "444-4444", "444-4444", "111-1111", "111-1111", "123-4567", "123-4567"],
    "email": ["Desk1_Email@email.com", "Desk1_Email@email.com", "Desk3_Email@email.com", "Desk4_Email@email.com", "Desk5@email.com", "Desk5@email.com", "Desk7@email.com", "Desk8@email.com", "Desk9@email.com", "Desk10@email.com"],
    "team1": ["Adam", "xxxx", "Tiana", "", "Gina", "Gina", "Ruby", "Becca", "John", ""],
    "team2": ["", "", "Dime", "", "Ed", "", "", "", "Fa", "Tim"],
}
df = pd.DataFrame(test_data)

# 设置多重索引
groupby_columns = ['desk', 'phone', 'email']
df.set_index(groupby_columns, inplace=True)

# 定义索引列合并逻辑
def merge_index_cells(styler):
    # 遍历每个索引层级
    for level in range(df.index.nlevels):
        idx_values = df.index.get_level_values(level)
        prev_val = None
        start_row = 0
        # 逐行检查重复值，标记合并范围
        for i, val in enumerate(idx_values):
            if val != prev_val:
                if i > start_row:
                    # 设置起始单元格的rowspan，并隐藏中间重复单元格
                    styler.set_table_styles([
                        {'selector': f'tbody tr:nth-child({start_row+1}) td:nth-child({level+1})',
                         'props': [('rowspan', i - start_row)]},
                        *[{'selector': f'tbody tr:nth-child({j+1}) td:nth-child({level+1})',
                           'props': [('display', 'none')]} for j in range(start_row+1, i)]
                    ])
                prev_val = val
                start_row = i
        # 处理最后一组重复值
        if len(idx_values) > start_row:
            styler.set_table_styles([
                {'selector': f'tbody tr:nth-child({start_row+1}) td:nth-child({level+1})',
                 'props': [('rowspan', len(idx_values) - start_row)]},
                *[{'selector': f'tbody tr:nth-child({j+1}) td:nth-child({level+1})',
                   'props': [('display', 'none')]} for j in range(start_row+1, len(idx_values))]
            ])
    return styler

# 应用样式并导出Excel
with io.BytesIO() as buffer:
    styled_df = df.style.pipe(merge_index_cells)
    # 需依赖openpyxl引擎，提前安装：pip install openpyxl
    styled_df.to_excel(buffer, sheet_name="Team Matrix", index=True, engine='openpyxl')
    
    buffer.seek(0)
    return HttpResponse(
        buffer.getvalue(),
        headers={
            "Content-Type": "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
            "Content-Disposition": "attachment; filename=excel-export.xlsx",
        },
        status=status.HTTP_201_CREATED,
    )

关键说明 #

1. 依赖要求：需要安装openpyxl库，它是Styler导出Excel的必需引擎。
2. 合并逻辑：通过遍历每个索引层级的重复值，设置单元格的rowspan属性，并隐藏重复行的对应单元格，实现和前两列一致的合并效果。
3. 兼容性：代码保留了Django HttpResponse的导出逻辑，仅修改Excel导出的样式处理部分，不影响原有业务流程。

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