1、使用worksheet.set_column()设置列宽
遍历每一列并使用worksheet.set_column来设置列宽为该列内容的最大长度，注意这样设置不适合列标题，仅适合列值。
import pandas as pdimport sqlalchemy as saimport urllibread_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数据到dataframemy_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_excelmy_dataframe.to_excel(writer, startrow = 1, sheet_name='Sheet1', index=False)workbook = writer.bookworksheet = 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 widthwriter.save()
2、使用StyleFrame自动调整
from styleframe import StyleFrameimport pandas as pdcolumns = ['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()