xls表格导入数据库功能实例代码
代码如下:
protected void Button1_Click(object sender, EventArgs e) { if (FileUpload1.HasFile == false) { Page.ClientScript.RegisterStartupScript(this.GetType(), "shifou", "<script>alert('请您选择 Excel文件')</script> "); return; } string Name = FileUpload1.PostedFile.FileName; //获取初始文件名 int j = Name.LastIndexOf("."); //取得文件名中最后一个"."的索引 string newext1 = Name.Substring(j); //获取文件扩展名 if (newext1 != ".xls" && newext1 != ".xlsx") { Page.ClientScript.RegisterStartupScript(this.GetType(), "wenjian", "<script>alert('只可以选择 Excel文件')</script>"); return;//当选择的不是Excel文件时,返回 } SqlConnection cn = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"]); cn.Open(); //savePath = Path.GetFullPath(FileUpload1.PostedFile.FileName);// FileUpload1.PostedFile.FileName.ToString(); if (FileUpload1.HasFile == false)//HasFile用来检查FileUpload是否有指定文件 { Page.ClientScript.RegisterStartupScript(this.GetType(), "wenjian", "<script>alert('Excel路径有 问题')</script>"); return;//当无文件时,返回 } filename = FileUpload1.FileName; //Path.GetFileNameWithoutExtension (FileUpload1.PostedFile.FileName); savePath = Server.MapPath(("../../UploadFiles/xls/") + filename); FileUpload1.SaveAs(savePath); //保存XLS文件 DataSet ds = ExecleDs(savePath, filename); //调用自定义方法 DataRow[] dr = ds.Tables[0].Select(); //定义一个DataRow数组 int rowsnum = ds.Tables[0].Rows.Count; if (rowsnum == 0) { //当Excel表为空时,对用户进行提示 Page.ClientScript.RegisterStartupScript(this.GetType(), "kong", "<script>alert('Excel表为空表, 无数据!')</script>"); } else { for (int i = 0; i < dr.Length; i++) { //自定义的model-student student.belong = ""; student.birthday = Convert.ToDateTime("1990-1-1"); student.classid = 1; student.id = 1; student.inschool = Convert.ToDateTime("1990-1-1"); student.phone = ""; student.sex = ""; student.type = ""; student.isaudite = "否"; student.roleid = Convert.ToInt32("22"); try { student.stuname = dr[i]["姓名"].ToString(); student.stuid = dr[i]["学号"].ToString(); student.username = dr[i]["姓名"].ToString(); student.userpassword = dr[i]["学号"].ToString(); } catch(Exception ex) { Response.Write("<script>alert('"+ex.Message+"')</script>"); return; } DataSet ds2 = studentbll.GetList("stuid='" + dr[i]["学号"].ToString() + "'"); if (ds2.Tables[0].Rows.Count == 0) { try { bool addstudent = Convert.ToBoolean(studentbll.Add(student)); if (addstudent) Page.ClientScript.RegisterStartupScript(this.GetType(), "daochu", "<script>alert('Excle表导入成功!')</script>"); } catch (MembershipCreateUserException ex) //捕捉异常 { Page.ClientScript.RegisterStartupScript(this.GetType(), "chongfu", "<script>alert('导入内容:" + ex.Message + "')</script> "); } } else { Page.ClientScript.RegisterStartupScript(this.GetType(), "chongfu", "<script>alert ('内容重复!禁止导入')</script> "); continue; } } } } public DataSet ExecleDs(string filenameurl, string table) { string strConn = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=" + filenameurl + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1;'";//这段字符串注意空格别写错否则会报错 OleDbConnection conn = new OleDbConnection(strConn); OleDbDataAdapter odda = new OleDbDataAdapter("select * from [Sheet18$]", conn);//【sheet18$】是表格名 DataSet ds = new DataSet(); odda.Fill(ds, table); return ds; }
相关推荐
morexyoung 2019-12-10
秋草正离离 2019-04-16
yygy0 2010-06-08
Stephenzsky 2019-01-09
dangyang 2019-01-08
nashxiejun 2018-05-03
lifang0 2017-07-17
zhuquan0 2017-01-21
PythonBiglove 2019-04-18
cxiaole 2019-04-16
lispython 2019-02-22
wangtengphp 2015-12-29
Pythonandme 2019-01-07
LHpython 2018-10-23