Is there a way to auto-adjust Excel column widths with pandas.ExcelWriter?


I am being asked to generate some Excel reports. I am currently using pandas quite heavily for my data, so naturally I would like to use the pandas.ExcelWriter method to generate these reports. However the fixed column widths are a problem.

The code I have so far is simple enough. Say I have a dataframe called "df":

writer = pd.ExcelWriter(excel_file_path, engine="openpyxl")
df.to_excel(writer, sheet_name="Summary")

I was looking over the pandas code, and I don"t really see any options to set column widths. Is there a trick out there in the universe to make it such that the columns auto-adjust to the data? Or is there something I can do after the fact to the xlsx file to adjust the column widths?

(I am using the OpenPyXL library, and generating .xlsx files - if that makes any difference.)

Thank you.

Answer rating: 76

Inspired by user6178746"s answer, I have the following:

# Given a dict of dataframes, for example:
# dfs = {"gadgets": df_gadgets, "widgets": df_widgets}

writer = pd.ExcelWriter(filename, engine="xlsxwriter")
for sheetname, df in dfs.items():  # loop through 'dict' of dataframes
    df.to_excel(writer, sheet_name=sheetname)  # send df to writer
    worksheet = writer.sheets[sheetname]  # pull worksheet object
    for idx, col in enumerate(df):  # loop through all columns
        series = df[col]
        max_len = max((
            series.astype(str).map(len).max(),  # len of largest item
            len(str(  # len of column name/header
            )) + 1  # adding a little extra space
        worksheet.set_column(idx, idx, max_len)  # set column width