Apache POI Custom Filters Java Example

In this part of Excel Apache POI AutoFilter approaches, we will discuss how to implement custom filter with Java examples. Let us examine custom filters on text columns, and then discuss how to implement the same on number columns.A list of custom filters that can be implemented includes;

• Equals
• Does Not Equal
• Begins With
• Ends With
• Contains
• Does not contain

You can find them in the screendump below:

Apache POI Custom Text Filter Example

Let us see how to write a program that implements these filters now.

POI Custom Filters - Begins With - Example

We have to use CTCustomFilters and CTCustomFilter class to define a custom filter. The steps are same as our autofilter by criteria tutorial.We begin by getting the CTAutoFilter object. Then, we create a filter column, set the column ID. From here on, we can set our custom filter. And once done, we have to apply the filter to the rows and columns. A Begins With Filter example is shown below: [ Only key changes are shown, full program available for download ]

CTAutoFilter sheetFilter=my_sheet.getCTWorksheet().getAutoFilter();                             
/* Step -2: Add new Filter Column */
CTFilterColumn  myFilterColumn=sheetFilter.insertNewFilterColumn(0);
/* Step-3: Set Filter Column ID */
myFilterColumn.setColId(1L);
/* Add a Custom Filter */
CTCustomFilters myCustomFilter=myFilterColumn.addNewCustomFilters();
/* This will be our begins with filter */
CTCustomFilter myFilter1= myCustomFilter.addNewCustomFilter();  
/* Set Value for Begins With Filter */          
myFilter1.setVal("AB*");  // Filter records that begin with AB
//Note that we are adding * after AB to implement begins with filter

XSSFRow r1;
/* Step-6: Loop through Rows and Apply Filter */
for(Row r : my_sheet) {
        for (Cell c : r) {
        if (c.getColumnIndex()==1 && !c.getStringCellValue().startsWith("AB")) {
                r1=(XSSFRow) c.getRow();
                if (r1.getRowNum()!=0) { /* Ignore top row */
                        /* Hide Row that does not meets begins with criteria */
                        r1.getCTRow().setHidden(true); }
        }                               
        }
}

Apache POI - Ends With - Custom Filter

This is an extension of the "begins with" filter example. You have to change the setVal method value to *AB instead of AB*. Also, you have change the method used to filter records. In this case, you can use endsWith method as shown below:

/* Set Value for Ends With Filter */            
myFilter1.setVal("*AB");  // Filter records that Ends with AB
//Note that we are adding * before AB to implement ends with filter
...
...
...
if (c.getColumnIndex()==1 && !c.getStringCellValue().endsWith("AB")) {

POI - Contains - Custom Filter Example

In this case, you put a * on either side and use string.contains or string.matches to filter records suitably. The Java code snippet is shown below:

/* String Contains Custom Filter */             
myFilter1.setVal("*AB*");  // Filter records that contains AB
//Note that we are adding * before and after AB to implement contains filter
...
...
...
if (c.getColumnIndex()==1 && !c.getStringCellValue().contains("AB")) {

On the same lines, you can also implement "not contains" filter, which does the opposite. Wait, there is a catch. You have to add an extra method setOperator as shown below:

/* Implement a Does not Contain Custom Filter in Apache POI */
myFilter1.setOperator(STFilterOperator.NOT_EQUAL);
myFilter1.setVal("*C*");  // Filter records that does not contain C
...
...
...
if (c.getColumnIndex()==1 && c.getStringCellValue().contains("C")) {

Multiple Custom Filters - AND / OR Logic - POI Examples

And Finally, you can also implement filters with AND / OR logic (multiple custom filters). A screenshot of this in Excel is provided below:

Multiple Custom Filters - Apache POI Example

Here you can choose additional sensible options for your custom filter that includes;

• does not begin with filter
• does not end with filter
• Multiple Custom Filters
• Wild Cards

You have to make the following additional changes for each of these filters:

1. Use setAnd method in CTCustomFilters class - AND Filter.
2. Define Multiple CTCustomFilter and specify Filter clauses.
3. Reload Data to match filter conditions.
4. Do not use the method if you want to implement an OR filter.

Here are a few examples;

"Does Not Begin With" AND "Does Not End With" - Custom Filter

/* Add a Custom Filter */
CTCustomFilters myCustomFilter=myFilterColumn.addNewCustomFilters();
/* Specify that this is an AND filter */
myCustomFilter.setAnd(true);
/* Add filters */
CTCustomFilter myFilter1= myCustomFilter.addNewCustomFilter();  
CTCustomFilter myFilter2= myCustomFilter.addNewCustomFilter();  
/* Does not Begin With Filter */                
myFilter1.setOperator(STFilterOperator.NOT_EQUAL);
myFilter1.setVal("M*");  
/* Does not End With Filter */
myFilter2.setOperator(STFilterOperator.NOT_EQUAL);
myFilter2.setVal("*1");


XSSFRow r1;
/* Implement Multiple Custom Filter and Hide Rows that do not match */
for(Row r : my_sheet) {
        for (Cell c : r) {
                if (c.getColumnIndex()==1 && (c.getStringCellValue().startsWith("M")||c.getStringCellValue().endsWith("1")) ) {
                        r1=(XSSFRow) c.getRow();
                        if (r1.getRowNum()!=0) { /* Ignore top row */                                                                           
                                        r1.getCTRow().setHidden(true); }
                }                               
        }
}

Give a try on the custom filters and let us know how you go on them.

Download Custom Filters Base Code
See all our Apache POI Tutorials