protected void btnImportGradeCheckExcelData_Click(object sender, EventArgs e) { string _folder = Server.MapPath(ConfigurationManager.AppSettings["GradeCheckImportExcel"]); if (FileUpload1.HasFile) { if (!Directory.Exists(_folder)) { Directory.CreateDirectory(_folder); } if (!(UploadFiles.GetExtension((FileUpload1.FileName)).ToLower().Trim() == ".xls" || UploadFiles.GetExtension((FileUpload1.FileName)).ToLower().Trim() == ".xlsx")) { Javascript.GoHistory(-1, "上传文件类型有误,请上传Excel文件:)", Page); return; } string _path = _folder + "ImportGradeCheckExcelData" + UploadFiles.DateTimeString() + ".xls"; FileUpload1.SaveAs(_path); string _termYear = ddlTermYearImportExcelData.SelectedValue; //读取要导入的成绩审核Excel文件的全部数据 GradeCheckExcelData gradeCheckExcelData = BllOperationAboutExcel.BllImportGradeCheckExcelData(_path, _termYear); //Response.End(); DalOperationAboutGradeCheck dal = new DalOperationAboutGradeCheck(); for (int i = 0; i < gradeCheckExcelData.listStudentsGradeCheckDetail.Count;i++ ) { DataTable dt = dal.GetGradeCheckItemsByGradeCheckItemName(gradeCheckExcelData.listStudentsGradeCheckDetail[i].gradeCheckItemName, ddlTermYearImportExcelData.SelectedValue).Tables[0]; //Response.Write(_studentsGradeCheckDetail.gradeCheckItemName+"<br/>"); if (dt.Rows.Count > 0) { //_studentsGradeCheckDetail.gradeCheckItemName = dt.Rows[0]["gradeCheckItemName"].ToString().Trim(); //Response.Write(dt.Rows[0]["gradeCheckItemName"].ToString().Trim()+":"+dt.Rows[0]["gradeCheckId"].ToString().Trim()+"<Br/>"); gradeCheckExcelData.listStudentsGradeCheckDetail[i].gradeCheckId = int.Parse(dt.Rows[0]["gradeCheckId"].ToString().Trim()); //_studentsGradeCheckDetail.gradeCheckItemDefaultValue = dt.Rows[0]["gradeCheckItemDefaultValue"].ToString().Trim(); } else { Javascript.GoHistory(-1, "成绩审核Excel文件有数据问题,\\n未找到" + ddlTermYearImportExcelData.SelectedItem.Text + "第" + gradeCheckExcelData.listStudentsGradeCheckDetail[i].colNo + "列审核规则\\n请检查此列名是否存在或者与教学辅助系统中的列名完全一致(包括空格标点等)!", Page); return; } } //Response.Write("ssss"); //Response.End(); //检测是否有不符合选择的学年及培养地要求的记录,只要有一条不符合,则不导入 for (int i = 0; i < gradeCheckExcelData.listStudentsGradeCheckDetail.Count; i++) { DataSet _checkdata = dal.CheckDataConsistenceByStudentNoTermYearLocale(gradeCheckExcelData.listStudentsGradeCheckDetail[i].studentNo, ddlTermYearImportExcelData.SelectedValue, ddlLocaleImportExcelData.SelectedValue); if (_checkdata.Tables[0].Rows.Count == 0) { Javascript.GoHistory(-1, "成绩审核Excel文件有数据问题,\\n学号为" + gradeCheckExcelData.listStudentsGradeCheckDetail[i].studentNo + "的学生数据记录不符合所选择的学年及培养地要求\\n请检查!", Page); return; } } //Response.Write(gradeCheckExcelData.listStudentsGradeCheckConfirm.Count + "<br/>"); string errorStudentNo = string.Empty; //将获取的导入数据存入数据库,事务控制 using (TransactionScope scope = new TransactionScope()) { try { //首先删除指定学年和培养地的成绩审核数据 dal.DeleteStudentsGradeCheckConfirmItemsByTermYear(_termYear, ddlLocaleImportExcelData.SelectedValue.Trim()); dal.DeleteStudentsGradeCheckDetailItemsByTermYear(_termYear, ddlLocaleImportExcelData.SelectedValue.Trim()); //接下来遍历获取到的导入数据,并写入数据库,还需要加一个学年和培养地验证判断(已添加) //首先导入usta_StudentsGradeCheckConfirm表数据 foreach (StudentsGradeCheckConfirm _studentsGradeCheckConfirm in gradeCheckExcelData.listStudentsGradeCheckConfirm) { //Response.Write(_studentsGradeCheckConfirm.studentNo + "<br/>"); errorStudentNo = _studentsGradeCheckConfirm.studentNo; dal.AddStudentGradeCheckConfirm(_studentsGradeCheckConfirm); } //其次导入usta_StudentsGradeCheckDetail表数据 foreach (StudentsGradeCheckDetail _studentsGradeCheckDetail in gradeCheckExcelData.listStudentsGradeCheckDetail) { //if (_studentsGradeCheckDetail.colNo == 8) //{ // Response.Write(_studentsGradeCheckDetail.gradeCheckId + "_" + _studentsGradeCheckDetail.gradeCheckDetailValue+"<Br/>"); //} errorStudentNo = _studentsGradeCheckDetail.studentNo; dal.AddGradeCheckDetailByStudentNo(_studentsGradeCheckDetail); } scope.Complete(); Javascript.AlertAndRedirect("导入成绩审核数据成功,点击确定查看:)", "/Administrator/StudentManager.aspx?fragment=1", Page); } catch (System.Exception ex) { MongoDBLog.LogRecord(ex); Javascript.GoHistory(-1, "导入成绩审核数据失败:(\\n学号为:" + errorStudentNo + "的学生数据不符合导入格式要\\n请检查!", Page); } } } else { Javascript.GoHistory(-1, "请先选择Excel文件!", Page); return; } }