Python - How to set or autofit the column width in an Excel sheet?

Question

I'm trying to write a pandas DataFrame to multiple Excel sheets, and the sheet names are determined by the "Service Type" column.

In my function, I'm trying to write some code that looks through each column in the Excel worksheets auto-adjusts the width so all of the text in each row is visible.

I think what I have written so far could work, but I'm not sure how to properly identify the sheet_name since I'm looking at a str(index).

This is what I've written so far:

    # Create a final DataFrame called "final_df" where rows have an Error value of 1
    final_df = stacked_df[stacked_df.Error == 1]
    # Creates a Pandas Excel writer using XlsxWriter as the engine
    writer = pd.ExcelWriter(LOGNAME, engine='xlsxwriter')

    # Group the output by "Service type" and save each DataFrame to a seperate sheet
    for index, group_df in final_df.groupby("Service type"):
        group_df.to_excel(writer, sheet_name=str(index), index=False)
    
    # Auto-adjust column's width
    for column in stacked_df:
        column_width = max(stacked_df[column].astype(str).map(len).max(), len(column))
        col_idx = stacked_df.columns.get_loc(column)
        writer.sheets[sheet_name=str(index)].set_column(col_idx, col_idx, column_width)
        
    # Close the Pandas Excel writer and output the Excel file
    writer.save()

This what the Excel sheet looks like:

This is what I want it to look like:

How do I make this work? Thank you.

Look at [wrapping text in excel](https://stackoverflow.com/questions/35745451/python-xlsxwriter-text-wrapping-and-links-styling) , you need to use xlsxwriter, look at the [documentation here](https://xlsxwriter.readthedocs.io/format.html) — ThePyGuy, Jun 13 '21 at 22:43

score 3 · Accepted Answer · answered Jun 14 '21 at 00:28

The type of writer.sheets is dict, where the keys are the names of the sheets and the values are Worksheet objects, so the way you're trying to reference the sheets is not correct.

writer.sheets[sheet_name=str(index)] INCORRECT

writer.sheets[sheet_name_as_string] CORRECT

Beyond that, there seems to be a problem with the logic: the index variable you're trying to use in the second loop is not defined. If you're trying to use the index from the first for-loop, then you should nest the loops.

For example:

writer = pd.ExcelWriter(LOGNAME, engine="xlsxwriter")

for sheet_idx, group_df in data.groupby("Service type"):
    
    # Create a worksheet from current GROUPBY object
    group_df.to_excel(writer, sheet_name=str(sheet_idx), index=False)

    # Loop through columns of current worksheet,
    # and set correct width for each one
    for column in group_df:
        column_width = max(group_df[column].astype(str).map(len).max(), len(column))
        col_idx = group_df.columns.get_loc(column)
        writer.sheets[str(sheet_idx)].set_column(col_idx, col_idx, column_width)

writer.save()

Python - How to set or autofit the column width in an Excel sheet?

1 Answers1