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