Use AutoFilter to Filter Excel Data in Java

Filtering data based on criteria is a very important feature. It helps the user to understand and analyze data easily. You can use the auto filter feature of Microsoft Excel to find, show or hide values in a single or multiple columns based on the choices that you select from list. When the filter is applied, then all the rows that do not meet your criteria are hidden completely.

You can use Aspose.Cells for Java to apply filter on your Excel data programmatically in Java easily with few lines of code. It can also be used to perform wide range of functions on Excel documents e.g. you can create, edit and manipulate Excel spreadsheets in any platform without any need to install Microsoft Excel or without using any sort of Microsoft Office automation.

Article Description

The purpose of this article is to explain how developers can use AutoFilter to filter Excel data in Java.

Supported Platforms

Aspose.Cells API supports various platforms including Java, .NET, C++, Android, JavaScript, PHP etc. Besides, Aspose.Cells is available in Cloud as REST or RESTful APIs.

Filter Data using AutoFilter in Microsoft Excel

Please do the following steps to filter data using AutoFilter in Microsoft Excel.

• Select the columns and click Data > Filter button inside the Sort & Filter section.
• Click the AutoFilter dropdown, select your choices from list and press OK.
• All the rows that do not match your criteria will be filtered out. Please see this snapshot for detail.

Apply AutoFilter in Microsoft Excel which can also be done with Aspose.Cells API programmatically.

Filter Data using AutoFilter in Aspose.Cells

This section explains how to filter Excel data using AutoFilter with Aspose.Cells API.

Sample Input Microsoft Excel Document

For demonstration, we will use the following sample input Microsoft Excel document that contains some data in four columns. We will apply AutoFilter on Vehicle and Color columns. Once, rows are filtered out, some of them will become hidden and the Grand Total for Qty1 and Qty2 columns shown inside the red lines will be modified accordingly.

Sample Input Microsoft Excel Document containing Data for applying AutoFilter using Aspose.Cells API.

Sample Code

The following sample code applies AutoFilter on Microsoft Excel data by performing these steps

• Load sample input Microsoft Excel document containing the sample data for auto filter.
• Apply auto filter to range.
• Adds two filters to first column.
• Refresh the auto filter.
• Adds another two filters to second column.
• Refresh the auto filter.
• Save the workbook in XLSX format. You can also save it in other formats e.g. XLS, XLSB, XLSM etc.

	// Directory path for input and output Excel files.
	String dirPath = "D:/Download/";
	// Load the input Excel file containing the sample data.
	Workbook wb = new Workbook(dirPath + "sampleUseAutoFilterToFilterExcelData.xlsx");
	// Access first worksheet.
	Worksheet ws = wb.getWorksheets().get(0);
	// Apply auto filter to the range.
	ws.getAutoFilter().setRange("D3:G3");
	// Add filter to first column (i.e. Vehicle) inside the range – Criteria –> Bike
	ws.getAutoFilter().addFilter(0, "Bike");
	// Add filter to first column (i.e. Vehicle) inside the range – Criteria –> Car
	ws.getAutoFilter().addFilter(0, "Car");
	// Refresh the auto filter.
	ws.getAutoFilter().refresh();
	// Add filter to second column (i.e. Color) inside the range – Criteria –> Green
	ws.getAutoFilter().addFilter(1, "Green");
	// Add filter to second column (i.e. Color) inside the range – Criteria –> Blue
	ws.getAutoFilter().addFilter(1, "Blue");
	// Refresh the auto filter.
	ws.getAutoFilter().refresh();
	// Save the workbook in XLSX format.
	// You can also save it to XLS or other formats.
	wb.save(dirPath + "outputUseAutoFilterToFilterExcelData.xlsx", SaveFormat.XLSX);

view raw

Use_AutoFilter_to_Filter_Excel_Data.java

hosted with ❤ by GitHub

Output Microsoft Excel Document by Aspose.Cells after applying AutoFilter

The following snapshot shows the Output Microsoft Excel Document generated by Aspose.Cells after applying AutoFilter with the code given above. As you can see, it now shows the filtered rows and new values of Grand Total displayed inside the red lines.

Output Microsoft Excel Document by Aspose.Cells API after applying AutoFilter.