You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by ce...@apache.org on 2019/10/09 19:13:00 UTC
svn commit: r1868198 [2/2] - in /poi/trunk/src:
java/org/apache/poi/hssf/usermodel/ java/org/apache/poi/ss/usermodel/
ooxml/java/org/apache/poi/xssf/streaming/
ooxml/java/org/apache/poi/xssf/usermodel/
testcases/org/apache/poi/hssf/usermodel/ testcases...
Modified: poi/trunk/src/testcases/org/apache/poi/ss/usermodel/TestDateUtil.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/usermodel/TestDateUtil.java?rev=1868198&r1=1868197&r2=1868198&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/ss/usermodel/TestDateUtil.java [UTF-8] (original)
+++ poi/trunk/src/testcases/org/apache/poi/ss/usermodel/TestDateUtil.java [UTF-8] Wed Oct 9 19:12:59 2019
@@ -17,18 +17,43 @@
package org.apache.poi.ss.usermodel;
-import static org.junit.Assert.assertEquals;
-import static org.junit.Assert.assertTrue;
-
+import static java.util.Calendar.*;
+import static org.junit.Assert.*;
+import static org.junit.Assert.fail;
+
+import java.io.IOException;
+import java.text.ParseException;
+import java.text.SimpleDateFormat;
+import java.time.LocalDateTime;
+import java.time.ZoneId;
import java.util.Calendar;
import java.util.Date;
+import java.util.Locale;
import java.util.TimeZone;
+import org.apache.poi.hssf.HSSFTestDataSamples;
+import org.apache.poi.hssf.model.InternalWorkbook;
+import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.util.LocaleUtil;
+import org.junit.AfterClass;
+import org.junit.BeforeClass;
import org.junit.Test;
public class TestDateUtil {
+ static TimeZone userTimeZone;
+
+ @BeforeClass
+ public static void setCEST() {
+ userTimeZone = LocaleUtil.getUserTimeZone();
+ LocaleUtil.setUserTimeZone(TimeZone.getTimeZone("CEST"));
+ }
+
+ @AfterClass
+ public static void resetTimeZone() {
+ LocaleUtil.setUserTimeZone(userTimeZone);
+ }
+
@Test
public void getJavaDate_InvalidValue() {
double dateValue = -1;
@@ -93,6 +118,32 @@ public class TestDateUtil {
assertEquals(expCal, actCal[2]);
assertEquals(expCal, actCal[3]);
}
+
+ @Test
+ public void getLocalDateTime_InvalidValue() {
+ double dateValue = -1;
+ TimeZone tz = LocaleUtil.getUserTimeZone();
+ boolean use1904windowing = false;
+ boolean roundSeconds = false;
+
+ assertEquals(null, DateUtil.getLocalDateTime(dateValue));
+ assertEquals(null, DateUtil.getLocalDateTime(dateValue, use1904windowing));
+ assertEquals(null, DateUtil.getLocalDateTime(dateValue, use1904windowing, roundSeconds));
+ }
+
+ @Test
+ public void getLocalDateTime_ValidValue() {
+ double dateValue = 0;
+ boolean use1904windowing = false;
+ boolean roundSeconds = false;
+
+ // note that the Date and Calendar examples use a zero day of month which is invalid in LocalDateTime
+ LocalDateTime date = LocalDateTime.of(1899, 12, 31, 0, 0);
+
+ assertEquals(date, DateUtil.getLocalDateTime(dateValue));
+ assertEquals(date, DateUtil.getLocalDateTime(dateValue, use1904windowing));
+ assertEquals(date, DateUtil.getLocalDateTime(dateValue, use1904windowing, roundSeconds));
+ }
@Test
public void isADateFormat() {
@@ -123,4 +174,571 @@ public class TestDateUtil {
// Cell show "2016年12月8日"
assertTrue(DateUtil.isADateFormat(178, "[DBNum3][$-804]yyyy\"\u5e74\"m\"\u6708\"d\"\u65e5\";@"));
}
+ /**
+ * Checks the date conversion functions in the DateUtil class.
+ */
+ @Test
+ public void dateConversion() {
+
+ // Iteratating over the hours exposes any rounding issues.
+ Calendar cal = LocaleUtil.getLocaleCalendar(2002,JANUARY,1,0,1,1);
+ for (int hour = 0; hour < 24; hour++) {
+ double excelDate = DateUtil.getExcelDate(cal.getTime(), false);
+
+ assertEquals("getJavaDate: Checking hour = " + hour, cal.getTime().getTime(),
+ DateUtil.getJavaDate(excelDate, false).getTime());
+
+ LocalDateTime ldt = LocalDateTime.ofInstant(cal.toInstant(), cal.getTimeZone().toZoneId());
+ assertEquals("getLocalDateTime: Checking hour = " + hour, ldt,
+ DateUtil.getLocalDateTime(excelDate, false));
+
+ cal.add(Calendar.HOUR_OF_DAY, 1);
+ }
+
+ // check 1900 and 1904 date windowing conversions
+ double excelDate = 36526.0;
+ // with 1900 windowing, excelDate is Jan. 1, 2000
+ // with 1904 windowing, excelDate is Jan. 2, 2004
+ cal.set(2000,JANUARY,1,0,0,0); // Jan. 1, 2000
+ Date dateIf1900 = cal.getTime();
+ cal.add(Calendar.YEAR,4); // now Jan. 1, 2004
+ cal.add(Calendar.DATE,1); // now Jan. 2, 2004
+ Date dateIf1904 = cal.getTime();
+ // 1900 windowing
+ assertEquals("Checking 1900 Date Windowing",
+ dateIf1900.getTime(),
+ DateUtil.getJavaDate(excelDate,false).getTime());
+ // 1904 windowing
+ assertEquals("Checking 1904 Date Windowing",
+ dateIf1904.getTime(),
+ DateUtil.getJavaDate(excelDate,true).getTime());
+ // 1900 windowing (LocalDateTime)
+ assertEquals("Checking 1900 Date Windowing",
+ LocalDateTime.of(2000,1,1,0,0),
+ DateUtil.getLocalDateTime(excelDate,false));
+ // 1904 windowing (LocalDateTime)
+ assertEquals("Checking 1904 Date Windowing",
+ LocalDateTime.of(2004,1,2,0,0),
+ DateUtil.getLocalDateTime(excelDate,true));
+ }
+
+ /**
+ * Checks the conversion of a java.util.date to Excel on a day when
+ * Daylight Saving Time starts.
+ */
+ @Test
+ public void excelConversionOnDSTStart() {
+ Calendar cal = LocaleUtil.getLocaleCalendar(2004,MARCH,28,0,0,0);
+ for (int hour = 0; hour < 24; hour++) {
+
+ // Skip 02:00 CET as that is the Daylight change time
+ // and Java converts it automatically to 03:00 CEST
+ if (hour == 2) {
+ continue;
+ }
+
+ cal.set(Calendar.HOUR_OF_DAY, hour);
+ Date javaDate = cal.getTime();
+ double excelDate = DateUtil.getExcelDate(javaDate, false);
+ double difference = excelDate - Math.floor(excelDate);
+ int differenceInHours = (int) (difference * 24 * 60 + 0.5) / 60;
+
+ assertEquals("Checking " + hour + " hour on Daylight Saving Time start date",
+ hour,
+ differenceInHours);
+ assertEquals("Checking " + hour + " hour on Daylight Saving Time start date",
+ javaDate.getTime(),
+ DateUtil.getJavaDate(excelDate, false).getTime());
+
+ // perform the same checks with LocalDateTime
+ LocalDateTime localDate = LocalDateTime.of(2004,3,28,hour,0,0);
+ double excelLocalDate = DateUtil.getExcelDate(localDate, false);
+ double differenceLocalDate = excelLocalDate - Math.floor(excelLocalDate);
+ int differenceLocalDateInHours = (int) (differenceLocalDate * 24 * 60 + 0.5) / 60;
+
+ assertEquals("Checking " + hour + " hour on Daylight Saving Time start date (LocalDateTime)",
+ hour,
+ differenceLocalDateInHours);
+ assertEquals("Checking " + hour + " hour on Daylight Saving Time start date (LocalDateTime)",
+ localDate,
+ DateUtil.getLocalDateTime(excelLocalDate, false));
+ }
+ }
+
+ /**
+ * Checks the conversion of an Excel date to a java.util.date on a day when
+ * Daylight Saving Time starts.
+ */
+ @Test
+ public void javaConversionOnDSTStart() {
+ Calendar cal = LocaleUtil.getLocaleCalendar(2004,MARCH,28,0,0,0);
+ double excelDate = DateUtil.getExcelDate(cal.getTime(), false);
+ double oneHour = 1.0 / 24;
+ double oneMinute = oneHour / 60;
+ for (int hour = 0; hour < 24; hour++, excelDate += oneHour) {
+
+ // Skip 02:00 CET as that is the Daylight change time
+ // and Java converts it automatically to 03:00 CEST
+ if (hour == 2) {
+ continue;
+ }
+
+ cal.set(Calendar.HOUR_OF_DAY, hour);
+ Date javaDate = DateUtil.getJavaDate(excelDate, false);
+ double actDate = DateUtil.getExcelDate(javaDate, false);
+ assertEquals("Checking " + hour + " hours on Daylight Saving Time start date",
+ excelDate, actDate, oneMinute);
+
+ // perform the same check with LocalDateTime
+ cal.set(Calendar.HOUR_OF_DAY, hour);
+ LocalDateTime localDate = DateUtil.getLocalDateTime(excelDate, false);
+ double actLocalDate = DateUtil.getExcelDate(localDate, false);
+ assertEquals("Checking " + hour + " hours on Daylight Saving Time start date (LocalDateTime)",
+ excelDate, actLocalDate, oneMinute);
+ }
+ }
+
+ /**
+ * Checks the conversion of a java.util.Date to Excel on a day when
+ * Daylight Saving Time ends.
+ */
+ @Test
+ public void excelConversionOnDSTEnd() {
+ Calendar cal = LocaleUtil.getLocaleCalendar(2004,OCTOBER,31,0,0,0);
+ for (int hour = 0; hour < 24; hour++) {
+ cal.set(Calendar.HOUR_OF_DAY, hour);
+ Date javaDate = cal.getTime();
+ double excelDate = DateUtil.getExcelDate(javaDate, false);
+ double difference = excelDate - Math.floor(excelDate);
+ int differenceInHours = (int) (difference * 24 * 60 + 0.5) / 60;
+ assertEquals("Checking " + hour + " hour on Daylight Saving Time end date",
+ hour,
+ differenceInHours);
+ assertEquals("Checking " + hour + " hour on Daylight Saving Time start date",
+ javaDate.getTime(),
+ DateUtil.getJavaDate(excelDate, false).getTime());
+
+ // perform the same checks using LocalDateTime
+ LocalDateTime localDate = LocalDateTime.of(2004,10,31,hour,0,0);
+ double excelLocalDate = DateUtil.getExcelDate(localDate, false);
+ double differenceLocalDate = excelLocalDate - Math.floor(excelLocalDate);
+ int differenceLocalDateInHours = (int) (difference * 24 * 60 + 0.5) / 60;
+ assertEquals("Checking " + hour + " hour on Daylight Saving Time end date (LocalDateTime)",
+ hour,
+ differenceLocalDateInHours);
+ assertEquals("Checking " + hour + " hour on Daylight Saving Time start date (LocalDateTime)",
+ localDate,
+ DateUtil.getLocalDateTime(excelLocalDate, false));
+ }
+ }
+
+ /**
+ * Checks the conversion of an Excel date to java.util.Date on a day when
+ * Daylight Saving Time ends.
+ */
+ @Test
+ public void javaConversionOnDSTEnd() {
+ Calendar cal = LocaleUtil.getLocaleCalendar(2004,OCTOBER,31,0,0,0);
+ double excelDate = DateUtil.getExcelDate(cal.getTime(), false);
+ double oneHour = 1.0 / 24;
+ double oneMinute = oneHour / 60;
+ for (int hour = 0; hour < 24; hour++, excelDate += oneHour) {
+ cal.set(Calendar.HOUR_OF_DAY, hour);
+ Date javaDate = DateUtil.getJavaDate(excelDate, false);
+ assertEquals("Checking " + hour + " hours on Daylight Saving Time start date",
+ excelDate,
+ DateUtil.getExcelDate(javaDate, false), oneMinute);
+
+ // perform the same checks using LocalDateTime
+ LocalDateTime localDate = DateUtil.getLocalDateTime(excelDate, false);
+ assertEquals("Checking " + hour + " hours on Daylight Saving Time start date",
+ excelDate,
+ DateUtil.getExcelDate(localDate, false), oneMinute);
+ }
+ }
+
+ /**
+ * Tests that we deal with time-zones properly
+ */
+ @Test
+ public void calendarConversion() {
+ TimeZone userTZ = LocaleUtil.getUserTimeZone();
+ LocaleUtil.setUserTimeZone(TimeZone.getTimeZone("CET"));
+ try {
+ Calendar cal = LocaleUtil.getLocaleCalendar(2002,JANUARY,1,12,1,1);
+ Date expected = cal.getTime();
+
+ // Iterating over the hours exposes any rounding issues.
+ for (int hour = -12; hour <= 12; hour++)
+ {
+ String id = "GMT" + (hour < 0 ? "" : "+") + hour + ":00";
+ cal.setTimeZone(TimeZone.getTimeZone(id));
+ cal.set(Calendar.HOUR_OF_DAY, 12);
+ double excelDate = DateUtil.getExcelDate(cal, false);
+ Date javaDate = DateUtil.getJavaDate(excelDate);
+
+ // Should match despite time-zone
+ assertEquals("Checking timezone " + id, expected.getTime(), javaDate.getTime());
+ }
+
+ // Check that the timezone aware getter works correctly
+ TimeZone cet = TimeZone.getTimeZone("Europe/Copenhagen");
+ TimeZone ldn = TimeZone.getTimeZone("Europe/London");
+
+ // 12:45 on 27th April 2012
+ double excelDate = 41026.53125;
+
+ // Same, no change
+ assertEquals(
+ DateUtil.getJavaDate(excelDate, false).getTime(),
+ DateUtil.getJavaDate(excelDate, false, cet).getTime()
+ );
+
+ // London vs Copenhagen, should differ by an hour
+ Date cetDate = DateUtil.getJavaDate(excelDate, false);
+ Date ldnDate = DateUtil.getJavaDate(excelDate, false, ldn);
+ assertEquals(ldnDate.getTime() - cetDate.getTime(), 60*60*1000);
+ } finally {
+ LocaleUtil.setUserTimeZone(userTZ);
+ }
+ }
+
+ /**
+ * Tests that we correctly detect date formats as such
+ */
+ @Test
+ public void identifyDateFormats() {
+ // First up, try with a few built in date formats
+ short[] builtins = new short[] { 0x0e, 0x0f, 0x10, 0x16, 0x2d, 0x2e };
+ for (short builtin : builtins) {
+ String formatStr = HSSFDataFormat.getBuiltinFormat(builtin);
+ assertTrue( DateUtil.isInternalDateFormat(builtin) );
+ assertTrue( DateUtil.isADateFormat(builtin,formatStr) );
+ }
+
+ // Now try a few built-in non date formats
+ builtins = new short[] { 0x01, 0x02, 0x17, 0x1f, 0x30 };
+ for (short builtin : builtins) {
+ String formatStr = HSSFDataFormat.getBuiltinFormat(builtin);
+ assertFalse( DateUtil.isInternalDateFormat(builtin) );
+ assertFalse( DateUtil.isADateFormat(builtin,formatStr) );
+ }
+
+ // Now for some non-internal ones
+ // These come after the real ones
+ int numBuiltins = HSSFDataFormat.getNumberOfBuiltinBuiltinFormats();
+ assertTrue(numBuiltins < 60);
+ short formatId = 60;
+ assertFalse( DateUtil.isInternalDateFormat(formatId) );
+
+ // Valid ones first
+ String[] formats = new String[] {
+ "yyyy-mm-dd", "yyyy/mm/dd", "yy/mm/dd", "yy/mmm/dd",
+ "dd/mm/yy", "dd/mm/yyyy", "dd/mmm/yy",
+ "dd-mm-yy", "dd-mm-yyyy",
+ "DD-MM-YY", "DD-mm-YYYY",
+ "dd\\-mm\\-yy", // Sometimes escaped
+ "dd.mm.yyyy", "dd\\.mm\\.yyyy",
+ "dd\\ mm\\.yyyy AM", "dd\\ mm\\.yyyy pm",
+ "dd\\ mm\\.yyyy\\-dd", "[h]:mm:ss",
+ "mm/dd/yy", "\"mm\"/\"dd\"/\"yy\"",
+ "m\\/d\\/yyyy",
+
+ // These crazy ones are valid
+ "yyyy-mm-dd;@", "yyyy/mm/dd;@",
+ "dd-mm-yy;@", "dd-mm-yyyy;@",
+ // These even crazier ones are also valid
+ // (who knows what they mean though...)
+ "[$-F800]dddd\\,\\ mmm\\ dd\\,\\ yyyy",
+ "[$-F900]ddd/mm/yyy",
+ // These ones specify colours, who knew that was allowed?
+ "[BLACK]dddd/mm/yy",
+ "[yeLLow]yyyy-mm-dd"
+ };
+ for (String format : formats) {
+ assertTrue(
+ format + " is a date format",
+ DateUtil.isADateFormat(formatId, format)
+ );
+ }
+
+ // Then time based ones too
+ formats = new String[] {
+ "yyyy-mm-dd hh:mm:ss", "yyyy/mm/dd HH:MM:SS",
+ "mm/dd HH:MM", "yy/mmm/dd SS",
+ "mm/dd HH:MM AM", "mm/dd HH:MM am",
+ "mm/dd HH:MM PM", "mm/dd HH:MM pm",
+ "m/d/yy h:mm AM/PM",
+ "hh:mm:ss", "hh:mm:ss.0", "mm:ss.0",
+ //support elapsed time [h],[m],[s]
+ "[hh]", "[mm]", "[ss]", "[SS]", "[red][hh]"
+ };
+ for (String format : formats) {
+ assertTrue(
+ format + " is a datetime format",
+ DateUtil.isADateFormat(formatId, format)
+ );
+ }
+
+ // Then invalid ones
+ formats = new String[] {
+ "yyyy*mm*dd",
+ "0.0", "0.000",
+ "0%", "0.0%",
+ "[]Foo", "[BLACK]0.00%",
+ "[ms]", "[Mh]",
+ "", null
+ };
+ for (String format : formats) {
+ assertFalse(
+ format + " is not a date or datetime format",
+ DateUtil.isADateFormat(formatId, format)
+ );
+ }
+
+ // And these are ones we probably shouldn't allow,
+ // but would need a better regexp
+ formats = new String[] {
+ "yyyy:mm:dd",
+ };
+ for (String format : formats) {
+ // assertFalse( DateUtil.isADateFormat(formatId, formats[i]) );
+ }
+ }
+
+ @Test
+ public void excelDateBorderCases() throws ParseException {
+ SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd", Locale.ROOT);
+ df.setTimeZone(LocaleUtil.getUserTimeZone());
+
+ Date date1 = df.parse("1900-01-01");
+ assertEquals(1.0, DateUtil.getExcelDate(date1), 0.00001);
+ assertEquals(1.0, DateUtil.getExcelDate(DateUtil.toLocalDateTime(date1)), 0.00001);
+ Date date31 = df.parse("1900-01-31");
+ assertEquals(31.0, DateUtil.getExcelDate(date31), 0.00001);
+ assertEquals(31.0, DateUtil.getExcelDate(DateUtil.toLocalDateTime(date31)), 0.00001);
+ Date date32 = df.parse("1900-02-01");
+ assertEquals(32.0, DateUtil.getExcelDate(date32), 0.00001);
+ assertEquals(32.0, DateUtil.getExcelDate(DateUtil.toLocalDateTime(date32)), 0.00001);
+ Date dateMinus1 = df.parse("1899-12-31");
+ assertEquals(/* BAD_DATE! */ -1.0, DateUtil.getExcelDate(dateMinus1), 0.00001);
+ assertEquals(/* BAD_DATE! */ -1.0, DateUtil.getExcelDate(DateUtil.toLocalDateTime(dateMinus1)), 0.00001);
+ }
+
+ @Test
+ public void dateBug_2Excel() {
+ assertEquals(59.0, DateUtil.getExcelDate(createDate(1900, FEBRUARY, 28), false), 0.00001);
+ assertEquals(61.0, DateUtil.getExcelDate(createDate(1900, MARCH, 1), false), 0.00001);
+
+ assertEquals(37315.00, DateUtil.getExcelDate(createDate(2002, FEBRUARY, 28), false), 0.00001);
+ assertEquals(37316.00, DateUtil.getExcelDate(createDate(2002, MARCH, 1), false), 0.00001);
+ assertEquals(37257.00, DateUtil.getExcelDate(createDate(2002, JANUARY, 1), false), 0.00001);
+ assertEquals(38074.00, DateUtil.getExcelDate(createDate(2004, MARCH, 28), false), 0.00001);
+
+ // perform the same checks using LocalDateTime
+ assertEquals(59.0, DateUtil.getExcelDate(LocalDateTime.of(1900, 2, 28, 0,0), false), 0.00001);
+ assertEquals(61.0, DateUtil.getExcelDate(LocalDateTime.of(1900, 3, 1, 0,0), false), 0.00001);
+
+ assertEquals(37315.00, DateUtil.getExcelDate(LocalDateTime.of(2002, 2, 28, 0,0), false), 0.00001);
+ assertEquals(37316.00, DateUtil.getExcelDate(LocalDateTime.of(2002, 3, 1, 0,0), false), 0.00001);
+ assertEquals(37257.00, DateUtil.getExcelDate(LocalDateTime.of(2002, 1, 1, 0,0), false), 0.00001);
+ assertEquals(38074.00, DateUtil.getExcelDate(LocalDateTime.of(2004, 3, 28, 0,0), false), 0.00001);
+ }
+
+ @Test
+ public void dateBug_2Java() {
+ assertEquals(createDate(1900, FEBRUARY, 28), DateUtil.getJavaDate(59.0, false));
+ assertEquals(createDate(1900, MARCH, 1), DateUtil.getJavaDate(61.0, false));
+
+ assertEquals(createDate(2002, FEBRUARY, 28), DateUtil.getJavaDate(37315.00, false));
+ assertEquals(createDate(2002, MARCH, 1), DateUtil.getJavaDate(37316.00, false));
+ assertEquals(createDate(2002, JANUARY, 1), DateUtil.getJavaDate(37257.00, false));
+ assertEquals(createDate(2004, MARCH, 28), DateUtil.getJavaDate(38074.00, false));
+
+ // perform the same checks using LocalDateTime
+ assertEquals(LocalDateTime.of(1900, 2, 28, 0, 0), DateUtil.getLocalDateTime(59.0, false));
+ assertEquals(LocalDateTime.of(1900, 3, 1, 0, 0), DateUtil.getLocalDateTime(61.0, false));
+
+ assertEquals(LocalDateTime.of(2002, 2, 28, 0, 0), DateUtil.getLocalDateTime(37315.00, false));
+ assertEquals(LocalDateTime.of(2002, 3, 1, 0, 0), DateUtil.getLocalDateTime(37316.00, false));
+ assertEquals(LocalDateTime.of(2002, 1, 1, 0, 0), DateUtil.getLocalDateTime(37257.00, false));
+ assertEquals(LocalDateTime.of(2004, 3, 28, 0, 0), DateUtil.getLocalDateTime(38074.00, false));
+ }
+
+ @Test
+ public void date1904() {
+ assertEquals(createDate(1904, JANUARY, 2), DateUtil.getJavaDate(1.0, true));
+ assertEquals(createDate(1904, JANUARY, 1), DateUtil.getJavaDate(0.0, true));
+ assertEquals(0.0, DateUtil.getExcelDate(createDate(1904, JANUARY, 1), true), 0.00001);
+ assertEquals(1.0, DateUtil.getExcelDate(createDate(1904, JANUARY, 2), true), 0.00001);
+
+ assertEquals(createDate(1998, JULY, 5), DateUtil.getJavaDate(35981, false));
+ assertEquals(createDate(1998, JULY, 5), DateUtil.getJavaDate(34519, true));
+
+ assertEquals(35981.0, DateUtil.getExcelDate(createDate(1998, JULY, 5), false), 0.00001);
+ assertEquals(34519.0, DateUtil.getExcelDate(createDate(1998, JULY, 5), true), 0.00001);
+
+ // perform the same checks using LocalDateTime
+ assertEquals(LocalDateTime.of(1904, 1, 2, 0, 0), DateUtil.getLocalDateTime(1.0, true));
+ assertEquals(LocalDateTime.of(1904, 1, 1, 0, 0), DateUtil.getLocalDateTime(0.0, true));
+ assertEquals(0.0, DateUtil.getExcelDate(LocalDateTime.of(1904, 1, 1, 0, 0), true), 0.00001);
+ assertEquals(1.0, DateUtil.getExcelDate(LocalDateTime.of(1904, 1, 2, 0, 0), true), 0.00001);
+
+ assertEquals(LocalDateTime.of(1998, 7, 5, 0, 0), DateUtil.getLocalDateTime(35981, false));
+ assertEquals(LocalDateTime.of(1998, 7, 5, 0, 0), DateUtil.getLocalDateTime(34519, true));
+
+ assertEquals(35981.0, DateUtil.getExcelDate(LocalDateTime.of(1998, 7, 5, 0, 0), false), 0.00001);
+ assertEquals(34519.0, DateUtil.getExcelDate(LocalDateTime.of(1998, 7, 5, 0, 0), true), 0.00001);
+ }
+
+ /**
+ * @param month zero based
+ * @param day one based
+ */
+ private static Date createDate(int year, int month, int day) {
+ return createDate(year, month, day, 0, 0, 0);
+ }
+
+ /**
+ * @param month zero based
+ * @param day one based
+ */
+ private static Date createDate(int year, int month, int day, int hour, int minute, int second) {
+ Calendar c = LocaleUtil.getLocaleCalendar(year, month, day, hour, minute, second);
+ return c.getTime();
+ }
+
+ /**
+ * Check if DateUtil.getAbsoluteDay works as advertised.
+ */
+ @Test
+ public void absoluteDay() {
+ // 1 Jan 1900 is 1 day after 31 Dec 1899
+ Calendar cal = LocaleUtil.getLocaleCalendar(1900,JANUARY,1,0,0,0);
+ assertEquals("Checking absolute day (1 Jan 1900)", 1, DateUtil.absoluteDay(cal, false));
+ LocalDateTime ldt = LocalDateTime.of(1900,1,1,0,0,0);
+ assertEquals("Checking absolute day (1 Jan 1900) (LocalDateTime)", 1, DateUtil.absoluteDay(ldt, false));
+ // 1 Jan 1901 is 366 days after 31 Dec 1899
+ ldt = LocalDateTime.of(1901,1,1,0,0,0);
+ cal.set(1901,JANUARY,1,0,0,0);
+ assertEquals("Checking absolute day (1 Jan 1901) (LocalDateTime)", 366, DateUtil.absoluteDay(ldt, false));
+ }
+
+ @Test
+ public void absoluteDayYearTooLow() {
+ Calendar cal = LocaleUtil.getLocaleCalendar(1899,JANUARY,1,0,0,0);
+ try {
+ DateUtil.absoluteDay(cal, false);
+ fail("Should fail here");
+ } catch (IllegalArgumentException e) {
+ // expected here
+ }
+
+ try {
+ cal.set(1903,JANUARY,1,0,0,0);
+ DateUtil.absoluteDay(cal, true);
+ fail("Should fail here");
+ } catch (IllegalArgumentException e) {
+ // expected here
+ }
+
+ // same for LocalDateTime
+ try {
+ DateUtil.absoluteDay(LocalDateTime.of(1899,1,1,0,0,0), false);
+ fail("Should fail here");
+ } catch (IllegalArgumentException e) {
+ // expected here
+ }
+
+ try {
+ DateUtil.absoluteDay(LocalDateTime.of(1903,1,1,0,0,0), true);
+ fail("Should fail here");
+ } catch (IllegalArgumentException e) {
+ // expected here
+ }
+ }
+
+ @Test
+ public void convertTime() {
+
+ final double delta = 1E-7; // a couple of digits more accuracy than strictly required
+ assertEquals(0.5, DateUtil.convertTime("12:00"), delta);
+ assertEquals(2.0/3, DateUtil.convertTime("16:00"), delta);
+ assertEquals(0.0000116, DateUtil.convertTime("0:00:01"), delta);
+ assertEquals(0.7330440, DateUtil.convertTime("17:35:35"), delta);
+ }
+
+ @Test
+ public void parseDate() {
+ assertEquals(createDate(2008, AUGUST, 3), DateUtil.parseYYYYMMDDDate("2008/08/03"));
+ assertEquals(createDate(1994, MAY, 1), DateUtil.parseYYYYMMDDDate("1994/05/01"));
+ }
+
+ /**
+ * Ensure that date values *with* a fractional portion get the right time of day
+ */
+ @Test
+ public void convertDateTime() {
+ // Excel day 30000 is date 18-Feb-1982
+ // 0.7 corresponds to time 16:48:00
+ Date actual = DateUtil.getJavaDate(30000.7);
+ Date expected = createDate(1982, 1, 18, 16, 48, 0);
+ assertEquals(expected, actual);
+
+ // note that months in Calendar are zero-based, in LocalDateTime one-based
+ LocalDateTime actualLocalDate = DateUtil.getLocalDateTime(30000.7);
+ LocalDateTime expectedLocalDate = LocalDateTime.of(1982, 2, 18, 16, 48, 0);
+ assertEquals(expectedLocalDate, actualLocalDate);
+ }
+
+ /**
+ * User reported a datetime issue in POI-2.5:
+ * Setting Cell's value to Jan 1, 1900 without a time doesn't return the same value set to
+ * @throws IOException
+ */
+ @Test
+ public void bug19172() throws IOException
+ {
+ HSSFWorkbook workbook = new HSSFWorkbook();
+ HSSFSheet sheet = workbook.createSheet();
+ HSSFCell cell = sheet.createRow(0).createCell(0);
+
+ // A pseudo special Excel dates
+ Calendar cal = LocaleUtil.getLocaleCalendar(1900, JANUARY, 1);
+
+ Date valueToTest = cal.getTime();
+
+ cell.setCellValue(valueToTest);
+
+ Date returnedValue = cell.getDateCellValue();
+
+ assertEquals(valueToTest.getTime(), returnedValue.getTime());
+
+ workbook.close();
+ }
+
+ /**
+ * DateUtil.isCellFormatted(Cell) should not true for a numeric cell
+ * that's formatted as ".0000"
+ */
+ @Test
+ public void bug54557() throws Exception {
+ final String format = ".0000";
+ boolean isDateFormat = DateUtil.isADateFormat(165, format);
+
+ assertEquals(false, isDateFormat);
+ }
+
+ @Test
+ public void bug56269() throws Exception {
+ double excelFraction = 41642.45833321759d;
+ Calendar calNoRound = DateUtil.getJavaCalendar(excelFraction, false);
+ assertEquals(10, calNoRound.get(Calendar.HOUR));
+ assertEquals(59, calNoRound.get(Calendar.MINUTE));
+ assertEquals(59, calNoRound.get(Calendar.SECOND));
+ Calendar calRound = DateUtil.getJavaCalendar(excelFraction, false, null, true);
+ assertEquals(11, calRound.get(Calendar.HOUR));
+ assertEquals(0, calRound.get(Calendar.MINUTE));
+ assertEquals(0, calRound.get(Calendar.SECOND));
+ }
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org