コード例 #1
0
        private static List <Token> ResolveFormulaReferences(string formula, TotalsFunctionHelper totalsCalculator, IEnumerable <CacheFieldNode> calculatedFields)
        {
            var resolvedFormulaTokens = new List <Token>();
            var tokens = totalsCalculator.Tokenize(formula);

            foreach (var token in tokens)
            {
                if (token.TokenType == TokenType.NameValue)
                {
                    // If a token references another calculated field, resolve the chain of formulas.
                    var field = calculatedFields.FirstOrDefault(f => f.Name.IsEquivalentTo(token.Value));
                    if (field != null)
                    {
                        var resolvedReferences = PivotTableDataManager.ResolveFormulaReferences(field.Formula, totalsCalculator, calculatedFields);
                        resolvedFormulaTokens.Add(new Token("(", TokenType.OpeningParenthesis));
                        resolvedFormulaTokens.AddRange(resolvedReferences);
                        resolvedFormulaTokens.Add(new Token(")", TokenType.ClosingParenthesis));
                    }
                    else
                    {
                        resolvedFormulaTokens.Add(token);
                    }
                }
                else
                {
                    resolvedFormulaTokens.Add(token);
                }
            }
            return(resolvedFormulaTokens);
        }
コード例 #2
0
        /// <summary>
        /// Resolve the name references and other formulas contained in a formula.
        /// </summary>
        /// <param name="calculatedFields">The list of calculated fields in the pivot table.</param>
        /// <param name="totalsCalculator">The function helper calculator.</param>
        /// <param name="pivotTable">The pivot table the fields are on.</param>
        public static void ConfigureCalculatedFields(IEnumerable <CacheFieldNode> calculatedFields, TotalsFunctionHelper totalsCalculator, ExcelPivotTable pivotTable)
        {
            // Add all of the cache field names to the calculation helper.
            var cacheFieldNames = new HashSet <string>(pivotTable.CacheDefinition.CacheFields.Select(c => c.Name));

            totalsCalculator.AddNames(cacheFieldNames);

            // Resolve any calclulated fields that may be referencing each other to forumlas composed of regular ol' cache fields.
            foreach (var calculatedField in calculatedFields)
            {
                var resolvedFormulaTokens = PivotTableDataManager.ResolveFormulaReferences(calculatedField.Formula, totalsCalculator, calculatedFields);
                foreach (var token in resolvedFormulaTokens.Where(t => t.TokenType == TokenType.NameValue))
                {
                    if (!calculatedField.ReferencedCacheFieldsToIndex.ContainsKey(token.Value))
                    {
                        var referencedFieldIndex = pivotTable.CacheDefinition.GetCacheFieldIndex(token.Value);
                        calculatedField.ReferencedCacheFieldsToIndex.Add(token.Value, referencedFieldIndex);
                    }
                }
                // Reconstruct the formula and wrap all field names in single ticks.
                string resolvedFormula = string.Empty;
                foreach (var token in resolvedFormulaTokens)
                {
                    string tokenValue = token.Value;
                    if (token.TokenType == TokenType.NameValue)
                    {
                        tokenValue = $"'{tokenValue}'";
                    }
                    resolvedFormula += tokenValue;
                }
                calculatedField.ResolvedFormula = resolvedFormula;
            }
        }
コード例 #3
0
        /// <summary>
        /// Updates the pivot table's worksheet with the latest calculated data.
        /// </summary>
        public void UpdateWorksheet()
        {
            using (var totalsCalculator = new TotalsFunctionHelper())
            {
                this.TotalsCalculator = totalsCalculator;
                // If the workbook has calculated fields, configure the calculation helper and cache fields appropriately.
                var calculatedFields = this.PivotTable.CacheDefinition.CacheFields.Where(c => !string.IsNullOrEmpty(c.Formula));
                if (calculatedFields.Any())
                {
                    PivotTableDataManager.ConfigureCalculatedFields(calculatedFields, totalsCalculator, this.PivotTable);
                }

                // Generate backing body data.
                var backingBodyData = this.GetPivotTableBodyBackingData();

                // Calculate grand (and grand-grand) totals, but don't write out the values yet.
                var columnGrandTotalHelper      = new ColumnGrandTotalHelper(this.PivotTable, backingBodyData, totalsCalculator);
                var columnGrandGrandTotalsLists = columnGrandTotalHelper.UpdateGrandTotals(out var columnGrandTotalBackingData);
                var rowGrandTotalHelper         = new RowGrandTotalHelper(this.PivotTable, backingBodyData, totalsCalculator);
                rowGrandTotalHelper.UpdateGrandTotals(out var rowGrandTotalBackingData);
                if (this.PivotTable.HasRowDataFields)
                {
                    rowGrandTotalHelper.CalculateGrandGrandTotals(columnGrandGrandTotalsLists);
                }
                else
                {
                    columnGrandTotalHelper.CalculateGrandGrandTotals(columnGrandGrandTotalsLists);
                }

                // Generate row and column grand grand totals backing data
                if (this.PivotTable.ColumnGrandTotals && this.PivotTable.RowGrandTotals && this.PivotTable.ColumnFields.Any())
                {
                    // Write grand-grand totals to worksheet (grand totals at bottom right corner of pivot table).
                    this.WriteGrandGrandTotals(columnGrandGrandTotalsLists);
                }

                // Write out row and column grand grand totals.
                if (this.PivotTable.ColumnGrandTotals)
                {
                    this.WriteGrandTotalValues(false, columnGrandTotalBackingData, columnGrandGrandTotalsLists);
                }
                if (this.PivotTable.RowGrandTotals)
                {
                    this.WriteGrandTotalValues(true, rowGrandTotalBackingData, columnGrandGrandTotalsLists);
                }

                // Write out body data applying "Show Data As" and other settings as necessary.
                this.WritePivotTableBodyData(backingBodyData, columnGrandTotalBackingData,
                                             rowGrandTotalBackingData, columnGrandGrandTotalsLists, totalsCalculator);
            }
        }