数据筛选

Microsoft Excel提供了自动筛选工作表数据的一些良好功能。Aspose.Cells for Python via .NET完全支持Microsoft Excel的自动筛选功能。本文解释了如何在Microsoft Excel中使用这些功能，以及如何使用Aspose.Cells for Python via .NET进行编码。

自动筛选数据

自动筛选是选择仅显示列表中您想要显示的项的最快方法。自动筛选功能允许用户根据一组标准过滤列表中的项目。根据文本、数字或日期进行筛选。

Microsoft Excel 中的自动筛选

要在 Microsoft Excel 中启用自动筛选功能：

1. 单击工作表中的标题行。
2. 从数据菜单中选择筛选，然后选择自动筛选。

在工作表上应用自动筛选时，过滤开关 (黑色箭头) 会出现在列标题右侧。

1. 单击筛选箭头，以查看筛选选项列表。

一些自动筛选选项包括：

选项	描述
All	在列表中显示所有项目一次。
Custom	自定义包含/不包含等筛选条件
Filter by Color	基于填充颜色的筛选
Date Filters	基于不同日期标准的行筛选
Number Filters	在数字上应用不同类型的筛选，例如比较，平均值和前10名等。
Text Filters	不同的筛选，如以…开始、以…结束、包含等。
Blanks/Non Blanks	这些筛选可以通过文本筛选空白值实现。

用户可以使用这些选项手动筛选其 Microsoft Excel 工作表中的数据。

使用Aspose.Cells for Python Excel库进行自动筛选

Aspose.Cells for Python via .NET提供的类Workbook代表Excel文件。Workbook类包含一个Worksheets集合，允许访问Excel文件中的每个工作表。

工作表由Worksheet类表示。Worksheet类提供了广泛的属性和方法来管理工作表。要创建自动筛选，请使用Worksheet类的AutoFilter属性。AutoFilter属性是AutoFilter类的对象，该类提供了Range属性，用于指定构成标题行的单元格范围。自动筛选应用于构成标题行的单元格范围。

在每个工作表中，您只能指定一个筛选范围。这是由Microsoft Excel限制的。要进行自定义数据过滤，请使用AutoFilter.Custom方法。

在下面的示例中，我们使用Aspose.Cells for Python via .NET创建了与上述在Microsoft Excel中创建的相同的自动筛选。

from aspose.cells import Workbook

#  For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET
#  The path to the documents directory.
dataDir = RunExamples.GetDataDir(".")
#  Instantiating a Workbook object
#  Opening the Excel file through the file stream
workbook = Workbook(dataDir + "book1.xls")
#  Accessing the first worksheet in the Excel file
worksheet = workbook.worksheets[0]
#  Creating AutoFilter by giving the cells range of the heading row
worksheet.auto_filter.range = "A1:B1"
#  Saving the modified Excel file
workbook.save(dataDir + "output.out.xls")

不同类型的筛选

Aspose.Cells for Python via .NET提供了多种选项来应用不同类型的筛选，如颜色筛选，日期筛选，数字筛选，文本筛选，空白筛选和非空白筛选。

填充色

Aspose.Cells for Python via .NET 提供了 AddFillColorFilter 功能，可以根据单元格的填充颜色属性来过滤数据。在下面给出的示例中，使用了一个模板文件，该文件的工作表的第一列具有不同的填充颜色，用于测试颜色过滤功能。示例文件可以从以下链接下载。

1. ColouredCells.xlsx
2. FilteredColouredCells.xlsx

from aspose.cells import BackgroundType, Workbook
from aspose.pydrawing import Color

#  For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET
#  The path to the documents directory.
# Source directory
sourceDir = RunExamples.Get_SourceDirectory()
# Output directory
outputDir = RunExamples.Get_OutputDirectory()
#  Instantiating a Workbook object
#  Opening the Excel file through the file stream
workbook = Workbook(sourceDir + "ColouredCells.xlsx")
#  Instantiating a CellsColor object for foreground color
clrForeground = workbook.create_cells_color()
clrForeground.color = Color.red
#  Instantiating a CellsColor object for background color
clrBackground = workbook.create_cells_color()
clrBackground.color = Color.white
#  Accessing the first worksheet in the Excel file
worksheet = workbook.worksheets[0]
#  Call AddFillColorFilter function to apply the filter
worksheet.auto_filter.add_fill_color_filter(0, BackgroundType.SOLID, clrForeground, clrBackground)
#  Call refresh function to update the worksheet
worksheet.auto_filter.refresh()
#  Saving the modified Excel file
workbook.save(outputDir + "FilteredColouredCells.xlsx")

日期

可实现不同类型的日期过滤，例如筛选所有包含 2018 年 1 月的日期的行。以下示例代码演示了如何使用 AddDateFilter 函数进行此筛选。示例文件如下。

1. Date.xlsx
2. FilteredDate.xlsx

from aspose.cells import DateTimeGroupingType, Workbook

#  For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET
#  The path to the documents directory.
# Source directory
sourceDir = RunExamples.Get_SourceDirectory()
# Output directory
outputDir = RunExamples.Get_OutputDirectory()
#  Instantiating a Workbook object
#  Opening the Excel file through the file stream
workbook = Workbook(sourceDir + "Date.xlsx")
#  Accessing the first worksheet in the Excel file
worksheet = workbook.worksheets[0]
#  Call AddDateFilter function to apply the filter
worksheet.auto_filter.add_date_filter(0, DateTimeGroupingType.MONTH, 2018, 1, 0, 0, 0, 0)
#  Call refresh function to update the worksheet
worksheet.auto_filter.refresh()
#  Saving the modified Excel file
workbook.save(outputDir + "FilteredDate.xlsx")

动态日期

有时需要基于日期进行动态过滤，例如筛选所有在一月份的日期，而不考虑年份。在这种情况下，可以使用 DynamicFilter 函数，如以下示例代码所示。示例文件如下。

1. Date.xlsx
2. FilteredDynamicDate.xlsx

from aspose.cells import DynamicFilterType, Workbook

#  For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET
#  The path to the documents directory.
# Source directory
sourceDir = RunExamples.Get_SourceDirectory()
# Output directory
outputDir = RunExamples.Get_OutputDirectory()
#  Instantiating a Workbook object
#  Opening the Excel file through the file stream
workbook = Workbook(sourceDir + "Date.xlsx")
#  Accessing the first worksheet in the Excel file
worksheet = workbook.worksheets[0]
#  Call DynamicFilter function to apply the filter
worksheet.auto_filter.dynamic_filter(0, DynamicFilterType.JANUARY)
#  Call refresh function to update the worksheet
worksheet.auto_filter.refresh()
#  Saving the modified Excel file
workbook.save(outputDir + "FilteredDynamicDate.xlsx")

数字

可使用 Aspose.Cells for Python via .NET 应用自定义过滤，比如选择介于给定范围内的数字的单元格。以下示例演示了使用 Custom() 函数筛选数字的用法。示例文件如下。

1. Number.xlsx
2. FilteredNumber.xlsx

from aspose.cells import FilterOperatorType, Workbook

#  For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET
#  The path to the documents directory.
# Source directory
sourceDir = RunExamples.Get_SourceDirectory()
# Output directory
outputDir = RunExamples.Get_OutputDirectory()
#  Instantiating a Workbook object
#  Opening the Excel file through the file stream
workbook = Workbook(sourceDir + "Number.xlsx")
#  Accessing the first worksheet in the Excel file
worksheet = workbook.worksheets[0]
#  Call Custom function to apply the filter
worksheet.auto_filter.custom(0, FilterOperatorType.GREATER_OR_EQUAL, 5, True, FilterOperatorType.LESS_OR_EQUAL, 10)
#  Call refresh function to update the worksheet
worksheet.auto_filter.refresh()
#  Saving the modified Excel file
workbook.save(outputDir + "FilteredNumber.xlsx")

文本

如果某一列包含文本，并且需要选择包含特定文本的单元格，可以使用 Filter() 函数。在以下示例中，模板文件包含国家列表，要选择包含特定国家名称的行。以下代码演示了文本过滤。示例文件如下。

1. Text.xlsx
2. FilteredText.xlsx

from aspose.cells import Workbook

#  For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET
#  The path to the documents directory.
# Source directory
sourceDir = RunExamples.Get_SourceDirectory()
# Output directory
outputDir = RunExamples.Get_OutputDirectory()
#  Instantiating a Workbook object
#  Opening the Excel file through the file stream
workbook = Workbook(sourceDir + "Text.xlsx")
#  Accessing the first worksheet in the Excel file
worksheet = workbook.worksheets[0]
#  Call Filter function to apply the filter
worksheet.auto_filter.filter(0, "Angola")
#  Call refresh function to update the worksheet
worksheet.auto_filter.refresh()
#  Saving the modified Excel file
workbook.save(outputDir + "FilteredText.xlsx")

空白

如果某一列包含文本，有一些单元格为空白，并且需要筛选出只包含空白单元格的行，则可以使用 MatchBlanks() 函数，如下所示。示例文件如下。

1. 空白.xlsx
2. 筛选空白.xlsx

from aspose.cells import Workbook

#  For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET
#  The path to the documents directory.
# Source directory
sourceDir = RunExamples.Get_SourceDirectory()
# Output directory
outputDir = RunExamples.Get_OutputDirectory()
#  Instantiating a Workbook object
#  Opening the Excel file through the file stream
workbook = Workbook(sourceDir + "Blank.xlsx")
#  Accessing the first worksheet in the Excel file
worksheet = workbook.worksheets[0]
#  Call MatchBlanks function to apply the filter
worksheet.auto_filter.match_blanks(0)
#  Call refresh function to update the worksheet
worksheet.auto_filter.refresh()
#  Saving the modified Excel file
workbook.save(outputDir + "FilteredBlank.xlsx")

非空白

当需要过滤带有任何文本的单元格时，可以使用 MatchNonBlanks 过滤函数，如下所示。示例文件如下。

1. 空白.xlsx
2. 筛选非空白.xlsx

from aspose.cells import Workbook

#  For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET
#  The path to the documents directory.
# Source directory
sourceDir = RunExamples.Get_SourceDirectory()
# Output directory
outputDir = RunExamples.Get_OutputDirectory()
#  Instantiating a Workbook object
#  Opening the Excel file through the file stream
workbook = Workbook(sourceDir + "Blank.xlsx")
#  Accessing the first worksheet in the Excel file
worksheet = workbook.worksheets[0]
#  Call MatchNonBlanks function to apply the filter
worksheet.auto_filter.match_non_blanks(0)
#  Call refresh function to update the worksheet
worksheet.auto_filter.refresh()
#  Saving the modified Excel file
workbook.save(outputDir + "FilteredNonBlank.xlsx")

包含自定义筛选

Excel 提供了自定义过滤功能，可以筛选包含特定字符串的行。Aspose.Cells for Python via .NET 也提供了此功能，并通过示例文件中的名称进行了演示。示例文件如下。

1. 源样本国家名称.xlsx
2. outSourseSampleCountryNames.xlsx.

from aspose.cells import FilterOperatorType, Workbook

#  For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET
#  Instantiating a Workbook object containing sample data
workbook = Workbook("sourseSampleCountryNames.xlsx")
#  Accessing the first worksheet in the Excel file
worksheet = workbook.worksheets[0]
#  Creating AutoFilter by giving the cells range
worksheet.auto_filter.range = "A1:A18"
#  Initialize filter for rows containing string "Ba"
worksheet.auto_filter.custom(0, FilterOperatorType.CONTAINS, "Ba")
# Refresh the filter to show/hide filtered rows
worksheet.auto_filter.refresh()
#  Saving the modified Excel file
workbook.save("outSourseSampleCountryNames.xlsx")

不包含特定字符串的自定义筛选

Excel 提供了自定义过滤功能，可以筛选不包含特定字符串的行。Aspose.Cells for Python via .NET 也提供了此功能，并通过示例文件中的名称进行了演示。示例文件如下。

1. sourseSampleCountryNames.xlsx.

from aspose.cells import FilterOperatorType, Workbook

#  For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET
#  Instantiating a Workbook object containing sample data
workbook = Workbook("sourseSampleCountryNames.xlsx")
#  Accessing the first worksheet in the Excel file
worksheet = workbook.worksheets[0]
#  Creating AutoFilter by giving the cells range
worksheet.auto_filter.range = "A1:A18"
#  Initialize filter for rows containing string "Ba"
worksheet.auto_filter.custom(0, FilterOperatorType.NOT_CONTAINS, "Be")
# Refresh the filter to show/hide filtered rows
worksheet.auto_filter.refresh()
#  Saving the modified Excel file
workbook.save("outSourseSampleCountryNames.xlsx")

以指定字符串开头的自定义筛选

Excel 提供了自定义过滤功能，可以筛选以特定字符串开头的行。Aspose.Cells for Python via .NET 也提供了此功能，并通过示例文件中的名称进行了演示。示例文件如下。

1. sourseSampleCountryNames.xlsx.

from aspose.cells import FilterOperatorType, Workbook

#  For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET
#  Instantiating a Workbook object containing sample data
workbook = Workbook(sourceDir + "sourseSampleCountryNames.xlsx")
#  Accessing the first worksheet in the Excel file
worksheet = workbook.worksheets[0]
#  Creating AutoFilter by giving the cells range
worksheet.auto_filter.range = "A1:A18"
#  Initialize filter for rows starting with string "Ba"
worksheet.auto_filter.custom(0, FilterOperatorType.BEGINS_WITH, "Ba")
# Refresh the filter to show/hide filtered rows
worksheet.auto_filter.refresh()
#  Saving the modified Excel file
workbook.save(outputDir + "outSourseSampleCountryNames.xlsx")

以指定字符串结尾的自定义筛选

Excel 提供了自定义过滤功能，可以筛选以特定字符串结尾的行。Aspose.Cells for Python via .NET 也提供了此功能，并通过示例文件中的名称进行了演示。示例文件如下。

1. sourseSampleCountryNames.xlsx.

from aspose.cells import FilterOperatorType, Workbook

#  For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET
#  Instantiating a Workbook object containing sample data
workbook = Workbook(sourceDir + "sourseSampleCountryNames.xlsx")
#  Accessing the first worksheet in the Excel file
worksheet = workbook.worksheets[0]
#  Creating AutoFilter by giving the cells range
worksheet.auto_filter.range = "A1:A18"
#  Initialize filter for rows end with string "ia"
worksheet.auto_filter.custom(0, FilterOperatorType.BEGINS_WITH, "ia")
# Refresh the filter to show/hide filtered rows
worksheet.auto_filter.refresh()
#  Saving the modified Excel file
workbook.save(outputDir + "outSourseSampleCountryNames.xlsx")

高级主题

• 将 Microsoft Excel 的高级筛选应用于显示符合复杂条件的记录
• 在刷新自动筛选后获取所有隐藏行索引