How to compile and save multiple Excel files in one Excel file in AWS Glue

Question

I able to generate several Excel files and compile it using pandas.ExcelWriter on my local machine but how do I achieve the same result using AWS Glue?

def backup_report(filename):
    with pd.ExcelWriter(local_excel_file_path + '/{0}.xlsx'.format(filename), engine='xlsxwriter') as writer:
    # -------  insert metrics that x need calculation -------
        op_AdReq_fnl.to_excel(writer, sheet_name='weekly', index=True, startcol=0, startrow=2, header=True)
        op_dirt_imps.to_excel(writer, sheet_name='weekly', index=True, startcol=0, startrow=8, header=False)
        op_prog_imps.to_excel(writer, sheet_name='weekly', index=True, startcol=0, startrow=12, header=False)
        op_hse_imps.to_excel(writer, sheet_name='weekly', index=True, startcol=0, startrow=16, header=False)
        op_FillRt_fnl.to_excel(writer, sheet_name='weekly', index=True, startcol=0, startrow=29, header=False)
        op_dirt_rvn.to_excel(writer, sheet_name='weekly', index=True, startcol=0, startrow=57, header=False)
        op_prog_rvn.to_excel(writer, sheet_name='weekly', index=True, startcol=0, startrow=61, header=False)

        workbook  = writer.book
        worksheet = writer.sheets['weekly']

     # -------  report date -------
        update_dt = date.today().strftime("%d %b %Y")
        worksheet.write(0,0,'Last Update: '+ update_dt)

        report_dt = start_dt + " - " + end_dt
        worksheet.write(0, 1, '('+ report_dt + ')')

    # -------  write index names -------
        metric_format = workbook.add_format({'bold': False, 'font_color': 'black', 'align': 'left', 'valign': 'vcenter'})
        metric_format.set_border()
        metric_format2 = workbook.add_format({'bold': True, 'font_color': 'black', 'align': 'left', 'valign': 'vcenter'})
        metric_format2.set_border()
        metric_format2.set_bg_color('silver')

        fmt_number = workbook.add_format({'num_format': '#,,##0'})
        fmt_percent = workbook.add_format({'num_format': '0%'})

    writer.save()
    writer.close()

Answer 1

I successfully use the pd.Excelwriter in AWS Glue.

def backup_report(filename):
    with io.BytesIO() as output:
        with pd.ExcelWriter(output, engine='xlsxwriter') as writer:
             .
             .
             .
    writer.save()
    writer.close()