Outputting Excel with Django

[`xlwt`][xlwt] is an excellent Python module for generating [Microsoft Excel][msexcel] documents ([`xlrd` is its counterpart][xlrd] for consuming Excel documents). I use it in a [Django][django] Web application so a visitor can export her data as a spreadsheet.

Django’s documentation includes an example of [how to export data in comma-separated values (CSV) format][djangocsv]. CSV has the significant advantage of being a standard Python module as well as being a relatively simple and non-vendor specific format. However there are some disadvantages to using CSV:

1. Values can only be stored as strings or numbers.
2. Unicode text must be explicitly encoded as UTF-8.
3. Users are often unfamiliar with the `.csv` file name extension – “What the hell do I do with this damn you?”

It would be unfriendly of me to expect a user to open a CSV file and then format a column of date strings as proper date values (especially when the user is almost certainly using Excel already). So I choose Excel format over CSV format.

Dates in Excel documents (97/2004 format) are actually stored as numbers. In order to have them appear as dates one must apply a date formatting. You do this by using `xlwt.easyxf` to create a suitable style instance and then pass that when writing the cell data.

A word of advice: do _not_ instantiate style objects more than once! My initial approach created a new style whenever writing a date/time value. Only once I was testing with more than a few dozen rows did I discover that Excel will grow grumpy and complain about too many fonts being open when trying to display the spreadsheet. The correct approach is to have one instance for each different style and then re-use that instance for the appropriate type of value.

Here is an example that writes all objects of one class to a spreadsheet and sends that file to the client’s browser. You could stuff this in a Django view method.

from datetime import datetime, date
from django.http import HttpResponse
from myproject.myapp.models import MyModel
import xlwt

book = xlwt.Workbook(encoding=’utf8′)
sheet = book.add_sheet(‘untitled’)

default_style = xlwt.Style.default_style
datetime_style = xlwt.easyxf(num_format_str=’dd/mm/yyyy hh:mm’)
date_style = xlwt.easyxf(num_format_str=’dd/mm/yyyy’)

values_list = MyModel.objects.all().values_list()

for row, rowdata in enumerate(values_list):
for col, val in enumerate(rowdata):
if isinstance(val, datetime):
style = datetime_style
elif isinstance(val, date):
style = date_style
style = default_style

sheet.write(row, col, val, style=style)

response = HttpResponse(mimetype=’application/vnd.ms-excel’)
response[‘Content-Disposition’] = ‘attachment; filename=example.xls’
return response

That code works a peach with a 30,000 row / 25 column database, taking about a minute to generate a 13 megabyte file on [my lowly iMac G5][g5].

You want to buy me [a new Intel iMac][imac], don’t you? Yes, you do.

[xlwt]: http://pypi.python.org/pypi/xlwt
[xlrd]: http://pypi.python.org/pypi/xlrd
[msexcel]: http://office.microsoft.com/excel
[django]: http://www.djangoproject.com/
[djangocsv]: http://docs.djangoproject.com/en/dev/howto/outputting-csv/
[g5]: http://support.apple.com/kb/SP45
[imac]: http://www.apple.com/imac/

7 thoughts on “Outputting Excel with Django

  1. Glen

    Thank you! This couldn’t have come at a better time.

    Another improvement would be to add a headings variable with all or some columns needed so you can pull the information you want from values_list() and insert a heading row for the excel sheet.

    Example here: http://dpaste.com/hold/139260/

  2. Jorge

    Hi, xlwt module work very in my django application while it has in development server; but when install in a production server with apache-modpython, the reference to : ‘import xlwt ‘, fire the debug error “No module named xlwt…”

    If i run the python shell, the import xlwt command works fine.

    In the apache location, in my pythonpath i add the directory where is found (‘/usr/local/lib/python2.6/dist-packages/xlwt’) but …..not work

    Solutions?, thanks in advance.

  3. david Post author

    You have the right idea about adding the path to PythonPath in your Apache config, but you may have the configuration slightly wrong. What is the exact Apache/Django config? You probably want something like:

    PythonPath "['/usr/local/lib/python2.6/dist-packages'] + sys.path"

    This would go in the section of your Apache conf where you have configured your Django project.

    Or it could be that your mod_python module is using a different installed version of Python (different to what you use from the shell).

  4. Michael Rooney

    Thanks @Glen for attempting to help with column headers, though unfortunately that dpaste is gone.

    To add column headers, put these two lines after “values_list = …”:

    headers = [f.name for f in MyModel._meta.fields]
    values_list = [headers] + list(values_list)

  5. gerard

    Hi, I have in my model a field as a DateTime, I added to util (datetime.datetime, xlwt.easyxf(num_format_str='DD/MM/YYYY HH:MM:SS')),, but when i try to export it i get an error: can't subtract offset-naive and offset-aware datetimes. Iam using Django 1.7, Python 3.4 and PSQL 9.1. Anyone can help me with this?

Leave a Reply

Your email address will not be published. Required fields are marked *