private static Double getValue(AreaEval aeRange, int relRowIndex, int relColIndex) { ValueEval addend = aeRange.getRelativeValue(relRowIndex, relColIndex); if (addend instanceof NumberEval) { return ((NumberEval)addend).getNumberValue(); } // everything else (including string and boolean values) counts as zero return null; }
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 instanceof NumberEval) { return ((NumberEval)addend).getNumberValue(); } // everything else (including string and boolean values) counts as zero return 0.0; }
/** * For counts, this would return 1, for sums it returns a cell value or zero. * This is only called after all the criteria are confirmed true for the coordinates. * @param sumRange if used * @param relRowIndex * @param relColIndex * @return the aggregate input value corresponding to the given range coordinates */ private static double accumulate(AreaEval sumRange, int relRowIndex, int relColIndex) { if (sumRange == null) return 1.0; // count ValueEval addend = sumRange.getRelativeValue(relRowIndex, relColIndex); if (addend instanceof NumberEval) { return ((NumberEval)addend).getNumberValue(); } // everything else (including string and boolean values) counts as zero return 0.0; }
public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0) { ValueEval arg = arg0; if (arg instanceof RefEval) { // always use the first sheet RefEval re = (RefEval)arg; arg = re.getInnerValueEval(re.getFirstSheetIndex()); } else if (arg instanceof AreaEval) { // when the arg is an area, choose the top left cell arg = ((AreaEval) arg).getRelativeValue(0, 0); } if (arg instanceof StringEval) { // Text values are returned unmodified return arg; } if (arg instanceof ErrorEval) { // Error values also returned unmodified return arg; } // for all other argument types the result is empty string return StringEval.EMPTY_INSTANCE; } }
/** * 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, EvaluationCell cell) { CellRangeAddress range = cell.getArrayFormulaRange(); int relativeRowIndex = cell.getRowIndex() - range.getFirstRow(); int relativeColIndex = cell.getColumnIndex() - range.getFirstColumn(); 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; }
/** * @param sumRange the range to sum, if used (uses 1 for each match if not present) * @param ranges criteria ranges * @param predicates array of predicates, a predicate for each value in <code>ranges</code> * @return the computed value */ private static double aggregateMatchingCells(AreaEval sumRange, AreaEval[] ranges, I_MatchPredicate[] predicates) { int height = ranges[0].getHeight(); int width = ranges[0].getWidth(); double result = 0.0; for (int r = 0; r < height; r++) { for (int c = 0; c < width; c++) { boolean matches = true; for(int i = 0; i < ranges.length; i++){ AreaEval aeRange = ranges[i]; I_MatchPredicate mp = predicates[i]; // Bugs 60858 and 56420 show predicate can be null if (mp == null || !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(sumRange, r, c); } } } return result; }
private static double getScalarValue(ValueEval arg) throws EvaluationException { ValueEval eval; if (arg instanceof RefEval) { RefEval re = (RefEval) arg; if (re.getNumberOfSheets() > 1) { throw new EvaluationException(ErrorEval.VALUE_INVALID); } eval = re.getInnerValueEval(re.getFirstSheetIndex()); } else { eval = arg; } if (eval == null) { throw new RuntimeException("parameter may not be null"); } if (eval instanceof 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); } return getProductTerm(eval, true); }
for (int i = 0; i < h; i++) { for (int j = 0; j < w; j++) { ValueEval ve = ae.getRelativeValue(i, j); if (!(ve instanceof NumericValueEval)) { throw new EvaluationException(ErrorEval.VALUE_INVALID);
return ae.getRelativeValue(0, 0);
private static Double getValue(AreaEval aeRange, int relRowIndex, int relColIndex) { ValueEval addend = aeRange.getRelativeValue(relRowIndex, relColIndex); if (addend instanceof NumberEval) { return ((NumberEval)addend).getNumberValue(); } // everything else (including string and boolean values) counts as zero return null; }
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 instanceof NumberEval) { return ((NumberEval)addend).getNumberValue(); } // everything else (including string and boolean values) counts as zero return 0.0; }
private static Double getValue(AreaEval aeRange, int relRowIndex, int relColIndex) { ValueEval addend = aeRange.getRelativeValue(relRowIndex, relColIndex); if (addend instanceof NumberEval) { return ((NumberEval)addend).getNumberValue(); } // everything else (including string and boolean values) counts as zero return null; }
private static double accumulate(AreaEval aeSum, int relRowIndex, int relColIndex) { ValueEval addend = aeSum.getRelativeValue(relRowIndex, relColIndex); if (addend instanceof NumberEval) { return ((NumberEval)addend).getNumberValue(); } // everything else (including string and boolean values) counts as zero return 0.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 instanceof NumberEval) { return ((NumberEval)addend).getNumberValue(); } // everything else (including string and boolean values) counts as zero return 0.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 instanceof NumberEval) { return ((NumberEval)addend).getNumberValue(); } // everything else (including string and boolean values) counts as zero return 0.0; }
private static Double getValue(AreaEval aeRange, int relRowIndex, int relColIndex) { ValueEval addend = aeRange.getRelativeValue(relRowIndex, relColIndex); if (addend instanceof NumberEval) { return ((NumberEval)addend).getNumberValue(); } // everything else (including string and boolean values) counts as zero return null; }
private static double accumulate(AreaEval aeSum, int relRowIndex, int relColIndex) { ValueEval addend = aeSum.getRelativeValue(relRowIndex, relColIndex); if (addend instanceof NumberEval) { return ((NumberEval)addend).getNumberValue(); } // everything else (including string and boolean values) counts as zero return 0.0; }
/** * For counts, this would return 1, for sums it returns a cell value or zero. * This is only called after all the criteria are confirmed true for the coordinates. * @param sumRange if used * @param relRowIndex * @param relColIndex * @return the aggregate input value corresponding to the given range coordinates */ private static double accumulate(AreaEval sumRange, int relRowIndex, int relColIndex) { if (sumRange == null) return 1.0; // count ValueEval addend = sumRange.getRelativeValue(relRowIndex, relColIndex); if (addend instanceof NumberEval) { return ((NumberEval)addend).getNumberValue(); } // everything else (including string and boolean values) counts as zero return 0.0; }
public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0) { ValueEval arg = arg0; if (arg instanceof RefEval) { arg = ((RefEval) arg).getInnerValueEval(); } else if (arg instanceof AreaEval) { // when the arg is an area, choose the top left cell arg = ((AreaEval) arg).getRelativeValue(0, 0); } if (arg instanceof StringEval) { // Text values are returned unmodified return arg; } if (arg instanceof ErrorEval) { // Error values also returned unmodified return arg; } // for all other argument types the result is empty string return StringEval.EMPTY_INSTANCE; } }
private static double getScalarValue(ValueEval arg) throws EvaluationException { ValueEval eval; if (arg instanceof RefEval) { RefEval re = (RefEval) arg; eval = re.getInnerValueEval(); } else { eval = arg; } if (eval == null) { throw new RuntimeException("parameter may not be null"); } if (eval instanceof 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); } return getProductTerm(eval, true); }