有没有一种方法可以使用pandas.ExcelWriter自动调整Excel列宽？

def auto_width_columns(df, sheetname):
        workbook = writer.book  
        worksheet= writer.sheets[sheetname] 
    
        for i, col in enumerate(df.columns):
            column_len = max(df[col].astype(str).str.len().max(), len(col) + 2)
            worksheet.set_column(i, i, column_len)

import re
import openpyxl
..
for col in _ws.columns:
    max_lenght = 0
    print(col[0])
    col_name = re.findall('\w\d', str(col[0]))
    col_name = col_name[0]
    col_name = re.findall('\w', str(col_name))[0]
    print(col_name)
    for cell in col:
        try:
            if len(str(cell.value)) > max_lenght:
                max_lenght = len(cell.value)
        except:
            pass
    adjusted_width = (max_lenght+2)
    _ws.column_dimensions[col_name].width = adjusted_width

    for worksheet in writer.sheets.values():
        worksheet.set_column(0,last_column_value, required_width_constant)

import win32com.client as win32

xlApp = win32.Dispatch('Excel.Application')
wb = xlApp.Workbooks.Open(***file path to excel file goes here***)

ws = wb.Worksheets[***name of worksheet trying adjust column width***]
ws.Columns.AutoFit()

注意： Worksheet.Columns 属性表示一个 Range 对象。Autofit 是属于 Range 对象的一种方法。

def write_to_excel(writer, X, sheet_name, sep_only=False):
    #writer=writer object
    #X=dataframe
    #sheet_name=name of sheet
    #sep_only=True:write only as separate excel file, False: write as sheet to the writer object
    if sheet_name=="": 
        print("specify sheet_name!")
    else:
        X.to_excel(f"{output_folder}{prefix_excel_save}_{sheet_name}.xlsx")
        if not sep_only: 
            X.to_excel(writer, sheet_name=sheet_name)
            
            #fix column widths
            worksheet = writer.sheets[sheet_name]  # pull worksheet object
            for idx, col in enumerate(X.columns):  # loop through all columns
                series = X[col]
                max_len = max((
                    series.astype(str).map(len).max(),  # len of largest item
                    len(str(series.name))  # len of column name/header
                    )) + 1  # adding a little extra space
                worksheet.set_column(idx+1, idx+1, max_len)  # set column width (=1 because index = 1)
                
            #fix index width
            max_len=pd.Series(X.index.values).astype(str).map(len).max()+1
            worksheet.set_column(0, 0, max_len)
            
        if sep_only: 
            print(f'{sheet_name} is written as seperate file')
        else:
            print(f'{sheet_name} is written as seperate file')
            print(f'{sheet_name} is written as sheet')
    return writer

调用示例：

writer = write_to_excel(writer, dataframe, "Statistical_Analysis")

for column_cells in sheet.columns:
    new_column_length = max(len(str(cell.value)) for cell in column_cells)
    new_column_letter = (get_column_letter(column_cells[0].column))
    if new_column_length > 0:
        sheet.column_dimensions[new_column_letter].width = new_column_length*1.23

import pandas as pd
from openpyxl.utils import get_column_letter
def autosize_to_excel(self,filename: str, df: pd.DataFrame, sheet_name: str = 'sheetName',**kwargs):
    """
    The function 'autosize_to_excel' takes a DataFrame and saves it to an Excel file, adjusting the
    column widths to fit the data.
    
    :param filename: The filename parameter is a string that specifies the name of the Excel file
    that will be created or overwritten with the data from the DataFrame
    :type filename: str
    :param df: The parameter `df` is a pandas DataFrame that contains the data you want to write to
    the Excel file
    :type df: pd.DataFrame
    :param sheet_name: The parameter "sheet_name" is used to specify the name of the sheet in the
    Excel file where the DataFrame will be written. By default, it is set to 'sheetName', defaults
    to sheetName
    :type sheet_name: str (optional)
    """
    try:
        with pd.ExcelWriter(filename) as writer:
            df.to_excel(writer, sheet_name=sheet_name,**kwargs)

            for column in df:
                column_length = max(df[column].astype(str).map(len).max(), len(column))
                column_letter = get_column_letter(df.columns.get_loc(column) + 1) # Obtenha a letra da coluna
                writer.sheets[sheet_name].column_dimensions[column_letter].width = column_length + 2
    except Exception as err:
        print(err)

def text_length(text):
    """
    Get the effective text length in characters, taking into account newlines
    """
    if not text:
        return 0
    lines = text.split("\n")
    return max(len(line) for line in lines)

def _to_str_for_length(v, decimals=3):
    """
    Like str() but rounds decimals to predefined length
    """
    if isinstance(v, float):
        # Round to [decimal] places
        return str(Decimal(v).quantize(Decimal('1.' + '0' * decimals)).normalize())
    else:
        return str(v)


def auto_adjust_xlsx_column_width(df, writer, sheet_name, margin=3, length_factor=1.0, decimals=3, index=False):

    sheet = writer.sheets[sheet_name]
    _to_str = functools.partial(_to_str_for_length, decimals=decimals)
    # Compute & set column width for each column
    for column_name in df.columns:
        # Convert the value of the columns to string and select the 
        column_length =  max(df[column_name].apply(_to_str).map(text_length).max(), text_length(column_name)) + 5
        # Get index of column in XLSX
        # Column index is +1 if we also export the index column
        col_idx = df.columns.get_loc(column_name)
        if index:
            col_idx += 1
        # Set width of column to (column_length + margin)
        sheet.column_dimensions[openpyxl.utils.cell.get_column_letter(col_idx + 1)].width = column_length * length_factor + margin
    # Compute column width of index column (if enabled)
    if index: # If the index column is being exported
        index_length =  max(df.index.map(_to_str).map(text_length).max(), text_length(df.index.name))
        sheet.column_dimensions["A"].width = index_length * length_factor + margin

def auto_adjust_column_widths(excel_file : "Excel File Path", extra_space = 1) -> None:
    """
    Adjusts column widths of the excel file and replaces it with the adjusted one.
    Adjusting columns is based on the lengths of columns values (including column names).
    Parameters
    ----------
    excel_file :
        excel_file to adjust column widths.
    
    extra_space : 
        extra column width in addition to the value-based-widths
    """

    from openpyxl import load_workbook
    from openpyxl.utils import get_column_letter


    wb = load_workbook(excel_file)

    
    for ws in wb:
        df = pd.DataFrame(ws.values,)

        
        for i,r in (df.astype(str).applymap(len).max(axis=0) + extra_space).iteritems():
            ws.column_dimensions[get_column_letter(i+1)].width = r

    
    wb.save(excel_file)