xlwt is an excellent Python module for generating Microsoft Excel documents (xlrd is its counterpart for consuming Excel documents). I use it in a 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. 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:
- Values can only be stored as strings or numbers.
- Unicode text must be explicitly encoded as UTF-8.
- 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
else:
style = default_style
sheet.write(row, col, val, style=style)
response = HttpResponse(mimetype='application/vnd.ms-excel')
response['Content-Disposition'] = 'attachment; filename=example.xls'
book.save(response)
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.
You want to buy me a new Intel iMac, don’t you? Yes, you do.
david django, excel
