Openpyxl does not handle localized datetime correctly

Issue #499 resolved
Sébastien Diemer
created an issue

Openpyxl does not handle localized datetime correctly.
Depending on the order of the calls to openpyxl.utils.datetime.to_excel, the user might get different results.
This is caused by the @lru_cache decorator used by the to_excel function. When called with the same datetime, localized with different timezones, the cache considers both objects equal (since they effectively are in python).

Here is a script reproducing the bug:

import datetime
import pytz

from openpyxl.utils.datetime import to_excel

date_utc = pytz.utc.localize(datetime.datetime(2015, 7, 24))
date_paris = date_utc.astimezone(pytz.timezone('Europe/Paris'))

print 'Paris', to_excel(date_paris)
print 'UTC', to_excel(date_utc)
>>> Paris 42209.0833333
>>> UTC 42209.0833333

If a rerun the script, inverting the last two lines I get the following output:

>>> UTC 42209.0
>>> Paris 42209.0

Comments (11)

  1. CharlieC

    Thanks for the report. How do you think this should be resolved? Excel itself doesn't care about timezone. It actually doesn't care very much about datetimes.

  2. Sébastien Diemer reporter

    My expectations in this situation would be to have the function return the following:

    print to_excel(date_paris)
    >>> 42209.0833333
    print to_excel(date_utc)
    >>> 42209.0

    That is to say to not consider the timezone information in the datetime and just return the result one would obtain if the datetime was naïve.
    Maybe just doing:

    dt = dt.replace(tzinfo=None)

    at the beginning of the to_excel function would do it.

    of course this conversion to naive datetime shall happen before caching otherwise the problem stays the same.

  3. CharlieC

    I suppose naive datetimes would best reflect what you then have in an Excel file. The other alternative would be always to cast to UTC. What's your use case? Are you getting localised datetimes from something like a database?

  4. CharlieC

    Can you please be more precise about the timezone. ie. if they are in Australia should the file reflect local time only? (your suggestion) Or should the timezone in the database be respected? (cast to the UTC equivalent before converting to Excel).

  5. Sébastien Diemer reporter

    No my users do not care about UTC but are interested in seeing datetimes in their local time.
    This means that I need to convert upfront the datetimes in the correct timezone and provide them afterwards to openpyxl for xlsx export.

  6. Sébastien Diemer reporter

    Yes, I think this is what makes the most sense since excel cannot handle timezone aware datetimes.
    If you want UTC datetimes, you can still make the conversion in the client code.

  7. CharlieC

    Looking closer at this: the standard library doesn't have a concept of localised time so openpyxl's behaviour is correct. Please create a test case using the standard library.

  8. Log in to comment