Master SpringBoot Excel Export with EasyExcel: A Complete Guide

1. Environment Integration

1. Add Maven dependencies

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.1.2</version>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>

Refresh Maven after adding the dependencies.

2. Create entity class

@Data
public class UserExcelVO {
    @ExcelProperty(value = "用户ID", index = 0)
    private Long userId;

    @ExcelProperty(value = "注册时间", index = 1)
    @DateTimeFormat("yyyy-MM-dd")
    private Date registerTime;

    @ExcelProperty(value = "性别", index = 2)
    @Converter(SexConverter.class)
    private Integer sex;
}

The @ExcelProperty annotation maps fields to Excel columns; the index defines column order.

3. Write utility class

public class EasyExcelUtils {
    public static <T> void exportExcel(HttpServletResponse response,
                                      List<T> dataList,
                                      Class<T> clazz,
                                      String fileName) throws IOException {
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        EasyExcel.write(response.getOutputStream(), clazz)
                 .sheet("数据报表")
                 .doWrite(dataList);
    }
}

This utility encapsulates the export logic, handling response headers and invoking EasyExcel.

2. Practical Demonstration

1. Basic export

@GetMapping("/exportUser")
public void exportUser(HttpServletResponse response) throws IOException {
    List<UserExcelVO> dataList = userService.getUserListForExport();
    EasyExcelUtils.exportExcel(response, dataList, UserExcelVO.class, "用户信息表");
}

Running this endpoint produces a simple Excel file with the defined columns.

2. Complex header

@Data
public class ComplexHeaderVO {
    @ExcelProperty({"用户信息", "用户ID"})
    private Long userId;

    @ExcelProperty({"用户信息", "姓名"})
    private String userName;

    @ExcelProperty({"联系方式", "手机号"})
    private String phone;

    @ExcelProperty({"联系方式", "邮箱"})
    private String email;
}

Using an array in @ExcelProperty creates multi‑level headers.

3. Merge cells

public class MergeCellHandler implements CellWriteHandler {
    @Override
    public void afterCellDispose(CellWriteHandlerContext context) {
        // Determine rows/columns to merge based on context
        // Example: merge consecutive identical department names
    }
}

Register the handler with EasyExcel:

EasyExcel.write(...).registerWriteHandler(new MergeCellHandler()).doWrite(...);

4. Custom format

@ExcelProperty("金额")
@NumberFormat("#,##0.00")
private Double amount;

Formats numbers as currency and dates with @DateTimeFormat as needed.

5. Large‑data export

EasyExcel.write(response.getOutputStream(), UserExcelVO.class)
         .sheet("大数据报表")
         .registerWriteHandler(...)
         .doWrite(new AnalysisContext() -> {
             List<UserExcelVO> pageData = userService.getPageData(analysisContext.readRowHolder().getRowIndex());
             return pageData; // Return null when no more data
         });

Paging the data prevents memory overflow when exporting over 100,000 rows.

3. Pitfall Guide

Dependency conflicts – run mvn dependency:tree and exclude conflicting POI versions in pom.xml.

Annotation priority – place @ExcelProperty on fields consistently to avoid ordering issues.

Style settings – avoid excessive per‑cell styling; over‑styled sheets may become unreadable.

4. Summary

After years of struggling with POI, EasyExcel provides a smooth experience for Excel export in Spring Boot, covering basic export, complex multi‑level headers, cell merging, custom formatting, and efficient large‑scale paging, while highlighting common pitfalls such as dependency clashes and annotation misuse.