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

Answer 1

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