protected void Button1_Click(objectsender, EventArgs e)
{
SqlConnection conn= new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["LYConnectionString"].ConnectionString); ;//链接数据库
conn.Open();try{string fileurl = typename(FileUpload1);//调用typename方法取得excel文件路径
DataSet ds = new DataSet();//取得数据集
ds =xsldata(fileurl);int errorcount = 0;//记录错误信息条数
int insertcount = 0;//记录插入成功条数
int updatecount = 0;//记录更新信息条数
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{string stuid = ds.Tables[0].Rows[i][0].ToString();string stuname = ds.Tables[0].Rows[i][1].ToString();string stusex = ds.Tables[0].Rows[i][2].ToString();string zhuanye = ds.Tables[0].Rows[i][3].ToString();string classname = ds.Tables[0].Rows[i][4].ToString();
Response.Write(stuid);
Response.Write(stuname);
Response.Write(stusex);
Response.Write(zhuanye);
Response.Write(classname);if (stuid != "" && stuname != "" && stusex != "" && zhuanye != "" && classname != "")
{
SqlCommand selectcmd= new SqlCommand("select count(*) from stud", conn);int count =Convert.ToInt32(selectcmd.ExecuteScalar());if (count > 0)
{
SqlCommand selectcmd2= new SqlCommand("select count(*) from stud where stuid='" + stuid + "'", conn);int count2 =Convert.ToInt32(selectcmd2.ExecuteScalar());if (count2 > 0)
{
SqlCommand updatecmd= new SqlCommand("update stud set stuname='" + stuname + "',stusex='" + stusex + "',zhuanye='" + zhuanye + "',classname='" + classname + "' where stuid='" + stuid + "'", conn);
updatecmd.ExecuteNonQuery();
updatecount++;
}else{
SqlCommand insertcmd= new SqlCommand("insert into stud values('" + stuid + "','" + stuname + "','" + stusex + "','" + zhuanye + "','" + classname + "')", conn);
insertcmd.ExecuteNonQuery();
insertcount++;
}
}else{
SqlCommand insertcmd= new SqlCommand("insert into stud values('" + stuid + "','" + stuname + "','" + stusex + "','" + zhuanye + "','" + classname + "')", conn);
insertcmd.ExecuteNonQuery();//break;
}
}else{
errorcount++;
}
}
Response.Write("");
}//catch (Exception exp)//{//Response.Write("");//}
finally{
conn.Close();
}
}//判断上传文件,并保存文件
privateString typename(FileUpload fileloads)
{string fullfilename =fileloads.PostedFile.FileName;string filename = fullfilename.Substring(fullfilename.LastIndexOf("\\\\") + 1);string type = fullfilename.Substring(fullfilename.LastIndexOf(".") + 1);string murl = "";if (type == "xls")
{
fileloads.PostedFile.SaveAs(Server.MapPath("excel") + "\\\\" +filename);
murl= (Server.MapPath("excel") + "\\\\" +filename).ToString();
}else{
Response.Write("");
}returnmurl;
}//数据库导入数据集dataset
private DataSet xsldata(stringfilepath)
{string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties='Excel 8.0;IMEX=1'";
//如果是导入excel2013版本,连接字符串则应该变成