Document Solutions for Excel, Java 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 setVisible method of IPivotItem interface or use getPivotFilters method 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 setAllowMultipleFilters method of IPivotTable interface, enabling you to add label and value filters simultaneously to a PivotField. The default value of this method is true. If you set this method to false, the filter set later will be used.

    Note: When the value of setAllowMultipleFilters 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 methods of the IPivotFilter interface are read-only, except for the WholeDayFilter method, which is read-write. However, any changes to the value of WholeDayFilter method 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 setVisible method:

    Java
    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 GregorianCalendar(2018, 0, 6), "United States"},
        {2, "Canon EOS 1500D", "Consumer Electronics", 8239, new GregorianCalendar(2018, 0, 7), "United Kingdom"},
        {3, "Haier 394L 4Star", "Consumer Electronics", 617, new GregorianCalendar(2018, 0, 8), "United States"},
        {4, "IFB 6.5 Kg FullyAuto", "Consumer Electronics", 8384, new GregorianCalendar(2018, 0, 10), "Canada"},
        {5, "Mi LED 40inch", "Consumer Electronics", 2626, new GregorianCalendar(2018, 0, 10), "Germany"},
        {6, "Sennheiser HD 4.40-BT", "Consumer Electronics", 3610, new GregorianCalendar(2018, 0, 11), "United States"},
        {7, "Iphone XR", "Mobile", 9062, new GregorianCalendar(2018, 0, 11), "Australia"},
        {8, "OnePlus 7Pro", "Mobile", 6906, new GregorianCalendar(2018, 0, 16), "New Zealand"},
        {9, "Redmi 7", "Mobile", 2417, new GregorianCalendar(2018, 0, 16), "France"},
        {10, "Samsung S9", "Mobile", 7431, new GregorianCalendar(2018, 0, 16), "Canada"},
        {11, "OnePlus 7Pro", "Mobile", 8250, new GregorianCalendar(2018, 0, 16), "Germany"},
        {12, "Redmi 7", "Mobile", 7012, new GregorianCalendar(2018, 0, 18), "United States"},
        {13, "Bose 785593-0050", "Consumer Electronics", 1903, new GregorianCalendar(2018, 0, 20), "Germany"},
        {14, "Canon EOS 1500D", "Consumer Electronics", 2824, new GregorianCalendar(2018, 0, 22), "Canada"},
        {15, "Haier 394L 4Star", "Consumer Electronics", 6946, new GregorianCalendar(2018, 0, 24), "France"},
};

// Access first worksheet.
IWorksheet worksheet = workbook.getWorksheets().get(0);
        
// Add values to the range.
worksheet.getRange("G1:L16").setValue(sourceData);
        
// Set column width.
worksheet.getRange("G:L").setColumnWidth(15);
        
// Create pivot cache.
IPivotCache pivotcache = workbook.getPivotCaches().create(worksheet.getRange("G1:L16"));
        
// Add data to the pivot table.
IPivotTable pivottable = worksheet.getPivotTables().add(pivotcache, worksheet.getRange("A1"), "pivottable1");
        
// Set number format.
worksheet.getRange("J1:J16").setNumberFormat("$#,##0.00");

// Configure pivot table's fields.
IPivotField field_Product = pivottable.getPivotFields().get(1);
field_Product.setOrientation(PivotFieldOrientation.RowField);

IPivotField field_Amount = pivottable.getPivotFields().get(3);
field_Amount.setOrientation(PivotFieldOrientation.DataField);
field_Amount.setNumberFormat("$#,##0.00");

IPivotField field_Country = pivottable.getPivotFields().get(5);
field_Country.setOrientation(PivotFieldOrientation.PageField);

// Add row field filter.
field_Product.getPivotItems().get("Bose 785593-0050").setVisible(false);
field_Product.getPivotItems().get("Haier 394L 4Star").setVisible(false);
field_Product.getPivotItems().get("Iphone XR").setVisible(false);

// Add page filter.
field_Country.getPivotItems().get("United States").setVisible(false);
field_Country.getPivotItems().get("Canada").setVisible(false);

worksheet.getRange("A:B").getEntireColumn().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":

    Java
    Copy Code 
    // Configure pivot table's fields.
var field_Category = pivottable.getPivotFields().get("Category");
field_Category.setOrientation(PivotFieldOrientation.ColumnField);

var field_Product = pivottable.getPivotFields().get("Product");
field_Product.setOrientation(PivotFieldOrientation.RowField);

var field_Amount = pivottable.getPivotFields().get("Amount");
field_Amount.setOrientation(PivotFieldOrientation.DataField);
field_Amount.setNumberFormat("$#,##0.00");

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

worksheet.getRange("A:D").getEntireColumn().autoFit();

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

    Apply Value Filter

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

    Java
    Copy Code 
    // Configure pivot table's fields.
var field_Category = pivottable.getPivotFields().get("Category");
field_Category.setOrientation(PivotFieldOrientation.ColumnField);

var field_Product = pivottable.getPivotFields().get("Product");
field_Product.setOrientation(PivotFieldOrientation.RowField);

var field_Amount = pivottable.getPivotFields().get("Amount");
field_Amount.setOrientation(PivotFieldOrientation.DataField);
field_Amount.setNumberFormat("$#,##0.00");

PivotFilterOptions options = new PivotFilterOptions();
options.setAppliedDataField(0);
        
// Filter for products with sales volume greater than 7000.
field_Product.getPivotFilters().add(PivotFilterType.ValueGreaterThan, 7000, null, options);

worksheet.getRange("A:D").getEntireColumn().autoFit();

// Save the workbook.
workbook.save("PivotValueFilter.xlsx");
    Note: You must set setAppliedDataField method 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:

    Java
    Copy Code 
    // Configure pivot table's fields.
var field_Category = pivottable.getPivotFields().get("Category");
field_Category.setOrientation(PivotFieldOrientation.ColumnField);

var field_Product = pivottable.getPivotFields().get("Product");
field_Product.setOrientation(PivotFieldOrientation.RowField);

var field_Date = pivottable.getPivotFields().get("Date");
field_Date.setOrientation(PivotFieldOrientation.RowField);

var field_Amount = pivottable.getPivotFields().get("Amount");
field_Amount.setOrientation(PivotFieldOrientation.DataField);
field_Amount.setNumberFormat("$#,##0.00");

// Filter for products between two dates.
field_Date.getPivotFilters().add(PivotFilterType.DateBetween, LocalDate.of(2018, 1, 1), LocalDate.of(2018, 1, 15));

worksheet.getRange("A:D").getEntireColumn().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:

    Java
    Copy Code 
    // Configure pivot table's fields.
var field_Category = pivottable.getPivotFields().get("Category");
field_Category.setOrientation(PivotFieldOrientation.ColumnField);

var field_Product = pivottable.getPivotFields().get("Product");
field_Product.setOrientation(PivotFieldOrientation.RowField);

var field_Amount = pivottable.getPivotFields().get("Amount");
field_Amount.setOrientation(PivotFieldOrientation.DataField);
field_Amount.setNumberFormat("$#,##0.00");

// Filter for top 12 products.
PivotFilterOptions options = new PivotFilterOptions();
options.setIsTop10Type(true);
field_Product.getPivotFilters().add(PivotFilterType.Count, 12, null, options);

worksheet.getRange("A:D").getEntireColumn().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:

    Java
    Copy Code 
    // Configure pivot table's fields.
var field_Category = pivottable.getPivotFields().get("Category");
field_Category.setOrientation(PivotFieldOrientation.ColumnField);

var field_Product = pivottable.getPivotFields().get("Product");
field_Product.setOrientation(PivotFieldOrientation.RowField);

var field_Amount = pivottable.getPivotFields().get("Amount");
field_Amount.setOrientation(PivotFieldOrientation.DataField);
field_Amount.setNumberFormat("$#,##0.00");

// Allow adding label filter and value filter to a field at the same time.
pivottable.setAllowMultipleFilters(true);

// Filter for products with sales volume greater than 7000.
PivotFilterOptions options = new PivotFilterOptions();
options.setAppliedDataField(0);
field_Product.getPivotFilters().add(PivotFilterType.ValueGreaterThan, 7000, null, options);

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

worksheet.getRange("A:D").getEntireColumn().autoFit();

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