.net excel导入mysql_asp.net 中excel 导入数据库

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版本，连接字符串则应该变成