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

Upload your file (Drag file here)

Input file name

C#
VB.NET

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

Check next example or download examples from GitHub.