diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFEvaluationCell.java b/src/java/org/apache/poi/hssf/usermodel/HSSFEvaluationCell.java index 1771b2e..d9ead95 100644 --- a/src/java/org/apache/poi/hssf/usermodel/HSSFEvaluationCell.java +++ b/src/java/org/apache/poi/hssf/usermodel/HSSFEvaluationCell.java @@ -20,6 +20,7 @@ package org.apache.poi.hssf.usermodel; import org.apache.poi.ss.formula.EvaluationCell; import org.apache.poi.ss.formula.EvaluationSheet; import org.apache.poi.ss.usermodel.CellType; +import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.util.Internal; /** * HSSF wrapper for a cell under evaluation @@ -94,6 +95,17 @@ final class HSSFEvaluationCell implements EvaluationCell { public String getStringCellValue() { return _cell.getRichStringCellValue().getString(); } + + @Override + public CellRangeAddress getArrayFormulaRange() { + return _cell.getArrayFormulaRange(); + } + + @Override + public boolean isPartOfArrayFormulaGroup() { + return _cell.isPartOfArrayFormulaGroup(); + } + /** * Will return {@link CellType} in a future version of POI. * For forwards compatibility, do not hard-code cell type literals in your code. diff --git a/src/java/org/apache/poi/ss/formula/CacheAreaEval.java b/src/java/org/apache/poi/ss/formula/CacheAreaEval.java new file mode 100644 index 0000000..1a62248 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/CacheAreaEval.java @@ -0,0 +1,131 @@ +/* ==================================================================== + 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.ss.formula.TwoDEval; +import org.apache.poi.ss.formula.eval.AreaEval; +import org.apache.poi.ss.formula.eval.AreaEvalBase; +import org.apache.poi.ss.formula.eval.BlankEval; +import org.apache.poi.ss.formula.eval.ValueEval; +import org.apache.poi.ss.formula.ptg.AreaI; +import org.apache.poi.ss.formula.ptg.AreaI.OffsetArea; +import org.apache.poi.ss.util.CellReference; + +/** + * @author Robert Hulbert + * Provides holding structure for temporary values in arrays during the evaluation process. + * As such, Row/Column references do not actually correspond to data in the file. + */ + +public final class CacheAreaEval extends AreaEvalBase { + + /* Value Containter */ + private final ValueEval[] _values; + + public CacheAreaEval(AreaI ptg, ValueEval[] values) { + super(ptg); + _values = values; + } + + public CacheAreaEval(int firstRow, int firstColumn, int lastRow, int lastColumn, ValueEval[] values) { + super(firstRow, firstColumn, lastRow, lastColumn); + _values = values; + } + + public ValueEval getRelativeValue(int relativeRowIndex, int relativeColumnIndex) { + return getRelativeValue(-1, relativeRowIndex, relativeColumnIndex); + } + + public ValueEval getRelativeValue(int sheetIndex, int relativeRowIndex, int relativeColumnIndex) { + int oneDimensionalIndex = relativeRowIndex * getWidth() + relativeColumnIndex; + return _values[oneDimensionalIndex]; + } + + public AreaEval offset(int relFirstRowIx, int relLastRowIx, + int relFirstColIx, int relLastColIx) { + + AreaI area = new OffsetArea(getFirstRow(), getFirstColumn(), + relFirstRowIx, relLastRowIx, relFirstColIx, relLastColIx); + + int height = area.getLastRow() - area.getFirstRow() + 1; + int width = area.getLastColumn() - area.getFirstColumn() + 1; + + ValueEval newVals[] = new ValueEval[height * width]; + + int startRow = area.getFirstRow() - getFirstRow(); + int startCol = area.getFirstColumn() - getFirstColumn(); + + for (int j = 0; j < height; j++) { + for (int i = 0; i < width; i++) { + ValueEval temp; + + /* CacheAreaEval is only temporary value representation, does not equal sheet selection + * so any attempts going beyond the selection results in BlankEval + */ + if (startRow + j > getLastRow() || startCol + i > getLastColumn()) { + temp = BlankEval.instance; + } + else { + temp = _values[(startRow + j) * getWidth() + (startCol + i)]; + } + newVals[j * width + i] = temp; + } + } + + return new CacheAreaEval(area, newVals); + } + + public TwoDEval getRow(int rowIndex) { + if (rowIndex >= getHeight()) { + throw new IllegalArgumentException("Invalid rowIndex " + rowIndex + + ". Allowable range is (0.." + getHeight() + ")."); + } + int absRowIndex = getFirstRow() + rowIndex; + ValueEval[] values = new ValueEval[getWidth()]; + + for (int i = 0; i < values.length; i++) { + values[i] = getRelativeValue(rowIndex, i); + } + return new CacheAreaEval(absRowIndex, getFirstColumn() , absRowIndex, getLastColumn(), values); + } + + public TwoDEval getColumn(int columnIndex) { + if (columnIndex >= getWidth()) { + throw new IllegalArgumentException("Invalid columnIndex " + columnIndex + + ". Allowable range is (0.." + getWidth() + ")."); + } + int absColIndex = getFirstColumn() + columnIndex; + ValueEval[] values = new ValueEval[getHeight()]; + + for (int i = 0; i < values.length; i++) { + values[i] = getRelativeValue(i, columnIndex); + } + + return new CacheAreaEval(getFirstRow(), absColIndex, getLastRow(), absColIndex, values); + } + + public String toString() { + CellReference crA = new CellReference(getFirstRow(), getFirstColumn()); + CellReference crB = new CellReference(getLastRow(), getLastColumn()); + return getClass().getName() + "[" + + crA.formatAsString() + + ':' + + crB.formatAsString() + + "]"; + } +} diff --git a/src/java/org/apache/poi/ss/formula/EvaluationCell.java b/src/java/org/apache/poi/ss/formula/EvaluationCell.java index ecf53ed..30bb4dd 100644 --- a/src/java/org/apache/poi/ss/formula/EvaluationCell.java +++ b/src/java/org/apache/poi/ss/formula/EvaluationCell.java @@ -18,6 +18,7 @@ package org.apache.poi.ss.formula; import org.apache.poi.ss.usermodel.CellType; +import org.apache.poi.ss.util.CellRangeAddress; /** * Abstracts a cell for the purpose of formula evaluation. This interface represents both formula @@ -56,6 +57,8 @@ public interface EvaluationCell { String getStringCellValue(); boolean getBooleanCellValue(); int getErrorCellValue(); + CellRangeAddress getArrayFormulaRange(); + boolean isPartOfArrayFormulaGroup(); /** * Will return {@link CellType} in a future version of POI. diff --git a/src/java/org/apache/poi/ss/formula/OperationEvaluationContext.java b/src/java/org/apache/poi/ss/formula/OperationEvaluationContext.java index d7345f7..e92dad5 100644 --- a/src/java/org/apache/poi/ss/formula/OperationEvaluationContext.java +++ b/src/java/org/apache/poi/ss/formula/OperationEvaluationContext.java @@ -22,11 +22,15 @@ import org.apache.poi.ss.formula.CollaboratingWorkbooksEnvironment.WorkbookNotFo import org.apache.poi.ss.formula.EvaluationWorkbook.ExternalName; import org.apache.poi.ss.formula.EvaluationWorkbook.ExternalSheet; import org.apache.poi.ss.formula.EvaluationWorkbook.ExternalSheetRange; +import org.apache.poi.ss.formula.constant.ErrorConstant; import org.apache.poi.ss.formula.eval.AreaEval; +import org.apache.poi.ss.formula.eval.BoolEval; import org.apache.poi.ss.formula.eval.ErrorEval; import org.apache.poi.ss.formula.eval.ExternalNameEval; import org.apache.poi.ss.formula.eval.FunctionNameEval; +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.functions.FreeRefFunction; import org.apache.poi.ss.formula.ptg.Area3DPtg; @@ -331,6 +335,42 @@ public final class OperationEvaluationContext { return new LazyAreaEval(aptg.getFirstRow(), aptg.getFirstColumn(), aptg.getLastRow(), aptg.getLastColumn(), sre); } + + public ValueEval getAreaValueEval(int firstRowIndex, int firstColumnIndex, + int lastRowIndex, int lastColumnIndex, Object[][] tokens) { + + ValueEval values[] = new ValueEval[tokens.length * tokens[0].length]; + + int index = 0; + for (int jdx = 0; jdx < tokens.length; jdx++) { + for (int idx = 0; idx < tokens[0].length; idx++) { + values[index++] = convertObjectEval(tokens[jdx][idx]); + } + } + + return new CacheAreaEval(firstRowIndex, firstColumnIndex, lastRowIndex, + lastColumnIndex, values); + } + + private ValueEval convertObjectEval(Object token) { + if (token == null) { + throw new RuntimeException("Array item cannot be null"); + } + if (token instanceof String) { + return new StringEval((String)token); + } + if (token instanceof Double) { + return new NumberEval(((Double)token).doubleValue()); + } + if (token instanceof Boolean) { + return BoolEval.valueOf(((Boolean)token).booleanValue()); + } + if (token instanceof ErrorConstant) { + return ErrorEval.valueOf(((ErrorConstant)token).getErrorCode()); + } + throw new IllegalArgumentException("Unexpected constant class (" + token.getClass().getName() + ")"); + } + public ValueEval getNameXEval(NameXPtg nameXPtg) { // Is the name actually on our workbook? diff --git a/src/java/org/apache/poi/ss/formula/OperationEvaluatorFactory.java b/src/java/org/apache/poi/ss/formula/OperationEvaluatorFactory.java index 31b86b1..44faa06 100644 --- a/src/java/org/apache/poi/ss/formula/OperationEvaluatorFactory.java +++ b/src/java/org/apache/poi/ss/formula/OperationEvaluatorFactory.java @@ -52,6 +52,7 @@ 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.ArrayFunction; import org.apache.poi.ss.formula.functions.Function; import org.apache.poi.ss.formula.functions.Indirect; @@ -116,6 +117,12 @@ final class OperationEvaluatorFactory { Function result = _instancesByPtgClass.get(ptg); if (result != null) { + EvaluationSheet evalSheet = ec.getWorkbook().getSheet(ec.getSheetIndex()); + EvaluationCell evalCell = evalSheet.getCell(ec.getRowIndex(), ec.getColumnIndex()); + + if (evalCell.isPartOfArrayFormulaGroup() && result instanceof ArrayFunction) + return ((ArrayFunction) result).evaluateArray(args, ec.getRowIndex(), ec.getColumnIndex()); + return result.evaluate(args, ec.getRowIndex(), (short) ec.getColumnIndex()); } diff --git a/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java b/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java index 367dee5..c12408c 100644 --- a/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java +++ b/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java @@ -28,6 +28,7 @@ import java.util.TreeSet; import org.apache.poi.ss.SpreadsheetVersion; import org.apache.poi.ss.formula.CollaboratingWorkbooksEnvironment.WorkbookNotFoundException; import org.apache.poi.ss.formula.atp.AnalysisToolPak; +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; @@ -538,7 +539,9 @@ public final class WorkbookEvaluator { if (!stack.isEmpty()) { throw new IllegalStateException("evaluation stack not empty"); } - ValueEval result = dereferenceResult(value, ec.getRowIndex(), ec.getColumnIndex()); + + ValueEval result = dereferenceResult(value, ec); + if (dbgEvaluationOutputIndent > 0) { EVAL_LOG.log(POILogger.INFO, dbgIndentStr + "finshed eval of " + new CellReference(ec.getRowIndex(), ec.getColumnIndex()).formatAsString() @@ -574,6 +577,31 @@ public final class WorkbookEvaluator { } return index-startIndex; } + + /** + * Dereferences a single value from any AreaEval or RefEval evaluation + * result. If the supplied evaluationResult is just a plain value, it is + * returned as-is. + * + * @return a {@link NumberEval}, {@link StringEval}, {@link BoolEval}, or + * {@link ErrorEval}. Never null. {@link BlankEval} is + * converted to {@link NumberEval#ZERO} + */ + private static ValueEval dereferenceResult(ValueEval evaluationResult, OperationEvaluationContext ec) { + ValueEval value; + + EvaluationSheet evalSheet = ec.getWorkbook().getSheet(ec.getSheetIndex()); + EvaluationCell evalCell = evalSheet.getCell(ec.getRowIndex(), ec.getColumnIndex()); + + if (evalCell.isPartOfArrayFormulaGroup() && evaluationResult instanceof AreaEval) { + value = OperandResolver.getElementFromArray((AreaEval) evaluationResult, evalCell); + } + else { + value = dereferenceResult(evaluationResult, ec.getRowIndex(), ec.getColumnIndex()); + } + + return value; + } /** * Dereferences a single value from any AreaEval or RefEval evaluation @@ -667,6 +695,11 @@ public final class WorkbookEvaluator { AreaPtg aptg = (AreaPtg) ptg; return ec.getAreaEval(aptg.getFirstRow(), aptg.getFirstColumn(), aptg.getLastRow(), aptg.getLastColumn()); } + + if (ptg instanceof ArrayPtg) { + ArrayPtg aptg = (ArrayPtg) ptg; + return ec.getAreaValueEval(0, 0, aptg.getRowCount() - 1, aptg.getColumnCount() - 1, aptg.getTokenArrayValues()); + } if (ptg instanceof UnknownPtg) { // POI uses UnknownPtg when the encoded Ptg array seems to be corrupted. diff --git a/src/java/org/apache/poi/ss/formula/eval/FunctionEval.java b/src/java/org/apache/poi/ss/formula/eval/FunctionEval.java index d2a4d39..f2cee51 100644 --- a/src/java/org/apache/poi/ss/formula/eval/FunctionEval.java +++ b/src/java/org/apache/poi/ss/formula/eval/FunctionEval.java @@ -145,6 +145,7 @@ public final class FunctionEval { retval[82] = TextFunction.SEARCH; // 83: TRANSPOSE + retval[83] = MatrixFunction.TRANSPOSE; // 86: TYPE @@ -182,6 +183,10 @@ public final class FunctionEval { retval[FunctionID.INDIRECT] = null; // Indirect.evaluate has different signature retval[162] = TextFunction.CLEAN; + + retval[163] = MatrixFunction.MDETERM; + retval[164] = MatrixFunction.MINVERSE; + retval[165] = MatrixFunction.MMULT; retval[167] = new IPMT(); retval[168] = new PPMT(); diff --git a/src/java/org/apache/poi/ss/formula/eval/OperandResolver.java b/src/java/org/apache/poi/ss/formula/eval/OperandResolver.java index d2b899d..5c46886 100644 --- a/src/java/org/apache/poi/ss/formula/eval/OperandResolver.java +++ b/src/java/org/apache/poi/ss/formula/eval/OperandResolver.java @@ -17,6 +17,9 @@ package org.apache.poi.ss.formula.eval; +import org.apache.poi.ss.formula.EvaluationCell; +import org.apache.poi.ss.util.CellRangeAddress; +import org.apache.poi.ss.formula.eval.ErrorEval; import java.util.regex.Pattern; /** @@ -70,6 +73,40 @@ public final class OperandResolver { } return result; } + + /** + * Retrieves a single value from an area evaluation utilizing the 2D indices of the cell + * within its own area reference to index the value in the area evaluation. + * + * @param ae area reference after evaluation + * @param cell the source cell of the formula that contains its 2D indices + * @return a NumberEval, StringEval, BoolEval or BlankEval. or ErrorEval + * Never null. + */ + + public static ValueEval getElementFromArray(AreaEval ae, EvaluationCell cell) { + CellRangeAddress range = cell.getArrayFormulaRange(); + int relativeRowIndex = cell.getRowIndex() - range.getFirstRow(); + int relativeColIndex = cell.getColumnIndex() - range.getFirstColumn(); + //System.out.println("Row: " + relativeRowIndex + " Col: " + relativeColIndex); + + if (ae.isColumn()) { + if (ae.isRow()) { + return ae.getRelativeValue(0, 0); + } + else if(relativeRowIndex < ae.getHeight()) { + return ae.getRelativeValue(relativeRowIndex, 0); + } + } + else if (!ae.isRow() && relativeRowIndex < ae.getHeight() && relativeColIndex < ae.getWidth()) { + return ae.getRelativeValue(relativeRowIndex, relativeColIndex); + } + else if (ae.isRow() && relativeColIndex < ae.getWidth()) { + return ae.getRelativeValue(0, relativeColIndex); + } + + return ErrorEval.NA; + } /** * Implements (some perhaps not well known) Excel functionality to select a single cell from an diff --git a/src/java/org/apache/poi/ss/formula/eval/TwoOperandNumericOperation.java b/src/java/org/apache/poi/ss/formula/eval/TwoOperandNumericOperation.java index a4c05d9..3e9b551 100644 --- a/src/java/org/apache/poi/ss/formula/eval/TwoOperandNumericOperation.java +++ b/src/java/org/apache/poi/ss/formula/eval/TwoOperandNumericOperation.java @@ -17,18 +17,29 @@ package org.apache.poi.ss.formula.eval; +import org.apache.poi.ss.formula.functions.ArrayFunction; import org.apache.poi.ss.formula.functions.Fixed2ArgFunction; import org.apache.poi.ss.formula.functions.Function; +import org.apache.poi.ss.formula.functions.MatrixFunction.MutableValueCollector; +import org.apache.poi.ss.formula.functions.MatrixFunction.TwoArrayArg; /** * @author Josh Micich */ -public abstract class TwoOperandNumericOperation extends Fixed2ArgFunction { +public abstract class TwoOperandNumericOperation extends Fixed2ArgFunction implements ArrayFunction { protected final double singleOperandEvaluate(ValueEval arg, int srcCellRow, int srcCellCol) throws EvaluationException { ValueEval ve = OperandResolver.getSingleValue(arg, srcCellRow, srcCellCol); return OperandResolver.coerceValueToDouble(ve); } + + public ValueEval evaluateArray(ValueEval args[], int srcRowIndex, int srcColumnIndex) { + if (args.length != 2) { + return ErrorEval.VALUE_INVALID; + } + return new ArrayEval().evaluate(srcRowIndex, srcColumnIndex, args[0], args[1]); + } + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1) { double result; try { @@ -52,6 +63,29 @@ public abstract class TwoOperandNumericOperation extends Fixed2ArgFunction { protected abstract double evaluate(double d0, double d1) throws EvaluationException; + private final class ArrayEval extends TwoArrayArg { + private final MutableValueCollector instance = new MutableValueCollector(false, true); + + protected double[] collectValues(ValueEval arg) throws EvaluationException { + return instance.collectValues(arg); + } + + protected double[][] evaluate(double[][] d1, double[][] d2) throws IllegalArgumentException, EvaluationException { + int width = (d1[0].length < d2[0].length) ? d1[0].length : d2[0].length; + int height = (d1.length < d2.length) ? d1.length : d2.length; + + double result[][] = new double[height][width]; + + for (int j = 0; j < height; j++) { + for (int i = 0; i < width; i++) { + result[j][i] = TwoOperandNumericOperation.this.evaluate(d1[j][i], d2[j][i]); + } + } + + return result; + } + } + public static final Function AddEval = new TwoOperandNumericOperation() { protected double evaluate(double d0, double d1) { return d0+d1; diff --git a/src/java/org/apache/poi/ss/formula/eval/forked/ForkedEvaluationCell.java b/src/java/org/apache/poi/ss/formula/eval/forked/ForkedEvaluationCell.java index db01b06..0d86657 100644 --- a/src/java/org/apache/poi/ss/formula/eval/forked/ForkedEvaluationCell.java +++ b/src/java/org/apache/poi/ss/formula/eval/forked/ForkedEvaluationCell.java @@ -27,6 +27,7 @@ import org.apache.poi.ss.formula.eval.StringEval; import org.apache.poi.ss.formula.eval.ValueEval; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellType; +import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.util.Internal; /** @@ -155,6 +156,16 @@ final class ForkedEvaluationCell implements EvaluationCell { public int getColumnIndex() { return _masterCell.getColumnIndex(); } + + @Override + public CellRangeAddress getArrayFormulaRange() { + return _masterCell.getArrayFormulaRange(); + } + + @Override + public boolean isPartOfArrayFormulaGroup() { + return _masterCell.isPartOfArrayFormulaGroup(); + } /** * Will return {@link CellType} in a future version of POI. * For forwards compatibility, do not hard-code cell type literals in your code. diff --git a/src/java/org/apache/poi/ss/formula/functions/ArrayFunction.java b/src/java/org/apache/poi/ss/formula/functions/ArrayFunction.java new file mode 100644 index 0000000..3e864e5 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/functions/ArrayFunction.java @@ -0,0 +1,44 @@ +/* ==================================================================== + Licensed to the Apache Software Foundation (ASF) under one or more + contributor license agreements. See the NOTICE file distributed with + this work for additional information regarding copyright ownership. + The ASF licenses this file to You under the Apache License, Version 2.0 + (the "License"); you may not use this file except in compliance with + the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. +==================================================================== */ + +package org.apache.poi.ss.formula.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; + +/** + * @author Robert Hulbert + * Common Interface for any excel built-in function that has implemented array formula functionality. + */ + +public interface ArrayFunction { + + /** + * @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 evaluateArray(ValueEval[] args, int srcRowIndex, int srcColumnIndex); +} diff --git a/src/java/org/apache/poi/ss/formula/functions/MatrixFunction.java b/src/java/org/apache/poi/ss/formula/functions/MatrixFunction.java new file mode 100644 index 0000000..4038774 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/functions/MatrixFunction.java @@ -0,0 +1,339 @@ +/* ==================================================================== + 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.CacheAreaEval; +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.OperandResolver; +import org.apache.poi.ss.formula.eval.ValueEval; +import org.apache.commons.math3.exception.DimensionMismatchException; +import org.apache.commons.math3.linear.Array2DRowRealMatrix; +import org.apache.commons.math3.linear.LUDecomposition; +import org.apache.commons.math3.linear.MatrixUtils; + +/** + * @author Robert Hulbert + */ +public abstract class MatrixFunction implements Function{ + + public static final void checkValues(double[] results) throws EvaluationException { + for (int idx = 0; idx < results.length; idx++) { + if (Double.isNaN(results[idx]) || Double.isInfinite(results[idx])) { + throw new EvaluationException(ErrorEval.NUM_ERROR); + } + } + } + + protected final double singleOperandEvaluate(ValueEval arg, int srcCellRow, int srcCellCol) throws EvaluationException { + ValueEval ve = OperandResolver.getSingleValue(arg, srcCellRow, srcCellCol); + return OperandResolver.coerceValueToDouble(ve); + } + + /* converts 1D array to 2D array for calculations */ + private static double[][] fillDoubleArray(double[] vector, int rows, int cols) throws EvaluationException { + int i = 0, j = 0; + + if (rows < 1 || cols < 1 || vector.length < 1) { + throw new EvaluationException(ErrorEval.VALUE_INVALID); + } + + double[][] matrix = new double[rows][cols]; + + for (int idx = 0; idx < vector.length; idx++) { + if (j < matrix.length) { + if (i == matrix[0].length) { + i = 0; + j++; + } + matrix[j][i++] = vector[idx]; + } + } + + return matrix; + } + + /* retrieves 1D array from 2D array after calculations */ + private static double[] extractDoubleArray(double[][] matrix) throws EvaluationException { + int idx = 0; + + if (matrix == null || matrix.length < 1 || matrix[0].length < 1) { + throw new EvaluationException(ErrorEval.VALUE_INVALID); + } + + double[] vector = new double[matrix.length * matrix[0].length]; + + for (int j = 0; j < matrix.length; j++) { + for (int i = 0; i < matrix[0].length; i++) { + vector[idx++] = matrix[j][i]; + } + } + return vector; + } + + public static abstract class OneArrayArg extends Fixed1ArgFunction { + protected OneArrayArg() { + //no fields to initialize + } + + @Override + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0) { + if (arg0 instanceof AreaEval) { + double result[] = null, resultArray[][]; + int width = 1, height = 1; + + try { + double values[] = collectValues(arg0); + double array[][] = fillDoubleArray(values,((AreaEval) arg0).getHeight(),((AreaEval) arg0).getWidth()); + resultArray = evaluate(array); + width = resultArray[0].length; + height = resultArray.length; + result = extractDoubleArray(resultArray); + + checkValues(result); + } + catch(EvaluationException e){ + return e.getErrorEval(); + } + + ValueEval vals[] = new ValueEval[result.length]; + + for (int idx = 0; idx < result.length; idx++) { + vals[idx] = new NumberEval(result[idx]); + } + + if (result.length == 1) { + return vals[0]; + } + else { + /* find a better solution */ + return new CacheAreaEval(((AreaEval) arg0).getFirstRow(), ((AreaEval) arg0).getFirstColumn(), + ((AreaEval) arg0).getFirstRow() + height - 1, + ((AreaEval) arg0).getFirstColumn() + width - 1, vals); + } + } + else { + double result[][] = null; + try { + double value = NumericFunction.singleOperandEvaluate(arg0, srcRowIndex, srcColumnIndex); + double temp[][] = {{value}}; + result = evaluate(temp); + NumericFunction.checkValue(result[0][0]); + } + catch (EvaluationException e) { + return e.getErrorEval(); + } + + return new NumberEval(result[0][0]); + } + } + + protected abstract double[][] evaluate(double[][] d1) throws EvaluationException; + protected abstract double[] collectValues(ValueEval arg) throws EvaluationException; + } + + public static abstract class TwoArrayArg extends Fixed2ArgFunction { + protected TwoArrayArg() { + //no fields to initialize + } + + @Override + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1) { + double result[]; + int width = 1, height = 1; + + try { + double array0[][], array1[][], resultArray[][]; + + if (arg0 instanceof AreaEval) { + try { + double values[] = collectValues(arg0); + array0 = fillDoubleArray(values, ((AreaEval) arg0).getHeight(), ((AreaEval) arg0).getWidth()); + } + catch(EvaluationException e) { + return e.getErrorEval(); + } + } + else { + try { + double value = NumericFunction.singleOperandEvaluate(arg0, srcRowIndex, srcColumnIndex); + array0 = new double[][] {{value}}; + } + catch (EvaluationException e) { + return e.getErrorEval(); + } + } + + if (arg1 instanceof AreaEval) { + try { + double values[] = collectValues(arg1); + array1 = fillDoubleArray(values, ((AreaEval) arg1).getHeight(),((AreaEval) arg1).getWidth()); + } + catch (EvaluationException e) { + return e.getErrorEval(); + } + } + else { + try { + double value = NumericFunction.singleOperandEvaluate(arg1, srcRowIndex, srcColumnIndex); + array1 = new double[][] {{value}}; + } + catch (EvaluationException e) { + return e.getErrorEval(); + } + } + + resultArray = evaluate(array0, array1); + width = resultArray[0].length; + height = resultArray.length; + result = extractDoubleArray(resultArray); + checkValues(result); + } + catch (EvaluationException e) { + return e.getErrorEval(); + } + catch (IllegalArgumentException e) { + return ErrorEval.VALUE_INVALID; + } + + + ValueEval vals[] = new ValueEval[result.length]; + + for (int idx = 0; idx < result.length; idx++) { + vals[idx] = new NumberEval(result[idx]); + } + + if (result.length == 1) + return vals[0]; + else { + return new CacheAreaEval(((AreaEval) arg0).getFirstRow(), ((AreaEval) arg0).getFirstColumn(), + ((AreaEval) arg0).getFirstRow() + height - 1, + ((AreaEval) arg0).getFirstColumn() + width - 1, vals); + } + + } + + protected abstract double[][] evaluate(double[][] d1, double[][] d2) throws EvaluationException; + protected abstract double[] collectValues(ValueEval arg) throws EvaluationException; + + } + + public static final class MutableValueCollector extends MultiOperandNumericFunction { + public MutableValueCollector(boolean isReferenceBoolCounted, boolean isBlankCounted) { + super(isReferenceBoolCounted, isBlankCounted); + } + public double[] collectValues(ValueEval...operands) throws EvaluationException { + return getNumberArray(operands); + } + protected double evaluate(double[] values) { + throw new IllegalStateException("should not be called"); + } + } + + public static final Function MINVERSE = new OneArrayArg() { + private final MutableValueCollector instance = new MutableValueCollector(false, false); + + protected double[] collectValues(ValueEval arg) throws EvaluationException { + double[] values = instance.collectValues(arg); + + /* handle case where MDETERM is operating on an array that that is not completely filled*/ + if (arg instanceof AreaEval && values.length == 1) + throw new EvaluationException(ErrorEval.VALUE_INVALID); + + return values; + } + + protected double[][] evaluate(double[][] d1) throws EvaluationException { + if (d1.length != d1[0].length) { + throw new EvaluationException(ErrorEval.VALUE_INVALID); + } + + Array2DRowRealMatrix temp = new Array2DRowRealMatrix(d1); + return MatrixUtils.inverse(temp).getData(); + } + }; + + public static final Function TRANSPOSE = new OneArrayArg() { + private final MutableValueCollector instance = new MutableValueCollector(false, true); + + protected double[] collectValues(ValueEval arg) throws EvaluationException { + return instance.collectValues(arg); + } + + protected double[][] evaluate(double[][] d1) throws EvaluationException { + + Array2DRowRealMatrix temp = new Array2DRowRealMatrix(d1); + return temp.transpose().getData(); + } + }; + + public static final Function MDETERM = new OneArrayArg() { + private final MutableValueCollector instance = new MutableValueCollector(false, false); + + protected double[] collectValues(ValueEval arg) throws EvaluationException { + double[] values = instance.collectValues(arg); + + /* handle case where MDETERM is operating on an array that that is not completely filled*/ + if (arg instanceof AreaEval && values.length == 1) + throw new EvaluationException(ErrorEval.VALUE_INVALID); + + return instance.collectValues(arg); + } + + protected double[][] evaluate(double[][] d1) throws EvaluationException { + if (d1.length != d1[0].length) { + throw new EvaluationException(ErrorEval.VALUE_INVALID); + } + + double result[][] = new double[1][1]; + Array2DRowRealMatrix temp = new Array2DRowRealMatrix(d1); + result[0][0] = (new LUDecomposition(temp)).getDeterminant(); + return result; + } + }; + + public static final Function MMULT = new TwoArrayArg() { + private final MutableValueCollector instance = new MutableValueCollector(false, false); + + protected double[] collectValues(ValueEval arg) throws EvaluationException { + double values[] = instance.collectValues(arg); + + /* handle case where MMULT is operating on an array that is not completely filled*/ + if (arg instanceof AreaEval && values.length == 1) + throw new EvaluationException(ErrorEval.VALUE_INVALID); + + return values; + } + + protected double[][] evaluate(double[][] d1, double[][] d2) throws EvaluationException{ + Array2DRowRealMatrix first = new Array2DRowRealMatrix(d1); + Array2DRowRealMatrix second = new Array2DRowRealMatrix(d2); + + try { + MatrixUtils.checkMultiplicationCompatible(first, second); + } + catch (DimensionMismatchException e) { + throw new EvaluationException(ErrorEval.VALUE_INVALID); + } + + return first.multiply(second).getData(); + } + }; +} diff --git a/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFEvaluationCell.java b/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFEvaluationCell.java index 8c11e8b..f31ed71 100644 --- a/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFEvaluationCell.java +++ b/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFEvaluationCell.java @@ -20,6 +20,7 @@ package org.apache.poi.xssf.streaming; import org.apache.poi.ss.formula.EvaluationCell; import org.apache.poi.ss.formula.EvaluationSheet; import org.apache.poi.ss.usermodel.CellType; +import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.util.Internal; /** @@ -96,6 +97,17 @@ final class SXSSFEvaluationCell implements EvaluationCell { public String getStringCellValue() { return _cell.getRichStringCellValue().getString(); } + + @Override + public CellRangeAddress getArrayFormulaRange() { + return _cell.getArrayFormulaRange(); + } + + @Override + public boolean isPartOfArrayFormulaGroup() { + return _cell.isPartOfArrayFormulaGroup(); + } + /** * Will return {@link CellType} in a future version of POI. * For forwards compatibility, do not hard-code cell type literals in your code. diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java index a39fd47..17e06ac 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java @@ -483,9 +483,12 @@ public final class XSSFCell implements Cell { } CTCellFormula f = _cell.getF(); - if (isPartOfArrayFormulaGroup() && f == null) { - XSSFCell cell = getSheet().getFirstCellInArrayFormula(this); - return cell.getCellFormula(fpb); + if (isPartOfArrayFormulaGroup()) { + /* In an excel generated array formula, the formula property might be set, but the string is empty in slave cells */ + if (f == null || f.getStringValue().isEmpty()) { + XSSFCell cell = getSheet().getFirstCellInArrayFormula(this); + return cell.getCellFormula(fpb); + } } if (f.getT() == STCellFormulaType.SHARED) { return convertSharedFormula((int)f.getSi(), fpb == null ? XSSFEvaluationWorkbook.create(getSheet().getWorkbook()) : fpb); diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationCell.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationCell.java index 129052e..6a903d6 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationCell.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationCell.java @@ -20,6 +20,7 @@ package org.apache.poi.xssf.usermodel; import org.apache.poi.ss.formula.EvaluationCell; import org.apache.poi.ss.formula.EvaluationSheet; import org.apache.poi.ss.usermodel.CellType; +import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.util.Internal; /** @@ -97,6 +98,17 @@ final class XSSFEvaluationCell implements EvaluationCell { public String getStringCellValue() { return _cell.getRichStringCellValue().getString(); } + + @Override + public CellRangeAddress getArrayFormulaRange() { + return _cell.getArrayFormulaRange(); + } + + @Override + public boolean isPartOfArrayFormulaGroup() { + return _cell.isPartOfArrayFormulaGroup(); + } + /** * Will return {@link CellType} in a future version of POI. * For forwards compatibility, do not hard-code cell type literals in your code. diff --git a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestMatrixFormulasFromSpreadsheet.java b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestMatrixFormulasFromSpreadsheet.java new file mode 100644 index 0000000..53cfc45 --- /dev/null +++ b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestMatrixFormulasFromSpreadsheet.java @@ -0,0 +1,226 @@ +package org.apache.poi.xssf.usermodel; + +import static org.junit.Assert.assertEquals; +import static org.junit.Assert.assertNotNull; +import static org.junit.Assert.fail; + +import java.util.ArrayList; +import java.util.Collection; +import java.util.List; +import java.util.Locale; + + +import org.apache.poi.ss.formula.eval.ErrorEval; +import org.apache.poi.ss.formula.functions.TestMathX; +import org.apache.poi.ss.usermodel.Cell; +import org.apache.poi.ss.usermodel.CellType; +import org.apache.poi.ss.usermodel.CellValue; +import org.apache.poi.ss.usermodel.FormulaEvaluator; +import org.apache.poi.ss.usermodel.Row; +import org.apache.poi.ss.usermodel.Sheet; +import org.apache.poi.util.LocaleUtil; +import org.apache.poi.xssf.XSSFTestDataSamples; +import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator; +import org.apache.poi.xssf.usermodel.XSSFWorkbook; +import org.junit.AfterClass; +import org.junit.Test; +import org.junit.runner.RunWith; +import org.junit.runners.Parameterized; +import org.junit.runners.Parameterized.Parameter; +import org.junit.runners.Parameterized.Parameters; + +import junit.framework.AssertionFailedError; + +@RunWith(Parameterized.class) +public final class TestMatrixFormulasFromSpreadsheet { + + private static XSSFWorkbook workbook; + private static Sheet sheet; + private static FormulaEvaluator evaluator; + private static Locale userLocale; + + /* + * Unlike TestFormulaFromSpreadsheet which this class is modified from, there is no + * differentiation between operators and functions, if more functionality is implemented with + * array formulas then it might be worth it to separate operators from functions + * + * Also, output matrices are statically 3x3, if larger matrices wanted to be tested + * then adding matrix size parameter would be useful and parsing would be based off that. + */ + + private static interface Navigator { + /** + * Name of the test spreadsheet (found in the standard test data folder) + */ + String FILENAME = "MatrixFormulaEvalTestData.xlsx"; + /** + * Row (zero-based) in the spreadsheet where operations start + */ + int START_OPERATORS_ROW_INDEX = 1; + /** + * Column (zero-based) in the spreadsheet where operations start + */ + int START_OPERATORS_COL_INDEX = 0; + /** + * Column (zero-based) in the spreadsheet where evaluations start + */ + int START_RESULT_COL_INDEX = 7; + /** + * Column separation in the spreadsheet between evaluations and expected results + */ + int COL_OFF_EXPECTED_RESULT = 3; + /** + * Row separation in the spreadsheet between operations + */ + int ROW_OFF_NEXT_OP = 4; + /** + * Used to indicate when there are no more operations left + */ + String END_OF_TESTS = ""; + + } + + /* Parameters for test case */ + @Parameter(0) + public String targetFunctionName; + @Parameter(1) + public int formulasRowIdx; + + @AfterClass + public static void closeResource() throws Exception { + LocaleUtil.setUserLocale(userLocale); + workbook.close(); + } + + /* generating parameter instances */ + @Parameters(name="{0}") + public static Collection data() throws Exception { + // Function "Text" uses custom-formats which are locale specific + // can't set the locale on a per-testrun execution, as some settings have been + // already set, when we would try to change the locale by then + userLocale = LocaleUtil.getUserLocale(); + LocaleUtil.setUserLocale(Locale.ROOT); + + workbook = XSSFTestDataSamples.openSampleWorkbook(Navigator.FILENAME); + sheet = workbook.getSheetAt(0); + evaluator = new XSSFFormulaEvaluator(workbook); + + List data = new ArrayList(); + + processFunctionGroup(data, Navigator.START_OPERATORS_ROW_INDEX, null); + + return data; + } + + /** + * @param startRowIndex row index in the spreadsheet where the first function/operator is found + * @param testFocusFunctionName name of a single function/operator to test alone. + * Typically pass null to test all functions + */ + private static void processFunctionGroup(List data, int startRowIndex, String testFocusFunctionName) { + for (int rowIndex = startRowIndex; true; rowIndex += Navigator.ROW_OFF_NEXT_OP) { + Row r = sheet.getRow(rowIndex); + String targetFunctionName = getTargetFunctionName(r); + assertNotNull("Test spreadsheet cell empty on row (" + + (rowIndex) + "). Expected function name or '" + + Navigator.END_OF_TESTS + "'", targetFunctionName); + if(targetFunctionName.equals(Navigator.END_OF_TESTS)) { + // found end of functions list + break; + } + if(testFocusFunctionName == null || targetFunctionName.equalsIgnoreCase(testFocusFunctionName)) { + data.add(new Object[]{targetFunctionName, rowIndex}); + } + } + } + + @Test + public void processFunctionRow() { + + int endColNum = Navigator.START_RESULT_COL_INDEX + Navigator.COL_OFF_EXPECTED_RESULT; + + for (int rowNum = formulasRowIdx; rowNum < formulasRowIdx + Navigator.ROW_OFF_NEXT_OP - 1; rowNum++) { + for (int colNum = Navigator.START_RESULT_COL_INDEX; colNum < endColNum; colNum++) { + Row r = sheet.getRow(rowNum); + + /* mainly to escape row failures on MDETERM which only returns a scalar */ + if (r == null) { + continue; + } + + Cell c = sheet.getRow(rowNum).getCell(colNum); + + if (c == null || c.getCellTypeEnum() != CellType.FORMULA) { + continue; + } + + CellValue actValue = evaluator.evaluate(c); + Cell expValue = sheet.getRow(rowNum).getCell(colNum + Navigator.COL_OFF_EXPECTED_RESULT); + + String msg = String.format(Locale.ROOT, "Function '%s': Formula: %s @ %d:%d" + , targetFunctionName, c.getCellFormula(), rowNum, colNum); + + assertNotNull(msg + " - Bad setup data expected value is null", expValue); + assertNotNull(msg + " - actual value was null", actValue); + + final CellType cellType = expValue.getCellTypeEnum(); + switch (cellType) { + case BLANK: + assertEquals(msg, CellType.BLANK, actValue.getCellTypeEnum()); + break; + case BOOLEAN: + assertEquals(msg, CellType.BOOLEAN, actValue.getCellTypeEnum()); + assertEquals(msg, expValue.getBooleanCellValue(), actValue.getBooleanValue()); + break; + case ERROR: + assertEquals(msg, CellType.ERROR, actValue.getCellTypeEnum()); + assertEquals(msg, ErrorEval.getText(expValue.getErrorCellValue()), ErrorEval.getText(actValue.getErrorValue())); + break; + case FORMULA: // will never be used, since we will call method after formula evaluation + fail("Cannot expect formula as result of formula evaluation: " + msg); + case NUMERIC: + assertEquals(msg, CellType.NUMERIC, actValue.getCellTypeEnum()); + TestMathX.assertEquals(msg, expValue.getNumericCellValue(), actValue.getNumberValue(), TestMathX.POS_ZERO, TestMathX.DIFF_TOLERANCE_FACTOR); + break; + case STRING: + assertEquals(msg, CellType.STRING, actValue.getCellTypeEnum()); + assertEquals(msg, expValue.getRichStringCellValue().getString(), actValue.getStringValue()); + break; + default: + fail("Unexpected cell type: " + cellType); + } + } + } + } + + /** + * @return null if cell is missing, empty or blank + */ + private static String getTargetFunctionName(Row r) { + if(r == null) { + System.err.println("Warning - given null row, can't figure out function name"); + return null; + } + Cell cell = r.getCell(Navigator.START_OPERATORS_COL_INDEX); + System.err.println(String.valueOf(Navigator.START_OPERATORS_COL_INDEX)); + if(cell == null) { + System.err.println("Warning - Row " + r.getRowNum() + " has no cell " + Navigator.START_OPERATORS_COL_INDEX + ", can't figure out function name"); + return null; + } + if(cell.getCellTypeEnum() == CellType.BLANK) { + return null; + } + if(cell.getCellTypeEnum() == CellType.STRING) { + return cell.getRichStringCellValue().getString(); + } + + throw new AssertionFailedError("Bad cell type for 'function name' column: (" + + cell.getCellTypeEnum() + ") row (" + (r.getRowNum() +1) + ")"); + } + + + + + + +}