src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDateUtil.java

   1
   2 /* ====================================================================
   3    Licensed to the Apache Software Foundation (ASF) under one or more
   4    contributor license agreements.  See the NOTICE file distributed with
   5    this work for additional information regarding copyright ownership.
   6    The ASF licenses this file to You under the Apache License, Version 2.0
   7    (the "License"); you may not use this file except in compliance with
   8    the License.  You may obtain a copy of the License at
   9
  10       http://www.apache.org/licenses/LICENSE-2.0
  11
  12    Unless required by applicable law or agreed to in writing, software
  13    distributed under the License is distributed on an "AS IS" BASIS,
  14    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  15    See the License for the specific language governing permissions and
  16    limitations under the License.
  17 ==================================================================== */
  18
  19
  20 package org.apache.poi.hssf.usermodel;
  21
  22 import java.util.Calendar;
  23 import java.util.Date;
  24 import java.util.GregorianCalendar;
  25 import java.util.TimeZone;
  26
  27 import junit.framework.TestCase;
  28
  29 import org.apache.poi.hssf.HSSFTestDataSamples;
  30 import org.apache.poi.hssf.model.Workbook;
  31
  32 /**
  33  * Class TestHSSFDateUtil
  34  *
  35  *
  36  * @author  Dan Sherman (dsherman at isisph.com)
  37  * @author  Hack Kampbjorn (hak at 2mba.dk)
  38  * @author  Pavel Krupets (pkrupets at palmtreebusiness dot com)
  39  * @author Alex Jacoby (ajacoby at gmail.com)
  40  * @version %I%, %G%
  41  */
  42 public class TestHSSFDateUtil extends TestCase {
  43
  44     public static final int CALENDAR_JANUARY = 0;
  45     public static final int CALENDAR_FEBRUARY = 1;
  46     public static final int CALENDAR_MARCH = 2;
  47     public static final int CALENDAR_APRIL = 3;
  48     public static final int CALENDAR_JULY = 6;
  49     public static final int CALENDAR_OCTOBER = 9;
  50
  51     public TestHSSFDateUtil(String s)
  52     {
  53         super(s);
  54     }
  55
  56     /**
  57      * Checks the date conversion functions in the HSSFDateUtil class.
  58      */
  59
  60     public void testDateConversion()
  61             throws Exception
  62     {
  63
  64         // Iteratating over the hours exposes any rounding issues.
  65         for (int hour = 0; hour < 23; hour++)
  66         {
  67             GregorianCalendar date      = new GregorianCalendar(2002, 0, 1,
  68                     hour, 1, 1);
  69             double            excelDate =
  70                     HSSFDateUtil.getExcelDate(date.getTime(), false);
  71
  72             assertEquals("Checking hour = " + hour, date.getTime().getTime(),
  73                     HSSFDateUtil.getJavaDate(excelDate, false).getTime());
  74         }
  75
  76         // check 1900 and 1904 date windowing conversions
  77         double excelDate = 36526.0;
  78         // with 1900 windowing, excelDate is Jan. 1, 2000
  79         // with 1904 windowing, excelDate is Jan. 2, 2004
  80         GregorianCalendar cal = new GregorianCalendar(2000,0,1); // Jan. 1, 2000
  81         Date dateIf1900 = cal.getTime();
  82         cal.add(GregorianCalendar.YEAR,4); // now Jan. 1, 2004
  83         cal.add(GregorianCalendar.DATE,1); // now Jan. 2, 2004
  84         Date dateIf1904 = cal.getTime();
  85         // 1900 windowing
  86         assertEquals("Checking 1900 Date Windowing",
  87                 dateIf1900.getTime(),
  88                 HSSFDateUtil.getJavaDate(excelDate,false).getTime());
  89         // 1904 windowing
  90         assertEquals("Checking 1904 Date Windowing",
  91                 dateIf1904.getTime(),
  92                 HSSFDateUtil.getJavaDate(excelDate,true).getTime());
  93     }
  94
  95     /**
  96      * Checks the conversion of a java.util.date to Excel on a day when
  97      * Daylight Saving Time starts.
  98      */
  99     public void testExcelConversionOnDSTStart() {
 100         TimeZone cet = TimeZone.getTimeZone("Europe/Copenhagen");
 101         TimeZone.setDefault(cet);
 102         Calendar cal = new GregorianCalendar(2004, CALENDAR_MARCH, 28);
 103         for (int hour = 0; hour < 24; hour++) {
 104
 105             // Skip 02:00 CET as that is the Daylight change time
 106             // and Java converts it automatically to 03:00 CEST
 107             if (hour == 2) {
 108                 continue;
 109             }
 110
 111             cal.set(Calendar.HOUR_OF_DAY, hour);
 112             Date javaDate = cal.getTime();
 113             double excelDate = HSSFDateUtil.getExcelDate(javaDate, false);
 114             double difference = excelDate - Math.floor(excelDate);
 115             int differenceInHours = (int) (difference * 24 * 60 + 0.5) / 60;
 116             assertEquals("Checking " + hour + " hour on Daylight Saving Time start date",
 117                     hour,
 118                     differenceInHours);
 119             assertEquals("Checking " + hour + " hour on Daylight Saving Time start date",
 120                     javaDate.getTime(),
 121                     HSSFDateUtil.getJavaDate(excelDate, false).getTime());
 122         }
 123     }
 124
 125     /**
 126      * Checks the conversion of an Excel date to a java.util.date on a day when
 127      * Daylight Saving Time starts.
 128      */
 129     public void testJavaConversionOnDSTStart() {
 130         TimeZone cet = TimeZone.getTimeZone("Europe/Copenhagen");
 131         TimeZone.setDefault(cet);
 132         Calendar cal = new GregorianCalendar(2004, CALENDAR_MARCH, 28);
 133         double excelDate = HSSFDateUtil.getExcelDate(cal.getTime(), 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 + " hours on Daylight Saving Time start date",
 147                     excelDate,
 148                     HSSFDateUtil.getExcelDate(javaDate, false), oneMinute);
 149         }
 150     }
 151
 152     /**
 153      * Checks the conversion of a java.util.Date to Excel on a day when
 154      * Daylight Saving Time ends.
 155      */
 156     public void testExcelConversionOnDSTEnd() {
 157         TimeZone cet = TimeZone.getTimeZone("Europe/Copenhagen");
 158         TimeZone.setDefault(cet);
 159         Calendar cal = new GregorianCalendar(2004, CALENDAR_OCTOBER, 31);
 160         for (int hour = 0; hour < 24; hour++) {
 161             cal.set(Calendar.HOUR_OF_DAY, hour);
 162             Date javaDate = cal.getTime();
 163             double excelDate = HSSFDateUtil.getExcelDate(javaDate, false);
 164             double difference = excelDate - Math.floor(excelDate);
 165             int differenceInHours = (int) (difference * 24 * 60 + 0.5) / 60;
 166             assertEquals("Checking " + hour + " hour on Daylight Saving Time end date",
 167                     hour,
 168                     differenceInHours);
 169             assertEquals("Checking " + hour + " hour on Daylight Saving Time start date",
 170                     javaDate.getTime(),
 171                     HSSFDateUtil.getJavaDate(excelDate, false).getTime());
 172         }
 173     }
 174
 175     /**
 176      * Checks the conversion of an Excel date to java.util.Date on a day when
 177      * Daylight Saving Time ends.
 178      */
 179     public void testJavaConversionOnDSTEnd() {
 180         TimeZone cet = TimeZone.getTimeZone("Europe/Copenhagen");
 181         TimeZone.setDefault(cet);
 182         Calendar cal = new GregorianCalendar(2004, CALENDAR_OCTOBER, 31);
 183         double excelDate = HSSFDateUtil.getExcelDate(cal.getTime(), false);
 184         double oneHour = 1.0 / 24;
 185         double oneMinute = oneHour / 60;
 186         for (int hour = 0; hour < 24; hour++, excelDate += oneHour) {
 187             cal.set(Calendar.HOUR_OF_DAY, hour);
 188             Date javaDate = HSSFDateUtil.getJavaDate(excelDate, false);
 189             assertEquals("Checking " + hour + " hours on Daylight Saving Time start date",
 190                     excelDate,
 191                     HSSFDateUtil.getExcelDate(javaDate, false), oneMinute);
 192         }
 193     }
 194
 195     /**
 196      * Tests that we deal with timezones properly
 197      */
 198     public void testCalendarConversion() {
 199         GregorianCalendar date = new GregorianCalendar(2002, 0, 1, 12, 1, 1);
 200         Date expected = date.getTime();
 201         double expectedExcel = HSSFDateUtil.getExcelDate(expected);
 202
 203         // Iteratating over the hours exposes any rounding issues.
 204         for (int hour = -12; hour <= 12; hour++)
 205         {
 206             String id = "GMT" + (hour < 0 ? "" : "+") + hour + ":00";
 207             date.setTimeZone(TimeZone.getTimeZone(id));
 208             date.set(Calendar.HOUR_OF_DAY, 12);
 209             double excelDate = HSSFDateUtil.getExcelDate(date, false);
 210             Date javaDate = HSSFDateUtil.getJavaDate(excelDate);
 211
 212             // Should match despite timezone
 213             assertEquals("Checking timezone " + id, expected.getTime(), javaDate.getTime());
 214         }
 215     }
 216
 217
 218     /**
 219      * Tests that we correctly detect date formats as such
 220      */
 221     public void testIdentifyDateFormats() {
 222         // First up, try with a few built in date formats
 223         short[] builtins = new short[] { 0x0e, 0x0f, 0x10, 0x16, 0x2d, 0x2e };
 224         for(int i=0; i<builtins.length; i++) {
 225             String formatStr = HSSFDataFormat.getBuiltinFormat(builtins[i]);
 226             assertTrue( HSSFDateUtil.isInternalDateFormat(builtins[i]) );
 227             assertTrue( HSSFDateUtil.isADateFormat(builtins[i],formatStr) );
 228         }
 229
 230         // Now try a few built-in non date formats
 231         builtins = new short[] { 0x01, 0x02, 0x17, 0x1f, 0x30 };
 232         for(int i=0; i<builtins.length; i++) {
 233             String formatStr = HSSFDataFormat.getBuiltinFormat(builtins[i]);
 234             assertFalse( HSSFDateUtil.isInternalDateFormat(builtins[i]) );
 235             assertFalse( HSSFDateUtil.isADateFormat(builtins[i],formatStr) );
 236         }
 237
 238         // Now for some non-internal ones
 239         // These come after the real ones
 240         int numBuiltins = HSSFDataFormat.getNumberOfBuiltinBuiltinFormats();
 241         assertTrue(numBuiltins < 60);
 242         short formatId = 60;
 243         assertFalse( HSSFDateUtil.isInternalDateFormat(formatId) );
 244
 245         // Valid ones first
 246         String[] formats = new String[] {
 247                 "yyyy-mm-dd", "yyyy/mm/dd", "yy/mm/dd", "yy/mmm/dd",
 248                 "dd/mm/yy", "dd/mm/yyyy", "dd/mmm/yy",
 249                 "dd-mm-yy", "dd-mm-yyyy",
 250                 "DD-MM-YY", "DD-mm-YYYY",
 251                 "dd\\-mm\\-yy", // Sometimes escaped
 252
 253                 // These crazy ones are valid
 254                 "yyyy-mm-dd;@", "yyyy/mm/dd;@",
 255                 "dd-mm-yy;@", "dd-mm-yyyy;@",
 256                 // These even crazier ones are also valid
 257                 // (who knows what they mean though...)
 258                 "[$-F800]dddd\\,\\ mmm\\ dd\\,\\ yyyy",
 259                 "[$-F900]ddd/mm/yyy",
 260                 // These ones specify colours, who knew that was allowed?
 261                 "[BLACK]dddd/mm/yy",
 262                 "[yeLLow]yyyy-mm-dd"
 263         };
 264         for(int i=0; i<formats.length; i++) {
 265             assertTrue(
 266                         formats[i] + " is a date format",
 267                         HSSFDateUtil.isADateFormat(formatId, formats[i])
 268             );
 269         }
 270
 271         // Then time based ones too
 272         formats = new String[] {
 273                 "yyyy-mm-dd hh:mm:ss", "yyyy/mm/dd HH:MM:SS",
 274                 "mm/dd HH:MM", "yy/mmm/dd SS",
 275                 "mm/dd HH:MM AM", "mm/dd HH:MM am",
 276                 "mm/dd HH:MM PM", "mm/dd HH:MM pm"
 277         };
 278         for(int i=0; i<formats.length; i++) {
 279             assertTrue(
 280                         formats[i] + " is a datetime format",
 281                         HSSFDateUtil.isADateFormat(formatId, formats[i])
 282             );
 283         }
 284
 285         // Then invalid ones
 286         formats = new String[] {
 287                 "yyyy*mm*dd",
 288                 "0.0", "0.000",
 289                 "0%", "0.0%",
 290                 "[]Foo", "[BLACK]0.00%",
 291                 "", null
 292         };
 293         for(int i=0; i<formats.length; i++) {
 294             assertFalse(
 295                         formats[i] + " is not a date or datetime format",
 296                         HSSFDateUtil.isADateFormat(formatId, formats[i])
 297             );
 298         }
 299
 300         // And these are ones we probably shouldn't allow,
 301         //  but would need a better regexp
 302         formats = new String[] {
 303                 "yyyy:mm:dd",
 304         };
 305         for(int i=0; i<formats.length; i++) {
 306         //    assertFalse( HSSFDateUtil.isADateFormat(formatId, formats[i]) );
 307         }
 308     }
 309
 310     /**
 311      * Test that against a real, test file, we still do everything
 312      *  correctly
 313      */
 314     public void testOnARealFile() throws Exception {
 315
 316         HSSFWorkbook workbook = HSSFTestDataSamples.openSampleWorkbook("DateFormats.xls");
 317         HSSFSheet sheet       = workbook.getSheetAt(0);
 318         Workbook wb           = workbook.getWorkbook();
 319
 320         HSSFRow  row;
 321         HSSFCell cell;
 322         HSSFCellStyle style;
 323
 324         double aug_10_2007 = 39304.0;
 325
 326         // Should have dates in 2nd column
 327         // All of them are the 10th of August
 328         // 2 US dates, 3 UK dates
 329         row  = sheet.getRow(0);
 330         cell = row.getCell((short)1);
 331         style = cell.getCellStyle();
 332         assertEquals(aug_10_2007, cell.getNumericCellValue(), 0.0001);
 333         assertEquals("d-mmm-yy", style.getDataFormatString(wb));
 334         assertTrue(HSSFDateUtil.isInternalDateFormat(style.getDataFormat()));
 335         assertTrue(HSSFDateUtil.isADateFormat(style.getDataFormat(), style.getDataFormatString(wb)));
 336         assertTrue(HSSFDateUtil.isCellDateFormatted(cell));
 337
 338         row  = sheet.getRow(1);
 339         cell = row.getCell((short)1);
 340         style = cell.getCellStyle();
 341         assertEquals(aug_10_2007, cell.getNumericCellValue(), 0.0001);
 342         assertFalse(HSSFDateUtil.isInternalDateFormat(cell.getCellStyle().getDataFormat()));
 343         assertTrue(HSSFDateUtil.isADateFormat(style.getDataFormat(), style.getDataFormatString(wb)));
 344         assertTrue(HSSFDateUtil.isCellDateFormatted(cell));
 345
 346         row  = sheet.getRow(2);
 347         cell = row.getCell((short)1);
 348         style = cell.getCellStyle();
 349         assertEquals(aug_10_2007, cell.getNumericCellValue(), 0.0001);
 350         assertTrue(HSSFDateUtil.isInternalDateFormat(cell.getCellStyle().getDataFormat()));
 351         assertTrue(HSSFDateUtil.isADateFormat(style.getDataFormat(), style.getDataFormatString(wb)));
 352         assertTrue(HSSFDateUtil.isCellDateFormatted(cell));
 353
 354         row  = sheet.getRow(3);
 355         cell = row.getCell((short)1);
 356         style = cell.getCellStyle();
 357         assertEquals(aug_10_2007, cell.getNumericCellValue(), 0.0001);
 358         assertFalse(HSSFDateUtil.isInternalDateFormat(cell.getCellStyle().getDataFormat()));
 359         assertTrue(HSSFDateUtil.isADateFormat(style.getDataFormat(), style.getDataFormatString(wb)));
 360         assertTrue(HSSFDateUtil.isCellDateFormatted(cell));
 361
 362         row  = sheet.getRow(4);
 363         cell = row.getCell((short)1);
 364         style = cell.getCellStyle();
 365         assertEquals(aug_10_2007, cell.getNumericCellValue(), 0.0001);
 366         assertFalse(HSSFDateUtil.isInternalDateFormat(cell.getCellStyle().getDataFormat()));
 367         assertTrue(HSSFDateUtil.isADateFormat(style.getDataFormat(), style.getDataFormatString(wb)));
 368         assertTrue(HSSFDateUtil.isCellDateFormatted(cell));
 369     }
 370
 371     public void testDateBug_2Excel() {
 372         assertEquals(59.0, HSSFDateUtil.getExcelDate(createDate(1900, CALENDAR_FEBRUARY, 28), false), 0.00001);
 373         assertEquals(61.0, HSSFDateUtil.getExcelDate(createDate(1900, CALENDAR_MARCH, 1), false), 0.00001);
 374
 375         assertEquals(37315.00, HSSFDateUtil.getExcelDate(createDate(2002, CALENDAR_FEBRUARY, 28), false), 0.00001);
 376         assertEquals(37316.00, HSSFDateUtil.getExcelDate(createDate(2002, CALENDAR_MARCH, 1), false), 0.00001);
 377         assertEquals(37257.00, HSSFDateUtil.getExcelDate(createDate(2002, CALENDAR_JANUARY, 1), false), 0.00001);
 378         assertEquals(38074.00, HSSFDateUtil.getExcelDate(createDate(2004, CALENDAR_MARCH, 28), false), 0.00001);
 379     }
 380
 381     public void testDateBug_2Java() {
 382         assertEquals(createDate(1900, CALENDAR_FEBRUARY, 28), HSSFDateUtil.getJavaDate(59.0, false));
 383         assertEquals(createDate(1900, CALENDAR_MARCH, 1), HSSFDateUtil.getJavaDate(61.0, false));
 384
 385         assertEquals(createDate(2002, CALENDAR_FEBRUARY, 28), HSSFDateUtil.getJavaDate(37315.00, false));
 386         assertEquals(createDate(2002, CALENDAR_MARCH, 1), HSSFDateUtil.getJavaDate(37316.00, false));
 387         assertEquals(createDate(2002, CALENDAR_JANUARY, 1), HSSFDateUtil.getJavaDate(37257.00, false));
 388         assertEquals(createDate(2004, CALENDAR_MARCH, 28), HSSFDateUtil.getJavaDate(38074.00, false));
 389     }
 390
 391     public void testDate1904() {
 392         assertEquals(createDate(1904, CALENDAR_JANUARY, 2), HSSFDateUtil.getJavaDate(1.0, true));
 393         assertEquals(createDate(1904, CALENDAR_JANUARY, 1), HSSFDateUtil.getJavaDate(0.0, true));
 394         assertEquals(0.0, HSSFDateUtil.getExcelDate(createDate(1904, CALENDAR_JANUARY, 1), true), 0.00001);
 395         assertEquals(1.0, HSSFDateUtil.getExcelDate(createDate(1904, CALENDAR_JANUARY, 2), true), 0.00001);
 396
 397         assertEquals(createDate(1998, CALENDAR_JULY, 5), HSSFDateUtil.getJavaDate(35981, false));
 398         assertEquals(createDate(1998, CALENDAR_JULY, 5), HSSFDateUtil.getJavaDate(34519, true));
 399
 400         assertEquals(35981.0, HSSFDateUtil.getExcelDate(createDate(1998, CALENDAR_JULY, 5), false), 0.00001);
 401         assertEquals(34519.0, HSSFDateUtil.getExcelDate(createDate(1998, CALENDAR_JULY, 5), true), 0.00001);
 402     }
 403
 404     private Date createDate(int year, int month, int day) {
 405         Calendar c = new GregorianCalendar();
 406         c.set(year, month, day, 0, 0, 0);
 407         c.set(Calendar.MILLISECOND, 0);
 408         return c.getTime();
 409     }
 410
 411     /**
 412      * Check if HSSFDateUtil.getAbsoluteDay works as advertised.
 413      */
 414     public void testAbsoluteDay() {
 415         // 1 Jan 1900 is 1 day after 31 Dec 1899
 416         GregorianCalendar calendar = new GregorianCalendar(1900, 0, 1);
 417         assertEquals("Checking absolute day (1 Jan 1900)", 1, HSSFDateUtil.absoluteDay(calendar, false));
 418         // 1 Jan 1901 is 366 days after 31 Dec 1899
 419         calendar = new GregorianCalendar(1901, 0, 1);
 420         assertEquals("Checking absolute day (1 Jan 1901)", 366, HSSFDateUtil.absoluteDay(calendar, false));
 421     }
 422
 423     public static void main(String [] args) {
 424         System.out
 425                 .println("Testing org.apache.poi.hssf.usermodel.TestHSSFDateUtil");
 426         junit.textui.TestRunner.run(TestHSSFDateUtil.class);
 427     }
 428 }