Attachment #31588 for bug #56269

View | Details | Raw Unified | Return to bug 56269
Collapse All | Expand All

Lines 317-323 Link Here

(-)src/java/org/apache/poi/ss/formula/atp/YearFracCalculator.java (-1 / +1 lines)
317		317
318	private static SimpleDate createDate(int dayCount) {	318	private static SimpleDate createDate(int dayCount) {
319	GregorianCalendar calendar = new GregorianCalendar(UTC_TIME_ZONE);	319	GregorianCalendar calendar = new GregorianCalendar(UTC_TIME_ZONE);
320	DateUtil.setCalendar(calendar, dayCount, 0, false);	320	DateUtil.setCalendar(calendar, dayCount, 0, false, false);
321	return new SimpleDate(calendar);	321	return new SimpleDate(calendar);
322	}	322	}
323		323




     *  @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, 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 <code>TimeZone</code> in Java uses Daylight
     *  Saving Time then the conversion back to an Excel date may not give

     *  @see java.util.TimeZone
     */
    public static Date getJavaDate(double date, boolean use1904windowing) {
        return getJavaCalendar(date, use1904windowing, null, false).getTime();
    }


    public static void setCalendar(Calendar calendar, int wholeDays,
            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) {

            dayAdjust = 0;
        }
        calendar.set(startYear,0, wholeDays + dayAdjust, 0, 0, 0);
        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,

     *  @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, 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
     */
    public static Calendar getJavaCalendarUTC(double date, boolean use1904windowing) {
    	return getJavaCalendar(date, use1904windowing, TIMEZONE_UTC, 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
     * @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;
        }

        } else {
            calendar = new GregorianCalendar();     // using default time-zone
        }
        setCalendar(calendar, wholeDays, millisecondsInDay, use1904windowing, roundSeconds);
        return calendar;
    }


    }


    @SuppressWarnings("serial")
    private static final class FormatException extends Exception {
        public FormatException(String msg) {
            super(msg);





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

 * @author Alex Jacoby (ajacoby at gmail.com)
 * @version %I%, %G%
 */
public final class TestHSSFDateUtil {

    public static final int CALENDAR_JANUARY = 0;
    public static final int CALENDAR_FEBRUARY = 1;

    /**
     * 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++)
        {

     * Checks the conversion of a java.util.date to Excel on a day when
     * Daylight Saving Time starts.
     */
    @Test
    public void excelConversionOnDSTStart() {
        TimeZone cet = TimeZone.getTimeZone("Europe/Copenhagen");
        TimeZone.setDefault(cet);
        Calendar cal = new GregorianCalendar(2004, CALENDAR_MARCH, 28);

     * Checks the conversion of an Excel date to a java.util.date on a day when
     * Daylight Saving Time starts.
     */
    @Test
    public void javaConversionOnDSTStart() {
        TimeZone cet = TimeZone.getTimeZone("Europe/Copenhagen");
        TimeZone.setDefault(cet);
        Calendar cal = new GregorianCalendar(2004, CALENDAR_MARCH, 28);

     * Checks the conversion of a java.util.Date to Excel on a day when
     * Daylight Saving Time ends.
     */
    @Test
    public void excelConversionOnDSTEnd() {
        TimeZone cet = TimeZone.getTimeZone("Europe/Copenhagen");
        TimeZone.setDefault(cet);
        Calendar cal = new GregorianCalendar(2004, CALENDAR_OCTOBER, 31);

     * Checks the conversion of an Excel date to java.util.Date on a day when
     * Daylight Saving Time ends.
     */
    @Test
    public void javaConversionOnDSTEnd() {
        TimeZone cet = TimeZone.getTimeZone("Europe/Copenhagen");
        TimeZone.setDefault(cet);
        Calendar cal = new GregorianCalendar(2004, CALENDAR_OCTOBER, 31);

    /**
     * Tests that we deal with time-zones properly
     */
    @Test
    public void calendarConversion() {
        GregorianCalendar date = new GregorianCalendar(2002, 0, 1, 12, 1, 1);
        Date expected = date.getTime();


    /**
     * 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(int i=0; i<builtins.length; i++) {

     * Test that against a real, test file, we still do everything
     *  correctly
     */
    @Test
    public void onARealFile() {

        HSSFWorkbook workbook = HSSFTestDataSamples.openSampleWorkbook("DateFormats.xls");
        HSSFSheet sheet       = workbook.getSheetAt(0);

        assertTrue(HSSFDateUtil.isCellDateFormatted(cell));
    }

    @Test
    public void dateBug_2Excel() {
        assertEquals(59.0, HSSFDateUtil.getExcelDate(createDate(1900, CALENDAR_FEBRUARY, 28), false), 0.00001);
        assertEquals(61.0, HSSFDateUtil.getExcelDate(createDate(1900, CALENDAR_MARCH, 1), false), 0.00001);


        assertEquals(38074.00, HSSFDateUtil.getExcelDate(createDate(2004, CALENDAR_MARCH, 28), false), 0.00001);
    }

    @Test
    public void dateBug_2Java() {
        assertEquals(createDate(1900, CALENDAR_FEBRUARY, 28), HSSFDateUtil.getJavaDate(59.0, false));
        assertEquals(createDate(1900, CALENDAR_MARCH, 1), HSSFDateUtil.getJavaDate(61.0, false));


        assertEquals(createDate(2004, CALENDAR_MARCH, 28), HSSFDateUtil.getJavaDate(38074.00, false));
    }

    @Test
    public void date1904() {
        assertEquals(createDate(1904, CALENDAR_JANUARY, 2), HSSFDateUtil.getJavaDate(1.0, true));
        assertEquals(createDate(1904, CALENDAR_JANUARY, 1), HSSFDateUtil.getJavaDate(0.0, true));
        assertEquals(0.0, HSSFDateUtil.getExcelDate(createDate(1904, CALENDAR_JANUARY, 1), true), 0.00001);

    /**
     * Check if HSSFDateUtil.getAbsoluteDay works as advertised.
     */
    @Test
    public void absoluteDay() {
        // 1 Jan 1900 is 1 day after 31 Dec 1899
        GregorianCalendar calendar = new GregorianCalendar(1900, 0, 1);
        assertEquals("Checking absolute day (1 Jan 1900)", 1, HSSFDateUtil.absoluteDay(calendar, false));

        assertEquals("Checking absolute day (1 Jan 1901)", 366, HSSFDateUtil.absoluteDay(calendar, false));
    }

    @Test
    public void convertTime() {

        final double delta = 1E-7; // a couple of digits more accuracy than strictly required
        assertEquals(0.5, HSSFDateUtil.convertTime("12:00"), delta);

        assertEquals(0.7330440, HSSFDateUtil.convertTime("17:35:35"), delta);
    }

    @Test
    public void parseDate() {
        assertEquals(createDate(2008, Calendar.AUGUST, 3), HSSFDateUtil.parseYYYYMMDDDate("2008/08/03"));
        assertEquals(createDate(1994, Calendar.MAY, 1), HSSFDateUtil.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 = HSSFDateUtil.getJavaDate(30000.7);

     * 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
     */
    @Test
    public void bug19172()
    {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet();

     * 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 = HSSFDateUtil.isADateFormat(165, format);
       
       assertEquals(false, isDateFormat);
    }
    
    @Test
    public void bug56269() throws Exception {
        double excelFraction = 41642.45833321759d;
        Calendar calNoRound = HSSFDateUtil.getJavaCalendar(excelFraction, false);
        assertEquals(10, calNoRound.get(Calendar.HOUR));
        assertEquals(59, calNoRound.get(Calendar.MINUTE));
        assertEquals(59, calNoRound.get(Calendar.SECOND));
        Calendar calRound = HSSFDateUtil.getJavaCalendar(excelFraction, false, null, true);
        assertEquals(11, calRound.get(Calendar.HOUR));
        assertEquals(0, calRound.get(Calendar.MINUTE));
        assertEquals(0, calRound.get(Calendar.SECOND));
    }
}

Return to bug 56269

Lines 185-195 Link Here

(-)src/java/org/apache/poi/ss/usermodel/DateUtil.java (-12 / +64 lines)
185	* @return Java representation of the date, or null if date is not a valid Excel date	185	* @return Java representation of the date, or null if date is not a valid Excel date
186	*/	186	*/
187	public static Date getJavaDate(double date, boolean use1904windowing, TimeZone tz) {	187	public static Date getJavaDate(double date, boolean use1904windowing, TimeZone tz) {
188	return getJavaCalendar(date, use1904windowing, tz).getTime();	188	return getJavaCalendar(date, use1904windowing, tz, false).getTime();
189	}	189	}
		190
190	/**	191	/**
191	* Given an Excel date with either 1900 or 1904 date windowing,	192	* Given an Excel date with either 1900 or 1904 date windowing,
192	* converts it to a java.util.Date.	193	* converts it to a java.util.Date.
		194	*
		195	* Excel Dates and Times are stored without any timezone
		196	* information. If you know (through other means) that your file
		197	* uses a different TimeZone to the system default, you can use
		198	* this version of the getJavaDate() method to handle it.
		199	*
		200	* @param date The Excel date.
		201	* @param tz The TimeZone to evaluate the date in
		202	* @param use1904windowing true if date uses 1904 windowing,
		203	* or false if using 1900 date windowing.
		204	* @param roundSeconds round to closest second
		205	* @return Java representation of the date, or null if date is not a valid Excel date
		206	*/
		207	public static Date getJavaDate(double date, boolean use1904windowing, TimeZone tz, boolean roundSeconds) {
		208	return getJavaCalendar(date, use1904windowing, tz, roundSeconds).getTime();
		209	}
		210
		211	/**
		212	* Given an Excel date with either 1900 or 1904 date windowing,
		213	* converts it to a java.util.Date.
193	*	214	*
194	* NOTE: If the default <code>TimeZone</code> in Java uses Daylight	215	* NOTE: If the default <code>TimeZone</code> in Java uses Daylight
195	* Saving Time then the conversion back to an Excel date may not give	216	* Saving Time then the conversion back to an Excel date may not give
Lines 207-218 Link Here
207	* @see java.util.TimeZone	228	* @see java.util.TimeZone
208	*/	229	*/
209	public static Date getJavaDate(double date, boolean use1904windowing) {	230	public static Date getJavaDate(double date, boolean use1904windowing) {
210	return getJavaCalendar(date, use1904windowing).getTime();	231	return getJavaCalendar(date, use1904windowing, null, false).getTime();
211	}	232	}
212		233
213		234
214	public static void setCalendar(Calendar calendar, int wholeDays,	235	public static void setCalendar(Calendar calendar, int wholeDays,
215	int millisecondsInDay, boolean use1904windowing) {	236	int millisecondsInDay, boolean use1904windowing, boolean roundSeconds) {
216	int startYear = 1900;	237	int startYear = 1900;
217	int dayAdjust = -1; // Excel thinks 2/29/1900 is a valid date, which it isn't	238	int dayAdjust = -1; // Excel thinks 2/29/1900 is a valid date, which it isn't
218	if (use1904windowing) {	239	if (use1904windowing) {
Lines 225-236 Link Here
225	dayAdjust = 0;	246	dayAdjust = 0;
226	}	247	}
227	calendar.set(startYear,0, wholeDays + dayAdjust, 0, 0, 0);	248	calendar.set(startYear,0, wholeDays + dayAdjust, 0, 0, 0);
228	calendar.set(GregorianCalendar.MILLISECOND, millisecondsInDay);	249	calendar.set(Calendar.MILLISECOND, millisecondsInDay);
		250	if (roundSeconds) {
		251	calendar.add(Calendar.MILLISECOND, 500);
		252	calendar.clear(Calendar.MILLISECOND);
		253	}
229	}	254	}
230		255
231		256
232	/**	257	/**
233	* Get EXCEL date as Java Calendar (with default time zone).	258	* Get EXCEL date as Java Calendar (with default time zone).
		259	* This is like {@link #getJavaDate(double)} but returns a Calendar object.
		260	* @param date The Excel date.
		261	* @return Java representation of the date, or null if date is not a valid Excel date
		262	*/
		263	public static Calendar getJavaCalendar(double date) {
		264	return getJavaCalendar(date, false, (TimeZone)null, false);
		265	}
		266
		267	/**
		268	* Get EXCEL date as Java Calendar (with default time zone).
234	* This is like {@link #getJavaDate(double, boolean)} but returns a Calendar object.	269	* This is like {@link #getJavaDate(double, boolean)} but returns a Calendar object.
235	* @param date The Excel date.	270	* @param date The Excel date.
236	* @param use1904windowing true if date uses 1904 windowing,	271	* @param use1904windowing true if date uses 1904 windowing,
Lines 238-244 Link Here
238	* @return Java representation of the date, or null if date is not a valid Excel date	273	* @return Java representation of the date, or null if date is not a valid Excel date
239	*/	274	*/
240	public static Calendar getJavaCalendar(double date, boolean use1904windowing) {	275	public static Calendar getJavaCalendar(double date, boolean use1904windowing) {
241	return getJavaCalendar(date, use1904windowing, (TimeZone)null);	276	return getJavaCalendar(date, use1904windowing, (TimeZone)null, false);
242	}	277	}
243		278
244	/**	279	/**
Lines 245-266 Link Here
245	* Get EXCEL date as Java Calendar with UTC time zone.	280	* Get EXCEL date as Java Calendar with UTC time zone.
246	* This is similar to {@link #getJavaDate(double, boolean)} but returns a	281	* This is similar to {@link #getJavaDate(double, boolean)} but returns a
247	* Calendar object that has UTC as time zone, so no daylight saving hassle.	282	* Calendar object that has UTC as time zone, so no daylight saving hassle.
248	* @param date The Excel date.	283	* @param date The Excel date.
249	* @param use1904windowing true if date uses 1904 windowing,	284	* @param use1904windowing true if date uses 1904 windowing,
250	* or false if using 1900 date windowing.	285	* or false if using 1900 date windowing.
251	* @return Java representation of the date in UTC, or null if date is not a valid Excel date	286	* @return Java representation of the date in UTC, or null if date is not a valid Excel date
252	*/	287	*/
253	public static Calendar getJavaCalendarUTC(double date, boolean use1904windowing) {	288	public static Calendar getJavaCalendarUTC(double date, boolean use1904windowing) {
254	return getJavaCalendar(date, use1904windowing, TIMEZONE_UTC);	289	return getJavaCalendar(date, use1904windowing, TIMEZONE_UTC, false);
255	}	290	}
256		291
257		292
258	/**	293	/**
259	* Get EXCEL date as Java Calendar with given time zone.	294	* Get EXCEL date as Java Calendar with given time zone.
260	* @see #getJavaDate(double, TimeZone)	295	* @param date The Excel date.
		296	* @param use1904windowing true if date uses 1904 windowing,
		297	* or false if using 1900 date windowing.
		298	* @param timeZone The TimeZone to evaluate the date in
261	* @return Java representation of the date, or null if date is not a valid Excel date	299	* @return Java representation of the date, or null if date is not a valid Excel date
262	*/	300	*/
263	public static Calendar getJavaCalendar(double date, boolean use1904windowing, TimeZone timeZone) {	301	public static Calendar getJavaCalendar(double date, boolean use1904windowing, TimeZone timeZone) {
		302	return getJavaCalendar(date, use1904windowing, timeZone, false);
		303	}
		304
		305	/**
		306	* Get EXCEL date as Java Calendar with given time zone.
		307	* @param date The Excel date.
		308	* @param use1904windowing true if date uses 1904 windowing,
		309	* or false if using 1900 date windowing.
		310	* @param timeZone The TimeZone to evaluate the date in
		311	* @param roundSeconds round to closest second
		312	* @return Java representation of the date, or null if date is not a valid Excel date
		313	*/
		314	public static Calendar getJavaCalendar(double date, boolean use1904windowing, TimeZone timeZone, boolean roundSeconds) {
264	if (!isValidExcelDate(date)) {	315	if (!isValidExcelDate(date)) {
265	return null;	316	return null;
266	}	317	}
Lines 272-278 Link Here
272	} else {	323	} else {
273	calendar = new GregorianCalendar(); // using default time-zone	324	calendar = new GregorianCalendar(); // using default time-zone
274	}	325	}
275	setCalendar(calendar, wholeDays, millisecondsInDay, use1904windowing);	326	setCalendar(calendar, wholeDays, millisecondsInDay, use1904windowing, roundSeconds);
276	return calendar;	327	return calendar;
277	}	328	}
278		329
Lines 537-542 Link Here
537	}	588	}
538		589
539		590
		591	@SuppressWarnings("serial")
540	private static final class FormatException extends Exception {	592	private static final class FormatException extends Exception {
541	public FormatException(String msg) {	593	public FormatException(String msg) {
542	super(msg);	594	super(msg);

Lines 17-31 Link Here

(-)src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDateUtil.java (-21 / +53 lines)
17		17
18	package org.apache.poi.hssf.usermodel;	18	package org.apache.poi.hssf.usermodel;
19		19
		20	import static org.junit.Assert.assertEquals;
		21	import static org.junit.Assert.assertFalse;
		22	import static org.junit.Assert.assertTrue;
		23
20	import java.util.Calendar;	24	import java.util.Calendar;
21	import java.util.Date;	25	import java.util.Date;
22	import java.util.GregorianCalendar;	26	import java.util.GregorianCalendar;
23	import java.util.TimeZone;	27	import java.util.TimeZone;
24		28
25	import junit.framework.TestCase;
26
27	import org.apache.poi.hssf.HSSFTestDataSamples;	29	import org.apache.poi.hssf.HSSFTestDataSamples;
28	import org.apache.poi.hssf.model.InternalWorkbook;	30	import org.apache.poi.hssf.model.InternalWorkbook;
		31	import org.junit.Test;
29		32
30	/**	33	/**
31	* Class TestHSSFDateUtil	34	* Class TestHSSFDateUtil
Lines 37-43 Link Here
37	* @author Alex Jacoby (ajacoby at gmail.com)	40	* @author Alex Jacoby (ajacoby at gmail.com)
38	* @version %I%, %G%	41	* @version %I%, %G%
39	*/	42	*/
40	public final class TestHSSFDateUtil extends TestCase {	43	public final class TestHSSFDateUtil {
41		44
42	public static final int CALENDAR_JANUARY = 0;	45	public static final int CALENDAR_JANUARY = 0;
43	public static final int CALENDAR_FEBRUARY = 1;	46	public static final int CALENDAR_FEBRUARY = 1;
Lines 49-57 Link Here
49	/**	52	/**
50	* Checks the date conversion functions in the HSSFDateUtil class.	53	* Checks the date conversion functions in the HSSFDateUtil class.
51	*/	54	*/
		55	@Test
		56	public void dateConversion() {
52		57
53	public void testDateConversion() {
54
55	// Iteratating over the hours exposes any rounding issues.	58	// Iteratating over the hours exposes any rounding issues.
56	for (int hour = 0; hour < 23; hour++)	59	for (int hour = 0; hour < 23; hour++)
57	{	60	{
Lines 87-93 Link Here
87	* Checks the conversion of a java.util.date to Excel on a day when	90	* Checks the conversion of a java.util.date to Excel on a day when
88	* Daylight Saving Time starts.	91	* Daylight Saving Time starts.
89	*/	92	*/
90	public void testExcelConversionOnDSTStart() {	93	@Test
		94	public void excelConversionOnDSTStart() {
91	TimeZone cet = TimeZone.getTimeZone("Europe/Copenhagen");	95	TimeZone cet = TimeZone.getTimeZone("Europe/Copenhagen");
92	TimeZone.setDefault(cet);	96	TimeZone.setDefault(cet);
93	Calendar cal = new GregorianCalendar(2004, CALENDAR_MARCH, 28);	97	Calendar cal = new GregorianCalendar(2004, CALENDAR_MARCH, 28);
Lines 117-123 Link Here
117	* Checks the conversion of an Excel date to a java.util.date on a day when	121	* Checks the conversion of an Excel date to a java.util.date on a day when
118	* Daylight Saving Time starts.	122	* Daylight Saving Time starts.
119	*/	123	*/
120	public void testJavaConversionOnDSTStart() {	124	@Test
		125	public void javaConversionOnDSTStart() {
121	TimeZone cet = TimeZone.getTimeZone("Europe/Copenhagen");	126	TimeZone cet = TimeZone.getTimeZone("Europe/Copenhagen");
122	TimeZone.setDefault(cet);	127	TimeZone.setDefault(cet);
123	Calendar cal = new GregorianCalendar(2004, CALENDAR_MARCH, 28);	128	Calendar cal = new GregorianCalendar(2004, CALENDAR_MARCH, 28);
Lines 144-150 Link Here
144	* Checks the conversion of a java.util.Date to Excel on a day when	149	* Checks the conversion of a java.util.Date to Excel on a day when
145	* Daylight Saving Time ends.	150	* Daylight Saving Time ends.
146	*/	151	*/
147	public void testExcelConversionOnDSTEnd() {	152	@Test
		153	public void excelConversionOnDSTEnd() {
148	TimeZone cet = TimeZone.getTimeZone("Europe/Copenhagen");	154	TimeZone cet = TimeZone.getTimeZone("Europe/Copenhagen");
149	TimeZone.setDefault(cet);	155	TimeZone.setDefault(cet);
150	Calendar cal = new GregorianCalendar(2004, CALENDAR_OCTOBER, 31);	156	Calendar cal = new GregorianCalendar(2004, CALENDAR_OCTOBER, 31);
Lines 167-173 Link Here
167	* Checks the conversion of an Excel date to java.util.Date on a day when	173	* Checks the conversion of an Excel date to java.util.Date on a day when
168	* Daylight Saving Time ends.	174	* Daylight Saving Time ends.
169	*/	175	*/
170	public void testJavaConversionOnDSTEnd() {	176	@Test
		177	public void javaConversionOnDSTEnd() {
171	TimeZone cet = TimeZone.getTimeZone("Europe/Copenhagen");	178	TimeZone cet = TimeZone.getTimeZone("Europe/Copenhagen");
172	TimeZone.setDefault(cet);	179	TimeZone.setDefault(cet);
173	Calendar cal = new GregorianCalendar(2004, CALENDAR_OCTOBER, 31);	180	Calendar cal = new GregorianCalendar(2004, CALENDAR_OCTOBER, 31);
Lines 186-192 Link Here
186	/**	193	/**
187	* Tests that we deal with time-zones properly	194	* Tests that we deal with time-zones properly
188	*/	195	*/
189	public void testCalendarConversion() {	196	@Test
		197	public void calendarConversion() {
190	GregorianCalendar date = new GregorianCalendar(2002, 0, 1, 12, 1, 1);	198	GregorianCalendar date = new GregorianCalendar(2002, 0, 1, 12, 1, 1);
191	Date expected = date.getTime();	199	Date expected = date.getTime();
192		200
Lines 226-232 Link Here
226	/**	234	/**
227	* Tests that we correctly detect date formats as such	235	* Tests that we correctly detect date formats as such
228	*/	236	*/
229	public void testIdentifyDateFormats() {	237	@Test
		238	public void identifyDateFormats() {
230	// First up, try with a few built in date formats	239	// First up, try with a few built in date formats
231	short[] builtins = new short[] { 0x0e, 0x0f, 0x10, 0x16, 0x2d, 0x2e };	240	short[] builtins = new short[] { 0x0e, 0x0f, 0x10, 0x16, 0x2d, 0x2e };
232	for(int i=0; i<builtins.length; i++) {	241	for(int i=0; i<builtins.length; i++) {
Lines 329-335 Link Here
329	* Test that against a real, test file, we still do everything	338	* Test that against a real, test file, we still do everything
330	* correctly	339	* correctly
331	*/	340	*/
332	public void testOnARealFile() {	341	@Test
		342	public void onARealFile() {
333		343
334	HSSFWorkbook workbook = HSSFTestDataSamples.openSampleWorkbook("DateFormats.xls");	344	HSSFWorkbook workbook = HSSFTestDataSamples.openSampleWorkbook("DateFormats.xls");
335	HSSFSheet sheet = workbook.getSheetAt(0);	345	HSSFSheet sheet = workbook.getSheetAt(0);
Lines 386-392 Link Here
386	assertTrue(HSSFDateUtil.isCellDateFormatted(cell));	396	assertTrue(HSSFDateUtil.isCellDateFormatted(cell));
387	}	397	}
388		398
389	public void testDateBug_2Excel() {	399	@Test
		400	public void dateBug_2Excel() {
390	assertEquals(59.0, HSSFDateUtil.getExcelDate(createDate(1900, CALENDAR_FEBRUARY, 28), false), 0.00001);	401	assertEquals(59.0, HSSFDateUtil.getExcelDate(createDate(1900, CALENDAR_FEBRUARY, 28), false), 0.00001);
391	assertEquals(61.0, HSSFDateUtil.getExcelDate(createDate(1900, CALENDAR_MARCH, 1), false), 0.00001);	402	assertEquals(61.0, HSSFDateUtil.getExcelDate(createDate(1900, CALENDAR_MARCH, 1), false), 0.00001);
392		403
Lines 396-402 Link Here
396	assertEquals(38074.00, HSSFDateUtil.getExcelDate(createDate(2004, CALENDAR_MARCH, 28), false), 0.00001);	407	assertEquals(38074.00, HSSFDateUtil.getExcelDate(createDate(2004, CALENDAR_MARCH, 28), false), 0.00001);
397	}	408	}
398		409
399	public void testDateBug_2Java() {	410	@Test
		411	public void dateBug_2Java() {
400	assertEquals(createDate(1900, CALENDAR_FEBRUARY, 28), HSSFDateUtil.getJavaDate(59.0, false));	412	assertEquals(createDate(1900, CALENDAR_FEBRUARY, 28), HSSFDateUtil.getJavaDate(59.0, false));
401	assertEquals(createDate(1900, CALENDAR_MARCH, 1), HSSFDateUtil.getJavaDate(61.0, false));	413	assertEquals(createDate(1900, CALENDAR_MARCH, 1), HSSFDateUtil.getJavaDate(61.0, false));
402		414
Lines 406-412 Link Here
406	assertEquals(createDate(2004, CALENDAR_MARCH, 28), HSSFDateUtil.getJavaDate(38074.00, false));	418	assertEquals(createDate(2004, CALENDAR_MARCH, 28), HSSFDateUtil.getJavaDate(38074.00, false));
407	}	419	}
408		420
409	public void testDate1904() {	421	@Test
		422	public void date1904() {
410	assertEquals(createDate(1904, CALENDAR_JANUARY, 2), HSSFDateUtil.getJavaDate(1.0, true));	423	assertEquals(createDate(1904, CALENDAR_JANUARY, 2), HSSFDateUtil.getJavaDate(1.0, true));
411	assertEquals(createDate(1904, CALENDAR_JANUARY, 1), HSSFDateUtil.getJavaDate(0.0, true));	424	assertEquals(createDate(1904, CALENDAR_JANUARY, 1), HSSFDateUtil.getJavaDate(0.0, true));
412	assertEquals(0.0, HSSFDateUtil.getExcelDate(createDate(1904, CALENDAR_JANUARY, 1), true), 0.00001);	425	assertEquals(0.0, HSSFDateUtil.getExcelDate(createDate(1904, CALENDAR_JANUARY, 1), true), 0.00001);
Lines 441-447 Link Here
441	/**	454	/**
442	* Check if HSSFDateUtil.getAbsoluteDay works as advertised.	455	* Check if HSSFDateUtil.getAbsoluteDay works as advertised.
443	*/	456	*/
444	public void testAbsoluteDay() {	457	@Test
		458	public void absoluteDay() {
445	// 1 Jan 1900 is 1 day after 31 Dec 1899	459	// 1 Jan 1900 is 1 day after 31 Dec 1899
446	GregorianCalendar calendar = new GregorianCalendar(1900, 0, 1);	460	GregorianCalendar calendar = new GregorianCalendar(1900, 0, 1);
447	assertEquals("Checking absolute day (1 Jan 1900)", 1, HSSFDateUtil.absoluteDay(calendar, false));	461	assertEquals("Checking absolute day (1 Jan 1900)", 1, HSSFDateUtil.absoluteDay(calendar, false));
Lines 450-456 Link Here
450	assertEquals("Checking absolute day (1 Jan 1901)", 366, HSSFDateUtil.absoluteDay(calendar, false));	464	assertEquals("Checking absolute day (1 Jan 1901)", 366, HSSFDateUtil.absoluteDay(calendar, false));
451	}	465	}
452		466
453	public void testConvertTime() {	467	@Test
		468	public void convertTime() {
454		469
455	final double delta = 1E-7; // a couple of digits more accuracy than strictly required	470	final double delta = 1E-7; // a couple of digits more accuracy than strictly required
456	assertEquals(0.5, HSSFDateUtil.convertTime("12:00"), delta);	471	assertEquals(0.5, HSSFDateUtil.convertTime("12:00"), delta);
Lines 459-465 Link Here
459	assertEquals(0.7330440, HSSFDateUtil.convertTime("17:35:35"), delta);	474	assertEquals(0.7330440, HSSFDateUtil.convertTime("17:35:35"), delta);
460	}	475	}
461		476
462	public void testParseDate() {	477	@Test
		478	public void parseDate() {
463	assertEquals(createDate(2008, Calendar.AUGUST, 3), HSSFDateUtil.parseYYYYMMDDDate("2008/08/03"));	479	assertEquals(createDate(2008, Calendar.AUGUST, 3), HSSFDateUtil.parseYYYYMMDDDate("2008/08/03"));
464	assertEquals(createDate(1994, Calendar.MAY, 1), HSSFDateUtil.parseYYYYMMDDDate("1994/05/01"));	480	assertEquals(createDate(1994, Calendar.MAY, 1), HSSFDateUtil.parseYYYYMMDDDate("1994/05/01"));
465	}	481	}
Lines 467-473 Link Here
467	/**	483	/**
468	* Ensure that date values with a fractional portion get the right time of day	484	* Ensure that date values with a fractional portion get the right time of day
469	*/	485	*/
470	public void testConvertDateTime() {	486	@Test
		487	public void convertDateTime() {
471	// Excel day 30000 is date 18-Feb-1982	488	// Excel day 30000 is date 18-Feb-1982
472	// 0.7 corresponds to time 16:48:00	489	// 0.7 corresponds to time 16:48:00
473	Date actual = HSSFDateUtil.getJavaDate(30000.7);	490	Date actual = HSSFDateUtil.getJavaDate(30000.7);
Lines 479-485 Link Here
479	* User reported a datetime issue in POI-2.5:	496	* User reported a datetime issue in POI-2.5:
480	* Setting Cell's value to Jan 1, 1900 without a time doesn't return the same value set to	497	* Setting Cell's value to Jan 1, 1900 without a time doesn't return the same value set to
481	*/	498	*/
482	public void testBug19172()	499	@Test
		500	public void bug19172()
483	{	501	{
484	HSSFWorkbook workbook = new HSSFWorkbook();	502	HSSFWorkbook workbook = new HSSFWorkbook();
485	HSSFSheet sheet = workbook.createSheet();	503	HSSFSheet sheet = workbook.createSheet();
Lines 503-512 Link Here
503	* DateUtil.isCellFormatted(Cell) should not true for a numeric cell	521	* DateUtil.isCellFormatted(Cell) should not true for a numeric cell
504	* that's formatted as ".0000"	522	* that's formatted as ".0000"
505	*/	523	*/
506	public void testBug54557() throws Exception {	524	@Test
		525	public void bug54557() throws Exception {
507	final String format = ".0000";	526	final String format = ".0000";
508	boolean isDateFormat = HSSFDateUtil.isADateFormat(165, format);	527	boolean isDateFormat = HSSFDateUtil.isADateFormat(165, format);
509		528
510	assertEquals(false, isDateFormat);	529	assertEquals(false, isDateFormat);
511	}	530	}
		531
		532	@Test
		533	public void bug56269() throws Exception {
		534	double excelFraction = 41642.45833321759d;
		535	Calendar calNoRound = HSSFDateUtil.getJavaCalendar(excelFraction, false);
		536	assertEquals(10, calNoRound.get(Calendar.HOUR));
		537	assertEquals(59, calNoRound.get(Calendar.MINUTE));
		538	assertEquals(59, calNoRound.get(Calendar.SECOND));
		539	Calendar calRound = HSSFDateUtil.getJavaCalendar(excelFraction, false, null, true);
		540	assertEquals(11, calRound.get(Calendar.HOUR));
		541	assertEquals(0, calRound.get(Calendar.MINUTE));
		542	assertEquals(0, calRound.get(Calendar.SECOND));
		543	}
512	}	544	}