/** * 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 <tt>NumberEval</tt>, <tt>StringEval</tt>, <tt>BoolEval</tt> or <tt>BlankEval</tt>. or <tt>ErrorEval<tt> * Never <code>null</code>. */ public static ValueEval GetElementFromArray(AreaEval ae, IEvaluationCell cell) { CellRangeAddress range = cell.ArrayFormulaRange; int relativeRowIndex = cell.RowIndex - range.FirstRow; int relativeColIndex = cell.ColumnIndex - range.FirstColumn; if (ae.IsColumn) { if (ae.IsRow) { return(ae.GetRelativeValue(0, 0)); } else if (relativeRowIndex < ae.Height) { return(ae.GetRelativeValue(relativeRowIndex, 0)); } } else if (!ae.IsRow && relativeRowIndex < ae.Height && relativeColIndex < ae.Width) { return(ae.GetRelativeValue(relativeRowIndex, relativeColIndex)); } else if (ae.IsRow && relativeColIndex < ae.Width) { return(ae.GetRelativeValue(0, relativeColIndex)); } return(ErrorEval.NA); }
private static void CollectValues(ValueEval arg, IList temp) { if (arg is AreaEval) { AreaEval ae = (AreaEval)arg; int width = ae.Width; int height = ae.Height; for (int rrIx = 0; rrIx < height; rrIx++) { for (int rcIx = 0; rcIx < width; rcIx++) { ValueEval ve1 = ae.GetRelativeValue(rrIx, rcIx); CollectValue(ve1, temp, false); } } return; } if (arg is RefEval) { RefEval re = (RefEval)arg; CollectValue(re.InnerValueEval, temp, true); return; } CollectValue(arg, temp, true); }
private ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval lookupEval, ValueEval indexEval, ValueEval returnEval, String notFound, LookupUtils.MatchMode matchMode, LookupUtils.SearchMode searchMode, bool isSingleValue) { try { ValueEval lookupValue = OperandResolver.GetSingleValue(lookupEval, srcRowIndex, srcColumnIndex); TwoDEval tableArray = LookupUtils.ResolveTableArrayArg(indexEval); int matchedRow; try { matchedRow = LookupUtils.XlookupIndexOfValue(lookupValue, LookupUtils.CreateColumnVector(tableArray, 0), matchMode, searchMode); } catch (EvaluationException e) { if (ErrorEval.NA.Equals(e.GetErrorEval())) { if (string.IsNullOrEmpty(notFound)) { if (returnEval is AreaEval) { AreaEval area = (AreaEval)returnEval; int width = area.Width; if (isSingleValue || width <= 1) { return(new StringEval(notFound)); } return(new NotFoundAreaEval(notFound, width)); } else { return(new StringEval(notFound)); } } return(ErrorEval.NA); } else { return(e.GetErrorEval()); } } if (returnEval is AreaEval) { AreaEval area = (AreaEval)returnEval; if (isSingleValue) { return(area.GetRelativeValue(matchedRow, 0)); } return(area.Offset(matchedRow, matchedRow, 0, area.Width - 1)); } else { return(returnEval); } } catch (EvaluationException e) { return(e.GetErrorEval()); } }
/** * Collects values from a single argument */ private void CollectValues(ValueEval operand, DoubleList temp) { if (operand is AreaEval) { AreaEval ae = (AreaEval)operand; int width = ae.Width; int height = ae.Height; for (int rrIx = 0; rrIx < height; rrIx++) { for (int rcIx = 0; rcIx < width; rcIx++) { ValueEval ve = ae.GetRelativeValue(rrIx, rcIx); CollectValue(ve, true, temp); } } return; } if (operand is RefEval) { RefEval re = (RefEval)operand; CollectValue(re.InnerValueEval, true, temp); return; } CollectValue((ValueEval)operand, false, temp); }
protected override ValueEval GetItemInternal(int index) { int rowIx = index / _width; int colIx = index % _width; return(_ae.GetRelativeValue(rowIx, colIx)); }
/** * * @param ranges criteria ranges, each range must be of the same dimensions as <code>aeSum</code> * @param predicates array of predicates, a predicate for each value in <code>ranges</code> * @param aeSum the range to sum * * @return the computed value */ private static double SumMatchingCells(AreaEval[] ranges, I_MatchPredicate[] predicates, AreaEval aeSum) { int height = aeSum.Height; int width = aeSum.Width; double result = 0.0; for (int r = 0; r < height; r++) { for (int c = 0; c < width; c++) { bool matches = true; for (int i = 0; i < ranges.Length; i++) { AreaEval aeRange = ranges[i]; I_MatchPredicate mp = predicates[i]; if (!mp.Matches(aeRange.GetRelativeValue(r, c))) { matches = false; break; } } if (matches) { // sum only if all of the corresponding criteria specified are true for that cell. result += Accumulate(aeSum, r, c); } } } return(result); }
/** * * @param ranges criteria ranges, each range must be of the same dimensions as <code>aeSum</code> * @param predicates array of predicates, a predicate for each value in <code>ranges</code> * @param aeSum the range to sum * * @return the computed value */ internal static double CalcMatchingCells(AreaEval[] ranges, IMatchPredicate[] predicates, AreaEval aeSum, double initialValue, System.Func <double, double?, double> calc) { int height = aeSum.Height; int width = aeSum.Width; double result = initialValue; for (int r = 0; r < height; r++) { for (int c = 0; c < width; c++) { bool matches = true; for (int i = 0; i < ranges.Length; i++) { AreaEval aeRange = ranges[i]; IMatchPredicate mp = predicates[i]; if (!mp.Matches(aeRange.GetRelativeValue(r, c))) { matches = false; break; } } if (matches) { // sum only if all of the corresponding criteria specified are true for that cell. result = calc(result, ReadValue(aeSum, r, c)); } } } return(result); }
/** * 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 <tt>NumberEval</tt>, <tt>StringEval</tt>, <tt>BoolEval</tt>, * <tt>BlankEval</tt> or <tt>ErrorEval</tt>. Never <c>null</c>. */ private static ValueEval DereferenceValue(ValueEval evaluationResult, int srcRowNum, int srcColNum) { if (evaluationResult is RefEval) { RefEval rv = (RefEval)evaluationResult; return(rv.InnerValueEval); } if (evaluationResult is AreaEval) { AreaEval ae = (AreaEval)evaluationResult; if (ae.IsRow) { if (ae.IsColumn) { return(ae.GetRelativeValue(0, 0)); } return(ae.GetValueAt(ae.FirstRow, srcColNum)); } if (ae.IsColumn) { return(ae.GetValueAt(srcRowNum, ae.FirstColumn)); } return(ErrorEval.VALUE_INVALID); } return(evaluationResult); }
public ValueEval GetItem(int index) { if (index > _size) { throw new IndexOutOfRangeException("Specified index (" + index + ") is outside the allowed range (0.." + (_size - 1) + ")"); } return(_tableArray.GetRelativeValue(index, _columnIndex)); }
/** * @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)); }
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); }
/** * 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); }
private static double Accumulate(AreaEval aeRange, IMatchPredicate 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); }
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); } } } }
/** * * @param ranges criteria ranges, each range must be of the same dimensions as <code>aeAvg</code> * @param predicates array of predicates, a predicate for each value in <code>ranges</code> * @param aeAvg the range to calculate * * @return the computed value */ private static double GetAvgFromMatchingCells(AreaEval[] ranges, IMatchPredicate[] predicates, AreaEval aeAvg) { int height = aeAvg.Height; int width = aeAvg.Width; double sum = 0.0; int valuesCount = 0; for (int r = 0; r < height; r++) { for (int c = 0; c < width; c++) { bool matches = true; for (int i = 0; i < ranges.Length; i++) { AreaEval aeRange = ranges[i]; IMatchPredicate mp = predicates[i]; if (!mp.Matches(aeRange.GetRelativeValue(r, c))) { matches = false; break; } } if (matches) { // sum only if all of the corresponding criteria specified are true for that cell. var result = Accumulate(aeAvg, r, c); if (result == null) { continue; } sum += result.Value; valuesCount++; } } } if (valuesCount <= 0) { throw new EvaluationException(ErrorEval.VALUE_INVALID); } return(valuesCount > 0 ? (sum / valuesCount) : 0); }
/** * @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 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); }
private static double GetScalarValue(ValueEval arg) { ValueEval eval; if (arg is RefEval) { RefEval re = (RefEval)arg; if (re.NumberOfSheets > 1) { throw new EvaluationException(ErrorEval.VALUE_INVALID); } eval = re.GetInnerValueEval(re.FirstSheetIndex); } else { eval = arg; } if (eval == null) { throw new ArgumentException("parameter may not be null"); } if (eval is AreaEval) { AreaEval ae = (AreaEval)eval; // an area ref can work as a scalar value if it is 1x1 if (!ae.IsColumn || !ae.IsRow) { throw new EvaluationException(ErrorEval.VALUE_INVALID); } eval = ae.GetRelativeValue(0, 0); } if (!(eval is ValueEval)) { throw new ArgumentException("Unexpected value eval class (" + eval.GetType().Name + ")"); } return(GetProductTerm((ValueEval)eval, true)); }
/** * @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 bool Calculate(ValueEval[] args) { bool result = InitialResultValue; bool atleastOneNonBlank = false; bool?tempVe; /* * Note: no short-circuit bool loop exit because any ErrorEvals will override the result */ for (int i = 0, iSize = args.Length; i < iSize; i++) { ValueEval arg = args[i]; if (arg is AreaEval) { AreaEval ae = (AreaEval)arg; int height = ae.Height; int width = ae.Width; for (int rrIx = 0; rrIx < height; rrIx++) { for (int rcIx = 0; rcIx < width; rcIx++) { ValueEval ve = ae.GetRelativeValue(rrIx, rcIx); tempVe = OperandResolver.CoerceValueToBoolean(ve, true); if (tempVe != null) { result = PartialEvaluate(result, Convert.ToBoolean(tempVe)); atleastOneNonBlank = true; } } } continue; } if (arg is RefEval) { ValueEval ve = ((RefEval)arg).InnerValueEval; tempVe = OperandResolver.CoerceValueToBoolean(ve, true); } else if (arg is ValueEval) { ValueEval ve = (ValueEval)arg; tempVe = OperandResolver.CoerceValueToBoolean(ve, false); } else { throw new InvalidOperationException("Unexpected eval (" + arg.GetType().Name + ")"); } if (tempVe != null) { result = PartialEvaluate(result, Convert.ToBoolean(tempVe)); atleastOneNonBlank = true; } } if (!atleastOneNonBlank) { throw new EvaluationException(ErrorEval.VALUE_INVALID); } 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 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 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 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; }