Set Autofilter in Excel - Java POI Example Program

Autofilters - Introduction


In this tutorial,we will discuss how to set autofilters in Excel for your worksheet data, in Java, using Apache POI library. Here is small screenshot that explains what autofilters is all about

Setting AutoFilters - Excel Java POI Example Program
Setting AutoFilters - Excel Java POI Example Program
When you enable auto filter for your data, it helps you to easily filter your data across the columns where you have enabled it. Often, when you create big reports with POI, you may want to enable auto filtering, so that the target audience of your document, can filter the data to suit to their needs. Fortunately, Apache POI offers a very simple and elegant mechanism to enable auto filter on your worksheet data. Let us see how to do this for XLS / XLSX workbook formats in this tutorial.

XLS - setAutoFilter- Enable Auto-filter - Apache POI Example Program


You enable auto filter by using setAutoFilter method available in the HSSFSheet class. Here is a Java program that explains how to enable auto filter for XLS format workbooks.
import java.io.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.*;
import org.apache.poi.ss.usermodel.ComparisonOperator;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.util.CellRangeAddress;
public class Autofilter {  
        public static void main(String[] args) throws Exception{
                /* Create Workbook and Worksheet - Add Input Rows */
                HSSFWorkbook my_workbook = new HSSFWorkbook();
                HSSFSheet my_sheet = my_workbook.createSheet("Autofilter");
                /* Create Test Data */
                /* Add header rows */
                Row row0 = my_sheet.createRow(0);               
                row0.createCell(0).setCellValue("Quarter");
                row0.createCell(1).setCellValue("Brand");
                row0.createCell(2).setCellValue("Count");
                /* Add test data*/
                Row row1 = my_sheet.createRow(1);       
                row1.createCell(0).setCellValue("Q1");
                row1.createCell(1).setCellValue("A");
                row1.createCell(2).setCellValue(new Double(10));
                
                Row row2 = my_sheet.createRow(2);       
                row2.createCell(0).setCellValue("Q1");
                row2.createCell(1).setCellValue("B");
                row2.createCell(2).setCellValue(new Double(15));
                
                Row row3 = my_sheet.createRow(3);       
                row3.createCell(0).setCellValue("Q2");
                row3.createCell(1).setCellValue("A");
                row3.createCell(2).setCellValue(new Double(23));
                
                Row row4 = my_sheet.createRow(4);       
                row4.createCell(0).setCellValue("Q2");
                row4.createCell(1).setCellValue("C");
                row4.createCell(2).setCellValue(new Double(24));
                
                my_sheet.setAutoFilter(CellRangeAddress.valueOf("A1:C5"));
                /* Write changes to the workbook */
                FileOutputStream out = new FileOutputStream(new File("C:\\excel_auto_filter.xls"));
                my_workbook.write(out);
                out.close();
        }
}

One line, and you are done. The output of this program is shown below:
XLS-SetAutoFilter-Excel POI Example Output
XLS-SetAutoFilter-Excel POI Example Output

XLSX - setAutoFilter- - Apache POI Example Program


The Java program that enables an XLSX worksheet to have filters over a range of cells is provided in this section. Note that you can even open an existing workbook in update mode and enable auto filter on it if required. Our examples are based for new workbooks though.
import java.io.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
public class AutofilterXLSX {  
        public static void main(String[] args) throws Exception{
                /* Auto filter for xlsx workbooks */
                XSSFWorkbook my_workbook = new XSSFWorkbook();
                XSSFSheet my_sheet = my_workbook.createSheet("Autofilter");
                /* Create Test Data */
                /* Add header rows */
                Row row0 = my_sheet.createRow(0);               
                row0.createCell(0).setCellValue("Quarter");
                row0.createCell(1).setCellValue("Brand");
                row0.createCell(2).setCellValue("Count");
                /* Add test data*/
                Row row1 = my_sheet.createRow(1);       
                row1.createCell(0).setCellValue("Q1");
                row1.createCell(1).setCellValue("A");
                row1.createCell(2).setCellValue(new Double(10));
                
                Row row2 = my_sheet.createRow(2);       
                row2.createCell(0).setCellValue("Q1");
                row2.createCell(1).setCellValue("B");
                row2.createCell(2).setCellValue(new Double(15));
                
                Row row3 = my_sheet.createRow(3);       
                row3.createCell(0).setCellValue("Q2");
                row3.createCell(1).setCellValue("A");
                row3.createCell(2).setCellValue(new Double(23));
                
                Row row4 = my_sheet.createRow(4);       
                row4.createCell(0).setCellValue("Q2");
                row4.createCell(1).setCellValue("C");
                row4.createCell(2).setCellValue(new Double(24));
                
                my_sheet.setAutoFilter(CellRangeAddress.valueOf("A1:C5"));
                /* Write changes to the workbook */
                FileOutputStream out = new FileOutputStream(new File("C:\\auto_filter_output.xlsx"));
                my_workbook.write(out);
                out.close();
        }
}

The output of this program is shown below:

XLSX - POI Auto Filter Example Program - Output
XLSX - POI Auto Filter Example Program - Output
That completes our crash course on auto filters. If you have a question, you can post it in the comments section. Otherwise, see you in a different POI tutorial next time.

6 comments:

  1. Can we filter a column based on color ? Something like AutoFilter by criteria. Is it possible from java apache poi ? Please let me know




















    ReplyDelete
  2. Yes, it is possible but require a bit of wrapping on top of Apache POI. Check our post on the below link that explains this.
    http://thinktibits.blogspot.com/2014/09/Excel-auto-filter-criteria-Apache-POI-Example.html

    ReplyDelete
  3. While your example shows a way for us to apply an auto-filter, do you know if there's a way to verify there's auto-filter on a row?

    ReplyDelete
  4. Can we apply filter for large excel file around 32mb

    ReplyDelete
  5. How to filter xls file particular set of column by string in it. And get no of visible active rows after filter.

    And that value should paste it to another CSV file particular cell

    ReplyDelete