将Excel的数据导入到Sql数据库
private void button1_Click(object sender, EventArgs e)
{
openFileDialog1.ShowDialog();
string filename = openFileDialog1.FileName;
string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; " + "Data Source =" + filename + ";Extended Properties=Excel 8.0";
OleDbConnection myConn = new OleDbConnection(strCon);
myConn.Open();
string strCom = "Select * FROM [Sheet1$] ";
OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
DataSet dataSet1 = new DataSet();
myCommand.Fill(dataSet1, "[Sheet1$]");
myConn.Close();
//窗体上实例化一数据显示器DataGrid,并将Excel的数据显示出来.
DataGrid mygrid = new DataGrid();
mygrid.BeginInit();
mygrid.Location = new System.Drawing.Point(8, 8);
mygrid.Width = 800;
mygrid.Height = 600;
this.Controls.Add(mygrid);
mygrid.EndInit();
mygrid.SetDataBinding(dataSet1, "[Sheet1$]");
// 将数据集的数据放到SQL数据库里.
int num = dataSet1.Tables[0].Rows.Count; //数据集里的行数
for (int i = 0; i < num; i++)
{
string pp0 = mygrid[i, 0].ToString();
string pp1 = mygrid[i, 1].ToString();
string pp2 = mygrid[i, 2].ToString();
DateTime pp3 = Convert.ToDateTime(mygrid[i, 4]);
string strii = "select * into from" + dataSet1.Tables[0].TableName;
string str = "insert into DRUTEST values('" + pp0 + "','" + pp1 + "','" + pp2 + "','" + pp3 + "')";
string connectionString = "server=Sunny; database=people; trusted_connection=true";
SqlConnection connection = new SqlConnection(connectionString);
System.Data.DataTable dt = new System.Data.DataTable("Employees");
SqlDataAdapter da = new SqlDataAdapter(str, connection);
da.Fill(dt);
}
}