From b34e1e25d6500ed4249ab35486429ecb804e4f52 Mon Sep 17 00:00:00 2001 From: Yegor Kozlov Date: Sun, 21 Nov 2010 12:04:56 +0000 Subject: [PATCH] moved common formula-related code to org.apache.poi.ss.formula, eliminated dependencies on HSSF, reduced the number of eclipse warnings git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1037436 13f79535-47bb-0310-9956-ffa450edef68 --- .../poi/ss/formula/atp/AnalysisToolPak.java | 164 +++++++++ .../poi/ss/formula/atp/ParityFunction.java | 67 ++++ .../poi/ss/formula/atp/RandBetween.java | 84 +++++ .../apache/poi/ss/formula/atp/YearFrac.java | 159 ++++++++ .../ss/formula/atp/YearFracCalculator.java | 344 ++++++++++++++++++ .../apache/poi/ss/formula/eval/AreaEval.java | 93 +++++ .../poi/ss/formula/eval/AreaEvalBase.java | 117 ++++++ .../apache/poi/ss/formula/eval/BlankEval.java | 35 ++ .../apache/poi/ss/formula/eval/BoolEval.java | 64 ++++ .../poi/ss/formula/eval/ConcatEval.java | 60 +++ .../apache/poi/ss/formula/eval/ErrorEval.java | 111 ++++++ .../ss/formula/eval/EvaluationException.java | 134 +++++++ .../poi/ss/formula/eval/FunctionEval.java | 249 +++++++++++++ .../poi/ss/formula/eval/IntersectionEval.java | 96 +++++ .../poi/ss/formula/eval/MissingArgEval.java | 36 ++ .../apache/poi/ss/formula/eval/NameEval.java | 46 +++ .../apache/poi/ss/formula/eval/NameXEval.java | 44 +++ .../poi/ss/formula/eval/NumberEval.java | 73 ++++ .../poi/ss/formula/eval/NumericValueEval.java | 30 ++ .../poi/ss/formula/eval/OperandResolver.java | 324 +++++++++++++++++ .../poi/ss/formula/eval/PercentEval.java | 49 +++ .../apache/poi/ss/formula/eval/RangeEval.java | 75 ++++ .../apache/poi/ss/formula/eval/RefEval.java | 51 +++ .../poi/ss/formula/eval/RefEvalBase.java | 40 ++ .../formula/eval/RelationalOperationEval.java | 168 +++++++++ .../poi/ss/formula/eval/StringEval.java | 54 +++ .../poi/ss/formula/eval/StringValueEval.java | 30 ++ .../eval/TwoOperandNumericOperation.java | 87 +++++ .../poi/ss/formula/eval/UnaryMinusEval.java | 47 +++ .../poi/ss/formula/eval/UnaryPlusEval.java | 51 +++ .../apache/poi/ss/formula/eval/ValueEval.java | 25 ++ .../eval/forked/ForkedEvaluationCell.java | 31 +- .../eval/forked/ForkedEvaluationWorkbook.java | 1 - .../formula/eval/forked/ForkedEvaluator.java | 26 +- .../apache/poi/ss/usermodel/CellValue.java | 3 +- .../LookupFunctionsTestCaseData.xls | Bin 41984 -> 41472 bytes 36 files changed, 3036 insertions(+), 32 deletions(-) create mode 100644 src/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java create mode 100644 src/java/org/apache/poi/ss/formula/atp/ParityFunction.java create mode 100644 src/java/org/apache/poi/ss/formula/atp/RandBetween.java create mode 100644 src/java/org/apache/poi/ss/formula/atp/YearFrac.java create mode 100644 src/java/org/apache/poi/ss/formula/atp/YearFracCalculator.java create mode 100644 src/java/org/apache/poi/ss/formula/eval/AreaEval.java create mode 100644 src/java/org/apache/poi/ss/formula/eval/AreaEvalBase.java create mode 100644 src/java/org/apache/poi/ss/formula/eval/BlankEval.java create mode 100644 src/java/org/apache/poi/ss/formula/eval/BoolEval.java create mode 100644 src/java/org/apache/poi/ss/formula/eval/ConcatEval.java create mode 100644 src/java/org/apache/poi/ss/formula/eval/ErrorEval.java create mode 100644 src/java/org/apache/poi/ss/formula/eval/EvaluationException.java create mode 100644 src/java/org/apache/poi/ss/formula/eval/FunctionEval.java create mode 100644 src/java/org/apache/poi/ss/formula/eval/IntersectionEval.java create mode 100644 src/java/org/apache/poi/ss/formula/eval/MissingArgEval.java create mode 100644 src/java/org/apache/poi/ss/formula/eval/NameEval.java create mode 100644 src/java/org/apache/poi/ss/formula/eval/NameXEval.java create mode 100644 src/java/org/apache/poi/ss/formula/eval/NumberEval.java create mode 100644 src/java/org/apache/poi/ss/formula/eval/NumericValueEval.java create mode 100644 src/java/org/apache/poi/ss/formula/eval/OperandResolver.java create mode 100644 src/java/org/apache/poi/ss/formula/eval/PercentEval.java create mode 100644 src/java/org/apache/poi/ss/formula/eval/RangeEval.java create mode 100644 src/java/org/apache/poi/ss/formula/eval/RefEval.java create mode 100644 src/java/org/apache/poi/ss/formula/eval/RefEvalBase.java create mode 100644 src/java/org/apache/poi/ss/formula/eval/RelationalOperationEval.java create mode 100644 src/java/org/apache/poi/ss/formula/eval/StringEval.java create mode 100644 src/java/org/apache/poi/ss/formula/eval/StringValueEval.java create mode 100644 src/java/org/apache/poi/ss/formula/eval/TwoOperandNumericOperation.java create mode 100644 src/java/org/apache/poi/ss/formula/eval/UnaryMinusEval.java create mode 100644 src/java/org/apache/poi/ss/formula/eval/UnaryPlusEval.java create mode 100644 src/java/org/apache/poi/ss/formula/eval/ValueEval.java diff --git a/src/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java b/src/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java new file mode 100644 index 0000000000..58871a64ba --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java @@ -0,0 +1,164 @@ +/* ==================================================================== + 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.ss.formula.atp; + +import java.util.HashMap; +import java.util.Map; + +import org.apache.poi.ss.formula.eval.ValueEval; +import org.apache.poi.ss.formula.functions.FreeRefFunction; +import org.apache.poi.ss.formula.udf.UDFFinder; +import org.apache.poi.ss.formula.OperationEvaluationContext; +import org.apache.poi.ss.formula.eval.NotImplementedException; + +/** + * @author Josh Micich + * @author Petr Udalau - systematized work of add-in libraries and user defined functions. + */ +public final class AnalysisToolPak implements UDFFinder { + + public static final UDFFinder instance = new AnalysisToolPak(); + + private static final class NotImplemented implements FreeRefFunction { + private final String _functionName; + + public NotImplemented(String functionName) { + _functionName = functionName; + } + + public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) { + throw new NotImplementedException(_functionName); + } + }; + + private final Map _functionsByName = createFunctionsMap(); + + + private AnalysisToolPak() { + // enforce singleton + } + + public FreeRefFunction findFunction(String name) { + return _functionsByName.get(name); + } + + private 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", ParityFunction.IS_EVEN); + r(m, "ISODD", ParityFunction.IS_ODD); + 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, "RANDBETWEEN", RandBetween.instance); + 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 ? new NotImplemented(functionName) : pFunc; + m.put(functionName, func); + } +} diff --git a/src/java/org/apache/poi/ss/formula/atp/ParityFunction.java b/src/java/org/apache/poi/ss/formula/atp/ParityFunction.java new file mode 100644 index 0000000000..04b5e92239 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/atp/ParityFunction.java @@ -0,0 +1,67 @@ +/* ==================================================================== + 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.ss.formula.atp; + +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.OperandResolver; +import org.apache.poi.ss.formula.eval.ValueEval; +import org.apache.poi.ss.formula.functions.FreeRefFunction; +import org.apache.poi.ss.formula.OperationEvaluationContext; +/** + * Implementation of Excel 'Analysis ToolPak' function ISEVEN() ISODD()
+ * + * @author Josh Micich + */ +final class ParityFunction implements FreeRefFunction { + + public static final FreeRefFunction IS_EVEN = new ParityFunction(0); + public static final FreeRefFunction IS_ODD = new ParityFunction(1); + private final int _desiredParity; + + private ParityFunction(int desiredParity) { + _desiredParity = desiredParity; + } + + public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) { + if (args.length != 1) { + return ErrorEval.VALUE_INVALID; + } + + int val; + try { + val = evaluateArgParity(args[0], ec.getRowIndex(), ec.getColumnIndex()); + } catch (EvaluationException e) { + return e.getErrorEval(); + } + + return BoolEval.valueOf(val == _desiredParity); + } + + private static int evaluateArgParity(ValueEval arg, int srcCellRow, int srcCellCol) throws EvaluationException { + ValueEval ve = OperandResolver.getSingleValue(arg, srcCellRow, (short)srcCellCol); + + double d = OperandResolver.coerceValueToDouble(ve); + if (d < 0) { + d = -d; + } + long v = (long) Math.floor(d); + return (int) (v & 0x0001); + } +} diff --git a/src/java/org/apache/poi/ss/formula/atp/RandBetween.java b/src/java/org/apache/poi/ss/formula/atp/RandBetween.java new file mode 100644 index 0000000000..4da7a53ef5 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/atp/RandBetween.java @@ -0,0 +1,84 @@ +/* ==================================================================== + 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.ss.formula.atp; + +import org.apache.poi.ss.formula.eval.ErrorEval; +import org.apache.poi.ss.formula.eval.EvaluationException; +import org.apache.poi.ss.formula.eval.NumberEval; +import org.apache.poi.ss.formula.eval.OperandResolver; +import org.apache.poi.ss.formula.eval.ValueEval; +import org.apache.poi.ss.formula.functions.FreeRefFunction; +import org.apache.poi.ss.formula.OperationEvaluationContext; + +/** + * Implementation of Excel 'Analysis ToolPak' function RANDBETWEEN()
+ * + * Returns a random integer number between the numbers you specify.

+ * + * Syntax
+ * RANDBETWEEN(bottom, top)

+ * + * bottom is the smallest integer RANDBETWEEN will return.
+ * top is the largest integer RANDBETWEEN will return.
+ + * @author Brendan Nolan + */ +final class RandBetween implements FreeRefFunction{ + + public static final FreeRefFunction instance = new RandBetween(); + + private RandBetween() { + //enforces singleton + } + + /** + * Evaluate for RANDBETWEEN(). Must be given two arguments. Bottom must be greater than top. + * Bottom is rounded up and top value is rounded down. After rounding top has to be set greater + * than top. + * + * @see org.apache.poi.ss.formula.functions.FreeRefFunction#evaluate(org.apache.poi.ss.formula.eval.ValueEval[], org.apache.poi.ss.formula.OperationEvaluationContext) + */ + public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) { + + double bottom, top; + + if (args.length != 2) { + return ErrorEval.VALUE_INVALID; + } + + try { + bottom = OperandResolver.coerceValueToDouble(OperandResolver.getSingleValue(args[0], ec.getRowIndex(), ec.getColumnIndex())); + top = OperandResolver.coerceValueToDouble(OperandResolver.getSingleValue(args[1], ec.getRowIndex(), ec.getColumnIndex())); + if(bottom > top) { + return ErrorEval.NUM_ERROR; + } + } catch (EvaluationException e) { + return ErrorEval.VALUE_INVALID; + } + + bottom = Math.ceil(bottom); + top = Math.floor(top); + + if(bottom > top) { + top = bottom; + } + + return new NumberEval((bottom + (int)(Math.random() * ((top - bottom) + 1)))); + + } + +} diff --git a/src/java/org/apache/poi/ss/formula/atp/YearFrac.java b/src/java/org/apache/poi/ss/formula/atp/YearFrac.java new file mode 100644 index 0000000000..062fcf6f9e --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/atp/YearFrac.java @@ -0,0 +1,159 @@ +/* ==================================================================== + 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.ss.formula.atp; + +import java.util.Calendar; +import java.util.GregorianCalendar; +import java.util.regex.Pattern; + +import org.apache.poi.ss.formula.eval.ErrorEval; +import org.apache.poi.ss.formula.eval.EvaluationException; +import org.apache.poi.ss.formula.eval.NumberEval; +import org.apache.poi.ss.formula.eval.OperandResolver; +import org.apache.poi.ss.formula.eval.StringEval; +import org.apache.poi.ss.formula.eval.ValueEval; +import org.apache.poi.ss.formula.functions.FreeRefFunction; +import org.apache.poi.ss.formula.OperationEvaluationContext; +import org.apache.poi.ss.usermodel.DateUtil; +/** + * 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(ValueEval[] args, OperationEvaluationContext ec) { + int srcCellRow = ec.getRowIndex(); + int srcCellCol = ec.getColumnIndex(); + 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(ValueEval arg, int srcCellRow, int srcCellCol) throws EvaluationException { + ValueEval ve = OperandResolver.getSingleValue(arg, srcCellRow, (short) 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 DateUtil.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); + } + cal.set(Calendar.DAY_OF_MONTH, day); + return cal; + } + + private static int evaluateIntArg(ValueEval arg, int srcCellRow, int srcCellCol) throws EvaluationException { + ValueEval ve = OperandResolver.getSingleValue(arg, srcCellRow, (short) srcCellCol); + return OperandResolver.coerceValueToInt(ve); + } +} diff --git a/src/java/org/apache/poi/ss/formula/atp/YearFracCalculator.java b/src/java/org/apache/poi/ss/formula/atp/YearFracCalculator.java new file mode 100644 index 0000000000..765b23ae6c --- /dev/null +++ b/src/java/org/apache/poi/ss/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.ss.formula.atp; + +import java.util.Calendar; +import java.util.GregorianCalendar; +import java.util.TimeZone; + +import org.apache.poi.ss.formula.eval.ErrorEval; +import org.apache.poi.ss.formula.eval.EvaluationException; +import org.apache.poi.ss.usermodel.DateUtil; + + +/** + * 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); + DateUtil.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/ss/formula/eval/AreaEval.java b/src/java/org/apache/poi/ss/formula/eval/AreaEval.java new file mode 100644 index 0000000000..ec2e41cf22 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/eval/AreaEval.java @@ -0,0 +1,93 @@ +/* ==================================================================== + 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.ss.formula.eval; + +import org.apache.poi.ss.formula.TwoDEval; +/** + * @author Amol S. Deshmukh < amolweb at ya hoo dot com > + * + */ +public interface AreaEval extends TwoDEval { + + /** + * returns the 0-based index of the first row in + * this area. + */ + int getFirstRow(); + + /** + * returns the 0-based index of the last row in + * this area. + */ + int getLastRow(); + + /** + * returns the 0-based index of the first col in + * this area. + */ + int getFirstColumn(); + + /** + * returns the 0-based index of the last col in + * this area. + */ + int getLastColumn(); + + /** + * @return the ValueEval from within this area at the specified row and col index. Never + * null (possibly {@link BlankEval}). The specified indexes should be absolute + * indexes in the sheet and not relative indexes within the area. + */ + ValueEval getAbsoluteValue(int row, int col); + + /** + * returns true if the cell at row and col specified + * as absolute indexes in the sheet is contained in + * this area. + * @param row + * @param col + */ + boolean contains(int row, int col); + + /** + * returns true if the specified col is in range + * @param col + */ + boolean containsColumn(int col); + + /** + * returns true if the specified row is in range + * @param row + */ + boolean containsRow(int row); + + int getWidth(); + int getHeight(); + /** + * @return the ValueEval from within this area at the specified relativeRowIndex and + * relativeColumnIndex. Never null (possibly {@link BlankEval}). The + * specified indexes should relative to the top left corner of this area. + */ + ValueEval getRelativeValue(int relativeRowIndex, int relativeColumnIndex); + + /** + * Creates an {@link AreaEval} offset by a relative amount from from the upper left cell + * of this area + */ + AreaEval offset(int relFirstRowIx, int relLastRowIx, int relFirstColIx, int relLastColIx); +} diff --git a/src/java/org/apache/poi/ss/formula/eval/AreaEvalBase.java b/src/java/org/apache/poi/ss/formula/eval/AreaEvalBase.java new file mode 100644 index 0000000000..8f09e6e07a --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/eval/AreaEvalBase.java @@ -0,0 +1,117 @@ +/* ==================================================================== + 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.ss.formula.eval; + +import org.apache.poi.hssf.record.formula.AreaI; + +/** + * @author Josh Micich + */ +public abstract class AreaEvalBase implements AreaEval { + + private final int _firstColumn; + private final int _firstRow; + private final int _lastColumn; + private final int _lastRow; + private final int _nColumns; + private final int _nRows; + + protected AreaEvalBase(int firstRow, int firstColumn, int lastRow, int lastColumn) { + _firstColumn = firstColumn; + _firstRow = firstRow; + _lastColumn = lastColumn; + _lastRow = lastRow; + + _nColumns = _lastColumn - _firstColumn + 1; + _nRows = _lastRow - _firstRow + 1; + } + + protected AreaEvalBase(AreaI ptg) { + _firstRow = ptg.getFirstRow(); + _firstColumn = ptg.getFirstColumn(); + _lastRow = ptg.getLastRow(); + _lastColumn = ptg.getLastColumn(); + + _nColumns = _lastColumn - _firstColumn + 1; + _nRows = _lastRow - _firstRow + 1; + } + + public final int getFirstColumn() { + return _firstColumn; + } + + public final int getFirstRow() { + return _firstRow; + } + + public final int getLastColumn() { + return _lastColumn; + } + + public final int getLastRow() { + return _lastRow; + } + public final ValueEval getAbsoluteValue(int row, int col) { + int rowOffsetIx = row - _firstRow; + int colOffsetIx = col - _firstColumn; + + if(rowOffsetIx < 0 || rowOffsetIx >= _nRows) { + throw new IllegalArgumentException("Specified row index (" + row + + ") is outside the allowed range (" + _firstRow + ".." + _lastRow + ")"); + } + if(colOffsetIx < 0 || colOffsetIx >= _nColumns) { + throw new IllegalArgumentException("Specified column index (" + col + + ") is outside the allowed range (" + _firstColumn + ".." + col + ")"); + } + return getRelativeValue(rowOffsetIx, colOffsetIx); + } + + public final boolean contains(int row, int col) { + return _firstRow <= row && _lastRow >= row + && _firstColumn <= col && _lastColumn >= col; + } + + public final boolean containsRow(int row) { + return _firstRow <= row && _lastRow >= row; + } + + public final boolean containsColumn(int col) { + return _firstColumn <= col && _lastColumn >= col; + } + + public final boolean isColumn() { + return _firstColumn == _lastColumn; + } + + public final boolean isRow() { + return _firstRow == _lastRow; + } + public int getHeight() { + return _lastRow-_firstRow+1; + } + + public final ValueEval getValue(int row, int col) { + return getRelativeValue(row, col); + } + + public abstract ValueEval getRelativeValue(int relativeRowIndex, int relativeColumnIndex); + + public int getWidth() { + return _lastColumn-_firstColumn+1; + } +} diff --git a/src/java/org/apache/poi/ss/formula/eval/BlankEval.java b/src/java/org/apache/poi/ss/formula/eval/BlankEval.java new file mode 100644 index 0000000000..b49b2992ab --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/eval/BlankEval.java @@ -0,0 +1,35 @@ +/* ==================================================================== + 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.ss.formula.eval; + +/** + * @author Amol S. Deshmukh < amolweb at ya hoo dot com > This class is a + * marker class. It is a special value for empty cells. + */ +public final class BlankEval implements ValueEval { + + public static final BlankEval instance = new BlankEval(); + /** + * @deprecated (Nov 2009) use {@link #instance} + */ + public static final BlankEval INSTANCE = instance; + + private BlankEval() { + // enforce singleton + } +} diff --git a/src/java/org/apache/poi/ss/formula/eval/BoolEval.java b/src/java/org/apache/poi/ss/formula/eval/BoolEval.java new file mode 100644 index 0000000000..cab4105d0f --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/eval/BoolEval.java @@ -0,0 +1,64 @@ +/* ==================================================================== + 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.ss.formula.eval; + +/** + * @author Amol S. Deshmukh < amolweb at ya hoo dot com > + */ +public final class BoolEval implements NumericValueEval, StringValueEval { + + private boolean _value; + + public static final BoolEval FALSE = new BoolEval(false); + + public static final BoolEval TRUE = new BoolEval(true); + + /** + * Convenience method for the following:
+ * (b ? BoolEval.TRUE : BoolEval.FALSE) + * + * @return the BoolEval instance representing b. + */ + public static final BoolEval valueOf(boolean b) { + return b ? TRUE : FALSE; + } + + private BoolEval(boolean value) { + _value = value; + } + + public boolean getBooleanValue() { + return _value; + } + + public double getNumberValue() { + return _value ? 1 : 0; + } + + public String getStringValue() { + return _value ? "TRUE" : "FALSE"; + } + + public String toString() { + StringBuilder sb = new StringBuilder(64); + sb.append(getClass().getName()).append(" ["); + sb.append(getStringValue()); + sb.append("]"); + return sb.toString(); + } +} diff --git a/src/java/org/apache/poi/ss/formula/eval/ConcatEval.java b/src/java/org/apache/poi/ss/formula/eval/ConcatEval.java new file mode 100644 index 0000000000..5622be8705 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/eval/ConcatEval.java @@ -0,0 +1,60 @@ +/* ==================================================================== + 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.ss.formula.eval; + +import org.apache.poi.ss.formula.functions.Fixed2ArgFunction; +import org.apache.poi.ss.formula.functions.Function; + +/** + * @author Amol S. Deshmukh < amolweb at ya hoo dot com > + */ +public final class ConcatEval extends Fixed2ArgFunction { + + public static final Function instance = new ConcatEval(); + + private ConcatEval() { + // enforce singleton + } + + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1) { + ValueEval ve0; + ValueEval ve1; + try { + ve0 = OperandResolver.getSingleValue(arg0, srcRowIndex, srcColumnIndex); + ve1 = OperandResolver.getSingleValue(arg1, srcRowIndex, srcColumnIndex); + } catch (EvaluationException e) { + return e.getErrorEval(); + } + StringBuilder sb = new StringBuilder(); + sb.append(getText(ve0)); + sb.append(getText(ve1)); + return new StringEval(sb.toString()); + } + + private Object getText(ValueEval ve) { + if (ve instanceof StringValueEval) { + StringValueEval sve = (StringValueEval) ve; + return sve.getStringValue(); + } + if (ve == BlankEval.instance) { + return ""; + } + throw new IllegalAccessError("Unexpected value type (" + + ve.getClass().getName() + ")"); + } +} diff --git a/src/java/org/apache/poi/ss/formula/eval/ErrorEval.java b/src/java/org/apache/poi/ss/formula/eval/ErrorEval.java new file mode 100644 index 0000000000..6cc8136b37 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/eval/ErrorEval.java @@ -0,0 +1,111 @@ +/* +* 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.ss.formula.eval; + +import org.apache.poi.ss.usermodel.ErrorConstants; + +/** + * @author Amol S. Deshmukh < amolweb at ya hoo dot com > + * + */ +public final class ErrorEval implements ValueEval { + + // convenient access to namespace + private static final ErrorConstants EC = null; + + /** #NULL! - Intersection of two cell ranges is empty */ + public static final ErrorEval NULL_INTERSECTION = new ErrorEval(EC.ERROR_NULL); + /** #DIV/0! - Division by zero */ + public static final ErrorEval DIV_ZERO = new ErrorEval(EC.ERROR_DIV_0); + /** #VALUE! - Wrong type of operand */ + public static final ErrorEval VALUE_INVALID = new ErrorEval(EC.ERROR_VALUE); + /** #REF! - Illegal or deleted cell reference */ + public static final ErrorEval REF_INVALID = new ErrorEval(EC.ERROR_REF); + /** #NAME? - Wrong function or range name */ + public static final ErrorEval NAME_INVALID = new ErrorEval(EC.ERROR_NAME); + /** #NUM! - Value range overflow */ + public static final ErrorEval NUM_ERROR = new ErrorEval(EC.ERROR_NUM); + /** #N/A - Argument or function not available */ + public static final ErrorEval NA = new ErrorEval(EC.ERROR_NA); + + + // POI internal error codes + private static final int CIRCULAR_REF_ERROR_CODE = 0xFFFFFFC4; + private static final int FUNCTION_NOT_IMPLEMENTED_CODE = 0xFFFFFFE2; + + // Note - Excel does not seem to represent this condition with an error code + public static final ErrorEval CIRCULAR_REF_ERROR = new ErrorEval(CIRCULAR_REF_ERROR_CODE); + + + /** + * Translates an Excel internal error code into the corresponding POI ErrorEval instance + * @param errorCode + */ + public static ErrorEval valueOf(int errorCode) { + switch(errorCode) { + case ErrorConstants.ERROR_NULL: return NULL_INTERSECTION; + case ErrorConstants.ERROR_DIV_0: return DIV_ZERO; + case ErrorConstants.ERROR_VALUE: return VALUE_INVALID; + case ErrorConstants.ERROR_REF: return REF_INVALID; + case ErrorConstants.ERROR_NAME: return NAME_INVALID; + case ErrorConstants.ERROR_NUM: return NUM_ERROR; + case ErrorConstants.ERROR_NA: return NA; + // non-std errors (conditions modeled as errors by POI) + case CIRCULAR_REF_ERROR_CODE: return CIRCULAR_REF_ERROR; + } + throw new RuntimeException("Unexpected error code (" + errorCode + ")"); + } + + /** + * Converts error codes to text. Handles non-standard error codes OK. + * For debug/test purposes (and for formatting error messages). + * @return the String representation of the specified Excel error code. + */ + public static String getText(int errorCode) { + if(ErrorConstants.isValidCode(errorCode)) { + return ErrorConstants.getText(errorCode); + } + // It is desirable to make these (arbitrary) strings look clearly different from any other + // value expression that might appear in a formula. In addition these error strings should + // look unlike the standard Excel errors. Hence tilde ('~') was used. + switch(errorCode) { + case CIRCULAR_REF_ERROR_CODE: return "~CIRCULAR~REF~"; + case FUNCTION_NOT_IMPLEMENTED_CODE: return "~FUNCTION~NOT~IMPLEMENTED~"; + } + return "~non~std~err(" + errorCode + ")~"; + } + + private int _errorCode; + /** + * @param errorCode an 8-bit value + */ + private ErrorEval(int errorCode) { + _errorCode = errorCode; + } + + public int getErrorCode() { + return _errorCode; + } + public String toString() { + StringBuffer sb = new StringBuffer(64); + sb.append(getClass().getName()).append(" ["); + sb.append(getText(_errorCode)); + sb.append("]"); + return sb.toString(); + } +} diff --git a/src/java/org/apache/poi/ss/formula/eval/EvaluationException.java b/src/java/org/apache/poi/ss/formula/eval/EvaluationException.java new file mode 100644 index 0000000000..bb9d9574bc --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/eval/EvaluationException.java @@ -0,0 +1,134 @@ +/* ==================================================================== + 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.ss.formula.eval; + +/** + * This class is used to simplify error handling logic within operator and function + * implementations. Note - OperationEval.evaluate() and Function.evaluate() + * method signatures do not throw this exception so it cannot propagate outside.

+ * + * Here is an example coded without EvaluationException, to show how it can help: + * 

+ * public Eval evaluate(Eval[] args, int srcRow, short srcCol) {
+ *	// ...
+ *	Eval arg0 = args[0];
+ *	if(arg0 instanceof ErrorEval) {
+ *		return arg0;
+ *	}
+ *	if(!(arg0 instanceof AreaEval)) {
+ *		return ErrorEval.VALUE_INVALID;
+ *	}
+ *	double temp = 0;
+ *	AreaEval area = (AreaEval)arg0;
+ *	ValueEval[] values = area.getValues();
+ *	for (int i = 0; i < values.length; i++) {
+ *		ValueEval ve = values[i];
+ *		if(ve instanceof ErrorEval) {
+ *			return ve;
+ *		}
+ *		if(!(ve instanceof NumericValueEval)) {
+ *			return ErrorEval.VALUE_INVALID;
+ *		}
+ *		temp += ((NumericValueEval)ve).getNumberValue();
+ *	}
+ *	// ...
+ * }	 
+ *
+ * In this example, if any error is encountered while processing the arguments, an error is + * returned immediately. This code is difficult to refactor due to all the points where errors + * are returned.
+ * Using EvaluationException allows the error returning code to be consolidated to one + * place.

+ * 

+ * public Eval evaluate(Eval[] args, int srcRow, short srcCol) {
+ *	try {
+ *		// ...
+ *		AreaEval area = getAreaArg(args[0]);
+ *		double temp = sumValues(area.getValues());
+ *		// ...
+ *	} catch (EvaluationException e) {
+ *		return e.getErrorEval();
+ *	}
+ *}
+ *
+ *private static AreaEval getAreaArg(Eval arg0) throws EvaluationException {
+ *	if (arg0 instanceof ErrorEval) {
+ *		throw new EvaluationException((ErrorEval) arg0);
+ *	}
+ *	if (arg0 instanceof AreaEval) {
+ *		return (AreaEval) arg0;
+ *	}
+ *	throw EvaluationException.invalidValue();
+ *}
+ *
+ *private double sumValues(ValueEval[] values) throws EvaluationException {
+ *	double temp = 0;
+ *	for (int i = 0; i < values.length; i++) {
+ *		ValueEval ve = values[i];
+ *		if (ve instanceof ErrorEval) {
+ *			throw new EvaluationException((ErrorEval) ve);
+ *		}
+ *		if (!(ve instanceof NumericValueEval)) {
+ *			throw EvaluationException.invalidValue();
+ *		}
+ *		temp += ((NumericValueEval) ve).getNumberValue();
+ *	}
+ *	return temp;
+ *}
+ *
+ * It is not mandatory to use EvaluationException, doing so might give the following advantages:
+ * - Methods can more easily be extracted, allowing for re-use.
+ * - Type management (typecasting etc) is simpler because error conditions have been separated from + * intermediate calculation values.
+ * - Fewer local variables are required. Local variables can have stronger types.
+ * - It is easier to mimic common Excel error handling behaviour (exit upon encountering first + * error), because exceptions conveniently propagate up the call stack regardless of execution + * points or the number of levels of nested calls.

+ * + * Note - Only standard evaluation errors are represented by EvaluationException ( + * i.e. conditions expected to be encountered when evaluating arbitrary Excel formulas). Conditions + * that could never occur in an Excel spreadsheet should result in runtime exceptions. Care should + * be taken to not translate any POI internal error into an Excel evaluation error code. + * + * @author Josh Micich + */ +public final class EvaluationException extends Exception { + private final ErrorEval _errorEval; + + public EvaluationException(ErrorEval errorEval) { + _errorEval = errorEval; + } + // some convenience factory methods + + /** #VALUE! - Wrong type of operand */ + public static EvaluationException invalidValue() { + return new EvaluationException(ErrorEval.VALUE_INVALID); + } + /** #REF! - Illegal or deleted cell reference */ + public static EvaluationException invalidRef() { + return new EvaluationException(ErrorEval.REF_INVALID); + } + /** #NUM! - Value range overflow */ + public static EvaluationException numberError() { + return new EvaluationException(ErrorEval.NUM_ERROR); + } + + public ErrorEval getErrorEval() { + return _errorEval; + } +} diff --git a/src/java/org/apache/poi/ss/formula/eval/FunctionEval.java b/src/java/org/apache/poi/ss/formula/eval/FunctionEval.java new file mode 100644 index 0000000000..85957dad48 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/eval/FunctionEval.java @@ -0,0 +1,249 @@ +/* ==================================================================== + 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.ss.formula.eval; + +import org.apache.poi.ss.formula.function.FunctionMetadata; +import org.apache.poi.ss.formula.function.FunctionMetadataRegistry; +import org.apache.poi.ss.formula.functions.*; + +/** + * @author Amol S. Deshmukh < amolweb at ya hoo dot com > + */ +public final class FunctionEval { + /** + * Some function IDs that require special treatment + */ + private static final class FunctionID { + /** 1 */ + public static final int IF = FunctionMetadataRegistry.FUNCTION_INDEX_IF; + /** 4 */ + public static final int SUM = FunctionMetadataRegistry.FUNCTION_INDEX_SUM; + /** 78 */ + public static final int OFFSET = 78; + /** 100 */ + public static final int CHOOSE = FunctionMetadataRegistry.FUNCTION_INDEX_CHOOSE; + /** 148 */ + public static final int INDIRECT = FunctionMetadataRegistry.FUNCTION_INDEX_INDIRECT; + /** 255 */ + public static final int EXTERNAL_FUNC = FunctionMetadataRegistry.FUNCTION_INDEX_EXTERNAL; + } + // convenient access to namespace + private static final FunctionID ID = null; + + /** + * Array elements corresponding to unimplemented functions are null + */ + protected static final Function[] functions = produceFunctions(); + + private static Function[] produceFunctions() { + Function[] retval = new Function[368]; + + retval[0] = new Count(); + retval[ID.IF] = new IfFunc(); + retval[2] = LogicalFunction.ISNA; + retval[3] = LogicalFunction.ISERROR; + retval[ID.SUM] = AggregateFunction.SUM; + retval[5] = AggregateFunction.AVERAGE; + retval[6] = AggregateFunction.MIN; + retval[7] = AggregateFunction.MAX; + retval[8] = new RowFunc(); // ROW + retval[9] = new Column(); + retval[10] = new Na(); + retval[11] = new Npv(); + retval[12] = AggregateFunction.STDEV; + retval[13] = NumericFunction.DOLLAR; + + retval[15] = NumericFunction.SIN; + retval[16] = NumericFunction.COS; + retval[17] = NumericFunction.TAN; + retval[18] = NumericFunction.ATAN; + retval[19] = NumericFunction.PI; + retval[20] = NumericFunction.SQRT; + retval[21] = NumericFunction.EXP; + retval[22] = NumericFunction.LN; + retval[23] = NumericFunction.LOG10; + retval[24] = NumericFunction.ABS; + retval[25] = NumericFunction.INT; + retval[26] = NumericFunction.SIGN; + retval[27] = NumericFunction.ROUND; + retval[28] = new Lookup(); + retval[29] = new Index(); + + retval[31] = TextFunction.MID; + retval[32] = TextFunction.LEN; + retval[33] = new Value(); + retval[34] = BooleanFunction.TRUE; + retval[35] = BooleanFunction.FALSE; + retval[36] = BooleanFunction.AND; + retval[37] = BooleanFunction.OR; + retval[38] = BooleanFunction.NOT; + retval[39] = NumericFunction.MOD; + retval[48] = TextFunction.TEXT; + + retval[56] = FinanceFunction.PV; + retval[57] = FinanceFunction.FV; + retval[58] = FinanceFunction.NPER; + retval[59] = FinanceFunction.PMT; + + retval[63] = NumericFunction.RAND; + retval[64] = new Match(); + retval[65] = DateFunc.instance; + retval[66] = new TimeFunc(); + retval[67] = CalendarFieldFunction.DAY; + retval[68] = CalendarFieldFunction.MONTH; + retval[69] = CalendarFieldFunction.YEAR; + + retval[74] = new Now(); + + retval[76] = new Rows(); + retval[77] = new Columns(); + retval[82] = TextFunction.SEARCH; + retval[ID.OFFSET] = new Offset(); + retval[82] = TextFunction.SEARCH; + + retval[97] = NumericFunction.ATAN2; + retval[98] = NumericFunction.ASIN; + retval[99] = NumericFunction.ACOS; + retval[ID.CHOOSE] = new Choose(); + retval[101] = new Hlookup(); + retval[102] = new Vlookup(); + + retval[105] = LogicalFunction.ISREF; + + retval[109] = NumericFunction.LOG; + + retval[112] = TextFunction.LOWER; + retval[113] = TextFunction.UPPER; + + retval[115] = TextFunction.LEFT; + retval[116] = TextFunction.RIGHT; + retval[117] = TextFunction.EXACT; + retval[118] = TextFunction.TRIM; + retval[119] = new Replace(); + retval[120] = new Substitute(); + + retval[124] = TextFunction.FIND; + + retval[127] = LogicalFunction.ISTEXT; + retval[128] = LogicalFunction.ISNUMBER; + retval[129] = LogicalFunction.ISBLANK; + retval[130] = new T(); + + retval[ID.INDIRECT] = null; // Indirect.evaluate has different signature + + retval[169] = new Counta(); + + retval[183] = AggregateFunction.PRODUCT; + retval[184] = NumericFunction.FACT; + + retval[190] = LogicalFunction.ISNONTEXT; + retval[197] = NumericFunction.TRUNC; + retval[198] = LogicalFunction.ISLOGICAL; + + retval[212] = NumericFunction.ROUNDUP; + retval[213] = NumericFunction.ROUNDDOWN; + + retval[220] = new Days360(); + retval[221] = new Today(); + + retval[227] = AggregateFunction.MEDIAN; + retval[228] = new Sumproduct(); + retval[229] = NumericFunction.SINH; + retval[230] = NumericFunction.COSH; + retval[231] = NumericFunction.TANH; + retval[232] = NumericFunction.ASINH; + retval[233] = NumericFunction.ACOSH; + retval[234] = NumericFunction.ATANH; + + retval[ID.EXTERNAL_FUNC] = null; // ExternalFunction is a FreeREfFunction + + retval[261] = new Errortype(); + + retval[269] = AggregateFunction.AVEDEV; + + retval[276] = NumericFunction.COMBIN; + + retval[279] = new Even(); + + retval[285] = NumericFunction.FLOOR; + + retval[288] = NumericFunction.CEILING; + + retval[298] = new Odd(); + + retval[300] = NumericFunction.POISSON; + + retval[303] = new Sumxmy2(); + retval[304] = new Sumx2my2(); + retval[305] = new Sumx2py2(); + + retval[318] = AggregateFunction.DEVSQ; + + retval[321] = AggregateFunction.SUMSQ; + + retval[325] = AggregateFunction.LARGE; + retval[326] = AggregateFunction.SMALL; + + retval[330] = new Mode(); + + retval[336] = TextFunction.CONCATENATE; + retval[337] = NumericFunction.POWER; + + retval[342] = NumericFunction.RADIANS; + retval[343] = NumericFunction.DEGREES; + + retval[344] = new Subtotal(); + retval[345] = new Sumif(); + retval[346] = new Countif(); + retval[347] = new Countblank(); + + retval[359] = new Hyperlink(); + + retval[362] = MinaMaxa.MAXA; + retval[363] = MinaMaxa.MINA; + + for (int i = 0; i < retval.length; i++) { + Function f = retval[i]; + if (f == null) { + FunctionMetadata fm = FunctionMetadataRegistry.getFunctionByIndex(i); + if (fm == null) { + continue; + } + retval[i] = new NotImplementedFunction(fm.getName()); + } + } + return retval; + } + /** + * @return null if the specified functionIndex is for INDIRECT() or any external (add-in) function. + */ + public static Function getBasicFunction(int functionIndex) { + // check for 'free ref' functions first + switch (functionIndex) { + case FunctionID.INDIRECT: + case FunctionID.EXTERNAL_FUNC: + return null; + } + // else - must be plain function + Function result = functions[functionIndex]; + if (result == null) { + throw new NotImplementedException("FuncIx=" + functionIndex); + } + return result; + } +} diff --git a/src/java/org/apache/poi/ss/formula/eval/IntersectionEval.java b/src/java/org/apache/poi/ss/formula/eval/IntersectionEval.java new file mode 100644 index 0000000000..4063790c73 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/eval/IntersectionEval.java @@ -0,0 +1,96 @@ +/* ==================================================================== + 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.ss.formula.eval; + +import org.apache.poi.ss.formula.functions.Fixed2ArgFunction; +import org.apache.poi.ss.formula.functions.Function; + +/** + * @author Josh Micich + */ +public final class IntersectionEval extends Fixed2ArgFunction { + + public static final Function instance = new IntersectionEval(); + + private IntersectionEval() { + // enforces singleton + } + + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1) { + + try { + AreaEval reA = evaluateRef(arg0); + AreaEval reB = evaluateRef(arg1); + AreaEval result = resolveRange(reA, reB); + if (result == null) { + return ErrorEval.NULL_INTERSECTION; + } + return result; + } catch (EvaluationException e) { + return e.getErrorEval(); + } + } + + /** + * @return simple rectangular {@link AreaEval} which represents the intersection of areas + * aeA and aeB. If the two areas do not intersect, the result is null. + */ + private static AreaEval resolveRange(AreaEval aeA, AreaEval aeB) { + + int aeAfr = aeA.getFirstRow(); + int aeAfc = aeA.getFirstColumn(); + int aeBlc = aeB.getLastColumn(); + if (aeAfc > aeBlc) { + return null; + } + int aeBfc = aeB.getFirstColumn(); + if (aeBfc > aeA.getLastColumn()) { + return null; + } + int aeBlr = aeB.getLastRow(); + if (aeAfr > aeBlr) { + return null; + } + int aeBfr = aeB.getFirstRow(); + int aeAlr = aeA.getLastRow(); + if (aeBfr > aeAlr) { + return null; + } + + + int top = Math.max(aeAfr, aeBfr); + int bottom = Math.min(aeAlr, aeBlr); + int left = Math.max(aeAfc, aeBfc); + int right = Math.min(aeA.getLastColumn(), aeBlc); + + return aeA.offset(top-aeAfr, bottom-aeAfr, left-aeAfc, right-aeAfc); + } + + private static AreaEval evaluateRef(ValueEval arg) throws EvaluationException { + if (arg instanceof AreaEval) { + return (AreaEval) arg; + } + if (arg instanceof RefEval) { + return ((RefEval) arg).offset(0, 0, 0, 0); + } + if (arg instanceof ErrorEval) { + throw new EvaluationException((ErrorEval)arg); + } + throw new IllegalArgumentException("Unexpected ref arg class (" + arg.getClass().getName() + ")"); + } +} diff --git a/src/java/org/apache/poi/ss/formula/eval/MissingArgEval.java b/src/java/org/apache/poi/ss/formula/eval/MissingArgEval.java new file mode 100644 index 0000000000..70d25040f4 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/eval/MissingArgEval.java @@ -0,0 +1,36 @@ +/* ==================================================================== + 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.ss.formula.eval; + +/** + * Represents the (intermediate) evaluated result of a missing function argument. In most cases + * this can be translated into {@link BlankEval} but there are some notable exceptions. Functions + * COUNT and COUNTA do count their missing args. Note - the differences between + * {@link MissingArgEval} and {@link BlankEval} have not been investigated fully, so the POI + * evaluator may need to be updated to account for these as they are found. + * + * @author Josh Micich + */ +public final class MissingArgEval implements ValueEval { + + public static final MissingArgEval instance = new MissingArgEval(); + + private MissingArgEval() { + // enforce singleton + } +} diff --git a/src/java/org/apache/poi/ss/formula/eval/NameEval.java b/src/java/org/apache/poi/ss/formula/eval/NameEval.java new file mode 100644 index 0000000000..decefb9366 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/eval/NameEval.java @@ -0,0 +1,46 @@ +/* ==================================================================== + 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.ss.formula.eval; + +/** + * @author Josh Micich + */ +public final class NameEval implements ValueEval { + + private final String _functionName; + + /** + * Creates a NameEval representing a function name + */ + public NameEval(String functionName) { + _functionName = functionName; + } + + + public String getFunctionName() { + return _functionName; + } + + public String toString() { + StringBuffer sb = new StringBuffer(64); + sb.append(getClass().getName()).append(" ["); + sb.append(_functionName); + sb.append("]"); + return sb.toString(); + } +} diff --git a/src/java/org/apache/poi/ss/formula/eval/NameXEval.java b/src/java/org/apache/poi/ss/formula/eval/NameXEval.java new file mode 100644 index 0000000000..951d4619be --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/eval/NameXEval.java @@ -0,0 +1,44 @@ +/* ==================================================================== + 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.ss.formula.eval; + +import org.apache.poi.hssf.record.formula.NameXPtg; + +/** + * @author Josh Micich + */ +public final class NameXEval implements ValueEval { + + private final NameXPtg _ptg; + + public NameXEval(NameXPtg ptg) { + _ptg = ptg; + } + + public NameXPtg getPtg() { + return _ptg; + } + + public String toString() { + StringBuffer sb = new StringBuffer(64); + sb.append(getClass().getName()).append(" ["); + sb.append(_ptg.getSheetRefIndex()).append(", ").append(_ptg.getNameIndex()); + sb.append("]"); + return sb.toString(); + } +} diff --git a/src/java/org/apache/poi/ss/formula/eval/NumberEval.java b/src/java/org/apache/poi/ss/formula/eval/NumberEval.java new file mode 100644 index 0000000000..e500f707ce --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/eval/NumberEval.java @@ -0,0 +1,73 @@ +/* +* 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. +*/ +/* + * Created on May 8, 2005 + * + */ +package org.apache.poi.ss.formula.eval; + +import org.apache.poi.hssf.record.formula.IntPtg; +import org.apache.poi.hssf.record.formula.NumberPtg; +import org.apache.poi.hssf.record.formula.Ptg; +import org.apache.poi.ss.util.NumberToTextConverter; + +/** + * @author Amol S. Deshmukh < amolweb at ya hoo dot com > + * + */ +public final class NumberEval implements NumericValueEval, StringValueEval { + + public static final NumberEval ZERO = new NumberEval(0); + + private final double _value; + private String _stringValue; + + public NumberEval(Ptg ptg) { + if (ptg == null) { + throw new IllegalArgumentException("ptg must not be null"); + } + if (ptg instanceof IntPtg) { + _value = ((IntPtg) ptg).getValue(); + } else if (ptg instanceof NumberPtg) { + _value = ((NumberPtg) ptg).getValue(); + } else { + throw new IllegalArgumentException("bad argument type (" + ptg.getClass().getName() + ")"); + } + } + + public NumberEval(double value) { + _value = value; + } + + public double getNumberValue() { + return _value; + } + + public String getStringValue() { + if (_stringValue == null) { + _stringValue = NumberToTextConverter.toText(_value); + } + return _stringValue; + } + public final String toString() { + StringBuffer sb = new StringBuffer(64); + sb.append(getClass().getName()).append(" ["); + sb.append(getStringValue()); + sb.append("]"); + return sb.toString(); + } +} diff --git a/src/java/org/apache/poi/ss/formula/eval/NumericValueEval.java b/src/java/org/apache/poi/ss/formula/eval/NumericValueEval.java new file mode 100644 index 0000000000..056f21cdf7 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/eval/NumericValueEval.java @@ -0,0 +1,30 @@ +/* +* 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. +*/ +/* + * Created on May 8, 2005 + * + */ +package org.apache.poi.ss.formula.eval; + +/** + * @author Amol S. Deshmukh < amolweb at ya hoo dot com > + * + */ +public interface NumericValueEval extends ValueEval { + + public abstract double getNumberValue(); +} diff --git a/src/java/org/apache/poi/ss/formula/eval/OperandResolver.java b/src/java/org/apache/poi/ss/formula/eval/OperandResolver.java new file mode 100644 index 0000000000..eea1a3828c --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/eval/OperandResolver.java @@ -0,0 +1,324 @@ +/* ==================================================================== + 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.ss.formula.eval; + +import java.util.regex.Pattern; + +/** + * Provides functionality for evaluating arguments to functions and operators. + * + * @author Josh Micich + * @author Brendan Nolan + */ +public final class OperandResolver { + + // Based on regular expression defined in JavaDoc at {@link java.lang.Double#valueOf} + // modified to remove support for NaN, Infinity, Hexadecimal support and floating type suffixes + private static final String Digits = "(\\p{Digit}+)"; + private static final String Exp = "[eE][+-]?"+Digits; + private static final String fpRegex = + ("[\\x00-\\x20]*" + + "[+-]?(" + + "((("+Digits+"(\\.)?("+Digits+"?)("+Exp+")?)|"+ + "(\\.("+Digits+")("+Exp+")?))))"+ + "[\\x00-\\x20]*"); + + + private OperandResolver() { + // no instances of this class + } + + /** + * Retrieves a single value from a variety of different argument types according to standard + * Excel rules. Does not perform any type conversion. + * @param arg the evaluated argument as passed to the function or operator. + * @param srcCellRow used when arg is a single column AreaRef + * @param srcCellCol used when arg is a single row AreaRef + * @return a NumberEval, StringEval, BoolEval or BlankEval. + * Never null or ErrorEval. + * @throws EvaluationException(#VALUE!) if srcCellRow or srcCellCol do not properly index into + * an AreaEval. If the actual value retrieved is an ErrorEval, a corresponding + * EvaluationException is thrown. + */ + public static ValueEval getSingleValue(ValueEval arg, int srcCellRow, int srcCellCol) + throws EvaluationException { + ValueEval result; + if (arg instanceof RefEval) { + result = ((RefEval) arg).getInnerValueEval(); + } else if (arg instanceof AreaEval) { + result = chooseSingleElementFromArea((AreaEval) arg, srcCellRow, srcCellCol); + } else { + result = arg; + } + if (result instanceof ErrorEval) { + throw new EvaluationException((ErrorEval) result); + } + return result; + } + + /** + * Implements (some perhaps not well known) Excel functionality to select a single cell from an + * area depending on the coordinates of the calling cell. Here is an example demonstrating + * both selection from a single row area and a single column area in the same formula. + * + * + * + * + * + * + * + *
  A  B  C  D 
1152025 
2   200
3   300
3   400
+ * + * If the formula "=1000+A1:B1+D2:D3" is put into the 9 cells from A2 to C4, the spreadsheet + * will look like this: + * + * + * + * + * + * + * + *
  A  B  C  D 
1152025 
212151220#VALUE!200
313151320#VALUE!300
4#VALUE!#VALUE!#VALUE!400
+ * + * Note that the row area (A1:B1) does not include column C and the column area (D2:D3) does + * not include row 4, so the values in C1(=25) and D4(=400) are not accessible to the formula + * as written, but in the 4 cells A2:B3, the row and column selection works ok.

+ * + * The same concept is extended to references across sheets, such that even multi-row, + * multi-column areas can be useful.

+ * + * Of course with carefully (or carelessly) chosen parameters, cyclic references can occur and + * hence this method can throw a 'circular reference' EvaluationException. Note that + * this method does not attempt to detect cycles. Every cell in the specified Area ae + * has already been evaluated prior to this method call. Any cell (or cells) part of + * ae that would incur a cyclic reference error if selected by this method, will + * already have the value ErrorEval.CIRCULAR_REF_ERROR upon entry to this method. It + * is assumed logic exists elsewhere to produce this behaviour. + * + * @return whatever the selected cell's evaluated value is. Never null. Never + * ErrorEval. + * @throws EvaluationException if there is a problem with indexing into the area, or if the + * evaluated cell has an error. + */ + public static ValueEval chooseSingleElementFromArea(AreaEval ae, + int srcCellRow, int srcCellCol) throws EvaluationException { + ValueEval result = chooseSingleElementFromAreaInternal(ae, srcCellRow, srcCellCol); + if (result instanceof ErrorEval) { + throw new EvaluationException((ErrorEval) result); + } + return result; + } + + /** + * @return possibly ErrorEval, and null + */ + private static ValueEval chooseSingleElementFromAreaInternal(AreaEval ae, + int srcCellRow, int srcCellCol) throws EvaluationException { + + if(false) { + // this is too simplistic + if(ae.containsRow(srcCellRow) && ae.containsColumn(srcCellCol)) { + throw new EvaluationException(ErrorEval.CIRCULAR_REF_ERROR); + } + /* + Circular references are not dealt with directly here, but it is worth noting some issues. + + ANY one of the return statements in this method could return a cell that is identical + to the one immediately being evaluated. The evaluating cell is identified by srcCellRow, + srcCellRow AND sheet. The sheet is not available in any nearby calling method, so that's + one reason why circular references are not easy to detect here. (The sheet of the returned + cell can be obtained from ae if it is an Area3DEval.) + + Another reason there's little value in attempting to detect circular references here is + that only direct circular references could be detected. If the cycle involved two or more + cells this method could not detect it. + + Logic to detect evaluation cycles of all kinds has been coded in EvaluationCycleDetector + (and FormulaEvaluator). + */ + } + + if (ae.isColumn()) { + if(ae.isRow()) { + return ae.getRelativeValue(0, 0); + } + if(!ae.containsRow(srcCellRow)) { + throw EvaluationException.invalidValue(); + } + return ae.getAbsoluteValue(srcCellRow, ae.getFirstColumn()); + } + if(!ae.isRow()) { + // multi-column, multi-row area + if(ae.containsRow(srcCellRow) && ae.containsColumn(srcCellCol)) { + return ae.getAbsoluteValue(ae.getFirstRow(), ae.getFirstColumn()); + } + throw EvaluationException.invalidValue(); + } + if(!ae.containsColumn(srcCellCol)) { + throw EvaluationException.invalidValue(); + } + return ae.getAbsoluteValue(ae.getFirstRow(), srcCellCol); + } + + /** + * Applies some conversion rules if the supplied value is not already an integer.
+ * Value is first coerced to a double ( See coerceValueToDouble() ). + * Note - BlankEval is converted to 0.

+ * + * Excel typically converts doubles to integers by truncating toward negative infinity.
+ * The equivalent java code is:
+ *   return (int)Math.floor(d);
+ * not:
+ *   return (int)d; // wrong - rounds toward zero + * + */ + public static int coerceValueToInt(ValueEval ev) throws EvaluationException { + if (ev == BlankEval.instance) { + return 0; + } + double d = coerceValueToDouble(ev); + // Note - the standard java type conversion from double to int truncates toward zero. + // but Math.floor() truncates toward negative infinity + return (int)Math.floor(d); + } + + /** + * Applies some conversion rules if the supplied value is not already a number. + * Note - BlankEval is converted to {@link NumberEval#ZERO}. + * @param ev must be a {@link NumberEval}, {@link StringEval}, {@link BoolEval} or + * {@link BlankEval} + * @return actual, parsed or interpreted double value (respectively). + * @throws EvaluationException(#VALUE!) only if a StringEval is supplied and cannot be parsed + * as a double (See parseDouble() for allowable formats). + * @throws RuntimeException if the supplied parameter is not {@link NumberEval}, + * {@link StringEval}, {@link BoolEval} or {@link BlankEval} + */ + public static double coerceValueToDouble(ValueEval ev) throws EvaluationException { + + if (ev == BlankEval.instance) { + return 0.0; + } + if (ev instanceof NumericValueEval) { + // this also handles booleans + return ((NumericValueEval)ev).getNumberValue(); + } + if (ev instanceof StringEval) { + Double dd = parseDouble(((StringEval) ev).getStringValue()); + if (dd == null) { + throw EvaluationException.invalidValue(); + } + return dd.doubleValue(); + } + throw new RuntimeException("Unexpected arg eval type (" + ev.getClass().getName() + ")"); + } + + /** + * Converts a string to a double using standard rules that Excel would use.
+ * Tolerates leading and trailing spaces,

+ * + * Doesn't support currency prefixes, commas, percentage signs or arithmetic operations strings. + * + * Some examples:
+ * " 123 " -> 123.0
+ * ".123" -> 0.123
+ * "1E4" -> 1000
+ * "-123" -> -123.0
+ * These not supported yet:
+ * " $ 1,000.00 " -> 1000.0
+ * "$1.25E4" -> 12500.0
+ * "5**2" -> 500
+ * "250%" -> 2.5
+ * + * @return null if the specified text cannot be parsed as a number + */ + public static Double parseDouble(String pText) { + + if (Pattern.matches(fpRegex, pText)) + try { + return Double.parseDouble(pText); + } catch (NumberFormatException e) { + return null; + } + else { + return null; + } + + } + + /** + * @param ve must be a NumberEval, StringEval, BoolEval, or BlankEval + * @return the converted string value. never null + */ + public static String coerceValueToString(ValueEval ve) { + if (ve instanceof StringValueEval) { + StringValueEval sve = (StringValueEval) ve; + return sve.getStringValue(); + } + if (ve == BlankEval.instance) { + return ""; + } + throw new IllegalArgumentException("Unexpected eval class (" + ve.getClass().getName() + ")"); + } + + /** + * @return null to represent blank values + * @throws EvaluationException if ve is an ErrorEval, or if a string value cannot be converted + */ + public static Boolean coerceValueToBoolean(ValueEval ve, boolean stringsAreBlanks) throws EvaluationException { + + if (ve == null || ve == BlankEval.instance) { + // TODO - remove 've == null' condition once AreaEval is fixed + return null; + } + if (ve instanceof BoolEval) { + return Boolean.valueOf(((BoolEval) ve).getBooleanValue()); + } + + if (ve == BlankEval.instance) { + return null; + } + + if (ve instanceof StringEval) { + if (stringsAreBlanks) { + return null; + } + String str = ((StringEval) ve).getStringValue(); + if (str.equalsIgnoreCase("true")) { + return Boolean.TRUE; + } + if (str.equalsIgnoreCase("false")) { + return Boolean.FALSE; + } + // else - string cannot be converted to boolean + throw new EvaluationException(ErrorEval.VALUE_INVALID); + } + + if (ve instanceof NumericValueEval) { + NumericValueEval ne = (NumericValueEval) ve; + double d = ne.getNumberValue(); + if (Double.isNaN(d)) { + throw new EvaluationException(ErrorEval.VALUE_INVALID); + } + return Boolean.valueOf(d != 0); + } + if (ve instanceof ErrorEval) { + throw new EvaluationException((ErrorEval) ve); + } + throw new RuntimeException("Unexpected eval (" + ve.getClass().getName() + ")"); + } +} diff --git a/src/java/org/apache/poi/ss/formula/eval/PercentEval.java b/src/java/org/apache/poi/ss/formula/eval/PercentEval.java new file mode 100644 index 0000000000..263574a406 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/eval/PercentEval.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.ss.formula.eval; + +import org.apache.poi.ss.formula.functions.Fixed1ArgFunction; +import org.apache.poi.ss.formula.functions.Function; + + +/** + * Implementation of Excel formula token '%'.

+ * @author Josh Micich + */ +public final class PercentEval extends Fixed1ArgFunction { + + public static final Function instance = new PercentEval(); + + private PercentEval() { + // enforce singleton + } + + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0) { + double d; + try { + ValueEval ve = OperandResolver.getSingleValue(arg0, srcRowIndex, srcColumnIndex); + d = OperandResolver.coerceValueToDouble(ve); + } catch (EvaluationException e) { + return e.getErrorEval(); + } + if (d == 0.0) { // this '==' matches +0.0 and -0.0 + return NumberEval.ZERO; + } + return new NumberEval(d / 100); + } +} diff --git a/src/java/org/apache/poi/ss/formula/eval/RangeEval.java b/src/java/org/apache/poi/ss/formula/eval/RangeEval.java new file mode 100644 index 0000000000..617d20a765 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/eval/RangeEval.java @@ -0,0 +1,75 @@ +/* ==================================================================== + 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.ss.formula.eval; + +import org.apache.poi.ss.formula.functions.Fixed2ArgFunction; +import org.apache.poi.ss.formula.functions.Function; + + +/** + * + * @author Josh Micich + */ +public final class RangeEval extends Fixed2ArgFunction { + + public static final Function instance = new RangeEval(); + + private RangeEval() { + // enforces singleton + } + + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1) { + + try { + AreaEval reA = evaluateRef(arg0); + AreaEval reB = evaluateRef(arg1); + return resolveRange(reA, reB); + } catch (EvaluationException e) { + return e.getErrorEval(); + } + } + + /** + * @return simple rectangular {@link AreaEval} which fully encloses both areas + * aeA and aeB + */ + private static AreaEval resolveRange(AreaEval aeA, AreaEval aeB) { + int aeAfr = aeA.getFirstRow(); + int aeAfc = aeA.getFirstColumn(); + + int top = Math.min(aeAfr, aeB.getFirstRow()); + int bottom = Math.max(aeA.getLastRow(), aeB.getLastRow()); + int left = Math.min(aeAfc, aeB.getFirstColumn()); + int right = Math.max(aeA.getLastColumn(), aeB.getLastColumn()); + + return aeA.offset(top-aeAfr, bottom-aeAfr, left-aeAfc, right-aeAfc); + } + + private static AreaEval evaluateRef(ValueEval arg) throws EvaluationException { + if (arg instanceof AreaEval) { + return (AreaEval) arg; + } + if (arg instanceof RefEval) { + return ((RefEval) arg).offset(0, 0, 0, 0); + } + if (arg instanceof ErrorEval) { + throw new EvaluationException((ErrorEval)arg); + } + throw new IllegalArgumentException("Unexpected ref arg class (" + arg.getClass().getName() + ")"); + } +} diff --git a/src/java/org/apache/poi/ss/formula/eval/RefEval.java b/src/java/org/apache/poi/ss/formula/eval/RefEval.java new file mode 100644 index 0000000000..768e90a8a5 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/eval/RefEval.java @@ -0,0 +1,51 @@ +/* ==================================================================== + 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.ss.formula.eval; + +/** + * @author Amol S Deshmukh < amolweb at ya hoo dot com > + * + * RefEval is the super interface for Ref2D and Ref3DEval. Basically a RefEval + * impl should contain reference to the original ReferencePtg or Ref3DPtg as + * well as the final "value" resulting from the evaluation of the cell + * reference. Thus if the Cell has type CELL_TYPE_NUMERIC, the contained + * value object should be of type NumberEval; if cell type is CELL_TYPE_STRING, + * contained value object should be of type StringEval + */ +public interface RefEval extends ValueEval { + + /** + * @return the evaluated value of the cell referred to by this RefEval. + */ + ValueEval getInnerValueEval(); + + /** + * returns the zero based column index. + */ + int getColumn(); + + /** + * returns the zero based row index. + */ + int getRow(); + + /** + * Creates an {@link AreaEval} offset by a relative amount from this RefEval + */ + AreaEval offset(int relFirstRowIx, int relLastRowIx, int relFirstColIx, int relLastColIx); +} diff --git a/src/java/org/apache/poi/ss/formula/eval/RefEvalBase.java b/src/java/org/apache/poi/ss/formula/eval/RefEvalBase.java new file mode 100644 index 0000000000..83d20fb49a --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/eval/RefEvalBase.java @@ -0,0 +1,40 @@ +/* ==================================================================== + 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.ss.formula.eval; + +/** + * Common base class for implementors of {@link RefEval} + * + * @author Josh Micich + */ +public abstract class RefEvalBase implements RefEval { + + private final int _rowIndex; + private final int _columnIndex; + + protected RefEvalBase(int rowIndex, int columnIndex) { + _rowIndex = rowIndex; + _columnIndex = columnIndex; + } + public final int getRow() { + return _rowIndex; + } + public final int getColumn() { + return _columnIndex; + } +} diff --git a/src/java/org/apache/poi/ss/formula/eval/RelationalOperationEval.java b/src/java/org/apache/poi/ss/formula/eval/RelationalOperationEval.java new file mode 100644 index 0000000000..8b3be19199 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/eval/RelationalOperationEval.java @@ -0,0 +1,168 @@ +/* ==================================================================== + 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.ss.formula.eval; + +import org.apache.poi.ss.formula.functions.Fixed2ArgFunction; +import org.apache.poi.ss.formula.functions.Function; +import org.apache.poi.ss.util.NumberComparer; + +/** + * Base class for all comparison operator evaluators + * + * @author Amol S. Deshmukh < amolweb at ya hoo dot com > + */ +public abstract class RelationalOperationEval extends Fixed2ArgFunction { + + /** + * Converts a standard compare result (-1, 0, 1) to true or false + * according to subclass' comparison type. + */ + protected abstract boolean convertComparisonResult(int cmpResult); + + /** + * This is a description of how the relational operators apply in MS Excel. + * Use this as a guideline when testing/implementing the evaluate methods + * for the relational operators Evals. + * + * 

+	 * Bool.TRUE > any number.
+	 * Bool > any string. ALWAYS
+	 * Bool.TRUE > Bool.FALSE
+	 * Bool.FALSE == Blank
+	 *
+	 * Strings are never converted to numbers or booleans
+	 * String > any number. ALWAYS
+	 * Non-empty String > Blank
+	 * Empty String == Blank
+	 * String are sorted dictionary wise
+	 *
+	 * Blank > Negative numbers
+	 * Blank == 0
+	 * Blank < Positive numbers
+	 *
+ */ + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1) { + + ValueEval vA; + ValueEval vB; + try { + vA = OperandResolver.getSingleValue(arg0, srcRowIndex, srcColumnIndex); + vB = OperandResolver.getSingleValue(arg1, srcRowIndex, srcColumnIndex); + } catch (EvaluationException e) { + return e.getErrorEval(); + } + int cmpResult = doCompare(vA, vB); + boolean result = convertComparisonResult(cmpResult); + return BoolEval.valueOf(result); + } + + private static int doCompare(ValueEval va, ValueEval vb) { + // special cases when one operand is blank + if (va == BlankEval.instance) { + return compareBlank(vb); + } + if (vb == BlankEval.instance) { + return -compareBlank(va); + } + + if (va instanceof BoolEval) { + if (vb instanceof BoolEval) { + BoolEval bA = (BoolEval) va; + BoolEval bB = (BoolEval) vb; + if (bA.getBooleanValue() == bB.getBooleanValue()) { + return 0; + } + return bA.getBooleanValue() ? 1 : -1; + } + return 1; + } + if (vb instanceof BoolEval) { + return -1; + } + if (va instanceof StringEval) { + if (vb instanceof StringEval) { + StringEval sA = (StringEval) va; + StringEval sB = (StringEval) vb; + return sA.getStringValue().compareToIgnoreCase(sB.getStringValue()); + } + return 1; + } + if (vb instanceof StringEval) { + return -1; + } + if (va instanceof NumberEval) { + if (vb instanceof NumberEval) { + NumberEval nA = (NumberEval) va; + NumberEval nB = (NumberEval) vb; + return NumberComparer.compare(nA.getNumberValue(), nB.getNumberValue()); + } + } + throw new IllegalArgumentException("Bad operand types (" + va.getClass().getName() + "), (" + + vb.getClass().getName() + ")"); + } + + private static int compareBlank(ValueEval v) { + if (v == BlankEval.instance) { + return 0; + } + if (v instanceof BoolEval) { + BoolEval boolEval = (BoolEval) v; + return boolEval.getBooleanValue() ? -1 : 0; + } + if (v instanceof NumberEval) { + NumberEval ne = (NumberEval) v; + return NumberComparer.compare(0.0, ne.getNumberValue()); + } + if (v instanceof StringEval) { + StringEval se = (StringEval) v; + return se.getStringValue().length() < 1 ? 0 : -1; + } + throw new IllegalArgumentException("bad value class (" + v.getClass().getName() + ")"); + } + + public static final Function EqualEval = new RelationalOperationEval() { + protected boolean convertComparisonResult(int cmpResult) { + return cmpResult == 0; + } + }; + public static final Function GreaterEqualEval = new RelationalOperationEval() { + protected boolean convertComparisonResult(int cmpResult) { + return cmpResult >= 0; + } + }; + public static final Function GreaterThanEval = new RelationalOperationEval() { + protected boolean convertComparisonResult(int cmpResult) { + return cmpResult > 0; + } + }; + public static final Function LessEqualEval = new RelationalOperationEval() { + protected boolean convertComparisonResult(int cmpResult) { + return cmpResult <= 0; + } + }; + public static final Function LessThanEval = new RelationalOperationEval() { + protected boolean convertComparisonResult(int cmpResult) { + return cmpResult < 0; + } + }; + public static final Function NotEqualEval = new RelationalOperationEval() { + protected boolean convertComparisonResult(int cmpResult) { + return cmpResult != 0; + } + }; +} diff --git a/src/java/org/apache/poi/ss/formula/eval/StringEval.java b/src/java/org/apache/poi/ss/formula/eval/StringEval.java new file mode 100644 index 0000000000..b2596fa105 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/eval/StringEval.java @@ -0,0 +1,54 @@ +/* ==================================================================== + 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.ss.formula.eval; + +import org.apache.poi.hssf.record.formula.Ptg; +import org.apache.poi.hssf.record.formula.StringPtg; + +/** + * @author Amol S. Deshmukh < amolweb at ya hoo dot com > + */ +public final class StringEval implements StringValueEval { + + public static final StringEval EMPTY_INSTANCE = new StringEval(""); + + private final String _value; + + public StringEval(Ptg ptg) { + this(((StringPtg) ptg).getValue()); + } + + public StringEval(String value) { + if (value == null) { + throw new IllegalArgumentException("value must not be null"); + } + _value = value; + } + + public String getStringValue() { + return _value; + } + + public String toString() { + StringBuilder sb = new StringBuilder(64); + sb.append(getClass().getName()).append(" ["); + sb.append(_value); + sb.append("]"); + return sb.toString(); + } +} diff --git a/src/java/org/apache/poi/ss/formula/eval/StringValueEval.java b/src/java/org/apache/poi/ss/formula/eval/StringValueEval.java new file mode 100644 index 0000000000..4e1b712fbc --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/eval/StringValueEval.java @@ -0,0 +1,30 @@ +/* +* 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.ss.formula.eval; + +/** + * @author Amol S. Deshmukh < amolweb at ya hoo dot com > + * + */ +public interface StringValueEval extends ValueEval { + + /** + * @return never null, possibly empty string. + */ + String getStringValue(); +} diff --git a/src/java/org/apache/poi/ss/formula/eval/TwoOperandNumericOperation.java b/src/java/org/apache/poi/ss/formula/eval/TwoOperandNumericOperation.java new file mode 100644 index 0000000000..a4c05d96f0 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/eval/TwoOperandNumericOperation.java @@ -0,0 +1,87 @@ +/* ==================================================================== + 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.ss.formula.eval; + +import org.apache.poi.ss.formula.functions.Fixed2ArgFunction; +import org.apache.poi.ss.formula.functions.Function; + +/** + * @author Josh Micich + */ +public abstract class TwoOperandNumericOperation extends Fixed2ArgFunction { + + protected final double singleOperandEvaluate(ValueEval arg, int srcCellRow, int srcCellCol) throws EvaluationException { + ValueEval ve = OperandResolver.getSingleValue(arg, srcCellRow, srcCellCol); + return OperandResolver.coerceValueToDouble(ve); + } + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1) { + double result; + try { + double d0 = singleOperandEvaluate(arg0, srcRowIndex, srcColumnIndex); + double d1 = singleOperandEvaluate(arg1, srcRowIndex, srcColumnIndex); + result = evaluate(d0, d1); + if (result == 0.0) { // this '==' matches +0.0 and -0.0 + // Excel converts -0.0 to +0.0 for '*', '/', '%', '+' and '^' + if (!(this instanceof SubtractEvalClass)) { + return NumberEval.ZERO; + } + } + if (Double.isNaN(result) || Double.isInfinite(result)) { + return ErrorEval.NUM_ERROR; + } + } catch (EvaluationException e) { + return e.getErrorEval(); + } + return new NumberEval(result); + } + + protected abstract double evaluate(double d0, double d1) throws EvaluationException; + + public static final Function AddEval = new TwoOperandNumericOperation() { + protected double evaluate(double d0, double d1) { + return d0+d1; + } + }; + public static final Function DivideEval = new TwoOperandNumericOperation() { + protected double evaluate(double d0, double d1) throws EvaluationException { + if (d1 == 0.0) { + throw new EvaluationException(ErrorEval.DIV_ZERO); + } + return d0/d1; + } + }; + public static final Function MultiplyEval = new TwoOperandNumericOperation() { + protected double evaluate(double d0, double d1) { + return d0*d1; + } + }; + public static final Function PowerEval = new TwoOperandNumericOperation() { + protected double evaluate(double d0, double d1) { + return Math.pow(d0, d1); + } + }; + private static final class SubtractEvalClass extends TwoOperandNumericOperation { + public SubtractEvalClass() { + // + } + protected double evaluate(double d0, double d1) { + return d0-d1; + } + } + public static final Function SubtractEval = new SubtractEvalClass(); +} diff --git a/src/java/org/apache/poi/ss/formula/eval/UnaryMinusEval.java b/src/java/org/apache/poi/ss/formula/eval/UnaryMinusEval.java new file mode 100644 index 0000000000..02d6d5e12c --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/eval/UnaryMinusEval.java @@ -0,0 +1,47 @@ +/* ==================================================================== + 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.ss.formula.eval; + +import org.apache.poi.ss.formula.functions.Fixed1ArgFunction; +import org.apache.poi.ss.formula.functions.Function; + +/** + * @author Amol S. Deshmukh < amolweb at ya hoo dot com > + */ +public final class UnaryMinusEval extends Fixed1ArgFunction { + + public static final Function instance = new UnaryMinusEval(); + + private UnaryMinusEval() { + // enforce singleton + } + + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0) { + double d; + try { + ValueEval ve = OperandResolver.getSingleValue(arg0, srcRowIndex, srcColumnIndex); + d = OperandResolver.coerceValueToDouble(ve); + } catch (EvaluationException e) { + return e.getErrorEval(); + } + if (d == 0.0) { // this '==' matches +0.0 and -0.0 + return NumberEval.ZERO; + } + return new NumberEval(-d); + } +} diff --git a/src/java/org/apache/poi/ss/formula/eval/UnaryPlusEval.java b/src/java/org/apache/poi/ss/formula/eval/UnaryPlusEval.java new file mode 100644 index 0000000000..9b10f2b10c --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/eval/UnaryPlusEval.java @@ -0,0 +1,51 @@ +/* ==================================================================== + 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.ss.formula.eval; + +import org.apache.poi.ss.formula.functions.Fixed1ArgFunction; +import org.apache.poi.ss.formula.functions.Function; + + +/** + * @author Amol S. Deshmukh < amolweb at ya hoo dot com > + */ +public final class UnaryPlusEval extends Fixed1ArgFunction { + + public static final Function instance = new UnaryPlusEval(); + + private UnaryPlusEval() { + // enforce singleton + } + + public ValueEval evaluate(int srcCellRow, int srcCellCol, ValueEval arg0) { + double d; + try { + ValueEval ve = OperandResolver.getSingleValue(arg0, srcCellRow, srcCellCol); + if(ve instanceof StringEval) { + // Note - asymmetric with UnaryMinus + // -"hello" evaluates to #VALUE! + // but +"hello" evaluates to "hello" + return ve; + } + d = OperandResolver.coerceValueToDouble(ve); + } catch (EvaluationException e) { + return e.getErrorEval(); + } + return new NumberEval(+d); + } +} diff --git a/src/java/org/apache/poi/ss/formula/eval/ValueEval.java b/src/java/org/apache/poi/ss/formula/eval/ValueEval.java new file mode 100644 index 0000000000..c1309a7df6 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/eval/ValueEval.java @@ -0,0 +1,25 @@ +/* ==================================================================== + 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.ss.formula.eval; + +/** + * @author Amol S. Deshmukh < amolweb at ya hoo dot com > + */ +public interface ValueEval { + // no methods +} diff --git a/src/java/org/apache/poi/ss/formula/eval/forked/ForkedEvaluationCell.java b/src/java/org/apache/poi/ss/formula/eval/forked/ForkedEvaluationCell.java index 8dd298193c..8e86902570 100644 --- a/src/java/org/apache/poi/ss/formula/eval/forked/ForkedEvaluationCell.java +++ b/src/java/org/apache/poi/ss/formula/eval/forked/ForkedEvaluationCell.java @@ -17,13 +17,12 @@ package org.apache.poi.ss.formula.eval.forked; -import org.apache.poi.hssf.record.formula.eval.BlankEval; -import org.apache.poi.hssf.record.formula.eval.BoolEval; -import org.apache.poi.hssf.record.formula.eval.ErrorEval; -import org.apache.poi.hssf.record.formula.eval.NumberEval; -import org.apache.poi.hssf.record.formula.eval.StringEval; -import org.apache.poi.hssf.record.formula.eval.ValueEval; -import org.apache.poi.hssf.usermodel.HSSFCell; +import org.apache.poi.ss.formula.eval.BlankEval; +import org.apache.poi.ss.formula.eval.BoolEval; +import org.apache.poi.ss.formula.eval.ErrorEval; +import org.apache.poi.ss.formula.eval.NumberEval; +import org.apache.poi.ss.formula.eval.StringEval; +import org.apache.poi.ss.formula.eval.ValueEval; import org.apache.poi.ss.formula.EvaluationCell; import org.apache.poi.ss.formula.EvaluationSheet; import org.apache.poi.ss.usermodel.Cell; @@ -60,27 +59,27 @@ final class ForkedEvaluationCell implements EvaluationCell { Class cls = value.getClass(); if (cls == NumberEval.class) { - _cellType = HSSFCell.CELL_TYPE_NUMERIC; + _cellType = Cell.CELL_TYPE_NUMERIC; _numberValue = ((NumberEval)value).getNumberValue(); return; } if (cls == StringEval.class) { - _cellType = HSSFCell.CELL_TYPE_STRING; + _cellType = Cell.CELL_TYPE_STRING; _stringValue = ((StringEval)value).getStringValue(); return; } if (cls == BoolEval.class) { - _cellType = HSSFCell.CELL_TYPE_BOOLEAN; + _cellType = Cell.CELL_TYPE_BOOLEAN; _booleanValue = ((BoolEval)value).getBooleanValue(); return; } if (cls == ErrorEval.class) { - _cellType = HSSFCell.CELL_TYPE_ERROR; + _cellType = Cell.CELL_TYPE_ERROR; _errorValue = ((ErrorEval)value).getErrorCode(); return; } if (cls == BlankEval.class) { - _cellType = HSSFCell.CELL_TYPE_BLANK; + _cellType = Cell.CELL_TYPE_BLANK; return; } throw new IllegalArgumentException("Unexpected value class (" + cls.getName() + ")"); @@ -105,19 +104,19 @@ final class ForkedEvaluationCell implements EvaluationCell { return _cellType; } public boolean getBooleanCellValue() { - checkCellType(HSSFCell.CELL_TYPE_BOOLEAN); + checkCellType(Cell.CELL_TYPE_BOOLEAN); return _booleanValue; } public int getErrorCellValue() { - checkCellType(HSSFCell.CELL_TYPE_ERROR); + checkCellType(Cell.CELL_TYPE_ERROR); return _errorValue; } public double getNumericCellValue() { - checkCellType(HSSFCell.CELL_TYPE_NUMERIC); + checkCellType(Cell.CELL_TYPE_NUMERIC); return _numberValue; } public String getStringCellValue() { - checkCellType(HSSFCell.CELL_TYPE_STRING); + checkCellType(Cell.CELL_TYPE_STRING); return _stringValue; } public EvaluationSheet getSheet() { diff --git a/src/java/org/apache/poi/ss/formula/eval/forked/ForkedEvaluationWorkbook.java b/src/java/org/apache/poi/ss/formula/eval/forked/ForkedEvaluationWorkbook.java index c916006764..8742b92143 100644 --- a/src/java/org/apache/poi/ss/formula/eval/forked/ForkedEvaluationWorkbook.java +++ b/src/java/org/apache/poi/ss/formula/eval/forked/ForkedEvaluationWorkbook.java @@ -23,7 +23,6 @@ import java.util.Map; import org.apache.poi.hssf.record.formula.NamePtg; import org.apache.poi.hssf.record.formula.NameXPtg; import org.apache.poi.hssf.record.formula.Ptg; -import org.apache.poi.hssf.record.formula.functions.FreeRefFunction; import org.apache.poi.ss.formula.EvaluationCell; import org.apache.poi.ss.formula.EvaluationName; import org.apache.poi.ss.formula.EvaluationSheet; diff --git a/src/java/org/apache/poi/ss/formula/eval/forked/ForkedEvaluator.java b/src/java/org/apache/poi/ss/formula/eval/forked/ForkedEvaluator.java index 258a5166fc..21fa576384 100644 --- a/src/java/org/apache/poi/ss/formula/eval/forked/ForkedEvaluator.java +++ b/src/java/org/apache/poi/ss/formula/eval/forked/ForkedEvaluator.java @@ -17,13 +17,12 @@ package org.apache.poi.ss.formula.eval.forked; -import org.apache.poi.hssf.record.formula.eval.BoolEval; -import org.apache.poi.hssf.record.formula.eval.ErrorEval; -import org.apache.poi.hssf.record.formula.eval.NumberEval; -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.udf.UDFFinder; -import org.apache.poi.hssf.usermodel.HSSFCell; +import org.apache.poi.ss.formula.eval.BoolEval; +import org.apache.poi.ss.formula.eval.ErrorEval; +import org.apache.poi.ss.formula.eval.NumberEval; +import org.apache.poi.ss.formula.eval.StringEval; +import org.apache.poi.ss.formula.eval.ValueEval; +import org.apache.poi.ss.formula.udf.UDFFinder; import org.apache.poi.hssf.usermodel.HSSFEvaluationWorkbook; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.formula.CollaboratingWorkbooksEnvironment; @@ -31,6 +30,7 @@ import org.apache.poi.ss.formula.EvaluationCell; import org.apache.poi.ss.formula.EvaluationWorkbook; import org.apache.poi.ss.formula.IStabilityClassifier; import org.apache.poi.ss.formula.WorkbookEvaluator; +import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Workbook; /** @@ -113,17 +113,17 @@ public final class ForkedEvaluator { EvaluationCell cell = _sewb.getEvaluationCell(sheetName, rowIndex, columnIndex); switch (cell.getCellType()) { - case HSSFCell.CELL_TYPE_BOOLEAN: + case Cell.CELL_TYPE_BOOLEAN: return BoolEval.valueOf(cell.getBooleanCellValue()); - case HSSFCell.CELL_TYPE_ERROR: + case Cell.CELL_TYPE_ERROR: return ErrorEval.valueOf(cell.getErrorCellValue()); - case HSSFCell.CELL_TYPE_FORMULA: + case Cell.CELL_TYPE_FORMULA: return _evaluator.evaluate(cell); - case HSSFCell.CELL_TYPE_NUMERIC: + case Cell.CELL_TYPE_NUMERIC: return new NumberEval(cell.getNumericCellValue()); - case HSSFCell.CELL_TYPE_STRING: + case Cell.CELL_TYPE_STRING: return new StringEval(cell.getStringCellValue()); - case HSSFCell.CELL_TYPE_BLANK: + case Cell.CELL_TYPE_BLANK: return null; } throw new IllegalStateException("Bad cell type (" + cell.getCellType() + ")"); diff --git a/src/java/org/apache/poi/ss/usermodel/CellValue.java b/src/java/org/apache/poi/ss/usermodel/CellValue.java index 664f100562..cfe69ac7f2 100644 --- a/src/java/org/apache/poi/ss/usermodel/CellValue.java +++ b/src/java/org/apache/poi/ss/usermodel/CellValue.java @@ -17,8 +17,7 @@ package org.apache.poi.ss.usermodel; -import org.apache.poi.hssf.record.formula.eval.ErrorEval; -import org.apache.poi.ss.usermodel.Cell; +import org.apache.poi.ss.formula.eval.ErrorEval; /** * Mimics the 'data view' of a cell. This allows formula evaluator diff --git a/test-data/spreadsheet/LookupFunctionsTestCaseData.xls b/test-data/spreadsheet/LookupFunctionsTestCaseData.xls index 94f16e9840536697981a8c31ce59900625c6236f..ea7fad9f3df61e28629bfb11ff343d3b092fee7e 100644 GIT binary patch literal 41472 zcmeHw4R~BvdFGkXNE%tP$Fk-BziatVc5Gouw&VO+V>@viVkf~iA%Rdcl17%WtT8iE zY-bZ9Lefo3pdkqXL$)PAp7n-ongUzWXQ5WQ&(b`to82u52^83rZTbMaOG8q2X*bF2 z`+na&bMC#G8IKl1q07i;=AQ4I^ZlLQ?>pyQ|L6bI`r^ZXu=uY{%nnKz0}rXc{2 z;h8}`vyIt@2gcth7K;ir29Lk~{0eE{b?|yEDz6S9fl!aYI&VZsA~Ye)Kxjs2L70g! z3n7JoCSY0-<{->PXhWEXFdtz7!a{^a2#XPxAS^{#hOiuA1%ijL5}_So6~bzSH3(}F z)**BttVh^@Z~?+bgiQ#Y2ya2yjL?P9jnIRz1z{_~HiQciwj*4Ga52Iq2$v#UhOh&n z7a@(X6JZy^ZiLGbu0Ys>a3#WCgsTwVif}ao+d0EEf#+YLsxHgw0m$;ReBx*^7I7Si zyd=MknUrr)r9R>N8*kmR{|9&fd2BzPCpDXShsl||Icoawo5SyPKXXhx#&&CYsHHqf zr|#37_2!@%H^=ao0UxJO-=k(wcp_xX4C8sU3epbH=NQQTLmsMR4JEzk$Li7Qa;9H+ z$b! zWsov}5oz^}XWqhbfHTz%@wDXouza`6_bhzJ%$KFiw*=CKvFqE&pEUPUM?l|bc4tP0 z`|`v86iH2$*=Vbj6vI&X73yUw^D&I*6_Vl*yrRgh<&!p|@oz7WFi|<_+faFhQq=s8 z^0b(=rE=1xm6Bo@?8}NgG=;-ssN{^+atsZiY$>uaqdcuoBdK`1=nzkh=K5@YATyd- zo>+>Oiy5(fb^EG~t5$73y#A7#I&9>o^~)10(XN+<(%*F;JMivsrgmV&^l8g=ZVux? zoT%1;@Ahb}2sS_Kg_v0}cOE7y8$8RIAHAtV05`3_#0Ru*Y6stkJJN?ir4th~rsl&P z!EzhCV4)3Os6;QOZ%o8NVk$wHp0E}m+1u-gZo?`7^L>e^EnlK*3kzvi@SM5FRcF({NGl9KUx9)cm?>UD!_lg z0{pQG@PAbS{-Fx+O8iv9|3$&WHMi)$q=(D??6h1h3cE#BhCh=u~*JBJ3 z_)n2RKDK?XuK?dz0luLuoVJqSkBld@p9Eg2Uv1B{sRaJ4xekvoi1o>xzYRMDXgk63hW-r_8;|jXGccv zk0;Cp6Lm%YZk%Y4-%S$<`R$x&#P2+~GtgeIIC*>o%f+!={*-qpdvfglcoTdaXn-vST_Hw%uC00iRA!kRe%fq*mMB*C)hs?p=)ATb;6 z5{R&Gc=Tv?z~{FG?h~lsn(X*kJ|p~_2?IWxuxbZ8*W|{s_s5&zTfuB|?f97BNtlZ# z>XBy#TrI$UXn1S{nP$N0!dPyMcv|6p!GQOUOn#ISP{#XUVXA#bP?6J8gOfi>Bw?Ilz&d z;WWY^&<~zA4?g%H+)sn1=QIFtL-in>mH=1}$^vlH>{;R=Kq+CtnD>aqc^3xo@1?;4 z)O`T;>g~_og_f}La)OfP`hA=FGKJy(yD((@=pbP;2+Iu&?93l2+=U_I>sdmZ2^}9T zoM~U zI0(K`8tlZRG_RRQo6|@?fxK|ejiDaBLjN#Ez2U*(es2`D&Az|~uP}c6xbWqj%8t!7 zrc6F4>)`OQ<0DybEL#{Wc)3CE`hC}4d-dC|^TrEoD53-}lRq+k3^cr(Gb7{K0!ke! zpS_Sfmi5L*#|z_`k!Y!Xfl|=e@oYa>M#JyR4rOj0&W-1T)K3g$N4>Ej@RjKUcbR-X zbIKbos9YixnAiiSNM>Y&`3hEod}j1W_V7q9cXa&tWrq&D{R%HLI^gx^Mh=gTAG@p@ zllVSK^9~8(t|(wij-F_aE$CI?%IorfZM?^icVmdkZ{955UGlAv9(mp(-&^H-n|xm= z-`nN;B7S#C*)A#DC1ty$Y?qYnlCoV=woA%(N!czb+r2r;?pB{I`@1cgVOyC-u=o6p z07FGzZfwZAIopq6vp^M(9~&)TqzHlYxf2Cgy3tm4e*6oK(OUE|d--f(d}Pc6LYIVy zg)9B1FuD#(!^yI(X3#5b2DEL>Zo){aB*T7%3_NyfDF4R+a>w$ z&mCjmDs1$I2LU|Qi3-cVGn>z`Eo@@_XZrdv+^@R)s_Wmf852eyMk^LXuf!Saph3(1bjvYLn#|T{*%4Wx6n8oJH76VY?gSir;0MTH%jagi)@PITG%y)`R`#L;w z`7VBS^Q(tnTllq=U)%U~A-}fs>mq)!D;*?JZ?{&_#h~UtW-*uzVLU20)=-h7W0~R6 z0!GO^#l zy+A|kAITI7-ie`MG~#j68H+G=F?L@%4xuEO;|R`PfR&NA&Y$$wt=!qeffeb4!}((z zk536HP6+G!q12nRSQLr!F;o|Z2P_q2M{CcGDsB)e>DoUH>VVPgBj71YVERC9@Hj;y*I_wgkbbmj(-DtmPr)M(;BxnO1g~6!F z!e)N4?hCB@0_(oOx-YQq3#|JB>%PFcFI)ry{y?$C%Z-c5gg#pFrRZ}q2OWprvjaQO z;zx5{kURED?6_S!FyK#?QuV?4^@|?Rd zH%kj7Q{Ip+q6=QT-*MYFdhL;(+b)A+yWiK^KZ7-YspVxot^F;&lo&TT7;Z&NJb`wF zD2WC7NNK=A*W~!XG1kM(mCZ{}D3EU!ktWVT9M8ez{>&(}$L@s&o6lxP&_8k*#mBty z zKY=mIs$i#8o;CxF=Ki#8wQ(e$LvvuSB2#_Yk=%(+uW0g#q}hpqqs1g2_&C=_Sopt$ z-zQ=}ip|69{bKBG<_i4Ijvx5+S^WNXe8~grFp|Ae_qI>}Yy9Rh2Hb)H>5BT~1E0a~ zvHIfK_1Mq&`-X>O6ZpNl@onZ?_=(cuk$`{54)OXe`b z|2d5GKUuA@fnZiX{4tw%NoP@y=xL@ z5Qn{&ID~lDWxrX^{F%ESjw$`e5@$f`Tiw9_AJ-&KT+>kh0`e0dpA)BwXQ3|h+Fe=0 zvV{E<>A4wq89rmcl7kSQpAS*mxv*B(Bx0s`mU&pd0qNYNCB7R#k1+XwCgaaO`)*@t zA=~ZbH*VI&UTjSR9D{j2SvnJo~|mRe#R{DGx|IhP;9w ze7u12-+z~;i%yVov3$m{o!eui=6WnA|0@!at8V+mBb*$(`Fa@9`m!pcdJB;p6= z?7WwG31a*t;Tu=~1Ber^OQ1 zR?{oMN|_b~EF`B_087T{6@Udgy#gF}rX>Rmfaw*$SUkM~?AldX+8OB&?6-B5`$IxH zN_LP2}ii|B00COdhKv}sR2p#+Tjwc1|-#MhbK2{KvKPSVCU6>qa_!# zHGl+>@LZyOr@Nz>b1k;uQed4UOOvlLsGqVyxNddubq{(A*o(F?X@7$ z1zF_|xT-cJ)oW*UZAhxu&YIegRIi=2wIQiqJL_sgQoVLMN=Ud~ddM^sL+dd19VQh2 z^qHcoosUK)ul2~$D3cfS{^MB2G$gS0k?c+l!4ur zj53AYE1xMY?D#1w*&~lUGKHz$-rgxp1!0yc!W1>P?^IJk*wj_*1;}1?T@YO-Z=7y{ zP17yVIo$#@KUD_SHY+fs*s05s&B&&sA`8Ii4;Qd)>}!7)Y-7>2O;-sCtCJx_m2HQ> z>VtfDEl7&J)sghnf+RZKMC+CIvug-b#F^Thx1xaSW4*mMmscXTw5K%ZpI;w~R?4=L zD#~_z+o6jSu@X~Lo5zJABoAdPG>_tce<*+?+S|96)I(iPu_tXC!zqFi5<3Ka;R|1w z(%XYD+uI2PZu!{W9)wM;Gu}`M)>&`)Ix9CW28SD~#vsfx6&!9XQ$g6&A?RW-OI<3gWcCo2U9uW4Iel1pfbYxoLYd_fu<#E z?{o{Kr(0m>bPMd7Zh_s^3*497Z|>vD!vDk)%ZoE{+GsNlOL@42<>BH~KmPQ}iVnNn z6>Usp2mk7&9~bNS%w_}iuc5DID#G?E2z3Y+g!S3MSHAP@S`e4KJLD-NT{Nazk zQwzckRS2tt_s3nEuY)>{NH?~7K9g6K^Uy% zbNkLseybLQZ>fSXSj*@3{o!}MUkk#XDhPwMd~V-s&wZ&DgdPxXtD%;=dp`EX=M^D4 zGCmdh^6DxGgSFh<^UaTctQLeFRS*Vixx432zVXRg5N@o3Fj&joJzseEPisNAxeCHy zEqC|a{}=vfQ(cE!svr#3a(B-^PTpIN!<~^4ds(ru;wX*%^ACKr79=YpNP@MAGvn9o zuLa4P2$En88uO`te`763)<=*8Yt5L6fzQ-}WK#r5u%@to_}JHKLDCgL6099#{`Z4> z$|1QZTsyHP#X4L(&ft=w9rx?GR)Go4xs%Vdv;F?|fpFpY{9-e1EN9G!+#DUjle?*k zeO4ar_3(sII8HzQ9V)rF)k1(;II0H-!VGYoai@P44vtVch z@;DY~ILlff*!C?8T!J=~SkRbdL1UH$jop{@!YtI+0r_s+pVj-jpn>#`NS+3~#lYq9 z%)2n~y_;E)Z*3%RqL@V9aohyX%hIMFH-a^1XC!B1v1LlmFzzeECE{C)vvIrpB<|9( zzDA|ac=!1fRCfr}PNELE5VbYN3i7M}k}#^+;$i|9)Ole#+~AD)ye4{4SWBifT?7 zR60;pK&7)&9H=6Ft8Q)?R2(i;xs5>q?@Nw{l^bsZcf19D9PNNL;u)<0u^ zx;jF5Av*OwsgR?noPkh}sw*{%Qb)F3mv(alP(_CS*iwvA*8mZv$fK^=F?E3{0}Tvi zE6YQ$ru~-I6Xpqr7aD+s&6L;SiNe=m43BPY-xn?xpMeI-Vq>6hz`7lgXj$9dPpjAs zKpL~XUt_lSYwW({i3pW>z;&(2H?^*UV;@J4P+bw=NNdcJ)|e%&vHOy%!=&r$q1@{r zT)t!jQ62XsyTbYEQc&_C)ae*>M^#L1fw3u;QQaabMD3}{dG9@9PEm=rU#cWF#-`94 z<|5vV!Rj4AmPfO;9lqRIo13NPT@E(tBbX@0Fv@H*pG+=9pt|&U! z>{9_4aohvgJpmZYa$8y7a4e*BmF`>yLfEbt8izd*k45Xgjo*n>#d;HnSae-iruQl3hX);xp{O71)5do6Mg z%I9x;d1c!^ca*SvzjuA$`C;5@iUA|9Z14=u_a};HakB)UulL&DXP$UFaL+KeK>~gr z?V}6{i913;UT3T>gAo92jY}AoHC2bUKorWCG zCI1fE)I;b;k_*fc$t>BBDz5~oyBupsEO8zRBxwfseJN*H`Fsf7=R={KY)X~74|kaL zvl2h8R~YkO4&Y@w2Y>my&;ZoPzqolCf41w`Ut_{u$kek7#r-Z6GaF{RIN=*+xp*BK z)N^xOJb|t@k#O;PR$j`*ljz>h1=5?)lpdYw(xH97%c9x(U6$I`^LlLlY!WS{?rFPh z5N5mVRG2OWY}eK<%Nt=1pLSWDylp=1vbZlcC7&*nY(DL>yiw-xX_svZ!ITEOsms?S zLjbSeI@nK|DQ`;%re$ZAfz1lR+GLnXgCO$ov@8dD zhfniy;E%%QhhVA~9{h0m6d!w;gDE~9%WyC)%TpE(rulei!ogGy9$#><8|$b8oG&^j6qIc_QYq*E`JRScOe4N}Jdkw#%mUj5H&wad~nQ zNsW@UD=}yq`kgyqTKq{rcfMKPeYS;>JojOLcP!^F+T%9IrgW{UQk!?`2P%p_gi1O0NN|qY1 z*kQR#(u_6w3|=WY8ztvXKWC$#^HNa2sOYzaPy8>og(S+A7z|Z(QaB|w@|yH{4Oeth zR1_0_h>6lD2T9>zm(M}c=ioBofa4`OKjU+N9%WTzIq0UmO~L`ytT9#@rexu#sce-s z1^D6ILn>E^Mw)~ltcQiyCZC@j!Vh~ib3Xm_MaDzXKr?ZDI*1I0BB6%JsBAvJ;8z}2_!gOMOc$~8 zNH8TY?_+aLmBpJ1FiA^+c(=b_8^xR;UiJpPOG?zU1vN)yjn$#x7R0Dx8VJfdtB0lm z8D>7t$|-r7BgFuE#Q4549X94VaxBPq!llfD7*TS(5|lDra2QFMR7`UzdLjgZ5W==F zLcU(kBAgpSI9Cuh3c|Sog#bYa;k+AuJTgyBGd+$y30c%^QEk@;%amG^5ANQ& zt_aBFa$h6FyBy1al)S8i`axFZBdz%;3upNqAbM~1{7EL6p43* zP&6hjqx%Y>$-_V704M@uJ&GcRk8l7SLiKs_0bSegphpzf8tFwH-9( z`D_hnYs?2>{U!gK%cn3lC8cW&T)B2vWA-U!yD}t+B5d)Y!MCQJr9TyDtg-^eOC3=mONYzkL%@?eBCcCr!xf z5#zK^L))L^V8nHpj(O&Ff7)03doAtnrD6MfhDoN?Z!p#V=G>=dx3vmscBim);>>Qa z0%9cla<9(&<;-riDeFaTv}##>^&<2;5F_(yc4I9d9*z#ooTW93H3PP?l-l3BP|8~1 z=@JLkEO6yOEpV1rTvXo;J_qy`QjVejNxPbx9;Jnt&p|;r;DvE5MI4Qyk@un;`Duhy z=@LK5v}%Mda$=@Ft(~t{t=jp!k*2uF&y9#tBWmYci=qRjUWp$rZ`8bBAgjxyFu@Z9 zWP-*`y2Qk6p9wYfl?mU}_YGS$^=a6W2{rZK=`&$X{k`v@71e?C5)+|nQB&V4wn_Mj z`TWq-xBSGO$ou@LsjvK4Q(x^^HTB`Y7k<>#f0xgXHT7@s`GHH;u9idmsHt!Hp>vI9 zo9c&6Px;wp96xIKD?h&B@7vXC_|vYYe$?>4$>)bIVODL&tr^iz8P|g}nr3M<)tnc{&TyQt3q5A}B{uj3}+kfGDjaK<(5H z+VWO`%&Lw6wNvQ`a42UA%AAi<-2ti*&W0)Mq-wKO=K$<(Un2qMK&Xb)OF)f~VZMuw zQmTIFHAt6)nI)8uA&nfC^Bo{bM!bOUfNEBk0h2UlkrGV`NZBOx*;)!qVBxbu*~3-k ztzrGBt3XxGwTp5|B-T-&&ki9F2tupyfFFWcxtVLt?$#1Qs-#uK#)?;XYz;Jvu&<$m zu7Y44Mtu#+BT>@Vkj5zzMakicVzOCSZ-W&(V#}+yAti&&T&b!I3R0`|JtzTs$mx4X z3%}}l(8}eqC1u`&Z<4ZBz9m)G%2!hJh16jbNXNPY938T)hQBdSZq(@^VEBhHs|UbhUMQIRdS`F4^5JtXOkRS1dQ0D>Oz3sHWi~(>{97ZR6jBDCHKS<#K_}QQ~)qY+E3r5QyYB(k)|R% zE)$HF$7NG6#>~rS6{Gq!uq?hwF|MkBQ9T$IBitSl9)S^3Kaa}#0IZM8;WgM`?uHLx zJ7l8A;4Z#$6ixmh{%%2_m*5}{5Y9q)Cl-!F_&p$hG_cwszgwuQ5w|J?Q-f`F8Q7W- zOpUm;A($Ei9buS^6QrAx|2~DF6=Kq?I=iUj8*>>a zG87IWTN6gMI)H3-S!8_OE=PYNL*WoItdeazAZKd=$aoM5JVd`nPeBFf~}Khw)r}iG6Se?I*sTyny$Nt!!&KM zxg4+?)ZOzYo!Dpx^XGT+u$}z}jDU~BjWv4Wm*2_b4n5(&wK)HYjRv-Sd*~@<9&L4T zI)5IO6C0XdhkA(~ilTP(4LR)3`aIPsGrQ!daZZ^i4A`+yv1Of8#=*2d*N5^sr%W^- zoiYyQoH7pPoH7okG}ta2%sFKo%sFKoOv|#nMPbxT)G5O~8V7SHCmc*2Av`(ZV4ANj z1XH?P$+&!4mM141O!INE7lqLQ<6x>6I$#`3@zDX}V2Y0p7zfj`big>6=A)n8!Bmdr zA()=9SP_D~+QcEIoKuFb2&VCD61Utm_OrQK^dtpOH4t+gbJGqPjx;s{F}5m=wIId= zU>ci+SQBDgch&*Up;&$L>Rh2?NYl9XcWJb$U0O>hZDuHK7Snt`Ki523j?}3MYvf#p zm=5z#ZjQt3^JJJ`7dVemhgz`b8kWZmH!_yR_#HgfAQOQe$KXVuR7jO|K{@Qpu`EJIJXvS+ewUx3^5qG>s4Z&K!LE<*e6(yDfDC$v40~aM$j}o1%r=H&K%x|B00v7R1rX+en_L}Hhw68HKzDKXPXfj!MhY&~GxeTks~N(`r#lXzA- zRb67KWA%|aDwLQ1 z(F}owD9g)@Dx7!z4-F+^NqqR#Dk&U|ln?z)n78IFE z)N&0t^=I9LFSHpMpCR-c5E2876SB%^rS-b`LW-rHL{9t~qmW#Xv0gb~xq4+iJ0aDF zczG#juwK=N=+c7qIzxnXX+a^0!yQ3t4$;#iNLTpucm&DuqDPQinkrShN3+m#X+e6d zij=Wwn9>3`_3RNRE^0#09@$FNvqvs1C|`@vb7?_(%%^93sOuEy?U4V4RbyNlto7;f z_Jfm;C-GCThm;qd!4IZU3Z@Z@9@>GGAzZhHu(S#ml=RbrSXyODwQ2ORI|_589G9Qe z#GDZIIa1E1%?*{CE9J-$%gqgyn=9pPT3e`Go0Q`LV3upsa%Od!^0i4hn>H_0Zl09m zQkUiCh3L(bayD&#sN8%h$K^1~%@38EFXe0+UoPtOvOvnQ=dj#@P`L$C&ZaF4m0Kv~ zxU6Qmg<8%Z0v1X+o3v)s~9xusIhrY-aNTqt}llX6S^a?3*H zmPt9Awmd{{xs;>1KzhqV<(5l1o3+LgiLSIi}G~bw9e-O`|+6 zZIzT;>6cp-Dz{4L*)+=R%B_}i?S8q{TFz_}*;h+Brag~ZyESIab*0*wt~ZG@q=9)L zb7lNZ^(K)-C2tZ3A^ots&OBwzfBV3%mM3Y(`8`Q;_s4mX^zM(w-Q6GSNwRl;H16*H zXx!cX(Kwf-diO`;O_<8`u8_ttIZW|PQPcV#t*d8>E=B8Ivd4vjFzcC`3Zq5s%4!#; z&FNt3;hG?qKTW3E$sQPYtKvXg;=T2h)6z+3Pzc=Ly|wP3bvl zI!{*PKNO#YU&UVE$%e1*IPVEJ4y?Z!oAt=u4eP~7-ljWov~a-=!8{byfOQ}a z1aC<`Nvz9kz zRVRY(30bGX)oB*CdqVbL4F$D!?cL9)py=!tncaC2*My!_NdpNL=$om2Gq-r{H5B5t zrsTqty6@|sIH3Y9UfCv8JJB{lIw;*`7vD~ds6tPi=q8jsNAkk9kI06pf6^mJMXs>u zITD({y+vj#NxZFke1pB1 z>f-?psxN#wW$GP|Zse!@70z8)Wk%0k5VcAFnhMfRUgwa%39UpMfP?9BluO=*lK$NC*1_EJ*1?o6 zm$we4Wx2d{FwIAgPZY-C#KBZA^td>f;-fdk!4w}oB@U)#=>>5x%|{Q1gQ*O3IolD$bmPI*vjQc#=v+v~5F8TC&`#=4n zoP5@W_|y$zyB=eoOvx!4=JfBk`?GTbhLf5L4Yw9f6Jcv9a9ilokomDIOCa0kv0dEhKo1a-`% zlcXa=t5TP>s4mN`?7A#J0IZ}AR6=1#T4TZsOgpws-F~ETL|*K6;L~!P%_DE*rL-M* zUQNh~RafzL)5fjiN!_mB?O}Lof;mau9)D*qq9$k@fO=_bt|E1=j&w6lZ%z7}Z&-?w zq<;(^_A3R}tvFhIBxy~Ey}t6fBn8RR)>uW7p*5x&7p+K_%n!t(h8YxIKRO`Yv!^3f&qIM#ub0ksB(Vq3wd(Cr^Cy3cjsXwLB0-DO zRy`LpDFYXbVa4cyZB)z!N{=rDfEYNAZRz>FzUsuJ=eljx z`)9fRUT;rrpWL8sDxA4~0~By9yG9C9_3lhJWmsZ@RqsxCQzCn&`^l{zl2L|dxSda6V{re7os`al91Azu#}}K`0<_m#q(bYJGTp zVqb-4-B071)LJkN^{fm}8q_$x|KG2Qd%#M}9{ysnH+8p1FNlNMuRs4kX@KpE?wdM< zH}HaY+RhCKw2gUui!fSM{QdRkpPvTaD5CCXuoZ+qguHmy@4fO5*AC78{6~}Kf^}c~ z=4Qa2Kw#TodK!VY-d+T@l|u+zL5(0_NX4}o`rh~=bNVsx3M&M*wb|?VQV=cc;g`?! zYuuPUg8$O`>f9|OxtkFcIQ<0shx_xnLT+%(+n?zl9v#aShP*3I_Gd{ajbJXlWa4x0 zdpgGF|JwhS{Xe+-&tq)QwS20{AAjyIo_nZs$?W?-4*56!!{d}c3Bl=yf%cR7f#uTu91@E_WH z`?JE^_4_vU;XhUHmx74o1_pNKj}(L^qWJ&DbNR9CfaKk4^Ad@DZUJI4#)={FcjWR% z6=ub03(1>U(` zOaA)%7p8$2d;olZ{&BogvCq7B+H~ps4~~kpAYlLDSn>1vOqc%8i>}4<{9 literal 41984 zcmeHw4U}9*b!K(XUynvo&**pb|1=u?S{l!fEKB~;w0mJmY-ISXOT*#qVrv757m^+I5eA;4itcGsBf*@Oe0 z{l0sv`n^~Ex;0}HVzO?j-t@awb?>cPw{G3KRqyp*{pYUdKK!SvziqtFe$!-LoNYI) z5qJvUndH-H%+2`1`xj?tXBB7+zP|eTC6d6;!0WB(ye6b(q!uLhc^gs&sU2wnQU}sP zq(w-Jk+Mio0n>%F1ZgSKGNk25E09(qbtA1pT8*>@X)V$^r1eM}kOHKQNIgiKkb03e zBW*$2inI-BJJJrM%aC>=?Lz89x*Vw=X#i;uX$WaI(iKR1kgi19i?k2vDx|BCu0gsM zX+P31QV!_=(m|v{NQaTGLpp+VJ<<(GHzNHC(oIN5k*MoT$0FZ&z7Xmdt}_+1x)XpfL_<8r6afug1*CRYYmN^V?7N-udV=so*Ei{OxnuFQ;x{z7{ig zf_llEF{gx&Gg@mN*Ci&MqTV_l=2DfUOYhN=E#|nHHfQlKFKxdOQWz7S2&tF}e4lE7 zbQ|cWkk9c$9vT#lWj$l>TA+1BGa@_`qzC55&tA}XZJ#TB%4;u~#-}}lQQX>M{%FHj z+P(~H-ipj`o3!~v^V7H*^Btsn%sJZm;1~HhC_Lm-$Wz9*`RTWH0=JR zB-Yii#7>N+y$z`}U$VqE(VLBNuy6M_jwFD?0+mnZ3Pdn*i?FezgVk?CJsEC%5HJ zf=VBp0=NYyw?*si2%?pC1hE>!a2?%gC^ z1OIp(_y_C2|D+E5kL$qyT^;y4>cH#qQxE?q1rPmwk$+VVzf%XU{M43Lern;rUkA^h z)`5Sj4*W0c!2eAh_+Qk4&zGM+6FhA-@=-m>IQYA}y586Ez7Es;UMbHtlgRVkV`HD+ z@c9j<`OgL30vjUme?$TKIQ`sS2fniod`BXj>$IeC#I;+ze^sBko)h?A8!iE{BvXF4 z#*4sFUEy5!S$Kf2SOR)*&yI18DCMj2QT^w-(c-xmU$Hz?JX}i(ysC#EvvRZWC%U>; zbgY1!KOk_fI~{(Nv&z2(eu}_7IdhFFaF5Sr4*u70@y!0J9JrRPS6=K46@VXWXZSV6 zRVy3FPXykcV3$}#%98j?q!)rwX_BEkgHAnPuip?p_KXHeEZOyYivTF7r?f zE{L#xUnXl6y0x6NG2QK0jF~81TY*K=^QoZD<2)#E(KIg#6v(_PaEhH*1=_HARcM=U z1$N53DA1hDs{-x7yeiN@%&P(|$Gj@gq|B=VZOyzY(AYGraKIZ69HdS4)w@{+N@0vD zr=DjJJe2W~&}7X43DvMZ656pjAfam3M?&K^2P9P2`bcPq=75AMTptO|<{Xevt?MIU z4DpgkE*@>%I}Ae30ZHTD>7E;s#=XNZ=o~m{+&he;&H+i|-eDki4oDjJ4%UkiB)Eys zB|3HnhZ^?|L%|^@b&)jg9mbO9fTVHnV8)*dlE%Fg%neE7-q|=eB#nCqOYloKN5lQ~ z2jHf;A!*z@y>mm-xOXkWxoo!Vl^p~D6t+O#djAMrxvtRm8 zvtjT2mN$8AM~OC>yjb=p51C{nFTC(V6-k>#{Fb-8Wj3&%OaiPu0t0UOEJ%Pol<}G3 zK8-R($Bv)GmVNZ2AFW|(czC#msVK}bMVO*y&`Tv1h1Ghom!Wt=zrgpCxyv-K3fz*K zSB1X$R=B)cfxXQRj0v{ZE$K%wwTi5ORZYBt)3Go9@2HOXz74n5Let7FjZvlU5ZKtr zb8qgFNM?r{BjM)WC6V~kjo+`kfh6pvn&#B1Gq)1MF*ZDWf3gr~FaCCoI{*B}nBOVf z+YEcQX5hO4J$oTlWvW&^xH%dkd7@B9J!b#zi3k#ZwC}BohqA0$PdXi&oxNB^;!My} zPd!yL+M_Tx+6e=0`MA*@h1K>Mw|k>~Hk{mNNp>-6Zk!mSFvnEX+&HG9uv!zu{pV2g zY9v)#ft%G~1z%ND4`pumrh{uxp}GEAhI03ZYIOLz>7bSqZkH=3GAChLirIkL3fx-{ zS)W5&hv!=%H{S{e=3C+5d@CGkT;cKb5`=`X1Rm_)9PA1XjEoefDg%#YZZ?n6->{!l z^ZMCE*c0i;8HfPq90Ht87{Nc?6;@-M!fs|;b7AanzW1Zq7JjFI(J7l5!6BJ0pUhw_ znVtq@)LaXhmcrNz-}+`UnI%4%p;|Iq8jw+gE@WB@V?X@Sx01;$^~vn6C9|Ue8Jhei zm?j9StuXez=l(vK%rc+M6}4pg8j#^cVaa6BH~;I$$z+!MWcJjO8EimCO~16Jy)gE( z|MB%?GAl@CcVkW405ZGhplKJ6e&*tLlgX^~$s}mnrUqo9ns)K%_y6KE$z-~HG6|Zt zwE>x^rd>Sx>~}w#OlFl&CPC9KYd|KdX%~-PocTsFnbkg-1WmiV0hy?#T|D~fZ~Y*d z%o?9ef~E~MAQRQJi${O@wNE9JSxYi|8f#hrGJEErX@f)W`Q)=oh6W9vs!dzxlS$CD z-Uei%nl?D}_4mCenap~hOoFCuYd|KdX@f&w_^%&GCbPjOlb~rk8<2@=+ThSrAO2D@ znZPHLplSUL$V4@5aOm-`SPSj<%|@S0f~M_mKqjhbgF`==c{Hgt3vs9btNZG_dBB@D z*Uh%oohyww_VDMDk!}i+LZf`78$F~^Ey9M?tv4ql?G2Gae|)5yJ)}{cG3JB+;qGLl zn?t0~79Z($4{20GjJYuSd&x+*gh-(qKGIzt(x^V*G~^$DF&XLB5GgdlM>^mkjrP7V z|M$H|l8|PwqXy^oV1D$m%)WTfr`F6i;q+$Sj8bd<%~NwuvV}q+C8Y0$hR#fmWQ0WhU2t zDTpC@mx|Xc?B> zd7%|$w|Qk-ae_Q78<^K(CU_mINS$35no(zqSEqS4gF4eVWmgj4cf_7ZC6&GlZK%=b z)o7btSW_cTYdOvfW54IR2hL6Ar}C9z>A~Pc;epCynd{@I(pSuO;vnn;IPl26pOXIP z0o4Z~^Alj_0q6r4IIj2E*;c*U(KduJt}hm$s8`4FrdH23<47@&+QwCt5g3OH#2l{0 zIBSF`;thrT=)~0N0BZ%0(WXvF_69IS-JbxXr@^4E5kv(rU^d+NkfNh1wt+J>gpa2e zW04U-4%_D=?@Bw$L*O zO-mDy(;jM%wME79czOX&iz9x0RhXaifAJ=IN3%$(Y z-w6i^cIoKE)S1Gl9sf6Z%yh#p9hHtggH9ifS%9XhHdbwsvpx(1wL)+UaULEs=q-im zN+}QX;u+@DS{UYAfS4^UL8oX~bVVcf6mcNe-X{<&vI-BCx?nIIhvK}Z3`pJrO)trz zYNE2zPvL90SZ&7{O`y1?SSdV~ITgo|UV!mJ92GFdhZ4Bz5mpdQawd(DpwQ6joik~A z=S-U3J)Z8s;{e!eI5u689zX99T@KX8Lubm7%wUO`#7F>Xf>(kl|b+6Je|szr*X6H?^5Qc=0hDhd_OP5 z%#mX0EF2yjkih&`7y*C5ZH4mmWCh)Iv{*bdeJ(hWKQ&oEgs(Kf%OEd9yzJ)X3SRc` zawRW&dD+KHKMrXeA1@Rt!J*>WvxsS31sh|(LJd!hPn3i5xe|n0mQo|dsY-rgsvO*( zFHIDu%R!}3t^_0ba-kfI6-&XSGBY+kHA3&Kd{uDA(PPJMy8YIj!42+<{tfa|=%@oH z4&8uw<1P8Kg`hYVRK^RzS>O*|KRq>3vB)R$<#KR-d}3rgm?oWy$JABFj475*_vO#! zN5%_%=ZX`3ltGH+^OZhyy-NEe>lLt$A1O|roR}IdJaBSq z`Yf_b`Ki-|lUiuGF_!em$aJYNH6rpJ>R$#dd2$p7LpRF(yil9U)TT1EsZ4DuQ=7`v zrZTmuOl>Of0|C&VK06ic4vNzvGNBKj?V5b)bTCz{1n1C`h0*;>jhQM2QSJhiz^R5~ zqoYtPG)9#vS^}2y6db=ZaI!0K`+~9jWVrwrNNy^n=>jGN`96RN>BMcfUxx}(27_n- zXB|A$F;M~MnZkqUOtKH=%Ehwczk>$*Xl+rX4VJrJg>o~z`q0*OHko56j3`wF`T z;Gp#P?Zfqoz5y_C|&?<^of%c@3IV!h(f-&S#tAf%fj&W@Xx@@9wKU!13 zTmr5z;I~4bD^wFoXd*wk6)i2Ebwm!5^r4+-^CaZQVwBg`JZ&dcd}~lVM;qA(&Aq04 zX5!p_Xv;N)snPvErQt=BXR5KK>1CMIDbh+GChkKpK`c^WHD z!^np6I)F9>w-wLBoK956gB~S|qD}{c1EP2nr>BY~G<6%gZ)|!}EIX_Q%ti$*#}qkz z?wrh$=&#%FV9gbPZIc{DbT%X_tr;OkjvZ zcPCj6veXttZQ-@FZ!*|v#$fBO9mF)QGn)DV!GI5_DKX@i*bVgqR{@O{|GN7jdp`)1 zR_gDUD~(*KUm-*CeYae%kn0|~UMbhTa-}&e4M^PqsXHKb2c+(R)E$tz15$TD>JCWV z0jWDkM}@$X4I`ePk|7B$#F7wCnO3d{Zv`fY z{M(_9sbyk}vqv8~tn`{fsYqSe#s1Hq8iDQGbojb&PBF+Tt5}X;B z(PA(@ae5q{gt!3C$%Gk2uaqk#4(L#(@R&CmQ#gCB0tZYpMH-My6u8a9=7}y^bgA+N zDO--tlzfa-sr}6hY`1^hQT>?Mn<2V-a4gCEoP9h+X zXht$CkU+?OR@lF3^Ij}F-W$m|_`Q_*AYdPiz~Hf%cLMg#2#g4T*gj6&8GD?#BaLBU zKj2OX{_3^^1TaJK0T2!KmDU0{g<0h-8y_y;5!6F0KEkueWRUmNq}DYh0L1k3oLOmxwgNkdKL zdq5!rm^Fm)CIxmWG22KqO1E&TCH3IBjqQDoW%f_WlPMCEts8J1!Js++6 zGtmV;A%sPz;T>7eG6XAkM3|LVM=*~hvshkh9{J76>m-;*PP6hd2i}p#VR?-M@5tQ& z^Lku)N4|y;QqnteG%PQFcowgrI05pXLiED_w;v}aK8=#bZOf*^jYdMZlE8H%(x0Jh;WlHwg7nHA4L6(p7+^;bae1K1 z{rtZmX3_jj=vUJg9aTSEP|S=1P0Bojq*u(+h~ZT5j}fq6+~@FEUYe!@>=#G(RZ6c) z;Qt2+a6V08me-M=#)yBWWdbG&P_3|=oK z+oUADf6Y?{uK|TMWQbJF?^-d#dN~GT9i0(QT}Njuuknt~h=@``C1ZJ24l=?4R~+OZ zV|l$+IH0{G2Y+Zez=-lW7-W0fg#(IN@7QIuACz6Y^5b<`dxRg(HKcN*NTgl(;d+Do zv|E1m3qKsuEcpq`4{Wl}&mMBX(TJ6u<$y|oT3+w5Cuo;Rg>+D2<~HGg_gO%cg9Vm@ z;T%hLSYA&^$qtmvnX)a}VN2$OSI!qv^}<}?c%||%9N^RoTmB?(Oir(=Uf7iuD9K{b z^LtLoE))j2oYp*1nhQ#nQ&N;HrlTlXuD%@EMV9QcnJC#sRxZ>)&Sg<#kXIBL-zVmf z@!bkQbK#C$6d5+s2{lAUVe|WEZ0BLQ?~pll-p9@(!7N0n>O%`wi#Ho#l3@ely_o)W zn9K}RE`pb%K?7<;-6^PPmG!O(6|w6iV(bKyL}PO7o=!`E0D9c<1z>76 zmWJe5k?&~Bnw2T`4DFSm6mvl{f$SioKwGc2Cv;YHB@VCBiVd< zd0B~RwBk)<>tjvki&9gwz2@-xs5e8KD&EX`18-)%X`7)(Kql8)i4gC4I0RXoc+s(n zqRNMiRn(C?QUvpwgOa%%U%@IAHzZQrK#ChIMaq4HP-IQg+#ob7z@&FA5nGR{xWh*{ z01l!0tT|}9;ile|-ho%my#cPpN6i;;JY|P@t9cY@2fp8oe|I6qwAlP=_1|%PJC5_1 zHv?LdNN51m(WoamAS^2{czqVsdvIsW7gF8m6&{mmhs$*}hAo6E-&3;^^`mQ{oZ>J2 z?b@i{YK{7>MsrDYHU2d*!F=PH_{MD!*V}Gwk{UCx@Kd(yQUzr5{ zd82-7GxRo%?5h1sMEz*VqftM854@;fEsPxdPW7+11BjMJD8j4AmrE;)P{qclE@aVQ_g$dM1DkL1IC-ci3-QiKl&2(jjl>&9H7e)MhVc=!>% z9H5$)6yeiYnnw88ODvg^6*!}my!*w(`!rmZ2E8z7l@>l28U0InBLUkM{nsWdP8Sta z3>p=5F%*pouCXm4G%G&`Krht>>TIk@19coTY;&_vW>0W^z=)js>*m7sxr~`XA-g`% zK;0Gw>ej{sbqE6GG{9IJs3Uoe&k-RBwuqB_IS9J=TnvJUg{ID(%lHzl&HpRxl zt+*J;pvsVWjn5hLb{j81kl!tCT)f~4*3v*7je!f*<*FPs3e_nG8meP$#YGP2CX)kh zE3h3uupC4pgqDM{aL@)-(^|yQCKBmKJ@V5A3FWH%WO6EzHoIV;JY7Ig!(SQ@q#09O z=E++WA6-q)(P=ul}K_}RD;fK+83WYJjoE1vy6D7-!#srlg7ZcQgm&OEXCCHD) z1UFiKTuksEEkD@1am(tMnrKYW@x!=#7GqNRp&Bbc2TjP2MhKN38zHm-IE@f;z92su zA?&gIaPLUQ+!jXg8R$#qr$dy6{7?xxsvHnw2PB=7pZj3Hc35Sh^j%bu6o@@Bs;Db| zMip}+WTIRsD0ib8QM%Y7QM!ns3uq-q3^ky|h+&9wk)XW7qSUydN`$jvmTw|Q2^U4g z++ZaVi6X{&NW+P)hZai@T?-tP55tSOYGW1)Wga_XW0Hc5$P$Ye2{ICm#uk~ScMd7h z&<-(1soz~lVGTlo>`;zy6?s?Kp)9H~MHP8xl}pXhD209x5dwiAbOMhAELoI?!(4o> ztBR0)*d@)r%r?6#q89TEO6IDPWKc4PXM|LeG*@gUd(DLq7g3xWr%J1KhlnVzsQiZ`w}U7q1_pDc z7iEZCLxVVNXSI(pC_4u_?UQXMjS;$SrXav!D5UYd^y?3_2MHIspkPc&s zIr+&U(n*LWIy%%#7fobE#)=TEw8&j-F^+t-Am3%XL8FX#V#qdPZJkDFxRH(c9K_%P zjt(<5c7v~We(duW>Sr23NaTwvn9p>!_Htt26 z7+QP;=~44*2qGRw*iplY$MFREVx-sLLBMhRy-oh{=td9P?>p*r6?|B40*ph?FSjKD zMg@^-Jh5N6)p=_vbn?%UE|GLQv#N;Rz=hX4N7Q81iRE{Cr97on0 zLDrjyjPJ9c^(Qh^jv<4W?DT+~ZH^%0!<*p2+xFsnEF5R3EZ?8A{s|PyA#+wjw!O9p zR{9hiuUiuF@>LdOlES2bT{O7Ud=)G5QFJ$>%Zx8GE=(1~8bjj3&4Q8Nab}_BG$=c; zHZI(waba3;F3*e{;n(A*mL9~&`^GzpHXD`7Bp! zJgW0#S9s5pDNow7T<+`@<)-i3cW0+Ny_GgYGCnMw0HY!F%hB-ou$BZE6~Qk@f6a$+ z`QpR4n($%t=zSPRfDhyP-iL8~`7lOT{D=#8Z+#fgRQWKTsq$ew_v6Fpy7(}j0rFw= z?tB=}MEEd9D|{Fu2R@9k03XJ6z7ON_*@tmK>%+LHjKXMBlAf9&NAg)@d|_nxc7h_QO0b zqr<#~vAjjGyv5A3JAia@v>y57C~>QrYjLM`eO#t?_|LEpTO%j6nm{j;(-TkY(}r7| z>r%W%4?KzG*MnMaHQ6k#)%CU=?$}mN7A(!N7Hb@c129(7b3Lw_u$dJ?a&0af`G#tQT!D=Wne?UTERM7P}DC1GdFH zR)7{Gh7-1UVQx|57JES|rNS29XxjmFxMR<;Rs7LEO8M@bmgK=7N=oyhE!J~oVP3Sw ztFN3 zEvD5P4r4#C#TpKC(rA-D4)dZd=G$;Jl&JNz#VSR5O<|akvs2h&cgpVVw#ASDTa1vI zwzw0%WoD*bZW6SbE*75Jlup(F+-rcIU8f0w>?F$u5NVsBJRinrJeja*-z2H(j>L{fd~uira0IHOl9j z>`wmmie9(Jnym3X_5_=(@jcgMjqind(qyCY{&s0{m=|p_g{Y_gTJeq+7Z-fLZ6q4~ zA{q$;6E?DewbDlRU&%&l=n&5?Nxy37@RVhp1}ju$_FmYp8Z@Nr*+>ljG(Hm=|p%4M2D}u_H!L4<~XOCp{ic40+MRiD8}!Rfk81&&4`yGuHM_;pHZaMUV4cZLxI4uyhHQqt(2q1iJ)_%Ucqw$B=dw3on(#k{I{j@9dyddF?OZreUJ0o_v1 z<*kaaCS}ciMVuWA)ZbJ(su6^4TqXu9JE=S0#L|i`82v^<3Wi7`^pU?`~Uf zeXQPksps-G#OiI3dYsWHmkqIc8>Ald7?*k+G5^EqwFp7H4R4Zr!T$TP`LX#P?$06a zwik~3-n##|tPMarx+EcNV*;!v0k$athKW0dk8L0g^q=Vzc?QzH*V^_4vGxVhK9@&6 z!hYW<_3p9tHpc30lzJ|YyoU99q~7any`EUT9;xT@*q*T7CaL#pw%(>#y-iZj<+06S zyqUye&?AI3$d592b-hjF3e!|2!gFuH+0j9aumjJu#djE_I~Fos`! z7^M)6byF_%GJF`rPCiWJ=*f@z?w4cO$%iq-6ot`8Vt@DtX+{CnJa>@ha|RpTVjBz` zYuF3BA0oRV_8GD33AZOXZshlw+oa?PUa=9rL~y>mKoGe~W67KJP@U#+er4cB@92-Y z;DjEs;i6vi=sCLOCV!bDT=1n~-@>&J7c9C=(0ZM?qpz)ZUAUtZ^==97=%=>Ambrir zZqa@QA5*h!W_^nPn>e*-9EX`~kc*al#;!kRfD=#hRih=0r?uynMaBQ^pS#gF|U3YY5= zZgwJ=6@5N0^t_Gh1+J6A=kshRqq@6>du*8SeazG`@}>cXaMuT8mfIoZjYTzT zpu0XUN^nz-mEEq7J32#TZXx@OZ72r2L+18YBo?7(QK%dgpk0?*HFtR36KTZj){cK= z@!B&bWYXc4jX+%(Iu+!?FnsRKE(|?U=$R7TW^yM*{+p#@&vHbm$kS|kQiN+>a?g_@ zkcam)o1PI-yzW594%FZo5qqG+i8$#Iu=vKBJIX;()OiHVAxn4!3_TU?FFl>1oY8nj zxBSrYuD{s%%yQ3@HG1w#HOW2q^_&$SSaQf*CrNl!4`^@{Q+zz4LGi_(Q>%U4tBCS! zf1Q&RuKfv*d)ae&`ncB=(6DKGvO=ZcpR8b1F){?)$qJej`@k1a63HbV^5W?@$CrD^ zi)M_!iIjy4t^%-+d|B;ZmeV1c^tcy$k@{oLr#RI5xK~yljJ1z@S!Bf!F+-cumy1aV`PrmHTp= zxol^qUix6DNPHOm5+6pp=ELYO`7j(Csexh1 zUIXKx_v_NXZS~|wU)G0l-1#v2vObKytPkTBst=>>@L}98^aPpY8Ji4Ro5NM>vJij7S%#~e;t zt}TamZ6KVV%jD>xA8cA%b^W|8OwtQ|a=p_&cW2xwPuc=f=PXKn=(N!%@)XhcOAo8NVI1VscUcei9sc!aB01bH& z_f%bWw{)ZH0&cW#{xrtx4&_5IyT z9CWLQSjZ>;X5k1apL)t0hb1kaTp55*oalB+g2g-5j+=Q@$SopkR8(IvLA57!`;rL^ z2}(lO2zUv%rNA`;DByti94Uwz>Qcg~;UWxBUH_1td%FbR+_BFtBk-0ROAjjn(!;Ut zInonV)76GkHTTMin}kAqZ6QBnLJC+%T!KP$lexSuN;u2_M zDif|jNFiKc~k5LBD!$QE(vtzPh*XtU>b$z&sV2kwi5b#?q z1->2v6yQ+Lk%Dx+E-+lzbBPgly?d*KcD=qm0uRModOS!DdRRKlk)CwDt~y-TyLE?l zy*oedED6t#g91*6UxEU=USBq$mc*SW*RFRb$M3a_qicv3hrus_HrDleuH0D*dy*W> zPj)?>nJj**L%UwjUmG)S>G4G%poi1dj-DO$4JRf&rtP}k9?iC+-W|d|XDQ$a^^2f@ zQ`U2&AYC6G(PkUgm}u9B2ezqd?uhmSmL892gC5Rc&yk*Vy&m9pU2hL?BmO}OdSu&$ zlk|NLpnx;km!QC|*K^$3_3jk6cD*~deZex$BlkcXd2z)hx?JMMh_ryef>5hu7Mv#BBk%564#Qf8zA}Lxvsu>Vx&|o z7so2Wbr0azbA*<;=IRT-f8R5y9P-VR(|`M1_RA^i?aTQzYJd7`U-{Y-eQP=&e;?Yv z^PfJ>_Gi#=y3K6=tw>z6+=aw7`6v>t+9VR&P(fn5u`!G3ROLW