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());
case 3:
return(tally.CountA());
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 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.");
}
}