From d543f9034ac19978938ce54de34e0d0a3bd4eb49 Mon Sep 17 00:00:00 2001 From: Nick Burch Date: Thu, 7 Feb 2008 16:53:23 +0000 Subject: [PATCH] Handle timezones better with cell.setCellValue(Calendar), so now 20:00-03:00, 20:00+00:00 and 20:00+03:00 will all be recorded as 20:00, and not 17:00 / 20:00 / 23:00 (pass a Date not a Calendar for old behaviour) - patch from bug #38641 git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@619502 13f79535-47bb-0310-9956-ffa450edef68 --- src/documentation/content/xdocs/changes.xml | 1 + src/documentation/content/xdocs/status.xml | 1 + .../apache/poi/hssf/usermodel/HSSFCell.java | 9 +++++- .../poi/hssf/usermodel/HSSFDateUtil.java | 32 ++++++++++++++----- .../poi/hssf/usermodel/TestHSSFDateUtil.java | 23 +++++++++++++ 5 files changed, 57 insertions(+), 9 deletions(-) diff --git a/src/documentation/content/xdocs/changes.xml b/src/documentation/content/xdocs/changes.xml index aee0a20997..f0727826c8 100644 --- a/src/documentation/content/xdocs/changes.xml +++ b/src/documentation/content/xdocs/changes.xml @@ -36,6 +36,7 @@ + 38641 - Handle timezones better with cell.setCellValue(Calendar), so now 20:00-03:00, 20:00+00:00 and 20:00+03:00 will all be recorded as 20:00, and not 17:00 / 20:00 / 23:00 (pass a Date not a Calendar for old behaviour) 44373 - Have HSSFDateUtil.isADateFormat recognize more formats as being dates 37923 - Support for Excel hyperlinks Implement hashCode() and equals(obj) on HSSFFont and HSSFCellStyle diff --git a/src/documentation/content/xdocs/status.xml b/src/documentation/content/xdocs/status.xml index 084aba2afe..951b9df606 100644 --- a/src/documentation/content/xdocs/status.xml +++ b/src/documentation/content/xdocs/status.xml @@ -33,6 +33,7 @@ + 38641 - Handle timezones better with cell.setCellValue(Calendar), so now 20:00-03:00, 20:00+00:00 and 20:00+03:00 will all be recorded as 20:00, and not 17:00 / 20:00 / 23:00 (pass a Date not a Calendar for old behaviour) 44373 - Have HSSFDateUtil.isADateFormat recognize more formats as being dates 37923 - Support for Excel hyperlinks Implement hashCode() and equals(obj) on HSSFFont and HSSFCellStyle diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java b/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java index 646efe3103..d07e7937fc 100644 --- a/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java +++ b/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java @@ -546,6 +546,13 @@ public class HSSFCell /** * set a date value for the cell. Excel treats dates as numeric so you will need to format the cell as * a date. + * + * This will set the cell value based on the Calendar's timezone. As Excel + * does not support timezones this means that both 20:00+03:00 and + * 20:00-03:00 will be reported as the same value (20:00) even that there + * are 6 hours difference between the two times. This difference can be + * preserved by using setCellValue(value.getTime()) which will + * automatically shift the times to the default timezone. * * @param value the date value to set this cell to. For formulas we'll set the * precalculated value, for numerics we'll set its value. For othertypes we @@ -553,7 +560,7 @@ public class HSSFCell */ public void setCellValue(Calendar value) { - setCellValue(value.getTime()); + setCellValue( HSSFDateUtil.getExcelDate(value, this.book.isUsing1904DateWindowing()) ); } /** diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFDateUtil.java b/src/java/org/apache/poi/hssf/usermodel/HSSFDateUtil.java index d0ad798afa..0e3d1ee54c 100644 --- a/src/java/org/apache/poi/hssf/usermodel/HSSFDateUtil.java +++ b/src/java/org/apache/poi/hssf/usermodel/HSSFDateUtil.java @@ -70,9 +70,25 @@ public class HSSFDateUtil public static double getExcelDate(Date date, boolean use1904windowing) { Calendar calStart = new GregorianCalendar(); calStart.setTime(date); // If date includes hours, minutes, and seconds, set them to 0 - - if ((!use1904windowing && calStart.get(Calendar.YEAR) < 1900) || - (use1904windowing && calStart.get(Calendar.YEAR) < 1904)) + return internalGetExcelDate(calStart, use1904windowing); + } + /** + * Given a Date in the form of a Calendar, converts it into a double + * representing its internal Excel representation, which is the + * number of days since 1/1/1900. Fractional days represent hours, + * minutes, and seconds. + * + * @return Excel representation of Date (-1 if error - test for error by checking for less than 0.1) + * @param date the Calendar holding the date to convert + * @param use1904windowing Should 1900 or 1904 date windowing be used? + */ + public static double getExcelDate(Calendar date, boolean use1904windowing) { + // Don't alter the supplied Calendar as we do our work + return internalGetExcelDate( (Calendar)date.clone(), use1904windowing ); + } + private static double internalGetExcelDate(Calendar date, boolean use1904windowing) { + if ((!use1904windowing && date.get(Calendar.YEAR) < 1900) || + (use1904windowing && date.get(Calendar.YEAR) < 1904)) { return BAD_DATE; } else { @@ -83,12 +99,12 @@ public class HSSFDateUtil // 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 fraction = (((date.get(Calendar.HOUR_OF_DAY) * 60 + + date.get(Calendar.MINUTE) + ) * 60 + date.get(Calendar.SECOND) + ) * 1000 + date.get(Calendar.MILLISECOND) ) / ( double ) DAY_MILLISECONDS; - calStart = dayStart(calStart); + Calendar calStart = dayStart(date); double value = fraction + absoluteDay(calStart, use1904windowing); diff --git a/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDateUtil.java b/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDateUtil.java index 08874399eb..38078d9df6 100644 --- a/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDateUtil.java +++ b/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDateUtil.java @@ -196,6 +196,29 @@ public class TestHSSFDateUtil } } + /** + * Tests that we deal with timezones properly + */ + public void testCalendarConversion() { + GregorianCalendar date = new GregorianCalendar(2002, 0, 1, 12, 1, 1); + Date expected = date.getTime(); + double expectedExcel = HSSFDateUtil.getExcelDate(expected); + + // Iteratating over the hours exposes any rounding issues. + for (int hour = -12; hour <= 12; hour++) + { + String id = "GMT" + (hour < 0 ? "" : "+") + hour + ":00"; + date.setTimeZone(TimeZone.getTimeZone(id)); + date.set(Calendar.HOUR_OF_DAY, 12); + double excelDate = HSSFDateUtil.getExcelDate(date, false); + Date javaDate = HSSFDateUtil.getJavaDate(excelDate); + + // Should match despite timezone + assertEquals("Checking timezone " + id, expected.getTime(), javaDate.getTime()); + } + } + + /** * Tests that we correctly detect date formats as such */ -- 2.39.5