xlwt doesn't cope with offset-aware datetimes
python...@raf.org
xlwt doesn't expect to receive datetimes with time zones.
what's the correct way for me to deal with this? should i
convert to UTC or just removing the time-zone information?
i.e. what timezone is the excel epoch in and what timezone
would excel display the value in? i'm thinking that just
deleting the time zone information is probably the right
thing to do.
cheers,
raf
python-2.6.1, xlwt-0.7.2
Exception...
File "lib/report.py", line 800, in write_cell
sheet.write(row, col, cell_visible, real_style)
File "/Library/Python/2.6/site-packages/xlwt/Worksheet.py", line 1003, in write
self.row(r).write(c, label, style)
File "/Library/Python/2.6/site-packages/xlwt/Row.py", line 240, in write
date_number = self.__excel_date_dt(label)
File "/Library/Python/2.6/site-packages/xlwt/Row.py", line 95, in __excel_date_dt
delta = date - epoch
TypeError: can't subtract offset-naive and offset-aware datetimes
Adrian Klaver
It would seem the problem is you are trying to work with different datetimes,
one with tz and one without. That is a Python datetime module issue. It will
not allow that.
--
Adrian Klaver
adrian...@gmail.com
John Machin
> xlwt doesn't expect to receive datetimes with time zones.
That's correct. What's the basis of your expectation that it should?
> what's the correct way for me to deal with this? should i
> convert to UTC or just removing the time-zone information?
> i.e. what timezone is the excel epoch in
There is no such thing as "the" Excel epoch. It has *two* epochs. There is
no separate data type in Excel for datetimes. It's a float plus a number
format. There's no provision for a timezone. Read the section on Excel
dates near the start of the xlrd documentation
(https://secure.simplistix.co.uk/svn/xlrd/trunk/xlrd/doc/xlrd.html).
In general, it's news to me that Excel knows what a timezone is. AFAIK
there are no format operators that display timezones, and no non-VBA
functions that deal with timezones.
> and what timezone
> would excel display the value in?
It's always a good idea to have the task specifier try to mock up what
they want in Excel by typing at it before anyone starts writing code ...
> i'm thinking that just
> deleting the time zone information is probably the right
> thing to do.
Yes, well, if all the users are in the same TZ and/or what TZ is used is
clearly displayed somewhere, I suppose so ...
Cheers,
John
python...@raf.org
it is not a datetime module issue. the datetime module is
doing the right thing as it makes no sense to subtract a
datetime with no timezone from a datetime with a time zone.
but it's not me that is trying to work with different
datetimes. i am only working with datetimes that have time
zone information.
however, the xlwt module assumes that datetimes passed to it
do not have time zone information when it creates the
zoneless epoch variable and subtracts it from the datetime
that its client has passed to it (which may or may not have
timezone information).
so it's either an issue for the xlwt module (which could be
changed to strip out the timezone information if that is the
correct thing to do - see patch below) or for its clients
which all have to ensure that timezones passed to xlwt do
not have tzinfo.
i am asking what the best thing for clients to do in this
situation. i have just stripped out the timezone information
before passing the datetimes to xlwt and it looks fine in
openoffice but i haven't checked it in excel yet. I suspect
that it'll be fine there too. i.e. i don't think excel will
be converting from UTC to the local timezone for display.
cheers,
raf
--- Row.py.orig 2009-06-01 22:42:27.000000000 +1000
+++ Row.py 2010-12-01 11:03:40.000000000 +1100
@@ -91,6 +91,9 @@ class Row(object):
date = dt.datetime.combine(dt.datetime(1900, 1, 1), date)
epoch = dt.datetime(1900, 1, 1, 0, 0, 0)
else:
+ # Remove any timezone information
+ if date.tzinfo != None:
+ date = dt.datetime(date.year, date.month, date.day, date.hour, date.minute, date.second, date.microsecond)
epoch = dt.datetime(1899, 12, 31, 0, 0, 0)
delta = date - epoch
xldate = delta.days + float(delta.seconds) / (24*60*60)
python...@raf.org
> On Wed, December 1, 2010 7:59 am, python...@raf.org wrote:
>
> > xlwt doesn't expect to receive datetimes with time zones.
>
> That's correct. What's the basis of your expectation that it should?
just the fact that datetime objects have a tzinfo field that isn't
necessarily None and that the xlwt tutorial didn't mention
that datetime objects passed to it were required to have tzinfo
set to None. it's a simple enough thing to happen if not
warned against doing it.
> > what's the correct way for me to deal with this? should i
> > convert to UTC or just removing the time-zone information?
> > i.e. what timezone is the excel epoch in
>
> There is no such thing as "the" Excel epoch. It has *two* epochs. There is
> no separate data type in Excel for datetimes. It's a float plus a number
> format. There's no provision for a timezone. Read the section on Excel
> dates near the start of the xlrd documentation
> (https://secure.simplistix.co.uk/svn/xlrd/trunk/xlrd/doc/xlrd.html).
>
> In general, it's news to me that Excel knows what a timezone is. AFAIK
> there are no format operators that display timezones, and no non-VBA
> functions that deal with timezones.
i'm not suggesting that excel does know what a timezone is.
i'm just asking if it does. it seems not.
> > and what timezone
> > would excel display the value in?
>
> It's always a good idea to have the task specifier try to mock up what
> they want in Excel by typing at it before anyone starts writing code ...
>
> > i'm thinking that just
> > deleting the time zone information is probably the right
> > thing to do.
>
> Yes, well, if all the users are in the same TZ and/or what TZ is used is
> clearly displayed somewhere, I suppose so ...
thanks.
> Cheers,
> John
cheers,
raf
Adrian Klaver
I would vote against this. Blindly stripping the tzinfo is not a good idea. If
the datetime was pegged to a tz then that information should not be discarded
without thought. I would think it would be up to the programmer to be aware of
what tz the incoming data is versus the existing data and make the appropriate
conversion before submitting it to xlwt. In other words the error thrown serves
a purpose and should not be swallowed.
--
Adrian Klaver
adrian...@gmail.com
python...@raf.org
i agree. i didn't really think it was a good idea for xlwt
to do this even when i wrote it. it was just a thought.
cheers,
raf