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.

<code>&lt;dependency&gt;
    &lt;groupId&gt;com.pig4cloud.excel&lt;/groupId&gt;
    &lt;artifactId&gt;excel-spring-boot-starter&lt;/artifactId&gt;
    &lt;version&gt;0.0.1&lt;/version&gt;
&lt;/dependency&gt;
</code>

Usage

Simply return a

List

from a Controller and add the

@ResponseExcel

annotation.

<code>@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 {};
}
</code>

Basic usage

Return a single sheet and export all fields.

<code>@ResponseExcel(name = "lengleng", sheet = "demoList")
@GetMapping("/e1")
public List&lt;DemoData&gt; e1() {
    List&lt;DemoData&gt; 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;
}
</code>

Custom field properties

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

Ignore some fields

<code>@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;
}
</code>

Export multiple sheets

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

Set export password

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

Advanced usage: template export

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

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