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

Question

I need to provide Excel formatted data from a django database for visitors.

The only way I can think of is through the following steps:

• Retrieve data from a database.
• Wrap it with a Workbook object from openpyxl .
• Save it somewhere temporarily.
• Read it again as "rb".
• excel mime return image.
• Delete excel file on disk. (Is it useless now?)

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

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

Answer 1

You really don't need to save data anywhere on disk; Openpyxl has a way to do this, although this is not well documented. Once upon a time, I created something like this using xlwt, but recently I also created something similar in the Falcon environment using openpyxl.

Combining the two, your code will look something like this:

 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 creating larger files, I would recommend using StreamingHttpResponse, but I'm sure this will at least help you.

This is just a piece of code based on the merger of the two projects that I worked on, so it may not be entirely correct. It should be pretty close though. The output in Falcon looked like this:

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

UPDATE: Now this is much simpler since I completely rewrote my old django-excel-response library using openpyxl! Now it can 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 HttpResponse ! Minimum documentation included, improvements / suggestions welcome. :)

Answer 2

it worked for me

 from openpyxl import Workbook, load_workbook from openpyxl.writer.excel import save_virtual_workbook wb = Workbook() ... response = HttpResponse(content=save_virtual_workbook(wb), content_type='application/ms-excel') response['Content-Disposition'] = 'attachment; filename=Inform.xlsx' return response