Excel AutoFilter in C# and VB.NET

The following example shows how to filter rows in a specific cell range of an Excel worksheet with C# and VB.NET using GemBox.Spreadsheet.

Filtered rows are hidden in all output file formats. The active Filter is written only to the XLSX file format.

Excel AutoFilter applied with GemBox.Spreadsheet
Screenshot of Excel AutoFilter applied with GemBox.Spreadsheet
using System;
using GemBox.Spreadsheet;

class Program
{
    static void Main()
    {
        // If using Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");

        var workbook = new ExcelFile();
        var worksheet = workbook.Worksheets.Add("Filtering");

        int rowCount = %RowCount%;

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

        var cells = worksheet.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 = worksheet.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();

        workbook.Save("Filtering.%OutputFileType%");
    }
}
Imports System
Imports GemBox.Spreadsheet

Module Program

    Sub Main()

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

        Dim workbook As New ExcelFile()
        Dim worksheet = workbook.Worksheets.Add("Filtering")

        Dim rowCount As Integer = %RowCount%

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

        Dim cells = worksheet.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 As 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 = worksheet.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()

        workbook.Save("Filtering.%OutputFileType%")
    End Sub
End Module

See also


Next steps

GemBox.Spreadsheet is a .NET component that enables you to read, write, edit, convert, and print spreadsheet files from your .NET applications using one simple API.

Download Buy