记录工作

需求是导入数据的时候需要判断哪一行是空行，或者哪一行超过限制字符，然后返回给前端做展示

@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 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 importUsers(MultipartFile file) {
List 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列,电话格式不正确！");  
    }  
}

到此本文就结束了