Calculating Pivot tables

Pivot Data Calculation

EPPlus from version 7.2 can calculate pivot tables to extract data from it and also to use the GetPivotData function. The calculation can use the stored pivot cache records or create the cache from source data within the workbook where the pivot table is created. To calculate and fetch data from a pivot table, EPPlus provides the following methods and properties:

Functions & properties

Method/Property	Description
ExcelPivotTable.Calculate(refresh)	Calculates the pivot table. Parameter 0 - if true the cache will be refreshed before calculating. If false the existing cache will be used. If no cache exists the cache will be created from the source range.
ExcelPivotTable.CalculatedData	Gets data from the pivot table for a data field. Use the SelectField and GetValue methods to get the desired calculated values in a fluent way.
ExcelPivotTable.GetPivotData(criteriaList, dataField)	Gets data from the pivot table for a data field. Parameter 0 is the name of the data field. Parameter 1 is a list if criterias for the row/column fields.
ExcelPivotTable.IsCalculated	Boolean flag indicating if the pivot table is calculated.

Using the ExcelPivotTable.CalculatedData property

The CalculatedData property can be used to get the calculated data from a pivot table in a fluent way. The calculated data will call the ExcelPivotTable.Calculate method if the pivot table has not been calculated before. If you have updated the source data of the pivot table always refresh the pivot table cache and call the Calculate method before fetching any calculated data.

var grandTotal = pivotTable.CalculatedData.GetValue(); //Gets the grant total from the pivot table for the first data field.
var capVerde = pt.CalculatedData.SelectField("Country", "Cape verde").GetValue(); //Gets the value for  row/column field "Country" for the first data field.
var hellenKuhlman2017Q3Tax = pt.CalculatedData.
        SelectField("Name", "Hellen Kuhlman").
        SelectField("Years", "2017").
        SelectField("Quarters", "Q3").
        GetValue("Tax");                        //Get the value for data field "Tax", for several row/column fields.

Using the ExcelPivotTable.GetPivotData method

The GetPivotData is another option to get data from the pivot table.
It works very similar to the GETPIVOTDATA worksheet function. The GetPivotData method will call the ExcelPivotTable.Calculate method if the pivot table has not been calculated before. If you have updated the source data of the pivot table always refresh the pivot table cache and call the Calculate method before fetching any calculated data.

     var dataWorksheet = package.Workbook.Worksheets["Data"];
     // Add a pivot table starting from cell A1. Data is located in the existing Data worksheet
     var pt = ws.PivotTables.Add(ws.Cells["A1"], dataWorksheet.Cells["A1:D17"], "PivotTable1");
     // Add one column field and one row field.
     var columnField = pt.ColumnFields.Add(pt.Fields["Continent"]);
     var rowField = pt.RowFields.Add(pt.Fields["Country"]);
     // Add a data field on "Sales"
     pt.DataFields.Add(pt.Fields["Sales"]);

     // Calculate the pivot table. 
     // The pivot table will be calculated when calculating the worksheet, if no previous calculation has been made (If the IsCalculated flag is false). 
     // The 'true' in the first parameter causes the pivot cache to be refreshed before calculating.
     // If the source data used by the pivot table contains formulas, those formulas must be calculated before calculating the pivot table. 
     pt.Calculate(true); //Only neccesary if you have changed the source data.
     
     //Now get the Sales for the cell North America/USA
      var usaSales = pt.GetPivotData(
         "Sales", //The data field we want to fetch
         new List<PivotDataCriteria> 
         { 
            new PivotDataCriteria(columnField, "North America"), 
            new PivotDataCriteria(rowField, "USA") 
         });
    
     //Now get the subtotal for Sales for North America. 
      var northAmericaSales = pt.GetPivotData(
         "Sales", //The data field we want to fetch
         new List<PivotDataCriteria> 
         { 
            new PivotDataCriteria(columnField, "North America")) 
         });

     //Now get the grand total for Sales for North America. 
      var northAmericaSales = pt.GetPivotData(
         "Sales", //The data field we want to fetch
         new List<PivotDataCriteria> 
         { 
         });

     ws.Calculate();

Using the GETPIVOTDATA function

The GETPIVOTDATA worksheet function fetches data from a pivot table, by specifying the data field and the row and/or column values. If you modify the source data of the pivot table you must refresh and calculate the pivot table before calling calculate any cell referencing GetPivotData function.

     var dataWorksheet = package.Workbook.Worksheets["Data"];
     // Add a pivot table starting from cell A1. Data is located in the existing Data worksheet
     var pt = ws.PivotTables.Add(ws.Cells["A1"], dataWorksheet.Cells["A1:D17"], "PivotTable1");
     // Add one column field and one row field.
     pt.ColumnFields.Add(pt.Fields["Continent"]);
     pt.RowFields.Add(pt.Fields["Country"]);
     // Add a data field on "Sales"
     pt.DataFields.Add(pt.Fields["Sales"]);

     // Calculate the pivot table. 
     // The pivot table will be calculated when calculating the worksheet, if no previous calculation has been made (If the IsCalculated flag is false). 
     // The 'true' in the first parameter causes the pivot cache to be refreshed before calculating.
     // If the source data used by the pivot table contains formulas, those formulas must be calculated before calculating the pivot table. 
     pt.Calculate(true); //Only necessary if you have changed the source data.

     //Add some formulas that fetch data from the pivot table.
     ws.Cells["G5"].Formula = "GETPIVOTDATA(\"Sales\",$A$1,\"Continent\",\"North America\",\"Country\",\"USA\")";    
     ws.Cells["G6"].Formula = "GETPIVOTDATA(\"Sales\",$A$1,\"Continent\",\"Europe\")";
     ws.Cells["G7"].Formula = "GETPIVOTDATA(\"Sales\",$A$1)";
     ws.Cells["G8"].Formula = "GETPIVOTDATA(\"Sales\",$A$1,\"Continent\",\"North America\",\"Country\",\"Sweden\")";
     
     //Now calculate the worksheet will get the values from the pivot table into cells G5:G8
     ws.Calculate();

Supported functionality

Calculating Data Fields on rows and columns:

Functions:

• Sum
• Count
• Average
• Min
• Max
• Product
• Count Numbers
• StdDev
• StdDevP
• Var
• VarP

Support for Show Value as

Show Value As:

• % of Grand Total
• % of Column Total
• % of Row Total
• % Of
• % of Parent Row Total
• % of Parent Column Total
• % of Parent Total
• Difference From
• % Difference From
• Running Total in
• % Running Total in
• Rank smallest to largest
• Rank largest to smallest
• Index

Sorting

• Sorting Ascending or Descending per field.

Page fields

• Filter on a single or multiple items.

Filters

• Caption filters
• Numeric Filters
• Date & Time Filters
• Value Filters
• Top/bottom filters
• Slicers

Calculated Fields

• Calculated fields using formulas supported in EPPlus and valid in a pivot table formula field.

Current limitations:

• EPPlus will not filter on Timeline filters.
• EPPlus will not handle external or OLAP data sources.
• EPPlus will not handle data model data sources.