import data from MS excel to datagridview c#.net 2005

ds ps

I have to import data from excel to datagridview control & then to sql database. i tried to import from excel with below code but its showing err msg .. i couldnot understand where it goes wrong... can anyone help me for this.. if its ok or have any other idea to do it.... plz help me at earliest.....

Code is like below:
private void btnimport_Click(object sender, EventArgs e)
{
Load_File(dataGridView1, "C:\\telph.xls", "Sheet1");

}
private void Load_File(DataGridView dg, String filename, String SSheet)
{
string cs = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename + ";Extended Properties=Excel 8.0;HDR=YES";
try
{
OleDbConnection cn = new OleDbConnection(cs);
if (!System.IO.File.Exists(filename))
{
MessageBox.Show("file not exists");
}
OleDbDataAdapter dAdapter = new OleDbDataAdapter("Select * From [" + SSheet + "$]", cs);
DataSet dts = new DataSet();
dAdapter.Fill(dts);
//DataTable dt = dts.Tables[0];
//return dt;
dg.DataSource = dts.Tables[0];
}
catch (Exception ex)
{
MessageBox.Show("ttt");
}
}

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Roei Bar17y ago
Accepted answer
First of all we are declaring the ODBC namespace which we will use an Excel DNS;

using System.Data.Odbc;

then we will write these codes for connecting to a specific Excel file;

String strConn = @"Dsn=Excel Files;dbq=excel_file_path;defaultdir=excel_file_dir;driverid=1046;maxbuffersize=2048;pagetimeout=5";
            OdbcConnection objConn = new OdbcConnection(strConn);
            objConn.Open();
            OdbcDataAdapter adp = new OdbcDataAdapter("select * from [Sheet1$]", objConn);
            DataSet ds = new DataSet();
            adp.Fill(ds);
            dataGridView1.DataSource = ds.Tables[0];
            objConn.Close();

Thats all by using these codes you have got the datas you wanted to...

Cheers;)
ds ps17y ago
thanks for this post.Good one
Master Billa17y ago
Hi

You code is correct, but there something missing

private void btnimport_Click(object sender, EventArgs e)
{
Load_File(dataGridView1, "C:\\telph.xls", "sheet1"); // Please check sheet name is correct

}
private void Load_File(DataGridView dg, String filename, String SSheet)
{
string cs = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename + ";Extended Properties=""Excel 8.0;HDR=YES;""";
try
{
OleDbConnection cn = new OleDbConnection(cs);
if (!System.IO.File.Exists(filename))
{
MessageBox.Show("file not exists");
}
OleDbDataAdapter dAdapter = new OleDbDataAdapter("Select * From [" + SSheet + "$]", cs);
DataSet dts = new DataSet();
dAdapter.Fill(dts);
//DataTable dt = dts.Tables[0];
//return dt;
dg.DataSource = dts.Tables[0];
}
catch (Exception ex)
{
MessageBox.Show("ttt");
}
}

I have changed in connectionstring. now try it will work

here some more inforamtion

http://davidhayden.com/blog/dave/archive/2006/05/26/2973.aspx

Thank you