Attachment #29155 for bug #53644

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




 * @author  Hack Kampbjorn (hak at 2mba.dk)
 * @author  Alex Jacoby (ajacoby at gmail.com)
 * @author  Pavel Krupets (pkrupets at palmtreebusiness dot com)
 * @author  Thies Wellpott
 */
public class DateUtil {
    protected DateUtil() {
        // no instances of this class
    }
    private static final int SECONDS_PER_MINUTE = 60;
    private static final int MINUTES_PER_HOUR = 60;
    private static final int HOURS_PER_DAY = 24;
    private static final int SECONDS_PER_DAY = (HOURS_PER_DAY * MINUTES_PER_HOUR * SECONDS_PER_MINUTE);

    public static final int SECONDS_PER_MINUTE = 60;
    public static final int MINUTES_PER_HOUR = 60;
    public static final int HOURS_PER_DAY = 24;
    public static final int SECONDS_PER_DAY = (HOURS_PER_DAY * MINUTES_PER_HOUR * SECONDS_PER_MINUTE);

    private static final int    BAD_DATE         = -1;   // used to specify that date is invalid
    public static final long   DAY_MILLISECONDS = SECONDS_PER_DAY * 1000L;

    private static final Pattern TIME_SEPARATOR_PATTERN = Pattern.compile(":");


    //  elapsed time patterns: [h],[m] and [s]
    private static final Pattern date_ptrn4 = Pattern.compile("^\\[([hH]+|[mM]+|[sS]+)\\]");

    // only get this static info once (because operations are not really cheap)
    private static final TimeZone TIMEZONE_UTC = TimeZone.getTimeZone("UTC");


    /**
     * 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 Java representation of the date, or null if date is not a valid Excel date
     */
    public static Date getJavaDate(double date, boolean use1904windowing, TimeZone tz) {
        return getJavaCalendar(date, use1904windowing, tz).getTime();
          return null;
       }
       Calendar calendar;
       if (tz != null)
          calendar = new GregorianCalendar(tz);
       else
          calendar = new GregorianCalendar(); // using default time-zone
       
      int wholeDays = (int)Math.floor(date);
      int millisecondsInDay = (int)((date - wholeDays) * DAY_MILLISECONDS + 0.5);
      setCalendar(calendar, wholeDays, millisecondsInDay, use1904windowing);
      return calendar.getTime();
    }
    /**
     *  Given an Excel date with either 1900 or 1904 date windowing,

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


    public static void setCalendar(Calendar calendar, int wholeDays,
            int millisecondsInDay, boolean use1904windowing) {
        int startYear = 1900;



    /**
     * 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,
     *   or false if using 1900 date 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);
    }

    /**
     * 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);
    }


    /**
     * Get EXCEL date as Java Calendar with given time zone.
     * @see getJavaDate(double, TimeZone)
     * @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) {
        if (!isValidExcelDate(date)) {
            return null;
        }
        int wholeDays = (int)Math.floor(date);
        int millisecondsInDay = (int)((date - wholeDays) * DAY_MILLISECONDS + 0.5);
        Calendar calendar;
        if (timeZone != null) {
            calendar = new GregorianCalendar(timeZone);
        } else {
            calendar = new GregorianCalendar();     // using default time-zone
        }
        setCalendar(calendar, wholeDays, millisecondsInDay, use1904windowing);
        return calendar;
    }


    /**
     * Given a format ID and its format String, will check to see if the
     *  format represents a date format or not.
     * Firstly, it will check to see if the format ID corresponds to an

        }

        String fs = formatString;
        /*if (false) {
            // Normalize the format string. The code below is equivalent
            // to the following consecutive regexp replacements:



             // The code above was reworked as suggested in bug 48425:
             // simple loop is more efficient than consecutive regexp replacements.
        }*/
        StringBuilder sb = new StringBuilder(fs.length());
        for (int i = 0; i < fs.length(); i++) {
            char c = fs.charAt(i);

Lines 117-122 Link Here

(-)src/java/org/apache/poi/ss/formula/eval/FunctionEval.java (+1 lines)
117	retval[68] = CalendarFieldFunction.MONTH;	117	retval[68] = CalendarFieldFunction.MONTH;
118	retval[69] = CalendarFieldFunction.YEAR;	118	retval[69] = CalendarFieldFunction.YEAR;
119		119
		120	retval[70] = WeekdayFunc.instance;
120	retval[71] = CalendarFieldFunction.HOUR;	121	retval[71] = CalendarFieldFunction.HOUR;
121	retval[72] = CalendarFieldFunction.MINUTE;	122	retval[72] = CalendarFieldFunction.MINUTE;
122	retval[73] = CalendarFieldFunction.SECOND;	123	retval[73] = CalendarFieldFunction.SECOND;

Lines 43-52 Link Here

(-)src/java/org/apache/poi/ss/formula/functions/Address.java (-2 / +2 lines)
43	int col = (int)NumericFunction.singleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);	43	int col = (int)NumericFunction.singleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);
44		44
45	int refType;	45	int refType;
46	if(args.length > 2){	46	if (args.length > 2 && args[2] != MissingArgEval.instance) {
47	refType = (int)NumericFunction.singleOperandEvaluate(args[2], srcRowIndex, srcColumnIndex);	47	refType = (int)NumericFunction.singleOperandEvaluate(args[2], srcRowIndex, srcColumnIndex);
48	} else {	48	} else {
49	refType = REF_ABSOLUTE;	49	refType = REF_ABSOLUTE; // this is also the default if parameter is not given
50	}	50	}
51	switch (refType){	51	switch (refType){
52	case REF_ABSOLUTE:	52	case REF_ABSOLUTE:




package org.apache.poi.ss.formula.functions;

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

import org.apache.poi.ss.formula.eval.ErrorEval;
import org.apache.poi.ss.formula.eval.EvaluationException;

 * Implementation of Excel functions Date parsing functions:
 *  Date - DAY, MONTH and YEAR
 *  Time - HOUR, MINUTE and SECOND
 *
 * @author Others (not mentioned in code)
 * @author Thies Wellpott
 */
public final class CalendarFieldFunction extends Fixed1ArgFunction {
	public static final Function YEAR = new CalendarFieldFunction(Calendar.YEAR);
	public static final Function MONTH = new CalendarFieldFunction(Calendar.MONTH);
	public static final Function DAY = new CalendarFieldFunction(Calendar.DAY_OF_MONTH);
	public static final Function HOUR = new CalendarFieldFunction(Calendar.HOUR_OF_DAY);
    public static final Function MINUTE = new CalendarFieldFunction(Calendar.MINUTE);
    public static final Function SECOND = new CalendarFieldFunction(Calendar.SECOND);

	private final int _dateFieldId;


	private int getCalField(double serialDate) {
	   // For some reason, a date of 0 in Excel gets shown
	   //  as the non existant 1900-01-00
		if (((int)serialDate) == 0) {
			switch (_dateFieldId) {
				case Calendar.YEAR: return 1900;
				case Calendar.MONTH: return 1;

		}

		// TODO Figure out if we're in 1900 or 1904
		// EXCEL functions round up nearly a half second (probably to prevent floating point
		// rounding issues); use UTC here to prevent daylight saving issues for HOUR
		Calendar c = DateUtil.getJavaCalendarUTC(serialDate + 0.4995 / DateUtil.SECONDS_PER_DAY, false);
		c.setTime(d);
		int result = c.get(_dateFieldId);
		
		// Month is a special case due to C semantics

Lines 20-25 Link Here

(-)src/java/org/apache/poi/ss/formula/functions/BooleanFunction.java (+3 lines)
20	import org.apache.poi.ss.formula.eval.BoolEval;	20	import org.apache.poi.ss.formula.eval.BoolEval;
21	import org.apache.poi.ss.formula.eval.ErrorEval;	21	import org.apache.poi.ss.formula.eval.ErrorEval;
22	import org.apache.poi.ss.formula.eval.EvaluationException;	22	import org.apache.poi.ss.formula.eval.EvaluationException;
		23	import org.apache.poi.ss.formula.eval.MissingArgEval;
23	import org.apache.poi.ss.formula.eval.OperandResolver;	24	import org.apache.poi.ss.formula.eval.OperandResolver;
24	import org.apache.poi.ss.formula.eval.RefEval;	25	import org.apache.poi.ss.formula.eval.RefEval;
25	import org.apache.poi.ss.formula.eval.ValueEval;	26	import org.apache.poi.ss.formula.eval.ValueEval;
Lines 81-86 Link Here
81	if (arg instanceof RefEval) {	82	if (arg instanceof RefEval) {
82	ValueEval ve = ((RefEval) arg).getInnerValueEval();	83	ValueEval ve = ((RefEval) arg).getInnerValueEval();
83	tempVe = OperandResolver.coerceValueToBoolean(ve, true);	84	tempVe = OperandResolver.coerceValueToBoolean(ve, true);
		85	} else if (arg == MissingArgEval.instance) {
		86	tempVe = null; // you can leave out parameters, they are simply ignored
84	} else {	87	} else {
85	tempVe = OperandResolver.coerceValueToBoolean(arg, false);	88	tempVe = OperandResolver.coerceValueToBoolean(arg, false);
86	}	89	}




            <p>
                As of Feb 2012, POI supports about 140 built-in functions,
                see <link href="#appendixA">Appendix A</link> for the full list.
                You can programmatically list supported / unsuported functions using the following helper methods:
            </p>
            <source>
    // list of functions that POI can evaluate
    Collection&lt;String&gt; supportedFuncs = WorkbookEvaluator.getSupportedFunctionNames();

    // list of functions that are not supported by POI
    Collection&lt;String&gt; unsupportedFuncs = WorkbookEvaluator.getNotSupportedFunctionNames();

    VAR
    VARP
    VLOOKUP
    WEEKDAY
    WORKDAY
    YEAR
    YEARFRAC

Lines 59-64 Link Here

(-)src/testcases/org/apache/poi/ss/formula/functions/TestCalendarFieldFunction.java (+27 lines)
59	confirm("SECOND(40627.4860417)", 54);	59	confirm("SECOND(40627.4860417)", 54);
60	}	60	}
61		61
		62	public void testRounding() {
		63	// 41484.999994200 = 23:59:59,499
		64	// 41484.9999942129 = 23:59:59,500 (but sub-milliseconds are below 0.5 (0.49999453965575), XLS-second results in 59)
		65	// 41484.9999942130 = 23:59:59,500 (sub-milliseconds are 0.50000334065408, XLS-second results in 00)
		66
		67	confirm("DAY(41484.999994200)", 29);
		68	confirm("SECOND(41484.999994200)", 59);
		69
		70	confirm("DAY(41484.9999942129)", 29);
		71	confirm("HOUR(41484.9999942129)", 23);
		72	confirm("MINUTE(41484.9999942129)", 59);
		73	confirm("SECOND(41484.9999942129)", 59);
		74
		75	confirm("DAY(41484.9999942130)", 30);
		76	confirm("HOUR(41484.9999942130)", 0);
		77	confirm("MINUTE(41484.9999942130)", 0);
		78	confirm("SECOND(41484.9999942130)", 0);
		79	}
		80
		81	public void testDaylightSaving() {
		82	confirm("HOUR(41364.08263888890000)", 1); // 31.03.2013 01:59:00,000
		83	confirm("HOUR(41364.08333333330000)", 2); // 31.03.2013 02:00:00,000 (this time does not exist in TZ CET, but EXCEL does not care)
		84	confirm("HOUR(41364.08402777780000)", 2); // 31.03.2013 02:01:00,000
		85	confirm("HOUR(41364.12430555560000)", 2); // 31.03.2013 02:59:00,000
		86	confirm("HOUR(41364.12500000000000)", 3); // 31.03.2013 03:00:00,000
		87	}
		88
62	public void testBugDate() {	89	public void testBugDate() {
63	confirm("YEAR(0.0)", 1900);	90	confirm("YEAR(0.0)", 1900);
64	confirm("MONTH(0.0)", 1);	91	confirm("MONTH(0.0)", 1);

Lines 33-38 Link Here

(-)src/testcases/org/apache/poi/ss/formula/functions/TestAddress.java (+3 lines)
33	String formulaText = "ADDRESS(1,2)";	33	String formulaText = "ADDRESS(1,2)";
34	confirmResult(fe, cell, formulaText, "$B$1");	34	confirmResult(fe, cell, formulaText, "$B$1");
35		35
		36	formulaText = "ADDRESS(1,2,)"; // with explicitly empty third parameter
		37	confirmResult(fe, cell, formulaText, "$B$1");
		38
36	formulaText = "ADDRESS(22,44)";	39	formulaText = "ADDRESS(22,44)";
37	confirmResult(fe, cell, formulaText, "$AR$22");	40	confirmResult(fe, cell, formulaText, "$AR$22");
38		41

Lines 50-60 Link Here

(-)patch.xml (-2 / +2 lines)
50	<filterchain>	50	<filterchain>
51	<!-- capture any new files -->	51	<!-- capture any new files -->
52	<linecontainsregexp>	52	<linecontainsregexp>
53	<regexp pattern="(\?\|A)......"/>	53	<regexp pattern="^(\?\|A)......"/>
54	</linecontainsregexp>	54	</linecontainsregexp>
55	<!-- filter out the first six characters -->	55	<!-- filter out the first six characters -->
56	<tokenfilter>	56	<tokenfilter>
57	<replaceregex pattern="(.......)" replace=""/>	57	<replaceregex pattern="^(.......)" replace=""/>
58	</tokenfilter>	58	</tokenfilter>
59	<!--remove line breaks -->	59	<!--remove line breaks -->
60	<striplinebreaks/>	60	<striplinebreaks/>

Return to bug 53644

Lines 33-50 Link Here

(-)src/java/org/apache/poi/ss/usermodel/DateUtil.java (-21 / +65 lines)
33	* @author Hack Kampbjorn (hak at 2mba.dk)	33	* @author Hack Kampbjorn (hak at 2mba.dk)
34	* @author Alex Jacoby (ajacoby at gmail.com)	34	* @author Alex Jacoby (ajacoby at gmail.com)
35	* @author Pavel Krupets (pkrupets at palmtreebusiness dot com)	35	* @author Pavel Krupets (pkrupets at palmtreebusiness dot com)
		36	* @author Thies Wellpott
36	*/	37	*/
37	public class DateUtil {	38	public class DateUtil {
38	protected DateUtil() {	39	protected DateUtil() {
39	// no instances of this class	40	// no instances of this class
40	}	41	}
41	private static final int SECONDS_PER_MINUTE = 60;
42	private static final int MINUTES_PER_HOUR = 60;
43	private static final int HOURS_PER_DAY = 24;
44	private static final int SECONDS_PER_DAY = (HOURS_PER_DAY * MINUTES_PER_HOUR * SECONDS_PER_MINUTE);
45		42
		43	public static final int SECONDS_PER_MINUTE = 60;
		44	public static final int MINUTES_PER_HOUR = 60;
		45	public static final int HOURS_PER_DAY = 24;
		46	public static final int SECONDS_PER_DAY = (HOURS_PER_DAY * MINUTES_PER_HOUR * SECONDS_PER_MINUTE);
		47
46	private static final int BAD_DATE = -1; // used to specify that date is invalid	48	private static final int BAD_DATE = -1; // used to specify that date is invalid
47	private static final long DAY_MILLISECONDS = SECONDS_PER_DAY * 1000L;	49	public static final long DAY_MILLISECONDS = SECONDS_PER_DAY * 1000L;
48		50
49	private static final Pattern TIME_SEPARATOR_PATTERN = Pattern.compile(":");	51	private static final Pattern TIME_SEPARATOR_PATTERN = Pattern.compile(":");
50		52
Lines 57-62 Link Here
57	// elapsed time patterns: [h],[m] and [s]	59	// elapsed time patterns: [h],[m] and [s]
58	private static final Pattern date_ptrn4 = Pattern.compile("^\\[([hH]+\|[mM]+\|[sS]+)\\]");	60	private static final Pattern date_ptrn4 = Pattern.compile("^\\[([hH]+\|[mM]+\|[sS]+)\\]");
59		61
		62	// only get this static info once (because operations are not really cheap)
		63	private static final TimeZone TIMEZONE_UTC = TimeZone.getTimeZone("UTC");
		64
		65
60	/**	66	/**
61	* Given a Date, converts it into a double representing its internal Excel representation,	67	* Given a Date, converts it into a double representing its internal Excel representation,
62	* which is the number of days since 1/1/1900. Fractional days represent hours, minutes, and seconds.	68	* which is the number of days since 1/1/1900. Fractional days represent hours, minutes, and seconds.
Lines 178-196 Link Here
178	* @return Java representation of the date, or null if date is not a valid Excel date	184	* @return Java representation of the date, or null if date is not a valid Excel date
179	*/	185	*/
180	public static Date getJavaDate(double date, boolean use1904windowing, TimeZone tz) {	186	public static Date getJavaDate(double date, boolean use1904windowing, TimeZone tz) {
181	if (!isValidExcelDate(date)) {	187	return getJavaCalendar(date, use1904windowing, tz).getTime();
182	return null;
183	}
184	Calendar calendar;
185	if (tz != null)
186	calendar = new GregorianCalendar(tz);
187	else
188	calendar = new GregorianCalendar(); // using default time-zone
189
190	int wholeDays = (int)Math.floor(date);
191	int millisecondsInDay = (int)((date - wholeDays) * DAY_MILLISECONDS + 0.5);
192	setCalendar(calendar, wholeDays, millisecondsInDay, use1904windowing);
193	return calendar.getTime();
194	}	188	}
195	/**	189	/**
196	* Given an Excel date with either 1900 or 1904 date windowing,	190	* Given an Excel date with either 1900 or 1904 date windowing,
Lines 212-219 Link Here
212	* @see java.util.TimeZone	206	* @see java.util.TimeZone
213	*/	207	*/
214	public static Date getJavaDate(double date, boolean use1904windowing) {	208	public static Date getJavaDate(double date, boolean use1904windowing) {
215	return getJavaDate(date, use1904windowing, (TimeZone)null);	209	return getJavaCalendar(date, use1904windowing).getTime();
216	}	210	}
		211
		212
217	public static void setCalendar(Calendar calendar, int wholeDays,	213	public static void setCalendar(Calendar calendar, int wholeDays,
218	int millisecondsInDay, boolean use1904windowing) {	214	int millisecondsInDay, boolean use1904windowing) {
219	int startYear = 1900;	215	int startYear = 1900;
Lines 233-238 Link Here
233		229
234		230
235	/**	231	/**
		232	* Get EXCEL date as Java Calendar (with default time zone).
		233	* This is like {@link #getJavaDate(double, boolean)} but returns a Calendar object.
		234	* @param date The Excel date.
		235	* @param use1904windowing true if date uses 1904 windowing,
		236	* or false if using 1900 date windowing.
		237	* @return Java representation of the date, or null if date is not a valid Excel date
		238	*/
		239	public static Calendar getJavaCalendar(double date, boolean use1904windowing) {
		240	return getJavaCalendar(date, use1904windowing, (TimeZone)null);
		241	}
		242
		243	/**
		244	* Get EXCEL date as Java Calendar with UTC time zone.
		245	* This is similar to {@link #getJavaDate(double, boolean)} but returns a
		246	* Calendar object that has UTC as time zone, so no daylight saving hassle.
		247	* @param date The Excel date.
		248	* @param use1904windowing true if date uses 1904 windowing,
		249	* or false if using 1900 date windowing.
		250	* @return Java representation of the date in UTC, or null if date is not a valid Excel date
		251	*/
		252	public static Calendar getJavaCalendarUTC(double date, boolean use1904windowing) {
		253	return getJavaCalendar(date, use1904windowing, TIMEZONE_UTC);
		254	}
		255
		256
		257	/**
		258	* Get EXCEL date as Java Calendar with given time zone.
		259	* @see getJavaDate(double, TimeZone)
		260	* @return Java representation of the date, or null if date is not a valid Excel date
		261	*/
		262	public static Calendar getJavaCalendar(double date, boolean use1904windowing, TimeZone timeZone) {
		263	if (!isValidExcelDate(date)) {
		264	return null;
		265	}
		266	int wholeDays = (int)Math.floor(date);
		267	int millisecondsInDay = (int)((date - wholeDays) * DAY_MILLISECONDS + 0.5);
		268	Calendar calendar;
		269	if (timeZone != null) {
		270	calendar = new GregorianCalendar(timeZone);
		271	} else {
		272	calendar = new GregorianCalendar(); // using default time-zone
		273	}
		274	setCalendar(calendar, wholeDays, millisecondsInDay, use1904windowing);
		275	return calendar;
		276	}
		277
		278
		279	/**
236	* Given a format ID and its format String, will check to see if the	280	* Given a format ID and its format String, will check to see if the
237	* format represents a date format or not.	281	* format represents a date format or not.
238	* Firstly, it will check to see if the format ID corresponds to an	282	* Firstly, it will check to see if the format ID corresponds to an
Lines 257-263 Link Here
257	}	301	}
258		302
259	String fs = formatString;	303	String fs = formatString;
260	if (false) {	304	/*if (false) {
261	// Normalize the format string. The code below is equivalent	305	// Normalize the format string. The code below is equivalent
262	// to the following consecutive regexp replacements:	306	// to the following consecutive regexp replacements:
263		307
Lines 276-282 Link Here
276		320
277	// The code above was reworked as suggested in bug 48425:	321	// The code above was reworked as suggested in bug 48425:
278	// simple loop is more efficient than consecutive regexp replacements.	322	// simple loop is more efficient than consecutive regexp replacements.
279	}	323	}*/
280	StringBuilder sb = new StringBuilder(fs.length());	324	StringBuilder sb = new StringBuilder(fs.length());
281	for (int i = 0; i < fs.length(); i++) {	325	for (int i = 0; i < fs.length(); i++) {
282	char c = fs.charAt(i);	326	char c = fs.charAt(i);

Lines 18-25 Link Here

(-)src/java/org/apache/poi/ss/formula/functions/CalendarFieldFunction.java (-9 / +9 lines)
18	package org.apache.poi.ss.formula.functions;	18	package org.apache.poi.ss.formula.functions;
19		19
20	import java.util.Calendar;	20	import java.util.Calendar;
21	import java.util.Date;
22	import java.util.GregorianCalendar;
23		21
24	import org.apache.poi.ss.formula.eval.ErrorEval;	22	import org.apache.poi.ss.formula.eval.ErrorEval;
25	import org.apache.poi.ss.formula.eval.EvaluationException;	23	import org.apache.poi.ss.formula.eval.EvaluationException;
Lines 32-45 Link Here
32	* Implementation of Excel functions Date parsing functions:	30	* Implementation of Excel functions Date parsing functions:
33	* Date - DAY, MONTH and YEAR	31	* Date - DAY, MONTH and YEAR
34	* Time - HOUR, MINUTE and SECOND	32	* Time - HOUR, MINUTE and SECOND
		33	*
		34	* @author Others (not mentioned in code)
		35	* @author Thies Wellpott
35	*/	36	*/
36	public final class CalendarFieldFunction extends Fixed1ArgFunction {	37	public final class CalendarFieldFunction extends Fixed1ArgFunction {
37	public static final Function YEAR = new CalendarFieldFunction(Calendar.YEAR);	38	public static final Function YEAR = new CalendarFieldFunction(Calendar.YEAR);
38	public static final Function MONTH = new CalendarFieldFunction(Calendar.MONTH);	39	public static final Function MONTH = new CalendarFieldFunction(Calendar.MONTH);
39	public static final Function DAY = new CalendarFieldFunction(Calendar.DAY_OF_MONTH);	40	public static final Function DAY = new CalendarFieldFunction(Calendar.DAY_OF_MONTH);
40	public static final Function HOUR = new CalendarFieldFunction(Calendar.HOUR_OF_DAY);	41	public static final Function HOUR = new CalendarFieldFunction(Calendar.HOUR_OF_DAY);
41	public static final Function MINUTE = new CalendarFieldFunction(Calendar.MINUTE);	42	public static final Function MINUTE = new CalendarFieldFunction(Calendar.MINUTE);
42	public static final Function SECOND = new CalendarFieldFunction(Calendar.SECOND);	43	public static final Function SECOND = new CalendarFieldFunction(Calendar.SECOND);
43		44
44	private final int _dateFieldId;	45	private final int _dateFieldId;
45		46
Lines 64-70 Link Here
64	private int getCalField(double serialDate) {	65	private int getCalField(double serialDate) {
65	// For some reason, a date of 0 in Excel gets shown	66	// For some reason, a date of 0 in Excel gets shown
66	// as the non existant 1900-01-00	67	// as the non existant 1900-01-00
67	if(((int)serialDate) == 0) {	68	if (((int)serialDate) == 0) {
68	switch (_dateFieldId) {	69	switch (_dateFieldId) {
69	case Calendar.YEAR: return 1900;	70	case Calendar.YEAR: return 1900;
70	case Calendar.MONTH: return 1;	71	case Calendar.MONTH: return 1;
Lines 74-83 Link Here
74	}	75	}
75		76
76	// TODO Figure out if we're in 1900 or 1904	77	// TODO Figure out if we're in 1900 or 1904
77	Date d = DateUtil.getJavaDate(serialDate, false);	78	// EXCEL functions round up nearly a half second (probably to prevent floating point
78		79	// rounding issues); use UTC here to prevent daylight saving issues for HOUR
79	Calendar c = new GregorianCalendar();	80	Calendar c = DateUtil.getJavaCalendarUTC(serialDate + 0.4995 / DateUtil.SECONDS_PER_DAY, false);
80	c.setTime(d);
81	int result = c.get(_dateFieldId);	81	int result = c.get(_dateFieldId);
82		82
83	// Month is a special case due to C semantics	83	// Month is a special case due to C semantics

Lines 88-98 Link Here

(-)src/documentation/content/xdocs/spreadsheet/eval-devguide.xml (-2 / +3 lines)
88	<p>	88	<p>
89	As of Feb 2012, POI supports about 140 built-in functions,	89	As of Feb 2012, POI supports about 140 built-in functions,
90	see <link href="#appendixA">Appendix A</link> for the full list.	90	see <link href="#appendixA">Appendix A</link> for the full list.
91	You can programmatically list supported / unsuported functions using trhe following helper methods:	91	You can programmatically list supported / unsuported functions using the following helper methods:
92	</p>	92	</p>
93	<source>	93	<source>
94	// list of functions that POI can evaluate	94	// list of functions that POI can evaluate
95	Collection<String> suportedFuncs = WorkbookEvaluator.getSupportedFunctionNames();	95	Collection<String> supportedFuncs = WorkbookEvaluator.getSupportedFunctionNames();
96		96
97	// list of functions that are not supported by POI	97	// list of functions that are not supported by POI
98	Collection<String> unsupportedFuncs = WorkbookEvaluator.getNotSupportedFunctionNames();	98	Collection<String> unsupportedFuncs = WorkbookEvaluator.getNotSupportedFunctionNames();
Lines 366-371 Link Here
366	VAR	366	VAR
367	VARP	367	VARP
368	VLOOKUP	368	VLOOKUP
		369	WEEKDAY
369	WORKDAY	370	WORKDAY
370	YEAR	371	YEAR
371	YEARFRAC	372	YEARFRAC