Using xlsxwriter in Google App Engine for Python

Question

I am wondering if anyone knows how to use xlsxwriter in Google App Engine for Python. The documentation only shows how to open,write and save to a file. I've looked at workarounds using StringIO for other Excel libraries, but they don't seem transferable to xlsxwriter. The main reason seems to be that in other libraries you can supply a StringIO buffer, whereas in xlsxwriter you can only supply a string for the name of the file.

I have a basic solution in place using pyexcelerator but xlsxwriter is so much more feature rich that I'd like to use it, if possible.

Have you seen : http://stackoverflow.com/questions/16393242/xlsxwriter-object-save-as-http-response-to-create-download-in-django — voscausa, Jun 09 '13 at 21:11
Just an FYI, that doesn't work on Google App-Engine because it xlsxwriter tries to save things to a temp directory. — Steve G, Jun 12 '13 at 00:23
Update: Version >= 0.4.8 of XlsxWriter works with the Google App Engine. See [this example](http://xlsxwriter.readthedocs.org/en/latest/example_headers_footers.html). — jmcnamara, Nov 26 '13 at 11:43

score 3 · Answer 1 · edited May 23 '17 at 12:28

UPD: the issue was fixed by the xlsxwriter author (works since 0.4.8 version). See the example.

Relying on my answer in this thread, here's what should work on GAE:

from xlsxwriter.workbook import Workbook

class IndexHandler(webapp2.RequestHandler):
    def get(self):
        book = Workbook(self.response.out)
        sheet = book.add_worksheet('test')
        sheet.write(0, 0, 'Hello, world!')
        book.close()

        # construct response
        self.response.headers['Content-Type'] = 'application/ms-excel'
        self.response.headers['Content-Transfer-Encoding'] = 'Binary'
        self.response.headers['Content-disposition'] = 'attachment; filename="workbook.xls"'

But, it throws an error:

NotImplementedError: Only tempfile.TemporaryFile is available for use

because xlsxwriter tries to write into the temp directory using tempfile.tempdir anyway, see source of _store_workbook method. And, GAE doesn't allow tempfile module to be used in the project: see source, because, as you know, no access to the disk there.

So, a "vicious circle" here. Probably you should think about modifying _store_workbook method to make it work completely in-memory. Or, may be you can mock tempfile.tempdir call on the fly and replace it with your own in-memory object.

Another option is to create an issue on xlsxwriter issue tracker, I bet @jmcnamara has some good ideas on the subject.

Hope that helps.

Thanks. I actually started going to down the tempfile.tempdir rabbit hole, when I decided to post here. I will definitely submit something to their issue tracker. — Steve G, Jun 12 '13 at 00:25
To solve the NotImplementedError, set the *cache* parameter during Api() create to None and it will disable the default file based cache. — 0xc0de, Sep 05 '14 at 07:45

Jay Patel · Accepted Answer · 2019-07-23T13:56:59.977

I recently developed a project on App Engine using python & xlsxwriter. I face a similar issue, and I found a better workaround which doesn't require extra code and works as running on the local system.

We can use the tmp environment - a runtime filesystem in App Engine which exists in the life span of a request/task queue.

while creating a writer object pass the /tmp/file as shown below,

writer = pd.ExcelWriter('/tmp/my_file.xlsx', engine='xlsxwriter')
#
# perform your operations
#
writer.save()

In the subsequent code, you can read the same file and perform desired operations, like send it in the mail or upload it somewhere like...

file_path = '/tmp/'+ filename
with open(file_path, 'rb') as f:
    my_file_data = f.read()
    f.close()

Using xlsxwriter in Google App Engine for Python

2 Answers2

Linked