Return openpyxl workbook object as HttpResponse in django. Is it possible?
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. :)
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