EasyExcel导出工具类

yuanxl 1年前 ⋅ 846 阅读
public String uploadFileByTemplateWithEasyExcel(String path, String name, EnumCollectionBizCode bizCode,
        Map<Integer, List<Object>> dataMap, Map<Integer, Map<String, Object>> objMap) {
        MemoryMXBean memoryMXBean = ManagementFactory.getMemoryMXBean();
        //椎内存使用情况
        MemoryUsage memoryUsageBegin = memoryMXBean.getHeapMemoryUsage();
        long start = System.currentTimeMillis();
        log.info("开始导出excel,start:{},内存已用:{}", start, memoryUsageBegin);
        List<TemplateConfigBO> templateList = templateCore.queryConfig(bizCode.getCode());
        FileProcessService fileProcessService = fileProcessServiceFactory.getInstance(AppParamUtil.getTenantId());
        if (CollectionUtils.isEmpty(templateList)) {
            throw new MyBizException(EnumCollectionErrorCode.EXPORT_FILE_ERROR.getCode(), "模板不存在!");
        }
        String tempUrl = templateList.get(0)
            .getTemplateUrl();
        File tmpFile = new File(System.currentTimeMillis() + ".xlsx");
        File tmpTempleteFile = new File(System.currentTimeMillis() + "模板.xlsx");
        try (InputStream is = POICacheManager.getFile(tempUrl)) {
            if (Objects.isNull(is)) {
                throw new MyBizException(EnumCollectionErrorCode.GET_URL_TIMEOUT.getCode(),
                    EnumCollectionErrorCode.GET_URL_TIMEOUT.getDescription());
            }
            FileUtils.writeByteArrayToFile(tmpTempleteFile, toByteArray(is));
            WriteSheet writeSheet = EasyExcel.writerSheet()
                .build();
            ExcelWriter excelWriter = EasyExcel.write(tmpFile)
                .withTemplate(tmpTempleteFile)
                .build();
            dataMap.keySet()
                .stream()
                .forEach(x -> {
                    List<Object> objects = dataMap.get(x);
                    writeSheet.setSheetNo(x);
                    excelWriter.fill(objects, writeSheet);
                    if (Objects.nonNull(objMap) && CollectionUtil.isNotEmpty(objMap.get(x))) {
                        excelWriter.fill(objMap.get(x), writeSheet);
                    }
                    MemoryUsage mem = memoryMXBean.getHeapMemoryUsage();
                    log.info("处理第{}个sheet页,内存已占用情况:{}", x + 1, mem);
                });
            excelWriter.finish();
            String s = fileProcessService.uploadFile(path + File.separator + name,
                FileUtils.readFileToByteArray(tmpFile));
            log.info("文件路径:{}", s);
            return s;
        } catch (IOException e) {
            log.error(e.getMessage(), e);
            throw new MyBizException(EnumCollectionErrorCode.EXPORT_FILE_ERROR.getCode(), "导出失败!");
        } finally {
            long end = System.currentTimeMillis();
            //椎内存使用情况
            MemoryUsage memoryUsagEend = memoryMXBean.getHeapMemoryUsage();
            log.info("开始导出excel,end:{},内存已用:{}", end, memoryUsagEend);
            //删除临时文件
            log.info("删除临时文件:{},结果:{}", tmpFile.getAbsoluteFile(), tmpFile.delete());
            log.info("删除临时模板:{},结果:{}", tmpTempleteFile.getAbsoluteFile(), tmpTempleteFile.delete());
            BigDecimal seconds = (new BigDecimal(end).subtract(new BigDecimal(start))).divide(new BigDecimal(1000));
            log.info("==========根据模板:{}导出,用时:[{}]秒==========", bizCode.getDescription(), seconds);
        }
    }
    /**
     * 初始化zip上传
     *
     * @param bizCode
     */
    public void initUploadZip(EnumCollectionBizCode bizCode) {
        String tmpName = getTemplateTmpPath(bizCode) + bizCode.getCode() + EXCEL_SUFFIX;
        File file = new File(tmpName);
        List<TemplateConfigBO> templateList = templateCore.queryConfig(bizCode.getCode());
        FileProcessService fileProcessService = fileProcessServiceFactory.getInstance(AppParamUtil.getTenantId());
        if (CollectionUtils.isEmpty(templateList)) {
            throw new MyBizException(EnumCollectionErrorCode.EXPORT_FILE_ERROR.getCode(), "模板不存在!");
        }
        String tempUrl = templateList.get(0)
            .getTemplateUrl();
        try (InputStream is = POICacheManager.getFile(tempUrl)) {
            if (Objects.isNull(is)) {
                throw new MyBizException(EnumCollectionErrorCode.GET_URL_TIMEOUT.getCode(),
                    EnumCollectionErrorCode.GET_URL_TIMEOUT.getDescription());
            }
            FileUtils.writeByteArrayToFile(file, toByteArray(is));
            loaclTempleteFileMap.put(AppParamUtil.getOperatorId() + bizCode.getCode(), file);
            log.info("模板:{}缓存成功,路径{}", bizCode.getDescription(), file.getAbsolutePath());
        } catch (IOException e) {
            log.error(e.getMessage(), e);
            throw new MyBizException(EnumCollectionErrorCode.GET_URL_TIMEOUT.getCode(), "模板文件缓存失败");
        }
    }
    /**
     * 使用easyExcel根据模板导出文档
     * 将文档暂存到本地,调用本方法需要配合@buildZipFile使用,否则会造成本地磁盘脏数据
     *
     * @param name 生成文件的文件名
     * @param dataMap Map<sheetNum,List<data>>
     * @return
     */
    public void uploadFileToLoacalByTemplateWithEasyExcel(String name, EnumCollectionBizCode bizCode,
        Map<Integer, List<Object>> dataMap, Map<Integer, Map<String, Object>> objMap) {
        long start = System.currentTimeMillis();
        File template = loaclTempleteFileMap.get(AppParamUtil.getOperatorId() + bizCode.getCode());
        File loacalFile = new File(getExcelTmpPath(bizCode) + name);
        try {
            File parentFile = loacalFile.getParentFile();
            if (!parentFile.exists()) {
                FileUtils.forceMkdirParent(loacalFile);
            }
        } catch (IOException e) {
            log.error(e.getMessage(), e);
            throw new MyBizException(EnumCollectionErrorCode.GET_URL_TIMEOUT.getCode(), "创建父级目录失败!");
        }
        if (Objects.isNull(template)) {
            throw new MyBizException(EnumCollectionErrorCode.GET_URL_TIMEOUT.getCode(),
                "本地模板文件不存在,请调用initUploadZip方法!");
        }
        WriteSheet writeSheet = EasyExcel.writerSheet()
            .build();
        ExcelWriter excelWriter = EasyExcel.write(loacalFile)
            .withTemplate(template)
            .build();
        dataMap.keySet()
            .stream()
            .forEach(x -> {
                List<Object> objects = dataMap.get(x);
                writeSheet.setSheetNo(x);
                excelWriter.fill(objects, writeSheet);
                if (Objects.nonNull(objMap) && CollectionUtil.isNotEmpty(objMap.get(x))) {
                    excelWriter.fill(objMap.get(x), writeSheet);
                }
            });
        excelWriter.finish();
        log.info("文件:{}本地缓存成功,路径:{}", name, loacalFile.getAbsolutePath());
        long end = System.currentTimeMillis();
        BigDecimal seconds = (new BigDecimal(end).subtract(new BigDecimal(start))).divide(new BigDecimal(1000));
        log.info("==========根据模板:{}导出文件:{},用时:[{}]秒==========", bizCode.getDescription(), name, seconds);
    }
    /**
     * @param ftpPath ftp路径
     * @param name zip文件名
     * @return
     */
    public String buildZipFileAndUpload(EnumCollectionBizCode bizCode, String ftpPath, String name) {
        String loacalFilePath = getExcelTmpPath(bizCode);
        String loacalZipFile = getZipTmpPath(bizCode);
        File zipFile = ZipUtil.zip(loacalFilePath, loacalZipFile + name);
        //ZipUtil.fileToZip(loacalFilePath, loacalZipFile, name);
        //File zipFile = new File(loacalZipFile + name);
        log.info("压缩文件生成成功,文件路径:{}", zipFile.getAbsolutePath());
        FileProcessService fileProcessService = fileProcessServiceFactory.getInstance(AppParamUtil.getTenantId());
        try {
            byte[] bytes = FileUtils.readFileToByteArray(zipFile);
            String s = fileProcessService.uploadFile(ftpPath + name, bytes);
            log.info("文件路径:{}", s);
            return s;
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            throw new MyBizException(EnumCollectionErrorCode.GET_URL_TIMEOUT.getCode(), "上传失败!");
        } finally {
            //移除内存缓存
            loaclTempleteFileMap.remove(AppParamUtil.getOperatorId() + bizCode.getCode());
            //删除excel缓存文件和文件夹
            boolean delTmpExcel = FileUtil.del(loacalFilePath);
            log.info("删除临时excel文件及其路径{},结果:{}", loacalFilePath, delTmpExcel);
            //删除模板缓存文件和文件夹
            String tmpTemplate = getTemplateTmpPath(bizCode);
            boolean del = FileUtil.del(tmpTemplate);
            log.info("删除临时excel导出模板[{}],结果:{}", bizCode.getCode(), del);
            //删除zip文件
            boolean zipTmp = FileUtil.del(loacalZipFile);
            log.info("删除临时zip文件极其路径{},结果:{}", loacalZipFile, zipTmp);
        }
    }
    /**
     * 获取操作员对应的临时excel文件空间
     *
     * @param bizCode
     * @return
     */
    private String getExcelTmpPath(EnumCollectionBizCode bizCode) {
        return MessageFormat.format(LOCAL_FILE_PATH, AppParamUtil.getOperatorId(), bizCode);
    }
    /**
     * 获取操作员对应的临时tmplate模板文件空间
     *
     * @param bizCode
     * @return
     */
    private String getTemplateTmpPath(EnumCollectionBizCode bizCode) {
        return MessageFormat.format(LOCAL_TMP_PATH, AppParamUtil.getOperatorId(), bizCode);
    }
    /**
     * 获取操作员对应的临时zip文件空间
     *
     * @param bizCode
     * @return
     */
    private String getZipTmpPath(EnumCollectionBizCode bizCode) {
        return MessageFormat.format(LOCAL_ZIP_PATH, AppParamUtil.getOperatorId(), bizCode);
    }
    public static byte[] toByteArray(InputStream input) throws IOException {
        ByteArrayOutputStream output = new ByteArrayOutputStream();
        byte[] buffer = new byte[4096];
        int n = 0;
        while (-1 != (n = input.read(buffer))) {
            output.write(buffer, 0, n);
        }
        return output.toByteArray();
    }

ddd

全部评论: 0

    我有话说: