EPPlus 5

Features and technical overview

Pivot table filters and calculated columns

EPPlus

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

pivotTable4.RowFields.Add(pivotTable4.Fields["CompanyName"]);
pivotTable4.RowFields.Add(pivotTable4.Fields["OrderDate"]);

//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