java导入excel表格，并赋上对应的值

最新推荐文章于 2023-02-22 15:54:53 发布

置顶 Cardiac ゜

最新推荐文章于 2023-02-22 15:54:53 发布

阅读量559

点赞数

文章标签：导入excel 简单

本文链接：https://blog.csdn.net/qq_43722564/article/details/92760712

版权

//前台先上传excel到服务器  再获取上传到服务器excel的URL 创建输入流 并通过URL打开流
        SysOssEntity ossEntity = sysOssService.selectById(storeRecordsEntity.getOssId());
        URL url = new URL(ossEntity.getUrl());
        InputStream inputStream = url.openStream();
        XSSFWorkbook wb0 = new XSSFWorkbook(inputStream);
        Sheet sht0 = wb0.getSheetAt(0);
        List<StoreRecordsEntity> temp = new ArrayList();

        String[] headers = {"仓库编号", "品名", "单位", "编号", "分类", "仓库名", "库存"};
        IEExcelUtils.checkXSSFHeader(headers,wb0);

        //非法数据品名
        List<String> illegalDataProductNames = new ArrayList<>();
        //对Sheet中的每一行进行迭代
        for (Row r : sht0) {
            //如果当前行的行号（从0开始）未达到2（第三行）则从新循环
            if (r.getRowNum() < 1) {
                continue;
            }
            StoreRecordsEntity recordsEntity = new StoreRecordsEntity();
            //仓库编号为空，跳过
            if (r.getCell(0)==null){
                illegalDataProductNames.add(r.getCell(1).getStringCellValue());
                continue;
            }
            r.getCell(0).setCellType(CellType.STRING);
            recordsEntity.setRepertoryId(Long.valueOf(r.getCell(0).getStringCellValue()));
            recordsEntity.setProductName(r.getCell(1).getStringCellValue());
            recordsEntity.setUnitName(r.getCell(2).getStringCellValue());
            //编号为空，跳过
            if (r.getCell(3)==null){
                illegalDataProductNames.add(r.getCell(1).getStringCellValue());
                continue;
            }
            r.getCell(3).setCellType(CellType.STRING);
            recordsEntity.setProductNo(r.getCell(3).getStringCellValue());
            recordsEntity.setCategoryName(r.getCell(4).getStringCellValue());
            recordsEntity.setRepertoryName(r.getCell(5).getStringCellValue());
            //库存为空，跳过
            if (r.getCell(6)==null){
                illegalDataProductNames.add(r.getCell(1).getStringCellValue());
                continue;
            }
            r.getCell(6).setCellType(CellType.STRING);
            BigDecimal a = new BigDecimal(r.getCell(6).getStringCellValue());
            recordsEntity.setCurrentNums(a);
            Map<String, Object> params = new HashMap<>();
            params.put("productNo", recordsEntity.getProductNo());
            params.put("repertory_id", recordsEntity.getRepertoryId());
            StoreRecordsEntity recordsEntity1 = recordsService.queryInventory2(params);
            //成本价
            BigDecimal unitPrice = BigDecimalUtils.div(BigDecimal.ROUND_HALF_UP,recordsEntity1.getTotalPrice(),recordsEntity1.getUnitNums());
            recordsEntity.setUnitPrice(unitPrice);
            //数据有缺失，直接跳过
            if (recordsEntity1==null){
                illegalDataProductNames.add(r.getCell(1).getStringCellValue());
                continue;
            }
            recordsEntity.setProductId(recordsEntity1.getProductId());
            recordsEntity.setRepertoryId(recordsEntity1.getRepertoryId());
            recordsEntity.setUnitId(recordsEntity1.getUnitId());
            temp.add(recordsEntity);
        }
        /*
         * 获取所有包含 productName、productNo、unit_nums 的StoreRecordsEntity集合
         * 遍历temp 根据productNo查询storeRecordsEntities里对应的数据
         * 并且equals productName是否一样
         * 把对应数据的unit_nums设置给originalQuantity（原数量）
         */
        List<StoreRecordsEntity> storeRecordsEntities = recordsService.queryInventoryNumsAll();
        temp.forEach(list -> {
            storeRecordsEntities.forEach(entity -> {
                if (list.getProductNo().equals(entity.getProductNo()) && list.getProductName().equals(entity.getProductName())) {
                    list.setOriginalQuantity(entity.getUnitNums());
                }
            });
        });
        inputStream.close();
        //如果记录的非法数据大于0，即代表有非法数据，则返回这个。没有非法数据就不需要返回
        return R.ok(illegalDataProductNames.size()>0?illegalDataProductNames.toString()+"因数据非法被去除":"导入成功").put("list", temp);