Writing Pandas DataFrame to Excel: How to auto-adjust column widths

import numpy as npimport pandas as pdfrom pathlib import Pathfrom typing import Union, Optional, List, Tuplefrom openpyxl import load_workbookfrom openpyxl.utils import get_column_letterdef append_df_to_excel(        filename: Union[str, Path],        df: pd.DataFrame,        sheet_name: str = 'Sheet1',        startrow: int = None,        max_col_width: int = 40,        autofilter: bool = False,        fmt_int: str = "#,##0",        fmt_float: str = "#,##0.00",        fmt_date: str = "yyyy-mm-dd",        fmt_datetime: str = "yyyy-mm-dd hh:mm",        truncate_sheet: bool = False,        **to_excel_kwargs) -> None:    """    Append a DataFrame [df] to existing Excel file [filename]    into [sheet_name] Sheet.    If [filename] doesn't exist, then this function will create it.    @param filename: File path or existing ExcelWriter                     (Example: '/path/to/file.xlsx')    @param df: DataFrame to save to workbook    @param sheet_name: Name of sheet which will contain DataFrame.                       (default: 'Sheet1')    @param startrow: upper left cell row to dump data frame.                     Per default (startrow=None) calculate the last row                     in the existing DF and write to the next row...    @param max_col_width: maximum column width in Excel. Default: 30    @param autofilter: boolean - whether add Excel autofilter or not. Default: True    @param fmt_int: Excel format for integer numbers    @param fmt_float: Excel format for float numbers    @param fmt_date: Excel format for dates    @param fmt_datetime: Excel format for datetime's    @param truncate_sheet: truncate (remove and recreate) [sheet_name]                           before writing DataFrame to Excel file    @param to_excel_kwargs: arguments which will be passed to `DataFrame.to_excel()`                            [can be a dictionary]    @return: None    Usage examples:    >>> append_df_to_excel('d:/temp/test.xlsx', df, autofilter=True,                           freeze_panes=(1,0))    >>> append_df_to_excel('d:/temp/test.xlsx', df, header=None, index=False)    >>> append_df_to_excel('d:/temp/test.xlsx', df, sheet_name='Sheet2',                           index=False)    >>> append_df_to_excel('d:/temp/test.xlsx', df, sheet_name='Sheet2',                           index=False, startrow=25)    >>> append_df_to_excel('d:/temp/test.xlsx', df, index=False,                           fmt_datetime="dd.mm.yyyy hh:mm")    (c) [MaxU](https://stackoverflow.com/users/5741205/maxu?tab=profile)    """    def set_column_format(ws, column_letter, fmt):        for cell in ws[column_letter]:            cell.number_format = fmt    filename = Path(filename)    file_exists = filename.is_file()    # process parameters    first_col = int(to_excel_kwargs.get("index", True)) + 1    sheet_name = to_excel_kwargs.get("sheet_name", "Sheet1")    # ignore [engine] parameter if it was passed    if 'engine' in to_excel_kwargs:        to_excel_kwargs.pop('engine')    with pd.ExcelWriter(        filename.with_suffix(".xlsx"),        engine="openpyxl",        mode="a" if file_exists else "w",        date_format=fmt_date,        datetime_format=fmt_datetime,        **to_excel_kwargs    ) as writer:        if file_exists:            # try to open an existing workbook            writer.book = load_workbook(filename)            # get the last row in the existing Excel sheet            # if it was not specified explicitly            if startrow is None and sheet_name in writer.book.sheetnames:                startrow = writer.book[sheet_name].max_row            # truncate sheet            if truncate_sheet and sheet_name in writer.book.sheetnames:                # index of [sheet_name] sheet                idx = writer.book.sheetnames.index(sheet_name)                # remove [sheet_name]                writer.book.remove(writer.book.worksheets[idx])                # create an empty sheet [sheet_name] using old index                writer.book.create_sheet(sheet_name, idx)            # copy existing sheets            writer.sheets = {ws.title:ws for ws in writer.book.worksheets}        else:            # file doesn't exist, we are creating a new one            startrow = 0        # write out the DataFrame to an ExcelWriter        df.to_excel(writer, sheet_name=sheet_name, startrow=startrow,                    **to_excel_kwargs)        # automatically set columns' width        worksheet = writer.sheets[sheet_name]        for xl_col_no, dtyp in enumerate(df.dtypes, first_col):            col_no = xl_col_no - first_col            width = max(df.iloc[:, col_no].astype(str).str.len().max(),                        len(df.columns[col_no]) + 6)            width = min(max_col_width, width)            # print(f"column: [{df.columns[col_no]} ({dtyp.name})]\twidth:\t[{width}]")            column_letter = get_column_letter(xl_col_no)            worksheet.column_dimensions[column_letter].width = width            if np.issubdtype(dtyp, np.integer):                set_column_format(worksheet, column_letter, fmt_int)            if np.issubdtype(dtyp, np.floating):                set_column_format(worksheet, column_letter, fmt_float)        if autofilter:            worksheet.auto_filter.ref = worksheet.dimensions