Use AutoFilter to Filter Excel Data in C#.NET

Filtering data based on criteria is a very important feature. It helps the user to understand and analyze data easily. You can use the auto filter feature of Microsoft Excel to find, show or hide values in a single or multiple columns based on the choices that you select from list. When the filter is applied, then all the rows that do not meet your criteria are hidden completely.

You can use Aspose.Cells for .NET to apply filter on your Excel data programmatically in C# easily with few lines of code. It can also be used to perform wide range of functions on Excel documents e.g. you can create, edit and manipulate Excel spreadsheets in any platform without any need to install Microsoft Excel or without using any sort of Microsoft Office automation.

Article Description

The purpose of this article is to explain how developers can use AutoFilter to filter Excel data in C# or in any .NET Framework supported language e.g. VB.NET etc.

Supported Platforms

Aspose.Cells API supports all .NET frameworks e.g. .NET 2.0, .NET 3.5, .NET 4.0, .NET 7.0, .NET Core, .NET Standard 2.0, Xamarin etc. It is also available in other platforms e.g. Java, C++, Android, JavaScript, PHP etc. Besides, Aspose.Cells is available in Cloud as REST or RESTful APIs.

Filter Data using AutoFilter in Microsoft Excel

Please do the following steps to filter data using AutoFilter in Microsoft Excel.

• Select the columns and click Data > Filter button inside the Sort & Filter section.
• Click the AutoFilter dropdown, select your choices from list and press OK.
• All the rows that do not match your criteria will be filtered out. Please see this snapshot for detail.

Filter Data using AutoFilter in Aspose.Cells

This section explains how to filter Excel data using AutoFilter with Aspose.Cells API.

Sample Input Microsoft Excel Document

For demonstration, we will use the following sample input Microsoft Excel document that contains some data in four columns. We will apply AutoFilter on Vehicle and Color columns. Once, rows are filtered out, some of them will become hidden and the Grand Total for Qty1 and Qty2 columns shown inside the red lines will be modified accordingly.

Sample Code

The following sample code applies AutoFilter on Microsoft Excel data by performing these steps

• Load sample input Microsoft Excel document containing the sample data for auto filter.
• Apply auto filter to range.
• Adds two filters to first column.
• Refresh the auto filter.
• Adds another two filters to second column.
• Refresh the auto filter.
• Save the workbook in XLSX format. You can also save it in other formats e.g. XLS, XLSB, XLSM etc.

Output Microsoft Excel Document by Aspose.Cells after applying AutoFilter

The following snapshot shows the Output Microsoft Excel Document generated by Aspose.Cells after applying AutoFilter with the code given above. As you can see, it now shows the filtered rows and new values of Grand Total displayed inside the red lines.