Attachment #20939 for bug #43551

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




 * @author  Glen Stampoultzis (glens at apache.org)
 * @author  Dan Sherman (dsherman at isisph.com)
 * @author  Hack Kampbjorn (hak at 2mba.dk)
 * @author  Alex Jacoby (ajacoby at gmail.com)
 * @author  Pavel Krupets (pkrupets at palmtreebusiness dot com)
 */

public class HSSFDateUtil

     * @return Excel representation of Date (-1 if error - test for error by checking for less than 0.1)
     * @param  date the Date
     */
    
    public static double getExcelDate(Date date, boolean use1904windowing) {
    {
        Calendar calStart = new GregorianCalendar();
        calStart.setTime(date);   // If date includes hours, minutes, and seconds, set them to 0
        
        if ((!use1904windowing && calStart.get(Calendar.YEAR) < 1900) || 
            (use1904windowing && calStart.get(Calendar.YEAR) < 1904)) 
        {
            return BAD_DATE;
        } else {
        else
        {
	    // Because of daylight time saving we cannot use
	    //     date.getTime() - calStart.getTimeInMillis()
	    // as the difference in milliseconds between 00:00 and 04:00

                               ) * 1000 + calStart.get(Calendar.MILLISECOND)
                              ) / ( double ) DAY_MILLISECONDS;
            calStart = dayStart(calStart);

            double value = fraction + absoluteDay(calStart);
            
            double value = fraction + absoluteDay(calStart, use1904windowing);
            
            if (!use1904windowing && value >= 60) {
                value++;
            } else if (use1904windowing) {
                value--;
            }
            
            return value;
        }
    }

    /**
     * Given a excel date, converts it into a Date.
     * Assumes 1900 date windowing.
     *
     * @param  date the Excel Date
     *
     * @return Java representation of a date (null if error)
     * @see #getJavaDate(double,boolean)
     */

    public static Date getJavaDate(double date)
    {
        return getJavaDate(date,false);
    }
    
    /**
     *  Given an Excel date with either 1900 or 1904 date windowing,

            GregorianCalendar calendar = new GregorianCalendar(startYear,0,
                                                     wholeDays + dayAdjust);
            int millisecondsInDay = (int)((date - Math.floor(date)) * 
                                          DAY_MILLISECONDS + 0.5);
            calendar.set(GregorianCalendar.MILLISECOND, millisecondsInDay);
            return calendar.getTime();
        }

     *  Check if a cell contains a date
     *  Since dates are stored internally in Excel as double values 
     *  we infer it is a date if it is formatted as such. 
     *  @see #isADateFormat(int, String)
     *  @see #isInternalDateFormat(int)
     */
    public static boolean isCellDateFormatted(HSSFCell cell) {

     *   excel date formats.
     *  As Excel stores a great many of its dates in "non-internal"
     *   date formats, you will not normally want to use this method.
     *  @see #isADateFormat(int,String)
     *  @see #isInternalDateFormat(int)
     */
    public static boolean isCellInternalDateFormatted(HSSFCell cell) {

     * @exception IllegalArgumentException if date is invalid
     */

    static int absoluteDay(Calendar cal, boolean use1904windowing)
    {
        return cal.get(Calendar.DAY_OF_YEAR)
               + daysInPriorYears(cal.get(Calendar.YEAR), use1904windowing);
    }

    /**

     *
     * @return    days  number of days in years prior to yr.
     * @param     yr    a year (1900 < yr < 4000)
     * @param use1904windowing 
     * @exception IllegalArgumentException if year is outside of range.
     */

    private static int daysInPriorYears(int yr, boolean use1904windowing)
    {
        if ((!use1904windowing && yr < 1900) || (use1904windowing && yr < 1900)) {
            throw new IllegalArgumentException("'year' must be 1900 or greater");
                "'year' must be 1900 or greater");
        }
        
        int yr1  = yr - 1;

                       + yr1 / 400 // plus years divisible by 400 
                       - 460;      // leap days in previous 1900 years
        
        return 365 * (yr - (use1904windowing ? 1904 : 1900)) + leapDays;
    }
    
    // set HH:MM:SS fields of cal to 00:00:00:000

Lines 521-527 Link Here

(-)src/java/org/apache/poi/hssf/usermodel/HSSFCell.java (-1 / +1 lines)
521	*/	521	*/
522	public void setCellValue(Date value)	522	public void setCellValue(Date value)
523	{	523	{
524	setCellValue(HSSFDateUtil.getExcelDate(value));	524	setCellValue(HSSFDateUtil.getExcelDate(value, this.book.isUsing1904DateWindowing()));
525	}	525	}
526		526
527	/**	527	/**

Lines 70-76 Link Here

(-)src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Date.java (-1 / +1 lines)
70	c.set(year, month, day, 0, 0, 0);	70	c.set(year, month, day, 0, 0, 0);
71	c.set(Calendar.MILLISECOND, 0);	71	c.set(Calendar.MILLISECOND, 0);
72		72
73	return new NumberEval(HSSFDateUtil.getExcelDate(c.getTime()));	73	return new NumberEval(HSSFDateUtil.getExcelDate(c.getTime(), false)); // XXX fix 1900/1904 problem
74	}	74	}
75	}	75	}
76		76

Lines 51-57 Link Here

(-)src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Year.java (-1 / +1 lines)
51	if (ve instanceof NumericValueEval) {	51	if (ve instanceof NumericValueEval) {
52	NumericValueEval ne = (NumericValueEval) ve;	52	NumericValueEval ne = (NumericValueEval) ve;
53	if (HSSFDateUtil.isValidExcelDate(ne.getNumberValue())) {	53	if (HSSFDateUtil.isValidExcelDate(ne.getNumberValue())) {
54	java.util.Date d = HSSFDateUtil.getJavaDate(ne.getNumberValue());	54	java.util.Date d = HSSFDateUtil.getJavaDate(ne.getNumberValue(), false); // XXX fix 1900/1904 problem
55	retval = new NumberEval(d.getYear()+1900);	55	retval = new NumberEval(d.getYear()+1900);
56	} else {	56	} else {
57	retval = ErrorEval.NUM_ERROR;	57	retval = ErrorEval.NUM_ERROR;

Lines 46-52 Link Here

(-)src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Day.java (-1 / +1 lines)
46	if (ve instanceof NumericValueEval) {	46	if (ve instanceof NumericValueEval) {
47	NumericValueEval ne = (NumericValueEval) ve;	47	NumericValueEval ne = (NumericValueEval) ve;
48	if (HSSFDateUtil.isValidExcelDate(ne.getNumberValue())) {	48	if (HSSFDateUtil.isValidExcelDate(ne.getNumberValue())) {
49	java.util.Date d = HSSFDateUtil.getJavaDate(ne.getNumberValue());	49	java.util.Date d = HSSFDateUtil.getJavaDate(ne.getNumberValue(), false); // XXX fix 1900/1904 problem
50	java.util.Calendar c = java.util.Calendar.getInstance();	50	java.util.Calendar c = java.util.Calendar.getInstance();
51	c.setTime(d);	51	c.setTime(d);
52	retval = new NumberEval(c.get(java.util.Calendar.DAY_OF_MONTH));	52	retval = new NumberEval(c.get(java.util.Calendar.DAY_OF_MONTH));

Lines 50-56 Link Here

(-)src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Month.java (-1 / +1 lines)
50	if (ve instanceof NumericValueEval) {	50	if (ve instanceof NumericValueEval) {
51	NumericValueEval ne = (NumericValueEval) ve;	51	NumericValueEval ne = (NumericValueEval) ve;
52	if (HSSFDateUtil.isValidExcelDate(ne.getNumberValue())) {	52	if (HSSFDateUtil.isValidExcelDate(ne.getNumberValue())) {
53	java.util.Date d = HSSFDateUtil.getJavaDate(ne.getNumberValue());	53	java.util.Date d = HSSFDateUtil.getJavaDate(ne.getNumberValue(), false); // XXX fix 1900/1904 problem
54	retval = new NumberEval(d.getMonth()+1);	54	retval = new NumberEval(d.getMonth()+1);
55	} else {	55	} else {
56	retval = ErrorEval.NUM_ERROR;	56	retval = ErrorEval.NUM_ERROR;

Lines 283-289 Link Here

(-)src/scratchpad/examples/src/org/apache/poi/hssf/eventusermodel/examples/XLS2CSVmra.java (-1 / +1 lines)
283	format = format.replaceAll("\\\\-","-");	283	format = format.replaceAll("\\\\-","-");
284		284
285	// Format as a date	285	// Format as a date
286	Date d = HSSFDateUtil.getJavaDate(value);	286	Date d = HSSFDateUtil.getJavaDate(value, false);
287	DateFormat df = new SimpleDateFormat(format);	287	DateFormat df = new SimpleDateFormat(format);
288	return df.format(d);	288	return df.format(d);
289	} else {	289	} else {

Lines 36-41 Link Here

(-)src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDateUtil.java (-31 / +59 lines)
36	*	36	*
37	* @author Dan Sherman (dsherman at isisph.com)	37	* @author Dan Sherman (dsherman at isisph.com)
38	* @author Hack Kampbjorn (hak at 2mba.dk)	38	* @author Hack Kampbjorn (hak at 2mba.dk)
		39	* @author Pavel Krupets (pkrupets at palmtreebusiness dot com)
		40	* @author Alex Jacoby (ajacoby at gmail.com)
39	* @version %I%, %G%	41	* @version %I%, %G%
40	*/	42	*/
41		43
Lines 47-53 Link Here
47	public static final int CALENDAR_FEBRUARY = 1;	49	public static final int CALENDAR_FEBRUARY = 1;
48	public static final int CALENDAR_MARCH = 2;	50	public static final int CALENDAR_MARCH = 2;
49	public static final int CALENDAR_APRIL = 3;	51	public static final int CALENDAR_APRIL = 3;
50		52	public static final int CALENDAR_JULY = 6;
		53	public static final int CALENDAR_OCTOBER = 9;
		54
51	public TestHSSFDateUtil(String s)	55	public TestHSSFDateUtil(String s)
52	{	56	{
53	super(s);	57	super(s);
Lines 67-76 Link Here
67	GregorianCalendar date = new GregorianCalendar(2002, 0, 1,	71	GregorianCalendar date = new GregorianCalendar(2002, 0, 1,
68	hour, 1, 1);	72	hour, 1, 1);
69	double excelDate =	73	double excelDate =
70	HSSFDateUtil.getExcelDate(date.getTime());	74	HSSFDateUtil.getExcelDate(date.getTime(), false);
71		75
72	assertEquals("Checking hour = " + hour, date.getTime().getTime(),	76	assertEquals("Checking hour = " + hour, date.getTime().getTime(),
73	HSSFDateUtil.getJavaDate(excelDate).getTime());	77	HSSFDateUtil.getJavaDate(excelDate, false).getTime());
74	}	78	}
75		79
76	// check 1900 and 1904 date windowing conversions	80	// check 1900 and 1904 date windowing conversions
Lines 99-105 Link Here
99	public void testExcelConversionOnDSTStart() {	103	public void testExcelConversionOnDSTStart() {
100	TimeZone cet = TimeZone.getTimeZone("Europe/Copenhagen");	104	TimeZone cet = TimeZone.getTimeZone("Europe/Copenhagen");
101	TimeZone.setDefault(cet);	105	TimeZone.setDefault(cet);
102	Calendar cal = new GregorianCalendar(2004, Calendar.MARCH, 28);	106	Calendar cal = new GregorianCalendar(2004, CALENDAR_MARCH, 28);
103	for (int hour = 0; hour < 24; hour++) {	107	for (int hour = 0; hour < 24; hour++) {
104		108
105	// Skip 02:00 CET as that is the Daylight change time	109	// Skip 02:00 CET as that is the Daylight change time
Lines 110-116 Link Here
110		114
111	cal.set(Calendar.HOUR_OF_DAY, hour);	115	cal.set(Calendar.HOUR_OF_DAY, hour);
112	Date javaDate = cal.getTime();	116	Date javaDate = cal.getTime();
113	double excelDate = HSSFDateUtil.getExcelDate(javaDate);	117	double excelDate = HSSFDateUtil.getExcelDate(javaDate, false);
114	double difference = excelDate - Math.floor(excelDate);	118	double difference = excelDate - Math.floor(excelDate);
115	int differenceInHours = (int) (difference * 24 * 60 + 0.5) / 60;	119	int differenceInHours = (int) (difference * 24 * 60 + 0.5) / 60;
116	assertEquals("Checking " + hour + " hour on Daylight Saving Time start date",	120	assertEquals("Checking " + hour + " hour on Daylight Saving Time start date",
Lines 118-124 Link Here
118	differenceInHours);	122	differenceInHours);
119	assertEquals("Checking " + hour + " hour on Daylight Saving Time start date",	123	assertEquals("Checking " + hour + " hour on Daylight Saving Time start date",
120	javaDate.getTime(),	124	javaDate.getTime(),
121	HSSFDateUtil.getJavaDate(excelDate).getTime());	125	HSSFDateUtil.getJavaDate(excelDate, false).getTime());
122	}	126	}
123	}	127	}
124		128
Lines 129-136 Link Here
129	public void testJavaConversionOnDSTStart() {	133	public void testJavaConversionOnDSTStart() {
130	TimeZone cet = TimeZone.getTimeZone("Europe/Copenhagen");	134	TimeZone cet = TimeZone.getTimeZone("Europe/Copenhagen");
131	TimeZone.setDefault(cet);	135	TimeZone.setDefault(cet);
132	Calendar cal = new GregorianCalendar(2004, Calendar.MARCH, 28);	136	Calendar cal = new GregorianCalendar(2004, CALENDAR_MARCH, 28);
133	double excelDate = HSSFDateUtil.getExcelDate(cal.getTime());	137	double excelDate = HSSFDateUtil.getExcelDate(cal.getTime(), false);
134	double oneHour = 1.0 / 24;	138	double oneHour = 1.0 / 24;
135	double oneMinute = oneHour / 60;	139	double oneMinute = oneHour / 60;
136	for (int hour = 0; hour < 24; hour++, excelDate += oneHour) {	140	for (int hour = 0; hour < 24; hour++, excelDate += oneHour) {
Lines 142-151 Link Here
142	}	146	}
143		147
144	cal.set(Calendar.HOUR_OF_DAY, hour);	148	cal.set(Calendar.HOUR_OF_DAY, hour);
145	Date javaDate = HSSFDateUtil.getJavaDate(excelDate);	149	Date javaDate = HSSFDateUtil.getJavaDate(excelDate, false);
146	assertEquals("Checking " + hour + " hours on Daylight Saving Time start date",	150	assertEquals("Checking " + hour + " hours on Daylight Saving Time start date",
147	excelDate,	151	excelDate,
148	HSSFDateUtil.getExcelDate(javaDate), oneMinute);	152	HSSFDateUtil.getExcelDate(javaDate, false), oneMinute);
149	}	153	}
150	}	154	}
151		155
Lines 156-166 Link Here
156	public void testExcelConversionOnDSTEnd() {	160	public void testExcelConversionOnDSTEnd() {
157	TimeZone cet = TimeZone.getTimeZone("Europe/Copenhagen");	161	TimeZone cet = TimeZone.getTimeZone("Europe/Copenhagen");
158	TimeZone.setDefault(cet);	162	TimeZone.setDefault(cet);
159	Calendar cal = new GregorianCalendar(2004, Calendar.OCTOBER, 31);	163	Calendar cal = new GregorianCalendar(2004, CALENDAR_OCTOBER, 31);
160	for (int hour = 0; hour < 24; hour++) {	164	for (int hour = 0; hour < 24; hour++) {
161	cal.set(Calendar.HOUR_OF_DAY, hour);	165	cal.set(Calendar.HOUR_OF_DAY, hour);
162	Date javaDate = cal.getTime();	166	Date javaDate = cal.getTime();
163	double excelDate = HSSFDateUtil.getExcelDate(javaDate);	167	double excelDate = HSSFDateUtil.getExcelDate(javaDate, false);
164	double difference = excelDate - Math.floor(excelDate);	168	double difference = excelDate - Math.floor(excelDate);
165	int differenceInHours = (int) (difference * 24 * 60 + 0.5) / 60;	169	int differenceInHours = (int) (difference * 24 * 60 + 0.5) / 60;
166	assertEquals("Checking " + hour + " hour on Daylight Saving Time end date",	170	assertEquals("Checking " + hour + " hour on Daylight Saving Time end date",
Lines 168-174 Link Here
168	differenceInHours);	172	differenceInHours);
169	assertEquals("Checking " + hour + " hour on Daylight Saving Time start date",	173	assertEquals("Checking " + hour + " hour on Daylight Saving Time start date",
170	javaDate.getTime(),	174	javaDate.getTime(),
171	HSSFDateUtil.getJavaDate(excelDate).getTime());	175	HSSFDateUtil.getJavaDate(excelDate, false).getTime());
172	}	176	}
173	}	177	}
174		178
Lines 179-194 Link Here
179	public void testJavaConversionOnDSTEnd() {	183	public void testJavaConversionOnDSTEnd() {
180	TimeZone cet = TimeZone.getTimeZone("Europe/Copenhagen");	184	TimeZone cet = TimeZone.getTimeZone("Europe/Copenhagen");
181	TimeZone.setDefault(cet);	185	TimeZone.setDefault(cet);
182	Calendar cal = new GregorianCalendar(2004, Calendar.OCTOBER, 31);	186	Calendar cal = new GregorianCalendar(2004, CALENDAR_OCTOBER, 31);
183	double excelDate = HSSFDateUtil.getExcelDate(cal.getTime());	187	double excelDate = HSSFDateUtil.getExcelDate(cal.getTime(), false);
184	double oneHour = 1.0 / 24;	188	double oneHour = 1.0 / 24;
185	double oneMinute = oneHour / 60;	189	double oneMinute = oneHour / 60;
186	for (int hour = 0; hour < 24; hour++, excelDate += oneHour) {	190	for (int hour = 0; hour < 24; hour++, excelDate += oneHour) {
187	cal.set(Calendar.HOUR_OF_DAY, hour);	191	cal.set(Calendar.HOUR_OF_DAY, hour);
188	Date javaDate = HSSFDateUtil.getJavaDate(excelDate);	192	Date javaDate = HSSFDateUtil.getJavaDate(excelDate, false);
189	assertEquals("Checking " + hour + " hours on Daylight Saving Time start date",	193	assertEquals("Checking " + hour + " hours on Daylight Saving Time start date",
190	excelDate,	194	excelDate,
191	HSSFDateUtil.getExcelDate(javaDate), oneMinute);	195	HSSFDateUtil.getExcelDate(javaDate, false), oneMinute);
192	}	196	}
193	}	197	}
194		198
Lines 315-339 Link Here
315	}	319	}
316		320
317	public void testDateBug_2Excel() {	321	public void testDateBug_2Excel() {
318	assertEquals(59.0, HSSFDateUtil.getExcelDate(createDate(1900, CALENDAR_FEBRUARY, 28)), 0.00001);	322	assertEquals(59.0, HSSFDateUtil.getExcelDate(createDate(1900, CALENDAR_FEBRUARY, 28), false), 0.00001);
319	assertEquals(61.0, HSSFDateUtil.getExcelDate(createDate(1900, CALENDAR_MARCH, 1)), 0.00001);	323	assertEquals(61.0, HSSFDateUtil.getExcelDate(createDate(1900, CALENDAR_MARCH, 1), false), 0.00001);
320		324
321	assertEquals(37315.00, HSSFDateUtil.getExcelDate(createDate(2002, CALENDAR_FEBRUARY, 28)), 0.00001);	325	assertEquals(37315.00, HSSFDateUtil.getExcelDate(createDate(2002, CALENDAR_FEBRUARY, 28), false), 0.00001);
322	assertEquals(37316.00, HSSFDateUtil.getExcelDate(createDate(2002, CALENDAR_MARCH, 1)), 0.00001);	326	assertEquals(37316.00, HSSFDateUtil.getExcelDate(createDate(2002, CALENDAR_MARCH, 1), false), 0.00001);
323	assertEquals(37257.00, HSSFDateUtil.getExcelDate(createDate(2002, CALENDAR_JANUARY, 1)), 0.00001);	327	assertEquals(37257.00, HSSFDateUtil.getExcelDate(createDate(2002, CALENDAR_JANUARY, 1), false), 0.00001);
324	assertEquals(38074.00, HSSFDateUtil.getExcelDate(createDate(2004, CALENDAR_MARCH, 28)), 0.00001);	328	assertEquals(38074.00, HSSFDateUtil.getExcelDate(createDate(2004, CALENDAR_MARCH, 28), false), 0.00001);
325	}	329	}
326		330
327	public void testDateBug_2Java() {	331	public void testDateBug_2Java() {
328	assertEquals(createDate(1900, Calendar.FEBRUARY, 28), HSSFDateUtil.getJavaDate(59.0));	332	assertEquals(createDate(1900, CALENDAR_FEBRUARY, 28), HSSFDateUtil.getJavaDate(59.0, false));
329	assertEquals(createDate(1900, Calendar.MARCH, 1), HSSFDateUtil.getJavaDate(61.0));	333	assertEquals(createDate(1900, CALENDAR_MARCH, 1), HSSFDateUtil.getJavaDate(61.0, false));
330		334
331	assertEquals(createDate(2002, Calendar.FEBRUARY, 28), HSSFDateUtil.getJavaDate(37315.00));	335	assertEquals(createDate(2002, CALENDAR_FEBRUARY, 28), HSSFDateUtil.getJavaDate(37315.00, false));
332	assertEquals(createDate(2002, Calendar.MARCH, 1), HSSFDateUtil.getJavaDate(37316.00));	336	assertEquals(createDate(2002, CALENDAR_MARCH, 1), HSSFDateUtil.getJavaDate(37316.00, false));
333	assertEquals(createDate(2002, Calendar.JANUARY, 1), HSSFDateUtil.getJavaDate(37257.00));	337	assertEquals(createDate(2002, CALENDAR_JANUARY, 1), HSSFDateUtil.getJavaDate(37257.00, false));
334	assertEquals(createDate(2004, Calendar.MARCH, 28), HSSFDateUtil.getJavaDate(38074.00));	338	assertEquals(createDate(2004, CALENDAR_MARCH, 28), HSSFDateUtil.getJavaDate(38074.00, false));
335	}	339	}
336		340
		341	public void testDate1904() {
		342	assertEquals(createDate(1904, CALENDAR_JANUARY, 2), HSSFDateUtil.getJavaDate(1.0, true));
		343	assertEquals(createDate(1904, CALENDAR_JANUARY, 1), HSSFDateUtil.getJavaDate(0.0, true));
		344	assertEquals(0.0, HSSFDateUtil.getExcelDate(createDate(1904, CALENDAR_JANUARY, 1), true), 0.00001);
		345	assertEquals(1.0, HSSFDateUtil.getExcelDate(createDate(1904, CALENDAR_JANUARY, 2), true), 0.00001);
		346
		347	assertEquals(createDate(1998, CALENDAR_JULY, 5), HSSFDateUtil.getJavaDate(35981, false));
		348	assertEquals(createDate(1998, CALENDAR_JULY, 5), HSSFDateUtil.getJavaDate(34519, true));
		349
		350	assertEquals(35981.0, HSSFDateUtil.getExcelDate(createDate(1998, CALENDAR_JULY, 5), false), 0.00001);
		351	assertEquals(34519.0, HSSFDateUtil.getExcelDate(createDate(1998, CALENDAR_JULY, 5), true), 0.00001);
		352	}
		353
337	private Date createDate(int year, int month, int day) {	354	private Date createDate(int year, int month, int day) {
338	Calendar c = new GregorianCalendar();	355	Calendar c = new GregorianCalendar();
339	c.set(year, month, day, 0, 0, 0);	356	c.set(year, month, day, 0, 0, 0);
Lines 341-350 Link Here
341	return c.getTime();	358	return c.getTime();
342	}	359	}
343		360
		361	/**
		362	* Check if HSSFDateUtil.getAbsoluteDay works as advertised.
		363	*/
		364	public void testAbsoluteDay() {
		365	// 1 Jan 1900 is 1 day after 31 Dec 1899
		366	GregorianCalendar calendar = new GregorianCalendar(1900, 0, 1);
		367	assertEquals("Checking absolute day (1 Jan 1900)", 1, HSSFDateUtil.absoluteDay(calendar, false));
		368	// 1 Jan 1901 is 366 days after 31 Dec 1899
		369	calendar = new GregorianCalendar(1901, 0, 1);
		370	assertEquals("Checking absolute day (1 Jan 1901)", 366, HSSFDateUtil.absoluteDay(calendar, false));
		371	}
		372
344	public static void main(String [] args) {	373	public static void main(String [] args) {
345	System.out	374	System.out
346	.println("Testing org.apache.poi.hssf.usermodel.TestHSSFDateUtil");	375	.println("Testing org.apache.poi.hssf.usermodel.TestHSSFDateUtil");
347	junit.textui.TestRunner.run(TestHSSFDateUtil.class);	376	junit.textui.TestRunner.run(TestHSSFDateUtil.class);
348	}	377	}
349	}	378	}
350




 * paticular datatypes, etc.
 * @author Andrew C. Oliver (andy at superlinksoftware dot com)
 * @author  Dan Sherman (dsherman at isisph.com)
 * @author Alex Jacoby (ajacoby at gmail.com)
 */

public class TestHSSFCell

    }

    /**
     * Checks that the recognition of files using 1904 date windowing
     *  is working properly. Conversion of the date is also an issue,
     *  but there's a separate unit test for that.
     */
     public void testDateWindowingRead() throws Exception {
         GregorianCalendar cal = new GregorianCalendar(2000,0,1); // Jan. 1, 2000
         Date date = cal.getTime();
         String path = System.getProperty("HSSF.testdata.path");

         // first check a file with 1900 Date Windowing
         String filename = path + "/1900DateWindowing.xls";
         FileInputStream stream   = new FileInputStream(filename);
         POIFSFileSystem fs       = new POIFSFileSystem(stream);
         HSSFWorkbook    workbook = new HSSFWorkbook(fs);
         HSSFSheet       sheet    = workbook.getSheetAt(0);

         assertEquals("Date from file using 1900 Date Windowing",
                         date.getTime(),
                            sheet.getRow(0).getCell((short)0)
                               .getDateCellValue().getTime());
         stream.close();
         
         // now check a file with 1904 Date Windowing
         filename = path + "/1904DateWindowing.xls";
         stream   = new FileInputStream(filename);
         fs       = new POIFSFileSystem(stream);
         workbook = new HSSFWorkbook(fs);
         sheet    = workbook.getSheetAt(0);

         assertEquals("Date from file using 1904 Date Windowing",
                         date.getTime(),
                            sheet.getRow(0).getCell((short)0)
                               .getDateCellValue().getTime());
         stream.close();
     }

     /**
      * Checks that dates are properly written to both types of files:
      * those with 1900 and 1904 date windowing.  Note that if the
      * previous test ({@link #testDateWindowingRead}) fails, the
      * results of this test are meaningless.
      */
      public void testDateWindowingWrite() throws Exception {
          GregorianCalendar cal = new GregorianCalendar(2000,0,1); // Jan. 1, 2000
          Date date = cal.getTime();
          String path = System.getProperty("HSSF.testdata.path");

          // first check a file with 1900 Date Windowing
          String filename = path + "/1900DateWindowing.xls";
          writeCell(filename, 0, (short) 1, date);          
          assertEquals("Date from file using 1900 Date Windowing",
                          date.getTime(),
                          readCell(filename, 0, (short) 1).getTime());
          
          // now check a file with 1904 Date Windowing
          filename = path + "/1904DateWindowing.xls";
          writeCell(filename, 0, (short) 1, date);          
          assertEquals("Date from file using 1900 Date Windowing",
                          date.getTime(),
                          readCell(filename, 0, (short) 1).getTime());
      }

      /**
       * Sets cell value and writes file.
       */
      private void writeCell(String filename,
     		 int rowIdx, short colIdx, Date date) throws Exception {
          FileInputStream stream   = new FileInputStream(filename);
          POIFSFileSystem fs       = new POIFSFileSystem(stream);
          HSSFWorkbook    workbook = new HSSFWorkbook(fs);
          HSSFSheet       sheet    = workbook.getSheetAt(0);
          HSSFRow         row      = sheet.getRow(rowIdx);
          HSSFCell        cell     = row.getCell(colIdx);
          
          if (cell == null) {
        	  cell = row.createCell(colIdx);
          }
          cell.setCellValue(date);
          
          // Write the file
          stream.close();
          FileOutputStream oStream = new FileOutputStream(filename);
          workbook.write(oStream);
          oStream.close();
      }
      
      /**
       * Reads cell value from file.
       */
      private Date readCell(String filename,
     		 int rowIdx, short colIdx) throws Exception {
          FileInputStream stream   = new FileInputStream(filename);
          POIFSFileSystem fs       = new POIFSFileSystem(stream);
          HSSFWorkbook    workbook = new HSSFWorkbook(fs);
          HSSFSheet       sheet    = workbook.getSheetAt(0);
          HSSFRow         row      = sheet.getRow(rowIdx);
          HSSFCell        cell     = row.getCell(colIdx);
          return cell.getDateCellValue();
      }
      
    /**
     * Tests that the active cell can be correctly read and set
     */

Lines 63-69 Link Here

(-)src/testcases/org/apache/poi/hssf/usermodel/TestReadWriteChart.java (-1 / +1 lines)
63	//System.out.println("first assertion for date");	63	//System.out.println("first assertion for date");
64	assertEquals(new GregorianCalendar(2000, 0, 1, 10, 51, 2).getTime(),	64	assertEquals(new GregorianCalendar(2000, 0, 1, 10, 51, 2).getTime(),
65	HSSFDateUtil	65	HSSFDateUtil
66	.getJavaDate(firstCell.getNumericCellValue()));	66	.getJavaDate(firstCell.getNumericCellValue(), false));
67	HSSFRow row = sheet.createRow(( short ) 15);	67	HSSFRow row = sheet.createRow(( short ) 15);
68	HSSFCell cell = row.createCell(( short ) 1);	68	HSSFCell cell = row.createCell(( short ) 1);
69		69

Return to bug 43551

Lines 35-40 Link Here

(-)src/java/org/apache/poi/hssf/usermodel/HSSFDateUtil.java (-39 / +25 lines)
35	* @author Glen Stampoultzis (glens at apache.org)	35	* @author Glen Stampoultzis (glens at apache.org)
36	* @author Dan Sherman (dsherman at isisph.com)	36	* @author Dan Sherman (dsherman at isisph.com)
37	* @author Hack Kampbjorn (hak at 2mba.dk)	37	* @author Hack Kampbjorn (hak at 2mba.dk)
		38	* @author Alex Jacoby (ajacoby at gmail.com)
		39	* @author Pavel Krupets (pkrupets at palmtreebusiness dot com)
38	*/	40	*/
39		41
40	public class HSSFDateUtil	42	public class HSSFDateUtil
Lines 54-72 Link Here
54	* @return Excel representation of Date (-1 if error - test for error by checking for less than 0.1)	56	* @return Excel representation of Date (-1 if error - test for error by checking for less than 0.1)
55	* @param date the Date	57	* @param date the Date
56	*/	58	*/
57		59
58	public static double getExcelDate(Date date)	60	public static double getExcelDate(Date date, boolean use1904windowing) {
59	{
60	Calendar calStart = new GregorianCalendar();	61	Calendar calStart = new GregorianCalendar();
61		62	calStart.setTime(date); // If date includes hours, minutes, and seconds, set them to 0
62	calStart.setTime(	63
63	date); // If date includes hours, minutes, and seconds, set them to 0	64	if ((!use1904windowing && calStart.get(Calendar.YEAR) < 1900) \|\|
64	if (calStart.get(Calendar.YEAR) < 1900)	65	(use1904windowing && calStart.get(Calendar.YEAR) < 1904))
65	{	66	{
66	return BAD_DATE;	67	return BAD_DATE;
67	}	68	} else {
68	else
69	{
70	// Because of daylight time saving we cannot use	69	// Because of daylight time saving we cannot use
71	// date.getTime() - calStart.getTimeInMillis()	70	// date.getTime() - calStart.getTimeInMillis()
72	// as the difference in milliseconds between 00:00 and 04:00	71	// as the difference in milliseconds between 00:00 and 04:00
Lines 80-110 Link Here
80	) * 1000 + calStart.get(Calendar.MILLISECOND)	79	) * 1000 + calStart.get(Calendar.MILLISECOND)
81	) / ( double ) DAY_MILLISECONDS;	80	) / ( double ) DAY_MILLISECONDS;
82	calStart = dayStart(calStart);	81	calStart = dayStart(calStart);
83
84	double value = fraction + absoluteDay(calStart);
85		82
86	if (value >= 60) {	83	double value = fraction + absoluteDay(calStart, use1904windowing);
87	value += 1;	84
		85	if (!use1904windowing && value >= 60) {
		86	value++;
		87	} else if (use1904windowing) {
		88	value--;
88	}	89	}
89		90
90	return value;	91	return value;
91	}	92	}
92	}	93	}
93
94	/**
95	* Given a excel date, converts it into a Date.
96	* Assumes 1900 date windowing.
97	*
98	* @param date the Excel Date
99	*
100	* @return Java representation of a date (null if error)
101	* @see #getJavaDate(double,boolean)
102	*/
103
104	public static Date getJavaDate(double date)
105	{
106	return getJavaDate(date,false);
107	}
108		94
109	/**	95	/**
110	* Given an Excel date with either 1900 or 1904 date windowing,	96	* Given an Excel date with either 1900 or 1904 date windowing,
Lines 142-148 Link Here
142	GregorianCalendar calendar = new GregorianCalendar(startYear,0,	128	GregorianCalendar calendar = new GregorianCalendar(startYear,0,
143	wholeDays + dayAdjust);	129	wholeDays + dayAdjust);
144	int millisecondsInDay = (int)((date - Math.floor(date)) *	130	int millisecondsInDay = (int)((date - Math.floor(date)) *
145	(double) DAY_MILLISECONDS + 0.5);	131	DAY_MILLISECONDS + 0.5);
146	calendar.set(GregorianCalendar.MILLISECOND, millisecondsInDay);	132	calendar.set(GregorianCalendar.MILLISECOND, millisecondsInDay);
147	return calendar.getTime();	133	return calendar.getTime();
148	}	134	}
Lines 238-244 Link Here
238	* Check if a cell contains a date	224	* Check if a cell contains a date
239	* Since dates are stored internally in Excel as double values	225	* Since dates are stored internally in Excel as double values
240	* we infer it is a date if it is formatted as such.	226	* we infer it is a date if it is formatted as such.
241	* @see #isADateFormat(int,string)	227	* @see #isADateFormat(int, String)
242	* @see #isInternalDateFormat(int)	228	* @see #isInternalDateFormat(int)
243	*/	229	*/
244	public static boolean isCellDateFormatted(HSSFCell cell) {	230	public static boolean isCellDateFormatted(HSSFCell cell) {
Lines 259-265 Link Here
259	* excel date formats.	245	* excel date formats.
260	* As Excel stores a great many of its dates in "non-internal"	246	* As Excel stores a great many of its dates in "non-internal"
261	* date formats, you will not normally want to use this method.	247	* date formats, you will not normally want to use this method.
262	* @see #isADateFormat(int,string)	248	* @see #isADateFormat(int,String)
263	* @see #isInternalDateFormat(int)	249	* @see #isInternalDateFormat(int)
264	*/	250	*/
265	public static boolean isCellInternalDateFormatted(HSSFCell cell) {	251	public static boolean isCellInternalDateFormatted(HSSFCell cell) {
Lines 296-305 Link Here
296	* @exception IllegalArgumentException if date is invalid	282	* @exception IllegalArgumentException if date is invalid
297	*/	283	*/
298		284
299	private static int absoluteDay(Calendar cal)	285	static int absoluteDay(Calendar cal, boolean use1904windowing)
300	{	286	{
301	return cal.get(Calendar.DAY_OF_YEAR)	287	return cal.get(Calendar.DAY_OF_YEAR)
302	+ daysInPriorYears(cal.get(Calendar.YEAR));	288	+ daysInPriorYears(cal.get(Calendar.YEAR), use1904windowing);
303	}	289	}
304		290
305	/**	291	/**
Lines 307-320 Link Here
307	*	293	*
308	* @return days number of days in years prior to yr.	294	* @return days number of days in years prior to yr.
309	* @param yr a year (1900 < yr < 4000)	295	* @param yr a year (1900 < yr < 4000)
		296	* @param use1904windowing
310	* @exception IllegalArgumentException if year is outside of range.	297	* @exception IllegalArgumentException if year is outside of range.
311	*/	298	*/
312		299
313	private static int daysInPriorYears(int yr)	300	private static int daysInPriorYears(int yr, boolean use1904windowing)
314	{	301	{
315	if (yr < 1900) {	302	if ((!use1904windowing && yr < 1900) \|\| (use1904windowing && yr < 1900)) {
316	throw new IllegalArgumentException(	303	throw new IllegalArgumentException("'year' must be 1900 or greater");
317	"'year' must be 1900 or greater");
318	}	304	}
319		305
320	int yr1 = yr - 1;	306	int yr1 = yr - 1;
Lines 323-329 Link Here
323	+ yr1 / 400 // plus years divisible by 400	309	+ yr1 / 400 // plus years divisible by 400
324	- 460; // leap days in previous 1900 years	310	- 460; // leap days in previous 1900 years
325		311
326	return 365 * (yr - 1900) + leapDays;	312	return 365 * (yr - (use1904windowing ? 1904 : 1900)) + leapDays;
327	}	313	}
328		314
329	// set HH:MM:SS fields of cal to 00:00:00:000	315	// set HH:MM:SS fields of cal to 00:00:00:000

Lines 44-49 Link Here

(-)src/testcases/org/apache/poi/hssf/usermodel/TestHSSFCell.java (-33 / +98 lines)
44	* paticular datatypes, etc.	44	* paticular datatypes, etc.
45	* @author Andrew C. Oliver (andy at superlinksoftware dot com)	45	* @author Andrew C. Oliver (andy at superlinksoftware dot com)
46	* @author Dan Sherman (dsherman at isisph.com)	46	* @author Dan Sherman (dsherman at isisph.com)
		47	* @author Alex Jacoby (ajacoby at gmail.com)
47	*/	48	*/
48		49
49	public class TestHSSFCell	50	public class TestHSSFCell
Lines 107-148 Link Here
107	}	108	}
108		109
109	/**	110	/**
110	* Checks that the recognition of files using 1904 date windowing	111	* Checks that the recognition of files using 1904 date windowing
111	* is working properly. Conversion of the date is also an issue,	112	* is working properly. Conversion of the date is also an issue,
112	* but there's a separate unit test for that.	113	* but there's a separate unit test for that.
113	*/	114	*/
114	public void testDateWindowing() throws Exception {	115	public void testDateWindowingRead() throws Exception {
115	GregorianCalendar cal = new GregorianCalendar(2000,0,1); // Jan. 1, 2000	116	GregorianCalendar cal = new GregorianCalendar(2000,0,1); // Jan. 1, 2000
116	Date date = cal.getTime();	117	Date date = cal.getTime();
117	String path = System.getProperty("HSSF.testdata.path");	118	String path = System.getProperty("HSSF.testdata.path");
118		119
119	// first check a file with 1900 Date Windowing	120	// first check a file with 1900 Date Windowing
120	String filename = path + "/1900DateWindowing.xls";	121	String filename = path + "/1900DateWindowing.xls";
121	FileInputStream stream = new FileInputStream(filename);	122	FileInputStream stream = new FileInputStream(filename);
122	POIFSFileSystem fs = new POIFSFileSystem(stream);	123	POIFSFileSystem fs = new POIFSFileSystem(stream);
123	HSSFWorkbook workbook = new HSSFWorkbook(fs);	124	HSSFWorkbook workbook = new HSSFWorkbook(fs);
124	HSSFSheet sheet = workbook.getSheetAt(0);	125	HSSFSheet sheet = workbook.getSheetAt(0);
125		126
126	assertEquals("Date from file using 1900 Date Windowing",	127	assertEquals("Date from file using 1900 Date Windowing",
127	date.getTime(),	128	date.getTime(),
128	sheet.getRow(0).getCell((short)0)	129	sheet.getRow(0).getCell((short)0)
129	.getDateCellValue().getTime());	130	.getDateCellValue().getTime());
130	stream.close();	131	stream.close();
131		132
132	// now check a file with 1904 Date Windowing	133	// now check a file with 1904 Date Windowing
133	filename = path + "/1904DateWindowing.xls";	134	filename = path + "/1904DateWindowing.xls";
134	stream = new FileInputStream(filename);	135	stream = new FileInputStream(filename);
135	fs = new POIFSFileSystem(stream);	136	fs = new POIFSFileSystem(stream);
136	workbook = new HSSFWorkbook(fs);	137	workbook = new HSSFWorkbook(fs);
137	sheet = workbook.getSheetAt(0);	138	sheet = workbook.getSheetAt(0);
138		139
139	assertEquals("Date from file using 1904 Date Windowing",	140	assertEquals("Date from file using 1904 Date Windowing",
140	date.getTime(),	141	date.getTime(),
141	sheet.getRow(0).getCell((short)0)	142	sheet.getRow(0).getCell((short)0)
142	.getDateCellValue().getTime());	143	.getDateCellValue().getTime());
143	stream.close();	144	stream.close();
144	}	145	}
145		146
		147	/**
		148	* Checks that dates are properly written to both types of files:
		149	* those with 1900 and 1904 date windowing. Note that if the
		150	* previous test ({@link #testDateWindowingRead}) fails, the
		151	* results of this test are meaningless.
		152	*/
		153	public void testDateWindowingWrite() throws Exception {
		154	GregorianCalendar cal = new GregorianCalendar(2000,0,1); // Jan. 1, 2000
		155	Date date = cal.getTime();
		156	String path = System.getProperty("HSSF.testdata.path");
		157
		158	// first check a file with 1900 Date Windowing
		159	String filename = path + "/1900DateWindowing.xls";
		160	writeCell(filename, 0, (short) 1, date);
		161	assertEquals("Date from file using 1900 Date Windowing",
		162	date.getTime(),
		163	readCell(filename, 0, (short) 1).getTime());
		164
		165	// now check a file with 1904 Date Windowing
		166	filename = path + "/1904DateWindowing.xls";
		167	writeCell(filename, 0, (short) 1, date);
		168	assertEquals("Date from file using 1900 Date Windowing",
		169	date.getTime(),
		170	readCell(filename, 0, (short) 1).getTime());
		171	}
		172
		173	/**
		174	* Sets cell value and writes file.
		175	*/
		176	private void writeCell(String filename,
		177	int rowIdx, short colIdx, Date date) throws Exception {
		178	FileInputStream stream = new FileInputStream(filename);
		179	POIFSFileSystem fs = new POIFSFileSystem(stream);
		180	HSSFWorkbook workbook = new HSSFWorkbook(fs);
		181	HSSFSheet sheet = workbook.getSheetAt(0);
		182	HSSFRow row = sheet.getRow(rowIdx);
		183	HSSFCell cell = row.getCell(colIdx);
		184
		185	if (cell == null) {
		186	cell = row.createCell(colIdx);
		187	}
		188	cell.setCellValue(date);
		189
		190	// Write the file
		191	stream.close();
		192	FileOutputStream oStream = new FileOutputStream(filename);
		193	workbook.write(oStream);
		194	oStream.close();
		195	}
		196
		197	/**
		198	* Reads cell value from file.
		199	*/
		200	private Date readCell(String filename,
		201	int rowIdx, short colIdx) throws Exception {
		202	FileInputStream stream = new FileInputStream(filename);
		203	POIFSFileSystem fs = new POIFSFileSystem(stream);
		204	HSSFWorkbook workbook = new HSSFWorkbook(fs);
		205	HSSFSheet sheet = workbook.getSheetAt(0);
		206	HSSFRow row = sheet.getRow(rowIdx);
		207	HSSFCell cell = row.getCell(colIdx);
		208	return cell.getDateCellValue();
		209	}
		210
146	/**	211	/**
147	* Tests that the active cell can be correctly read and set	212	* Tests that the active cell can be correctly read and set
148	*/	213	*/