5

I want to be able to find if a particular string exists in a range using ClosedXML, however, I cannot find any find command in the documentation. Currently I loop through 1000s of rows to find if the string exists. Is there a more efficient way to do this?

Here is an example of my code:

for (int j = 3; j <= PipeSheet.LastRowUsed().RowNumber(); j ++)
{
     if ((PipeSheet.Cell(j, ProdCodeColumnInPipe).Value.ToString().Trim() == SheetToEdit.Cell(i, ProdCodeColumnInMain).Value.ToString().Trim() & PipeSheet.Cell(j, 3).Value.ToString().Trim() == SheetToEdit.Cell(i, RegionCodeInMain).Value.ToString().Trim()))
     {
           SheetToEdit.Cell(i, ColumnToEdit).Value = "+";

           if ((new[] { "Open", "Under Review" }).Contains(PipeSheet.Cell(j, 5).Value.ToString().Trim()) & (new[] { "Forecast"}).Contains(PipeSheet.Cell(j, 4).Value.ToString().Trim()))
           {
                  if (FirstColumnHighlight > 1 & LastColumnHighlight > 1)
                  {
                        for (int k = FirstColumnHighlight; k <= LastColumnHighlight; k++)
                        {
                              SheetToEdit.Cell(i, k).Style.Fill.BackgroundColor = XLColor.FromArgb(255, 255, 0);
                        }
                  }
           }
    }
}
djblois
  • 963
  • 1
  • 17
  • 52

1 Answers1

14

Firstly, your goal is best solved using conditional formatting.

But to answer your question, you can search for a string:

sheet.CellsUsed(cell => cell.GetString() == searchstring)

Reference: https://github.com/ClosedXML/ClosedXML/wiki/Better-lambdas

-- UPDATE --

There is a pull request at https://github.com/ClosedXML/ClosedXML/pull/399 to help with this, for example:

 foundCells = ws.Search("searchText", CompareOptions.OrdinalIgnoreCase);
Francois Botha
  • 4,520
  • 1
  • 34
  • 46
  • TY Francois, I might be able to use both ideas to speed up my procedure. However, just curious that code what does it return? It doesn't seem to return a cell. So how would I get the row number from that? – djblois May 10 '17 at 14:20
  • @djblois It returns `IEnumerable`, i.e. all the cells that match your search predicate. – Francois Botha May 10 '17 at 14:41
  • how can I then loop through them? so then I can do another test? Ty for all your help. – djblois May 10 '17 at 14:43
  • 1
    @djblois You're best off asking general programming questions in another post. – Francois Botha May 10 '17 at 15:58
  • To answer the loop question, you can use Linq. (Ensure `System.Core` is referenced and a `using System.Linq` statement is added to your code). In my case I only wanted the first value found so using the above example: `foundCells = ws.Search("searchText", CompareOptions.OrdinalIgnoreCase).FirstOrDefault().Value.ToString(); ` – OldDog Oct 07 '21 at 20:43