easyExcel导入实现

taiguolaotu

已于 2023-11-03 11:52:35 修改

阅读量122

点赞数

分类专栏： EasyPoi 文章标签： java

于 2023-11-03 11:52:06 首次发布

本文链接：https://blog.csdn.net/taiguolaotu/article/details/134200016

版权

EasyPoi 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

具体上代码，我这里写的只是冰上一脚,具体大家可以看网上的实现。
依赖

  <!--导入依赖-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.11</version>
        </dependency>
        
        <!--下面这个依赖主要是实现
        private List<DemoData> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
        newArrayListWithExpectedSize这个方法。-->
        
        <dependency>
            <groupId>com.google.guava</groupId>
            <artifactId>guava</artifactId>
            <version>30.1-jre</version>
        </dependency>

代码

	/**
	 * 导入商品
	 */
	@ApiOperation("导入商品")
	@PostMapping("importGoodsInfo")
	public List<String> importGoodsInfo(@RequestParam("file") MultipartFile file) {
		return goodsInfoService.importGoodsInfo(file);
	}

 /**
     * 导入商品
     * @param file
     */
    @Override
    @Transactional(rollbackFor = Exception.class)
    public List<String> importGoodsInfo(MultipartFile file) {
        String accountId = LoginUtils.getLoginAccountId();
        log.info("GoodsInfoServiceImpl.importGoodsInfo; 导入商品开始,操作人: {};", accountId);

        // 检查文件类型
        if (!Objects.requireNonNull(file.getOriginalFilename()).endsWith(GoodsConstants.FORMAT)) {
            throw new BusinessException(GoodsResultCode.NOT_SUPPORT_FORMAT, GoodsConstants.FORMAT);
        }
        //不可同时导入操作
        Optional<RLock> opLock = Optional.empty();
        ExcelResp excelResp = new ExcelResp();
        try {
            opLock = redisLockUtils.tryLock(GoodsConstants.MANAGER_ADD_GOODS, BasicConstants.CACHE_TIME_3, TimeUnit.MINUTES);
            opLock.orElseThrow(() -> new BusinessException(GoodsResultCode.OPERATE_FREQUENTLY));

            //获取所有数据
            List<ExcelDataReq> lineImportDtoList = EasyExcel.read(file.getInputStream()).head(ExcelDataReq.class).sheet().doReadSync();
            if (lineImportDtoList.size() <= YnEnums.INVALID.getCode()) {
                throw new BusinessException(GoodsResultCode.IMPORT_DATA_SIZE, YnEnums.INVALID.getCode());
            }
            //数据库缓存查询excel单次最大数据导入量
            int configValue = platformConfigExport.getConfigInteger(PlatformConfigEnum.MAX_BATCH_COUNT);
            if (lineImportDtoList.size() > configValue) {
                throw new BusinessException(GoodsResultCode.MAX_ROW_NUMBER, configValue);
            }
            //校验数据
            excelResp = verifyData(lineImportDtoList);
            if (CollectionUtils.isNotEmpty(excelResp.getExcelDataReqs())) {
                saveData(lineImportDtoList);
            }
        } catch (Exception e) {
            //不进行throw
            log.error("GoodsInfoServiceImpl.importGoodsInfo: 商品导入失败: ", e);
        } finally {
            // 解锁
            redisLockUtils.unLock(opLock);
        }
        return excelResp.getErrorList();
    }

 /**
     * 校验所有数据
     */
    private ExcelResp verifyData(List<ExcelDataReq> excelDataReqs) {
        ExcelResp excelResp = new ExcelResp();
        List<String> errorList = new ArrayList<>();
        List<ExcelDataReq> excelDataReqDelete = excelDataReqs;

        //查询一二三级供应商,品牌类别都不为空的数据
        List<ExcelDataReq> excelDataNotNull = excelDataReqs.stream().filter(e -> StringUtils.isNotBlank(e.getFirstCategoryDesc()) &&
                StringUtils.isNotBlank(e.getSecondCategoryDesc()) && StringUtils.isNotBlank(e.getThirdCategoryDesc())
                && StringUtils.isNotBlank(e.getBrandName()) && StringUtils.isNotBlank(e.getSupplierName())
        ).collect(Collectors.toList());
        if (CollectionUtils.isEmpty(excelDataNotNull)) {
            log.info("excel表格无数据");
            errorList.add("excel表格无数据");
            excelResp.setErrorList(errorList);
            return excelResp;
        }

        //校验商品编号重复//
        List<String> goodsSn = excelDataReqs.stream().map(e -> e.getGoodsSn().trim()).distinct().collect(Collectors.toList());
        List<GoodsInfo> goodsInfos = goodsInfoService.lambdaQuery().in(GoodsInfo::getGoodsSn, goodsSn).eq(GoodsInfo::getYn, YnEnums.VALID.getCode()).list();
        if (CollectionUtils.isNotEmpty(goodsInfos)) {
            List<String> goodsSnData = goodsInfos.stream().map(GoodsInfo::getGoodsSn).collect(Collectors.toList());
            errorList.add(String.format(GoodsConstants.GOODS_EXIST, String.join(",", goodsSnData)));
            //删掉已存在商品编号
            excelDataReqDelete.removeIf(next -> goodsSnData.contains(next.getGoodsSn()));
        }

        //校验一级分类//
        //校验分类是否存在,不存在不可添加  key为分类名称,value该分类名称下的商品
        //2,3,4
        Map<String, List<ExcelDataReq>> firstCategory = excelDataNotNull.stream().collect(Collectors.groupingBy(e -> e.getFirstCategoryDesc().trim(), Collectors.toList()));
        //1,2,3
        List<String> oneCateGory = goodsCategoryMapper.selectCategories(CategoryLevelEnum.ONE_LEVEL.getCode()).stream().map(GoodsCategoryDTO::getCategoryName).collect(Collectors.toList());
        if (CollectionUtils.isEmpty(oneCateGory)) {
            log.info("一级分类不存在,不可导入");
            errorList.add("一级分类不存在,不可导入");
            excelResp.setErrorList(errorList);
            return excelResp;
        }
        Set<String> firstCategoryName = firstCategory.keySet();
        List<String> oneName = firstCategoryName.stream().filter(e -> !oneCateGory.contains(e)).collect(Collectors.toList());
        //要删除的商品
        if (CollectionUtils.isNotEmpty(oneName)) {
            Map<String, List<ExcelDataReq>> collect = firstCategory.entrySet().stream()
                    .filter(entry -> {
                        String key = entry.getKey();
                        return oneName.contains(key);
                    })
                    .collect(Collectors.toMap(Map.Entry::getKey, Map.Entry::getValue));

            // 获取所有待删除的商品编号
            List<String> collect1 = collect.values().stream().flatMap(Collection::stream).map(ExcelDataReq::getGoodsSn).collect(Collectors.toList());
            //删掉已存在商品编号
            errorList.add(String.format(GoodsConstants.ONE_CATEGORY_EXIST, String.join(",", collect1)));
            excelDataReqDelete.removeIf(next -> collect1.contains(next.getGoodsSn()));
        }

        //校验二级分类//
        Map<String, List<ExcelDataReq>> secondCategory = excelDataNotNull.stream().collect(Collectors.groupingBy(e -> e.getSecondCategoryDesc().trim(), Collectors.toList()));
        List<String> twoCateGory = goodsCategoryMapper.selectCategories(CategoryLevelEnum.TWO_LEVEL.getCode()).stream().map(GoodsCategoryDTO::getCategoryName).collect(Collectors.toList());
        if (CollectionUtils.isEmpty(twoCateGory)) {
            log.info("二级分类不存在,不可导入");
            errorList.add("二级分类不存在,不可导入");
            excelResp.setErrorList(errorList);
            return excelResp;
        }
        Set<String> secondCategoryName = secondCategory.keySet();
        List<String> secondName = secondCategoryName.stream().filter(e -> !twoCateGory.contains(e)).collect(Collectors.toList());
        //要删除的商品
        if (CollectionUtils.isNotEmpty(secondName)) {
            Map<String, List<ExcelDataReq>> collect = secondCategory.entrySet().stream()
                    .filter(entry -> {
                        String key = entry.getKey();
                        return secondName.contains(key);
                    })
                    .collect(Collectors.toMap(Map.Entry::getKey, Map.Entry::getValue));

            // 获取所有待删除的商品编号
            List<String> collect1 = collect.values().stream().flatMap(Collection::stream).map(ExcelDataReq::getGoodsSn).collect(Collectors.toList());
            //删掉已存在商品编号
            errorList.add(String.format(GoodsConstants.TWO_CATEGORY_EXIST, String.join(",", collect1)));
            excelDataReqDelete.removeIf(next -> collect1.contains(next.getGoodsSn()));
        }

        //校验三级分类//
        Map<String, List<ExcelDataReq>> thirdCategory = excelDataNotNull.stream().collect(Collectors.groupingBy(e -> e.getThirdCategoryDesc().trim(), Collectors.toList()));
        List<String> threeCateGory = goodsCategoryMapper.selectCategories(CategoryLevelEnum.THREE_LEVEL.getCode()).stream().map(GoodsCategoryDTO::getCategoryName).collect(Collectors.toList());
        if (CollectionUtils.isEmpty(twoCateGory)) {
            log.info("三级分类不存在,不可导入");
            errorList.add("三级分类不存在,不可导入");
            excelResp.setErrorList(errorList);
            return excelResp;
        }
        Set<String> thirdCategoryName = thirdCategory.keySet();
        List<String> thirdName = thirdCategoryName.stream().filter(e -> !threeCateGory.contains(e)).collect(Collectors.toList());
        //要删除的商品
        if (CollectionUtils.isNotEmpty(thirdName)) {
            Map<String, List<ExcelDataReq>> collect = thirdCategory.entrySet().stream()
                    .filter(entry -> {
                        String key = entry.getKey();
                        return thirdName.contains(key);
                        //return Collections.singletonList(oneName).contains(key);
                    })
                    .collect(Collectors.toMap(Map.Entry::getKey, Map.Entry::getValue));

            // 获取所有待删除的商品编号
            List<String> collect1 = collect.values().stream().flatMap(Collection::stream).map(ExcelDataReq::getGoodsSn).collect(Collectors.toList());
            //删掉已存在商品编号
            errorList.add(String.format(GoodsConstants.THREE_CATEGORY_EXIST, String.join(",", collect1)));
            excelDataReqDelete.removeIf(next -> collect1.contains(next.getGoodsSn()));
        }

        /校验品牌、供应商是否存在,不存在不可添加/
        Map<String, List<ExcelDataReq>> brandName = excelDataNotNull.stream().collect(Collectors.groupingBy(ExcelDataReq::getBrandName, Collectors.toList()));
        List<String> brandNameData = goodsBrandService.lambdaQuery().eq(GoodsBrand::getEnableStatus,YnEnums.VALID.getCode()).
                eq(GoodsBrand::getYn, YnEnums.VALID.getCode()).list().stream().map(e -> e.getBrandName().trim()).collect(Collectors.toList());
        if (CollectionUtils.isEmpty(brandNameData)) {
            log.info("品牌不存在,不可导入");
            errorList.add("品牌不存在,不可导入");
            excelResp.setErrorList(errorList);
            return excelResp;
        }
        Set<String> brandNameForm = brandName.keySet();
        List<String> brandNameFormData = brandNameForm.stream().filter(e -> !brandNameData.contains(e)).collect(Collectors.toList());
        //要删除的商品
        if (CollectionUtils.isNotEmpty(brandNameFormData)) {
            Map<String, List<ExcelDataReq>> collect = brandName.entrySet().stream()
                    .filter(entry -> {
                        String key = entry.getKey();
                        return brandNameFormData.contains(key);
                    })
                    .collect(Collectors.toMap(Map.Entry::getKey, Map.Entry::getValue));

            // 获取所有待删除的商品编号
            List<String> collect1 = collect.values().stream().flatMap(Collection::stream).map(ExcelDataReq::getGoodsSn).collect(Collectors.toList());
            //删掉已存在商品编号
            errorList.add(String.format(GoodsConstants.BRAND_NAME_EXIST, String.join(",", collect1)));
            excelDataReqDelete.removeIf(next -> collect1.contains(next.getGoodsSn()));
        }

        Map<String, List<ExcelDataReq>> supplierName = excelDataNotNull.stream().collect(Collectors.groupingBy(ExcelDataReq::getSupplierName, Collectors.toList()));
        List<String> supplierNameData = goodsSupplierService.lambdaQuery().eq(GoodsSupplier::getEnableStatus, YnEnums.VALID.getCode()).
                eq(GoodsSupplier::getYn, YnEnums.VALID.getCode()).list().stream().map(e -> e.getSupplierName().trim()).collect(Collectors.toList());
        if (CollectionUtils.isEmpty(supplierNameData)) {
            log.info("供应商不存在,不可导入");
            errorList.add("供应商不存在,不可导入");
            excelResp.setErrorList(errorList);
            return excelResp;
        }
        Set<String> supplierNameForm = supplierName.keySet();
        List<String> supplierNameFormData = supplierNameForm.stream().filter(e -> !supplierNameData.contains(e)).collect(Collectors.toList());
        //要删除的商品
        if (CollectionUtils.isNotEmpty(supplierNameFormData)) {
            Map<String, List<ExcelDataReq>> collect = supplierName.entrySet().stream()
                    .filter(entry -> {
                        String key = entry.getKey();
                        return supplierNameFormData.contains(key);
                    })
                    .collect(Collectors.toMap(Map.Entry::getKey, Map.Entry::getValue));

            // 获取所有待删除的商品编号
            List<String> collect1 = collect.values().stream().flatMap(Collection::stream).map(ExcelDataReq::getGoodsSn).collect(Collectors.toList());
            //删掉已存在商品编号
            errorList.add(String.format(GoodsConstants.SUPPLIER_EXIST, String.join(",", collect1)));
            excelDataReqDelete.removeIf(next -> collect1.contains(next.getGoodsSn()));
        }
        //数据组装
        excelResp.setErrorList(errorList);
        excelResp.setExcelDataReqs(excelDataReqDelete);
        return excelResp;
    }

 /**
     * 组装数据存储数据库
     */
    private void saveData(List<ExcelDataReq> cachedDataList) {
        log.info("{}条数据，开始存储数据库！", cachedDataList.size());
        // 品牌名称集合
        Set<String> brandNameSet = new HashSet<>();
        // 分类名称集合
        Set<String> categoryNameSet = new HashSet<>();
        // 供应商名称集合
        Set<String> supplierNameSet = new HashSet<>();
        // 获取构建商品需要的信息
        getBuildInfo(cachedDataList, brandNameSet, categoryNameSet, supplierNameSet);
        Map<String, Integer> brandNameMap = goodsBrandService.queryByName(brandNameSet);
        Map<String, Integer> categoryNameMap = iGoodsCategoryService.queryByName(categoryNameSet);
        Map<String, Integer> supplierNameMap = goodsSupplierService.queryByName(supplierNameSet);

        // 需要入库商品编号集合
        Map<String, GoodsInfo> goodsSnMap = new HashMap<>();
        // 需要入库的商品集合
        ArrayList<GoodsInfo> goodsInfoList = new ArrayList<>();
        // 商品编号库存map
        Map<String, Integer> goodsSnStockMap = cachedDataList.stream()
                .collect(Collectors.groupingBy(ExcelDataReq::getGoodsSn))
                .entrySet().stream()
                .collect(Collectors.toMap(Map.Entry::getKey,
                        entry -> entry.getValue().stream()
                                .mapToInt(ExcelDataReq::getTotalStock)
                                .sum()));
        // 构建入库信息
        for (ExcelDataReq data : cachedDataList) {
            if (!goodsSnMap.containsKey(data.getGoodsSn())) {
                // 构建入库商品信息
                GoodsInfo goodsInfo = buildGoodsInfo(brandNameMap, categoryNameMap, supplierNameMap, data, goodsSnStockMap);
                goodsInfoList.add(goodsInfo);
                goodsSnMap.put(data.getGoodsSn(), goodsInfo);
            }
        }
        log.info("GoodsInfoServiceImpl.saveData ; 开始执行入库");
        transactionTemplate.execute(action -> {
            // 入库商品
            List<List<GoodsInfo>> goodsInfoPart = Lists.partition(goodsInfoList, GoodsConstants.BATCH_COUNT);
            for (List<GoodsInfo> infos : goodsInfoPart) {
                this.saveBatch(infos);
            }
            log.info("GoodsInfoServiceImpl.saveData ; 商品集合入库完成,总入库条数:{}", goodsInfoList.size());
            // 需要入库的商品规格集合
            List<GoodsSpecification> goodsSpecifications = new ArrayList<>();
            List<GoodsStore> goodsStores = new ArrayList<>();
            // 商品规格
            HashMap<String, GoodsSpecification> goodsSpecificationMap = new HashMap<>();
            for (ExcelDataReq data : cachedDataList) {
                String goodsSn = data.getGoodsSn();
                GoodsInfo goodsInfo = goodsSnMap.get(goodsSn);
                //规格
                GoodsSpecification specification = BeanUtil.copyBean(goodsInfo, GoodsSpecification.class);
                specification.setGoodsId(String.valueOf(goodsInfo.getId()));
                specification.setLinedPrice(data.getMinLinePrice());
                specification.setInsertBaseInfo();
                specification.setYn(YnEnums.VALID.getCode());
                specification.setRealPrice(data.getRealPrice());
                goodsSpecifications.add(specification);

                String goodsSpecificationKey = goodsInfo.getGoodsSn().concat("-").concat(specification.getSpecificationName());
                goodsSpecificationMap.put(goodsSpecificationKey, specification);
                //库存
                GoodsStore store = BeanUtil.copyBean(data, GoodsStore.class);
                store.setGoodsSpecificationName(specification.getSpecificationName());
                store.setAvailableStock(store.getTotalStock());
                store.setLockedStock(0);
                store.setEnableStatus(YnEnums.VALID.getCode());
                store.setSupplierName(goodsInfo.getSupplierName());
                store.setGoodsId(String.valueOf(goodsInfo.getId()));
                store.setSupplierId(goodsInfo.getSupplierId());
                store.setYn(YnEnums.VALID.getCode());
                store.setInsertBaseInfo();
                goodsStores.add(store);

            }
            List<List<GoodsSpecification>> specificationPart = Lists.partition(goodsSpecifications, GoodsConstants.BATCH_COUNT);
            for (List<GoodsSpecification> list : specificationPart) {
                goodsSpecificationService.saveBatch(list);
                log.info("GoodsInfoServiceImpl.saveData ; 商品规格集合入库,入库条数:{}", list.size());
            }
            log.info("GoodsInfoServiceImpl.saveData ; 商品规格集合入库,总入库条数:{}", goodsSpecifications.size());
            // 更新库存对应的规格ID
            for (GoodsStore store : goodsStores) {
                String key = store.getGoodsSn().concat("-").concat(store.getGoodsSpecificationName());
                GoodsSpecification specification = goodsSpecificationMap.get(key);
                store.setGoodsSpecificationId(String.valueOf(specification.getId()));
            }
            List<List<GoodsStore>> goodsStorePart = Lists.partition(goodsStores, GoodsConstants.BATCH_COUNT);
            for (List<GoodsStore> stores : goodsStorePart) {
                goodsStoreService.saveBatch(stores);
                log.info("GoodsInfoServiceImpl.saveData ; 商品库存集合入库,入库条数:{}", stores.size());
            }
            log.info("GoodsInfoServiceImpl.saveData ; 商品库存集合入库,总入库条数:{}", goodsStores.size());

            // 更新商品表中实际售价最低的规格ID
            Map<String, GoodsSpecification> goodsRealMap = goodsSpecifications.stream()
                    .collect(Collectors.groupingBy(GoodsSpecification::getGoodsSn))
                    .entrySet().stream()
                    .collect(Collectors.toMap(Map.Entry::getKey,
                            entry -> entry.getValue().stream().min(Comparator.comparing(GoodsSpecification::getRealPrice)).get()));

            for (GoodsInfo info : goodsInfoList) {
                GoodsSpecification specification = goodsRealMap.get(info.getGoodsSn());
                if (specification != null) {
                    info.setSpecificationId(String.valueOf(specification.getId()));
                    info.setMinRealPrice(specification.getRealPrice());
                    info.setMinLinePrice(specification.getLinedPrice());
                    info.setSpecificationName(specification.getSpecificationName());
                }
            }

            for (List<GoodsInfo> infos : goodsInfoPart) {
                goodsinfomapper.batchUpdateSpecificationId(infos);
            }
            log.info("GoodsInfoServiceImpl.saveData ; 批量更新商品中规格ID完成");
            return Boolean.TRUE;
        });


    }

    private void getBuildInfo(List<ExcelDataReq> cachedDataList, Set<String> brandNameSet, Set<String> categoryNameSet, Set<String> supplierNameSet) {
        for (ExcelDataReq data : cachedDataList) {
            String brandName = data.getBrandName();
            brandNameSet.add(brandName);
            String firstCategoryDesc = data.getFirstCategoryDesc();
            categoryNameSet.add(firstCategoryDesc);
            String secondCategoryDesc = data.getSecondCategoryDesc();
            categoryNameSet.add(secondCategoryDesc);
            String thirdCategoryDesc = data.getThirdCategoryDesc();
            categoryNameSet.add(thirdCategoryDesc);
            String supplierName = data.getSupplierName();
            supplierNameSet.add(supplierName);
        }
    }

    private GoodsInfo buildGoodsInfo(Map<String, Integer> brandNameMap, Map<String, Integer> categoryNameMap, Map<String, Integer> supplierNameMap, ExcelDataReq data,Map<String, Integer> map ) {
        GoodsInfo goodsInfo = BeanUtil.copyBean(data, GoodsInfo.class);
        //商品
        if (StringUtils.isNotBlank(goodsInfo.getGoodsMasterImage())) {
            goodsInfo.setGoodsMasterImage(JSON.toJSONString(Arrays.asList(goodsInfo.getGoodsMasterImage().split(","))));
        }
        if (StringUtils.isNotBlank(goodsInfo.getRichTextUrl())) {
            goodsInfo.setRichTextUrl(JSON.toJSONString(Arrays.asList(goodsInfo.getRichTextUrl().split(","))));
        }
        //根据名称查询Id
        goodsInfo.setFirstCategoryId(String.valueOf(categoryNameMap.get(goodsInfo.getFirstCategoryDesc())));
        goodsInfo.setSecondCategoryId(String.valueOf(categoryNameMap.get(goodsInfo.getSecondCategoryDesc())));
        goodsInfo.setThirdCategoryId(String.valueOf(categoryNameMap.get(goodsInfo.getThirdCategoryDesc())));

        goodsInfo.setBrandId(String.valueOf(brandNameMap.get(goodsInfo.getBrandName())));
        goodsInfo.setSupplierId(String.valueOf(supplierNameMap.get(goodsInfo.getSupplierName())));
        goodsInfo.setInsertBaseInfo();

        //是否上下架
        goodsInfo.setIsShow(YnEnums.VALID.getCode());
        //是否置顶
        goodsInfo.setIsTop(YnEnums.INVALID.getCode());
        //禁用状态
        goodsInfo.setEnableStatus(YnEnums.VALID.getCode());
        //虚拟销量
        goodsInfo.setVirtualSaleVolume(YnEnums.INVALID.getCode());
        //实际销量
        goodsInfo.setRealSaleVolume(YnEnums.INVALID.getCode());
        //是否有库存
        goodsInfo.setStockStatus(NumberUtils.gtZero(map.get(goodsInfo.getGoodsSn())) ? YnEnums.VALID.getCode() : YnEnums.INVALID.getCode());

        return goodsInfo;
    }

期间会用到mybatis-plus

 <!--mybatis-plus 及 插件依赖-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.4.2</version>
        </dependency>
        <!--mybatis plus代码生成器-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-generator</artifactId>
            <version>3.4.1</version>
        </dependency>