大数据量的Excel导入和导出

大数据量的Excel导入和导出

涉及到的技术

1. poi：使用SXSSFWorkbook，对数据进行分页且批量写入到磁盘(可以是任何outputsteam)，减少数据在内存中的存储
2. easyExcel：从磁盘上读取Excel文件，通过监听器的方式，可以读取表头，以及一行一行的数据，当满足一定条件(如1000行)，在进行业务逻辑的处理并入库，返回错误数据，并将错误数据回写到Excel中，供用户下载，让用户知道导入失败数据的失败原因。
3. groovy：扩展导入导出标题列配置，基础导入导出配置为一个被ExportExcelProperty或ImportExcelProperty注解修饰的类，而通过groovy动态生成class对象，导入导出标题列可以是任何形式的key/value对（必须是有序的，如LinkedHashMap），key为字段名称，value为Excel表格的标题。

maven依赖

compile ('com.alibaba:easyexcel:2.2.10') {
        exclude group: 'org.apache.poi'
        exclude group: 'com.alibaba', module: 'fastjson'
        exclude group: 'org.projectlombok'
        exclude group: 'org.springframework.boot'
    }
compile('org.apache.poi:poi:3.17')
compile('org.apache.poi:poi-ooxml:3.17')
compile('org.codehaus.groovy:groovy-all:2.4.13')

导入导出门面类

@Component
@Slf4j
public class MyExcelContent {

    @Resource
    private ExcelHandleTmplate excelHandleTmplate;

    /**
     * Excel导入导出线程池
     */
    private ExecutorService excelThreadPool = new ThreadPoolExecutor(5, 10, 60L,
            TimeUnit.SECONDS, new LinkedBlockingQueue(10));

    /**
     * Excel导入入口
     * @param configDto
     * @return
     */
    public ResponseMessage exelImport(ExcelImportConfigDto configDto){
        if(configDto==null || configDto.getExcelFile() == null){
            return ResponseMessage.faild("导入文件为空");
        }

        try {
            //导入Excel验证 包括Excel格式  大小 是否加密等常规验证，如果验证通过，返回一个将MultipartFile类型转换成本地文件的句柄，是一个临时文件
            File file = excelHandleTmplate.commonCheck(configDto.getExcelFile(), configDto.getMaxLineLimit());
            //生成文件名，该文件名为最后在下载管理中看到的文件名
            String fileName = getFileName(configDto.getUserId(),configDto.getOperType());
            configDto.setUpload(new FileInputStream(file));
            configDto.setFileName(fileName);
            excelThreadPool.execute(() -> {
                try {
                    log.info("导入异步处理开始,{}", configDto);
                    long startTime = System.currentTimeMillis();
                    excelHandleTmplate.importExcel(configDto, configDto.getExcelImportBusinessHandle()::handleBusiness);
                    long endTime = System.currentTimeMillis();
                    log.info("导入异步处理完成,{},耗时:{}ms", configDto, endTime - startTime);
                } catch (Exception e) {
                    log.info("导入异步处理异常,{}", configDto, e);
                }finally {
                    file.delete();
                }
            });
        }catch (BusinessException e) {
            log.error("导入异常!", e);
            return ResponseMessage.faild(e.getMessage());
        } catch (Exception e){
            return ResponseMessage.faild("导入异常!");
        }
        return ResponseMessage.success(null, "导入成功,请稍后在下载查询查看导入结果!");
    }

    /**
     * Excel导出入口
     * @param configDto
     * @return
     */
    public ResponseMessage exelExport(ExcelExportConfigDto configDto){
        //获取最大导出量  如果有配置 则取配置 如果没有 则取默认
        long count = ExcelExportBusinessHandle.MAX_EXCEL_EXPORT_LINES_LIMIT;

        // 限制导出数据量
        if (configDto.getExcelExportBusinessHandle().getCount() > count) {
            return ResponseMessage.faild("总量超过" + count + "，不支持导出");
        }

        //生成文件名，该文件名为最后在下载管理中看到的文件名
        String fileName = getFileName(configDto.getUserId(),configDto.getOperType());
        configDto.setFileName(fileName);
        try {
            excelThreadPool.execute(() -> {
                try {
                    log.info("导出异步处理开始,{}", configDto);
                    long startTime = System.currentTimeMillis();
                    excelHandleTmplate.exportExcel(configDto);
                    long endTime = System.currentTimeMillis();
                    log.info("导出异步处理完成,{},耗时:{}ms", configDto, endTime - startTime);
                } catch (Exception e) {
                    log.info("导出异步处理异常,{}", configDto, e);
                }
            });
        }catch (BusinessException e) {
            log.error("导出异常!", e);
            return ResponseMessage.faild(e.getMessage());
        } catch (Exception e){
            return ResponseMessage.faild("导出异常!");
        }
        return ResponseMessage.success(null, "导出成功,请稍后在下载查询查看导入结果!");
    }

    /**
     * 生成Excel文件名
     * @param userId
     * @param typeEn
     * @return
     */
    private String getFileName(String userId,String typeEn) {
        StringJoiner nameJoiner = new StringJoiner("_", "", ".xlsx");
        nameJoiner.add(typeEn).add(userId).add(String.valueOf(System.nanoTime())).toString();
        return nameJoiner.toString();
    }
}

导入导出业务处理模板类

@Component
@Slf4j
public class ExcelHandleTmplate {
    @Resource
    private FileDownloadMapper fileDownloadMapper;

    private static final Object EXCEL_PWD_LOCK = new Object();

    /**
     * 文件保存的根目录
     */
    private static final String FILE_ROOT_PATH="D:/temp/";
    /**
     * 导入文件最多大小
     */
    private static final Integer IMPORT_FILE_SIZE=10*1024*1024;

    
    public Optional<Integer> exportExcel(ExcelExportConfigDto configDto) {

        Integer taskId = initExcelTask(configDto.getFileRelativePath(),configDto.getFileName(), configDto.getOperType());
        boolean sucessFlag = true;
        try {
            log.info("[excel export start], fileId:{}, ExcelExportConfigDto:{}", taskId, configDto);
            String filePath = FILE_ROOT_PATH+ File.separator +configDto.getFileRelativePath()+ File.separator +configDto.getFileName();
            Optional<Integer> exportCount = new ExcelExporter().exportExcel(configDto.getExcelConfig(), filePath, configDto.getExcelExportBusinessHandle());
            return exportCount;
        } catch (Exception e) {
            log.error("[excel export error]，fileId:{}, ExcelImportConfigDto：{}", taskId, configDto,e);
            sucessFlag = false;
            throw new BusinessException("导出异常");
        }finally {
            postExcelTask(taskId,sucessFlag);
            configDto.getExcelExportBusinessHandle().postExport(sucessFlag,configDto);
        }
    }


    public Optional<Integer> importExcel(ExcelImportConfigDto configDto, Function<List<?>, List<?>> impl){
        Integer fileId = initExcelTask(configDto.getFileRelativePath(),configDto.getFileName(), configDto.getOperType());
        boolean sucessFlag = true;
        try {
            log.info("[excel import start], fileId:{}, ExcelImportConfigDto：{}", fileId, configDto);
            String filePath = FILE_ROOT_PATH+ File.separator +configDto.getFileRelativePath()+ File.separator +configDto.getFileName();
            Optional<Integer> importCount = new ExcelImporter().importExcel(filePath,configDto, impl);
            return importCount;
        }catch (Exception e){
            log.error("[excel import error]，fileId:{}, ExcelImportConfigDto：{}", fileId, configDto,e);
            sucessFlag = false;
            throw new BusinessException("导入异常");
        }finally {
            postExcelTask(fileId, sucessFlag);
            configDto.getExcelImportBusinessHandle().postImportData(sucessFlag, configDto);
        }
    }


    /**
     * 初始化导入导出数据供页面查询
     * @param fileName
     * @param operType
     * @return
     */
    private Integer initExcelTask(String localFileAbsPath,String fileName, String operType) {
        FileDownload fileDownload = new FileDownload();
        fileDownload.setInsertUser("当前操作用户ID");
        fileDownload.setFileName(fileName);
        fileDownload.setStatus(FileStatusEnum.GENERATE_ING.getKey());
        fileDownload.setStatusRemark(FileStatusEnum.GENERATE_ING.getValue());
        fileDownload.setType(operType);
        fileDownload.setFilePath(localFileAbsPath);
        fileDownload.setIsEncrypted((byte) 1);
        fileDownloadMapper.save(fileDownload);
        return fileDownload.getId();
    }

    /**
     *  更新最终状态到fileDownload中供页面查询 且将文件加密保存到磁盘上
     * @param fileId fileDownload的ID
     * @param successFlag 正常还是异常
     */
    private void postExcelTask(int fileId, boolean successFlag){
        //更新文件记录表
        FileDownload fd = fileDownloadMapper.findOneById(fileId);
        if (fd == null) {
            log.error("导入生成下载文件不存在,fileDownload:{}", fileId);
            return;
        }

        if(!successFlag){
            fd.setStatus(FileStatusEnum.GENERATE_FAILURE.getKey());
            fd.setStatusRemark(FileStatusEnum.GENERATE_FAILURE.getValue());
            fileDownloadMapper.updateByPrimaryKey(fd);
            return;
        }
        try {
            String filePath = FILE_ROOT_PATH+ File.separator +fd.getFilePath()+ File.separator +fd.getFileName();
            //加密密码
            String password = getUUIDByRandReplacedLower();
            setExcelPassword(filePath, filePath, password);
            fd.setStatus(FileStatusEnum.GENERATE_SUCCESS.getKey());
            fd.setStatusRemark(FileStatusEnum.GENERATE_SUCCESS.getValue());
            fd.setFilePassword(password);
            fileDownloadMapper.updateByPrimaryKey(fd);
        } catch (Exception e) {
            log.error("导入导出报错", e);
            fd.setStatus(FileStatusEnum.GENERATE_FAILURE.getKey());
            fd.setStatusRemark(FileStatusEnum.GENERATE_FAILURE.getValue());
            fileDownloadMapper.updateByPrimaryKey(fd);
        }
    }

    /**
     * 为Excel加密
     * @param filepath
     * @param targetPath
     * @param password
     * @throws 
     */
    private void setExcelPassword(String filepath, String targetPath, String password) throws IOException {
        // 防止多线程情况下， JVM cash
        synchronized (EXCEL_PWD_LOCK) {
            File tempFile = copyTmpFile(filepath);
            if (tempFile == null) {
                throw new RuntimeException("setExcelPassword copy temp file Error!");
            }
            try (POIFSFileSystem fs = new POIFSFileSystem()) {
                EncryptionInfo info = new EncryptionInfo(EncryptionMode.agile);
                // EncryptionInfo info = new EncryptionInfo(EncryptionMode.agile, CipherAlgorithm.aes192, HashAlgorithm.sha384, -1, -1, null);
                Encryptor enc = info.getEncryptor();
                enc.confirmPassword(password);
                // Read in an existing OOXML file and write to encrypted output stream
                // don't forget to close the output stream otherwise the padding bytes aren't added
                try (OPCPackage opc = OPCPackage.open(tempFile, PackageAccess.READ_WRITE)) {
                    // 这个流不能在这里关
                    OutputStream os = enc.getDataStream(fs);
                    opc.save(os);
                } catch (GeneralSecurityException | InvalidFormatException ex) {
                    throw new IOException(ex);
                }
                // Write out the encrypted version
                try (FileOutputStream fos = new FileOutputStream(org.springframework.util.StringUtils.isEmpty(targetPath) ? filepath : targetPath)) {
                    fs.writeFilesystem(fos);
                }
            }finally {
                if(tempFile != null && tempFile.exists()) {
                    tempFile.delete();
                }
            }
        }
    }


    /**
     * 导入时 对Excel进行常规验证
     * @param upload 上传的文件
     * @param maxLimitLines
     * @return
     */
    public File commonCheck(MultipartFile upload, Integer maxLimitLines) {
        if (null == upload) {
            throw new BusinessException("文件为空");
        }

        // 文件上传大小限制，10M
        if (upload.getSize() > IMPORT_FILE_SIZE) {
            throw new BusinessException("文件不能大于" +  IMPORT_FILE_SIZE + " M");
        }

        //校验文件
        String extension = FilenameUtils.getExtension(upload.getOriginalFilename()).toLowerCase();
        if (!"xls".equals(extension) && !"xlsx".equals(extension)) {
            throw new BusinessException("请上传[.xls,.xlsx]格式的文件!");
        }
        File tempFile = copyTmpFile(upload);
        try {
            boolean hasNoPwd = checkHasNoPassword(new FileInputStream(tempFile));
            if (!hasNoPwd) {
                throw new BusinessException("导入提示, 该文件已加密，不能导入!");
            }
        } catch (Exception e) {
            throw new BusinessException("导入提示, 导入文件不存在");
        }

        try {
            if (maxLimitLines == null) {
                maxLimitLines = ExcelImportBusinessHandle.MAX_EXCEL_EXPORT_LINES_LIMIT;
            }
            // 通过SAX获取Excel总行数，防止内存溢出
            int rowCount = ExcelImporter.getRowCount(new FileInputStream(tempFile));
            if (rowCount > maxLimitLines) {
                throw new BusinessException("导入提示, 总行数不能超过" + maxLimitLines + "行!");
            }
        } catch (Exception e) {
            throw new BusinessException("导入提示, EXCEL解析异常！");
        }
        return tempFile;
    }


    /**
     * 验证Excel是否加密
     * @param is
     * @return
     */
    private static boolean checkHasNoPassword(InputStream is) {
        try {
            EncryptionInfo info = new EncryptionInfo(new POIFSFileSystem(is));
            Decryptor d = info.getDecryptor();
            if (!d.verifyPassword(Decryptor.DEFAULT_PASSWORD)) {
                return false;
            }
        } catch (GeneralSecurityException ex) {
            log.error("Unable to process encrypted document", ex);
        } catch (OfficeXmlFileException ex3) {
            // 该文件是明文文件，会抛出这个异常
            return true;
        } catch (Exception ex3) {
            // 其他的异常说明Excel
            return false;
        }
        return false;
    }

    /**
     * 复制文件
     * @param path
     * @return
     */
    private File copyTmpFile(String path) {
        try {
            File source = new File(path);
            if(source.exists()) {
                // 临时文件名
                String newFileName = UUID.randomUUID().toString() + "_" + Thread.currentThread().getId() + "_" + source.getName() + "_.tmp";
                // 临时文件全路径
                File file = new File(getFilePath(newFileName));
                // 不存在就创建
                if (!file.exists()) {
                    file.createNewFile();
                }
                FileUtils.copyInputStreamToFile(new FileInputStream(new File(path)), file);
                return file;
            }else {
                return null;
            }
        } catch (Exception e) {
            log.error("Temporary file copy exception, And exception information", e);
            return null;
        }
    }

    /**
     * 复制文件
     * @param upload
     * @return
     */
    private File copyTmpFile(MultipartFile upload) {
        try {
            // 减少并发产生的文件覆盖操作
            String newFilePath = System.nanoTime() + "_import.tmp";
            File file  = new File(getFilePath(newFilePath));
            if(!file.exists()){
                file.createNewFile();
            }
            FileUtils.copyInputStreamToFile(upload.getInputStream(), file);
            // 记录Excel文件上传后，本地磁盘的临时文件地址（该文件保留3天），便于出现问题后，可以直接从服务器上直接拿对应有问题的文件
            log.info("拷贝后的文件路径：{}", file.getAbsolutePath());
            return file;
        } catch (Exception e) {
            log.error("Temporary file copy exception, And exception information", e);
        }
        return null;
    }

    /**
     * 获取文件路径
     * @param fileName
     * @return
     */
    private String getFilePath(String fileName) {
        return FILE_ROOT_PATH + File.separator + fileName;
    }

    /**
     * 生成Excel加密密码
     * @return
     */
    private String getUUIDByRandReplacedLower() {
        //文件密码
        return UUID.randomUUID().toString().replace("-", "").toLowerCase().substring(0, 10);
    }
}

导出处理器

public class ExcelExporter<T> extends CommonForkJoinService<T, Integer> {

    private static final Logger logger = LoggerFactory.getLogger(ExcelExporter.class);

    /**
     * 单次Excel导出的最大行数
     */
    public static final int MAX_COUNT_IN_SHEET = 1000000;

    private CellStyle xssfCellStyle;

    public ExcelExporter() {
        super.setMaxCount(MAX_COUNT_IN_SHEET);
    }


    /**
     * 分页导出数据
     * @param cls
     * @param fileAbsPath
     * @param scanner
     * @return
     * @throws 
     */
    public Optional<Integer> exportExcel(Class<T> cls, String fileAbsPath, ExcelExportBusinessHandle<T> scanner) throws IOException {
        // 存放excel标题
        List<String> titleList = Lists.newArrayList();
        // 存放Filed[]
        List<Field> fieldList = Lists.newArrayList();
        // 解析导出excel配置
        parseEasyExcelConfig(cls, titleList, fieldList);

        // 将数据写入excel
        return writeData2Excel(fileAbsPath, titleList, fieldList, scanner);
    }

    /**
     * 将数据写入到Excel里
     * @param fileAbsPath
     * @param titleList
     * @param fieldList
     * @param scanner
     * @return
     * @throws 
     */
    private Optional<Integer> writeData2Excel(String fileAbsPath, List<String> titleList, List<Field> fieldList, ExcelExportBusinessHandle<T> scanner) throws IOException {
        SXSSFWorkbook sxssfWorkbook = null;
        Optional<Integer> result = null;
        long excelStart = System.currentTimeMillis();

        try (FileOutputStream stream = new FileOutputStream(new File(fileAbsPath));) {
            // the number of rows that are kept in memory until flushed out
            sxssfWorkbook = new SXSSFWorkbook(100);
            xssfCellStyle = setStyle(sxssfWorkbook);
            // 本地导出的总行数
            // 创建一个sheet
            Sheet currentSheet = sxssfWorkbook.createSheet(scanner.sheetNamePrefix());

            // Excel已经处理到的行
            AtomicInteger currentRowIndex = new AtomicInteger(-1);
            // 创建title行
            Row firstRow = currentSheet.createRow(currentRowIndex.incrementAndGet());
            for (int j = 0; j < titleList.size(); j++) {
                setCellValue(firstRow,j,titleList.get(j));
            }

            // forkJoin处理的每个list
            Function<List<T>, Integer> impl = ts -> {
                int dealCount = 0;
                // 遍历每一行，导出行数据到excel
                try {
                    if (CollectionUtils.isNotEmpty(ts)) {
                        for (T record : ts) {
                            dealSingleRow(fieldList, currentSheet, record, currentRowIndex);
                            dealCount++;
                        }
                    }
                } catch (Exception e) {
                    logger.error("Excel {} 写入异常,", fileAbsPath, e);
                }
                return dealCount;
            };

            long start = System.currentTimeMillis();
            // 将数据写入到excel， 并返回总条数
            result = super.forkJoinWithScanner(scanner, impl, Integer::sum);
            logger.info("Export Data to {} , cost:{} ms", fileAbsPath, (System.currentTimeMillis() - start));
            // 写入到磁盘
            sxssfWorkbook.write(stream);
            logger.info("Export Data to {} finish ,total cost:{} ms", fileAbsPath, (System.currentTimeMillis() - excelStart));
        } catch (InterruptedException | ExecutionException e) {
            logger.error("Export data to {} error", fileAbsPath,e);
            throw new IOException(e);
        } finally {
            if (sxssfWorkbook != null) {
                sxssfWorkbook.close();
                // SXSSF会分配临时文件，必须始终通过调用dispose方法来明确清理这些文件。
                sxssfWorkbook.dispose();
            }
        }
        return result;
    }

    /**
     * 设置Excel样式
     * @param workbook
     * @return
     */
    private CellStyle setStyle(SXSSFWorkbook workbook) {
        CellStyle style = workbook.createCellStyle();
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        XSSFColor color = new XSSFColor(java.awt.Color.BLACK);
        style.setTopBorderColor(color.getIndex());
        style.setLeftBorderColor(color.getIndex());
        style.setBottomBorderColor(color.getIndex());
        style.setRightBorderColor(color.getIndex());
        return style;
    }

    /**
     * 创建单行excecl row
     * @param fieldList
     * @param currentSheet
     * @param record
     * @param currentRowIndex
     * @throws IllegalAccessException
     */
    private void dealSingleRow(List<Field> fieldList, Sheet currentSheet, T record, AtomicInteger currentRowIndex) throws IllegalAccessException {
        Row row = null;
        // 由于createRow不是线程安全的, 对单个sheet进行创建row时，可能会并发
        synchronized (this) {
            row = currentSheet.createRow(currentRowIndex.incrementAndGet());
        }

        int cellIndex = 0;
        for (Field field : fieldList) {
            if (!field.isAccessible()) {
                field.setAccessible(true);
            }
            // 通过反射 调用record的get方法获取值
            Object valueObj = field.get(record);
            String value = null;
            if (valueObj == null) {
                value = "";
            } else {
                // TODO 其他类型扩展？
                if (valueObj instanceof Date) {
                    value = DateUtil.format((Date) valueObj, "yyyy-MM-dd HH:mm:ss");
                } else {
                    value = valueObj.toString();
                }
            }
            setCellValue(row, cellIndex++, value);
        }
    }

    /**
     * 处理单个单元格
     * @param row
     * @param cellIndex
     * @param value
     */
    private void setCellValue(Row row, int cellIndex, String value) {
        Cell cell = row.createCell(cellIndex);
        cell.setCellStyle(xssfCellStyle);
        cell.setCellValue(value);
    }


    /**
     * 解析Excel导出配置DTO， 获取title等信息
     * @param cls
     * @param titleList
     * @param fieldList
     */
    protected void parseEasyExcelConfig(Class<T> cls, List<String> titleList, List<Field> fieldList) {
        Assert.notNull(cls, "Excel 配置类不能为空！");

        Field[] titleFieldSet = cls.getDeclaredFields();
        if (titleFieldSet.length <= 0) {
            throw new IllegalArgumentException(cls.getName() + " 配置错误！");
        }
        // titleOrder 排序顺序按照cell中的index存放
        TreeMap<Integer, ExportExcelProperty> titleOrder = new TreeMap<>();
        // FieldOrder 排序顺序按照cell中的index存放
        TreeMap<Integer, Field> fieldOrder = new TreeMap<>();
        for (Field field : titleFieldSet) {
            ExportExcelProperty yes = field.getAnnotation(ExportExcelProperty.class);
            if (null != yes) {
                titleOrder.put(yes.index(), yes);
                fieldOrder.put(yes.index(), field);
            }
        }

        // 将解析出的title信息放到list
        for (Integer index : titleOrder.keySet()) {
            titleList.add(titleOrder.get(index).value()[0]);
        }
        // 将解析出来的get方法存到list
        for (Integer index : fieldOrder.keySet()) {
            fieldList.add(fieldOrder.get(index));
        }

        Assert.notEmpty(titleList, cls.getName() + " Title未配置");
        Assert.notEmpty(fieldList, cls.getName() + " Field未配置");
        Assert.isTrue(fieldList.size() == titleList.size(), " @ExcelProperty配置错误，请检查是否含有null");

    }
}

导出处理器父类

public class CommonForkJoinService<T, R> {

    private static final Logger logger = LoggerFactory.getLogger(CommonForkJoinService.class);
    /**
     * 默认forkJoin线程池
     */
    private static final ForkJoinPool DEFAULT_POOL = new ForkJoinPool(CpuCountUtil.getCpuNumber("CommonForkJoinService", null));

    /**
     * 每次从数据库扫描的条数
     */
    private Integer pageSize = 20000;
    /**
     * 当导出数据大于此值时， 强制启动forkjoin
     */
    private Integer useForkJoinGt = 20000;

    private ForkJoinPool customPool ;

    /**
     * 数据扫描的最大条数
     */
    private Integer maxCount = 1000000;

    public Integer getMaxCount() {
        return maxCount;
    }

    public void setMaxCount(Integer maxCount) {
        this.maxCount = maxCount;
    }

    public Integer getPageSize() {
        return pageSize;
    }

    public void setPageSize(Integer pageSize) {
        this.pageSize = pageSize;
    }

    public Integer getUseForkJoinGt() {
        return useForkJoinGt;
    }

    public void setUseForkJoinGt(Integer useForkJoinGt) {
        this.useForkJoinGt = useForkJoinGt;
    }

    public ForkJoinPool getCustomPool() {
        if(this.customPool == null) {
            return DEFAULT_POOL;
        }
        return customPool;
    }

    /**
     * 子类可以重写改方法，达到自定义fork-join线程池
     * @param customPool
     */
    public void setCustomPool(ForkJoinPool customPool) {
        this.customPool = customPool;
    }

    /**
     * 通用数据扫描，如果超过了 <code>useForkJoinGt</code> 则强制启动fork-join的方式
     * T- 输入类型 R-返回类型
     * @param scanner
     * @param impl
     * @param join
     * @return
     * @throws 
     * @throws InterruptedException
     */
    public Optional<R> forkJoinWithScanner(ExcelExportBusinessHandle<T> scanner, Function<List<T>, R> impl, BiFunction<R, R, R> join) throws ExecutionException, InterruptedException {
        // 调用查询总条数
        long start = System.currentTimeMillis();
        Integer totalCount = scanner.getCount();
        long end = (System.currentTimeMillis() - start);
        if(end > 100) {
            logger.info("forkJoinWithScanner 调用getCount()耗时：{}",end );
        }
        if (totalCount == null || totalCount == 0) {
            logger.warn("Empty total count impl, just return empty!");
            return Optional.empty();
        }

        // 数据总条数, 如果总条数超过100W，则只导出100W, 子类可以修改此值
        if (totalCount > maxCount) {
            totalCount = maxCount;
        }

        logger.info("forkJoinWithScanner 限制总条数：{}", new DecimalFormat("#,###").format(totalCount.longValue()));

        // 每次查询条
        int pageSize = scanner.getPageSize() == 0 ? getPageSize() : scanner.getPageSize() ;
        // 符合条件的总页数
        int totalPages = totalCount / pageSize + (totalCount % pageSize == 0 ? 0 : 1);
        // 当前扫描页
        int currentPage = -1;
        // 如果总条数大于指定条数，则强制启动fork-join
        boolean useForkJoin = totalCount.compareTo(useForkJoinGt) > 0;
        // 如果客户端明确要求不使用fork-join，则关闭
        if(scanner.useForkJoinPerPage() == false) {
            useForkJoin = false;
        }

        // 分页查询符合条件的数据
        List<T> dbList = null;
        // 返回值
        R returnValue = null;
        // 总页数是从1开始的，程序这里要从0开始，故改为大于
        while (totalCount > 0 && totalPages > ++currentPage) {
            long pageStart = System.currentTimeMillis();
            // 根据分页参数，获取数据, currentPage 从0开始
            dbList = scanner.getDetailList(currentPage, pageSize);
            logger.info("forkJoinWithScanner getDetailList()耗时：{}", (System.currentTimeMillis() - pageStart));

            if (CollectionUtils.isNotEmpty(dbList)) {
                if(dbList.size() > pageSize) {
                    logger.error("[开发人员请注意][严重]分页查询出来的数据行数大于pageSize, 数据大小：{}  分页参数：{}", dbList.size(), pageSize);
                    throw new RuntimeException("分页查询出来的数据行数大于" + getPageSize());
                }
                if (useForkJoin) {
                    // 生成fork-join任务
                    CommonRecursiveTask<T, R> task = new CommonRecursiveTask<>(dbList, impl, join);
                    // 提交fork-join任务
                    ForkJoinTask<R> result = getCustomPool().submit(task);
                    // 将fork-join返回的值封装
                    if (returnValue == null) {
                        returnValue = result.get();
                    } else {
                        if (join != null) {
                            returnValue = join.apply(returnValue, result.get());
                        }
                    }
                } else {
                    R temp = null;
                    if (impl != null) {
                        temp = impl.apply(dbList);
                    }
                    if(returnValue == null) {
                        returnValue = temp;
                    }else {
                        returnValue = join.apply(returnValue, temp);
                    }
                }
            } else {
                break;
            }
        }
        return Optional.ofNullable(returnValue);
    }
}

导出大量数据时的forkjoin类

public class CommonRecursiveTask<T, R> extends RecursiveTask<R> {

    /**
     * 最小执行的任务数
     */
    private static final int MIN_TASK_COUNT = 100;

    /**
     * 需要处理的数据
     */
    private List<T> dataList;
    /**
     * 每个最小单元的List对应的处理逻辑函数
     */
    private Function<List<T>, R> impl;
    /**
     * 每个最小单元的List处理完成后，结果合并函数
     */
    private BiFunction<R, R, R> join;

    public CommonRecursiveTask(List<T> dataList, Function<List<T>, R> impl, BiFunction<R, R, R> join) {
        this.dataList = dataList;
        this.impl = impl;
        this.join = join;
    }

    public CommonRecursiveTask() {
    }

    public void setDataList(List<T> dataList) {
        this.dataList = dataList;
    }

    public void setImpl(Function<List<T>, R> impl) {
        this.impl = impl;
    }

    public void setJoin(BiFunction<R, R, R> join) {
        this.join = join;
    }

    @Override
    protected R compute() {
        // 是否还要继续拆分List
        boolean canCompute = dataList.size() <= MIN_TASK_COUNT;
        if (canCompute) {
            if (impl != null) {
                return impl.apply(this.dataList);
            } else {
                throw new IllegalArgumentException("fork-join处理异常，业务代码为空！");
            }
        } else {

            final List<T> leftTempList = dataList.subList(0, dataList.size() / 2);
            final List<T> rightTempList = dataList.subList(dataList.size() / 2, dataList.size());

            CommonRecursiveTask<T, R> leftTask = new CommonRecursiveTask<T, R>(leftTempList, impl, join);
            CommonRecursiveTask<T, R> rightTask = new CommonRecursiveTask<T, R>(rightTempList, impl, join);

            // 执行子任务
            leftTask.fork();
            rightTask.fork();

            // 等待任务执行结束合并其结果
            R leftResult = leftTask.join();
            R rightResult = rightTask.join();

            if (join != null) {
                // 合并子任务
                return join.apply(leftResult, rightResult);
            } else {
                throw new IllegalArgumentException("fork-join处理异常，合并分片代码为空！");
            }
        }
    }
}

导出配置类

@Getter
@Setter
public class ExcelExportConfigDto {

    /**
     * 生成的本地文件相对路径  需要调用端设置
     */
    private String fileRelativePath;
    /**
     * 当前导入操作用户名 需要调用端设置
     */
    private String userId;
    /**
     * 文件名 自动设置
     */
    private String fileName;
    /**
     * 操作类型 需要调用端设置
     */
    private String operType;
    /**
     * 数据扫苗方式  需要调用端设置
     */
    private ExcelExportBusinessHandle excelExportBusinessHandle;
    /**
     * excel的配置dto， 需要调用端设置
     */
    private Class excelConfig;

    public ExcelExportConfigDto(String fileRelativePath, String userId,String operType, Class excelConfig, ExcelExportBusinessHandle excelExportBusinessHandle) {
        this.fileRelativePath = fileRelativePath;
        this.operType = operType;
        this.excelExportBusinessHandle = excelExportBusinessHandle;
        this.excelConfig = excelConfig;
        this.userId = userId;
    }
}

导出钩子类

public interface ExcelExportBusinessHandle<T> {

    /**
     * 默认最大Excel导出行数
     */
    int MAX_EXCEL_EXPORT_LINES_LIMIT = 1_000_000;

    /**
     * 获取数据总条数，会根据这个返回的结果计算分页参数
     */
    Integer getCount();

    /**
     * 根据计算出的分页参数，获取数据
     * @param pageIndex 当前页
     * @param pageSize  当前页大小
     * @return 分页查询结果集
     */
    List<T> getDetailList(Integer pageIndex, Integer pageSize);


    /**
     * 分页扫描时默认扫描的行数
     * @return
     */
    default Integer getPageSize() {
        return 1000;
    }

    /**
     * 导出时默认的sheet名称
     */
    default String sheetNamePrefix() {
        return "Sheet-";
    }

    /**
     * 默认不开启fork-join  因为投诉工单系统这边很多导出都要排序
     *
     * @return
     */
    default boolean useForkJoinPerPage() {
        return false;
    }

    /**
     * 数据导出结束后 的操作
     * @param flag 导出操作是否正常结束 true为正常结束，false为导出异常
     * @param configDto
     */
    default void postExport(boolean flag,ExcelExportConfigDto configDto){}
}

导入处理器

public class ExcelImporter<T>{
    /**
     * slf4j 日志组件
     */
    private static final Logger logger = LoggerFactory.getLogger(ExcelImporter.class);
    /**
     * 导出excel SXSSFWorkbook
     */
    SXSSFWorkbook sxssfWorkbook = null;
    /**
     * 导出excel 当前操作的sheet
     */
    private Sheet currentSheet;
    /**
     * Excel已经处理到的行
     */
    private AtomicInteger currentRowIndex = new AtomicInteger(-1);
    /**
     * 输出到本地文件的流
     */
    private FileOutputStream stream;
    /**
     * 存放excel标题
     */
    private List<String> titleList = Lists.newArrayList();
    /**
     * 存放Filed[]
     */
    private List<Field> fieldList = Lists.newArrayList();
    private Field errorInfo;


    /**
     * 每次从Excel读取1000行后处理业务
     */
    private int MAX_SIZE_PER_IMPORT = 1000;

    /**
     * 导入数据
     * @param localFailedPath
     * @param configDto
     * @param impl
     * @return
     * @throws Exception
     */
    public Optional<Integer> importExcel(String localFailedPath, ExcelImportConfigDto configDto, Function<List<T>, List<T>> impl ) throws Exception {

        Integer pageSize = configDto.getPageSize();
        if(pageSize!=null){
            MAX_SIZE_PER_IMPORT = pageSize;
        }
        // 解析导出excel配置
        parseEasyExcelConfig(configDto.getExcelConfig());
        // 初始化导入校验失败的EXCEL
        initFailedExcel(localFailedPath, "失败列表");
        long start = System.currentTimeMillis();
        // 已经解析成功的Excel行数
        AtomicInteger execCount = new AtomicInteger(0);

        EasyExcel.read(configDto.getUpload(), new ReadListener() {
            // 每次从Excel转换出来的数据
            List<T> dtoList = new ArrayList<>(MAX_SIZE_PER_IMPORT);
            Map<Integer, String> headerMap = new HashMap<>();
            @Override
            public void onException(Exception exception, AnalysisContext context) throws Exception {
                logger.error("EasyExcel解析时发生异常:{}", configDto.getOperType(), exception);
                throw new BusinessException("EasyExcel解析时发生异常");
            }

            @Override
            public void invokeHead(Map headMap, AnalysisContext context) {
                Map<Integer, CellData> formatHeadMap = (Map<Integer, CellData>)headMap;
                for (Map.Entry<Integer, CellData> entry : formatHeadMap.entrySet()) {
                    headerMap.put(entry.getKey(), entry.getValue().getStringValue());
                }
            }

            @Override
            public void invoke(Object data, AnalysisContext context) {
                if(data !=null && data instanceof Map) {
                    Map<Integer,String> dataMap = (Map) data;
                    Map<String, String> header2DataMap = new HashMap<>();
                    for (Map.Entry<Integer, String> entry : dataMap.entrySet()) {
                        header2DataMap.put(headerMap.get(entry.getKey()), entry.getValue());
                    }
                    try {
                        T dto = (T)configDto.getExcelConfig().newInstance();
                        readExcelDataFromMap(dto, header2DataMap);
                        dtoList.add(dto);
                    } catch (Exception e) {
                        logger.error("通过反射读取Excel数据异常, type:{}", configDto.getOperType(), e);
                        throw new BusinessException("EasyExcel解析时发生异常");
                    }
                    execCount.incrementAndGet();
                } else {
                    logger.warn("通过EasyExcel读取数据，返回的数据为空 或者不是Map类型 type:{}",configDto.getOperType());
                    throw new BusinessException("通过EasyExcel读取数据，返回的数据为空");
                }
                if (dtoList.size() < MAX_SIZE_PER_IMPORT) {
                    return;
                }
                doWithBusinessCode();
            }

            private void doWithBusinessCode() {
                // 超过指定条数，调用业务代码
                List<T> failedList = new ArrayList<>();
                try {
                    failedList = impl.apply(dtoList);
                }catch (Exception e){
                    logger.error("EasyExcel写入错误信息到Excel异常:{}", configDto.getOperType(), e);
                    failedList = dtoList;
                    for(T t:failedList){
                        errorInfo.setAccessible(true);
                        try {
                            Object o = errorInfo.get(t);
                            if(o==null){
                                errorInfo.set(t, "处理数据业务异常");
                            }
                        } catch (IllegalAccessException e1) {
                            logger.error("处理数据业务异常", configDto.getOperType(), e);
                        }

                    }
                }
                Optional.ofNullable(failedList).orElse(Collections.emptyList()).forEach(failedData -> {
                    try {
                        dealSingleRow(fieldList,currentSheet,failedData,currentRowIndex);
                    } catch (Exception e) {
                        logger.error("EasyExcel写入错误信息到Excel异常:{}", configDto.getOperType(), e);
                        throw new BusinessException("EasyExcel写入错误信息到Excel异常");
                    }
                });
                dtoList = new ArrayList<>(MAX_SIZE_PER_IMPORT);
            }

            @Override
            public void extra(CellExtra extra, AnalysisContext context) {

            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext context) {
                if (CollectionUtils.isNotEmpty(dtoList)) {
                    doWithBusinessCode();
                }
                // Excel解析完毕
                try {
                    sxssfWorkbook.write(stream);
                } catch (IOException e) {
                    logger.error("EasyExcel写入错误信息到Excel异常 刷盘阶段 {}", configDto.getOperType(), e);
                }
            }

            @Override
            public boolean hasNext(AnalysisContext context) {
                return true;
            }
        }).ignoreEmptyRow(true).excelType(ExcelTypeEnum.XLSX).sheet(0).doRead();
        logger.info("EasyExcel导入读取excel，导入类型：{}, 处理条数：{}, 耗时：{} ms",configDto.getOperType(), execCount.get(), (System.currentTimeMillis() - start));
        return Optional.of(execCount.get());
    }

    /**
     * 获取Excel文件总行数
     * @param file
     * @return
     */
    public static int getRowCount(InputStream file) {
        AtomicInteger count = new AtomicInteger(0);
        EasyExcel.read(file, new ReadListener() {
            @Override
            public void onException(Exception exception, AnalysisContext context) throws Exception {
                logger.error("EasyExcel获取Excel总行数失败", exception);
                throw new BusinessException("EasyExcel获取Excel总行数失败");
            }

            @Override
            public void invokeHead(Map headMap, AnalysisContext context) {
            }

            @Override
            public void invoke(Object data, AnalysisContext context) {
                count.incrementAndGet();
            }

            @Override
            public void extra(CellExtra extra, AnalysisContext context) {
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext context) {
            }

            @Override
            public boolean hasNext(AnalysisContext context) {
                return true;
            }
        }).excelType(ExcelTypeEnum.XLSX).ignoreEmptyRow(false).sheet(0).doRead();

        if (count.get() > 500000) {
            logger.warn("[开发人员请注意][严重]获取Excel的行数超过50W，可能存在性能问题，请检查！");
        }
        return count.get();
    }


    /**
     * 初始化导入失败的excel，新建本地excel，初始化title
     * @param fileAbsPath
     * @param sheetName
     * @throws Exception
     */
    public void initFailedExcel(String fileAbsPath,String sheetName) throws Exception {
        stream = new FileOutputStream(new File(fileAbsPath));
        // the number of rows that are kept in memory until flushed out
        sxssfWorkbook = new SXSSFWorkbook(100);
        // 本地导出的总行数
        // 创建一个sheet
        currentSheet = sxssfWorkbook.createSheet(sheetName);

        // 创建title行
        Row firstRow = currentSheet.createRow(currentRowIndex.incrementAndGet());
        for (int j = 0; j < titleList.size(); j++) {
            firstRow.createCell(j).setCellValue(titleList.get(j));
        }
    }

    /**
     * 解析Excel导出配置DTO， 获取title等信息
     * @param cls
     */
    protected void parseEasyExcelConfig(Class<T> cls) {
        Assert.notNull(cls, "Excel 配置类不能为空！");

        Field[] titleFieldSet = cls.getDeclaredFields();
        if (titleFieldSet.length <= 0) {
            throw new IllegalArgumentException(cls.getName() + " 配置错误！");
        }
        // 解决父类中有属性的情况
        titleFieldSet = ArrayUtils.addAll(titleFieldSet, cls.getFields());
        // titleOrder 排序顺序按照cell中的index存放
        TreeMap<Integer, ImportExcelProperty> titleOrder = new TreeMap<>();
        // FieldOrder 排序顺序按照cell中的index存放
        TreeMap<Integer, Field> fieldOrder = new TreeMap<>();
        for (Field field : titleFieldSet) {
            ImportExcelProperty yes = field.getAnnotation(ImportExcelProperty.class);
            if (null != yes) {
                titleOrder.put(yes.index(), yes);
                fieldOrder.put(yes.index(), field);
            }
        }

        // 将解析出的title信息放到list
        for (Integer index : titleOrder.keySet()) {
            titleList.add(titleOrder.get(index).value()[0]);
        }
        // 将解析出来的get方法存到list
        for (Integer index : fieldOrder.keySet()) {
            Field field = fieldOrder.get(index);
            fieldList.add(field);
            if(field.getName().equalsIgnoreCase("errorInfo")){
                errorInfo = field;
            }
        }

        Assert.notEmpty(titleList, cls.getName() + " Title未配置");
        Assert.notEmpty(fieldList, cls.getName() + " Field未配置");
        Assert.isTrue(fieldList.size() == titleList.size(), " @Cell配置错误，请检查是否含有null");

    }

    /**
     * 创建单行excecl row
     * @param fieldList
     * @param currentSheet
     * @param record
     * @param currentRowIndex
     * @throws IllegalAccessException
     */
    private void dealSingleRow(List<Field> fieldList, Sheet currentSheet, T record, AtomicInteger currentRowIndex) throws IllegalAccessException {
        Row row = null;
        // 由于createRow不是线程安全的, 对单个sheet进行创建row时，可能会并发
        synchronized (this) {
            row = currentSheet.createRow(currentRowIndex.incrementAndGet());
        }
        int cellIndex = 0;
        for (Field field : fieldList) {
            if (!field.isAccessible()) {
                field.setAccessible(true);
            }
            // 通过反射 调用record的get方法获取值
            Object valueObj = field.get(record);
            String value = null;
            if (valueObj == null) {
                value = "";
            } else {
                if (valueObj instanceof Date) {
                    value = DateUtil.format((Date) valueObj,"yyyy-MM-dd HH:mm:ss");
                } else {
                    value = valueObj.toString();
                }
            }
            setCellValue(row, cellIndex++, value);
        }
    }


    /**
     * 处理单个单元格
     * @param row
     * @param cellIndex
     * @param value
     */
    private void setCellValue(Row row, int cellIndex, String value) {
        row.createCell(cellIndex).setCellValue(value);
    }


    /**
     * 通过反射，从map中获取数据到dto
     * @param dto
     * @param dataMap
     */
    private void readExcelDataFromMap(T dto, Map<String, String> dataMap) {
        try {
            Field[] fields = dto.getClass().getDeclaredFields();
            for (Field field : fields) {
                if (!field.isAccessible()) {
                    field.setAccessible(true);
                }
                ImportExcelProperty cell = field.getAnnotation(ImportExcelProperty.class);
                if (cell != null) {
                    String data = dataMap.get(cell.value()[0]);
                    field.set(dto, stringToObject(data,field.getType(),cell.format()));
                }
            }
        } catch (Exception e) {
            logger.error("读取Excel数据异常！ ", e);
            throw new BusinessException("读取Excel数据异常");
        }
    }

    /**
     * 类型转换
     * @param data
     * @param clazz
     * @param format
     * @return
     */
    private Object stringToObject(String data,Class<?> clazz,String format){
        if(clazz.isAssignableFrom(String.class)){
            return data;
        }
        if(clazz.isAssignableFrom(Integer.class)){
            return Integer.valueOf(data);
        }
        if(clazz.isAssignableFrom(Long.class)){
            return Long.valueOf(data);
        }
        if(clazz.isAssignableFrom(Float.class)){
            return Float.valueOf(data);
        }
        if(clazz.isAssignableFrom(Double.class)){
            return Double.valueOf(data);
        }
        if(clazz.isAssignableFrom(Boolean.class)){
            return Boolean.valueOf(data);
        }
        if(clazz.isAssignableFrom(Date.class)){
            if(StringUtils.isNotBlank(format)){
                return DateUtil.parse(data,format);
            }
            return DateUtil.parse(data,"yyyy-MM-dd");
        }
        return null;
    }
}

导入配置类

@Getter
@Setter
public class ExcelImportConfigDto<T> {
    /**
     * 操作类型  需要调用端设置
     */
    private String operType;
    /**
     * 当前导入操作用户名 需要调用端设置
     */
    private String userId;
    /**
     * excel的配置dto 需要调用端设置
     */
    private Class<T> excelConfig;
    /**
     * Excel业务处理类 需要调用端设置
     */
    private ExcelImportBusinessHandle<T> excelImportBusinessHandle;

    /**
     * 导入数据最大行数 需要调用端设置  默认为100W行
     */
    private Integer maxLineLimit;

    /**
     * 每页处理数据，默认为1000行  导入是一页一页的导入，如果不同页的数据有依赖关系 那么可以把这个参数设置大些
     */
    private Integer pageSize;
    /**
     * 上传最原始的文件  需要调用端设置
     */
    private MultipartFile excelFile;

    /**
     * 文件相对路径 需要调用端设置
     */
    private String fileRelativePath;

    /**
     * 文件名称
     */
    private String fileName;


    /**
     * 上传的输入流
     */
    private InputStream upload;

    public ExcelImportConfigDto(MultipartFile excelFile,String userId,String operType,String fileRelativePath, Class<T> excelConfig, ExcelImportBusinessHandle<T> excelImportBusinessHandle) {
        this.operType = operType;
        this.excelConfig = excelConfig;
        this.fileRelativePath = fileRelativePath;
        this.excelImportBusinessHandle = excelImportBusinessHandle;
        this.excelFile = excelFile;
        this.userId = userId;
    }

}

导入钩子类

public interface ExcelImportBusinessHandle<T> {
    /**
     * 默认最大Excel导入行数
     */
    int MAX_EXCEL_EXPORT_LINES_LIMIT = 1_000_000;

    List<T> handleBusiness(List<T> list);

    /**
     * 数据导入结束后 的操作
     * @param flag 导入操作是否正常结束 true为正常结束，false为导出异常
     * @param configDto
     */
    default void postImportData(boolean flag,ExcelImportConfigDto<T> configDto){}
}

自定义注解

//1. 模拟的ExcelProperty注解，在原生的easyExcel里，用的是ExcelProperty注解表示导入导出字段这里分开了，因为导入和导出可能对应的字段不一样
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
@Inherited
//模拟的ExcelProperty注解
public @interface ImportExcelProperty {
    String[] value() default {""};
    int index() default -1;
    int order() default Integer.MAX_VALUE;
    Class<? extends Converter> converter() default AutoConverter.class;

    /**
     * 日期类型的格式化
     * @return
     */
    String format() default "";
}

@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
@Inherited
//模拟的ExcelProperty注解
public @interface ExportExcelProperty {

    String[] value() default {""};

    int index() default -1;

    int order() default Integer.MAX_VALUE;

    Class<? extends Converter> converter() default AutoConverter.class;

    String format() default "";
}

其他辅助类

public class BusinessException extends RuntimeException{
    
    private Integer code;
    private String message;

    public BusinessException(String message) {
        this.code = 1;
        this.message = message;
    }

    public BusinessException(Integer code,String message) {
        this.code = code;
        this.message = message;
    }

    public Integer getCode() {
        return code;
    }

    @Override
    public String getMessage() {
        return message;
    }
}

@Slf4j
public class CpuCountUtil {

    /**
     * 用于使用forkjoin时获取最大并行数
     * @param forkjoinName	调用方
     * @param threadNumber	有值使用自定义并行数，无值、小于等于0、大于最大CPU核数使用一半的CPU线程数
     * @return
     */
    public static int getCpuNumber(String forkjoinName,Integer threadNumber) {
        long start = System.nanoTime();
        try {
            int cpuNumber = Runtime.getRuntime().availableProcessors();
            if (null == threadNumber) {
                threadNumber = (cpuNumber/2)+1;
            }else if (threadNumber >= cpuNumber) {
                threadNumber = cpuNumber;
            }
        } catch (Exception e) {
            threadNumber = 2;
            log.warn("forkjoinName：{}获取CPU核数异常", forkjoinName);
        }
        log.info("forkjoinName：{}获取{}个CPU耗时（纳秒）：{}", forkjoinName, threadNumber, System.nanoTime()-start);
        return threadNumber;
    }
}

@Data
public class FileDownload {
    private int id;
    /**
     * 操作人姓名
     */
    private String insertUser;
    /**
     * 文件路径
     */
    private String filePath;
    /**
     * 文件名称
     */
    private String fileName;
    /**
     * 状态
     */
    private String status;
    /**
     * 状态描述
     */
    private String statusRemark;
    /**
     * 类型
     */
    private String type;
    /**
     * 是否加密：1:不加密；2:加密
     */
    private byte isEncrypted;
    /**
     * 文件打开密码
     */
    private String filePassword;
    /**
     * 操作时间
     */
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")  //取日期时使用
    @DateTimeFormat(pattern = "yyyy-MM-dd")//存日期时使用
    private Date insertTime;
    /**
     * 更新时间
     */
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")  //取日期时使用
    @DateTimeFormat(pattern = "yyyy-MM-dd")//存日期时使用
    private Date updateTime;
}

@Mapper
public interface FileDownloadMapper {

    /**
     * 存储一条信息
     * @param entity
     */
    void save(@Param("entity") FileDownload entity);

    /**
     * 通过操作人姓名分页查询列表
     * @param insertUser
     * @param pageStart
     * @param pageSize
     * @return
     */
    List<FileDownload> findByInsertUser(@Param("insertUser") String insertUser, @Param("pageStart") int pageStart
            , @Param("pageSize") int pageSize);

    /**
     * 通过操作人姓名查询总数
     * @param insertUser
     * @return
     */
    int countByInsertUser(@Param("insertUser") String insertUser);

    /**
     * 通过id查询一条数据
     * @param id
     * @return
     */
    FileDownload findOneById(@Param("id") int id);

    /**
     * 通过主键id更新一条信息
     * @param fileDownload
     * @return
     */
    int updateByPrimaryKey(@Param("entity") FileDownload fileDownload);

}

public enum FileStatusEnum {

    GENERATE_ING("in", "生成中"),
    GENERATE_SUCCESS("success", "生成成功"),
    GENERATE_FAILURE("fail", "生成失败");


    private final String key;
    private final String value;

    FileStatusEnum(String key, String value) {
        this.key = key;
        this.value = value;
    }

    public String getKey() {
        return key;
    }

    public String getValue() {
        return value;
    }

    public static String getValue(String key) {
        for (FileStatusEnum fileStatusEnum : values()) {
            if (key.equals(fileStatusEnum.getKey())) {
                return fileStatusEnum.getValue();
            }
        }
        return null;
    }
}

@Data
public class ResponseMessage implements Serializable {
    private int code = 1;
    private Object data;
    private String message = "执行成功";

    public ResponseMessage() {
    }
    public ResponseMessage(int code, String message) {
        this.code = code;
        this.message = message;
    }
    public ResponseMessage(int code, Object data, String message) {
        this.code = code;
        this.data = data;
        this.message = message;
    }

    public static ResponseMessage faild(String message){
        ResponseMessage result = new ResponseMessage();
        result.setCode(0);
        result.setMessage(message);
        return result;
    }

    public static ResponseMessage success(Object data,String message){
        ResponseMessage result = new ResponseMessage();
        result.setMessage(message);
        result.setData(data);
        return result;
    }

}

示例

@Data
public class ExcelImportParentConfig {
    @ImportExcelProperty(index = Integer.MAX_VALUE,value = "错误信息")
    public String errorInfo;
}

public class MyDbDto {
}

@Data
public class MyImportExportConfig extends ExcelImportParentConfig {
    //导入导出都有这个字段
    @ImportExcelProperty(index = 10,value = "字段1")
    @ExportExcelProperty(index = 10,value = "字段1")
    private String f1;

    //导入导出都有这个字段
    @ImportExcelProperty(index = 20,value = "字段2")
    @ExportExcelProperty(index = 20,value = "字段2")
    private String f2;

    //导出特有字段
    @ExportExcelProperty(index = 30,value = "字段3")
    private String f3;

    //导入特有字段
    @ImportExcelProperty(index = 30,value = "字段4")
    private String f4;
}

@Service
public class Test {

    @Resource
    private MyExcelContent myExcelContent;
    private String PATH_TO_EXCEL="test";
    public ResponseMessage importExcel(String userId, MultipartFile excelFile) throws Exception {

        ExcelImportConfigDto<MyImportExportConfig> configDto = new ExcelImportConfigDto(excelFile, userId,"测试导入", "", MyImportExportConfig.class,
                new ExcelImportBusinessHandle<MyImportExportConfig>() {
                    @Override
                    public List<MyImportExportConfig> handleBusiness(List<MyImportExportConfig> list) {
                        //数据验证时，异常的数据集合
                        List<MyImportExportConfig> errors = new ArrayList<>();
                        //需要入库的数据集合
                        List<MyImportExportConfig> success = new ArrayList<>();
                        //数据验证，即把list里的数据分成异常数据和正确数据
                        
                        if(CollectionUtils.isNotEmpty(success)){
                            //把正确数据批量入库
                            
                        }
                        return errors;
                    }
                });

        return myExcelContent.exelImport(configDto);
    }

    public ResponseMessage exportExcel(String userId) {
        //根据查询条件查询数据总数
        Long dataCount = null;

        ExcelExportConfigDto configDto = new ExcelExportConfigDto(PATH_TO_EXCEL, userId,"测试导出", MyImportExportConfig.class,
                new ExcelExportBusinessHandle<MyImportExportConfig>() {
                    @Override
                    public Integer getCount() {
                        return dataCount.intValue();
                    }

                    @Override
                    public List<MyImportExportConfig> getDetailList(Integer pageIndex, Integer pageSize) {
                        //根据查询条件分页查询数据
                        List<MyDbDto> dbResults = null;
                        List<MyImportExportConfig> results = new ArrayList<>();
                        if(CollectionUtils.isNotEmpty(dbResults)){
                            dbResults.forEach(data->{
                                MyImportExportConfig config = new MyImportExportConfig();
                                //将数据库查询出的数据转换成需要导出的数据类型
                                results.add(config);
                            });

                        }
                        return results;
                    }
                });
        ResponseMessage results = myExcelContent.exelExport(configDto);
        return results;
    }
}

扩展

@Target(ElementType.TYPE)
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface ExcelEnum {

}

//上述示例，是需要先创建导入导出字段配置类，如MyImportExportConfig，但如果导入导出来源于配置文件或者数据库或者以前为枚举配置的，怎么办呢
//为了保证底层架构不变，我们可以在此基础上进行扩展
/**
 * 类功能描述：将由ExcelEnum注解修饰的类的类缓存起来，
 * key为由ExcelEnum注解修饰的Class
 * value
 * key为导入导出标识  value为该类的getExportFieldMap（或者getImportFieldMap）静态方法返回的数据构造成新的Class，该Class主要是为了导入导出而生成的
 */
@Component
@Slf4j
public class MyExcelHandleRunner implements ApplicationRunner {
    public static final Map<Class<?>,Map<String,Class<?>>> EXCEL_EXPORT_IMPORT_CLASS_MAP = new HashMap<>();
    //和springboot main方法所在类的package一致即可
    private static final String BASE_PACKAGE = "";
    private static final String RESOURCE_PATTERN = "/**/*.class";
    public static final String EXPORT="export";
    public static final String IMPORT="import";

    @Override
    public void run(ApplicationArguments args) throws Exception {
        handle();
    }

    /**
     * 为EXCEL_EXPORT_IMPORT_CLASS_MAP初始化值
     * key为由ExcelEnum注解修饰的Class
     * value
     * key为导入导出标识  value为该类的getExportFieldMap（或者getImportFieldMap）静态方法返回的数据构造成新的Class，该Class主要是为了导入导出而生成的
     *
     *
     * @throws Exception
     */
    private void handle()throws Exception {
        //使用groovy进行动态类生成
        GroovyClassLoader groovyClassLoader = new GroovyClassLoader();
        //获取所有被ExcelEnum注解修饰的类
        List<Class<?>> allExcelEnum = getAllExcelEnum();
        //遍历被ExcelEnum修饰的类
        for(Class<?> enumClazz:allExcelEnum){
            Map<String,Class<?>> map = new HashMap<>();
            String enumName = enumClazz.getSimpleName();
            //获取静态方法getExportFieldMap并执行该方法，获得一个map，key为字段属性，value为导入导出标题
            Method exportMethod = enumClazz.getDeclaredMethod("getExportFieldMap");
            Map<String,String> exportMap = (Map<String,String>)exportMethod.invoke(null);
            //动态创建用ImportExcelProperty注解修饰的导入导出配置类
            Class<?> aClass = initExport(enumName, exportMap, groovyClassLoader);
            if(aClass!=null){
                map.put(EXPORT, aClass);
            }

            Method importMethod = enumClazz.getDeclaredMethod("getImportFieldMap");
            Map<String,String> importMap = (Map<String,String>)importMethod.invoke(null);
            Class<?> bClass = initImport(enumName, importMap, groovyClassLoader);
            if(bClass!=null){
                map.put(IMPORT, bClass);
            }


            EXCEL_EXPORT_IMPORT_CLASS_MAP.put(enumClazz, map);
        }
    }

    /**
     * 构造导出配置类
     * @param enumName
     * @param exportMap
     * @param groovyClassLoader
     * @return
     */
    private Class<?> initExport(String enumName,Map<String,String> exportMap,GroovyClassLoader groovyClassLoader){
        if(exportMap==null||exportMap.isEmpty()){
            return null;
        }

        StringBuilder stringBuilder = new StringBuilder("package com.test.pojo.excel;\n");
        stringBuilder.append("import com.test.excel.ExcelImportParentConfig;\n" +
                "import com.test.excel.ExportExcelProperty;\n" +
                "import com.test.excel.ImportExcelProperty;\n");

        stringBuilder.append("public class "+ enumName +"ExportConfig extends ExcelImportParentConfig{\n");
        stringBuilder.append("\n");

        int i=0;
        for(Map.Entry<String,String> entry:exportMap.entrySet()){
            String k = entry.getKey();
            String v = entry.getValue();
            stringBuilder.append("@ExportExcelProperty(index = " + i + ", value = \"" + v + "\")\n");
            stringBuilder.append("private String "+k+";\n");
            i++;
        }

        stringBuilder.append("}\n");
        Class<?> clazz = groovyClassLoader.parseClass(stringBuilder.toString());
        return clazz;
    }

    /**
     * 构造导入配置类
     * @param enumName
     * @param importMap
     * @param groovyClassLoader
     * @return
     */
    private Class<?> initImport(String enumName,Map<String,String> importMap,GroovyClassLoader groovyClassLoader){
        if(importMap==null||importMap.isEmpty()){
            return null;
        }
        StringBuilder stringBuilder = new StringBuilder("package com.test.pojo.excel;\n");
        stringBuilder.append("import com.test.excel.ExcelImportParentConfig;\n" +
                "import com.test.excel.ExportExcelProperty;\n" +
                "import com.test.excel.ImportExcelProperty;\n");

        stringBuilder.append("public class "+ enumName +"ImportConfig extends ExcelImportParentConfig{\n");
        stringBuilder.append("\n");

        int i=0;
        for(Map.Entry<String,String> entry:importMap.entrySet()){
            String k = entry.getKey();
            String v = entry.getValue();
            stringBuilder.append("@ImportExcelProperty(index = " + i + ", value = \"" + v + "\")\n");
            stringBuilder.append("private String "+k+";\n");
            i++;
        }

        stringBuilder.append("}\n");
        Class<?> clazz = groovyClassLoader.parseClass(stringBuilder.toString());
        return clazz;
    }

    /**
     * 获取com.test路径下所有被ExcelEnum修饰的类
     * @return
     */
    private List<Class<?>> getAllExcelEnum(){
        //spring工具类，可以获取指定路径下的全部类
        ResourcePatternResolver resourcePatternResolver = new PathMatchingResourcePatternResolver();
        List<Class<?>> results = new ArrayList<>();
        try {
            String pattern = ResourcePatternResolver.CLASSPATH_ALL_URL_PREFIX +
                    ClassUtils.convertClassNameToResourcePath(BASE_PACKAGE) + RESOURCE_PATTERN;
            Resource[] resources = resourcePatternResolver.getResources(pattern);
            //MetadataReader 的工厂类
            MetadataReaderFactory readerfactory = new CachingMetadataReaderFactory(resourcePatternResolver);
            for (Resource resource : resources) {
                //用于读取类信息
                MetadataReader reader = readerfactory.getMetadataReader(resource);
                //扫描到的class
                String classname = reader.getClassMetadata().getClassName();
                Class<?> clazz = Class.forName(classname);
                //判断是否有指定主解
                ExcelEnum anno = clazz.getAnnotation(ExcelEnum.class);
                if (anno != null) {
                    results.add(clazz);
                }
            }
        } catch (IOException | ClassNotFoundException e) {
            log.error("获取导入导出枚举异常",e);
        }
        return results;
    }
}


//必须要加注解ExcelEnum，这样在spring启动的时候会被自定义的runner动态生成底层框架需要的导入导出配置类
@ExcelEnum
@Component
public class FromDbExcelConfig {
    private static Map<String,String> importHeaderMaps;
    private static Map<String,String> exportHeaderMaps;
    @PostConstruct
    public void init() {
        //从数据库获取导入导出配置
        importHeaderMaps = null;
        exportHeaderMaps = null;
    }

    /**
     * 为自动构建导出配置类调用
     * @return
     */
    public static Map<String,String> getExportFieldMap(){
        return exportHeaderMaps;
    }

    /**
     * 为自动构建导出配置类调用
     * @return
     */
    public static Map<String,String> getImportFieldMap(){
        return importHeaderMaps;
    }
}

@Service
@Slf4j
public class Test {
    @Resource
    private MyExcelContent myExcelContent;
    private static final String PATH_TO_EXCEL="test1";


    public ResponseMessage exportExcel() {

        //根据查询条件查询总数
        Integer dataCount = null;
        //导出字段和标题
        Map<String, String> headerMap = FromDbExcelConfig.getExportFieldMap();

        //获取动态生成的导出配置类
        Class<?> configClass = MyExcelHandleRunner.EXCEL_EXPORT_IMPORT_CLASS_MAP.get(FromDbExcelConfig.class).get(MyExcelHandleRunner.EXPORT);
        ExcelExportConfigDto configDto = new ExcelExportConfigDto(PATH_TO_EXCEL, "userId","测试扩展导出", configClass,
                new ExcelExportBusinessHandle() {
                    @Override
                    public Integer getCount() {
                        return dataCount;
                    }

                    @Override
                    public List getDetailList(Integer pageIndex, Integer pageSize) {
                        List result = new ArrayList();
                        try {
                            //分页查询
                            List<MyDbDto> exportList = null;

                            for (MyDbDto data : exportList) {
                                Object o = configClass.newInstance();
                                //类型转换
                                for (Map.Entry<String, String> entry : headerMap.entrySet()) {

                                    String key = entry.getKey();
                                    Field declaredField = configClass.getDeclaredField(key);
                                    declaredField.setAccessible(true);
                                    Object value = declaredField.get(data);
                                    String columnValue = "-";
                                    if (value != null) {
                                        if (StringUtils.isNotBlank(value.toString())) {
                                            columnValue = value.toString();
                                        }
                                    }
                                    declaredField.set(o, columnValue);
                                }

                                result.add(o);
                            }

                        }catch (Exception e) {
                            log.error("导出异常", e);
                        }

                        return result;
                    }
                });
        return myExcelContent.exelExport(configDto);
    }
}