title: Reading Excel using C# and OleDB - How to identify the used range?
body: <p>I am reading an Excel file using good old OleDBConnections and OleDBCommands. How do I identify the used range of the excel sheet?</p>
  
  <p>For example, if I were to use InterOp, I can always do this - </p>
  
  <pre><code>Excel.Application xlApp = new Excel.Application();
  Excel.Workbook xWb;
  Excel.Worksheet xWs;
  Excel.Range range;
  
  xWb = xlApp.Workbooks.Open(@"D:\Technical\C#\WorkingFolder\HelloWorld.xls", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
  xWs = (Excel.Worksheet)xWb.Worksheets.get_Item(1);
  range = xWs.UsedRange;
  </code></pre>
  
  <p>Now, I get a list of all the used columns. With OleDB, how do I get it. Now, even if I were to nail down the column list, how will I know that the rows end here. </p>
  
  <p>Basically, my requirement is this. </p>
  
  <p>I am about to receive an Excel (2003) file. The data will always include Headers (i.e., column name). It does not mean that the data will always be populated from A1:J200</p>
  
  <p>It can also be from A5:J204 too. </p>
  
  <p>So, in using OleDBCommand</p>
  
  <pre><code>string sConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Technical\C#\WorkingFolder\HelloWorld.xls;Extended Properties=""Excel 8.0;HDR=YES;""";
  string sCommandString = "SELECT Employee_Id, First_Name FROM [Sheet1$A1:J200]";
  OleDbConnection xlConnection = new OleDbConnection(sConnectionString);
  xlConnection.Open();
  OleDbCommand xlCommand = new OleDbCommand(sCommandString, xlConnection);
  OleDbDataAdapter tempDataAdapter = new OleDbDataAdapter();
  tempDataAdapter.SelectCommand = xlCommand;
  DataTable tempDataTable = new DataTable("Whatever_I_Want");
  tempDataAdapter.Fill(tempDataTable);
  </code></pre>
  
  <p>Now, the line </p>
  
  <p>sCommandString has the hardcoded value of A1:J200, what if the excel I receive has the data range as A5:204, what will I do?</p>
  
  <p>Any help would be great!</p>