Import Excel into Datagridview

Question

I use this code to open an excel file and save it to 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; 

How to explain: This link is for youtube

But I get an ISAM error. Any suggestion?

Answer 1

Since you did not respond to my comment above, I am posting a solution for both.

You are Missing ' in Extended Properties

For Excel 2003 try this ( TRIED AND TESTED )

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

By the way, I have not worked with Jet for a long time. I am using 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

try the next snippet, its working tone.

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

Answer 3

I used the following code, it works!

 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 4

try the following program

 using System; using System.Data; using System.Windows.Forms; using System.Data.SqlClient; namespace WindowsFormsApplication1 { public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void button1_Click(object sender, EventArgs e) { System.Data.OleDb.OleDbConnection MyConnection; System.Data.DataSet DtSet; System.Data.OleDb.OleDbDataAdapter MyCommand; MyConnection = new System.Data.OleDb.OleDbConnection(@"provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\csharp.net-informations.xls';Extended Properties=Excel 8.0;"); MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection); MyCommand.TableMappings.Add("Table", "Net-informations.com"); DtSet = new System.Data.DataSet(); MyCommand.Fill(DtSet); dataGridView1.DataSource = DtSet.Tables[0]; MyConnection.Close(); } } }