Search in Excel

Following example shows how to search text in a spreadsheet file. It also shows how to use CellRange.GetReadEnumerator for iterating over range of cells.

Screenshot of Excel search with GemBox.Spreadsheet

C#
VB.NET

View on GitHub

using System;
using System.Text;
using GemBox.Spreadsheet;

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

        var workbook = ExcelFile.Load("%InputFileName%");

        var searchText = "Apollo 13";

        var worksheet = workbook.Worksheets[0];

        var sb = new StringBuilder();

        int objectRow, objectColumn;
        worksheet.Cells.FindText(searchText, false, false, out objectRow, out objectColumn);

        if (objectRow == -1 || objectColumn == -1)
            sb.AppendLine("Can't find text.");
        else
        {
            sb.AppendLine(searchText + " was launched on " + worksheet.Cells[objectRow, 2].Value.ToString() + ".");

            var nationality = worksheet.Cells[objectRow, 1].Value as string;
            if (nationality != null)
            {
                var nationalityText = nationality.Trim().ToLowerInvariant();

                int nationalityCounter = 0;

                var enumerator = worksheet.Columns[1].Cells.GetReadEnumerator();
                while (enumerator.MoveNext())
                {
                    var cell = enumerator.Current;
                    var cellValue = cell.Value as string;
                    if (cellValue != null && cellValue.Trim().ToLowerInvariant() == nationalityText)
                        nationalityCounter++;
                }

                sb.AppendFormat("There are {0} entires for {1}.", nationalityCounter, nationality);
            }
        }

        Console.WriteLine(sb.ToString());
    }
}

Imports System
Imports System.Text
Imports GemBox.Spreadsheet

Module Program

    Sub Main()

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

        Dim workbook = ExcelFile.Load("%InputFileName%")

        Dim searchText = "Apollo 13"

        Dim worksheet = workbook.Worksheets(0)

        Dim sb = New StringBuilder()

        Dim objectRow, objectColumn As Integer
        worksheet.Cells.FindText(searchText, False, False, objectRow, objectColumn)

        If objectRow = -1 Or objectColumn = -1 Then
            sb.AppendLine("Can't find text.")
        Else

            sb.AppendLine(searchText & " was launched on " & worksheet.Cells(objectRow, 2).Value.ToString() & ".")

            Dim nationality = CType(worksheet.Cells(objectRow, 1).Value, String)
            If Not String.IsNullOrEmpty(nationality) Then

                Dim nationalityText = nationality.Trim().ToLowerInvariant()

                Dim nationalityCounter = 0

                Dim enumerator = worksheet.Columns(1).Cells.GetReadEnumerator()
                While enumerator.MoveNext()

                    Dim cell = enumerator.Current
                    Dim cellValue = CType(cell.Value, String)
                    If Not String.IsNullOrEmpty(cellValue) Then
                        If cellValue.Trim().ToLowerInvariant() = nationalityText Then nationalityCounter = nationalityCounter + 1
                    End If
                End While

                sb.AppendFormat("There are {0} entires for {1}.", nationalityCounter, nationality)
            End If
        End If

        Console.WriteLine(sb.ToString())
    End Sub
End Module

Want more?

Next example GitHub

Check the next example or select an example from the menu. You can also download our examples from the GitHub.

Like it?

Download Buy

If you want to try the GemBox.Spreadsheet yourself, you can download the free version. It delivers the same performance and set of features as the professional version, but with some operations limited. To remove the limitation, you need to purchase a license.

Published: December 13, 2018 | Modified: September 8, 2021 | Author: Mario Zorica