From 622698f590fc8b79baa9fd7cab13256e5aac9a78 Mon Sep 17 00:00:00 2001 From: Glen Stampoultzis Date: Wed, 4 Aug 2004 00:43:47 +0000 Subject: [PATCH] Applied patch 27574 When setting a cell's value to the date 2004-03-28 08:00 it is presented as 07:00 in Excel. This only happens on the days when DST starts or ends. git-svn-id: https://svn.apache.org/repos/asf/jakarta/poi/branches/REL_2_BRANCH@353576 13f79535-47bb-0310-9956-ffa450edef68 --- .../poi/hssf/usermodel/HSSFDateUtil.java | 25 ++- .../poi/hssf/usermodel/TestHSSFDateUtil.java | 156 +++++++++++++++--- 2 files changed, 156 insertions(+), 25 deletions(-) diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFDateUtil.java b/src/java/org/apache/poi/hssf/usermodel/HSSFDateUtil.java index f8efbc9785..985ba7648c 100644 --- a/src/java/org/apache/poi/hssf/usermodel/HSSFDateUtil.java +++ b/src/java/org/apache/poi/hssf/usermodel/HSSFDateUtil.java @@ -33,6 +33,7 @@ import java.util.GregorianCalendar; * @author Michael Harhen * @author Glen Stampoultzis (glens at apache.org) * @author Dan Sherman (dsherman at isisph.com) + * @author Hack Kampbjorn (hak at 2mba.dk) */ public class HSSFDateUtil @@ -68,9 +69,19 @@ public class HSSFDateUtil } else { + // Because of daylight time saving we cannot use + // date.getTime() - calStart.getTimeInMillis() + // as the difference in milliseconds between 00:00 and 04:00 + // can be 3, 4 or 5 hours but Excel expects it to always + // be 4 hours. + // E.g. 2004-03-28 04:00 CEST - 2004-03-28 00:00 CET is 3 hours + // and 2004-10-31 04:00 CET - 2004-10-31 00:00 CEST is 5 hours + double fraction = (((calStart.get(Calendar.HOUR_OF_DAY) * 60 + + calStart.get(Calendar.MINUTE) + ) * 60 + calStart.get(Calendar.SECOND) + ) * 1000 + calStart.get(Calendar.MILLISECOND) + ) / ( double ) DAY_MILLISECONDS; calStart = dayStart(calStart); - double fraction = (date.getTime() - calStart.getTime().getTime()) - / ( double ) DAY_MILLISECONDS; return fraction + ( double ) absoluteDay(calStart) - CAL_1900_ABSOLUTE; @@ -96,10 +107,20 @@ public class HSSFDateUtil * Given an Excel date with either 1900 or 1904 date windowing, * converts it to a java.util.Date. * + * NOTE: If the default TimeZone in Java uses Daylight + * Saving Time then the conversion back to an Excel date may not give + * the same value, that is the comparison + * excelDate == getExcelDate(getJavaDate(excelDate,false)) + * is not always true. For example if default timezone is + * Europe/Copenhagen, on 2004-03-28 the minute after + * 01:59 CET is 03:00 CEST, if the excel date represents a time between + * 02:00 and 03:00 then it is converted to past 03:00 summer time + * * @param date The Excel date. * @param use1904windowing true if date uses 1904 windowing, * or false if using 1900 date windowing. * @return Java representation of the date, or null if date is not a valid Excel date + * @see java.util.TimeZone */ public static Date getJavaDate(double date, boolean use1904windowing) { if (isValidExcelDate(date)) { diff --git a/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDateUtil.java b/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDateUtil.java index 66a5ff47dd..0880617ed9 100644 --- a/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDateUtil.java +++ b/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDateUtil.java @@ -1,27 +1,29 @@ /* ==================================================================== - Copyright 2002-2004 Apache Software Foundation +Copyright 2002-2004 Apache Software Foundation - Licensed under the Apache License, Version 2.0 (the "License"); - you may not use this file except in compliance with the License. - You may obtain a copy of the License at +Licensed under the Apache License, Version 2.0 (the "License"); +you may not use this file except in compliance with the License. +You may obtain a copy of the License at - http://www.apache.org/licenses/LICENSE-2.0 +http://www.apache.org/licenses/LICENSE-2.0 - Unless required by applicable law or agreed to in writing, software - distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the License for the specific language governing permissions and - limitations under the License. +Unless required by applicable law or agreed to in writing, software +distributed under the License is distributed on an "AS IS" BASIS, +WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +See the License for the specific language governing permissions and +limitations under the License. ==================================================================== */ - + package org.apache.poi.hssf.usermodel; import junit.framework.TestCase; import java.util.Date; +import java.util.Calendar; import java.util.GregorianCalendar; +import java.util.TimeZone; /** * Class TestHSSFDateUtil @@ -29,11 +31,12 @@ import java.util.GregorianCalendar; * * @author * @author Dan Sherman (dsherman at isisph.com) + * @author Hack Kampbjorn (hak at 2mba.dk) * @version %I%, %G% */ public class TestHSSFDateUtil - extends TestCase + extends TestCase { public TestHSSFDateUtil(String s) { @@ -45,25 +48,25 @@ public class TestHSSFDateUtil */ public void testDateConversion() - throws Exception + throws Exception { // Iteratating over the hours exposes any rounding issues. for (int hour = 0; hour < 23; hour++) { GregorianCalendar date = new GregorianCalendar(2002, 0, 1, - hour, 1, 1); + hour, 1, 1); double excelDate = - HSSFDateUtil.getExcelDate(date.getTime()); + HSSFDateUtil.getExcelDate(date.getTime()); assertEquals("Checking hour = " + hour, date.getTime().getTime(), - HSSFDateUtil.getJavaDate(excelDate).getTime()); + HSSFDateUtil.getJavaDate(excelDate).getTime()); } - + // check 1900 and 1904 date windowing conversions double excelDate = 36526.0; - // with 1900 windowing, excelDate is Jan. 1, 2000 - // with 1904 windowing, excelDate is Jan. 2, 2004 + // with 1900 windowing, excelDate is Jan. 1, 2000 + // with 1904 windowing, excelDate is Jan. 2, 2004 GregorianCalendar cal = new GregorianCalendar(2000,0,1); // Jan. 1, 2000 Date dateIf1900 = cal.getTime(); cal.add(GregorianCalendar.YEAR,4); // now Jan. 1, 2004 @@ -71,11 +74,118 @@ public class TestHSSFDateUtil Date dateIf1904 = cal.getTime(); // 1900 windowing assertEquals("Checking 1900 Date Windowing", - dateIf1900.getTime(), - HSSFDateUtil.getJavaDate(excelDate,false).getTime()); + dateIf1900.getTime(), + HSSFDateUtil.getJavaDate(excelDate,false).getTime()); // 1904 windowing assertEquals("Checking 1904 Date Windowing", - dateIf1904.getTime(), - HSSFDateUtil.getJavaDate(excelDate,true).getTime()); + dateIf1904.getTime(), + HSSFDateUtil.getJavaDate(excelDate,true).getTime()); } + + /** + * Checks the conversion of a java.util.date to Excel on a day when + * Daylight Saving Time starts. + */ + public void testExcelConversionOnDSTStart() { + TimeZone cet = TimeZone.getTimeZone("Europe/Copenhagen"); + TimeZone.setDefault(cet); + Calendar cal = new GregorianCalendar(2004, Calendar.MARCH, 28); + for (int hour = 0; hour < 24; hour++) { + + // Skip 02:00 CET as that is the Daylight change time + // and Java converts it automatically to 03:00 CEST + if (hour == 2) { + continue; + } + + cal.set(Calendar.HOUR_OF_DAY, hour); + Date javaDate = cal.getTime(); + double excelDate = HSSFDateUtil.getExcelDate(javaDate); + double difference = excelDate - Math.floor(excelDate); + int differenceInHours = (int) (difference * 24 * 60 + 0.5) / 60; + assertEquals("Checking " + hour + " hour on Daylight Saving Time start date", + hour, + differenceInHours); + assertEquals("Checking " + hour + " hour on Daylight Saving Time start date", + javaDate.getTime(), + HSSFDateUtil.getJavaDate(excelDate).getTime()); + } + } + + /** + * Checks the conversion of an Excel date to a java.util.date on a day when + * Daylight Saving Time starts. + */ + public void testJavaConversionOnDSTStart() { + TimeZone cet = TimeZone.getTimeZone("Europe/Copenhagen"); + TimeZone.setDefault(cet); + Calendar cal = new GregorianCalendar(2004, Calendar.MARCH, 28); + double excelDate = HSSFDateUtil.getExcelDate(cal.getTime()); + double oneHour = 1.0 / 24; + double oneMinute = oneHour / 60; + for (int hour = 0; hour < 24; hour++, excelDate += oneHour) { + + // Skip 02:00 CET as that is the Daylight change time + // and Java converts it automatically to 03:00 CEST + if (hour == 2) { + continue; + } + + cal.set(Calendar.HOUR_OF_DAY, hour); + Date javaDate = HSSFDateUtil.getJavaDate(excelDate); + assertEquals("Checking " + hour + " hours on Daylight Saving Time start date", + excelDate, + HSSFDateUtil.getExcelDate(javaDate), oneMinute); + } + } + + /** + * Checks the conversion of a java.util.Date to Excel on a day when + * Daylight Saving Time ends. + */ + public void testExcelConversionOnDSTEnd() { + TimeZone cet = TimeZone.getTimeZone("Europe/Copenhagen"); + TimeZone.setDefault(cet); + Calendar cal = new GregorianCalendar(2004, Calendar.OCTOBER, 31); + for (int hour = 0; hour < 24; hour++) { + cal.set(Calendar.HOUR_OF_DAY, hour); + Date javaDate = cal.getTime(); + double excelDate = HSSFDateUtil.getExcelDate(javaDate); + double difference = excelDate - Math.floor(excelDate); + int differenceInHours = (int) (difference * 24 * 60 + 0.5) / 60; + assertEquals("Checking " + hour + " hour on Daylight Saving Time end date", + hour, + differenceInHours); + assertEquals("Checking " + hour + " hour on Daylight Saving Time start date", + javaDate.getTime(), + HSSFDateUtil.getJavaDate(excelDate).getTime()); + } + } + + /** + * Checks the conversion of an Excel date to java.util.Date on a day when + * Daylight Saving Time ends. + */ + public void testJavaConversionOnDSTEnd() { + TimeZone cet = TimeZone.getTimeZone("Europe/Copenhagen"); + TimeZone.setDefault(cet); + Calendar cal = new GregorianCalendar(2004, Calendar.OCTOBER, 31); + double excelDate = HSSFDateUtil.getExcelDate(cal.getTime()); + double oneHour = 1.0 / 24; + double oneMinute = oneHour / 60; + for (int hour = 0; hour < 24; hour++, excelDate += oneHour) { + cal.set(Calendar.HOUR_OF_DAY, hour); + Date javaDate = HSSFDateUtil.getJavaDate(excelDate); + assertEquals("Checking " + hour + " hours on Daylight Saving Time start date", + excelDate, + HSSFDateUtil.getExcelDate(javaDate), oneMinute); + } + } + + public static void main(String [] args) { + System.out + .println("Testing org.apache.poi.hssf.usermodel.TestHSSFDateUtil"); + junit.textui.TestRunner.run(TestHSSFDateUtil.class); + } + } -- 2.39.5