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 }