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