Reading only specific rows from Excel using ClosedXML

Question

I would like to read only specific row from an Excel based on value in one of the columns using Lynq query or similar instead of looping through entire rows. Below one does the trick if I exactly know the column index.

var result = workSheet.Rows().Where(x => x.Cell(6)?.Value.ToString() == constant).FirstOrDefault();

Since column index can change at any time, I would like to read row data using column name. Even though syntax support it (Cell("ColumnName")) , result is empty every time .

Thanks in advance subin

Answer 1

You can use XLHelper.GetColumnNumberFromLetter() to get the correct column number for any given column letter.

So you code would change to:

var result = workSheet.Rows()
    .Where(x => x.Cell(XLHelper.GetColumnNumberFromLetter("F"))?.Value.ToString() == constant)
    .FirstOrDefault();

Answer 2

You could use the search function to get the column - cell and then get the number with .Address.ColumnNumer

var columnCell = wSheet.Search(columnConstant, CompareOptions.OrdinalIgnoreCase).FirstOrDefault();

var result = workSheet.Rows().Where(x => x.Cell(columnCell.Address.ColumnNumber)?.Value.ToString() == constant).FirstOrDefault();