1

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

Javier Montón
  • 4,601
  • 3
  • 21
  • 29
subin joseph
  • 11
  • 2
  • What do you mean by column name? Spreadsheets have row numbers and column LETTERS or numbers. Are you referring to the column letter, e.g. column `M`? – Francois Botha Sep 30 '20 at 08:40
  • I meant is the Column Letters..Instead of using Cell(6) , I want to use cell("ColumnLetter")...Eventhough syntax support it ..it always returns null – subin joseph Sep 30 '20 at 10:42

2 Answers2

0

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();
Francois Botha
  • 4,520
  • 1
  • 34
  • 46
0

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();
hasi05
  • 194
  • 1
  • 5