从excel批量倒入数据库
private DataSet GetCollection()
{
DataSet ds=new DataSet();
string strCon,strCmm;
//strCon="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\\irms\\tmp\\irsbd.xls;Extended Properties=Excel 8.0;";
strCon="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+Server.MapPath("tmp\\irsbd.xls")+";Extended Properties=Excel 8.0;";
strCmm="select distinct * from [Sheet1$]";
OleDbConnection oleCnn=new OleDbConnection(strCon);
OleDbCommand oleCmm=new OleDbCommand(strCmm,oleCnn);
OleDbDataAdapter oleDa=new OleDbDataAdapter(oleCmm);
oleDa.Fill(ds,"irsbd");
return ds;
}
private void PutData(DataSet ds)
{
string strCon=Application["strCon"].ToString();
string strSql="select top 1 * from irsbd";
DataSet myDs=new DataSet();
SqlDataAdapter da=new SqlDataAdapter(strSql,strCon);
da.Fill(myDs,"irsbd");
for(int i=0;i<ds.Tables[0].Rows.Count;i++)
if(ds.Tables[0].Rows[i]["sbdno"].ToString().Trim()!="")
{
DataRow dr=myDs.Tables[0].NewRow();
DataRow dr1=ds.Tables[0].Rows[i];
dr["sbdno"]=dr1["sbdno"];
dr["sbdnm"]=dr1["sbdnm"];
dr["sbdpd"]=dr1["sbdno"];
dr["sbdit"]=dr1["sbdit"];
dr["sbddt"]=dr1["sbddt"];
dr["sbdco"]=tbCo.Text.Trim();
dr["sbdel"]=DateTime.Today;
dr["sbdcs"]=0;
dr["sbdas"]=0;
dr["sbdps"]=0;
dr["sbdcs1"]=0;
dr["sbdcs2"]=0;
dr["sbdcs3"]=0;
dr["sbdcs4"]=0;
dr["sbdas1"]=0;
dr["sbdas2"]=0;
dr["sbdas3"]=0;
dr["sbdas4"]=0;
myDs.Tables[0].Rows.Add(dr);
}
SqlCommandBuilder sqlCb=new SqlCommandBuilder(da);
da.Update(myDs,"irsbd");
myDs.AcceptChanges();
}
{
DataSet ds=new DataSet();
string strCon,strCmm;
//strCon="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\\irms\\tmp\\irsbd.xls;Extended Properties=Excel 8.0;";
strCon="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+Server.MapPath("tmp\\irsbd.xls")+";Extended Properties=Excel 8.0;";
strCmm="select distinct * from [Sheet1$]";
OleDbConnection oleCnn=new OleDbConnection(strCon);
OleDbCommand oleCmm=new OleDbCommand(strCmm,oleCnn);
OleDbDataAdapter oleDa=new OleDbDataAdapter(oleCmm);
oleDa.Fill(ds,"irsbd");
return ds;
}
private void PutData(DataSet ds)
{
string strCon=Application["strCon"].ToString();
string strSql="select top 1 * from irsbd";
DataSet myDs=new DataSet();
SqlDataAdapter da=new SqlDataAdapter(strSql,strCon);
da.Fill(myDs,"irsbd");
for(int i=0;i<ds.Tables[0].Rows.Count;i++)
if(ds.Tables[0].Rows[i]["sbdno"].ToString().Trim()!="")
{
DataRow dr=myDs.Tables[0].NewRow();
DataRow dr1=ds.Tables[0].Rows[i];
dr["sbdno"]=dr1["sbdno"];
dr["sbdnm"]=dr1["sbdnm"];
dr["sbdpd"]=dr1["sbdno"];
dr["sbdit"]=dr1["sbdit"];
dr["sbddt"]=dr1["sbddt"];
dr["sbdco"]=tbCo.Text.Trim();
dr["sbdel"]=DateTime.Today;
dr["sbdcs"]=0;
dr["sbdas"]=0;
dr["sbdps"]=0;
dr["sbdcs1"]=0;
dr["sbdcs2"]=0;
dr["sbdcs3"]=0;
dr["sbdcs4"]=0;
dr["sbdas1"]=0;
dr["sbdas2"]=0;
dr["sbdas3"]=0;
dr["sbdas4"]=0;
myDs.Tables[0].Rows.Add(dr);
}
SqlCommandBuilder sqlCb=new SqlCommandBuilder(da);
da.Update(myDs,"irsbd");
myDs.AcceptChanges();
}