View | Details | Raw Unified | Return to bug 43551
Collapse All | Expand All

(-)src/java/org/apache/poi/hssf/usermodel/HSSFDateUtil.java (-12 / +37 lines)
Lines 35-40 Link Here
35
 * @author  Glen Stampoultzis (glens at apache.org)
35
 * @author  Glen Stampoultzis (glens at apache.org)
36
 * @author  Dan Sherman (dsherman at isisph.com)
36
 * @author  Dan Sherman (dsherman at isisph.com)
37
 * @author  Hack Kampbjorn (hak at 2mba.dk)
37
 * @author  Hack Kampbjorn (hak at 2mba.dk)
38
 * @author  Alex Jacoby (ajacoby at gmail.com)
38
 */
39
 */
39
40
40
public class HSSFDateUtil
41
public class HSSFDateUtil
Lines 49-67 Link Here
49
    
50
    
50
    /**
51
    /**
51
     * Given a Date, converts it into a double representing its internal Excel representation,
52
     * Given a Date, converts it into a double representing its internal Excel representation,
52
     *   which is the number of days since 1/1/1900. Fractional days represent hours, minutes, and seconds.
53
     *   which is the number of days since 12/31/1899. Fractional days represent hours, minutes, and seconds.
53
     *
54
     *
54
     * @return Excel representation of Date (-1 if error - test for error by checking for less than 0.1)
55
     * @return Excel representation of Date (-1 if error - test for error by checking for less than 0.1)
55
     * @param  date the Date
56
     * @param  date the Date
57
     * @deprecated Not safe to use: Assumes 1900 date windowing.
58
     * @see #getExcelDate(Date,boolean)
56
     */
59
     */
57
60
58
    public static double getExcelDate(Date date)
61
    public static double getExcelDate(Date date)
59
    {
62
    {
63
    	return getExcelDate(date, false);
64
    }
65
    
66
    /**
67
     * Given a Date, converts it into a double representing its internal Excel representation,
68
     *   which is the number of days since 12/31/1899 (or 1/1/1904 if using 1904 date windowing).
69
     * Fractional days represent hours, minutes, and seconds.
70
     *
71
     * @return Excel representation of Date (-1 if error - test for error by checking for less than 0.1)
72
     * @param  date the Date
73
     * @param  use1904windowing  true if date uses 1904 windowing,
74
     *   or false if using 1900 date windowing.
75
     */
76
77
    public static double getExcelDate(Date date, boolean use1904windowing)
78
    {
60
        Calendar calStart = new GregorianCalendar();
79
        Calendar calStart = new GregorianCalendar();
61
80
62
        calStart.setTime(
81
        calStart.setTime(
63
            date);   // If date includes hours, minutes, and seconds, set them to 0
82
            date);   // If date includes hours, minutes, and seconds, set them to 0
64
        if (calStart.get(Calendar.YEAR) < 1900)
83
        int firstYear = use1904windowing? 1904 : 1900;
84
        if (calStart.get(Calendar.YEAR) < firstYear)
65
        {
85
        {
66
            return BAD_DATE;
86
            return BAD_DATE;
67
        }
87
        }
Lines 79-92 Link Here
79
                                ) * 60 + calStart.get(Calendar.SECOND)
99
                                ) * 60 + calStart.get(Calendar.SECOND)
80
                               ) * 1000 + calStart.get(Calendar.MILLISECOND)
100
                               ) * 1000 + calStart.get(Calendar.MILLISECOND)
81
                              ) / ( double ) DAY_MILLISECONDS;
101
                              ) / ( double ) DAY_MILLISECONDS;
82
            calStart = dayStart(calStart);
102
            resetTime(calStart);
83
103
84
            double value = fraction + absoluteDay(calStart);
104
            double value = fraction + absoluteDay(calStart);
85
            
105
            
86
            if (value >= 60) {
106
            // Handle Excel bug (doesn't apply to 1904 date windowing)
107
            if ((value >= 60) && !use1904windowing) {
87
                value += 1;
108
                value += 1;
88
            }
109
            }
89
            
110
            
111
            if (use1904windowing) {
112
            	value -= (365 * 4) + 1; // 4 years and 1 day 
113
            }
114
            
90
            return value;
115
            return value;
91
        }
116
        }
92
    }
117
    }
Lines 132-138 Link Here
132
            int wholeDays = (int)Math.floor(date);
157
            int wholeDays = (int)Math.floor(date);
133
            if (use1904windowing) {
158
            if (use1904windowing) {
134
                startYear = 1904;
159
                startYear = 1904;
135
                dayAdjust = 1; // 1904 date windowing uses 1/2/1904 as the first day
160
                dayAdjust = 1; // 1904 date windowing uses 1/2/1904 as day 1 (1/1/1900 is day 0)
136
            }
161
            }
137
            else if (wholeDays < 61) {
162
            else if (wholeDays < 61) {
138
                // Date is prior to 3/1/1900, so adjust because Excel thinks 2/29/1900 exists
163
                // Date is prior to 3/1/1900, so adjust because Excel thinks 2/29/1900 exists
Lines 289-309 Link Here
289
    }
314
    }
290
315
291
    /**
316
    /**
292
     * Given a Calendar, return the number of days since 1900/12/31.
317
     * Given a Calendar, return the number of days since 1899/12/31
318
     * (<b>non-private only for testing!</b>).
293
     *
319
     *
294
     * @return days number of days since 1900/12/31
320
     * @return days number of days since 1899/12/31
295
     * @param  cal the Calendar
321
     * @param  cal the Calendar
296
     * @exception IllegalArgumentException if date is invalid
322
     * @exception IllegalArgumentException if date is invalid
297
     */
323
     */
298
324
299
    private static int absoluteDay(Calendar cal)
325
    static int absoluteDay(Calendar cal)
300
    {
326
    {
301
        return cal.get(Calendar.DAY_OF_YEAR)
327
        return cal.get(Calendar.DAY_OF_YEAR)
302
               + daysInPriorYears(cal.get(Calendar.YEAR));
328
               + daysInPriorYears(cal.get(Calendar.YEAR));
303
    }
329
    }
304
330
305
    /**
331
    /**
306
     * Return the number of days in prior years since 1900
332
     * Return the number of days in prior years since 1900.
307
     *
333
     *
308
     * @return    days  number of days in years prior to yr.
334
     * @return    days  number of days in years prior to yr.
309
     * @param     yr    a year (1900 < yr < 4000)
335
     * @param     yr    a year (1900 < yr < 4000)
Lines 326-333 Link Here
326
        return 365 * (yr - 1900) + leapDays;
352
        return 365 * (yr - 1900) + leapDays;
327
    }
353
    }
328
    
354
    
329
    // set HH:MM:SS fields of cal to 00:00:00:000
355
    /** Set HH:MM:SS fields of cal to 00:00:00:000. */
330
    private static Calendar dayStart(final Calendar cal)
356
    private static void resetTime(final Calendar cal)
331
    {
357
    {
332
        cal.get(Calendar
358
        cal.get(Calendar
333
            .HOUR_OF_DAY);   // force recalculation of internal fields
359
            .HOUR_OF_DAY);   // force recalculation of internal fields
Lines 337-343 Link Here
337
        cal.set(Calendar.MILLISECOND, 0);
363
        cal.set(Calendar.MILLISECOND, 0);
338
        cal.get(Calendar
364
        cal.get(Calendar
339
            .HOUR_OF_DAY);   // force recalculation of internal fields
365
            .HOUR_OF_DAY);   // force recalculation of internal fields
340
        return cal;
341
    }
366
    }
342
367
343
    // ---------------------------------------------------------------------------------------------------------
368
    // ---------------------------------------------------------------------------------------------------------
(-)src/java/org/apache/poi/hssf/usermodel/HSSFCell.java (-1 / +3 lines)
Lines 51-56 Link Here
51
 * @author  Dan Sherman (dsherman at isisph.com)
51
 * @author  Dan Sherman (dsherman at isisph.com)
52
 * @author  Brian Sanders (kestrel at burdell dot org) Active Cell support
52
 * @author  Brian Sanders (kestrel at burdell dot org) Active Cell support
53
 * @author  Yegor Kozlov cell comments support
53
 * @author  Yegor Kozlov cell comments support
54
 * @author  Alex Jacoby 1904 date windowing bugfix
54
 * @version 1.0-pre
55
 * @version 1.0-pre
55
 */
56
 */
56
57
Lines 521-527 Link Here
521
     */
522
     */
522
    public void setCellValue(Date value)
523
    public void setCellValue(Date value)
523
    {
524
    {
524
        setCellValue(HSSFDateUtil.getExcelDate(value));
525
        setCellValue(HSSFDateUtil.getExcelDate(value,
526
        		book.isUsing1904DateWindowing()));
525
    }
527
    }
526
528
527
    /**
529
    /**
(-)src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDateUtil.java (+16 lines)
Lines 93-98 Link Here
93
    }
93
    }
94
94
95
    /**
95
    /**
96
     * Check if HSSFDateUtil.getAbsoluteDay works as advertised.
97
     */
98
    public void testAbsoluteDay() {
99
    	// 1 Jan 1900 is 1 day after 31 Dec 1899
100
    	GregorianCalendar calendar = new GregorianCalendar(1900, 0, 1);
101
    	assertEquals("Checking absolute day (1 Jan 1900)",
102
    			1,
103
    			HSSFDateUtil.absoluteDay(calendar));
104
    	// 1 Jan 1901 is 366 days after 31 Dec 1899
105
    	calendar = new GregorianCalendar(1901, 0, 1);
106
    	assertEquals("Checking absolute day (1 Jan 1901)",
107
    			366,
108
    			HSSFDateUtil.absoluteDay(calendar));
109
    }
110
    
111
    /**
96
     * Checks the conversion of a java.util.date to Excel on a day when
112
     * Checks the conversion of a java.util.date to Excel on a day when
97
     * Daylight Saving Time starts.
113
     * Daylight Saving Time starts.
98
     */
114
     */
(-)src/testcases/org/apache/poi/hssf/usermodel/TestHSSFCell.java (-33 / +98 lines)
Lines 44-49 Link Here
44
 * paticular datatypes, etc.
44
 * paticular datatypes, etc.
45
 * @author Andrew C. Oliver (andy at superlinksoftware dot com)
45
 * @author Andrew C. Oliver (andy at superlinksoftware dot com)
46
 * @author  Dan Sherman (dsherman at isisph.com)
46
 * @author  Dan Sherman (dsherman at isisph.com)
47
 * @author Alex Jacoby (ajacoby at gmail.com)
47
 */
48
 */
48
49
49
public class TestHSSFCell
50
public class TestHSSFCell
Lines 107-148 Link Here
107
    }
108
    }
108
109
109
    /**
110
    /**
110
    * Checks that the recognition of files using 1904 date windowing
111
     * Checks that the recognition of files using 1904 date windowing
111
    *  is working properly. Conversion of the date is also an issue,
112
     *  is working properly. Conversion of the date is also an issue,
112
    *  but there's a separate unit test for that.
113
     *  but there's a separate unit test for that.
113
    */
114
     */
114
    public void testDateWindowing() throws Exception {
115
     public void testDateWindowingRead() throws Exception {
115
        GregorianCalendar cal = new GregorianCalendar(2000,0,1); // Jan. 1, 2000
116
         GregorianCalendar cal = new GregorianCalendar(2000,0,1); // Jan. 1, 2000
116
        Date date = cal.getTime();
117
         Date date = cal.getTime();
117
        String path = System.getProperty("HSSF.testdata.path");
118
         String path = System.getProperty("HSSF.testdata.path");
118
119
119
        // first check a file with 1900 Date Windowing
120
         // first check a file with 1900 Date Windowing
120
        String filename = path + "/1900DateWindowing.xls";
121
         String filename = path + "/1900DateWindowing.xls";
121
        FileInputStream stream   = new FileInputStream(filename);
122
         FileInputStream stream   = new FileInputStream(filename);
122
        POIFSFileSystem fs       = new POIFSFileSystem(stream);
123
         POIFSFileSystem fs       = new POIFSFileSystem(stream);
123
        HSSFWorkbook    workbook = new HSSFWorkbook(fs);
124
         HSSFWorkbook    workbook = new HSSFWorkbook(fs);
124
        HSSFSheet       sheet    = workbook.getSheetAt(0);
125
         HSSFSheet       sheet    = workbook.getSheetAt(0);
125
126
126
        assertEquals("Date from file using 1900 Date Windowing",
127
         assertEquals("Date from file using 1900 Date Windowing",
127
                        date.getTime(),
128
                         date.getTime(),
128
                           sheet.getRow(0).getCell((short)0)
129
                            sheet.getRow(0).getCell((short)0)
129
                              .getDateCellValue().getTime());
130
                               .getDateCellValue().getTime());
130
        stream.close();
131
         stream.close();
131
        
132
         
132
        // now check a file with 1904 Date Windowing
133
         // now check a file with 1904 Date Windowing
133
        filename = path + "/1904DateWindowing.xls";
134
         filename = path + "/1904DateWindowing.xls";
134
        stream   = new FileInputStream(filename);
135
         stream   = new FileInputStream(filename);
135
        fs       = new POIFSFileSystem(stream);
136
         fs       = new POIFSFileSystem(stream);
136
        workbook = new HSSFWorkbook(fs);
137
         workbook = new HSSFWorkbook(fs);
137
        sheet    = workbook.getSheetAt(0);
138
         sheet    = workbook.getSheetAt(0);
138
139
139
        assertEquals("Date from file using 1904 Date Windowing",
140
         assertEquals("Date from file using 1904 Date Windowing",
140
                        date.getTime(),
141
                         date.getTime(),
141
                           sheet.getRow(0).getCell((short)0)
142
                            sheet.getRow(0).getCell((short)0)
142
                              .getDateCellValue().getTime());
143
                               .getDateCellValue().getTime());
143
        stream.close();
144
         stream.close();
144
    }
145
     }
145
    
146
147
     /**
148
      * Checks that dates are properly written to both types of files:
149
      * those with 1900 and 1904 date windowing.  Note that if the
150
      * previous test ({@link #testDateWindowingRead}) fails, the
151
      * results of this test are meaningless.
152
      */
153
      public void testDateWindowingWrite() throws Exception {
154
          GregorianCalendar cal = new GregorianCalendar(2000,0,1); // Jan. 1, 2000
155
          Date date = cal.getTime();
156
          String path = System.getProperty("HSSF.testdata.path");
157
158
          // first check a file with 1900 Date Windowing
159
          String filename = path + "/1900DateWindowing.xls";
160
          writeCell(filename, 0, (short) 1, date);          
161
          assertEquals("Date from file using 1900 Date Windowing",
162
                          date.getTime(),
163
                          readCell(filename, 0, (short) 1).getTime());
164
          
165
          // now check a file with 1904 Date Windowing
166
          filename = path + "/1904DateWindowing.xls";
167
          writeCell(filename, 0, (short) 1, date);          
168
          assertEquals("Date from file using 1900 Date Windowing",
169
                          date.getTime(),
170
                          readCell(filename, 0, (short) 1).getTime());
171
      }
172
173
      /**
174
       * Sets cell value and writes file.
175
       */
176
      private void writeCell(String filename,
177
     		 int rowIdx, short colIdx, Date date) throws Exception {
178
          FileInputStream stream   = new FileInputStream(filename);
179
          POIFSFileSystem fs       = new POIFSFileSystem(stream);
180
          HSSFWorkbook    workbook = new HSSFWorkbook(fs);
181
          HSSFSheet       sheet    = workbook.getSheetAt(0);
182
          HSSFRow         row      = sheet.getRow(rowIdx);
183
          HSSFCell        cell     = row.getCell(colIdx);
184
          
185
          if (cell == null) {
186
        	  cell = row.createCell(colIdx);
187
          }
188
          cell.setCellValue(date);
189
          
190
          // Write the file
191
          stream.close();
192
          FileOutputStream oStream = new FileOutputStream(filename);
193
          workbook.write(oStream);
194
          oStream.close();
195
      }
196
      
197
      /**
198
       * Reads cell value from file.
199
       */
200
      private Date readCell(String filename,
201
     		 int rowIdx, short colIdx) throws Exception {
202
          FileInputStream stream   = new FileInputStream(filename);
203
          POIFSFileSystem fs       = new POIFSFileSystem(stream);
204
          HSSFWorkbook    workbook = new HSSFWorkbook(fs);
205
          HSSFSheet       sheet    = workbook.getSheetAt(0);
206
          HSSFRow         row      = sheet.getRow(rowIdx);
207
          HSSFCell        cell     = row.getCell(colIdx);
208
          return cell.getDateCellValue();
209
      }
210
      
146
    /**
211
    /**
147
     * Tests that the active cell can be correctly read and set
212
     * Tests that the active cell can be correctly read and set
148
     */
213
     */

Return to bug 43551