Boost Excel Import/Export Performance in Spring Boot with EasyExcel

Why not use native POI

Apache POI is the low‑level Excel library, but using its raw API leads to three major problems: massive memory consumption that easily triggers OOM (e.g., 100k rows consume 1‑2 GB), repetitive boilerplate code for each cell, and missing handling for edge cases such as date formats, numeric precision, enum translation, header validation, and error‑row location.

EasyExcel as the optimal solution

Extremely low memory usage : SAX‑based event processing reads and writes rows one by one, keeping memory at only a few megabytes even for millions of rows.

Annotation‑driven development : Adding @ExcelProperty (and optional format annotations) to entity fields eliminates manual cell handling.

Rich built‑in capabilities : Automatic conversion for dates, numbers, enums; support for complex headers, merged cells, custom styles, and template filling.

Asynchronous read/write : Listener callbacks allow row‑by‑row processing, decoupling business logic from parsing.

Production‑grade reliability : Widely used inside Alibaba, community‑tested, and stable.

Code examples

1. Core Maven dependencies

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.3.2</version>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <optional>true</optional>
</dependency>

2. Define Excel mapping entity

@Data
@ColumnWidth(20) // global default column width
public class UserExcelVO {
    @ExcelProperty(value = "用户ID", index = 0)
    private Long userId;

    @ExcelProperty(value = "用户名", index = 1)
    private String username;

    @ExcelProperty(value = "手机号", index = 2)
    private String phone;

    @ExcelProperty(value = "所属部门", index = 3)
    private String deptName;

    @DateTimeFormat("yyyy-MM-dd HH:mm:ss")
    @ExcelProperty(value = "创建时间", index = 4)
    @ColumnWidth(25)
    private LocalDateTime createTime;

    @ExcelProperty(value = "状态", index = 5)
    private String status;
}

3. One‑line export endpoint

@RestController
@RequestMapping("/excel")
public class ExcelController {

    @Autowired
    private UserService userService;

    @GetMapping("/user/export")
    public void exportUser(HttpServletResponse response) throws IOException {
        List<UserExcelVO> list = userService.listAllUser();
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        String fileName = URLEncoder.encode("用户数据导出", StandardCharsets.UTF_8).replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        EasyExcel.write(response.getOutputStream(), UserExcelVO.class)
                .sheet("用户列表")
                .doWrite(list);
    }
}

4. Import with a generic listener

public class CommonExcelListener<T> extends AnalysisEventListener<T> {
    private static final int BATCH_SIZE = 1000;
    private final List<T> batchList = new ArrayList<>(BATCH_SIZE);
    private final Consumer<List<T>> batchConsumer;

    public CommonExcelListener(Consumer<List<T>> batchConsumer) {
        this.batchConsumer = batchConsumer;
    }

    @Override
    public void invoke(T data, AnalysisContext context) {
        batchList.add(data);
        if (batchList.size() >= BATCH_SIZE) {
            batchConsumer.accept(batchList);
            batchList.clear();
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        if (!batchList.isEmpty()) {
            batchConsumer.accept(batchList);
            batchList.clear();
        }
    }
}

@PostMapping("/user/import")
public Result<String> importUser(MultipartFile file) throws IOException {
    CommonExcelListener<UserExcelVO> listener = new CommonExcelListener<>(batch -> {
        userService.batchSaveUser(batch);
    });
    EasyExcel.read(file.getInputStream(), UserExcelVO.class, listener)
            .sheet()
            .doRead();
    return Result.success("导入成功");
}

5. Custom converter for enum/dictionary fields

public class StatusConverter implements Converter<Integer> {
    @Override
    public Class<Integer> supportJavaTypeKey() { return Integer.class; }

    @Override
    public WriteCellData<?> convertToExcelData(Integer value, ExcelContentProperty contentProperty,
                                                GlobalConfiguration globalConfiguration) {
        if (value == null) { return new WriteCellData<>(""); }
        return new WriteCellData<>(value == 1 ? "启用" : "禁用");
    }
}

Usage in the entity:

@ExcelProperty(value = "状态", index = 5, converter = StatusConverter.class)
private Integer status;

6. Row‑level validation with JSR‑380

// Inside the listener's invoke method
Set<ConstraintViolation<T>> violations = validator.validate(data);
if (!violations.isEmpty()) {
    int rowIndex = context.readRowHolder().getRowIndex() + 1;
    String msg = violations.iterator().next().getMessage();
    errorList.add("第" + rowIndex + "行：" + msg);
}

7. Complex multi‑level header and merged cells

public class OrderExcelVO {
    @ExcelProperty({"订单信息", "订单编号"})
    private String orderNo;

    @ExcelProperty({"订单信息", "下单时间"})
    private LocalDateTime orderTime;

    @ExcelProperty({"收货信息", "收货人"})
    private String receiver;

    @ExcelProperty({"收货信息", "收货地址"})
    private String address;
}

8. Paginated streaming export for tens of millions of rows

@GetMapping("/user/export/big")
public void exportBigUser(HttpServletResponse response) throws IOException {
    String fileName = URLEncoder.encode("全量用户数据", StandardCharsets.UTF_8).replaceAll("\\+", "%20");
    response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
    int pageSize = 1000;
    int pageNum = 1;
    ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), UserExcelVO.class)
            .sheet("用户列表")
            .build();
    while (true) {
        List<UserExcelVO> pageList = userService.listUserByPage(pageNum, pageSize);
        if (pageList.isEmpty()) { break; }
        excelWriter.write(pageList);
        pageList.clear();
        pageNum++;
    }
    excelWriter.finish();
}

9. Template‑based export for fixed‑style reports

Map<String, Object> data = new HashMap<>();
data.put("name", "张三");
data.put("amount", 12800);
EasyExcel.write(response.getOutputStream())
        .withTemplate("template/invoice_template.xlsx")
        .sheet()
        .doFill(data);

Common pitfalls and solutions

OOM when exporting large files – caused by loading the entire result set into a List; solved by pagination + batch write, or async + object storage for extremely large files.

Chinese filename garbled – caused by inconsistent browser handling of raw filenames; solved by using the standard filename*=utf-8'' encoding.

Partial import success without error location – caused by lack of row‑level validation; solved by validating each row, collecting error messages, and returning the full error list before any DB commit.

Merged‑cell data loss – caused by only the first row containing a value; solved by EasyExcel's built‑in merge‑cell handler or a custom listener that caches the previous non‑null value.

Malicious large uploads – caused by no file‑size or rate limits; solved by restricting uploads to ≤50 MB, adding rate‑limiting, and verifying file magic numbers.

Transaction rollback failure in batch processing – caused by each batch committing independently; solved by a pre‑validation phase that collects all errors before any commit, or by tracking progress and rolling back on failure.

Conclusion

Excel import/export is a ubiquitous back‑end requirement, yet naïve POI usage quickly leads to memory exhaustion and tangled code. EasyExcel rewrites the read/write model to use streaming, dramatically reduces memory footprint, and its annotation‑driven API makes business code concise and maintainable. By mastering annotations, a generic listener, batch streaming, and row‑level validation, developers can cover the majority of real‑world scenarios with stable performance and clean code, effectively eliminating the “POI memory nightmare”.