StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow to get specific elements from an existing Excel file using C# with the Interop.Excel library?
text
Body
copied!<p>I need to get data from an <strong>Excel</strong> file to print them in a HTML table (using MVC, no gridview) and eventually store them in a Database.</p> <p>The mapping between table and Excel is as follows:</p> <p>Excel -> Table</p> <p>First Row -> Table headers</p> <p>Other Cells -> Table data</p> <p>I am using the <strong>Interop.Excel</strong> library that provides methods to manipulate Excel files in .NET.</p> <p>With this code I obtain in <strong>lworkSheet</strong> variable the <strong>Nth</strong> worksheet of the Excel file:</p> <pre><code>var lworkSheet = xlWorkBook.Worksheets.get_Item(N); </code></pre> <p>Let's assume the Excel file has just one worksheet (N = 1), I can use worksheet specific properties to get <strong>Rows, Columns, Cells and Range</strong>. These properties return objects of type <strong>Interop.Excel.Range</strong>.</p> <p>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):</p> <pre><code>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); } } </code></pre> <p>Besides the computational waste of cycling all rows and columns, these solution is still not acceptable because the <strong>get_Item()</strong> method returns <strong>Range</strong> objects!! In order to get the item in the cell I have to use the <strong>get_Range(cell_start, cell_end)</strong> method and specify the cells in the "A1", "A2", etc... format.</p> <p>QUESTIONS:</p> <p>1) Any way to identify last item in row and column?</p> <p>2) Any way to get the value in the cell without specify the range?</p> <p>3) Any library that implements the <strong>Excel.Range increment</strong>? (i.e. (A1++) == A2, etc...).</p> <p>4) If none of the above is feasible, is there an easy way to read Excel with <strong>OLEDB</strong>?</p> <p>Thanks</p> <p>Francesco</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload