/**
* @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;
}
private static void ConfirmPredicate(bool expectedResult, I_MatchPredicate matchPredicate, int value)
{
Assert.AreEqual(expectedResult, matchPredicate.Matches(new NumberEval(value)));
}
public Count()
{
_predicate = defaultPredicate;
}
private static void ConfirmPredicate(bool expectedResult, I_MatchPredicate matchPredicate, ErrorEval value)
{
Assert.AreEqual(expectedResult, matchPredicate.Matches(value));
}
/**
*
* @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;
}
