How to Add Autofilter to Excel in C#, VB.NET

When working with large data sets in Excel, it can be a challenge not only to calculate data, but also to find the relevant information. Luckily, we can add AutoFilter in a range of cells or table to display the data you want and hid the rest. This article will show you how to filter data in Excel using Spire.XLS with C# and VB.NET.

Code Snippets

Step 1: Create a Workbook object and load an existing Excel file.

Workbook workbook = new Workbook();
workbook.LoadFromFile(@"C:\Users\Administrator\Desktop\sample.xlsx");

Step 2: Get the first worksheet.

Worksheet sheet = workbook.Worksheets[0];

Step 3: Get the AutoFilters object.

AutoFiltersCollection filters = sheet.AutoFilters;

Step 4: Set the range to be filtered.

filters.Range = sheet.Range[1, 2, sheet.LastRow, 2];

Step 5: Add a filter criteria.

filters.AddFilter(0, "Laptop");

Step 6: Filter the data.

filters.Filter();

Step 7: Save to file.

workbook.SaveToFile("output.xlsx", ExcelVersion.Version2010);

Output:

How to Add Autofilter to Excel in C#, VB.NET

Full Code:

[C#]
using Spire.Xls;
using Spire.Xls.Collections;
namespace AddAutofilter
{
    class Program
    {
        static void Main(string[] args)
        {
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(@"C:\Users\Administrator\Desktop\sample.xlsx");
            Worksheet sheet = workbook.Worksheets[0];
            AutoFiltersCollection filters = sheet.AutoFilters;
            filters.Range = sheet.Range[1, 2, sheet.LastRow, 2];
            filters.AddFilter(0, "Laptop");
            filters.Filter();
            workbook.SaveToFile("output.xlsx", ExcelVersion.Version2010);
        }
    }
}
[VB.NET]
Imports Spire.Xls
Imports Spire.Xls.Collections
Namespace AddAutofilter
	Class Program
		Private Shared Sub Main(args As String())
			Dim workbook As New Workbook()
			workbook.LoadFromFile("C:\Users\Administrator\Desktop\sample.xlsx")
			Dim sheet As Worksheet = workbook.Worksheets(0)
			Dim filters As AutoFiltersCollection = sheet.AutoFilters
			filters.Range = sheet.Range(1, 2, sheet.LastRow, 2)
			filters.AddFilter(0, "Laptop")
			filters.Filter()
			workbook.SaveToFile("output.xlsx", ExcelVersion.Version2010)
		End Sub
	End Class
End Namespace