文章讨论了使用POI库导入Excel文件时的低效率问题，作者提到原始方法逐行处理导致速度缓慢。作者尝试了异步处理和改进解析逻辑，同时分享了另一个效率较高的导入方法，寻求更多优化建议。
基于POI导入Execl表批量导入库
之前写过一个导入表存库的方法，但是只能说能实现但是效率很低 一千多条要十几分钟，人麻了
导入EXecl表方法 能实现功能但是存库慢数据多要十几分钟
结构就类似一个一个数组显示的
可以看到我下面的逻辑就是取出一个然后存一个，一个个去取然后在一个个去存，就很慢，但是可以参考下
//这里太慢我还搞个了个异步
@Async
public void importFile(MultipartFile file) throws IOException {
List<PortalTenantMemberEntity> employees = parseExcel(file.getInputStream(), "Sheet1");
if (employees.size()!=0){
portalTenantMemberMapper.batchInsert(employees);
}
// return true;
}
上下两个方法上面的调用下面的接口
private List<PortalTenantMemberEntity> parseExcel(InputStream inputStream, String sheetName) throws IOException {
List<PortalTenantMemberEntity> employees = new ArrayList<>();
List<PortalOrganizationEntity> portalOrganizationEntityList = new ArrayList<>();
try (Workbook workbook = WorkbookFactory.create(inputStream)) {
Sheet sheet = workbook.getSheet(sheetName);
if (sheet == null) {
throw new IllegalArgumentException("请检查表是否是“Sheet1”,找不到表: " + sheetName);
}
Iterator<Row> rowIterator = sheet.iterator();
rowIterator.next(); // Skip header row
while (rowIterator.hasNext()) {
Row row = rowIterator.next();
if (row!=null && row.getCell(1)!=null) {
PortalTenantMemberEntity portalTenantMemberEntity = new PortalTenantMemberEntity();
UUID uuid = UUID.randomUUID();
portalTenantMemberEntity.setId(uuid.toString());
portalTenantMemberEntity.setTenantId("1");
LambdaQueryWrapper<PortalUserEntity> portalUserEntityLambdaQueryWrapper = new LambdaQueryWrapper<PortalUserEntity>().eq(PortalUserEntity::getUserAccount, row.getCell(0).getStringCellValue()).last("limit 1");
PortalUserEntity portalUserEntity = portalUserMapper.selectOne(portalUserEntityLambdaQueryWrapper);
if (null != portalUserEntity) {
System.out.println("有数据");
portalTenantMemberEntity.setUserId(portalUserEntity.getId());
LambdaQueryWrapper<PortalOrganizationEntity> queryWrapper = new LambdaQueryWrapper<PortalOrganizationEntity>().eq(PortalOrganizationEntity::getOrgName, row.getCell(1).getStringCellValue()).last("limit 1");
PortalOrganizationEntity organizationEntity = getOne(queryWrapper);
if (null != organizationEntity) {
portalTenantMemberEntity.setOrgId(organizationEntity.getId());
LambdaQueryWrapper<PortalTenantMemberEntity> portalTenantMemberEntityLambdaQueryWrapper = new LambdaQueryWrapper<PortalTenantMemberEntity>()
.eq(PortalTenantMemberEntity::getOrgId, portalTenantMemberEntity.getOrgId())
.eq(PortalTenantMemberEntity::getUserId, portalTenantMemberEntity.getUserId());
List<PortalTenantMemberEntity> portalTenantMemberEntities = portalTenantMemberMapper.selectList(portalTenantMemberEntityLambdaQueryWrapper);
if (portalTenantMemberEntities.size()==0){
employees.add(portalTenantMemberEntity);
}
} /* else {
PortalOrganizationEntity organizationEntity1 = new PortalOrganizationEntity();
UUID uuid1 = UUID.randomUUID();
organizationEntity1.setId(uuid1.toString());
organizationEntity1.setOrgName(row.getCell(1).getStringCellValue());
organizationEntity1.setTenantId("1");
organizationEntity1.setOrgCode(uuid.toString());
organizationEntity1.setOrgStaus(1);
organizationEntity1.setOrgType(1);
saveOrUpdate(organizationEntity1);
System.out.println("更了:"+uuid1.toString());
// portalOrganizationEntityList.add(organizationEntity1);
portalTenantMemberEntity.setOrgId(uuid1.toString());
}*/
}
}
}
// saveOrUpdateBatch(portalOrganizationEntityList);
}
return employees;
}
(另一种方法)导入EXecl表这个效率很高只要一秒左右
public RestResult<String> test(@ApiParam(name = "file", value = "文件", required = true) @RequestPart("file") MultipartFile file,HttpServletRequest req) throws IOException {
//文件获取名称
String originalFilename = file.getOriginalFilename();
String suffix = originalFilename.substring(originalFilename.lastIndexOf(".") + 1);
if (file == null || file.isEmpty()) {
return RestResult.buildErrorResult("上传文件为空，请重新上传");
}
if (!suffix.equals("xls") && !suffix.equals("xlsx")) {
return RestResult.buildErrorResult("文件格式错误，请上传excel文件");
}
//直接读取文件流，其实也具体不清楚是什么方法
ExcelReader reader = ExcelUtil.getReader(file.getInputStream());
/** 因为模板中第一行为标题，所以这儿设置从1之后读取 */
//这个就是直接装成包含的数组外面的是list 可以理解为总行数的数组，里面的可以理解为行的每列数据（我是这么想的）
List<List<Object>> read = reader.read(1, reader.getRowCount());
if (CollUtil.isNotEmpty(read)){
read = read.stream().filter(val->ObjectUtil.isNotEmpty(val)).collect(Collectors.toList());
}
List<Object> objects = reader.read(0, reader.getRowCount()).get(0);
//这边就是做表头的判断，具体也可以根据实际业务判断
if (!("一级角色组").equals(objects.get(0))){
return RestResult.buildErrorResult("文件模板错误，请检查模板标头是否正确并存在一级角色组");
}else if (!("二级角色组").equals(objects.get(1))){
return RestResult.buildErrorResult("文件模板错误，请检查模板标头是否正确并存在二级角色组");
}else if (!("三级角色组").equals(objects.get(2))){
return RestResult.buildErrorResult("文件模板错误，请检查模板标头是否正确并存在三级角色组");
}else if (!("用户账号").equals(objects.get(3))){
return RestResult.buildErrorResult("文件模板错误，请检查模板标头是否正确并存在用户账号");
}else if (!("用户姓名").equals(objects.get(4))){
return RestResult.buildErrorResult("文件模板错误，请检查模板标头是否正确并存在用户姓名");
}
if (read.size() == 0) {
return RestResult.buildErrorResult("文件数据为空，请重新上传");
} else {
//导入数据处理
String message = null;
try {
//这个忽略，无关参数
String icanUserAccount = req.getHeader("IcanUserAccount");
//这就是把数组传到业务层实现具体业务就是看你们自己了
portalOrganizationService.importTemplateDataBuild(read,icanUserAccount);
// message = portalOrganizationService.importTemplateDataBuild(read);
} catch (Exception e) {
e.printStackTrace();
return RestResult.buildErrorResult(e.getMessage());
}
return RestResult.buildSuccessResult(message);
}
}
//这个是我导入表数据，还要区分父子级到表里，重复的不存
public void importTemplateDataBuild(List<List<Object>> read,String icanUserAccount) {
if (CollUtil.isNotEmpty(read)) {
//流去重
List<List<Object>> collect = read.stream().distinct().collect(Collectors.toList());
//把 List<List<Object>> 转 成实体的数组 List<PortalRoleConfigEntity>
List<PortalRoleConfigEntity> personList = collect.stream()
.map(e -> new PortalRoleConfigEntity((String)e.get(0),(String)e.get(1),(String)e.get(2),(String)e.get(3),(String)e.get(4))).collect(Collectors.toList());
ServletRequestAttributes attributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
if (ObjectUtil.isNotEmpty(attributes)) {
HttpServletRequest request = attributes.getRequest();
icanUserAccount = request.getHeader("Icanuseraccount");
}
if (null==icanUserAccount){
icanUserAccount="admin";
}
// portalRoleConfigService.handleRoleConfig(personList,icanUserAccount);
//过滤表格一级父亲然后过滤重复
List<String> ruleSerials = read.stream().map(val -> (String) val.get(0)).filter(val -> StrUtil.isNotEmpty(val)).distinct().collect(Collectors.toList());
//查表的所有数据
List<PortalRoleEntity> portalRoleEntities = portalRoleMapper.selectList(null);
//这边也是表查询的数据去重
List<String> ruleSerialsa = portalRoleEntities.stream().map(val -> (String) val.getRoleDesc()).filter(val -> StrUtil.isNotEmpty(val)).distinct().collect(Collectors.toList());
// 这个就是把把两个数组对比然后查出ruleSerials有但是ruleSerialsa 没有得数据
List<String> difference = ruleSerials.stream()
.filter(item -> !ruleSerialsa.contains(item))
.collect(Collectors.toList());
//判断有没有存在，有就存没有就过
if (difference.size()!=0){
//保存数据
}
//查询前两个父子级角色在数据
List<List<Object>> lastTwo = collect.stream()
.map(list -> list.subList(1, 3))
.collect(Collectors.toList());
//查询后两个父子级角色在数据
List<List<Object>> firstTwo = collect.stream()
.map(list -> list.subList(0, 2))
.collect(Collectors.toList());
// //拼接一起
lastTwo.addAll(firstTwo);
//把数组的中文替换成id
//查出父id
//这个就是把后面几个按照上面的方法一起执行下，但是代码不对，逻辑是对的
List<String> idList = firstTwo.stream().map(val -> (String) val.get(0)).filter(val -> StrUtil.isNotEmpty(val)).collect(Collectors.toList());
List<String> idList2 = lastTwo.stream().map(val -> (String) val.get(0)).filter(val -> StrUtil.isNotEmpty(val)).collect(Collectors.toList());
//查出父id
//就是把上面的数据转成id和父id的map然后在根据表查出的对比
List<Map<String, String>> collect1 = IntStream.range(0, idList.size())
.mapToObj(i -> {
Map<String, String> map = new HashMap<>();
map.put("id", idList.get(i));
map.put("parentId", idList2.get(i));
return map;
})
.collect(Collectors.toList());
System.out.println(collect1);
// 查出角色表所有父子级
List<Map<String, Object>> mapList = portalRoleEntities.stream()
.map(entity -> {
// 创建一个新的 Map 对象来存储 PortalRoleEntity 对象的属性
Map<String, Object> map = new HashMap<>();
// 将 PortalRoleEntity 对象的属性添加到 Map 对象中
map.put("id", entity.getId());
map.put("parentId", entity.getParentId());
return map;
})
.collect(Collectors.toList());
//查出表里没有的数据然后在存入数据库,再按照取出表里没，但是表格有的数据
然后在存入表
List<Map<String, String>> result = collect1.stream()
.filter(map -> !mapList.contains(map))
.collect(Collectors.toList());
// List<PortalRoleEntity> collect1 = lastTwo.stream().map(e -> new PortalRoleEntity((String) e.get(0), (String) e.get(1))).collect(Collectors.toList());
// new LambdaQueryWrapper<PortalUserEntity>();
// portalRoleMapper.sele(collect1);
System.out.println(result);
// System.out.println(lastTwo);
}
}
大家要是更好方法可以都说说，因为之前没做过就只能凭借自己的想法写。要是有更好的逻辑或者方法可以评论说说，不然作为程序员会的不多收入就上不去了 呜呜呜呜呜
790
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
