poi-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From n...@apache.org
Subject svn commit: r619502 - in /poi/trunk/src: documentation/content/xdocs/ java/org/apache/poi/hssf/usermodel/ testcases/org/apache/poi/hssf/usermodel/
Date Thu, 07 Feb 2008 16:53:27 GMT
Author: nick
Date: Thu Feb  7 08:53:23 2008
New Revision: 619502

URL: http://svn.apache.org/viewvc?rev=619502&view=rev
Log:
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

Modified:
    poi/trunk/src/documentation/content/xdocs/changes.xml
    poi/trunk/src/documentation/content/xdocs/status.xml
    poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java
    poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFDateUtil.java
    poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDateUtil.java

Modified: poi/trunk/src/documentation/content/xdocs/changes.xml
URL: http://svn.apache.org/viewvc/poi/trunk/src/documentation/content/xdocs/changes.xml?rev=619502&r1=619501&r2=619502&view=diff
==============================================================================
--- poi/trunk/src/documentation/content/xdocs/changes.xml (original)
+++ poi/trunk/src/documentation/content/xdocs/changes.xml Thu Feb  7 08:53:23 2008
@@ -36,6 +36,7 @@
 
 		<!-- Don't forget to update status.xml too! -->
         <release version="3.1-beta1" date="2008-??-??">
+           <action dev="POI-DEVELOPERS" type="fix">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)</action>
            <action dev="POI-DEVELOPERS" type="fix">44373 - Have HSSFDateUtil.isADateFormat
recognize more formats as being dates</action>
            <action dev="POI-DEVELOPERS" type="add">37923 - Support for Excel hyperlinks</action>
            <action dev="POI-DEVELOPERS" type="add">Implement hashCode() and equals(obj)
on HSSFFont and HSSFCellStyle</action>

Modified: poi/trunk/src/documentation/content/xdocs/status.xml
URL: http://svn.apache.org/viewvc/poi/trunk/src/documentation/content/xdocs/status.xml?rev=619502&r1=619501&r2=619502&view=diff
==============================================================================
--- poi/trunk/src/documentation/content/xdocs/status.xml (original)
+++ poi/trunk/src/documentation/content/xdocs/status.xml Thu Feb  7 08:53:23 2008
@@ -33,6 +33,7 @@
 	<!-- Don't forget to update changes.xml too! -->
     <changes>
         <release version="3.1-beta1" date="2008-??-??">
+           <action dev="POI-DEVELOPERS" type="fix">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)</action>
            <action dev="POI-DEVELOPERS" type="fix">44373 - Have HSSFDateUtil.isADateFormat
recognize more formats as being dates</action>
            <action dev="POI-DEVELOPERS" type="add">37923 - Support for Excel hyperlinks</action>
            <action dev="POI-DEVELOPERS" type="add">Implement hashCode() and equals(obj)
on HSSFFont and HSSFCellStyle</action>

Modified: poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java?rev=619502&r1=619501&r2=619502&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java Thu Feb  7 08:53:23 2008
@@ -546,6 +546,13 @@
     /**
      * 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 <code>setCellValue(value.getTime())</code> 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 void setCellValue(Calendar value)
     {
-        setCellValue(value.getTime());
+        setCellValue( HSSFDateUtil.getExcelDate(value, this.book.isUsing1904DateWindowing())
);
     }
 
     /**

Modified: poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFDateUtil.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFDateUtil.java?rev=619502&r1=619501&r2=619502&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFDateUtil.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFDateUtil.java Thu Feb  7 08:53:23
2008
@@ -70,9 +70,25 @@
     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 @@
 	    // 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);
             

Modified: poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDateUtil.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDateUtil.java?rev=619502&r1=619501&r2=619502&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDateUtil.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDateUtil.java Thu Feb  7
08:53:23 2008
@@ -197,6 +197,29 @@
     }
     
     /**
+     * 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
      */
     public void testIdentifyDateFormats() {



---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org


Mime
View raw message