private static object Subtotal(List <Expression> p)
{
var fId = (int)(Double)p[0];
var tally = new Tally(p.Skip(1));
switch (fId)
{
case 1:
return(tally.Average());
case 2:
return(tally.Count(true));
case 3:
return(tally.Count(false));
case 4:
return(tally.Max());
case 5:
return(tally.Min());
case 6:
return(tally.Product());
case 7:
return(tally.Std());
case 8:
return(tally.StdP());
case 9:
return(tally.Sum());
case 10:
return(tally.Var());
case 11:
return(tally.VarP());
default:
throw new ArgumentException("Function not supported.");
}
}
private static object SumIf(List <Expression> p)
{
// get parameters
var range = p[0] as IEnumerable; // range of values to match the criteria against
var sumRange = p.Count < 3 ?
p[0] as XObjectExpression :
p[2] as XObjectExpression; // range of values to sum up
var criteria = p[1].Evaluate(); // the criteria to evaluate
// build list of values in range and sumRange
var rangeValues = new List <object>();
foreach (var value in range)
{
rangeValues.Add(value);
}
var sumRangeValues = new List <object>();
foreach (var value in sumRange)
{
sumRangeValues.Add(value);
}
// compute total
var ce = new CalcEngine();
var tally = new Tally();
for (var i = 0; i < Math.Max(rangeValues.Count, sumRangeValues.Count); i++)
{
var targetValue = i < rangeValues.Count ? rangeValues[i] : string.Empty;
if (CalcEngineHelpers.ValueSatisfiesCriteria(targetValue, criteria, ce))
{
var value = i < sumRangeValues.Count ? sumRangeValues[i] : 0d;
tally.AddValue(value);
}
}
// done
return(tally.Sum());
}
private static object SumIf(List <Expression> p)
{
// get parameters
var range = p[0] as IEnumerable;
var sumRange = p.Count < 3 ? range : p[2] as IEnumerable;
var criteria = p[1].Evaluate();
// build list of values in range and sumRange
var rangeValues = new List <object>();
foreach (var value in range)
{
rangeValues.Add(value);
}
var sumRangeValues = new List <object>();
foreach (var value in sumRange)
{
sumRangeValues.Add(value);
}
// compute total
var ce = new CalcEngine();
var tally = new Tally();
for (var i = 0; i < Math.Min(rangeValues.Count, sumRangeValues.Count); i++)
{
if (ValueSatisfiesCriteria(rangeValues[i], criteria, ce))
{
tally.AddValue(sumRangeValues[i]);
}
}
// done
return(tally.Sum());
}
private static object SumSq(List <Expression> p)
{
var t = new Tally(p);
return(t.Numerics().Sum(v => Math.Pow(v, 2)));
}
private static object SumIfs(List <Expression> p)
{
// get parameters
var sumRange = p[0] as IEnumerable;
var sumRangeValues = new List <object>();
foreach (var value in sumRange)
{
sumRangeValues.Add(value);
}
var ce = new CalcEngine();
var tally = new Tally();
int numberOfCriteria = p.Count / 2; // int division returns floor() automatically, that's what we want.
// prepare criteria-parameters:
var criteriaRanges = new Tuple <object, IList <object> > [numberOfCriteria];
for (int criteriaPair = 0; criteriaPair < numberOfCriteria; criteriaPair++)
{
var criteriaRange = p[criteriaPair * 2 + 1] as IEnumerable;
if (criteriaRange == null)
{
throw new CellReferenceException($"Expected parameter {criteriaPair * 2 + 2} to be a range");
}
var criterion = p[criteriaPair * 2 + 2].Evaluate();
var criteriaRangeValues = criteriaRange.Cast <Object>().ToList();
criteriaRanges[criteriaPair] = new Tuple <object, IList <object> >(
criterion,
criteriaRangeValues);
}
for (var i = 0; i < sumRangeValues.Count; i++)
{
bool shouldUseValue = true;
foreach (var criteriaPair in criteriaRanges)
{
if (!CalcEngineHelpers.ValueSatisfiesCriteria(
i < criteriaPair.Item2.Count ? criteriaPair.Item2[i] : string.Empty,
criteriaPair.Item1,
ce))
{
shouldUseValue = false;
break; // we're done with the inner loop as we can't ever get true again.
}
}
if (shouldUseValue)
{
tally.AddValue(sumRangeValues[i]);
}
}
// done
return(tally.Sum());
}
private static object Subtotal(List <Expression> p)
{
// Skip cells that already evaluate a SUBTOTAL
bool hasSubtotalInFormula(Expression e)
{
if (e is FunctionExpression fe && (fe.FunctionDefinition.Function.Method.Name == nameof(Subtotal) || fe.Parameters.Any(fp => hasSubtotalInFormula(fp))))
{
return(true);
}
if (e is BinaryExpression be)
{
return(hasSubtotalInFormula(be.LeftExpression) || hasSubtotalInFormula(be.RightExpression));
}
if (e is UnaryExpression ue)
{
return(hasSubtotalInFormula(ue.Expression));
}
return(false);
};
IEnumerable <Expression> extractExpressionsWithoutSubtotal(CellRangeReference crr)
{
var ce = crr.CalcEngine as XLCalcEngine;
return(crr.Range
.CellsUsed()
.Where(c =>
{
if (c.HasFormula)
{
var expression = ce.ExpressionCache[c.FormulaA1];
return !hasSubtotalInFormula(expression);
}
else
{
return true;
}
})
.Select(c => new XObjectExpression(new CellRangeReference(c.AsRange(), (XLCalcEngine)crr.CalcEngine)) as Expression));
};
var expressions = p.Skip(1)
.SelectMany(e =>
e is XObjectExpression xoe && xoe.Value is CellRangeReference crr
? extractExpressionsWithoutSubtotal(crr)
: new[] { e })
.ToArray();
var fId = (int)(Double)p[0];
var tally = new Tally(expressions);
switch (fId)
{
case 1:
return(tally.Average());
case 2:
return(tally.Count(true));
case 3:
return(tally.Count(false));
case 4:
return(tally.Max());
case 5:
return(tally.Min());
case 6:
return(tally.Product());
case 7:
return(tally.Std());
case 8:
return(tally.StdP());
case 9:
return(tally.Sum());
case 10:
return(tally.Var());
case 11:
return(tally.VarP());
default:
throw new ArgumentException("Function not supported.");
}
}
