Skip to content

Instantly share code, notes, and snippets.

@MrYoda
Last active June 16, 2023 05:03
Show Gist options
  • Save MrYoda/d3b39f565de9c107608d7ec53aa82835 to your computer and use it in GitHub Desktop.
Save MrYoda/d3b39f565de9c107608d7ec53aa82835 to your computer and use it in GitHub Desktop.
Python 3, Django 1.9+: Excel file creation and send on-the-fly with XlsxWriter & BytesIO
import xlsxwriter
from io import BytesIO
from django.http import StreamingHttpResponse
from django.views.generic import View
def get_foo_table_data():
"""
Some table data
"""
return [
[1, 2, 3],
[4, 5, 6],
[7, 8, 9],
]
class MyView(View):
def get(self, request):
data = get_foo_table_data()
# create workbook with worksheet
output = BytesIO()
book = xlsxwriter.Workbook(output)
sheet = book.add_worksheet()
# fill worksheet with foo data
for row, columns in enumerate(data):
for column, cell_data in enumerate(columns):
sheet.write(row, column, cell_data)
book.close() # close book and save it in "output"
output.seek(0) # seek stream on begin to retrieve all data from it
# send "output" object to stream with mimetype and filename
response = StreamingHttpResponse(
output, content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
)
response['Content-Disposition'] = 'attachment; filename=foo.xlsx'
return response
@MrYoda
Copy link
Author

MrYoda commented Mar 28, 2017

Can you test to see?

I have tested it. Seeking really makes sense. Gist fixed and works.

@thaxy
Copy link

thaxy commented Mar 31, 2020

So the whole content of the generated .xlsx file is within the output stream, right? If your excel file is let's say 1GB in size then the machine you are running this on occupies 1GB of RAM. It doesn't make sense to use a StreamingHttpResponse if I am right.

@MrYoda
Copy link
Author

MrYoda commented Mar 31, 2020

So the whole content of the generated .xlsx file is within the output stream, right? If your excel file is let's say 1GB in size then the machine you are running this on occupies 1GB of RAM. It doesn't make sense to use a StreamingHttpResponse if I am right.

Yes, but it is only sample. I think you could use file IO instead of BytesIO to prevent usage of huge RAM amount.

@sagarguptay2j
Copy link

Can we stream the file while writing into the workbook(can have multiple sheets)?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment