OpenPyxl如何实现单元格列宽自动适配内容？

当然有对应功能，不同的Excel处理库都提供了实现方式，以下是常见工具的具体用法：

1. OpenPyXL（Python） #

OpenPyXL原生支持auto_size属性，但对中文的适配有时不够理想，手动计算内容长度再设置宽度会更稳妥：

from openpyxl import Workbook
from openpyxl.utils import get_column_letter

wb = Workbook()
ws = wb.active

# 填充示例数据
ws.append(["短标题", "这是一个很长很长的列标题", "中等长度标题"])

# 遍历所有列，计算并设置合适宽度
for col in ws.columns:
    max_length = 0
    column = col[0].column_letter
    for cell in col:
        try:
            cell_length = len(str(cell.value))
            if cell_length > max_length:
                max_length = cell_length
        except:
            pass
    # 增加缓冲避免内容贴边，乘以1.2适配不同字体宽度
    ws.column_dimensions[column].width = (max_length + 2) * 1.2

wb.save("autofit_openpyxl.xlsx")

如果想直接用原生自动适配，可替换遍历代码为：

for col in ws.columns:
    column = col[0].column_letter
    ws.column_dimensions[column].auto_size = True

2. XlsxWriter（Python） #

XlsxWriter的autofit()方法可以完美模拟双击列标的效果，它会基于单元格内容和字体自动计算宽度：

import xlsxwriter

workbook = xlsxwriter.Workbook('autofit_xlsxwriter.xlsx')
worksheet = workbook.add_worksheet()

# 设置字体（会影响自动计算的宽度）
title_format = workbook.add_format({'bold': True, 'font_size': 12})

# 写入标题内容
worksheet.write('A1', '短标题', title_format)
worksheet.write('B1', '这是一个很长很长的列标题', title_format)
worksheet.write('C1', '中等长度标题', title_format)

# 自动调整所有列宽
worksheet.autofit()

workbook.close()

3. Pandas（Python） #

如果用Pandas生成Excel，可结合上述引擎实现自动列宽：

基于OpenPyXL引擎： #

import pandas as pd

df = pd.DataFrame({
    '短标题': [1, 2, 3],
    '这是一个很长很长的列标题': [4, 5, 6],
    '中等长度标题': [7, 8, 9]
})

with pd.ExcelWriter('autofit_pandas_openpyxl.xlsx', engine='openpyxl') as writer:
    df.to_excel(writer, index=False)
    worksheet = writer.sheets['Sheet1']
    
    # 遍历设置自动宽度
    for col in worksheet.columns:
        max_length = 0
        column = col[0].column_letter
        for cell in col:
            cell_length = len(str(cell.value))
            if cell_length > max_length:
                max_length = cell_length
        worksheet.column_dimensions[column].width = max_length + 2

基于XlsxWriter引擎： #

with pd.ExcelWriter('autofit_pandas_xlsxwriter.xlsx', engine='xlsxwriter') as writer:
    df.to_excel(writer, index=False)
    worksheet = writer.sheets['Sheet1']
    worksheet.autofit()

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