Table filters and slicers

EPPlus 5 lets you add/modify table filters and slicers. A table filter is set on a table column and defines a filter critera for the data in the table. A slicer is a drawing object connected to a value filter of a table column.

Table filters

EPPlus supports the following filters for tables:

• Value filters - Filters on texts/values in a table column
• Date group filters - Filters by grouping dates per year, month, day, etc.
• Custom filters - Filters using operators, like GreaterThanOrEqual, Between, etc.
• Top 10 filters - Filter using top or bottom criterias for percent or value.
• Dynamic filters - Filter using various date- and average criterias.

This code is from Sample 13 (links below)

var ws = p.Workbook.Worksheets.Add("TableFilter");
// This method just loads data from a db into a range
ExcelRangeBase range = await LoadFromDatabase(connectionString, ws);

var tbl = ws.Tables.Add(range, "tblFilter");
tbl.TableStyle = OfficeOpenXml.Table.TableStyles.Medium23;
tbl.ShowFilter = true;
//Add a value filter
var colCompany = tbl.AutoFilter.Columns.AddValueFilterColumn(0);
colCompany.Filters.Add("Walsh LLC");
colCompany.Filters.Add("Harber-Goldner");
colCompany.Filters.Add("Sporer, Mertz and Jaskolski");

//Add a second filter on order value
var colOrderValue = tbl.AutoFilter.Columns.AddCustomFilterColumn(6);
colOrderValue.Filters.Add(new ExcelFilterCustomItem("500", eFilterOperator.GreaterThanOrEqual));

//Apply the filters.
tbl.AutoFilter.ApplyFilter();
range.AutoFitColumns(0);

Table filters work similar to auto filters.

See Sample 4.2-C# or Sample 4.2-VB

Table slicers

A slicer is a drawing object connected to a value filter of a table column and is supported from EPPlus 5.4.

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

See also

Tables