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