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)));
}
