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格式不正确";
}
}
