Search in Excel
GemBox.Spreadsheet is a .NET component that enables developers to read, write, convert and print spreadsheet files (XLSX, XLS, ODS, CSV, HTML, PDF or XPS) from their .NET applications using one simple API. GemBox.Spreadsheet Free is free of charge while GemBox.Spreadsheet Professional is a commercial version licensed per developer.
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
See the full code below, use RUN EXAMPLE to execute.
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.