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;
    }

Answer 2

I used the following code, it's working!

using System.Data.OleDb;
using System.IO;
using System.Text.RegularExpressions;

private void btopen_Click(object sender, EventArgs e)
{
   try
   {
     OpenFileDialog openFileDialog1 = new OpenFileDialog();  //create openfileDialog Object
     openFileDialog1.Filter = "XML Files (*.xml; *.xls; *.xlsx; *.xlsm; *.xlsb) |*.xml; *.xls; *.xlsx; *.xlsm; *.xlsb";//open file format define Excel Files(.xls)|*.xls| Excel Files(.xlsx)|*.xlsx| 
     openFileDialog1.FilterIndex = 3;

     openFileDialog1.Multiselect = false;        //not allow multiline selection at the file selection level
     openFileDialog1.Title = "Open Text File-R13";   //define the name of openfileDialog
     openFileDialog1.InitialDirectory = @"Desktop"; //define the initial directory

     if (openFileDialog1.ShowDialog() == DialogResult.OK)        //executing when file open
     {
       string pathName = openFileDialog1.FileName;
       fileName = System.IO.Path.GetFileNameWithoutExtension(openFileDialog1.FileName);
       DataTable tbContainer = new DataTable();
       string strConn = string.Empty;
       string sheetName = fileName;

       FileInfo file = new FileInfo(pathName);
       if (!file.Exists) { throw new Exception("Error, file doesn't exists!"); }
       string extension = file.Extension;
       switch (extension)
       {
          case ".xls":
                   strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
                   break;
          case ".xlsx":
                   strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pathName + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'";
                   break;
          default:
                   strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
                   break;
         }
         OleDbConnection cnnxls = new OleDbConnection(strConn);
         OleDbDataAdapter oda = new OleDbDataAdapter(string.Format("select * from [{0}$]", sheetName), cnnxls);
         oda.Fill(tbContainer);

         dtGrid.DataSource = tbContainer;
       }

     }
     catch (Exception)
     {
        MessageBox.Show("Error!");
     }
  }

Answer 3

try this following snippet, its working fine.

private void button1_Click(object sender, EventArgs e)
{
     try
     {
             OpenFileDialog openfile1 = new OpenFileDialog();
             if (openfile1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
             {
                   this.textBox1.Text = openfile1.FileName;
             }
             {
                   string pathconn = "Provider = Microsoft.jet.OLEDB.4.0; Data source=" + textBox1.Text + ";Extended Properties=\"Excel 8.0;HDR= yes;\";";
                   OleDbConnection conn = new OleDbConnection(pathconn);
                   OleDbDataAdapter MyDataAdapter = new OleDbDataAdapter("Select * from [" + textBox2.Text + "$]", conn);
                   DataTable dt = new DataTable();
                   MyDataAdapter.Fill(dt);
                   dataGridView1.DataSource = dt;
             }
      }
      catch { }
}