EPPlus 5

Features and technical overview

Pivot table filters and calculated columns


EPPlus 5.4 adds support for pivot table filters, calculated columns and shared pivot table caches. The following filters are supported.

  • 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.
Item filters

Adding a page field and deselect items.

//Add a pagefield
var pageField = pivotTable3.PageFields.Add(pivotTable3.Fields["CompanyName"]);
pageField.Items.Refresh();  //Refresh the items from the source range.

pageField.Items[1].Hidden = true;   //Hide item with index 1 in the items collection
pageField.Items.GetByValue("Walsh LLC").Hidden = true;  //Hide the item with supplied the value .

Other filters

You can also add more advanced filters. For example a caption filter (Label filter in Excel)...

var rowField1 = pivotTable4.RowFields.Add(pivotTable4.Fields["Name"]);

//Add the Caption filter (Label filter in Excel) to the pivot table.
rowField1.Filters.AddCaptionFilter(ePivotTableCaptionFilterType.CaptionNotBeginsWith, "C");

Calculated fields

EPPlus 5.4 also supports adding calculated fields to a pivot table.

//Create a new pivot table using the same cache as pivot table 2.
var pivotTable4 = wsPivot4.PivotTables.Add(wsPivot4.Cells["A3"], pivotCache, "PerWithCalculatedField");


//Be careful with formulas as they can cause the pivot table to become corrupt if they are entered invalidly
var calcField = pivotTable4.Fields.AddCalculatedField("Total", "'OrderValue'+'Tax'+'Freight'");
calcField.Format = "#,##0";

//Add the data fields and format them
ExcelPivotTableDataField dataField;
dataField = pivotTable4.DataFields.Add(pivotTable4.Fields["OrderValue"]);
dataField.Format = "#,##0";
dataField = pivotTable4.DataFields.Add(pivotTable4.Fields["Tax"]);
dataField.Format = "#,##0";
dataField = pivotTable4.DataFields.Add(pivotTable4.Fields["Freight"]);
dataField.Format = "#,##0";
dataField = pivotTable4.DataFields.Add(pivotTable4.Fields["Total"]);
dataField.Format = "#,##0";

Check out this wiki article or sample 18 for more details.
.NET Core , .NET Framework