Find a text in a range using EPPlus

Question

I'm trying to find a text in an excel sheet (ws), but I cannot be sure where exactly will be that text, I only know it will be in a given range (A1:B64).

When I use this code (for test purpouse), it works fine and I can find the value:

var Row as object = From cell In ws.SelectedRange(2,2)
                    Where cell.Value.ToString().Equals("any text")
                    Select cell.Start.Row

But when I changed the code to this:

var Row as object = From cell In ws.SelectedRange("A1:B64")
                    Where cell.Value.ToString().Equals("any text")
                    Select cell.Start.Row

I get an error that said:

 System.NullReferenceException: 'Object reference not set to an instance of 
 an object.'
 OfficeOpenXml.ExcelRangeBase.Value.get returned Nothing.

I also tried change the values for the selected ranges to:

 ws.SelectedRange(ws.Dimension.Address)     
 ws.SelectedRange(ws.Dimension.Start.Row, ws.Dimension.End.Column)
 ws.SelectedRange(1,1,64, 2)
 ws.Cells("A1:B64")

All of them throw an error.

Any help?

Thanks in advance.

Answer 1

Couldn't it be that you have cells within the range that has no value set? In that case EPPlus will return null and your ToString() call will throw an exception.

Haven't tried this code myself, but maybe you can use the "Safe Navigation Operator" in C#? Like this:

var Row as object = From cell In ws.SelectedRange("A1:B64")
                Where cell.Value?.ToString().Equals("any text")
                Select cell.Start.Row