从AWS Glue向S3上传两个文件失败：第二个文件报‘I/O operation on closed file’错误求解决方案

Hey there, let's break down why you're hitting that "I/O operation on closed file" error and fix it up.

What's Causing the Error? #

The core issue is how pd.ExcelWriter interacts with the with statement. When you exit the with pd.ExcelWriter(output, engine='xlsxwriter') as writer: block, the writer automatically closes the underlying output stream—even if you called writer.save() manually. By the time you try the second s3.upload_fileobj call, that stream is already closed, hence the error. Also, your writer.close() line is redundant because the with statement handles closing the writer for you.

Fix Option 1: Cache Excel Content in Memory (Most Robust) #

Instead of reusing the same BytesIO stream twice, read the full Excel content into a bytes variable before the stream gets closed. You can then create fresh BytesIO instances from this cached content for each upload—no more worries about closed handles or stream position issues.

Here's the updated code:

def backup_report(filename):
    with io.BytesIO() as output:
        with pd.ExcelWriter(output, engine='xlsxwriter') as writer:
            # ------- insert metrics that 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']
            writer.save()
            output.seek(0)
            # Cache the full Excel content as bytes
            excel_content = output.read()
    
    # Create new streams for each upload
    with io.BytesIO(excel_content) as file1_stream:
        s3.upload_fileobj(file1_stream, args['bucket_name'], args['key_name'] + f'file1_{rpt_name_start}_{rpt_name_end}.xlsx')
    
    with io.BytesIO(excel_content) as backup_stream:
        s3.upload_fileobj(backup_stream, args['bucket_name'], args['key_name'] + 'file1_backup.xlsx')

Fix Option 2: Upload Both Files Inside the ExcelWriter Context #

If you prefer to keep everything in the same context, you can perform both uploads before the ExcelWriter block closes the stream. Just make sure to reset the stream position with seek(0) before each upload:

def backup_report(filename):
    with io.BytesIO() as output:
        with pd.ExcelWriter(output, engine='xlsxwriter') as writer:
            # ------- insert metrics that 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']
            writer.save()
            
            # First upload
            output.seek(0)
            s3.upload_fileobj(output, args['bucket_name'], args['key_name'] + f'file1_{rpt_name_start}_{rpt_name_end}.xlsx')
            
            # Reset stream position for second upload
            output.seek(0)
            s3.upload_fileobj(output, args['bucket_name'], args['key_name'] + 'file1_backup.xlsx')
    
    # Removed redundant writer.close() — the with statement handles this

Option 1 is my go-to because it decouples Excel generation from the upload process, making the code easier to debug and maintain. Option 2 works too, but you have to be strict about resetting the stream position each time.

内容的提问来源于stack exchange，提问作者Auliya Hidayat