GAE/webapp2: Serving Excel file created by script using xlwt

I am trying to use google app engine, webapp2 and a script based on xlwt that calls its save method to create a specific excel file in situ and let it be downloaded by the user.

Now I have tried several different approaches according to various websites, but none so far yielded any success. I will outline my tries and if any of you knows how to get it to work, I would be very grateful.

Thanks a lot in advance. If you need any more information, please let me know and I will update it the moment, I see it.

NOTE
xls_create(arg, filename_or_stream) is the function that calls xlwt.save() and creates the excel file to be downloaded.

Try 1: "Using StreamIO"
This was my best try so far. It gave me even an excel file, but for some reason it did not completely "finish the content". It looked like it only wrote about a quarter of what it should have written. On checking my script stand_alone it all worked fine, so I am 100% confident that it was not a problem with my script, but with the code below.

Addendum
It turns out that the main mistake here was that in the line a_list = self.request.get('a_list'), I did not put allow_multiple=True. Because of this only the first value of the list, I was passing over, was being used.

I recommend AlexMartelli's minimal example of how to use xlwt, webapp2 and GAE below to do something similar to me.

class XLSCreator(webapp2.RequestHandler):

    def post(self):
        self.response.headers['Content-Type'] = 'application/vnd.ms-excel'
        a_list = self.request.get('a_list')

        # create a stringIO object
        output = StringIO.StringIO()

        # Create file in memory
        xls_create(a_list, output)

        # Set back to start
        output.seek(0)
        self.response.out.write(output.getvalue())

        # When uncommented, process does not finish
        #output.close()

Try 2:
The next two are desperation tries ;) I picked up from code all over and just stuck it in and tried. Sadly, no luck.

class XLSCreator(webapp2.RequestHandler):

def post(self):

    fname = 'excelfile.xls'
    self.response.headers['Content-Type'] = 'application/vnd.ms-excel'
    self.response.headers['Content-Disposition'] = 'attachment; filename="%s"' % fname
    a_list = self.request.get('a_list')
    self.response.out.write(xls_create(a_list, fname))

Try 3:

class XLSCreator(webapp2.RequestHandler):

def post(self):

    fname = 'excelfile.xls'
    self.response.headers['Content-Type'] = 'application/vnd.ms-excel'
    self.response.headers['Content-Disposition'] = 'attachment; filename="%s"' % fname
    a_list = self.request.get('a_list')

    xls_create(a_list, self.response.out)

Solution

Here's a "hello world" (!) GAE handler trying to do the kind of thing you're talking about: this is main.py, to which app.yaml routes all URLs; I've copied xlwt/*.py into the subdirectory xlwt of the directory in which main.py and app.yaml live.

import webapp2
import StringIO
import xlwt

def makeit():
    workbook = xlwt.Workbook() 
    sheet = workbook.add_sheet("Hello World") 
    sheet.write(0, 0, 'Hello world!')
    out = StringIO.StringIO()
    workbook.save(out)
    return out

class MainHandler(webapp2.RequestHandler):
    def get(self):
        self.response.headers['Content-Type'] = 'application/vnd.ms-excel'
        out = makeit()
        self.response.write(out.getvalue())

app = webapp2.WSGIApplication([
    ('/', MainHandler)
], debug=True)

Visiting the / on this GAE app with my Chrome browser downloads a download.xls file (5632 bytes) which the Mac's Preview app is happy to visualize as the one-cell spreadsheet it is.

Now, please try to modify this minimally until it reproduces the bug you observe (maybe the resulting xls would serialize to more than 32MB, which is documented as the maximum size of an App Engine response?) -- that should help diagnose the root cause of your problem, which, now we know, is not the simple issue of using xlwt.save with a StringIO argument (I also tried the unneeded out.seek(0) and while unneeded it still produces the right result:-).