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
Excel search with GemBox.Spreadsheet
Upload your file (Drag file here)
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.