XlsxWriter object save as http response to create download in Django

Question

XlsxWriter object save as http response to create download in Django?

Pashm

Answer 1

I think you're asking about how to create an excel file in memory using xlsxwriter and return it via HttpResponse. Here's an example:

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

Hourglass

That's nice. If you get some time could you flesh it out into an example program that I could add to the XlsxWriter examples. – Collie

Sure, will do. By the way, strictly speaking, xlsxwriter doesn't really stores the workbook in memory - it uses tempfile.tempdir internally - this cause problems on google app engine: please see #17014555. – Hourglass

Thanks. I saw that other post. That is what brought me here. :-) A fully in-memory file assembly without access to a directory would be tricky but probably doable. I'll put it up high on the TODO list. – Collie

@jmcnamara, I've sent you a pull request. See what else should I include in it, thanks. – Hourglass

why are you using 0,0 and then the actual content? any logic, please explalin – Intradermal

@Clayton 0, 0 specifies the cell, in this case A1. – Withdrawn

does not work. __init__() got an unexpected keyword argument 'mimetype' on line: response = HttpResponse(output.read(), mimetype="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet") – Curcuma

Answer 2

A little update on @alecxe response for Python 3 (io.BytesIO instead of StringIO.StringIO) and Django >= 1.5 (content_type instead of mimetype), with the fully in-memory file assembly that has since been implemented by @jmcnamara ({'in_memory': True}) !
Here is the full example :

import io

from django.http.response import HttpResponse

from xlsxwriter.workbook import Workbook


def your_view(request):

    output = io.BytesIO()

    workbook = Workbook(output, {'in_memory': True})
    worksheet = workbook.add_worksheet()
    worksheet.write(0, 0, 'Hello, world!')
    workbook.close()

    output.seek(0)

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

    output.close()

    return response

Conglobate

God bless you for this answer – Sheri

Heads Up: xlsxwriter builds the file using temporary filesystem files, so the reader may find it confusing that using in_memory actually uses StringIO. However, the final file output is determined by the first parameter to Workbook(). If it's a string, a file with that name is created (even if in_memory is used). If it's a StringIO, the final file will be stored in memory. The text in this example helped – Drosophila

Also, it might be good to close output. I don't think it's necessary, but its good practice for memory management. In the StringIO docs they demonstrate closing the object. – Drosophila

you are godsend – Torso

Answer 3

When it comes to Django, you can even do without the whole StringIO shenanigans. HttpResponse behaves just like a StringIO in that respect:

from django.http import HttpResponse
from xlsxwriter.workbook import Workbook

def your_view(request):
    # your view logic here

    # create the HttpResponse object ...
    response = HttpResponse(content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
    response['Content-Disposition'] = "attachment; filename=test.xlsx"

    # .. and pass it into the XLSXWriter
    book = Workbook(response, {'in_memory': True})
    sheet = book.add_worksheet('test')       
    sheet.write(0, 0, 'Hello, world!')
    book.close()

    return response

Addendum: You need to specify {'in_memory': True} or you might get HttpResponse has no attribute seek(). Thanks @Jeb

Shane

I tried (Python 2.7) and it fires 'HttpResponse' object has no attribute 'seek' error anyway. – Hoshi

latest Django version? – Shane

nope - I had to use 1.7.7 - sorry for not mentioning that. – Hoshi

Hey @Radek, I just tried it with your environment: Python2.7 and Django-1.7.7 and -1.7.11 - both worked like a charm. Maybe, if possible, try upgrading your environment? According to djangoproject.com 1.7 is insecure AND no longer supported anyways. – Shane

Hi @nutz, Django 1.7 is unsupported, indeed. However sometimes you get the version to work with without ability to immediate upgrade :( The env is Python 2.7.9, Django 1.7.7, experienced on Windows 10. – Hoshi

Anyway I believe your approach is right and should be followed by anyone reading this and only knowing that such error might be experienced in some outdated config. – Hoshi

exactly what I was looking for. (using django 1.9 / python 3.5) – Minimal

It worked! Thank you very much. Using Django 1.9.11 and Python 2.7.11 – Deplume

Using Python 3, Django 3, and DRF. It's working perfectly fine. – Holcomb

This is the only working answer for me as of Django 3.1.6. The other answers just produce a document with the text object [Object] – Unmade

Answer 4

It's better to follow the official doc from jmcnamara (package developer)

Example: Simple Django class

Okra

Answer 5

I use nodejs with reportProgress: True. My Django code is like this;

output = io.BytesIO()
workbook = xlsxwriter.Workbook(output, {'in_memory': True})
worksheet = workbook.add_worksheet()

worksheet.write(y, x, column_name)
worksheet.write(y, x, column_name)

workbook.close()
output.seek(0)
return FileResponse(output.read(), filename="reservations.xlsx")

If you do this then you can use like this

Scarface

Answer 6

A simple Django View class to write an Excel file using the XlsxWriter module.

import io
from django.http import HttpResponse
from django.views.generic import View
import xlsxwriter


def get_simple_table_data():
    # Simulate a more complex table read.
    return [[1, 2, 3],
            [4, 5, 6],
            [7, 8, 9]]


class MyView(View):

    def get(self, request):

        # Create an in-memory output file for the new workbook.
        output = io.BytesIO()

        # Even though the final file will be in memory the module uses temp
        # files during assembly for efficiency. To avoid this on servers that
        # don't allow temp files, for example the Google APP Engine, set the
        # 'in_memory' Workbook() constructor option as shown in the docs.
        workbook = xlsxwriter.Workbook(output)
        worksheet = workbook.add_worksheet()

        # Get some data to write to the spreadsheet.
        data = get_simple_table_data()

        # Write some test data.
        for row_num, columns in enumerate(data):
            for col_num, cell_data in enumerate(columns):
                worksheet.write(row_num, col_num, cell_data)

        # Close the workbook before sending the data.
        workbook.close()

        # Rewind the buffer.
        output.seek(0)

        # Set up the Http response.
        filename = 'django_simple.xlsx'
        response = HttpResponse(
            output,
            content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
        )
        response['Content-Disposition'] = 'attachment; filename=%s' % filename

        return response

Majunga