Python 3, Django 1.9+: Excel file creation and send on-the-fly with XlsxWriter & BytesIO

views.py

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

Can you test to see?

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

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.

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.

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