public void CalculateEmptyValuesTest()
{
var totalFunctionHelper = new TotalsFunctionHelper();
var result = totalFunctionHelper.Calculate(OfficeOpenXml.Table.PivotTable.DataFieldFunctions.Average, new List <object>());
Assert.IsNull(result);
}
public void CalculateStdDevDataFieldFunctionTest()
{
var totalFunctionHelper = new TotalsFunctionHelper();
var values = new List <object>()
{
5, 2, 1, 6, 10, 22, 7
};
var result = totalFunctionHelper.Calculate(OfficeOpenXml.Table.PivotTable.DataFieldFunctions.StdDev, values);
Assert.AreEqual(7.044078905, Math.Round((double)result, 9));
}
public void CalculateProductDataFieldFunctionTest()
{
var totalFunctionHelper = new TotalsFunctionHelper();
var values = new List <object>()
{
5, 2, 1, 6, 10, 22, 7
};
var result = totalFunctionHelper.Calculate(OfficeOpenXml.Table.PivotTable.DataFieldFunctions.Product, values);
Assert.AreEqual(92400, (double)result);
}
public void GetTokenNameValuesNullFormulaTest()
{
using (var helper = new TotalsFunctionHelper())
{
helper.AddNames(new HashSet <string> {
"Count", "Item"
});
var tokens = helper.Tokenize(null);
Assert.IsNull(tokens);
}
}
public void CalculateVarPDataFieldFunctionTest()
{
var totalFunctionHelper = new TotalsFunctionHelper();
var values = new List <object>()
{
5, 2, 1, 6, 10, 22, 7
};
var result = totalFunctionHelper.Calculate(OfficeOpenXml.Table.PivotTable.DataFieldFunctions.VarP, values);
Assert.AreEqual(42.53061224, Math.Round((double)result, 8));
}
public void GetTokenNameValuesTest()
{
using (var helper = new TotalsFunctionHelper())
{
helper.AddNames(new HashSet <string> {
"Cost", "Count"
});
var tokens = helper.Tokenize("Count*Cost");
Assert.AreEqual("Count", tokens.ElementAt(0).Value);
Assert.AreEqual("*", tokens.ElementAt(1).Value);
Assert.AreEqual("Cost", tokens.ElementAt(2).Value);
}
}
/// <summary>
/// Executes the GETPIVOTDATA function with the specified arguments.
/// </summary>
/// <param name="arguments">The arguments of the function to evaluate.</param>
/// <param name="context">The context that the function is to be evaluated in.</param>
/// <returns>The result of function evaluation as a <see cref="CompileResult"/>.</returns>
public override CompileResult Execute(IEnumerable <FunctionArgument> arguments, ParsingContext context)
{
string fieldName = arguments.ElementAt(0).Value?.ToString();
var pivotTableAddress = arguments.ElementAt(1).ValueAsRangeInfo?.Address;
if (string.IsNullOrEmpty(fieldName) || pivotTableAddress == null)
{
return(new CompileResult(eErrorType.Ref));
}
var pivotTable = context.ExcelDataProvider.GetPivotTable(pivotTableAddress);
if (pivotTable == null)
{
return(new CompileResult(eErrorType.Ref));
}
var fieldPairs = arguments.Skip(2).ToList();
if (fieldPairs.Count % 2 == 1)
{
return(new CompileResult(eErrorType.Ref));
}
// Convert arguments 2...n into field/value pairs.
var fieldValueIndices = this.ResolveFieldValuePairs(fieldPairs, pivotTable.CacheDefinition);
int fieldIndex = this.GetFieldIndex(fieldName, pivotTable);
// The field/value pairs or field was not found in the pivot table.
if (fieldValueIndices == null || fieldIndex == -1)
{
return(new CompileResult(eErrorType.Ref));
}
using (var totalsCalculator = new TotalsFunctionHelper())
{
var pageFieldIndices = pivotTable.GetPageFieldIndices();
var matchingValues = pivotTable.CacheDefinition.CacheRecords.FindMatchingValues(fieldValueIndices, null, pageFieldIndices, fieldIndex);
var dataField = pivotTable.DataFields.FirstOrDefault(d => d.Index == fieldIndex);
var subtotal = totalsCalculator.Calculate(dataField.Function, matchingValues);
if (subtotal == null)
{
return(new CompileResult(eErrorType.Ref));
}
return(new CompileResult(subtotal, DataType.Decimal));
}
}
public void EvaluateCalculatedFieldFormulaNullValue()
{
using (var helper = new TotalsFunctionHelper())
{
var fieldValues = new Dictionary <string, List <object> >
{
{ "Count", new List <object> {
1, 2, 3, 4
} },
{ "Cost", new List <object> {
5, 6, 7, 8
} }
};
helper.AddNames(new HashSet <string>(fieldValues.Keys));
var result = helper.EvaluateCalculatedFieldFormula(fieldValues, null);
Assert.AreEqual(null, result);
}
}
private double?GetCalculatedFieldTotal(PivotItemTreeNode node, ExcelPivotTable pivotTable, CacheFieldNode cacheField)
{
var totalsFunction = new TotalsFunctionHelper();
var calculatedFields = pivotTable.CacheDefinition.CacheFields.Where(f => !string.IsNullOrEmpty(f.Formula));
PivotTableDataManager.ConfigureCalculatedFields(calculatedFields, totalsFunction, pivotTable);
var fieldNameToValues = new Dictionary <string, List <object> >();
foreach (var cacheFieldName in cacheField.ReferencedCacheFieldsToIndex.Keys)
{
var values = pivotTable.CacheDefinition.CacheRecords.GetChildDataFieldValues(node.CacheRecordIndices, cacheField.ReferencedCacheFieldsToIndex[cacheFieldName]);
fieldNameToValues.Add(cacheFieldName, values);
}
var total = totalsFunction.EvaluateCalculatedFieldFormula(fieldNameToValues, cacheField.ResolvedFormula);
if (double.TryParse(total.ToString(), out var result))
{
return(result);
}
return(null);
}
public void GetTokenNameValuesWithFunctionTest()
{
using (var helper = new TotalsFunctionHelper())
{
helper.AddNames(new HashSet <string> {
"Cost", "Count", "Item"
});
var tokens = helper.Tokenize("SUM(Count)*COUNT(Cost) - Item");
Assert.AreEqual("SUM", tokens.ElementAt(0).Value);
Assert.AreEqual("(", tokens.ElementAt(1).Value);
Assert.AreEqual("Count", tokens.ElementAt(2).Value);
Assert.AreEqual(")", tokens.ElementAt(3).Value);
Assert.AreEqual("*", tokens.ElementAt(4).Value);
Assert.AreEqual("COUNT", tokens.ElementAt(5).Value);
Assert.AreEqual("(", tokens.ElementAt(6).Value);
Assert.AreEqual("Cost", tokens.ElementAt(7).Value);
Assert.AreEqual(")", tokens.ElementAt(8).Value);
Assert.AreEqual("-", tokens.ElementAt(9).Value);
Assert.AreEqual("Item", tokens.ElementAt(10).Value);
}
}
/// <summary>
/// Constructs the calculator.
/// </summary>
/// <param name="pivotTable">The pivot table to calculate against.</param>
/// <param name="dataFieldCollectionIndex">The index of the data field that the calculator is calculating.</param>
/// <param name="totalsCalculator">A <see cref="TotalsFunctionHelper"/> to calculate values with.</param>
public PercentOfParentCalculatorBase(ExcelPivotTable pivotTable, int dataFieldCollectionIndex, TotalsFunctionHelper totalsCalculator)
: base(pivotTable, dataFieldCollectionIndex, totalsCalculator)
{
}
/// <summary>
/// Create a new <see cref="RowGrandTotalHelper"/> 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 RowGrandTotalHelper(ExcelPivotTable pivotTable, PivotCellBackingData[,] backingData, TotalsFunctionHelper totalsCalculator)
: base(pivotTable, backingData, totalsCalculator)
{
this.MajorHeaderCollection = this.PivotTable.ColumnHeaders;
this.MinorHeaderCollection = this.PivotTable.RowHeaders;
}
/// <summary>
/// Constructs the calculator.
/// </summary>
/// <param name="pivotTable">The pivot table to calculate against.</param>
/// <param name="dataFieldCollectionIndex">The index of the data field that the calculator is calculating.</param>
/// <param name="totalsCalculator">A <see cref="TotalsFunctionHelper"/> to calculate values with.</param>
public NoCalculationCalcutor(ExcelPivotTable pivotTable, int dataFieldCollectionIndex, TotalsFunctionHelper totalsCalculator)
: base(pivotTable, dataFieldCollectionIndex, totalsCalculator)
{
}
/// <summary>
/// Factory method to get the appropriate class to calculate ShowDataAs values.
/// </summary>
/// <param name="showDataAs">The <see cref="ShowDataAs"/> type to get a calculator class for.</param>
/// <param name="pivotTable">The pivot table that the calculator is calculating against.</param>
/// <param name="dataFieldCollectionIndex">The index of the data field that the calculator is calculating.</param>
/// <param name="totalsCalculator">The <see cref="TotalsFunctionHelper"/> to calculate totals.</param>
/// <returns>The appropriate calculator class for the ShowDataAs value.</returns>
public static ShowDataAsCalculatorBase GetShowDataAsCalculator(ShowDataAs showDataAs, ExcelPivotTable pivotTable, int dataFieldCollectionIndex, TotalsFunctionHelper totalsCalculator)
{
switch (showDataAs)
{
case ShowDataAs.NoCalculation:
return(new NoCalculationCalcutor(pivotTable, dataFieldCollectionIndex, totalsCalculator));
case ShowDataAs.PercentOfTotal:
return(new PercentOfTotalCalculator(pivotTable, dataFieldCollectionIndex, totalsCalculator));
case ShowDataAs.PercentOfRow:
return(new PercentOfRowCalculator(pivotTable, dataFieldCollectionIndex, totalsCalculator));
case ShowDataAs.PercentOfCol:
return(new PercentOfColCalculator(pivotTable, dataFieldCollectionIndex, totalsCalculator));
case ShowDataAs.Percent:
return(new PercentOfCalculator(pivotTable, dataFieldCollectionIndex, totalsCalculator));
case ShowDataAs.PercentOfParentRow:
return(new PercentOfParentRowCalculator(pivotTable, dataFieldCollectionIndex, totalsCalculator));
case ShowDataAs.PercentOfParentCol:
return(new PercentOfParentColumnCalculator(pivotTable, dataFieldCollectionIndex, totalsCalculator));
case ShowDataAs.PercentOfParent:
return(new PercentOfParentCalculator(pivotTable, dataFieldCollectionIndex, totalsCalculator));
case ShowDataAs.PercentDiff:
case ShowDataAs.PercentOfRunningTotal:
case ShowDataAs.RankAscending:
case ShowDataAs.RankDescending:
case ShowDataAs.RunTotal:
case ShowDataAs.Index:
case ShowDataAs.Difference:
// TODO: Implement the rest of these settings. See user stories 11882..11890
throw new InvalidOperationException($"Unsupported dataField ShowDataAs setting '{showDataAs}'");
default:
throw new InvalidOperationException($"Unknown dataField ShowDataAs setting '{showDataAs}'");
}
}
/// <summary>
/// Base calculator constructor.
/// </summary>
/// <param name="pivotTable">The pivot table to calculate against.</param>
/// <param name="dataFieldCollectionIndex">The index of the data field that the calculator is calculating.</param>
public ShowDataAsCalculatorBase(ExcelPivotTable pivotTable, int dataFieldCollectionIndex, TotalsFunctionHelper totalsCalculator)
{
this.PivotTable = pivotTable;
this.DataFieldCollectionIndex = dataFieldCollectionIndex;
this.TotalsCalculator = totalsCalculator;
}
