EPPlus 5/6/7

Features and technical overview

Pivot table enhancement

Auto sort - Sorting on data fields

EPPlus 5.7 adds support for auto sort via pivot areas.

        
rowField1 = pt3.RowFields.Add(pt3.Fields["Name"]);
var columnField1 = pt3.ColumnFields.Add(pt3.Fields["Country"]);
dataField = pt3.DataFields.Add(pt3.Fields["OrderValue"]);
dataField.Format = "#,##0";
//Add auto sort on the data field we have just added. We want to sort by the column field with value "Poland""
rowField1.SetAutoSort(dataField, eSortType.Ascending);

var conditionField = rowField1.AutoSort.Conditions.Fields.Add(columnField1);
//Before setting a reference to a value column we need to refresh the items cache.
columnField1.Items.Refresh();
conditionField.Items.AddByValue("Poland");

Data Field - ShowDataAs

EPPlus from version 5.7 supports setting the ShowDataAs (Show Value As in Excel) property on pivot table data fields. ShowDataAs sets different calculation options on the data field.

  • Difference from
  • Difference from %
  • % of
  • % of grand total
  • % of column total
  • % of row total
  • % of parent row
  • % of parent column
  • % of parent total
  • Index
  • Running total
  • % of running total
  • Rank ascending
  • Rank descending
Pivot table sorted

Filters

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 .
Pivot table filter

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

See also

 Back