-
Notifications
You must be signed in to change notification settings - Fork 275
Calculating Pivot tables
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:
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. |
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.
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();
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();
Functions:
- Sum
- Count
- Average
- Min
- Max
- Product
- Count Numbers
- StdDev
- StdDevP
- Var
- VarP
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 Ascending or Descending per field.
- Filter on a single or multiple items.
- Caption filters
- Numeric Filters
- Date & Time Filters
- Value Filters
- Top/bottom filters
- Slicers
- Calculated fields using formulas supported in EPPlus and valid in a pivot table formula field.
- EPPlus will not filter on Timeline filters.
- EPPlus will not handle external or OLAP data sources.
- EPPlus will not handle data model data sources.
EPPlus Software AB - https://epplussoftware.com
- Getting Started
- The Sample Project
- EPPlus and Docker
- EPPlus and Blazor
- EPPlus and Linux
- Security
- Configuration
- Releases/versioning
- What is new in EPPlus 5+
- Breaking Changes in EPPlus 5
- Breaking Changes in EPPlus 6
- Breaking Changes in EPPlus 7
- Addressing a worksheet
- Dimension/Used range
- Copying ranges/sheets
- Insert/Delete
- Filling ranges
- Sorting ranges
- Taking and skipping columns/rows
- Data validation
- Comments
- Freeze and Split Panes
- Header and Footer
- Autofit columns
- Grouping and Ungrouping Rows and Columns
- Formatting and styling
- Conditional formatting
- Using Themes
- Working with custom named table- or slicer- styles