date handling in xlwt

[Wichert Akkerman]

I'm trying to figure out how to store a date in a generated xls file.
The xlwt documentation says that I can simply use Worksheet.write: "A
datetime.datetime, datetime.date, or datetime.time instance is converted
into Excel date format". When I do this the resulting cell is displayed
as a number when I open the xls file with Numbers or LibreOffice, and
xlrd says the cell type is XL_CELL_NUMBER where I would expect
XL_CELL_DATE. Is that indeed a bug in xlwt?

Wichert.

[John Machin]

No, it's a flaw in the Excel data model. The xlrd docs explain what's going on:

"""
Dates in Excel spreadsheets

In reality, there are no such things. What you have are floating point numbers and pious hope. There are several problems with Excel dates:

(1) Dates are not stored as a separate data type; they are stored as floating point numbers and you have to rely on (a) the "number format" applied to them in Excel and/or (b) knowing which cells are supposed to have dates in them. This module helps with (a) by inspecting the format that has been applied to each number cell; if it appears to be a date format, the cell is classified as a date rather than a number.
"""

You might also like to look at the tutorial that you can get via www.python-excel.org ... in particular page 24 has:

"""
Date
When passed a datetime.datetime, datetime.date or datetime.time, the write
methods will write a Date cell.
The set_cell_date method of the Row class can also be used to write Date cells.
Note: As mentioned earlier, a date is not really a separate type in Excel; if you don't apply a
date format, it will be treated as a number.
"""

Cheers,
John
 

[Wichert Akkerman]

On 2011-12-12 00:23, John Machin wrote:
> No, it's a flaw in the Excel data model. The xlrd docs explain what's
> going on:
>
> """
> Dates in Excel spreadsheets
>
> In reality, there are no such things. What you have are floating point
> numbers and pious hope. There are several problems with Excel dates:
>
> (1) Dates are not stored as a separate data type; they are stored as
> floating point numbers and you have to rely on (a) the "number format"
> applied to them in Excel and/or (b) knowing which cells are supposed to
> have dates in them. This module helps with (a) by inspecting the format
> that has been applied to each number cell; if it appears to be a date
> format, the cell is classified as a date rather than a number.
> """
>
> You might also like to look at the tutorial that you can get via
> www.python-excel.org ... in particular page 24 has:
>
> """
> Date
> When passed a datetime.datetime, datetime.date or datetime.time, the write
> methods will write a Date cell.
> The set_cell_date method of the Row class can also be used to write Date
> cells.
> Note: As mentioned earlier, a date is not really a separate type in
> Excel; if you don't apply a
> date format, it will be treated as a number.
> """

Why doesn't xlwd (and xlrd) do that automatically? I can't think of a
reason not to do that.

Wichert.

--
Wichert Akkerman <wic...@wiggy.net> It is simple to make things.
http://www.wiggy.net/ It is hard to make things simple.

[David Avraamides]

I don't think that would be a good approach. What format should it automatically apply? Short date, long date, date with time, time only, just the month and year? There is no right option so xlrd/xlwt should leave it to the user to make that decision. Explicit is better than implicit, IMO.

--
You received this message because you are subscribed to the Google Groups "python-excel" group.

To post to this group, send an email to python...@googlegroups.com.
To unsubscribe from this group, send email to python-excel+unsubscribe@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/python-excel?hl=en-GB.

[Wichert Akkerman]

On 12/12/2011 02:21 PM, David Avraamides wrote:
> I don't think that would be a good approach. What format should it
> automatically apply? Short date, long date, date with time, time only,
> just the month and year? There is no right option so xlrd/xlwt should
> leave it to the user to make that decision. Explicit is better than
> implicit, IMO.

But isn't any default better than a number? Right now there is no way to
tell that it even is a date.

Wichert.

[David Avraamides]

But you can just do this yourself:

DEFAULT_STYLE = xlwt.XFStyle()

DATE_STYLE = xlwt.XFStyle()

DATE_STYLE.num_format_str = 'M/D/YY'

def default_column_style(val):

    if isinstance(val, (datetime.date, datetime.datetime)):

        return DATE_STYLE

    return DEFAULT_STYLE

...

ws.write(r, c, val, default_column_style(val))

Then you can control exactly what style you want to use for date or any other type.

[Wichert Akkerman]

On 12/12/2011 02:38 PM, David Avraamides wrote:

But you can just do this yourself:

DEFAULT_STYLE = xlwt.XFStyle()

DATE_STYLE = xlwt.XFStyle()

DATE_STYLE.num_format_str = 'M/D/YY'

def default_column_style(val):

    if isinstance(val, (datetime.date, datetime.datetime)):

        return DATE_STYLE

    return DEFAULT_STYLE

...

ws.write(r, c, val, default_column_style(val))

Then you can control exactly what style you want to use for date or any other type.

Of course I can (and it would be very helpful to have this prominently mentioned in the xlwt docs), but that does not change my remark: the default behaviour of xlwt effectively turns any date into a number, making it completely invisible to any reader of the xls file that the cell is supposed to contain a date. I'm arguing that changing xlwt to use a default style with a date format for those cells is an improvement: a reader of the xls file can always see that it contains a cell. At worst the exact format should be different, but that is a simple change for any reader. But that change has now become optional, where it is mandatory now.

Wichert.

[David Avraamides]

I guess it's just a difference of opinion. Excel won't do this for you because there is no native "date" type in Excel. If you enter a string that looks like a date (12/12/2011), Excel will automatically format the cell as a date and store the value 40889.0 but then there is no guesswork on Excel because you've explicitly specified the format of the date. If you enter 40889 into a cell, it will just display it as a number because it doesn't know any better. You can then format the number as a date to see it displayed differently but it's still a number. Note also if you enter an invalid date in a date-like format, Excel will just treat as text (40/40/51).

Maybe xlwt could offer a slightly higher-level interface that would do auto-formatting of dates, but I still think you'd want the user to configure the default mapping of types to styles since there aren't universally good defaults. I guess I'm fine providing that layer myself.

--
You received this message because you are subscribed to the Google Groups "python-excel" group.
To post to this group, send an email to python...@googlegroups.com.

To unsubscribe from this group, send email to python-excel...@googlegroups.com.

[John Machin]

On Monday, December 12, 2011 6:12:30 PM UTC+11, Wichert Akkerman wrote:

Why doesn't xlwd (and xlrd) do that automatically? I can't think of a
reason not to do that.

xlwt: what David A wrote, but see separate response

xlrd: as documented, xlrd bends over backwards parsing number formats to determine whether the cell is intended  to be a date or not. I can't imagine what else you would like xlrd to do. Please explain.

 

[John Machin]

The default format certainly wouldn't be M/D/YY: (1) Y2K! (2) 1/2/2012 would be ambiguous (2) variable number of digits in month is eye-irrtitating.

In any case, it would be better for the format to differ by type:

datetime: 2011-12-13 07:02:59
date: 2011-12-13
time: 07:02:59

[Chris Withers]

On 12/12/2011 19:55, John Machin wrote:
>
> xlrd: as documented, xlrd bends over backwards parsing number formats to
> determine whether the cell is intended to be a date or not. I can't
> imagine what else you would like xlrd to do. Please explain.

People want to get date/datetime/time objects as the cell value.
I think that's a reasonable expectation, but I remember you being
opposed to this?

cheers,

Chris

--
Simplistix - Content Management, Batch Processing & Python Consulting
- http://www.simplistix.co.uk

[John Yeung]

On Thu, Dec 15, 2011 at 6:32 AM, Chris Withers <ch...@simplistix.co.uk> wrote:
> On 12/12/2011 19:55, John Machin wrote:
>>
>> xlrd: as documented, xlrd bends over backwards
>> parsing number formats to determine whether the
>> cell is intended to be a date or not. I can't imagine
>> what else you would like xlrd to do. Please explain.
>
> People want to get date/datetime/time objects as the
> cell value. I think that's a reasonable expectation, but I
> remember you being opposed to this?

I can't speak for John Machin of course, but I personally don't mind
that xlrd doesn't do more with dates. It does tell you if it thinks a
cell is a date, and it provides xldate_as_tuple so if you trust that
the cell is indeed a date, you can create a Python date object rather
easily. On the other hand, if xlrd were to always give you a date
object for such cells, and what you really wanted was the raw value,
it is trickier to get the raw value back.

What I think the OP is really after is not more xlrd functionality,
but rather a default date style to be applied automatically in xlwt
when writing a date value with no explicit style. Personally, I find
this quite reasonable. OP is willing to accept that the default date
format won't necessarily be locale-appropriate, just that it be
clearly recognizable (i.e. human-readable) as a date, so I would go
with ISO.

(Where appropriate, "date" above refers to date, datetime, or time.)

John Y.

[Dexter Aparicio]

I WANT to get the date/datetime/time object as the cell value because the the next app that is feed with the .csv file expects the same convention. But somehow, python is becoming like Microsoft Windows by auto-formatting the value in the cell , and hence, this broke our application big time! From there, I stopped using Python in this particular aspect, and I had to manually do a "Save-as" in MSExcel in order to preserve the values in the date column in the expected convention. 

Whoever is responsible for the particular python module, please do not auto-behave stuff. Thank you that's all amigos!

--
You received this message because you are subscribed to the Google Groups "python-excel" group.

To post to this group, send an email to python...@googlegroups.com.

To unsubscribe from this group, send email to python-excel+unsubscribe@googlegroups.com.

[John Yeung]

On Wed, Dec 21, 2011 at 10:33 PM, Dexter Aparicio <dai...@gmail.com> wrote:
> I WANT to get the date/datetime/time object as the cell
> value because the the next app that is feed with the
> .csv file expects the same convention. But somehow,
> python is becoming like Microsoft Windows by auto-
> formatting the value in the cell , and hence, this broke
> our application big time! From there, I stopped using
> Python in this particular aspect, and I had to manually
> do a "Save-as" in MSExcel in order to preserve the
> values in the date column in the expected convention.

I think you have a misunderstanding of what is actually going on.
Python doesn't autoformat anything, and xlrd and xlwt certainly don't
autoformat anything.

You said the "next app" receives a CSV file... but what is this next
app, and how does it interpret CSV fields? (CSV fields don't
*inherently* have any formatting, though some applications such as
Excel will *assume* formatting when reading CSVs.)

You said you had to resave the file from Excel... so what did the
dates look like in Excel, and what kind of file did you save to?

If you were to get date/datetime/time objects (which you can do very
easily, by the way), what would you do with them, and how would that
help?

John Y.