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

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

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

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.