How to Export Arbitrary Excel Files with Spring Boot and EasyExcel Annotations

Why EasyExcel?

EasyExcel, an Alibaba open‑source library, optimizes the traditional Apache POI approach. It reduces memory usage from ~100 MB to a few megabytes for a 3 MB file, reads 75 MB (46 000 rows × 25 columns) within one minute on a 64 MB heap, and offers a concise API through annotations and fluent chaining.

Environment Setup

Add the latest EasyExcel dependency and required Spring Boot starters to pom.xml:

<properties>
    <easyexcel.version>3.3.2</easyexcel.version>
</properties>
<dependencies>
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>easyexcel</artifactId>
        <version>${easyexcel.version}</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>
</dependencies>

Entity Definition with Annotations

Map Excel columns to Java fields using @ExcelProperty, set column width with @ColumnWidth, format dates with @DateTimeFormat, and ignore fields with @ExcelIgnore:

@Data
@NoArgsConstructor
@AllArgsConstructor
public class User implements Serializable {
    @ExcelProperty(value = "ID", index = 0)
    private Integer id;
    @ExcelProperty(value = "用户名", index = 1)
    @ColumnWidth(10)
    private String username;
    @ExcelProperty(value = "密码", index = 2)
    private String password;
    @ExcelProperty(value = "昵称", index = 3)
    private String nickName;
    @ExcelProperty(value = "年龄", index = 4)
    private Integer age;
    @ExcelProperty(value = "性别", index = 5)
    private String sex;
    @ExcelProperty(value = "住址", index = 6)
    private String address;
    @DateTimeFormat("yyyy-MM-dd HH:mm:ss")
    @ExcelProperty(value = "创建时间", index = 7)
    private Date createTime;
}

Basic Export Controller

Expose an endpoint that writes the user list to the response stream:

@RestController
@RequestMapping("/excel")
public class ExcelExportController {
    @Autowired
    private UserService userService;

    @GetMapping("/exportUsers")
    public void exportUserExcel(HttpServletResponse response) throws IOException {
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        String fileName = URLEncoder.encode("用户信息表", "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-Disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        List<User> userList = userService.findAllUsers();
        EasyExcel.write(response.getOutputStream(), User.class)
                 .sheet("用户信息")
                 .doWrite(userList);
    }
}

Advanced Export Features

Dynamic headers and custom data rows are built in memory before writing:

public void exportWithDynamicHead(HttpServletResponse response) {
    List<List<String>> headList = new ArrayList<>();
    headList.add(Arrays.asList("基本信息", "姓名"));
    headList.add(Arrays.asList("基本信息", "年龄"));
    headList.add(Arrays.asList("联系信息", "邮箱"));
    List<List<Object>> dataList = new ArrayList<>();
    for (User user : userList) {
        List<Object> row = new ArrayList<>();
        row.add(user.getUsername());
        row.add(user.getAge());
        row.add(user.getEmail());
        dataList.add(row);
    }
    EasyExcel.write(response.getOutputStream())
             .head(headList)
             .sheet("动态表头示例")
             .doWrite(dataList);
}

Import Listener Design

The listener processes rows in batches to avoid OOM and persists them via a service:

public class UserDataListener extends AnalysisEventListener<User> {
    private static final int BATCH_COUNT = 100;
    private List<User> cachedList = new ArrayList<>(BATCH_COUNT);
    private final UserService userService;
    public UserDataListener(UserService userService) { this.userService = userService; }
    @Override
    public void invoke(User user, AnalysisContext context) {
        cachedList.add(user);
        if (cachedList.size() >= BATCH_COUNT) { saveData(); cachedList.clear(); }
    }
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) { if (!cachedList.isEmpty()) saveData(); }
    private void saveData() { userService.batchSaveUsers(cachedList); }
}

Import Controller

@PostMapping("/importUsers")
public ResponseEntity<String> importUsers(@RequestParam("file") MultipartFile file) {
    try {
        UserService userService = ...; // obtain service
        EasyExcel.read(file.getInputStream(), User.class, new UserDataListener(userService))
                 .sheet()
                 .doRead();
        return ResponseEntity.ok("数据导入成功");
    } catch (IOException e) {
        return ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR)
                             .body("文件读取失败: " + e.getMessage());
    }
}

Async Processing for Large Files

Configure a thread‑pool executor and annotate the import method with @Async to run the import in a separate thread:

@Configuration
@EnableAsync
public class AsyncConfig {
    @Bean("excelTaskExecutor")
    public TaskExecutor taskExecutor() {
        ThreadPoolTaskExecutor executor = new ThreadPoolTaskExecutor();
        executor.setCorePoolSize(5);
        executor.setMaxPoolSize(10);
        executor.setQueueCapacity(100);
        executor.setThreadNamePrefix("excel-import-");
        executor.initialize();
        return executor;
    }
}

@Service
public class AsyncExcelImportService {
    @Async("excelTaskExecutor")
    public CompletableFuture<String> importLargeExcel(MultipartFile file) {
        long start = System.currentTimeMillis();
        // import logic
        return CompletableFuture.completedFuture("导入完成，耗时：" + (System.currentTimeMillis() - start) + "ms");
    }
}

Data Validation and Error Handling

public class ValidatedUserListener extends AnalysisEventListener<User> {
    private List<ExcelError> errors = new ArrayList<>();
    @Override
    public void invoke(User user, AnalysisContext context) {
        Set<ConstraintViolation<User>> violations = validator.validate(user);
        if (!violations.isEmpty()) {
            errors.add(new ExcelError(context.readRowHolder().getRowIndex(), violations.toString()));
            return; // skip invalid row
        }
        // process valid data
    }
    public List<ExcelError> getErrors() { return errors; }
}

Template Export and Encryption

// Export with a predefined template
public void exportWithTemplate(HttpServletResponse response) {
    String templatePath = "/templates/user-template.xlsx";
    EasyExcel.write(response.getOutputStream())
             .withTemplate(templatePath)
             .sheet()
             .doWrite(userList);
}
// Export encrypted Excel
public void exportEncryptedExcel(HttpServletResponse response) {
    EasyExcel.write(response.getOutputStream(), User.class)
             .sheet("用户数据")
             .password("123456")
             .doWrite(userList);
}

Performance Optimization Tips

Batch size : Adjust BATCH_COUNT according to data volume, typically 500‑5000 rows.

Memory management : Clear cached lists promptly to avoid leaks.

Database operations : Use bulk inserts instead of single‑row inserts.

Asynchronous handling : Process massive files asynchronously to prevent request time‑outs.

Common Issues and Solutions

Chinese garbled characters : Ensure correct charset and Content-Type settings.

Out‑of‑memory errors : Leverage listener mode to avoid loading the entire file.

Data validation : Perform real‑time checks in invoke and record errors.

Transaction management : Consider programmatic transactions inside the listener.

Spring Boot combined with EasyExcel provides an efficient, developer‑friendly solution for Excel import/export. Understanding the listener model, batch strategies, and optional features such as async processing, validation, templates, and encryption enables you to tailor the implementation to real‑world business requirements.