Master Fast, Low-Memory Excel Export in Spring Boot with EasyExcel

EasyExcel

EasyExcel is a Java‑based open‑source project for simple, low‑memory Excel read/write. It can handle hundreds of megabytes of Excel while keeping memory usage minimal. It reads a 75 MB (46 W rows × 25 columns) file in under a minute with 64 MB memory; the fast mode is even quicker but uses a little over 100 MB.

Spring Boot starter dependency

Convenient for web environments using easyexcel, already uploaded to Maven repository.

<dependency>
    <groupId>com.pig4cloud.excel</groupId>
    <artifactId>excel-spring-boot-starter</artifactId>
    <version>0.0.1</version>
</dependency>

Usage

Simply return a List from a Controller and add the @ResponseExcel annotation.

@Documented
@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ResponseExcel {
    String name() default "";
    ExcelTypeEnum suffix() default ExcelTypeEnum.XLSX;
    String password() default "";
    String[] sheet() default {};
    boolean inMemory() default false;
    String template() default "";
    String[] include() default {};
    String[] exclude() default {};
    Class<? extends WriteHandler>[] writeHandler() default {};
    Class<? extends Converter>[] converter() default {};
}

Basic usage

Return a single sheet and export all fields.

@ResponseExcel(name = "lengleng", sheet = "demoList")
@GetMapping("/e1")
public List<DemoData> e1() {
    List<DemoData> dataList = new ArrayList<>();
    for (int i = 0; i < 100; i++) {
        DemoData data = new DemoData();
        data.setUsername("tr1" + i);
        data.setPassword("tr2" + i);
        dataList.add(data);
    }
    return dataList;
}

@Data
public class DemoData {
    private String username;
    private String password;
}

Custom field properties

@Data
public class DemoData {
    @ColumnWidth(50) // define column width
    @ExcelProperty("用户名") // define column name
    private String username;
    @ExcelProperty("密码")
    private String password;
}

Ignore some fields

@Data
public class DemoData {
    @ColumnWidth(50) // define column width
    @ExcelProperty("用户名") // define column name
    @ContentStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 40)
    private String username;
    @ExcelProperty("密码")
    private String password;
}

Export multiple sheets

@ResponseExcel(name = "lengleng", sheet = {"第一个sheet","第二个sheet"})
@GetMapping("/e1")
public List<List<DemoData>> e1() {
    List<List<DemoData>> lists = new ArrayList<>();
    lists.add(list());
    lists.add(list());
    return lists;
}

Set export password

@ResponseExcel(name = "lengleng", sheet = "sheetName", password = "lengleng")
@GetMapping("/e1")
public List<List<DemoData>> e1() {
    List<List<DemoData>> lists = new ArrayList<>();
    lists.add(list());
    lists.add(list());
    return lists;
}

Advanced usage: template export

@ResponseExcel(name = "模板测试excel", sheet = "sheetName", template = "example.xlsx")
@GetMapping("/e1")
public List<DemoData> e1() {
    return list();
}

Other usage

Theoretically supports most configurations of alibaba/easyexcel v2.1.6.

Supports native configuration annotations of alibaba/easyexcel.

GitHub starter address, can be forked and customized.

Reference

[1]

alibaba/easyexcel: https://github.com/alibaba/easyexcel

[2]

alibaba/easyexcel: https://www.yuque.com/easyexcel/doc/write

[3]

GitHub starter address, can be forked and customized: https://github.com/pigxcloud/excel-spring-boot-starter