From 2ea7bc5eef9b7ec2ab26263e091f9cdb77aac699 Mon Sep 17 00:00:00 2001 From: Josh Micich Date: Mon, 25 Aug 2008 08:09:02 +0000 Subject: [PATCH] Initial support for evaluating external add-in functions like YEARFRAC git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@688650 13f79535-47bb-0310-9956-ffa450edef68 --- src/documentation/content/xdocs/changes.xml | 1 + src/documentation/content/xdocs/status.xml | 1 + .../apache/poi/hssf/record/SupBookRecord.java | 1 + .../poi/hssf/record/formula/NameXPtg.java | 13 +- .../record/formula/atp/AnalysisToolPak.java | 154 ++++++++ .../poi/hssf/record/formula/atp/YearFrac.java | 160 ++++++++ .../formula/atp/YearFracCalculator.java | 344 ++++++++++++++++++ .../record/formula/eval/ExternalFunction.java | 50 ++- .../hssf/record/formula/eval/NameXEval.java | 49 +++ .../poi/hssf/usermodel/HSSFDateUtil.java | 15 +- .../hssf/usermodel/HSSFFormulaEvaluator.java | 101 ++--- .../apache/poi/hssf/data/yearfracExamples.xls | Bin 0 -> 29184 bytes .../formula/TestExternalFunctionFormulas.java | 2 +- .../formula/atp/TestYearFracCalculator.java | 66 ++++ ...TestYearFracCalculatorFromSpreadsheet.java | 178 +++++++++ .../poi/hssf/usermodel/TestHSSFDateUtil.java | 115 +++--- 16 files changed, 1124 insertions(+), 126 deletions(-) create mode 100644 src/java/org/apache/poi/hssf/record/formula/atp/AnalysisToolPak.java create mode 100644 src/java/org/apache/poi/hssf/record/formula/atp/YearFrac.java create mode 100644 src/java/org/apache/poi/hssf/record/formula/atp/YearFracCalculator.java create mode 100644 src/java/org/apache/poi/hssf/record/formula/eval/NameXEval.java create mode 100644 src/testcases/org/apache/poi/hssf/data/yearfracExamples.xls create mode 100644 src/testcases/org/apache/poi/hssf/record/formula/atp/TestYearFracCalculator.java create mode 100644 src/testcases/org/apache/poi/hssf/record/formula/atp/TestYearFracCalculatorFromSpreadsheet.java diff --git a/src/documentation/content/xdocs/changes.xml b/src/documentation/content/xdocs/changes.xml index 26a9c72109..f58cf5430a 100644 --- a/src/documentation/content/xdocs/changes.xml +++ b/src/documentation/content/xdocs/changes.xml @@ -37,6 +37,7 @@ + Initial support for evaluating external add-in functions like YEARFRAC 45672 - Fix for MissingRecordAwareHSSFListener to prevent multiple LastCellOfRowDummyRecords when shared formulas are present 45645 - Fix for HSSFSheet.autoSizeColumn() for widths exceeding Short.MAX_VALUE 45623 - Support for additional HSSF header and footer fields, including bold and full file path diff --git a/src/documentation/content/xdocs/status.xml b/src/documentation/content/xdocs/status.xml index 1b086fddc7..a3548f5ece 100644 --- a/src/documentation/content/xdocs/status.xml +++ b/src/documentation/content/xdocs/status.xml @@ -34,6 +34,7 @@ + Initial support for evaluating external add-in functions like YEARFRAC 45672 - Fix for MissingRecordAwareHSSFListener to prevent multiple LastCellOfRowDummyRecords when shared formulas are present 45645 - Fix for HSSFSheet.autoSizeColumn() for widths exceeding Short.MAX_VALUE 45623 - Support for additional HSSF header and footer fields, including bold and full file path diff --git a/src/java/org/apache/poi/hssf/record/SupBookRecord.java b/src/java/org/apache/poi/hssf/record/SupBookRecord.java index cb4eff8406..c75e2db894 100644 --- a/src/java/org/apache/poi/hssf/record/SupBookRecord.java +++ b/src/java/org/apache/poi/hssf/record/SupBookRecord.java @@ -150,6 +150,7 @@ public final class SupBookRecord extends Record { sb.append("Internal References "); sb.append(" nSheets= ").append(field_1_number_of_sheets); } + sb.append("]"); return sb.toString(); } private int getDataSize() { diff --git a/src/java/org/apache/poi/hssf/record/formula/NameXPtg.java b/src/java/org/apache/poi/hssf/record/formula/NameXPtg.java index 98ab39f05b..45a75fb2a0 100644 --- a/src/java/org/apache/poi/hssf/record/formula/NameXPtg.java +++ b/src/java/org/apache/poi/hssf/record/formula/NameXPtg.java @@ -30,11 +30,11 @@ public final class NameXPtg extends OperandPtg { private final static int SIZE = 7; /** index to REF entry in externsheet record */ - private int _sheetRefIndex; + private final int _sheetRefIndex; /** index to defined name or externname table(1 based) */ - private int _nameNumber; + private final int _nameNumber; /** reserved must be 0 */ - private int _reserved; + private final int _reserved; private NameXPtg(int sheetRefIndex, int nameNumber, int reserved) { _sheetRefIndex = sheetRefIndex; @@ -73,4 +73,11 @@ public final class NameXPtg extends OperandPtg { public byte getDefaultOperandClass() { return Ptg.CLASS_VALUE; } + + public int getSheetRefIndex() { + return _sheetRefIndex; + } + public int getNameIndex() { + return _nameNumber - 1; + } } diff --git a/src/java/org/apache/poi/hssf/record/formula/atp/AnalysisToolPak.java b/src/java/org/apache/poi/hssf/record/formula/atp/AnalysisToolPak.java new file mode 100644 index 0000000000..60f06695d0 --- /dev/null +++ b/src/java/org/apache/poi/hssf/record/formula/atp/AnalysisToolPak.java @@ -0,0 +1,154 @@ +/* ==================================================================== + Licensed to the Apache Software Foundation (ASF) under one or more + contributor license agreements. See the NOTICE file distributed with + this work for additional information regarding copyright ownership. + The ASF licenses this file to You under the Apache License, Version 2.0 + (the "License"); you may not use this file except in compliance with + the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. +==================================================================== */ + +package org.apache.poi.hssf.record.formula.atp; + +import java.util.HashMap; +import java.util.Map; + +import org.apache.poi.hssf.record.formula.eval.ErrorEval; +import org.apache.poi.hssf.record.formula.eval.Eval; +import org.apache.poi.hssf.record.formula.eval.ValueEval; +import org.apache.poi.hssf.record.formula.functions.FreeRefFunction; +import org.apache.poi.hssf.usermodel.HSSFSheet; +import org.apache.poi.hssf.usermodel.HSSFWorkbook; + +public final class AnalysisToolPak { + + private static final FreeRefFunction NotImplemented = new FreeRefFunction() { + + public ValueEval evaluate(Eval[] args, int srcCellRow, short srcCellCol, + HSSFWorkbook workbook, HSSFSheet sheet) { + return ErrorEval.FUNCTION_NOT_IMPLEMENTED; + } + }; + + private static Map _functionsByName = createFunctionsMap(); + + private AnalysisToolPak() { + // no instances of this class + } + + public static FreeRefFunction findFunction(String name) { + return (FreeRefFunction)_functionsByName.get(name); + } + + private static Map createFunctionsMap() { + Map m = new HashMap(100); + + r(m, "ACCRINT", null); + r(m, "ACCRINTM", null); + r(m, "AMORDEGRC", null); + r(m, "AMORLINC", null); + r(m, "BESSELI", null); + r(m, "BESSELJ", null); + r(m, "BESSELK", null); + r(m, "BESSELY", null); + r(m, "BIN2DEC", null); + r(m, "BIN2HEX", null); + r(m, "BIN2OCT", null); + r(m, "CO MPLEX", null); + r(m, "CONVERT", null); + r(m, "COUPDAYBS", null); + r(m, "COUPDAYS", null); + r(m, "COUPDAYSNC", null); + r(m, "COUPNCD", null); + r(m, "COUPNUM", null); + r(m, "COUPPCD", null); + r(m, "CUMIPMT", null); + r(m, "CUMPRINC", null); + r(m, "DEC2BIN", null); + r(m, "DEC2HEX", null); + r(m, "DEC2OCT", null); + r(m, "DELTA", null); + r(m, "DISC", null); + r(m, "DOLLARDE", null); + r(m, "DOLLARFR", null); + r(m, "DURATION", null); + r(m, "EDATE", null); + r(m, "EFFECT", null); + r(m, "EOMONTH", null); + r(m, "ERF", null); + r(m, "ERFC", null); + r(m, "FACTDOUBLE", null); + r(m, "FVSCHEDULE", null); + r(m, "GCD", null); + r(m, "GESTEP", null); + r(m, "HEX2BIN", null); + r(m, "HEX2DEC", null); + r(m, "HEX2OCT", null); + r(m, "IMABS", null); + r(m, "IMAGINARY", null); + r(m, "IMARGUMENT", null); + r(m, "IMCONJUGATE", null); + r(m, "IMCOS", null); + r(m, "IMDIV", null); + r(m, "IMEXP", null); + r(m, "IMLN", null); + r(m, "IMLOG10", null); + r(m, "IMLOG2", null); + r(m, "IMPOWER", null); + r(m, "IMPRODUCT", null); + r(m, "IMREAL", null); + r(m, "IMSIN", null); + r(m, "IMSQRT", null); + r(m, "IMSUB", null); + r(m, "IMSUM", null); + r(m, "INTRATE", null); + r(m, "ISEVEN", null); + r(m, "ISODD", null); + r(m, "LCM", null); + r(m, "MDURATION", null); + r(m, "MROUND", null); + r(m, "MULTINOMIAL", null); + r(m, "NETWORKDAYS", null); + r(m, "NOMINAL", null); + r(m, "OCT2BIN", null); + r(m, "OCT2DEC", null); + r(m, "OCT2HEX", null); + r(m, "ODDFPRICE", null); + r(m, "ODDFYIELD", null); + r(m, "ODDLPRICE", null); + r(m, "ODDLYIELD", null); + r(m, "PRICE", null); + r(m, "PRICEDISC", null); + r(m, "PRICEMAT", null); + r(m, "QUOTIENT", null); + r(m, "RAND BETWEEN", null); + r(m, "RECEIVED", null); + r(m, "SERIESSUM", null); + r(m, "SQRTPI", null); + r(m, "TBILLEQ", null); + r(m, "TBILLPRICE", null); + r(m, "TBILLYIELD", null); + r(m, "WEEKNUM", null); + r(m, "WORKDAY", null); + r(m, "XIRR", null); + r(m, "XNPV", null); + r(m, "YEARFRAC", YearFrac.instance); + r(m, "YIELD", null); + r(m, "YIELDDISC", null); + r(m, "YIELDMAT", null); + + return m; + } + + private static void r(Map m, String functionName, FreeRefFunction pFunc) { + FreeRefFunction func = pFunc == null ? NotImplemented : pFunc; + m.put(functionName, func); + } +} diff --git a/src/java/org/apache/poi/hssf/record/formula/atp/YearFrac.java b/src/java/org/apache/poi/hssf/record/formula/atp/YearFrac.java new file mode 100644 index 0000000000..5861871920 --- /dev/null +++ b/src/java/org/apache/poi/hssf/record/formula/atp/YearFrac.java @@ -0,0 +1,160 @@ +/* ==================================================================== + Licensed to the Apache Software Foundation (ASF) under one or more + contributor license agreements. See the NOTICE file distributed with + this work for additional information regarding copyright ownership. + The ASF licenses this file to You under the Apache License, Version 2.0 + (the "License"); you may not use this file except in compliance with + the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. +==================================================================== */ + +package org.apache.poi.hssf.record.formula.atp; + +import java.util.Calendar; +import java.util.GregorianCalendar; +import java.util.regex.Pattern; + +import org.apache.poi.hssf.record.formula.eval.ErrorEval; +import org.apache.poi.hssf.record.formula.eval.Eval; +import org.apache.poi.hssf.record.formula.eval.EvaluationException; +import org.apache.poi.hssf.record.formula.eval.NumberEval; +import org.apache.poi.hssf.record.formula.eval.OperandResolver; +import org.apache.poi.hssf.record.formula.eval.StringEval; +import org.apache.poi.hssf.record.formula.eval.ValueEval; +import org.apache.poi.hssf.record.formula.functions.FreeRefFunction; +import org.apache.poi.hssf.usermodel.HSSFDateUtil; +import org.apache.poi.hssf.usermodel.HSSFSheet; +import org.apache.poi.hssf.usermodel.HSSFWorkbook; +/** + * Implementation of Excel 'Analysis ToolPak' function YEARFRAC()
+ * + * Returns the fraction of the year spanned by two dates.

+ * + * Syntax
+ * YEARFRAC(startDate, endDate, basis)

+ * + * The basis optionally specifies the behaviour of YEARFRAC as follows: + * + * + * + * + * + * + * + * + *
ValueDays per MonthDays per Year
0 (default)30360
1actualactual
2actual360
3actual365
430360
+ * + */ +final class YearFrac implements FreeRefFunction { + + public static final FreeRefFunction instance = new YearFrac(); + + private YearFrac() { + // enforce singleton + } + + public ValueEval evaluate(Eval[] args, int srcCellRow, short srcCellCol, HSSFWorkbook workbook, + HSSFSheet sheet) { + + double result; + try { + int basis = 0; // default + switch(args.length) { + case 3: + basis = evaluateIntArg(args[2], srcCellRow, srcCellCol); + case 2: + break; + default: + return ErrorEval.VALUE_INVALID; + } + double startDateVal = evaluateDateArg(args[0], srcCellRow, srcCellCol); + double endDateVal = evaluateDateArg(args[1], srcCellRow, srcCellCol); + result = YearFracCalculator.calculate(startDateVal, endDateVal, basis); + } catch (EvaluationException e) { + return e.getErrorEval(); + } + + return new NumberEval(result); + } + + private static double evaluateDateArg(Eval arg, int srcCellRow, short srcCellCol) throws EvaluationException { + ValueEval ve = OperandResolver.getSingleValue(arg, srcCellRow, srcCellCol); + + if (ve instanceof StringEval) { + String strVal = ((StringEval) ve).getStringValue(); + Double dVal = OperandResolver.parseDouble(strVal); + if (dVal != null) { + return dVal.doubleValue(); + } + Calendar date = parseDate(strVal); + return HSSFDateUtil.getExcelDate(date, false); + } + return OperandResolver.coerceValueToDouble(ve); + } + + private static Calendar parseDate(String strVal) throws EvaluationException { + String[] parts = Pattern.compile("/").split(strVal); + if (parts.length != 3) { + throw new EvaluationException(ErrorEval.VALUE_INVALID); + } + String part2 = parts[2]; + int spacePos = part2.indexOf(' '); + if (spacePos > 0) { + // drop time portion if present + part2 = part2.substring(0, spacePos); + } + int f0; + int f1; + int f2; + try { + f0 = Integer.parseInt(parts[0]); + f1 = Integer.parseInt(parts[1]); + f2 = Integer.parseInt(part2); + } catch (NumberFormatException e) { + throw new EvaluationException(ErrorEval.VALUE_INVALID); + } + if (f0<0 || f1<0 || f2<0 || f0>12 || f1>12 || f2>12) { + // easy to see this cannot be a valid date + throw new EvaluationException(ErrorEval.VALUE_INVALID); + } + + if (f0 >= 1900 && f0 < 9999) { + // when 4 digit value appears first, the format is YYYY/MM/DD, regardless of OS settings + return makeDate(f0, f1, f2); + } + // otherwise the format seems to depend on OS settings (default date format) + if (false) { + // MM/DD/YYYY is probably a good guess, if the in the US + return makeDate(f2, f0, f1); + } + // TODO - find a way to choose the correct date format + throw new RuntimeException("Unable to determine date format for text '" + strVal + "'"); + } + + /** + * @param month 1-based + */ + private static Calendar makeDate(int year, int month, int day) throws EvaluationException { + if (month < 1 || month > 12) { + throw new EvaluationException(ErrorEval.VALUE_INVALID); + } + Calendar cal = new GregorianCalendar(year, month-1, 1, 0, 0, 0); + cal.set(Calendar.MILLISECOND, 0); + if (day <1 || day>cal.getActualMaximum(Calendar.DAY_OF_MONTH)) { + throw new EvaluationException(ErrorEval.VALUE_INVALID); + } + return cal; + } + + private static int evaluateIntArg(Eval arg, int srcCellRow, short srcCellCol) throws EvaluationException { + ValueEval ve = OperandResolver.getSingleValue(arg, srcCellRow, srcCellCol); + return OperandResolver.coerceValueToInt(ve); + } +} diff --git a/src/java/org/apache/poi/hssf/record/formula/atp/YearFracCalculator.java b/src/java/org/apache/poi/hssf/record/formula/atp/YearFracCalculator.java new file mode 100644 index 0000000000..40a5eb8dbd --- /dev/null +++ b/src/java/org/apache/poi/hssf/record/formula/atp/YearFracCalculator.java @@ -0,0 +1,344 @@ +/* ==================================================================== + Licensed to the Apache Software Foundation (ASF) under one or more + contributor license agreements. See the NOTICE file distributed with + this work for additional information regarding copyright ownership. + The ASF licenses this file to You under the Apache License, Version 2.0 + (the "License"); you may not use this file except in compliance with + the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. +==================================================================== */ + +package org.apache.poi.hssf.record.formula.atp; + +import java.util.Calendar; +import java.util.GregorianCalendar; +import java.util.TimeZone; + +import org.apache.poi.hssf.record.formula.eval.ErrorEval; +import org.apache.poi.hssf.record.formula.eval.EvaluationException; +import org.apache.poi.hssf.usermodel.HSSFDateUtil; + + +/** + * Internal calculation methods for Excel 'Analysis ToolPak' function YEARFRAC()
+ * + * Algorithm inspired by www.dwheeler.com/yearfrac + * + * @author Josh Micich + */ +final class YearFracCalculator { + /** use UTC time-zone to avoid daylight savings issues */ + private static final TimeZone UTC_TIME_ZONE = TimeZone.getTimeZone("UTC"); + private static final int MS_PER_HOUR = 60 * 60 * 1000; + private static final int MS_PER_DAY = 24 * MS_PER_HOUR; + private static final int DAYS_PER_NORMAL_YEAR = 365; + private static final int DAYS_PER_LEAP_YEAR = DAYS_PER_NORMAL_YEAR + 1; + + /** the length of normal long months i.e. 31 */ + private static final int LONG_MONTH_LEN = 31; + /** the length of normal short months i.e. 30 */ + private static final int SHORT_MONTH_LEN = 30; + private static final int SHORT_FEB_LEN = 28; + private static final int LONG_FEB_LEN = SHORT_FEB_LEN + 1; + + private YearFracCalculator() { + // no instances of this class + } + + + public static double calculate(double pStartDateVal, double pEndDateVal, int basis) throws EvaluationException { + + if (basis < 0 || basis >= 5) { + // if basis is invalid the result is #NUM! + throw new EvaluationException(ErrorEval.NUM_ERROR); + } + + // common logic for all bases + + // truncate day values + int startDateVal = (int) Math.floor(pStartDateVal); + int endDateVal = (int) Math.floor(pEndDateVal); + if (startDateVal == endDateVal) { + // when dates are equal, result is zero + return 0; + } + // swap start and end if out of order + if (startDateVal > endDateVal) { + int temp = startDateVal; + startDateVal = endDateVal; + endDateVal = temp; + } + + switch (basis) { + case 0: return basis0(startDateVal, endDateVal); + case 1: return basis1(startDateVal, endDateVal); + case 2: return basis2(startDateVal, endDateVal); + case 3: return basis3(startDateVal, endDateVal); + case 4: return basis4(startDateVal, endDateVal); + } + throw new IllegalStateException("cannot happen"); + } + + + /** + * @param startDateVal assumed to be less than or equal to endDateVal + * @param endDateVal assumed to be greater than or equal to startDateVal + */ + public static double basis0(int startDateVal, int endDateVal) { + SimpleDate startDate = createDate(startDateVal); + SimpleDate endDate = createDate(endDateVal); + int date1day = startDate.day; + int date2day = endDate.day; + + // basis zero has funny adjustments to the day-of-month fields when at end-of-month + if (date1day == LONG_MONTH_LEN && date2day == LONG_MONTH_LEN) { + date1day = SHORT_MONTH_LEN; + date2day = SHORT_MONTH_LEN; + } else if (date1day == LONG_MONTH_LEN) { + date1day = SHORT_MONTH_LEN; + } else if (date1day == SHORT_MONTH_LEN && date2day == LONG_MONTH_LEN) { + date2day = SHORT_MONTH_LEN; + // Note: If date2day==31, it STAYS 31 if date1day < 30. + // Special fixes for February: + } else if (startDate.month == 2 && isLastDayOfMonth(startDate)) { + // Note - these assignments deliberately set Feb 30 date. + date1day = SHORT_MONTH_LEN; + if (endDate.month == 2 && isLastDayOfMonth(endDate)) { + // only adjusted when first date is last day in Feb + date2day = SHORT_MONTH_LEN; + } + } + return calculateAdjusted(startDate, endDate, date1day, date2day); + } + /** + * @param startDateVal assumed to be less than or equal to endDateVal + * @param endDateVal assumed to be greater than or equal to startDateVal + */ + public static double basis1(int startDateVal, int endDateVal) { + SimpleDate startDate = createDate(startDateVal); + SimpleDate endDate = createDate(endDateVal); + double yearLength; + if (isGreaterThanOneYear(startDate, endDate)) { + yearLength = averageYearLength(startDate.year, endDate.year); + } else if (shouldCountFeb29(startDate, endDate)) { + yearLength = DAYS_PER_LEAP_YEAR; + } else { + yearLength = DAYS_PER_NORMAL_YEAR; + } + return dateDiff(startDate.tsMilliseconds, endDate.tsMilliseconds) / yearLength; + } + + /** + * @param startDateVal assumed to be less than or equal to endDateVal + * @param endDateVal assumed to be greater than or equal to startDateVal + */ + public static double basis2(int startDateVal, int endDateVal) { + return (endDateVal - startDateVal) / 360.0; + } + /** + * @param startDateVal assumed to be less than or equal to endDateVal + * @param endDateVal assumed to be greater than or equal to startDateVal + */ + public static double basis3(double startDateVal, double endDateVal) { + return (endDateVal - startDateVal) / 365.0; + } + /** + * @param startDateVal assumed to be less than or equal to endDateVal + * @param endDateVal assumed to be greater than or equal to startDateVal + */ + public static double basis4(int startDateVal, int endDateVal) { + SimpleDate startDate = createDate(startDateVal); + SimpleDate endDate = createDate(endDateVal); + int date1day = startDate.day; + int date2day = endDate.day; + + + // basis four has funny adjustments to the day-of-month fields when at end-of-month + if (date1day == LONG_MONTH_LEN) { + date1day = SHORT_MONTH_LEN; + } + if (date2day == LONG_MONTH_LEN) { + date2day = SHORT_MONTH_LEN; + } + // Note - no adjustments for end of Feb + return calculateAdjusted(startDate, endDate, date1day, date2day); + } + + + private static double calculateAdjusted(SimpleDate startDate, SimpleDate endDate, int date1day, + int date2day) { + double dayCount + = (endDate.year - startDate.year) * 360 + + (endDate.month - startDate.month) * SHORT_MONTH_LEN + + (date2day - date1day) * 1; + return dayCount / 360; + } + + private static boolean isLastDayOfMonth(SimpleDate date) { + if (date.day < SHORT_FEB_LEN) { + return false; + } + return date.day == getLastDayOfMonth(date); + } + + private static int getLastDayOfMonth(SimpleDate date) { + switch (date.month) { + case 1: + case 3: + case 5: + case 7: + case 8: + case 10: + case 12: + return LONG_MONTH_LEN; + case 4: + case 6: + case 9: + case 11: + return SHORT_MONTH_LEN; + } + if (isLeapYear(date.year)) { + return LONG_FEB_LEN; + } + return SHORT_FEB_LEN; + } + + /** + * Assumes dates are no more than 1 year apart. + * @return true if dates both within a leap year, or span a period including Feb 29 + */ + private static boolean shouldCountFeb29(SimpleDate start, SimpleDate end) { + boolean startIsLeapYear = isLeapYear(start.year); + if (startIsLeapYear && start.year == end.year) { + // note - dates may not actually span Feb-29, but it gets counted anyway in this case + return true; + } + + boolean endIsLeapYear = isLeapYear(end.year); + if (!startIsLeapYear && !endIsLeapYear) { + return false; + } + if (startIsLeapYear) { + switch (start.month) { + case SimpleDate.JANUARY: + case SimpleDate.FEBRUARY: + return true; + } + return false; + } + if (endIsLeapYear) { + switch (end.month) { + case SimpleDate.JANUARY: + return false; + case SimpleDate.FEBRUARY: + break; + default: + return true; + } + return end.day == LONG_FEB_LEN; + } + return false; + } + + /** + * @return the whole number of days between the two time-stamps. Both time-stamps are + * assumed to represent 12:00 midnight on the respective day. + */ + private static int dateDiff(long startDateMS, long endDateMS) { + long msDiff = endDateMS - startDateMS; + + // some extra checks to make sure we don't hide some other bug with the rounding + int remainderHours = (int) ((msDiff % MS_PER_DAY) / MS_PER_HOUR); + switch (remainderHours) { + case 0: // normal case + break; + case 1: // transition from normal time to daylight savings adjusted + case 23: // transition from daylight savings adjusted to normal time + // Unexpected since we are using UTC_TIME_ZONE + default: + throw new RuntimeException("Unexpected date diff between " + startDateMS + " and " + endDateMS); + + } + return (int) (0.5 + ((double)msDiff / MS_PER_DAY)); + } + + private static double averageYearLength(int startYear, int endYear) { + int dayCount = 0; + for (int i=startYear; i<=endYear; i++) { + dayCount += DAYS_PER_NORMAL_YEAR; + if (isLeapYear(i)) { + dayCount++; + } + } + double numberOfYears = endYear-startYear+1; + return dayCount / numberOfYears; + } + + private static boolean isLeapYear(int i) { + // leap years are always divisible by 4 + if (i % 4 != 0) { + return false; + } + // each 4th century is a leap year + if (i % 400 == 0) { + return true; + } + // all other centuries are *not* leap years + if (i % 100 == 0) { + return false; + } + return true; + } + + private static boolean isGreaterThanOneYear(SimpleDate start, SimpleDate end) { + if (start.year == end.year) { + return false; + } + if (start.year + 1 != end.year) { + return true; + } + + if (start.month > end.month) { + return false; + } + if (start.month < end.month) { + return true; + } + + return start.day < end.day; + } + + private static SimpleDate createDate(int dayCount) { + GregorianCalendar calendar = new GregorianCalendar(UTC_TIME_ZONE); + HSSFDateUtil.setCalendar(calendar, dayCount, 0, false); + return new SimpleDate(calendar); + } + + private static final class SimpleDate { + + public static final int JANUARY = 1; + public static final int FEBRUARY = 2; + + public final int year; + /** 1-based month */ + public final int month; + /** day of month */ + public final int day; + /** milliseconds since 1970 */ + public long tsMilliseconds; + + public SimpleDate(Calendar cal) { + year = cal.get(Calendar.YEAR); + month = cal.get(Calendar.MONTH) + 1; + day = cal.get(Calendar.DAY_OF_MONTH); + tsMilliseconds = cal.getTimeInMillis(); + } + } +} diff --git a/src/java/org/apache/poi/hssf/record/formula/eval/ExternalFunction.java b/src/java/org/apache/poi/hssf/record/formula/eval/ExternalFunction.java index b1d81e6524..6959d146f9 100755 --- a/src/java/org/apache/poi/hssf/record/formula/eval/ExternalFunction.java +++ b/src/java/org/apache/poi/hssf/record/formula/eval/ExternalFunction.java @@ -17,14 +17,16 @@ package org.apache.poi.hssf.record.formula.eval; +import org.apache.poi.hssf.record.formula.atp.AnalysisToolPak; import org.apache.poi.hssf.record.formula.functions.FreeRefFunction; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; /** * - * Common entry point for all external functions (where + * Common entry point for all user-defined (non-built-in) functions (where * AbstractFunctionPtg.field_2_fnc_index == 255) * + * TODO rename to UserDefinedFunction * @author Josh Micich */ final class ExternalFunction implements FreeRefFunction { @@ -36,27 +38,43 @@ final class ExternalFunction implements FreeRefFunction { throw new RuntimeException("function name argument missing"); } - if (!(args[0] instanceof NameEval)) { - throw new RuntimeException("First argument should be a NameEval, but got (" - + args[0].getClass().getName() + ")"); - } - NameEval functionNameEval = (NameEval) args[0]; - - int nOutGoingArgs = nIncomingArgs -1; - Eval[] outGoingArgs = new Eval[nOutGoingArgs]; - System.arraycopy(args, 1, outGoingArgs, 0, nOutGoingArgs); - + Eval nameArg = args[0]; FreeRefFunction targetFunc; try { - targetFunc = findTargetFunction(workbook, functionNameEval); + if (nameArg instanceof NameEval) { + targetFunc = findInternalUserDefinedFunction(workbook, (NameEval) nameArg); + } else if (nameArg instanceof NameXEval) { + targetFunc = findExternalUserDefinedFunction(workbook, (NameXEval) nameArg); + } else { + throw new RuntimeException("First argument should be a NameEval, but got (" + + nameArg.getClass().getName() + ")"); + } } catch (EvaluationException e) { return e.getErrorEval(); } - + int nOutGoingArgs = nIncomingArgs -1; + Eval[] outGoingArgs = new Eval[nOutGoingArgs]; + System.arraycopy(args, 1, outGoingArgs, 0, nOutGoingArgs); return targetFunc.evaluate(outGoingArgs, srcCellRow, srcCellCol, workbook, sheet); } - private FreeRefFunction findTargetFunction(HSSFWorkbook workbook, NameEval functionNameEval) throws EvaluationException { + private FreeRefFunction findExternalUserDefinedFunction(HSSFWorkbook workbook, + NameXEval n) throws EvaluationException { + String functionName = workbook.resolveNameXText(n.getSheetRefIndex(), n.getNameNumber()); + + if(false) { + System.out.println("received call to external user defined function (" + functionName + ")"); + } + // currently only looking for functions from the 'Analysis TookPak' + // not sure how much this logic would need to change to support other or multiple add-ins. + FreeRefFunction result = AnalysisToolPak.findFunction(functionName); + if (result != null) { + return result; + } + throw new EvaluationException(ErrorEval.FUNCTION_NOT_IMPLEMENTED); + } + + private FreeRefFunction findInternalUserDefinedFunction(HSSFWorkbook workbook, NameEval functionNameEval) throws EvaluationException { int numberOfNames = workbook.getNumberOfNames(); @@ -68,7 +86,7 @@ final class ExternalFunction implements FreeRefFunction { String functionName = workbook.getNameName(nameIndex); if(false) { - System.out.println("received call to external function index (" + functionName + ")"); + System.out.println("received call to internal user defined function (" + functionName + ")"); } // TODO - detect if the NameRecord corresponds to a named range, function, or something undefined // throw the right errors in these cases @@ -77,5 +95,5 @@ final class ExternalFunction implements FreeRefFunction { throw new EvaluationException(ErrorEval.FUNCTION_NOT_IMPLEMENTED); } - } + diff --git a/src/java/org/apache/poi/hssf/record/formula/eval/NameXEval.java b/src/java/org/apache/poi/hssf/record/formula/eval/NameXEval.java new file mode 100644 index 0000000000..12b6be3805 --- /dev/null +++ b/src/java/org/apache/poi/hssf/record/formula/eval/NameXEval.java @@ -0,0 +1,49 @@ +/* ==================================================================== + Licensed to the Apache Software Foundation (ASF) under one or more + contributor license agreements. See the NOTICE file distributed with + this work for additional information regarding copyright ownership. + The ASF licenses this file to You under the Apache License, Version 2.0 + (the "License"); you may not use this file except in compliance with + the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. +==================================================================== */ + +package org.apache.poi.hssf.record.formula.eval; + +/** + * @author Josh Micich + */ +public final class NameXEval implements Eval { + + /** index to REF entry in externsheet record */ + private final int _sheetRefIndex; + /** index to defined name or externname table(1 based) */ + private final int _nameNumber; + + public NameXEval(int sheetRefIndex, int nameNumber) { + _sheetRefIndex = sheetRefIndex; + _nameNumber = nameNumber; + } + + public int getSheetRefIndex() { + return _sheetRefIndex; + } + public int getNameNumber() { + return _nameNumber; + } + + public String toString() { + StringBuffer sb = new StringBuffer(64); + sb.append(getClass().getName()).append(" ["); + sb.append(_sheetRefIndex).append(", ").append(_nameNumber); + sb.append("]"); + return sb.toString(); + } +} diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFDateUtil.java b/src/java/org/apache/poi/hssf/usermodel/HSSFDateUtil.java index 457f0b9ed1..7f45be23b4 100644 --- a/src/java/org/apache/poi/hssf/usermodel/HSSFDateUtil.java +++ b/src/java/org/apache/poi/hssf/usermodel/HSSFDateUtil.java @@ -158,9 +158,16 @@ public final class HSSFDateUtil { if (!isValidExcelDate(date)) { return null; } + int wholeDays = (int)Math.floor(date); + int millisecondsInDay = (int)((date - wholeDays) * DAY_MILLISECONDS + 0.5); + Calendar calendar = new GregorianCalendar(); // using default time-zone + setCalendar(calendar, wholeDays, millisecondsInDay, use1904windowing); + return calendar.getTime(); + } + public static void setCalendar(Calendar calendar, int wholeDays, int millisecondsInDay, + boolean use1904windowing) { int startYear = 1900; int dayAdjust = -1; // Excel thinks 2/29/1900 is a valid date, which it isn't - int wholeDays = (int)Math.floor(date); if (use1904windowing) { startYear = 1904; dayAdjust = 1; // 1904 date windowing uses 1/2/1904 as the first day @@ -170,12 +177,8 @@ public final class HSSFDateUtil { // If Excel date == 2/29/1900, will become 3/1/1900 in Java representation dayAdjust = 0; } - GregorianCalendar calendar = new GregorianCalendar(startYear,0, - wholeDays + dayAdjust); - int millisecondsInDay = (int)((date - Math.floor(date)) * - DAY_MILLISECONDS + 0.5); + calendar.set(startYear,0, wholeDays + dayAdjust, 0, 0, 0); calendar.set(GregorianCalendar.MILLISECOND, millisecondsInDay); - return calendar.getTime(); } /** diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java b/src/java/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java index fac66b2cc5..df38237fd4 100644 --- a/src/java/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java +++ b/src/java/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java @@ -51,6 +51,7 @@ import org.apache.poi.hssf.record.formula.eval.ErrorEval; import org.apache.poi.hssf.record.formula.eval.Eval; import org.apache.poi.hssf.record.formula.eval.FunctionEval; import org.apache.poi.hssf.record.formula.eval.NameEval; +import org.apache.poi.hssf.record.formula.eval.NameXEval; import org.apache.poi.hssf.record.formula.eval.NumberEval; import org.apache.poi.hssf.record.formula.eval.OperationEval; import org.apache.poi.hssf.record.formula.eval.Ref2DEval; @@ -61,10 +62,10 @@ import org.apache.poi.hssf.record.formula.eval.ValueEval; /** * @author Amol S. Deshmukh < amolweb at ya hoo dot com > - * + * */ public class HSSFFormulaEvaluator { - + // params to lookup the right constructor using reflection private static final Class[] VALUE_CONTRUCTOR_CLASS_ARRAY = new Class[] { Ptg.class }; @@ -78,8 +79,8 @@ public class HSSFFormulaEvaluator { private static final Map VALUE_EVALS_MAP = new HashMap(); /* - * Following is the mapping between the Ptg tokens returned - * by the FormulaParser and the *Eval classes that are used + * Following is the mapping between the Ptg tokens returned + * by the FormulaParser and the *Eval classes that are used * by the FormulaEvaluator */ static { @@ -90,15 +91,15 @@ public class HSSFFormulaEvaluator { } - + protected HSSFSheet _sheet; protected HSSFWorkbook _workbook; - + public HSSFFormulaEvaluator(HSSFSheet sheet, HSSFWorkbook workbook) { _sheet = sheet; _workbook = workbook; } - + /** * Does nothing * @deprecated - not needed, since the current row can be derived from the cell @@ -107,24 +108,24 @@ public class HSSFFormulaEvaluator { // do nothing } - + /** * Returns an underlying FormulaParser, for the specified * Formula String and HSSFWorkbook. * This will allow you to generate the Ptgs yourself, if * your needs are more complex than just having the - * formula evaluated. + * formula evaluated. */ public static FormulaParser getUnderlyingParser(HSSFWorkbook workbook, String formula) { return new FormulaParser(formula, workbook); } - + /** * If cell contains a formula, the formula is evaluated and returned, * else the CellValue simply copies the appropriate cell value from * the cell and also its cell type. This method should be preferred over * evaluateInCell() when the call should not modify the contents of the - * original cell. + * original cell. * @param cell */ public CellValue evaluate(HSSFCell cell) { @@ -157,17 +158,17 @@ public class HSSFFormulaEvaluator { } return retval; } - - + + /** * If cell contains formula, it evaluates the formula, * and saves the result of the formula. The cell * remains as a formula cell. * Else if cell does not contain formula, this method leaves - * the cell unchanged. + * the cell unchanged. * Note that the type of the formula result is returned, * so you know what kind of value is also stored with - * the formula. + * the formula. * 

      * int evaluatedCellType = evaluator.evaluateFormulaCell(cell);
      *
@@ -205,14 +206,14 @@ public class HSSFFormulaEvaluator { } return -1; } - + /** * If cell contains formula, it evaluates the formula, and * puts the formula result back into the cell, in place * of the old formula. * Else if cell does not contain formula, this method leaves - * the cell unchanged. - * Note that the same instance of HSSFCell is returned to + * the cell unchanged. + * Note that the same instance of HSSFCell is returned to * allow chained calls like: * 
      * int evaluatedCellType = evaluator.evaluateInCell(cell).getCellType();
@@ -252,7 +253,7 @@ public class HSSFFormulaEvaluator {
         }
         return cell;
     }
-    
+
     /**
      * Loops over all cells in all sheets of the supplied
      *  workbook.
@@ -261,7 +262,7 @@ public class HSSFFormulaEvaluator {
      *  remain as formula cells.
      * For cells that do not contain formulas, no changes
      *  are made.
-     * This is a helpful wrapper around looping over all 
+     * This is a helpful wrapper around looping over all
      *  cells, and calling evaluateFormulaCell on each one.
      */
 	public static void evaluateAllFormulaCells(HSSFWorkbook wb) {
@@ -280,8 +281,8 @@ public class HSSFFormulaEvaluator {
 			}
 		}
 	}
-        
-    
+
+
     /**
      * Returns a CellValue wrapper around the supplied ValueEval instance.
      * @param eval
@@ -318,19 +319,19 @@ public class HSSFFormulaEvaluator {
         }
         return retval;
     }
-    
+
     /**
-     * Dev. Note: Internal evaluate must be passed only a formula cell 
+     * Dev. Note: Internal evaluate must be passed only a formula cell
      * else a runtime exception will be thrown somewhere inside the method.
      * (Hence this is a private method.)
      */
     private static ValueEval internalEvaluate(HSSFCell srcCell, HSSFSheet sheet, HSSFWorkbook workbook) {
         int srcRowNum = srcCell.getRowIndex();
         short srcColNum = srcCell.getCellNum();
-        
-        
+
+
         EvaluationCycleDetector tracker = EvaluationCycleDetectorManager.getTracker();
-        
+
         if(!tracker.startEvaluate(workbook, sheet, srcRowNum, srcColNum)) {
             return ErrorEval.CIRCULAR_REF_ERROR;
         }
@@ -340,7 +341,7 @@ public class HSSFFormulaEvaluator {
             tracker.endEvaluate(workbook, sheet, srcRowNum, srcColNum);
         }
     }
-    private static ValueEval evaluateCell(HSSFWorkbook workbook, HSSFSheet sheet, 
+    private static ValueEval evaluateCell(HSSFWorkbook workbook, HSSFSheet sheet,
             int srcRowNum, short srcColNum, String cellFormulaText) {
 
     	Ptg[] ptgs = FormulaParser.parse(cellFormulaText, workbook);
@@ -350,20 +351,21 @@ public class HSSFFormulaEvaluator {
 
             // since we don't know how to handle these yet :(
             Ptg ptg = ptgs[i];
-            if (ptg instanceof ControlPtg) { 
+            if (ptg instanceof ControlPtg) {
                 // skip Parentheses, Attr, etc
-                continue; 
+                continue;
             }
             if (ptg instanceof MemErrPtg) { continue; }
             if (ptg instanceof MissingArgPtg) { continue; }
-            if (ptg instanceof NamePtg) { 
+            if (ptg instanceof NamePtg) {
                 // named ranges, macro functions
                 NamePtg namePtg = (NamePtg) ptg;
                 stack.push(new NameEval(namePtg.getIndex()));
-                continue; 
+                continue;
             }
             if (ptg instanceof NameXPtg) {
-                // TODO - external functions
+                NameXPtg nameXPtg = (NameXPtg) ptg;
+                stack.push(new NameXEval(nameXPtg.getSheetRefIndex(), nameXPtg.getNameIndex()));
                 continue;
             }
             if (ptg instanceof UnknownPtg) { continue; }
@@ -426,9 +428,9 @@ public class HSSFFormulaEvaluator {
         }
         value = dereferenceValue(value, srcRowNum, srcColNum);
         if (value instanceof BlankEval) {
-        	// Note Excel behaviour here. A blank final final value is converted to zero.  
+        	// Note Excel behaviour here. A blank final final value is converted to zero.
             return NumberEval.ZERO;
-            // Formulas _never_ evaluate to blank.  If a formula appears to have evaluated to 
+            // Formulas _never_ evaluate to blank.  If a formula appears to have evaluated to
             // blank, the actual value is empty string. This can be verified with ISBLANK().
         }
         return value;
@@ -472,13 +474,13 @@ public class HSSFFormulaEvaluator {
         }
         return operation.evaluate(ops, srcRowNum, srcColNum);
     }
-    
+
     public static AreaEval evaluateAreaPtg(HSSFSheet sheet, HSSFWorkbook workbook, AreaPtg ap) {
         int row0 = ap.getFirstRow();
         int col0 = ap.getFirstColumn();
         int row1 = ap.getLastRow();
         int col1 = ap.getLastColumn();
-        
+
         // If the last row is -1, then the
         //  reference is for the rest of the column
         // (eg C:C)
@@ -497,7 +499,7 @@ public class HSSFFormulaEvaluator {
     	int col1 = a3dp.getLastColumn();
         Workbook wb = workbook.getWorkbook();
         HSSFSheet xsheet = workbook.getSheetAt(wb.getSheetIndexFromExternSheetIndex(a3dp.getExternSheetIndex()));
-        
+
         // If the last row is -1, then the
         //  reference is for the rest of the column
         // (eg C:C)
@@ -505,12 +507,12 @@ public class HSSFFormulaEvaluator {
         if(row1 == -1 && row0 >= 0) {
             row1 = (short)xsheet.getLastRowNum();
         }
-        
+
         ValueEval[] values = evalArea(workbook, xsheet, row0, col0, row1, col1);
         return new Area3DEval(a3dp, values);
     }
-    
-    private static ValueEval[] evalArea(HSSFWorkbook workbook, HSSFSheet sheet, 
+
+    private static ValueEval[] evalArea(HSSFWorkbook workbook, HSSFSheet sheet,
     		int row0, int col0, int row1, int col1) {
         ValueEval[] values = new ValueEval[(row1 - row0 + 1) * (col1 - col0 + 1)];
         for (int x = row0; sheet != null && x < row1 + 1; x++) {
@@ -533,7 +535,7 @@ public class HSSFFormulaEvaluator {
      * one of: Area3DPtg, AreaPtg, ReferencePtg, Ref3DPtg, IntPtg, NumberPtg,
      * StringPtg, BoolPtg 
special Note: OperationPtg subtypes cannot be
      * passed here!
-     * 
+     *
      * @param ptg
      */
     protected static Eval getEvalForPtg(Ptg ptg) {
@@ -607,12 +609,12 @@ public class HSSFFormulaEvaluator {
      * Creates a Ref2DEval for ReferencePtg.
      * Non existent cells are treated as RefEvals containing BlankEval.
      */
-    private static Ref2DEval createRef2DEval(RefPtg ptg, HSSFCell cell, 
+    private static Ref2DEval createRef2DEval(RefPtg ptg, HSSFCell cell,
             HSSFSheet sheet, HSSFWorkbook workbook) {
         if (cell == null) {
             return new Ref2DEval(ptg, BlankEval.INSTANCE);
         }
-        
+
         switch (cell.getCellType()) {
             case HSSFCell.CELL_TYPE_NUMERIC:
                 return new Ref2DEval(ptg, new NumberEval(cell.getNumericCellValue()));
@@ -633,7 +635,7 @@ public class HSSFFormulaEvaluator {
     /**
      * create a Ref3DEval for Ref3DPtg.
      */
-    private static Ref3DEval createRef3DEval(Ref3DPtg ptg, HSSFCell cell, 
+    private static Ref3DEval createRef3DEval(Ref3DPtg ptg, HSSFCell cell,
             HSSFSheet sheet, HSSFWorkbook workbook) {
         if (cell == null) {
             return new Ref3DEval(ptg, BlankEval.INSTANCE);
@@ -654,9 +656,9 @@ public class HSSFFormulaEvaluator {
         }
         throw new RuntimeException("Unexpected cell type (" + cell.getCellType() + ")");
     }
-    
+
     /**
-     * Mimics the 'data view' of a cell. This allows formula evaluator 
+     * Mimics the 'data view' of a cell. This allows formula evaluator
      * to return a CellValue instead of precasting the value to String
      * or Number or boolean type.
      * @author Amol S. Deshmukh < amolweb at ya hoo dot com >
@@ -667,7 +669,7 @@ public class HSSFFormulaEvaluator {
         private double numberValue;
         private boolean booleanValue;
         private byte errorValue;
-        
+
         /**
          * CellType should be one of the types defined in HSSFCell
          * @param cellType
@@ -750,7 +752,7 @@ public class HSSFFormulaEvaluator {
 
     /**
      * debug method
-     * 
+     *
      * @param formula
      * @param sheet
      * @param workbook
@@ -770,5 +772,4 @@ public class HSSFFormulaEvaluator {
         }
         System.out.println("");
     }
-
 }
diff --git a/src/testcases/org/apache/poi/hssf/data/yearfracExamples.xls b/src/testcases/org/apache/poi/hssf/data/yearfracExamples.xls
new file mode 100644
index 0000000000000000000000000000000000000000..2b2be7d7d87d090ed3dc202e7daf2739050b13c1
GIT binary patch
literal 29184
zcmeI53vgUldB@LPt+cC$Ey=MYM=xJXYrSGSw&fV-VQc-6jfrF#N5%>$*`RP?D5K3t_`Byy}n+CCvbVaY7O(&=%5kAWV|&|9{Rs
z=RSA6A`B&rujj1p{q~&ie7|$P^WAgLy?6bukJg_5(5D)o6oGA%C=oB`%0-z+ZegEE
zZZ$%z!w!#M&gF7SCeNcBh!NK23|MQTK9LTW~8L25->iew`#Lux~6M@k_rN9sW8MCwB7
zM(RPj3~2?@N~Bdtmm{r4T7$F}sTYZU$n+Y|{;Q}f{$EVpBaY(VEc&8f9KqiiaT2Xp
z{8kT-(T2xR%ue!Ch^+h++xh29UmUyp`Io+C-Tw6SpT48)>(&*J&8ci+A8MZwhr|JC
z$01c}9-oUu%%b0F8;U8)svj9wIZMTL;)FPie`At;Ct6`r+C<5$n8N;%0!q73KVu-r
z4{a!r)%VrcoTE~)4K<8Q8#1y7=4a0a)UVl#t)K1nyi!h$)n!=ka-o{QRWM_j&ob
z5#QV>hDG!BBOM!%gXgGkT4|Z@&*f*E{9K4nOMFI_`L2|#L1cOY`IF*yc4&9P2Ka{=!#MMFt$-!?VrTf8gCT$a?a%BJ9yRM!&exF}aG^qaK<%UZ%#D)<=-=y}ljDt?c@
z{f+z+dE|eeM}A)(`Q3TspUflwcpmv*=aIiBk363}`Sd>^_3#ox`k`ru59E<6d&2pZ
zJz@Dr^62?g9{GcLf<)(j?Kl2h%
z%D*jkV;2!n*~805j~vC7oR^Z0+{P}hDg*LBy}ay{`Az#M|L3KsqvuBK;)+x0;pM56
z8#_Pdw3{P8TU%RSS&w$UOUijEtM#jPR_$MkNsyEW+L@QVQXa5#v6g=u^GC7Cv;!}X
zqw@2jTE-d9&AB;ohI*u&j={|*$61%x*xVq0dvdXOyUr(to5iE{ySJW7t`w(ya#;YE
z@{8qV3+K8#8Q~n*vk+&HSh#?i4$q^2no-W90I#27^D&7HHLn6ZHO{L5W6!(_B<5Ry
zoieW)7zXB5fa7;w1vs$hRe&RYUIiEm=2d_(VqOIpBnlST92gIb9wqsmFD?g4W|A(a
zrlYofDj8J5P*a2wx?z4Lj6X#vp=;(>!k|=y61r=CC5${pD4`4ISHf^tgc7=SekEK^
zyr7bE1BH8s>ybq$Dcm~@wnZo@+&f$mEka4*-r4#
zM2(%by@h*+tHQ1&d6g9I9WE;up`>u{;7nbNlES@X7pJ6f?<^}$N#WkX4B^5!YUMxw
z0BkQ#N#Wi}6{n{&xkm=wa;u1tok|9c;w>9x
z6eo7vBFXN(_ui0EeSLi)qr5V06lF>^SHe_Fd1c{Q>}AMaa9$9cC-a8WybAE{)VvC;
znr{K#_fj>mx7mTdh7C_kRwJ8=iY$Pu5q|;gV_*4iua5=iHoVK`hE{f|uU7gFg@uDW
zZ_`~+Nv^N365i*#ppxL}CfKj*pv3K_5Kh>gdB@5fV|{&Zjc&x;xxWgz^KWmA1v^FE
zw&gWe>oXzBMkK9ksdXxl5`v%iIY|GH~r1r5~?wnDgL(u*A
z-ya(7UYQ>4ltH)L^l10W!hLon@_PHMFS^g7;-WX)=w|fFv{Bx0qmA;)!XXIj(H_qW
zwN$vk)xH8jSG7(h_XbV}*PuW=|FsO;Jvdao!&jdU!j^0_meAeM8UFSR7uaM9M0!QI
zK;L`|qze}~6I+D4g}B~ayL!33(%w8io;fnR=5%sUoaV{h*{pbLZUOE}tj4nkHl8oA
z#b(@(9T)0utcrXVo_$EfGn3zY<`=nA?z8960?WBv!fI-R)vOIy(^jAw6&qzWrJ2d+
zzW+pYHH(7P^oFbHC{T@xp0b*<%;Zmg@%`v(E(un%E?iAdfod3EOArUpP>IasGv|L6
zUCrWPHCKeISyiAKMrEg(B>LvR{wKPcx?nZy!_}-UP>qVSlBPT}`O<%UC%T$?Rx%HSa|53}_jGhM4Z&(6JgvPzHC|6UH}LG|J{w)ll3+Cvp4M5Q8n36F8+iQb
z&qr5tX|S3IPrIx@HC|6UH*hZd-RNo>gVjWM+T{hR@p{_1flq(`$I;a^1*?hhwB7>M
zcs=dhz>AMP7+p;>t65*z(`;0;z6eiS+xz|p9#_>cpkXsHttD7Zgr}tnRO9uuwY}fD
z^Zn7)v<9n*@U*T1)p$K^ZSR-<{jTV0mIkYd@U#^Ls_}Z-+TQy=^wsEU>|iw!p0>I`
zHC|6!+k55_Cqf7NW?8VB2v1vApc=2It?m7F_Vy^6D%{q`rL;M3ZVsF`TXKoK=Sm@l
zZv8^E(soxVJSwPkSwN}Ri*Q$G_h7Wrl&ci}6I8l9pw#O#Lfrk&*GDT|?ka`11eJCN
zlzKfxh`EWsiB{U-Dur(Zm97jZ_4gbWv$y@yjWA(X8p&qs3qj1wu?LK;!kTVG?7?z=aN3K^bnH@k?H4S!f3DQPOu*
z?mIdYtH#qPxIwb@U}pS~y)`pEeL8tdz^;Z|1?)PA$>2C1Qnaz3fpycscCF$fhMW|7
z2dTD-mjh+SDs`l-Qb*bEh-}|INpy$BvB69-TR94`*(kJ)PX)R~5S=SAz#oZ^na$?E53K|M_6(NwoPe
z%()r^TkHS%}_UpA8b8)c)jdnEPs$A~Sx=@+cwGdDHV9D;a
znX!qfBRAQ&RtOkcQiGO#EexXXPr=ZeV4%82kS>4$vvlVvrH-zcfMqHWo{25QMTSQ?
zVqZ{sZG>_a|MDo$_5EG;2LPe+UaxY*z@YL)5z18@%%faQ4PsLHIzQ|5@2O5`Gbf<9*<`oYA8cdK7
zmjLI85*uiyax7*U6~WJGh+xZ
zfxv7vBQWoRVv^3lrx;k&6^)FiSai|6XR$KlI6PcxPGC6p`farYs@w%npOFtUQ)p9%~2^G7J&Sp+05BPP&DllHCV-`m7WdL6FNK>$MqeB{(UEDycj&w*?db
zqcgEeaize*{?Lh8+2d~uR9XksL$VFReeMnUjF7%Qr@*yD9cfF{k+wt~F_c|3SBkbv
z`AsM-MMvxg^NhLhXOq!YucoUGPt8uFwbuAcl~kj9hT$Ev=s31l*xN0txJIftI!g}-
ztUZ)mDpjz-Rqw~8Q~A~x*zP)xC|Z3)7poN;(MMw6*vt`zEEQh+{D#IV5vli~h8c`2
z9krsuDo^6Fh2NVT5%2XZlH3luTKpS^k^_?VI+%P??hS&S!H|#9Ux`+n5^t3r}uc*z+CB-3B+d
z@b5mn0D!-7=kHbRSmIJ!Qe5A^dC&Geo43Y9b3=n%`m*qv$jk5=eyUs#TS@USDr@=8;hWpZHJ@_1E|w>aUFHM}^eOH2;~#IrDm?alX{UN8zjRIe|y8xL=D;
zzTlG-FMjkF-x@ryyKldg_el9=^7BumWDa@swjAS22-~_9-#i>=FVrEuL*{;`{OmyP
zNx41S$LEIiewWemL1aYEb)_yqcEC2GZJGvj>O3PO1BYtgJr&2Eb)zR
z;BsDWoU(BOHYFpG4W^8E+dLF979B>J<7fj_)`mZG%E&p@g*SR0!OuT*=gX;HWOYeT&fc?6mE%vU>gZ&%ZmSLBBZ3`;oH`nzK+uQc;
z9_Sz0IJAAcM>w>1c+1XR8@Kgu-@JEV*z?78LwkmI?%J_&*U+whk9bf2a6m)<)}dWP
zgG1Z;19Difd(Y0HJv)c5-`Kl=vFhUXhuZ?u%le02cd?^?^FDq#_s2s0%dZ2mD4qY?
zSMN7Fa4zJTi!iYhh`NJsk?Lq!G;M+Yi_dc0t1Q$)~@og{Cpqyun;B!Cz
z4dQj=5zKmbdg{Hn8{GnO!tH+Wc7wl{)VDVI)*+}xbVWwRoSG>82(WLWn_nnSv{
z{dQU)7EoiwgA$ct!pXy_KT_36L(Q2^Y^IO{G#gqq@W4I}T8W##%1uv^wP(~|)GA;)PWnAwH%D7e=l$AxuxSSr$R~{j&h>%rA$f_b_
ze3va)M|FgZ#KC;E5wb-Qvc(ayx(Hc)gsdS#wj@H<7$IwlkTplhS|Vhv0htvmlYY=A
zUXtr}F_FfarVtOwqY@nPSbbND9~HnFBfjG*O&N~(FX?<+j(wg}A3}R_4N!gChm}2+
zreF1;jBt)(IO3+hE5Q-nP#wi_#8IJ+SSvSm#9G~)2`48h_;Uq{f7Z+6O5IAzYuGSKm|EK{npmg${@DzgstRKm8#o2>Qs
zyk!ziQz|NRt>Q{Hun;L|$l{|y7Q>E}-4f4<5{!R7V(XS;|Hh@oo3KY_j$Xq&te8bQ
zS|S`~zLTn$#Y0bl8McubPb4a{OEq(;FwCW>PnEG{8R$@D9OhEtFqaC$3`URbTpRZ?
zLqj$NwTc-WhPl+oOiKERVcM-IyCh{FKOJt_@92Hivt=&jKasLb^=z4@EYp-_E+z74
zN?8Wxk+MuUlx3O{cjk4^mIWviK1x`TO+l@q1c#w43sEw{8p;3@Lv^b$G
zPH4)6rcAh$$fNt;X&Kp<2~7#j$`KMSC8%JV>%Ub#N?4IiL9L<$hoR(y)NVQUCC?d#
za=_b{o6?Z`Bz=y@@+|6j-)-#6O67g!nzCF|mb;YYhB7V7KnMG>TvOsF>J(+UO9>D4
zSj!(K^Li{aWK&S9D8XSU%R`imorcosIqjcrUn+?nmlcNcMrkqT3beRFQ&wn71OXk3
zIG<20Gv`n`WgL&K(3BM}Wrd`Cq3!i={@EB~Q5HegjYY5`n}S+J2@XS9At`k^_9f5j
zK}t0)WnV^mEc(u|`26RdO0zGe#g&?}Qd8pHdQFKuK98+ZeOamdvQkr4x|Ez*RSRt`
z&u!kmgcaEo)GA7F7|O~JC1)yzGCVGQ65W@uP20gWpTBptakgd+E5DFm(ZcgIF!}eW2;?CP{B6WV{3er(2z|*
zt)c{np{x#hEa%6D((zBvxb#VMjIA-0b3b}2&7ahelr@^NMpM?flr@I(gR%^Ckg`Vi
zWsPw9vc{zZ6?D5ETkE5QhHMII6(u+fC11#J%Q5lcP^M)W=ul;xzO2=hwJs&7Jl1kv=^dBQkWE3Yq6CMbyOdv7xk`^QE*HueB*kys)HqO!Qf7DAl-x4pQO=
zuyj_(V;8%Wm<7PL^vJ-TM{9nL^JSfn5*o58s8y8UFqC{v#VtpV<#nc^^fAfk6FDyH
z45f-bsEQWXY05fHS?Bg;ouO3Y5;{~Fhq6xhWt~e2D%f_Y<;%zJDkWvTj}jWPDX3MH
z;4qYYOU5lnN?th|N*|M?j2xHshEhczR7H#HHD$e~tamBv4W$~F(4opWl=Ygj-lYT;
zYRC092LrH@Hc-sp`-
zdR#6slq&k5Dq6fmQ!dezOI*q&hVp~Q7_p#3m2oJSXv!rnC8%JVJ1!f2l+ch(L9L<$
zhoR&vN^Uv!CD&^VWq4frdRJ368cH=TQ57w2)Rc{yveBh%G?dC?p+l8%C>u3pqe}@Y
z*!J$eBRBlO!m^HN@=-!VHU+hc5*&t-uP(XeNXZo_Lm3{IzTVZ8O@>m9OH@URn>1yU
zrfhO4n+&D$Sm;n?9Lgq5+2m4!3bws*?vb0$mXfmBM+ptt6x1q8a2QIyIpvlkCD+sp
zrLX7mdu+3zRO1p=(c)%J*{ms>UCL%dsXP`sR2he|SyMKQaIVwz=aH*C?JVFKEc7pjJ_W!%*_IHMiX3cs}4;
z_>H0GtqF0TcowU$&ldfUEB`iHD7X^7EJDWnA;Em@5wcW-tRq6!86oS6kab7MdIB=~
zGdWC8ha<4h!*y)3O;4)`jG@C`vvp6~x~FZor){^V<=GTER2iqIZQawhFjt=#rLe92
z^2GNhtt7l-nXjjzA)A6))zjcGJd*P{FoO{Iq>XEJ4aPA0;$oQ&6iY!C@%*Mxk4dl=L-2
z8IHh_v8l~astAm#Xfb|qTN&D>DcfAiHbbdm6LhFD4rQCB#M_8^B>_|(Yx!RWct2C5!nT!99{**lRfCr4^ie`XHU+hc
z5*&uILsIH;9G8sQhB6#|BFAN?p;XZaRng*3P1&g_J6+07L#f6kbf_{8Wv8aZ%cq(W
zRIqK^7Z&Wlp@fuOK1yiFrl3|)g2PaDN=jXhlspR=%5e0FoHKM8N)>%j6)nd9(I9KY
zjR
zhEhczR7H!sHD$M^>~<-;4W$~F(4opWl--)L+oj}NyWYO+@lirUHU+hc5*&uIJ4DHu
zf}!+{LK%IIMMa+;L#d(Ya<2s`Eb?JL;|@y8x}nrhD6
z3H}u?+(-T|BXLZyeRd-;tc@V?_hl!LDv_p<$iTOk$^CXDzTEdtB(}$GNNk^bkb><2
zksOQ6L0#PP{cS}hw1fSjUZE6K5K$$Yl*RTPojG)zZ&x0dTAACwS|Z~aB8Ejrt)xqiL~TY_^38G`?JR|i_a9fw1clT+gv
wH5~1=t5^5h#<}DfW6RX!  0.000000001) {
+			double hours = diff * 365 * 24;
+			System.out.println(startDate + " " + endDate + " off by " + hours + " hours");
+			assertEquals(expectedValue, actualValue, 0.000000001);
+		}
+		
+	}
+
+	private static double md(int year, int month, int day) {
+		Calendar c = new GregorianCalendar();
+		
+		c.set(year, month-1, day, 0, 0, 0);
+		c.set(Calendar.MILLISECOND, 0);
+		return HSSFDateUtil.getExcelDate(c.getTime());
+	}
+}
diff --git a/src/testcases/org/apache/poi/hssf/record/formula/atp/TestYearFracCalculatorFromSpreadsheet.java b/src/testcases/org/apache/poi/hssf/record/formula/atp/TestYearFracCalculatorFromSpreadsheet.java
new file mode 100644
index 0000000000..2cad8e3620
--- /dev/null
+++ b/src/testcases/org/apache/poi/hssf/record/formula/atp/TestYearFracCalculatorFromSpreadsheet.java
@@ -0,0 +1,178 @@
+/* ====================================================================
+   Licensed to the Apache Software Foundation (ASF) under one or more
+   contributor license agreements.  See the NOTICE file distributed with
+   this work for additional information regarding copyright ownership.
+   The ASF licenses this file to You under the Apache License, Version 2.0
+   (the "License"); you may not use this file except in compliance with
+   the License.  You may obtain a copy of the License at
+
+       http://www.apache.org/licenses/LICENSE-2.0
+
+   Unless required by applicable law or agreed to in writing, software
+   distributed under the License is distributed on an "AS IS" BASIS,
+   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+   See the License for the specific language governing permissions and
+   limitations under the License.
+==================================================================== */
+
+package org.apache.poi.hssf.record.formula.atp;
+
+import java.io.PrintStream;
+import java.util.Calendar;
+import java.util.GregorianCalendar;
+import java.util.Iterator;
+
+import junit.framework.Assert;
+import junit.framework.AssertionFailedError;
+import junit.framework.ComparisonFailure;
+import junit.framework.TestCase;
+
+import org.apache.poi.hssf.HSSFTestDataSamples;
+import org.apache.poi.hssf.record.formula.eval.EvaluationException;
+import org.apache.poi.hssf.usermodel.HSSFCell;
+import org.apache.poi.hssf.usermodel.HSSFDateUtil;
+import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
+import org.apache.poi.hssf.usermodel.HSSFRow;
+import org.apache.poi.hssf.usermodel.HSSFSheet;
+import org.apache.poi.hssf.usermodel.HSSFWorkbook;
+
+/**
+ * Tests YearFracCalculator using test-cases listed in a sample spreadsheet
+ * 
+ * @author Josh Micich
+ */
+public final class TestYearFracCalculatorFromSpreadsheet extends TestCase {
+	
+	private static final class SS {
+
+		public static final int BASIS_COLUMN = 1; // "B"
+		public static final int START_YEAR_COLUMN = 2; // "C"
+		public static final int END_YEAR_COLUMN = 5; // "F"
+		public static final int YEARFRAC_FORMULA_COLUMN = 11; // "L"
+		public static final int EXPECTED_RESULT_COLUMN = 13; // "N"
+	}
+
+	public void testAll() {
+		
+		HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("yearfracExamples.xls");
+		HSSFSheet sheet = wb.getSheetAt(0);
+		HSSFFormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator(sheet, wb);
+		int nSuccess = 0;
+		int nFailures = 0;
+		int nUnexpectedErrors = 0;
+		Iterator rowIterator = sheet.rowIterator();
+		while(rowIterator.hasNext()) {
+			HSSFRow row = (HSSFRow) rowIterator.next();
+			
+			HSSFCell cell = row.getCell(SS.YEARFRAC_FORMULA_COLUMN);
+			if (cell == null || cell.getCellType() != HSSFCell.CELL_TYPE_FORMULA) {
+				continue;
+			}
+			try {
+				processRow(row, cell, formulaEvaluator);
+				nSuccess++;
+			} catch (RuntimeException e) {
+				nUnexpectedErrors ++;
+				printShortStackTrace(System.err, e);
+			} catch (AssertionFailedError e) {
+				nFailures ++;
+				printShortStackTrace(System.err, e);
+			}
+		}
+		if (nUnexpectedErrors + nFailures > 0) {
+			String msg = nFailures + " failures(s) and " + nUnexpectedErrors 
+				+ " unexpected errors(s) occurred. See stderr for details";
+			throw new AssertionFailedError(msg);
+		}
+		if (nSuccess < 1) {
+			throw new RuntimeException("No test sample cases found");
+		}
+	}
+	
+	private static void processRow(HSSFRow row, HSSFCell cell, HSSFFormulaEvaluator formulaEvaluator) {
+		
+		double startDate = makeDate(row, SS.START_YEAR_COLUMN);
+		double endDate = makeDate(row, SS.END_YEAR_COLUMN);
+		
+		int basis = getIntCell(row, SS.BASIS_COLUMN);
+		
+		double expectedValue = getDoubleCell(row, SS.EXPECTED_RESULT_COLUMN);
+		
+		double actualValue;
+		try {
+			actualValue = YearFracCalculator.calculate(startDate, endDate, basis);
+		} catch (EvaluationException e) {
+			throw new RuntimeException(e);
+		}
+		if (expectedValue != actualValue) {
+			throw new ComparisonFailure("Direct calculate failed - row " + (row.getRowNum()+1), 
+					String.valueOf(expectedValue), String.valueOf(actualValue));
+		}
+		actualValue = formulaEvaluator.evaluate(cell).getNumberValue();
+		if (expectedValue != actualValue) {
+			throw new ComparisonFailure("Formula evaluate failed - row " + (row.getRowNum()+1), 
+					String.valueOf(expectedValue), String.valueOf(actualValue));
+		}
+	}
+
+	private static double makeDate(HSSFRow row, int yearColumn) {
+		int year = getIntCell(row, yearColumn + 0);
+		int month = getIntCell(row, yearColumn + 1);
+		int day = getIntCell(row, yearColumn + 2);
+		Calendar c = new GregorianCalendar(year, month-1, day, 0, 0, 0);
+		c.set(Calendar.MILLISECOND, 0);
+		return HSSFDateUtil.getExcelDate(c.getTime());
+	}
+
+	private static int getIntCell(HSSFRow row, int colIx) {
+		double dVal = getDoubleCell(row, colIx);
+		if (Math.floor(dVal) != dVal) {
+			throw new RuntimeException("Non integer value (" + dVal 
+					+ ") cell found at column " + (char)('A' + colIx));
+		}
+		return (int)dVal;
+	}
+
+	private static double getDoubleCell(HSSFRow row, int colIx) {
+		HSSFCell cell = row.getCell(colIx);
+		if (cell == null) {
+			throw new RuntimeException("No cell found at column " + colIx);
+		}
+		double dVal = cell.getNumericCellValue();
+		return dVal;
+	}
+
+	/**
+	 * Useful to keep output concise when expecting many failures to be reported by this test case
+	 * TODO - refactor duplicates in other Test~FromSpreadsheet classes
+	 */
+	private static void printShortStackTrace(PrintStream ps, Throwable e) {
+		StackTraceElement[] stes = e.getStackTrace();
+		
+		int startIx = 0;
+		// skip any top frames inside junit.framework.Assert
+		while(startIx= endIx) {
+			// something went wrong. just print the whole stack trace
+			e.printStackTrace(ps);
+		}
+		endIx -= 4; // skip 4 frames of reflection invocation
+		ps.println(e.toString());
+		for(int i=startIx; i