Monthly Archives: September 2009

Working with Active Directory FILETIME values in Python

[How To Convert a UNIX time_t to a Win32 FILETIME or SYSTEMTIME][kb]:

> Under Win32 platforms, file times are maintained primarily in the form of
> a 64-bit FILETIME structure, which represents the number of 100-nanosecond
> intervals since January 1, 1601 UTC (coordinate universal time).

***UPDATED* New version with fixes by Tim Williams for preserving microseconds. See [here for details][updated].**

It just so happens that [Microsoft Active Directory][msad] uses the same 64-bit value to store some time values. For example [the `accountExpires` attribute][expires] is in this format. Linked below is a module for Python with utility functions for converting between [Python’s datetime instances][datetime] and Microsoft’s FILETIME values.

Very handy if you enjoy querying Active Directory for login accounts that are due to expire. And who wouldn’t enjoy that? On a Monday.

[Download filetimes.py module for converting between FILETIME and `datetime` objects.][filetimes] This code is released under a 2-clause BSD license.

Example usage:

>>> from filetimes import filetime_to_dt, dt_to_filetime, utc
>>> filetime_to_dt(116444736000000000)
datetime.datetime(1970, 1, 1, 0, 0)
>>> filetime_to_dt(128930364000000000)
datetime.datetime(2009, 7, 25, 23, 0)
>>> “%.0f” % dt_to_filetime(datetime(2009, 7, 25, 23, 0))
‘128930364000000000’
>>> dt_to_filetime(datetime(1970, 1, 1, 0, 0, tzinfo=utc))
116444736000000000L
>>> dt_to_filetime(datetime(1970, 1, 1, 0, 0))
116444736000000000L

I even remembered to write tests for once!

[kb]: http://support.microsoft.com/kb/167296
[msad]: http://www.microsoft.com/windowsserver2008/en/us/active-directory.aspx
[datetime]: http://docs.python.org/library/datetime.html
[expires]: http://msdn.microsoft.com/en-us/library/ms675098(VS.85).aspx
[filetimes]: /b/wp-content/filetimes.py
[updated]: http://reliablybroken.com/b/2011/09/free-software-ftw-updated-filetimes-py/

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
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][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/