Post
Ask Question

How to Read Records from Excel to Datatable in C#

table

The above is sample excel sheet for demo.

I have named it as StudentDetails.

Here is steps to read all the records to datatable

Step 1: Create a oledb connection,command and adapter fields.

Step 2: Create method like to initialize oledb connection string.

  1. void  InitializeOledbConnection(string filename, string extrn)    
  2. {    
  3. string connString = "";    
  4.     
  5. if (extrn == ".xls")    
  6.  //Connectionstring for excel v8.0    
  7.     
  8.     connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + your excel file path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";    
  9.     else    
  10.             //Connectionstring fo excel v12.0    
  11.        connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + your excel file path + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\"";    
  12.     
  13.     OledbConn = new OleDbConnection(connString);    
  14. }   
Step 3: Create a method like below to read records from excel file I name it as ReadFile().
  1. private DataTable ReadFile()    
  2. {    
  3.     try    
  4.     {    
  5.       
  6.         DataTable schemaTable = new DataTable();    
  7.         OledbCmd = new OleDbCommand();    
  8.         OledbCmd.Connection = OledbConn;    
  9.         OledbConn.Open();     
  10.         OledbCmd.CommandText = "Select * from [StudentDetails$]";    
  11.         OleDbDataReader dr = OledbCmd.ExecuteReader();    
  12.         DataTable ContentTable = null;    
  13.         if (dr.HasRows)    
  14.         {    
  15.             ContentTable = new DataTable();    
  16.             ContentTable.Columns.Add("Name", typeof(string));    
  17.             ContentTable.Columns.Add("RollNo", typeof(string));    
  18.             ContentTable.Columns.Add("Dept", typeof(string));    
  19.             while (dr.Read())    
  20.             {    
  21.                 if (dr[0].ToString().Trim() != string.Empty && dr[1].ToString().Trim() != string.Empty && dr[2].ToString().Trim() != string.Empty && dr[0].ToString().Trim() != " " && dr[1].ToString().Trim() != " " && dr[2].ToString().Trim() != " ")    
  22.                     ContentTable.Rows.Add(dr[0].ToString().Trim(), dr[1].ToString().Trim(), dr[2].ToString().Trim());    
  23.   
  24.             }    
  25.         }    
  26.         dr.Close();    
  27.   
  28.         OledbConn.Close();     
  29.         return ContentTable;    
  30.     }    
  31.     catch (Exception ex)    
  32.     {    
  33.         throw ex;    
  34.   
  35.     }    
  36. }   
Step 4: Now we reached the file step to invoke all the methods we created.

I just call the above methods in btn click event.
  1. protected btnimport_click(object sender, eventargs e)    
  2. {    
  3.     InitializeOledbConnection(“C:\Sample.xls”,”.xls” );    
  4.            DataTable tempTable=  ReadFile();    
  5. }  
That is it :)
Next Recommended Reading Read and Import Excel File into DataSet or DataTable using C# in ASP.NET