/** * @return the number of evaluated cells in the range that match the specified criteria */ public static int CountMatchingCellsInArea(TwoDEval 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.GetValue(rrIx, rcIx); if (criteriaPredicate is I_MatchAreaPredicate) { I_MatchAreaPredicate areaPredicate = (I_MatchAreaPredicate)criteriaPredicate; if (!areaPredicate.Matches(areaEval, rrIx, rcIx)) { continue; } } if (criteriaPredicate.Matches(ve)) { result++; } } } return(result); }
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(); } }
/** * * @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); }
/** * @return the number of evaluated cells in the range that match the specified criteria */ public static int CountMatchingCellsInArea(TwoDEval 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.GetValue(rrIx, rcIx); if (criteriaPredicate is I_MatchAreaPredicate) { I_MatchAreaPredicate areaPredicate = (I_MatchAreaPredicate)criteriaPredicate; if (!areaPredicate.Matches(areaEval, rrIx, rcIx)) continue; } if (criteriaPredicate.Matches(ve)) { result++; } } } return result; }
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 1 if the evaluated cell matches the specified criteria */ public static int CountMatchingCell(RefEval refEval, I_MatchPredicate criteriaPredicate) { if (criteriaPredicate.Matches(refEval.InnerValueEval)) { return 1; } return 0; }
/** * @return 1 if the evaluated cell matches the specified criteria */ public static int CountMatchingCell(RefEval refEval, I_MatchPredicate criteriaPredicate) { if (criteriaPredicate.Matches(refEval.InnerValueEval)) { return(1); } return(0); }
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 void TestCriteriaPredicateNe_Bug46647() { I_MatchPredicate mp = Countif.CreateCriteriaPredicate(new StringEval("<>aa"), 0, 0); StringEval seA = new StringEval("aa"); // this should not match the criteria '<>aa' StringEval seB = new StringEval("bb"); // this should match if (mp.Matches(seA) && !mp.Matches(seB)) { throw new AssertionException("Identified bug 46647"); } Assert.IsFalse(mp.Matches(seA)); Assert.IsTrue(mp.Matches(seB)); // general Tests for not-equal (<>) operator AreaEval range; ValueEval[] values; values = new ValueEval[] { new StringEval("aa"), new StringEval("def"), new StringEval("aa"), new StringEval("ghi"), new StringEval("aa"), new StringEval("aa"), }; range = EvalFactory.CreateAreaEval("A1:A6", values); ConfirmCountIf(2, range, new StringEval("<>aa")); values = new ValueEval[] { new StringEval("ab"), new StringEval("aabb"), new StringEval("aa"), // match new StringEval("abb"), new StringEval("aab"), new StringEval("ba"), // match }; range = EvalFactory.CreateAreaEval("A1:A6", values); ConfirmCountIf(2, range, new StringEval("<>a*b")); values = new ValueEval[] { new NumberEval(222), new NumberEval(222), new NumberEval(111), new StringEval("aa"), new StringEval("111"), }; range = EvalFactory.CreateAreaEval("A1:A5", values); ConfirmCountIf(4, range, new StringEval("<>111")); }
public override 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)); }
/** * @return the number of evaluated cells in the range that match the specified criteria */ private double CountMatchingCellsInArea(ValueEval rangeArg, I_MatchPredicate criteriaPredicate) { if (rangeArg is RefEval) { return(CountUtils.CountMatchingCell((RefEval)rangeArg, criteriaPredicate)); } else if (rangeArg is TwoDEval) { return(CountUtils.CountMatchingCellsInArea((TwoDEval)rangeArg, criteriaPredicate)); } else { throw new ArgumentException("Bad range arg type (" + rangeArg.GetType().Name + ")"); } }
public static int CountArg(ValueEval eval, I_MatchPredicate criteriaPredicate) { if (eval == null) { throw new ArgumentException("eval must not be null"); } if (eval is AreaEval) { return(CountUtils.CountMatchingCellsInArea((AreaEval)eval, criteriaPredicate)); } if (eval is RefEval) { return(CountUtils.CountMatchingCell((RefEval)eval, criteriaPredicate)); } return(criteriaPredicate.Matches(eval) ? 1 : 0); }
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); }
public static int CountArg(ValueEval eval, I_MatchPredicate criteriaPredicate) { if (eval == null) { throw new ArgumentException("eval must not be null"); } if (eval is AreaEval) { return CountUtils.CountMatchingCellsInArea((AreaEval)eval, criteriaPredicate); } if (eval is RefEval) { return CountUtils.CountMatchingCell((RefEval)eval, criteriaPredicate); } return criteriaPredicate.Matches(eval) ? 1 : 0; }
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; }
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); }
/** * @return the number of Evaluated cells in the range that match the specified criteria */ private ValueEval CountMatchingCellsInArea(ValueEval rangeArg, I_MatchPredicate criteriaPredicate) { int result; if (rangeArg is RefEval) { result = CountUtils.CountMatchingCell((RefEval)rangeArg, criteriaPredicate); } else if (rangeArg is AreaEval) { result = CountUtils.CountMatchingCellsInArea((AreaEval)rangeArg, criteriaPredicate); } else { throw new ArgumentException("Bad range arg type (" + rangeArg.GetType().Name + ")"); } return(new NumberEval(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; }
/** * @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); }
public ValueEval Evaluate(ValueEval[] args, int srcCellRow, int srcCellCol) { switch (args.Length) { case 2: // expected break; default: // TODO - it doesn't seem to be possible to enter COUNTIF() into Excel with the wrong arg Count // perhaps this should be an exception return(ErrorEval.VALUE_INVALID); } ValueEval range = (ValueEval)args[0]; ValueEval criteriaArg = args[1]; if (criteriaArg is RefEval) { // criteria Is not a literal value, but a cell reference // for example COUNTIF(B2:D4, E1) RefEval re = (RefEval)criteriaArg; criteriaArg = re.InnerValueEval; } else { // other non literal tokens such as function calls, have been fully Evaluated // for example COUNTIF(B2:D4, COLUMN(E1)) } if (criteriaArg is BlankEval) { return(NumberEval.ZERO); } I_MatchPredicate mp = CreateCriteriaPredicate(criteriaArg); return(CountMatchingCellsInArea(range, mp)); }
/** * @return the number of evaluated cells in the range that match the specified criteria */ private double CountMatchingCellsInArea(ValueEval rangeArg, I_MatchPredicate criteriaPredicate) { if (rangeArg is RefEval) { return CountUtils.CountMatchingCell((RefEval)rangeArg, criteriaPredicate); } else if (rangeArg is TwoDEval) { return CountUtils.CountMatchingCellsInArea((TwoDEval)rangeArg, criteriaPredicate); } else { throw new ArgumentException("Bad range arg type (" + rangeArg.GetType().Name + ")"); } }
private static void ConfirmPredicate(bool expectedResult, I_MatchPredicate matchPredicate, int value) { Assert.AreEqual(expectedResult, matchPredicate.Matches(new NumberEval(value))); }
private Counta(I_MatchPredicate criteriaPredicate) { _predicate = criteriaPredicate; }
private Count(I_MatchPredicate criteriaPredicate) { _predicate = criteriaPredicate; }
private static void ConfirmPredicate(bool expectedResult, I_MatchPredicate matchPredicate, String value) { ValueEval ev = (value == null) ? BlankEval.instance : (ValueEval)new StringEval(value); Assert.AreEqual(expectedResult, matchPredicate.Matches(ev)); }
private static void ConfirmPredicate(bool expectedResult, I_MatchPredicate matchPredicate, String value) { ValueEval ev = (value == null) ? BlankEval.instance : (ValueEval) new StringEval(value); Assert.AreEqual(expectedResult, matchPredicate.Matches(ev)); }
private static void ConfirmPredicate(bool expectedResult, I_MatchPredicate matchPredicate, ErrorEval value) { Assert.AreEqual(expectedResult, matchPredicate.Matches(value)); }
/** * @return the number of Evaluated cells in the range that match the specified criteria */ private Eval CountMatchingCellsInArea(Eval rangeArg, I_MatchPredicate criteriaPredicate) { int result; if (rangeArg is RefEval) { result = CountUtils.CountMatchingCell((RefEval)rangeArg, criteriaPredicate); } else if (rangeArg is AreaEval) { result = CountUtils.CountMatchingCellsInArea((AreaEval)rangeArg, criteriaPredicate); } else { throw new ArgumentException("Bad range arg type (" + rangeArg.GetType().Name + ")"); } return new NumberEval(result); }
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; }
public Counta() { _predicate = defaultPredicate; }
public Count() { _predicate = defaultPredicate; }
/** * * @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; }