Excel Data into datatable

Look at the GetConnectionString method in this recently posted article: Dynamic Excel file loading with SSIS.
It builds the connection string for the particular excel file supplied and works for me.
Take into account that sometimes the Microsoft.Ace.OLEDB.12.0 provider is not registered on the computer and you must download and install it: Microsoft Access Database Engine 2010 Redistributable.

Hi ,
Try this will Guide you .

if (!IsPostBack)
      {
          String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\Book1.xls;Extended Properties=\"Excel 8.0;Readonly=False;IMEX=0;\"";


          using (OleDbConnection objConn = new OleDbConnection(sConnectionString))
          {
              objConn.Open();

              using (OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [Info$]", objConn))
              {
                  objCmdSelect.CommandType = CommandType.Text;
                  OleDbDataAdapter objAdapter1 = new OleDbDataAdapter(objCmdSelect);

                  DataSet objDataset1 = new DataSet();

                  objAdapter1.Fill(objDataset1);
                  GridView1.DataSource = objDataset1.Tables[0];
                  GridView1.DataBind();
              }

          }
      }

Best Regards
M.Mitwalli

protected void btnImport_Click(object sender, EventArgs e)
    {
        if (Excel.HasFile)
        {
            string Filename = Path.GetFileName(Excel.FileName);
            string ext = Path.GetExtension(Excel.FileName);

            
            Excel.SaveAs(Server.MapPath("Excel/"+Filename));
            
            Save(Filename);
        }

    }



    public void Save(String FileName)
    {
        string ExcelString = @"Provider=Microsoft.Ace.OLEDB.12.0;Data Source='"+Server.MapPath("Excel/"+FileName)+"';Extended Properties='Excel 8.0; HDR=YES;IMEX=1;'";
        OleDbConnection conn = new OleDbConnection(ExcelString);

        OleDbCommand cmd = new OleDbCommand();
        cmd = new OleDbCommand("Select  * from [Sheet1$]",conn);
        OleDbDataAdapter adp = new OleDbDataAdapter(cmd);
        adp.SelectCommand = cmd;
       
        DataTable dt = new DataTable();
        adp.Fill(dt);
        //Response.Write(dt.Rows[0]["FirstName"].ToString());
        for (int i = 0; i <dt.rows.count;>        {
            Int32 k = 3;
            ImportDAL obj = new ImportDAL();
            obj.Question = dt.Rows[i]["Question"].ToString();
            obj.NoOfOptions = dt.Rows[i]["NoOfOptions"].ToString();
            Int32 OptionNo = Convert.ToInt32( dt.Rows[i]["NoOfOptions"].ToString());
            obj.OtherDetails = dt.Rows[i]["OtherDetails"].ToString();
            Int32 ID = obj.Save_Question();
            //Response.Write(ID); 
         
            for (int j = 0; j <OptionNo; j++)
            {
                obj.QuestionID = ID;
                obj.Option = dt.Rows[i][k].ToString();
                obj.Save_Options();
                k++;

            }
        }
        conn.Close();
    }

Hi,
This link may help :
nice-tutorials.blogspot.com/2011/09/external-table-is-not-in-expected.html?m=1

I had a small page doiing the same thing. here is the code snippet for the pages code behind.

public partial class _Default : System.Web.UI.Page 
{
    OleDbConnection con = null;

    protected void Page_Load(object sender, EventArgs e)
    {
        string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\testExcel.xls;Extended Properties='Excel 8.0;HDR=YES;'";

        con = new OleDbConnection(connectionString);
    }


    protected void Button1_Click(object sender, EventArgs e)
    {
        try
        {
            con.Open();

            Label1.Text = con.State.ToString();

            if (con.State == ConnectionState.Open)
            {
                OleDbCommand cmd = con.CreateCommand();
                cmd.CommandText = "Select * from [Sheet1$]";
                cmd.CommandType = CommandType.Text;
                
                OleDbDataAdapter da = new OleDbDataAdapter(cmd);
                DataTable table = new DataTable();

                da.Fill(table);

                GridView1.DataSource = table;
                GridView1.DataBind();
            }
        }
        finally
        {
            con.Close();
        }
    }
}

Which version of excel you are trying to read??

this error generally comes when using Excel 2007 file with a connection string that uses: Microsoft.Jet.OLEDB.4.0 and Extended Properties=Excel 8.0

try these

public static string excelFilePath = @"C:\test.xlsx";

public static string excelConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFilePath + ";Extended Properties=Excel 12.0;";