Interop Excel Range/Cell Issues

[EDN Admin]

Ive been researching online for weeks on how to write code that interacts with an Excel spreadsheet. I need to be able to read a sheet (the project will have the same format, but with varying row numbers), convert it to a structure (or object array, Im
still not sure which would be the best option), then create and write a sheet with data values.
Right now Im ensuring that I can scan a sheet, get the type of every cell, and be able to create a structure or object array from the sheets contents. I am using a for loop and the Cells[,] function.
<div style="color:Black;background-color:White; <pre>
<span style="color:Blue; using System;<br/>
<span style="color:Blue; using System.Collections.Generic;<br/>
<span style="color:Blue; using System.ComponentModel;<br/>
<span style="color:Blue; using System.Data;<br/>
<span style="color:Blue; using System.Drawing;<br/>
<span style="color:Blue; using System.Linq;<br/>
<span style="color:Blue; using System.Text;<br/>
<span style="color:Blue; using System.Windows.Forms;<br/>
<span style="color:Blue; using Excel = Microsoft.Office.Interop.Excel;<br/>
<span style="color:Blue; using System.Reflection;<br/>
<br/>
<span style="color:Blue; namespace interopExcel<br/>
{<br/>
<span style="color:Blue; public <span style="color:Blue; partial <span style="color:Blue; class Form1 : Form<br/>
{<br/>
<span style="color:Blue; public Form1()<br/>
{<br/>
InitializeComponent();<br/>
SetControlState();<br/>
}<br/>
<br/>
Excel.Application xlApp;<br/>
Excel.Workbook xlWorkBook;<br/>
Excel.Worksheet xlWorkSheet;<br/>
Excel.Range range;<br/>
<br/>
<br/>
<span style="color:Green; //button1 opens an existing excel file<br/>
<span style="color:Blue; private <span style="color:Blue; void button1_Click(<span style="color:Blue; object sender, EventArgs e)<br/>
{<br/>
<span style="color:Blue; string str;<br/>
<span style="color:Blue; double dbl;<br/>
<span style="color:Blue; float flt;<br/>
<span style="color:Blue; int rCnt = 0;<br/>
<span style="color:Blue; int cCnt = 0;<br/>
<br/>
<span style="color:Green; // Displays an OpenFileDialog so the user can select a Spreadsheet.<br/>
OpenFileDialog openFileDialog1 = <span style="color:Blue; new OpenFileDialog();<br/>
openFileDialog1.Filter = <span style="color:#A31515; "Excel files|*.xls";<br/>
openFileDialog1.Title = <span style="color:#A31515; "Select an Excel Spreadsheet";<br/>
<br/>
<br/>
<span style="color:Blue; if (openFileDialog1.ShowDialog() == DialogResult.OK)<br/>
{<br/>
openLabel.Text = openFileDialog1.FileName;<br/>
xlApp = <span style="color:Blue; new Excel.Application();<br/>
xlWorkBook = xlApp.Workbooks.Open(openFileDialog1.FileName, 0, <span style="color:Blue; true, 5, <span style="color:#A31515; "", <span style="color:#A31515; "", <span style="color:Blue; true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, <span style="color:#A31515; "t", <span style="color:Blue; false, <span style="color:Blue; false, 0, <span style="color:Blue; true, 1, 0);<br/>
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);<br/>
<br/>
range = xlWorkSheet.UsedRange;<br/>
<span style="color:Blue; int rowRange = range.Rows.Count;<br/>
<span style="color:Blue; int colRange = range.Columns.Count;<br/>
<br/>
<span style="color:Green; //uses a message box to show the cell type<br/>
<br/>
<span style="color:Blue; for (rCnt = 1; rCnt <= rowRange; rCnt++)<br/>
{<br/>
<span style="color:Blue; for (cCnt = 1; cCnt <= colRange; cCnt++)<br/>
{<br/>
<br/>
MessageBox.Show(<span style="color:#A31515; "The cell [" + rCnt.ToString() + <span style="color:#A31515; "," + cCnt.ToString() + <span style="color:#A31515; "] is of type: " +<br/>
(range.Cells[cCnt, rCnt] <span style="color:Blue; as Excel.Range).Value2.GetType());<br/>
}<br/>
}<br/>
xlWorkBook.Close(<span style="color:Blue; true, <span style="color:Blue; null, <span style="color:Blue; null);<br/>
xlApp.Quit();<br/>
<br/>
releaseObject(xlWorkSheet);<br/>
releaseObject(xlWorkBook);<br/>
releaseObject(xlApp);<br/>
}<br/>

[/code]

I would like to look at a cell, determine its type, and if its of type string, it will save as a string, and if its of type double, it will save as a float or double.

This is working but for some reason the for loop only sees a square of cells as having valid data. My spreadsheet Im testing it on is as follows, please pardon the spacing:

<table border="0" cellspacing="0" cellpadding="0" width="660" style="height:216px
<col width="82" style="width:62pt <col width="63" style="width:47pt <col span="3" width="64" style="width:48pt
<tbody>
<tr height="17" style="height:12.75pt
<td width="82" height="17" style="height:12.75pt; width:62pt Assay Name</td>
<td width="63" style="width:47pt Threshold</td>
<td width="64" style="width:48pt m</td>
<td width="64" style="width:48pt b</td>
<td width="64" style="width:48pt TEST_PARAM</td>
</tr>
<tr height="17" style="height:12.75pt
<td height="17" style="height:12.75pt bacteria1</td>
<td align="right 55</td>
<td align="right 5</td>
<td align="right 5</td>
<td>X</td>
</tr>
<tr height="17" style="height:12.75pt
<td height="17" style="height:12.75pt bacteria2</td>
<td align="right 18</td>
<td align="right 9</td>
<td align="right 7</td>
<td>Y</td>
</tr>
<tr height="17" style="height:12.75pt
<td height="17" style="height:12.75pt bacteria3</td>
<td align="right 94</td>
<td align="right 4</td>
<td align="right 6</td>
<td>Z</td>
</tr>
<tr height="17" style="height:12.75pt
<td height="17" style="height:12.75pt bacteria4</td>
<td align="right 453</td>
<td align="right 15</td>
<td align="right 5</td>
<td>AA</td>
</tr>
<tr height="17" style="height:12.75pt
<td height="17" style="height:12.75pt bacteria5</td>
<td align="right 5</td>
<td align="right 2</td>
<td align="right 7</td>
<td>BB</td>
</tr>
<tr height="17" style="height:12.75pt
<td height="17" style="height:12.75pt bacteria6</td>
<td align="right 63</td>
<td align="right 1</td>
<td align="right 5</td>
<td>CC</td>
</tr>
<tr height="17" style="height:12.75pt
<td height="17" style="height:12.75pt bacteria7</td>
<td align="right 54</td>
<td align="right 5</td>
<td align="right 7</td>
<td>DD</td>
</tr>
<tr height="17" style="height:12.75pt
<td height="17" style="height:12.75pt bacteria8</td>
<td align="right 4</td>
<td align="right 5</td>
<td align="right 6</td>
<td>EE</td>
</tr>
<tr height="17" style="height:12.75pt
<td height="17" style="height:12.75pt bacteria9</td>
<td align="right 45</td>
<td align="right 6</td>
<td align="right 5</td>
<td>FF</td>
</tr>
<tr height="17" style="height:12.75pt
<td height="17" style="height:12.75pt bacteria10</td>
<td align="right 12</td>
<td align="right 1</td>
<td align="right 7</td>
<td>GG</td>
</tr>
</tbody>
</table>
From here, I can only read a 5x5 portion of the sheet, once the for loop sees [6,1] the contents are null and the program crashes. The contents are NOT null, it would be bacteria5.
The second issue is that the type the program sees within the TEST_PARAM column is double, when it is clearly text and should be of type string... I cannot figure this one out.
Any time I try to use the Cells[,] function (I used it to copy this sheet as another .xls) within the for loop, it only accepted the 5x5 square of cells. I tried to use this copying function on a sheet just like the one above, but without the TEST_PARAM
column - I only got a 4x4 square back!

I tried manually changing the type within Excel 2003 from general to text but it didnt change anything.

My questions I am looking for guidance on are:
1. Why does the for loop with Cells[,] not work as it should?
2. Why does excel see the TEST_PARAMETER column cells as double?
3. What should I save members of a sheet as? (structure, object array, etc.)

Any other tips would be of great assistance as this is my first Windows Application.


View the full article