EPPlus 5/6/7

Features and technical overview



EPPlus supports adding, modifying and removing table- and pivot table- slicers from version 5.4 and up. Here is some code and a screenshot on how to add three table slicers.

//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("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"]);

//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);
Table slicer

Pivot table slicers

From version 5.4 EPPlus also supports adding pivot table slicers. This sample adds three slicers to two pivot tables, where the the first slicer is connected to both pivot tables and the other two to one each. Note that to to connect a slicer to two pivot tables, the pivot tables must share the same cache.

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.SetPosition(0, 0, 0, 0);
slicer1.Style = eSlicerStyle.Light4;

var slicer2 = pivotTable1.Fields["Company Name"].AddSlicer();
slicer2.Caption = "Company Name - PivotTable1";
slicer2.SetPosition(0, 0, 3, 0);
slicer2.To.Column = 7;

var slicer3 = pivotTable2.Fields["Order date"].AddSlicer();
slicer3.Caption = "Order date - PivotTable2";
slicer3.SetPosition(0, 0, 7, 0);
slicer3.To.Column = 11;
Pivot table slicer4