--- src/java/org/apache/poi/ss/usermodel/DateUtil.java (revision 1368395)
+++ src/java/org/apache/poi/ss/usermodel/DateUtil.java (working copy)
@@ -33,18 +33,20 @@
* @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
- private static final long DAY_MILLISECONDS = SECONDS_PER_DAY * 1000L;
+ public static final long DAY_MILLISECONDS = SECONDS_PER_DAY * 1000L;
private static final Pattern TIME_SEPARATOR_PATTERN = Pattern.compile(":");
@@ -57,6 +59,10 @@
// 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.
@@ -178,19 +184,7 @@
* @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) {
- if (!isValidExcelDate(date)) {
- 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();
+ return getJavaCalendar(date, use1904windowing, tz).getTime();
}
/**
* Given an Excel date with either 1900 or 1904 date windowing,
@@ -212,8 +206,10 @@
* @see java.util.TimeZone
*/
public static Date getJavaDate(double date, boolean use1904windowing) {
- return getJavaDate(date, use1904windowing, (TimeZone)null);
+ return getJavaCalendar(date, use1904windowing).getTime();
}
+
+
public static void setCalendar(Calendar calendar, int wholeDays,
int millisecondsInDay, boolean use1904windowing) {
int startYear = 1900;
@@ -233,6 +229,54 @@
/**
+ * 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
@@ -257,7 +301,7 @@
}
String fs = formatString;
- if (false) {
+ /*if (false) {
// Normalize the format string. The code below is equivalent
// to the following consecutive regexp replacements:
@@ -276,7 +320,7 @@
// 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);
--- src/java/org/apache/poi/ss/formula/eval/FunctionEval.java (revision 1368395)
+++ src/java/org/apache/poi/ss/formula/eval/FunctionEval.java (working copy)
@@ -117,6 +117,7 @@
retval[68] = CalendarFieldFunction.MONTH;
retval[69] = CalendarFieldFunction.YEAR;
+ retval[70] = WeekdayFunc.instance;
retval[71] = CalendarFieldFunction.HOUR;
retval[72] = CalendarFieldFunction.MINUTE;
retval[73] = CalendarFieldFunction.SECOND;
--- src/java/org/apache/poi/ss/formula/functions/Address.java (revision 1368395)
+++ src/java/org/apache/poi/ss/formula/functions/Address.java (working copy)
@@ -43,10 +43,10 @@
int col = (int)NumericFunction.singleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);
int refType;
- if(args.length > 2){
+ if (args.length > 2 && args[2] != MissingArgEval.instance) {
refType = (int)NumericFunction.singleOperandEvaluate(args[2], srcRowIndex, srcColumnIndex);
} else {
- refType = REF_ABSOLUTE;
+ refType = REF_ABSOLUTE; // this is also the default if parameter is not given
}
switch (refType){
case REF_ABSOLUTE:
--- src/java/org/apache/poi/ss/formula/functions/CalendarFieldFunction.java (revision 1368395)
+++ src/java/org/apache/poi/ss/formula/functions/CalendarFieldFunction.java (working copy)
@@ -18,8 +18,6 @@
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;
@@ -32,14 +30,17 @@
* 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);
+ public static final Function MINUTE = new CalendarFieldFunction(Calendar.MINUTE);
+ public static final Function SECOND = new CalendarFieldFunction(Calendar.SECOND);
private final int _dateFieldId;
@@ -64,7 +65,7 @@
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) {
+ if (((int)serialDate) == 0) {
switch (_dateFieldId) {
case Calendar.YEAR: return 1900;
case Calendar.MONTH: return 1;
@@ -74,10 +75,9 @@
}
// TODO Figure out if we're in 1900 or 1904
- Date d = DateUtil.getJavaDate(serialDate, false);
-
- Calendar c = new GregorianCalendar();
- c.setTime(d);
+ // 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);
int result = c.get(_dateFieldId);
// Month is a special case due to C semantics
--- src/java/org/apache/poi/ss/formula/functions/BooleanFunction.java (revision 1368395)
+++ src/java/org/apache/poi/ss/formula/functions/BooleanFunction.java (working copy)
@@ -20,6 +20,7 @@
import org.apache.poi.ss.formula.eval.BoolEval;
import org.apache.poi.ss.formula.eval.ErrorEval;
import org.apache.poi.ss.formula.eval.EvaluationException;
+import org.apache.poi.ss.formula.eval.MissingArgEval;
import org.apache.poi.ss.formula.eval.OperandResolver;
import org.apache.poi.ss.formula.eval.RefEval;
import org.apache.poi.ss.formula.eval.ValueEval;
@@ -81,6 +82,8 @@
if (arg instanceof RefEval) {
ValueEval ve = ((RefEval) arg).getInnerValueEval();
tempVe = OperandResolver.coerceValueToBoolean(ve, true);
+ } else if (arg == MissingArgEval.instance) {
+ tempVe = null; // you can leave out parameters, they are simply ignored
} else {
tempVe = OperandResolver.coerceValueToBoolean(arg, false);
}
--- src/documentation/content/xdocs/spreadsheet/eval-devguide.xml (revision 1368395)
+++ src/documentation/content/xdocs/spreadsheet/eval-devguide.xml (working copy)
@@ -88,11 +88,11 @@
As of Feb 2012, POI supports about 140 built-in functions,
see Appendix A for the full list.
- You can programmatically list supported / unsuported functions using trhe following helper methods:
+ You can programmatically list supported / unsuported functions using the following helper methods:
// list of functions that POI can evaluate
- Collection<String> suportedFuncs = WorkbookEvaluator.getSupportedFunctionNames();
+ Collection<String> supportedFuncs = WorkbookEvaluator.getSupportedFunctionNames();
// list of functions that are not supported by POI
Collection<String> unsupportedFuncs = WorkbookEvaluator.getNotSupportedFunctionNames();
@@ -366,6 +366,7 @@
VAR
VARP
VLOOKUP
+ WEEKDAY
WORKDAY
YEAR
YEARFRAC
--- src/testcases/org/apache/poi/ss/formula/functions/TestCalendarFieldFunction.java (revision 1368395)
+++ src/testcases/org/apache/poi/ss/formula/functions/TestCalendarFieldFunction.java (working copy)
@@ -59,6 +59,33 @@
confirm("SECOND(40627.4860417)", 54);
}
+ public void testRounding() {
+ // 41484.999994200 = 23:59:59,499
+ // 41484.9999942129 = 23:59:59,500 (but sub-milliseconds are below 0.5 (0.49999453965575), XLS-second results in 59)
+ // 41484.9999942130 = 23:59:59,500 (sub-milliseconds are 0.50000334065408, XLS-second results in 00)
+
+ confirm("DAY(41484.999994200)", 29);
+ confirm("SECOND(41484.999994200)", 59);
+
+ confirm("DAY(41484.9999942129)", 29);
+ confirm("HOUR(41484.9999942129)", 23);
+ confirm("MINUTE(41484.9999942129)", 59);
+ confirm("SECOND(41484.9999942129)", 59);
+
+ confirm("DAY(41484.9999942130)", 30);
+ confirm("HOUR(41484.9999942130)", 0);
+ confirm("MINUTE(41484.9999942130)", 0);
+ confirm("SECOND(41484.9999942130)", 0);
+ }
+
+ public void testDaylightSaving() {
+ confirm("HOUR(41364.08263888890000)", 1); // 31.03.2013 01:59:00,000
+ 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)
+ confirm("HOUR(41364.08402777780000)", 2); // 31.03.2013 02:01:00,000
+ confirm("HOUR(41364.12430555560000)", 2); // 31.03.2013 02:59:00,000
+ confirm("HOUR(41364.12500000000000)", 3); // 31.03.2013 03:00:00,000
+ }
+
public void testBugDate() {
confirm("YEAR(0.0)", 1900);
confirm("MONTH(0.0)", 1);
--- src/testcases/org/apache/poi/ss/formula/functions/TestAddress.java (revision 1368395)
+++ src/testcases/org/apache/poi/ss/formula/functions/TestAddress.java (working copy)
@@ -33,6 +33,9 @@
String formulaText = "ADDRESS(1,2)";
confirmResult(fe, cell, formulaText, "$B$1");
+ formulaText = "ADDRESS(1,2,)"; // with explicitly empty third parameter
+ confirmResult(fe, cell, formulaText, "$B$1");
+
formulaText = "ADDRESS(22,44)";
confirmResult(fe, cell, formulaText, "$AR$22");
--- patch.xml (revision 1368395)
+++ patch.xml (working copy)
@@ -50,11 +50,11 @@
-
+
-
+