C# code to find the end range of excel sheet data using Data tables
<p>I am trying the find the Excel Row position to get the END point for my data fetch Range for excel using SSIS script component. I am using Datatable in a Script task to facilitate this.
In the below example my sheet range always starts with 1. I need to get the end position by searching "End of data" in the range A2:A10</p>

<p><img src="http://i.stack.imgur.com/KmxT6.gif" alt="enter image description here"></p>

<p>Below is the Code I am using</p>

<pre><code>string currentable ="Sheet1$"
string strSQL = "Select * From ["+currentTable+"A2:A10]";
int iCnt = 0;
OleDbDataAdapter objAdapter = new OleDbDataAdapter(strSQL, excelConnection);
DataSet ds = new DataSet();
objAdapter.Fill(ds, currentTable);
DataTable dt = ds.Tables[currentTable];


foreach (DataRow  row in dt.Rows)
{
    MessageBox.Show("In for Loop");
    iCnt = iCnt + 1;
    MessageBox.Show(row[iCnt].ToString());


}
</code></pre>

<p>In this code I am getting the following error</p>

<p>System.IndexOutOfRangeException: Cannot find column 1.</p>

<p>I need to browse through the First column of the excel and search the string "End of Data"  and get its position.</p>

<p>Any ideas/help</p>

<p>Thanks</p>
