Master Excel Export in Java with EasyExcel: Tips, Code & Pitfalls

1. Setting Up EasyExcel – Environment Integration

1. Add Maven Dependency

Include the EasyExcel dependency in pom.xml, ensuring version compatibility with Spring Boot.

<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>

2. Create Entity Class

Define a Java class that maps each Excel column using @ExcelProperty and optional converters.

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

3. Write Export Utility

Encapsulate common export logic in a 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 Basic to Advanced 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 form of @ExcelProperty for 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. Merge Cells

Implement a custom CellWriteHandler to merge consecutive identical cells.

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

Register the handler during export:

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

4. Custom Formatting

Apply @NumberFormat or @DateTimeFormat for currency and date styles.

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

5. Large Data Export

Use streaming with pagination to avoid OOM when exporting over 100,000 rows.

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 to locate conflicting POI versions and exclude them in pom.xml.

2. Annotation Priority

Prefer placing @ExcelProperty on fields rather than methods to keep column order predictable.

3. Style Overuse

Avoid excessive per‑cell styling, which can make the generated file unreadable.

4. Conclusion

Switching from POI to EasyExcel dramatically simplifies Excel export in Java, handling complex headers, large datasets, and custom formatting with concise code, while the provided utilities and best‑practice tips help avoid common pitfalls.