001: /* ====================================================================
002: Licensed to the Apache Software Foundation (ASF) under one or more
003: contributor license agreements. See the NOTICE file distributed with
004: this work for additional information regarding copyright ownership.
005: The ASF licenses this file to You under the Apache License, Version 2.0
006: (the "License"); you may not use this file except in compliance with
007: the License. You may obtain a copy of the License at
008:
009: http://www.apache.org/licenses/LICENSE-2.0
010:
011: Unless required by applicable law or agreed to in writing, software
012: distributed under the License is distributed on an "AS IS" BASIS,
013: WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
014: See the License for the specific language governing permissions and
015: limitations under the License.
016: ==================================================================== */
017:
018: /*
019: * DateUtil.java
020: *
021: * Created on January 19, 2002, 9:30 AM
022: */
023: package org.apache.poi.hssf.usermodel;
024:
025: import java.util.Calendar;
026: import java.util.Date;
027: import java.util.GregorianCalendar;
028:
029: /**
030: * Contains methods for dealing with Excel dates.
031: *
032: * @author Michael Harhen
033: * @author Glen Stampoultzis (glens at apache.org)
034: * @author Dan Sherman (dsherman at isisph.com)
035: * @author Hack Kampbjorn (hak at 2mba.dk)
036: * @author Alex Jacoby (ajacoby at gmail.com)
037: * @author Pavel Krupets (pkrupets at palmtreebusiness dot com)
038: */
039:
040: public class HSSFDateUtil {
041: private HSSFDateUtil() {
042: }
043:
044: private static final int BAD_DATE = -1; // used to specify that date is invalid
045: private static final long DAY_MILLISECONDS = 24 * 60 * 60 * 1000;
046:
047: /**
048: * Given a Date, converts it into a double representing its internal Excel representation,
049: * which is the number of days since 1/1/1900. Fractional days represent hours, minutes, and seconds.
050: *
051: * @return Excel representation of Date (-1 if error - test for error by checking for less than 0.1)
052: * @param date the Date
053: */
054: public static double getExcelDate(Date date) {
055: return getExcelDate(date, false);
056: }
057:
058: /**
059: * Given a Date, converts it into a double representing its internal Excel representation,
060: * which is the number of days since 1/1/1900. Fractional days represent hours, minutes, and seconds.
061: *
062: * @return Excel representation of Date (-1 if error - test for error by checking for less than 0.1)
063: * @param date the Date
064: * @param use1904windowing Should 1900 or 1904 date windowing be used?
065: */
066: public static double getExcelDate(Date date,
067: boolean use1904windowing) {
068: Calendar calStart = new GregorianCalendar();
069: calStart.setTime(date); // If date includes hours, minutes, and seconds, set them to 0
070:
071: if ((!use1904windowing && calStart.get(Calendar.YEAR) < 1900)
072: || (use1904windowing && calStart.get(Calendar.YEAR) < 1904)) {
073: return BAD_DATE;
074: } else {
075: // Because of daylight time saving we cannot use
076: // date.getTime() - calStart.getTimeInMillis()
077: // as the difference in milliseconds between 00:00 and 04:00
078: // can be 3, 4 or 5 hours but Excel expects it to always
079: // be 4 hours.
080: // E.g. 2004-03-28 04:00 CEST - 2004-03-28 00:00 CET is 3 hours
081: // and 2004-10-31 04:00 CET - 2004-10-31 00:00 CEST is 5 hours
082: double fraction = (((calStart.get(Calendar.HOUR_OF_DAY) * 60 + calStart
083: .get(Calendar.MINUTE)) * 60 + calStart
084: .get(Calendar.SECOND)) * 1000 + calStart
085: .get(Calendar.MILLISECOND))
086: / (double) DAY_MILLISECONDS;
087: calStart = dayStart(calStart);
088:
089: double value = fraction
090: + absoluteDay(calStart, use1904windowing);
091:
092: if (!use1904windowing && value >= 60) {
093: value++;
094: } else if (use1904windowing) {
095: value--;
096: }
097:
098: return value;
099: }
100: }
101:
102: /**
103: * Given an Excel date with using 1900 date windowing, and
104: * converts it to a java.util.Date.
105: *
106: * NOTE: If the default <code>TimeZone</code> in Java uses Daylight
107: * Saving Time then the conversion back to an Excel date may not give
108: * the same value, that is the comparison
109: * <CODE>excelDate == getExcelDate(getJavaDate(excelDate,false))</CODE>
110: * is not always true. For example if default timezone is
111: * <code>Europe/Copenhagen</code>, on 2004-03-28 the minute after
112: * 01:59 CET is 03:00 CEST, if the excel date represents a time between
113: * 02:00 and 03:00 then it is converted to past 03:00 summer time
114: *
115: * @param date The Excel date.
116: * @return Java representation of the date, or null if date is not a valid Excel date
117: * @see java.util.TimeZone
118: */
119: public static Date getJavaDate(double date) {
120: return getJavaDate(date, false);
121: }
122:
123: /**
124: * Given an Excel date with either 1900 or 1904 date windowing,
125: * converts it to a java.util.Date.
126: *
127: * NOTE: If the default <code>TimeZone</code> in Java uses Daylight
128: * Saving Time then the conversion back to an Excel date may not give
129: * the same value, that is the comparison
130: * <CODE>excelDate == getExcelDate(getJavaDate(excelDate,false))</CODE>
131: * is not always true. For example if default timezone is
132: * <code>Europe/Copenhagen</code>, on 2004-03-28 the minute after
133: * 01:59 CET is 03:00 CEST, if the excel date represents a time between
134: * 02:00 and 03:00 then it is converted to past 03:00 summer time
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: * @see java.util.TimeZone
141: */
142: public static Date getJavaDate(double date, boolean use1904windowing) {
143: if (isValidExcelDate(date)) {
144: int startYear = 1900;
145: int dayAdjust = -1; // Excel thinks 2/29/1900 is a valid date, which it isn't
146: int wholeDays = (int) Math.floor(date);
147: if (use1904windowing) {
148: startYear = 1904;
149: dayAdjust = 1; // 1904 date windowing uses 1/2/1904 as the first day
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(
156: startYear, 0, wholeDays + dayAdjust);
157: int millisecondsInDay = (int) ((date - Math.floor(date))
158: * DAY_MILLISECONDS + 0.5);
159: calendar.set(GregorianCalendar.MILLISECOND,
160: millisecondsInDay);
161: return calendar.getTime();
162: } else {
163: return null;
164: }
165: }
166:
167: /**
168: * Given a format ID and its format String, will check to see if the
169: * format represents a date format or not.
170: * Firstly, it will check to see if the format ID corresponds to an
171: * internal excel date format (eg most US date formats)
172: * If not, it will check to see if the format string only contains
173: * date formatting characters (ymd-/), which covers most
174: * non US date formats.
175: *
176: * @param formatIndex The index of the format, eg from ExtendedFormatRecord.getFormatIndex
177: * @param formatString The format string, eg from FormatRecord.getFormatString
178: * @see #isInternalDateFormat(int)
179: */
180: public static boolean isADateFormat(int formatIndex,
181: String formatString) {
182: // First up, is this an internal date format?
183: if (isInternalDateFormat(formatIndex)) {
184: return true;
185: }
186:
187: // If we didn't get a real string, it can't be
188: if (formatString == null || formatString.length() == 0) {
189: return false;
190: }
191:
192: String fs = formatString;
193:
194: // Translate \- into just -, before matching
195: fs = fs.replaceAll("\\\\-", "-");
196: // And \, into ,
197: fs = fs.replaceAll("\\\\,", ",");
198: // And '\ ' into ' '
199: fs = fs.replaceAll("\\\\ ", " ");
200:
201: // If it end in ;@, that's some crazy dd/mm vs mm/dd
202: // switching stuff, which we can ignore
203: fs = fs.replaceAll(";@", "");
204:
205: // If it starts with [$-...], then it is a date, but
206: // who knows what that starting bit is all about
207: fs = fs.replaceAll("\\[\\$\\-.*?\\]", "");
208:
209: // Otherwise, check it's only made up of:
210: // y m d - / ,
211: if (fs.matches("^[ymd\\-/, ]+$")) {
212: return true;
213: }
214:
215: return false;
216: }
217:
218: /**
219: * Given a format ID this will check whether the format represents
220: * an internal excel date format or not.
221: * @see #isADateFormat(int, java.lang.String)
222: */
223: public static boolean isInternalDateFormat(int format) {
224: boolean retval = false;
225:
226: switch (format) {
227: // Internal Date Formats as described on page 427 in
228: // Microsoft Excel Dev's Kit...
229: case 0x0e:
230: case 0x0f:
231: case 0x10:
232: case 0x11:
233: case 0x12:
234: case 0x13:
235: case 0x14:
236: case 0x15:
237: case 0x16:
238: case 0x2d:
239: case 0x2e:
240: case 0x2f:
241: retval = true;
242: break;
243:
244: default:
245: retval = false;
246: break;
247: }
248: return retval;
249: }
250:
251: /**
252: * Check if a cell contains a date
253: * Since dates are stored internally in Excel as double values
254: * we infer it is a date if it is formatted as such.
255: * @see #isADateFormat(int, String)
256: * @see #isInternalDateFormat(int)
257: */
258: public static boolean isCellDateFormatted(HSSFCell cell) {
259: if (cell == null)
260: return false;
261: boolean bDate = false;
262:
263: double d = cell.getNumericCellValue();
264: if (HSSFDateUtil.isValidExcelDate(d)) {
265: HSSFCellStyle style = cell.getCellStyle();
266: int i = style.getDataFormat();
267: String f = style.getDataFormatString(cell
268: .getBoundWorkbook());
269: bDate = isADateFormat(i, f);
270: }
271: return bDate;
272: }
273:
274: /**
275: * Check if a cell contains a date, checking only for internal
276: * excel date formats.
277: * As Excel stores a great many of its dates in "non-internal"
278: * date formats, you will not normally want to use this method.
279: * @see #isADateFormat(int,String)
280: * @see #isInternalDateFormat(int)
281: */
282: public static boolean isCellInternalDateFormatted(HSSFCell cell) {
283: if (cell == null)
284: return false;
285: boolean bDate = false;
286:
287: double d = cell.getNumericCellValue();
288: if (HSSFDateUtil.isValidExcelDate(d)) {
289: HSSFCellStyle style = cell.getCellStyle();
290: int i = style.getDataFormat();
291: bDate = isInternalDateFormat(i);
292: }
293: return bDate;
294: }
295:
296: /**
297: * Given a double, checks if it is a valid Excel date.
298: *
299: * @return true if valid
300: * @param value the double value
301: */
302:
303: public static boolean isValidExcelDate(double value) {
304: return (value > -Double.MIN_VALUE);
305: }
306:
307: /**
308: * Given a Calendar, return the number of days since 1900/12/31.
309: *
310: * @return days number of days since 1900/12/31
311: * @param cal the Calendar
312: * @exception IllegalArgumentException if date is invalid
313: */
314:
315: static int absoluteDay(Calendar cal, boolean use1904windowing) {
316: return cal.get(Calendar.DAY_OF_YEAR)
317: + daysInPriorYears(cal.get(Calendar.YEAR),
318: use1904windowing);
319: }
320:
321: /**
322: * Return the number of days in prior years since 1900
323: *
324: * @return days number of days in years prior to yr.
325: * @param yr a year (1900 < yr < 4000)
326: * @param use1904windowing
327: * @exception IllegalArgumentException if year is outside of range.
328: */
329:
330: private static int daysInPriorYears(int yr, boolean use1904windowing) {
331: if ((!use1904windowing && yr < 1900)
332: || (use1904windowing && yr < 1900)) {
333: throw new IllegalArgumentException(
334: "'year' must be 1900 or greater");
335: }
336:
337: int yr1 = yr - 1;
338: int leapDays = yr1 / 4 // plus julian leap days in prior years
339: - yr1 / 100 // minus prior century years
340: + yr1 / 400 // plus years divisible by 400
341: - 460; // leap days in previous 1900 years
342:
343: return 365 * (yr - (use1904windowing ? 1904 : 1900)) + leapDays;
344: }
345:
346: // set HH:MM:SS fields of cal to 00:00:00:000
347: private static Calendar dayStart(final Calendar cal) {
348: cal.get(Calendar.HOUR_OF_DAY); // force recalculation of internal fields
349: cal.set(Calendar.HOUR_OF_DAY, 0);
350: cal.set(Calendar.MINUTE, 0);
351: cal.set(Calendar.SECOND, 0);
352: cal.set(Calendar.MILLISECOND, 0);
353: cal.get(Calendar.HOUR_OF_DAY); // force recalculation of internal fields
354: return cal;
355: }
356:
357: // ---------------------------------------------------------------------------------------------------------
358: }
|