Spring Boot + EasyExcel: High‑Performance, Elegant Excel Import/Export

Excel import/export is a routine requirement in backend systems, but using raw Apache POI often leads to huge memory consumption and repetitive boilerplate code.

Why native POI fails

High memory usage – HSSF/XSSF loads the whole file into memory; a 100 000‑row sheet can consume 1‑2 GB, causing OOM.

Boilerplate code – manual cell type checks, conversion and exception handling make the code noisy.

Lack of edge‑case handling – date format, numeric precision, enum translation, header validation and error‑row locating all need custom logic.

EasyExcel as the optimal solution

EasyExcel, an Alibaba open‑source library, rewrites POI with a SAX‑based event model. It reads and writes rows one by one, keeping memory usage at only a few megabytes even for millions of rows. Annotation‑driven mapping reduces code by about 80 % and integrates smoothly with Spring Boot auto‑configuration.

Minimal memory footprint : streaming parsing prevents OOM.

Annotation‑driven development : @ExcelProperty maps columns directly.

Rich built‑in features : automatic date/number/enum conversion, multi‑level headers, merged cells, custom styles, template filling.

Asynchronous read/write : listener callbacks decouple business logic from parsing.

Production‑grade stability : widely used inside Alibaba.

Code walkthrough

1. Add Maven dependency

<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 a mapping entity

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import lombok.Data;
import java.time.LocalDateTime;

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

    @ExcelProperty(value = "Username", index = 1)
    private String username;

    @ExcelProperty(value = "Phone", index = 2)
    private String phone;

    @ExcelProperty(value = "Department", index = 3)
    private String deptName;

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

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

3. Export endpoint (one‑line write)

@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("UserDataExport", StandardCharsets.UTF_8).replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        EasyExcel.write(response.getOutputStream(), UserExcelVO.class)
                 .sheet("User List")
                 .doWrite(list);
    }
}

4. Import endpoint with listener

@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("Import successful");
}

5. Custom converter (enum ↔ text)

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

    @Override
    public WriteCellData<?> convertToExcelData(Integer value, ExcelContentProperty prop,
                                                GlobalConfiguration config) {
        if (value == null) return new WriteCellData<>("");
        return new WriteCellData<>(value == 1 ? "Enabled" : "Disabled");
    }
}

Apply it with

@ExcelProperty(value = "Status", index = 5, converter = StatusConverter.class)

.

6. Row‑level validation

Use JSR‑380 annotations on fields and validate inside the listener. Errors are collected with row numbers and returned to the client.

7. Complex headers and merged cells

@Data
public class OrderExcelVO {
    @ExcelProperty({"Order Info", "Order No"})
    private String orderNo;

    @ExcelProperty({"Order Info", "Order Time"})
    private LocalDateTime orderTime;

    @ExcelProperty({"Recipient", "Name"})
    private String receiver;

    @ExcelProperty({"Recipient", "Address"})
    private String address;
}

8. Pagination‑driven streaming export for massive data

@GetMapping("/user/export/big")
public void exportBigUser(HttpServletResponse response) throws IOException {
    // set headers as before
    int pageSize = 1000;
    int pageNum = 1;
    ExcelWriter writer = EasyExcel.write(response.getOutputStream(), UserExcelVO.class)
                                   .sheet("User List")
                                   .build();
    while (true) {
        List<UserExcelVO> page = userService.listUserByPage(pageNum, pageSize);
        if (page.isEmpty()) break;
        writer.write(page);
        page.clear();
        pageNum++;
    }
    writer.finish();
}

9. Template‑based export

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

Common pitfalls and solutions

OOM on large export – caused by loading all data at once; fix by paging + streaming.

Filename garbled in browsers – use filename*=utf-8'' encoding.

Partial import failures without error location – add per‑row validation and error collection.

Merged‑cell values lost – use EasyExcel’s merge handler or cache previous row values.

Malicious large uploads – limit file size (e.g., 50 MB) and apply rate‑limiting.

Transaction rollback failure in batch import – perform full validation before any commit or manage batch transactions explicitly.

Conclusion

EasyExcel rewrites the POI read/write model, delivering millisecond‑level memory usage while keeping the API concise through annotations. Combined with Spring Boot, custom converters, validation, pagination, and template filling, it covers more than 90 % of typical backend Excel scenarios, eliminating the performance and maintenance headaches of native POI.