Excel Auto Filter - criteria Apache POI Example

Let us discuss how to expand the auto filter capabilities in Apache POI in this post.I wrote a tutorial initially that explained how to set auto filters using POI. But, that was very basic in nature and (at the time of writing) advanced filtering (Auto Filter with criteria) operations are not available in POI. In this post, you will find some Java examples to write your own auto-filter conditions in POI with the help of OOXML. We will use the base example from the post earlier, so make sure you grab the code from there.

There can be multiple conditions of  basic auto filtering as provided below:

1) Filter on a single column - with a single Cell Value
2) Filter on a single column - Multiple values
3) Filter on Multiple Columns - One Cell Value
4) Filter on Multiple Columns- Multiple Cell Values

Auto Filter Single Criteria - POI Example

If you want to define a filter value, which is a value on the cell , you can follow the code provided in this section. The high level steps are captured below:

• Define the Basic Auto Filter using setAutoFilter method.
• Get CTAutoFilter object.
• Add a new filter column - CTFilterColumn
• Create a CTFilter object for the Filter Column
• Set the Filter Criteria
• Loop through your Rows / Columns and Apply the Filter Criteria (Yes! you have to do this)
• Write workbook to file.

You have to loop through the rows and columns to apply the filter criteria - otherwise when you open the workbook the filter column will be set with a value but the rows would not be refreshed with this value. The code is shown below:

/* Step-1: Get the CTAutoFilter Object */
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);
/* Step-4: Add new Filter */
CTFilter myFilter=myFilterColumn.addNewFilters().insertNewFilter(0);
/* Step -5: Define Auto Filter Condition - We filter Brand with Value of "A" */
myFilter.setVal("A");                           
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().equals("A")) {
                        r1=(XSSFRow) c.getRow();
                        if (r1.getRowNum()!=0) { /* Ignore top row */
                                /* Hide Row that does not meet Filter Criteria */
                                r1.getCTRow().setHidden(true); }
                                }                               
        }
}

The full code is available on the link. The output produced by the code is shown in the screen dump below:

Apache POI AutoFilter On Cell Value Output

Auto Filter on Multiple Values - Example

If you want to auto filter on multiple values inside a same column, (for example, A and B), you have to make simple modifications to the code provided earlier. This is shown below:

/* Step-1: Get the CTAutoFilter Object */
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);
/* Step-4: Add Multiple Filters on a Single Column */
CTFilters listofFilters=myFilterColumn.addNewFilters();
CTFilter myFilter1=listofFilters.addNewFilter();
CTFilter myFilter2=listofFilters.addNewFilter();
/* Step-5: Define Multiple Filters */
myFilter1.setVal("A");  // Filter by Both A and B
myFilter2.setVal("B");                  
/* Add this to a list for comparison */
List<String> list = new ArrayList<String>();
list.add("A");
list.add("B");          
XSSFRow r1;
/* Step-6: Loop through Rows and Apply Filter */
for(Row r : my_sheet) {
        for (Cell c : r) {
                if (c.getColumnIndex()==1 && !list.contains(c.getStringCellValue())) {
                        r1=(XSSFRow) c.getRow();
                        if (r1.getRowNum()!=0) { /* Ignore top row */
                                /* Hide Row that does not meet Filter Criteria */
                                r1.getCTRow().setHidden(true); }
                }                               
        }
}

You have to include java.util.ArrayList in the import in addition. Note that we have used addNewFilter method in CTFilters class in this case.The resulting output is shown in the screen dump below:

Apache POI Multiple Filters - AutoFilter Example

Auto-Filter Multiple Columns - Apache POI Example

Now that you know how to apply Auto-Filter on single column with conditions, it is very easy to extend this to support multiple columns.  Here we will filter all rows with Quarter set to Q1 and Brand set to A. The change you have to do would be to create multiple CTFilterColumn objects and define your filters. And you have to refresh your rows and columns to compare against both the columns. The Java code is provided below:

/* Step-1: Get the CTAutoFilter Object */
CTAutoFilter sheetFilter=my_sheet.getCTWorksheet().getAutoFilter();             

/* Step -2: Filter Multiple Columns in POI with Auto Filter */
CTFilterColumn  myFilterColumn=sheetFilter.insertNewFilterColumn(0);            
myFilterColumn.setColId(1L);

CTFilterColumn  myFilterColumn2=sheetFilter.insertNewFilterColumn(1);           
myFilterColumn2.setColId(0L);
                
CTFilters firstColumnFilter=myFilterColumn.addNewFilters();
CTFilter myFilter1=firstColumnFilter.addNewFilter();

CTFilters secondColumnFilter=myFilterColumn2.addNewFilters();
CTFilter myFilter2=secondColumnFilter.addNewFilter();

/* Define Filter Conditions Across both columns */

myFilter1.setVal("A");
myFilter2.setVal("Q1");                 

/* Add Filter Conditions to List */

List<String> list1 = new ArrayList<String>();
list1.add("A");

List<String> list2 = new ArrayList<String>();
list2.add("Q1");

/* Refresh Records to Match Multiple Filter Conditions */
XSSFRow r1;
/* Step-6: Loop through Rows and Apply Filter */
for(Row r : my_sheet) {
        for (Cell c : r) {
                //big IF condition to check both columns with value
                if ( (c.getColumnIndex()==0 && !list2.contains(c.getStringCellValue())) || (c.getColumnIndex()==1 && !list1.contains(c.getStringCellValue())) ){
                        r1=(XSSFRow) c.getRow();
                        if (r1.getRowNum()!=0) { /* Ignore top row */
                        /* Hide Row that does not meet Filter Criteria */
                                r1.getCTRow().setHidden(true); }
                }                               
        }
}

Check out all our Apache POI Tutorials

Auto Filter Many Columns with Multiple criteria in POI

Finally, the last bit. Again, with simple modifications to the above section, you can get auto filtering done on multiple columns with multiple criteria. This is shown below: (you have to use the code from above section and replace part of it)

CTFilters firstColumnFilter=myFilterColumn.addNewFilters();
CTFilter myFilter1_1=firstColumnFilter.addNewFilter();
CTFilter myFilter1_2=firstColumnFilter.addNewFilter();

CTFilters secondColumnFilter=myFilterColumn2.addNewFilters();
CTFilter myFilter2_1=secondColumnFilter.addNewFilter();
CTFilter myFilter2_2=secondColumnFilter.addNewFilter();

/* Define Filter Conditions */

myFilter1_1.setVal("A");
myFilter1_2.setVal("B");
myFilter2_1.setVal("Q1");                       
myFilter2_2.setVal("Q2");                       

List<String> list1 = new ArrayList<String>();
list1.add("A");
list1.add("B");

List<String> list2 = new ArrayList<String>();
list2.add("Q1");
list2.add("Q2");

For a change, the output of this code in XML format is shown below. You can see that multiple conditions are applied across multiple columns;

        <autoFilter ref="A1:C5">
                <filterColumn colId="1">
                        <filters>
                                <filter val="A"/>
                                <filter val="B"/>
                        </filters>
                </filterColumn>
                <filterColumn colId="0">
                        <filters>
                                <filter val="Q1"/>
                                <filter val="Q2"/>
                        </filters>
                </filterColumn>
        </autoFilter>

You would need ooxml-schemas-1.1.jar file to compile the Java examples in this post. You can download that from the Apache Website. The examples are tested on the latest version of POI 3.11. (at the time of writing)