/// <summary>
/// Updates the grand totals backing data.
/// </summary>
/// <returns>The list of values used to calcluate grand-grand totals.</returns>
public PivotCellBackingData[] UpdateGrandTotals(out List <PivotCellBackingData> grandTotalBackingData)
{
var grandTotalValueLists = new PivotCellBackingData[this.PivotTable.DataFields.Count];
var grandGrandTotalValueLists = new PivotCellBackingData[this.PivotTable.DataFields.Count];
grandTotalBackingData = new List <PivotCellBackingData>();
for (int majorIndex = 0; majorIndex < this.MajorHeaderCollection.Count; majorIndex++)
{
// Reset values lists.
for (int i = 0; i < grandTotalValueLists.Count(); i++)
{
grandTotalValueLists[i] = null;
}
var majorHeader = this.MajorHeaderCollection[majorIndex];
int dataFieldCollectionIndex = -1;
int minorIndex = 0;
for (; minorIndex < this.MinorHeaderCollection.Count; minorIndex++)
{
dataFieldCollectionIndex = this.AddMatchingValues(majorHeader, majorIndex, minorIndex,
dataFieldCollectionIndex, grandTotalValueLists, grandGrandTotalValueLists);
}
if (dataFieldCollectionIndex != -1)
{
this.CalculateBackingDataTotal(majorIndex, grandTotalValueLists, majorHeader.TotalType);
foreach (var grandTotalBackingCell in grandTotalValueLists.Where(t => t != null))
{
grandTotalBackingCell.MajorAxisIndex = majorIndex;
grandTotalBackingData.Add(grandTotalBackingCell);
}
}
}
return(grandGrandTotalValueLists);
}
/// <summary>
/// Calculates the total value of a cell.
/// </summary>
/// <param name="dataField">The datafield that the cell is calculated for.</param>
/// <param name="backingData">The data that backs the cell value.</param>
/// <param name="rowTotalType">The type of total function specified by the row used to calculate the cell.</param>
/// <param name="columnTotalType">The type of total function specified by the column used to calculate the cell.</param>
/// <returns>The calculated value.</returns>
public object CalculateCellTotal(ExcelPivotTableDataField dataField, PivotCellBackingData backingData,
string rowTotalType = null, string columnTotalType = null)
{
if (backingData == null)
{
return(null);
}
if (!string.IsNullOrEmpty(rowTotalType) && !rowTotalType.IsEquivalentTo("default"))
{
// Only calculate a value if the row and column functions match up, or if there is no column function specified.
if (string.IsNullOrEmpty(columnTotalType) || rowTotalType.IsEquivalentTo(columnTotalType))
{
// Calculate the value with rowTotalType as the function.
var function = ExcelPivotTableField.SubtotalFunctionTypeToDataFieldFunctionEnum[rowTotalType];
return(this.Calculate(function, backingData.GetBackingValues()));
}
// No value for this cell.
return(null);
}
else if (!string.IsNullOrEmpty(columnTotalType) && !columnTotalType.IsEquivalentTo("default"))
{
// We already know that the row subtotal function type is either empty or default because of the previous condition.
// Calculate the value with columnTotalType as the function.
var function = ExcelPivotTableField.SubtotalFunctionTypeToDataFieldFunctionEnum[columnTotalType];
return(this.Calculate(function, backingData.GetBackingValues()));
}
else if (string.IsNullOrEmpty(backingData.Formula))
{
return(this.Calculate(dataField.Function, backingData.GetBackingValues()));
}
else
{
return(this.EvaluateCalculatedFieldFormula(backingData.GetCalculatedCellBackingValues(), backingData.Formula));
}
}
/// <summary>
/// Updates the grand totals in the pivot table.
/// </summary>
/// <returns>The list of values used to calcluate grand-grand totals.</returns>
public PivotCellBackingData[] UpdateGrandTotals()
{
var grandTotalValueLists = new PivotCellBackingData[this.PivotTable.DataFields.Count];
var grandGrandTotalValueLists = new PivotCellBackingData[this.PivotTable.DataFields.Count];
for (int majorIndex = 0; majorIndex < this.MajorHeaderCollection.Count; majorIndex++)
{
// Reset values lists.
for (int i = 0; i < grandTotalValueLists.Count(); i++)
{
grandTotalValueLists[i] = null;
}
var majorHeader = this.MajorHeaderCollection[majorIndex];
int dataFieldCollectionIndex = -1;
int minorIndex = 0;
for (; minorIndex < this.MinorHeaderCollection.Count; minorIndex++)
{
dataFieldCollectionIndex = this.AddMatchingValues(majorHeader, majorIndex, minorIndex,
dataFieldCollectionIndex, grandTotalValueLists, grandGrandTotalValueLists);
}
if (dataFieldCollectionIndex != -1)
{
this.WriteGrandTotal(majorIndex, grandTotalValueLists);
}
}
return(grandGrandTotalValueLists);
}
private PivotCellBackingData[,] GetPivotTableBodyBackingData()
{
var backingData = new PivotCellBackingData[this.PivotTable.RowHeaders.Count(), this.PivotTable.ColumnHeaders.Count()];
int dataColumn = 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 dataRow = this.PivotTable.Address.Start.Row + this.PivotTable.FirstDataRow - 1;
for (int row = 0; row < this.PivotTable.RowHeaders.Count; row++)
{
dataRow++;
var rowHeader = this.PivotTable.RowHeaders[row];
if (rowHeader.IsGrandTotal || columnHeader.IsGrandTotal)
{
continue;
}
var dataFieldCollectionIndex = this.PivotTable.HasRowDataFields ? rowHeader.DataFieldCollectionIndex : columnHeader.DataFieldCollectionIndex;
backingData[row, column] = this.GetBodyBackingCellValues(
this.PivotTable,
dataFieldCollectionIndex,
rowHeader.UsedCacheRecordIndices,
columnHeader.UsedCacheRecordIndices,
rowHeader.TotalType,
columnHeader.TotalType,
this.TotalsCalculator);
if (rowHeader.IsPlaceHolder)
{
backingData[row, column].ShowValue = true;
}
else if ((rowHeader.CacheRecordIndices == null && columnHeader.CacheRecordIndices.Count == this.PivotTable.ColumnFields.Count) ||
rowHeader.CacheRecordIndices.Count == this.PivotTable.RowFields.Count)
{
// At a leaf node.
backingData[row, column].ShowValue = true;
}
else if (this.PivotTable.HasRowDataFields)
{
if (rowHeader.PivotTableField != null && rowHeader.PivotTableField.DefaultSubtotal && !rowHeader.TotalType.IsEquivalentTo("none"))
{
if ((rowHeader.PivotTableField != null && rowHeader.PivotTableField.SubtotalTop && !rowHeader.IsAboveDataField) ||
!string.IsNullOrEmpty(rowHeader.TotalType))
{
backingData[row, column].ShowValue = true;
}
}
}
else if (rowHeader.PivotTableField.DefaultSubtotal && !rowHeader.TotalType.IsEquivalentTo("none") &&
(rowHeader.TotalType != null || rowHeader.PivotTableField.SubtotalTop))
{
backingData[row, column].ShowValue = true;
}
}
dataColumn++;
}
return(backingData);
}
/// <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>
/// Clones the current <see cref="PivotCellBackingData"/>.
/// </summary>
/// <returns>A copy of the current <see cref="PivotCellBackingData"/> object.</returns>
public PivotCellBackingData Clone()
{
PivotCellBackingData backingData = null;
if (this.IsCalculatedCell)
{
var valuesDictionary = new Dictionary <string, List <object> >();
foreach (var keyValue in this.CalculatedCellBackingData)
{
valuesDictionary.Add(keyValue.Key, new List <object>(keyValue.Value));
}
backingData = new PivotCellBackingData(valuesDictionary, this.Formula);
}
else
{
backingData = new PivotCellBackingData(new List <object>(this.BackingData));
}
backingData.ShowValue = this.ShowValue;
return(backingData);
}
/// <summary>
/// Merges the data from the specified <paramref name="cellBackingData"/> into this <see cref="PivotCellBackingData"/>.
/// Both must be either calculated or non-calculated backing datas.
/// </summary>
/// <param name="cellBackingData">The backing data to merge.</param>
public void Merge(PivotCellBackingData cellBackingData)
{
if (this.IsCalculatedCell != cellBackingData.IsCalculatedCell)
{
throw new InvalidOperationException($"Cannot merge {nameof(PivotCellBackingData)} of different types.");
}
if (this.IsCalculatedCell)
{
if (cellBackingData.CalculatedCellBackingData != null)
{
if (this.CalculatedCellBackingData == null)
{
this.CalculatedCellBackingData = new Dictionary <string, List <object> >();
}
foreach (var fieldName in cellBackingData.CalculatedCellBackingData.Keys)
{
if (!this.CalculatedCellBackingData.ContainsKey(fieldName))
{
this.CalculatedCellBackingData.Add(fieldName, cellBackingData.CalculatedCellBackingData[fieldName]);
}
else
{
this.CalculatedCellBackingData[fieldName].AddRange(cellBackingData.CalculatedCellBackingData[fieldName]);
}
}
}
}
else
{
if (cellBackingData.BackingData != null)
{
if (this.BackingData == null)
{
this.BackingData = new List <object>();
}
this.BackingData.AddRange(cellBackingData.BackingData);
}
}
}
/// <summary> /// Updates the specified <paramref name="backingData"/> with the grand total result and corresponding cell location. /// </summary> /// <param name="index">The major index of the corresponding cell.</param> /// <param name="dataFieldCollectionIndex">The index of the data field to use the formula of.</param> /// <param name="backingData">The values to use to calculate the total.</param> protected abstract void UpdateGrandGrandTotalBackingDataTotal(int index, int dataFieldCollectionIndex, PivotCellBackingData backingData);
/// <summary>
/// Calculates and writes the value for a cell into a worksheet.
/// </summary>
/// <param name="cell">The cell to write a value into.</param>
/// <param name="dataField">The data field that the value is under.</param>
/// <param name="backingData">The data used to calculated the cell's value.</param>
/// <param name="styles">The style to apply to the cell.</param>
public void WriteCellTotal(ExcelRange cell, ExcelPivotTableDataField dataField, PivotCellBackingData backingData, ExcelStyles styles)
{
if (backingData == null)
{
return;
}
if (string.IsNullOrEmpty(backingData.Formula))
{
cell.Value = this.Calculate(dataField.Function, backingData.GetBackingValues());
}
else
{
cell.Value = this.EvaluateCalculatedFieldFormula(backingData.GetCalculatedCellBackingValues(), backingData.Formula);
}
var style = styles.NumberFormats.FirstOrDefault(n => n.NumFmtId == dataField.NumFmtId);
if (style != null)
{
cell.Style.Numberformat.Format = style.Format;
}
}
/// <summary>
/// Writes the grand total for the specified <paramref name="backingData"/> in the cell at the specified <paramref name="index"/>.
/// </summary>
/// <param name="index">The major index of the cell to write the total to.</param>
/// <param name="dataField">The data field to use the number format of.</param>
/// <param name="backingData">The values to use to calculate the total.</param>
protected override void WriteCellTotal(int index, ExcelPivotTableDataField dataField, PivotCellBackingData backingData)
{
var cell = this.PivotTable.Worksheet.Cells[this.PivotTable.Address.End.Row, index];
var styles = this.PivotTable.Worksheet.Workbook.Styles;
base.TotalsCalculator.WriteCellTotal(cell, dataField, backingData, styles);
}
/// <summary> /// Writes the grand total for the specified <paramref name="backingData"/> in the cell at the specified <paramref name="index"/>. /// </summary> /// <param name="index">The major index of the cell to write the total to.</param> /// <param name="dataField">The data field to use the number format of.</param> /// <param name="backingData">The values to use to calculate the total.</param> protected abstract void WriteCellTotal(int index, ExcelPivotTableDataField dataField, PivotCellBackingData backingData);
