controller类：
@Autowired
private IStudentService iStudentService;
@AutoLog(value = "导入-学生信息")
@ApiOperation(value = "导入-学生信息", notes = "导入-学生信息")
@PostMapping(value = "/student")
public Result<?> importStudent(@RequestParam(name = "file", required = true) MultipartFile file) throws IOException {
List<Student> studentList = iStudentService.importStudent(file);
if (CollectionUtils.isEmpty(studentList)) {
return Result.error("导入失败");
}
return Result.ok(studentList);
}
service类：
List<Student> importStudent(MultipartFile file) throws IOException;
imp类：
@Override
@Transactional(rollbackFor = Exception.class)
public List<Student> importStudent(MultipartFile multipartFile) throws IOException {
if (!ExcelBaseUtil.isExcel(multipartFile)) {
throw new RuntimeException(ErrorMessage.ERROR_IS_NULL_OR_NOT_EXCEL);
}
List<Student> resultList = new ArrayList<>();
Workbook workbook = ExcelBaseUtil.createWorkbook(multipartFile);
if (workbook != null) {
// 获取工作表
Sheet sheet = workbook.getSheetAt(0);
// 获取sheet中第一行行号
int firstRowNum = sheet.getFirstRowNum();
// 获取sheet中最后一行行号
int lastRowNum = sheet.getLastRowNum();
// 循环插入数据
for (int i = firstRowNum + 1; i <= lastRowNum; i++) { // 标题的下一行开始
Row row = sheet.getRow(i);
if (ExcelBaseUtil.isEmptyRow(row) == false) {
resultList.add(setStudentData(row, i));
}
}
}
return resultList;
}
private Student setStudentData(Row row, Integer i) {
//学号，列的下标从0开始
Cell studentNumberCell = row.getCell(1);
String studentNumberCellValue = ExcelBaseUtil.getVal(studentNumberCell).trim();
//姓名
Cell nameCell = row.getCell(2);
String nameCellValue = ExcelBaseUtil.getVal(nameCell).trim();
//学院
Cell collegeNameCell = row.getCell(3);
String collegeNameCellValue = ExcelBaseUtil.getVal(collegeNameCell).trim();
//获取学院id
SysDepart sysDepart = iSysDepartService.getOne(Wrappers.<SysDepart>query().lambda().eq(SysDepart::getDepartName, collegeNameCellValue));
if (sysDepart == null) {
throw new RuntimeException("第" + i + "行，学号为：" + studentNumberCellValue + "，学生姓名为：" + nameCellValue + "的学生，学院信息未查询到，请核对后重新导入该文件");
}
//专业
Cell majorNameCell = row.getCell(4);
String majorNameCellValue = ExcelBaseUtil.getVal(majorNameCell).trim();
//获取专业id
Major major = iMajorService.getOne(Wrappers.<Major>query().lambda().eq(Major::getFullName, majorNameCellValue));
if (major == null) {
throw new RuntimeException("第" + i + "行，学号为：" + studentNumberCellValue + "，姓名为：" + nameCellValue + "的学生，专业信息未查询到，请核对后重新导入该文件");
}
//校验专业是不是学院下的
if (!judgeMajorIsSysDept(sysDepart, major)) {
throw new RuntimeException("第" + i + "行，学号为：" + studentNumberCellValue + "，姓名为：" + nameCellValue + "的学生，专业不属于该学院下，请核对后重新导入该文件");
}
//密码
Cell passWordCell = row.getCell(5);
String passWordCellValue = ExcelBaseUtil.getVal(passWordCell).trim();
//性别
Cell sexCell = row.getCell(6);
String sexCellValue = ExcelBaseUtil.getVal(sexCell).trim();
//国籍
Cell nationalityCell = row.getCell(7);
String nationalityCellValue = ExcelBaseUtil.getVal(nationalityCell).trim();
//省份
Cell provinceCell = row.getCell(8);
String provinceCellValue = ExcelBaseUtil.getVal(provinceCell).trim();
//政治面貌
Cell politicalLandscapeCell = row.getCell(9);
String politicalLandscapeCellValue = ExcelBaseUtil.getVal(politicalLandscapeCell).trim();
//状态
Cell statusCell = row.getCell(10);
String statusCellValue = ExcelBaseUtil.getVal(statusCell).trim();
//入学时间
Cell enrolDayCell = row.getCell(11);
String enrolDayCellValue = ExcelBaseUtil.getVal(enrolDayCell).trim();
String enrolDay = enrolDayCellValue.replaceAll("\\.", "-");
return createStudent(studentNumberCellValue, nameCellValue, sysDepart, major, passWordCellValue, nationalityCellValue, provinceCellValue, politicalLandscapeCellValue, statusCellValue, enrolDay,sexCellValue);
}
private boolean judgeMajorIsSysDept(SysDepart sysDepart, Major major) {
if (StrUtil.equals(major.getDepartId(), sysDepart.getId())) {
return true;
}
return false;
}
private Student createStudent(String studentNumberCellValue, String nameCellValue, SysDepart sysDepart, Major major, String passWordCellValue, String nationalityCellValue, String provinceCellValue, String politicalLandscapeCellValue, String statusCellValue, String enrolDayCellValue,String sexCellValue) {
//查询学生是否存在
Student student = getStudent(studentNumberCellValue);
//查询用户是否存在
SysUser sysUser = iSysUserService.getOne(Wrappers.<SysUser>query().lambda().eq(SysUser::getUsername, studentNumberCellValue));
//1、学生信息不存在
if (student == null) {
//用户不存在
if (sysUser == null) {
sysUser = setSysUser(studentNumberCellValue, nameCellValue, passWordCellValue);
}
//创建学生
student = setStudent(sysUser, sysDepart, major, nationalityCellValue, provinceCellValue, politicalLandscapeCellValue, statusCellValue, enrolDayCellValue, studentNumberCellValue, nameCellValue,sexCellValue);
return student;
} else {
// 存在则更新
updateStudent(student, studentNumberCellValue, nameCellValue, sysDepart, major, passWordCellValue, nationalityCellValue, provinceCellValue, politicalLandscapeCellValue, statusCellValue, enrolDayCellValue, sexCellValue);
}
//2、学生信息不存在
if (sysUser == null) {
sysUser = setSysUser(studentNumberCellValue, nameCellValue, passWordCellValue);
//更改学生表中的用户id
student.setUserId(sysUser.getId());
super.updateById(student);
} else {
// 存在则更新
if (passWordCellValue != null) {
String salt = oConvertUtils.randomGen(8);
sysUser.setSalt(salt);
String passwordEncode = PasswordUtil.encrypt(studentNumberCellValue, passWordCellValue, salt);
sysUser.setPassword(passwordEncode);
}
if(nameCellValue != null){
sysUser.setRealname(nameCellValue);
}
iSysUserService.updateById(sysUser);
}
return student;
}
private Student setStudent(SysUser sysUsers, SysDepart sysDepart, Major major, String nationalityCellValue, String provinceCellValue, String politicalLandscapeCellValue, String statusCellValue, String enrolDayCellValue, String studentNumberCellValue, String nameCellValue,String sexCellValue) {
Student student = new Student();
student.setUserId(sysUsers.getId());
student.setName(nameCellValue);
student.setCollegeId(sysDepart.getId());
student.setMajorId(major.getId());
//国籍
SysDictItem countrySysDictItem = iSysDictItemService.getByDictCodeAndDictItemText("country", nationalityCellValue);
student.setCountryId(Integer.valueOf(countrySysDictItem.getItemValue() == null ? null : countrySysDictItem.getItemValue()));
//省份
SysDictItem provinceSysDictItem = iSysDictItemService.getByDictCodeAndDictItemText("province", provinceCellValue);
student.setProvinceId(Integer.valueOf(provinceSysDictItem.getItemValue() == null ? null : provinceSysDictItem.getItemValue()));
//政治面貌
SysDictItem politicsSysDictItem = iSysDictItemService.getByDictCodeAndDictItemText("politics", politicalLandscapeCellValue);
student.setPoliticsId(Integer.valueOf(politicsSysDictItem.getItemValue() == null ? null : politicsSysDictItem.getItemValue()));
//性别
SysDictItem sexSysDictItem = iSysDictItemService.getByDictCodeAndDictItemText("sex", sexCellValue);
student.setSex(Integer.valueOf(sexSysDictItem.getItemValue() == null ? null : sexSysDictItem.getItemValue()));
//状态
if (StrUtil.equals(statusCellValue, "在校")) {
student.setStatus(1);
}
if (StrUtil.equals(statusCellValue, "不在校")) {
student.setStatus(2);
}
student.setEnrolDay(DateUtil.parse(enrolDayCellValue, DatePattern.NORM_DATE_PATTERN));
student.setStudentNumber(studentNumberCellValue);
super.save(student);
return student;
}
private Student updateStudent(Student student, String studentNumberCellValue, String nameCellValue, SysDepart sysDepart, Major major, String passWordCellValue, String nationalityCellValue, String provinceCellValue, String politicalLandscapeCellValue, String statusCellValue, String enrolDayCellValue,String sexCellValue) {
if(nameCellValue != null){
student.setName(nameCellValue);
}
if (sysDepart != null) {
student.setCollegeId(sysDepart.getId());
}
if(major != null){
student.setMajorId(major.getId());
}
//国籍
SysDictItem countrySysDictItem = iSysDictItemService.getByDictCodeAndDictItemText("country", nationalityCellValue);
student.setCountryId(Integer.valueOf(countrySysDictItem.getItemValue() == null ? null : countrySysDictItem.getItemValue()));
//省份
SysDictItem provinceSysDictItem = iSysDictItemService.getByDictCodeAndDictItemText("province", provinceCellValue);
student.setProvinceId(Integer.valueOf(provinceSysDictItem.getItemValue() == null ? null : provinceSysDictItem.getItemValue()));
//政治面貌
SysDictItem politicsSysDictItem = iSysDictItemService.getByDictCodeAndDictItemText("politics", politicalLandscapeCellValue);
student.setPoliticsId(Integer.valueOf(politicsSysDictItem.getItemValue() == null ? null : politicsSysDictItem.getItemValue()));
//性别
SysDictItem sexSysDictItem = iSysDictItemService.getByDictCodeAndDictItemText("sex", sexCellValue);
student.setSex(Integer.valueOf(sexSysDictItem.getItemValue() == null ? null : sexSysDictItem.getItemValue()));
//状态
if (StrUtil.equals(statusCellValue, "在校")) {
student.setStatus(1);
}
if (StrUtil.equals(statusCellValue, "不在校")) {
student.setStatus(2);
}
student.setEnrolDay(DateUtil.parse(enrolDayCellValue, DatePattern.NORM_DATE_PATTERN));
updateById(student);
return student;
}
private SysUser setSysUser(String studentNumberCellValue, String nameCellValue, String passWordCellValue) {
SysUser sysUsers = new SysUser();
String salt = oConvertUtils.randomGen(8);
sysUsers.setRealname(nameCellValue);
sysUsers.setUsername(studentNumberCellValue);
sysUsers.setSalt(salt);
String passwordEncode = PasswordUtil.encrypt(studentNumberCellValue, passWordCellValue, salt);
sysUsers.setPassword(passwordEncode);
sysUsers.setStatus(1);
sysUsers.setDelFlag("0");
iSysUserService.save(sysUsers);
//获取学生的权限
SysRole sysRole = iSysRoleService.getOne(Wrappers.<SysRole>query().lambda().eq(SysRole::getRoleName, "学生"));
if (sysRole != null) {
SysUserRole sysUserRole = new SysUserRole(sysUsers.getId(), sysRole.getId());
iSysUserRoleService.save(sysUserRole);
}
return sysUsers;
}
private Student getStudent(String studentNumberCellValue) {
return super.getOne(Wrappers.<Student>query().lambda().eq(Student::getStudentNumber, studentNumberCellValue));
}
浙公网安备 33010602011771号