Excel streaming export

The difference between the streaming export and the default export is that the streaming export adopts the producer consumer mode, which allows batch data acquisition and batch writing to excel. The default mode is sxssf, which has a very low memory consumption. In a real sense, the streaming export realizes massive data export. In addition, the streaming export supports the unique characteristics of zip compression package.

There are three steps to using streaming export:

1. Export configuration

DefaultStreamExcelBuilder<ArtCrowd> streamExcelBuilder = DefaultStreamExcelBuilder
        .of(ArtCrowd.class) // If exporting map type data, please use of(map.class)
        .threadPool(Executors.newFixedThreadPool(10))// Thread pool, optional
        .capacity(10_000)// Capacity setting, used in active division excel, optional
        .start();

2. Data addition

The append parameter can be a list or a single data. It is recommended to append a single data, such as bean and map

// streamExcelBuilder.append(data);
streamExcelBuilder.asyncAppend(this::getDataList);

In some cases, personalized headers or summaries may be required. These requirements may involve merging rows, columns, styles and other complex layouts. DefaultStreamExcelBuilder itself is not able to support bean. In version 3.6.0 and later, DefaultStreamExcelBuilder supports template appending. Complex layouts are defined in the template. The appending methods are as follows:

DefaultStreamExcelBuilder<ArtCrowd> streamExcelBuilder = DefaultStreamExcelBuilder
        .of(ArtCrowd.class)
        .templateHandler(FreemarkerTemplateHandler.class)// Add template data, optional, suitable for extremely personalized data export
        .start();

Map<String,Object> dataMap=new HashMap<>();
dataMap.put("title","测试");
streamExcelBuilder.append("/templates/xxx.ftl",dataMap);

3. Completion of build

Workbook workbook = streamExcelBuilder.build();

To maximize export performance, call the nostyle() method to disable styles completely.

Defaultstreamexcelbuilder exports in sxssf mode (low memory consumption) by default, which does not support automatic column width.

Attachment export example:

Try with resource mode cannot return workbook because it will be closed in close method

try (DefaultStreamExcelBuilder<ArtCrowd> streamExcelBuilder = DefaultStreamExcelBuilder
                .of(ArtCrowd.class)
                .threadPool(Executors.newFixedThreadPool(10))
                .start()) {
    // Multithread obtains data asynchronously and appends it to excel. Join waits for thread execution to complete
    for (int i = 0; i < 100; i++) {
       // Data addition
       defaultExcelBuilder.asyncAppend(this::getDataList);
    }
    // Final build
    Workbook workbook = streamExcelBuilder.build();
    AttachmentExportUtil.export(workbook, "艺术生信息", response);
}

Multi file export example:

Set the capacity of Excel (10_000). If it is set, a new EXCEL will be generated when the number of Excel lines reaches the capacity

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

Zip export example:

Set excel capacity（capacity(10_000)）

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

Export using annotations

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

For details of corresponding notes, please refer to notes