001 /**
002 * =========================================
003 * LibFormula : a free Java formula library
004 * =========================================
005 *
006 * Project Info: http://reporting.pentaho.org/libformula/
007 *
008 * (C) Copyright 2006-2007, by Pentaho Corporation and Contributors.
009 *
010 * This library is free software; you can redistribute it and/or modify it under the terms
011 * of the GNU Lesser General Public License as published by the Free Software Foundation;
012 * either version 2.1 of the License, or (at your option) any later version.
013 *
014 * This library is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY;
015 * without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
016 * See the GNU Lesser General Public License for more details.
017 *
018 * You should have received a copy of the GNU Lesser General Public License along with this
019 * library; if not, write to the Free Software Foundation, Inc., 59 Temple Place, Suite 330,
020 * Boston, MA 02111-1307, USA.
021 *
022 * [Java is a trademark or registered trademark of Sun Microsystems, Inc.
023 * in the United States and other countries.]
024 *
025 *
026 * ------------
027 * $Id: HSSFDateUtil.java 3522 2007-10-16 10:56:57Z tmorgner $
028 * ------------
029 * (C) Copyright 2006-2007, by Pentaho Corporation.
030 */
031
032 /*
033 * DateUtil.java
034 *
035 * Created on January 19, 2002, 9:30 AM
036 */
037 package org.jfree.formula.util;
038
039 import java.util.Calendar;
040 import java.util.Date;
041 import java.util.GregorianCalendar;
042
043 import org.jfree.formula.LibFormulaBoot;
044
045 /**
046 * Contains methods for dealing with Excel dates.
047 * <br/>
048 * Modified by Cedric Pronzato
049 *
050 * @author Michael Harhen
051 * @author Glen Stampoultzis (glens at apache.org)
052 * @author Dan Sherman (dsherman at isisph.com)
053 * @author Hack Kampbjorn (hak at 2mba.dk)
054 */
055
056 public class HSSFDateUtil
057 {
058 private HSSFDateUtil()
059 {
060 }
061
062 private static final int BAD_DATE =
063 -1; // used to specify that date is invalid
064 private static final long DAY_MILLISECONDS = 24 * 60 * 60 * 1000;
065 private static final double CAL_1900_ABSOLUTE =
066 ( double ) absoluteDay(new GregorianCalendar(1900, Calendar
067 .JANUARY, 1)) - 2.0;
068
069 /**
070 * Given a Date, converts it into a double representing its internal Excel representation,
071 * which is the number of days since 1/1/1900. Fractional days represent hours, minutes, and seconds.
072 *
073 * @return Excel representation of Date (-1 if error - test for error by checking for less than 0.1)
074 * @param date the Date
075 */
076
077 public static double getExcelDate(final Date date)
078 {
079 Calendar calStart = new GregorianCalendar();
080
081 calStart.setTime(
082 date); // If date includes hours, minutes, and seconds, set them to 0
083 // if (calStart.get(Calendar.YEAR) < 1900)
084 // {
085 // return BAD_DATE;
086 // }
087 // else
088 // {
089 // Because of daylight time saving we cannot use
090 // date.getTime() - calStart.getTimeInMillis()
091 // as the difference in milliseconds between 00:00 and 04:00
092 // can be 3, 4 or 5 hours but Excel expects it to always
093 // be 4 hours.
094 // E.g. 2004-03-28 04:00 CEST - 2004-03-28 00:00 CET is 3 hours
095 // and 2004-10-31 04:00 CET - 2004-10-31 00:00 CEST is 5 hours
096 final double fraction = (((calStart.get(Calendar.HOUR_OF_DAY) * 60
097 + calStart.get(Calendar.MINUTE)
098 ) * 60 + calStart.get(Calendar.SECOND)
099 ) * 1000 + calStart.get(Calendar.MILLISECOND)
100 ) / ( double ) DAY_MILLISECONDS;
101 calStart = dayStart(calStart);
102
103 return fraction + ( double ) absoluteDay(calStart)
104 - CAL_1900_ABSOLUTE;
105 }
106 // }
107
108 /**
109 * Given a excel date, converts it into a Date.
110 * Assumes 1900 date windowing.
111 *
112 * @param date the Excel Date
113 *
114 * @return Java representation of a date (null if error)
115 * @see #getJavaDate(double,boolean)
116 */
117
118 public static Date getJavaDate(final double date)
119 {
120 final String dateSystem = LibFormulaBoot.getInstance().getGlobalConfig()
121 .getConfigProperty("org.jfree.formula.datesystem.1904", "false");
122 return getJavaDate(date, "true".equals(dateSystem));
123 }
124
125 /**
126 * Given an Excel date with either 1900 or 1904 date windowing,
127 * converts it to a java.util.Date.
128 *
129 * NOTE: If the default <code>TimeZone</code> in Java uses Daylight
130 * Saving Time then the conversion back to an Excel date may not give
131 * the same value, that is the comparison
132 * <CODE>excelDate == getExcelDate(getJavaDate(excelDate,false))</CODE>
133 * is not always true. For example if default timezone is
134 * <code>Europe/Copenhagen</code>, on 2004-03-28 the minute after
135 * 01:59 CET is 03:00 CEST, if the excel date represents a time between
136 * 02:00 and 03:00 then it is converted to past 03:00 summer time
137 *
138 * @param date The Excel date.
139 * @param use1904windowing true if date uses 1904 windowing,
140 * or false if using 1900 date windowing.
141 * @return Java representation of the date, or null if date is not a valid Excel date
142 * @see java.util.TimeZone
143 */
144 public static Date getJavaDate(final double date, final boolean use1904windowing) {
145 if (isValidExcelDate(date)) {
146 int startYear = 1900;
147 int dayAdjust = -1; // Excel thinks 2/29/1900 is a valid date, which it isn't
148 final int wholeDays = (int)Math.floor(date);
149 if (use1904windowing) {
150 startYear = 1904;
151 dayAdjust = 1; // 1904 date windowing uses 1/2/1904 as the first day
152 }
153 else if (wholeDays < 61) {
154 // Date is prior to 3/1/1900, so adjust because Excel thinks 2/29/1900 exists
155 // If Excel date == 2/29/1900, will become 3/1/1900 in Java representation
156 dayAdjust = 0;
157 }
158 final GregorianCalendar calendar = new GregorianCalendar(startYear,0,
159 wholeDays + dayAdjust);
160 final int millisecondsInDay = (int)((date - Math.floor(date)) *
161 (double) DAY_MILLISECONDS + 0.5);
162 calendar.set(GregorianCalendar.MILLISECOND, millisecondsInDay);
163 return calendar.getTime();
164 }
165 else {
166 return null;
167 }
168 }
169
170 /**
171 * given a format ID this will check whether the format represents
172 * an internal date format or not.
173 */
174 public static boolean isInternalDateFormat(final int format) {
175 boolean retval;
176
177 switch(format) {
178 // Internal Date Formats as described on page 427 in
179 // Microsoft Excel Dev's Kit...
180 case 0x0e:
181 case 0x0f:
182 case 0x10:
183 case 0x11:
184 case 0x12:
185 case 0x13:
186 case 0x14:
187 case 0x15:
188 case 0x16:
189 case 0x2d:
190 case 0x2e:
191 case 0x2f:
192 retval = true;
193 break;
194
195 default:
196 retval = false;
197 break;
198 }
199 return retval;
200 }
201
202
203
204 /**
205 * Given a double, checks if it is a valid Excel date.
206 *
207 * @return true if valid
208 * @param value the double value
209 */
210
211 public static boolean isValidExcelDate(final double value)
212 {
213 return (value > -Double.MIN_VALUE);
214 }
215
216 /**
217 * Given a Calendar, return the number of days since 1600/12/31.
218 *
219 * @return days number of days since 1600/12/31
220 * @param cal the Calendar
221 * @exception IllegalArgumentException if date is invalid
222 */
223
224 private static int absoluteDay(final Calendar cal)
225 {
226 return cal.get(Calendar.DAY_OF_YEAR)
227 + daysInPriorYears(cal.get(Calendar.YEAR));
228 }
229
230 /**
231 * Return the number of days in prior years since 1601
232 *
233 * @return days number of days in years prior to yr.
234 * @param yr a year (1600 < yr < 4000)
235 * @exception IllegalArgumentException if year is outside of range.
236 */
237
238 private static int daysInPriorYears(final int yr)
239 {
240 if (yr < 1601)
241 {
242 throw new IllegalArgumentException(
243 "'year' must be 1601 or greater");
244 }
245 final int y = yr - 1601;
246
247 return 365 * y // days in prior years
248 + y / 4 // plus julian leap days in prior years
249 - y / 100 // minus prior century years
250 + y / 400;
251 }
252
253 // set HH:MM:SS fields of cal to 00:00:00:000
254 private static Calendar dayStart(final Calendar cal)
255 {
256 cal.get(Calendar
257 .HOUR_OF_DAY); // force recalculation of internal fields
258 cal.set(Calendar.HOUR_OF_DAY, 0);
259 cal.set(Calendar.MINUTE, 0);
260 cal.set(Calendar.SECOND, 0);
261 cal.set(Calendar.MILLISECOND, 0);
262 cal.get(Calendar
263 .HOUR_OF_DAY); // force recalculation of internal fields
264 return cal;
265 }
266
267 // ---------------------------------------------------------------------------------------------------------
268 }