【下载文档:  Asp.net中把Excel数据存储至SQL_Server中具体实现方法.txt 】

---

Asp.net中把Excel数据存储至SQL Server中的具体实现方法
ExcelWrapper复制代码 代码如下: ///

/// 查询EXCEL电子表格添加到DATASET ///

/// 文件路径 /// dataset中的表名(并不是要和数据库中的表一样) /// public static 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); conn.Open(); DataSet ds = new DataSet(); OleDbDataAdapter odda = new OleDbDataAdapter("select * from [Sheet1$]", conn); odda.Fill(ds, table); return ds; }
.cs复制代码 代码如下: // 提交按钮 protected void imgbtnSubmit_Click(object sender, ImageClickEventArgs e) { try { if (!FileUpload1.HasFile) { JsHelper.Alert("请您选择Excel文件", this); return; }
// 取得文件后缀名 string extension = System.IO.Path.GetExtension(FileUpload1.FileName).ToString().ToLower(); if (extension != ".xls" && extension != ".xlsx") { JsHelper.Alert("只可以选择Excel文件", this); return; }
// 构造Exel存在服务器相对路径的文件名，并SaveAs 将上传的文件内容保存在服务器上 string filename = DateTime.Now.ToString("yyyymmddhhMMss") + FileUpload1.FileName; string savePath = Server.MapPath(("~\\upfiles\\") + filename); FileUpload1.SaveAs(savePath);
DataSet ds = ExcelWrapper.ExecleDs(savePath, filename); DataRow[] dr = ds.Tables[0].Select(); int rowsnum = ds.Tables[0].Rows.Count; List lstMsg = new List(); if (rowsnum == 0) { JsHelper.Alert("Excel表为空表,无数据", this); } else { for (int i = 0; i < dr.Length; i++) { String error = "";
// excel列名不能变 string num = dr[i]["学号"].ToString(); string name = dr[i]["姓名"].ToString(); string pwd = dr[i]["密码"].ToString(); string collegeNum = dr[i]["学院编号"].ToString(); string birth = dr[i]["生日"].ToString();
if (!BLL.M_CollegeBLL.GetAllCollegeNum().Contains(collegeNum)) { error += "所属学院不存 "; }
if (String.IsNullOrEmpty(collegeNum)) { error += "请选择该学生所在院系 "; }
if (String.IsNullOrEmpty(num)) { error += "学号不能为空 "; } else if (!Utility.IsLetterThanSomeLength(num, 25)) { error += "学号的长度过长 "; }
if (String.IsNullOrEmpty(name)) { error += "姓名不能为空 "; } else if (!Utility.IsLetterThanSomeLength(name, 25)) { error += "姓名的长度过长 "; }
if (String.IsNullOrEmpty(birth)) { error += "出生日期不能为空 "; } else if (!Utility.IsDateTime(birth)) { error += "出生日期格式不正确 "; } if (String.IsNullOrEmpty(sex)) { error += "性别不能为空 "; } if (String.IsNullOrEmpty(error)) { M_Student stu = new M_Student(); stu.Num = num; stu.Name = name; stu.Pwd = pwd; stu.CollegeNum = collegeNum; stu.Birthday = Convert.ToDateTime(birth);
// 该学号不存在 if (!BLL.M_StudentBLL.GetAllStuNum().Contains(num)) { BLL.M_StudentBLL.Add(stu); } else { BLL.M_StudentBLL.Modify(stu); } } else { lstMsg.Add("学号为" + num + "未导入成功，" + "原因:" + error + "。"); } } } this.lblHint.Text = "导入完成。"; if (null != lstMsg) { this.lblHint.Text += "共有" + lstMsg.Count() + "条记录未成功。

"; foreach (string s in lstMsg) { this.lblHint.Text += s; } } } catch { this.lblHint.Text = "程序出错，请您检查需要导入的表！"; } }
效果图