Skip to content

Excel流式导出

清沐 edited this page Mar 23, 2020 · 42 revisions

流式导出与默认导出不同之处在于:流式导出采用生产者消费者模式,允许分批获取数据,分批写入Excel,且默认采用SXSSF模式,内存占用量极低,真正意义上实现海量数据导出,另外,流式导出支持zip压缩包等独有特性。

使用流式导出分为三步:

  1. 导出配置
DefaultStreamExcelBuilder<ArtCrowd> streamExcelBuilder = DefaultStreamExcelBuilder
        .of(ArtCrowd.class) // 如导出Map类型数据,请使用of(Map.class)
        .threadPool(Executors.newFixedThreadPool(10))// 线程池,可选
        .capacity(10_000)// 容量设定,在主动划分excel使用,可选
        .start();
  1. 数据追加

append参数可为列表,也可为单个数据,建议使用单个数据追加,如Bean、Map

streamExcelBuilder.append(data);
  1. 完成构建
Workbook workbook = streamExcelBuilder.build();

如需最大化提升导出性能,请调用noStyle()方法全面禁止样式.

DefaultStreamExcelBuilder默认采用SXSSF模式(内存占用低)导出,该模式下不支持自动列宽.

附件导出示例:

try-with-resource方式不可返回Workbook,因为在close方法中会关闭Wrokbook

try (DefaultStreamExcelBuilder<ArtCrowd> streamExcelBuilder = DefaultStreamExcelBuilder
                .of(ArtCrowd.class)
                .threadPool(Executors.newFixedThreadPool(10))
                .start()) {
    // 多线程异步获取数据并追加至excel,join等待线程执行完成
    List<CompletableFuture> futures = new ArrayList<>();
    for (int i = 0; i < 100; i++) {
        CompletableFuture future = CompletableFuture.runAsync(() -> {
            List<ArtCrowd> dataList = this.getDataList();
            // 数据追加
            streamExcelBuilder.append(dataList);
        });
        futures.add(future);
    }
    futures.forEach(CompletableFuture::join);
    // 最终构建
    Workbook workbook = defaultExcelBuilder.build();
    AttachmentExportUtil.export(workbook, "艺术生信息", response);
}

多文件导出示例:

设置Excel容量(capacity(10_000)),如设置,则当Excel的行数达到容量后产生新的Excel

try (DefaultStreamExcelBuilder<ArtCrowd> streamExcelBuilder = DefaultStreamExcelBuilder
        .of(ArtCrowd.class)
        .threadPool(Executors.newFixedThreadPool(10))
        .capacity(10_000)
        .start()) {
    ......
    // 最终构建
    List<Path> paths = streamExcelBuilder.buildAsPaths();
    // do something
}

zip导出示例:

设置Excel容量(capacity(10_000))

try (DefaultStreamExcelBuilder<ArtCrowd> streamExcelBuilder = DefaultStreamExcelBuilder
        .of(ArtCrowd.class)
        .threadPool(Executors.newFixedThreadPool(10))
        .capacity(10_000)
        .start()) {
    ......
    // 最终构建
    Path zip = streamExcelBuilder.buildAsZip("test");
    AttachmentExportUtil.export(zip,"finalName.zip",response);
}

导出使用注解

  1. @ExcelModel(includeAllField,excludeParent,workbookType,sheetName,useFieldNameAsTitle,defaultValue)
  2. @IgnoreColumn
  3. @ExcelColumn(title,order,format,groups,defaultValue)

对应注解详情请见:注解

Clone this wiki locally