java 导入Excel数据校验判断哪行那列
记录工作
需求是导入数据的时候需要判断哪一行是空行，或者哪一行超过限制字符，然后返回给前端做展示
@PostMapping("/importExcel")
@ApiOperation("导入用户")
@ApiImplicitParams({
@ApiImplicitParam(name = "file", value = "Excel 文件", required = true, dataTypeClass = MultipartFile.class),
@ApiImplicitParam(name = "updateSupport", value = "是否支持更新，默认为 false", example = "true", dataTypeClass = Boolean.class)
})
public Response<SaveAndUpdateVO> importExcel(@RequestParam("file") MultipartFile file,
@RequestParam(value = "updateSupport", required = false, defaultValue = "false") Boolean updateSupport) throws Exception {
SaveAndUpdateVO saveAndUpdateVO = SaveAndUpdateVO.builder()
.isSuccess(sysUserService.importUsers(file, updateSupport)).build();
return Response.build(saveAndUpdateVO);
}
这里的Response类是自己做的统一返回封装类，SaveAndUpdateVO这个类也是一个返回类，可以自己用其他方法或者类代替
public Boolean importUsers(MultipartFile file, boolean isUpdateSupport) { if (file.isEmpty()) { throw new BizException(ErrorCode.DATA_ERROR, "导入用户数据不能为空！"); } List<UserImportExcelResponse> userImportExcelResponses = importUsers(file); userImportExcelResponses.forEach(importUser -> { SysUser sysUsers = getOne(new LambdaQueryWrapper<SysUser>().eq(SysUser::getLoginName, importUser.getLoginName()) .eq(SysUser::getDelFlag, Constants.DEL_FLAG_DELETED)); // 判断用户如果不存在，在进行插入 if (sysUsers == null) { AssertBiz.isMobile(importUser.getPhone(), "手机号码格式错误!"); SysUser sysUser = new SysUser(); BeanUtil.copyProperties(importUser, sysUser); //设置创建时间 sysUser.setServerCreateTime(LocalDateTime.now()); //设置默认密码密码 BCryptPasswordEncoder bCryptPasswordEncoder = new BCryptPasswordEncoder(); sysUser.setPassword(bCryptPasswordEncoder.encode("Admin@123456")); //设置主键id-uuid String userId = IdUtils.getId(); sysUser.setId(userId); sysUser.setDelFlag(Constants.DEL_FLAG_DELETED); //新增用户 save(sysUser); AssertBiz.isNotEmpty(importUser.getRoleName(), "角色名称不能为空！"); SysRole sysRoles = sysRoleService.getOne(new LambdaQueryWrapper<SysRole>().eq(SysRole::getRoleName, importUser.getRoleName()) .eq(SysRole::getDelFlag, Constants.DEL_FLAG_DELETED)); AssertBiz.notNull(sysRoles, "该角色不存在！"); //新增用户角色 SysUserRole sysUserRole = new SysUserRole(userId, sysRoles.getId(), LocalDateTime.now(), LocalDateTime.now(), Constants.DEL_FLAG_DELETED); sysUserRoleService.save(sysUserRole); AssertBiz.isNotEmpty(importUser.getOrgName(), "部门名称不能为空！"); SysOrganization one = sysOrganizationService.getOne(new LambdaQueryWrapper<SysOrganization>().select(SysOrganization::getId) .eq(SysOrganization::getOrgName, importUser.getOrgName()) .eq(SysOrganization::getDelFlag, Constants.DEL_FLAG_DELETED)); AssertBiz.notNull(one, "该部门不存在！或是用户没有该部门权限！"); //新增用户组织 SysUserOrg sysUserOrg = new SysUserOrg(userId, one.getId(), LocalDateTime.now(), LocalDateTime.now(), Constants.DEL_FLAG_DELETED); sysUserOrgService.save(sysUserOrg); return; } // 如果存在，判断是否允许更新 if (!isUpdateSupport) { throw new BizException(ErrorCode.DATA_ERROR, "用户账号[" + importUser.getLoginName() + "]已存在，不能重复添加！"); } AssertBiz.isNotEmpty(sysUsers.getId(), "用户id不能为空！"); LambdaUpdateWrapper<SysUser> updateWrapper = new LambdaUpdateWrapper<>(); updateWrapper.eq(SysUser::getId, sysUsers.getId()) .set(SysUser::getStatus, importUser.getStatus()) .set(SysUser::getUserName, importUser.getUserName()) .set(SysUser::getPhone, importUser.getPhone()) .set(SysUser::getServerUpdateTime, LocalDateTime.now()); update(updateWrapper); }); return true; }
/** * 导入数据解析校验 * * @param file * @return */ private List<UserImportExcelResponse> importUsers(MultipartFile file) { List<UserImportExcelResponse> list = new ArrayList<>(); try { //根据路径获取这个操作excel的实例 XSSFWorkbook wb = new XSSFWorkbook(file.getInputStream()); //根据页面index 获取sheet页 XSSFSheet sheet = wb.getSheetAt(0); XSSFRow row; int sheets = sheet.getPhysicalNumberOfRows(); //循环sesheet页中数据从第二行开始，第一行是标题 for (int i = 1; i < sheets; i++) { //获取每一行数据 row = sheet.getRow(i); String loginName = row.getCell(0).toString(); String userName = row.getCell(1).toString(); String roleName = row.getCell(2).toString(); String orgName = row.getCell(3).toString(); String phone = row.getCell(4).toString(); String status = row.getCell(5).toString(); int line = i + 1; checkLength(loginName, userName, roleName, orgName, phone, line); UserImportExcelResponse excel = new UserImportExcelResponse(); AssertBiz.isNotEmpty(loginName, "在excel表第" + line + "行，第1列，用户登录名不能为空！"); excel.setLoginName(loginName); AssertBiz.isNotEmpty(userName, "在excel表第" + line + "行，第2列，用户名称不能为空！"); excel.setUserName(userName); AssertBiz.isNotEmpty(roleName, "在excel表第" + line + "行，第3列，角色名称不能为空！"); excel.setRoleName(roleName); AssertBiz.isNotEmpty(orgName, "在excel表第" + line + "行，第4列，部门名称不能为空！"); excel.setOrgName(orgName); AssertBiz.isNotEmpty(phone, "在excel表第" + line + "行，第5列，电话不能为空！"); excel.setPhone(phone); AssertBiz.isNotEmpty(status, "在excel表第" + line + "行，第6列，用户状态不能为空！"); excel.setStatus(status); list.add(excel); } } catch (Exception e) { e.printStackTrace(); String substring = String.valueOf(e).substring(String.valueOf(e).indexOf(":") + 1); throw new BizException(ErrorCode.DATA_ERROR, substring); } return list; }
/** * 校验字符串长度 * * @param loginName * @param userName * @param roleName * @param orgName * @param phone */ private void checkLength(String loginName, String userName, String roleName, String orgName, String phone, int row) { int loginNameLength = loginName.length(); int userNameLength = userName.length(); int roleNameLength = roleName.length(); int orgNameLength = orgName.length(); int phoneLength = phone.length(); if (loginNameLength > 16) { throw new BizException(ErrorCode.DATA_ERROR, "在excel表第" + row + "行，第1列,导入用户登录名不能超过16个字符！"); } if (userNameLength > 16) { throw new BizException(ErrorCode.DATA_ERROR, "在excel表第" + row + "行，第2列,导入用户昵称名不能超过16个字符！"); } if (roleNameLength > 16) { throw new BizException(ErrorCode.DATA_ERROR, "在excel表第" + row + "行，第3列,导入角色名不能超过16个字符！"); } if (orgNameLength > 16) { throw new BizException(ErrorCode.DATA_ERROR, "在excel表第" + row + "行，第4列,导入部门名不能超过16个字符！"); } if (phoneLength > 11) { throw new BizException(ErrorCode.DATA_ERROR, "在excel表第" + row + "行，第5列,电话格式不正确！"); } if (phoneLength < 11) { throw new BizException(ErrorCode.DATA_ERROR, "在excel表第" + row + "行，第5列,电话格式不正确！"); } }
到此本文就结束了
