Import Excel to Datagridview

Question

I'm using this code to open an excel file and save it in a DataGridView:

string name = "Items";
string constr = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source=" + Dialog_Excel.FileName.ToString() + "; Extented Properties =\"Excel 8.0; HDR=Yes;\";";
OleDbConnection con = new OleDbConnection(constr);
OleDbDataAdapter sda = new OleDbDataAdapter("Select * From [" + name + "$]", con);
DataTable data = new DataTable();
sda.Fill(data);
grid_items.DataSource = data;

As explained on: This youtube link

But I get an ISAM error. Any suggestion?

Answer 1

I am posting a solution for both Excel 2003 and Excel 2007+.

You are missing ' in Extended Properties

For Excel 2003 try this

    private void button1_Click(object sender, EventArgs e)
    {
        String name = "Items";
        String constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
                        "C:\\Sample.xls" + 
                        ";Extended Properties='Excel 8.0;HDR=YES;';";

        OleDbConnection con = new OleDbConnection(constr);
        OleDbCommand oconn = new OleDbCommand("Select * From [" + name + "$]", con);
        con.Open();

        OleDbDataAdapter sda = new OleDbDataAdapter(oconn);
        DataTable data = new DataTable();
        sda.Fill(data);
        grid_items.DataSource = data;
    }

BTW, I stopped working with Jet longtime ago. I use ACE now.

    private void button1_Click(object sender, EventArgs e)
    {
        String name = "Items";
        String constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                        "C:\\Sample.xls" + 
                        ";Extended Properties='Excel 8.0;HDR=YES;';";

        OleDbConnection con = new OleDbConnection(constr);
        OleDbCommand oconn = new OleDbCommand("Select * From [" + name + "$]", con);
        con.Open();

        OleDbDataAdapter sda = new OleDbDataAdapter(oconn);
        DataTable data = new DataTable();
        sda.Fill(data);
        grid_items.DataSource = data;
    }

For Excel 2007+

    private void button1_Click(object sender, EventArgs e)
    {
        String name = "Items";
        String constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                        "C:\\Sample.xlsx" + 
                        ";Extended Properties='Excel 12.0 XML;HDR=YES;';";

        OleDbConnection con = new OleDbConnection(constr);
        OleDbCommand oconn = new OleDbCommand("Select * From [" + name + "$]", con);
        con.Open();

        OleDbDataAdapter sda = new OleDbDataAdapter(oconn);
        DataTable data = new DataTable();
        sda.Fill(data);
        grid_items.DataSource = data;
    }