From faa2a64d4af6e89b62bba899bfe1040675765a5a Mon Sep 17 00:00:00 2001 From: Andreas Beeker Date: Sun, 4 May 2014 21:17:18 +0000 Subject: [PATCH] Bug 56269 - DateFormat - Rounding of fractionals git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1592419 13f79535-47bb-0310-9956-ffa450edef68 --- .../ss/formula/atp/YearFracCalculator.java | 2 +- .../org/apache/poi/ss/usermodel/DateUtil.java | 76 ++++++++++++++++--- .../poi/hssf/usermodel/TestHSSFDateUtil.java | 74 +++++++++++++----- 3 files changed, 118 insertions(+), 34 deletions(-) diff --git a/src/java/org/apache/poi/ss/formula/atp/YearFracCalculator.java b/src/java/org/apache/poi/ss/formula/atp/YearFracCalculator.java index 765b23ae6c..9fda8dcfb0 100644 --- a/src/java/org/apache/poi/ss/formula/atp/YearFracCalculator.java +++ b/src/java/org/apache/poi/ss/formula/atp/YearFracCalculator.java @@ -317,7 +317,7 @@ final class YearFracCalculator { private static SimpleDate createDate(int dayCount) { GregorianCalendar calendar = new GregorianCalendar(UTC_TIME_ZONE); - DateUtil.setCalendar(calendar, dayCount, 0, false); + DateUtil.setCalendar(calendar, dayCount, 0, false, false); return new SimpleDate(calendar); } diff --git a/src/java/org/apache/poi/ss/usermodel/DateUtil.java b/src/java/org/apache/poi/ss/usermodel/DateUtil.java index 6bd832dfbb..e4d870de7f 100644 --- a/src/java/org/apache/poi/ss/usermodel/DateUtil.java +++ b/src/java/org/apache/poi/ss/usermodel/DateUtil.java @@ -185,8 +185,29 @@ public class DateUtil { * @return Java representation of the date, or null if date is not a valid Excel date */ public static Date getJavaDate(double date, boolean use1904windowing, TimeZone tz) { - return getJavaCalendar(date, use1904windowing, tz).getTime(); + return getJavaCalendar(date, use1904windowing, tz, false).getTime(); } + + /** + * Given an Excel date with either 1900 or 1904 date windowing, + * converts it to a java.util.Date. + * + * Excel Dates and Times are stored without any timezone + * information. If you know (through other means) that your file + * uses a different TimeZone to the system default, you can use + * this version of the getJavaDate() method to handle it. + * + * @param date The Excel date. + * @param tz The TimeZone to evaluate the date in + * @param use1904windowing true if date uses 1904 windowing, + * or false if using 1900 date windowing. + * @param roundSeconds round to closest second + * @return Java representation of the date, or null if date is not a valid Excel date + */ + public static Date getJavaDate(double date, boolean use1904windowing, TimeZone tz, boolean roundSeconds) { + return getJavaCalendar(date, use1904windowing, tz, roundSeconds).getTime(); + } + /** * Given an Excel date with either 1900 or 1904 date windowing, * converts it to a java.util.Date. @@ -207,12 +228,12 @@ public class DateUtil { * @see java.util.TimeZone */ public static Date getJavaDate(double date, boolean use1904windowing) { - return getJavaCalendar(date, use1904windowing).getTime(); + return getJavaCalendar(date, use1904windowing, null, false).getTime(); } public static void setCalendar(Calendar calendar, int wholeDays, - int millisecondsInDay, boolean use1904windowing) { + int millisecondsInDay, boolean use1904windowing, boolean roundSeconds) { int startYear = 1900; int dayAdjust = -1; // Excel thinks 2/29/1900 is a valid date, which it isn't if (use1904windowing) { @@ -225,10 +246,24 @@ public class DateUtil { dayAdjust = 0; } calendar.set(startYear,0, wholeDays + dayAdjust, 0, 0, 0); - calendar.set(GregorianCalendar.MILLISECOND, millisecondsInDay); + calendar.set(Calendar.MILLISECOND, millisecondsInDay); + if (roundSeconds) { + calendar.add(Calendar.MILLISECOND, 500); + calendar.clear(Calendar.MILLISECOND); + } } + /** + * Get EXCEL date as Java Calendar (with default time zone). + * This is like {@link #getJavaDate(double)} but returns a Calendar object. + * @param date The Excel date. + * @return Java representation of the date, or null if date is not a valid Excel date + */ + public static Calendar getJavaCalendar(double date) { + return getJavaCalendar(date, false, (TimeZone)null, false); + } + /** * Get EXCEL date as Java Calendar (with default time zone). * This is like {@link #getJavaDate(double, boolean)} but returns a Calendar object. @@ -238,29 +273,45 @@ public class DateUtil { * @return Java representation of the date, or null if date is not a valid Excel date */ public static Calendar getJavaCalendar(double date, boolean use1904windowing) { - return getJavaCalendar(date, use1904windowing, (TimeZone)null); + return getJavaCalendar(date, use1904windowing, (TimeZone)null, false); } /** * Get EXCEL date as Java Calendar with UTC time zone. * This is similar to {@link #getJavaDate(double, boolean)} but returns a * Calendar object that has UTC as time zone, so no daylight saving hassle. - * @param date The Excel date. - * @param use1904windowing true if date uses 1904 windowing, - * or false if using 1900 date windowing. - * @return Java representation of the date in UTC, or null if date is not a valid Excel date + * @param date The Excel date. + * @param use1904windowing true if date uses 1904 windowing, + * or false if using 1900 date windowing. + * @return Java representation of the date in UTC, or null if date is not a valid Excel date */ public static Calendar getJavaCalendarUTC(double date, boolean use1904windowing) { - return getJavaCalendar(date, use1904windowing, TIMEZONE_UTC); + return getJavaCalendar(date, use1904windowing, TIMEZONE_UTC, false); } /** * Get EXCEL date as Java Calendar with given time zone. - * @see #getJavaDate(double, TimeZone) + * @param date The Excel date. + * @param use1904windowing true if date uses 1904 windowing, + * or false if using 1900 date windowing. + * @param timeZone The TimeZone to evaluate the date in * @return Java representation of the date, or null if date is not a valid Excel date */ public static Calendar getJavaCalendar(double date, boolean use1904windowing, TimeZone timeZone) { + return getJavaCalendar(date, use1904windowing, timeZone, false); + } + + /** + * Get EXCEL date as Java Calendar with given time zone. + * @param date The Excel date. + * @param use1904windowing true if date uses 1904 windowing, + * or false if using 1900 date windowing. + * @param timeZone The TimeZone to evaluate the date in + * @param roundSeconds round to closest second + * @return Java representation of the date, or null if date is not a valid Excel date + */ + public static Calendar getJavaCalendar(double date, boolean use1904windowing, TimeZone timeZone, boolean roundSeconds) { if (!isValidExcelDate(date)) { return null; } @@ -272,7 +323,7 @@ public class DateUtil { } else { calendar = new GregorianCalendar(); // using default time-zone } - setCalendar(calendar, wholeDays, millisecondsInDay, use1904windowing); + setCalendar(calendar, wholeDays, millisecondsInDay, use1904windowing, roundSeconds); return calendar; } @@ -537,6 +588,7 @@ public class DateUtil { } + @SuppressWarnings("serial") private static final class FormatException extends Exception { public FormatException(String msg) { super(msg); diff --git a/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDateUtil.java b/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDateUtil.java index 6733f73fa2..022975d9ae 100644 --- a/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDateUtil.java +++ b/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDateUtil.java @@ -17,15 +17,18 @@ package org.apache.poi.hssf.usermodel; +import static org.junit.Assert.assertEquals; +import static org.junit.Assert.assertFalse; +import static org.junit.Assert.assertTrue; + import java.util.Calendar; import java.util.Date; import java.util.GregorianCalendar; import java.util.TimeZone; -import junit.framework.TestCase; - import org.apache.poi.hssf.HSSFTestDataSamples; import org.apache.poi.hssf.model.InternalWorkbook; +import org.junit.Test; /** * Class TestHSSFDateUtil @@ -37,7 +40,7 @@ import org.apache.poi.hssf.model.InternalWorkbook; * @author Alex Jacoby (ajacoby at gmail.com) * @version %I%, %G% */ -public final class TestHSSFDateUtil extends TestCase { +public final class TestHSSFDateUtil { public static final int CALENDAR_JANUARY = 0; public static final int CALENDAR_FEBRUARY = 1; @@ -49,8 +52,8 @@ public final class TestHSSFDateUtil extends TestCase { /** * Checks the date conversion functions in the HSSFDateUtil class. */ - - public void testDateConversion() { + @Test + public void dateConversion() { // Iteratating over the hours exposes any rounding issues. for (int hour = 0; hour < 23; hour++) @@ -87,7 +90,8 @@ public final class TestHSSFDateUtil extends TestCase { * Checks the conversion of a java.util.date to Excel on a day when * Daylight Saving Time starts. */ - public void testExcelConversionOnDSTStart() { + @Test + public void excelConversionOnDSTStart() { TimeZone cet = TimeZone.getTimeZone("Europe/Copenhagen"); TimeZone.setDefault(cet); Calendar cal = new GregorianCalendar(2004, CALENDAR_MARCH, 28); @@ -117,7 +121,8 @@ public final class TestHSSFDateUtil extends TestCase { * Checks the conversion of an Excel date to a java.util.date on a day when * Daylight Saving Time starts. */ - public void testJavaConversionOnDSTStart() { + @Test + public void javaConversionOnDSTStart() { TimeZone cet = TimeZone.getTimeZone("Europe/Copenhagen"); TimeZone.setDefault(cet); Calendar cal = new GregorianCalendar(2004, CALENDAR_MARCH, 28); @@ -144,7 +149,8 @@ public final class TestHSSFDateUtil extends TestCase { * Checks the conversion of a java.util.Date to Excel on a day when * Daylight Saving Time ends. */ - public void testExcelConversionOnDSTEnd() { + @Test + public void excelConversionOnDSTEnd() { TimeZone cet = TimeZone.getTimeZone("Europe/Copenhagen"); TimeZone.setDefault(cet); Calendar cal = new GregorianCalendar(2004, CALENDAR_OCTOBER, 31); @@ -167,7 +173,8 @@ public final class TestHSSFDateUtil extends TestCase { * Checks the conversion of an Excel date to java.util.Date on a day when * Daylight Saving Time ends. */ - public void testJavaConversionOnDSTEnd() { + @Test + public void javaConversionOnDSTEnd() { TimeZone cet = TimeZone.getTimeZone("Europe/Copenhagen"); TimeZone.setDefault(cet); Calendar cal = new GregorianCalendar(2004, CALENDAR_OCTOBER, 31); @@ -186,7 +193,8 @@ public final class TestHSSFDateUtil extends TestCase { /** * Tests that we deal with time-zones properly */ - public void testCalendarConversion() { + @Test + public void calendarConversion() { GregorianCalendar date = new GregorianCalendar(2002, 0, 1, 12, 1, 1); Date expected = date.getTime(); @@ -226,7 +234,8 @@ public final class TestHSSFDateUtil extends TestCase { /** * Tests that we correctly detect date formats as such */ - public void testIdentifyDateFormats() { + @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(int i=0; i