easyExcel导入实现

easyExcel导入实现"/>

easyExcel导入实现

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

  <!--导入依赖--><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) {//不进行throwlog.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,4Map<String, List<ExcelDataReq>> firstCategory = excelDataNotNull.stream().collect(Collectors.groupingBy(e -> e.getFirstCategoryDesc().trim(), Collectors.toList()));//1,2,3List<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<>();// 商品编号库存mapMap<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());// 更新库存对应的规格IDfor (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());// 更新商品表中实际售价最低的规格IDMap<String, GoodsSpecification> goodsRealMap = goodsSpecifications.stream().collect(Collectors.groupingBy(GoodsSpecification::getGoodsSn)).entrySet().stream().collect(Collectors.toMap(Map.Entry::getKey,entry -> entry.getValue().stream().min(Comparatorparing(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(","))));}//根据名称查询IdgoodsInfo.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>