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.

void InitializeOledbConnection(string filename, string extrn)
{
string connString = "";
if (extrn == ".xls")
//Connectionstring for excel v8.0
connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + your excel file path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
else
//Connectionstring fo excel v12.0
connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + your excel file path + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\"";
OledbConn = new OleDbConnection(connString);
}

Step 3: Create a method like below to read records from excel file I name it as ReadFile().

private DataTable ReadFile()
{
try
{
DataTable schemaTable = new DataTable();
OledbCmd = new OleDbCommand();
OledbCmd.Connection = OledbConn;
OledbConn.Open();
OledbCmd.CommandText = "Select * from [StudentDetails$]";
OleDbDataReader dr = OledbCmd.ExecuteReader();
DataTable ContentTable = null;
if (dr.HasRows)
{
ContentTable = new DataTable();
ContentTable.Columns.Add("Name", typeof(string));
ContentTable.Columns.Add("RollNo", typeof(string));
ContentTable.Columns.Add("Dept", typeof(string));
while (dr.Read())
{
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() != " ")
ContentTable.Rows.Add(dr[0].ToString().Trim(), dr[1].ToString().Trim(), dr[2].ToString().Trim());
}
}
dr.Close();
OledbConn.Close();
return ContentTable;
}
catch (Exception ex)
{
throw ex;
}
}

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.

protected btnimport_click(object sender, eventargs e)
{
InitializeOledbConnection(“C:\Sample.xls”,”.xls” );
DataTable tempTable= ReadFile();
}

That is it :)

1 Comment

Join the conversation! Your thoughts help the community grow.

Srilekha Bolamoni8y ago
Am having an excel data I have read that excel data in c# and want to store it in datatable and fetch particular columns from datatable and save it to sql database without using Oledb. Can you please help me out for this.