private static double sumMatchingCells(AreaEval aeRange, I_MatchPredicate mp, AreaEval aeSum) { int height=aeRange.getHeight(); int width= aeRange.getWidth(); double result = 0.0; for (int r=0; r<height; r++) { for (int c=0; c<width; c++) { result += accumulate(aeRange, mp, aeSum, r, c); } } return result; }
/** * Verify that each <code>criteriaRanges</code> argument contains the same number of rows and columns * including the <code>sumRange</code> argument if present * @param sumRange if used, it must match the shape of the criteriaRanges * @param criteriaRanges to check * @throws EvaluationException if the ranges do not match. */ private static void validateCriteriaRanges(AreaEval sumRange, AreaEval[] criteriaRanges) throws EvaluationException { int h = criteriaRanges[0].getHeight(); int w = criteriaRanges[0].getWidth(); if (sumRange != null && (sumRange.getHeight() != h || sumRange.getWidth() != w) ) { throw EvaluationException.invalidValue(); } for(AreaEval r : criteriaRanges){ if(r.getHeight() != h || r.getWidth() != w ) { throw EvaluationException.invalidValue(); } } }
/** * For a given database returns the column number for a column heading. * * @param db Database. * @param name Column heading. * @return Corresponding column number. * @throws EvaluationException If it's not possible to turn all headings into strings. */ private static int getColumnForString(AreaEval db,String name) throws EvaluationException { int resultColumn = -1; final int width = db.getWidth(); for(int column = 0; column < width; ++column) { ValueEval columnNameValueEval = resolveReference(db, 0, column); if(columnNameValueEval instanceof BlankEval) { continue; } if(columnNameValueEval instanceof ErrorEval) { continue; } String columnName = OperandResolver.coerceValueToString(columnNameValueEval); if(name.equals(columnName)) { resultColumn = column; break; } } return resultColumn; }
/** * @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) throws EvaluationException { if (eval instanceof AreaEval) { return ((AreaEval) eval).offset(0, aeRange.getHeight()-1, 0, aeRange.getWidth()-1); } if (eval instanceof RefEval) { return ((RefEval)eval).offset(0, aeRange.getHeight()-1, 0, aeRange.getWidth()-1); } throw new EvaluationException(ErrorEval.VALUE_INVALID); }
private static ValueEval eval(double arg0, AreaEval aeRange, boolean descending_order) { int rank = 1; int height=aeRange.getHeight(); int width= aeRange.getWidth(); for (int r=0; r<height; r++) { for (int c=0; c<width; c++) { Double value = getValue(aeRange, r, c); if(value==null)continue; if(descending_order && value>arg0 || !descending_order && value<arg0){ rank++; } } } return new NumberEval(rank); }
/** * @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; }
int w = ae.getWidth(); int h = ae.getHeight(); ar = new double[h][w];
/** * 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; }
for(int conditionRow = 1; conditionRow < height; ++conditionRow) { boolean matches = true; final int width = cdb.getWidth(); for(int column = 0; column < width; ++column) { // columns are ANDed
double array[][] = fillDoubleArray(values,((AreaEval) arg0).getHeight(),((AreaEval) arg0).getWidth()); resultArray = evaluate(array); width = resultArray[0].length;
/** * 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) throws EvaluationException { for(AreaEval r : criteriaRanges){ if(r.getHeight() != sumRange.getHeight() || r.getWidth() != sumRange.getWidth() ) { throw EvaluationException.invalidValue(); } } }
/** * 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) throws EvaluationException { for(AreaEval r : criteriaRanges){ if(r.getHeight() != sumRange.getHeight() || r.getWidth() != sumRange.getWidth() ) { throw EvaluationException.invalidValue(); } } }
private static double sumMatchingCells(AreaEval aeRange, I_MatchPredicate mp, AreaEval aeSum) { int height=aeRange.getHeight(); int width= aeRange.getWidth(); double result = 0.0; for (int r=0; r<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.getHeight(); int width= aeRange.getWidth(); 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; }
private static double sumMatchingCells(AreaEval aeRange, I_MatchPredicate mp, AreaEval aeSum) { int height=aeRange.getHeight(); int width= aeRange.getWidth(); double result = 0.0; for (int r=0; r<height; r++) { for (int c=0; c<width; c++) { result += accumulate(aeRange, mp, aeSum, r, c); } } return result; }
/** * @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) throws EvaluationException { if (eval instanceof AreaEval) { return ((AreaEval) eval).offset(0, aeRange.getHeight()-1, 0, aeRange.getWidth()-1); } if (eval instanceof RefEval) { return ((RefEval)eval).offset(0, aeRange.getHeight()-1, 0, aeRange.getWidth()-1); } throw new EvaluationException(ErrorEval.VALUE_INVALID); }
/** * @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) throws EvaluationException { if (eval instanceof AreaEval) { return ((AreaEval) eval).offset(0, aeRange.getHeight()-1, 0, aeRange.getWidth()-1); } if (eval instanceof RefEval) { return ((RefEval)eval).offset(0, aeRange.getHeight()-1, 0, aeRange.getWidth()-1); } throw new EvaluationException(ErrorEval.VALUE_INVALID); }
/** * @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) throws EvaluationException { if (eval instanceof AreaEval) { return ((AreaEval) eval).offset(0, aeRange.getHeight()-1, 0, aeRange.getWidth()-1); } if (eval instanceof RefEval) { return ((RefEval)eval).offset(0, aeRange.getHeight()-1, 0, aeRange.getWidth()-1); } throw new EvaluationException(ErrorEval.VALUE_INVALID); }