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);
}
/// <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;
}
}
/// <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);
}
}