"""
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_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()
                                                   'total_function': 'average'}],
                                      'autofilter': False,