FindOptions() only finds first instance of value

Aspose.Cells Product Family
1

I am using aspose cells to look for values in an excel file.


My problem is that even though a values occurs several times in a file I am only able to find first instance.

My code is below and I would expect it to loop through sheets in excel file and search all content i a sheet but that does not seem to be the case. can you help please?

br
Hans Jørgen

FileStream fstream = new FileStream(path, FileMode.Open);

//Instantiating a Workbook object
//Opening the Excel file through the file stream
Workbook workbook = new Workbook(fstream);
Worksheet worksheet;
for (int i = 0; i < workbook.Worksheets.Count; i++)
{
worksheet = workbook.Worksheets[i];
FindOptions opts = new FindOptions();
opts.LookAtType = LookAtType.StartWith;
CellArea ca = new CellArea();
ca.StartRow = 0;
ca.StartColumn = 0;
ca.EndRow = worksheet.Cells.MaxDataRow;
ca.EndColumn = worksheet.Cells.MaxDataColumn;
opts.SetRange(ca);
Aspose.Cells.Cell cell1 = worksheet.Cells.Find("DQRC Cycle ", null, opts);
if (cell1 != null)
{

//we found our value and does whatever is needed
}
}
2

Hi,


Thanks for your posting and using Aspose.Cells.

You will have to find all cells one by one in a loop until the find method does not return null. Here is a correct code. I have attached the sample excel file used in this code and also shown its console output for your reference.

C#
Workbook workbook = new Workbook(“sample.xlsx”);

Worksheet worksheet = workbook.Worksheets[0];
FindOptions opts = new FindOptions();
opts.LookAtType = LookAtType.StartWith;
// opts.LookInType = LookInType.Values;//If you don’t want to look in formulas and only look in values

CellArea ca = new CellArea();
ca.StartRow = 0;
ca.StartColumn = 0;
ca.EndRow = worksheet.Cells.MaxDataRow;
ca.EndColumn = worksheet.Cells.MaxDataColumn;
opts.SetRange(ca);

Cell cell = null;

while((cell = worksheet.Cells.Find(“abc”, cell, opts))!=null)
{
Debug.WriteLine(cell.Name);
}


Console Output
A1
F4
I10
B13
3

That did the trick - thank you :slight_smile:


4

Hi,


Good to know that the suggested code segment works for your needs. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.

Thank you.