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>
/// Create a new <see cref="GrandTotalHelperBase"/> object.
/// </summary>
/// <param name="pivotTable">The <see cref="ExcelPivotTable"/>.</param>
/// <param name="backingData">The data backing the pivot table.</param>
/// <param name="totalsCalculator">The calculation helper.</param>
protected GrandTotalHelperBase(ExcelPivotTable pivotTable, PivotCellBackingData[,] backingData, TotalsFunctionHelper totalsCalculator)
{
if (pivotTable == null)
{
throw new ArgumentNullException(nameof(pivotTable));
}
this.PivotTable = pivotTable;
this.BackingData = backingData;
this.TotalsCalculator = totalsCalculator;
}
/// <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);
}
}
/// <summary>
/// Gets the backing cell values for a given set of row header and column header indices and a data field.
/// </summary>
/// <param name="pivotTable">The pivot table to get backing values for.</param>
/// <param name="dataFieldCollectionIndex">The index of the data field to get backing values for.</param>
/// <param name="rowHeaderIndices">The row indices to filter values down by.</param>
/// <param name="columnHeaderIndices">The column indices to filter values down by.</param>
/// <param name="rowHeaderTotalType">The row function type to calculate values with.</param>
/// <param name="columnHeaderTotalType">The column function type to calculate values with.</param>
/// <param name="functionCalculator">The <see cref="TotalsFunctionHelper"/> to perform calculations with.</param>
/// <returns>A <see cref="PivotCellBackingData"/> containing the backing values and a calculated result.</returns>
public static PivotCellBackingData GetParentBackingCellValues(
ExcelPivotTable pivotTable,
int dataFieldCollectionIndex,
List <Tuple <int, int> > rowHeaderIndices,
List <Tuple <int, int> > columnHeaderIndices,
string rowHeaderTotalType,
string columnHeaderTotalType,
TotalsFunctionHelper functionCalculator)
{
var dataField = pivotTable.DataFields[dataFieldCollectionIndex];
var cacheField = pivotTable.CacheDefinition.CacheFields[dataField.Index];
PivotCellBackingData backingData = null;
if (string.IsNullOrEmpty(cacheField.Formula))
{
var matchingValues = pivotTable.CacheDefinition.CacheRecords.FindMatchingValues(
rowHeaderIndices,
columnHeaderIndices,
pivotTable.GetPageFieldIndices(),
dataField.Index,
pivotTable,
true);
backingData = new PivotCellBackingData(matchingValues);
}
else
{
// If a formula is present, it is a calculated field which needs to be evaluated.
var fieldNameToValues = new Dictionary <string, List <object> >();
foreach (var cacheFieldName in cacheField.ReferencedCacheFieldsToIndex.Keys)
{
var values = pivotTable.CacheDefinition.CacheRecords.FindMatchingValues(
rowHeaderIndices,
columnHeaderIndices,
pivotTable.GetPageFieldIndices(),
cacheField.ReferencedCacheFieldsToIndex[cacheFieldName],
pivotTable,
true);
fieldNameToValues.Add(cacheFieldName, values);
}
backingData = new PivotCellBackingData(fieldNameToValues, cacheField.ResolvedFormula);
}
var value = functionCalculator.CalculateCellTotal(dataField, backingData, rowHeaderTotalType, columnHeaderTotalType);
if (backingData != null)
{
backingData.Result = value;
}
return(backingData);
}
/// <summary>
/// Create a new <see cref="ColumnGrandTotalHelper"/> object.
/// </summary>
/// <param name="pivotTable">The <see cref="ExcelPivotTable"/>.</param>
/// <param name="backingData">The data backing the pivot table.</param>
/// <param name="totalsCalculator">The calculation helper.</param>
internal ColumnGrandTotalHelper(ExcelPivotTable pivotTable, PivotCellBackingData[,] backingData, TotalsFunctionHelper totalsCalculator)
: base(pivotTable, backingData, totalsCalculator)
{
this.MajorHeaderCollection = this.PivotTable.RowHeaders;
this.MinorHeaderCollection = this.PivotTable.ColumnHeaders;
}
private void WritePivotTableBodyData(PivotCellBackingData[,] backingDatas,
List <PivotCellBackingData> columnGrandTotalsValuesLists, List <PivotCellBackingData> rowGrandTotalsValuesLists,
PivotCellBackingData[] grandGrandTotalValues, TotalsFunctionHelper totalsCalculator)
{
int sheetColumn = this.PivotTable.Address.Start.Column + this.PivotTable.FirstDataCol;
for (int column = 0; column < this.PivotTable.ColumnHeaders.Count; column++)
{
var columnHeader = this.PivotTable.ColumnHeaders[column];
int sheetRow = this.PivotTable.Address.Start.Row + this.PivotTable.FirstDataRow - 1;
for (int row = 0; row < this.PivotTable.RowHeaders.Count; row++)
{
sheetRow++;
var rowHeader = this.PivotTable.RowHeaders[row];
if (rowHeader.IsGrandTotal || columnHeader.IsGrandTotal || !backingDatas[row, column].ShowValue)
{
continue;
}
var dataFieldCollectionIndex = this.PivotTable.HasRowDataFields ? rowHeader.DataFieldCollectionIndex : columnHeader.DataFieldCollectionIndex;
var dataField = this.PivotTable.DataFields[dataFieldCollectionIndex];
var showDataAsCalculator = ShowDataAsFactory.GetShowDataAsCalculator(dataField.ShowDataAs, this.PivotTable, dataFieldCollectionIndex, this.TotalsCalculator);
var value = showDataAsCalculator.CalculateBodyValue(
row, column,
backingDatas,
grandGrandTotalValues,
rowGrandTotalsValuesLists,
columnGrandTotalsValuesLists);
var cell = this.PivotTable.Worksheet.Cells[sheetRow, sheetColumn];
this.WriteCellValue(value, cell, dataField, this.PivotTable.Workbook.Styles);
}
sheetColumn++;
}
}
/// <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;
}
}
