Search in Excel

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

Screenshot

Searching Screenshot

See the full code below, use RUN EXAMPLE to execute.

Upload your file (Drag files here)

1using System;
2using System.Collections.Generic;
3using System.Text;
4using GemBox.Spreadsheet;
5using GemBox.Spreadsheet.ConditionalFormatting;
6using GemBox.Spreadsheet.PivotTables;
7
8class Sample
9{
10    [STAThread]
11    static void Main(string[] args)
12    {
13        // If using Professional version, put your serial key below.
14        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
15
16        ExcelFile ef = ExcelFile.Load("SimpleTemplate.xlsx");
17
18        string searchText = "Apollo 13";
19
20        var ws = ef.Worksheets[0];
21
22        StringBuilder sb = new StringBuilder();
23
24        int objectRow, objectColumn;
25        ws.Cells.FindText(searchText, false, false, out objectRow, out objectColumn);
26
27        if (objectRow == -1 || objectColumn == -1)
28            sb.AppendLine("Can't find text.");
29        else
30        {
31            sb.AppendLine(searchText + " was launched on " + ws.Cells[objectRow, 2].Value + ".");
32
33            string nationality = ws.Cells[objectRow, 1].Value as string;
34            if (nationality != null)
35            {
36                string nationalityText = nationality.Trim().ToLowerInvariant();
37
38                int nationalityCounter = 0;
39
40                CellRangeEnumerator enumerator = ws.Columns[1].Cells.GetReadEnumerator();
41                while (enumerator.MoveNext())
42                {
43                    ExcelCell cell = enumerator.Current;
44                    var cellValue = cell.Value as string;
45                    if (cellValue != null && cellValue.Trim().ToLowerInvariant() == nationalityText)
46                        nationalityCounter++;
47                }
48
49                sb.AppendFormat("There are {0} entires for {1}.", nationalityCounter, nationality);
50            }
51        }
52
53        Console.WriteLine(sb.ToString());
54    }
55}
1Imports System
2Imports System.Collections.Generic
3Imports System.Text
4Imports GemBox.Spreadsheet
5Imports GemBox.Spreadsheet.ConditionalFormatting
6Imports GemBox.Spreadsheet.PivotTables
7
8Module Samples
9
10    Sub Main()
11
12        ' If using Professional version, put your serial key below.
13        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")
14
15        Dim ef As ExcelFile = ExcelFile.Load("SimpleTemplate.xlsx")
16
17        Dim searchText = "Apollo 13"
18
19        Dim ws = ef.Worksheets(0)
20
21        Dim sb = New StringBuilder()
22
23        Dim objectRow, objectColumn As Integer
24        ws.Cells.FindText(searchText, False, False, objectRow, objectColumn)
25
26        If objectRow = -1 Or objectColumn = -1 Then
27            sb.AppendLine("Can't find text.")
28        Else
29
30            sb.AppendLine(searchText & " was launched on " & ws.Cells(objectRow, 2).Value & ".")
31
32            Dim nationality = CType(ws.Cells(objectRow, 1).Value, String)
33
34            If Not String.IsNullOrEmpty(nationality) Then
35
36                Dim nationalityText = nationality.Trim().ToLowerInvariant()
37
38                Dim nationalityCounter = 0
39
40                Dim enumerator = ws.Columns(1).Cells.GetReadEnumerator()
41                While enumerator.MoveNext()
42
43                    Dim cell = enumerator.Current
44                    Dim cellValue = CType(cell.Value, String)
45                    If Not String.IsNullOrEmpty(cellValue) Then
46                        If cellValue.Trim().ToLowerInvariant() = nationalityText Then nationalityCounter = nationalityCounter + 1
47                    End If
48
49                End While
50
51                sb.AppendFormat("There are {0} entires for {1}.", nationalityCounter, nationality)
52            End If
53
54        End If
55
56        Console.WriteLine(sb.ToString())
57
58    End Sub
59
60End Module

Check next sample or find out more about GemBox.Spreadsheet and GemBox Software.