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 = "";
  5. if (extrn == ".xls")
  6. //Connectionstring for excel v8.0
  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\"";
  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. {
  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());
  24. }
  25. }
  26. dr.Close();
  28. OledbConn.Close();
  29. return ContentTable;
  30. }
  31. catch (Exception ex)
  32. {
  33. throw ex;
  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 :)