Return openpyxl workbook object as HttpResponse in django. Is it possible?

Question

I need to provide an excel formatted data from django's database to visitors.

The only way I can think of is with these steps:

1. Extract data from database.
2. Wrap it with Workbook object from openpyxl.
3. Save it somewhere temporarily.
4. Read it again as 'rb'.
5. return view with excel's mime type.
6. Delete excel file on disk. (It's useless now right?)

That should do it. But, I think there's another better way to do this. I mean maybe there's a way to return openpyxl object as HttpResponse directly without intermediate file medium.

So, my question here is: is it possible to return openpyxl's Worbook object? (I'm new with openpyxl)

Answer 1

You don't actually need to save the data anywhere on disk; openpyxl has a way to do this, although it's not very well documented. A long while back, I created something like this using xlwt, but I recently also built something similar in the Falcon framework, using openpyxl.

Putting these two together, your code would look something along the lines of:

from django.http import HttpResponse
from openpyxl import Workbook
from openpyxl.writer.excel import save_virtual_workbook


workbook = Workbook()
worksheet = workbook.active

# ... worksheet.append(...) all of your data ...

response = HttpResponse(content=save_virtual_workbook(workbook), mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
response['Content-Disposition'] = 'attachment; filename=myexport.xlsx'
return response

If you are generating larger files, I would recommend looking into using StreamingHttpResponse, but I believe this will at least get you going.

This is just an off-the-cuff snippet based on a merge of the two project I worked on, so it may not be exactly correct. It should be pretty close though. The output in Falcon looked like:

response.content_type = 'application/octet-stream;'
response.set_header('Content-Disposition', 'attachment; filename=myexport.xlsx')
response.body = save_virtual_workbook(workbook)

UPDATE: This is now much easier, as I did a complete rewrite of my old django-excel-response library, using openpyxl! It can now be found here: https://github.com/tarkatronic/django-excel-response

You can install it with pip install django-excel-response, and start using it as an alternative to Django's HttpResponse! There is minimal documentation included, and improvements/suggestions are welcome. :)

Answer 2

Since save_virtual_workbook will be obsolete, I used stream insted.

from openpyxl import Workbook
from tempfile import NamedTemporaryFile

def exportToExcel(request):
   workbook = Workbook()
   ...

   with NamedTemporaryFile() as tmp:
      workbook.save(tmp.name)
      tmp.seek(0)
      stream = tmp.read()

   response = HttpResponse(content=stream, content_type='application/ms-excel', )
   response['Content-Disposition'] = f'attachment; filename=ExportedExcel-{datetime.now().strftime("%Y%m%d%H%M")}.xlsx'
   return response