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:1.8.1.11)
   Gecko/20071204 Ubuntu/7.10 (gutsy) Firefox/2.0.0.11
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/1.3.26.1a 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 part that you're interested in is that last line, 'Content-Type: image/jpeg'. That line tells the browser what the data that follows means. The browser then decides how to display the data, or what external application to open to display it.

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

Chris McAvoy is a developer for PSC Group LLC in Chicago, Illinois. He specializes in Python and Ruby web applications. He also keeps a blog at http://weblog.lonelylion.com.