Creating Excel Files with Python and Django
Downloading Excel from Django
Now that you can create Excel-compatible files with Python, how do you allow them to be downloaded with Django? It's pretty straightforward, and shows us how to generate and provide just about any kind of binary file. If you can create it, your users can download it.
The secret is in the 'content-type' HTTP header. When your browser requests a file from a server, the transaction looks like this:
GET /wp-content/uploads/2007/10/cropped-dsc_0020.jpg HTTP/1.1 Host: weblog.lonelylion.com User-Agent:Mozilla/5.0 (X11; U; Linux i686; en-US; rv:220.127.116.11) Gecko/20071204 Ubuntu/7.10 (gutsy) Firefox/18.104.22.168 Accept: image/png,*/*;q=0.5 Accept-Language: en-us,en;q=0.5 Accept-Encoding: gzip,deflate Accept-Charset: ISO-8859-1,utf-8;q=0.7,*;q=0.7 Keep-Alive: 300 Connection: keep-alive Referer: http://lonelylion.com/ HTTP/1.x 200 OK Date: Sat, 02 Feb 2008 17:53:58 GMT Server: Apache/1.3.37 (Unix) mod_throttle/3.1.2 DAV/1.0.3 mod_fastcgi/2.4.2 mod_gzip/22.214.171.124a PHP/4.4.8 mod_ssl/2.8.22 OpenSSL/0.9.7e Last-Modified: Thu, 01 Nov 2007 03:22:12 GMT Etag: "798f213-5c88-47294664" Accept-Ranges: bytes Content-Length: 23688 Keep-Alive: timeout=2, max=97 Connection: Keep-Alive Content-Type: image/jpeg
The above example is a jpeg image, which is why the Content-Type is image/jpeg. If you change that header to 'application/ms-excel', the file will be opened with Excel. Changing header types in Django is very easy; here is an example of a Django view that sends the browser a csv file and tells the browser to open it in Excel:
import csv from StringIO import StringIO from django.http import HttpResponse def show_excel(request): # use a StringIO buffer rather than opening a file output = StringIO() w = csv.writer(output) for i in range(10): w.writerow(range(10)) # rewind the virtual file output.seek(0) return HttpResponse(output.read(), mimetype='application/ms-excel')
I made a few modifications to your original csv example; it's using a file-like StringIO object rather than actually opening a file, and it's wrapping the response in the Django HttpResponse object, which is the standard return value object type of a Django view. You're also setting the Content-Type header to 'application/ms-excel' by passing HttpResponse the content_type option.
The above pattern lets you return just about any kind of binary data through the web browser. You can use libraries that generate PDFs, images, sound files, video, whatever...as long as you know the appropriate Content-Type header and can generate the binary data.
Now, apply your technique to outputting an actual Excel file generated by pyExcelerator:
from pyExcelerator import * from django.http import HttpResponse def show_excel(request): wb = Workbook() ws0 = wb.add_sheet('0') for x in range(10): for y in range(10): # writing to a specific x,y ws0.write(x,y,"this is cell %s, %s" % (x,y)) wb.save('output.xls') return HttpResponse(open('output.xls','r').read(), mimetype='application/ms-excel')
Again, it's pretty similar to the previous example of creating an Excel file with pyExcelerator. There's one bit that isn't ideal, though—creating a temporary file to write the data to, then opening it and reading from it. This could cause problems on a high traffic site. The file could become corrupted as one user hits it while another is reading from it. You could attach time stamps to the file name to help with this, but really it would be ideal if you could pass the Workbook save method a file-like object to save to. It isn't currently an option in pyExcelerator, but I'm sure a patch could be offered.
More often than not, I just send the browser a csv file with the Content-Type set to 'application/ms-excel'. The use of StringIO makes for a clean implementation, and most end users don't mind the bit of oddness because the amount of time it takes to build a downloadable spreadsheet is typically less than five minutes. It keeps users happy, and lets them play with their data very quickly. The more they play with it, the better able they'll be to request HTML views of it that they'll actually use. Everyone wins.
About the Author
Page 2 of 2