Is there a way to auto-adjust Excel column widths with pandas.ExcelWriter?
Inspired by user6178746's answer, I have the following:
# Given a dict of dataframes, for example:# 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()
I'm posting this because I just ran into the same issue and found that the official documentation for Xlsxwriter and pandas still have this functionality listed as unsupported. I hacked together a solution that solved the issue i was having. I basically just iterate through each column and use worksheet.set_column to set the column width == the max length of the contents of that column.
One important note, however. This solution does not fit the column headers, simply the column values. That should be an easy change though if you need to fit the headers instead. Hope this helps someone :)
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)#Output some SQL Server data into a dataframemy_sql_query = """ SELECT * FROM dbo.my_table """my_dataframe = pd.read_sql_query(my_sql_query,con=read_engine)#Set destination directory to save excel.xlsFilepath = r'H:\my_project' + "\\" + 'my_file_name.xlsx'writer = pd.ExcelWriter(xlsFilepath, engine='xlsxwriter')#Write excel to file using pandas to_excelmy_dataframe.to_excel(writer, startrow = 1, sheet_name='Sheet1', index=False)#Indicate workbook and worksheet for formattingworkbook = writer.bookworksheet = writer.sheets['Sheet1']#Iterate through each column and set the width == the max length in that column. A padding length of 2 is also added.for i, col in enumerate(my_dataframe.columns): # find length of column i column_len = my_dataframe[col].astype(str).str.len().max() # Setting the length if the column header is larger # than the max column value length column_len = max(column_len, len(col)) + 2 # set the column length worksheet.set_column(i, i, column_len)writer.save()
There is a nice package that I started to use recently called StyleFrame.
it gets DataFrame and lets you to style it very easily...
by default the columns width is auto-adjusting.
for example:
from StyleFrame import StyleFrameimport pandas as pddf = pd.DataFrame({'aaaaaaaaaaa': [1, 2, 3], 'bbbbbbbbb': [1, 1, 1], 'ccccccccccc': [2, 3, 4]})excel_writer = StyleFrame.ExcelWriter('example.xlsx')sf = StyleFrame(df)sf.to_excel(excel_writer=excel_writer, row_to_add_filters=0, columns_and_rows_to_freeze='B2')excel_writer.save()you can also change the columns width:
sf.set_column_width(columns=['aaaaaaaaaaa', 'bbbbbbbbb'], width=35.3)UPDATE 1
In version 1.4 best_fit argument was added to StyleFrame.to_excel.See the documentation.
UPDATE 2
Here's a sample of code that works for StyleFrame 3.x.x
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()