/**
*
* @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);
}
public ValueEval Evaluate(ValueEval[] args, OperationEvaluationContext ec)
{
if (args.Length < 3 || args.Length % 2 == 0)
{
return(ErrorEval.VALUE_INVALID);
}
try
{
AreaEval minRange = Sumifs.ConvertRangeArg(args[0]);
// collect pairs of ranges and criteria
AreaEval[] ae = new AreaEval[(args.Length - 1) / 2];
IMatchPredicate[] mp = new IMatchPredicate[ae.Length];
for (int i = 1, k = 0; i < args.Length; i += 2, k++)
{
ae[k] = Sumifs.ConvertRangeArg(args[i]);
mp[k] = Countif.CreateCriteriaPredicate(args[i + 1], ec.RowIndex, ec.ColumnIndex);
}
Sumifs.ValidateCriteriaRanges(ae, minRange);
Sumifs.ValidateCriteria(mp);
double result = Sumifs.CalcMatchingCells(ae, mp, minRange, double.NaN, (init, current) => !current.HasValue ? init : double.IsNaN(init) ? current.Value : current.Value < init ? current.Value : init);
return(new NumberEval(result));
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
}
/**
* @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;
}
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];
IMatchPredicate[] mp = new IMatchPredicate[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 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);
}
/**
*
* @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);
}
public ValueEval Evaluate(ValueEval[] args, OperationEvaluationContext ec)
{
if (args.Length < 3 || args.Length % 2 == 0)
{
return(ErrorEval.VALUE_INVALID);
}
try
{
AreaEval avgRange = ConvertRangeArg(args[0]);
// collect pairs of ranges and criteria
AreaEval[] ae = new AreaEval[(args.Length - 1) / 2];
IMatchPredicate[] mp = new IMatchPredicate[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, avgRange);
double result = GetAvgFromMatchingCells(ae, mp, avgRange);
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, 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);
}
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
IMatchPredicate mp = Countif.CreateCriteriaPredicate(arg1, srcRowIndex, srcColumnIndex);
double result = SumMatchingCells(aeRange, mp, aeSum);
return(new NumberEval(result));
}
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"));
}
public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1)
{
IMatchPredicate 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
*/
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);
}
/**
* @return the number of evaluated cells in the range that match the specified criteria
*/
private double CountMatchingCellsInArea(ValueEval rangeArg, IMatchPredicate criteriaPredicate)
{
if (rangeArg is RefEval)
{
return(CountUtils.CountMatchingCellsInRef((RefEval)rangeArg, criteriaPredicate));
}
else if (rangeArg is ThreeDEval)
{
return(CountUtils.CountMatchingCellsInArea((ThreeDEval)rangeArg, criteriaPredicate));
}
else
{
throw new ArgumentException("Bad range arg type (" + rangeArg.GetType().Name + ")");
}
}
private static double SumMatchingCells(AreaEval aeRange, IMatchPredicate 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, 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 SumMatchingCells(AreaEval aeRange, IMatchPredicate mp, AreaEval aeSum)
{
int height = aeRange.Height;
int width = aeRange.Width;
double result = 0.0D;
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, 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 ValueEval Eval(int srcRowIndex, int srcColumnIndex, ValueEval arg1, AreaEval aeRange,
AreaEval aeSum)
{
// TODO - junit to prove last arg must be srcColumnIndex and not srcRowIndex
IMatchPredicate mp = Countif.CreateCriteriaPredicate(arg1, srcRowIndex, srcColumnIndex);
// handle empty cells
if (mp == null)
{
return(NumberEval.ZERO);
}
try
{
double result = SumMatchingCells(aeRange, mp, aeSum);
return(new NumberEval(result));
}
catch (EvaluationException var)
{
return(var.GetErrorEval());
}
}
private static void ConfirmPredicate(bool expectedResult, IMatchPredicate matchPredicate, int value)
{
Assert.AreEqual(expectedResult, matchPredicate.Matches(new NumberEval(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, 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;
}
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, ErrorEval value)
{
Assert.AreEqual(expectedResult, matchPredicate.Matches(value));
}
public Counta()
{
_predicate = defaultPredicate;
}
private Counta(IMatchPredicate criteriaPredicate)
{
_predicate = criteriaPredicate;
}
/**
* @return the number of evaluated cells in the range that match the specified criteria
*/
private double CountMatchingCellsInArea(ValueEval rangeArg, IMatchPredicate criteriaPredicate)
{
if (rangeArg is RefEval)
{
return CountUtils.CountMatchingCellsInRef((RefEval)rangeArg, criteriaPredicate);
}
else if (rangeArg is ThreeDEval)
{
return CountUtils.CountMatchingCellsInArea((ThreeDEval)rangeArg, criteriaPredicate);
}
else
{
throw new ArgumentException("Bad range arg type (" + rangeArg.GetType().Name + ")");
}
}
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 void ConfirmPredicate(bool expectedResult, IMatchPredicate matchPredicate, String value)
{
ValueEval ev = (value == null) ? BlankEval.instance : (ValueEval) new StringEval(value);
Assert.AreEqual(expectedResult, matchPredicate.Matches(ev));
}
public Counta()
{
_predicate = defaultPredicate;
}
private Counta(IMatchPredicate criteriaPredicate)
{
_predicate = criteriaPredicate;
}
