Attachment #17688 for bug #38641

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





        calStart.setTime(
            date);   // If date includes hours, minutes, and seconds, set them to 0
        return internalGetExcelDate(calStart);
    }

    /**
     * Given a Date, converts it into a double representing its internal Excel representation,
     *   which is the number of days since 1/1/1900. Fractional days represent hours, minutes, and seconds.
     *
     * @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(Calendar date)
    {
        return internalGetExcelDate((Calendar) date.clone());
    }

    // The time will be change to the daystart
    private static double internalGetExcelDate(Calendar date)
    {
        if (date.get(Calendar.YEAR) < 1900)
        {
            return BAD_DATE;
        }

	    // 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 = (((date.get(Calendar.HOUR_OF_DAY) * 60
                                 + date.get(Calendar.MINUTE)
                                ) * 60 + date.get(Calendar.SECOND)
                               ) * 1000 + date.get(Calendar.MILLISECOND)
                              ) / ( double ) DAY_MILLISECONDS;
            date = dayStart(date);

            return fraction + ( double ) absoluteDay(date)
                   - CAL_1900_ABSOLUTE;
        }
    }




     * set a date value for the cell. Excel treats dates as numeric so you will need to format the cell as
     * a date.
     *
     * This will set the cell value based on the Calendar's timezone as Excel
     * does not support timezones this means that both 20:00+03:00 and
     * 20:00-03:00 will be reported as the same value (20:00) even that there
     * are 6 hours difference between the two times. This difference can be
     * preserved by using <code>setCellValue(value.getTime())</code> which will
     * automatically shift the times to the default timezone.
     *
     * @param value  the date value to set this cell to.  For formulas we'll set the
     *        precalculated value, for numerics we'll set its value. For othertypes we
     *        will change the cell to a numeric cell and set its value.
     */
    public void setCellValue(Calendar value)
    {
        setCellValue(HSSFDateUtil.getExcelDate(value));
    }

    /**




        }
    }

    /**
     * Checks the conversion of a calendar uses the Calendar's TimeZone
     */
    public void testCalendarConversion() {
        GregorianCalendar date = new GregorianCalendar(2002, 0, 1, 12, 1, 1);
        Date expected = date.getTime();

        // Iteratating over the hours exposes any rounding issues.
        for (int hour = -12; hour <= 12; hour++)
        {
            String id = "GMT" + (hour < 0 ? "" : "+") + hour + ":00";
            date.setTimeZone(TimeZone.getTimeZone(id));
            date.set(Calendar.HOUR_OF_DAY, 12);
            double excelDate = HSSFDateUtil.getExcelDate(date);
            Date javaDate = HSSFDateUtil.getJavaDate(excelDate);
            assertEquals("Checking timezone " + id, expected.getTime(), javaDate.getTime());
        }
    }

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

Return to bug 38641

Lines 63-69 Link Here

(-)src/java/org/apache/poi/hssf/usermodel/HSSFDateUtil.java (-7 / +25 lines)
63		63
64	calStart.setTime(	64	calStart.setTime(
65	date); // If date includes hours, minutes, and seconds, set them to 0	65	date); // If date includes hours, minutes, and seconds, set them to 0
66	if (calStart.get(Calendar.YEAR) < 1900)	66	return internalGetExcelDate(calStart);
		67	}
		68
		69	/**
		70	* Given a Date, converts it into a double representing its internal Excel representation,
		71	* which is the number of days since 1/1/1900. Fractional days represent hours, minutes, and seconds.
		72	*
		73	* @return Excel representation of Date (-1 if error - test for error by checking for less than 0.1)
		74	* @param date the Date
		75	*/
		76	public static double getExcelDate(Calendar date)
		77	{
		78	return internalGetExcelDate((Calendar) date.clone());
		79	}
		80
		81	// The time will be change to the daystart
		82	private static double internalGetExcelDate(Calendar date)
		83	{
		84	if (date.get(Calendar.YEAR) < 1900)
67	{	85	{
68	return BAD_DATE;	86	return BAD_DATE;
69	}	87	}
Lines 76-89 Link Here
76	// be 4 hours.	94	// be 4 hours.
77	// E.g. 2004-03-28 04:00 CEST - 2004-03-28 00:00 CET is 3 hours	95	// E.g. 2004-03-28 04:00 CEST - 2004-03-28 00:00 CET is 3 hours
78	// and 2004-10-31 04:00 CET - 2004-10-31 00:00 CEST is 5 hours	96	// and 2004-10-31 04:00 CET - 2004-10-31 00:00 CEST is 5 hours
79	double fraction = (((calStart.get(Calendar.HOUR_OF_DAY) * 60	97	double fraction = (((date.get(Calendar.HOUR_OF_DAY) * 60
80	+ calStart.get(Calendar.MINUTE)	98	+ date.get(Calendar.MINUTE)
81	) * 60 + calStart.get(Calendar.SECOND)	99	) * 60 + date.get(Calendar.SECOND)
82	) * 1000 + calStart.get(Calendar.MILLISECOND)	100	) * 1000 + date.get(Calendar.MILLISECOND)
83	) / ( double ) DAY_MILLISECONDS;	101	) / ( double ) DAY_MILLISECONDS;
84	calStart = dayStart(calStart);	102	date = dayStart(date);
85		103
86	return fraction + ( double ) absoluteDay(calStart)	104	return fraction + ( double ) absoluteDay(date)
87	- CAL_1900_ABSOLUTE;	105	- CAL_1900_ABSOLUTE;
88	}	106	}
89	}	107	}

Lines 518-530 Link Here

(-)src/java/org/apache/poi/hssf/usermodel/HSSFCell.java (-1 / +8 lines)
518	* set a date value for the cell. Excel treats dates as numeric so you will need to format the cell as	518	* set a date value for the cell. Excel treats dates as numeric so you will need to format the cell as
519	* a date.	519	* a date.
520	*	520	*
		521	* This will set the cell value based on the Calendar's timezone as Excel
		522	* does not support timezones this means that both 20:00+03:00 and
		523	* 20:00-03:00 will be reported as the same value (20:00) even that there
		524	* are 6 hours difference between the two times. This difference can be
		525	* preserved by using <code>setCellValue(value.getTime())</code> which will
		526	* automatically shift the times to the default timezone.
		527	*
521	* @param value the date value to set this cell to. For formulas we'll set the	528	* @param value the date value to set this cell to. For formulas we'll set the
522	* precalculated value, for numerics we'll set its value. For othertypes we	529	* precalculated value, for numerics we'll set its value. For othertypes we
523	* will change the cell to a numeric cell and set its value.	530	* will change the cell to a numeric cell and set its value.
524	*/	531	*/
525	public void setCellValue(Calendar value)	532	public void setCellValue(Calendar value)
526	{	533	{
527	setCellValue(value.getTime());	534	setCellValue(HSSFDateUtil.getExcelDate(value));
528	}	535	}
529		536
530	/**	537	/**

Lines 182-187 Link Here

(-)src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDateUtil.java (+19 lines)
182	}	182	}
183	}	183	}
184		184
		185	/**
		186	* Checks the conversion of a calendar uses the Calendar's TimeZone
		187	*/
		188	public void testCalendarConversion() {
		189	GregorianCalendar date = new GregorianCalendar(2002, 0, 1, 12, 1, 1);
		190	Date expected = date.getTime();
		191
		192	// Iteratating over the hours exposes any rounding issues.
		193	for (int hour = -12; hour <= 12; hour++)
		194	{
		195	String id = "GMT" + (hour < 0 ? "" : "+") + hour + ":00";
		196	date.setTimeZone(TimeZone.getTimeZone(id));
		197	date.set(Calendar.HOUR_OF_DAY, 12);
		198	double excelDate = HSSFDateUtil.getExcelDate(date);
		199	Date javaDate = HSSFDateUtil.getJavaDate(excelDate);
		200	assertEquals("Checking timezone " + id, expected.getTime(), javaDate.getTime());
		201	}
		202	}
		203
185	public static void main(String [] args) {	204	public static void main(String [] args) {
186	System.out	205	System.out
187	.println("Testing org.apache.poi.hssf.usermodel.TestHSSFDateUtil");	206	.println("Testing org.apache.poi.hssf.usermodel.TestHSSFDateUtil");