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: package org.apache.poi.hssf.usermodel;
019:
020: import junit.framework.TestCase;
021:
022: import java.io.FileInputStream;
023: import java.util.Date;
024: import java.util.Calendar;
025: import java.util.GregorianCalendar;
026: import java.util.TimeZone;
027:
028: import org.apache.poi.hssf.model.Workbook;
029: import org.apache.poi.poifs.filesystem.POIFSFileSystem;
030:
031: /**
032: * Class TestHSSFDateUtil
033: *
034: *
035: * @author Dan Sherman (dsherman at isisph.com)
036: * @author Hack Kampbjorn (hak at 2mba.dk)
037: * @author Pavel Krupets (pkrupets at palmtreebusiness dot com)
038: * @author Alex Jacoby (ajacoby at gmail.com)
039: * @version %I%, %G%
040: */
041:
042: public class TestHSSFDateUtil extends TestCase {
043:
044: public static final int CALENDAR_JANUARY = 0;
045: public static final int CALENDAR_FEBRUARY = 1;
046: public static final int CALENDAR_MARCH = 2;
047: public static final int CALENDAR_APRIL = 3;
048: public static final int CALENDAR_JULY = 6;
049: public static final int CALENDAR_OCTOBER = 9;
050:
051: public TestHSSFDateUtil(String s) {
052: super (s);
053: }
054:
055: /**
056: * Checks the date conversion functions in the HSSFDateUtil class.
057: */
058:
059: public void testDateConversion() throws Exception {
060:
061: // Iteratating over the hours exposes any rounding issues.
062: for (int hour = 0; hour < 23; hour++) {
063: GregorianCalendar date = new GregorianCalendar(2002, 0, 1,
064: hour, 1, 1);
065: double excelDate = HSSFDateUtil.getExcelDate(
066: date.getTime(), false);
067:
068: assertEquals("Checking hour = " + hour, date.getTime()
069: .getTime(), HSSFDateUtil.getJavaDate(excelDate,
070: false).getTime());
071: }
072:
073: // check 1900 and 1904 date windowing conversions
074: double excelDate = 36526.0;
075: // with 1900 windowing, excelDate is Jan. 1, 2000
076: // with 1904 windowing, excelDate is Jan. 2, 2004
077: GregorianCalendar cal = new GregorianCalendar(2000, 0, 1); // Jan. 1, 2000
078: Date dateIf1900 = cal.getTime();
079: cal.add(GregorianCalendar.YEAR, 4); // now Jan. 1, 2004
080: cal.add(GregorianCalendar.DATE, 1); // now Jan. 2, 2004
081: Date dateIf1904 = cal.getTime();
082: // 1900 windowing
083: assertEquals("Checking 1900 Date Windowing", dateIf1900
084: .getTime(), HSSFDateUtil.getJavaDate(excelDate, false)
085: .getTime());
086: // 1904 windowing
087: assertEquals("Checking 1904 Date Windowing", dateIf1904
088: .getTime(), HSSFDateUtil.getJavaDate(excelDate, true)
089: .getTime());
090: }
091:
092: /**
093: * Checks the conversion of a java.util.date to Excel on a day when
094: * Daylight Saving Time starts.
095: */
096: public void testExcelConversionOnDSTStart() {
097: TimeZone cet = TimeZone.getTimeZone("Europe/Copenhagen");
098: TimeZone.setDefault(cet);
099: Calendar cal = new GregorianCalendar(2004, CALENDAR_MARCH, 28);
100: for (int hour = 0; hour < 24; hour++) {
101:
102: // Skip 02:00 CET as that is the Daylight change time
103: // and Java converts it automatically to 03:00 CEST
104: if (hour == 2) {
105: continue;
106: }
107:
108: cal.set(Calendar.HOUR_OF_DAY, hour);
109: Date javaDate = cal.getTime();
110: double excelDate = HSSFDateUtil.getExcelDate(javaDate,
111: false);
112: double difference = excelDate - Math.floor(excelDate);
113: int differenceInHours = (int) (difference * 24 * 60 + 0.5) / 60;
114: assertEquals("Checking " + hour
115: + " hour on Daylight Saving Time start date", hour,
116: differenceInHours);
117: assertEquals("Checking " + hour
118: + " hour on Daylight Saving Time start date",
119: javaDate.getTime(), HSSFDateUtil.getJavaDate(
120: excelDate, false).getTime());
121: }
122: }
123:
124: /**
125: * Checks the conversion of an Excel date to a java.util.date on a day when
126: * Daylight Saving Time starts.
127: */
128: public void testJavaConversionOnDSTStart() {
129: TimeZone cet = TimeZone.getTimeZone("Europe/Copenhagen");
130: TimeZone.setDefault(cet);
131: Calendar cal = new GregorianCalendar(2004, CALENDAR_MARCH, 28);
132: double excelDate = HSSFDateUtil.getExcelDate(cal.getTime(),
133: false);
134: double oneHour = 1.0 / 24;
135: double oneMinute = oneHour / 60;
136: for (int hour = 0; hour < 24; hour++, excelDate += oneHour) {
137:
138: // Skip 02:00 CET as that is the Daylight change time
139: // and Java converts it automatically to 03:00 CEST
140: if (hour == 2) {
141: continue;
142: }
143:
144: cal.set(Calendar.HOUR_OF_DAY, hour);
145: Date javaDate = HSSFDateUtil.getJavaDate(excelDate, false);
146: assertEquals("Checking " + hour
147: + " hours on Daylight Saving Time start date",
148: excelDate, HSSFDateUtil.getExcelDate(javaDate,
149: false), oneMinute);
150: }
151: }
152:
153: /**
154: * Checks the conversion of a java.util.Date to Excel on a day when
155: * Daylight Saving Time ends.
156: */
157: public void testExcelConversionOnDSTEnd() {
158: TimeZone cet = TimeZone.getTimeZone("Europe/Copenhagen");
159: TimeZone.setDefault(cet);
160: Calendar cal = new GregorianCalendar(2004, CALENDAR_OCTOBER, 31);
161: for (int hour = 0; hour < 24; hour++) {
162: cal.set(Calendar.HOUR_OF_DAY, hour);
163: Date javaDate = cal.getTime();
164: double excelDate = HSSFDateUtil.getExcelDate(javaDate,
165: false);
166: double difference = excelDate - Math.floor(excelDate);
167: int differenceInHours = (int) (difference * 24 * 60 + 0.5) / 60;
168: assertEquals("Checking " + hour
169: + " hour on Daylight Saving Time end date", hour,
170: differenceInHours);
171: assertEquals("Checking " + hour
172: + " hour on Daylight Saving Time start date",
173: javaDate.getTime(), HSSFDateUtil.getJavaDate(
174: excelDate, false).getTime());
175: }
176: }
177:
178: /**
179: * Checks the conversion of an Excel date to java.util.Date on a day when
180: * Daylight Saving Time ends.
181: */
182: public void testJavaConversionOnDSTEnd() {
183: TimeZone cet = TimeZone.getTimeZone("Europe/Copenhagen");
184: TimeZone.setDefault(cet);
185: Calendar cal = new GregorianCalendar(2004, CALENDAR_OCTOBER, 31);
186: double excelDate = HSSFDateUtil.getExcelDate(cal.getTime(),
187: false);
188: double oneHour = 1.0 / 24;
189: double oneMinute = oneHour / 60;
190: for (int hour = 0; hour < 24; hour++, excelDate += oneHour) {
191: cal.set(Calendar.HOUR_OF_DAY, hour);
192: Date javaDate = HSSFDateUtil.getJavaDate(excelDate, false);
193: assertEquals("Checking " + hour
194: + " hours on Daylight Saving Time start date",
195: excelDate, HSSFDateUtil.getExcelDate(javaDate,
196: false), oneMinute);
197: }
198: }
199:
200: /**
201: * Tests that we correctly detect date formats as such
202: */
203: public void testIdentifyDateFormats() {
204: // First up, try with a few built in date formats
205: short[] builtins = new short[] { 0x0e, 0x0f, 0x10, 0x16, 0x2d,
206: 0x2e };
207: for (int i = 0; i < builtins.length; i++) {
208: String formatStr = HSSFDataFormat
209: .getBuiltinFormat(builtins[i]);
210: assertTrue(HSSFDateUtil.isInternalDateFormat(builtins[i]));
211: assertTrue(HSSFDateUtil.isADateFormat(builtins[i],
212: formatStr));
213: }
214:
215: // Now try a few built-in non date formats
216: builtins = new short[] { 0x01, 0x02, 0x17, 0x1f, 0x30 };
217: for (int i = 0; i < builtins.length; i++) {
218: String formatStr = HSSFDataFormat
219: .getBuiltinFormat(builtins[i]);
220: assertFalse(HSSFDateUtil.isInternalDateFormat(builtins[i]));
221: assertFalse(HSSFDateUtil.isADateFormat(builtins[i],
222: formatStr));
223: }
224:
225: // Now for some non-internal ones
226: // These come after the real ones
227: int numBuiltins = HSSFDataFormat
228: .getNumberOfBuiltinBuiltinFormats();
229: assertTrue(numBuiltins < 60);
230: short formatId = 60;
231: assertFalse(HSSFDateUtil.isInternalDateFormat(formatId));
232:
233: // Valid ones first
234: String[] formats = new String[] { "yyyy-mm-dd", "yyyy/mm/dd",
235: "yy/mm/dd", "yy/mmm/dd", "dd/mm/yy", "dd/mm/yyyy",
236: "dd/mmm/yy",
237: "dd-mm-yy",
238: "dd-mm-yyyy",
239: "dd\\-mm\\-yy", // Sometimes escaped
240:
241: // These crazy ones are valid
242: "yyyy-mm-dd;@", "yyyy/mm/dd;@",
243: "dd-mm-yy;@",
244: "dd-mm-yyyy;@",
245: // These even crazier ones are also valid
246: // (who knows what they mean though...)
247: "[$-F800]dddd\\,\\ mmm\\ dd\\,\\ yyyy",
248: "[$-F900]ddd/mm/yyy", };
249: for (int i = 0; i < formats.length; i++) {
250: assertTrue(HSSFDateUtil.isADateFormat(formatId, formats[i]));
251: }
252:
253: // Then invalid ones
254: formats = new String[] { "yyyy:mm:dd", "0.0", "0.000", "0%",
255: "0.0%", "", null };
256: for (int i = 0; i < formats.length; i++) {
257: assertFalse(HSSFDateUtil
258: .isADateFormat(formatId, formats[i]));
259: }
260: }
261:
262: /**
263: * Test that against a real, test file, we still do everything
264: * correctly
265: */
266: public void testOnARealFile() throws Exception {
267: String path = System.getProperty("HSSF.testdata.path");
268: String filename = path + "/DateFormats.xls";
269: POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(
270: filename));
271: HSSFWorkbook workbook = new HSSFWorkbook(fs);
272: HSSFSheet sheet = workbook.getSheetAt(0);
273: Workbook wb = workbook.getWorkbook();
274:
275: HSSFRow row;
276: HSSFCell cell;
277: HSSFCellStyle style;
278:
279: double aug_10_2007 = 39304.0;
280:
281: // Should have dates in 2nd column
282: // All of them are the 10th of August
283: // 2 US dates, 3 UK dates
284: row = sheet.getRow(0);
285: cell = row.getCell((short) 1);
286: style = cell.getCellStyle();
287: assertEquals(aug_10_2007, cell.getNumericCellValue(), 0.0001);
288: assertEquals("d-mmm-yy", style.getDataFormatString(wb));
289: assertTrue(HSSFDateUtil.isInternalDateFormat(style
290: .getDataFormat()));
291: assertTrue(HSSFDateUtil.isADateFormat(style.getDataFormat(),
292: style.getDataFormatString(wb)));
293: assertTrue(HSSFDateUtil.isCellDateFormatted(cell));
294:
295: row = sheet.getRow(1);
296: cell = row.getCell((short) 1);
297: style = cell.getCellStyle();
298: assertEquals(aug_10_2007, cell.getNumericCellValue(), 0.0001);
299: assertFalse(HSSFDateUtil.isInternalDateFormat(cell
300: .getCellStyle().getDataFormat()));
301: assertTrue(HSSFDateUtil.isADateFormat(style.getDataFormat(),
302: style.getDataFormatString(wb)));
303: assertTrue(HSSFDateUtil.isCellDateFormatted(cell));
304:
305: row = sheet.getRow(2);
306: cell = row.getCell((short) 1);
307: style = cell.getCellStyle();
308: assertEquals(aug_10_2007, cell.getNumericCellValue(), 0.0001);
309: assertTrue(HSSFDateUtil.isInternalDateFormat(cell
310: .getCellStyle().getDataFormat()));
311: assertTrue(HSSFDateUtil.isADateFormat(style.getDataFormat(),
312: style.getDataFormatString(wb)));
313: assertTrue(HSSFDateUtil.isCellDateFormatted(cell));
314:
315: row = sheet.getRow(3);
316: cell = row.getCell((short) 1);
317: style = cell.getCellStyle();
318: assertEquals(aug_10_2007, cell.getNumericCellValue(), 0.0001);
319: assertFalse(HSSFDateUtil.isInternalDateFormat(cell
320: .getCellStyle().getDataFormat()));
321: assertTrue(HSSFDateUtil.isADateFormat(style.getDataFormat(),
322: style.getDataFormatString(wb)));
323: assertTrue(HSSFDateUtil.isCellDateFormatted(cell));
324:
325: row = sheet.getRow(4);
326: cell = row.getCell((short) 1);
327: style = cell.getCellStyle();
328: assertEquals(aug_10_2007, cell.getNumericCellValue(), 0.0001);
329: assertFalse(HSSFDateUtil.isInternalDateFormat(cell
330: .getCellStyle().getDataFormat()));
331: assertTrue(HSSFDateUtil.isADateFormat(style.getDataFormat(),
332: style.getDataFormatString(wb)));
333: assertTrue(HSSFDateUtil.isCellDateFormatted(cell));
334: }
335:
336: public void testDateBug_2Excel() {
337: assertEquals(59.0, HSSFDateUtil.getExcelDate(createDate(1900,
338: CALENDAR_FEBRUARY, 28), false), 0.00001);
339: assertEquals(61.0, HSSFDateUtil.getExcelDate(createDate(1900,
340: CALENDAR_MARCH, 1), false), 0.00001);
341:
342: assertEquals(37315.00, HSSFDateUtil.getExcelDate(createDate(
343: 2002, CALENDAR_FEBRUARY, 28), false), 0.00001);
344: assertEquals(37316.00, HSSFDateUtil.getExcelDate(createDate(
345: 2002, CALENDAR_MARCH, 1), false), 0.00001);
346: assertEquals(37257.00, HSSFDateUtil.getExcelDate(createDate(
347: 2002, CALENDAR_JANUARY, 1), false), 0.00001);
348: assertEquals(38074.00, HSSFDateUtil.getExcelDate(createDate(
349: 2004, CALENDAR_MARCH, 28), false), 0.00001);
350: }
351:
352: public void testDateBug_2Java() {
353: assertEquals(createDate(1900, CALENDAR_FEBRUARY, 28),
354: HSSFDateUtil.getJavaDate(59.0, false));
355: assertEquals(createDate(1900, CALENDAR_MARCH, 1), HSSFDateUtil
356: .getJavaDate(61.0, false));
357:
358: assertEquals(createDate(2002, CALENDAR_FEBRUARY, 28),
359: HSSFDateUtil.getJavaDate(37315.00, false));
360: assertEquals(createDate(2002, CALENDAR_MARCH, 1), HSSFDateUtil
361: .getJavaDate(37316.00, false));
362: assertEquals(createDate(2002, CALENDAR_JANUARY, 1),
363: HSSFDateUtil.getJavaDate(37257.00, false));
364: assertEquals(createDate(2004, CALENDAR_MARCH, 28), HSSFDateUtil
365: .getJavaDate(38074.00, false));
366: }
367:
368: public void testDate1904() {
369: assertEquals(createDate(1904, CALENDAR_JANUARY, 2),
370: HSSFDateUtil.getJavaDate(1.0, true));
371: assertEquals(createDate(1904, CALENDAR_JANUARY, 1),
372: HSSFDateUtil.getJavaDate(0.0, true));
373: assertEquals(0.0, HSSFDateUtil.getExcelDate(createDate(1904,
374: CALENDAR_JANUARY, 1), true), 0.00001);
375: assertEquals(1.0, HSSFDateUtil.getExcelDate(createDate(1904,
376: CALENDAR_JANUARY, 2), true), 0.00001);
377:
378: assertEquals(createDate(1998, CALENDAR_JULY, 5), HSSFDateUtil
379: .getJavaDate(35981, false));
380: assertEquals(createDate(1998, CALENDAR_JULY, 5), HSSFDateUtil
381: .getJavaDate(34519, true));
382:
383: assertEquals(35981.0, HSSFDateUtil.getExcelDate(createDate(
384: 1998, CALENDAR_JULY, 5), false), 0.00001);
385: assertEquals(34519.0, HSSFDateUtil.getExcelDate(createDate(
386: 1998, CALENDAR_JULY, 5), true), 0.00001);
387: }
388:
389: private Date createDate(int year, int month, int day) {
390: Calendar c = new GregorianCalendar();
391: c.set(year, month, day, 0, 0, 0);
392: c.set(Calendar.MILLISECOND, 0);
393: return c.getTime();
394: }
395:
396: /**
397: * Check if HSSFDateUtil.getAbsoluteDay works as advertised.
398: */
399: public void testAbsoluteDay() {
400: // 1 Jan 1900 is 1 day after 31 Dec 1899
401: GregorianCalendar calendar = new GregorianCalendar(1900, 0, 1);
402: assertEquals("Checking absolute day (1 Jan 1900)", 1,
403: HSSFDateUtil.absoluteDay(calendar, false));
404: // 1 Jan 1901 is 366 days after 31 Dec 1899
405: calendar = new GregorianCalendar(1901, 0, 1);
406: assertEquals("Checking absolute day (1 Jan 1901)", 366,
407: HSSFDateUtil.absoluteDay(calendar, false));
408: }
409:
410: public static void main(String[] args) {
411: System.out
412: .println("Testing org.apache.poi.hssf.usermodel.TestHSSFDateUtil");
413: junit.textui.TestRunner.run(TestHSSFDateUtil.class);
414: }
415: }
|