Example #1
0
        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.");
            }
        }
Example #2
0
        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.");
            }
        }