Here in this article, we are going to provide you an overview of applying and working with filters on Excel range.
C# Syntax of Applying Filters in Excel
The below code line applies filter on specified excel range.
excelSheet.Range[excelRange].AutoFilter(object Field, object Criteria1);The first parameter of AutoFilter function is Field that is the integer offset of the field on which you want to apply the filter.
The second parameter of AutoFilter function is Criteria1. Here we specify the criteria value to filter.
Excel Auto Filter by C# Examples
Let’s illustrate how to use auto filter in excel with the help of C# programming language by the below given sample data.
Below given code line applies filter on row 1 in above given sample data and filters “Pen” in field 2.
excelSheet.Range["1:1"].AutoFilter(2,"Pen");
Applying Multiple Filters on Excel Range
We use xlOr property of XlAutoFilterOperator for filtering more than one value in a column. Below given code line applies filters on field in row 1 and filters “Pen” or “Pencil” value records.
excelSheet.Range["1:1"].AutoFilter(2, "Pen", Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlOr, "Pencil");
Not Equals Filter in Excel
If you need to see the record where Item is not equal to “Pencil”, the below given code line will help you to achieve that.
excelSheet.Range["1:1"].AutoFilter(2, "<>Pencil");
Filtering Less than or Greater than value in Excel Filter
If you need to filter the records where quantity is less than 60, the below given code line will help you to achieve that.
excelSheet.Range["1:1"].AutoFilter(3, "<60");
If you need to see greater than 60,
excelSheet.Range["1:1"].AutoFilter(3, ">60");
If you need to see greater than 60 and less than 70,
excelSheet.Range["1:1"].AutoFilter(3, ">60", Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlAnd, "<70");
Removing Auto Filter from Excel Range
excelSheet.AutoFilterMode = false;