Working with Slicers

EPPlus supports adding, modifying and removing table- and pivot table- slicers from version 5.4. A slicer is a drawing object used for filtering a datasource. In the case of a table, a slicer can be connected to a columns value filter. For a pivot table it can be connected to any data field.

Table slicers

Tables slicers can be added to a table via the column itself or via the workshees Drawing collection. A table slicer reflects the value filter of the table column.

The following code is from Sample 24 (links below). It adds and positions three different types of table slicers on a worksheet.

//You can either add a slicer via the table column...
var slicer1 = tbl.Columns[0].AddSlicer();
//Filter values are compared to the Text property of the cell. 
slicer1.FilterValues.Add("Barton-Veum");
slicer1.FilterValues.Add("Christiansen LLC");
slicer1.SetPosition(0, 0, 0, 0);

//... or you can add it via the drawings collection.
var slicer2 = worksheet1.Drawings.AddTableSlicer(tbl.Columns["Country"]);
slicer2.SetPosition(0,192);

//A slicer also supports date groups...
var slicer3 = tbl.Columns["Order Date"].AddSlicer();
slicer3.FilterValues.Add(new ExcelFilterDateGroupItem(2017, 6));
slicer3.FilterValues.Add(new ExcelFilterDateGroupItem(2017, 7));
slicer3.FilterValues.Add(new ExcelFilterDateGroupItem(2017, 8));
slicer3.SetPosition(0, 384);

See Slicer Sample 7.3-C# or Slicer Sample 7.3-VB

Pivot table slicers

Pivot table slicers works in a very similar way. The slicer can be added via the pivot table field or via the worksheets Drawing collection. The slicer reflects the item filter of a pivot table field. In the sample below we add a slicer and hides three values in the items collection.

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);