记录工作
需求是导入数据的时候需要判断哪一行是空行，或者哪一行超过限制字符，然后返回给前端做展示
@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列,电话格式不正确！");
}
}
到此本文就结束了
标签：java,String,AssertBiz,excel,哪行,表第,new,那列,row From： https://www.cnblogs.com/Dshzs17/p/16979086.html