From e395280ac447e5390b07f0ac23816de813db6ac3 Mon Sep 17 00:00:00 2001 From: Yegor Kozlov Date: Sun, 21 Nov 2010 12:09:29 +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@1037439 13f79535-47bb-0310-9956-ffa450edef68 --- .../apache/poi/ss/formula/CellCacheEntry.java | 12 +- .../poi/ss/formula/CellEvaluationFrame.java | 2 +- .../poi/ss/formula/EvaluationCache.java | 25 +- .../apache/poi/ss/formula/EvaluationCell.java | 5 +- .../poi/ss/formula/EvaluationTracker.java | 9 +- .../org/apache/poi/ss/formula/Formula.java | 8 +- .../poi/ss/formula/FormulaCellCacheEntry.java | 2 +- .../apache/poi/ss/formula/FormulaParser.java | 20 +- .../apache/poi/ss/formula/FormulaShifter.java | 296 +++++++++ .../poi/ss/formula/IEvaluationListener.java | 2 +- .../poi/ss/formula/IStabilityClassifier.java | 4 +- .../apache/poi/ss/formula/LazyAreaEval.java | 6 +- .../apache/poi/ss/formula/LazyRefEval.java | 6 +- .../formula/OperationEvaluationContext.java | 4 +- .../ss/formula/OperationEvaluatorFactory.java | 26 +- .../org/apache/poi/ss/formula/ParseNode.java | 2 +- .../ss/formula/PlainValueCellCacheEntry.java | 2 +- .../apache/poi/ss/formula/SharedFormula.java | 98 +++ .../poi/ss/formula/SheetNameFormatter.java | 223 +++++++ .../poi/ss/formula/SheetRefEvaluator.java | 2 +- .../org/apache/poi/ss/formula/TwoDEval.java | 6 +- .../poi/ss/formula/UserDefinedFunction.java | 9 +- .../poi/ss/formula/WorkbookEvaluator.java | 31 +- .../formula/function/FunctionDataBuilder.java | 91 +++ .../ss/formula/function/FunctionMetadata.java | 90 +++ .../function/FunctionMetadataReader.java | 195 ++++++ .../function/FunctionMetadataRegistry.java | 90 +++ .../formula/functions/AggregateFunction.java | 145 +++++ .../ss/formula/functions/BooleanFunction.java | 146 +++++ .../functions/CalendarFieldFunction.java | 87 +++ .../poi/ss/formula/functions/Choose.java | 57 ++ .../poi/ss/formula/functions/Column.java | 54 ++ .../poi/ss/formula/functions/Columns.java | 45 ++ .../poi/ss/formula/functions/Count.java | 77 +++ .../poi/ss/formula/functions/CountUtils.java | 80 +++ .../poi/ss/formula/functions/Counta.java | 72 +++ .../poi/ss/formula/functions/Countblank.java | 60 ++ .../poi/ss/formula/functions/Countif.java | 528 +++++++++++++++ .../poi/ss/formula/functions/DateFunc.java | 92 +++ .../poi/ss/formula/functions/Days360.java | 118 ++++ .../poi/ss/formula/functions/Errortype.java | 79 +++ .../apache/poi/ss/formula/functions/Even.java | 49 ++ .../ss/formula/functions/FinanceFunction.java | 118 ++++ .../poi/ss/formula/functions/FinanceLib.java | 185 ++++++ .../formula/functions/Fixed0ArgFunction.java | 35 + .../formula/functions/Fixed1ArgFunction.java | 35 + .../formula/functions/Fixed2ArgFunction.java | 35 + .../formula/functions/Fixed3ArgFunction.java | 35 + .../formula/functions/Fixed4ArgFunction.java | 35 + .../ss/formula/functions/FreeRefFunction.java | 51 ++ .../poi/ss/formula/functions/Function.java | 43 ++ .../ss/formula/functions/Function0Arg.java | 32 + .../ss/formula/functions/Function1Arg.java | 32 + .../ss/formula/functions/Function2Arg.java | 32 + .../ss/formula/functions/Function3Arg.java | 32 + .../ss/formula/functions/Function4Arg.java | 32 + .../poi/ss/formula/functions/Hlookup.java | 80 +++ .../poi/ss/formula/functions/Hyperlink.java | 48 ++ .../poi/ss/formula/functions/IfFunc.java | 79 +++ .../poi/ss/formula/functions/Index.java | 171 +++++ .../poi/ss/formula/functions/Indirect.java | 239 +++++++ .../ss/formula/functions/LogicalFunction.java | 119 ++++ .../poi/ss/formula/functions/Lookup.java | 76 +++ .../poi/ss/formula/functions/LookupUtils.java | 603 ++++++++++++++++++ .../poi/ss/formula/functions/Match.java | 251 ++++++++ .../poi/ss/formula/functions/MathX.java | 444 +++++++++++++ .../poi/ss/formula/functions/MinaMaxa.java | 40 ++ .../apache/poi/ss/formula/functions/Mode.java | 133 ++++ .../MultiOperandNumericFunction.java | 197 ++++++ .../apache/poi/ss/formula/functions/Na.java | 33 + .../functions/NotImplementedFunction.java | 47 ++ .../apache/poi/ss/formula/functions/Now.java | 37 ++ .../apache/poi/ss/formula/functions/Npv.java | 107 ++++ .../ss/formula/functions/NumericFunction.java | 495 ++++++++++++++ .../apache/poi/ss/formula/functions/Odd.java | 45 ++ .../poi/ss/formula/functions/Offset.java | 227 +++++++ .../poi/ss/formula/functions/Replace.java | 74 +++ .../poi/ss/formula/functions/RowFunc.java | 59 ++ .../apache/poi/ss/formula/functions/Rows.java | 45 ++ .../poi/ss/formula/functions/StatsLib.java | 137 ++++ .../poi/ss/formula/functions/Substitute.java | 108 ++++ .../poi/ss/formula/functions/Subtotal.java | 100 +++ .../poi/ss/formula/functions/Sumif.java | 127 ++++ .../poi/ss/formula/functions/Sumproduct.java | 231 +++++++ .../poi/ss/formula/functions/Sumx2my2.java | 43 ++ .../poi/ss/formula/functions/Sumx2py2.java | 43 ++ .../poi/ss/formula/functions/Sumxmy2.java | 43 ++ .../apache/poi/ss/formula/functions/T.java | 56 ++ .../ss/formula/functions/TextFunction.java | 372 +++++++++++ .../poi/ss/formula/functions/TimeFunc.java | 87 +++ .../poi/ss/formula/functions/Today.java | 41 ++ .../poi/ss/formula/functions/Value.java | 184 ++++++ .../formula/functions/Var1or2ArgFunction.java | 40 ++ .../formula/functions/Var2or3ArgFunction.java | 40 ++ .../formula/functions/Var3or4ArgFunction.java | 40 ++ .../poi/ss/formula/functions/Vlookup.java | 81 +++ .../formula/functions/XYNumericFunction.java | 177 +++++ .../ss/formula/udf/AggregatingUDFFinder.java | 52 ++ .../poi/ss/formula/udf/DefaultUDFFinder.java | 49 ++ .../apache/poi/ss/formula/udf/UDFFinder.java | 38 ++ .../org/apache/poi/ss/util/CellReference.java | 2 +- .../org/apache/poi/ss/util/WorkbookUtil.java | 2 +- 102 files changed, 9330 insertions(+), 99 deletions(-) create mode 100644 src/java/org/apache/poi/ss/formula/FormulaShifter.java create mode 100644 src/java/org/apache/poi/ss/formula/SharedFormula.java create mode 100644 src/java/org/apache/poi/ss/formula/SheetNameFormatter.java create mode 100644 src/java/org/apache/poi/ss/formula/function/FunctionDataBuilder.java create mode 100644 src/java/org/apache/poi/ss/formula/function/FunctionMetadata.java create mode 100644 src/java/org/apache/poi/ss/formula/function/FunctionMetadataReader.java create mode 100644 src/java/org/apache/poi/ss/formula/function/FunctionMetadataRegistry.java create mode 100644 src/java/org/apache/poi/ss/formula/functions/AggregateFunction.java create mode 100644 src/java/org/apache/poi/ss/formula/functions/BooleanFunction.java create mode 100644 src/java/org/apache/poi/ss/formula/functions/CalendarFieldFunction.java create mode 100644 src/java/org/apache/poi/ss/formula/functions/Choose.java create mode 100644 src/java/org/apache/poi/ss/formula/functions/Column.java create mode 100644 src/java/org/apache/poi/ss/formula/functions/Columns.java create mode 100644 src/java/org/apache/poi/ss/formula/functions/Count.java create mode 100644 src/java/org/apache/poi/ss/formula/functions/CountUtils.java create mode 100644 src/java/org/apache/poi/ss/formula/functions/Counta.java create mode 100644 src/java/org/apache/poi/ss/formula/functions/Countblank.java create mode 100644 src/java/org/apache/poi/ss/formula/functions/Countif.java create mode 100644 src/java/org/apache/poi/ss/formula/functions/DateFunc.java create mode 100644 src/java/org/apache/poi/ss/formula/functions/Days360.java create mode 100644 src/java/org/apache/poi/ss/formula/functions/Errortype.java create mode 100644 src/java/org/apache/poi/ss/formula/functions/Even.java create mode 100644 src/java/org/apache/poi/ss/formula/functions/FinanceFunction.java create mode 100644 src/java/org/apache/poi/ss/formula/functions/FinanceLib.java create mode 100644 src/java/org/apache/poi/ss/formula/functions/Fixed0ArgFunction.java create mode 100644 src/java/org/apache/poi/ss/formula/functions/Fixed1ArgFunction.java create mode 100644 src/java/org/apache/poi/ss/formula/functions/Fixed2ArgFunction.java create mode 100644 src/java/org/apache/poi/ss/formula/functions/Fixed3ArgFunction.java create mode 100644 src/java/org/apache/poi/ss/formula/functions/Fixed4ArgFunction.java create mode 100644 src/java/org/apache/poi/ss/formula/functions/FreeRefFunction.java create mode 100644 src/java/org/apache/poi/ss/formula/functions/Function.java create mode 100644 src/java/org/apache/poi/ss/formula/functions/Function0Arg.java create mode 100644 src/java/org/apache/poi/ss/formula/functions/Function1Arg.java create mode 100644 src/java/org/apache/poi/ss/formula/functions/Function2Arg.java create mode 100644 src/java/org/apache/poi/ss/formula/functions/Function3Arg.java create mode 100644 src/java/org/apache/poi/ss/formula/functions/Function4Arg.java create mode 100644 src/java/org/apache/poi/ss/formula/functions/Hlookup.java create mode 100644 src/java/org/apache/poi/ss/formula/functions/Hyperlink.java create mode 100644 src/java/org/apache/poi/ss/formula/functions/IfFunc.java create mode 100644 src/java/org/apache/poi/ss/formula/functions/Index.java create mode 100644 src/java/org/apache/poi/ss/formula/functions/Indirect.java create mode 100644 src/java/org/apache/poi/ss/formula/functions/LogicalFunction.java create mode 100644 src/java/org/apache/poi/ss/formula/functions/Lookup.java create mode 100644 src/java/org/apache/poi/ss/formula/functions/LookupUtils.java create mode 100644 src/java/org/apache/poi/ss/formula/functions/Match.java create mode 100644 src/java/org/apache/poi/ss/formula/functions/MathX.java create mode 100644 src/java/org/apache/poi/ss/formula/functions/MinaMaxa.java create mode 100644 src/java/org/apache/poi/ss/formula/functions/Mode.java create mode 100644 src/java/org/apache/poi/ss/formula/functions/MultiOperandNumericFunction.java create mode 100644 src/java/org/apache/poi/ss/formula/functions/Na.java create mode 100644 src/java/org/apache/poi/ss/formula/functions/NotImplementedFunction.java create mode 100644 src/java/org/apache/poi/ss/formula/functions/Now.java create mode 100644 src/java/org/apache/poi/ss/formula/functions/Npv.java create mode 100644 src/java/org/apache/poi/ss/formula/functions/NumericFunction.java create mode 100644 src/java/org/apache/poi/ss/formula/functions/Odd.java create mode 100644 src/java/org/apache/poi/ss/formula/functions/Offset.java create mode 100644 src/java/org/apache/poi/ss/formula/functions/Replace.java create mode 100644 src/java/org/apache/poi/ss/formula/functions/RowFunc.java create mode 100644 src/java/org/apache/poi/ss/formula/functions/Rows.java create mode 100644 src/java/org/apache/poi/ss/formula/functions/StatsLib.java create mode 100644 src/java/org/apache/poi/ss/formula/functions/Substitute.java create mode 100644 src/java/org/apache/poi/ss/formula/functions/Subtotal.java create mode 100644 src/java/org/apache/poi/ss/formula/functions/Sumif.java create mode 100644 src/java/org/apache/poi/ss/formula/functions/Sumproduct.java create mode 100644 src/java/org/apache/poi/ss/formula/functions/Sumx2my2.java create mode 100644 src/java/org/apache/poi/ss/formula/functions/Sumx2py2.java create mode 100644 src/java/org/apache/poi/ss/formula/functions/Sumxmy2.java create mode 100644 src/java/org/apache/poi/ss/formula/functions/T.java create mode 100644 src/java/org/apache/poi/ss/formula/functions/TextFunction.java create mode 100644 src/java/org/apache/poi/ss/formula/functions/TimeFunc.java create mode 100644 src/java/org/apache/poi/ss/formula/functions/Today.java create mode 100644 src/java/org/apache/poi/ss/formula/functions/Value.java create mode 100644 src/java/org/apache/poi/ss/formula/functions/Var1or2ArgFunction.java create mode 100644 src/java/org/apache/poi/ss/formula/functions/Var2or3ArgFunction.java create mode 100644 src/java/org/apache/poi/ss/formula/functions/Var3or4ArgFunction.java create mode 100644 src/java/org/apache/poi/ss/formula/functions/Vlookup.java create mode 100644 src/java/org/apache/poi/ss/formula/functions/XYNumericFunction.java create mode 100644 src/java/org/apache/poi/ss/formula/udf/AggregatingUDFFinder.java create mode 100644 src/java/org/apache/poi/ss/formula/udf/DefaultUDFFinder.java create mode 100644 src/java/org/apache/poi/ss/formula/udf/UDFFinder.java diff --git a/src/java/org/apache/poi/ss/formula/CellCacheEntry.java b/src/java/org/apache/poi/ss/formula/CellCacheEntry.java index 0d72ef6264..dbf89e9a5b 100644 --- a/src/java/org/apache/poi/ss/formula/CellCacheEntry.java +++ b/src/java/org/apache/poi/ss/formula/CellCacheEntry.java @@ -17,12 +17,12 @@ package org.apache.poi.ss.formula; -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.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.IEvaluationListener.ICacheEntry; /** diff --git a/src/java/org/apache/poi/ss/formula/CellEvaluationFrame.java b/src/java/org/apache/poi/ss/formula/CellEvaluationFrame.java index 186fe6e1c8..2f9c9521fa 100644 --- a/src/java/org/apache/poi/ss/formula/CellEvaluationFrame.java +++ b/src/java/org/apache/poi/ss/formula/CellEvaluationFrame.java @@ -20,7 +20,7 @@ package org.apache.poi.ss.formula; import java.util.HashSet; import java.util.Set; -import org.apache.poi.hssf.record.formula.eval.ValueEval; +import org.apache.poi.ss.formula.eval.ValueEval; /** * Stores details about the current evaluation of a cell.
diff --git a/src/java/org/apache/poi/ss/formula/EvaluationCache.java b/src/java/org/apache/poi/ss/formula/EvaluationCache.java index e0f89c444e..6b9278e3c5 100644 --- a/src/java/org/apache/poi/ss/formula/EvaluationCache.java +++ b/src/java/org/apache/poi/ss/formula/EvaluationCache.java @@ -17,22 +17,21 @@ package org.apache.poi.ss.formula; -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.hssf.usermodel.HSSFFormulaEvaluator; +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.FormulaCellCache.IEntryOperation; import org.apache.poi.ss.formula.FormulaUsedBlankCellSet.BookSheetKey; import org.apache.poi.ss.formula.PlainCellCache.Loc; +import org.apache.poi.ss.usermodel.Cell; /** - * Performance optimisation for {@link HSSFFormulaEvaluator}. This class stores previously - * calculated values of already visited cells, to avoid unnecessary re-calculation when the - * same cells are referenced multiple times + * Performance optimisation for {@link org.apache.poi.ss.usermodel.FormulaEvaluator}. + * This class stores previously calculated values of already visited cells, + * to avoid unnecessary re-calculation when the same cells are referenced multiple times * * @author Josh Micich */ @@ -57,7 +56,7 @@ final class EvaluationCache { Loc loc = new Loc(bookIndex, sheetIndex, rowIndex, columnIndex); PlainValueCellCacheEntry pcce = _plainCellCache.get(loc); - if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) { + if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) { if (fcce == null) { fcce = new FormulaCellCacheEntry(); if (pcce == null) { @@ -198,7 +197,7 @@ final class EvaluationCache { } public void notifyDeleteCell(int bookIndex, int sheetIndex, EvaluationCell cell) { - if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) { + if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) { FormulaCellCacheEntry fcce = _formulaCellCache.remove(cell); if (fcce == null) { // formula cell has not been evaluated yet diff --git a/src/java/org/apache/poi/ss/formula/EvaluationCell.java b/src/java/org/apache/poi/ss/formula/EvaluationCell.java index e225934061..eef4da18da 100644 --- a/src/java/org/apache/poi/ss/formula/EvaluationCell.java +++ b/src/java/org/apache/poi/ss/formula/EvaluationCell.java @@ -17,8 +17,6 @@ package org.apache.poi.ss.formula; -import java.util.HashMap; - /** * Abstracts a cell for the purpose of formula evaluation. This interface represents both formula * and non-formula cells.
@@ -29,7 +27,8 @@ import java.util.HashMap; */ public interface EvaluationCell { /** - * @return an Object that identifies the underlying cell, suitable for use as a key in a {@link HashMap} + * @return an Object that identifies the underlying cell, + * suitable for use as a key in a {@link java.util.HashMap} */ Object getIdentityKey(); diff --git a/src/java/org/apache/poi/ss/formula/EvaluationTracker.java b/src/java/org/apache/poi/ss/formula/EvaluationTracker.java index 00da33af9e..ef7cb1187b 100644 --- a/src/java/org/apache/poi/ss/formula/EvaluationTracker.java +++ b/src/java/org/apache/poi/ss/formula/EvaluationTracker.java @@ -22,14 +22,13 @@ import java.util.HashSet; import java.util.List; import java.util.Set; -import org.apache.poi.hssf.record.formula.eval.BlankEval; -import org.apache.poi.hssf.record.formula.eval.ErrorEval; -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.ErrorEval; +import org.apache.poi.ss.formula.eval.ValueEval; /** * Instances of this class keep track of multiple dependent cell evaluations due - * to recursive calls to {@link WorkbookEvaluator#evaluate(HSSFCell)} + * to recursive calls to {@link WorkbookEvaluator#evaluate(EvaluationCell)}} * The main purpose of this class is to detect an attempt to evaluate a cell * that is already being evaluated. In other words, it detects circular * references in spreadsheet formulas. diff --git a/src/java/org/apache/poi/ss/formula/Formula.java b/src/java/org/apache/poi/ss/formula/Formula.java index 0eb8a7f6dc..da1ef1af71 100644 --- a/src/java/org/apache/poi/ss/formula/Formula.java +++ b/src/java/org/apache/poi/ss/formula/Formula.java @@ -19,9 +19,6 @@ package org.apache.poi.ss.formula; import java.util.Arrays; -import org.apache.poi.hssf.record.ArrayRecord; -import org.apache.poi.hssf.record.SharedFormulaRecord; -import org.apache.poi.hssf.record.TableRecord; import org.apache.poi.hssf.record.formula.ExpPtg; import org.apache.poi.hssf.record.formula.Ptg; import org.apache.poi.hssf.record.formula.TblPtg; @@ -165,8 +162,9 @@ public class Formula { } /** - * Gets the locator for the corresponding {@link SharedFormulaRecord}, {@link ArrayRecord} or - * {@link TableRecord} if this formula belongs to such a grouping. The {@link CellReference} + * Gets the locator for the corresponding {@link org.apache.poi.hssf.record.SharedFormulaRecord}, + * {@link org.apache.poi.hssf.record.ArrayRecord} or {@link org.apache.poi.hssf.record.TableRecord} + * if this formula belongs to such a grouping. The {@link CellReference} * returned by this method will match the top left corner of the range of that grouping. * The return value is usually not the same as the location of the cell containing this formula. * diff --git a/src/java/org/apache/poi/ss/formula/FormulaCellCacheEntry.java b/src/java/org/apache/poi/ss/formula/FormulaCellCacheEntry.java index a02b772116..2d399494ab 100644 --- a/src/java/org/apache/poi/ss/formula/FormulaCellCacheEntry.java +++ b/src/java/org/apache/poi/ss/formula/FormulaCellCacheEntry.java @@ -21,7 +21,7 @@ import java.util.Collections; import java.util.HashSet; import java.util.Set; -import org.apache.poi.hssf.record.formula.eval.ValueEval; +import org.apache.poi.ss.formula.eval.ValueEval; import org.apache.poi.ss.formula.FormulaUsedBlankCellSet.BookSheetKey; diff --git a/src/java/org/apache/poi/ss/formula/FormulaParser.java b/src/java/org/apache/poi/ss/formula/FormulaParser.java index db46b53e86..4399045832 100644 --- a/src/java/org/apache/poi/ss/formula/FormulaParser.java +++ b/src/java/org/apache/poi/ss/formula/FormulaParser.java @@ -23,9 +23,9 @@ import java.util.regex.Pattern; import org.apache.poi.hssf.record.constant.ErrorConstant; import org.apache.poi.hssf.record.formula.*; -import org.apache.poi.hssf.record.formula.function.FunctionMetadata; -import org.apache.poi.hssf.record.formula.function.FunctionMetadataRegistry; -import org.apache.poi.hssf.usermodel.HSSFErrorConstants; +import org.apache.poi.ss.formula.function.FunctionMetadata; +import org.apache.poi.ss.formula.function.FunctionMetadataRegistry; +import org.apache.poi.ss.usermodel.ErrorConstants; import org.apache.poi.ss.SpreadsheetVersion; import org.apache.poi.ss.util.AreaReference; import org.apache.poi.ss.util.CellReference; @@ -1297,13 +1297,13 @@ public final class FormulaParser { case 'V': if(part1.equals("VALUE")) { Match('!'); - return HSSFErrorConstants.ERROR_VALUE; + return ErrorConstants.ERROR_VALUE; } throw expected("#VALUE!"); case 'R': if(part1.equals("REF")) { Match('!'); - return HSSFErrorConstants.ERROR_REF; + return ErrorConstants.ERROR_REF; } throw expected("#REF!"); case 'D': @@ -1311,21 +1311,21 @@ public final class FormulaParser { Match('/'); Match('0'); Match('!'); - return HSSFErrorConstants.ERROR_DIV_0; + return ErrorConstants.ERROR_DIV_0; } throw expected("#DIV/0!"); case 'N': if(part1.equals("NAME")) { Match('?'); // only one that ends in '?' - return HSSFErrorConstants.ERROR_NAME; + return ErrorConstants.ERROR_NAME; } if(part1.equals("NUM")) { Match('!'); - return HSSFErrorConstants.ERROR_NUM; + return ErrorConstants.ERROR_NUM; } if(part1.equals("NULL")) { Match('!'); - return HSSFErrorConstants.ERROR_NULL; + return ErrorConstants.ERROR_NULL; } if(part1.equals("N")) { Match('/'); @@ -1334,7 +1334,7 @@ public final class FormulaParser { } Match(look); // Note - no '!' or '?' suffix - return HSSFErrorConstants.ERROR_NA; + return ErrorConstants.ERROR_NA; } throw expected("#NAME?, #NUM!, #NULL! or #N/A"); diff --git a/src/java/org/apache/poi/ss/formula/FormulaShifter.java b/src/java/org/apache/poi/ss/formula/FormulaShifter.java new file mode 100644 index 0000000000..750eecc373 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/FormulaShifter.java @@ -0,0 +1,296 @@ +/* ==================================================================== + 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; + +import org.apache.poi.hssf.record.formula.*; + + +/** + * @author Josh Micich + */ +public final class FormulaShifter { + + /** + * Extern sheet index of sheet where moving is occurring + */ + private final int _externSheetIndex; + private final int _firstMovedIndex; + private final int _lastMovedIndex; + private final int _amountToMove; + + private FormulaShifter(int externSheetIndex, int firstMovedIndex, int lastMovedIndex, int amountToMove) { + if (amountToMove == 0) { + throw new IllegalArgumentException("amountToMove must not be zero"); + } + if (firstMovedIndex > lastMovedIndex) { + throw new IllegalArgumentException("firstMovedIndex, lastMovedIndex out of order"); + } + _externSheetIndex = externSheetIndex; + _firstMovedIndex = firstMovedIndex; + _lastMovedIndex = lastMovedIndex; + _amountToMove = amountToMove; + } + + public static FormulaShifter createForRowShift(int externSheetIndex, int firstMovedRowIndex, int lastMovedRowIndex, int numberOfRowsToMove) { + return new FormulaShifter(externSheetIndex, firstMovedRowIndex, lastMovedRowIndex, numberOfRowsToMove); + } + + public String toString() { + StringBuffer sb = new StringBuffer(); + + sb.append(getClass().getName()); + sb.append(" ["); + sb.append(_firstMovedIndex); + sb.append(_lastMovedIndex); + sb.append(_amountToMove); + return sb.toString(); + } + + /** + * @param ptgs - if necessary, will get modified by this method + * @param currentExternSheetIx - the extern sheet index of the sheet that contains the formula being adjusted + * @return true if a change was made to the formula tokens + */ + public boolean adjustFormula(Ptg[] ptgs, int currentExternSheetIx) { + boolean refsWereChanged = false; + for(int i=0; itrue if this Ptg needed to be changed + */ + private Ptg adjustPtgDueToRowMove(Ptg ptg, int currentExternSheetIx) { + if(ptg instanceof RefPtg) { + if (currentExternSheetIx != _externSheetIndex) { + // local refs on other sheets are unaffected + return null; + } + RefPtg rptg = (RefPtg)ptg; + return rowMoveRefPtg(rptg); + } + if(ptg instanceof Ref3DPtg) { + Ref3DPtg rptg = (Ref3DPtg)ptg; + if (_externSheetIndex != rptg.getExternSheetIndex()) { + // only move 3D refs that refer to the sheet with cells being moved + // (currentExternSheetIx is irrelevant) + return null; + } + return rowMoveRefPtg(rptg); + } + if(ptg instanceof Area2DPtgBase) { + if (currentExternSheetIx != _externSheetIndex) { + // local refs on other sheets are unaffected + return ptg; + } + return rowMoveAreaPtg((Area2DPtgBase)ptg); + } + if(ptg instanceof Area3DPtg) { + Area3DPtg aptg = (Area3DPtg)ptg; + if (_externSheetIndex != aptg.getExternSheetIndex()) { + // only move 3D refs that refer to the sheet with cells being moved + // (currentExternSheetIx is irrelevant) + return null; + } + return rowMoveAreaPtg(aptg); + } + return null; + } + + private Ptg rowMoveRefPtg(RefPtgBase rptg) { + int refRow = rptg.getRow(); + if (_firstMovedIndex <= refRow && refRow <= _lastMovedIndex) { + // Rows being moved completely enclose the ref. + // - move the area ref along with the rows regardless of destination + rptg.setRow(refRow + _amountToMove); + return rptg; + } + // else rules for adjusting area may also depend on the destination of the moved rows + + int destFirstRowIndex = _firstMovedIndex + _amountToMove; + int destLastRowIndex = _lastMovedIndex + _amountToMove; + + // ref is outside source rows + // check for clashes with destination + + if (destLastRowIndex < refRow || refRow < destFirstRowIndex) { + // destination rows are completely outside ref + return null; + } + + if (destFirstRowIndex <= refRow && refRow <= destLastRowIndex) { + // destination rows enclose the area (possibly exactly) + return createDeletedRef(rptg); + } + throw new IllegalStateException("Situation not covered: (" + _firstMovedIndex + ", " + + _lastMovedIndex + ", " + _amountToMove + ", " + refRow + ", " + refRow + ")"); + } + + private Ptg rowMoveAreaPtg(AreaPtgBase aptg) { + int aFirstRow = aptg.getFirstRow(); + int aLastRow = aptg.getLastRow(); + if (_firstMovedIndex <= aFirstRow && aLastRow <= _lastMovedIndex) { + // Rows being moved completely enclose the area ref. + // - move the area ref along with the rows regardless of destination + aptg.setFirstRow(aFirstRow + _amountToMove); + aptg.setLastRow(aLastRow + _amountToMove); + return aptg; + } + // else rules for adjusting area may also depend on the destination of the moved rows + + int destFirstRowIndex = _firstMovedIndex + _amountToMove; + int destLastRowIndex = _lastMovedIndex + _amountToMove; + + if (aFirstRow < _firstMovedIndex && _lastMovedIndex < aLastRow) { + // Rows moved were originally *completely* within the area ref + + // If the destination of the rows overlaps either the top + // or bottom of the area ref there will be a change + if (destFirstRowIndex < aFirstRow && aFirstRow <= destLastRowIndex) { + // truncate the top of the area by the moved rows + aptg.setFirstRow(destLastRowIndex+1); + return aptg; + } else if (destFirstRowIndex <= aLastRow && aLastRow < destLastRowIndex) { + // truncate the bottom of the area by the moved rows + aptg.setLastRow(destFirstRowIndex-1); + return aptg; + } + // else - rows have moved completely outside the area ref, + // or still remain completely within the area ref + return null; // - no change to the area + } + if (_firstMovedIndex <= aFirstRow && aFirstRow <= _lastMovedIndex) { + // Rows moved include the first row of the area ref, but not the last row + // btw: (aLastRow > _lastMovedIndex) + if (_amountToMove < 0) { + // simple case - expand area by shifting top upward + aptg.setFirstRow(aFirstRow + _amountToMove); + return aptg; + } + if (destFirstRowIndex > aLastRow) { + // in this case, excel ignores the row move + return null; + } + int newFirstRowIx = aFirstRow + _amountToMove; + if (destLastRowIndex < aLastRow) { + // end of area is preserved (will remain exact same row) + // the top area row is moved simply + aptg.setFirstRow(newFirstRowIx); + return aptg; + } + // else - bottom area row has been replaced - both area top and bottom may move now + int areaRemainingTopRowIx = _lastMovedIndex + 1; + if (destFirstRowIndex > areaRemainingTopRowIx) { + // old top row of area has moved deep within the area, and exposed a new top row + newFirstRowIx = areaRemainingTopRowIx; + } + aptg.setFirstRow(newFirstRowIx); + aptg.setLastRow(Math.max(aLastRow, destLastRowIndex)); + return aptg; + } + if (_firstMovedIndex <= aLastRow && aLastRow <= _lastMovedIndex) { + // Rows moved include the last row of the area ref, but not the first + // btw: (aFirstRow < _firstMovedIndex) + if (_amountToMove > 0) { + // simple case - expand area by shifting bottom downward + aptg.setLastRow(aLastRow + _amountToMove); + return aptg; + } + if (destLastRowIndex < aFirstRow) { + // in this case, excel ignores the row move + return null; + } + int newLastRowIx = aLastRow + _amountToMove; + if (destFirstRowIndex > aFirstRow) { + // top of area is preserved (will remain exact same row) + // the bottom area row is moved simply + aptg.setLastRow(newLastRowIx); + return aptg; + } + // else - top area row has been replaced - both area top and bottom may move now + int areaRemainingBottomRowIx = _firstMovedIndex - 1; + if (destLastRowIndex < areaRemainingBottomRowIx) { + // old bottom row of area has moved up deep within the area, and exposed a new bottom row + newLastRowIx = areaRemainingBottomRowIx; + } + aptg.setFirstRow(Math.min(aFirstRow, destFirstRowIndex)); + aptg.setLastRow(newLastRowIx); + return aptg; + } + // else source rows include none of the rows of the area ref + // check for clashes with destination + + if (destLastRowIndex < aFirstRow || aLastRow < destFirstRowIndex) { + // destination rows are completely outside area ref + return null; + } + + if (destFirstRowIndex <= aFirstRow && aLastRow <= destLastRowIndex) { + // destination rows enclose the area (possibly exactly) + return createDeletedRef(aptg); + } + + if (aFirstRow <= destFirstRowIndex && destLastRowIndex <= aLastRow) { + // destination rows are within area ref (possibly exact on top or bottom, but not both) + return null; // - no change to area + } + + if (destFirstRowIndex < aFirstRow && aFirstRow <= destLastRowIndex) { + // dest rows overlap top of area + // - truncate the top + aptg.setFirstRow(destLastRowIndex+1); + return aptg; + } + if (destFirstRowIndex < aLastRow && aLastRow <= destLastRowIndex) { + // dest rows overlap bottom of area + // - truncate the bottom + aptg.setLastRow(destFirstRowIndex-1); + return aptg; + } + throw new IllegalStateException("Situation not covered: (" + _firstMovedIndex + ", " + + _lastMovedIndex + ", " + _amountToMove + ", " + aFirstRow + ", " + aLastRow + ")"); + } + + private static Ptg createDeletedRef(Ptg ptg) { + if (ptg instanceof RefPtg) { + return new RefErrorPtg(); + } + if (ptg instanceof Ref3DPtg) { + Ref3DPtg rptg = (Ref3DPtg) ptg; + return new DeletedRef3DPtg(rptg.getExternSheetIndex()); + } + if (ptg instanceof AreaPtg) { + return new AreaErrPtg(); + } + if (ptg instanceof Area3DPtg) { + Area3DPtg area3DPtg = (Area3DPtg) ptg; + return new DeletedArea3DPtg(area3DPtg.getExternSheetIndex()); + } + + throw new IllegalArgumentException("Unexpected ref ptg class (" + ptg.getClass().getName() + ")"); + } +} diff --git a/src/java/org/apache/poi/ss/formula/IEvaluationListener.java b/src/java/org/apache/poi/ss/formula/IEvaluationListener.java index bad90e7401..bab3ba325e 100644 --- a/src/java/org/apache/poi/ss/formula/IEvaluationListener.java +++ b/src/java/org/apache/poi/ss/formula/IEvaluationListener.java @@ -17,7 +17,7 @@ package org.apache.poi.ss.formula; -import org.apache.poi.hssf.record.formula.eval.ValueEval; +import org.apache.poi.ss.formula.eval.ValueEval; /** * Tests can implement this class to track the internal working of the {@link WorkbookEvaluator}.
diff --git a/src/java/org/apache/poi/ss/formula/IStabilityClassifier.java b/src/java/org/apache/poi/ss/formula/IStabilityClassifier.java index a7d2d3f478..826a61016c 100644 --- a/src/java/org/apache/poi/ss/formula/IStabilityClassifier.java +++ b/src/java/org/apache/poi/ss/formula/IStabilityClassifier.java @@ -17,8 +17,6 @@ package org.apache.poi.ss.formula; -import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator; - /** * Used to help optimise cell evaluation result caching by allowing applications to specify which * parts of a workbook are final.
@@ -49,7 +47,7 @@ import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator; *
  • To retain freedom to change any cell definition at any time, an application may classify all * cells as 'not final'. This freedom comes at the expense of greater memory consumption.
    • *
  • For the purpose of these classifications, setting the cached formula result of a cell (for - * example in {@link HSSFFormulaEvaluator#evaluateFormulaCell(org.apache.poi.ss.usermodel.Cell)}) + * example in {@link org.apache.poi.ss.usermodel.FormulaEvaluator#evaluateFormulaCell(org.apache.poi.ss.usermodel.Cell)}) * does not constitute changing the definition of the cell.
    • *
  • Updating cells which have been classified as 'final' will cause the evaluator to behave * unpredictably (typically ignoring the update).
    • diff --git a/src/java/org/apache/poi/ss/formula/LazyAreaEval.java b/src/java/org/apache/poi/ss/formula/LazyAreaEval.java index 5063d1e6e8..65eaa0a5cc 100644 --- a/src/java/org/apache/poi/ss/formula/LazyAreaEval.java +++ b/src/java/org/apache/poi/ss/formula/LazyAreaEval.java @@ -19,9 +19,9 @@ package org.apache.poi.ss.formula; import org.apache.poi.hssf.record.formula.AreaI; import org.apache.poi.hssf.record.formula.AreaI.OffsetArea; -import org.apache.poi.hssf.record.formula.eval.AreaEval; -import org.apache.poi.hssf.record.formula.eval.AreaEvalBase; -import org.apache.poi.hssf.record.formula.eval.ValueEval; +import org.apache.poi.ss.formula.eval.AreaEval; +import org.apache.poi.ss.formula.eval.AreaEvalBase; +import org.apache.poi.ss.formula.eval.ValueEval; import org.apache.poi.hssf.util.CellReference; /** diff --git a/src/java/org/apache/poi/ss/formula/LazyRefEval.java b/src/java/org/apache/poi/ss/formula/LazyRefEval.java index 7a4aabea11..63dffdeb18 100644 --- a/src/java/org/apache/poi/ss/formula/LazyRefEval.java +++ b/src/java/org/apache/poi/ss/formula/LazyRefEval.java @@ -19,9 +19,9 @@ package org.apache.poi.ss.formula; import org.apache.poi.hssf.record.formula.AreaI; import org.apache.poi.hssf.record.formula.AreaI.OffsetArea; -import org.apache.poi.hssf.record.formula.eval.AreaEval; -import org.apache.poi.hssf.record.formula.eval.RefEvalBase; -import org.apache.poi.hssf.record.formula.eval.ValueEval; +import org.apache.poi.ss.formula.eval.AreaEval; +import org.apache.poi.ss.formula.eval.RefEvalBase; +import org.apache.poi.ss.formula.eval.ValueEval; import org.apache.poi.hssf.util.CellReference; /** diff --git a/src/java/org/apache/poi/ss/formula/OperationEvaluationContext.java b/src/java/org/apache/poi/ss/formula/OperationEvaluationContext.java index 71db36e0c6..e7355a49ff 100644 --- a/src/java/org/apache/poi/ss/formula/OperationEvaluationContext.java +++ b/src/java/org/apache/poi/ss/formula/OperationEvaluationContext.java @@ -21,8 +21,8 @@ import org.apache.poi.hssf.record.formula.Area3DPtg; import org.apache.poi.hssf.record.formula.NameXPtg; import org.apache.poi.hssf.record.formula.Ptg; import org.apache.poi.hssf.record.formula.Ref3DPtg; -import org.apache.poi.hssf.record.formula.eval.*; -import org.apache.poi.hssf.record.formula.functions.FreeRefFunction; +import org.apache.poi.ss.formula.eval.*; +import org.apache.poi.ss.formula.functions.FreeRefFunction; import org.apache.poi.ss.SpreadsheetVersion; import org.apache.poi.ss.formula.CollaboratingWorkbooksEnvironment.WorkbookNotFoundException; import org.apache.poi.ss.formula.EvaluationWorkbook.ExternalName; diff --git a/src/java/org/apache/poi/ss/formula/OperationEvaluatorFactory.java b/src/java/org/apache/poi/ss/formula/OperationEvaluatorFactory.java index 87cef4f9cd..d1ca78fc67 100644 --- a/src/java/org/apache/poi/ss/formula/OperationEvaluatorFactory.java +++ b/src/java/org/apache/poi/ss/formula/OperationEvaluatorFactory.java @@ -41,19 +41,19 @@ import org.apache.poi.hssf.record.formula.RangePtg; import org.apache.poi.hssf.record.formula.SubtractPtg; import org.apache.poi.hssf.record.formula.UnaryMinusPtg; import org.apache.poi.hssf.record.formula.UnaryPlusPtg; -import org.apache.poi.hssf.record.formula.eval.ConcatEval; -import org.apache.poi.hssf.record.formula.eval.FunctionEval; -import org.apache.poi.hssf.record.formula.eval.IntersectionEval; -import org.apache.poi.hssf.record.formula.eval.PercentEval; -import org.apache.poi.hssf.record.formula.eval.RangeEval; -import org.apache.poi.hssf.record.formula.eval.RelationalOperationEval; -import org.apache.poi.hssf.record.formula.eval.TwoOperandNumericOperation; -import org.apache.poi.hssf.record.formula.eval.UnaryMinusEval; -import org.apache.poi.hssf.record.formula.eval.UnaryPlusEval; -import org.apache.poi.hssf.record.formula.eval.ValueEval; -import org.apache.poi.hssf.record.formula.function.FunctionMetadataRegistry; -import org.apache.poi.hssf.record.formula.functions.Function; -import org.apache.poi.hssf.record.formula.functions.Indirect; +import org.apache.poi.ss.formula.eval.ConcatEval; +import org.apache.poi.ss.formula.eval.FunctionEval; +import org.apache.poi.ss.formula.eval.IntersectionEval; +import org.apache.poi.ss.formula.eval.PercentEval; +import org.apache.poi.ss.formula.eval.RangeEval; +import org.apache.poi.ss.formula.eval.RelationalOperationEval; +import org.apache.poi.ss.formula.eval.TwoOperandNumericOperation; +import org.apache.poi.ss.formula.eval.UnaryMinusEval; +import org.apache.poi.ss.formula.eval.UnaryPlusEval; +import org.apache.poi.ss.formula.eval.ValueEval; +import org.apache.poi.ss.formula.function.FunctionMetadataRegistry; +import org.apache.poi.ss.formula.functions.Function; +import org.apache.poi.ss.formula.functions.Indirect; /** * This class creates OperationEval instances to help evaluate OperationPtg diff --git a/src/java/org/apache/poi/ss/formula/ParseNode.java b/src/java/org/apache/poi/ss/formula/ParseNode.java index 9b2859daa5..ebe30d40aa 100644 --- a/src/java/org/apache/poi/ss/formula/ParseNode.java +++ b/src/java/org/apache/poi/ss/formula/ParseNode.java @@ -23,7 +23,7 @@ import org.apache.poi.hssf.record.formula.FuncVarPtg; import org.apache.poi.hssf.record.formula.MemAreaPtg; import org.apache.poi.hssf.record.formula.MemFuncPtg; import org.apache.poi.hssf.record.formula.Ptg; -import org.apache.poi.hssf.record.formula.function.FunctionMetadataRegistry; +import org.apache.poi.ss.formula.function.FunctionMetadataRegistry; /** * Represents a syntactic element from a formula by encapsulating the corresponding Ptg * token. Each ParseNode may have child ParseNodes in the case when the wrapped diff --git a/src/java/org/apache/poi/ss/formula/PlainValueCellCacheEntry.java b/src/java/org/apache/poi/ss/formula/PlainValueCellCacheEntry.java index d1ffb51fa3..171b12e179 100644 --- a/src/java/org/apache/poi/ss/formula/PlainValueCellCacheEntry.java +++ b/src/java/org/apache/poi/ss/formula/PlainValueCellCacheEntry.java @@ -17,7 +17,7 @@ package org.apache.poi.ss.formula; -import org.apache.poi.hssf.record.formula.eval.ValueEval; +import org.apache.poi.ss.formula.eval.ValueEval; /** * Used for non-formula cells, primarily to keep track of the referencing (formula) cells. diff --git a/src/java/org/apache/poi/ss/formula/SharedFormula.java b/src/java/org/apache/poi/ss/formula/SharedFormula.java new file mode 100644 index 0000000000..70cbeffe27 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/SharedFormula.java @@ -0,0 +1,98 @@ +/* ==================================================================== + 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; + +import org.apache.poi.hssf.record.formula.*; +import org.apache.poi.ss.SpreadsheetVersion; + +/** + * Encapsulates logic to convert shared formulaa into non shared equivalent + */ +public class SharedFormula { + + private final int _columnWrappingMask; + private final int _rowWrappingMask; + + public SharedFormula(SpreadsheetVersion ssVersion){ + _columnWrappingMask = ssVersion.getLastColumnIndex(); //"IV" for .xls and "XFD" for .xlsx + _rowWrappingMask = ssVersion.getLastRowIndex(); + } + + /** + * Creates a non shared formula from the shared formula counterpart, i.e. + * Converts the shared formula into the equivalent {@link org.apache.poi.hssf.record.formula.Ptg} array that it would have, + * were it not shared. + * + * @param ptgs parsed tokens of the shared formula + * @param formulaRow + * @param formulaColumn + */ + public Ptg[] convertSharedFormulas(Ptg[] ptgs, int formulaRow, int formulaColumn) { + + Ptg[] newPtgStack = new Ptg[ptgs.length]; + + for (int k = 0; k < ptgs.length; k++) { + Ptg ptg = ptgs[k]; + byte originalOperandClass = -1; + if (!ptg.isBaseToken()) { + originalOperandClass = ptg.getPtgClass(); + } + if (ptg instanceof RefPtgBase) { + RefPtgBase refNPtg = (RefPtgBase)ptg; + ptg = new RefPtg(fixupRelativeRow(formulaRow,refNPtg.getRow(),refNPtg.isRowRelative()), + fixupRelativeColumn(formulaColumn,refNPtg.getColumn(),refNPtg.isColRelative()), + refNPtg.isRowRelative(), + refNPtg.isColRelative()); + ptg.setClass(originalOperandClass); + } else if (ptg instanceof AreaPtgBase) { + AreaPtgBase areaNPtg = (AreaPtgBase)ptg; + ptg = new AreaPtg(fixupRelativeRow(formulaRow,areaNPtg.getFirstRow(),areaNPtg.isFirstRowRelative()), + fixupRelativeRow(formulaRow,areaNPtg.getLastRow(),areaNPtg.isLastRowRelative()), + fixupRelativeColumn(formulaColumn,areaNPtg.getFirstColumn(),areaNPtg.isFirstColRelative()), + fixupRelativeColumn(formulaColumn,areaNPtg.getLastColumn(),areaNPtg.isLastColRelative()), + areaNPtg.isFirstRowRelative(), + areaNPtg.isLastRowRelative(), + areaNPtg.isFirstColRelative(), + areaNPtg.isLastColRelative()); + ptg.setClass(originalOperandClass); + } else if (ptg instanceof OperandPtg) { + // Any subclass of OperandPtg is mutable, so it's safest to not share these instances. + ptg = ((OperandPtg) ptg).copy(); + } else { + // all other Ptgs are immutable and can be shared + } + newPtgStack[k] = ptg; + } + return newPtgStack; + } + + private int fixupRelativeColumn(int currentcolumn, int column, boolean relative) { + if(relative) { + // mask out upper bits to produce 'wrapping' at the maximum column ("IV" for .xls and "XFD" for .xlsx) + return (column + currentcolumn) & _columnWrappingMask; + } + return column; + } + + private int fixupRelativeRow(int currentrow, int row, boolean relative) { + if(relative) { + return (row+currentrow) & _rowWrappingMask; + } + return row; + } + +} diff --git a/src/java/org/apache/poi/ss/formula/SheetNameFormatter.java b/src/java/org/apache/poi/ss/formula/SheetNameFormatter.java new file mode 100644 index 0000000000..4797048875 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/SheetNameFormatter.java @@ -0,0 +1,223 @@ +/* ==================================================================== + 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; + +import java.util.regex.Matcher; +import java.util.regex.Pattern; + +import org.apache.poi.ss.util.CellReference; +import org.apache.poi.ss.SpreadsheetVersion; + +/** + * Formats sheet names for use in formula expressions. + * + * @author Josh Micich + */ +public final class SheetNameFormatter { + + private static final char DELIMITER = '\''; + + /** + * Matches a single cell ref with no absolute ('$') markers + */ + private static final Pattern CELL_REF_PATTERN = Pattern.compile("([A-Za-z]+)([0-9]+)"); + + private SheetNameFormatter() { + // no instances of this class + } + /** + * Used to format sheet names as they would appear in cell formula expressions. + * @return the sheet name unchanged if there is no need for delimiting. Otherwise the sheet + * name is enclosed in single quotes ('). Any single quotes which were already present in the + * sheet name will be converted to double single quotes (''). + */ + public static String format(String rawSheetName) { + StringBuffer sb = new StringBuffer(rawSheetName.length() + 2); + appendFormat(sb, rawSheetName); + return sb.toString(); + } + + /** + * Convenience method for ({@link #format(String)}) when a StringBuffer is already available. + * + * @param out - sheet name will be appended here possibly with delimiting quotes + */ + public static void appendFormat(StringBuffer out, String rawSheetName) { + boolean needsQuotes = needsDelimiting(rawSheetName); + if(needsQuotes) { + out.append(DELIMITER); + appendAndEscape(out, rawSheetName); + out.append(DELIMITER); + } else { + out.append(rawSheetName); + } + } + public static void appendFormat(StringBuffer out, String workbookName, String rawSheetName) { + boolean needsQuotes = needsDelimiting(workbookName) || needsDelimiting(rawSheetName); + if(needsQuotes) { + out.append(DELIMITER); + out.append('['); + appendAndEscape(out, workbookName.replace('[', '(').replace(']', ')')); + out.append(']'); + appendAndEscape(out, rawSheetName); + out.append(DELIMITER); + } else { + out.append('['); + out.append(workbookName); + out.append(']'); + out.append(rawSheetName); + } + } + + private static void appendAndEscape(StringBuffer sb, String rawSheetName) { + int len = rawSheetName.length(); + for(int i=0; itrue if the presence of the specified character in a sheet name would + * require the sheet name to be delimited in formulas. This includes every non-alphanumeric + * character besides underscore '_' and dot '.'. + */ + /* package */ static boolean isSpecialChar(char ch) { + // note - Character.isJavaIdentifierPart() would allow dollars '$' + if(Character.isLetterOrDigit(ch)) { + return false; + } + switch(ch) { + case '.': // dot is OK + case '_': // underscore is OK + return false; + case '\n': + case '\r': + case '\t': + throw new RuntimeException("Illegal character (0x" + + Integer.toHexString(ch) + ") found in sheet name"); + } + return true; + } + + + /** + * Used to decide whether sheet names like 'AB123' need delimiting due to the fact that they + * look like cell references. + *

    + * This code is currently being used for translating formulas represented with Ptg + * tokens into human readable text form. In formula expressions, a sheet name always has a + * trailing '!' so there is little chance for ambiguity. It doesn't matter too much what this + * method returns but it is worth noting the likely consumers of these formula text strings: + *

      + *
    1. POI's own formula parser
      2. + *
    2. Visual reading by human
      3. + *
    3. VBA automation entry into Excel cell contents e.g. ActiveCell.Formula = "=c64!A1"
      4. + *
    4. Manual entry into Excel cell contents
      5. + *
    5. Some third party formula parser
      6. + *
    + * + * At the time of writing, POI's formula parser tolerates cell-like sheet names in formulas + * with or without delimiters. The same goes for Excel(2007), both manual and automated entry. + *

    + * For better or worse this implementation attempts to replicate Excel's formula renderer. + * Excel uses range checking on the apparent 'row' and 'column' components. Note however that + * the maximum sheet size varies across versions. + * @see org.apache.poi.ss.util.CellReference + */ + /* package */ static boolean cellReferenceIsWithinRange(String lettersPrefix, String numbersSuffix) { + return CellReference.cellReferenceIsWithinRange(lettersPrefix, numbersSuffix, SpreadsheetVersion.EXCEL97); + } + + /** + * Note - this method assumes the specified rawSheetName has only letters and digits. It + * cannot be used to match absolute or range references (using the dollar or colon char). + *

    + * Some notable cases: + *

    + * + * + * + * + * + * + * + * + * + * + *
    Input Result Comments
    "A1"  true 
    "a111"  true 
    "AA"  false 
    "aa1"  true 
    "A1A"  false 
    "A1A1"  false 
    "A$1:$C$20"  falseNot a plain cell reference
    "SALES20080101"  trueStill needs delimiting even though well out of range
    + * + * @return true if there is any possible ambiguity that the specified rawSheetName + * could be interpreted as a valid cell name. + */ + /* package */ static boolean nameLooksLikePlainCellReference(String rawSheetName) { + Matcher matcher = CELL_REF_PATTERN.matcher(rawSheetName); + if(!matcher.matches()) { + return false; + } + + // rawSheetName == "Sheet1" gets this far. + String lettersPrefix = matcher.group(1); + String numbersSuffix = matcher.group(2); + return cellReferenceIsWithinRange(lettersPrefix, numbersSuffix); + } +} diff --git a/src/java/org/apache/poi/ss/formula/SheetRefEvaluator.java b/src/java/org/apache/poi/ss/formula/SheetRefEvaluator.java index 97568cfec4..e6d15861ef 100644 --- a/src/java/org/apache/poi/ss/formula/SheetRefEvaluator.java +++ b/src/java/org/apache/poi/ss/formula/SheetRefEvaluator.java @@ -17,7 +17,7 @@ package org.apache.poi.ss.formula; -import org.apache.poi.hssf.record.formula.eval.ValueEval; +import org.apache.poi.ss.formula.eval.ValueEval; /** * * diff --git a/src/java/org/apache/poi/ss/formula/TwoDEval.java b/src/java/org/apache/poi/ss/formula/TwoDEval.java index fbb24e8dde..7b9411ae01 100644 --- a/src/java/org/apache/poi/ss/formula/TwoDEval.java +++ b/src/java/org/apache/poi/ss/formula/TwoDEval.java @@ -17,11 +17,11 @@ package org.apache.poi.ss.formula; -import org.apache.poi.hssf.record.formula.eval.AreaEval; -import org.apache.poi.hssf.record.formula.eval.ValueEval; +import org.apache.poi.ss.formula.eval.AreaEval; +import org.apache.poi.ss.formula.eval.ValueEval; /** - * Common interface of {@link AreaEval} and {@link org.apache.poi.hssf.record.formula.eval.AreaEvalBase} + * Common interface of {@link AreaEval} and {@link org.apache.poi.ss.formula.eval.AreaEvalBase} * * @author Josh Micich */ diff --git a/src/java/org/apache/poi/ss/formula/UserDefinedFunction.java b/src/java/org/apache/poi/ss/formula/UserDefinedFunction.java index 7bf612a47c..c1b22ba9ba 100644 --- a/src/java/org/apache/poi/ss/formula/UserDefinedFunction.java +++ b/src/java/org/apache/poi/ss/formula/UserDefinedFunction.java @@ -17,11 +17,10 @@ package org.apache.poi.ss.formula; -import org.apache.poi.hssf.record.formula.eval.NameEval; -import org.apache.poi.hssf.record.formula.eval.NameXEval; -import org.apache.poi.hssf.record.formula.eval.ValueEval; -import org.apache.poi.hssf.record.formula.functions.FreeRefFunction; -import org.apache.poi.ss.formula.OperationEvaluationContext; +import org.apache.poi.ss.formula.eval.NameEval; +import org.apache.poi.ss.formula.eval.NameXEval; +import org.apache.poi.ss.formula.eval.ValueEval; +import org.apache.poi.ss.formula.functions.FreeRefFunction; import org.apache.poi.ss.formula.eval.NotImplementedException; /** * diff --git a/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java b/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java index 60d31a707d..5717be22b0 100644 --- a/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java +++ b/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java @@ -48,23 +48,20 @@ import org.apache.poi.hssf.record.formula.RefPtg; import org.apache.poi.hssf.record.formula.StringPtg; import org.apache.poi.hssf.record.formula.UnionPtg; import org.apache.poi.hssf.record.formula.UnknownPtg; -import org.apache.poi.hssf.record.formula.eval.AreaEval; -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.EvaluationException; -import org.apache.poi.hssf.record.formula.eval.MissingArgEval; -import org.apache.poi.hssf.record.formula.eval.NameEval; -import org.apache.poi.hssf.record.formula.eval.NameXEval; -import org.apache.poi.hssf.record.formula.eval.NumberEval; -import org.apache.poi.hssf.record.formula.eval.OperandResolver; -import org.apache.poi.hssf.record.formula.eval.RefEval; -import org.apache.poi.hssf.record.formula.eval.StringEval; -import org.apache.poi.hssf.record.formula.eval.ValueEval; -import org.apache.poi.hssf.record.formula.functions.Choose; -import org.apache.poi.hssf.record.formula.functions.FreeRefFunction; -import org.apache.poi.hssf.record.formula.functions.IfFunc; -import org.apache.poi.hssf.record.formula.udf.UDFFinder; +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.EvaluationException; +import org.apache.poi.ss.formula.eval.MissingArgEval; +import org.apache.poi.ss.formula.eval.NameEval; +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.Choose; +import org.apache.poi.ss.formula.functions.FreeRefFunction; +import org.apache.poi.ss.formula.functions.IfFunc; +import org.apache.poi.ss.formula.udf.UDFFinder; import org.apache.poi.hssf.usermodel.HSSFEvaluationWorkbook; import org.apache.poi.hssf.util.CellReference; import org.apache.poi.ss.formula.CollaboratingWorkbooksEnvironment.WorkbookNotFoundException; diff --git a/src/java/org/apache/poi/ss/formula/function/FunctionDataBuilder.java b/src/java/org/apache/poi/ss/formula/function/FunctionDataBuilder.java new file mode 100644 index 0000000000..6109cb7b94 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/function/FunctionDataBuilder.java @@ -0,0 +1,91 @@ +/* ==================================================================== + 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.function; + +import java.util.HashMap; +import java.util.HashSet; +import java.util.Map; +import java.util.Set; + +/** + * Temporarily collects FunctionMetadata instances for creation of a + * FunctionMetadataRegistry. + * + * @author Josh Micich + */ +final class FunctionDataBuilder { + private int _maxFunctionIndex; + private final Map _functionDataByName; + private final Map _functionDataByIndex; + /** stores indexes of all functions with footnotes (i.e. whose definitions might change) */ + private final Set _mutatingFunctionIndexes; + + public FunctionDataBuilder(int sizeEstimate) { + _maxFunctionIndex = -1; + _functionDataByName = new HashMap(sizeEstimate * 3 / 2); + _functionDataByIndex = new HashMap(sizeEstimate * 3 / 2); + _mutatingFunctionIndexes = new HashSet(); + } + + public void add(int functionIndex, String functionName, int minParams, int maxParams, + byte returnClassCode, byte[] parameterClassCodes, boolean hasFootnote) { + FunctionMetadata fm = new FunctionMetadata(functionIndex, functionName, minParams, maxParams, + returnClassCode, parameterClassCodes); + + Integer indexKey = Integer.valueOf(functionIndex); + + + if(functionIndex > _maxFunctionIndex) { + _maxFunctionIndex = functionIndex; + } + // allow function definitions to change only if both previous and the new items have footnotes + FunctionMetadata prevFM; + prevFM = (FunctionMetadata) _functionDataByName.get(functionName); + if(prevFM != null) { + if(!hasFootnote || !_mutatingFunctionIndexes.contains(indexKey)) { + throw new RuntimeException("Multiple entries for function name '" + functionName + "'"); + } + _functionDataByIndex.remove(Integer.valueOf(prevFM.getIndex())); + } + prevFM = (FunctionMetadata) _functionDataByIndex.get(indexKey); + if(prevFM != null) { + if(!hasFootnote || !_mutatingFunctionIndexes.contains(indexKey)) { + throw new RuntimeException("Multiple entries for function index (" + functionIndex + ")"); + } + _functionDataByName.remove(prevFM.getName()); + } + if(hasFootnote) { + _mutatingFunctionIndexes.add(indexKey); + } + _functionDataByIndex.put(indexKey, fm); + _functionDataByName.put(functionName, fm); + } + + public FunctionMetadataRegistry build() { + + FunctionMetadata[] jumbledArray = new FunctionMetadata[_functionDataByName.size()]; + _functionDataByName.values().toArray(jumbledArray); + FunctionMetadata[] fdIndexArray = new FunctionMetadata[_maxFunctionIndex+1]; + for (int i = 0; i < jumbledArray.length; i++) { + FunctionMetadata fd = jumbledArray[i]; + fdIndexArray[fd.getIndex()] = fd; + } + + return new FunctionMetadataRegistry(fdIndexArray, _functionDataByName); + } +} diff --git a/src/java/org/apache/poi/ss/formula/function/FunctionMetadata.java b/src/java/org/apache/poi/ss/formula/function/FunctionMetadata.java new file mode 100644 index 0000000000..0a72bba384 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/function/FunctionMetadata.java @@ -0,0 +1,90 @@ +/* ==================================================================== + 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.function; + +/** + * Holds information about Excel built-in functions. + * + * @author Josh Micich + */ +public final class FunctionMetadata { + /** + * maxParams=30 in functionMetadata.txt means the maximum number arguments supported + * by the given version of Excel. Validation routines should take the actual limit (Excel 97 or 2007) + * from the SpreadsheetVersion enum. + * Perhaps a value like 'M' should be used instead of '30' in functionMetadata.txt + * to make that file more version neutral. + * @see org.apache.poi.ss.formula.FormulaParser#validateNumArgs(int, FunctionMetadata) + */ + private static final short FUNCTION_MAX_PARAMS = 30; + + private final int _index; + private final String _name; + private final int _minParams; + private final int _maxParams; + private final byte _returnClassCode; + private final byte[] _parameterClassCodes; + + /* package */ FunctionMetadata(int index, String name, int minParams, int maxParams, + byte returnClassCode, byte[] parameterClassCodes) { + _index = index; + _name = name; + _minParams = minParams; + _maxParams = maxParams; + _returnClassCode = returnClassCode; + _parameterClassCodes = parameterClassCodes; + } + public int getIndex() { + return _index; + } + public String getName() { + return _name; + } + public int getMinParams() { + return _minParams; + } + public int getMaxParams() { + return _maxParams; + } + public boolean hasFixedArgsLength() { + return _minParams == _maxParams; + } + public byte getReturnClassCode() { + return _returnClassCode; + } + public byte[] getParameterClassCodes() { + return _parameterClassCodes.clone(); + } + /** + * Some varags functions (like VLOOKUP) have a specific limit to the number of arguments that + * can be passed. Other functions (like SUM) don't have such a limit. For those functions, + * the spreadsheet version determines the maximum number of arguments that can be passed. + * @return true if this function can the maximum number of arguments allowable by + * the {@link org.apache.poi.ss.SpreadsheetVersion} + */ + public boolean hasUnlimitedVarags() { + return FUNCTION_MAX_PARAMS == _maxParams; + } + public String toString() { + StringBuffer sb = new StringBuffer(64); + sb.append(getClass().getName()).append(" ["); + sb.append(_index).append(" ").append(_name); + sb.append("]"); + return sb.toString(); + } +} diff --git a/src/java/org/apache/poi/ss/formula/function/FunctionMetadataReader.java b/src/java/org/apache/poi/ss/formula/function/FunctionMetadataReader.java new file mode 100644 index 0000000000..53ba79c5da --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/function/FunctionMetadataReader.java @@ -0,0 +1,195 @@ +/* ==================================================================== + 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.function; + +import java.io.BufferedReader; +import java.io.IOException; +import java.io.InputStream; +import java.io.InputStreamReader; +import java.io.UnsupportedEncodingException; +import java.util.Arrays; +import java.util.HashSet; +import java.util.Set; +import java.util.regex.Pattern; + +import org.apache.poi.hssf.record.formula.Ptg; + +/** + * Converts the text meta-data file into a FunctionMetadataRegistry + * + * @author Josh Micich + */ +final class FunctionMetadataReader { + + private static final String METADATA_FILE_NAME = "functionMetadata.txt"; + + /** plain ASCII text metadata file uses three dots for ellipsis */ + private static final String ELLIPSIS = "..."; + + private static final Pattern TAB_DELIM_PATTERN = Pattern.compile("\t"); + private static final Pattern SPACE_DELIM_PATTERN = Pattern.compile(" "); + private static final byte[] EMPTY_BYTE_ARRAY = { }; + + private static final String[] DIGIT_ENDING_FUNCTION_NAMES = { + // Digits at the end of a function might be due to a left-over footnote marker. + // except in these cases + "LOG10", "ATAN2", "DAYS360", "SUMXMY2", "SUMX2MY2", "SUMX2PY2", + }; + private static final Set DIGIT_ENDING_FUNCTION_NAMES_SET = new HashSet(Arrays.asList(DIGIT_ENDING_FUNCTION_NAMES)); + + public static FunctionMetadataRegistry createRegistry() { + InputStream is = FunctionMetadataReader.class.getResourceAsStream(METADATA_FILE_NAME); + if (is == null) { + throw new RuntimeException("resource '" + METADATA_FILE_NAME + "' not found"); + } + + BufferedReader br; + try { + br = new BufferedReader(new InputStreamReader(is,"UTF-8")); + } catch(UnsupportedEncodingException e) { + throw new RuntimeException(e); + } + FunctionDataBuilder fdb = new FunctionDataBuilder(400); + + try { + while (true) { + String line = br.readLine(); + if (line == null) { + break; + } + if (line.length() < 1 || line.charAt(0) == '#') { + continue; + } + String trimLine = line.trim(); + if (trimLine.length() < 1) { + continue; + } + processLine(fdb, line); + } + br.close(); + } catch (IOException e) { + throw new RuntimeException(e); + } + + return fdb.build(); + } + + private static void processLine(FunctionDataBuilder fdb, String line) { + + String[] parts = TAB_DELIM_PATTERN.split(line, -2); + if(parts.length != 8) { + throw new RuntimeException("Bad line format '" + line + "' - expected 8 data fields"); + } + int functionIndex = parseInt(parts[0]); + String functionName = parts[1]; + int minParams = parseInt(parts[2]); + int maxParams = parseInt(parts[3]); + byte returnClassCode = parseReturnTypeCode(parts[4]); + byte[] parameterClassCodes = parseOperandTypeCodes(parts[5]); + // 6 isVolatile + boolean hasNote = parts[7].length() > 0; + + validateFunctionName(functionName); + // TODO - make POI use isVolatile + fdb.add(functionIndex, functionName, minParams, maxParams, + returnClassCode, parameterClassCodes, hasNote); + } + + + private static byte parseReturnTypeCode(String code) { + if(code.length() == 0) { + return Ptg.CLASS_REF; // happens for GETPIVOTDATA + } + return parseOperandTypeCode(code); + } + + private static byte[] parseOperandTypeCodes(String codes) { + if(codes.length() < 1) { + return EMPTY_BYTE_ARRAY; // happens for GETPIVOTDATA + } + if(isDash(codes)) { + // '-' means empty: + return EMPTY_BYTE_ARRAY; + } + String[] array = SPACE_DELIM_PATTERN.split(codes); + int nItems = array.length; + if(ELLIPSIS.equals(array[nItems-1])) { + // final ellipsis is optional, and ignored + // (all unspecified params are assumed to be the same as the last) + nItems --; + } + byte[] result = new byte[nItems]; + for (int i = 0; i < nItems; i++) { + result[i] = parseOperandTypeCode(array[i]); + } + return result; + } + + private static boolean isDash(String codes) { + if(codes.length() == 1) { + switch (codes.charAt(0)) { + case '-': + return true; + } + } + return false; + } + + private static byte parseOperandTypeCode(String code) { + if(code.length() != 1) { + throw new RuntimeException("Bad operand type code format '" + code + "' expected single char"); + } + switch(code.charAt(0)) { + case 'V': return Ptg.CLASS_VALUE; + case 'R': return Ptg.CLASS_REF; + case 'A': return Ptg.CLASS_ARRAY; + } + throw new IllegalArgumentException("Unexpected operand type code '" + code + "' (" + (int)code.charAt(0) + ")"); + } + + /** + * Makes sure that footnote digits from the original OOO document have not been accidentally + * left behind + */ + private static void validateFunctionName(String functionName) { + int len = functionName.length(); + int ix = len - 1; + if (!Character.isDigit(functionName.charAt(ix))) { + return; + } + while(ix >= 0) { + if (!Character.isDigit(functionName.charAt(ix))) { + break; + } + ix--; + } + if(DIGIT_ENDING_FUNCTION_NAMES_SET.contains(functionName)) { + return; + } + throw new RuntimeException("Invalid function name '" + functionName + + "' (is footnote number incorrectly appended)"); + } + + private static int parseInt(String valStr) { + try { + return Integer.parseInt(valStr); + } catch (NumberFormatException e) { + throw new RuntimeException("Value '" + valStr + "' could not be parsed as an integer"); + } + } +} diff --git a/src/java/org/apache/poi/ss/formula/function/FunctionMetadataRegistry.java b/src/java/org/apache/poi/ss/formula/function/FunctionMetadataRegistry.java new file mode 100644 index 0000000000..bbe9ecfd80 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/function/FunctionMetadataRegistry.java @@ -0,0 +1,90 @@ +/* ==================================================================== + 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.function; + +import java.util.Map; +import java.util.Set; +/** + * Allows clients to get {@link FunctionMetadata} instances for any built-in function of Excel. + * + * @author Josh Micich + */ +public final class FunctionMetadataRegistry { + /** + * The name of the IF function (i.e. "IF"). Extracted as a constant for clarity. + */ + public static final String FUNCTION_NAME_IF = "IF"; + + public static final int FUNCTION_INDEX_IF = 1; + public static final short FUNCTION_INDEX_SUM = 4; + public static final int FUNCTION_INDEX_CHOOSE = 100; + public static final short FUNCTION_INDEX_INDIRECT = 148; + public static final short FUNCTION_INDEX_EXTERNAL = 255; + + private static FunctionMetadataRegistry _instance; + + private final FunctionMetadata[] _functionDataByIndex; + private final Map _functionDataByName; + + private static FunctionMetadataRegistry getInstance() { + if (_instance == null) { + _instance = FunctionMetadataReader.createRegistry(); + } + return _instance; + } + + /* package */ FunctionMetadataRegistry(FunctionMetadata[] functionDataByIndex, Map functionDataByName) { + _functionDataByIndex = functionDataByIndex; + _functionDataByName = functionDataByName; + } + + /* package */ Set getAllFunctionNames() { + return _functionDataByName.keySet(); + } + + + public static FunctionMetadata getFunctionByIndex(int index) { + return getInstance().getFunctionByIndexInternal(index); + } + + private FunctionMetadata getFunctionByIndexInternal(int index) { + return _functionDataByIndex[index]; + } + /** + * Resolves a built-in function index. + * @param name uppercase function name + * @return a negative value if the function name is not found. + * This typically occurs for external functions. + */ + public static short lookupIndexByName(String name) { + FunctionMetadata fd = getInstance().getFunctionByNameInternal(name); + if (fd == null) { + return -1; + } + return (short) fd.getIndex(); + } + + private FunctionMetadata getFunctionByNameInternal(String name) { + return _functionDataByName.get(name); + } + + + public static FunctionMetadata getFunctionByName(String name) { + return getInstance().getFunctionByNameInternal(name); + } +} diff --git a/src/java/org/apache/poi/ss/formula/functions/AggregateFunction.java b/src/java/org/apache/poi/ss/formula/functions/AggregateFunction.java new file mode 100644 index 0000000000..a8cff4e130 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/functions/AggregateFunction.java @@ -0,0 +1,145 @@ +/* ==================================================================== + 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.functions; + +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; + +/** + * @author Amol S. Deshmukh < amolweb at ya hoo dot com > + */ +public abstract class AggregateFunction extends MultiOperandNumericFunction { + + private static final class LargeSmall extends Fixed2ArgFunction { + private final boolean _isLarge; + protected LargeSmall(boolean isLarge) { + _isLarge = isLarge; + } + + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, + ValueEval arg1) { + double dn; + try { + ValueEval ve1 = OperandResolver.getSingleValue(arg1, srcRowIndex, srcColumnIndex); + dn = OperandResolver.coerceValueToDouble(ve1); + } catch (EvaluationException e1) { + // all errors in the second arg translate to #VALUE! + return ErrorEval.VALUE_INVALID; + } + // weird Excel behaviour on second arg + if (dn < 1.0) { + // values between 0.0 and 1.0 result in #NUM! + return ErrorEval.NUM_ERROR; + } + // all other values are rounded up to the next integer + int k = (int) Math.ceil(dn); + + double result; + try { + double[] ds = ValueCollector.collectValues(arg0); + if (k > ds.length) { + return ErrorEval.NUM_ERROR; + } + result = _isLarge ? StatsLib.kthLargest(ds, k) : StatsLib.kthSmallest(ds, k); + NumericFunction.checkValue(result); + } catch (EvaluationException e) { + return e.getErrorEval(); + } + + return new NumberEval(result); + } + } + private static final class ValueCollector extends MultiOperandNumericFunction { + private static final ValueCollector instance = new ValueCollector(); + public ValueCollector() { + super(false, false); + } + public static double[] collectValues(ValueEval...operands) throws EvaluationException { + return instance.getNumberArray(operands); + } + protected double evaluate(double[] values) { + throw new IllegalStateException("should not be called"); + } + } + + protected AggregateFunction() { + super(false, false); + } + + public static final Function AVEDEV = new AggregateFunction() { + protected double evaluate(double[] values) { + return StatsLib.avedev(values); + } + }; + public static final Function AVERAGE = new AggregateFunction() { + protected double evaluate(double[] values) throws EvaluationException { + if (values.length < 1) { + throw new EvaluationException(ErrorEval.DIV_ZERO); + } + return MathX.average(values); + } + }; + public static final Function DEVSQ = new AggregateFunction() { + protected double evaluate(double[] values) { + return StatsLib.devsq(values); + } + }; + public static final Function LARGE = new LargeSmall(true); + public static final Function MAX = new AggregateFunction() { + protected double evaluate(double[] values) { + return values.length > 0 ? MathX.max(values) : 0; + } + }; + public static final Function MEDIAN = new AggregateFunction() { + protected double evaluate(double[] values) { + return StatsLib.median(values); + } + }; + public static final Function MIN = new AggregateFunction() { + protected double evaluate(double[] values) { + return values.length > 0 ? MathX.min(values) : 0; + } + }; + public static final Function PRODUCT = new AggregateFunction() { + protected double evaluate(double[] values) { + return MathX.product(values); + } + }; + public static final Function SMALL = new LargeSmall(false); + public static final Function STDEV = new AggregateFunction() { + protected double evaluate(double[] values) throws EvaluationException { + if (values.length < 1) { + throw new EvaluationException(ErrorEval.DIV_ZERO); + } + return StatsLib.stdev(values); + } + }; + public static final Function SUM = new AggregateFunction() { + protected double evaluate(double[] values) { + return MathX.sum(values); + } + }; + public static final Function SUMSQ = new AggregateFunction() { + protected double evaluate(double[] values) { + return MathX.sumsq(values); + } + }; +} diff --git a/src/java/org/apache/poi/ss/formula/functions/BooleanFunction.java b/src/java/org/apache/poi/ss/formula/functions/BooleanFunction.java new file mode 100644 index 0000000000..b90f929fb9 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/functions/BooleanFunction.java @@ -0,0 +1,146 @@ +/* ==================================================================== + 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.functions; + +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.RefEval; +import org.apache.poi.ss.formula.eval.ValueEval; +import org.apache.poi.ss.formula.TwoDEval; + +/** + * Here are the general rules concerning Boolean functions: + *
      + *
    1. Blanks are ignored (not either true or false)
      2. + *
    2. Strings are ignored if part of an area ref or cell ref, otherwise they must be 'true' or 'false'
      3. + *
    3. Numbers: 0 is false. Any other number is TRUE
      4. + *
    4. Areas: *all* cells in area are evaluated according to the above rules
      5. + *
    + * + * @author Amol S. Deshmukh < amolweb at ya hoo dot com > + */ +public abstract class BooleanFunction implements Function { + + public final ValueEval evaluate(ValueEval[] args, int srcRow, int srcCol) { + if (args.length < 1) { + return ErrorEval.VALUE_INVALID; + } + boolean boolResult; + try { + boolResult = calculate(args); + } catch (EvaluationException e) { + return e.getErrorEval(); + } + return BoolEval.valueOf(boolResult); + } + + private boolean calculate(ValueEval[] args) throws EvaluationException { + + boolean result = getInitialResultValue(); + boolean atleastOneNonBlank = false; + + /* + * Note: no short-circuit boolean loop exit because any ErrorEvals will override the result + */ + for (int i=0, iSize=args.length; i= args.length) { + return ErrorEval.VALUE_INVALID; + } + ValueEval result = OperandResolver.getSingleValue(args[ix], srcRowIndex, srcColumnIndex); + if (result == MissingArgEval.instance) { + return BlankEval.instance; + } + return result; + } catch (EvaluationException e) { + return e.getErrorEval(); + } + } + + public static int evaluateFirstArg(ValueEval arg0, int srcRowIndex, int srcColumnIndex) + throws EvaluationException { + ValueEval ev = OperandResolver.getSingleValue(arg0, srcRowIndex, srcColumnIndex); + return OperandResolver.coerceValueToInt(ev); + } +} diff --git a/src/java/org/apache/poi/ss/formula/functions/Column.java b/src/java/org/apache/poi/ss/formula/functions/Column.java new file mode 100644 index 0000000000..06e629db0f --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/functions/Column.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.functions; + +import org.apache.poi.ss.formula.eval.AreaEval; +import org.apache.poi.ss.formula.eval.ErrorEval; +import org.apache.poi.ss.formula.eval.NumberEval; +import org.apache.poi.ss.formula.eval.RefEval; +import org.apache.poi.ss.formula.eval.ValueEval; + +public final class Column implements Function0Arg, Function1Arg { + + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex) { + return new NumberEval(srcColumnIndex+1); + } + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0) { + int rnum; + + if (arg0 instanceof AreaEval) { + rnum = ((AreaEval) arg0).getFirstColumn(); + } else if (arg0 instanceof RefEval) { + rnum = ((RefEval) arg0).getColumn(); + } else { + // anything else is not valid argument + return ErrorEval.VALUE_INVALID; + } + + return new NumberEval(rnum + 1); + } + public ValueEval evaluate(ValueEval[] args, int srcRowIndex, int srcColumnIndex) { + switch (args.length) { + case 1: + return evaluate(srcRowIndex, srcColumnIndex, args[0]); + case 0: + return new NumberEval(srcColumnIndex+1); + } + return ErrorEval.VALUE_INVALID; + } +} diff --git a/src/java/org/apache/poi/ss/formula/functions/Columns.java b/src/java/org/apache/poi/ss/formula/functions/Columns.java new file mode 100644 index 0000000000..3c1fdc54a2 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/functions/Columns.java @@ -0,0 +1,45 @@ +/* ==================================================================== + 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.functions; + +import org.apache.poi.ss.formula.eval.ErrorEval; +import org.apache.poi.ss.formula.eval.NumberEval; +import org.apache.poi.ss.formula.eval.RefEval; +import org.apache.poi.ss.formula.eval.ValueEval; +import org.apache.poi.ss.formula.TwoDEval; + +/** + * Implementation for Excel COLUMNS function. + * + * @author Josh Micich + */ +public final class Columns extends Fixed1ArgFunction { + + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0) { + + int result; + if (arg0 instanceof TwoDEval) { + result = ((TwoDEval) arg0).getWidth(); + } else if (arg0 instanceof RefEval) { + result = 1; + } else { // anything else is not valid argument + return ErrorEval.VALUE_INVALID; + } + return new NumberEval(result); + } +} diff --git a/src/java/org/apache/poi/ss/formula/functions/Count.java b/src/java/org/apache/poi/ss/formula/functions/Count.java new file mode 100644 index 0000000000..8a1e41dfb7 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/functions/Count.java @@ -0,0 +1,77 @@ +/* ==================================================================== + 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.functions; + +import org.apache.poi.ss.formula.eval.ErrorEval; +import org.apache.poi.ss.formula.eval.MissingArgEval; +import org.apache.poi.ss.formula.eval.NumberEval; +import org.apache.poi.ss.formula.eval.ValueEval; +import org.apache.poi.ss.formula.functions.CountUtils.I_MatchPredicate; + +/** + * Counts the number of cells that contain numeric data within + * the list of arguments. + * + * Excel Syntax + * COUNT(value1,value2,...) + * Value1, value2, ... are 1 to 30 arguments representing the values or ranges to be counted. + * + * TODO: Check this properly matches excel on edge cases + * like formula cells, error cells etc + */ +public final class Count implements Function { + + public ValueEval evaluate(ValueEval[] args, int srcCellRow, int srcCellCol) { + int nArgs = args.length; + if (nArgs < 1) { + // too few arguments + return ErrorEval.VALUE_INVALID; + } + + if (nArgs > 30) { + // too many arguments + return ErrorEval.VALUE_INVALID; + } + + int temp = 0; + + for(int i=0; i 30) { + // too many arguments + return ErrorEval.VALUE_INVALID; + } + + int temp = 0; + + for(int i=0; i + * Syntax: COUNTBLANK ( range ) + * + * + *
    range   is the range of cells to count blanks
    + *

    + * + * @author Mads Mohr Christensen + */ +public final class Countblank extends Fixed1ArgFunction { + + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0) { + + double result; + if (arg0 instanceof RefEval) { + result = CountUtils.countMatchingCell((RefEval) arg0, predicate); + } else if (arg0 instanceof TwoDEval) { + result = CountUtils.countMatchingCellsInArea((TwoDEval) arg0, predicate); + } else { + throw new IllegalArgumentException("Bad range arg type (" + arg0.getClass().getName() + ")"); + } + return new NumberEval(result); + } + + private static final I_MatchPredicate predicate = new I_MatchPredicate() { + + public boolean matches(ValueEval valueEval) { + // Note - only BlankEval counts + return valueEval == BlankEval.instance; + } + }; +} diff --git a/src/java/org/apache/poi/ss/formula/functions/Countif.java b/src/java/org/apache/poi/ss/formula/functions/Countif.java new file mode 100644 index 0000000000..2b7601c871 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/functions/Countif.java @@ -0,0 +1,528 @@ +/* ==================================================================== + 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.functions; + +import java.util.regex.Pattern; + +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.EvaluationException; +import org.apache.poi.ss.formula.eval.NumberEval; +import org.apache.poi.ss.formula.eval.OperandResolver; +import org.apache.poi.ss.formula.eval.RefEval; +import org.apache.poi.ss.formula.eval.StringEval; +import org.apache.poi.ss.formula.eval.ValueEval; +import org.apache.poi.ss.formula.functions.CountUtils.I_MatchPredicate; +import org.apache.poi.ss.formula.TwoDEval; +import org.apache.poi.ss.usermodel.ErrorConstants; + +/** + * Implementation for the function COUNTIF + *

    + * Syntax: COUNTIF ( range, criteria ) + * + * + * + *
    range   is the range of cells to be counted based on the criteria
    criteriais used to determine which cells to count
    + *

    + * + * @author Josh Micich + */ +public final class Countif extends Fixed2ArgFunction { + + private static final class CmpOp { + public static final int NONE = 0; + public static final int EQ = 1; + public static final int NE = 2; + public static final int LE = 3; + public static final int LT = 4; + public static final int GT = 5; + public static final int GE = 6; + + public static final CmpOp OP_NONE = op("", NONE); + public static final CmpOp OP_EQ = op("=", EQ); + public static final CmpOp OP_NE = op("<>", NE); + public static final CmpOp OP_LE = op("<=", LE); + public static final CmpOp OP_LT = op("<", LT); + public static final CmpOp OP_GT = op(">", GT); + public static final CmpOp OP_GE = op(">=", GE); + private final String _representation; + private final int _code; + + private static CmpOp op(String rep, int code) { + return new CmpOp(rep, code); + } + private CmpOp(String representation, int code) { + _representation = representation; + _code = code; + } + /** + * @return number of characters used to represent this operator + */ + public int getLength() { + return _representation.length(); + } + public int getCode() { + return _code; + } + public static CmpOp getOperator(String value) { + int len = value.length(); + if (len < 1) { + return OP_NONE; + } + + char firstChar = value.charAt(0); + + switch(firstChar) { + case '=': + return OP_EQ; + case '>': + if (len > 1) { + switch(value.charAt(1)) { + case '=': + return OP_GE; + } + } + return OP_GT; + case '<': + if (len > 1) { + switch(value.charAt(1)) { + case '=': + return OP_LE; + case '>': + return OP_NE; + } + } + return OP_LT; + } + return OP_NONE; + } + public boolean evaluate(boolean cmpResult) { + switch (_code) { + case NONE: + case EQ: + return cmpResult; + case NE: + return !cmpResult; + } + throw new RuntimeException("Cannot call boolean evaluate on non-equality operator '" + + _representation + "'"); + } + public boolean evaluate(int cmpResult) { + switch (_code) { + case NONE: + case EQ: + return cmpResult == 0; + case NE: return cmpResult != 0; + case LT: return cmpResult < 0; + case LE: return cmpResult <= 0; + case GT: return cmpResult > 0; + case GE: return cmpResult <= 0; + } + throw new RuntimeException("Cannot call boolean evaluate on non-equality operator '" + + _representation + "'"); + } + public String toString() { + StringBuffer sb = new StringBuffer(64); + sb.append(getClass().getName()); + sb.append(" [").append(_representation).append("]"); + return sb.toString(); + } + public String getRepresentation() { + return _representation; + } + } + + private static abstract class MatcherBase implements I_MatchPredicate { + private final CmpOp _operator; + + MatcherBase(CmpOp operator) { + _operator = operator; + } + protected final int getCode() { + return _operator.getCode(); + } + protected final boolean evaluate(int cmpResult) { + return _operator.evaluate(cmpResult); + } + protected final boolean evaluate(boolean cmpResult) { + return _operator.evaluate(cmpResult); + } + @Override + public final String toString() { + StringBuffer sb = new StringBuffer(64); + sb.append(getClass().getName()).append(" ["); + sb.append(_operator.getRepresentation()); + sb.append(getValueText()); + sb.append("]"); + return sb.toString(); + } + protected abstract String getValueText(); + } + + private static final class NumberMatcher extends MatcherBase { + + private final double _value; + + public NumberMatcher(double value, CmpOp operator) { + super(operator); + _value = value; + } + @Override + protected String getValueText() { + return String.valueOf(_value); + } + + public boolean matches(ValueEval x) { + double testValue; + if(x instanceof StringEval) { + // if the target(x) is a string, but parses as a number + // it may still count as a match, only for the equality operator + switch (getCode()) { + case CmpOp.EQ: + case CmpOp.NONE: + break; + case CmpOp.NE: + // Always matches (inconsistent with above two cases). + // for example '<>123' matches '123', '4', 'abc', etc + return true; + default: + // never matches (also inconsistent with above three cases). + // for example '>5' does not match '6', + return false; + } + StringEval se = (StringEval)x; + Double val = OperandResolver.parseDouble(se.getStringValue()); + if(val == null) { + // x is text that is not a number + return false; + } + return _value == val.doubleValue(); + } else if((x instanceof NumberEval)) { + NumberEval ne = (NumberEval) x; + testValue = ne.getNumberValue(); + } else { + return false; + } + return evaluate(Double.compare(testValue, _value)); + } + } + private static final class BooleanMatcher extends MatcherBase { + + private final int _value; + + public BooleanMatcher(boolean value, CmpOp operator) { + super(operator); + _value = boolToInt(value); + } + @Override + protected String getValueText() { + return _value == 1 ? "TRUE" : "FALSE"; + } + + private static int boolToInt(boolean value) { + return value ? 1 : 0; + } + + public boolean matches(ValueEval x) { + int testValue; + if(x instanceof StringEval) { + if (true) { // change to false to observe more intuitive behaviour + // Note - Unlike with numbers, it seems that COUNTIF never matches + // boolean values when the target(x) is a string + return false; + } + StringEval se = (StringEval)x; + Boolean val = parseBoolean(se.getStringValue()); + if(val == null) { + // x is text that is not a boolean + return false; + } + testValue = boolToInt(val.booleanValue()); + } else if((x instanceof BoolEval)) { + BoolEval be = (BoolEval) x; + testValue = boolToInt(be.getBooleanValue()); + } else { + return false; + } + return evaluate(testValue - _value); + } + } + private static final class ErrorMatcher extends MatcherBase { + + private final int _value; + + public ErrorMatcher(int errorCode, CmpOp operator) { + super(operator); + _value = errorCode; + } + @Override + protected String getValueText() { + return ErrorConstants.getText(_value); + } + + public boolean matches(ValueEval x) { + if(x instanceof ErrorEval) { + int testValue = ((ErrorEval)x).getErrorCode(); + return evaluate(testValue - _value); + } + return false; + } + } + private static final class StringMatcher extends MatcherBase { + + private final String _value; + private final Pattern _pattern; + + public StringMatcher(String value, CmpOp operator) { + super(operator); + _value = value; + switch(operator.getCode()) { + case CmpOp.NONE: + case CmpOp.EQ: + case CmpOp.NE: + _pattern = getWildCardPattern(value); + break; + default: + // pattern matching is never used for < > <= => + _pattern = null; + } + } + @Override + protected String getValueText() { + if (_pattern == null) { + return _value; + } + return _pattern.pattern(); + } + + public boolean matches(ValueEval x) { + if (x instanceof BlankEval) { + switch(getCode()) { + case CmpOp.NONE: + case CmpOp.EQ: + return _value.length() == 0; + } + // no other criteria matches a blank cell + return false; + } + if(!(x instanceof StringEval)) { + // must always be string + // even if match str is wild, but contains only digits + // e.g. '4*7', NumberEval(4567) does not match + return false; + } + String testedValue = ((StringEval) x).getStringValue(); + if (testedValue.length() < 1 && _value.length() < 1) { + // odd case: criteria '=' behaves differently to criteria '' + + switch(getCode()) { + case CmpOp.NONE: return true; + case CmpOp.EQ: return false; + case CmpOp.NE: return true; + } + return false; + } + if (_pattern != null) { + return evaluate(_pattern.matcher(testedValue).matches()); + } + return evaluate(testedValue.compareTo(_value)); + } + /** + * Translates Excel countif wildcard strings into java regex strings + * @return null if the specified value contains no special wildcard characters. + */ + private static Pattern getWildCardPattern(String value) { + int len = value.length(); + StringBuffer sb = new StringBuffer(len); + boolean hasWildCard = false; + for(int i=0; inull if the arg evaluates to blank. + */ + /* package */ static I_MatchPredicate createCriteriaPredicate(ValueEval arg, int srcRowIndex, int srcColumnIndex) { + + ValueEval evaluatedCriteriaArg = evaluateCriteriaArg(arg, srcRowIndex, srcColumnIndex); + + if(evaluatedCriteriaArg instanceof NumberEval) { + return new NumberMatcher(((NumberEval)evaluatedCriteriaArg).getNumberValue(), CmpOp.OP_NONE); + } + if(evaluatedCriteriaArg instanceof BoolEval) { + return new BooleanMatcher(((BoolEval)evaluatedCriteriaArg).getBooleanValue(), CmpOp.OP_NONE); + } + + if(evaluatedCriteriaArg instanceof StringEval) { + return createGeneralMatchPredicate((StringEval)evaluatedCriteriaArg); + } + if(evaluatedCriteriaArg instanceof ErrorEval) { + return new ErrorMatcher(((ErrorEval)evaluatedCriteriaArg).getErrorCode(), CmpOp.OP_NONE); + } + if(evaluatedCriteriaArg == BlankEval.instance) { + return null; + } + throw new RuntimeException("Unexpected type for criteria (" + + evaluatedCriteriaArg.getClass().getName() + ")"); + } + + /** + * + * @return the de-referenced criteria arg (possibly {@link ErrorEval}) + */ + private static ValueEval evaluateCriteriaArg(ValueEval arg, int srcRowIndex, int srcColumnIndex) { + try { + return OperandResolver.getSingleValue(arg, srcRowIndex, (short)srcColumnIndex); + } catch (EvaluationException e) { + return e.getErrorEval(); + } + } + /** + * When the second argument is a string, many things are possible + */ + private static I_MatchPredicate createGeneralMatchPredicate(StringEval stringEval) { + String value = stringEval.getStringValue(); + CmpOp operator = CmpOp.getOperator(value); + value = value.substring(operator.getLength()); + + Boolean booleanVal = parseBoolean(value); + if(booleanVal != null) { + return new BooleanMatcher(booleanVal.booleanValue(), operator); + } + + Double doubleVal = OperandResolver.parseDouble(value); + if(doubleVal != null) { + return new NumberMatcher(doubleVal.doubleValue(), operator); + } + ErrorEval ee = parseError(value); + if (ee != null) { + return new ErrorMatcher(ee.getErrorCode(), operator); + } + + //else - just a plain string with no interpretation. + return new StringMatcher(value, operator); + } + private static ErrorEval parseError(String value) { + if (value.length() < 4 || value.charAt(0) != '#') { + return null; + } + if (value.equals("#NULL!")) return ErrorEval.NULL_INTERSECTION; + if (value.equals("#DIV/0!")) return ErrorEval.DIV_ZERO; + if (value.equals("#VALUE!")) return ErrorEval.VALUE_INVALID; + if (value.equals("#REF!")) return ErrorEval.REF_INVALID; + if (value.equals("#NAME?")) return ErrorEval.NAME_INVALID; + if (value.equals("#NUM!")) return ErrorEval.NUM_ERROR; + if (value.equals("#N/A")) return ErrorEval.NA; + + return null; + } + /** + * Boolean literals ('TRUE', 'FALSE') treated similarly but NOT same as numbers. + */ + /* package */ static Boolean parseBoolean(String strRep) { + if (strRep.length() < 1) { + return null; + } + switch(strRep.charAt(0)) { + case 't': + case 'T': + if("TRUE".equalsIgnoreCase(strRep)) { + return Boolean.TRUE; + } + break; + case 'f': + case 'F': + if("FALSE".equalsIgnoreCase(strRep)) { + return Boolean.FALSE; + } + break; + } + return null; + } +} diff --git a/src/java/org/apache/poi/ss/formula/functions/DateFunc.java b/src/java/org/apache/poi/ss/formula/functions/DateFunc.java new file mode 100644 index 0000000000..81da051bf7 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/functions/DateFunc.java @@ -0,0 +1,92 @@ +/* ==================================================================== + 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.functions; + +import java.util.Calendar; +import java.util.GregorianCalendar; + +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.ValueEval; +import org.apache.poi.ss.usermodel.DateUtil; + + +/** + * Implementation for the Excel function DATE + * + * @author Pavel Krupets (pkrupets at palmtreebusiness dot com) + */ +public final class DateFunc extends Fixed3ArgFunction { + + public static final Function instance = new DateFunc(); + + private DateFunc() { + // no fields to initialise + } + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1, + ValueEval arg2) { + double result; + try { + double d0 = NumericFunction.singleOperandEvaluate(arg0, srcRowIndex, srcColumnIndex); + double d1 = NumericFunction.singleOperandEvaluate(arg1, srcRowIndex, srcColumnIndex); + double d2 = NumericFunction.singleOperandEvaluate(arg2, srcRowIndex, srcColumnIndex); + result = evaluate(getYear(d0), (int) (d1 - 1), (int) d2); + NumericFunction.checkValue(result); + } catch (EvaluationException e) { + return e.getErrorEval(); + } + return new NumberEval(result); + } + + private static double evaluate(int year, int month, int pDay) throws EvaluationException { + + if (year < 0 || month < 0 || pDay < 0) { + throw new EvaluationException(ErrorEval.VALUE_INVALID); + } + + if (year == 1900 && month == Calendar.FEBRUARY && pDay == 29) { + return 60.0; + } + + int day = pDay; + if (year == 1900) { + if ((month == Calendar.JANUARY && day >= 60) || + (month == Calendar.FEBRUARY && day >= 30)) { + day--; + } + } + + Calendar c = new GregorianCalendar(); + + c.set(year, month, day, 0, 0, 0); + c.set(Calendar.MILLISECOND, 0); + + return DateUtil.getExcelDate(c.getTime(), false); // TODO - fix 1900/1904 problem + } + + private static int getYear(double d) { + int year = (int)d; + + if (year < 0) { + return -1; + } + + return year < 1900 ? 1900 + year : year; + } +} diff --git a/src/java/org/apache/poi/ss/formula/functions/Days360.java b/src/java/org/apache/poi/ss/formula/functions/Days360.java new file mode 100644 index 0000000000..f2bd1a5ddb --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/functions/Days360.java @@ -0,0 +1,118 @@ +/* ==================================================================== + 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.functions; + +import java.util.Calendar; +import java.util.GregorianCalendar; + +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.usermodel.DateUtil; + +/** + * Calculates the number of days between two dates based on a 360-day year + * (twelve 30-day months), which is used in some accounting calculations. Use + * this function to help compute payments if your accounting system is based on + * twelve 30-day months. + * + * @author PUdalau + */ +public class Days360 extends Var2or3ArgFunction { + + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1) { + double result; + try { + double d0 = NumericFunction.singleOperandEvaluate(arg0, srcRowIndex, srcColumnIndex); + double d1 = NumericFunction.singleOperandEvaluate(arg1, srcRowIndex, srcColumnIndex); + result = evaluate(d0, d1, false); + } catch (EvaluationException e) { + return e.getErrorEval(); + } + return new NumberEval(result); + } + + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1, + ValueEval arg2) { + double result; + try { + double d0 = NumericFunction.singleOperandEvaluate(arg0, srcRowIndex, srcColumnIndex); + double d1 = NumericFunction.singleOperandEvaluate(arg1, srcRowIndex, srcColumnIndex); + ValueEval ve = OperandResolver.getSingleValue(arg2, srcRowIndex, srcColumnIndex); + Boolean method = OperandResolver.coerceValueToBoolean(ve, false); + result = evaluate(d0, d1, method == null ? false : method.booleanValue()); + } catch (EvaluationException e) { + return e.getErrorEval(); + } + return new NumberEval(result); + } + + private static double evaluate(double d0, double d1, boolean method) { + Calendar startingDate = getStartingDate(d0); + Calendar endingDate = getEndingDateAccordingToStartingDate(d1, startingDate); + long startingDay = startingDate.get(Calendar.MONTH) * 30 + startingDate.get(Calendar.DAY_OF_MONTH); + long endingDay = (endingDate.get(Calendar.YEAR) - startingDate.get(Calendar.YEAR)) * 360 + + endingDate.get(Calendar.MONTH) * 30 + endingDate.get(Calendar.DAY_OF_MONTH); + return endingDay - startingDay; + } + + private static Calendar getDate(double date) { + Calendar processedDate = new GregorianCalendar(); + processedDate.setTime(DateUtil.getJavaDate(date, false)); + return processedDate; + } + + private static Calendar getStartingDate(double date) { + Calendar startingDate = getDate(date); + if (isLastDayOfMonth(startingDate)) { + startingDate.set(Calendar.DAY_OF_MONTH, 30); + } + return startingDate; + } + + private static Calendar getEndingDateAccordingToStartingDate(double date, Calendar startingDate) { + Calendar endingDate = getDate(date); + endingDate.setTime(DateUtil.getJavaDate(date, false)); + if (isLastDayOfMonth(endingDate)) { + if (startingDate.get(Calendar.DATE) < 30) { + endingDate = getFirstDayOfNextMonth(endingDate); + } + } + return endingDate; + } + + private static boolean isLastDayOfMonth(Calendar date) { + Calendar clone = (Calendar) date.clone(); + clone.add(java.util.Calendar.MONTH, 1); + clone.add(java.util.Calendar.DAY_OF_MONTH, -1); + int lastDayOfMonth = clone.get(Calendar.DAY_OF_MONTH); + return date.get(Calendar.DAY_OF_MONTH) == lastDayOfMonth; + } + + private static Calendar getFirstDayOfNextMonth(Calendar date) { + Calendar newDate = (Calendar) date.clone(); + if (date.get(Calendar.MONTH) < Calendar.DECEMBER) { + newDate.set(Calendar.MONTH, date.get(Calendar.MONTH) + 1); + } else { + newDate.set(Calendar.MONTH, 1); + newDate.set(Calendar.YEAR, date.get(Calendar.YEAR) + 1); + } + newDate.set(Calendar.DATE, 1); + return newDate; + } +} diff --git a/src/java/org/apache/poi/ss/formula/functions/Errortype.java b/src/java/org/apache/poi/ss/formula/functions/Errortype.java new file mode 100644 index 0000000000..c82644280c --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/functions/Errortype.java @@ -0,0 +1,79 @@ +/* ==================================================================== + 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.functions; + +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.usermodel.ErrorConstants; + +/** + * Implementation for the ERROR.TYPE() Excel function. + *

    + * Syntax:
    + * ERROR.TYPE(errorValue)

    + *

    + * Returns a number corresponding to the error type of the supplied argument.

    + *

    + * + * + * + * + * + * + * + * + * + * + *
    errorValueReturn Value
    #NULL!1
    #DIV/0!2
    #VALUE!3
    #REF!4
    #NAME?5
    #NUM!6
    #N/A!7
    everything else#N/A!
    + * + * Note - the results of ERROR.TYPE() are different to the constants defined in + * ErrorConstants. + *

    + * + * @author Josh Micich + */ +public final class Errortype extends Fixed1ArgFunction { + + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0) { + + try { + OperandResolver.getSingleValue(arg0, srcRowIndex, srcColumnIndex); + return ErrorEval.NA; + } catch (EvaluationException e) { + int result = translateErrorCodeToErrorTypeValue(e.getErrorEval().getErrorCode()); + return new NumberEval(result); + } + } + + private int translateErrorCodeToErrorTypeValue(int errorCode) { + switch (errorCode) { + case ErrorConstants.ERROR_NULL: return 1; + case ErrorConstants.ERROR_DIV_0: return 2; + case ErrorConstants.ERROR_VALUE: return 3; + case ErrorConstants.ERROR_REF: return 4; + case ErrorConstants.ERROR_NAME: return 5; + case ErrorConstants.ERROR_NUM: return 6; + case ErrorConstants.ERROR_NA : return 7; + } + throw new IllegalArgumentException("Invalid error code (" + errorCode + ")"); + } + +} diff --git a/src/java/org/apache/poi/ss/formula/functions/Even.java b/src/java/org/apache/poi/ss/formula/functions/Even.java new file mode 100644 index 0000000000..f691265324 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/functions/Even.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.functions; + + +/** + * @author Amol S. Deshmukh < amolweb at ya hoo dot com > + * + */ +public final class Even extends NumericFunction.OneArg { + + private static final long PARITY_MASK = 0xFFFFFFFFFFFFFFFEL; + + protected double evaluate(double d) { + if (d==0) { + return 0; + } + long result; + if (d>0) { + result = calcEven(d); + } else { + result = -calcEven(-d); + } + return result; + } + + private static long calcEven(double d) { + long x = ((long) d) & PARITY_MASK; + if (x == d) { + return x; + } + return x + 2; + } +} diff --git a/src/java/org/apache/poi/ss/formula/functions/FinanceFunction.java b/src/java/org/apache/poi/ss/formula/functions/FinanceFunction.java new file mode 100644 index 0000000000..def0acc344 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/functions/FinanceFunction.java @@ -0,0 +1,118 @@ +/* ==================================================================== + 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.functions; + +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.NumberEval; +import org.apache.poi.ss.formula.eval.ValueEval; + +/** + * @author Amol S. Deshmukh < amolweb at ya hoo dot com > + */ +public abstract class FinanceFunction implements Function3Arg, Function4Arg { + private static final ValueEval DEFAULT_ARG3 = NumberEval.ZERO; + private static final ValueEval DEFAULT_ARG4 = BoolEval.FALSE; + + + protected FinanceFunction() { + // no instance fields + } + + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1, + ValueEval arg2) { + return evaluate(srcRowIndex, srcColumnIndex, arg0, arg1, arg2, DEFAULT_ARG3); + } + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1, + ValueEval arg2, ValueEval arg3) { + return evaluate(srcRowIndex, srcColumnIndex, arg0, arg1, arg2, arg3, DEFAULT_ARG4); + } + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1, + ValueEval arg2, ValueEval arg3, ValueEval arg4) { + double result; + try { + double d0 = NumericFunction.singleOperandEvaluate(arg0, srcRowIndex, srcColumnIndex); + double d1 = NumericFunction.singleOperandEvaluate(arg1, srcRowIndex, srcColumnIndex); + double d2 = NumericFunction.singleOperandEvaluate(arg2, srcRowIndex, srcColumnIndex); + double d3 = NumericFunction.singleOperandEvaluate(arg3, srcRowIndex, srcColumnIndex); + double d4 = NumericFunction.singleOperandEvaluate(arg4, srcRowIndex, srcColumnIndex); + result = evaluate(d0, d1, d2, d3, d4 != 0.0); + NumericFunction.checkValue(result); + } catch (EvaluationException e) { + return e.getErrorEval(); + } + return new NumberEval(result); + } + public ValueEval evaluate(ValueEval[] args, int srcRowIndex, int srcColumnIndex) { + switch (args.length) { + case 3: + return evaluate(srcRowIndex, srcColumnIndex, args[0], args[1], args[2], DEFAULT_ARG3, DEFAULT_ARG4); + case 4: + return evaluate(srcRowIndex, srcColumnIndex, args[0], args[1], args[2], args[3], DEFAULT_ARG4); + case 5: + return evaluate(srcRowIndex, srcColumnIndex, args[0], args[1], args[2], args[3], args[4]); + } + return ErrorEval.VALUE_INVALID; + } + + protected double evaluate(double[] ds) throws EvaluationException { + // All finance functions have 3 to 5 args, first 4 are numbers, last is boolean + // default for last 2 args are 0.0 and false + // Text boolean literals are not valid for the last arg + + double arg3 = 0.0; + double arg4 = 0.0; + + switch(ds.length) { + case 5: + arg4 = ds[4]; + case 4: + arg3 = ds[3]; + case 3: + break; + default: + throw new IllegalStateException("Wrong number of arguments"); + } + return evaluate(ds[0], ds[1], ds[2], arg3, arg4!=0.0); + } + + protected abstract double evaluate(double rate, double arg1, double arg2, double arg3, boolean type) throws EvaluationException ; + + + public static final Function FV = new FinanceFunction() { + protected double evaluate(double rate, double arg1, double arg2, double arg3, boolean type) { + return FinanceLib.fv(rate, arg1, arg2, arg3, type); + } + }; + public static final Function NPER = new FinanceFunction() { + protected double evaluate(double rate, double arg1, double arg2, double arg3, boolean type) { + return FinanceLib.nper(rate, arg1, arg2, arg3, type); + } + }; + public static final Function PMT = new FinanceFunction() { + protected double evaluate(double rate, double arg1, double arg2, double arg3, boolean type) { + return FinanceLib.pmt(rate, arg1, arg2, arg3, type); + } + }; + public static final Function PV = new FinanceFunction() { + protected double evaluate(double rate, double arg1, double arg2, double arg3, boolean type) { + return FinanceLib.pv(rate, arg1, arg2, arg3, type); + } + }; +} diff --git a/src/java/org/apache/poi/ss/formula/functions/FinanceLib.java b/src/java/org/apache/poi/ss/formula/functions/FinanceLib.java new file mode 100644 index 0000000000..d61e7e020f --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/functions/FinanceLib.java @@ -0,0 +1,185 @@ +/* ==================================================================== + 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.functions; + +/** + * @author Amol S. Deshmukh < amolweb at ya hoo dot com > + * + * + * This class is a functon library for common fiscal functions. + * Glossary of terms/abbreviations: + *
    + *
      + *
    • FV: Future Value
      • + *
    • PV: Present Value
      • + *
    • NPV: Net Present Value
      • + *
    • PMT: (Periodic) Payment
      • + * + *
    + * For more info on the terms/abbreviations please use the references below + * (hyperlinks are subject to change): + *
    Online References: + *
      + *
    1. GNU Emacs Calc 2.02 Manual: http://theory.uwinnipeg.ca/gnu/calc/calc_203.html
      2. + *
    2. Yahoo Financial Glossary: http://biz.yahoo.com/f/g/nn.html#y
      3. + *
    3. MS Excel function reference: http://office.microsoft.com/en-us/assistance/CH062528251033.aspx
      4. + *
    + *

    Implementation Notes:

    + * Symbols used in the formulae that follow:
    + *
      + *
    • p: present value
      • + *
    • f: future value
      • + *
    • n: number of periods
      • + *
    • y: payment (in each period)
      • + *
    • r: rate
      • + *
    • ^: the power operator (NOT the java bitwise XOR operator!)
      • + *
    + * [From MS Excel function reference] Following are some of the key formulas + * that are used in this implementation: + * 
    + * p(1+r)^n + y(1+rt)((1+r)^n-1)/r + f=0   ...{when r!=0}
+ * ny + p + f=0                            ...{when r=0}
+ *
    + */ +final class FinanceLib { + + private FinanceLib() { + // no instances of this class + } + + /** + * Future value of an amount given the number of payments, rate, amount + * of individual payment, present value and boolean value indicating whether + * payments are due at the beginning of period + * (false => payments are due at end of period) + * @param r rate + * @param n num of periods + * @param y pmt per period + * @param p future value + * @param t type (true=pmt at end of period, false=pmt at begining of period) + */ + public static double fv(double r, double n, double y, double p, boolean t) { + double retval = 0; + if (r == 0) { + retval = -1*(p+(n*y)); + } + else { + double r1 = r + 1; + retval =((1-Math.pow(r1, n)) * (t ? r1 : 1) * y ) / r + - + p*Math.pow(r1, n); + } + return retval; + } + + /** + * Present value of an amount given the number of future payments, rate, amount + * of individual payment, future value and boolean value indicating whether + * payments are due at the beginning of period + * (false => payments are due at end of period) + * @param r + * @param n + * @param y + * @param f + * @param t + */ + public static double pv(double r, double n, double y, double f, boolean t) { + double retval = 0; + if (r == 0) { + retval = -1*((n*y)+f); + } + else { + double r1 = r + 1; + retval =(( ( 1 - Math.pow(r1, n) ) / r ) * (t ? r1 : 1) * y - f) + / + Math.pow(r1, n); + } + return retval; + } + + /** + * calculates the Net Present Value of a principal amount + * given the discount rate and a sequence of cash flows + * (supplied as an array). If the amounts are income the value should + * be positive, else if they are payments and not income, the + * value should be negative. + * @param r + * @param cfs cashflow amounts + */ + public static double npv(double r, double[] cfs) { + double npv = 0; + double r1 = r + 1; + double trate = r1; + for (int i=0, iSize=cfs.length; i + * Two important functions with this feature are INDIRECT and OFFSET

    + * + * When POI evaluates formulas, each reference argument is capable of evaluating any cell inside + * its range. Actually, even cells outside the reference range but on the same sheet can be + * evaluated. This allows OFFSET to be implemented like most other functions - taking only + * the arguments, and source cell coordinates. + * + * For the moment this interface only exists to serve the INDIRECT which can decode + * arbitrary text into cell references, and evaluate them.. + * + * @author Josh Micich + */ +public interface FreeRefFunction { + /** + * @param args the pre-evaluated arguments for this function. args is never null, + * nor are any of its elements. + * @param ec primarily used to identify the source cell containing the formula being evaluated. + * may also be used to dynamically create reference evals. + * @return never null. Possibly an instance of ErrorEval in the case of + * a specified Excel error (Exceptions are never thrown to represent Excel errors). + */ + ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec); +} diff --git a/src/java/org/apache/poi/ss/formula/functions/Function.java b/src/java/org/apache/poi/ss/formula/functions/Function.java new file mode 100644 index 0000000000..5f97b81a24 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/functions/Function.java @@ -0,0 +1,43 @@ +/* ==================================================================== + 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.functions; + +import org.apache.poi.ss.formula.eval.BlankEval; +import org.apache.poi.ss.formula.eval.ErrorEval; +import org.apache.poi.ss.formula.eval.MissingArgEval; +import org.apache.poi.ss.formula.eval.ValueEval; + +/** + * Common interface for all implementations of Excel built-in functions. + * + * @author Amol S. Deshmukh < amolweb at ya hoo dot com > + */ +public interface Function { + + /** + * @param args the evaluated function arguments. Empty values are represented with + * {@link BlankEval} or {@link MissingArgEval}, never null. + * @param srcRowIndex row index of the cell containing the formula under evaluation + * @param srcColumnIndex column index of the cell containing the formula under evaluation + * @return The evaluated result, possibly an {@link ErrorEval}, never null. + * Note - Excel uses the error code #NUM! instead of IEEE NaN, so when + * numeric functions evaluate to {@link Double#NaN} be sure to translate the result to {@link + * ErrorEval#NUM_ERROR}. + */ + ValueEval evaluate(ValueEval[] args, int srcRowIndex, int srcColumnIndex); +} diff --git a/src/java/org/apache/poi/ss/formula/functions/Function0Arg.java b/src/java/org/apache/poi/ss/formula/functions/Function0Arg.java new file mode 100644 index 0000000000..749053ea15 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/functions/Function0Arg.java @@ -0,0 +1,32 @@ +/* ==================================================================== + 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.functions; + +import org.apache.poi.ss.formula.eval.ValueEval; + +/** + * Implemented by all functions that can be called with zero arguments + * + * @author Josh Micich + */ +public interface Function0Arg extends Function { + /** + * see {@link Function#evaluate(ValueEval[], int, int)} + */ + ValueEval evaluate(int srcRowIndex, int srcColumnIndex); +} diff --git a/src/java/org/apache/poi/ss/formula/functions/Function1Arg.java b/src/java/org/apache/poi/ss/formula/functions/Function1Arg.java new file mode 100644 index 0000000000..ec214f4f09 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/functions/Function1Arg.java @@ -0,0 +1,32 @@ +/* ==================================================================== + 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.functions; + +import org.apache.poi.ss.formula.eval.ValueEval; + +/** + * Implemented by all functions that can be called with one argument + * + * @author Josh Micich + */ +public interface Function1Arg extends Function { + /** + * see {@link Function#evaluate(ValueEval[], int, int)} + */ + ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0); +} diff --git a/src/java/org/apache/poi/ss/formula/functions/Function2Arg.java b/src/java/org/apache/poi/ss/formula/functions/Function2Arg.java new file mode 100644 index 0000000000..bd395cc51e --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/functions/Function2Arg.java @@ -0,0 +1,32 @@ +/* ==================================================================== + 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.functions; + +import org.apache.poi.ss.formula.eval.ValueEval; + +/** + * Implemented by all functions that can be called with two arguments + * + * @author Josh Micich + */ +public interface Function2Arg extends Function { + /** + * see {@link Function#evaluate(ValueEval[], int, int)} + */ + ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1); +} diff --git a/src/java/org/apache/poi/ss/formula/functions/Function3Arg.java b/src/java/org/apache/poi/ss/formula/functions/Function3Arg.java new file mode 100644 index 0000000000..ad5eee1586 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/functions/Function3Arg.java @@ -0,0 +1,32 @@ +/* ==================================================================== + 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.functions; + +import org.apache.poi.ss.formula.eval.ValueEval; + +/** + * Implemented by all functions that can be called with three arguments + * + * @author Josh Micich + */ +public interface Function3Arg extends Function { + /** + * see {@link Function#evaluate(ValueEval[], int, int)} + */ + ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1, ValueEval arg2); +} diff --git a/src/java/org/apache/poi/ss/formula/functions/Function4Arg.java b/src/java/org/apache/poi/ss/formula/functions/Function4Arg.java new file mode 100644 index 0000000000..f0eee52241 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/functions/Function4Arg.java @@ -0,0 +1,32 @@ +/* ==================================================================== + 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.functions; + +import org.apache.poi.ss.formula.eval.ValueEval; + +/** + * Implemented by all functions that can be called with four arguments + * + * @author Josh Micich + */ +public interface Function4Arg extends Function { + /** + * see {@link Function#evaluate(ValueEval[], int, int)} + */ + ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1, ValueEval arg2, ValueEval arg3); +} diff --git a/src/java/org/apache/poi/ss/formula/functions/Hlookup.java b/src/java/org/apache/poi/ss/formula/functions/Hlookup.java new file mode 100644 index 0000000000..c6190dcbac --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/functions/Hlookup.java @@ -0,0 +1,80 @@ +/* ==================================================================== + 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.functions; + +import org.apache.poi.ss.formula.eval.BoolEval; +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.LookupUtils.ValueVector; +import org.apache.poi.ss.formula.TwoDEval; +/** + * Implementation of the HLOOKUP() function.

    + * + * HLOOKUP finds a column in a lookup table by the first row value and returns the value from another row.
    + * + * Syntax:
    + * HLOOKUP(lookup_value, table_array, row_index_num, range_lookup)

    + * + * lookup_value The value to be found in the first column of the table array.
    + * table_array An area reference for the lookup data.
    + * row_index_num a 1 based index specifying which row value of the lookup data will be returned.
    + * range_lookup If TRUE (default), HLOOKUP finds the largest value less than or equal to + * the lookup_value. If FALSE, only exact matches will be considered
    + * + * @author Josh Micich + */ +public final class Hlookup extends Var3or4ArgFunction { + private static final ValueEval DEFAULT_ARG3 = BoolEval.TRUE; + + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1, + ValueEval arg2) { + return evaluate(srcRowIndex, srcColumnIndex, arg0, arg1, arg2, DEFAULT_ARG3); + } + + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1, + ValueEval arg2, ValueEval arg3) { + try { + // Evaluation order: + // arg0 lookup_value, arg1 table_array, arg3 range_lookup, find lookup value, arg2 row_index, fetch result + ValueEval lookupValue = OperandResolver.getSingleValue(arg0, srcRowIndex, srcColumnIndex); + TwoDEval tableArray = LookupUtils.resolveTableArrayArg(arg1); + boolean isRangeLookup = LookupUtils.resolveRangeLookupArg(arg3, srcRowIndex, srcColumnIndex); + int colIndex = LookupUtils.lookupIndexOfValue(lookupValue, LookupUtils.createRowVector(tableArray, 0), isRangeLookup); + int rowIndex = LookupUtils.resolveRowOrColIndexArg(arg2, srcRowIndex, srcColumnIndex); + ValueVector resultCol = createResultColumnVector(tableArray, rowIndex); + return resultCol.getItem(colIndex); + } catch (EvaluationException e) { + return e.getErrorEval(); + } + } + + /** + * Returns one column from an AreaEval + * + * @param rowIndex assumed to be non-negative + * + * @throws EvaluationException (#REF!) if colIndex is too high + */ + private ValueVector createResultColumnVector(TwoDEval tableArray, int rowIndex) throws EvaluationException { + if(rowIndex >= tableArray.getHeight()) { + throw EvaluationException.invalidRef(); + } + return LookupUtils.createRowVector(tableArray, rowIndex); + } +} diff --git a/src/java/org/apache/poi/ss/formula/functions/Hyperlink.java b/src/java/org/apache/poi/ss/formula/functions/Hyperlink.java new file mode 100644 index 0000000000..a43b040145 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/functions/Hyperlink.java @@ -0,0 +1,48 @@ +/* ==================================================================== + 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.functions; + +import org.apache.poi.ss.formula.eval.ValueEval; + +/** + * Implementation of Excel HYPERLINK function.

    + * + * In Excel this function has special behaviour - it causes the displayed cell value to behave like + * a hyperlink in the GUI. From an evaluation perspective however, it is very simple.

    + * + * Syntax:
    + * HYPERLINK(link_location, friendly_name)

    + * + * link_location The URL of the hyperlink
    + * friendly_name (optional) the value to display

    + * + * Returns last argument. Leaves type unchanged (does not convert to {@link org.apache.poi.ss.formula.eval.StringEval}). + * + * @author Wayne Clingingsmith + */ +public final class Hyperlink extends Var1or2ArgFunction { + + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0) { + return arg0; + } + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1) { + // note - if last arg is MissingArgEval, result will be NumberEval.ZERO, + // but WorkbookEvaluator does that translation + return arg1; + } +} diff --git a/src/java/org/apache/poi/ss/formula/functions/IfFunc.java b/src/java/org/apache/poi/ss/formula/functions/IfFunc.java new file mode 100644 index 0000000000..2af174f9df --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/functions/IfFunc.java @@ -0,0 +1,79 @@ +/* ==================================================================== + 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.functions; + +import org.apache.poi.ss.formula.eval.BlankEval; +import org.apache.poi.ss.formula.eval.BoolEval; +import org.apache.poi.ss.formula.eval.EvaluationException; +import org.apache.poi.ss.formula.eval.MissingArgEval; +import org.apache.poi.ss.formula.eval.OperandResolver; +import org.apache.poi.ss.formula.eval.ValueEval; + +/** + * Implementation for the Excel function IF + * + * @author Amol S. Deshmukh < amolweb at ya hoo dot com > + */ +public final class IfFunc extends Var2or3ArgFunction { + + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1) { + boolean b; + try { + b = evaluateFirstArg(arg0, srcRowIndex, srcColumnIndex); + } catch (EvaluationException e) { + return e.getErrorEval(); + } + if (b) { + if (arg1 == MissingArgEval.instance) { + return BlankEval.instance; + } + return arg1; + } + return BoolEval.FALSE; + } + + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1, + ValueEval arg2) { + boolean b; + try { + b = evaluateFirstArg(arg0, srcRowIndex, srcColumnIndex); + } catch (EvaluationException e) { + return e.getErrorEval(); + } + if (b) { + if (arg1 == MissingArgEval.instance) { + return BlankEval.instance; + } + return arg1; + } + if (arg2 == MissingArgEval.instance) { + return BlankEval.instance; + } + return arg2; + } + + public static boolean evaluateFirstArg(ValueEval arg, int srcCellRow, int srcCellCol) + throws EvaluationException { + ValueEval ve = OperandResolver.getSingleValue(arg, srcCellRow, srcCellCol); + Boolean b = OperandResolver.coerceValueToBoolean(ve, false); + if (b == null) { + return false; + } + return b.booleanValue(); + } +} diff --git a/src/java/org/apache/poi/ss/formula/functions/Index.java b/src/java/org/apache/poi/ss/formula/functions/Index.java new file mode 100644 index 0000000000..e4884b2061 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/functions/Index.java @@ -0,0 +1,171 @@ +/* ==================================================================== + 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.functions; + +import org.apache.poi.ss.formula.eval.BlankEval; +import org.apache.poi.ss.formula.eval.ErrorEval; +import org.apache.poi.ss.formula.eval.EvaluationException; +import org.apache.poi.ss.formula.eval.MissingArgEval; +import org.apache.poi.ss.formula.eval.OperandResolver; +import org.apache.poi.ss.formula.eval.RefEval; +import org.apache.poi.ss.formula.eval.ValueEval; +import org.apache.poi.ss.formula.TwoDEval; + +/** + * Implementation for the Excel function INDEX + *

    + * + * Syntax :
    + * INDEX ( reference, row_num[, column_num [, area_num]])
    + * INDEX ( array, row_num[, column_num]) + * + * + * + * + * + * + *
    referencetypically an area reference, possibly a union of areas
    arraya literal array value (currently not supported)
    row_numselects the row within the array or area reference
    column_numselects column within the array or area reference. default is 1
    area_numused when reference is a union of areas
    + *

    + * + * @author Josh Micich + */ +public final class Index implements Function2Arg, Function3Arg, Function4Arg { + + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1) { + TwoDEval reference = convertFirstArg(arg0); + + int columnIx = 0; + try { + int rowIx = resolveIndexArg(arg1, srcRowIndex, srcColumnIndex); + + if (!reference.isColumn()) { + if (!reference.isRow()) { + // always an error with 2-D area refs + // Note - the type of error changes if the pRowArg is negative + return ErrorEval.REF_INVALID; + } + // When the two-arg version of INDEX() has been invoked and the reference + // is a single column ref, the row arg seems to get used as the column index + columnIx = rowIx; + rowIx = 0; + } + + return getValueFromArea(reference, rowIx, columnIx); + } catch (EvaluationException e) { + return e.getErrorEval(); + } + } + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1, + ValueEval arg2) { + TwoDEval reference = convertFirstArg(arg0); + + try { + int columnIx = resolveIndexArg(arg2, srcRowIndex, srcColumnIndex); + int rowIx = resolveIndexArg(arg1, srcRowIndex, srcColumnIndex); + return getValueFromArea(reference, rowIx, columnIx); + } catch (EvaluationException e) { + return e.getErrorEval(); + } + } + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1, + ValueEval arg2, ValueEval arg3) { + throw new RuntimeException("Incomplete code" + + " - don't know how to support the 'area_num' parameter yet)"); + // Excel expression might look like this "INDEX( (A1:B4, C3:D6, D2:E5 ), 1, 2, 3) + // In this example, the 3rd area would be used i.e. D2:E5, and the overall result would be E2 + // Token array might be encoded like this: MemAreaPtg, AreaPtg, AreaPtg, UnionPtg, UnionPtg, ParenthesesPtg + // The formula parser doesn't seem to support this yet. Not sure if the evaluator does either + } + + private static TwoDEval convertFirstArg(ValueEval arg0) { + ValueEval firstArg = arg0; + if (firstArg instanceof RefEval) { + // convert to area ref for simpler code in getValueFromArea() + return ((RefEval)firstArg).offset(0, 0, 0, 0); + } + if((firstArg instanceof TwoDEval)) { + return (TwoDEval) firstArg; + } + // else the other variation of this function takes an array as the first argument + // it seems like interface 'ArrayEval' does not even exist yet + throw new RuntimeException("Incomplete code - cannot handle first arg of type (" + + firstArg.getClass().getName() + ")"); + + } + + public ValueEval evaluate(ValueEval[] args, int srcRowIndex, int srcColumnIndex) { + switch (args.length) { + case 2: + return evaluate(srcRowIndex, srcColumnIndex, args[0], args[1]); + case 3: + return evaluate(srcRowIndex, srcColumnIndex, args[0], args[1], args[2]); + case 4: + return evaluate(srcRowIndex, srcColumnIndex, args[0], args[1], args[2], args[3]); + } + return ErrorEval.VALUE_INVALID; + } + + private static ValueEval getValueFromArea(TwoDEval ae, int pRowIx, int pColumnIx) + throws EvaluationException { + assert pRowIx >= 0; + assert pColumnIx >= 0; + + TwoDEval result = ae; + + if (pRowIx != 0) { + // Slightly irregular logic for bounds checking errors + if (pRowIx > ae.getHeight()) { + // high bounds check fail gives #REF! if arg was explicitly passed + throw new EvaluationException(ErrorEval.REF_INVALID); + } + result = result.getRow(pRowIx-1); + } + + if (pColumnIx != 0) { + // Slightly irregular logic for bounds checking errors + if (pColumnIx > ae.getWidth()) { + // high bounds check fail gives #REF! if arg was explicitly passed + throw new EvaluationException(ErrorEval.REF_INVALID); + } + result = result.getColumn(pColumnIx-1); + } + return result; + } + + + /** + * @param arg a 1-based index. + * @return the resolved 1-based index. Zero if the arg was missing or blank + * @throws EvaluationException if the arg is an error value evaluates to a negative numeric value + */ + private static int resolveIndexArg(ValueEval arg, int srcCellRow, int srcCellCol) throws EvaluationException { + + ValueEval ev = OperandResolver.getSingleValue(arg, srcCellRow, srcCellCol); + if (ev == MissingArgEval.instance) { + return 0; + } + if (ev == BlankEval.instance) { + return 0; + } + int result = OperandResolver.coerceValueToInt(ev); + if (result < 0) { + throw new EvaluationException(ErrorEval.VALUE_INVALID); + } + return result; + } +} diff --git a/src/java/org/apache/poi/ss/formula/functions/Indirect.java b/src/java/org/apache/poi/ss/formula/functions/Indirect.java new file mode 100644 index 0000000000..7774af506d --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/functions/Indirect.java @@ -0,0 +1,239 @@ +/* ==================================================================== + 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.functions; + +import org.apache.poi.ss.formula.eval.BlankEval; +import org.apache.poi.ss.formula.eval.ErrorEval; +import org.apache.poi.ss.formula.eval.EvaluationException; +import org.apache.poi.ss.formula.eval.MissingArgEval; +import org.apache.poi.ss.formula.eval.OperandResolver; +import org.apache.poi.ss.formula.eval.ValueEval; +import org.apache.poi.ss.formula.OperationEvaluationContext; + +/** + * Implementation for Excel function INDIRECT

    + * + * INDIRECT() returns the cell or area reference denoted by the text argument.

    + * + * Syntax:
    + * INDIRECT(ref_text,isA1Style)

    + * + * ref_text a string representation of the desired reference as it would + * normally be written in a cell formula.
    + * isA1Style (default TRUE) specifies whether the ref_text should be + * interpreted as A1-style or R1C1-style. + * + * @author Josh Micich + */ +public final class Indirect implements FreeRefFunction { + + public static final FreeRefFunction instance = new Indirect(); + + private Indirect() { + // enforce singleton + } + + public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) { + if (args.length < 1) { + return ErrorEval.VALUE_INVALID; + } + + boolean isA1style; + String text; + try { + ValueEval ve = OperandResolver.getSingleValue(args[0], ec.getRowIndex(), ec + .getColumnIndex()); + text = OperandResolver.coerceValueToString(ve); + switch (args.length) { + case 1: + isA1style = true; + break; + case 2: + isA1style = evaluateBooleanArg(args[1], ec); + break; + default: + return ErrorEval.VALUE_INVALID; + } + } catch (EvaluationException e) { + return e.getErrorEval(); + } + + return evaluateIndirect(ec, text, isA1style); + } + + private static boolean evaluateBooleanArg(ValueEval arg, OperationEvaluationContext ec) + throws EvaluationException { + ValueEval ve = OperandResolver.getSingleValue(arg, ec.getRowIndex(), ec.getColumnIndex()); + + if (ve == BlankEval.instance || ve == MissingArgEval.instance) { + return false; + } + // numeric quantities follow standard boolean conversion rules + // for strings, only "TRUE" and "FALSE" (case insensitive) are valid + return OperandResolver.coerceValueToBoolean(ve, false).booleanValue(); + } + + private static ValueEval evaluateIndirect(OperationEvaluationContext ec, String text, + boolean isA1style) { + // Search backwards for '!' because sheet names can contain '!' + int plingPos = text.lastIndexOf('!'); + + String workbookName; + String sheetName; + String refText; // whitespace around this gets trimmed OK + if (plingPos < 0) { + workbookName = null; + sheetName = null; + refText = text; + } else { + String[] parts = parseWorkbookAndSheetName(text.subSequence(0, plingPos)); + if (parts == null) { + return ErrorEval.REF_INVALID; + } + workbookName = parts[0]; + sheetName = parts[1]; + refText = text.substring(plingPos + 1); + } + + String refStrPart1; + String refStrPart2; + + int colonPos = refText.indexOf(':'); + if (colonPos < 0) { + refStrPart1 = refText.trim(); + refStrPart2 = null; + } else { + refStrPart1 = refText.substring(0, colonPos).trim(); + refStrPart2 = refText.substring(colonPos + 1).trim(); + } + return ec.getDynamicReference(workbookName, sheetName, refStrPart1, refStrPart2, isA1style); + } + + /** + * @return array of length 2: {workbookName, sheetName,}. Second element will always be + * present. First element may be null if sheetName is unqualified. + * Returns null if text cannot be parsed. + */ + private static String[] parseWorkbookAndSheetName(CharSequence text) { + int lastIx = text.length() - 1; + if (lastIx < 0) { + return null; + } + if (canTrim(text)) { + return null; + } + char firstChar = text.charAt(0); + if (Character.isWhitespace(firstChar)) { + return null; + } + if (firstChar == '\'') { + // workbookName or sheetName needs quoting + // quotes go around both + if (text.charAt(lastIx) != '\'') { + return null; + } + firstChar = text.charAt(1); + if (Character.isWhitespace(firstChar)) { + return null; + } + String wbName; + int sheetStartPos; + if (firstChar == '[') { + int rbPos = text.toString().lastIndexOf(']'); + if (rbPos < 0) { + return null; + } + wbName = unescapeString(text.subSequence(2, rbPos)); + if (wbName == null || canTrim(wbName)) { + return null; + } + sheetStartPos = rbPos + 1; + } else { + wbName = null; + sheetStartPos = 1; + } + + // else - just sheet name + String sheetName = unescapeString(text.subSequence(sheetStartPos, lastIx)); + if (sheetName == null) { // note - when quoted, sheetName can + // start/end with whitespace + return null; + } + return new String[] { wbName, sheetName, }; + } + + if (firstChar == '[') { + int rbPos = text.toString().lastIndexOf(']'); + if (rbPos < 0) { + return null; + } + CharSequence wbName = text.subSequence(1, rbPos); + if (canTrim(wbName)) { + return null; + } + CharSequence sheetName = text.subSequence(rbPos + 1, text.length()); + if (canTrim(sheetName)) { + return null; + } + return new String[] { wbName.toString(), sheetName.toString(), }; + } + // else - just sheet name + return new String[] { null, text.toString(), }; + } + + /** + * @return null if there is a syntax error in any escape sequence + * (the typical syntax error is a single quote character not followed by another). + */ + private static String unescapeString(CharSequence text) { + int len = text.length(); + StringBuilder sb = new StringBuilder(len); + int i = 0; + while (i < len) { + char ch = text.charAt(i); + if (ch == '\'') { + // every quote must be followed by another + i++; + if (i >= len) { + return null; + } + ch = text.charAt(i); + if (ch != '\'') { + return null; + } + } + sb.append(ch); + i++; + } + return sb.toString(); + } + + private static boolean canTrim(CharSequence text) { + int lastIx = text.length() - 1; + if (lastIx < 0) { + return false; + } + if (Character.isWhitespace(text.charAt(0))) { + return true; + } + if (Character.isWhitespace(text.charAt(lastIx))) { + return true; + } + return false; + } +} diff --git a/src/java/org/apache/poi/ss/formula/functions/LogicalFunction.java b/src/java/org/apache/poi/ss/formula/functions/LogicalFunction.java new file mode 100644 index 0000000000..ef18dab645 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/functions/LogicalFunction.java @@ -0,0 +1,119 @@ +/* ==================================================================== + 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.functions; + +import org.apache.poi.ss.formula.eval.AreaEval; +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.EvaluationException; +import org.apache.poi.ss.formula.eval.NumberEval; +import org.apache.poi.ss.formula.eval.OperandResolver; +import org.apache.poi.ss.formula.eval.RefEval; +import org.apache.poi.ss.formula.eval.StringEval; +import org.apache.poi.ss.formula.eval.ValueEval; + +/** + * @author Amol S. Deshmukh < amolweb at ya hoo dot com > + * @author Josh Micich + */ +public abstract class LogicalFunction extends Fixed1ArgFunction { + + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0) { + ValueEval ve; + try { + ve = OperandResolver.getSingleValue(arg0, srcRowIndex, srcColumnIndex); + } catch (EvaluationException e) { + if (false) { + // Note - it is more usual to propagate error codes straight to the result like this: + return e.getErrorEval(); + // but logical functions behave a little differently + } + // this will usually cause a 'FALSE' result except for ISNONTEXT() + ve = e.getErrorEval(); + } + return BoolEval.valueOf(evaluate(ve)); + + } + /** + * @param arg any {@link ValueEval}, potentially {@link BlankEval} or {@link ErrorEval}. + */ + protected abstract boolean evaluate(ValueEval arg); + + public static final Function ISLOGICAL = new LogicalFunction() { + protected boolean evaluate(ValueEval arg) { + return arg instanceof BoolEval; + } + }; + public static final Function ISNONTEXT = new LogicalFunction() { + protected boolean evaluate(ValueEval arg) { + return !(arg instanceof StringEval); + } + }; + public static final Function ISNUMBER = new LogicalFunction() { + protected boolean evaluate(ValueEval arg) { + return arg instanceof NumberEval; + } + }; + public static final Function ISTEXT = new LogicalFunction() { + protected boolean evaluate(ValueEval arg) { + return arg instanceof StringEval; + } + }; + + public static final Function ISBLANK = new LogicalFunction() { + + protected boolean evaluate(ValueEval arg) { + return arg instanceof BlankEval; + } + }; + + public static final Function ISERROR = new LogicalFunction() { + + protected boolean evaluate(ValueEval arg) { + return arg instanceof ErrorEval; + } + }; + + /** + * Implementation for Excel ISNA() function.

    + * + * Syntax:
    + * ISNA(value)

    + * + * value The value to be tested
    + *
    + * Returns TRUE if the specified value is '#N/A', FALSE otherwise. + */ + public static final Function ISNA = new LogicalFunction() { + + protected boolean evaluate(ValueEval arg) { + return arg == ErrorEval.NA; + } + }; + + public static final Function ISREF = new Fixed1ArgFunction() { + + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0) { + if (arg0 instanceof RefEval || arg0 instanceof AreaEval) { + return BoolEval.TRUE; + } + return BoolEval.FALSE; + } + }; +} diff --git a/src/java/org/apache/poi/ss/formula/functions/Lookup.java b/src/java/org/apache/poi/ss/formula/functions/Lookup.java new file mode 100644 index 0000000000..10f8a09a75 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/functions/Lookup.java @@ -0,0 +1,76 @@ +/* ==================================================================== + 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.functions; + +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.LookupUtils.ValueVector; +import org.apache.poi.ss.formula.TwoDEval; + +/** + * Implementation of Excel function LOOKUP.

    + * + * LOOKUP finds an index row in a lookup table by the first column value and returns the value from another column. + * + * Syntax:
    + * VLOOKUP(lookup_value, lookup_vector, result_vector)

    + * + * lookup_value The value to be found in the lookup vector.
    + * lookup_vector An area reference for the lookup data.
    + * result_vector Single row or single column area reference from which the result value is chosen.
    + * + * @author Josh Micich + */ +public final class Lookup extends Var2or3ArgFunction { + + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1) { + // complex rules to choose lookupVector and resultVector from the single area ref + throw new RuntimeException("Two arg version of LOOKUP not supported yet"); + } + + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1, + ValueEval arg2) { + try { + ValueEval lookupValue = OperandResolver.getSingleValue(arg0, srcRowIndex, srcColumnIndex); + TwoDEval aeLookupVector = LookupUtils.resolveTableArrayArg(arg1); + TwoDEval aeResultVector = LookupUtils.resolveTableArrayArg(arg2); + + ValueVector lookupVector = createVector(aeLookupVector); + ValueVector resultVector = createVector(aeResultVector); + if(lookupVector.getSize() > resultVector.getSize()) { + // Excel seems to handle this by accessing past the end of the result vector. + throw new RuntimeException("Lookup vector and result vector of differing sizes not supported yet"); + } + int index = LookupUtils.lookupIndexOfValue(lookupValue, lookupVector, true); + + return resultVector.getItem(index); + } catch (EvaluationException e) { + return e.getErrorEval(); + } + } + + private static ValueVector createVector(TwoDEval ae) { + ValueVector result = LookupUtils.createVector(ae); + if (result != null) { + return result; + } + // extra complexity required to emulate the way LOOKUP can handles these abnormal cases. + throw new RuntimeException("non-vector lookup or result areas not supported yet"); + } +} diff --git a/src/java/org/apache/poi/ss/formula/functions/LookupUtils.java b/src/java/org/apache/poi/ss/formula/functions/LookupUtils.java new file mode 100644 index 0000000000..62bc71b120 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/functions/LookupUtils.java @@ -0,0 +1,603 @@ +/* ==================================================================== + 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.functions; + +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.EvaluationException; +import org.apache.poi.ss.formula.eval.NumberEval; +import org.apache.poi.ss.formula.eval.NumericValueEval; +import org.apache.poi.ss.formula.eval.OperandResolver; +import org.apache.poi.ss.formula.eval.RefEval; +import org.apache.poi.ss.formula.eval.StringEval; +import org.apache.poi.ss.formula.eval.ValueEval; +import org.apache.poi.ss.formula.TwoDEval; + +/** + * Common functionality used by VLOOKUP, HLOOKUP, LOOKUP and MATCH + * + * @author Josh Micich + */ +final class LookupUtils { + + /** + * Represents a single row or column within an AreaEval. + */ + public interface ValueVector { + ValueEval getItem(int index); + int getSize(); + } + + + private static final class RowVector implements ValueVector { + + private final TwoDEval _tableArray; + private final int _size; + private final int _rowIndex; + + public RowVector(TwoDEval tableArray, int rowIndex) { + _rowIndex = rowIndex; + int lastRowIx = tableArray.getHeight() - 1; + if(rowIndex < 0 || rowIndex > lastRowIx) { + throw new IllegalArgumentException("Specified row index (" + rowIndex + + ") is outside the allowed range (0.." + lastRowIx + ")"); + } + _tableArray = tableArray; + _size = tableArray.getWidth(); + } + + public ValueEval getItem(int index) { + if(index > _size) { + throw new ArrayIndexOutOfBoundsException("Specified index (" + index + + ") is outside the allowed range (0.." + (_size-1) + ")"); + } + return _tableArray.getValue(_rowIndex, index); + } + public int getSize() { + return _size; + } + } + + private static final class ColumnVector implements ValueVector { + + private final TwoDEval _tableArray; + private final int _size; + private final int _columnIndex; + + public ColumnVector(TwoDEval tableArray, int columnIndex) { + _columnIndex = columnIndex; + int lastColIx = tableArray.getWidth()-1; + if(columnIndex < 0 || columnIndex > lastColIx) { + throw new IllegalArgumentException("Specified column index (" + columnIndex + + ") is outside the allowed range (0.." + lastColIx + ")"); + } + _tableArray = tableArray; + _size = _tableArray.getHeight(); + } + + public ValueEval getItem(int index) { + if(index > _size) { + throw new ArrayIndexOutOfBoundsException("Specified index (" + index + + ") is outside the allowed range (0.." + (_size-1) + ")"); + } + return _tableArray.getValue(index, _columnIndex); + } + public int getSize() { + return _size; + } + } + + public static ValueVector createRowVector(TwoDEval tableArray, int relativeRowIndex) { + return new RowVector(tableArray, relativeRowIndex); + } + public static ValueVector createColumnVector(TwoDEval tableArray, int relativeColumnIndex) { + return new ColumnVector(tableArray, relativeColumnIndex); + } + /** + * @return null if the supplied area is neither a single row nor a single colum + */ + public static ValueVector createVector(TwoDEval ae) { + if (ae.isColumn()) { + return createColumnVector(ae, 0); + } + if (ae.isRow()) { + return createRowVector(ae, 0); + } + return null; + } + + /** + * Enumeration to support 4 valued comparison results.

    + * Excel lookup functions have complex behaviour in the case where the lookup array has mixed + * types, and/or is unordered. Contrary to suggestions in some Excel documentation, there + * does not appear to be a universal ordering across types. The binary search algorithm used + * changes behaviour when the evaluated 'mid' value has a different type to the lookup value.

    + * + * A simple int might have done the same job, but there is risk in confusion with the well + * known Comparable.compareTo() and Comparator.compare() which both use + * a ubiquitous 3 value result encoding. + */ + public static final class CompareResult { + private final boolean _isTypeMismatch; + private final boolean _isLessThan; + private final boolean _isEqual; + private final boolean _isGreaterThan; + + private CompareResult(boolean isTypeMismatch, int simpleCompareResult) { + if(isTypeMismatch) { + _isTypeMismatch = true; + _isLessThan = false; + _isEqual = false; + _isGreaterThan = false; + } else { + _isTypeMismatch = false; + _isLessThan = simpleCompareResult < 0; + _isEqual = simpleCompareResult == 0; + _isGreaterThan = simpleCompareResult > 0; + } + } + public static final CompareResult TYPE_MISMATCH = new CompareResult(true, 0); + public static final CompareResult LESS_THAN = new CompareResult(false, -1); + public static final CompareResult EQUAL = new CompareResult(false, 0); + public static final CompareResult GREATER_THAN = new CompareResult(false, +1); + + public static final CompareResult valueOf(int simpleCompareResult) { + if(simpleCompareResult < 0) { + return LESS_THAN; + } + if(simpleCompareResult > 0) { + return GREATER_THAN; + } + return EQUAL; + } + + public boolean isTypeMismatch() { + return _isTypeMismatch; + } + public boolean isLessThan() { + return _isLessThan; + } + public boolean isEqual() { + return _isEqual; + } + public boolean isGreaterThan() { + return _isGreaterThan; + } + public String toString() { + StringBuffer sb = new StringBuffer(64); + sb.append(getClass().getName()).append(" ["); + sb.append(formatAsString()); + sb.append("]"); + return sb.toString(); + } + + private String formatAsString() { + if(_isTypeMismatch) { + return "TYPE_MISMATCH"; + } + if(_isLessThan) { + return "LESS_THAN"; + } + if(_isEqual) { + return "EQUAL"; + } + if(_isGreaterThan) { + return "GREATER_THAN"; + } + // toString must be reliable + return "??error??"; + } + } + + public interface LookupValueComparer { + /** + * @return one of 4 instances or CompareResult: LESS_THAN, EQUAL, + * GREATER_THAN or TYPE_MISMATCH + */ + CompareResult compareTo(ValueEval other); + } + + private static abstract class LookupValueComparerBase implements LookupValueComparer { + + private final Class _targetClass; + protected LookupValueComparerBase(ValueEval targetValue) { + if(targetValue == null) { + throw new RuntimeException("targetValue cannot be null"); + } + _targetClass = targetValue.getClass(); + } + public final CompareResult compareTo(ValueEval other) { + if (other == null) { + throw new RuntimeException("compare to value cannot be null"); + } + if (_targetClass != other.getClass()) { + return CompareResult.TYPE_MISMATCH; + } + return compareSameType(other); + } + public String toString() { + StringBuffer sb = new StringBuffer(64); + sb.append(getClass().getName()).append(" ["); + sb.append(getValueAsString()); + sb.append("]"); + return sb.toString(); + } + protected abstract CompareResult compareSameType(ValueEval other); + /** used only for debug purposes */ + protected abstract String getValueAsString(); + } + + private static final class StringLookupComparer extends LookupValueComparerBase { + private String _value; + + protected StringLookupComparer(StringEval se) { + super(se); + _value = se.getStringValue(); + } + protected CompareResult compareSameType(ValueEval other) { + StringEval se = (StringEval) other; + return CompareResult.valueOf(_value.compareToIgnoreCase(se.getStringValue())); + } + protected String getValueAsString() { + return _value; + } + } + private static final class NumberLookupComparer extends LookupValueComparerBase { + private double _value; + + protected NumberLookupComparer(NumberEval ne) { + super(ne); + _value = ne.getNumberValue(); + } + protected CompareResult compareSameType(ValueEval other) { + NumberEval ne = (NumberEval) other; + return CompareResult.valueOf(Double.compare(_value, ne.getNumberValue())); + } + protected String getValueAsString() { + return String.valueOf(_value); + } + } + private static final class BooleanLookupComparer extends LookupValueComparerBase { + private boolean _value; + + protected BooleanLookupComparer(BoolEval be) { + super(be); + _value = be.getBooleanValue(); + } + protected CompareResult compareSameType(ValueEval other) { + BoolEval be = (BoolEval) other; + boolean otherVal = be.getBooleanValue(); + if(_value == otherVal) { + return CompareResult.EQUAL; + } + // TRUE > FALSE + if(_value) { + return CompareResult.GREATER_THAN; + } + return CompareResult.LESS_THAN; + } + protected String getValueAsString() { + return String.valueOf(_value); + } + } + + /** + * Processes the third argument to VLOOKUP, or HLOOKUP (col_index_num + * or row_index_num respectively).
    + * Sample behaviour: + * + * + * + * + * + * + * + * + * + * + * + * + * + *
    Input   ReturnValue  Thrown Error
    54 
    2.92 
    "5"4 
    "2.18e1"21 
    "-$2"-3*
    FALSE-1*
    TRUE0 
    "TRUE" #REF!
    "abc" #REF!
    "" #REF!
    <blank> #VALUE!

    + * + * Note - out of range errors (result index too high) are handled by the caller. + * @return column or row index as a zero-based value, never negative. + * @throws EvaluationException when the specified arg cannot be coerced to a non-negative integer + */ + public static int resolveRowOrColIndexArg(ValueEval rowColIndexArg, int srcCellRow, int srcCellCol) throws EvaluationException { + if(rowColIndexArg == null) { + throw new IllegalArgumentException("argument must not be null"); + } + + ValueEval veRowColIndexArg; + try { + veRowColIndexArg = OperandResolver.getSingleValue(rowColIndexArg, srcCellRow, (short)srcCellCol); + } catch (EvaluationException e) { + // All errors get translated to #REF! + throw EvaluationException.invalidRef(); + } + int oneBasedIndex; + if(veRowColIndexArg instanceof StringEval) { + StringEval se = (StringEval) veRowColIndexArg; + String strVal = se.getStringValue(); + Double dVal = OperandResolver.parseDouble(strVal); + if(dVal == null) { + // String does not resolve to a number. Raise #REF! error. + throw EvaluationException.invalidRef(); + // This includes text booleans "TRUE" and "FALSE". They are not valid. + } + // else - numeric value parses OK + } + // actual BoolEval values get interpreted as FALSE->0 and TRUE->1 + oneBasedIndex = OperandResolver.coerceValueToInt(veRowColIndexArg); + if (oneBasedIndex < 1) { + // note this is asymmetric with the errors when the index is too large (#REF!) + throw EvaluationException.invalidValue(); + } + return oneBasedIndex - 1; // convert to zero based + } + + + + /** + * The second argument (table_array) should be an area ref, but can actually be a cell ref, in + * which case it is interpreted as a 1x1 area ref. Other scalar values cause #VALUE! error. + */ + public static TwoDEval resolveTableArrayArg(ValueEval eval) throws EvaluationException { + if (eval instanceof TwoDEval) { + return (TwoDEval) eval; + } + + if(eval instanceof RefEval) { + RefEval refEval = (RefEval) eval; + // Make this cell ref look like a 1x1 area ref. + + // It doesn't matter if eval is a 2D or 3D ref, because that detail is never asked of AreaEval. + return refEval.offset(0, 0, 0, 0); + } + throw EvaluationException.invalidValue(); + } + + + /** + * Resolves the last (optional) parameter (range_lookup) to the VLOOKUP and HLOOKUP functions. + * @param rangeLookupArg must not be null + */ + public static boolean resolveRangeLookupArg(ValueEval rangeLookupArg, int srcCellRow, int srcCellCol) throws EvaluationException { + + ValueEval valEval = OperandResolver.getSingleValue(rangeLookupArg, srcCellRow, srcCellCol); + if(valEval instanceof BlankEval) { + // Tricky: + // fourth arg supplied but evaluates to blank + // this does not get the default value + return false; + } + if(valEval instanceof BoolEval) { + // Happy day flow + BoolEval boolEval = (BoolEval) valEval; + return boolEval.getBooleanValue(); + } + + if (valEval instanceof StringEval) { + String stringValue = ((StringEval) valEval).getStringValue(); + if(stringValue.length() < 1) { + // More trickiness: + // Empty string is not the same as BlankEval. It causes #VALUE! error + throw EvaluationException.invalidValue(); + } + // TODO move parseBoolean to OperandResolver + Boolean b = Countif.parseBoolean(stringValue); + if(b != null) { + // string converted to boolean OK + return b.booleanValue(); + } + // Even more trickiness: + // Note - even if the StringEval represents a number value (for example "1"), + // Excel does not resolve it to a boolean. + throw EvaluationException.invalidValue(); + // This is in contrast to the code below,, where NumberEvals values (for + // example 0.01) *do* resolve to equivalent boolean values. + } + if (valEval instanceof NumericValueEval) { + NumericValueEval nve = (NumericValueEval) valEval; + // zero is FALSE, everything else is TRUE + return 0.0 != nve.getNumberValue(); + } + throw new RuntimeException("Unexpected eval type (" + valEval.getClass().getName() + ")"); + } + + public static int lookupIndexOfValue(ValueEval lookupValue, ValueVector vector, boolean isRangeLookup) throws EvaluationException { + LookupValueComparer lookupComparer = createLookupComparer(lookupValue); + int result; + if(isRangeLookup) { + result = performBinarySearch(vector, lookupComparer); + } else { + result = lookupIndexOfExactValue(lookupComparer, vector); + } + if(result < 0) { + throw new EvaluationException(ErrorEval.NA); + } + return result; + } + + + /** + * Finds first (lowest index) exact occurrence of specified value. + * @param lookupValue the value to be found in column or row vector + * @param vector the values to be searched. For VLOOKUP this is the first column of the + * tableArray. For HLOOKUP this is the first row of the tableArray. + * @return zero based index into the vector, -1 if value cannot be found + */ + private static int lookupIndexOfExactValue(LookupValueComparer lookupComparer, ValueVector vector) { + + // find first occurrence of lookup value + int size = vector.getSize(); + for (int i = 0; i < size; i++) { + if(lookupComparer.compareTo(vector.getItem(i)).isEqual()) { + return i; + } + } + return -1; + } + + + /** + * Encapsulates some standard binary search functionality so the unusual Excel behaviour can + * be clearly distinguished. + */ + private static final class BinarySearchIndexes { + + private int _lowIx; + private int _highIx; + + public BinarySearchIndexes(int highIx) { + _lowIx = -1; + _highIx = highIx; + } + + /** + * @return -1 if the search range is empty + */ + public int getMidIx() { + int ixDiff = _highIx - _lowIx; + if(ixDiff < 2) { + return -1; + } + return _lowIx + (ixDiff / 2); + } + + public int getLowIx() { + return _lowIx; + } + public int getHighIx() { + return _highIx; + } + public void narrowSearch(int midIx, boolean isLessThan) { + if(isLessThan) { + _highIx = midIx; + } else { + _lowIx = midIx; + } + } + } + /** + * Excel has funny behaviour when the some elements in the search vector are the wrong type. + * + */ + private static int performBinarySearch(ValueVector vector, LookupValueComparer lookupComparer) { + // both low and high indexes point to values assumed too low and too high. + BinarySearchIndexes bsi = new BinarySearchIndexes(vector.getSize()); + + while(true) { + int midIx = bsi.getMidIx(); + + if(midIx < 0) { + return bsi.getLowIx(); + } + CompareResult cr = lookupComparer.compareTo(vector.getItem(midIx)); + if(cr.isTypeMismatch()) { + int newMidIx = handleMidValueTypeMismatch(lookupComparer, vector, bsi, midIx); + if(newMidIx < 0) { + continue; + } + midIx = newMidIx; + cr = lookupComparer.compareTo(vector.getItem(midIx)); + } + if(cr.isEqual()) { + return findLastIndexInRunOfEqualValues(lookupComparer, vector, midIx, bsi.getHighIx()); + } + bsi.narrowSearch(midIx, cr.isLessThan()); + } + } + /** + * Excel seems to handle mismatched types initially by just stepping 'mid' ix forward to the + * first compatible value. + * @param midIx 'mid' index (value which has the wrong type) + * @return usually -1, signifying that the BinarySearchIndex has been narrowed to the new mid + * index. Zero or greater signifies that an exact match for the lookup value was found + */ + private static int handleMidValueTypeMismatch(LookupValueComparer lookupComparer, ValueVector vector, + BinarySearchIndexes bsi, int midIx) { + int newMid = midIx; + int highIx = bsi.getHighIx(); + + while(true) { + newMid++; + if(newMid == highIx) { + // every element from midIx to highIx was the wrong type + // move highIx down to the low end of the mid values + bsi.narrowSearch(midIx, true); + return -1; + } + CompareResult cr = lookupComparer.compareTo(vector.getItem(newMid)); + if(cr.isLessThan() && newMid == highIx-1) { + // move highIx down to the low end of the mid values + bsi.narrowSearch(midIx, true); + return -1; + // but only when "newMid == highIx-1"? slightly weird. + // It would seem more efficient to always do this. + } + if(cr.isTypeMismatch()) { + // keep stepping over values until the right type is found + continue; + } + if(cr.isEqual()) { + return newMid; + } + // Note - if moving highIx down (due to lookup + * + * Syntax:
    + * MATCH(lookup_value, lookup_array, match_type)

    + * + * Returns a 1-based index specifying at what position in the lookup_array the specified + * lookup_value is found.

    + * + * Specific matching behaviour can be modified with the optional match_type parameter. + * + * + * + * + * + * + *
    ValueMatching Behaviour
    1(default) find the largest value that is less than or equal to lookup_value. + * The lookup_array must be in ascending order*.
    0find the first value that is exactly equal to lookup_value. + * The lookup_array can be in any order.
    -1find the smallest value that is greater than or equal to lookup_value. + * The lookup_array must be in descending order*.
    + * + * * Note regarding order - For the match_type cases that require the lookup_array to + * be ordered, MATCH() can produce incorrect results if this requirement is not met. Observed + * behaviour in Excel is to return the lowest index value for which every item after that index + * breaks the match rule.
    + * The (ascending) sort order expected by MATCH() is:
    + * numbers (low to high), strings (A to Z), boolean (FALSE to TRUE)
    + * MATCH() ignores all elements in the lookup_array with a different type to the lookup_value. + * Type conversion of the lookup_array elements is never performed. + * + * + * @author Josh Micich + */ +public final class Match extends Var2or3ArgFunction { + + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1) { + // default match_type is 1.0 + return eval(srcRowIndex, srcColumnIndex, arg0, arg1, 1.0); + } + + + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1, + ValueEval arg2) { + + double match_type; + + try { + match_type = evaluateMatchTypeArg(arg2, srcRowIndex, srcColumnIndex); + } catch (EvaluationException e) { + // Excel/MATCH() seems to have slightly abnormal handling of errors with + // the last parameter. Errors do not propagate up. Every error gets + // translated into #REF! + return ErrorEval.REF_INVALID; + } + + return eval(srcRowIndex, srcColumnIndex, arg0, arg1, match_type); + } + + private static ValueEval eval(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1, + double match_type) { + boolean matchExact = match_type == 0; + // Note - Excel does not strictly require -1 and +1 + boolean findLargestLessThanOrEqual = match_type > 0; + + try { + ValueEval lookupValue = OperandResolver.getSingleValue(arg0, srcRowIndex, srcColumnIndex); + ValueVector lookupRange = evaluateLookupRange(arg1); + int index = findIndexOfValue(lookupValue, lookupRange, matchExact, findLargestLessThanOrEqual); + return new NumberEval(index + 1); // +1 to convert to 1-based + } catch (EvaluationException e) { + return e.getErrorEval(); + } + } + + private static final class SingleValueVector implements ValueVector { + + private final ValueEval _value; + + public SingleValueVector(ValueEval value) { + _value = value; + } + + public ValueEval getItem(int index) { + if (index != 0) { + throw new RuntimeException("Invalid index (" + + index + ") only zero is allowed"); + } + return _value; + } + + public int getSize() { + return 1; + } + } + + private static ValueVector evaluateLookupRange(ValueEval eval) throws EvaluationException { + if (eval instanceof RefEval) { + RefEval re = (RefEval) eval; + return new SingleValueVector(re.getInnerValueEval()); + } + if (eval instanceof TwoDEval) { + ValueVector result = LookupUtils.createVector((TwoDEval)eval); + if (result == null) { + throw new EvaluationException(ErrorEval.NA); + } + return result; + } + + // Error handling for lookup_range arg is also unusual + if(eval instanceof NumericValueEval) { + throw new EvaluationException(ErrorEval.NA); + } + if (eval instanceof StringEval) { + StringEval se = (StringEval) eval; + Double d = OperandResolver.parseDouble(se.getStringValue()); + if(d == null) { + // plain string + throw new EvaluationException(ErrorEval.VALUE_INVALID); + } + // else looks like a number + throw new EvaluationException(ErrorEval.NA); + } + throw new RuntimeException("Unexpected eval type (" + eval.getClass().getName() + ")"); + } + + + + private static double evaluateMatchTypeArg(ValueEval arg, int srcCellRow, int srcCellCol) + throws EvaluationException { + ValueEval match_type = OperandResolver.getSingleValue(arg, srcCellRow, srcCellCol); + + if(match_type instanceof ErrorEval) { + throw new EvaluationException((ErrorEval)match_type); + } + if(match_type instanceof NumericValueEval) { + NumericValueEval ne = (NumericValueEval) match_type; + return ne.getNumberValue(); + } + if (match_type instanceof StringEval) { + StringEval se = (StringEval) match_type; + Double d = OperandResolver.parseDouble(se.getStringValue()); + if(d == null) { + // plain string + throw new EvaluationException(ErrorEval.VALUE_INVALID); + } + // if the string parses as a number, it is OK + return d.doubleValue(); + } + throw new RuntimeException("Unexpected match_type type (" + match_type.getClass().getName() + ")"); + } + + /** + * @return zero based index + */ + private static int findIndexOfValue(ValueEval lookupValue, ValueVector lookupRange, + boolean matchExact, boolean findLargestLessThanOrEqual) throws EvaluationException { + + LookupValueComparer lookupComparer = createLookupComparer(lookupValue, matchExact); + + int size = lookupRange.getSize(); + if(matchExact) { + for (int i = 0; i < size; i++) { + if(lookupComparer.compareTo(lookupRange.getItem(i)).isEqual()) { + return i; + } + } + throw new EvaluationException(ErrorEval.NA); + } + + if(findLargestLessThanOrEqual) { + // Note - backward iteration + for (int i = size - 1; i>=0; i--) { + CompareResult cmp = lookupComparer.compareTo(lookupRange.getItem(i)); + if(cmp.isTypeMismatch()) { + continue; + } + if(!cmp.isLessThan()) { + return i; + } + } + throw new EvaluationException(ErrorEval.NA); + } + + // else - find smallest greater than or equal to + // TODO - is binary search used for (match_type==+1) ? + for (int i = 0; i=0 || stringValue.indexOf('*') >=0) { + return true; + } + return false; + } +} diff --git a/src/java/org/apache/poi/ss/formula/functions/MathX.java b/src/java/org/apache/poi/ss/formula/functions/MathX.java new file mode 100644 index 0000000000..4914ad5060 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/functions/MathX.java @@ -0,0 +1,444 @@ +/* ==================================================================== + 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.functions; + + +/** + * @author Amol S. Deshmukh < amolweb at ya hoo dot com > + * This class is an extension to the standard math library + * provided by java.lang.Math class. It follows the Math class + * in that it has a private constructor and all static methods. + */ +final class MathX { + + private MathX() { + // no instances of this class + } + + + /** + * Returns a value rounded to p digits after decimal. + * If p is negative, then the number is rounded to + * places to the left of the decimal point. eg. + * 10.23 rounded to -1 will give: 10. If p is zero, + * the returned value is rounded to the nearest integral + * value. + *

    If n is negative, the resulting value is obtained + * as the round value of absolute value of n multiplied + * by the sign value of n (@see MathX.sign(double d)). + * Thus, -0.6666666 rounded to p=0 will give -1 not 0. + *

    If n is NaN, returned value is NaN. + * @param n + * @param p + */ + public static double round(double n, int p) { + double retval; + + if (Double.isNaN(n) || Double.isInfinite(n)) { + retval = Double.NaN; + } + else { + if (p != 0) { + double temp = Math.pow(10, p); + retval = Math.round(n*temp)/temp; + } + else { + retval = Math.round(n); + } + } + + return retval; + } + + /** + * Returns a value rounded-up to p digits after decimal. + * If p is negative, then the number is rounded to + * places to the left of the decimal point. eg. + * 10.23 rounded to -1 will give: 20. If p is zero, + * the returned value is rounded to the nearest integral + * value. + *

    If n is negative, the resulting value is obtained + * as the round-up value of absolute value of n multiplied + * by the sign value of n (@see MathX.sign(double d)). + * Thus, -0.2 rounded-up to p=0 will give -1 not 0. + *

    If n is NaN, returned value is NaN. + * @param n + * @param p + */ + public static double roundUp(double n, int p) { + double retval; + + if (Double.isNaN(n) || Double.isInfinite(n)) { + retval = Double.NaN; + } + else { + if (p != 0) { + double temp = Math.pow(10, p); + double nat = Math.abs(n*temp); + + retval = sign(n) * + ((nat == (long) nat) + ? nat / temp + : Math.round(nat + 0.5) / temp); + } + else { + double na = Math.abs(n); + retval = sign(n) * + ((na == (long) na) + ? na + : (long) na + 1); + } + } + + return retval; + } + + /** + * Returns a value rounded to p digits after decimal. + * If p is negative, then the number is rounded to + * places to the left of the decimal point. eg. + * 10.23 rounded to -1 will give: 10. If p is zero, + * the returned value is rounded to the nearest integral + * value. + *

    If n is negative, the resulting value is obtained + * as the round-up value of absolute value of n multiplied + * by the sign value of n (@see MathX.sign(double d)). + * Thus, -0.8 rounded-down to p=0 will give 0 not -1. + *

    If n is NaN, returned value is NaN. + * @param n + * @param p + */ + public static double roundDown(double n, int p) { + double retval; + + if (Double.isNaN(n) || Double.isInfinite(n)) { + retval = Double.NaN; + } + else { + if (p != 0) { + double temp = Math.pow(10, p); + retval = sign(n) * Math.round((Math.abs(n)*temp) - 0.5)/temp; + } + else { + retval = (long) n; + } + } + + return retval; + } + + + /** + * If d < 0, returns short -1 + *
    + * If d > 0, returns short 1 + *
    + * If d == 0, returns short 0 + *

    If d is NaN, then 1 will be returned. It is the responsibility + * of caller to check for d isNaN if some other value is desired. + * @param d + */ + public static short sign(double d) { + return (short) ((d == 0) + ? 0 + : (d < 0) + ? -1 + : 1); + } + + /** + * average of all values + * @param values + */ + public static double average(double[] values) { + double ave = 0; + double sum = 0; + for (int i=0, iSize=values.length; i 0) { + product = 1; + for (int i=0, iSize=values.length; i + * When n and s are "valid" arguments, the returned value is: Math.floor(n/s) * s; + *
    + * n and s are invalid if any of following conditions are true: + *

      + *
    • s is zero
      • + *
    • n is negative and s is positive
      • + *
    • n is positive and s is negative
      • + *
    + * In all such cases, Double.NaN is returned. + * @param n + * @param s + */ + public static double floor(double n, double s) { + double f; + + if ((n<0 && s>0) || (n>0 && s<0) || (s==0 && n!=0)) { + f = Double.NaN; + } + else { + f = (n==0 || s==0) ? 0 : Math.floor(n/s) * s; + } + + return f; + } + + /** + * Note: this function is different from java.lang.Math.ceil(..). + *

    + * When n and s are "valid" arguments, the returned value is: Math.ceiling(n/s) * s; + *
    + * n and s are invalid if any of following conditions are true: + *

      + *
    • s is zero
      • + *
    • n is negative and s is positive
      • + *
    • n is positive and s is negative
      • + *
    + * In all such cases, Double.NaN is returned. + * @param n + * @param s + */ + public static double ceiling(double n, double s) { + double c; + + if ((n<0 && s>0) || (n>0 && s<0)) { + c = Double.NaN; + } + else { + c = (n == 0 || s == 0) ? 0 : Math.ceil(n/s) * s; + } + + return c; + } + + /** + *
    for all n >= 1; factorial n = n * (n-1) * (n-2) * ... * 1 + *
    else if n == 0; factorial n = 1 + *
    else if n < 0; factorial n = Double.NaN + *
    Loss of precision can occur if n is large enough. + * If n is large so that the resulting value would be greater + * than Double.MAX_VALUE; Double.POSITIVE_INFINITY is returned. + * If n < 0, Double.NaN is returned. + * @param n + */ + public static double factorial(int n) { + double d = 1; + + if (n >= 0) { + if (n <= 170) { + for (int i=1; i<=n; i++) { + d *= i; + } + } + else { + d = Double.POSITIVE_INFINITY; + } + } + else { + d = Double.NaN; + } + return d; + } + + + /** + * returns the remainder resulting from operation: + * n / d. + *
    The result has the sign of the divisor. + *
    Examples: + *
      + *
    • mod(3.4, 2) = 1.4
      • + *
    • mod(-3.4, 2) = 0.6
      • + *
    • mod(-3.4, -2) = -1.4
      • + *
    • mod(3.4, -2) = -0.6
      • + *
    + * If d == 0, result is NaN + * @param n + * @param d + */ + public static double mod(double n, double d) { + double result = 0; + + if (d == 0) { + result = Double.NaN; + } + else if (sign(n) == sign(d)) { + result = n % d; + } + else { + result = ((n % d) + d) % d; + } + + return result; + } + + /** + * inverse hyperbolic cosine + * @param d + */ + public static double acosh(double d) { + return Math.log(Math.sqrt(Math.pow(d, 2) - 1) + d); + } + + /** + * inverse hyperbolic sine + * @param d + */ + public static double asinh(double d) { + return Math.log(Math.sqrt(d*d + 1) + d); + } + + /** + * inverse hyperbolic tangent + * @param d + */ + public static double atanh(double d) { + return Math.log((1 + d)/(1 - d)) / 2; + } + + /** + * hyperbolic cosine + * @param d + */ + public static double cosh(double d) { + double ePowX = Math.pow(Math.E, d); + double ePowNegX = Math.pow(Math.E, -d); + return (ePowX + ePowNegX) / 2; + } + + /** + * hyperbolic sine + * @param d + */ + public static double sinh(double d) { + double ePowX = Math.pow(Math.E, d); + double ePowNegX = Math.pow(Math.E, -d); + return (ePowX - ePowNegX) / 2; + } + + /** + * hyperbolic tangent + * @param d + */ + public static double tanh(double d) { + double ePowX = Math.pow(Math.E, d); + double ePowNegX = Math.pow(Math.E, -d); + return (ePowX - ePowNegX) / (ePowX + ePowNegX); + } + + + /** + * returns the total number of combinations possible when + * k items are chosen out of total of n items. If the number + * is too large, loss of precision may occur (since returned + * value is double). If the returned value is larger than + * Double.MAX_VALUE, Double.POSITIVE_INFINITY is returned. + * If either of the parameters is negative, Double.NaN is returned. + * @param n + * @param k + */ + public static double nChooseK(int n, int k) { + double d = 1; + if (n<0 || k<0 || n 0 ? MathX.max(values) : 0; + } + }; + public static final Function MINA = new MinaMaxa() { + protected double evaluate(double[] values) { + return values.length > 0 ? MathX.min(values) : 0; + } + }; +} diff --git a/src/java/org/apache/poi/ss/formula/functions/Mode.java b/src/java/org/apache/poi/ss/formula/functions/Mode.java new file mode 100644 index 0000000000..2816d7823d --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/functions/Mode.java @@ -0,0 +1,133 @@ +/* ==================================================================== + 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.functions; + +import java.util.ArrayList; +import java.util.Arrays; +import java.util.List; + +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.EvaluationException; +import org.apache.poi.ss.formula.eval.NumberEval; +import org.apache.poi.ss.formula.eval.RefEval; +import org.apache.poi.ss.formula.eval.StringEval; +import org.apache.poi.ss.formula.eval.ValueEval; +import org.apache.poi.ss.formula.TwoDEval; + +/** + * @author Amol S. Deshmukh < amolweb at ya hoo dot com > + * + */ +public final class Mode implements Function { + + /** + * if v is zero length or contains no duplicates, return value is + * Double.NaN. Else returns the value that occurs most times and if there is + * a tie, returns the first such value. + * + * @param v + */ + public static double evaluate(double[] v) throws EvaluationException { + if (v.length < 2) { + throw new EvaluationException(ErrorEval.NA); + } + + // very naive impl, may need to be optimized + int[] counts = new int[v.length]; + Arrays.fill(counts, 1); + for (int i = 0, iSize = v.length; i < iSize; i++) { + for (int j = i + 1, jSize = v.length; j < jSize; j++) { + if (v[i] == v[j]) + counts[i]++; + } + } + double maxv = 0; + int maxc = 0; + for (int i = 0, iSize = counts.length; i < iSize; i++) { + if (counts[i] > maxc) { + maxv = v[i]; + maxc = counts[i]; + } + } + if (maxc > 1) { + return maxv; + } + throw new EvaluationException(ErrorEval.NA); + + } + + public ValueEval evaluate(ValueEval[] args, int srcCellRow, int srcCellCol) { + double result; + try { + List temp = new ArrayList(); + for (int i = 0; i < args.length; i++) { + collectValues(args[i], temp); + } + double[] values = new double[temp.size()]; + for (int i = 0; i < values.length; i++) { + values[i] = temp.get(i).doubleValue(); + } + result = evaluate(values); + } catch (EvaluationException e) { + return e.getErrorEval(); + } + return new NumberEval(result); + } + + private static void collectValues(ValueEval arg, List temp) throws EvaluationException { + if (arg instanceof TwoDEval) { + TwoDEval ae = (TwoDEval) arg; + int width = ae.getWidth(); + int height = ae.getHeight(); + for (int rrIx = 0; rrIx < height; rrIx++) { + for (int rcIx = 0; rcIx < width; rcIx++) { + ValueEval ve1 = ae.getValue(rrIx, rcIx); + collectValue(ve1, temp, false); + } + } + return; + } + if (arg instanceof RefEval) { + RefEval re = (RefEval) arg; + collectValue(re.getInnerValueEval(), temp, true); + return; + } + collectValue(arg, temp, true); + + } + + private static void collectValue(ValueEval arg, List temp, boolean mustBeNumber) + throws EvaluationException { + if (arg instanceof ErrorEval) { + throw new EvaluationException((ErrorEval) arg); + } + if (arg == BlankEval.instance || arg instanceof BoolEval || arg instanceof StringEval) { + if (mustBeNumber) { + throw EvaluationException.invalidValue(); + } + return; + } + if (arg instanceof NumberEval) { + temp.add(new Double(((NumberEval) arg).getNumberValue())); + return; + } + throw new RuntimeException("Unexpected value type (" + arg.getClass().getName() + ")"); + } +} diff --git a/src/java/org/apache/poi/ss/formula/functions/MultiOperandNumericFunction.java b/src/java/org/apache/poi/ss/formula/functions/MultiOperandNumericFunction.java new file mode 100644 index 0000000000..f75bab29f1 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/functions/MultiOperandNumericFunction.java @@ -0,0 +1,197 @@ +/* ==================================================================== + 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.functions; + +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.EvaluationException; +import org.apache.poi.ss.formula.eval.NumberEval; +import org.apache.poi.ss.formula.eval.OperandResolver; +import org.apache.poi.ss.formula.eval.RefEval; +import org.apache.poi.ss.formula.eval.StringEval; +import org.apache.poi.ss.formula.eval.ValueEval; +import org.apache.poi.ss.formula.TwoDEval; + +/** + * @author Amol S. Deshmukh < amolweb at ya hoo dot com > + * This is the super class for all excel function evaluator + * classes that take variable number of operands, and + * where the order of operands does not matter + */ +public abstract class MultiOperandNumericFunction implements Function { + + private final boolean _isReferenceBoolCounted; + private final boolean _isBlankCounted; + + protected MultiOperandNumericFunction(boolean isReferenceBoolCounted, boolean isBlankCounted) { + _isReferenceBoolCounted = isReferenceBoolCounted; + _isBlankCounted = isBlankCounted; + } + + static final double[] EMPTY_DOUBLE_ARRAY = { }; + + private static class DoubleList { + private double[] _array; + private int _count; + + public DoubleList() { + _array = new double[8]; + _count = 0; + } + + public double[] toArray() { + if(_count < 1) { + return EMPTY_DOUBLE_ARRAY; + } + double[] result = new double[_count]; + System.arraycopy(_array, 0, result, 0, _count); + return result; + } + + private void ensureCapacity(int reqSize) { + if(reqSize > _array.length) { + int newSize = reqSize * 3 / 2; // grow with 50% extra + double[] newArr = new double[newSize]; + System.arraycopy(_array, 0, newArr, 0, _count); + _array = newArr; + } + } + + public void add(double value) { + ensureCapacity(_count + 1); + _array[_count] = value; + _count++; + } + } + + private static final int DEFAULT_MAX_NUM_OPERANDS = 30; + + public final ValueEval evaluate(ValueEval[] args, int srcCellRow, int srcCellCol) { + + double d; + try { + double[] values = getNumberArray(args); + d = evaluate(values); + } catch (EvaluationException e) { + return e.getErrorEval(); + } + + if (Double.isNaN(d) || Double.isInfinite(d)) + return ErrorEval.NUM_ERROR; + + return new NumberEval(d); + } + + protected abstract double evaluate(double[] values) throws EvaluationException; + + /** + * Maximum number of operands accepted by this function. + * Subclasses may override to change default value. + */ + protected int getMaxNumOperands() { + return DEFAULT_MAX_NUM_OPERANDS; + } + + /** + * Returns a double array that contains values for the numeric cells + * from among the list of operands. Blanks and Blank equivalent cells + * are ignored. Error operands or cells containing operands of type + * that are considered invalid and would result in #VALUE! error in + * excel cause this function to return null. + * + * @return never null + */ + protected final double[] getNumberArray(ValueEval[] operands) throws EvaluationException { + if (operands.length > getMaxNumOperands()) { + throw EvaluationException.invalidValue(); + } + DoubleList retval = new DoubleList(); + + for (int i=0, iSize=operands.length; iresult is NaN or Infinity + */ + static final void checkValue(double result) throws EvaluationException { + if (Double.isNaN(result) || Double.isInfinite(result)) { + throw new EvaluationException(ErrorEval.NUM_ERROR); + } + } + + public final ValueEval evaluate(ValueEval[] args, int srcCellRow, int srcCellCol) { + double result; + try { + result = eval(args, srcCellRow, srcCellCol); + checkValue(result); + } catch (EvaluationException e) { + return e.getErrorEval(); + } + return new NumberEval(result); + } + + protected abstract double eval(ValueEval[] args, int srcCellRow, int srcCellCol) throws EvaluationException; + + /* -------------------------------------------------------------------------- */ + // intermediate sub-classes (one-arg, two-arg and multi-arg) + + public static abstract class OneArg extends Fixed1ArgFunction { + protected OneArg() { + // no fields to initialise + } + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0) { + double result; + try { + double d = singleOperandEvaluate(arg0, srcRowIndex, srcColumnIndex); + result = evaluate(d); + checkValue(result); + } catch (EvaluationException e) { + return e.getErrorEval(); + } + return new NumberEval(result); + } + protected final double eval(ValueEval[] args, int srcCellRow, int srcCellCol) throws EvaluationException { + if (args.length != 1) { + throw new EvaluationException(ErrorEval.VALUE_INVALID); + } + double d = singleOperandEvaluate(args[0], srcCellRow, srcCellCol); + return evaluate(d); + } + protected abstract double evaluate(double d) throws EvaluationException; + } + + public static abstract class TwoArg extends Fixed2ArgFunction { + protected TwoArg() { + // no fields to initialise + } + + + 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); + checkValue(result); + } catch (EvaluationException e) { + return e.getErrorEval(); + } + return new NumberEval(result); + } + + protected abstract double evaluate(double d0, double d1) throws EvaluationException; + } + + /* -------------------------------------------------------------------------- */ + + public static final Function ABS = new OneArg() { + protected double evaluate(double d) { + return Math.abs(d); + } + }; + public static final Function ACOS = new OneArg() { + protected double evaluate(double d) { + return Math.acos(d); + } + }; + public static final Function ACOSH = new OneArg() { + protected double evaluate(double d) { + return MathX.acosh(d); + } + }; + public static final Function ASIN = new OneArg() { + protected double evaluate(double d) { + return Math.asin(d); + } + }; + public static final Function ASINH = new OneArg() { + protected double evaluate(double d) { + return MathX.asinh(d); + } + }; + public static final Function ATAN = new OneArg() { + protected double evaluate(double d) { + return Math.atan(d); + } + }; + public static final Function ATANH = new OneArg() { + protected double evaluate(double d) { + return MathX.atanh(d); + } + }; + public static final Function COS = new OneArg() { + protected double evaluate(double d) { + return Math.cos(d); + } + }; + public static final Function COSH = new OneArg() { + protected double evaluate(double d) { + return MathX.cosh(d); + } + }; + public static final Function DEGREES = new OneArg() { + protected double evaluate(double d) { + return Math.toDegrees(d); + } + }; + static final NumberEval DOLLAR_ARG2_DEFAULT = new NumberEval(2.0); + public static final Function DOLLAR = new Var1or2ArgFunction() { + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0) { + return evaluate(srcRowIndex, srcColumnIndex, arg0, DOLLAR_ARG2_DEFAULT); + } + + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, + ValueEval arg1) { + double val; + double d1; + try { + val = singleOperandEvaluate(arg0, srcRowIndex, srcColumnIndex); + d1 = singleOperandEvaluate(arg1, srcRowIndex, srcColumnIndex); + } catch (EvaluationException e) { + return e.getErrorEval(); + } + // second arg converts to int by truncating toward zero + int nPlaces = (int)d1; + + if (nPlaces > 127) { + return ErrorEval.VALUE_INVALID; + } + + + // TODO - DOLLAR() function impl is NQR + // result should be StringEval, with leading '$' and thousands separators + // current junits are asserting incorrect behaviour + return new NumberEval(val); + } + }; + public static final Function EXP = new OneArg() { + protected double evaluate(double d) { + return Math.pow(Math.E, d); + } + }; + public static final Function FACT = new OneArg() { + protected double evaluate(double d) { + return MathX.factorial((int)d); + } + }; + public static final Function INT = new OneArg() { + protected double evaluate(double d) { + return Math.round(d-0.5); + } + }; + public static final Function LN = new OneArg() { + protected double evaluate(double d) { + return Math.log(d); + } + }; + public static final Function LOG10 = new OneArg() { + protected double evaluate(double d) { + return Math.log(d) / LOG_10_TO_BASE_e; + } + }; + public static final Function RADIANS = new OneArg() { + protected double evaluate(double d) { + return Math.toRadians(d); + } + }; + public static final Function SIGN = new OneArg() { + protected double evaluate(double d) { + return MathX.sign(d); + } + }; + public static final Function SIN = new OneArg() { + protected double evaluate(double d) { + return Math.sin(d); + } + }; + public static final Function SINH = new OneArg() { + protected double evaluate(double d) { + return MathX.sinh(d); + } + }; + public static final Function SQRT = new OneArg() { + protected double evaluate(double d) { + return Math.sqrt(d); + } + }; + + public static final Function TAN = new OneArg() { + protected double evaluate(double d) { + return Math.tan(d); + } + }; + public static final Function TANH = new OneArg() { + protected double evaluate(double d) { + return MathX.tanh(d); + } + }; + + /* -------------------------------------------------------------------------- */ + + public static final Function ATAN2 = new TwoArg() { + protected double evaluate(double d0, double d1) throws EvaluationException { + if (d0 == ZERO && d1 == ZERO) { + throw new EvaluationException(ErrorEval.DIV_ZERO); + } + return Math.atan2(d1, d0); + } + }; + public static final Function CEILING = new TwoArg() { + protected double evaluate(double d0, double d1) { + return MathX.ceiling(d0, d1); + } + }; + public static final Function COMBIN = new TwoArg() { + protected double evaluate(double d0, double d1) throws EvaluationException { + if (d0 > Integer.MAX_VALUE || d1 > Integer.MAX_VALUE) { + throw new EvaluationException(ErrorEval.NUM_ERROR); + } + return MathX.nChooseK((int) d0, (int) d1); + } + }; + public static final Function FLOOR = new TwoArg() { + protected double evaluate(double d0, double d1) throws EvaluationException { + if (d1 == ZERO) { + if (d0 == ZERO) { + return ZERO; + } + throw new EvaluationException(ErrorEval.DIV_ZERO); + } + return MathX.floor(d0, d1); + } + }; + public static final Function MOD = new TwoArg() { + protected double evaluate(double d0, double d1) throws EvaluationException { + if (d1 == ZERO) { + throw new EvaluationException(ErrorEval.DIV_ZERO); + } + return MathX.mod(d0, d1); + } + }; + public static final Function POWER = new TwoArg() { + protected double evaluate(double d0, double d1) { + return Math.pow(d0, d1); + } + }; + public static final Function ROUND = new TwoArg() { + protected double evaluate(double d0, double d1) { + return MathX.round(d0, (int)d1); + } + }; + public static final Function ROUNDDOWN = new TwoArg() { + protected double evaluate(double d0, double d1) { + return MathX.roundDown(d0, (int)d1); + } + }; + public static final Function ROUNDUP = new TwoArg() { + protected double evaluate(double d0, double d1) { + return MathX.roundUp(d0, (int)d1); + } + }; + static final NumberEval TRUNC_ARG2_DEFAULT = new NumberEval(0); + public static final Function TRUNC = new Var1or2ArgFunction() { + + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0) { + return evaluate(srcRowIndex, srcColumnIndex, arg0, TRUNC_ARG2_DEFAULT); + } + + 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); + double multi = Math.pow(10d,d1); + result = Math.floor(d0 * multi) / multi; + checkValue(result); + }catch (EvaluationException e) { + return e.getErrorEval(); + } + return new NumberEval(result); + } + }; + + /* -------------------------------------------------------------------------- */ + + private static final class Log extends Var1or2ArgFunction { + public Log() { + // no instance fields + } + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0) { + double result; + try { + double d0 = NumericFunction.singleOperandEvaluate(arg0, srcRowIndex, srcColumnIndex); + result = Math.log(d0) / LOG_10_TO_BASE_e; + NumericFunction.checkValue(result); + } catch (EvaluationException e) { + return e.getErrorEval(); + } + return new NumberEval(result); + } + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, + ValueEval arg1) { + double result; + try { + double d0 = NumericFunction.singleOperandEvaluate(arg0, srcRowIndex, srcColumnIndex); + double d1 = NumericFunction.singleOperandEvaluate(arg1, srcRowIndex, srcColumnIndex); + double logE = Math.log(d0); + double base = d1; + if (base == Math.E) { + result = logE; + } else { + result = logE / Math.log(base); + } + NumericFunction.checkValue(result); + } catch (EvaluationException e) { + return e.getErrorEval(); + } + return new NumberEval(result); + } + } + + public static final Function LOG = new Log(); + + static final NumberEval PI_EVAL = new NumberEval(Math.PI); + public static final Function PI = new Fixed0ArgFunction() { + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex) { + return PI_EVAL; + } + }; + public static final Function RAND = new Fixed0ArgFunction() { + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex) { + return new NumberEval(Math.random()); + } + }; + public static final Function POISSON = new Fixed3ArgFunction() { + + private final static double DEFAULT_RETURN_RESULT =1; + + /** + * This checks is x = 0 and the mean = 0. + * Excel currently returns the value 1 where as the + * maths common implementation will error. + * @param x The number. + * @param mean The mean. + * @return If a default value should be returned. + */ + private boolean isDefaultResult(double x, double mean) { + + if ( x == 0 && mean == 0 ) { + return true; + } + return false; + } + + private boolean checkArgument(double aDouble) throws EvaluationException { + + NumericFunction.checkValue(aDouble); + + // make sure that the number is positive + if (aDouble < 0) { + throw new EvaluationException(ErrorEval.NUM_ERROR); + } + + return true; + } + + private double probability(int k, double lambda) { + return Math.pow(lambda, k) * Math.exp(-lambda) / factorial(k); + } + + private double cumulativeProbability(int x, double lambda) { + double result = 0; + for(int k = 0; k <= x; k++){ + result += probability(k, lambda); + } + return result; + } + + /** All long-representable factorials */ + private final long[] FACTORIALS = new long[] { + 1l, 1l, 2l, + 6l, 24l, 120l, + 720l, 5040l, 40320l, + 362880l, 3628800l, 39916800l, + 479001600l, 6227020800l, 87178291200l, + 1307674368000l, 20922789888000l, 355687428096000l, + 6402373705728000l, 121645100408832000l, 2432902008176640000l }; + + + public long factorial(final int n) { + if (n < 0 || n > 20) { + throw new IllegalArgumentException("Valid argument should be in the range [0..20]"); + } + return FACTORIALS[n]; + } + + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1, ValueEval arg2) { + + // arguments/result for this function + double mean=0; + double x=0; + boolean cumulative = ((BoolEval)arg2).getBooleanValue(); + double result=0; + + try { + x = NumericFunction.singleOperandEvaluate(arg0, srcRowIndex, srcColumnIndex); + mean = NumericFunction.singleOperandEvaluate(arg1, srcRowIndex, srcColumnIndex); + + // check for default result : excel implementation for 0,0 + // is different to Math Common. + if (isDefaultResult(x,mean)) { + return new NumberEval(DEFAULT_RETURN_RESULT); + } + // check the arguments : as per excel function def + checkArgument(x); + checkArgument(mean); + + // truncate x : as per excel function def + if ( cumulative ) { + result = cumulativeProbability((int)x, mean); + } else { + result = probability((int)x, mean); + } + + // check the result + NumericFunction.checkValue(result); + + } catch (EvaluationException e) { + return e.getErrorEval(); + } + + return new NumberEval(result); + + } + }; +} diff --git a/src/java/org/apache/poi/ss/formula/functions/Odd.java b/src/java/org/apache/poi/ss/formula/functions/Odd.java new file mode 100644 index 0000000000..e2e93d92f8 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/functions/Odd.java @@ -0,0 +1,45 @@ +/* ==================================================================== + 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.functions; + +/** + * @author Amol S. Deshmukh < amolweb at ya hoo dot com > + * + */ +public final class Odd extends NumericFunction.OneArg { + private static final long PARITY_MASK = 0xFFFFFFFFFFFFFFFEL; + + protected double evaluate(double d) { + if (d==0) { + return 1; + } + if (d>0) { + return calcOdd(d); + } + return -calcOdd(-d); + } + + private static long calcOdd(double d) { + double dpm1 = d+1; + long x = ((long) dpm1) & PARITY_MASK; + if (x == dpm1) { + return x-1; + } + return x + 1; + } +} diff --git a/src/java/org/apache/poi/ss/formula/functions/Offset.java b/src/java/org/apache/poi/ss/formula/functions/Offset.java new file mode 100644 index 0000000000..d9f240d67e --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/functions/Offset.java @@ -0,0 +1,227 @@ +/* ==================================================================== + 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.functions; + +import org.apache.poi.ss.formula.eval.AreaEval; +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.RefEval; +import org.apache.poi.ss.formula.eval.ValueEval; +/** + * Implementation for Excel function OFFSET()

    + * + * OFFSET returns an area reference that is a specified number of rows and columns from a + * reference cell or area.

    + * + * Syntax:
    + * OFFSET(reference, rows, cols, height, width)

    + * reference is the base reference.
    + * rows is the number of rows up or down from the base reference.
    + * cols is the number of columns left or right from the base reference.
    + * height (default same height as base reference) is the row count for the returned area reference.
    + * width (default same width as base reference) is the column count for the returned area reference.
    + * + * @author Josh Micich + */ +public final class Offset implements Function { + // These values are specific to BIFF8 + private static final int LAST_VALID_ROW_INDEX = 0xFFFF; + private static final int LAST_VALID_COLUMN_INDEX = 0xFF; + + + /** + * A one dimensional base + offset. Represents either a row range or a column range. + * Two instances of this class together specify an area range. + */ + /* package */ static final class LinearOffsetRange { + + private final int _offset; + private final int _length; + + public LinearOffsetRange(int offset, int length) { + if(length == 0) { + // handled that condition much earlier + throw new RuntimeException("length may not be zero"); + } + _offset = offset; + _length = length; + } + + public short getFirstIndex() { + return (short) _offset; + } + public short getLastIndex() { + return (short) (_offset + _length - 1); + } + /** + * Moves the range by the specified translation amount.

    + * + * This method also 'normalises' the range: Excel specifies that the width and height + * parameters (length field here) cannot be negative. However, OFFSET() does produce + * sensible results in these cases. That behavior is replicated here.

    + * + * @param translationAmount may be zero negative or positive + * + * @return the equivalent LinearOffsetRange with a positive length, moved by the + * specified translationAmount. + */ + public LinearOffsetRange normaliseAndTranslate(int translationAmount) { + if (_length > 0) { + if(translationAmount == 0) { + return this; + } + return new LinearOffsetRange(translationAmount + _offset, _length); + } + return new LinearOffsetRange(translationAmount + _offset + _length + 1, -_length); + } + + public boolean isOutOfBounds(int lowValidIx, int highValidIx) { + if(_offset < lowValidIx) { + return true; + } + if(getLastIndex() > highValidIx) { + return true; + } + return false; + } + public String toString() { + StringBuffer sb = new StringBuffer(64); + sb.append(getClass().getName()).append(" ["); + sb.append(_offset).append("...").append(getLastIndex()); + sb.append("]"); + return sb.toString(); + } + } + + /** + * Encapsulates either an area or cell reference which may be 2d or 3d. + */ + private static final class BaseRef { + private final int _firstRowIndex; + private final int _firstColumnIndex; + private final int _width; + private final int _height; + private final RefEval _refEval; + private final AreaEval _areaEval; + + public BaseRef(RefEval re) { + _refEval = re; + _areaEval = null; + _firstRowIndex = re.getRow(); + _firstColumnIndex = re.getColumn(); + _height = 1; + _width = 1; + } + + public BaseRef(AreaEval ae) { + _refEval = null; + _areaEval = ae; + _firstRowIndex = ae.getFirstRow(); + _firstColumnIndex = ae.getFirstColumn(); + _height = ae.getLastRow() - ae.getFirstRow() + 1; + _width = ae.getLastColumn() - ae.getFirstColumn() + 1; + } + + public int getWidth() { + return _width; + } + public int getHeight() { + return _height; + } + public int getFirstRowIndex() { + return _firstRowIndex; + } + public int getFirstColumnIndex() { + return _firstColumnIndex; + } + + public AreaEval offset(int relFirstRowIx, int relLastRowIx, + int relFirstColIx, int relLastColIx) { + if (_refEval == null) { + return _areaEval.offset(relFirstRowIx, relLastRowIx, relFirstColIx, relLastColIx); + } + return _refEval.offset(relFirstRowIx, relLastRowIx, relFirstColIx, relLastColIx); + } + } + + public ValueEval evaluate(ValueEval[] args, int srcCellRow, int srcCellCol) { + if(args.length < 3 || args.length > 5) { + return ErrorEval.VALUE_INVALID; + } + + try { + BaseRef baseRef = evaluateBaseRef(args[0]); + int rowOffset = evaluateIntArg(args[1], srcCellRow, srcCellCol); + int columnOffset = evaluateIntArg(args[2], srcCellRow, srcCellCol); + int height = baseRef.getHeight(); + int width = baseRef.getWidth(); + switch(args.length) { + case 5: + width = evaluateIntArg(args[4], srcCellRow, srcCellCol); + case 4: + height = evaluateIntArg(args[3], srcCellRow, srcCellCol); + } + // Zero height or width raises #REF! error + if(height == 0 || width == 0) { + return ErrorEval.REF_INVALID; + } + LinearOffsetRange rowOffsetRange = new LinearOffsetRange(rowOffset, height); + LinearOffsetRange colOffsetRange = new LinearOffsetRange(columnOffset, width); + return createOffset(baseRef, rowOffsetRange, colOffsetRange); + } catch (EvaluationException e) { + return e.getErrorEval(); + } + } + + private static AreaEval createOffset(BaseRef baseRef, + LinearOffsetRange orRow, LinearOffsetRange orCol) throws EvaluationException { + LinearOffsetRange absRows = orRow.normaliseAndTranslate(baseRef.getFirstRowIndex()); + LinearOffsetRange absCols = orCol.normaliseAndTranslate(baseRef.getFirstColumnIndex()); + + if(absRows.isOutOfBounds(0, LAST_VALID_ROW_INDEX)) { + throw new EvaluationException(ErrorEval.REF_INVALID); + } + if(absCols.isOutOfBounds(0, LAST_VALID_COLUMN_INDEX)) { + throw new EvaluationException(ErrorEval.REF_INVALID); + } + return baseRef.offset(orRow.getFirstIndex(), orRow.getLastIndex(), orCol.getFirstIndex(), orCol.getLastIndex()); + } + + private static BaseRef evaluateBaseRef(ValueEval eval) throws EvaluationException { + + if(eval instanceof RefEval) { + return new BaseRef((RefEval)eval); + } + if(eval instanceof AreaEval) { + return new BaseRef((AreaEval)eval); + } + if (eval instanceof ErrorEval) { + throw new EvaluationException((ErrorEval) eval); + } + throw new EvaluationException(ErrorEval.VALUE_INVALID); + } + + /** + * OFFSET's numeric arguments (2..5) have similar processing rules + */ + static int evaluateIntArg(ValueEval eval, int srcCellRow, int srcCellCol) throws EvaluationException { + ValueEval ve = OperandResolver.getSingleValue(eval, srcCellRow, srcCellCol); + return OperandResolver.coerceValueToInt(ve); + } +} diff --git a/src/java/org/apache/poi/ss/formula/functions/Replace.java b/src/java/org/apache/poi/ss/formula/functions/Replace.java new file mode 100644 index 0000000000..dd366dbd25 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/functions/Replace.java @@ -0,0 +1,74 @@ +/* ==================================================================== + 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.functions; + +import org.apache.poi.ss.formula.eval.ErrorEval; +import org.apache.poi.ss.formula.eval.EvaluationException; +import org.apache.poi.ss.formula.eval.StringEval; +import org.apache.poi.ss.formula.eval.ValueEval; + +/** + * An implementation of the Excel REPLACE() function:

    + * Replaces part of a text string based on the number of characters + * you specify, with another text string.
    + * + * Syntax:
    + * REPLACE(oldText, startNum, numChars, newText)

    + * + * oldText The text string containing characters to replace
    + * startNum The position of the first character to replace (1-based)
    + * numChars The number of characters to replace
    + * newText The new text value to replace the removed section
    + * + * @author Manda Wilson < wilson at c bio dot msk cc dot org > + */ +public final class Replace extends Fixed4ArgFunction { + + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1, + ValueEval arg2, ValueEval arg3) { + + String oldStr; + int startNum; + int numChars; + String newStr; + try { + oldStr = TextFunction.evaluateStringArg(arg0, srcRowIndex, srcColumnIndex); + startNum = TextFunction.evaluateIntArg(arg1, srcRowIndex, srcColumnIndex); + numChars = TextFunction.evaluateIntArg(arg2, srcRowIndex, srcColumnIndex); + newStr = TextFunction.evaluateStringArg(arg3, srcRowIndex, srcColumnIndex); + } catch (EvaluationException e) { + return e.getErrorEval(); + } + + if (startNum < 1 || numChars < 0) { + return ErrorEval.VALUE_INVALID; + } + StringBuffer strBuff = new StringBuffer(oldStr); + // remove any characters that should be replaced + if (startNum <= oldStr.length() && numChars != 0) { + strBuff.delete(startNum - 1, startNum - 1 + numChars); + } + // now insert (or append) newStr + if (startNum > strBuff.length()) { + strBuff.append(newStr); + } else { + strBuff.insert(startNum - 1, newStr); + } + return new StringEval(strBuff.toString()); + } +} diff --git a/src/java/org/apache/poi/ss/formula/functions/RowFunc.java b/src/java/org/apache/poi/ss/formula/functions/RowFunc.java new file mode 100644 index 0000000000..f0d468fb67 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/functions/RowFunc.java @@ -0,0 +1,59 @@ +/* ==================================================================== + 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.functions; + +import org.apache.poi.ss.formula.eval.AreaEval; +import org.apache.poi.ss.formula.eval.ErrorEval; +import org.apache.poi.ss.formula.eval.NumberEval; +import org.apache.poi.ss.formula.eval.RefEval; +import org.apache.poi.ss.formula.eval.ValueEval; + +/** + * Implementation for the Excel function ROW + * + * @author Josh Micich + */ +public final class RowFunc implements Function0Arg, Function1Arg { + + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex) { + return new NumberEval(srcRowIndex+1); + } + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0) { + int rnum; + + if (arg0 instanceof AreaEval) { + rnum = ((AreaEval) arg0).getFirstRow(); + } else if (arg0 instanceof RefEval) { + rnum = ((RefEval) arg0).getRow(); + } else { + // anything else is not valid argument + return ErrorEval.VALUE_INVALID; + } + + return new NumberEval(rnum + 1); + } + public ValueEval evaluate(ValueEval[] args, int srcRowIndex, int srcColumnIndex) { + switch (args.length) { + case 1: + return evaluate(srcRowIndex, srcColumnIndex, args[0]); + case 0: + return new NumberEval(srcRowIndex+1); + } + return ErrorEval.VALUE_INVALID; + } +} diff --git a/src/java/org/apache/poi/ss/formula/functions/Rows.java b/src/java/org/apache/poi/ss/formula/functions/Rows.java new file mode 100644 index 0000000000..1e363222ab --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/functions/Rows.java @@ -0,0 +1,45 @@ +/* ==================================================================== + 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.functions; + +import org.apache.poi.ss.formula.eval.ErrorEval; +import org.apache.poi.ss.formula.eval.NumberEval; +import org.apache.poi.ss.formula.eval.RefEval; +import org.apache.poi.ss.formula.eval.ValueEval; +import org.apache.poi.ss.formula.TwoDEval; + +/** + * Implementation for Excel ROWS function. + * + * @author Josh Micich + */ +public final class Rows extends Fixed1ArgFunction { + + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0) { + + int result; + if (arg0 instanceof TwoDEval) { + result = ((TwoDEval) arg0).getHeight(); + } else if (arg0 instanceof RefEval) { + result = 1; + } else { // anything else is not valid argument + return ErrorEval.VALUE_INVALID; + } + return new NumberEval(result); + } +} diff --git a/src/java/org/apache/poi/ss/formula/functions/StatsLib.java b/src/java/org/apache/poi/ss/formula/functions/StatsLib.java new file mode 100644 index 0000000000..645f7e4384 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/functions/StatsLib.java @@ -0,0 +1,137 @@ +/* ==================================================================== + 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.functions; + +import java.util.Arrays; + +/** + * @author Amol S. Deshmukh < amolweb at ya hoo dot com > + * + * Library for common statistics functions + */ +final class StatsLib { + + private StatsLib() { + // no instances of this class + } + + + /** + * returns the mean of deviations from mean. + * @param v + */ + public static double avedev(double[] v) { + double r = 0; + double m = 0; + double s = 0; + for (int i=0, iSize=v.length; i 1) { + r = Math.sqrt( devsq(v) / (v.length - 1) ); + } + return r; + } + + + public static double median(double[] v) { + double r = Double.NaN; + + if (v!=null && v.length >= 1) { + int n = v.length; + Arrays.sort(v); + r = (n % 2 == 0) + ? (v[n / 2] + v[n / 2 - 1]) / 2 + : v[n / 2]; + } + + return r; + } + + + public static double devsq(double[] v) { + double r = Double.NaN; + if (v!=null && v.length >= 1) { + double m = 0; + double s = 0; + int n = v.length; + for (int i=0; i + * k <= 0 & k >= v.length and null or empty arrays + * will result in return value Double.NaN + */ + public static double kthLargest(double[] v, int k) { + double r = Double.NaN; + int index = k-1; // since arrays are 0-based + if (v!=null && v.length > index && index >= 0) { + Arrays.sort(v); + r = v[v.length-index-1]; + } + return r; + } + + /** + * returns the kth smallest element in the array. Duplicates + * are considered as distinct values. Hence, eg. + * for array {1,1,2,4,3,3} & k=2, returned value is 1. + *
    + * k <= 0 & k >= v.length or null array or empty array + * will result in return value Double.NaN + * @param v + * @param k + */ + public static double kthSmallest(double[] v, int k) { + double r = Double.NaN; + int index = k-1; // since arrays are 0-based + if (v!=null && v.length > index && index >= 0) { + Arrays.sort(v); + r = v[index]; + } + return r; + } +} diff --git a/src/java/org/apache/poi/ss/formula/functions/Substitute.java b/src/java/org/apache/poi/ss/formula/functions/Substitute.java new file mode 100644 index 0000000000..23954b1f0a --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/functions/Substitute.java @@ -0,0 +1,108 @@ +/* ==================================================================== + 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.functions; + +import org.apache.poi.ss.formula.eval.ErrorEval; +import org.apache.poi.ss.formula.eval.EvaluationException; +import org.apache.poi.ss.formula.eval.StringEval; +import org.apache.poi.ss.formula.eval.ValueEval; + +/** + * An implementation of the SUBSTITUTE function:

    + * Substitutes text in a text string with new text, some number of times. + * @author Manda Wilson < wilson at c bio dot msk cc dot org > + */ +public final class Substitute extends Var3or4ArgFunction { + + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1, + ValueEval arg2) { + String result; + try { + String oldStr = TextFunction.evaluateStringArg(arg0, srcRowIndex, srcColumnIndex); + String searchStr = TextFunction.evaluateStringArg(arg1, srcRowIndex, srcColumnIndex); + String newStr = TextFunction.evaluateStringArg(arg2, srcRowIndex, srcColumnIndex); + + result = replaceAllOccurrences(oldStr, searchStr, newStr); + } catch (EvaluationException e) { + return e.getErrorEval(); + } + return new StringEval(result); + } + + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1, + ValueEval arg2, ValueEval arg3) { + String result; + try { + String oldStr = TextFunction.evaluateStringArg(arg0, srcRowIndex, srcColumnIndex); + String searchStr = TextFunction.evaluateStringArg(arg1, srcRowIndex, srcColumnIndex); + String newStr = TextFunction.evaluateStringArg(arg2, srcRowIndex, srcColumnIndex); + + int instanceNumber = TextFunction.evaluateIntArg(arg3, srcRowIndex, srcColumnIndex); + if (instanceNumber < 1) { + return ErrorEval.VALUE_INVALID; + } + result = replaceOneOccurrence(oldStr, searchStr, newStr, instanceNumber); + } catch (EvaluationException e) { + return e.getErrorEval(); + } + return new StringEval(result); + } + + private static String replaceAllOccurrences(String oldStr, String searchStr, String newStr) { + StringBuffer sb = new StringBuffer(); + int startIndex = 0; + int nextMatch = -1; + while (true) { + nextMatch = oldStr.indexOf(searchStr, startIndex); + if (nextMatch < 0) { + // store everything from end of last match to end of string + sb.append(oldStr.substring(startIndex)); + return sb.toString(); + } + // store everything from end of last match to start of this match + sb.append(oldStr.substring(startIndex, nextMatch)); + sb.append(newStr); + startIndex = nextMatch + searchStr.length(); + } + } + + private static String replaceOneOccurrence(String oldStr, String searchStr, String newStr, int instanceNumber) { + if (searchStr.length() < 1) { + return oldStr; + } + int startIndex = 0; + int nextMatch = -1; + int count=0; + while (true) { + nextMatch = oldStr.indexOf(searchStr, startIndex); + if (nextMatch < 0) { + // not enough occurrences found - leave unchanged + return oldStr; + } + count++; + if (count == instanceNumber) { + StringBuffer sb = new StringBuffer(oldStr.length() + newStr.length()); + sb.append(oldStr.substring(0, nextMatch)); + sb.append(newStr); + sb.append(oldStr.substring(nextMatch + searchStr.length())); + return sb.toString(); + } + startIndex = nextMatch + searchStr.length(); + } + } +} diff --git a/src/java/org/apache/poi/ss/formula/functions/Subtotal.java b/src/java/org/apache/poi/ss/formula/functions/Subtotal.java new file mode 100644 index 0000000000..8bbbfeea78 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/functions/Subtotal.java @@ -0,0 +1,100 @@ +/* ==================================================================== + 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.functions; + +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.eval.NotImplementedException; + +/** + * Implementation for the Excel function SUBTOTAL

    + * + * Syntax :
    + * SUBTOTAL ( functionCode, ref1, ref2 ... )
    + * + * + * + *
    functionCode(1-11) Selects the underlying aggregate function to be used (see table below)
    ref1, ref2 ...Arguments to be passed to the underlying aggregate function

    + *

    + * + * + * + * + * + * + * + * + * + * + * + * + * + * + * + *
    functionCodeAggregate Function
    1AVERAGE
    2COUNT
    3COUNTA
    4MAX
    5MIN
    6PRODUCT
    7STDEV
    8STDEVP *
    9AVERAGE
    10VAR *
    11VARP *
    101-111*

    + * * Not implemented in POI yet. Functions 101-111 are the same as functions 1-11 but with + * the option 'ignore hidden values'. + *

    + * + * @author Paul Tomlin < pault at bulk sms dot com > + */ +public class Subtotal implements Function { + + private static Function findFunction(int functionCode) throws EvaluationException { + switch (functionCode) { + case 1: return AggregateFunction.AVERAGE; + case 2: return new Count(); + case 3: return new Counta(); + case 4: return AggregateFunction.MAX; + case 5: return AggregateFunction.MIN; + case 6: return AggregateFunction.PRODUCT; + case 7: return AggregateFunction.STDEV; + case 8: throw new NotImplementedException("STDEVP"); + case 9: return AggregateFunction.SUM; + case 10: throw new NotImplementedException("VAR"); + case 11: throw new NotImplementedException("VARP"); + } + if (functionCode > 100 && functionCode < 112) { + throw new NotImplementedException("SUBTOTAL - with 'exclude hidden values' option"); + } + throw EvaluationException.invalidValue(); + } + + public ValueEval evaluate(ValueEval[] args, int srcRowIndex, int srcColumnIndex) { + int nInnerArgs = args.length-1; // -1: first arg is used to select from a basic aggregate function + if (nInnerArgs < 1) { + return ErrorEval.VALUE_INVALID; + } + + Function innerFunc; + try { + ValueEval ve = OperandResolver.getSingleValue(args[0], srcRowIndex, srcColumnIndex); + int functionCode = OperandResolver.coerceValueToInt(ve); + innerFunc = findFunction(functionCode); + } catch (EvaluationException e) { + return e.getErrorEval(); + } + + ValueEval[] innerArgs = new ValueEval[nInnerArgs]; + System.arraycopy(args, 1, innerArgs, 0, nInnerArgs); + + return innerFunc.evaluate(innerArgs, srcRowIndex, srcColumnIndex); + } +} diff --git a/src/java/org/apache/poi/ss/formula/functions/Sumif.java b/src/java/org/apache/poi/ss/formula/functions/Sumif.java new file mode 100644 index 0000000000..ef381adb02 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/functions/Sumif.java @@ -0,0 +1,127 @@ +/* ==================================================================== + 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.functions; + +import org.apache.poi.ss.formula.eval.AreaEval; +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.RefEval; +import org.apache.poi.ss.formula.eval.ValueEval; +import org.apache.poi.ss.formula.functions.CountUtils.I_MatchPredicate; + +/** + * Implementation for the Excel function SUMIF

    + * + * Syntax :
    + * SUMIF ( range, criteria, sum_range )
    + * + * + * + * + *
    rangeThe range over which criteria is applied. Also used for addend values when the third parameter is not present
    criteriaThe value or expression used to filter rows from range
    sum_rangeLocates the top-left corner of the corresponding range of addends - values to be added (after being selected by the criteria)

    + *

    + * @author Josh Micich + */ +public final class Sumif extends Var2or3ArgFunction { + + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1) { + + AreaEval aeRange; + try { + aeRange = convertRangeArg(arg0); + } catch (EvaluationException e) { + return e.getErrorEval(); + } + return eval(srcRowIndex, srcColumnIndex, arg1, aeRange, aeRange); + } + + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1, + ValueEval arg2) { + + AreaEval aeRange; + AreaEval aeSum; + try { + aeRange = convertRangeArg(arg0); + aeSum = createSumRange(arg2, aeRange); + } catch (EvaluationException e) { + return e.getErrorEval(); + } + return eval(srcRowIndex, srcColumnIndex, arg1, aeRange, aeSum); + } + + private static ValueEval eval(int srcRowIndex, int srcColumnIndex, ValueEval arg1, AreaEval aeRange, + AreaEval aeSum) { + // TODO - junit to prove last arg must be srcColumnIndex and not srcRowIndex + I_MatchPredicate mp = Countif.createCriteriaPredicate(arg1, srcRowIndex, srcColumnIndex); + double result = sumMatchingCells(aeRange, mp, aeSum); + return new NumberEval(result); + } + + private static double sumMatchingCells(AreaEval aeRange, I_MatchPredicate mp, AreaEval aeSum) { + int height=aeRange.getHeight(); + int width= aeRange.getWidth(); + + double result = 0.0; + for (int r=0; r + * + * Syntax :
    + * SUMPRODUCT ( array1[, array2[, array3[, ...]]]) + * + * + *
    array1, ... arrayN  typically area references, + * possibly cell references or scalar values

    + * + * Let An(i,j) represent the element in the ith row jth column + * of the nth array
    + * Assuming each array has the same dimensions (W, H), the result is defined as:
    + * SUMPRODUCT = Σi: 1..H   + * (  Σj: 1..W   + * (  Πn: 1..N + * An(i,j)  + * )  + * ) + *

    + * @author Josh Micich + */ +public final class Sumproduct implements Function { + + + public ValueEval evaluate(ValueEval[] args, int srcCellRow, int srcCellCol) { + + int maxN = args.length; + + if(maxN < 1) { + return ErrorEval.VALUE_INVALID; + } + ValueEval firstArg = args[0]; + try { + if(firstArg instanceof NumericValueEval) { + return evaluateSingleProduct(args); + } + if(firstArg instanceof RefEval) { + return evaluateSingleProduct(args); + } + if (firstArg instanceof TwoDEval) { + TwoDEval ae = (TwoDEval) firstArg; + if(ae.isRow() && ae.isColumn()) { + return evaluateSingleProduct(args); + } + return evaluateAreaSumProduct(args); + } + } catch (EvaluationException e) { + return e.getErrorEval(); + } + throw new RuntimeException("Invalid arg type for SUMPRODUCT: (" + + firstArg.getClass().getName() + ")"); + } + + private static ValueEval evaluateSingleProduct(ValueEval[] evalArgs) throws EvaluationException { + int maxN = evalArgs.length; + + double term = 1D; + for(int n=0; ndouble value for the specified ValueEval. + * @param isScalarProduct false for SUMPRODUCTs over area refs. + * @throws EvaluationException if ve represents an error value. + *

    + * Note - string values and empty cells are interpreted differently depending on + * isScalarProduct. For scalar products, if any term is blank or a string, the + * error (#VALUE!) is raised. For area (sum)products, if any term is blank or a string, the + * result is zero. + */ + private static double getProductTerm(ValueEval ve, boolean isScalarProduct) throws EvaluationException { + + if(ve instanceof BlankEval || ve == null) { + // TODO - shouldn't BlankEval.INSTANCE be used always instead of null? + // null seems to occur when the blank cell is part of an area ref (but not reliably) + if(isScalarProduct) { + throw new EvaluationException(ErrorEval.VALUE_INVALID); + } + return 0; + } + + if(ve instanceof ErrorEval) { + throw new EvaluationException((ErrorEval)ve); + } + if(ve instanceof StringEval) { + if(isScalarProduct) { + throw new EvaluationException(ErrorEval.VALUE_INVALID); + } + // Note for area SUMPRODUCTs, string values are interpreted as zero + // even if they would parse as valid numeric values + return 0; + } + if(ve instanceof NumericValueEval) { + NumericValueEval nve = (NumericValueEval) ve; + return nve.getNumberValue(); + } + throw new RuntimeException("Unexpected value eval class (" + + ve.getClass().getName() + ")"); + } +} diff --git a/src/java/org/apache/poi/ss/formula/functions/Sumx2my2.java b/src/java/org/apache/poi/ss/formula/functions/Sumx2my2.java new file mode 100644 index 0000000000..9c8c386687 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/functions/Sumx2my2.java @@ -0,0 +1,43 @@ +/* ==================================================================== + 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.functions; + + +/** + * Implementation of Excel function SUMX2MY2()

    + * + * Calculates the sum of differences of squares in two arrays of the same size.
    + * Syntax:
    + * SUMX2MY2(arrayX, arrayY)

    + * + * result = Σi: 0..n(xi2-yi2) + * + * @author Amol S. Deshmukh < amolweb at ya hoo dot com > + */ +public final class Sumx2my2 extends XYNumericFunction { + + private static final Accumulator XSquaredMinusYSquaredAccumulator = new Accumulator() { + public double accumulate(double x, double y) { + return x * x - y * y; + } + }; + + protected Accumulator createAccumulator() { + return XSquaredMinusYSquaredAccumulator; + } +} diff --git a/src/java/org/apache/poi/ss/formula/functions/Sumx2py2.java b/src/java/org/apache/poi/ss/formula/functions/Sumx2py2.java new file mode 100644 index 0000000000..6e40445a4f --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/functions/Sumx2py2.java @@ -0,0 +1,43 @@ +/* ==================================================================== + 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.functions; + + +/** + * Implementation of Excel function SUMX2PY2()

    + * + * Calculates the sum of squares in two arrays of the same size.
    + * Syntax:
    + * SUMX2PY2(arrayX, arrayY)

    + * + * result = Σi: 0..n(xi2+yi2) + * + * @author Amol S. Deshmukh < amolweb at ya hoo dot com > + */ +public final class Sumx2py2 extends XYNumericFunction { + + private static final Accumulator XSquaredPlusYSquaredAccumulator = new Accumulator() { + public double accumulate(double x, double y) { + return x * x + y * y; + } + }; + + protected Accumulator createAccumulator() { + return XSquaredPlusYSquaredAccumulator; + } +} diff --git a/src/java/org/apache/poi/ss/formula/functions/Sumxmy2.java b/src/java/org/apache/poi/ss/formula/functions/Sumxmy2.java new file mode 100644 index 0000000000..3955d06da0 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/functions/Sumxmy2.java @@ -0,0 +1,43 @@ +/* ==================================================================== + 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.functions; + +/** + * Implementation of Excel function SUMXMY2()

    + * + * Calculates the sum of squares of differences between two arrays of the same size.
    + * Syntax:
    + * SUMXMY2(arrayX, arrayY)

    + * + * result = Σi: 0..n(xi-yi)2 + * + * @author Amol S. Deshmukh < amolweb at ya hoo dot com > + */ +public final class Sumxmy2 extends XYNumericFunction { + + private static final Accumulator XMinusYSquaredAccumulator = new Accumulator() { + public double accumulate(double x, double y) { + double xmy = x - y; + return xmy * xmy; + } + }; + + protected Accumulator createAccumulator() { + return XMinusYSquaredAccumulator; + } +} diff --git a/src/java/org/apache/poi/ss/formula/functions/T.java b/src/java/org/apache/poi/ss/formula/functions/T.java new file mode 100644 index 0000000000..23fb5928c9 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/functions/T.java @@ -0,0 +1,56 @@ +/* ==================================================================== + 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.functions; + +import org.apache.poi.ss.formula.eval.AreaEval; +import org.apache.poi.ss.formula.eval.ErrorEval; +import org.apache.poi.ss.formula.eval.RefEval; +import org.apache.poi.ss.formula.eval.StringEval; +import org.apache.poi.ss.formula.eval.ValueEval; + +/** + * Implementation of Excel T() function + *

    + * If the argument is a text or error value it is returned unmodified. All other argument types + * cause an empty string result. If the argument is an area, the first (top-left) cell is used + * (regardless of the coordinates of the evaluating formula cell). + */ +public final class T extends Fixed1ArgFunction { + + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0) { + ValueEval arg = arg0; + if (arg instanceof RefEval) { + arg = ((RefEval) arg).getInnerValueEval(); + } else if (arg instanceof AreaEval) { + // when the arg is an area, choose the top left cell + arg = ((AreaEval) arg).getRelativeValue(0, 0); + } + + if (arg instanceof StringEval) { + // Text values are returned unmodified + return arg; + } + + if (arg instanceof ErrorEval) { + // Error values also returned unmodified + return arg; + } + // for all other argument types the result is empty string + return StringEval.EMPTY_INSTANCE; + } +} diff --git a/src/java/org/apache/poi/ss/formula/functions/TextFunction.java b/src/java/org/apache/poi/ss/formula/functions/TextFunction.java new file mode 100644 index 0000000000..dc57b4277b --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/functions/TextFunction.java @@ -0,0 +1,372 @@ +/* ==================================================================== + 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.functions; + +import java.text.DateFormat; +import java.text.DecimalFormat; +import java.text.NumberFormat; +import java.text.SimpleDateFormat; +import java.util.Calendar; +import java.util.GregorianCalendar; +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.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; + +/** + * @author Amol S. Deshmukh < amolweb at ya hoo dot com > + * @author Josh Micich + * @author Stephen Wolke (smwolke at geistig.com) + */ +public abstract class TextFunction implements Function { + + protected static final String EMPTY_STRING = ""; + + protected static final String evaluateStringArg(ValueEval eval, int srcRow, int srcCol) throws EvaluationException { + ValueEval ve = OperandResolver.getSingleValue(eval, srcRow, srcCol); + return OperandResolver.coerceValueToString(ve); + } + protected static final int evaluateIntArg(ValueEval arg, int srcCellRow, int srcCellCol) throws EvaluationException { + ValueEval ve = OperandResolver.getSingleValue(arg, srcCellRow, srcCellCol); + return OperandResolver.coerceValueToInt(ve); + } + + protected static final double evaluateDoubleArg(ValueEval arg, int srcCellRow, int srcCellCol) throws EvaluationException { + ValueEval ve = OperandResolver.getSingleValue(arg, srcCellRow, srcCellCol); + return OperandResolver.coerceValueToDouble(ve); + } + + public final ValueEval evaluate(ValueEval[] args, int srcCellRow, int srcCellCol) { + try { + return evaluateFunc(args, srcCellRow, srcCellCol); + } catch (EvaluationException e) { + return e.getErrorEval(); + } + } + + protected abstract ValueEval evaluateFunc(ValueEval[] args, int srcCellRow, int srcCellCol) throws EvaluationException; + + /* ---------------------------------------------------------------------- */ + + private static abstract class SingleArgTextFunc extends Fixed1ArgFunction { + + protected SingleArgTextFunc() { + // no fields to initialise + } + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0) { + String arg; + try { + arg = evaluateStringArg(arg0, srcRowIndex, srcColumnIndex); + } catch (EvaluationException e) { + return e.getErrorEval(); + } + return evaluate(arg); + } + protected abstract ValueEval evaluate(String arg); + } + + public static final Function LEN = new SingleArgTextFunc() { + protected ValueEval evaluate(String arg) { + return new NumberEval(arg.length()); + } + }; + public static final Function LOWER = new SingleArgTextFunc() { + protected ValueEval evaluate(String arg) { + return new StringEval(arg.toLowerCase()); + } + }; + public static final Function UPPER = new SingleArgTextFunc() { + protected ValueEval evaluate(String arg) { + return new StringEval(arg.toUpperCase()); + } + }; + /** + * An implementation of the TRIM function: + * Removes leading and trailing spaces from value if evaluated operand + * value is string. + * Author: Manda Wilson < wilson at c bio dot msk cc dot org > + */ + public static final Function TRIM = new SingleArgTextFunc() { + protected ValueEval evaluate(String arg) { + return new StringEval(arg.trim()); + } + }; + + /** + * An implementation of the MID function
    + * MID returns a specific number of + * characters from a text string, starting at the specified position.

    + * + * Syntax:
    MID(text, start_num, + * num_chars)
    + * + * Author: Manda Wilson < wilson at c bio dot msk cc dot org > + */ + public static final Function MID = new Fixed3ArgFunction() { + + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, + ValueEval arg1, ValueEval arg2) { + String text; + int startCharNum; + int numChars; + try { + text = evaluateStringArg(arg0, srcRowIndex, srcColumnIndex); + startCharNum = evaluateIntArg(arg1, srcRowIndex, srcColumnIndex); + numChars = evaluateIntArg(arg2, srcRowIndex, srcColumnIndex); + } catch (EvaluationException e) { + return e.getErrorEval(); + } + int startIx = startCharNum - 1; // convert to zero-based + + // Note - for start_num arg, blank/zero causes error(#VALUE!), + // but for num_chars causes empty string to be returned. + if (startIx < 0) { + return ErrorEval.VALUE_INVALID; + } + if (numChars < 0) { + return ErrorEval.VALUE_INVALID; + } + int len = text.length(); + if (numChars < 0 || startIx > len) { + return new StringEval(""); + } + int endIx = Math.min(startIx + numChars, len); + String result = text.substring(startIx, endIx); + return new StringEval(result); + } + }; + + private static final class LeftRight extends Var1or2ArgFunction { + private static final ValueEval DEFAULT_ARG1 = new NumberEval(1.0); + private final boolean _isLeft; + protected LeftRight(boolean isLeft) { + _isLeft = isLeft; + } + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0) { + return evaluate(srcRowIndex, srcColumnIndex, arg0, DEFAULT_ARG1); + } + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, + ValueEval arg1) { + String arg; + int index; + try { + arg = evaluateStringArg(arg0, srcRowIndex, srcColumnIndex); + index = evaluateIntArg(arg1, srcRowIndex, srcColumnIndex); + } catch (EvaluationException e) { + return e.getErrorEval(); + } + + if(index < 0) { + return ErrorEval.VALUE_INVALID; + } + + String result; + if (_isLeft) { + result = arg.substring(0, Math.min(arg.length(), index)); + } else { + result = arg.substring(Math.max(0, arg.length()-index)); + } + return new StringEval(result); + } + } + + public static final Function LEFT = new LeftRight(true); + public static final Function RIGHT = new LeftRight(false); + + public static final Function CONCATENATE = new Function() { + + public ValueEval evaluate(ValueEval[] args, int srcRowIndex, int srcColumnIndex) { + StringBuilder sb = new StringBuilder(); + for (int i=0, iSize=args.length; i + * TEXT returns a number value formatted with the given + * number formatting string. This function is not a complete implementation of + * the Excel function. This function implements decimal formatting + * with the Java class DecimalFormat. For date formatting this function uses + * the SimpleDateFormat class.

    + * + * Syntax:
    TEXT(value, format_text)
    + * + */ + public static final Function TEXT = new Fixed2ArgFunction() { + + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1) { + double s0; + String s1; + try { + s0 = evaluateDoubleArg(arg0, srcRowIndex, srcColumnIndex); + s1 = evaluateStringArg(arg1, srcRowIndex, srcColumnIndex); + } catch (EvaluationException e) { + return e.getErrorEval(); + } + if (s1.matches("[\\d,\\#,\\.,\\$,\\,]+")) { + NumberFormat formatter = new DecimalFormat(s1); + return new StringEval(formatter.format(s0)); + } else if (s1.indexOf("/") == s1.lastIndexOf("/") && s1.indexOf("/") >=0 && !s1.contains("-")) { + double wholePart = Math.floor(s0); + double decPart = s0 - wholePart; + if (wholePart * decPart == 0) { + return new StringEval("0"); + } + String[] parts = s1.split(" "); + String[] fractParts; + if (parts.length == 2) { + fractParts = parts[1].split("/"); + } else { + fractParts = s1.split("/"); + } + + if (fractParts.length == 2) { + double minVal = 1.0; + double currDenom = Math.pow(10 , fractParts[1].length()) - 1d; + double currNeum = 0; + for (int i = (int)(Math.pow(10, fractParts[1].length())- 1d); i > 0; i--) { + for(int i2 = (int)(Math.pow(10, fractParts[1].length())- 1d); i2 > 0; i2--){ + if (minVal >= Math.abs((double)i2/(double)i - decPart)) { + currDenom = i; + currNeum = i2; + minVal = Math.abs((double)i2/(double)i - decPart); + } + } + } + NumberFormat neumFormatter = new DecimalFormat(fractParts[0]); + NumberFormat denomFormatter = new DecimalFormat(fractParts[1]); + if (parts.length == 2) { + NumberFormat wholeFormatter = new DecimalFormat(parts[0]); + String result = wholeFormatter.format(wholePart) + " " + neumFormatter.format(currNeum) + "/" + denomFormatter.format(currDenom); + return new StringEval(result); + } else { + String result = neumFormatter.format(currNeum + (currDenom * wholePart)) + "/" + denomFormatter.format(currDenom); + return new StringEval(result); + } + } else { + return ErrorEval.VALUE_INVALID; + } + } else { + try { + DateFormat dateFormatter = new SimpleDateFormat(s1); + Calendar cal = new GregorianCalendar(1899, 11, 30, 0, 0, 0); + cal.add(Calendar.DATE, (int)Math.floor(s0)); + double dayFraction = s0 - Math.floor(s0); + cal.add(Calendar.MILLISECOND, (int) Math.round(dayFraction * 24 * 60 * 60 * 1000)); + return new StringEval(dateFormatter.format(cal.getTime())); + } catch (Exception e) { + return ErrorEval.VALUE_INVALID; + } + } + } + }; + + private static final class SearchFind extends Var2or3ArgFunction { + + private final boolean _isCaseSensitive; + + public SearchFind(boolean isCaseSensitive) { + _isCaseSensitive = isCaseSensitive; + } + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1) { + try { + String needle = TextFunction.evaluateStringArg(arg0, srcRowIndex, srcColumnIndex); + String haystack = TextFunction.evaluateStringArg(arg1, srcRowIndex, srcColumnIndex); + return eval(haystack, needle, 0); + } catch (EvaluationException e) { + return e.getErrorEval(); + } + } + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1, + ValueEval arg2) { + try { + String needle = TextFunction.evaluateStringArg(arg0, srcRowIndex, srcColumnIndex); + String haystack = TextFunction.evaluateStringArg(arg1, srcRowIndex, srcColumnIndex); + // evaluate third arg and convert from 1-based to 0-based index + int startpos = TextFunction.evaluateIntArg(arg2, srcRowIndex, srcColumnIndex) - 1; + if (startpos < 0) { + return ErrorEval.VALUE_INVALID; + } + return eval(haystack, needle, startpos); + } catch (EvaluationException e) { + return e.getErrorEval(); + } + } + private ValueEval eval(String haystack, String needle, int startIndex) { + int result; + if (_isCaseSensitive) { + result = haystack.indexOf(needle, startIndex); + } else { + result = haystack.toUpperCase().indexOf(needle.toUpperCase(), startIndex); + } + if (result == -1) { + return ErrorEval.VALUE_INVALID; + } + return new NumberEval(result + 1); + } + } + /** + * Implementation of the FIND() function.

    + * + * Syntax:
    + * FIND(find_text, within_text, start_num)

    + * + * FIND returns the character position of the first (case sensitive) occurrence of + * find_text inside within_text. The third parameter, + * start_num, is optional (default=1) and specifies where to start searching + * from. Character positions are 1-based.

    + * + * Author: Torstein Tauno Svendsen (torstei@officenet.no) + */ + public static final Function FIND = new SearchFind(true); + /** + * Implementation of the FIND() function.

    + * + * Syntax:
    + * SEARCH(find_text, within_text, start_num)

    + * + * SEARCH is a case-insensitive version of FIND() + */ + public static final Function SEARCH = new SearchFind(false); +} diff --git a/src/java/org/apache/poi/ss/formula/functions/TimeFunc.java b/src/java/org/apache/poi/ss/formula/functions/TimeFunc.java new file mode 100644 index 0000000000..8423919e1a --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/functions/TimeFunc.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.functions; + +import org.apache.poi.ss.formula.eval.ErrorEval; +import org.apache.poi.ss.formula.eval.EvaluationException; +import org.apache.poi.ss.formula.eval.MissingArgEval; +import org.apache.poi.ss.formula.eval.NumberEval; +import org.apache.poi.ss.formula.eval.OperandResolver; +import org.apache.poi.ss.formula.eval.ValueEval; + +/** + * Implementation for the Excel function TIME + * + * @author Steven Butler (sebutler @ gmail dot com) + * + * Based on POI {@link DateFunc} + */ +public final class TimeFunc extends Fixed3ArgFunction { + + private static final int SECONDS_PER_MINUTE = 60; + private static final int SECONDS_PER_HOUR = 3600; + private static final int HOURS_PER_DAY = 24; + private static final int SECONDS_PER_DAY = HOURS_PER_DAY * SECONDS_PER_HOUR; + + + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1, + ValueEval arg2) { + double result; + try { + result = evaluate(evalArg(arg0, srcRowIndex, srcColumnIndex), evalArg(arg1, srcRowIndex, srcColumnIndex), evalArg(arg2, srcRowIndex, srcColumnIndex)); + } catch (EvaluationException e) { + return e.getErrorEval(); + } + return new NumberEval(result); + } + private static int evalArg(ValueEval arg, int srcRowIndex, int srcColumnIndex) throws EvaluationException { + if (arg == MissingArgEval.instance) { + return 0; + } + ValueEval ev = OperandResolver.getSingleValue(arg, srcRowIndex, srcColumnIndex); + // Excel silently truncates double values to integers + return OperandResolver.coerceValueToInt(ev); + } + /** + * Converts the supplied hours, minutes and seconds to an Excel time value. + * + * + * @param ds array of 3 doubles containing hours, minutes and seconds. + * Non-integer inputs are truncated to an integer before further calculation + * of the time value. + * @return An Excel representation of a time of day. + * If the time value represents more than a day, the days are removed from + * the result, leaving only the time of day component. + * @throws org.apache.poi.ss.formula.eval.EvaluationException + * If any of the arguments are greater than 32767 or the hours + * minutes and seconds when combined form a time value less than 0, the function + * evaluates to an error. + */ + private static double evaluate(int hours, int minutes, int seconds) throws EvaluationException { + + if (hours > 32767 || minutes > 32767 || seconds > 32767) { + throw new EvaluationException(ErrorEval.VALUE_INVALID); + } + int totalSeconds = hours * SECONDS_PER_HOUR + minutes * SECONDS_PER_MINUTE + seconds; + + if (totalSeconds < 0) { + throw new EvaluationException(ErrorEval.VALUE_INVALID); + } + return (totalSeconds % SECONDS_PER_DAY) / (double)SECONDS_PER_DAY; + } +} diff --git a/src/java/org/apache/poi/ss/formula/functions/Today.java b/src/java/org/apache/poi/ss/formula/functions/Today.java new file mode 100644 index 0000000000..8abc2f0d92 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/functions/Today.java @@ -0,0 +1,41 @@ +/* ==================================================================== + 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.functions; + +import java.util.Calendar; +import java.util.GregorianCalendar; + +import org.apache.poi.ss.formula.eval.NumberEval; +import org.apache.poi.ss.formula.eval.ValueEval; +import org.apache.poi.ss.usermodel.DateUtil; + +/** + * Implementation of Excel TODAY() Function
    + * + * @author Frank Taffelt + */ +public final class Today extends Fixed0ArgFunction { + + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex) { + + Calendar now = new GregorianCalendar(); + now.set(now.get(Calendar.YEAR), now.get(Calendar.MONTH), now.get(Calendar.DATE),0,0,0); + now.set(Calendar.MILLISECOND, 0); + return new NumberEval(DateUtil.getExcelDate(now.getTime())); + } +} diff --git a/src/java/org/apache/poi/ss/formula/functions/Value.java b/src/java/org/apache/poi/ss/formula/functions/Value.java new file mode 100644 index 0000000000..811db9c14c --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/functions/Value.java @@ -0,0 +1,184 @@ +/* ==================================================================== + 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.functions; + +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; + +/** + * Implementation for Excel VALUE() function.

    + * + * Syntax:
    VALUE(text)
    + * + * Converts the text argument to a number. Leading and/or trailing whitespace is + * ignored. Currency symbols and thousands separators are stripped out. + * Scientific notation is also supported. If the supplied text does not convert + * properly the result is #VALUE! error. Blank string converts to zero. + * + * @author Josh Micich + */ +public final class Value extends Fixed1ArgFunction { + + /** "1,0000" is valid, "1,00" is not */ + private static final int MIN_DISTANCE_BETWEEN_THOUSANDS_SEPARATOR = 4; + private static final Double ZERO = new Double(0.0); + + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0) { + ValueEval veText; + try { + veText = OperandResolver.getSingleValue(arg0, srcRowIndex, srcColumnIndex); + } catch (EvaluationException e) { + return e.getErrorEval(); + } + String strText = OperandResolver.coerceValueToString(veText); + Double result = convertTextToNumber(strText); + if (result == null) { + return ErrorEval.VALUE_INVALID; + } + return new NumberEval(result.doubleValue()); + } + + /** + * TODO see if the same functionality is needed in {@link OperandResolver#parseDouble(String)} + * + * @return null if there is any problem converting the text + */ + private static Double convertTextToNumber(String strText) { + boolean foundCurrency = false; + boolean foundUnaryPlus = false; + boolean foundUnaryMinus = false; + + int len = strText.length(); + int i; + for (i = 0; i < len; i++) { + char ch = strText.charAt(i); + if (Character.isDigit(ch) || ch == '.') { + break; + } + switch (ch) { + case ' ': + // intervening spaces between '$', '-', '+' are OK + continue; + case '$': + if (foundCurrency) { + // only one currency symbols is allowed + return null; + } + foundCurrency = true; + continue; + case '+': + if (foundUnaryMinus || foundUnaryPlus) { + return null; + } + foundUnaryPlus = true; + continue; + case '-': + if (foundUnaryMinus || foundUnaryPlus) { + return null; + } + foundUnaryMinus = true; + continue; + default: + // all other characters are illegal + return null; + } + } + if (i >= len) { + // didn't find digits or '.' + if (foundCurrency || foundUnaryMinus || foundUnaryPlus) { + return null; + } + return ZERO; + } + + // remove thousands separators + + boolean foundDecimalPoint = false; + int lastThousandsSeparatorIndex = Short.MIN_VALUE; + + StringBuffer sb = new StringBuffer(len); + for (; i < len; i++) { + char ch = strText.charAt(i); + if (Character.isDigit(ch)) { + sb.append(ch); + continue; + } + switch (ch) { + case ' ': + String remainingText = strText.substring(i); + if (remainingText.trim().length() > 0) { + // intervening spaces not allowed once the digits start + return null; + } + break; + case '.': + if (foundDecimalPoint) { + return null; + } + if (i - lastThousandsSeparatorIndex < MIN_DISTANCE_BETWEEN_THOUSANDS_SEPARATOR) { + return null; + } + foundDecimalPoint = true; + sb.append('.'); + continue; + case ',': + if (foundDecimalPoint) { + // thousands separators not allowed after '.' or 'E' + return null; + } + int distanceBetweenThousandsSeparators = i - lastThousandsSeparatorIndex; + // as long as there are 3 or more digits between + if (distanceBetweenThousandsSeparators < MIN_DISTANCE_BETWEEN_THOUSANDS_SEPARATOR) { + return null; + } + lastThousandsSeparatorIndex = i; + // don't append ',' + continue; + + case 'E': + case 'e': + if (i - lastThousandsSeparatorIndex < MIN_DISTANCE_BETWEEN_THOUSANDS_SEPARATOR) { + return null; + } + // append rest of strText and skip to end of loop + sb.append(strText.substring(i)); + i = len; + break; + default: + // all other characters are illegal + return null; + } + } + if (!foundDecimalPoint) { + if (i - lastThousandsSeparatorIndex < MIN_DISTANCE_BETWEEN_THOUSANDS_SEPARATOR) { + return null; + } + } + double d; + try { + d = Double.parseDouble(sb.toString()); + } catch (NumberFormatException e) { + // still a problem parsing the number - probably out of range + return null; + } + return new Double(foundUnaryMinus ? -d : d); + } +} diff --git a/src/java/org/apache/poi/ss/formula/functions/Var1or2ArgFunction.java b/src/java/org/apache/poi/ss/formula/functions/Var1or2ArgFunction.java new file mode 100644 index 0000000000..1938b79d3b --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/functions/Var1or2ArgFunction.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.functions; + +import org.apache.poi.ss.formula.eval.ErrorEval; +import org.apache.poi.ss.formula.eval.ValueEval; + +/** + * Convenience base class for any function which must take two or three + * arguments + * + * @author Josh Micich + */ +abstract class Var1or2ArgFunction implements Function1Arg, Function2Arg { + + public final ValueEval evaluate(ValueEval[] args, int srcRowIndex, int srcColumnIndex) { + switch (args.length) { + case 1: + return evaluate(srcRowIndex, srcColumnIndex, args[0]); + case 2: + return evaluate(srcRowIndex, srcColumnIndex, args[0], args[1]); + } + return ErrorEval.VALUE_INVALID; + } +} diff --git a/src/java/org/apache/poi/ss/formula/functions/Var2or3ArgFunction.java b/src/java/org/apache/poi/ss/formula/functions/Var2or3ArgFunction.java new file mode 100644 index 0000000000..cf3f350d6e --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/functions/Var2or3ArgFunction.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.functions; + +import org.apache.poi.ss.formula.eval.ErrorEval; +import org.apache.poi.ss.formula.eval.ValueEval; + +/** + * Convenience base class for any function which must take two or three + * arguments + * + * @author Josh Micich + */ +abstract class Var2or3ArgFunction implements Function2Arg, Function3Arg { + + public final ValueEval evaluate(ValueEval[] args, int srcRowIndex, int srcColumnIndex) { + switch (args.length) { + case 2: + return evaluate(srcRowIndex, srcColumnIndex, args[0], args[1]); + case 3: + return evaluate(srcRowIndex, srcColumnIndex, args[0], args[1], args[2]); + } + return ErrorEval.VALUE_INVALID; + } +} diff --git a/src/java/org/apache/poi/ss/formula/functions/Var3or4ArgFunction.java b/src/java/org/apache/poi/ss/formula/functions/Var3or4ArgFunction.java new file mode 100644 index 0000000000..e701b2d9a2 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/functions/Var3or4ArgFunction.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.functions; + +import org.apache.poi.ss.formula.eval.ErrorEval; +import org.apache.poi.ss.formula.eval.ValueEval; + +/** + * Convenience base class for any function which must take three or four + * arguments + * + * @author Josh Micich + */ +abstract class Var3or4ArgFunction implements Function3Arg, Function4Arg { + + public final ValueEval evaluate(ValueEval[] args, int srcRowIndex, int srcColumnIndex) { + switch (args.length) { + case 3: + return evaluate(srcRowIndex, srcColumnIndex, args[0], args[1], args[2]); + case 4: + return evaluate(srcRowIndex, srcColumnIndex, args[0], args[1], args[2], args[3]); + } + return ErrorEval.VALUE_INVALID; + } +} diff --git a/src/java/org/apache/poi/ss/formula/functions/Vlookup.java b/src/java/org/apache/poi/ss/formula/functions/Vlookup.java new file mode 100644 index 0000000000..539be08163 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/functions/Vlookup.java @@ -0,0 +1,81 @@ +/* ==================================================================== + 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.functions; + +import org.apache.poi.ss.formula.eval.BoolEval; +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.LookupUtils.ValueVector; +import org.apache.poi.ss.formula.TwoDEval; +/** + * Implementation of the VLOOKUP() function.

    + * + * VLOOKUP finds a row in a lookup table by the first column value and returns the value from another column.
    + * + * Syntax:
    + * VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

    + * + * lookup_value The value to be found in the first column of the table array.
    + * table_array An area reference for the lookup data.
    + * col_index_num a 1 based index specifying which column value of the lookup data will be returned.
    + * range_lookup If TRUE (default), VLOOKUP finds the largest value less than or equal to + * the lookup_value. If FALSE, only exact matches will be considered
    + * + * @author Josh Micich + */ +public final class Vlookup extends Var3or4ArgFunction { + private static final ValueEval DEFAULT_ARG3 = BoolEval.TRUE; + + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1, + ValueEval arg2) { + return evaluate(srcRowIndex, srcColumnIndex, arg0, arg1, arg2, DEFAULT_ARG3); + } + + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1, + ValueEval arg2, ValueEval arg3) { + try { + // Evaluation order: + // arg0 lookup_value, arg1 table_array, arg3 range_lookup, find lookup value, arg2 col_index, fetch result + ValueEval lookupValue = OperandResolver.getSingleValue(arg0, srcRowIndex, srcColumnIndex); + TwoDEval tableArray = LookupUtils.resolveTableArrayArg(arg1); + boolean isRangeLookup = LookupUtils.resolveRangeLookupArg(arg3, srcRowIndex, srcColumnIndex); + int rowIndex = LookupUtils.lookupIndexOfValue(lookupValue, LookupUtils.createColumnVector(tableArray, 0), isRangeLookup); + int colIndex = LookupUtils.resolveRowOrColIndexArg(arg2, srcRowIndex, srcColumnIndex); + ValueVector resultCol = createResultColumnVector(tableArray, colIndex); + return resultCol.getItem(rowIndex); + } catch (EvaluationException e) { + return e.getErrorEval(); + } + } + + + /** + * Returns one column from an AreaEval + * + * @param colIndex assumed to be non-negative + * + * @throws EvaluationException (#REF!) if colIndex is too high + */ + private ValueVector createResultColumnVector(TwoDEval tableArray, int colIndex) throws EvaluationException { + if(colIndex >= tableArray.getWidth()) { + throw EvaluationException.invalidRef(); + } + return LookupUtils.createColumnVector(tableArray, colIndex); + } +} diff --git a/src/java/org/apache/poi/ss/formula/functions/XYNumericFunction.java b/src/java/org/apache/poi/ss/formula/functions/XYNumericFunction.java new file mode 100644 index 0000000000..10b496769a --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/functions/XYNumericFunction.java @@ -0,0 +1,177 @@ +/* ==================================================================== + 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.functions; + +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.RefEval; +import org.apache.poi.ss.formula.eval.ValueEval; +import org.apache.poi.ss.formula.functions.LookupUtils.ValueVector; +import org.apache.poi.ss.formula.TwoDEval; + +/** + * @author Amol S. Deshmukh < amolweb at ya hoo dot com > + */ +public abstract class XYNumericFunction extends Fixed2ArgFunction { + + private static abstract class ValueArray implements ValueVector { + private final int _size; + protected ValueArray(int size) { + _size = size; + } + public ValueEval getItem(int index) { + if (index < 0 || index > _size) { + throw new IllegalArgumentException("Specified index " + index + + " is outside range (0.." + (_size - 1) + ")"); + } + return getItemInternal(index); + } + protected abstract ValueEval getItemInternal(int index); + public final int getSize() { + return _size; + } + } + + private static final class SingleCellValueArray extends ValueArray { + private final ValueEval _value; + public SingleCellValueArray(ValueEval value) { + super(1); + _value = value; + } + protected ValueEval getItemInternal(int index) { + return _value; + } + } + + private static final class RefValueArray extends ValueArray { + private final RefEval _ref; + public RefValueArray(RefEval ref) { + super(1); + _ref = ref; + } + protected ValueEval getItemInternal(int index) { + return _ref.getInnerValueEval(); + } + } + + private static final class AreaValueArray extends ValueArray { + private final TwoDEval _ae; + private final int _width; + + public AreaValueArray(TwoDEval ae) { + super(ae.getWidth() * ae.getHeight()); + _ae = ae; + _width = ae.getWidth(); + } + protected ValueEval getItemInternal(int index) { + int rowIx = index / _width; + int colIx = index % _width; + return _ae.getValue(rowIx, colIx); + } + } + + protected static interface Accumulator { + double accumulate(double x, double y); + } + + /** + * Constructs a new instance of the Accumulator used to calculated this function + */ + protected abstract Accumulator createAccumulator(); + + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1) { + + double result; + try { + ValueVector vvX = createValueVector(arg0); + ValueVector vvY = createValueVector(arg1); + int size = vvX.getSize(); + if (size == 0 || vvY.getSize() != size) { + return ErrorEval.NA; + } + result = evaluateInternal(vvX, vvY, size); + } catch (EvaluationException e) { + return e.getErrorEval(); + } + if (Double.isNaN(result) || Double.isInfinite(result)) { + return ErrorEval.NUM_ERROR; + } + return new NumberEval(result); + } + + private double evaluateInternal(ValueVector x, ValueVector y, int size) + throws EvaluationException { + Accumulator acc = createAccumulator(); + + // error handling is as if the x is fully evaluated before y + ErrorEval firstXerr = null; + ErrorEval firstYerr = null; + boolean accumlatedSome = false; + double result = 0.0; + + for (int i = 0; i < size; i++) { + ValueEval vx = x.getItem(i); + ValueEval vy = y.getItem(i); + if (vx instanceof ErrorEval) { + if (firstXerr == null) { + firstXerr = (ErrorEval) vx; + continue; + } + } + if (vy instanceof ErrorEval) { + if (firstYerr == null) { + firstYerr = (ErrorEval) vy; + continue; + } + } + // only count pairs if both elements are numbers + if (vx instanceof NumberEval && vy instanceof NumberEval) { + accumlatedSome = true; + NumberEval nx = (NumberEval) vx; + NumberEval ny = (NumberEval) vy; + result += acc.accumulate(nx.getNumberValue(), ny.getNumberValue()); + } else { + // all other combinations of value types are silently ignored + } + } + if (firstXerr != null) { + throw new EvaluationException(firstXerr); + } + if (firstYerr != null) { + throw new EvaluationException(firstYerr); + } + if (!accumlatedSome) { + throw new EvaluationException(ErrorEval.DIV_ZERO); + } + return result; + } + + private static ValueVector createValueVector(ValueEval arg) throws EvaluationException { + if (arg instanceof ErrorEval) { + throw new EvaluationException((ErrorEval) arg); + } + if (arg instanceof TwoDEval) { + return new AreaValueArray((TwoDEval) arg); + } + if (arg instanceof RefEval) { + return new RefValueArray((RefEval) arg); + } + return new SingleCellValueArray(arg); + } +} diff --git a/src/java/org/apache/poi/ss/formula/udf/AggregatingUDFFinder.java b/src/java/org/apache/poi/ss/formula/udf/AggregatingUDFFinder.java new file mode 100644 index 0000000000..2be10e0ea8 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/udf/AggregatingUDFFinder.java @@ -0,0 +1,52 @@ +/* ==================================================================== + 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.udf; + +import org.apache.poi.ss.formula.functions.FreeRefFunction; + +/** + * Collects add-in libraries and VB macro functions together into one UDF finder + * + * @author PUdalau + */ +public final class AggregatingUDFFinder implements UDFFinder { + + private final UDFFinder[] _usedToolPacks; + + public AggregatingUDFFinder(UDFFinder ... usedToolPacks) { + _usedToolPacks = usedToolPacks.clone(); + } + + /** + * Returns executor by specified name. Returns null if + * function isn't contained by any registered tool pack. + * + * @param name Name of function. + * @return Function executor. null if not found + */ + public FreeRefFunction findFunction(String name) { + FreeRefFunction evaluatorForFunction; + for (UDFFinder pack : _usedToolPacks) { + evaluatorForFunction = pack.findFunction(name); + if (evaluatorForFunction != null) { + return evaluatorForFunction; + } + } + return null; + } +} diff --git a/src/java/org/apache/poi/ss/formula/udf/DefaultUDFFinder.java b/src/java/org/apache/poi/ss/formula/udf/DefaultUDFFinder.java new file mode 100644 index 0000000000..fd23cad977 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/udf/DefaultUDFFinder.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.udf; + +import java.util.HashMap; +import java.util.Map; + +import org.apache.poi.ss.formula.functions.FreeRefFunction; + +/** + * Default UDF finder - for adding your own user defined functions. + * + * @author PUdalau + */ +public final class DefaultUDFFinder implements UDFFinder { + private final Map _functionsByName; + + public DefaultUDFFinder(String[] functionNames, FreeRefFunction[] functionImpls) { + int nFuncs = functionNames.length; + if (functionImpls.length != nFuncs) { + throw new IllegalArgumentException( + "Mismatch in number of function names and implementations"); + } + HashMap m = new HashMap(nFuncs * 3 / 2); + for (int i = 0; i < functionImpls.length; i++) { + m.put(functionNames[i], functionImpls[i]); + } + _functionsByName = m; + } + + public FreeRefFunction findFunction(String name) { + return _functionsByName.get(name); + } +} diff --git a/src/java/org/apache/poi/ss/formula/udf/UDFFinder.java b/src/java/org/apache/poi/ss/formula/udf/UDFFinder.java new file mode 100644 index 0000000000..a98c3f189b --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/udf/UDFFinder.java @@ -0,0 +1,38 @@ +/* ==================================================================== + 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.udf; + +import org.apache.poi.ss.formula.atp.AnalysisToolPak; +import org.apache.poi.ss.formula.functions.FreeRefFunction; + +/** + * Common interface for "Add-in" libraries and user defined function libraries. + * + * @author PUdalau + */ +public interface UDFFinder { + public static final UDFFinder DEFAULT = new AggregatingUDFFinder(AnalysisToolPak.instance); + + /** + * Returns executor by specified name. Returns null if the function name is unknown. + * + * @param name Name of function. + * @return Function executor. + */ + FreeRefFunction findFunction(String name); +} diff --git a/src/java/org/apache/poi/ss/util/CellReference.java b/src/java/org/apache/poi/ss/util/CellReference.java index 5acebcef16..67f8fa0b15 100644 --- a/src/java/org/apache/poi/ss/util/CellReference.java +++ b/src/java/org/apache/poi/ss/util/CellReference.java @@ -20,7 +20,7 @@ package org.apache.poi.ss.util; import java.util.regex.Matcher; import java.util.regex.Pattern; -import org.apache.poi.hssf.record.formula.SheetNameFormatter; +import org.apache.poi.ss.formula.SheetNameFormatter; import org.apache.poi.ss.SpreadsheetVersion; import org.apache.poi.ss.usermodel.Cell; diff --git a/src/java/org/apache/poi/ss/util/WorkbookUtil.java b/src/java/org/apache/poi/ss/util/WorkbookUtil.java index b8c60da6ed..8ab44b9ce4 100644 --- a/src/java/org/apache/poi/ss/util/WorkbookUtil.java +++ b/src/java/org/apache/poi/ss/util/WorkbookUtil.java @@ -28,7 +28,7 @@ public class WorkbookUtil { /** * Creates a valid sheet name, which is conform to the rules. * In any case, the result safely can be used for - * {@link org.apache.poi.hssf.usermodel.HSSFWorkbook#setSheetName(int, String)}. + * {@link org.apache.poi.ss.usermodel.Workbook#setSheetName(int, String)}. *
    * Rules: *

      -- 2.39.5