Excel 数据导入（OleDb）
1 @using (Html.BeginForm("Student", "Excel", FormMethod.Post, new { enctype = "multipart/form-data" })) 2 { 3 4 <div style="margin-top: 20px;"> 5 <fieldset id="myfieldset1"> 6 <legend>学生信息导入</legend> 7 <p> 8 选择文件：<input id="FileUpload1" type="file" name="files" style="width: 250px; height: 24px; 9 background: White" class="easyui-validatebox" /></p> 10 <p> 11 <input id="btnImport1" type="submit" value="导入" style="width: 60px; height: 28px;" /></p> 12 <p style="color: Red; text-align: center;">@ViewBag.errorstu</p> 13 </fieldset> 14 </div> 15 }
1 public class ExcelController : Controller 2 { 3 ContractInfoBLL contract; 4 TeacherInfoBLL TIBLL; 5 ParentBLL PBLL; 6 public ExcelController() 7 { 8 if (contract == null) 9 { 10 contract = new ContractInfoBLL(); 11 } 12 if (TIBLL == null) 13 { 14 TIBLL = new TeacherInfoBLL(); 15 } 16 if (PBLL == null) 17 { 18 PBLL = new ParentBLL(); 19 } 20 } 21 public ActionResult Index(bool Code = true, string Message="") 22 { 23 if (!Code) 24 { 25 ViewBag.Message = Message; 26 } 27 return View(); 28 } 29 string StuExcel = ApiHelper.GetWebConfig("StuExcel", @"E:/需求/20160520 家宝贝/新建文件夹/学生用户.xlsx"); 30 /// <summary> 31 /// Excel学生用户导入 32 /// </summary> 33 /// <returns></returns> 34 [HttpPost] 35 public ActionResult Student(HttpPostedFileBase filebase) 36 { 37 #region 38 HttpPostedFileBase file = Request.Files["files"]; 39 string FileName; 40 string savePath; 41 if (file == null || file.ContentLength <= 0) 42 { 43 ViewBag.error = "文件不能为空"; 44 return View(); 45 } 46 else 47 { 48 string filename = Path.GetFileName(file.FileName); 49 int filesize = file.ContentLength;//获取上传文件的大小单位为字节byte 50 string fileEx = System.IO.Path.GetExtension(filename);//获取上传文件的扩展名 51 string NoFileName = System.IO.Path.GetFileNameWithoutExtension(filename);//获取无扩展名的文件名 52 int Maxsize = 4000 * 1024;//定义上传文件的最大空间大小为4M 53 string FileType = ".xls,.xlsx";//定义上传文件的类型字符串 54 55 FileName = NoFileName + DateTime.Now.ToString("yyyyMMddhhmmss") + fileEx; 56 if (!FileType.Contains(fileEx)) 57 { 58 ViewBag.error = "文件类型不对，只能导入xls和xlsx格式的文件"; 59 return View(); 60 } 61 if (filesize >= Maxsize) 62 { 63 ViewBag.error = "上传文件超过4M，不能上传"; 64 return View(); 65 } 66 string path = AppDomain.CurrentDomain.BaseDirectory + "App_Data/"; 67 savePath = Path.Combine(path, FileName); 68 file.SaveAs(savePath); 69 } 70 #endregion 71 List<Student_Excel> stuList = new ExcelBLL().GetStudentByExcel(savePath); 72 string Meg = ""; 73 int ItemSchool = 0; 74 int classID = 0; 75 int i = 0; 76 foreach (var item in stuList) 77 { 78 79 bool ishavecode = contract.IsHaveContractCode(item.ContractCode); 80 if (!ishavecode) 81 { 82 i++; 83 Meg = "学籍号重复"; 84 LoggerFactory.Instance.Logger_Info("学生用户_序号" + item.Number + "_学籍号：" + item.ContractCode + "_学生姓名：" + item.Name + "_原因：" + Meg); 85 } 86 else 87 { 88 ItemSchool = contract.GetGardenByName(item.ItemSchoolName); 89 classID = contract.GetClassInfoID(ItemSchool, item.ClassName); 90 if (classID != 0) 91 { 92 DateTime da = DateTime.Now; 93 ContractInfoList model = new ContractInfoList(); 94 model.ContractCode = item.ContractCode; 95 model.ContractStatus = (int)ContractStatus.Normal; 96 model.AdmissionsTeacherID = contract.GetTeacherIDbyName(item.TeacherName); 97 model.StudentName = item.Name; 98 model.StudentGender = item.Sex == "男" ? 0 : 1; 99 model.ParentName = item.ParentName; 100 model.ParenRelation = item.ParenRelation; 101 model.ParentTel = item.Phone; 102 model.EntranceTime = item.EntranceTime.HasValue ? item.EntranceTime.Value : DateTime.Now; 103 model.PaymentAmount = item.PaymentAmount != "" ? Convert.ToInt32(item.PaymentAmount) : 0; 104 model.ItemSchool = ItemSchool; 105 model.AddTime = da; 106 model.UpdateTime = da; 107 model.Remarks = ""; 108 model.ClassInfoID = classID; 109 110 bool boo = contract.IsCreateContract(model); 111 if (!boo) 112 { 113 i++; 114 Meg = "创建失败"; 115 LoggerFactory.Instance.Logger_Info("学生用户_序号" + item.Number + "_学籍号：" + item.ContractCode + "_学生姓名：" + item.Name + "_原因：" + Meg); 116 } 117 } 118 else 119 { 120 i++; 121 Meg = "班级不存在"; 122 LoggerFactory.Instance.Logger_Info("学生用户_序号" + item.Number + "_学籍号：" + item.ContractCode + "_学生姓名：" + item.Name + "_原因：" + Meg); 123 } 124 } 125 } 126 127 return Json(new { Code = Meg == "" ? true : false, Message = Meg != "" ? "未导入数据：" + i + "条" : "" }); 128 //ViewBag.errorstu = "导入成功"; 129 //return RedirectToAction("Index"); 130 } 131 132 }
1 public class ExcelBLL 2 { 3 public List<Student_Excel> GetStudentByExcel(string path) 4 { 5 List<Student_Excel> stuList = new List<Student_Excel>(); 6 DataSet ds = Helper.GetDataSet("select * from [Sheet1$]", path); 7 DataTable dt = ds.Tables[0];//忽略第一行表名，从第二行开始 8 foreach (DataRow row in dt.Rows) 9 { 10 if (row["序号"].ToString()!="") 11 { 12 DateTime ad = new DateTime(); 13 if (row["入学日期"].ToString() != "") 14 { 15 ad = Convert.ToDateTime(row["入学日期"]); 16 } 17 stuList.Add(new Student_Excel() 18 { 19 Number = row["序号"].ToString(), 20 ContractCode = row["学籍号"].ToString(), 21 Name = row["学生姓名"].ToString(), 22 Sex = row["性别"].ToString(), 23 ClassName = row["所在班级"].ToString(), 24 Phone = row["紧急联系电话"].ToString(), 25 ParenRelation = row["与孩子关系"].ToString(), 26 ParentName = row["家长姓名"].ToString(), 27 EntranceTime = ad,//Convert.ToDateTime(row["入学日期"]), 28 TeacherName = row["招生老师"].ToString(), 29 PaymentAmount = row["收费金额"].ToString(), 30 ItemSchoolName = row["所在园区"].ToString(), 31 }); 32 } 33 } 34 35 return stuList; 36 } 37 }
1 public class Helper:BaseBLL 2 { 3 #region Excel 导入 4 private static string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0; HDR=yes; IMEX=2'"; 5 6 public static DataSet GetDataSet(string sql, string path) 7 { 8 OleDbConnection conn = new OleDbConnection(string.Format(connstring, path)); 9 OleDbCommand cmd = new OleDbCommand(sql, conn); 10 OleDbDataAdapter da = new OleDbDataAdapter(cmd); 11 DataSet ds = new DataSet();//创建内存数据集 12 try 13 { 14 conn.Open(); 15 da.Fill(ds); 16 return ds; 17 } 18 catch (Exception e) 19 { 20 //写日志 21 22 throw e; 23 } 24 finally 25 { 26 conn.Close(); 27 } 28 } 29 #endregion 30 }
