|Summary:||DateUtil.getJavaDate() fringe cases|
|Component:||POI Overall||Assignee:||POI Developers List <dev>|
Description alex 2010-08-30 07:39:00 UTC
The DateUtil.getJavaDate() service uses a calendar with the local timezone to transform an excel double into a java.util.Date. Consider however the following example: A user fills in "00:00" in a cell and formats it as a time. The user then "drags" the bottom right corner downwards until row "27" is reached. This row will read "02:00:00". Because excel starts counting on the 31st of december 1899, rows 25, 26 and 27 are times expressed on the 1st of january 1900. Suppose that the system default for timezone is "Europe/Paris". If i then wish to format that date with SimpleDateFormat with timezone set to the same default, everything works fine: 1899-12-31 22:00:00 CET 1899-12-31 23:00:00 CET 1900-01-01 00:00:00 CET 1900-01-01 01:00:00 CET 1900-01-01 02:00:00 CET However if we set the timezone of the simpledateformat to "UTC", we get the following: 1899-12-31 22:00:00 UTC 1899-12-31 23:00:00 UTC 1900-01-01 00:50:39 UTC 1900-01-01 01:50:39 UTC If we set the system timezone to UTC (so the calendar uses UTC) then everything works again. Also note the results for default timezone "Europe/Brussels" with formatting for "UTC": 1899-12-31 22:00:00 UTC 1900-01-01 00:00:00 UTC 1900-01-01 01:00:00 UTC 1900-01-01 02:00:00 UTC Can we get an overloaded getJavaDate() call where we can define the timezone for the calendar in question? The attachment is an example xlsx with java code.
Comment 1 Nick Burch 2010-09-06 11:30:04 UTC
Excel dates don't have timezones attached to them As such, when working with the dates, you need to remember this! You should therefore either correct the timezone where known in your code, or ensure that your DateFormats are equally timezone naive. If you try to mix timezone naive and timezone specified things, it'll probably go wrong. There's a reason why Python warns you of this...