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