Iterate through all the data in excel file and find specific text

Question

I need to generate a excel file which range is not known, so I can't iterate through fixed range of columns/rows. So is there a way to iterate through the used cells in order to find all cells with text "V" using ClosedXML or OpenXML?

Answer 1

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);

Answer 2

In ClosedXML you can use something like this:

using (var cells = worksheet.CellsUsed(c => c.GetString() == "V"))
{
    foreach (var cell in cells)
    {
        // Do something with the cell ...
    }
}

Answer 3

You must opt for EPPlus, it is open source library to work with Excel and have good speed as compare to both either ClosedXML or OpenXML.