public void add(StudentPointModel studentpoint) { SqlConnection cn = null; SqlTransaction tc = null; try { cn = DbHelperSQL.getConnection(); tc = DbHelperSQL.startTransaction(cn); //执行新增写进数据库 studentpointDal.add(cn, tc, studentpoint); DbHelperSQL.commitTransaction(tc); } catch (Exception dalEx) { DbHelperSQL.rollBackTransaction(tc); throw new MakeException(ExpSort.数据库, dalEx.Message); } finally { DbHelperSQL.closeConnection(cn); } }
private void SetCellToStudentPoint(StudentPointModel studentPoint, IRow row, Dictionary<string, int> rowName) { var kemu1 = GetCellSafe(row, rowName, "语文"); if (kemu1 >= 0) studentPoint.YuWen = kemu1; var kemu2 = GetCellSafe(row, rowName, "数学"); if (kemu2 >= 0) studentPoint.ShuXue = kemu2; var kemu3 = GetCellSafe(row, rowName, "英语"); if (kemu3 >= 0) studentPoint.YingYu = kemu3; var kemu4 = GetCellSafe(row, rowName, "政治"); if (kemu4 >= 0) studentPoint.ZhengZhi = kemu4; var kemu5 = GetCellSafe(row, rowName, "物理"); if (kemu5 >= 0) studentPoint.WuLi = kemu5; var kemu6 = GetCellSafe(row, rowName, "化学"); if (kemu6 >= 0) studentPoint.HuaXue = kemu6; var kemu7 = GetCellSafe(row, rowName, "地理"); if (kemu7 >= 0) studentPoint.DiLi = kemu7; var kemu8 = GetCellSafe(row, rowName, "历史"); if (kemu8 >= 0) studentPoint.LiShi = kemu8; var kemu9 = GetCellSafe(row, rowName, "生物"); if (kemu9 >= 0) studentPoint.ShengWu = kemu9; var kemu10 = GetCellSafe(row, rowName, "美术"); if (kemu10 >= 0) studentPoint.MeiShu = kemu10; var kemu11 = GetCellSafe(row, rowName, "音乐"); if (kemu11 >= 0) studentPoint.YinYue = kemu11; var kemu12 = GetCellSafe(row, rowName, "体育"); if (kemu12 >= 0) studentPoint.TiYu = kemu12; var kemu13 = GetCellSafe(row, rowName, "德育考核"); if (kemu13 >= 0) studentPoint.DeYuKaoKe = kemu13; var kemu14 = GetCellSafe(row, rowName, "劳动技术"); if (kemu14 >= 0) studentPoint.NaoDongJiShu = kemu14; var kemu15 = GetCellSafe(row, rowName, "计算机"); if (kemu15 >= 0) studentPoint.JiSuanJi = kemu15; var kemu16 = GetCellSafe(row, rowName, "物理实验"); if (kemu16 >= 0) studentPoint.WuLiShiYan = kemu16; var kemu17 = GetCellSafe(row, rowName, "化学实验"); if (kemu17 >= 0) studentPoint.HuaXueShiYan = kemu17; var kemu18 = GetCellSafe(row, rowName, "生物实验"); if (kemu18 >= 0) studentPoint.ShengWuShiYan = kemu18; var kemu19 = GetCellSafe(row, rowName, "通用技术"); if (kemu19 >= 0) studentPoint.TongYongJiShu = kemu19; var kemu20 = GetCellSafe(row, rowName, "综合实践"); if (kemu20 >= 0) studentPoint.ZhongHeShiJian = kemu20; var kemu21 = GetCellSafe(row, rowName, "信息技术"); if (kemu21 >= 0) studentPoint.XinXiJiShu = kemu21; var kemu22 = GetCellSafe(row, rowName, "第二外语"); if (kemu22 >= 0) studentPoint.DiErWaiYu = kemu22; var kemu23 = GetCellSafe(row, rowName, "班级排名"); if (kemu23 >= 0) studentPoint.BanJiPaiMing = kemu23; var kemu24 = GetCellSafe(row, rowName, "年级排名"); if (kemu24 >= 0) studentPoint.NianJiPaiMing = kemu24; }
private string ImportStudentPointExcel(HttpPostedFile postedFile, string schoolNo, string banjiName, string examTime, string examType, string examName, List<StudentPointModel> newstudentPoints) { try { var ext = Path.GetExtension(postedFile.FileName); ISheet sheet; if (ext == ".xls") { //office 97-2003 HSSFWorkbook wk = new HSSFWorkbook(postedFile.InputStream); sheet = wk.GetSheet(wk.GetSheetName(0)); } else if (ext == ".xlsx") { //office 2007-now XSSFWorkbook wk = new XSSFWorkbook(postedFile.InputStream); sheet = wk.GetSheet(wk.GetSheetName(0)); } else { return "请使用xls或xlsx格式"; } if (sheet.LastRowNum < 1) { return "表内容不能为空"; } Dictionary<string, int> rowName = new Dictionary<string, int>(); IList<QueryModel> qmList = new List<QueryModel>(); qmList.Add(MakeUtil.getQueryModel("SchoolNo", "'", SqlWhere.WhereOperator.Equal, schoolNo)); if (banjiName != "") { qmList.Add(MakeUtil.getQueryModel("D_Name", "'", SqlWhere.WhereOperator.Equal, banjiName)); } var students = _departStaffEbi.getModelList(qmList, -1); qmList.Clear(); qmList.Add(MakeUtil.getQueryModel("SchoolNo", "'", SqlWhere.WhereOperator.Equal, schoolNo)); if (banjiName != "") { qmList.Add(MakeUtil.getQueryModel("BanjiName", "'", SqlWhere.WhereOperator.Equal, banjiName)); } qmList.Add(MakeUtil.getQueryModel("ExamType", "'", SqlWhere.WhereOperator.Equal, examType)); qmList.Add(MakeUtil.getQueryModel("ExamName", "'", SqlWhere.WhereOperator.Equal, examName)); var studentpoints = _studentPointEbi.getModelList(qmList, -1); // if (sheet.LastRowNum > students.Count) // { // return "Excel的导入内容比学生人数多"; // } for (int j = 0; j <= sheet.LastRowNum; j++) //LastRowNum 是当前表的总行数 { IRow row = sheet.GetRow(j); //读取当前行数据 //先读取表头信息 if (j == 0) { if (row != null) { for (int k = 0; k <= row.LastCellNum; k++) //LastCellNum 是当前行的总列数 { ICell cell = row.GetCell(k); //当前表格 if (cell != null && cell.ToString() != "") { //获取表头信息 rowName.Add(cell.ToString(), k); } } } } else { if (row != null) { var banji = row.GetCell(rowName["班级名称"]).ToString(); var xinming = row.GetCell(rowName["姓名"]).ToString(); var bianhao = row.GetCell(rowName["编号"]).ToString(); if (banjiName != "" && banji != banjiName) { return string.Format("Excel中的班级名称有误,你选择了 {0} 这个班级,Excel中不应该再出现别的班级", banjiName); } //根据前四项找出学生的成绩资料 if (!students.Any(p => p.D_Name == banji && p.DS_Name == xinming && p.DS_JID == bianhao)) { return string.Format("在班级{2}中找不到姓名为{0}编号为{1}的学生", xinming, bianhao, banji); } //然后在excel表中如果有相关资料的话便导入 var studentpointList = studentpoints.Where(p => p.BanjiName == banji && p.XinMing == xinming && p.BianHao == bianhao); if (studentpointList.Any()) { var studentpoint = studentpointList.FirstOrDefault(); if (studentpoint.TempIsOld) { continue; } SetCellToStudentPoint(studentpoint, row, rowName); studentpoint.TempIsOld = true; newstudentPoints.Add(studentpoint); } else { var studentpoint = new StudentPointModel(); studentpoint.SchoolNo = schoolNo; studentpoint.BanjiName = banji; studentpoint.XinMing = xinming; studentpoint.BianHao = bianhao; studentpoint.ExamType = examType; studentpoint.ExamName = examName; studentpoint.ExamTime = Convert.ToDateTime(examTime); SetCellToStudentPoint(studentpoint, row, rowName); newstudentPoints.Add(studentpoint); } } } } if (newstudentPoints.Any()) { _studentPointEbi.add(newstudentPoints); } return "OK"; } catch (Exception ex) { return "Excel格式不正确," + ex.Message; } catch { return "Excel格式不正确"; } }