ASF Bugzilla – Attachment 29155 Details for
Bug 53644
[PATCH] XLS formula bugfix (CalFieldFunc) + WeekDay addon
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
Remember
[x]
|
Forgot Password
Login:
[x]
[patch]
ant-generated patch text file
patch.txt (text/plain), 15.54 KB, created by
Thies Wellpott
on 2012-08-02 14:37:47 UTC
(
hide
)
Description:
ant-generated patch text file
Filename:
MIME Type:
Creator:
Thies Wellpott
Created:
2012-08-02 14:37:47 UTC
Size:
15.54 KB
patch
obsolete
>Index: src/java/org/apache/poi/ss/usermodel/DateUtil.java >=================================================================== >--- 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); >Index: src/java/org/apache/poi/ss/formula/eval/FunctionEval.java >=================================================================== >--- 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; >Index: src/java/org/apache/poi/ss/formula/functions/Address.java >=================================================================== >--- 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: >Index: src/java/org/apache/poi/ss/formula/functions/CalendarFieldFunction.java >=================================================================== >--- 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 >Index: src/java/org/apache/poi/ss/formula/functions/BooleanFunction.java >=================================================================== >--- 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); > } >Index: src/documentation/content/xdocs/spreadsheet/eval-devguide.xml >=================================================================== >--- src/documentation/content/xdocs/spreadsheet/eval-devguide.xml (revision 1368395) >+++ src/documentation/content/xdocs/spreadsheet/eval-devguide.xml (working copy) >@@ -88,11 +88,11 @@ > <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 trhe following helper methods: >+ You can programmatically list supported / unsuported functions using the following helper methods: > </p> > <source> > // 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 >Index: src/testcases/org/apache/poi/ss/formula/functions/TestCalendarFieldFunction.java >=================================================================== >--- 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); >Index: src/testcases/org/apache/poi/ss/formula/functions/TestAddress.java >=================================================================== >--- 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"); > >Index: patch.xml >=================================================================== >--- patch.xml (revision 1368395) >+++ patch.xml (working copy) >@@ -50,11 +50,11 @@ > <filterchain> > <!-- capture any new files --> > <linecontainsregexp> >- <regexp pattern="(\?|A)......"/> >+ <regexp pattern="^(\?|A)......"/> > </linecontainsregexp> > <!-- filter out the first six characters --> > <tokenfilter> >- <replaceregex pattern="(.......)" replace=""/> >+ <replaceregex pattern="^(.......)" replace=""/> > </tokenfilter> > <!--remove line breaks --> > <striplinebreaks/>
You cannot view the attachment while viewing its details because your browser does not support IFRAMEs.
View the attachment on a separate page
.
View Attachment As Diff
View Attachment As Raw
Actions:
View
|
Diff
Attachments on
bug 53644
: 29155 |
29156
|
29333