--- src/java/org/apache/poi/ss/formula/atp/YearFracCalculator.java (revision 1588874) +++ src/java/org/apache/poi/ss/formula/atp/YearFracCalculator.java (working copy) @@ -317,7 +317,7 @@ 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); } --- src/java/org/apache/poi/ss/usermodel/DateUtil.java (revision 1588874) +++ src/java/org/apache/poi/ss/usermodel/DateUtil.java (working copy) @@ -185,11 +185,32 @@ * @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. * * NOTE: If the default TimeZone in Java uses Daylight * Saving Time then the conversion back to an Excel date may not give @@ -207,12 +228,12 @@ * @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,12 +246,26 @@ 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. * @param date The Excel date. * @param use1904windowing true if date uses 1904 windowing, @@ -238,7 +273,7 @@ * @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); } /** @@ -245,22 +280,38 @@ * 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 @@ } 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 @@ } + @SuppressWarnings("serial") private static final class FormatException extends Exception { public FormatException(String msg) { super(msg); --- src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDateUtil.java (revision 1588874) +++ src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDateUtil.java (working copy) @@ -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 @@ * @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,9 +52,9 @@ /** * Checks the date conversion functions in the HSSFDateUtil class. */ + @Test + public void dateConversion() { - public void testDateConversion() { - // Iteratating over the hours exposes any rounding issues. for (int hour = 0; hour < 23; hour++) { @@ -87,7 +90,8 @@ * 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 @@ * 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 @@ * 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 @@ * 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 @@ /** * 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 @@ /** * 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