When the data in Excel worksheet is very large, you can add "text filter", "number filter", "color filter" or "date filter" in a selected area to filter the data. The filtered worksheet displays the data you need and hides the rest.
In the worksheet shown below, you can add a text filter in the column "category" to make the worksheet display only one category of data; you can add a number filter in the column "quantity" to make the worksheet display the rows containing the number between 80-100; you can add a color filter to display the rows having background color; you can also add a date filter to the “date” column to display the data within the specific month.
Let’s get started by importing the Spire.Xls.jar file in your java application.
Add Spire.Xls.jar as dependency
Method 1: Download Free Spire.XLS for Java pack, unzip it and you’ll get Spire.Doc.jar file from the “lib” folder. Import the jar file in your project as a denpendency.
Method 2: If you are creating a Maven project, you can easily add the jar dependency by adding the following configurations to the pom.xml.
<repositories>
<repository>
<id>com.e-iceblue</id>
<name>e-iceblue</name>
<url>http://repo.e-iceblue.com/nexus/content/groups/public/</url>
</repository>
</repositories>
<dependencies>
<dependency>
<groupId> e-iceblue </groupId>
<artifactId>spire.xls.free</artifactId>
<version>2.2.0</version>
</dependency>
</dependencies>
Example 1. Text Filter
//Create a Workbook object
Workbook wb = new Workbook();
//Load a sample Excel file
wb.loadFromFile("C:\\Users\\Administrator\\Desktop\\sample.xlsx");
//Get the first worksheet
Worksheet sheet = wb.getWorksheets().get(0);
//Get the AutoFiltersCollection object
AutoFiltersCollection filters = sheet.getAutoFilters();
//Set the cell range where the filters will be added
filters.setRange(sheet.getCellRange(2,2,16,5));
//Add a filter to the second column of the selected range and set the filter criteria, which is the text "C"
filters.addFilter(1, "C");
//Execute filter function
filters.filter();
//Save to file
wb.saveToFile("TextFilter.xlsx", ExcelVersion.Version2013);
Example 2. Number Filter
//Create a Workbook object
Workbook wb = new Workbook();
//Load a sample Excel file
wb.loadFromFile("C:\\Users\\Administrator\\Desktop\\sample.xlsx");
//Get the first worksheet
Worksheet sheet = wb.getWorksheets().get(0);
//Get the AutoFiltersCollection object
AutoFiltersCollection filters = sheet.getAutoFilters();
//Set the cell range where the filters will be added
filters.setRange(sheet.getCellRange(2,2,16,5));
//Add a custom filter to the third column of the selected range and set the filter criteria, which is to select the number greater than or equal to 80 and less than or equal to 100
filters.customFilter(2, FilterOperatorType.GreaterOrEqual, 80, true, FilterOperatorType.LessOrEqual, 100);
//Execute filter function
filters.filter();
//Save to file
wb.saveToFile("NumberFilter.xlsx", ExcelVersion.Version2013);
Example 3. Color Filter
//Create a Workbook object
Workbook wb = new Workbook();
//Load a sample Excel file
wb.loadFromFile("C:\\Users\\Administrator\\Desktop\\sample.xlsx");
//Get the first worksheet
Worksheet sheet = wb.getWorksheets().get(0);
//Get the AutoFiltersCollection object
AutoFiltersCollection filters = sheet.getAutoFilters();
//Set the cell range where the filters will be added
filters.setRange(sheet.getCellRange(2,2,16,5));
//Add a color filter to the first column of the selected range and set the filter criteria, that is, the background color of cell B4
filters.addFillColorFilter(0, sheet.getCellRange("B4").getStyle().getColor());
//Execute filter function
filters.filter();
//Save to file
wb.saveToFile("ColorFilter.xlsx", ExcelVersion.Version2013);
Example 4. Date Filter
//Create a Workbook object
Workbook wb = new Workbook();
//Load a sample Excel file
wb.loadFromFile("C:\\Users\\Administrator\\Desktop\\sample.xlsx");
//Get the first worksheet
Worksheet sheet = wb.getWorksheets().get(0);
//Get the AutoFiltersCollection object
AutoFiltersCollection filters = sheet.getAutoFilters();
//Set the cell range where the filters will be added
filters.setRange(sheet.getCellRange(2,2,16,5));
//Add a date filter in the fourth column of the selected range, using April 2019 as the filter criteria
filters.addDateFilter(3, DateTimeGroupingType.Month, 2019, 4, 1, 0, 0, 0 );
//Execute filter function
filters.filter();
//Save to file
wb.saveToFile("DateFilter.xlsx", ExcelVersion.Version2013);
Top comments (0)