Calculating Pivot tables and the GetPivotData Method

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 propeties:

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.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.GetPivotData method

     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 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 Ascenting 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 formulas using formulas supported in EPPlus and valid in a pivot table formula field.

Current limitations:

• EPPlus will not handle external data sources or OLAP data sources.
• EPPlus will not handle single or multiple subtotals functions set for a row/column field, only Default or None. The data field function is currently used as for the subtotals.