Python pandas 保存Excel自动调整列宽的方法及示例代码

简介：本文主要介绍Python中，使用pandas.ExcelWriter保存Excel文件数据时，自动判断调整列的宽度方法，以及相关的示例代码。

1、使用worksheet.set_column()设置列宽

遍历每一列并使用worksheet.set_column来设置列宽为该列内容的最大长度，注意这样设置不适合列标题，仅适合列值。

import pandas as pd

import sqlalchemy as sa

import urllib

read_server = 'serverName'

read_database = 'databaseName'

read_params = urllib.quote_plus("DRIVER={SQL Server};SERVER="+read_server+";DATABASE="+read_database+";TRUSTED_CONNECTION=Yes")

read_engine = sa.create_engine("mssql+pyodbc:///?odbc_connect=%s" % read_params)

#输出一些SQL Server数据到dataframe

my_sql_query = """ SELECT * FROM dbo.my_table """

my_dataframe = pd.read_sql_query(my_sql_query,con=read_engine)

#设置保存excel的目标目录

xlsFilepath = r'H:\my_project' + "\\" + 'my_file_name.xlsx'

writer = pd.ExcelWriter(xlsFilepath, engine='xlsxwriter')

#写excel文件使用pandas to_excel

my_dataframe.to_excel(writer, startrow = 1, sheet_name='Sheet1', index=False)

workbook = writer.book

worksheet = writer.sheets['Sheet1']

#遍历每一列并设置width ==该列的最大长度。填充长度也增加了2。

for i, col in enumerate(my_dataframe.columns):

# 求列I的长度

column_len = my_dataframe[col].astype(str).str.len().max()

# 如果列标题较大，则设置长度

# 大于最大列值长度

column_len = max(column_len, len(col)) + 2

# 设置列的长度

worksheet.set_column(i, i, column_len)

writer.save()

或者

# 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(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, idx, max_len) # set column width

writer.save()

2、使用StyleFrame自动调整

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()

原文地址：Python pandas 保存Excel自动调整列宽的方法及示例代码-CJavaPy

相关链接

 您阅读本篇文章共花了：