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. 

using System;
using System.Collections.Generic;
using System.Text;
using GemBox.Spreadsheet;
using GemBox.Spreadsheet.ConditionalFormatting;
using GemBox.Spreadsheet.PivotTables;
using GemBox.Spreadsheet.Tables;

class Sample
{
    [STAThread]
    static void Main(string[] args)
    {
        // If using Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");

        ExcelFile ef = new ExcelFile();
        ExcelWorksheet ws = ef.Worksheets.Add("Filtering");

        int rowCount = 500;

        // Specify sheet formatting.
        ws.Rows[0].Style.Font.Weight = ExcelFont.BoldWeight;
        ws.Columns[0].SetWidth(3, LengthUnit.Centimeter);
        ws.Columns[1].SetWidth(3, LengthUnit.Centimeter);
        ws.Columns[2].SetWidth(3, LengthUnit.Centimeter);
        ws.Columns[2].Style.NumberFormat = "[$$-409]#,##0.00";
        ws.Columns[3].SetWidth(3, LengthUnit.Centimeter);
        ws.Columns[3].Style.NumberFormat = "yyyy-mm-dd";

        var cells = ws.Cells;

        // Specify header row.
        cells[0, 0].Value = "Departments";
        cells[0, 1].Value = "Names";
        cells[0, 2].Value = "Salaries";
        cells[0, 3].Value = "Deadlines";

        // Insert random data to sheet.
        var random = new Random();
        var departments = new string[] { "Legal", "Marketing", "Finance", "Planning", "Purchasing" };
        var names = new string[] { "John Doe", "Fred Nurk", "Hans Meier", "Ivan Horvat" };
        for (int i = 0; i < rowCount; ++i)
        {
            cells[i + 1, 0].Value = departments[random.Next(departments.Length)];
            cells[i + 1, 1].Value = names[random.Next(names.Length)] + ' ' + (i + 1).ToString();
            cells[i + 1, 2].SetValue(random.Next(10, 101) * 100);
            cells[i + 1, 3].SetValue(DateTime.Now.AddDays(random.Next(-1, 2)));
        }

        // Specify range which will be filtered.
        var filterRange = ws.Cells.GetSubrangeAbsolute(0, 0, rowCount, 3);

        // Show only rows which satisfy following conditions:
        // - 'Departments' value is either "Legal" or "Marketing" or "Finance" and
        // - 'Names' value contains letter 'e' and
        // - 'Salaries' value is in the top 20 percent of all 'Salaries' values and
        // - 'Deadlines' value is today's date.
        // Shown rows are then sorted by 'Salaries' values in the descending order.
        filterRange.Filter().
            ByValues(0, "Legal", "Marketing", "Finance").
            ByCustom(1, FilterOperator.Equal, "*e*").
            ByTop10(2, true, true, 20).
            ByDynamic(3, DynamicFilterType.Today).
            SortBy(2, true).
            Apply();

        ef.Save("Filtering.xlsx");
    }
}
Imports System
Imports System.Collections.Generic
Imports System.Text
Imports GemBox.Spreadsheet
Imports GemBox.Spreadsheet.ConditionalFormatting
Imports GemBox.Spreadsheet.PivotTables
Imports GemBox.Spreadsheet.Tables

Module Samples

    Sub Main()

        ' If using Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")

        Dim ef As ExcelFile = New ExcelFile
        Dim ws As ExcelWorksheet = ef.Worksheets.Add("Filtering")

        Dim rowCount As Integer = 500

        ' Specify sheet formatting.
        ws.Rows(0).Style.Font.Weight = ExcelFont.BoldWeight
        ws.Columns(0).SetWidth(3, LengthUnit.Centimeter)
        ws.Columns(1).SetWidth(3, LengthUnit.Centimeter)
        ws.Columns(2).SetWidth(3, LengthUnit.Centimeter)
        ws.Columns(2).Style.NumberFormat = "[$$-409]#,##0.00"
        ws.Columns(3).SetWidth(3, LengthUnit.Centimeter)
        ws.Columns(3).Style.NumberFormat = "yyyy-mm-dd"

        Dim cells = ws.Cells

        ' Specify header row.
        cells(0, 0).Value = "Departments"
        cells(0, 1).Value = "Names"
        cells(0, 2).Value = "Salaries"
        cells(0, 3).Value = "Deadlines"

        ' Insert random data to sheet.
        Dim random = New Random
        Dim departments = New String() {"Legal", "Marketing", "Finance", "Planning", "Purchasing"}
        Dim names = New String() {"John Doe", "Fred Nurk", "Hans Meier", "Ivan Horvat"}
        For i As Integer = 0 To rowCount - 1
            cells(i + 1, 0).Value = departments(random.Next(departments.Length))
            cells(i + 1, 1).Value = names(random.Next(names.Length)) + " "c + (i + 1).ToString()
            cells(i + 1, 2).SetValue(random.Next(10, 101) * 100)
            cells(i + 1, 3).SetValue(DateTime.Now.AddDays(random.Next(-1, 2)))
        Next

        ' Specify range which will be filtered.
        Dim filterRange = ws.Cells.GetSubrangeAbsolute(0, 0, rowCount, 3)

        ' Show only rows which satisfy following conditions:
        ' - 'Departments' value is either "Legal" or "Marketing" or "Finance" and
        ' - 'Names' value contains letter 'e' and
        ' - 'Salaries' value is in the top 20 percent of all 'Salaries' values and
        ' - 'Deadlines' value is today's date.
        ' Shown rows are then sorted by 'Salaries' values in the descending order.
        filterRange.Filter().
            ByValues(0, "Legal", "Marketing", "Finance").
            ByCustom(1, FilterOperator.Equal, "*e*").
            ByTop10(2, True, True, 20).
            ByDynamic(3, DynamicFilterType.Today).
            SortBy(2, True).
            Apply()

        ef.Save("Filtering.xlsx")

    End Sub

End Module