Format output of excel file written by Pandas. Auto-size, auto-filter, number formats, date-time formats, freeze panels

pandas_excel_formater.py

import xlsxwriter

def excel_formatter(writer, sheet, df, num_format = None, auto_filter = False, auto_fit = False):
    workbook  = writer.book
    worksheet = writer.sheets[sheet]
    cols = list(df.columns)
    max_row, max_col = df.shape
    num_format = num_format if num_format else {}
    
    num_format_types = {
        'int_id': '0', # number no dp
        'int': '#,##0',
        'float': '#,##0.00',
        'pct': '0.0%',
        'gbp': '£#,##0',
    }
    
    for col, fmt_type in num_format.items():
        idx = cols.index(col)
        fmt = num_format_types[fmt_type]
        col = xlsxwriter.utility.xl_col_to_name(idx) # Alphabet representation of col
        formater = workbook.add_format({'num_format': fmt})
        worksheet.set_column(f'{col}:{col}', None, formater)
        
    if auto_filter:
        worksheet.autofilter(0, 0, max_row, max_col - 1)
        
    if auto_fit:
        for i, col in enumerate(cols):
            width = 1.25 * df[col].astype('str').str.len().max()
            worksheet.set_column(i, i, width)
            
# Example
import pandas as pd
# Set datetime formats
writer = pd.ExcelWriter("example.xlsx", engine='xlsxwriter', datetime_format='yyyy-dd-mm hh:mm:ss', date_format='yyyy-dd-mm')

# df1, df2, ect are Pandas DataFrames to be written to the excel file
# Freeze top row
df1.to_excel(writer, sheet_name='Sheet1', index=False, freeze_panes=(1,0))
df2.to_excel(writer, sheet_name='Sheet2', index=False, freeze_panes=(1,0))

# Format one column as a int and another as a GBP (£) currency
excel_formatter(writer, 'Sheet1', df1, num_format = {'col1':'int', 'col2': 'gbp'}, auto_filter = True, auto_fit = True)
excel_formatter(writer, 'Sheet2', df2, auto_filter = True, auto_fit = True)

writer.save()

Other xlsxwriter examples can be found here.