How to get specific elements from an existing Excel file using C# with the Interop.Excel library?

Question

I need to get data from an Excel file to print them in a HTML table (using MVC, no gridview) and eventually store them in a Database.

The mapping between table and Excel is as follows:

Excel -> Table

First Row -> Table headers

Other Cells -> Table data

I am using the Interop.Excel library that provides methods to manipulate Excel files in .NET.

With this code I obtain in lworkSheet variable the Nth worksheet of the Excel file:

var lworkSheet = xlWorkBook.Worksheets.get_Item(N);

Let's assume the Excel file has just one worksheet (N = 1), I can use worksheet specific properties to get Rows, Columns, Cells and Range. These properties return objects of type Interop.Excel.Range.

The problem is that Rows, Columns and Cells return, respectively, all the rows, columns and cells in the Excel file not just those that are filled with data. Therefore in order to get the data I do (the index of the Excel items are 1-based):

var lheaders = xlWorkSheet.Rows.get_Item(1);
var lexcelItems = new Excel.Range[xlWorkSheet.Rows.Count, xlWorkSheet.Columns.Count];

for (var i=0; i < xlWorkSheet.Rows.Count; i++)
{
    for(var j=0; j < xlWorkSheet.Columns.Count; j++)
    {
        lexcelItems[i,j] = xlWorkSheet.Cells.get_Item(i+2, j+1);
    }
}

Besides the computational waste of cycling all rows and columns, these solution is still not acceptable because the get_Item() method returns Range objects!! In order to get the item in the cell I have to use the get_Range(cell_start, cell_end) method and specify the cells in the "A1", "A2", etc... format.

QUESTIONS:

1) Any way to identify last item in row and column?

2) Any way to get the value in the cell without specify the range?

3) Any library that implements the Excel.Range increment? (i.e. (A1++) == A2, etc...).

4) If none of the above is feasible, is there an easy way to read Excel with OLEDB?

Thanks

Francesco

mcw · Accepted Answer · 2011-04-27 14:54:19Z

1

Haven't worked with this library so I'm just making an educated guess from the MSDN documentation. Are you looking for Worksheet.UsedRange?

http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel._worksheet.usedrange(v=office.11).aspx

Once you have the Range, it looks like you can use the Range.Cells property (analogous to the Worksheet.Cells property you're using) to get a range of a single cell and then use the Range.Value or Range.Value2 property to get that cell's value:

http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.range_properties(v=office.11).aspx

To specify the cells within the UsedRange based on position rather than row/column names, use Range.Offset:

http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.range(v=office.11).aspx

edited Apr 27, 2011 at 14:54

answered Apr 27, 2011 at 13:21

mcw

3,6461 gold badge33 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

CiccioMiami Over a year ago

yes this might be usefu, thanks. However it does not solve the problem of dealing with Range objects. How would you extract the actual values from Excel?

CiccioMiami Over a year ago

Well, if you use Value and Value2 properties you still need to specify the range: Range("a1").Value to get the value in cell A1. My problem is that I cannot cycle if I have to define everytime how to increment the columns by alphabet letter. The problem arises when after z you have to use 2 letters(AA to ZZ), then 3(AAA to ZZZ) and so on.

Ekkehard.Horner · Accepted Answer · 2011-04-27 14:32:50Z

1

Using OLEDB to access/process Excel data is better than to loop over .Cells(); putting a (selection of) a sheet with one SQL statement into a database table or .GetString a resultset as a HTML table without any Fors is attractive.

The con: if your data contains garbage, the high level ADO/OLEDB facilities won't give you a chance to interfere.

ADDED:

While doing some experiments wrt what "all" means for an Excel sheet, I stumbled upon an interesting fact? about the "$". Perhaps I'm not the only one being not aware of this:

This is the output from my VBScript test script:

-------------------------------------------------------------------------------
SELECT * FROM [SakAct$]
-------------------------------------------------------------------------------
|actor_id|first_name|last_name|last_update         |
|       3|ED        |CHASE    |2/15/2006 4:34:33 AM|
|       4|JENNIFER  |DAVIS    |2/15/2006 4:34:33 AM|
|       1|PENELOPE  |GUINESS  |2/15/2006 4:34:33 AM|
|       2|NICK      |WAHLBERG |2/15/2006 4:34:33 AM|
|  <NULL>|ED        |CHASE    |2/15/2006 4:34:33 AM|
|       5|          |DAVIS    |2/15/2006 4:34:33 AM|
|       6|PENELOPE  |         |2/15/2006 4:34:33 AM|
|       7|NICK      |WAHLBERG |       <NULL>       |
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
SELECT * FROM [SakAct]
-------------------------------------------------------------------------------
|actor_id|first_name|last_name|last_update         |
|       3|ED        |CHASE    |2/15/2006 4:34:33 AM|
|       4|JENNIFER  |DAVIS    |2/15/2006 4:34:33 AM|
|       1|PENELOPE  |GUINESS  |2/15/2006 4:34:33 AM|
|       2|NICK      |WAHLBERG |2/15/2006 4:34:33 AM|
-------------------------------------------------------------------------------

Looks like [Sheet$] turns on an Excel Quirks mode, that will consider more rows than the stricter approach enabled by [Sheet].

edited Apr 27, 2011 at 14:32

answered Apr 27, 2011 at 13:23

Ekkehard.Horner

38.8k2 gold badges50 silver badges101 bronze badges

4 Comments

CiccioMiami Over a year ago

Thanks for your answer. How would you, for instance, get the actually used cells from a worksheet? And the first row?

Ekkehard.Horner Over a year ago

@Francesco: If you do a "SELECT * FROM Sheet1$" you'll get "all" rows. "All" is defined by the provider; you may not agree. But for decent data I had never reason to complain. As to the first row: the returned recordset's field collection gives you field names and types.

CiccioMiami Over a year ago

Ok I understand the expression, but from where do you get the "Sheet1$"? Which kind of object it is?

Ekkehard.Horner Over a year ago

"Sheet1" is the name of the sheet; it functions like a tablename in standard SQL; the $ may or may not be needed depending on the provider.

Collectives™ on Stack Overflow

How to get specific elements from an existing Excel file using C# with the Interop.Excel library?

2 Answers 2

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related