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....