数据筛选

自动筛选数据

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

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 自动筛选

Aspose.Cells提供了一个表示Excel文件的类Workbook。Workbook类包含一个Worksheets集合,允许访问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-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Instantiating a Workbook object
// Opening the Excel file through the file stream
Workbook workbook = new Workbook(dataDir + "book1.xls");
// Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.Worksheets[0];
// Creating AutoFilter by giving the cells range of the heading row
worksheet.AutoFilter.Range = "A1:B1";
// Saving the modified Excel file
workbook.Save(dataDir + "output.out.xls");

不同类型的筛选

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

填充色

Aspose.Cells 提供了 AddFillColorFilter 函数,用于根据单元格的填充颜色属性筛选数据。在下面的示例中,使用具有工作表第一列中不同填充颜色的模板文件来测试颜色筛选功能。示例文件可从以下链接下载。

  1. ColouredCells.xlsx
  2. FilteredColouredCells.xlsx
// 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
string sourceDir = RunExamples.Get_SourceDirectory();
//Output directory
string outputDir = RunExamples.Get_OutputDirectory();
// Instantiating a Workbook object
// Opening the Excel file through the file stream
Workbook workbook = new Workbook(sourceDir + "ColouredCells.xlsx");
// Instantiating a CellsColor object for foreground color
CellsColor clrForeground = workbook.CreateCellsColor();
clrForeground.Color = Color.Red;
// Instantiating a CellsColor object for background color
CellsColor clrBackground = workbook.CreateCellsColor();
clrBackground.Color = Color.White;
// Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.Worksheets[0];
// Call AddFillColorFilter function to apply the filter
worksheet.AutoFilter.AddFillColorFilter(0, BackgroundType.Solid, clrForeground, clrBackground);
// Call refresh function to update the worksheet
worksheet.AutoFilter.Refresh();
// Saving the modified Excel file
workbook.Save(outputDir + "FilteredColouredCells.xlsx");
日期

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

  1. Date.xlsx
  2. FilteredDate.xlsx
// 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
string sourceDir = RunExamples.Get_SourceDirectory();
//Output directory
string outputDir = RunExamples.Get_OutputDirectory();
// Instantiating a Workbook object
// Opening the Excel file through the file stream
Workbook workbook = new Workbook(sourceDir + "Date.xlsx");
// Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.Worksheets[0];
// Call AddDateFilter function to apply the filter
worksheet.AutoFilter.AddDateFilter(0, DateTimeGroupingType.Month, 2018, 1, 0, 0, 0, 0);
// Call refresh function to update the worksheet
worksheet.AutoFilter.Refresh();
// Saving the modified Excel file
workbook.Save(outputDir + "FilteredDate.xlsx");
动态日期

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

  1. Date.xlsx
  2. FilteredDynamicDate.xlsx
// 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
string sourceDir = RunExamples.Get_SourceDirectory();
//Output directory
string outputDir = RunExamples.Get_OutputDirectory();
// Instantiating a Workbook object
// Opening the Excel file through the file stream
Workbook workbook = new Workbook(sourceDir + "Date.xlsx");
// Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.Worksheets[0];
// Call DynamicFilter function to apply the filter
worksheet.AutoFilter.DynamicFilter(0, DynamicFilterType.January);
// Call refresh function to update the worksheet
worksheet.AutoFilter.Refresh();
// Saving the modified Excel file
workbook.Save(outputDir + "FilteredDynamicDate.xlsx");
数字

可以使用 Aspose.Cells 应用自定义筛选,例如选择在给定范围内的数字的单元格。以下示例演示了使用 Custom() 函数筛选数字的用法。示例文件如下。

  1. Number.xlsx
  2. FilteredNumber.xlsx
// 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
string sourceDir = RunExamples.Get_SourceDirectory();
//Output directory
string outputDir = RunExamples.Get_OutputDirectory();
// Instantiating a Workbook object
// Opening the Excel file through the file stream
Workbook workbook = new Workbook(sourceDir + "Number.xlsx");
// Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.Worksheets[0];
// Call Custom function to apply the filter
worksheet.AutoFilter.Custom(0, FilterOperatorType.GreaterOrEqual, 5, true, FilterOperatorType.LessOrEqual, 10);
// Call refresh function to update the worksheet
worksheet.AutoFilter.Refresh();
// Saving the modified Excel file
workbook.Save(outputDir + "FilteredNumber.xlsx");
文本

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

  1. Text.xlsx
  2. FilteredText.xlsx
// 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
string sourceDir = RunExamples.Get_SourceDirectory();
//Output directory
string outputDir = RunExamples.Get_OutputDirectory();
// Instantiating a Workbook object
// Opening the Excel file through the file stream
Workbook workbook = new Workbook(sourceDir + "Text.xlsx");
// Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.Worksheets[0];
// Call Filter function to apply the filter
worksheet.AutoFilter.Filter(0, "Angola");
// Call refresh function to update the worksheet
worksheet.AutoFilter.Refresh();
// Saving the modified Excel file
workbook.Save(outputDir + "FilteredText.xlsx");
空白

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

  1. 空白.xlsx
  2. 筛选空白.xlsx
// 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
string sourceDir = RunExamples.Get_SourceDirectory();
//Output directory
string outputDir = RunExamples.Get_OutputDirectory();
// Instantiating a Workbook object
// Opening the Excel file through the file stream
Workbook workbook = new Workbook(sourceDir + "Blank.xlsx");
// Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.Worksheets[0];
// Call MatchBlanks function to apply the filter
worksheet.AutoFilter.MatchBlanks(0);
// Call refresh function to update the worksheet
worksheet.AutoFilter.Refresh();
// Saving the modified Excel file
workbook.Save(outputDir + "FilteredBlank.xlsx");
非空白

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

  1. 空白.xlsx
  2. 筛选非空白.xlsx
// 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
string sourceDir = RunExamples.Get_SourceDirectory();
//Output directory
string outputDir = RunExamples.Get_OutputDirectory();
// Instantiating a Workbook object
// Opening the Excel file through the file stream
Workbook workbook = new Workbook(sourceDir + "Blank.xlsx");
// Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.Worksheets[0];
// Call MatchNonBlanks function to apply the filter
worksheet.AutoFilter.MatchNonBlanks(0);
// Call refresh function to update the worksheet
worksheet.AutoFilter.Refresh();
// Saving the modified Excel file
workbook.Save(outputDir + "FilteredNonBlank.xlsx");
包含自定义筛选

Excel 提供了自定义筛选功能,例如筛选包含特定字符串的行。Aspose.Cells 中也提供了此功能,并且通过下面的示例演示了对样本文件中的名称进行筛选。示例文件如下。

  1. 源样本国家名称.xlsx
  2. outSourseSampleCountryNames.xlsx.
// 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 = new Workbook("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 containing string "Ba"
worksheet.AutoFilter.Custom(0, FilterOperatorType.Contains, "Ba");
//Refresh the filter to show/hide filtered rows
worksheet.AutoFilter.Refresh();
// Saving the modified Excel file
workbook.Save("outSourseSampleCountryNames.xlsx");
不包含特定字符串的自定义筛选

Excel提供了自定义筛选功能,例如筛选不包含特定字符串的行。这一功能在Aspose.Cells中可用,并通过以下演示在示例文件中筛选名称。示例文件可从以下链接下载。

  1. sourseSampleCountryNames.xlsx.
// 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 = new Workbook("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 containing string "Ba"
worksheet.AutoFilter.Custom(0, FilterOperatorType.NotContains, "Be");
//Refresh the filter to show/hide filtered rows
worksheet.AutoFilter.Refresh();
// Saving the modified Excel file
workbook.Save("outSourseSampleCountryNames.xlsx");
以指定字符串开头的自定义筛选

Excel提供了自定义筛选功能,例如筛选以特定字符串开头的行。这一功能在Aspose.Cells中可用,并通过以下演示在示例文件中筛选名称。

  1. sourseSampleCountryNames.xlsx.
// 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 = 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 中也提供了此功能,并且通过下面的示例演示了对下面给出的样本文件中的名称进行筛选。

  1. sourseSampleCountryNames.xlsx.
// 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 = 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 end with string "ia"
worksheet.AutoFilter.Custom(0, FilterOperatorType.BeginsWith, "ia");
//Refresh the filter to show/hide filtered rows
worksheet.AutoFilter.Refresh();
// Saving the modified Excel file
workbook.Save(outputDir + "outSourseSampleCountryNames.xlsx");

高级主题