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#]
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]
Dim workbook As Workbook = 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)