public ValueEval Evaluate(ValueEval[] args, OperationEvaluationContext ec) { if (args.Length < 3 || args.Length % 2 == 0) { return ErrorEval.VALUE_INVALID; } try { AreaEval sumRange = ConvertRangeArg(args[0]); // collect pairs of ranges and criteria AreaEval[] ae = new AreaEval[(args.Length - 1) / 2]; I_MatchPredicate[] mp = new I_MatchPredicate[ae.Length]; for (int i = 1, k = 0; i < args.Length; i += 2, k++) { ae[k] = ConvertRangeArg(args[i]); mp[k] = Countif.CreateCriteriaPredicate(args[i + 1], ec.RowIndex, ec.ColumnIndex); } ValidateCriteriaRanges(ae, sumRange); double result = SumMatchingCells(ae, mp, sumRange); return new NumberEval(result); } catch (EvaluationException e) { return e.GetErrorEval(); } }
/** * @return simple rectangular {@link AreaEval} which represents the intersection of areas * <c>aeA</c> and <c>aeB</c>. If the two areas do not intersect, the result is <code>null</code>. */ private static AreaEval ResolveRange(AreaEval aeA, AreaEval aeB) { int aeAfr = aeA.FirstRow; int aeAfc = aeA.FirstColumn; int aeBlc = aeB.LastColumn; if (aeAfc > aeBlc) { return null; } int aeBfc = aeB.FirstColumn; if (aeBfc > aeA.LastColumn) { return null; } int aeBlr = aeB.LastRow; if (aeAfr > aeBlr) { return null; } int aeBfr = aeB.FirstRow; int aeAlr = aeA.LastRow; if (aeBfr > aeAlr) { return null; } int top = Math.Max(aeAfr, aeBfr); int bottom = Math.Min(aeAlr, aeBlr); int left = Math.Max(aeAfc, aeBfc); int right = Math.Min(aeA.LastColumn, aeBlc); return aeA.Offset(top - aeAfr, bottom - aeAfr, left - aeAfc, right - aeAfc); }
/** * Returns one column from an <tt>AreaEval</tt> * * @(#VALUE!) if colIndex Is negative, (#REF!) if colIndex Is too high */ private ValueVector CreateResultColumnVector(AreaEval tableArray, int rowIndex) { if (rowIndex >= tableArray.Height) { throw EvaluationException.InvalidRef(); } return LookupUtils.CreateRowVector(tableArray, rowIndex); }
/** * Returns one column from an <c>AreaEval</c> * * @(#VALUE!) if colIndex Is negative, (#REF!) if colIndex Is too high */ private ValueVector CreateResultColumnVector(AreaEval tableArray, int rowIndex) { if (rowIndex >= tableArray.Height) { throw EvaluationException.InvalidRef(); } return(LookupUtils.CreateRowVector(tableArray, rowIndex)); }
/** * Returns one column from an <tt>AreaEval</tt> * * @(#VALUE!) if colIndex Is negative, (#REF!) if colIndex Is too high */ private ValueVector CreateResultColumnVector(AreaEval tableArray, int colIndex) { if (colIndex >= tableArray.Width) { throw EvaluationException.InvalidRef(); } return(LookupUtils.CreateColumnVector(tableArray, colIndex)); }
public BaseRef(RefEval re) { _refEval = re; _areaEval = null; _firstRowIndex = re.Row; _firstColumnIndex = re.Column; _height = 1; _width = 1; }
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)); }
public BaseRef(AreaEval ae) { _refEval = null; _areaEval = ae; _firstRowIndex = ae.FirstRow; _firstColumnIndex = ae.FirstColumn; _height = ae.LastRow - ae.FirstRow + 1; _width = ae.LastColumn - ae.FirstColumn + 1; }
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 ValueVector CreateVector(AreaEval 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 InvalidOperationException("non-vector lookup or result areas not supported yet"); }
/** * @return possibly <c>ErrorEval</c>, and <c>null</c> */ private static ValueEval ChooseSingleElementFromAreaInternal(AreaEval ae, int srcCellRow, int srcCellCol) { //if (false) //{ // // this is too simplistic // if (ae.ContainsRow(srcCellRow) && ae.ContainsColumn(srcCellCol)) // { // throw new EvaluationException(ErrorEval.CIRCULAR_REF_ERROR); // } // /* // Circular references are not dealt with directly here, but it is worth noting some Issues. // ANY one of the return statements in this method could return a cell that is identical // to the one immediately being Evaluated. The evaluating cell is identified by srcCellRow, // srcCellRow AND sheet. The sheet is not available in any nearby calling method, so that's // one reason why circular references are not easy to detect here. (The sheet of the returned // cell can be obtained from ae if it is an Area3DEval.) // Another reason there's little value in attempting to detect circular references here Is // that only direct circular references could be detected. If the cycle involved two or more // cells this method could not detect it. // Logic to detect evaluation cycles of all kinds has been coded in EvaluationCycleDetector // (and HSSFFormulaEvaluator). // */ //} if (ae.IsColumn) { if (ae.IsRow) { return(ae.GetRelativeValue(0, 0)); } if (!ae.ContainsRow(srcCellRow)) { throw EvaluationException.InvalidValue(); } return(ae.GetAbsoluteValue(srcCellRow, ae.FirstColumn)); } if (!ae.IsRow) { // multi-column, multi-row area if (ae.ContainsRow(srcCellRow) && ae.ContainsColumn(srcCellCol)) { return(ae.GetAbsoluteValue(ae.FirstRow, ae.FirstColumn)); } throw EvaluationException.InvalidValue(); } if (!ae.ContainsColumn(srcCellCol)) { throw EvaluationException.InvalidValue(); } return(ae.GetAbsoluteValue(ae.FirstRow, srcCellCol)); }
/** * Verify that each <code>criteriaRanges</code> argument contains the same number of rows and columns * as the <code>sumRange</code> argument * * @throws EvaluationException if */ private void ValidateCriteriaRanges(AreaEval[] criteriaRanges, AreaEval sumRange) { foreach (AreaEval r in criteriaRanges) { if (r.Height != sumRange.Height || r.Width != sumRange.Width) { throw EvaluationException.InvalidValue(); } } }
private static Double GetValue(AreaEval aeRange, int relRowIndex, int relColIndex) { ValueEval addend = aeRange.GetRelativeValue(relRowIndex, relColIndex); if (addend is NumberEval) { return(((NumberEval)addend).NumberValue); } // everything else (including string and boolean values) counts as zero return(Double.NaN); }
private static ValueVector CreateVector(AreaEval 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 InvalidOperationException("non-vector lookup or result areas not supported yet"); }
/** * Resolve a ValueEval that's in an AreaEval. * * @param db AreaEval from which the cell to resolve is retrieved. * @param dbRow Relative row in the AreaEval. * @param dbCol Relative column in the AreaEval. * @return A ValueEval that is a NumberEval, StringEval, BoolEval, BlankEval or ErrorEval. */ private static ValueEval ResolveReference(AreaEval db, int dbRow, int dbCol) { try { return(OperandResolver.GetSingleValue(db.GetValue(dbRow, dbCol), db.FirstRow + dbRow, db.FirstColumn + dbCol)); } catch (EvaluationException e) { return(e.GetErrorEval()); } }
/** * Implements (some perhaps not well known) Excel functionality to select a single cell from an * area depending on the coordinates of the calling cell. Here is an example demonstrating * both selection from a single row area and a single column area in the same formula. * * <table border="1" cellpAdding="1" cellspacing="1" summary="sample spReadsheet"> * <tr><th> </th><th> A </th><th> B </th><th> C </th><th> D </th></tr> * <tr><th>1</th><td>15</td><td>20</td><td>25</td><td> </td></tr> * <tr><th>2</th><td> </td><td> </td><td> </td><td>200</td></tr> * <tr><th>3</th><td> </td><td> </td><td> </td><td>300</td></tr> * <tr><th>3</th><td> </td><td> </td><td> </td><td>400</td></tr> * </table> * * If the formula "=1000+A1:B1+D2:D3" is put into the 9 cells from A2 to C4, the spReadsheet * will look like this: * * <table border="1" cellpAdding="1" cellspacing="1" summary="sample spReadsheet"> * <tr><th> </th><th> A </th><th> B </th><th> C </th><th> D </th></tr> * <tr><th>1</th><td>15</td><td>20</td><td>25</td><td> </td></tr> * <tr><th>2</th><td>1215</td><td>1220</td><td>#VALUE!</td><td>200</td></tr> * <tr><th>3</th><td>1315</td><td>1320</td><td>#VALUE!</td><td>300</td></tr> * <tr><th>4</th><td>#VALUE!</td><td>#VALUE!</td><td>#VALUE!</td><td>400</td></tr> * </table> * * Note that the row area (A1:B1) does not include column C and the column area (D2:D3) does * not include row 4, so the values in C1(=25) and D4(=400) are not accessible to the formula * as written, but in the 4 cells A2:B3, the row and column selection works ok.<p/> * * The same concept is extended to references across sheets, such that even multi-row, * multi-column areas can be useful.<p/> * * Of course with carefully (or carelessly) chosen parameters, cyclic references can occur and * hence this method <b>can</b> throw a 'circular reference' EvaluationException. Note that * this method does not attempt to detect cycles. Every cell in the specified Area <c>ae</c> * has already been Evaluated prior to this method call. Any cell (or cell<b>s</b>) part of * <c>ae</c> that would incur a cyclic reference error if selected by this method, will * already have the value <c>ErrorEval.CIRCULAR_REF_ERROR</c> upon entry to this method. It * is assumed logic exists elsewhere to produce this behaviour. * * @return whatever the selected cell's Evaluated value Is. Never <c>null</c>. Never * <c>ErrorEval</c>. * @if there is a problem with indexing into the area, or if the * Evaluated cell has an error. */ public static ValueEval ChooseSingleElementFromArea(AreaEval ae, int srcCellRow, int srcCellCol) { ValueEval result = ChooseSingleElementFromAreaInternal(ae, srcCellRow, srcCellCol); if (result is ErrorEval) { throw new EvaluationException((ErrorEval)result); } return(result); }
public void TestReferenceResult() { ValueEval[] values = new ValueEval[4]; Arrays.Fill(values, NumberEval.ZERO); AreaEval arg0 = EvalFactory.CreateAreaEval("A1:B2", values); ValueEval[] args = new ValueEval[] { arg0, new NumberEval(2), new NumberEval(1), }; ValueEval ve = FUNC_INST.Evaluate(args, -1, -1); ConfirmAreaEval("A2:A2", ve); }
/** * Verify that each <code>criteriaRanges</code> argument contains the same number of rows and columns * as the <code>sumRange</code> argument * * @throws EvaluationException if */ internal static void ValidateCriteriaRanges(AreaEval[] criteriaRanges, AreaEval sumRange) { foreach (AreaEval r in criteriaRanges) { if (r.Height != sumRange.Height || r.Width != sumRange.Width) { throw EvaluationException.InvalidValue(); } } }
/** * Reads the numeric values from the row/col of the specified area - other values return the indicated missing value. */ private static double?ReadValue(AreaEval aeSum, int relRowIndex, int relColIndex) { ValueEval addend = aeSum.GetRelativeValue(relRowIndex, relColIndex); if (addend is NumberEval) { return(((NumberEval)addend).NumberValue); } // everything else (including string and boolean values) counts as zero return(null); }
/** * Verify that each <code>criteriaRanges</code> argument contains the same number of rows and columns * as the <code>avgRange</code> argument * * @throws EvaluationException if */ private void ValidateCriteriaRanges(AreaEval[] criteriaRanges, AreaEval avgRange) { foreach (AreaEval r in criteriaRanges) { if (r.Height != avgRange.Height || r.Width != avgRange.Width) { throw EvaluationException.InvalidValue(); } } }
/** * @return <c>null</c> if the supplied area is neither a single row nor a single colum */ public static ValueVector CreateVector(AreaEval ae) { if (ae.IsColumn) { return(CreateColumnVector(ae, 0)); } if (ae.IsRow) { return(CreateRowVector(ae, 0)); } return(null); }
private static AreaEval ResolveRange(AreaEval aeA, AreaEval aeB) { int aeAfr = aeA.FirstRow; int aeAfc = aeA.FirstColumn; int top = Math.Min(aeAfr, aeB.FirstRow); int bottom = Math.Max(aeA.LastRow, aeB.LastRow); int left = Math.Min(aeAfc, aeB.FirstColumn); int right = Math.Max(aeA.LastColumn, aeB.LastColumn); return(aeA.Offset(top - aeAfr, bottom - aeAfr, left - aeAfc, right - aeAfc)); }
public void TestMismatchAreaDimensions() { AreaEval aeA = EvalFactory.CreateAreaEval("A1:A3", new ValueEval[3]); AreaEval aeB = EvalFactory.CreateAreaEval("B1:D1", new ValueEval[3]); ValueEval[] args; args = new ValueEval[] { aeA, aeB, }; Assert.AreEqual(ErrorEval.VALUE_INVALID, InvokeSumproduct(args)); args = new ValueEval[] { aeA, new NumberEval(5), }; Assert.AreEqual(ErrorEval.VALUE_INVALID, InvokeSumproduct(args)); }
/** * @return a range of the same dimensions as aeRange using eval to define the top left corner. * @throws EvaluationException if eval is not a reference */ private static AreaEval CreateSumRange(ValueEval eval, AreaEval aeRange) { if (eval is AreaEval) { return(((AreaEval)eval).Offset(0, aeRange.Height - 1, 0, aeRange.Width - 1)); } if (eval is RefEval) { return(((RefEval)eval).Offset(0, aeRange.Height - 1, 0, aeRange.Width - 1)); } throw new EvaluationException(ErrorEval.VALUE_INVALID); }
public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1) { try { AreaEval reA = EvaluateRef(arg0); AreaEval reB = EvaluateRef(arg1); return(ResolveRange(reA, reB)); } catch (EvaluationException e) { return(e.GetErrorEval()); } }
public void TestOneByOneArea() { AreaEval ae = EvalFactory.CreateAreaEval("A1:A1", new ValueEval[] { new NumberEval(7), }); ValueEval[] args = { ae, new NumberEval(2), }; ValueEval result = InvokeSumproduct(args); ConfirmDouble(14D, result); }
/** * Confirms that the result is an area ref with the specified coordinates * @return <c>ve</c> cast to {@link AreaEval} if it is valid */ private static AreaEval ConfirmAreaEval(String refText, ValueEval ve) { CellRangeAddress cra = CellRangeAddress.ValueOf(refText); Assert.IsTrue(ve is AreaEval); AreaEval ae = (AreaEval)ve; Assert.AreEqual(cra.FirstRow, ae.FirstRow); Assert.AreEqual(cra.FirstColumn, ae.FirstColumn); Assert.AreEqual(cra.LastRow, ae.LastRow); Assert.AreEqual(cra.LastColumn, ae.LastColumn); return(ae); }
public void TestTildeString() { ValueEval[] values = { new StringEval("what?"), new StringEval("all*") }; AreaEval ae = EvalFactory.CreateAreaEval("A1:A2", values); ConfirmInt(1, invokeMatch(new StringEval("what~?"), ae, MATCH_EXACT)); ConfirmInt(2, invokeMatch(new StringEval("all~*"), ae, MATCH_EXACT)); }
public RowVector(AreaEval tableArray, int rowIndex) { _rowIndex = rowIndex; int _rowAbsoluteIndex = tableArray.FirstRow + rowIndex; if (!tableArray.ContainsRow(_rowAbsoluteIndex)) { int lastRowIx = tableArray.LastRow - tableArray.FirstRow; throw new ArgumentException("Specified row index (" + rowIndex + ") is outside the allowed range (0.." + lastRowIx + ")"); } _tableArray = tableArray; _size = tableArray.Width; }
public ColumnVector(AreaEval tableArray, int columnIndex) { _columnIndex = columnIndex; int _columnAbsoluteIndex = tableArray.FirstColumn + columnIndex; if (!tableArray.ContainsColumn((short)_columnAbsoluteIndex)) { int lastColIx = tableArray.LastColumn - tableArray.FirstColumn; throw new ArgumentException("Specified column index (" + columnIndex + ") is outside the allowed range (0.." + lastColIx + ")"); } _tableArray = tableArray; _size = _tableArray.Height; }
public void TestAreaWithErrorCell() { ValueEval[] aValues = { ErrorEval.REF_INVALID, null, }; AreaEval aeA = EvalFactory.CreateAreaEval("A1:A2", aValues); AreaEval aeB = EvalFactory.CreateAreaEval("B1:B2", new ValueEval[2]); ValueEval[] args = { aeA, aeB, }; Assert.AreEqual(ErrorEval.REF_INVALID, InvokeSumproduct(args)); }
private ValueEval EvaluateAreaSumProduct(ValueEval[] evalArgs) { int maxN = evalArgs.Length; AreaEval[] args = new AreaEval[maxN]; try { Array.Copy(evalArgs, 0, args, 0, maxN); } catch (Exception) { // one of the other args was not an AreaRef return(ErrorEval.VALUE_INVALID); } AreaEval firstArg = args[0]; int height = firstArg.LastRow - firstArg.FirstRow + 1; int width = firstArg.LastColumn - firstArg.FirstColumn + 1; // TODO - junit // first check dimensions if (!AreasAllSameSize(args, height, width)) { // normally this results in #VALUE!, // but errors in individual cells take precedence for (int i = 1; i < args.Length; i++) { ThrowFirstError(args[i]); } return(ErrorEval.VALUE_INVALID); } double acc = 0; for (int rrIx = 0; rrIx < height; rrIx++) { for (int rcIx = 0; rcIx < width; rcIx++) { double term = 1D; for (int n = 0; n < maxN; n++) { double val = GetProductTerm(args[n].GetRelativeValue(rrIx, rcIx), false); term *= val; } acc += term; } } return(new NumberEval(acc)); }
private static double SumMatchingCells(AreaEval aeRange, IMatchPredicate mp, AreaEval aeSum) { int height = aeRange.Height; int width = aeRange.Width; double result = 0.0D; for (int r = 0; r < height; r++) { for (int c = 0; c < width; c++) { result += Accumulate(aeRange, mp, aeSum, r, c); } } return(result); }
private static double SumMatchingCells(AreaEval aeRange, I_MatchPredicate mp, AreaEval aeSum) { int height = aeRange.Height; int width = aeRange.Width; double result = 0.0; for (int r = 0; r < height; r++) { for (int c = 0; c < width; c++) { result += Accumulate(aeRange, mp, aeSum, r, c); } } return result; }
protected ValueEval SingleOperandEvaluate(Eval eval, int srcRow, short srcCol) { ValueEval retval; if (eval is AreaEval) { AreaEval ae = (AreaEval)eval; if (ae.Contains(srcRow, srcCol)) { // circular ref! retval = ErrorEval.CIRCULAR_REF_ERROR; } else if (ae.IsRow) { if (ae.ContainsColumn(srcCol)) { ValueEval ve = ae.GetValueAt(ae.FirstRow, srcCol); ve = Xlator.AttemptXlateToNumeric(ve); retval = Xlator.AttemptXlateToNumeric(ve); } else { retval = ErrorEval.VALUE_INVALID; } } else if (ae.IsColumn) { if (ae.ContainsRow(srcRow)) { ValueEval ve = ae.GetValueAt(srcRow, ae.FirstColumn); retval = Xlator.AttemptXlateToNumeric(ve); } else { retval = ErrorEval.VALUE_INVALID; } } else { retval = ErrorEval.VALUE_INVALID; } } else { retval = Xlator.AttemptXlateToNumeric((ValueEval)eval); } return(retval); }
/** * Returns an is StringValueEval or ErrorEval or BlankEval * * @param eval * @param srcRow * @param srcCol */ protected ValueEval SingleOperandEvaluate(Eval eval, int srcRow, short srcCol) { ValueEval retval; if (eval is AreaEval) { AreaEval ae = (AreaEval)eval; if (ae.Contains(srcRow, srcCol)) { // circular ref! retval = ErrorEval.CIRCULAR_REF_ERROR; } else if (ae.IsRow) { if (ae.ContainsColumn(srcCol)) { ValueEval ve = ae.GetValue(ae.FirstRow, srcCol); retval = InternalResolveEval(eval); } else { retval = ErrorEval.NAME_INVALID; } } else if (ae.IsColumn) { if (ae.ContainsRow(srcRow)) { ValueEval ve = ae.GetValue(srcRow, ae.FirstColumn); retval = InternalResolveEval(eval); } else { retval = ErrorEval.NAME_INVALID; } } else { retval = ErrorEval.NAME_INVALID; } } else { retval = InternalResolveEval(eval); } return(retval); }
private static bool AreasAllSameSize(AreaEval[] args, int height, int width) { for (int i = 0; i < args.Length; i++) { AreaEval areaEval = args[i]; // check that height and width match if (areaEval.Height != height) { return false; } if (areaEval.Width != width) { return false; } } return true; }
/** * Implements (some perhaps not well known) Excel functionality to select a single cell from an * area depending on the coordinates of the calling cell. Here is an example demonstrating * both selection from a single row area and a single column area in the same formula. * * <table border="1" cellpAdding="1" cellspacing="1" summary="sample spReadsheet"> * <tr><th> </th><th> A </th><th> B </th><th> C </th><th> D </th></tr> * <tr><th>1</th><td>15</td><td>20</td><td>25</td><td> </td></tr> * <tr><th>2</th><td> </td><td> </td><td> </td><td>200</td></tr> * <tr><th>3</th><td> </td><td> </td><td> </td><td>300</td></tr> * <tr><th>3</th><td> </td><td> </td><td> </td><td>400</td></tr> * </table> * * If the formula "=1000+A1:B1+D2:D3" is put into the 9 cells from A2 to C4, the spReadsheet * will look like this: * * <table border="1" cellpAdding="1" cellspacing="1" summary="sample spReadsheet"> * <tr><th> </th><th> A </th><th> B </th><th> C </th><th> D </th></tr> * <tr><th>1</th><td>15</td><td>20</td><td>25</td><td> </td></tr> * <tr><th>2</th><td>1215</td><td>1220</td><td>#VALUE!</td><td>200</td></tr> * <tr><th>3</th><td>1315</td><td>1320</td><td>#VALUE!</td><td>300</td></tr> * <tr><th>4</th><td>#VALUE!</td><td>#VALUE!</td><td>#VALUE!</td><td>400</td></tr> * </table> * * Note that the row area (A1:B1) does not include column C and the column area (D2:D3) does * not include row 4, so the values in C1(=25) and D4(=400) are not accessible to the formula * as written, but in the 4 cells A2:B3, the row and column selection works ok.<p/> * * The same concept is extended to references across sheets, such that even multi-row, * multi-column areas can be useful.<p/> * * Of course with carefully (or carelessly) chosen parameters, cyclic references can occur and * hence this method <b>can</b> throw a 'circular reference' EvaluationException. Note that * this method does not attempt to detect cycles. Every cell in the specified Area <tt>ae</tt> * has already been Evaluated prior to this method call. Any cell (or cell<b>s</b>) part of * <tt>ae</tt> that would incur a cyclic reference error if selected by this method, will * already have the value <t>ErrorEval.CIRCULAR_REF_ERROR</tt> upon entry to this method. It * is assumed logic exists elsewhere to produce this behaviour. * * @return whatever the selected cell's Evaluated value Is. Never <c>null</c>. Never * <tt>ErrorEval</tt>. * @if there is a problem with indexing into the area, or if the * Evaluated cell has an error. */ public static ValueEval ChooseSingleElementFromArea(AreaEval ae, int srcCellRow, int srcCellCol) { ValueEval result = ChooseSingleElementFromAreaInternal(ae, srcCellRow, srcCellCol); if (result == null) { // This seems to be required because AreaEval.Values array may contain nulls. // perhaps that should not be allowed. result = BlankEval.instance; } if (result is ErrorEval) { throw new EvaluationException((ErrorEval)result); } return result; }
public void TestInvalidMatchType() { ValueEval[] values = { new NumberEval(4), new NumberEval(5), new NumberEval(10), new NumberEval(10), new NumberEval(25), }; AreaEval ae = EvalFactory.CreateAreaEval("A1:A5", values); ConfirmInt(2, invokeMatch(new NumberEval(5), ae, MATCH_LARGEST_LTE)); Assert.AreEqual(ErrorEval.REF_INVALID, invokeMatch(new StringEval("Ben"), ae, MATCH_INVALID), "Should return #REF! for invalid match type"); }
/** * @return the number of evaluated cells in the range that match the specified criteria */ public static int CountMatchingCellsInArea(AreaEval areaEval, I_MatchPredicate criteriaPredicate) { int result = 0; int height = areaEval.Height; int width = areaEval.Width; for (int rrIx = 0; rrIx < height; rrIx++) { for (int rcIx = 0; rcIx < width; rcIx++) { ValueEval ve = areaEval.GetRelativeValue(rrIx, rcIx); if (criteriaPredicate.Matches(ve)) { result++; } } } return result; }
/** * @return possibly <tt>ErrorEval</tt>, and <c>null</c> */ private static ValueEval ChooseSingleElementFromAreaInternal(AreaEval ae, int srcCellRow, short srcCellCol) { //if (false) //{ // // this Is too simplistic // if (ae.ContainsRow(srcCellRow) && ae.ContainsColumn(srcCellCol)) // { // throw new EvaluationException(ErrorEval.CIRCULAR_REF_ERROR); // } // /* // Circular references are not dealt with directly here, but it Is worth noting some Issues. // ANY one of the return statements in this method could return a cell that Is identical // to the one immediately being Evaluated. The evaluating cell Is identified by srcCellRow, // srcCellRow AND sheet. The sheet Is not available in any nearby calling method, so that's // one reason why circular references are not easy to detect here. (The sheet of the returned // cell can be obtained from ae if it Is an Area3DEval.) // Another reason there's little value in attempting to detect circular references here Is // that only direct circular references could be detected. If the cycle involved two or more // cells this method could not detect it. // Logic to detect evaluation cycles of all kinds has been coded in EvaluationCycleDetector // (and HSSFFormulaEvaluator). // */ //} if (ae.IsColumn) { if (ae.IsRow) { return ae.GetRelativeValue(0, 0); } if (!ae.ContainsRow(srcCellRow)) { throw EvaluationException.InvalidValue(); } return ae.GetValueAt(srcCellRow, ae.FirstColumn); } if (!ae.IsRow) { // multi-column, multi-row area if (ae.ContainsRow(srcCellRow) && ae.ContainsColumn(srcCellCol)) { return ae.GetValueAt(ae.FirstRow, ae.FirstColumn); } throw EvaluationException.InvalidValue(); } if (!ae.ContainsColumn(srcCellCol)) { throw EvaluationException.InvalidValue(); } return ae.GetValueAt(ae.FirstRow, srcCellCol); }
private static Double GetValue(AreaEval aeRange, int relRowIndex, int relColIndex) { ValueEval addend = aeRange.GetRelativeValue(relRowIndex, relColIndex); if (addend is NumberEval) { return ((NumberEval)addend).NumberValue; } // everything else (including string and boolean values) counts as zero return Double.NaN; }
public static ValueVector CreateRowVector(AreaEval tableArray, int relativeRowIndex) { return new RowVector(tableArray, relativeRowIndex); }
/** * @return <c>null</c> if the supplied area is neither a single row nor a single colum */ public static ValueVector CreateVector(AreaEval ae) { if (ae.IsColumn) { return CreateColumnVector(ae, 0); } if (ae.IsRow) { return CreateRowVector(ae, 0); } return null; }
private static ValueEval eval(int srcRowIndex, int srcColumnIndex, double arg0, AreaEval aeRange, bool descending_order) { int rank = 1; int height = aeRange.Height; int width = aeRange.Width; for (int r = 0; r < height; r++) { for (int c = 0; c < width; c++) { Double value = GetValue(aeRange, r, c); if (Double.IsNaN(value)) continue; if (descending_order && value > arg0 || !descending_order && value < arg0) { rank++; } } } return new NumberEval(rank); }
/** * @return a range of the same dimensions as aeRange using eval to define the top left corner. * @throws EvaluationException if eval is not a reference */ private static AreaEval CreateSumRange(ValueEval eval, AreaEval aeRange) { if (eval is AreaEval) { return ((AreaEval)eval).Offset(0, aeRange.Height - 1, 0, aeRange.Width - 1); } if (eval is RefEval) { return ((RefEval)eval).Offset(0, aeRange.Height - 1, 0, aeRange.Width - 1); } throw new EvaluationException(ErrorEval.VALUE_INVALID); }
private static void ThrowFirstError(AreaEval areaEval) { int height = areaEval.Height; int width = areaEval.Width; for (int rrIx = 0; rrIx < height; rrIx++) { for (int rcIx = 0; rcIx < width; rcIx++) { ValueEval ve = areaEval.GetRelativeValue(rrIx, rcIx); if (ve is ErrorEval) { throw new EvaluationException((ErrorEval)ve); } } } }
/** * Implements (some perhaps not well known) Excel functionality to select a single cell from an * area depending on the coordinates of the calling cell. Here is an example demonstrating * both selection from a single row area and a single column area in the same formula. * * <table border="1" cellpAdding="1" cellspacing="1" summary="sample spReadsheet"> * <tr><th> </th><th> A </th><th> B </th><th> C </th><th> D </th></tr> * <tr><th>1</th><td>15</td><td>20</td><td>25</td><td> </td></tr> * <tr><th>2</th><td> </td><td> </td><td> </td><td>200</td></tr> * <tr><th>3</th><td> </td><td> </td><td> </td><td>300</td></tr> * <tr><th>3</th><td> </td><td> </td><td> </td><td>400</td></tr> * </table> * * If the formula "=1000+A1:B1+D2:D3" is put into the 9 cells from A2 to C4, the spReadsheet * will look like this: * * <table border="1" cellpAdding="1" cellspacing="1" summary="sample spReadsheet"> * <tr><th> </th><th> A </th><th> B </th><th> C </th><th> D </th></tr> * <tr><th>1</th><td>15</td><td>20</td><td>25</td><td> </td></tr> * <tr><th>2</th><td>1215</td><td>1220</td><td>#VALUE!</td><td>200</td></tr> * <tr><th>3</th><td>1315</td><td>1320</td><td>#VALUE!</td><td>300</td></tr> * <tr><th>4</th><td>#VALUE!</td><td>#VALUE!</td><td>#VALUE!</td><td>400</td></tr> * </table> * * Note that the row area (A1:B1) does not include column C and the column area (D2:D3) does * not include row 4, so the values in C1(=25) and D4(=400) are not accessible to the formula * as written, but in the 4 cells A2:B3, the row and column selection works ok.<p/> * * The same concept is extended to references across sheets, such that even multi-row, * multi-column areas can be useful.<p/> * * Of course with carefully (or carelessly) chosen parameters, cyclic references can occur and * hence this method <b>can</b> throw a 'circular reference' EvaluationException. Note that * this method does not attempt to detect cycles. Every cell in the specified Area <c>ae</c> * has already been Evaluated prior to this method call. Any cell (or cell<b>s</b>) part of * <c>ae</c> that would incur a cyclic reference error if selected by this method, will * already have the value <c>ErrorEval.CIRCULAR_REF_ERROR</c> upon entry to this method. It * is assumed logic exists elsewhere to produce this behaviour. * * @return whatever the selected cell's Evaluated value Is. Never <c>null</c>. Never * <c>ErrorEval</c>. * @if there is a problem with indexing into the area, or if the * Evaluated cell has an error. */ public static ValueEval ChooseSingleElementFromArea(AreaEval ae, int srcCellRow, int srcCellCol) { ValueEval result = ChooseSingleElementFromAreaInternal(ae, srcCellRow, srcCellCol); if (result is ErrorEval) { throw new EvaluationException((ErrorEval)result); } return result; }
/** * @param colArgWasPassed <code>false</code> if the INDEX argument lIst had just 2 items * (exactly 1 comma). If anything Is passed for the <tt>column_num</tt> argument * (including {@link BlankEval} or {@link MIssingArgEval}) this parameter will be * <code>true</code>. ThIs parameter is needed because error codes are slightly * different when only 2 args are passed. */ private static ValueEval GetValueFromArea(AreaEval ae, int pRowIx, int pColumnIx, bool colArgWasPassed, int srcRowIx, int srcColIx) { bool rowArgWasEmpty = pRowIx == 0; bool colArgWasEmpty = pColumnIx == 0; int rowIx; int columnIx; // when the area ref Is a single row or a single column, // there are special rules for conversion of rowIx and columnIx if (ae.IsRow) { if (ae.IsColumn) { // single cell ref rowIx = rowArgWasEmpty ? 0 : pRowIx - 1; columnIx = colArgWasEmpty ? 0 : pColumnIx - 1; } else { if (colArgWasPassed) { rowIx = rowArgWasEmpty ? 0 : pRowIx - 1; columnIx = pColumnIx - 1; } else { // special case - row arg seems to Get used as the column index rowIx = 0; // transfer both the index value and the empty flag from 'row' to 'column': columnIx = pRowIx - 1; colArgWasEmpty = rowArgWasEmpty; } } } else if (ae.IsColumn) { if (rowArgWasEmpty) { rowIx = srcRowIx - ae.FirstRow; } else { rowIx = pRowIx - 1; } if (colArgWasEmpty) { columnIx = 0; } else { columnIx = colArgWasEmpty ? 0 : pColumnIx - 1; } } else { // ae Is an area (not single row or column) if (!colArgWasPassed) { // always an error with 2-D area refs // Note - the type of error Changes if the pRowArg is negative throw new EvaluationException(pRowIx < 0 ? ErrorEval.VALUE_INVALID : ErrorEval.REF_INVALID); } // Normal case - area ref Is 2-D, and both index args were provided // if either arg Is missing (or blank) the logic is similar to OperandResolver.getSingleValue() if (rowArgWasEmpty) { rowIx = srcRowIx - ae.FirstRow; } else { rowIx = pRowIx - 1; } if (colArgWasEmpty) { columnIx = srcColIx - ae.FirstColumn; } else { columnIx = pColumnIx - 1; } } int width = ae.Width; int height = ae.Height; // Slightly irregular logic for bounds checking errors if (!rowArgWasEmpty && rowIx >= height || !colArgWasEmpty && columnIx >= width) { // high bounds check fail gives #REF! if arg was explicitly passed throw new EvaluationException(ErrorEval.REF_INVALID); } if (rowIx < 0 || columnIx < 0 || rowIx >= height || columnIx >= width) { throw new EvaluationException(ErrorEval.VALUE_INVALID); } return ae.GetRelativeValue(rowIx, columnIx); }
private static void Confirm(int expectedValue, AreaEval ae, int row, int col) { NumberEval v = (NumberEval)ae.GetAbsoluteValue(row, col); Assert.AreEqual(expectedValue, v.NumberValue, 0.0); }
private static void ConfirmCountBlank(int expected, AreaEval range) { ValueEval[] args = { range }; double result = NumericFunctionInvoker.Invoke(new Countblank(), args); Assert.AreEqual(expected, result, 0); }
private ValueEval EvaluateAreaSumProduct(ValueEval[] evalArgs) { int maxN = evalArgs.Length; AreaEval[] args = new AreaEval[maxN]; try { Array.Copy(evalArgs, 0, args, 0, maxN); } catch (Exception) { // one of the other args was not an AreaRef return ErrorEval.VALUE_INVALID; } AreaEval firstArg = args[0]; int height = firstArg.LastRow - firstArg.FirstRow + 1; int width = firstArg.LastColumn - firstArg.FirstColumn + 1; // TODO - junit // first check dimensions if (!AreasAllSameSize(args, height, width)) { // normally this results in #VALUE!, // but errors in individual cells take precedence for (int i = 1; i < args.Length; i++) { ThrowFirstError(args[i]); } return ErrorEval.VALUE_INVALID; } double acc = 0; for (int rrIx = 0; rrIx < height; rrIx++) { for (int rcIx = 0; rcIx < width; rcIx++) { double term = 1D; for (int n = 0; n < maxN; n++) { double val = GetProductTerm(args[n].GetRelativeValue(rrIx, rcIx), false); term *= val; } acc += term; } } return new NumberEval(acc); }
public static ValueVector CreateColumnVector(AreaEval tableArray, int relativeColumnIndex) { return new ColumnVector(tableArray, relativeColumnIndex); }
public AreaValueArray(AreaEval ae) : base(ae.Width * ae.Height) { _ae = ae; _width = ae.Width; }
private static void ConfirmCountIf(int expected, AreaEval range, ValueEval criteria) { ValueEval[] args = { range, criteria, }; double result = NumericFunctionInvoker.Invoke(new Countif(), args); Assert.AreEqual(expected, result, 0); }
private static double Accumulate(AreaEval aeRange, I_MatchPredicate mp, AreaEval aeSum, int relRowIndex, int relColIndex) { if (!mp.Matches(aeRange.GetRelativeValue(relRowIndex, relColIndex))) { return 0.0; } ValueEval addend = aeSum.GetRelativeValue(relRowIndex, relColIndex); if (addend is NumberEval) { return ((NumberEval)addend).NumberValue; } // everything else (including string and boolean values) counts as zero return 0.0; }