-
//传用户到user表,并关联学院和系(Excel文件形式)
verride
public void upload(HttpServletRequest request, MultipartFile file) {
XSSFWorkbook readExcel;
try {
readExcel = new XSSFWorkbook(file.getInputStream());
} catch (IOException e) {
log.error("\n####", e);
return;
}
Sheet sheet = readExcel.getSheetAt(0);//获取第0个工作表
User user = new User();
int lastRowNum = sheet.getLastRowNum();//获取最后一行的数字,从0开始算
for (int i = 1; i <= lastRowNum; i++) {
//获取第i行
Row row = sheet.getRow(i);
if (row == null) {
break;
}
//获取第i行的第0列 账号
XSSFCell cell0 = (XSSFCell) row.getCell(0);
cell0.setCellType(CellType.STRING);
if (cell0 == null || “”.equals(cell0.getStringCellValue())) {
continue;
}
user.setUsername(cell0.getStringCellValue());
//获取第i行的第1列 密码
XSSFCell cell1 = (XSSFCell) row.getCell(1);
if (cell1 != null) {
cell1.setCellType(CellType.STRING);
user.setPassword(cell1.getStringCellValue());
}
XSSFCell cell2 = (XSSFCell) row.getCell(2);
if (cell2 != null) {
cell2.setCellType(CellType.STRING);
user.setRealName(cell2.getStringCellValue());
}XSSFCell cell3 = (XSSFCell) row.getCell(3); if (cell3 != null) { cell3.setCellType(CellType.STRING); if (cell3.getStringCellValue() != null) { Integer identity = Integer.parseInt(cell3.getStringCellValue()); user.setIdentitySign(identity); } } XSSFCell cell4 = (XSSFCell) row.getCell(4); if (cell4 != null) { cell4.setCellType(CellType.STRING); user.setCollegeName(cell4.getStringCellValue()); } XSSFCell cell5 = (XSSFCell) row.getCell(5); if (cell5 != null) { cell5.setCellType(CellType.STRING); user.setMajorName(cell5.getStringCellValue()); } //这里可以是从redis,从其他地方获取,暂时只起一个记录功能 // 从session里找到操作者的id属性 User lander = (User) request.getSession().getAttribute("user"); Long operateId = lander.getId(); user.setOperator(operateId); user.setOperateIp(NetworkUtil.getIpAddress(request)); user.setOperateTime(new Timestamp(System.currentTimeMillis())); //判断用户在数据库user表中是否已经存在 Integer flag = userDao.countStuByUsername(user.getUsername()); if (flag == 0) { //不存在则导入 userDao.insertUser(user); } else { Long id = userDao.selectIdByUsername(user); //已存在,记录该用户的id user.setId(id); } Long userId = user.getId(); String collegeName = user.getCollegeName(); Long collegeId = groupDao.selectCollegeId(collegeName); Long majorId = groupDao.selectMajorId(user.getMajorName()); GroupUser collegeUser = new GroupUser(); collegeUser.setUserId(userId); collegeUser.setGroupId(collegeId); userDao.insertCollegeUser(collegeUser); GroupUser majorUser = new GroupUser(); majorUser.setGroupId(majorId); majorUser.setUserId(userId); userDao.insertMajorUser(majorUser); log.info("\n####录入成功"); }
}