Trying To Run Python Pandas Script For Every Sheet In Document With Sheet_name=None But Not Working

For days I'm trying to solve a problem which I have with sheet_name=None, however everything I tried is not working. I need to read a excel document and run this for every sheet in

Solution 1:

Use pd.ExcelWriter to create a new file and to_excel to write on different sheet:

import pandas as pd
import seaborn as sns

dfs = pd.read_excel('products2.xlsx', sheet_name=None, index_col=[0])

with pd.ExcelWriter('output_file.xlsx') as writer:  # <- HERE
    for name, df in dfs.items():
        print(name)
        df.columns = df.columns.str.split('_', expand=True)
        new_data = df.stack(0)
        new_data1 = new_data.eval('status = profit - loss + other')
        new_data2 = new_data1.eval('index = (profit / status) / (loss / status)')

        output = new_data2.unstack(1).swaplevel(0,1, axis=1).sort_index(axis=1)
        order = output.reindex(columns=['profit', 'loss', 'other', 'status', 'index'], level=1)

        rounding = order.round(3)

        cm = sns.light_palette("green", as_cmap=True)
        cc = sns.light_palette("red", as_cmap=True)
        pc = sns.color_palette("vlag", as_cmap=True)
        styler = rounding.style  # Keep Styler for reuse
        green = styler.background_gradient(
            cmap=cm,
            subset=rounding.columns.get_loc_level('profit', level=1)[0]
        )
        red = green.background_gradient(
            cmap=cc,
            subset=rounding.columns.get_loc_level('loss', level=1)[0]
        )

        styler.to_excel(writer, sheet_name=name)  # <- HERE

First sheet:

Second sheet: