Hide or drop index level when saving pandas styler to excel

Question

Following to this question, I'm trying to hide an index level when saving the Styler object to excel, but unfortunately the index argument only accepts True or False, either showing or hiding the whole index.

Code example to build the styled table:

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)
)

Then saving to excel as follows:

with pd.ExcelWriter('filename.xlsx') as writer:
    styler.to_excel(writer, index=True, sheet_name='sheet_name')
    # adjusting columns widths
    threshold_len = 20 # no column should have width more than 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))

With index=True, it produce the following results:

Even though the "index" index level is hidden in the html:

I tried to look for some method like set_column to delete or hide the column after writing to the excel file but with no luck.

Answer 1

One workaround I found for this is to remove the column with openpyxl after saving the file :

from openpyxl import load_workbook

with pd.ExcelWriter('filename.xlsx') as writer:
    styler.to_excel(writer, index=True, sheet_name='sheet_name')

wb = load_workbook('filename.xlsx')
ws = wb['sheet_name']
ws.delete_cols(3)
wb.save('filename.xlsx')