""" See http://pbpython.com/advanced-excel-workbooks.html for details on this script """ from __future__ import print_function import pandas as pd from xlsxwriter.utility import xl_rowcol_to_cell def format_excel(writer, df_size): """ Add Excel specific formatting to the workbook df_size is a tuple representing the size of the dataframe - typically called by df.shape -> (20,3) """ # Get the workbook and the summary sheet so we can add the formatting workbook = writer.book worksheet = writer.sheets['summary'] # Add currency formatting and apply it money_fmt = workbook.add_format({'num_format': 42, 'align': 'center'}) worksheet.set_column('A:A', 20) worksheet.set_column('B:C', 15, money_fmt) # Add 1 to row so we can include a total # subtract 1 from the column to handle because we don't care about index table_end = xl_rowcol_to_cell(df_size[0] + 1, df_size[1] - 1) # This assumes we start in the left hand corner table_range = 'A1:{}'.format(table_end) worksheet.add_table(table_range, {'columns': [{'header': 'account', 'total_string': 'Total'}, {'header': 'Total Sales', 'total_function': 'sum'}, {'header': 'Average Sales', 'total_function': 'average'}], 'autofilter': False, 'total_row': True, 'style': 'Table Style Medium 20'}) if __name__ == "__main__": sales_df = pd.read_excel('https://github.com/chris1610/pbpython/blob/master/data/sample-salesv3.xlsx?raw=true') sales_summary = sales_df.groupby(['name'])['ext price'].agg(['sum', 'mean']) # Reset the index for consistency when saving in Excel sales_summary.reset_index(inplace=True) writer = pd.ExcelWriter('sales_summary.xlsx', engine='xlsxwriter') sales_summary.to_excel(writer, 'summary', index=False) format_excel(writer, sales_summary.shape) writer.save()