Find and Seach using Aspose.Excel

Aspose.Cells Product Family
1

Hi,

Please find the following code in VB.NET using MS Excel and suggest me an equalant Aspose.excel code for search?

Private Sub DemoFind()
Dim currentFind As Excel.Range

’ Keep track of the first range you find.
Dim firstFind As Excel.Range = Nothing

’ You should specify all these parameters
’ every time you call this method, since they
’ can be overridden in the user interface.
currentFind = Me.Fruits.Find(“apples”, , _
Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart, _
Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, _
False)
While Not currentFind Is Nothing
If firstFind Is Nothing Then
firstFind = currentFind
ElseIf currentFind.Address = firstFind.Address Then
Exit While
End If
With currentFind.Font
.Color = System.Drawing.ColorTranslator.ToOle( _
System.Drawing.Color.Red)
.Bold = True
End With
currentFind =Me.Fruits.FindNext(currentFind)
End While
End Sub

2

Please try the following sample code with the attached fix.

’ Search string in range from B2 to F11

Dim cells as Cells = ExcelObj.Worksheets(0).Cells

Dim area as CellArea
area.StartRow = 1
area.EndRow = 10
area.StartColumn = 1
area.EndColumn = 5

Dim previousCell as Cell = null
Dim currentCell as Cell = cells.FindString(“apples”, previousCell, area)

While Not currentCell Is Nothing
previousCell = currentCell
With currentCell.Style.Font
.Color = Color.Red
.IsBold = True
End With
currentCell = cells.FindString(“apples”, previousCell, area)
End While

3

Hi,

Thanks for that…But in search i am really looking for a format to search inside a loop and validate for it.

For Ex, i am looking for cell values with “-” inside. (HEP-001,GEIO-98).
In Excel they have (-) for that.Here how can i search that?

-Balaji RJ

4

@rjbalaji79,
Aspose.Excel is discontinued and no more under development. It is replaced by Aspose.Cells that is much advanced and feature-rich product. It provides advanced features to find and search text in an Excel file. Here is an example that can be referred to find data having ‘-’.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET

// Instantiate the workbook object
Workbook workbook = new Workbook();
workbook.Worksheets[0].Cells["A1"].Value = "A1 Data";
workbook.Worksheets[0].Cells["A2"].Value = "-A2 Data";
workbook.Worksheets[0].Cells["A3"].Value = "HEP-001";
workbook.Worksheets[0].Cells["A4"].Value = "A4 Data-";
workbook.Worksheets[0].Cells["A5"].Value = "GEIO-98";
workbook.Worksheets[0].Cells["A6"].Value = "A6 Data";

// Get Cells collection
Cells cells = workbook.Worksheets[0].Cells;

FindOptions opts = new FindOptions();
opts.LookInType = LookInType.Values;

// Find the cell containing with the input string
opts.LookAtType = LookAtType.Contains;
Cell found = null;
do
{
found = workbook.Worksheets[0].Cells.Find("-", found, opts);
if (found != null)
Console.WriteLine(found.Value + " at " + found.Name);
} while (found != null);

Here is the program output:

-A2 Data at A2
A3-Data at A3
A4 Data- at A4
-------- at A5

For more details about this feature, refer to the following article:
Find or Search Data

For testing this code, get a free trial version here:
Aspose.Cells for .NET (Latest Version)

A comprehensive detailed solution is available here which contains hundreds of ready-to-run examples for testing different features of this product.