Document Solutions for Excel, .NET Edition | Document Solutions
Features / Pivot Table / Pivot Table Filter
In This Topic
    Pivot Table Filter
    In This Topic

    DsExcel enables you to filter values in pivot tables. You can either hide row fields or pages using Visible property of IPivotItem interface or use PivotFilters property of IPivotField interface, which enables you to add label, value, or date filters to the pivot table field using PivotFilterType enumeration that will be passed as a parameter in the Add method of IPivotFilters interface.

    Furthermore, DsExcel provides AllowMultipleFilters property of IPivotTable interface, enabling you to add label and value filters simultaneously to a PivotField. The default value of this property is true. If you set this property to false, the filter set later will be used.

    Note: When the value of AllowMultipleFilters changes from true to false, it will go through the IPivotField. If IPivotField has both the label and value filters, DsExcel will discard both filters.

    DsExcel also provides ClearLabelFilter, ClearValueFilter, and ClearAllFilters methods to delete the filters. ClearLabelFilter deletes the label filter, whereas ClearValueFilter deletes the value filter. ClearAllFilters deletes all the filters added to the field.

    Note: If you add two label filters or two value filters simultaneously using Add method, then the second filter will replace the first.
    Note: Most of the properties of the IPivotFilter interface are read-only, except for the WholeDayFilter property, which is read-write. However, any changes to the value of WholeDayFilter property will not trigger a refresh of the current pivot table. Hence, you have to call Update or Refresh methods of IPivotTable interface to update or refresh the filter results.

    You can apply the following types of filters to the pivot table:

    Apply Filter

    Refer to the following example code to hide row fields or pages using Visible property:

    C#
    Copy Code 
    // Create a new workbook.
Workbook workbook = new Workbook();

// Add data for the pivot table.
object[,] sourceData = new object[,] {
{ "Order ID", "Product",               "Category",              "Amount", "Date",                    "Country" },
{ 1,          "Bose 785593-0050",      "Consumer Electronics",  4270,     new DateTime(2018, 1, 6),  "United States" },
{ 2,          "Canon EOS 1500D",       "Consumer Electronics",  8239,     new DateTime(2018, 1, 7),  "United Kingdom" },
{ 3,          "Haier 394L 4Star",      "Consumer Electronics",  617,      new DateTime(2018, 1, 8),  "United States" },
{ 4,          "IFB 6.5 Kg FullyAuto",  "Consumer Electronics",  8384,     new DateTime(2018, 1, 10), "Canada" },
{ 5,          "Mi LED 40inch",         "Consumer Electronics",  2626,     new DateTime(2018, 1, 10), "Germany" },
{ 6,          "Sennheiser HD 4.40-BT", "Consumer Electronics",  3610,     new DateTime(2018, 1, 11), "United States" },
{ 7,          "Iphone XR",             "Mobile",                9062,     new DateTime(2018, 1, 11), "Australia" },
{ 8,          "OnePlus 7Pro",          "Mobile",                6906,     new DateTime(2018, 1, 16), "New Zealand" },
{ 9,          "Redmi 7",               "Mobile",                2417,     new DateTime(2018, 1, 16), "France" },
{ 10,         "Samsung S9",            "Mobile",                7431,     new DateTime(2018, 1, 16), "Canada" },
{ 11,         "OnePlus 7Pro",          "Mobile",                8250,     new DateTime(2018, 1, 16), "Germany" },
{ 12,         "Redmi 7",               "Mobile",                7012,     new DateTime(2018, 1, 18), "United States" },
{ 13,         "Bose 785593-0050",      "Consumer Electronics",  1903,     new DateTime(2018, 1, 20), "Germany" },
{ 14,         "Canon EOS 1500D",       "Consumer Electronics",  2824,     new DateTime(2018, 1, 22), "Canada" },
{ 15,         "Haier 394L 4Star",      "Consumer Electronics",  6946,     new DateTime(2018, 1, 24), "France" },
};

// Access first worksheet.
IWorksheet worksheet = workbook.Worksheets[0];

// Add values to the range.
worksheet.Range["G1:L16"].Value = sourceData;

// Set column width.
worksheet.Range["G:L"].ColumnWidth = 15;

// Create pivot cache.
var pivotcache = workbook.PivotCaches.Create(worksheet.Range["G1:L16"]);

// Add data to the pivot table.
var pivottable = worksheet.PivotTables.Add(pivotcache, worksheet.Range["A1"], "pivottable1");

// Set number format.
worksheet.Range["J1:J16"].NumberFormat = "$#,##0.00";

// Configure pivot table's fields.
var field_product = pivottable.PivotFields[1];
field_product.Orientation = PivotFieldOrientation.RowField;

var field_Amount = pivottable.PivotFields[3];
field_Amount.Orientation = PivotFieldOrientation.DataField;
field_Amount.NumberFormat = "$#,##0.00";

var field_Country = pivottable.PivotFields[5];
field_Country.Orientation = PivotFieldOrientation.PageField;

// Add row field filter.
field_product.PivotItems["Bose 785593-0050"].Visible = false;
field_product.PivotItems["Haier 394L 4Star"].Visible = false;
field_product.PivotItems["Iphone XR"].Visible = false;

// Add page filter.
field_Country.PivotItems["United States"].Visible = false;
field_Country.PivotItems["Canada"].Visible = false;

worksheet.Range["A:B"].EntireColumn.AutoFit();

// Save WorkBook.
workbook.Save("FilterPivotTable.xlsx");

    Apply Label Filter

    Refer to the example code to filter products based on the item names containing "mi":

    C#
    Copy Code 
    // Configure pivot table's fields.
var field_Category = pivottable.PivotFields["Category"];
field_Category.Orientation = PivotFieldOrientation.ColumnField;

var field_Product = pivottable.PivotFields["Product"];
field_Product.Orientation = PivotFieldOrientation.RowField;

var field_Amount = pivottable.PivotFields["Amount"];
field_Amount.Orientation = PivotFieldOrientation.DataField;
field_Amount.NumberFormat = "$#,##0.00";

// Filter for products where the product name contains 'mi'.
field_Product.PivotFilters.Add(PivotFilterType.CaptionContains, "mi");

worksheet.Range["A:D"].EntireColumn.AutoFit();

// Save the workbook.
workbook.Save("PivotLabelFilter.xlsx");

    Apply Value Filter

    Refer to the example code to filter products based on value:

    C#
    Copy Code 
    // Configure pivot table's fields.
var field_Category = pivottable.PivotFields["Category"];
field_Category.Orientation = PivotFieldOrientation.ColumnField;

var field_Product = pivottable.PivotFields["Product"];
field_Product.Orientation = PivotFieldOrientation.RowField;

var field_Amount = pivottable.PivotFields["Amount"];
field_Amount.Orientation = PivotFieldOrientation.DataField;
field_Amount.NumberFormat = "$#,##0.00";

// Filter for products with sales volume greater than 7000.
field_Product.PivotFilters.Add(PivotFilterType.ValueGreaterThan, 7000, options: new PivotFilterOptions { AppliedDataField = 0 });

worksheet.Range["A:D"].EntireColumn.AutoFit();

// Save the workbook.
workbook.Save("PivotValueFilter.xlsx");
    Note: You must set AppliedDataField property of PivotFilterOptions class with the value filter to specify which DataField to filter by.

    Apply Date Filter

    Refer to the following example code to filter products dated between two specified dates:

    C#
    Copy Code 
    // Configure pivot table's fields.
var field_Category = pivottable.PivotFields["Category"];
field_Category.Orientation = PivotFieldOrientation.ColumnField;

var field_Product = pivottable.PivotFields["Product"];
field_Product.Orientation = PivotFieldOrientation.RowField;

var field_Date = pivottable.PivotFields["Date"];
field_Date.Orientation = PivotFieldOrientation.RowField;

var field_Amount = pivottable.PivotFields["Amount"];
field_Amount.Orientation = PivotFieldOrientation.DataField;
field_Amount.NumberFormat = "$#,##0.00";

// Filter for products between two dates.
field_Date.PivotFilters.Add(PivotFilterType.DateBetween, new DateTime(2018, 1, 1), new DateTime(2018, 1, 15));

worksheet.Range["A:D"].EntireColumn.AutoFit();

// Save the workbook.
workbook.Save("PivotDateFilter.xlsx");

    Apply Top 10 Filter

    Refer to the example code to filter the top twelve products based on value:

    C#
    Copy Code 
    // Configure pivot table's fields.
var field_Category = pivottable.PivotFields["Category"];
field_Category.Orientation = PivotFieldOrientation.ColumnField;

var field_Product = pivottable.PivotFields["Product"];
field_Product.Orientation = PivotFieldOrientation.RowField;

var field_Amount = pivottable.PivotFields["Amount"];
field_Amount.Orientation = PivotFieldOrientation.DataField;
field_Amount.NumberFormat = "$#,##0.00";

// Filter for top 12 products.
field_Product.PivotFilters.Add(PivotFilterType.Count, 12, options: new PivotFilterOptions { IsTop10Type = true });

worksheet.Range["A:D"].EntireColumn.AutoFit();

// Save the workbook.
workbook.Save("PivotTopTenFilter.xlsx");

    Apply Multiple Filters

    Refer to the following example code to filter products using both lable and value filters:

    C#
    Copy Code 
    // Configure pivot table's fields.
var field_Category = pivottable.PivotFields["Category"];
field_Category.Orientation = PivotFieldOrientation.ColumnField;

var field_Product = pivottable.PivotFields["Product"];
field_Product.Orientation = PivotFieldOrientation.RowField;

var field_Amount = pivottable.PivotFields["Amount"];
field_Amount.Orientation = PivotFieldOrientation.DataField;
field_Amount.NumberFormat = "$#,##0.00";

// Allow adding label filter and value filter to a field at the same time.
pivottable.AllowMultipleFilters = true;

// Filter for products with sales volume greater than 7000.
field_Product.PivotFilters.Add(PivotFilterType.ValueGreaterThan, 7000, options: new PivotFilterOptions { AppliedDataField = 0 });

// Filter for products where the product name contains 'mi'.
field_Product.PivotFilters.Add(PivotFilterType.CaptionContains, "mi");

worksheet.Range["A:D"].EntireColumn.AutoFit();

// Save the workbook.
workbook.Save("MultipleFilter.xlsx");