long startTime = System.currentTimeMillis(); //获取开始时间
UploadFile file = getFile("jhj.ZT");
String year = getPara("year", time.getYear());// 年份
// 下载文件临时存放地
String path = PathKit.getWebRootPath() + File.separator + "temp" + File.separator;
File f = new File(path);
if (!f.exists()) {
f.mkdirs();// 0
// 如果创建目录成功，方法返回true，否则该方法返回false。
}
// 错误信息存放名称
String rfile = time.getNowTime("yyyyMMddHHmmss").toString() + ".xls";
int success = 0;// 成功条数
int error = 0;// 失败条数
String errormsg = ""; // 失败信息
List<Grad> list = new ArrayList<Grad>();
try {
// 创建工作簿
WritableWorkbook book = Workbook.createWorkbook(new File(path + rfile));
WritableSheet sheet = book.createSheet("导入失败数据", 0);
/*
* 合并行和列mergeCells(a,b,c,d)a：单元格的列号；b：单元格的行号；
* c：从单元格[a,b]起，向下合并的列数；d：从单元格[a,b]起，向下合并的行数。
*/
// 行表头开始
// 表头单元格样式 垂直、水平居中 宋体,9号
WritableFont cfont = new WritableFont(WritableFont.createFont("宋体"), 9);
WritableCellFormat cell = new WritableCellFormat(cfont);
// 水平居中
cell.setAlignment(Alignment.CENTRE);
// 垂直居中
cell.setVerticalAlignment(VerticalAlignment.CENTRE);
cell.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
// 自动换行
cell.setWrap(true);
sheet.setColumnView(0, 15);
sheet.setColumnView(1, 15);
sheet.setColumnView(2, 20);
sheet.setColumnView(3, 15);
sheet.setColumnView(4, 15);
sheet.setColumnView(5, 15);
sheet.setColumnView(6, 25);
sheet.setColumnView(7, 15);
sheet.setColumnView(8, 15);
sheet.setColumnView(9, 20);
sheet.setColumnView(10, 15);
sheet.setColumnView(11, 15);
sheet.setColumnView(12, 15);
sheet.setColumnView(13, 25);
sheet.setColumnView(14, 15);
sheet.setColumnView(15, 15);
sheet.setColumnView(16, 20);
sheet.setColumnView(17, 15);
sheet.setColumnView(18, 15);
sheet.setColumnView(19, 15);
sheet.setColumnView(20, 25);
sheet.setColumnView(21, 15);
sheet.setColumnView(22, 15);
sheet.setColumnView(23, 20);
sheet.setColumnView(24, 15);
sheet.setColumnView(25, 15);
sheet.setColumnView(26, 15);
sheet.setColumnView(27, 15);
sheet.setColumnView(28, 15);
WritableFont mfont = new WritableFont(WritableFont.createFont("宋体"), 9);
mfont.setColour(Colour.RED);
WritableCellFormat mcell = new WritableCellFormat(mfont);
// 水平居中
mcell.setAlignment(Alignment.CENTRE);
// 垂直居中
mcell.setVerticalAlignment(VerticalAlignment.CENTRE);
mcell.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
// 自动换行
mcell.setWrap(true);
// 第一列
InputStream is = new FileInputStream(file.getFile());
Workbook rwb = Workbook.getWorkbook(is);
Sheet rst = rwb.getSheet(0);
int rsRows = rst.getRows();
// 遍历行
for (int j = 1; j < rsRows; j++) {
errormsg = ""; // 失败信息
boolean t = true;// 保存
String stuNum = rst.getCell(0, j).getContents().trim();// 考生号
String IdCard = rst.getCell(1, j).getContents().trim();// 身份证号码
// 不允许为空
String name = rst.getCell(2, j).getContents().trim();// 姓名
String sexCode = rst.getCell(3, j).getContents().trim();// 性别代码
String sexName = rst.getCell(4, j).getContents().trim();// 性别名称
String nationCode = rst.getCell(5, j).getContents().trim();// 民族代码
String nationName = rst.getCell(6, j).getContents().trim();// 民族名称
String policyCode = rst.getCell(7, j).getContents().trim();// 政治面貌代码
String policyName = rst.getCell(8, j).getContents().trim();// 政治面貌名称
String schoolCode = rst.getCell(9, j).getContents().trim();// 院校代码
String schoolName = rst.getCell(10, j).getContents().trim();// 院校名称
String degreeCode = rst.getCell(11, j).getContents().trim();// 学历代码
String degreeName = rst.getCell(12, j).getContents().trim();// 学历名称
String majorCode = rst.getCell(13, j).getContents().trim();// 专业代码
String majorName = rst.getCell(14, j).getContents().trim();// 专业名称
String trainCode = rst.getCell(15, j).getContents().trim();// 培养方式代码
String trainName = rst.getCell(16, j).getContents().trim();// 培养方式名称
String collegeCode = rst.getCell(17, j).getContents().trim();// 所在院系代码
// 不允许为空
String collegeName = rst.getCell(18, j).getContents().trim();// 所在院系名称
// 不允许为空
String className = rst.getCell(19, j).getContents().trim();// 所在班级代码
// 不允许为空
String fd = rst.getCell(20, j).getContents().trim();// 班级辅导员
// 不允许为空
String stuNumber = rst.getCell(21, j).getContents().trim();// 学号
// 不允许为空
// 验证不为空的字段
if (StrKit.notBlank(IdCard) && StrKit.notBlank(className) && StrKit.notBlank(fd) && StrKit.notBlank(collegeCode)
&& StrKit.notBlank(collegeName) && StrKit.notBlank(stuNumber)) {
if (!(stuNum.getBytes().length < bysService.getColumnLength("GRAD", "EXAM_NUM").getBigDecimal("DATALENGTH").intValue())) {
errormsg = "/考生号 过长";
t = false;
}
if (!(IdCard.getBytes().length < bysService.getColumnLength("GRAD", "ID_CARD").getBigDecimal("DATALENGTH").intValue())) {
errormsg += "/身份证号 过长";
t = false;
}
if (!(name.getBytes().length < bysService.getColumnLength("GRAD", "NAME").getBigDecimal("DATALENGTH").intValue())) {
errormsg += "/姓名过长";
t = false;
}
if (!(schoolName.getBytes().length < bysService.getColumnLength("GRAD", "SCHOOL_NAME").getBigDecimal("DATALENGTH").intValue())) {
errormsg += "/学校名称过长";
t = false;
}
if (!(stuNumber.getBytes().length < bysService.getColumnLength("GRAD", "STUDENT_NUMBER").getBigDecimal("DATALENGTH").intValue())) {
errormsg += "/学号过长";
t = false;
}
// 性别效验并转换
if (StrKit.isBlank(sexCode)) {
sexCode = sexName.contains("女") ? "1" : sexName.contains("男") ? "2" : "9";
}
// if (bysService.checkClasses(className)) {//校验班级是否存在//TODO 不存在则创建
if (!bysService.getCOLLEGECodeOrSave(collegeCode, collegeName, year)) {// 保存院系
errormsg += "/院系代码保存失败";
t = false;
}
if (t) {
Record r = bysService.getGRADIdByStuNum(stuNum);// 判断学号是否存在,
Grad grad = new Grad();
grad.set("EXAM_NUM", stuNum);// 考生号
grad.set("ID_CARD", IdCard);// 身份证号
grad.set("NAME", name);// 学生姓名
grad.set("SEX", sexCode);// 性别代码
// 【就业信息册】民族代码
grad.set("NATION", nationCode);
// 政治面貌代码
grad.set("POLITICAL_STATUS_CODE", policyCode);
grad.set("SCHOOL_CODE", schoolCode);// 院校代码
grad.set("SCHOOL_NAME", schoolName);// 院校名称
grad.set("DEGREE", degreeCode);// 学历代码
if (bysService.checkCollege(collegeCode, collegeName, year)) {
grad.set("COLLEGE_CODE", collegeCode);// 学院代码
}
if(bysService.checkMajor(majorCode, majorName,year,collegeCode,stuNumber)){
grad.set("MAJOR_CODE", majorCode);// 专业代码
}
grad.set("TRAIN_STYLE_ID", trainCode);// 培养方式代码
// grad.set("TRAIN_FIRM", trainFirm);
if (bysService.checkClasses(stuNumber, className,majorCode,collegeCode,year)) {
grad.set("CLASSES_CODE", stuNumber.substring(0, 9));
if (!bysService.saveFdByClassCode(stuNumber.substring(0, 9), fd)) {// 辅导员
errormsg += "/系统中辅导员名称不存在";
t = false;
}
}
grad.set("PASSWORD", Encrypt.encrypt(IdCard.substring(IdCard.length() - 6, IdCard.length())));// 密码为身份证后6位
grad.set("YEAR", year);
grad.set("STUDENT_NUMBER", stuNumber);// 学号
if (t) {
if (r != null) {
grad.set("ID", r.get("ID"));
grad.update();
} else {
grad.save();
}
success++;
t = false;
} else {
t = true;
}
} else {
errormsg = "/此行数据中存在数据过长项，请校验后重新导入";
t = true;
}
} else {
errormsg = "必填项不能为空";
t = true;
}
if (t) {// 没有保存
error++;
sheet.addCell(new Label(0, 0, "考生号", cell));
sheet.addCell(new Label(1, 0, "身份证号码", cell));
sheet.addCell(new Label(2, 0, "姓名", cell));
sheet.addCell(new Label(3, 0, "性别代码", cell));
sheet.addCell(new Label(4, 0, "性别", cell));
sheet.addCell(new Label(5, 0, "民族代码", cell));
sheet.addCell(new Label(6, 0, "民族", cell));
sheet.addCell(new Label(7, 0, "政治面貌代码", cell));
sheet.addCell(new Label(8, 0, "政治面貌", cell));
sheet.addCell(new Label(9, 0, "院校代码", cell));
sheet.addCell(new Label(10, 0, "院校名称", cell));
sheet.addCell(new Label(11, 0, "学历代码", cell));
sheet.addCell(new Label(12, 0, "学历", cell));
sheet.addCell(new Label(13, 0, "专业代码", cell));
sheet.addCell(new Label(14, 0, "专业", cell));
sheet.addCell(new Label(15, 0, "培养方式代码", cell));
sheet.addCell(new Label(16, 0, "培养方式", cell));
sheet.addCell(new Label(17, 0, "院系代码", cell));
sheet.addCell(new Label(18, 0, "院系名称", cell));
sheet.addCell(new Label(19, 0, "所在班级", cell));
sheet.addCell(new Label(20, 0, "班级辅导员", cell));
sheet.addCell(new Label(21, 0, "学号", cell));
sheet.addCell(new Label(22, 0, "错误信息", cell));
sheet.addCell(new Label(0, error, stuNum + "", cell));
sheet.addCell(new Label(1, error, IdCard, cell));
sheet.addCell(new Label(2, error, name, cell));
sheet.addCell(new Label(3, error, sexCode, cell));
sheet.addCell(new Label(4, error, sexName, cell));
sheet.addCell(new Label(5, error, nationCode, cell));
sheet.addCell(new Label(6, error, nationName, cell));
sheet.addCell(new Label(7, error, policyCode, cell));
sheet.addCell(new Label(8, error, policyName, cell));
sheet.addCell(new Label(9, error, schoolCode, cell));
sheet.addCell(new Label(10, error, schoolName, cell));
sheet.addCell(new Label(11, error, degreeCode, cell));
sheet.addCell(new Label(12, error, degreeName, cell));
sheet.addCell(new Label(13, error, majorCode, cell));
sheet.addCell(new Label(14, error, majorName, cell));
sheet.addCell(new Label(15, error, trainCode, cell));
sheet.addCell(new Label(16, error, trainName, cell));
sheet.addCell(new Label(17, error, collegeCode, cell));
sheet.addCell(new Label(18, error, collegeName, cell));
sheet.addCell(new Label(19, error, className, cell));
sheet.addCell(new Label(20, error, fd, cell));
sheet.addCell(new Label(21, error, stuNumber, cell));
sheet.addCell(new Label(22, error, errormsg, mcell));
Grad bys = new Grad();
bys.set("STUDENT_NUMBER", stuNumber);
bys.set("EXAM_NUM", stuNum);
bys.set("ID_CARD", IdCard);
bys.set("NAME", name);
bys.set("SCHOOL_CODE", schoolCode);
bys.set("SCHOOL_NAME", schoolName);
list.add(bys);
}
}
book.write();
book.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
long endTime = System.currentTimeMillis(); //获取结束时间
System.out.println("程序运行时间：" + (endTime - startTime) + "ms"); //输出程序运行时间
setAttr("success", success);
setAttr("error", error);
setAttr("type", 1);
setAttr("rfile", rfile);
setAttr("list", list);
setAttr("year", year);
render("imp.jsp");
}
扫一扫
09-17
456
456
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
