public void TestCriteriaPredicateNe_Bug46647() { IMatchPredicate mp = Countif.CreateCriteriaPredicate(new StringEval("<>aa"), 0, 0); Assert.IsNotNull(mp); 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")); }
/** * * @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); }
/** * * @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, IMatchPredicate[] 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]; 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 += Accumulate(aeSum, r, c); } } } return(result); }
/** * @return the number of evaluated cells in the range that match the specified criteria */ public static int CountMatchingCellsInArea(ThreeDEval areaEval, IMatchPredicate criteriaPredicate) { int result = 0; for (int sIx = areaEval.FirstSheetIndex; sIx <= areaEval.LastSheetIndex; sIx++) { 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(sIx, 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); }
/** * @return 1 if the evaluated cell matches the specified criteria */ public static int CountMatchingCell(RefEval refEval, IMatchPredicate 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, IMatchPredicate criteriaPredicate) { if (criteriaPredicate.Matches(refEval.InnerValueEval)) { return(1); } return(0); }
/** * @return the number of evaluated cells in the range that match the specified criteria */ public static int CountMatchingCellsInRef(RefEval refEval, IMatchPredicate criteriaPredicate) { int result = 0; for (int sIx = refEval.FirstSheetIndex; sIx <= refEval.LastSheetIndex; sIx++) { ValueEval ve = refEval.GetInnerValueEval(sIx); if (criteriaPredicate.Matches(ve)) { result++; } } return result; }
/** * @return the number of evaluated cells in the range that match the specified criteria */ public static int CountMatchingCellsInRef(RefEval refEval, IMatchPredicate criteriaPredicate) { int result = 0; for (int sIx = refEval.FirstSheetIndex; sIx <= refEval.LastSheetIndex; sIx++) { ValueEval ve = refEval.GetInnerValueEval(sIx); if (criteriaPredicate.Matches(ve)) { result++; } } return(result); }
public static int CountArg(ValueEval eval, IMatchPredicate criteriaPredicate) { if (eval == null) { throw new ArgumentException("eval must not be null"); } if (eval is TwoDEval) { return(CountUtils.CountMatchingCellsInArea((TwoDEval)eval, criteriaPredicate)); } if (eval is RefEval) { return(CountUtils.CountMatchingCell((RefEval)eval, criteriaPredicate)); } return(criteriaPredicate.Matches(eval) ? 1 : 0); }
public static int CountArg(ValueEval eval, IMatchPredicate criteriaPredicate) { if (eval == null) { throw new ArgumentException("eval must not be null"); } if (eval is TwoDEval) { return CountUtils.CountMatchingCellsInArea((TwoDEval)eval, criteriaPredicate); } if (eval is RefEval) { return CountUtils.CountMatchingCell((RefEval)eval, criteriaPredicate); } return criteriaPredicate.Matches(eval) ? 1 : 0; }
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); }
/** * * @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); }
public static int CountArg(ValueEval eval, IMatchPredicate criteriaPredicate) { if (eval == null) { throw new ArgumentException("eval must not be null"); } if (eval is ThreeDEval) { return CountUtils.CountMatchingCellsInArea((ThreeDEval)eval, criteriaPredicate); } if (eval is TwoDEval) { throw new ArgumentException("Count requires 3D Evals, 2D ones aren't supported"); } if (eval is RefEval) { return CountUtils.CountMatchingCellsInRef((RefEval)eval, criteriaPredicate); } return criteriaPredicate.Matches(eval) ? 1 : 0; }
public static int CountArg(ValueEval eval, IMatchPredicate criteriaPredicate) { if (eval == null) { throw new ArgumentException("eval must not be null"); } if (eval is ThreeDEval) { return(CountUtils.CountMatchingCellsInArea((ThreeDEval)eval, criteriaPredicate)); } if (eval is TwoDEval) { throw new ArgumentException("Count requires 3D Evals, 2D ones aren't supported"); } if (eval is RefEval) { return(CountUtils.CountMatchingCellsInRef((RefEval)eval, criteriaPredicate)); } return(criteriaPredicate.Matches(eval) ? 1 : 0); }
private static void ConfirmPredicate(bool expectedResult, IMatchPredicate 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, IMatchPredicate matchPredicate, ErrorEval value) { Assert.AreEqual(expectedResult, matchPredicate.Matches(value)); }
/** * @return the number of evaluated cells in the range that match the specified criteria */ public static int CountMatchingCellsInArea(ThreeDEval areaEval, IMatchPredicate criteriaPredicate) { int result = 0; for (int sIx = areaEval.FirstSheetIndex; sIx <= areaEval.LastSheetIndex; sIx++) { 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(sIx, 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; }
private static void ConfirmPredicate(bool expectedResult, IMatchPredicate 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, IMatchPredicate matchPredicate, int value) { Assert.AreEqual(expectedResult, matchPredicate.Matches(new NumberEval(value))); }
private static void ConfirmPredicate(bool expectedResult, IMatchPredicate matchPredicate, ErrorEval value) { Assert.AreEqual(expectedResult, matchPredicate.Matches(value)); }
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 ConfirmPredicate(bool expectedResult, IMatchPredicate matchPredicate, int value) { Assert.AreEqual(expectedResult, matchPredicate.Matches(new NumberEval(value))); }