EasyExcel实现批量导入_easyexcel 导入数量

作者：花生_TL007 | 2024-04-27 09:37:30

踩

easyexcel 导入数量

pom引入

<dependency>
		<groupId>com.alibaba</groupId>
		<artifactId>easyexcel</artifactId>
		<version>2.2.3</version>
</dependency>
1
2
3
4
5

异步导入接口

controller层

    @ApiOperation(value = "1.0版本-客户信息导入")
    @PostMapping("/clubImport")
    public Result<Map<String, String>> clubImport(@RequestParam("file") MultipartFile file, @ApiParam("业务渠道") @RequestParam("businessChannel") Integer businessChannel
            , @RequestParam("shopCode") String shopCode, @RequestParam("shopName") String shopName,@ApiParam("导入模版类型：1-普通模版，2-抖音模版") @RequestParam("importType") Integer importType) {
        ImportClueParam importClueParam = new ImportClueParam();
        importClueParam.setFile(file);
        importClueParam.setShopCode(shopCode);
        importClueParam.setBusinessChannel(businessChannel);
        importClueParam.setShopName(shopName);
        importClueParam.setImportType(importType);
        importClueParam.setTest(AuthNHolderUtils.isTest());
        String batchNo = LocalDateTime.now().format(DateTimeFormatter.ofPattern(DateUtils.DATE_NO_CHAR_PATTERN));
        log.info("开始导入客户信息，本次导入批次号：{}，店铺code：{}，店铺名称：{}，业务渠道：{},模版类型：{}", batchNo, shopCode, shopName, businessChannel,importType);
        try {
            //加锁，同一时间只有一个导入任务在进行
            boolean tryLock = redisLock.tryLock(ImportConstant.IMPORT_LOCK_KEY + batchNo, 10);
            if (!tryLock) {
                return Result.fail("500", "存在其他正在进行的导入任务");
            }
            //数据解析和校验
            List<ClueImportTemporaryDO> covertList = clueImportService.handleExcel(importClueParam);
            log.info("批次号：{} 客户信息解析完成，本次导入的客户手机号：[{}]",batchNo, covertList.stream().map(ClueImportTemporaryDO::getPhone).collect(Collectors.joining(",")));
            //异步完成校验数据及导入任务
            clueImportService.asynHandleList(covertList, batchNo, importType);

            log.info("clubImport结束:{},batchNo:{}", LocalDateTime.now(), batchNo);
            //返回批次号
            HashMap<String, String> batchMap = new HashMap<>();
            batchMap.put("batchNo", batchNo);
            return Result.success(batchMap);
        } catch (DccCrmException e) {
            log.error("error_clueImport_DccCrmException异常:{}", e.getMessage());
            throw e;
        } catch (Exception e) {
            log.error("error_clueImport_Exception异常:{}", e.getMessage());
            if (e.getCause() instanceof DccCrmException) {
                throw (DccCrmException) e.getCause();
            }
            throw new DccCrmException(ErrorCodeEnum.EXCEL_ERROR);
        } finally {
            //释放锁
            redisLock.unlock(ImportConstant.IMPORT_LOCK_KEY + batchNo);
        }
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

返回实体对象

@Data
@ApiModel(value = "ImportCustomerEchoVO", description = "导入数据回显")
public class ImportCustomerEchoVO {

    @ApiModelProperty("列表数据")
    private List<ImportCustomerVO> importCustomerVOS;
    @ApiModelProperty("一共识别数据")
    private Integer total;
    @ApiModelProperty("新增成功客户数量")
    private Integer successNum;
    @ApiModelProperty("新增失败客户数据数量")
    private Integer failNum;
    @ApiModelProperty("批次号")
    private String batchUid;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

service层–clueImportService数据解析和校验

    @SneakyThrows
    @Override
    public List<ClueImportTemporaryDO> handleExcel(ImportClueParam importClueParam) {
        ExcelImportSimpleListener simpleListener = new ExcelImportSimpleListener();
        ExcelImportDouyinListener douyinListener = new ExcelImportDouyinListener();
        List<ClueImportTemporaryDO> voList = new ArrayList<>();
        //根据输入的来源渠道调用对应的模版进行导入
        if (importClueParam.getImportType().equals(ImportTypeEnum.DOUYIN.getCode())) {
            log.info("开始解析抖音excel模板的客户导入信息，导入批次号：{}", importClueParam.getBatchNo());
            EasyExcel.read(importClueParam.getFile().getInputStream(), ImportClueDouyinVO.class, douyinListener).sheet().doRead();
            voList = douyinListener.getList();
            if (voList.size() > 1000) {
                throw new DccCrmException(ErrorCodeEnum.MAX_1000);
            }
            voList.forEach(x -> {
                x.setLastOperator(AuthNHolder.displayName());
                x.setTemplateType(TemplateTypeEnum.DOUYIN.getType());
                x.setShopCode(importClueParam.getShopCode());
                x.setBusinessChannel(importClueParam.getBusinessChannel());
                x.setShopName(importClueParam.getShopName());
                x.setIsTest(importClueParam.isTest() ? 1 : 0);
            });
        } else if (importClueParam.getImportType().equals(ImportTypeEnum.SIMPLE.getCode())) {
            log.info("开始解析通用excel模板的客户导入信息，导入批次号：{}", importClueParam.getBatchNo());
            EasyExcel.read(importClueParam.getFile().getInputStream(), ImportClueSimpleVO.class, simpleListener).sheet().doRead();
            voList = simpleListener.getList();
            if (voList.size() > 1000) {
                throw new DccCrmException(ErrorCodeEnum.MAX_1000);
            }
            voList.forEach(x -> {
                x.setLastOperator(AuthNHolder.displayName());
                x.setTemplateType(TemplateTypeEnum.SIMPLE.getType());
                x.setShopCode(importClueParam.getShopCode());
                x.setBusinessChannel(importClueParam.getBusinessChannel());
                x.setShopName(importClueParam.getShopName());
                x.setIsTest(importClueParam.isTest() ? 1 : 0);
            });
        } else {
            throw new DccCrmException(ErrorCodeEnum.IMPORT_TYPE_ERROR);
        }
        return voList;
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

		//异步完成校验及导入
		@Override
    @Async
    public void asynHandleList(List<ClueImportTemporaryDO> voList, String batchNo, Integer importType) {
        log.info("开始异步处理解析出的客户信息，批次号：{}", batchNo);
        if (CollectionUtils.isEmpty(voList)) {
            redisTemplate.opsForValue().set(ImportConstant.PROGRESS_BAR_KEY + batchNo, new ImportRedisVO().setFlag(1).setDesc("导入成功"));
            return;
        }
        ImportRedisVO importRedisVO = new ImportRedisVO();
        //在redis中存入重复数据
        //封装phones
        List<String> phones = voList.stream().map(ClueImportTemporaryDO::getPhone).collect(Collectors.toList());
        String shopCode = voList.get(0).getShopCode();
        List<String> existPhones = new ArrayList<>();
        if (StringUtils.isNotBlank(shopCode) && CollectionUtils.isNotEmpty(phones)) {
            existPhones = customerSPI.getExistCustomerByShopAndPhone(shopCode, phones);
        }
        importRedisVO.setRepeatNum(existPhones.size());
        List<ClueImportTemporaryDO> failInsertList = new ArrayList<>();
        importRedisVO.setFlag(0);
        //校验数据
        List<ClueImportTemporaryDO> list = checkList(voList, batchNo, importRedisVO, importType);
        //插入临时表
        importRedisVO.setDesc("正在插入数据");
        redisTemplate.opsForValue().set(ImportConstant.PROGRESS_BAR_KEY + batchNo, importRedisVO);
        //分批处理插入，异常数据存入redis
        List<List<ClueImportTemporaryDO>> partition = Lists.partition(list, 100);
        partition.stream().forEach(page -> {
            try {
                clueImportTemporaryMapper.insertList(page);
            } catch (Exception e) {
                //插入失败，则单条插入，异常数据存入redis
                page.forEach(p -> {
                    try {
                        basicDao.insert(p, ClueImportTemporaryDO.class);
                    } catch (Exception exception) {
                        p.setRemark(StringUtils.isNotBlank(p.getRemark()) ? p.getRemark() : "" + " 数据入库异常，请检查字段长度");
                        failInsertList.add(p);
                    }
                });
            }

        });
        importRedisVO.setFailInsertList(failInsertList);
        //导入成功的调用线索服务
        importRedisVO.setDesc("正在传入线索服务");
        redisTemplate.opsForValue().set(ImportConstant.PROGRESS_BAR_KEY + batchNo, importRedisVO);
        List<ClueImportTemporaryDO> successList = list.stream().filter(x -> x.getImportState() == 1).collect(Collectors.toList());
        successList.removeAll(failInsertList);
        List<AddClueRequest> clues = convertClueData(successList);
        //调用线索dubbo接口，线索中心要求每次最多传500条
        List<List<AddClueRequest>> kozukiList = Lists.partition(clues, 500);
        kozukiList.forEach(clueSPI::batchAddClue);
        log.info("异步处理解析出的客户信息完成，批次号：{}", batchNo);
        importRedisVO.setFlag(1);
        importRedisVO.setDesc("导入成功");
        redisTemplate.opsForValue().set(ImportConstant.PROGRESS_BAR_KEY + batchNo, importRedisVO);
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59

ExcelImportSimpleListener

@Slf4j
public class ExcelImportSimpleListener extends AnalysisEventListener<ImportClueSimpleVO> {

    /**
     * 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
     */


    private static final int BATCH_COUNT = 3000;
    List<ClueImportTemporaryDO> list = new ArrayList<>();
    private static int count = 1;

    @Override
    public void invoke(ImportClueSimpleVO data, AnalysisContext context) {
        try {
            boolean isNullObject = BeanUtils.checkFieldAllNull(data);
            if(isNullObject){
                return;
            }
        } catch (IllegalAccessException e) {
            log.error("invoke 判空异常:{}",data,e);
        }
        ClueImportTemporaryDO clue = BeanUtils.convert(data, ClueImportTemporaryDO.class);
        String errorRemark = String.format("时间：%s 不符合标准，请输入标准格式yyyy-MM-dd HH:mm:dd;", data.getClueCollectionTime());
        if (StringUtils.isBlank(data.getClueCollectionTime())) {
            clue.setImportState(ImportStateEnum.FAIL.getType());
            clue.setRemark("线索收集日期为空");
            list.add(clue);
            return;
        }
        // 判断时间格式
        String timeStr = ExcelUtils.convertTime(data.getClueCollectionTime());
        clue.setImportState(ImportStateEnum.SUCCESS.getType());
        if (StringUtils.isBlank(timeStr)) {
            // 时间格式不符合，记录错误日志
            data.setErrorRemark(errorRemark);
            clue.setImportState(ImportStateEnum.FAIL.getType());
        } else {
            data.setClueCollectionTime(timeStr);
            boolean matches = Pattern.matches(DateUtils.TIME_PATTERN, timeStr);
            if (!matches) {
                data.setErrorRemark(errorRemark);
                clue.setImportState(ImportStateEnum.FAIL.getType());
            } else {
                SimpleDateFormat sdf = new SimpleDateFormat(DateUtils.DATE_TIME_PATTERN_SLASH);
                try {
                    clue.setClueCollectionTime(sdf.parse(timeStr).toInstant().atZone(ZoneOffset.ofHours(8)).toLocalDateTime());
                } catch (ParseException e) {
                    data.setErrorRemark(errorRemark);
                    clue.setImportState(ImportStateEnum.FAIL.getType());
                    log.error("导入时间转化错误：时间：{},错误：{}", data.getClueCollectionTime(), e.getMessage());
                }
            }
        }
        clue.setSourceChannel(Objects.nonNull(SourceChannelEnum.getByDesc(data.getInteractionSource())) ?
                SourceChannelEnum.getByDesc(data.getInteractionSource()).getCode() : null);
        clue.setRemark(StringUtils.isNotBlank(data.getErrorRemark()) ? data.getErrorRemark() : "");
        clue.setClueCollectionTimeStr(data.getClueCollectionTime());
        list.add(clue);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        saveData(count);
        System.out.println("所有数据解析完成！");
        System.out.println(" count ：" + count);
    }

    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        if (headMap.size() != 13 || !headMap.values().contains("客户姓名")) {
            throw new DccCrmException(ErrorCodeEnum.SIMPLE_TEMPORARY_ERROR);
        }
    }

    /**
     * 加上存储数据库
     */
    private void saveData(int count) {
        System.out.println("存储数据库成功！");
    }

    public List<ClueImportTemporaryDO> getList() {
        return this.list;
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86

前端轮询查看导入结果接口

controller层

    @ApiOperation(value = "1.0版本-根据批次号获取导入结果")
    @GetMapping("/getResult")
    public Result<ImportClueEchoVO> getResult(@RequestParam String batchNo) {
        try {
            ImportClueEchoVO importClueEchoVO = clueImportService.getResult(batchNo);
            return Result.success(importClueEchoVO);
        } catch (Exception e) {
            log.error("error_getResult", e);
            return Result.fail("500", e.getMessage());
        }
    }
1
2
3
4
5
6
7
8
9
10
11

service层–clueImportService

@Override
    public ImportClueEchoVO getResult(String batchNo) {
        ImportClueEchoVO importClueEchoVO = new ImportClueEchoVO();
        List<ClueImportTemporaryDO> listAll = clueImportTemporaryMapper.selectListByBatchNo(new QueryClueParam().setBatchNo(batchNo));
        List<ClueImportTemporaryDO> failList = listAll.stream().filter(x -> x.getImportState().equals(ImportStateEnum.FAIL.getType())).collect(Collectors.toList());
        //合并redis中的失败数据
        ImportRedisVO bar = (ImportRedisVO) redisTemplate.opsForValue().get(ImportConstant.PROGRESS_BAR_KEY + batchNo);
        if (CollectionUtils.isNotEmpty(failList)) {
            failList.addAll(bar.getFailInsertList());
        } else {
            failList = CollectionUtils.isNotEmpty(bar.getFailInsertList()) ? bar.getFailInsertList() : Collections.EMPTY_LIST;
        }
        if (CollectionUtils.isNotEmpty(listAll)) {
            listAll.addAll(bar.getFailInsertList());
        } else {
            listAll = CollectionUtils.isNotEmpty(bar.getFailInsertList()) ? bar.getFailInsertList() : Collections.EMPTY_LIST;
        }

        //数据Mapstruct复制
        List<ImportClueVO> importClueVOS = ClueExportConvert.INSTANCE.do2ClueVoList(failList);
        importClueEchoVO.setImportClueVOS(importClueVOS);
        importClueEchoVO.setTotal(listAll.size());
        importClueEchoVO.setFailNum(failList.size());
        importClueEchoVO.setSuccessNum(importClueEchoVO.getTotal() - failList.size());
        importClueEchoVO.setBatchNo(batchNo);
        //从redis中获取重复数
        importClueEchoVO.setRepeatNum(bar.getRepeatNum());
        return importClueEchoVO;
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

前端轮询查看导入进度条接口

controller层

    @ApiOperation(value = "1.0版本-根据批次号获取进度条信息")
    @GetMapping("/getProcessBar")
    public Result<ImportRedisVO> getProcessBar(@RequestParam String batchNo) {
        try {
            ImportRedisVO bar = (ImportRedisVO) redisTemplate.opsForValue().get(ImportConstant.PROGRESS_BAR_KEY + batchNo);
            return Result.success(bar);
        } catch (Exception e) {
            log.error("error_getResult", e);
            return Result.fail("500", e.getMessage());
        }
    }
1
2
3
4
5
6
7
8
9
10
11

确认导入–从临时表落库

controller层

    @ApiOperation(value = "客户信息-确认导入")
    @PostMapping("/confirmImport")
    public Result confirmCustomerImport(@RequestParam("batchUid") String batchUid, @RequestParam(value = "addClueFlag", required = false) Integer addClueFlag) {
        return Result.success(customerService.confirmCustomerImport(batchUid, addClueFlag));
    }
1
2
3
4
5

service层

    @Override
    @Transactional(rollbackFor = Exception.class)
    public List<ImportCustomerVO> confirmCustomerImport(String batchUid, Integer addClueFlag) {
        //1,通过批次号获取临时表数据
        List<CustomerImportTemporaryDO> customerImportTemporaryDOList = customerImportTemporaryDao.queryCustomerImportTemporaraByBatchUid(batchUid);
        if (CollectionUtils.isEmpty(customerImportTemporaryDOList)) {
            return new ArrayList<>();
        }
        //2,组装线索接口需要的数据  待修改
        //数据库customer转map
        Map<String, CustomerDO> customerDOMap = new HashMap<>();
        //数据库customer存在的code
        List<String> customerCodes = new ArrayList<>();
        List<String> phones = customerImportTemporaryDOList.stream().map(CustomerImportTemporaryDO::getPhone).collect(Collectors.toList());
        getCustomerInfo(phones, customerDOMap, customerCodes);

        //临时表数据分组
        Map<String, List<CustomerImportTemporaryDO>> customerImportTemporaryDOMap = customerImportTemporaryDOList.stream()
                .sorted(Comparator.comparing(CustomerImportTemporaryDO::getId))
                .collect(Collectors.groupingBy(CustomerImportTemporaryDO::getPhone, LinkedHashMap::new, Collectors.toList()));
        //意向周期分组
        List<Integer> cycleIds = new ArrayList<>();
        Map<String, CustomerIntentionCycleDO> customerIntentionCycleDOMap = getCustomerIntentionCycleDOMap(customerCodes, cycleIds);

        //意向车型
        Map<Integer, CustomerIntentionCarDO> customerIntentionCarDOMap = new HashMap<>();
        if (CollectionUtils.isNotEmpty(cycleIds)) {
            List<CustomerIntentionCarDO> customerIntentionCarDOS = customerIntentionCarDao.getByCycleIds(cycleIds);
            customerIntentionCarDOMap = customerIntentionCarDOS.stream().collect(Collectors.toMap(CustomerIntentionCarDO::getCycleId, Function.identity(), (o, n) -> n));
        }

        //跟进
        Map<Integer, List<CustomerFollowUpDO>> customerFollowUpDOMap;
        if (CollectionUtils.isNotEmpty(cycleIds)) {
            List<CustomerFollowUpDO> customerFollowUpDOS = customerFollowUpDao.queryByCycleIds(cycleIds);
            customerFollowUpDOMap = customerFollowUpDOS.stream().collect(Collectors.groupingBy(CustomerFollowUpDO::getCycleId));
        } else {
            customerFollowUpDOMap = new HashMap<>();
        }

        List<CustomerImportTemporaryDO> customerImportTemporaryDOListAdd = new ArrayList<>();
        customerImportTemporaryDOMap.forEach((phone, customerImportTemporaryDOS) -> {
            CustomerDO customerDO = customerDOMap.get(phone);
            CustomerImportTemporaryDO customerImportTemporaryDO = customerImportTemporaryDOS.get(customerImportTemporaryDOS.size() - 1);
            List<CustomerImportTemporaryDO> stroeList = customerImportTemporaryDOS.stream().
                    filter(item -> StringUtils.isNotBlank(item.getDeliverTheStoreCode())).sorted(Comparator.comparing(CustomerImportTemporaryDO::getId)).collect(Collectors.toList());
            if (customerDO == null) {
//                String storeCode = customerImportTemporaryDO.getDeliverTheStoreCode();
                if (CollectionUtils.isNotEmpty(stroeList)) {
                    customerImportTemporaryDOListAdd.add(stroeList.get(stroeList.size() - 1));
                }
            } else {
                if (customerDO.getIssuedFlag() == 0) {
                    if (CollectionUtils.isNotEmpty(stroeList)) {
                        customerImportTemporaryDOListAdd.add(stroeList.get(stroeList.size() - 1));
                    }
                }
            }
        });
        if (CollectionUtils.isNotEmpty(customerImportTemporaryDOListAdd)) {
            List<AddClueRequest> clues = convertClueData(customerImportTemporaryDOListAdd);
            //3,调用线索dubbo接口
            if (addClueFlag == null || 1 != addClueFlag) {//传入1时 不调线索
                clueSPI.batchAddClue(clues);
            }
        }
        //4，数据处理到新表里
        List<Integer> ids = customerImportTemporaryDOList.stream().map(CustomerImportTemporaryDO::getId).collect(Collectors.toList());
        if (CollectionUtils.isNotEmpty(ids)) {
            customerImportTemporaryMapper.updateIsImport(ids);
        }
        customerServiceUtil.sureCustomersByImport(customerImportTemporaryDOMap, customerDOMap,
                customerIntentionCycleDOMap, customerIntentionCarDOMap, customerFollowUpDOMap, AuthNHolder.userId(), AuthNHolder.userName(), batchUid);
        return BeanUtils.convert(customerImportTemporaryDOList, ImportCustomerVO.class);
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/花生_TL007/article/detail/496075