Reading Excel 2007 Rows in C# / .Net

[BethH]

I am trying to read an Excel spreadsheet row by row and transform the data in
each row into my own business object. I am trying to figure out the "best
practice" for doing this in C# / .NET 3.5 (VS2008). I have tried the
following:

1. Using an OleDbConnection to "SELECT FROM" my particular worksheet into a
DataTable. Then I must iterate over the DataTable and pull the data I want
from each DataRow. Actually this seems to be the most straightforward from a
coding perspective.

2. I found this example to read a particular cell from a worksheet using an
XmlDocument: http://msdn.microsoft.com/en-us/library/bb332058.aspx. This
seems to have A LOT of code behind it just to read one cell. And I am unsure
how to modify this code to iterate over the worksheet one row at a time.

3. Using Interop. I had an example using Interop to open the document and
read a worksheet. Then I found I had to read a range on a per column basis
into an array, and then I had to transform the data in the array into what I
was looking for. This also seems very cumbersome. Also, I had a working
example for an Excel 2003 spreadsheet, but when I tried to use the same code
to open an Excel 2007 spreadsheet, I got an "out of memory" error when just
opening the file. I'm not sure if I have some parameter not set correctly in
this case.

I am not sure if this is the correct forum to ask this question as this may
be more for the mechanics of using Excel rather than trying to read the file
programmatically. It seemed that using the OleDbConnection was not the
preferred method any more, but it seems to be the most straightforward for my
needs. Just wondering if anyone had any thoughts on this.

Thanks,
Beth

 

[Chuck]

I am trying to read an Excel spreadsheet row by row and transform the data in
each row into my own business object. I am trying to figure out the "best
practice" for doing this in C# / .NET 3.5 (VS2008). I have tried the
following:

1. Using an OleDbConnection to "SELECT FROM" my particular worksheet into a
DataTable. Then I must iterate over the DataTable and pull the data I want
from each DataRow. Actually this seems to be the most straightforward from a
coding perspective.

2. I found this example to read a particular cell from a worksheet using an
XmlDocument: http://msdn.microsoft.com/en-us/library/bb332058.aspx. This
seems to have A LOT of code behind it just to read one cell. And I am unsure
how to modify this code to iterate over the worksheet one row at a time.

3. Using Interop. I had an example using Interop to open the document and
read a worksheet. Then I found I had to read a range on a per column basis
into an array, and then I had to transform the data in the array into what I
was looking for. This also seems very cumbersome. Also, I had a working
example for an Excel 2003 spreadsheet, but when I tried to use the same code
to open an Excel 2007 spreadsheet, I got an "out of memory" error when just
opening the file. I'm not sure if I have some parameter not set correctly in
this case.

I am not sure if this is the correct forum to ask this question as this may
be more for the mechanics of using Excel rather than trying to read the file
programmatically. It seemed that using the OleDbConnection was not the
preferred method any more, but it seems to be the most straightforward for my
needs. Just wondering if anyone had any thoughts on this.

Thanks,
Beth

Not every expert using Excel is also an expert using C# / .NET
I suggest you ask your question in a C# / .NET forum.