public ActionResult UploadScore(IEnumerable <HttpPostedFileBase> files, int id) { if (files != null) { foreach (var file in files) { string fileExtentian = file.FileName.Substring(file.FileName.LastIndexOf(".")).ToLower(); if (fileExtentian == ".xls" || fileExtentian == ".xlsx") { string newFileName = DateTime.Now.ToString("yyyyMMddhhmmss") + fileExtentian; string path = Server.MapPath("~/Content/UploadFiles/Score_" + newFileName); file.SaveAs(path); var course = db.Courses.SingleOrDefault(c => c.Id == id); if (course != null) { //从Excel中导入学生考核课程的成绩,同一学生同一课程的成绩可以重复导入,后导入的覆盖先导入的。 ImportReturnModel b = ImportScoresFromExcel(path, course); if (b.Code == 1) { return(Json(new { code = "101", msg = "成绩数据导入成功。" })); } else { string others = "未找到对应信息,无法导入其成绩。\n"; foreach (string s in b.Others) { others += $"[{s}]"; } return(Json(new { code = "102", msg = "导入遇到一些问题。", data = others })); } } else { return(Json(new { code = "100", msg = $"未找到相应课程" })); } } } } return(Json(new { code = "100", msg = "成绩数据上传失败" })); }
/// <summary> /// 从上传的学生成绩Excel中导入学生的指定课程成绩信息 /// </summary> /// <param name="path"></param> /// <param name="cid">课程编号</param> /// <returns></returns> private ImportReturnModel ImportScoresFromExcel(string path, Course course) { //int result = 0;//0:失败 var result = new ImportReturnModel { Code = 0, Others = new List <string>() }; string connectionString = "Provider=Microsoft.Jet.OleDb.4.0; Data Source=" + path + "; Extended Properties=Excel 8.0;"; using (OleDbConnection Connection = new OleDbConnection(connectionString)) { DataTable dt = new DataTable(); Connection.Open(); using (OleDbCommand command = new OleDbCommand()) { command.Connection = Connection; command.CommandText = "SELECT * FROM [成绩表$]"; OleDbDataAdapter adapter = new OleDbDataAdapter(command); adapter.Fill(dt); foreach (DataRow dr in dt.Rows) { string number = dr["学号"].ToString().Trim(); string studentName = dr["姓名"].ToString().Trim(); //string courseName = dr["课程"].ToString().Trim(); float.TryParse(dr["成绩"].ToString().Trim(), out float scoreValue); //判断导入的学生成绩是否有对应学生信息。 var student = db.Students.SingleOrDefault(stu => stu.Number == number); if (student == null) { result.Others.Add(number + studentName); //如果没有,则收集学生学号和姓名。 continue; } //查找该学生该门课程成绩是否存在,如果存在则更新数据,不存在则新增数据。 var score = db.Scores.SingleOrDefault(ss => ss.Student.Number == number && ss.Course.CourseName == course.CourseName); if (score == null) { Score s = new Score { Student = student, Course = course, Value = scoreValue }; db.Scores.Add(s); } else { score.Value = scoreValue; db.Entry <Score>(score).State = System.Data.Entity.EntityState.Modified; } } adapter.Dispose(); int i = db.SaveChanges(); if (i > 0) { result.Code = 1; //1:成功 } } } return(result); }