jmcnamara/XlsxWriter — pull request #163 by baxeico (2014-10-09 11:27:14+00:00)
Title: make worksheet.write_datetime work with aware datetime objects

Consider this simple example:

from xlsxwriter.workbook import Workbook
from datetime import datetime
from pytz import utc

utc_dt = datetime(2002, 10, 27, 6, 0, 0, tzinfo=utc)

workbook = Workbook('test.xlsx')
datetime_format = workbook.add_format({'num_format': 'MM/DD/YYYY HH:MM:SS'})
worksheet = workbook.add_worksheet('Test')
worksheet.write_datetime(0, 0, utc_dt, datetime_format)
workbook.close()

In the current released version (0.5.8) it raises an exception:

Traceback (most recent call last):
File "test_datetime_aware.py", line 10, in
worksheet.write_datetime(0, 0, utc_dt, date_format)
File "/home/augusto/.virtualenvs/mtpl/local/lib/python2.7/site-packages/xlsxwriter/worksheet.py", line 61, in cell_wrapper
return method(self, *args, **kwargs)
File "/home/augusto/.virtualenvs/mtpl/local/lib/python2.7/site-packages/xlsxwriter/worksheet.py", line 664, in write_datetime
number = self._convert_date_time(date)
File "/home/augusto/.virtualenvs/mtpl/local/lib/python2.7/site-packages/xlsxwriter/worksheet.py", line 3285, in _convert_date_time
return datetime_to_excel_datetime(dt_obj, self.date_1904)
File "/home/augusto/.virtualenvs/mtpl/local/lib/python2.7/site-packages/xlsxwriter/utility.py", line 571, in datetime_to_excel_datetime
delta = dt_obj - epoch
TypeError: can't subtract offset-naive and offset-aware datetimes

With my patch the Excel file is created correctly, and the datetime value shown in Libreoffice looks correct to me.

It should be perfectly retro-compatible (naive datetime objects have tzinfo=None), and it should also be correct in terms of timezone handling. AFAIK Excel epoch is not defined in a specific timezone, but it should be considered in the "local" timezone. With my patch the epoch is actually considered in the timezone of the datetime object to convert, so it should be correct.

I would like to hear your comments on this last point though.