Pivot table filters and slicers
Pivot table filters are filters that are applied to Pivot table fields. Pivot table slicers are drawing objects that map to a Pivot table fields value filter. These two features are supported from EPPlus 5.4.
EPPlus supports the following filters for Pivot tables:
- Item filters - Filters on individual items in row/column or page fields.
- Caption filters (label filters) - Filters for text on row and column fields.
- Date, numeric and string filters - Filters using various operators such as Equals, NotBetween, GreaterThan, etc.
- Top 10 filters - Filter using top or bottom criterias for percent, count or value.
- Dynamic filters - Filter using various date- and average criterias.
The following code is from Sample 13 (link below)
ExcelRangeBase range = await LoadFromDatabase(connectionString, ws);
var tbl = ws.Tables.Add(range, "ptFilter");
tbl.TableStyle = OfficeOpenXml.Table.TableStyles.Medium23;
var pt1=ws.PivotTables.Add(ws.Cells["J1"], tbl, "PivotTable1");
var rowField = pt1.RowFields.Add(pt1.Fields["CompanyName"]);
var dataField = pt1.DataFields.Add(pt1.Fields["OrderValue"]);
//First deselect a company in the items list. To do so we first need to refresh the items from the range.
rowField.Items.Refresh(); //Refresh the items from the range.
rowField.Items.GetByValue("Sporer, Mertz and Jaskolski").Hidden=true;
//Add a caption filter on Company Name between A and D
rowField.Filters.AddCaptionFilter(ePivotTableCaptionFilterType.CaptionBetween, "A", "D");
//Add a value filter where OrderValue >= 100
rowField.Filters.AddValueFilter(ePivotTableValueFilterType.ValueGreaterThanOrEqual, dataField, 100);
//Add a second pivot table with some different filters.
var pt2 = ws.PivotTables.Add(ws.Cells["M1"], tbl, "PivotTable2");
var rowField1 = pt2.RowFields.Add(pt2.Fields["Currency"]);
var rowField2 = pt2.RowFields.Add(pt2.Fields["OrderDate"]);
rowField2.Format = "yyyy-MM-dd";
var dataField1 = pt2.DataFields.Add(pt2.Fields["OrderValue"]);
var dataField2 = pt2.DataFields.Add(pt2.Fields["OrderId"]);
dataField2.Function = DataFieldFunctions.CountNums;See Sample 4.2-C# or Sample 4.2-VB
Pivot table slicers are drawing objects that map to a Pivot table fields value filter. The slicer can be connected to any item filter in a Pivot table.
var pivotTable = wsPivot.PivotTables.Add(wsPivot.Cells["A1"], wsSource.Cells[wsSource.Dimension.Address], "PivotTable1");
pivotTable.RowFields.Add(pivotTable.Fields["Company Name"]);
pivotTable.DataFields.Add(pivotTable.Fields["Order Value"]);
pivotTable.DataFields.Add(pivotTable.Fields["Tax"]);
pivotTable.DataFields.Add(pivotTable.Fields["Freight"]);
pivotTable.DataOnRows = false;
var slicer1 = pivotTable.Fields["Name"].AddSlicer();
slicer1.SetPosition(0, 0, 10, 0);
slicer1.SetSize(400, 208);
slicer1.Style = eSlicerStyle.Light4;
slicer1.Cache.Data.Items.GetByValue("Brown Kutch").Hidden = true;
slicer1.Cache.Data.Items.GetByValue("Tierra Ratke").Hidden = true;
slicer1.Cache.Data.Items.GetByValue("Jamarcus Schimmel").Hidden = true;
//Add a column with two columns and start showing the item 3.
slicer1.ColumnCount = 2; //Use two columns on this slicer
slicer1.StartItem = 3; //First visible item is 3
slicer1.Cache.Data.CrossFilter = eCrossFilter.ShowItemsWithNoData;
slicer1.Cache.Data.SortOrder = eSortOrder.Descending;
A slicer can also be connected to multiple Pivot tables if the Pivot table share the same cache. In the sample below we add three slicers, one connected to both Pivot tables and the other two just connected to one Pivot table each.
var wsSource = p.Workbook.Worksheets["PivotTableSourceData"];
var wsPivot = p.Workbook.Worksheets.Add("OneSlicerToTwoPivotTables");
var pivotTable1 = wsPivot.PivotTables.Add(wsPivot.Cells["D15"], wsSource.Cells[wsSource.Dimension.Address], "PivotTable1");
pivotTable1.RowFields.Add(pivotTable1.Fields["Company Name"]);
pivotTable1.DataFields.Add(pivotTable1.Fields["Order Value"]);
pivotTable1.DataFields.Add(pivotTable1.Fields["Tax"]);
pivotTable1.DataFields.Add(pivotTable1.Fields["Freight"]);
pivotTable1.DataOnRows = false;
//To connect a slicer to multiple pivot tables the tables need to use the same pivot table cache, so we use pivotTable1's cache as source to pivotTable2...
var pivotTable2 = wsPivot.PivotTables.Add(wsPivot.Cells["H15"], pivotTable1.CacheDefinition, "PivotTable2");
pivotTable2.RowFields.Add(pivotTable2.Fields["Country"]);
pivotTable2.DataFields.Add(pivotTable2.Fields["Order Value"]);
pivotTable2.DataFields.Add(pivotTable2.Fields["Tax"]);
pivotTable2.DataFields.Add(pivotTable2.Fields["Freight"]);
pivotTable2.DataOnRows = false;
var slicer1 = pivotTable1.Fields["Country"].AddSlicer();
slicer1.Caption = "Country - Both";
//Now add the second pivot table to the slicer cache. This require that the pivot tables share the same cache.
slicer1.Cache.PivotTables.Add(pivotTable2);
slicer1.SetPosition(0, 0, 0, 0);
slicer1.Style = eSlicerStyle.Light4;
var slicer2 = pivotTable1.Fields["Company Name"].AddSlicer();
slicer2.Caption = "Company Name - PivotTable1";
slicer2.ChangeCellAnchor(eEditAs.Absolute);
slicer2.SetPosition(0, 192);
slicer2.SetSize(256, 260);
var slicer3 = pivotTable2.Fields["Order date"].AddSlicer();
slicer3.Caption = "Order date - PivotTable2";
slicer3.ChangeCellAnchor(eEditAs.Absolute);
slicer3.SetPosition(0, 448);
slicer3.SetSize(256, 260);