private void button2_Click(object sender, EventArgs e)
{
string ExcelConstr = @"Provider=Microsoft.ACE.OLEDB.12.0";
ExcelConstr += "Data Source=d:\book1.xls;Extended Properties=Excel 12.0";
string SqlConstr = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;";
SqlConstr += "Integrated Security=True;User Instance=True";
using (OleDbConnection con = new OleDbConnection(ExcelConstr))
{
con.Open();
OleDbCommand com = new OleDbCommand("Select * from [Sheet1$]", con);
OleDbDataReader dr = com.ExecuteReader();
using (SqlConnection sqlcon = new SqlConnection(SqlConstr))
{
sqlcon.Open();
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlcon))
{
bulkCopy.DestinationTableName = "Table1";
bulkCopy.ColumnMappings.Add("srno", "srno");
bulkCopy.ColumnMappings.Add("amount", "amount");
bulkCopy.WriteToServer(dr);
}
}
dr.Close();
dr.Dispose();
}
MessageBox.Show("successfully imported!");
//display the imported data in the datagrid
SqlDataAdapter da = new SqlDataAdapter("select * from Table1", SqlConstr);
DataTable dt = new DataTable();
da.Fill(dt);
dataGridViewX1.DataSource = dt;
}