This project has moved and is read-only. For the latest updates, please go here.

Using foreach to deal with Excel rows and skip the 1st row as header

Apr 24, 2014 at 10:49 AM
Edited Apr 24, 2014 at 12:54 PM
I have seen the following example here

Image
Image

My try is below
var wb = new XLWorkbook(Filename);
var ws = wb.Worksheets.First();
var range = ws.RangeUsed();
var table = range.AsTable();

foreach(var row in table.Rows())
{
    string test1 = row.Field("Name").GetString();  // <--- Doesn't work
    string test2 = row.Cell(1).GetString();             // <--- Works but doesn't skip header row
}
I need to skip the 1st row which in my case is a table header.
The problem is there isn't such overload for Rows(). All overloads require parameters.
Error 1 No overload for method 'Rows' takes '0' arguments

How can I use foreach to deal with the excel rows and skip the header?

I am using ClosedXML v0.69.1.0, Open XML Format SDK 2.0 and .NET 3.5 (VS2008)

Thanks
Apr 25, 2014 at 6:18 PM
You can indeed use Rows() function without any arguments. I tried it and it works
There are 3 overloads
  • IXLRangeRows Rows(Func<IXLRangeRow, bool> predicate = null); // but this could be empty!
  • IXLRangeRows Rows(string rows); // e.g. Rows("4:5"), Rows("7:8,10:11"), Rows("13")
  • IXLRangeRows Rows(int firstRow, int lastRow);
    string test1 = row.Field("Name").GetString();  // <--- Doesn't work
Note - this will only work if one of the columns has the heading of "Name"
Do any of your columns have the heading of "Name"?
    string test2 = row.Cell(1).GetString();             // <--- Works but doesn't skip header row
Of course not. You told it to iterate through all the rows!
To skip the first row do
    foreach(var row in table.Rows(table.FirstRow+1,table.LastRow))
Apr 25, 2014 at 6:59 PM
Thanks for your reply.
IXLRangeRows Rows(Func<IXLRangeRow, bool> predicate = null); // but this could be empty!
It doesn't work in my case, I have tried to use Rows() without arguments but the compiler fails.



Regarding
string test1 = row.Field("Name").GetString();
I surely have a column called "Name" but since the Rows() doesn't work I cannot test it.

According to the article and since the current row is set, I could get the data from Field("Name") without the need to skip the header row.
Apr 25, 2014 at 7:10 PM
That example on InfoQ is really old. Check: https://closedxml.codeplex.com/wikipage?title=Using%20Tables&referringTitle=Documentation
foreach (var row in table.DataRange.Rows()) // DataRange skips the headers
{
  var name = row.Field("Name").GetString();
}
Hope this helps.
Apr 28, 2014 at 1:56 PM
It really helped, but the trick was to use the correct type of parameter in Rows().
Since Rows() doesn't work, I used Rows((Func<IXLTableRow, bool>)null) and works fine.

The code changed to
foreach (var row in table.DataRange.Rows((Func<IXLTableRow, bool>)null)) 
{
  var name = row.Field("Name").GetString();
}
Thank you all!