# Pivot Table Filter

DsExcel Java is a MESCIUS Documents product that offers a comprehensive library to create, manipulate, convert, and share Microsoft Excel-compatible spreadsheets.

## Content



DsExcel enables you to filter values in pivot tables. You can either hide row fields or pages using [setVisible](/document-solutions/java-excel-api/api/online/com/grapecity/documents/excel/IPivotItem.html#setVisible) method of [IPivotItem](/document-solutions/java-excel-api/api/online/com/grapecity/documents/excel/IPivotItem.html) interface or use [getPivotFilters](/document-solutions/java-excel-api/api/online/com/grapecity/documents/excel/IPivotField.html#getPivotFilters) method of [IPivotField](/document-solutions/java-excel-api/api/online/com/grapecity/documents/excel/IPivotField.html) interface, which enables you to add label, value, or date filters to the pivot table field using [PivotFilterType](/document-solutions/java-excel-api/api/online/com/grapecity/documents/excel/PivotFilterType.html) enumeration that will be passed as a parameter in the [add](/document-solutions/java-excel-api/api/online/com/grapecity/documents/excel/IPivotFilters.html#add) method of [IPivotFilters](/document-solutions/java-excel-api/api/online/com/grapecity/documents/excel/IPivotFilters.html) interface.
Furthermore, DsExcel provides [setAllowMultipleFilters](/document-solutions/java-excel-api/api/online/com/grapecity/documents/excel/IPivotTable.html#setAllowMultipleFilters) method of [IPivotTable](/document-solutions/java-excel-api/api/online/com/grapecity/documents/excel/IPivotTable.html) 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.

> !type=note
>
> **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](/document-solutions/java-excel-api/api/online/com/grapecity/documents/excel/IPivotField.html#clearLabelFilter), [clearValueFilter](/document-solutions/java-excel-api/api/online/com/grapecity/documents/excel/IPivotField.html#clearValueFilter), and [clearAllFilters](/document-solutions/java-excel-api/api/online/com/grapecity/documents/excel/IPivotField.html#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.

> !type=note
>
> **Note**: If you add two label filters or two value filters simultaneously using add method, then the second filter will replace the first.
> !type=note
>
> **Note**: Most of the methods of the [IPivotFilter](/document-solutions/java-excel-api/api/online/com/grapecity/documents/excel/IPivotFilter.html) 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](/document-solutions/java-excel-api/api/online/com/grapecity/documents/excel/IPivotTable.html#update) or [refresh](/document-solutions/java-excel-api/api/online/com/grapecity/documents/excel/IPivotTable.html#refresh) methods of [IPivotTable](/document-solutions/java-excel-api/api/online/com/grapecity/documents/excel/IPivotTable.html) interface to update or refresh the filter results.

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

* [Apply Filter](/document-solutions/java-excel-api/docs/online/Features/UsePivotTable/PivotTableFilter#apply-filter)
* [Apply Label Filter](/document-solutions/java-excel-api/docs/online/Features/UsePivotTable/PivotTableFilter#apply-label-filter)
* [Apply Value Filter](/document-solutions/java-excel-api/docs/online/Features/UsePivotTable/PivotTableFilter#apply-value-filter)
* [Apply Date Filter](/document-solutions/java-excel-api/docs/online/Features/UsePivotTable/PivotTableFilter#apply-date-filter)
* [Apply Top 10 Filter](/document-solutions/java-excel-api/docs/online/Features/UsePivotTable/PivotTableFilter#apply-top-10-filter)
* [Apply Multiple Filters](/document-solutions/java-excel-api/docs/online/Features/UsePivotTable/PivotTableFilter#apply-multiple-filters)

## Apply Filter

Refer to the following example code to hide row fields or pages using setVisible method:

```Java
// 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
// 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
// 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");
```

> !type=note
>
> **Note**: You must set [setAppliedDataField](/document-solutions/java-excel-api/api/online/com/grapecity/documents/excel/PivotFilterOptions.html#setAppliedDataField) method of [PivotFilterOptions](/document-solutions/java-excel-api/api/online/com/grapecity/documents/excel/PivotFilterOptions.html) 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
// 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
// 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
// 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");
```