数据筛选
自动筛选数据
自动筛选是选择仅显示列表中您想要显示的项的最快方法。自动筛选功能允许用户根据一组标准过滤列表中的项目。根据文本、数字或日期进行筛选。
Microsoft Excel 中的自动筛选
要在 Microsoft Excel 中启用自动筛选功能:
- 单击工作表中的标题行。
- 从“数据”菜单中,选择“筛选”,然后选择“自动筛选”。
在工作表上应用自动筛选时,过滤开关 (黑色箭头) 会出现在列标题右侧。
- 单击筛选箭头,以查看筛选选项列表。
一些自动筛选选项包括:
选项 | 描述 |
---|---|
All | 在列表中显示所有项目一次。 |
Custom | 自定义包含/不包含等筛选条件 |
Filter by Color | 基于填充颜色的筛选 |
Date Filters | 基于不同日期标准的行筛选 |
Number Filters | 数字类型的不同筛选,如比较、平均值和前 10 等。 |
Text Filters | 不同的筛选,如以…开始、以…结束、包含等。 |
Blanks/Non Blanks | 这些筛选可以通过文本筛选空白值实现。 |
用户可以使用这些选项手动筛选其 Microsoft Excel 工作表中的数据。 |
Aspose.Cells 自动筛选
Aspose.Cells 提供了一个代表 Excel 文件的类,Workbook。Workbook 类包含一个WorksheetCollection,允许访问 Excel 文件中的每个工作表。
工作表由Worksheet类表示。Worksheet类提供了广泛的属性和方法来管理工作表。要创建自动筛选,使用Worksheet类的AutoFilter属性。AutoFilter属性是AutoFilter 类的对象,为指定组成标题行的单元格范围提供了Range属性。自动筛选应用于组成标题行的单元格范围。
在每个工作表中,您只能指定一个筛选范围。这受到Microsoft Excel的限制。对于自定义数据筛选,使用AutoFilter.Custom方法。
在下面的示例中,我们使用 Aspose.Cells 创建了与上一节中使用 Microsoft Excel 创建的相同的自动筛选。
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java | |
// Instantiating a Workbook object | |
Workbook workbook = new Workbook("AFData.xls"); | |
// Accessing the first worksheet in the Excel file | |
Worksheet worksheet = workbook.getWorksheets().get(0); | |
// Creating AutoFilter by giving the cells range | |
AutoFilter autoFilter = worksheet.getAutoFilter(); | |
autoFilter.setRange("A1:B1"); | |
// Saving the modified Excel file | |
workbook.save("AFData_out.xls"); | |
// Print message | |
System.out.println("Process completed successfully"); |
不同类型的筛选
Aspose.Cells 提供了多种选项,应用不同类型的筛选,如颜色筛选、日期筛选、数字筛选、文本筛选、空白筛选和非空白筛选。
填充色
Aspose.Cells提供了一个函数addFillColorFilter,根据单元格的填充颜色属性对数据进行筛选。在下面给出的示例中,使用具有表格第一列中不同填充颜色的模板文件来测试颜色筛选功能。可以下载以下文件来检查功能。
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java | |
// Instantiating a Workbook object | |
// Opening the Excel file through the file stream | |
Workbook workbook = new Workbook("ColouredCells.xlsx"); | |
// Instantiating a CellsColor object for foreground color | |
CellsColor clrForeground = workbook.createCellsColor(); | |
clrForeground.setColor(Color.getRed()); | |
// Instantiating a CellsColor object for background color | |
CellsColor clrBackground = workbook.createCellsColor(); | |
clrBackground.setColor(Color.getWhite()); | |
// Accessing the first worksheet in the Excel file | |
Worksheet worksheet = workbook.getWorksheets().get(0); | |
// Call AddFillColorFilter function to apply the filter | |
worksheet.getAutoFilter().addFillColorFilter(0, BackgroundType.SOLID, clrForeground, clrBackground); | |
// Call refresh function to update the worksheet | |
worksheet.getAutoFilter().refresh(); | |
// Saving the modified Excel file | |
workbook.save("FilteredColouredCells.xlsx"); | |
// Print message | |
System.out.println("Process completed successfully"); |
日期
可以实现不同类型的日期过滤,比如过滤所有包含2018年1月日期的行。以下示例代码演示了使用addDateFilter函数来实现此过滤。以下文件可用于测试此功能。
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java | |
// Instantiating a Workbook object | |
// Opening the Excel file through the file stream | |
Workbook workbook = new Workbook("Date.xlsx"); | |
// Accessing the first worksheet in the Excel file | |
Worksheet worksheet = workbook.getWorksheets().get(0); | |
// Call AddDateFilter function to apply the filter | |
worksheet.getAutoFilter().addDateFilter(0, DateTimeGroupingType.MONTH, 2018, 1, 0, 0, 0, 0); | |
// Call refresh function to update the worksheet | |
worksheet.getAutoFilter().refresh(); | |
// Saving the modified Excel file | |
workbook.save("FilteredDate.xlsx"); | |
// Print message | |
System.out.println("Process completed successfully"); |
动态日期
有时需要基于日期的动态过滤,例如筛选出所有包含一月日期的单元格,不考虑年份。在这种情况下,可以使用DynamicFilter函数,如下示例代码中所示。以下文件可用于测试。
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java | |
// Instantiating a Workbook object | |
// Opening the Excel file through the file stream | |
Workbook workbook = new Workbook("Date.xlsx"); | |
// Accessing the first worksheet in the Excel file | |
Worksheet worksheet = workbook.getWorksheets().get(0); | |
// Call DynamicFilter function to apply the filter | |
worksheet.getAutoFilter().dynamicFilter(0, DynamicFilterType.JANUARY); | |
// Call refresh function to update the worksheet | |
worksheet.getAutoFilter().refresh(); | |
// Saving the modified Excel file | |
workbook.save("FilteredDynamicDate.xlsx"); |
数字
可以使用Aspose.Cells应用自定义过滤,比如选择数值在给定范围之间的单元格。以下示例演示了使用custom()函数来过滤数字的用法。示例文件可从以下链接下载。
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java | |
// Instantiating a Workbook object | |
// Opening the Excel file through the file stream | |
Workbook workbook = new Workbook("Date.xlsx"); | |
// Accessing the first worksheet in the Excel file | |
Worksheet worksheet = workbook.getWorksheets().get(0); | |
// Call DynamicFilter function to apply the filter | |
worksheet.getAutoFilter().dynamicFilter(0, DynamicFilterType.JANUARY); | |
// Call refresh function to update the worksheet | |
worksheet.getAutoFilter().refresh(); | |
// Saving the modified Excel file | |
workbook.save("FilteredDynamicDate.xlsx"); |
文本
如果某列包含文本并且需要选择包含特定文本的单元格,可以使用filter()函数。在下面的示例中,模板文件包含一组国家,需要选择包含特定国家名称的行。以下代码演示了使用以下示例文件来过滤文本。
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java | |
// Instantiating a Workbook object | |
// Opening the Excel file through the file stream | |
Workbook workbook = new Workbook("Text.xlsx"); | |
// Accessing the first worksheet in the Excel file | |
Worksheet worksheet = workbook.getWorksheets().get(0); | |
// Call Filter function to apply the filter | |
worksheet.getAutoFilter().filter(0, "Angola"); | |
// Call refresh function to update the worksheet | |
worksheet.getAutoFilter().refresh(); | |
// Saving the modified Excel file | |
workbook.save("FilteredText.xlsx"); |
空白
如果某列包含文本,有一些单元格为空白,并且需要只选择存在空白单元格的行,可以使用matchBlanks()函数,如下所示。可以从以下链接下载示例文件。
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java | |
// Instantiating a Workbook object | |
// Opening the Excel file through the file stream | |
Workbook workbook = new Workbook("Blank.xlsx"); | |
// Accessing the first worksheet in the Excel file | |
Worksheet worksheet = workbook.getWorksheets().get(0); | |
// Call matchBlanks function to apply the filter | |
worksheet.getAutoFilter().matchBlanks(0); | |
// Call refresh function to update the worksheet | |
worksheet.getAutoFilter().refresh(); | |
// Saving the modified Excel file | |
workbook.save("FilteredBlank.xlsx"); |
非空白
当需要过滤包含任何文本的单元格时,可以使用以下演示以下MatchNonBlanks过滤函数。可以从以下链接下载示例文件。
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java | |
// Instantiating a Workbook object | |
// Opening the Excel file through the file stream | |
Workbook workbook = new Workbook("Blank.xlsx"); | |
// Accessing the first worksheet in the Excel file | |
Worksheet worksheet = workbook.getWorksheets().get(0); | |
// Call matchBlanks function to apply the filter | |
worksheet.getAutoFilter().matchBlanks(0); | |
// Call refresh function to update the worksheet | |
worksheet.getAutoFilter().refresh(); | |
// Saving the modified Excel file | |
workbook.save("FilteredBlank.xlsx"); |
包含自定义筛选
Excel提供了自定义筛选功能,例如筛选包含特定字符串的行。这一功能在Aspose.Cells中可用,并通过以下演示在示例文件中筛选名称。示例文件可从以下链接下载。
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java | |
// Instantiating a Workbook object containing sample data | |
Workbook workbook = new Workbook("sourseSampleCountryNames.xlsx"); | |
// Accessing the first worksheet in the Excel file | |
Worksheet worksheet = workbook.getWorksheets().get(0); | |
// Creating AutoFilter by giving the cells range | |
worksheet.getAutoFilter().setRange("A1:A18"); | |
// Initialize filter for rows containing string "Ba" | |
worksheet.getAutoFilter().custom(0, FilterOperatorType.CONTAINS, "Ba"); | |
//Refresh the filter to show/hide filtered rows | |
worksheet.getAutoFilter().refresh(); | |
// Saving the modified Excel file | |
workbook.save("outSourseSampleCountryNames.xlsx"); |
不包含特定字符串的自定义筛选
Excel提供了自定义筛选功能,例如筛选不包含特定字符串的行。这一功能在Aspose.Cells中可用,并通过以下演示在示例文件中筛选名称。示例文件可从以下链接下载。
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java | |
// Instantiating a Workbook object containing sample data | |
Workbook workbook = new Workbook("sourseSampleCountryNames.xlsx"); | |
// Accessing the first worksheet in the Excel file | |
Worksheet worksheet = workbook.getWorksheets().get(0); | |
// Creating AutoFilter by giving the cells range | |
worksheet.getAutoFilter().setRange("A1:A18"); | |
// Initialize filter for rows containing string "Ba" | |
worksheet.getAutoFilter().custom(0, FilterOperatorType.NOT_CONTAINS, "Ba"); | |
//Refresh the filter to show/hide filtered rows | |
worksheet.getAutoFilter().refresh(); | |
// Saving the modified Excel file | |
workbook.save("outSourseSampleCountryNames.xlsx"); |
以指定字符串开头的自定义筛选
Excel提供了自定义筛选功能,例如筛选以特定字符串开头的行。这一功能在Aspose.Cells中可用,并通过以下演示在示例文件中筛选名称。
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java | |
// Instantiating a Workbook object containing sample data | |
Workbook workbook = new Workbook(sourceDir + "sourseSampleCountryNames.xlsx"); | |
// Accessing the first worksheet in the Excel file | |
Worksheet worksheet = workbook.Worksheets[0]; | |
// Creating AutoFilter by giving the cells range | |
worksheet.AutoFilter.Range = "A1:A18"; | |
// Initialize filter for rows starting with string "Ba" | |
worksheet.AutoFilter.Custom(0, FilterOperatorType.BeginsWith, "Ba"); | |
//Refresh the filter to show/hide filtered rows | |
worksheet.AutoFilter.Refresh(); | |
// Saving the modified Excel file | |
workbook.Save(outputDir + "outSourseSampleCountryNames.xlsx"); |
以指定字符串结尾的自定义筛选
Excel提供了自定义筛选功能,例如筛选以特定字符串结尾的行。这一功能在Aspose.Cells中可用,并通过以下演示在示例文件中筛选名称。
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java | |
// Instantiating a Workbook object containing sample data | |
Workbook workbook = new Workbook(srcDir + "sourseSampleCountryNames.xlsx"); | |
// Accessing the first worksheet in the Excel file | |
Worksheet worksheet = workbook.getWorksheets().get(0); | |
// Creating AutoFilter by giving the cells range | |
worksheet.getAutoFilter().setRange("A1:A18"); | |
// Initialize filter for rows starting with string "Ba" | |
worksheet.getAutoFilter().custom(0, FilterOperatorType.ENDS_WITH, "ia"); | |
//Refresh the filter to show/hide filtered rows | |
worksheet.getAutoFilter().refresh(); | |
// Saving the modified Excel file | |
workbook.save(outDir + "outSourseSampleCountryNames.xlsx"); |