Data Filtering

Autofilter Data

Autofiltering is the quickest way to select only those items from the worksheet that you want to display in a list. The autofilter feature allows users to filter items in a list according to a set criteria. Filter based on text, numbers or dates.

Autofilter in Microsoft Excel

To activate the autofilter feature in Microsoft Excel:

  1. Click the heading row in a worksheet.
  2. From the Data menu, select Filter and then AutoFilter.

When you apply an autofilter to a worksheet, filter switches (black arrows) appear to the right of the column headings.

  1. Click a filter arrow to see a list of filter options.

Some of the autofilter options are:

Options Description
All Show all items in the list once.
Custom Customize filter criteria like contains/not contains
Filter by Color Filters based on filled color
Date Filters Filters rows based on different criteria on date
Number Filters Different types of filter on numbers like comparison, averages and Top 10 etc.
Text Filters Different filters like begins with, ends with, contains etc,
Blanks/Non Blanks These filters can be implemented through Text Filter Blank

Users manually filter their worksheet data in Microsoft Excel using these options.

Autofilter with Aspose.Cells

Aspose.Cells provides a class, Workbook that represents an Excel file. The Workbook class contains a Worksheets collection that allows access to each worksheet in the Excel file.

A worksheet is represented by the Worksheet class. The Worksheet class provides a wide range of properties and methods to manage worksheets. To create an autofilter, use the AutoFilter property of the Worksheet class. The AutoFilter property is an object of the AutoFilter class, which provides the Range property for specifying the range of cells that make up a heading row. An autofilter is applied to the range of cells that is the heading row.

In each worksheet, you can only specify one filter range. This is limited by Microsoft Excel. For custom data filtering, use the AutoFilter.Custom method.

In the example given below, we have created the same AutoFilter using Aspose.Cells as we created using Microsoft Excel in the above section.

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

Different types of Filter

Aspose.Cells provides multiple options to apply different type of filters like Color Filter, Date Filter, Number Filter, Text Filter, Blank Filters and None Blank Filters.

Fill Color

Aspose.Cells provides a function AddFillColorFilter to filter data based upon the fill color property of the cells. In the example given below, a template file having different fill colors in the first column of the sheet is used to test the color filtering function. Sample files can be downloaded from the following links.

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

Different type of date filters can be implemented like filtering all the rows having dates in January 2018. Following sample code demonstrates this filter using AddDateFilter function. Sample files are given below.

  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");
Dynamic Date

Sometimes dynamic filters are required based on date like all the cells having dates in January irrespective of the year. In this case DynamicFilter function is used as given in the following sample code. Sample files are given below.

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

Custom filters can be applied using Aspose.Cells like selecting cells having number between a given range. Following example demonstrates the usage of Custom() function to filter numbers. Sample files are given below.

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

If a column contains text and cells are to be selected containing particular text, Filter() function can be used. In the following example, template file contains list of countries and row is to be selected containing particular country name. Following code demonstrates filtering text. Sample files are given below.

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

If a column contains text such that few cells are blank, and filter is required to select those rows only where blank cells are present, MatchBlanks() function can be used as demonstrated below. Sample files are given below.

  1. Blank.xlsx
  2. FilteredBlank.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");
Non Blanks

When cells having any text are to be filtered, use MatchNonBlanks filter function as demonstrated below. Sample files are given below.

  1. Blank.xlsx
  2. FilteredNonBlank.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");
Custom filter with Contains

Excel provides custom filters like filter rows which contain some specific string. This feature is available in Aspose.Cells and demonstrated below by filtering the names in the sample file. Sample files are given below.

  1. sourseSampleCountryNames.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");
Custom filter with NotContains

Excel provides custom filters like filter rows which does not contain some specific string. This feature is available in Aspose.Cells and demonstrated below by filtering the names in the sample file given below.

  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");
Custom filter with BeginsWith

Excel provides custom filters like filter rows which begins with some specific string. This feature is available in Aspose.Cells and demonstrated below by filtering the names in the sample file given below.

  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");
Custom filter with EndsWith

Excel provides custom filters like filter rows which ends with some specific string. This feature is available in Aspose.Cells and demonstrated below by filtering the names in the sample file given below.

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

Advance topics

  • Apply Advanced Filter of Microsoft Excel to Display Records Meeting Complex Criteria
  • Get All Hidden Rows Indices after Refreshing AutoFilter