# 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(str_len).max(),  # len of largest item
            str_len(str(series.name))  # len of column name/header
            )) + 2  # adding a little extra space
        worksheet.set_column(idx, idx, max_len)  # set column width
writer.save()

一个中文utf8编码后是占3个字符，所以求长度的函数可以这样写

def str_len(str):
    try:
        row_l=len(str)
        utf8_l=len(str.encode('utf-8'))
        return (utf8_l-row_l)/2+row_l
    except:
        return None
    return None

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

python包含中文字符串长度