pandas openpyxl 设置Excel 列宽自适应

方法一

引入包

import pandas as pd
from openpyxl.utils import get_column_letter
from pandas import ExcelWriter
import numpy as np

自适应函数

def to_excel_auto_column_weight(df: pd.DataFrame, writer: ExcelWriter, sheet_name):
    """DataFrame保存为excel并自动设置列宽"""
    df.to_excel(writer, sheet_name=sheet_name, index=False)
    #  计算表头的字符宽度
    column_widths = (
        df.columns.to_series().apply(lambda x: len(x.encode('gbk'))).values
    )
    #  计算每列的最大字符宽度
    max_widths = (
        df.astype(str).applymap(lambda x: len(x.encode('gbk'))).agg(max).values
    )
    # 计算整体最大宽度
    widths = np.max([column_widths, max_widths], axis=0)
    # 设置列宽
    worksheet = writer.sheets[sheet_name]
    for i, width in enumerate(widths, 1):
        # openpyxl引擎设置字符宽度时会缩水0.5左右个字符，所以干脆+2使左右都空出一个字宽。
        worksheet.column_dimensions[get_column_letter(i)].width = width + 2

数据

df = pd.DataFrame({
    'Region': ['East', 'East', 'South', 'North', 'West', 'South', 'North', 'West', 'West', 'South', 'West', 'South'],
    'Item': ['Apple', 'Apple', 'Orange', 'Apple', 'Apple', 'Pear', 'Pear', 'Orange', 'Grape', 'Pear', 'Grape',
             'Orange'],
    'Volume': [9000, 5000, 9000, 2000, 9000, 7000, 9000, 1000, 1000, 10000, 6000, 3000],
    'Month': ['July', 'July', 'September', 'November', 'November', 'October', 'August', 'December', 'November', 'April',
              'January', 'MaysdfasdfljasjASDFLAJSDFJADSFASFLASDFJASDLFJADSFKADSJF ASFDFDDSFASDFASD']
})

保存

with pd.ExcelWriter(r'/Users/kingggg/Documents/PycharmProjects/tools/others/test.xlsx') as writer:
    to_excel_auto_column_weight(df, writer, f'TEST')

 

方法二

'''列宽自动'''
from styleframe import StyleFrame
import pandas as pd

columns = ['aaaaaaaaaaa', 'bbbbbbbbb', 'ccccccccccc', ]
df = pd.DataFrame(data={
        'aaaaaaaaaaa': [1, 2, 3, ],
        'bbbbbbbbb': [1, 1, 1, ],
        'ccccccccccc': [2, 3, 4, ],
    }, columns=columns,
)
excel_writer = StyleFrame.ExcelWriter('example.xlsx')
sf = StyleFrame(df)
sf.to_excel(
    excel_writer=excel_writer, 
    best_fit=columns,
    columns_and_rows_to_freeze='B2', 
    row_to_add_filters=0,
)
excel_writer.save()

# 执行结果保存    
df_streamline = pd.read_excel('/Users/soymilk/Downloads/bigscreen_report.xlsx')
excel_writer = StyleFrame.ExcelWriter('/Users/soymilk/Downloads/beautiful_report.xlsx')
sf = StyleFrame(df_streamline)
sf.to_excel(
    excel_writer=excel_writer, 
    # best_fit=columns,
    columns_and_rows_to_freeze='C2', 
    row_to_add_filters=0,
)
excel_writer.save()

 

 

原文：https://laowangblog.com/pandas-openpyxl-excel-column-dimensions.html