使用 easyexcel

使用easyexcel

1. pom引入依赖

     <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>easyexcel</artifactId>
                <version>2.1.6</version>
     </dependency>

2. Excel文件抽取及写入代码

   /**
     * 从 excel文件 抽取数据
     */
    public List<ExcelCouponDTO> getCheckDataFromExcel(String fileUrl) {
        List<ExcelCouponDTO> couponCheckDTOList = new LinkedList<>();
        Workbook workbook;
        try (InputStream is = getUrl(fileUrl).openStream()) {
            // 非excel文件则抛出异常
            if (!fileUrl.endsWith(ExcelTypeEnum.XLS.getValue())
                    && !fileUrl.endsWith(ExcelTypeEnum.XLSX.getValue())) {
                throw new ClientViewException("上传文件不是Excel类型文件，请检查");
            }
            if (fileUrl.endsWith(ExcelTypeEnum.XLS.getValue())) {
                workbook = new HSSFWorkbook(is);
            } else {
                workbook = new XSSFWorkbook(is);
            }
            int sheetCount = workbook.getNumberOfSheets();
            if (sheetCount == 0) {
                throw new com.bilibili.mall.common.exception.ClientViewException("文件为空!");
            }
            Sheet sheet = workbook.getSheetAt(0);
            int rowCount = sheet.getLastRowNum();
            if (rowCount < 1) {
                throw new com.bilibili.mall.common.exception.ClientViewException("文件内数据为空！");
            }
            // 从第一行读取
            for (int i = 1; i <= rowCount; i++) {
                Row row = sheet.getRow(i);
                if (row == null) {
                    continue;
                }
                int cellCount = row.getFirstCellNum();
                if (cellCount < 0) {
                    continue;
                }
                ExcelCouponDTO couponCheckDTO = new ExcelCouponDTO();
                couponCheckDTO.setType(getCellFormatValue(row.getCell(0)));
                couponCheckDTOList.add(couponCheckDTO);
            }
        } catch (IOException e) {
            logger.error("读取 excel 文件内容错误", e);
        }
        return couponCheckDTOList;
    }
   public URL getUrl(String fileUrl) {
        URL url;
        try {
            fileUrl = fileUrl.startsWith("//") ? "http:" + fileUrl : fileUrl;
            url = new URL(fileUrl);
        } catch (Exception e) {
            throw new com.bilibili.mall.common.exception.ClientViewException(com.bilibili.mall.common.enums.ErrorCode.ILLEGAL_PARAM.getCode(), com.bilibili.mall.common.enums.ErrorCode.ILLEGAL_PARAM.getMessage());
        }
        return url;
    }
    public static String getCellFormatValue(Cell cell) {
        String cellValue;
        if (cell != null) {
            // 判断cell类型
            switch (cell.getCellTypeEnum()) {
                case NUMERIC:
                    double doubleVal = cell.getNumericCellValue();
                    long longVal = Math.round(cell.getNumericCellValue());
                    if (Double.parseDouble(longVal + ".0") == doubleVal) {
                        cellValue = String.valueOf(longVal);
                    } else {
                        cellValue = BigDecimal.valueOf(doubleVal).toPlainString();
                    }
                    break;
                case FORMULA:
                    // 判断cell是否为日期格式
                    if (DateUtil.isCellDateFormatted(cell)) {
                        // 转换为日期格式YYYY-mm-dd
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                        cellValue = sdf.format(cell.getDateCellValue());
                    } else {
                        // 数字
                        cellValue = String.valueOf(cell.getNumericCellValue());
                    }
                    break;
                case STRING:
                    cellValue = cell.getRichStringCellValue().getString();
                    break;
                default:
                    cellValue = "";
            }
        } else {
            cellValue = "";
        }
        return cellValue;
    }
    /**
     * 批量创券文件生成
     */
    @Async
    public String createMultiCouponFile(List<ExcelCouponDTO> couponCheckDTOList) {
        // 1. 构建文件名称
        String fileName = "批量优惠券信息-" + System.currentTimeMillis() + ".xlsx";
        String fileFullName = localFilePath + File.separator + fileName;
        // 2. 构建文件内容
        List<ExcelUtils.MultiCouponMode> bodyDataList = new ArrayList<>();
        for (ExcelCouponDTO couponCheckDTO : couponCheckDTOList) {
            ExcelUtils.MultiCouponMode multiCouponMode = new ExcelUtils.MultiCouponMode();
            BeanUtils.copyProperties(couponCheckDTO, multiCouponMode);
            if (Boolean.TRUE.equals(couponCheckDTO.getUploadStatus())) {
                multiCouponMode.setUploadStatus("上传成功");
            } else {
                multiCouponMode.setUploadStatus("上传失败");
            }
            if (couponCheckDTO.getCreateStatus() == null) {
                multiCouponMode.setCreateStatus("未创建");
            } else if (Boolean.TRUE.equals(couponCheckDTO.getCreateStatus())) {
                multiCouponMode.setCreateStatus("创建成功");
            } else {
                multiCouponMode.setCreateStatus("创建失败");
            }
            bodyDataList.add(multiCouponMode);
        }
        // 3. 创建文件
        try {
            ExcelUtils.createMultiCouponFile(fileFullName, bodyDataList);
        } catch (Exception e) {
            logger.error(" createMultiCouponFile 文件创建失败", e);
        }
        // 4. 上传文件
        File file = new File(fileFullName);
        try {
            amazonS3.putObject(bucket, fileName, file);
            // 5. 删除本地文件
            logger.info(" createMultiCouponFile 删除本地文件是否成功 {} {}", fileName, file.delete());
        } catch (SdkClientException e) {
            logger.error("createMultiCouponFile 上传文件失败", e);
        }
        return asyncSendAssetHandler.downloadUrl(fileName);
    }

      @Data
    @NoArgsConstructor
    @AllArgsConstructor
    public static class MultiCouponMode {

        @ExcelProperty(value = "权益/资产类型", index = 0)
        private String type;

        @ExcelProperty(value = "优惠券名称", index = 1)
        private String couponName;
        }

    public static void createMultiCouponFile(String fileName, List<MultiCouponMode> multiCouponModes) throws Exception {
        createIfNecessary(fileName);
        ExcelWriter excelWriter = EasyExcelFactory.write()
                .head(MultiCouponMode.class)
                .file(fileName)
                .autoCloseStream(true)
                .build();
        excelWriter.write(multiCouponModes, EasyExcelFactory.writerSheet(1, "批量创券").build());
        excelWriter.finish();
    }

        private static void createIfNecessary(String fileName) throws Exception {
        File file = new File(fileName);
        if (!file.exists()) {
            file.getParentFile().mkdirs();
            if (!file.createNewFile()) {
                log.warn("create file failed");
            }
        }
    }