Reading from Excel File using ClosedXML

Question

My Excel file is not in tabular data. I am trying to read from an excel file. I have sections within my excel file that are tabular.

I need to loop through rows 3 to 20 which are tabular and read the data.

Here is party of my code:

     string fileName = "C:\\Folder1\\Prev.xlsx";
     var workbook = new XLWorkbook(fileName);
     var ws1 = workbook.Worksheet(1);

How do I loop through rows 3 to 20 and read columns 3,4, 6, 7, 8? Also if a row is empty, how do I determine that so I can skip over it without reading that each column has a value for a given row.

Raidri · Accepted Answer · 2017-12-07T08:12:31.327

55

To access a row:

var row = ws1.Row(3);

To check if the row is empty:

bool empty = row.IsEmpty();

To access a cell (column) in a row:

var cell = row.Cell(3);

To get the value from a cell:

object value = cell.Value;
// or
string value = cell.GetValue<string>();

For more information see the documentation.

edited Dec 07 '17 at 08:12

answered Apr 16 '15 at 10:15

Raidri

17,258
9
62
65

do you know a way to access cell styles and apply as html styles? – xwpdev Jun 18 '15 at 08:23
That's another question (which you should create, maybe). You can access styles with `cell.Style....` but you can not automatically convert them to HTML or CSS styles as far as I know. You would have to write a converter yourself. – Raidri Jun 18 '15 at 08:40
Could u tell me how take the value of cell using field name – Neeraj Mehta Oct 16 '15 at 10:10
@NeerajMehta You should post a new question for that. – Raidri Oct 16 '15 at 10:17
posted now http://stackoverflow.com/questions/33168302/how-to-get-the-value-of-excel-sheet-cell-using-their-field-name – Neeraj Mehta Oct 16 '15 at 10:32
@Raidri Please link to the new site at http://github.com/ClosedXML/ClosedXML . The Codeplex site is obsolete. – Francois Botha Dec 06 '17 at 21:19

Jess · Answer 2 · 2018-10-08T12:16:49.550

Here's my jam.

var rows = worksheet.RangeUsed().RowsUsed().Skip(1); // Skip header row
foreach (var row in rows)
{
    var rowNumber = row.RowNumber();
    // Process the row
}

If you just use .RowsUsed(), your range will contain a huge number of columns. Way more than are actually filled in!

So use .RangeUsed() first to limit the range. This will help you process the file faster!

You can also use .Skip(1) to skip over the column header row (if you have one).

RBT · Answer 3 · 2022-10-24T12:25:13.773

I'm not sure if this solution will solve OP's problem but I prefer using RowsUsed method. It can be used to get the list of only those rows which are non-empty or has been edited by the user. This way I can avoid making emptiness check while processing each row.

Below code snippet can process 3^rd to 20^th row numbers out of all the non-empty rows. I've filtered the empty rows before starting the foreach loop. Please bear in mind that filtering the non-empty rows before starting to process the rows can affect the total count of rows which will get processed. So you need to be careful while applying any logic which is based on the total number of rows processed inside foreach loop.

string fileName = "C:\\Folder1\\Prev.xlsx";
using (var excelWorkbook = new XLWorkbook(fileName))
{
    var nonEmptyDataRows = excelWorkbook.Worksheet(1).RowsUsed();

    foreach (var dataRow in nonEmptyDataRows)
    {
       //for row number check
       if(dataRow.RowNumber() >=3 && dataRow.RowNumber() <= 20)
       {
           //to get column # 3's data
           var cell = dataRow.Cell(3).Value;
       }
    }
}

RowsUsed method is helpful in commonly faced problems which require processing the rows of an excel sheet.

Saeid · Answer 4 · 2022-07-20T18:21:17.623

It works easily

 XLWorkbook workbook = new XLWorkbook(FilePath);
 var rowCount = workbook.Worksheet(1).LastRowUsed().RowNumber();
 var columnCount = workbook.Worksheet(1).LastColumnUsed().ColumnNumber();
 int column = 1;
 int row = 1;
 List<string> ll = new List<string>();
 while (row <= rowCount)
 {
      while (column <= columnCount)
      {
         string title = workbook.Worksheets.Worksheet(1).Cell(row, column).GetString();
                ll.Add(title);
                column++;
       }

 row++;
 column = 1;
}

Reading from Excel File using ClosedXML

4 Answers4

Linked