Attachment #10747 for bug #27574

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




 * @author  Michael Harhen
 * @author  Glen Stampoultzis (glens at apache.org)
 * @author  Dan Sherman (dsherman at isisph.com)
 * @author  Hack Kampbjorn (hak at 2mba.dk)
 */

public class HSSFDateUtil

        }
        else
        {
	    // Because of daylight time saving we cannot use
	    //     date.getTime() - calStart.getTimeInMillis()
	    // as the difference in milliseconds between 00:00 and 04:00
	    // can be 3, 4 or 5 hours but Excel expects it to always
	    // be 4 hours.
	    // E.g. 2004-03-28 04:00 CEST - 2004-03-28 00:00 CET is 3 hours
	    // and 2004-10-31 04:00 CET - 2004-10-31 00:00 CEST is 5 hours
            double fraction = (((calStart.get(Calendar.HOUR_OF_DAY) * 60
                                 + calStart.get(Calendar.MINUTE)
                                ) * 60 + calStart.get(Calendar.SECOND)
                               ) * 1000 + calStart.get(Calendar.MILLISECOND)
                              ) / ( double ) DAY_MILLISECONDS;
            calStart = dayStart(calStart);
            double fraction = (date.getTime() - calStart.getTime().getTime())
                              / ( double ) DAY_MILLISECONDS;

            return fraction + ( double ) absoluteDay(calStart)
                   - CAL_1900_ABSOLUTE;

     *  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
     *  the same value, that is the comparison
     *  <CODE>excelDate == getExcelDate(getJavaDate(excelDate,false))</CODE>
     *  is not always true. For example if default timezone is
     *  <code>Europe/Copenhagen</code>, on 2004-03-28 the minute after
     *  01:59 CET is 03:00 CEST, if the excel date represents a time between
     *  02:00 and 03:00 then it is converted to past 03:00 summer time
     *
     *  @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, or null if date is not a valid Excel date
     *  @see java.util.TimeZone
     */
    public static Date getJavaDate(double date, boolean use1904windowing) {
        if (isValidExcelDate(date)) {




import junit.framework.TestCase;

import java.util.Date;
import java.util.Calendar;
import java.util.GregorianCalendar;
import java.util.TimeZone;

/**
 * Class TestHSSFDateUtil

 *
 * @author
 * @author  Dan Sherman (dsherman at isisph.com)
 * @author  Hack Kampbjorn (hak at 2mba.dk)
 * @version %I%, %G%
 */


                        dateIf1904.getTime(),
                           HSSFDateUtil.getJavaDate(excelDate,true).getTime());
    }

    /**
     * Checks the conversion of a java.util.date to Excel on a day when
     * Daylight Saving Time starts.
     */
    public void testExcelConversionOnDSTStart() {
        TimeZone cet = TimeZone.getTimeZone("Europe/Copenhagen");
	TimeZone.setDefault(cet);
	Calendar cal = new GregorianCalendar(2004, Calendar.MARCH, 28);
	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 = HSSFDateUtil.getExcelDate(javaDate);
	    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(),
                                HSSFDateUtil.getJavaDate(excelDate).getTime());
	}
    }

    /**
     * Checks the conversion of an Excel date to a java.util.date on a day when
     * Daylight Saving Time starts.
     */
    public void testJavaConversionOnDSTStart() {
        TimeZone cet = TimeZone.getTimeZone("Europe/Copenhagen");
	TimeZone.setDefault(cet);
	Calendar cal = new GregorianCalendar(2004, Calendar.MARCH, 28);
        double excelDate = HSSFDateUtil.getExcelDate(cal.getTime());
        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 = HSSFDateUtil.getJavaDate(excelDate);
            assertEquals("Checking " + hour + " hours on Daylight Saving Time start date",
                             excelDate,
                                HSSFDateUtil.getExcelDate(javaDate), oneMinute);
	}
    }

    /**
     * Checks the conversion of a java.util.Date to Excel on a day when
     * Daylight Saving Time ends.
     */
    public void testExcelConversionOnDSTEnd() {
        TimeZone cet = TimeZone.getTimeZone("Europe/Copenhagen");
	TimeZone.setDefault(cet);
	Calendar cal = new GregorianCalendar(2004, Calendar.OCTOBER, 31);
	for (int hour = 0; hour < 24; hour++) {
	    cal.set(Calendar.HOUR_OF_DAY, hour);
	    Date javaDate = cal.getTime();
	    double excelDate = HSSFDateUtil.getExcelDate(javaDate);
	    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(),
                                HSSFDateUtil.getJavaDate(excelDate).getTime());
	}
    }

    /**
     * Checks the conversion of an Excel date to java.util.Date on a day when
     * Daylight Saving Time ends.
     */
    public void testJavaConversionOnDSTEnd() {
        TimeZone cet = TimeZone.getTimeZone("Europe/Copenhagen");
	TimeZone.setDefault(cet);
	Calendar cal = new GregorianCalendar(2004, Calendar.OCTOBER, 31);
        double excelDate = HSSFDateUtil.getExcelDate(cal.getTime());
        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 = HSSFDateUtil.getJavaDate(excelDate);
            assertEquals("Checking " + hour + " hours on Daylight Saving Time start date",
                             excelDate,
                                HSSFDateUtil.getExcelDate(javaDate), oneMinute);
	}
    }

    public static void main(String [] args) {
        System.out
        .println("Testing org.apache.poi.hssf.usermodel.TestHSSFDateUtil");
        junit.textui.TestRunner.run(TestHSSFDateUtil.class);
    }

}

Return to bug 27574

Lines 70-75 Link Here

(-)src/java/org/apache/poi/hssf/usermodel/HSSFDateUtil.java (-2 / +23 lines)
70	* @author Michael Harhen	70	* @author Michael Harhen
71	* @author Glen Stampoultzis (glens at apache.org)	71	* @author Glen Stampoultzis (glens at apache.org)
72	* @author Dan Sherman (dsherman at isisph.com)	72	* @author Dan Sherman (dsherman at isisph.com)
		73	* @author Hack Kampbjorn (hak at 2mba.dk)
73	*/	74	*/
74		75
75	public class HSSFDateUtil	76	public class HSSFDateUtil
Lines 105-113 Link Here
105	}	106	}
106	else	107	else
107	{	108	{
		109	// Because of daylight time saving we cannot use
		110	// date.getTime() - calStart.getTimeInMillis()
		111	// as the difference in milliseconds between 00:00 and 04:00
		112	// can be 3, 4 or 5 hours but Excel expects it to always
		113	// be 4 hours.
		114	// E.g. 2004-03-28 04:00 CEST - 2004-03-28 00:00 CET is 3 hours
		115	// and 2004-10-31 04:00 CET - 2004-10-31 00:00 CEST is 5 hours
		116	double fraction = (((calStart.get(Calendar.HOUR_OF_DAY) * 60
		117	+ calStart.get(Calendar.MINUTE)
		118	) * 60 + calStart.get(Calendar.SECOND)
		119	) * 1000 + calStart.get(Calendar.MILLISECOND)
		120	) / ( double ) DAY_MILLISECONDS;
108	calStart = dayStart(calStart);	121	calStart = dayStart(calStart);
109	double fraction = (date.getTime() - calStart.getTime().getTime())
110	/ ( double ) DAY_MILLISECONDS;
111		122
112	return fraction + ( double ) absoluteDay(calStart)	123	return fraction + ( double ) absoluteDay(calStart)
113	- CAL_1900_ABSOLUTE;	124	- CAL_1900_ABSOLUTE;
Lines 133-142 Link Here
133	* Given an Excel date with either 1900 or 1904 date windowing,	144	* Given an Excel date with either 1900 or 1904 date windowing,
134	* converts it to a java.util.Date.	145	* converts it to a java.util.Date.
135	*	146	*
		147	* NOTE: If the default <code>TimeZone</code> in Java uses Daylight
		148	* Saving Time then the conversion back to an Excel date may not give
		149	* the same value, that is the comparison
		150	* <CODE>excelDate == getExcelDate(getJavaDate(excelDate,false))</CODE>
		151	* is not always true. For example if default timezone is
		152	* <code>Europe/Copenhagen</code>, on 2004-03-28 the minute after
		153	* 01:59 CET is 03:00 CEST, if the excel date represents a time between
		154	* 02:00 and 03:00 then it is converted to past 03:00 summer time
		155	*
136	* @param date The Excel date.	156	* @param date The Excel date.
137	* @param use1904windowing true if date uses 1904 windowing,	157	* @param use1904windowing true if date uses 1904 windowing,
138	* or false if using 1900 date windowing.	158	* or false if using 1900 date windowing.
139	* @return Java representation of the date, or null if date is not a valid Excel date	159	* @return Java representation of the date, or null if date is not a valid Excel date
		160	* @see java.util.TimeZone
140	*/	161	*/
141	public static Date getJavaDate(double date, boolean use1904windowing) {	162	public static Date getJavaDate(double date, boolean use1904windowing) {
142	if (isValidExcelDate(date)) {	163	if (isValidExcelDate(date)) {

Lines 58-64 Link Here

(-)src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDateUtil.java (+110 lines)
58	import junit.framework.TestCase;	58	import junit.framework.TestCase;
59		59
60	import java.util.Date;	60	import java.util.Date;
		61	import java.util.Calendar;
61	import java.util.GregorianCalendar;	62	import java.util.GregorianCalendar;
		63	import java.util.TimeZone;
62		64
63	/**	65	/**
64	* Class TestHSSFDateUtil	66	* Class TestHSSFDateUtil
Lines 66-71 Link Here
66	*	68	*
67	* @author	69	* @author
68	* @author Dan Sherman (dsherman at isisph.com)	70	* @author Dan Sherman (dsherman at isisph.com)
		71	* @author Hack Kampbjorn (hak at 2mba.dk)
69	* @version %I%, %G%	72	* @version %I%, %G%
70	*/	73	*/
71		74
Lines 115-118 Link Here
115	dateIf1904.getTime(),	118	dateIf1904.getTime(),
116	HSSFDateUtil.getJavaDate(excelDate,true).getTime());	119	HSSFDateUtil.getJavaDate(excelDate,true).getTime());
117	}	120	}
		121
		122	/**
		123	* Checks the conversion of a java.util.date to Excel on a day when
		124	* Daylight Saving Time starts.
		125	*/
		126	public void testExcelConversionOnDSTStart() {
		127	TimeZone cet = TimeZone.getTimeZone("Europe/Copenhagen");
		128	TimeZone.setDefault(cet);
		129	Calendar cal = new GregorianCalendar(2004, Calendar.MARCH, 28);
		130	for (int hour = 0; hour < 24; hour++) {
		131
		132	// Skip 02:00 CET as that is the Daylight change time
		133	// and Java converts it automatically to 03:00 CEST
		134	if (hour == 2) {
		135	continue;
		136	}
		137
		138	cal.set(Calendar.HOUR_OF_DAY, hour);
		139	Date javaDate = cal.getTime();
		140	double excelDate = HSSFDateUtil.getExcelDate(javaDate);
		141	double difference = excelDate - Math.floor(excelDate);
		142	int differenceInHours = (int) (difference * 24 * 60 + 0.5) / 60;
		143	assertEquals("Checking " + hour + " hour on Daylight Saving Time start date",
		144	hour,
		145	differenceInHours);
		146	assertEquals("Checking " + hour + " hour on Daylight Saving Time start date",
		147	javaDate.getTime(),
		148	HSSFDateUtil.getJavaDate(excelDate).getTime());
		149	}
		150	}
		151
		152	/**
		153	* Checks the conversion of an Excel date to a java.util.date on a day when
		154	* Daylight Saving Time starts.
		155	*/
		156	public void testJavaConversionOnDSTStart() {
		157	TimeZone cet = TimeZone.getTimeZone("Europe/Copenhagen");
		158	TimeZone.setDefault(cet);
		159	Calendar cal = new GregorianCalendar(2004, Calendar.MARCH, 28);
		160	double excelDate = HSSFDateUtil.getExcelDate(cal.getTime());
		161	double oneHour = 1.0 / 24;
		162	double oneMinute = oneHour / 60;
		163	for (int hour = 0; hour < 24; hour++, excelDate += oneHour) {
		164
		165	// Skip 02:00 CET as that is the Daylight change time
		166	// and Java converts it automatically to 03:00 CEST
		167	if (hour == 2) {
		168	continue;
		169	}
		170
		171	cal.set(Calendar.HOUR_OF_DAY, hour);
		172	Date javaDate = HSSFDateUtil.getJavaDate(excelDate);
		173	assertEquals("Checking " + hour + " hours on Daylight Saving Time start date",
		174	excelDate,
		175	HSSFDateUtil.getExcelDate(javaDate), oneMinute);
		176	}
		177	}
		178
		179	/**
		180	* Checks the conversion of a java.util.Date to Excel on a day when
		181	* Daylight Saving Time ends.
		182	*/
		183	public void testExcelConversionOnDSTEnd() {
		184	TimeZone cet = TimeZone.getTimeZone("Europe/Copenhagen");
185	TimeZone.setDefault(cet);
186	Calendar cal = new GregorianCalendar(2004, Calendar.OCTOBER, 31);
187	for (int hour = 0; hour < 24; hour++) {
188	cal.set(Calendar.HOUR_OF_DAY, hour);
189	Date javaDate = cal.getTime();
190	double excelDate = HSSFDateUtil.getExcelDate(javaDate);
191	double difference = excelDate - Math.floor(excelDate);
192	int differenceInHours = (int) (difference * 24 * 60 + 0.5) / 60;
193	assertEquals("Checking " + hour + " hour on Daylight Saving Time end date",
194	hour,
195	differenceInHours);
196	assertEquals("Checking " + hour + " hour on Daylight Saving Time start date",
197	javaDate.getTime(),
198	HSSFDateUtil.getJavaDate(excelDate).getTime());
199	}
200	}
201
202	/**
203	* Checks the conversion of an Excel date to java.util.Date on a day when
204	* Daylight Saving Time ends.
205	*/
206	public void testJavaConversionOnDSTEnd() {
207	TimeZone cet = TimeZone.getTimeZone("Europe/Copenhagen");
208	TimeZone.setDefault(cet);
209	Calendar cal = new GregorianCalendar(2004, Calendar.OCTOBER, 31);
210	double excelDate = HSSFDateUtil.getExcelDate(cal.getTime());
211	double oneHour = 1.0 / 24;
212	double oneMinute = oneHour / 60;
213	for (int hour = 0; hour < 24; hour++, excelDate += oneHour) {
214	cal.set(Calendar.HOUR_OF_DAY, hour);
215	Date javaDate = HSSFDateUtil.getJavaDate(excelDate);
216	assertEquals("Checking " + hour + " hours on Daylight Saving Time start date",
217	excelDate,
218	HSSFDateUtil.getExcelDate(javaDate), oneMinute);
219	}
220	}
221
222	public static void main(String [] args) {
223	System.out
224	.println("Testing org.apache.poi.hssf.usermodel.TestHSSFDateUtil");
225	junit.textui.TestRunner.run(TestHSSFDateUtil.class);
226	}
227
118	}	228	}