在Java中将过滤器添加到Excel

最新推荐文章于 2022-06-30 22:25:29 发布

cunxiedian8614

最新推荐文章于 2022-06-30 22:25:29 发布

阅读量427

点赞数

文章标签： java

当Excel工作表中的数据很大时，可以在选定区域中添加“文本过滤器”，“数字过滤器”，“颜色过滤器”或“日期过滤器”以过滤数据。筛选后的工作表将显示您需要的数据，并隐藏其余的数据。

在下面显示的工作表中，您可以在“类别”列中添加文本过滤器，以使工作表仅显示数据的一种类别。您可以在“数量”列中添加数字过滤器，以使工作表显示包含80-100之间数字的行；您可以添加滤色器以显示具有背景色的行；您还可以在“日期”列中添加日期过滤器，以显示特定月份内的数据。

首先，将Spire.Xls.jar文件导入Java应用程序。

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.

方法2：如果要创建Maven项目，则可以通过将以下配置添加到pom.xml来轻松添加jar依赖项。

<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);