Conditional formatting under xlsxwriter enginer

Question

I have six different values in dataframe column 'index_val' that I have imported to a each new worksheets for the same workbook.

df_dict = {}

    for zone in df['index_val'].unique():
        zone_df = df[df['index_val'] == zone]
        df_dict[zone] = zone_df

    def save_xlsx(df_dict, path):
        with pd.ExcelWriter(path) as writer:
            for key in df_dict:
                df_dict[key].to_excel(writer, key, index=False)
            writer.save()

    save_xlsx(df_dict, 'report.xlsx')

The output produced in Excel sheet hasn't been formatted to fit the column width. Say, values in column 1 require a column width of 92, and values in column B are of width 16. I tried using the set_column method, but since the sheet names are stored as a dictionary, it's throwing back an error.

A chuck of that data is like this:

name_char   index_val  count    
name1       A          36
name2       A          38
name3       B          76
name4       C          12
name3       C          25
name6       F          42

There will be 6 sheets, each having names from index_val. Each sheet will have first column name_char, and second column as unique index_val. What do I like to have is to set the first column width to 92, and second column to 16. Or, is there a process to set the width automatically for each columns based on their maximum column value?

Answer 1

You can do the following:

df_dict = {}

for zone in df['index_val'].unique():
    zone_df = df[df['index_val'] == zone]
    df_dict[zone] = zone_df

def save_xlsx(df_dict, path):
    with pd.ExcelWriter(path) as writer:
        for key in df_dict:
            df_dict[key].to_excel(writer, key, index=False)
            workbook  = writer.book
            worksheet = writer.sheets[key]
            worksheet.set_column(1, 1, 92)
            worksheet.set_column(2, 2, 16)
        writer.save()

save_xlsx(df_dict, 'report.xlsx')