How to download xlsxwriter files in browser using BytesIO?

Question

I am generating an excel spreadsheet in python using xlsxwriter in the backend of a webapp, which is then stored on the server. The link to that is then sent to the frontend and downloaded in the browser. Similar to this: How to download excel file that is generated by xlsxwriter in browser with using fastapi?

I was wondering if there is not an easier way using BytesIO to download temporarily instead of processing and storing the file locally and taking up space.

import xlsxwriter
from io import BytesIO

workbook = xlsxwriter.Workbook('download/demo.xlsx')
worksheet = workbook.add_worksheet()

worksheet.set_column('A:A', 20)

bold = workbook.add_format({'bold': True})

worksheet.write('A1', 'Hello')

worksheet.write('A2', 'World', bold)

worksheet.write(2, 0, 123)
worksheet.write(3, 0, 123.456)

workbook.close()

Thank you so much for pointing me in the right direction.

Answer 1

Although it is documented that the filename argument to the constructor of Workbook is a string of the name of the new Excel file to create, a look at the source code reveals that filename is passed directly as the file argument to the constructor of zipfile.ZipFile, which can be a file-like object.

Also note that you can set the in_memory option for the Workbook constructor to avoid creating temporary files on the filesystem:

from fastapi import FastAPI
from fastapi.responses import Response

app = FastAPI()

@app.get("/your-path")
def your_api():
    file = BytesIO()
    workbook = xlsxwriter.Workbook(file, {'in_memory': True})
    worksheet = workbook.add_worksheet()
    ...
    workbook.close()
    return Response(
        content=file.getvalue(),
        media_type='application/vnd.ms-excel',
        headers={'Content-Disposition': 'attachment; filename="demo.xlsx"'}
    )