Python: generate xlsx in memory and stream file download?

Question

for example the following code creates the xlsx file first and then streams it as a download but I'm wondering if it is possible to send the xlsx data as it is being created. For example, imagine if a very large xlsx file needs to be generated, the user has to wait until it is finished and then receive the download, what I'd like is to start the xlsx file download in the user browser, and then send over the data as it is being generated. It seems trivial with a .csv file but not so with an xlsx file.

try:
    import cStringIO as StringIO
except ImportError:
    import StringIO

from django.http import HttpResponse

from xlsxwriter.workbook import Workbook


def your_view(request):
    # your view logic here

    # create a workbook in memory
    output = StringIO.StringIO()

    book = Workbook(output)
    sheet = book.add_worksheet('test')       
    sheet.write(0, 0, 'Hello, world!')
    book.close()

    # construct response
    output.seek(0)
    response = HttpResponse(output.read(), mimetype="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
    response['Content-Disposition'] = "attachment; filename=test.xlsx"

    return response

Marichyasana · Accepted Answer · 2014-12-10 04:57:18Z

8

xlsx format is a zip file that contains several individual files, so you can't create it on the fly and send it out as it is being created.

answered Dec 10, 2014 at 4:57

Marichyasana

3,1621 gold badge21 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

rdrey · Accepted Answer · 2015-03-09 17:21:15Z

6

Are you able to write tempfiles to disk while generating the XLSX?

If you are able to use tempfile you won't be memory bound, which is nice, but the download will still only start when the XLSX writer is done assembling the document.

If you can't write tempfiles, you'll have to follow this example http://xlsxwriter.readthedocs.org/en/latest/example_http_server.html and your code is unfortunately completely memory bound.

Streaming CSV is very easy, on the other hand. Here is code we use to stream any iterator of rows in a CSV response:

import csv
import io


def csv_generator(data_generator):
    csvfile = io.BytesIO()
    csvwriter = csv.writer(csvfile)

    def read_and_flush():
        csvfile.seek(0)
        data = csvfile.read()
        csvfile.seek(0)
        csvfile.truncate()
        return data

    for row in data_generator:
        csvwriter.writerow(row)
        yield read_and_flush()


def csv_stream_response(response, iterator, file_name="xxxx.csv"):

    response.content_type = 'text/csv'
    response.content_disposition = 'attachment;filename="' + file_name + '"'
    response.charset = 'utf8'
    response.content_encoding = 'utf8'
    response.app_iter = csv_generator(iterator)

    return response

answered Mar 9, 2015 at 17:21

rdrey

9,5494 gold badges43 silver badges52 bronze badges

2 Comments

user299709 Over a year ago

if one opens the csv in excel, will it automatically update with new rows as they are added? or do they have to refresh or reopen the file to see the update?

rdrey Over a year ago

The CSV stream will look like it's downloading in the browser, but since there is no file size set at the beginning it just has a progress spinner. Users could open the CSV before it's done downloading, but I think most users would see that the download is still ongoing.

Collectives™ on Stack Overflow

Python: generate xlsx in memory and stream file download?

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related