Master EasyExcel: From Setup to Advanced Export Techniques in Java

1. Setting Up EasyExcel – Environment Integration

1. Add Maven Dependency

First add the EasyExcel and Spring Boot starter dependencies to pom.xml, ensuring compatible versions.

<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

Define a Java class that maps each Excel column, using @ExcelProperty to specify header names and column order.

@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 works like a label for each field; the index determines column position.

3. Write Export Utility

Encapsulate the export logic in a reusable 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);
    }
}

2. Practical Scenarios – From Simple to Fancy Export

1. Basic Export

Call the utility from a controller.

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

2. Complex Header

Use an array of @ExcelProperty to create multi‑level headers.

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

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

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

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

3. Merged Cells

Implement a CellWriteHandler to merge cells with the same content.

public class MergeCellHandler implements CellWriteHandler {
    @Override
    public void afterCellDispose(CellWriteHandlerContext context) {
        // merge logic based on row and column indices
    }
}

Register the handler during export:

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

4. Custom Formatting

Apply @NumberFormat for monetary values and @DateTimeFormat for dates.

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

5. Large Data Export

When exporting more than 100 000 rows, use streaming to avoid OOM.

EasyExcel.write(response.getOutputStream(), UserExcelVO.class)
         .sheet("大数据报表")
         .doWrite(() -> {
             List<UserExcelVO> pageData = userService.getPageData(...);
             return pageData;
         });

3. Pitfall Guide

1. Dependency Conflicts

Run mvn dependency:tree and exclude conflicting POI versions.

2. Annotation Priority

Prefer placing @ExcelProperty on fields to keep order consistent.

3. Style Overuse

Avoid excessive cell styling, which can make the workbook unreadable.

4. Conclusion

EasyExcel simplifies Excel export in Java, handling basic needs, complex headers, merged cells, custom formats, and massive data sets while avoiding common pitfalls.