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.
<code>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()
</code>
<code>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()
</code>
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.
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:
<code>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"'}
)
</code>
<code>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"'}
)
</code>
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"'}
)
1