how to output xlsx generated by Openpyxl to browser?

Question

I was using stackoverflow for a while now and it helped me very often. Now I have a problem I couldn't solve myself or through searching. I'm trying to output my excel file generated by openpyxl in browser as I was doing it with phpexcel. The method appears to be the same, but I only get broken file. My code looks like this:

from openpyxl.workbook import Workbook
from openpyxl.writer.excel import ExcelWriter
from openpyxl.writer.excel import save_virtual_workbook
from openpyxl.cell import get_column_letter
from StringIO import StringIO

print 'Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
print 'Content-Disposition: attachment;filename="results.xlsx"'
print 'Cache-Control: max-age=0\n'

output = StringIO()

wb = Workbook()

ws = wb.worksheets[0]

ws.cell('A1').value = 3.14

wb.save(output)
print output.getvalue()
#print save_virtual_workbook(wb)

I use the version 1.5.8 and python 2.7. None of the approaches works. When I just use it from desktop and not browser it works flawlessly. I would be very thankful for help.

P.S. please don't tell me that using other language or program would be easier. I need to solve this with python.

Solution 1

this is work for me. I use python 2.7 and latest openpyxl and send_file from flask

... code ...

import StringIO
from openpyxl import Workbook
wb = Workbook()
ws = wb.active # worksheet
ws.title = "Excel Using Openpyxl"
c = ws.cell(row=5, column=5)
c.value = "Hi on 5,5"
out = StringIO.StringIO()
wb.save(out)
out.seek(0)

return send_file(out, mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            attachment_filename='xxl.xlsx', as_attachment=True)

Solution 2

output = HttpResponse(mimetype='application/application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
file_name = "Test.xlsx"
output['Content-Disposition'] = 'attachment; filename='+ file_name

wb = Workbook()

ws = wb.worksheets[0]

ws.cell('A1').value = 3.14

wb.save(output)

return output

I used this tips to download my files with openpyxl. Hope that will help

Solution 3

Writing the xlsx output to disk and then serving it up via Apache worked perfectly, but putting it out directly caused errors in Excel and other issues.

I added a couple of extra steps and made one minor change to your code:

buffer=output.getvalue()

In the HTTP headers:

print "Content-Length: " + str(len(buffer))

And used write() instead of print() to push the buffer into the standard output stream:

stdout.write(buffer)

Comments

[seeebek]

Yes I do not have too much experience with web programming in python. My mork is to translate some of this kind of apps from php to python and now I'm stuck with phppyton -> openpyxl. I need the same behavior. which is getting data from another script, sending it to this script putting it in excel file and outputing as download to browser. Possibly without saving it to server before.

[Charlie Clark]

That's terrible aliasing! from io import BytesIO is the way to do this.

[Charlie Clark]

@David the question is specifically about serving up an Excel file.

[Auspex]

+1 for including the Content-Length header. I could not get this to work without it. When I used print save_virtual_workbook(wb), my resulting zipped file had two extra bytes. If I used stdout.write() it had one extra byte. Providing Content-Length fixed it.

[4levels]

This worked perfectly in Django 2.0. After hours of fighting encoding issues, NamedTemporaryFile issues and what more this finally did the trick!