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.

Wartow

Have you seen : #16393742 – Judas

Just an FYI, that doesn't work on Google App-Engine because it xlsxwriter tries to save things to a temp directory. – Wartow

Update: Version >= 0.4.8 of XlsxWriter works with the Google App Engine. See this example. – Cuneal

Answer 1

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()

This

Answer 2

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.

Tabes

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. – Wartow

To solve the NotImplementedError, set the cache parameter during Api() create to None and it will disable the default file based cache. – Benita