Not logged in - Login

Read 1st page of Excel Worksheet with OLEDB

The following is an example of how to read in the 1st worksheet of an Excel file using the OLEDB data provider.

public static DataTable ReadExcelFirstWorksheet(string sFileName, int lStartRow, string sLastColumn, System.Web.UI.UpdatePanel updatePanel = null)
{
   DataTable tblRV = new DataTable();
   if (lStartRow < 1) throw new ArgumentException("Cannot be less than 1.", "lStartRow");
   if (lStartRow > 1 && string.IsNullOrWhiteSpace(sLastColumn)) throw new ArgumentException("Last Column must be specified if starting row is greater than 1", "sLastColumn");


   using (OleDbConnection oleConn = new System.Data.OleDb.OleDbConnection())
   using (OleDbCommand cmd = oleConn.CreateCommand())
   using (OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(cmd))
   {
      // verify that the file is an Excel file.
      string sExt = System.IO.Path.GetExtension(sFileName);
      if (sExt.Equals(".xls", StringComparison.CurrentCultureIgnoreCase))
      {
         // use the Excel 8.0 format for the connection string.
         oleConn.ConnectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=2'", sFileName);
      }
      else if (sExt.Equals(".xlsx", StringComparison.CurrentCultureIgnoreCase))
      {
         // use the Excel 12.0 format for the connection string.
         oleConn.ConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=YES;IMEX=1';", sFileName);
      }
      else
      {
         throw new System.Exception("File must have an Excel extension- *.xls or *.xlsx");
      }

      // open the connection.
      oleConn.Open();

      // get the schema to find the name of the 1st worksheet
      string sSheetName = string.Empty;
      using (var dtSchema = oleConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }))
      {
         sSheetName = dtSchema.Rows[0].Field<string>("TABLE_NAME");
      }

      if (string.IsNullOrWhiteSpace(sSheetName))
      {
         throw new System.Exception("Unable to retrieve name of 1st worksheet.");
      }

      cmd.CommandType = CommandType.Text;
      //cmd.CommandText = "select * from [Sheet1$]";

      if (lStartRow == 1)
      {
         cmd.CommandText = string.Format("select * from [{0}]", sSheetName);
      }
      else
      {
         cmd.CommandText = string.Format("select * from [{0}A{1}:{2}]", sSheetName, lStartRow, sLastColumn);
      }

      // read the rows into our data table.
      da.Fill(tblRV);

   }

   return tblRV;
}