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 */ 73 74 public class HSSFDateUtil 75 { 76 private HSSFDateUtil() 77 { 78 } 79 80 private static final int BAD_DATE = 81 -1; // used to specify that date is invalid 82 private static final long DAY_MILLISECONDS = 24 * 60 * 60 * 1000; 83 private static final double CAL_1900_ABSOLUTE = 84 ( double ) absoluteDay(new GregorianCalendar(1900, Calendar 85 .JANUARY, 1)) - 2.0; 86 87 /** 88 * Given a Date, converts it into a double representing its internal Excel representation, 89 * which is the number of days since 1/1/1900. Fractional days represent hours, minutes, and seconds. 90 * 91 * @return Excel representation of Date (-1 if error - test for error by checking for less than 0.1) 92 * @param date the Date 93 */ 94 95 public static double getExcelDate(Date date) 96 { 97 Calendar calStart = new GregorianCalendar(); 98 99 calStart.setTime( 100 date); // If date includes hours, minutes, and seconds, set them to 0 101 if (calStart.get(Calendar.YEAR) < 1900) 102 { 103 return BAD_DATE; 104 } 105 else 106 { 107 calStart = dayStart(calStart); 108 double fraction = (date.getTime() - calStart.getTime().getTime()) 109 / ( double ) DAY_MILLISECONDS; 110 111 return fraction + ( double ) absoluteDay(calStart) 112 - CAL_1900_ABSOLUTE; 113 } 114 } 115 116 /** 117 * Given a excel date, converts it into a Date. 118 * 119 * @param date the Excel Date 120 * 121 * @return Java representation of a date (null if error) 122 */ 123 124 public static Date getJavaDate(double date) 125 { 126 if (isValidExcelDate(date)) 127 { 128 int wholeDaysSince1900 = ( int ) Math.floor(date); 129 GregorianCalendar calendar = new GregorianCalendar(1900, 130 0, wholeDaysSince1900 131 - 1); 132 int millisecondsInDay = 133 ( int ) ((date - Math.floor(date)) 134 * ( double ) DAY_MILLISECONDS + 0.5); 135 136 calendar.set(GregorianCalendar.MILLISECOND, millisecondsInDay); 137 return calendar.getTime(); 138 } 139 else 140 { 141 return null; 142 } 143 } 144 145 /** 146 * Check if a cell contains a date 147 * Since dates are stored internally in Excel as double values 148 * we infer it is a date if it is formatted as such. 149 */ 150 public static boolean isCellDateFormatted(HSSFCell cell) { 151 if (cell == null) return false; 152 boolean bDate = false; 153 154 double d = cell.getNumericCellValue(); 155 if ( HSSFDateUtil.isValidExcelDate(d) ) { 156 HSSFCellStyle style = cell.getCellStyle(); 157 int i = style.getDataFormat(); 158 switch(i) { 159 // Internal Date Formats as described on page 427 in 160 // Microsoft Excel Dev's Kit... 161 case 0x0e: 162 case 0x0f: 163 case 0x10: 164 case 0x11: 165 case 0x12: 166 case 0x13: 167 case 0x14: 168 case 0x15: 169 case 0x16: 170 case 0x2d: 171 case 0x2e: 172 case 0x2f: 173 bDate = true; 174 break; 175 176 default: 177 bDate = false; 178 break; 179 } 180 } 181 return bDate; 182 } 183 184 185 /** 186 * Given a double, checks if it is a valid Excel date. 187 * 188 * @return true if valid 189 * @param value the double value 190 */ 191 192 public static boolean isValidExcelDate(double value) 193 { 194 return (value > -Double.MIN_VALUE); 195 } 196 197 /** 198 * Given a Calendar, return the number of days since 1600/12/31. 199 * 200 * @return days number of days since 1600/12/31 201 * @param cal the Calendar 202 * @exception IllegalArgumentException if date is invalid 203 */ 204 205 private static int absoluteDay(Calendar cal) 206 { 207 return cal.get(Calendar.DAY_OF_YEAR) 208 + daysInPriorYears(cal.get(Calendar.YEAR)); 209 } 210 211 /** 212 * Return the number of days in prior years since 1601 213 * 214 * @return days number of days in years prior to yr. 215 * @param yr a year (1600 < yr < 4000) 216 * @exception IllegalArgumentException if year is outside of range. 217 */ 218 219 private static int daysInPriorYears(int yr) 220 { 221 if (yr < 1601) 222 { 223 throw new IllegalArgumentException( 224 "'year' must be 1601 or greater"); 225 } 226 int y = yr - 1601; 227 int days = 365 * y // days in prior years 228 + y / 4 // plus julian leap days in prior years 229 - y / 100 // minus prior century years 230 + y / 400; // plus years divisible by 400 231 232 return days; 233 } 234 235 // set HH:MM:SS fields of cal to 00:00:00:000 236 private static Calendar dayStart(final Calendar cal) 237 { 238 cal.get(Calendar 239 .HOUR_OF_DAY); // force recalculation of internal fields 240 cal.set(Calendar.HOUR_OF_DAY, 0); 241 cal.set(Calendar.MINUTE, 0); 242 cal.set(Calendar.SECOND, 0); 243 cal.set(Calendar.MILLISECOND, 0); 244 cal.get(Calendar 245 .HOUR_OF_DAY); // force recalculation of internal fields 246 return cal; 247 } 248 249 // --------------------------------------------------------------------------------------------------------- 250 } 251