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