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 of criteria. Filters 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.
Custom Customize filter criteria like contains / not contains
Filter by Color Filters based on filled color
Date Filters Filters rows based on different date criteria
Number Filters Different types of filters on numbers, such as comparisons, 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 the Text Filter Blank option.

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 WorksheetCollection 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.

Different Types of Filter

Aspose.Cells provides multiple options to apply different types of filters like Color Filter, Date Filter, Number Filter, Text Filter, Blank Filters and Non‑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. The following files can be downloaded to check the functionality.

  1. ColouredCells.xlsx
  2. FilteredColouredCells.xlsx
Date

Different types of date filters can be implemented, such as filtering all rows that have dates in January 2018. The following sample code demonstrates this filter using the addDateFilter function. The following files can be used for testing this functionality.

  1. Date.xlsx
  2. FilteredDate.xlsx
Dynamic Date

Sometimes dynamic filters are required based on a date, such as all cells having dates in January irrespective of the year. In this case, the DynamicFilter function is used, as shown in the following sample code. The following files can be used for testing.

  1. Date.xlsx
  2. FilteredDynamicDate.xlsx
Number

Custom filters can be applied using Aspose.Cells, such as selecting cells that contain numbers between a given range. The following example demonstrates the usage of the custom() function to filter numbers. Sample files can be downloaded from the following links.

  1. Number.xlsx
  2. FilteredNumber.xlsx
Text

If a column contains text and you need to select cells that contain particular text, the filter() function can be used. In the following example, the template file contains a list of countries and rows are selected that contain a particular country name. The code demonstrates filtering text using the sample files below.

  1. Text.xlsx
  2. FilteredText.xlsx
Blanks

If a column contains text such that a few cells are blank, and you need to filter only those rows where blank cells are present, the matchBlanks() function can be used as demonstrated below. Sample files can be downloaded from the following links.

  1. Blank.xlsx
  2. FilteredBlank.xlsx
Non‑Blanks

When cells containing any text are to be filtered, use the matchNonBlanks() function as demonstrated below. Sample files can be downloaded from the following links.

  1. Blank.xlsx
  2. FilteredNonBlank.xlsx
Custom Filter with Contains

Excel provides custom filters that filter rows which contain a specific string. This feature is available in Aspose.Cells and is demonstrated below by filtering the names in the sample file. Sample files can be downloaded from the following links.

  1. sourceSampleCountryNames.xlsx
  2. outSourceSampleCountryNames.xlsx
Custom Filter with NotContains

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

  1. sourceSampleCountryNames.xlsx
Custom Filter with BeginsWith

Excel provides custom filters that filter rows which begin with a specific string. This feature is available in Aspose.Cells and is demonstrated below by filtering the names in the sample file.

  1. sourceSampleCountryNames.xlsx
Custom Filter with EndsWith

Excel provides custom filters that filter rows which end with a specific string. This feature is available in Aspose.Cells and is demonstrated below by filtering the names in the sample file.

  1. sourceSampleCountryNames.xlsx

Advanced Topics

Creating Subtotals Data Validation