Excel AutoFiltering in C# and VB.NET

Following example shows how to filter rows in a specific cell range of an Excel worksheet with C# and VB.NET.

Filtered rows are hidden in all output file formats. Active AutoFilter is written only to XLSX file format.

Screenshot

Excel AutoFiltering Screenshot

See the full code below, use RUN EXAMPLE to execute. 

1using System;
2using System.Collections.Generic;
3using System.Text;
4using GemBox.Spreadsheet;
5using GemBox.Spreadsheet.ConditionalFormatting;
6using GemBox.Spreadsheet.PivotTables;
7
8class Sample
9{
10    [STAThread]
11    static void Main(string[] args)
12    {
13        // If using Professional version, put your serial key below.
14        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
15
16        ExcelFile ef = new ExcelFile();
17        ExcelWorksheet ws = ef.Worksheets.Add("Filtering");
18
19        int rowCount = 500;
20
21        // Specify sheet formatting.
22        ws.Rows[0].Style.Font.Weight = ExcelFont.BoldWeight;
23        ws.Columns[0].SetWidth(3, LengthUnit.Centimeter);
24        ws.Columns[1].SetWidth(3, LengthUnit.Centimeter);
25        ws.Columns[2].SetWidth(3, LengthUnit.Centimeter);
26        ws.Columns[2].Style.NumberFormat = "[$$-409]#,##0.00";
27        ws.Columns[3].SetWidth(3, LengthUnit.Centimeter);
28        ws.Columns[3].Style.NumberFormat = "yyyy-mm-dd";
29
30        var cells = ws.Cells;
31
32        // Specify header row.
33        cells[0, 0].Value = "Departments";
34        cells[0, 1].Value = "Names";
35        cells[0, 2].Value = "Salaries";
36        cells[0, 3].Value = "Deadlines";
37
38        // Insert random data to sheet.
39        var random = new Random();
40        var departments = new string[] { "Legal", "Marketing", "Finance", "Planning", "Purchasing" };
41        var names = new string[] { "John Doe", "Fred Nurk", "Hans Meier", "Ivan Horvat" };
42        for (int i = 0; i < rowCount; ++i)
43        {
44            cells[i + 1, 0].Value = departments[random.Next(departments.Length)];
45            cells[i + 1, 1].Value = names[random.Next(names.Length)] + ' ' + (i + 1).ToString();
46            cells[i + 1, 2].SetValue(random.Next(10, 101) * 100);
47            cells[i + 1, 3].SetValue(DateTime.Now.AddDays(random.Next(-1, 2)));
48        }
49
50        // Specify range which will be filtered.
51        var filterRange = ws.Cells.GetSubrangeAbsolute(0, 0, rowCount, 3);
52
53        // Show only rows which satisfy following conditions:
54        // - 'Departments' value is either "Legal" or "Marketing" or "Finance" and
55        // - 'Names' value contains letter 'e' and
56        // - 'Salaries' value is in the top 20 percent of all 'Salaries' values and
57        // - 'Deadlines' value is today's date.
58        // Shown rows are then sorted by 'Salaries' values in the descending order.
59        filterRange.Filter().
60            ByValues(0, "Legal", "Marketing", "Finance").
61            ByCustom(1, FilterOperator.Equal, "*e*").
62            ByTop10(2, true, true, 20).
63            ByDynamic(3, DynamicFilterType.Today).
64            SortBy(2, true).
65            Apply();
66
67        ef.Save("Filtering.xlsx");
68    }
69}

1Imports System
2Imports System.Collections.Generic
3Imports System.Text
4Imports GemBox.Spreadsheet
5Imports GemBox.Spreadsheet.ConditionalFormatting
6Imports GemBox.Spreadsheet.PivotTables
7
8Module Samples
9
10    Sub Main()
11
12        ' If using Professional version, put your serial key below.
13        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")
14
15        Dim ef As ExcelFile = New ExcelFile
16        Dim ws As ExcelWorksheet = ef.Worksheets.Add("Filtering")
17
18        Dim rowCount As Integer = 500
19
20        ' Specify sheet formatting.
21        ws.Rows(0).Style.Font.Weight = ExcelFont.BoldWeight
22        ws.Columns(0).SetWidth(3, LengthUnit.Centimeter)
23        ws.Columns(1).SetWidth(3, LengthUnit.Centimeter)
24        ws.Columns(2).SetWidth(3, LengthUnit.Centimeter)
25        ws.Columns(2).Style.NumberFormat = "[$$-409]#,##0.00"
26        ws.Columns(3).SetWidth(3, LengthUnit.Centimeter)
27        ws.Columns(3).Style.NumberFormat = "yyyy-mm-dd"
28
29        Dim cells = ws.Cells
30
31        ' Specify header row.
32        cells(0, 0).Value = "Departments"
33        cells(0, 1).Value = "Names"
34        cells(0, 2).Value = "Salaries"
35        cells(0, 3).Value = "Deadlines"
36
37        ' Insert random data to sheet.
38        Dim random = New Random
39        Dim departments = New String() {"Legal", "Marketing", "Finance", "Planning", "Purchasing"}
40        Dim names = New String() {"John Doe", "Fred Nurk", "Hans Meier", "Ivan Horvat"}
41        For i As Integer = 0 To rowCount - 1
42            cells(i + 1, 0).Value = departments(random.Next(departments.Length))
43            cells(i + 1, 1).Value = names(random.Next(names.Length)) + " "c + (i + 1).ToString()
44            cells(i + 1, 2).SetValue(random.Next(10, 101) * 100)
45            cells(i + 1, 3).SetValue(DateTime.Now.AddDays(random.Next(-1, 2)))
46        Next
47
48        ' Specify range which will be filtered.
49        Dim filterRange = ws.Cells.GetSubrangeAbsolute(0, 0, rowCount, 3)
50
51        ' Show only rows which satisfy following conditions:
52        ' - 'Departments' value is either "Legal" or "Marketing" or "Finance" and
53        ' - 'Names' value contains letter 'e' and
54        ' - 'Salaries' value is in the top 20 percent of all 'Salaries' values and
55        ' - 'Deadlines' value is today's date.
56        ' Shown rows are then sorted by 'Salaries' values in the descending order.
57        filterRange.Filter().
58            ByValues(0, "Legal", "Marketing", "Finance").
59            ByCustom(1, FilterOperator.Equal, "*e*").
60            ByTop10(2, True, True, 20).
61            ByDynamic(3, DynamicFilterType.Today).
62            SortBy(2, True).
63            Apply()
64
65        ef.Save("Filtering.xlsx")
66
67    End Sub
68
69End Module

Check next sample or find out more about GemBox.Spreadsheet and GemBox Software.