pandas Styler导出Excel时如何隐藏多级索引的指定层级

问题背景 #

将Styler对象保存为Excel文件时，需要隐藏某一层级索引，但目前to_excel方法的index参数仅支持传入True或False布尔值，只能控制全部索引的显示或隐藏，无法单独控制单个索引层级的显隐。

复现用代码 #

构建带样式的多层索引表格 #

import pandas as pd

def multi_highlighter(row, range_colors):
    def find_color(value):
        for color, threshold in range_colors.items():
            if value < threshold:
                return color
        return "white"

    return [f"background-color: {find_color(v)}" for v in row]

range_colors = {"red": 18, "orange": 100}

data = pd.DataFrame({
    "Ex Date": ['2022-06-20', '2022-06-20', '2022-06-20', '2022-06-20', '2022-06-20', '2022-06-20', '2022-07-30', '2022-07-30', '2022-07-30'], 
    "Portfolio": ['UUU-SSS', 'UUU-SSS', 'UUU-SSS', 'RRR-DDD', 'RRR-DDD', 'RRR-DDD', 'KKK-VVV', 'KKK-VVV', 'KKK-VVV'],
    "Position": [120, 90, 110, 113, 111, 92, 104, 110, 110],
    "Strike": [18, 18, 19, 19, 20, 20, 15, 18, 19],
    })
table_styles = [
    {
        'selector': 'table, th, td', 
        'props': [('border', 'thin solid gray')]
    },
    {
        'selector': '', 
        'props': [('border-collapse', 'collapse !important')]
    },
    {
        'selector': "th.level2:not(.col_heading), thead th:first-child.blank",
        'props': [('display', 'None')]
    }
]

styler = (
    data
    .reset_index()
    .set_index(["Ex Date", "Portfolio", "index"])
    .style
    .apply(multi_highlighter, range_colors=range_colors, axis=1)
    .set_table_styles(table_styles, overwrite=False)
)

初始导出代码 #

with pd.ExcelWriter('filename.xlsx') as writer:
    styler.to_excel(writer, index=True, sheet_name='sheet_name')
    # 调整列宽
    threshold_len = 20 # 列宽最大不超过20
    for idx, col in enumerate(styler.data):
        longest_col_cell = styler.data[col].astype(str).str.len().max()
        col_head_len = len(str(col))
        max_len = max(longest_col_cell, col_head_len)
        writer.sheets['sheet_name'].set_column(idx, idx, min(max_len, threshold_len))

现象说明 #

• 设置index=True导出时，Excel会完整显示全部三层索引：
  
• 已经通过CSS样式在HTML渲染结果中隐藏了名为"index"的第三层索引，HTML渲染效果如下：
  
• 尝试查找类似set_column的方法在写入Excel后删除或隐藏对应索引列，初始未找到可行方案。

解决方法 #

使用xlsxwriter作为导出引擎，导出完成后直接调用工作表的列隐藏接口，指定需要隐藏的索引列即可，该方法不会破坏已设置的单元格样式，无需修改Styler对象本身的结构。
修改后的导出代码如下：

# 明确指定engine为xlsxwriter
with pd.ExcelWriter('filename.xlsx', engine='xlsxwriter') as writer:
    styler.to_excel(writer, index=True, sheet_name='sheet_name')
    worksheet = writer.sheets['sheet_name']
    
    # 隐藏第三层索引所在列：列序号从0开始计数，三层索引依次对应0、1、2号列，第三层对应序号2
    worksheet.set_column(2, 2, None, None, {'hidden': True})
    
    # 调整列宽，注意前3列为索引列，普通数据列从第3号位置开始
    threshold_len = 20
    for col_offset, col in enumerate(styler.data.columns):
        col_idx = 3 + col_offset
        longest_col_cell = styler.data[col].astype(str).str.len().max()
        col_head_len = len(str(col))
        max_len = max(longest_col_cell, col_head_len)
        worksheet.set_column(col_idx, col_idx, min(max_len, threshold_len))

如果需要隐藏其他层级的索引，只需要修改set_column的前两个参数（起始列序号、结束列序号）即可，单层级隐藏时两个参数传相同值即可。

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