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.

See the full code below, use Run Example to execute.

C#
VB.NET

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 = ExcelFile.Load("SimpleTemplate.xlsx");

        string searchText = "Apollo 13";

        var ws = ef.Worksheets[0];

        StringBuilder sb = new StringBuilder();

        int objectRow, objectColumn;
        ws.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 " + ws.Cells[objectRow, 2].Value + ".");

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

                int nationalityCounter = 0;

                CellRangeEnumerator enumerator = ws.Columns[1].Cells.GetReadEnumerator();
                while (enumerator.MoveNext())
                {
                    ExcelCell 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.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 = ExcelFile.Load("SimpleTemplate.xlsx")

        Dim searchText = "Apollo 13"

        Dim ws = ef.Worksheets(0)

        Dim sb = New StringBuilder()

        Dim objectRow, objectColumn As Integer
        ws.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 " & ws.Cells(objectRow, 2).Value & ".")

            Dim nationality = CType(ws.Cells(objectRow, 1).Value, String)

            If Not String.IsNullOrEmpty(nationality) Then

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

                Dim nationalityCounter = 0

                Dim enumerator = ws.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 sample.