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:
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