如何优化pd.ExcelWriter(xlsxwriter)的Excel列宽自动适配？

问题场景 #

现有如下pandas DataFrame：

import pandas as pd
from pathlib import Path

df1 = pd.DataFrame({
    'sfassaasfsa_id': [1321352,2211252],
    'SUB':['Phy','Phy'],
    'Revasfasf_Q1':[8215215210,1221500],
    'Revsaffsa_Q2':[6215120,525121215125120],
    'Reaasfasv_Q3':[20,12],
    'Revfsasf_Q4':[1215120,11221252]
})

需要完成：

• a) 根据SUB列结合Reaasfasv_Q3列的唯一组合过滤DataFrame
• b) 将过滤结果保存为多个Excel文件

当前代码可运行，但列宽调整逻辑仅简单对比字符串长度，无法根据内容类型精准适配显示宽度，希望实现Excel列宽的自动适配，同时优化导出逻辑。

当前代码：

column_name = "SUB"
col_name = "Reaasfasv_Q3"
for i,j in dict.fromkeys(zip(df1[column_name], df1[col_name])).keys():
    data_output = df1.query(f"{column_name} == @i & {col_name} == @j")
    if len(data_output) > 0:
        output_path = Path.cwd() / f"{i}_{j}_output.xlsx"
        print("output path is ", output_path)
        writer = pd.ExcelWriter(output_path, engine='xlsxwriter')
        data_output.to_excel(writer,sheet_name='results',index=False)
        for column in data_output:
            column_length = max(data_output[column].astype(str).map(len).max(), len(column))
            col_idx = data_output.columns.get_loc(column)
            writer.sheets['results'].set_column(col_idx, col_idx, column_length)
        writer.save()

优化方案 #

1. 按内容类型适配列宽 #

原列宽计算未考虑Excel中不同数据类型的显示差异（比如数字的实际显示宽度会比字符串长度略宽），可以针对数据类型调整计算逻辑：

• 字符串类型：取内容最大字符串长度和列名长度的较大值，再加1预留边距
• 数字/整数类型：取内容转字符串后的最大长度和列名长度的较大值，再加2适配数字显示边距
• 其他类型（如日期）按字符串逻辑处理

修改后的列宽计算代码片段：

writer = pd.ExcelWriter(output_path, engine='xlsxwriter')
data_output.to_excel(writer, sheet_name='results', index=False)
worksheet = writer.sheets['results']

for column in data_output:
    col_idx = data_output.columns.get_loc(column)
    dtype = data_output[column].dtype
    content_max_len = data_output[column].astype(str).map(len).max()
    header_len = len(column)
    
    # 根据类型调整列宽
    if pd.api.types.is_string_dtype(dtype):
        column_width = max(content_max_len, header_len) + 1
    elif pd.api.types.is_numeric_dtype(dtype):
        column_width = max(content_max_len, header_len) + 2
    else:
        column_width = max(content_max_len, header_len) + 1
    
    worksheet.set_column(col_idx, col_idx, column_width)

writer.close()

2. 简化分组导出逻辑 #

原代码用dict.fromkeys(zip(...))获取唯一分组的逻辑较绕，直接用pandas的groupby更简洁高效：

column_name = "SUB"
col_name = "Reaasfasv_Q3"

# 按指定列组合分组
for (sub_val, q3_val), group_df in df1.groupby([column_name, col_name]):
    output_path = Path.cwd() / f"{sub_val}_{q3_val}_output.xlsx"
    print("output path is ", output_path)
    
    writer = pd.ExcelWriter(output_path, engine='xlsxwriter')
    group_df.to_excel(writer, sheet_name='results', index=False)
    worksheet = writer.sheets['results']
    
    # 自动调整列宽
    for column in group_df:
        col_idx = group_df.columns.get_loc(column)
        dtype = group_df[column].dtype
        content_max_len = group_df[column].astype(str).map(len).max()
        header_len = len(column)
        
        if pd.api.types.is_string_dtype(dtype):
            width = max(content_max_len, header_len) + 1
        elif pd.api.types.is_numeric_dtype(dtype):
            width = max(content_max_len, header_len) + 2
        else:
            width = max(content_max_len, header_len) + 1
        
        worksheet.set_column(col_idx, col_idx, width)
    
    writer.close()

3. 封装通用列宽调整函数（可选） #

如果需要多次复用，可将自动调整列宽的逻辑封装为函数：

def auto_adjust_column_width(writer, df, sheet_name='results'):
    worksheet = writer.sheets[sheet_name]
    for column in df:
        col_idx = df.columns.get_loc(column)
        dtype = df[column].dtype
        content_max_len = df[column].astype(str).map(len).max()
        header_len = len(column)
        
        if pd.api.types.is_string_dtype(dtype):
            width = max(content_max_len, header_len) + 1
        elif pd.api.types.is_numeric_dtype(dtype):
            width = max(content_max_len, header_len) + 2
        else:
            width = max(content_max_len, header_len) + 1
        
        worksheet.set_column(col_idx, col_idx, width)

# 使用示例
for (sub_val, q3_val), group_df in df1.groupby([column_name, col_name]):
    output_path = Path.cwd() / f"{sub_val}_{q3_val}_output.xlsx"
    writer = pd.ExcelWriter(output_path, engine='xlsxwriter')
    group_df.to_excel(writer, sheet_name='results', index=False)
    auto_adjust_column_width(writer, group_df)
    writer.close()

内容的提问来源于stack exchange，提问作者The Great