How to Read Records from Excel to Datatable in C#

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 :)