Showing posts with label .Net Excel To DB. Show all posts
Showing posts with label .Net Excel To DB. Show all posts

Sunday, March 2, 2008

Excel To SQL

Transferring Excel sheet cell values to SQL server is never a tough job now.

Here is how we can transfer all the cell contents from an excel file to a database in a single click.

private void button1_Click(object sender, EventArgs e)
{
System.Data.Odbc.OdbcConnection con = new System.Data.Odbc.OdbcConnection();
string strConnString;
strConnString = "Driver={Microsoft Excel Driver
(*.xls)};DriverId=790;Dbq=c:\\test1.xls;DefaultDir=c:\\";
con.ConnectionString = strConnString;
con.Open();
string select = "select a,x from [sheet1$]";
System.Data.Odbc.OdbcDataAdapter odbcdataAdapter = new
System.Data.Odbc.OdbcDataAdapter(select, con);
DataSet excelDataset = new DataSet();
try
{
odbcdataAdapter.Fill(excelDataset);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
SqlConnection cnn = new SqlConnection();
cnn.ConnectionString = "server=abc;database=test1;uid=user;pwd=passwd;Connection Timeout=200;";
try
{
cnn.Open();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
SqlCommand cmm = new SqlCommand();
cmm.Connection = cnn;

foreach (DataRow row in excelDataset.Tables[0].Rows)
{
cmm.CommandText = "insert into testWriteTable values('" + row[0].ToString() + "','" +
row[1].ToString() + "')";
cmm.ExecuteNonQuery();
}
cnn.Close();
}

Hope this helps somebody out there.

Cheers....