Search in sources :

Example 1 with I_MatchPredicate

use of org.apache.poi.ss.formula.functions.CountUtils.I_MatchPredicate in project poi by apache.

the class Sumif method eval.

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);
    // handle empty cells
    if (mp == null) {
        return NumberEval.ZERO;
    }
    double result = sumMatchingCells(aeRange, mp, aeSum);
    return new NumberEval(result);
}
Also used : I_MatchPredicate(org.apache.poi.ss.formula.functions.CountUtils.I_MatchPredicate) NumberEval(org.apache.poi.ss.formula.eval.NumberEval)

Example 2 with I_MatchPredicate

use of org.apache.poi.ss.formula.functions.CountUtils.I_MatchPredicate in project poi by apache.

the class Countif method evaluate.

@Override
public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1) {
    I_MatchPredicate mp = createCriteriaPredicate(arg1, srcRowIndex, srcColumnIndex);
    if (mp == null) {
        // If the criteria arg is a reference to a blank cell, countif always returns zero.
        return NumberEval.ZERO;
    }
    double result = countMatchingCellsInArea(arg0, mp);
    return new NumberEval(result);
}
Also used : I_MatchPredicate(org.apache.poi.ss.formula.functions.CountUtils.I_MatchPredicate) NumberEval(org.apache.poi.ss.formula.eval.NumberEval)

Example 3 with I_MatchPredicate

use of org.apache.poi.ss.formula.functions.CountUtils.I_MatchPredicate in project poi by apache.

the class Baseifs method evaluate.

public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) {
    final boolean hasInitialRange = hasInitialRange();
    final int firstCriteria = hasInitialRange ? 1 : 0;
    if (args.length < (2 + firstCriteria) || args.length % 2 != firstCriteria) {
        return ErrorEval.VALUE_INVALID;
    }
    try {
        AreaEval sumRange = null;
        if (hasInitialRange) {
            sumRange = convertRangeArg(args[0]);
        }
        // collect pairs of ranges and criteria
        AreaEval[] ae = new AreaEval[(args.length - firstCriteria) / 2];
        I_MatchPredicate[] mp = new I_MatchPredicate[ae.length];
        for (int i = firstCriteria, k = 0; i < args.length; i += 2, k++) {
            ae[k] = convertRangeArg(args[i]);
            mp[k] = Countif.createCriteriaPredicate(args[i + 1], ec.getRowIndex(), ec.getColumnIndex());
        }
        validateCriteriaRanges(sumRange, ae);
        validateCriteria(mp);
        double result = aggregateMatchingCells(sumRange, ae, mp);
        return new NumberEval(result);
    } catch (EvaluationException e) {
        return e.getErrorEval();
    }
}
Also used : I_MatchPredicate(org.apache.poi.ss.formula.functions.CountUtils.I_MatchPredicate) EvaluationException(org.apache.poi.ss.formula.eval.EvaluationException) AreaEval(org.apache.poi.ss.formula.eval.AreaEval) NumberEval(org.apache.poi.ss.formula.eval.NumberEval)

Example 4 with I_MatchPredicate

use of org.apache.poi.ss.formula.functions.CountUtils.I_MatchPredicate in project poi by apache.

the class Baseifs method aggregateMatchingCells.

/**
     * @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;
}
Also used : I_MatchPredicate(org.apache.poi.ss.formula.functions.CountUtils.I_MatchPredicate) AreaEval(org.apache.poi.ss.formula.eval.AreaEval)

Example 5 with I_MatchPredicate

use of org.apache.poi.ss.formula.functions.CountUtils.I_MatchPredicate in project poi by apache.

the class TestCountFuncs method testCountifEmptyStringCriteria.

public void testCountifEmptyStringCriteria() {
    I_MatchPredicate mp;
    // pred '=' matches blank cell but not empty string
    mp = createCriteriaPredicate(new StringEval("="));
    confirmPredicate(false, mp, "");
    confirmPredicate(true, mp, NULL);
    // pred '' matches both blank cell but not empty string
    mp = createCriteriaPredicate(new StringEval(""));
    confirmPredicate(true, mp, "");
    confirmPredicate(true, mp, NULL);
    // pred '<>' matches empty string but not blank cell
    mp = createCriteriaPredicate(new StringEval("<>"));
    confirmPredicate(false, mp, NULL);
    confirmPredicate(true, mp, "");
}
Also used : I_MatchPredicate(org.apache.poi.ss.formula.functions.CountUtils.I_MatchPredicate) StringEval(org.apache.poi.ss.formula.eval.StringEval)

Aggregations

I_MatchPredicate (org.apache.poi.ss.formula.functions.CountUtils.I_MatchPredicate)11 StringEval (org.apache.poi.ss.formula.eval.StringEval)7 NumberEval (org.apache.poi.ss.formula.eval.NumberEval)5 AreaEval (org.apache.poi.ss.formula.eval.AreaEval)4 ValueEval (org.apache.poi.ss.formula.eval.ValueEval)2 AssertionFailedError (junit.framework.AssertionFailedError)1 EvaluationException (org.apache.poi.ss.formula.eval.EvaluationException)1