很久前整理的,写出来做个记录。
具体思路是:EXCEL --> DataSet --> DB
//
从excel档中读取数据到DataSet
// Response.Write(Request.Form["filePath"]+" <br>");
string oleconnstring = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + Request.Form[ " filePath " ] + " ;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1' " ;
string queryText = " SELECT * FROM [Sheet1$] " ;
OleDbConnection oleconn = new OleDbConnection(oleconnstring);
OleDbDataAdapter Dr1 = new OleDbDataAdapter(queryText,oleconn);
DataSet Ds1 = new DataSet();
try
... {
oleconn.Open();
Dr1.Fill(Ds1);
}
catch (Exception ex)
... {
Response.Write("错误:"+ex.Message);
}
finally
... {
oleconn.Close();
}
// 把DataSet中的数据插入数据库
try
... {
SqlConnection conn = new SqlConnection(connstring);
conn.Open();
SqlTransaction trans = conn.BeginTransaction();
//插入数据
string insertText="";
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.Transaction = trans;
for(int i=0;i <Ds1.Tables[0].Rows.Count;i++)
...{
insertText = "insert into t_relcorp_employee(aa,bb) values('"+Ds1.Tables[0].Rows[i][0]+"','"+Ds1.Tables[0].Rows[i][1]+"')";
cmd.CommandText = insertText;
cmd.ExecuteNonQuery();
}
trans.Commit();
}
catch (Exception exe)
... {
trans.Rollback();
this.Label1.Text ="导入失败:请确定格式是否正确,以下是错误的详细信息: <br>"+exe.ToString();
}
finally
... {
conn.Close();
}
// Response.Write(Request.Form["filePath"]+" <br>");
string oleconnstring = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + Request.Form[ " filePath " ] + " ;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1' " ;
string queryText = " SELECT * FROM [Sheet1$] " ;
OleDbConnection oleconn = new OleDbConnection(oleconnstring);
OleDbDataAdapter Dr1 = new OleDbDataAdapter(queryText,oleconn);
DataSet Ds1 = new DataSet();
try
... {
oleconn.Open();
Dr1.Fill(Ds1);
}
catch (Exception ex)
... {
Response.Write("错误:"+ex.Message);
}
finally
... {
oleconn.Close();
}
// 把DataSet中的数据插入数据库
try
... {
SqlConnection conn = new SqlConnection(connstring);
conn.Open();
SqlTransaction trans = conn.BeginTransaction();
//插入数据
string insertText="";
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.Transaction = trans;
for(int i=0;i <Ds1.Tables[0].Rows.Count;i++)
...{
insertText = "insert into t_relcorp_employee(aa,bb) values('"+Ds1.Tables[0].Rows[i][0]+"','"+Ds1.Tables[0].Rows[i][1]+"')";
cmd.CommandText = insertText;
cmd.ExecuteNonQuery();
}
trans.Commit();
}
catch (Exception exe)
... {
trans.Rollback();
this.Label1.Text ="导入失败:请确定格式是否正确,以下是错误的详细信息: <br>"+exe.ToString();
}
finally
... {
conn.Close();
}