1    
2    /* ====================================================================
3     * The Apache Software License, Version 1.1
4     *
5     * Copyright (c) 2002 The Apache Software Foundation.  All rights
6     * reserved.
7     *
8     * Redistribution and use in source and binary forms, with or without
9     * modification, are permitted provided that the following conditions
10    * are met:
11    *
12    * 1. Redistributions of source code must retain the above copyright
13    *    notice, this list of conditions and the following disclaimer.
14    *
15    * 2. Redistributions in binary form must reproduce the above copyright
16    *    notice, this list of conditions and the following disclaimer in
17    *    the documentation and/or other materials provided with the
18    *    distribution.
19    *
20    * 3. The end-user documentation included with the redistribution,
21    *    if any, must include the following acknowledgment:
22    *       "This product includes software developed by the
23    *        Apache Software Foundation (http://www.apache.org/)."
24    *    Alternately, this acknowledgment may appear in the software itself,
25    *    if and wherever such third-party acknowledgments normally appear.
26    *
27    * 4. The names "Apache" and "Apache Software Foundation" and
28    *    "Apache POI" must not be used to endorse or promote products
29    *    derived from this software without prior written permission. For
30    *    written permission, please contact apache@apache.org.
31    *
32    * 5. Products derived from this software may not be called "Apache",
33    *    "Apache POI", nor may "Apache" appear in their name, without
34    *    prior written permission of the Apache Software Foundation.
35    *
36    * THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESSED OR IMPLIED
37    * WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES
38    * OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
39    * DISCLAIMED.  IN NO EVENT SHALL THE APACHE SOFTWARE FOUNDATION OR
40    * ITS CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
41    * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
42    * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF
43    * USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
44    * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
45    * OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT
46    * OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
47    * SUCH DAMAGE.
48    * ====================================================================
49    *
50    * This software consists of voluntary contributions made by many
51    * individuals on behalf of the Apache Software Foundation.  For more
52    * information on the Apache Software Foundation, please see
53    * <http://www.apache.org/>.
54    */
55   
56   /*
57    * DateUtil.java
58    *
59    * Created on January 19, 2002, 9:30 AM
60    */
61   package org.apache.poi.hssf.usermodel;
62   
63   import java.util.Calendar;
64   import java.util.Date;
65   import java.util.GregorianCalendar;
66   
67   /**
68    * Contains methods for dealing with Excel dates.
69    *
70    * @author  Michael Harhen
71    * @author  Glen Stampoultzis (glens at apache.org)
72    * @author  Dan Sherman (dsherman at isisph.com)
73    */
74   
75   public class HSSFDateUtil
76   {
77       private HSSFDateUtil()
78       {
79       }
80   
81       private static final int    BAD_DATE          =
82           -1;   // used to specify that date is invalid
83       private static final long   DAY_MILLISECONDS  = 24 * 60 * 60 * 1000;
84       private static final double CAL_1900_ABSOLUTE =
85           ( double ) absoluteDay(new GregorianCalendar(1900, Calendar
86           .JANUARY, 1)) - 2.0;
87   
88       /**
89        * Given a Date, converts it into a double representing its internal Excel representation,
90        *   which is the number of days since 1/1/1900. Fractional days represent hours, minutes, and seconds.
91        *
92        * @return Excel representation of Date (-1 if error - test for error by checking for less than 0.1)
93        * @param  date the Date
94        */
95   
96       public static double getExcelDate(Date date)
97       {
98           Calendar calStart = new GregorianCalendar();
99   
100          calStart.setTime(
101              date);   // If date includes hours, minutes, and seconds, set them to 0
102          if (calStart.get(Calendar.YEAR) < 1900)
103          {
104              return BAD_DATE;
105          }
106          else
107          {
108              calStart = dayStart(calStart);
109              double fraction = (date.getTime() - calStart.getTime().getTime())
110                                / ( double ) DAY_MILLISECONDS;
111  
112              return fraction + ( double ) absoluteDay(calStart)
113                     - CAL_1900_ABSOLUTE;
114          }
115      }
116  
117      /**
118       * Given a excel date, converts it into a Date.
119       * Assumes 1900 date windowing.
120       *
121       * @param  date the Excel Date
122       *
123       * @return Java representation of a date (null if error)
124       * @see #getJavaDate(double,boolean)
125       */
126  
127      public static Date getJavaDate(double date)
128      {
129          return getJavaDate(date,false);
130      }
131      
132      /**
133       *  Given an Excel date with either 1900 or 1904 date windowing,
134       *  converts it to a java.util.Date.
135       *
136       *  @param date  The Excel date.
137       *  @param use1904windowing  true if date uses 1904 windowing,
138       *   or false if using 1900 date windowing.
139       *  @return Java representation of the date, or null if date is not a valid Excel date
140       */
141      public static Date getJavaDate(double date, boolean use1904windowing) {
142          if (isValidExcelDate(date)) {
143              int startYear = 1900;
144              int dayAdjust = -1; // Excel thinks 2/29/1900 is a valid date, which it isn't
145              int wholeDays = (int)Math.floor(date);
146              if (use1904windowing) {
147                  startYear = 1904;
148                  dayAdjust = 1; // 1904 date windowing uses 1/2/1904 as the first day
149              }
150              else if (wholeDays < 61) {
151                  // Date is prior to 3/1/1900, so adjust because Excel thinks 2/29/1900 exists
152                  // If Excel date == 2/29/1900, will become 3/1/1900 in Java representation
153                  dayAdjust = 0;
154              }
155              GregorianCalendar calendar = new GregorianCalendar(startYear,0,
156                                                       wholeDays + dayAdjust);
157              int millisecondsInDay = (int)((date - Math.floor(date)) * 
158                                            (double) DAY_MILLISECONDS + 0.5);
159              calendar.set(GregorianCalendar.MILLISECOND, millisecondsInDay);
160              return calendar.getTime();
161          }
162          else {
163              return null;
164          }
165      }
166  
167      /**
168       * given a format ID this will check whether the format represents
169       * an internal date format or not. 
170       */
171      public static boolean isInternalDateFormat(int format) {
172        boolean retval =false;
173  
174              switch(format) {
175                  // Internal Date Formats as described on page 427 in
176                  // Microsoft Excel Dev's Kit...
177                  case 0x0e:
178                  case 0x0f:
179                  case 0x10:
180                  case 0x11:
181                  case 0x12:
182                  case 0x13:
183                  case 0x14:
184                  case 0x15:
185                  case 0x16:
186                  case 0x2d:
187                  case 0x2e:
188                  case 0x2f:
189                  // Additional internal date formats found by inspection
190                  // Using Excel v.X 10.1.0 (Mac)
191                  case 0xa4:
192                  case 0xa5:
193                  case 0xa6:
194                  case 0xa7:
195                  case 0xa8:
196                  case 0xa9:
197                  case 0xaa:
198                  case 0xab:
199                  case 0xac:
200                  case 0xad:
201                      retval = true;
202                      break;
203                      
204                  default:
205                      retval = false;
206                      break;
207              }
208         return retval;
209      }
210  
211      /**
212       *  Check if a cell contains a date
213       *  Since dates are stored internally in Excel as double values 
214       *  we infer it is a date if it is formatted as such. 
215       *  @see #isInternalDateFormat(int)
216       */
217      public static boolean isCellDateFormatted(HSSFCell cell) {
218          if (cell == null) return false;
219          boolean bDate = false;
220          
221          double d = cell.getNumericCellValue();
222          if ( HSSFDateUtil.isValidExcelDate(d) ) {
223              HSSFCellStyle style = cell.getCellStyle();
224              int i = style.getDataFormat();
225              bDate = isInternalDateFormat(i);
226          }
227          return bDate;
228      }
229  
230  
231      /**
232       * Given a double, checks if it is a valid Excel date.
233       *
234       * @return true if valid
235       * @param  value the double value
236       */
237  
238      public static boolean isValidExcelDate(double value)
239      {
240          return (value > -Double.MIN_VALUE);
241      }
242  
243      /**
244       * Given a Calendar, return the number of days since 1600/12/31.
245       *
246       * @return days number of days since 1600/12/31
247       * @param  cal the Calendar
248       * @exception IllegalArgumentException if date is invalid
249       */
250  
251      private static int absoluteDay(Calendar cal)
252      {
253          return cal.get(Calendar.DAY_OF_YEAR)
254                 + daysInPriorYears(cal.get(Calendar.YEAR));
255      }
256  
257      /**
258       * Return the number of days in prior years since 1601
259       *
260       * @return    days  number of days in years prior to yr.
261       * @param     yr    a year (1600 < yr < 4000)
262       * @exception IllegalArgumentException if year is outside of range.
263       */
264  
265      private static int daysInPriorYears(int yr)
266      {
267          if (yr < 1601)
268          {
269              throw new IllegalArgumentException(
270                  "'year' must be 1601 or greater");
271          }
272          int y    = yr - 1601;
273          int days = 365 * y      // days in prior years
274                     + y / 4      // plus julian leap days in prior years
275                     - y / 100    // minus prior century years
276                     + y / 400;   // plus years divisible by 400
277  
278          return days;
279      }
280  
281      // set HH:MM:SS fields of cal to 00:00:00:000
282      private static Calendar dayStart(final Calendar cal)
283      {
284          cal.get(Calendar
285              .HOUR_OF_DAY);   // force recalculation of internal fields
286          cal.set(Calendar.HOUR_OF_DAY, 0);
287          cal.set(Calendar.MINUTE, 0);
288          cal.set(Calendar.SECOND, 0);
289          cal.set(Calendar.MILLISECOND, 0);
290          cal.get(Calendar
291              .HOUR_OF_DAY);   // force recalculation of internal fields
292          return cal;
293      }
294  
295      // ---------------------------------------------------------------------------------------------------------
296  }
297