听说你的导入导出这样操作？在做这些之前，我们还是要致敬EasyExcel工具类的制作团队，真的很强；我在三年前吧，用ea

在做这些之前，我们还是要致敬EasyExcel工具类的制作团队，真的很强；我在三年前吧，用easyExcel来导入导出百万级别数据测试，内存真的稳的狠；强烈推荐各位听油们使用EasyExcel工具！

项目中存在很多基础的导出操作，比方说下载订单、账单数据，那其实相对来讲更多的是比较的通用的表格头以及数据即可满足要求，比较复杂的表格设置我们本次不会去讲说给抽一个公共的方法去做，因为这个你只能慢慢按照需求大大去完善
除了导出功能，本次读取表格读取表格也是一个相对来讲常用的操作，虽然说频次不及导出，但实际做起来还是有些讨厌的地方要处理，我这里给出了下面的做法，供各位听油们参考

好了，废话不多说，LetMeSeeCode！

导出功能

/**
 * 导出的对象都会继承
 */
public static abstract class BaseWriteExcelData implements Serializable {

    public static final String SHEET_NAME_METHOD = "sheetName";
    protected String sheetName() {
        return "";
    }
}
    
@Data
@AllArgsConstructor
@NoArgsConstructor
public static class WriteExcelContext<E extends BaseWriteExcelData> {
    @NotNull(message = "导出文件名称不可为空")
    private String fileName;
    @NotNull(message = "导致表格类不可为空")
    private List<Class<E>> clsList;
    @NotNull(message = "查询数据方法不可为空")
    private List<Function<Integer, List<E>>> toWriteDataSupplierList;
}

/**
 * 测试导出对象
 */
@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class TestExportDTO extends BaseWriteExcelData {

    @ExcelProperty(value = "测试ID", index = 0)
    private String testNum;

    @Override
    protected String sheetName() {
        return "测试";
    }
}

/**
 * 导出工具方法
 */
@SneakyThrows
    public static String batchExportExcelData(WriteExcelContext<BaseWriteExcelData> writeExcelContext) {
        if (StringUtils.isBlank(writeExcelContext.getFileName())) throw new BizException("导出文件名称不可为空");
        if (CollectionUtils.isEmpty(writeExcelContext.getClsList())
            || CollectionUtils.isEmpty(writeExcelContext.getToWriteDataSupplierList())
            || writeExcelContext.getClsList().size() != writeExcelContext.getToWriteDataSupplierList().size())
            throw new BizException("请检查导出表格类与查询数据方法集合数量是否匹配");
        File tmpFile = null;
        ExcelWriter excelWriter = null;
        Integer pageNum = NumberUtils.INTEGER_ONE;
        String fileName = writeExcelContext.getFileName().replaceAll(XLS, StringUtils.EMPTY).replaceAll(XLSX, StringUtils.EMPTY);
        try {
            tmpFile = File.createTempFile(fileName, XLSX);
            if (writeExcelContext.getClsList().size() == NumberUtils.INTEGER_ONE) {
                excelWriter = EasyExcel.write(tmpFile, writeExcelContext.getClsList().get(0)).build();
                WriteSheet writeSheet = EasyExcel.writerSheet(ReflectUtil.invoke(ReflectUtil.newInstance(writeExcelContext.getClsList().get(0).getName())
                        , SHEET_NAME_METHOD, new Object[]{}).toString()).build();
                List<BaseWriteExcelData> data = writeExcelContext.getToWriteDataSupplierList().get(0).apply(pageNum);
                while (CollectionUtils.isNotEmpty(data)) {
                    excelWriter.write(data, writeSheet);
                    data = writeExcelContext.getToWriteDataSupplierList().get(0).apply(++pageNum);
                }
            } else {
                excelWriter = EasyExcel.write(tmpFile).build();
                for (int index = 0; index < writeExcelContext.getClsList().size(); index++) {
                    Function<Integer, List<BaseWriteExcelData>> toWriteDataSupplier = writeExcelContext.getToWriteDataSupplierList().get(index);
                    List<BaseWriteExcelData> data = toWriteDataSupplier.apply(pageNum);
                    WriteSheet writeSheet = EasyExcel.writerSheet(index
                            , ReflectUtil.invoke(ReflectUtil.newInstance(writeExcelContext.getClsList().get(index).getName())
                                    , SHEET_NAME_METHOD, new Object[]{}))
                            .head(writeExcelContext.getClsList().get(index)).build();
                    while (CollectionUtils.isNotEmpty(data)) {
                        excelWriter.write(data, writeSheet);
                        data = toWriteDataSupplier.apply(++pageNum);
                    }
                }
            }
            excelWriter.finish();
            String url = OssUtils.uploadWithFileName(tmpFile);
            logger.info("文件地址:{}", url);
            return url;
        } finally {
            if (Objects.nonNull(excelWriter)) {
                excelWriter.finish();
            }
            if (tmpFile.exists()) {
                boolean flag = tmpFile.delete();
                if (!flag) tmpFile.deleteOnExit();
            }
        }
    }

/**
 * 使用方式
 */
ExcelExportUtil.batchExportExcelData(new WriteExcelContext(
    getFileName(),
    Arrays.asList(TestExportDTO.class),
    Arrays.asList((Function<Integer, List>) integer -> {
    req.setPageNo(integer);
    // 分页获取数据
    List<TestExportDTO> list = queryList(req);
    return list;
})));

经过上边的方法就完成了分页导出数据的操作，数据查询，与导出操作解偶耦开，相对来讲是我这里目前的导出方式，只是抛砖引玉哈，有更好的请评论走起
好了顺着上边的方法，我们也将读取表格的方法提供掉

读取功能

/**
 * 读取的对象都会继承
 */
public static abstract class BaseReadExcelData implements Serializable {
        protected boolean legalRowData() {
            return true;
        }
    }

@Data
public static class ReadExcelTestData extends BaseReadExcelData {
    @ExcelProperty("列名1")
    private String columnNameOne;
    @ExcelProperty("列名2")
    private String columnNameTwo;

}

@Slf4j
public class ReportExcelReadListener implements ReadListener<ExcelExportUtil.ReadExcelTestData> {
    public static int BATCH_COUNT = 100;
    /**
     * Temporary storage of data
     */
    private List<ExcelExportUtil.ReadExcelTestData> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
    /**
     * consumer
     */
    private final Consumer<List<ExcelExportUtil.ReadExcelTestData>> consumer;

    public ReportExcelReadListener(Consumer<List<ExcelExportUtil.ReadExcelTestData>> consumer) {
        this.consumer = consumer;
    }

    @Override
    public void invoke(ExcelExportUtil.ReadExcelTestData data, AnalysisContext context) {
        Integer rowNum = context.readRowHolder().getRowIndex() + NumberUtils.INTEGER_ONE;
        if (!data.legalRowData()){
            throw BizException("第" + rowNum + "行数据缺失");
        }
        cachedDataList.add(data);
        if (cachedDataList.size() >= BATCH_COUNT) {
            consumer.accept(cachedDataList);
            cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        consumer.accept(cachedDataList);
    }

}

/**
 * 批量读取excel表格数据
 * @param url
 * @param cls
 * @param consumer
 */
@SneakyThrows
public static void batchReadExcelData(String url, Class<? extends BaseReadExcelData> cls, Consumer<List<? extends BaseReadExcelData>> consumer) {
    String suffix = url.endsWith(XLSX) ? XLSX : (url.endsWith(XLS) ? XLS : null);
    if (StringUtils.isBlank(suffix)) {
        return;
    }
    File tmpFile = null;
    try {
        tmpFile = File.createTempFile(String.valueOf(ThreadLocalRandom.current().nextLong(Long.MAX_VALUE)), suffix);

        URLConnection urlConnection = new URL(OssBucketEnum.BUCKET_DEFAULT.getDomain() + File.separator + url).openConnection();
        urlConnection.setRequestProperty("User-Agent","Mozilla/4.0 (compatible; MSIE 5.0; Windows XP; DigExt)");
        urlConnection.setConnectTimeout(6000);

        FileUtils.writeByteArrayToFile(tmpFile, IOUtils.toByteArray(urlConnection));

        EasyExcel.read(tmpFile, cls, new ReportExcelReadListener(dataList -> consumer.accept(dataList.stream()
                .map(data -> cls.cast(data)).collect(Collectors.toList())))).sheet().doRead();

    }catch (ExcelDataConvertException ex){
        throw BizException(ex.getMessage());
    } finally {
        if (tmpFile.exists()) {
            boolean flag = tmpFile.delete();
            if (!flag) tmpFile.deleteOnExit();
        }
    }
}

那听油们看下，上边就是我这里的导入导出的具体操作，代码上还有很多细节需要优化，如果有需要拿去即用哈，然后有哪些点想讨论可以在评论里细聊！