Engineering Large-Scale Excel Imports: Spring Boot + EasyExcel Streaming & Task Isolation

Why EasyExcel Over Apache POI

Apache POI's standard API ( WorkbookFactory.create() or new XSSFWorkbook()) loads the entire workbook into memory. A 500,000-row × 20-column file can exceed 1 GB heap, crashing apps with typical -Xmx512m settings. POI's event model (SAX via XSSFReader) avoids full loading but requires manual handling of sheet relationships, XML events, and cell state — high development cost. EasyExcel reimplements .xlsx parsing on top of POI, streaming row-by-row and releasing each row to GC. Memory usage depends only on batch size, not total rows. With ReadListener, you process each row or batch immediately, offering a practical middle ground between POI's simple-but-heavy API and its low-level event API.

Data Model & Template Design

Define a DTO with @ExcelProperty annotations mapping Chinese headers to fields. For dates, amounts, and long integers, use String fields instead of LocalDateTime or Long. Excel cell formats are unpredictable (e.g., "2023/1/1", "2023-01-01", or serial number 44431); direct type conversion throws exceptions on unrecognized formats. Parsing as strings lets you control validation later.

public class UserImportDTO {
    @ExcelProperty("用户名")
    private String username;
    @ExcelProperty("手机号")
    private String phone;
    @ExcelProperty("邮箱")
    private String email;
    @ExcelProperty("入职日期")
    private String hireDateStr;
    // getters/setters
}

For templates, fix columns when possible. If dynamic headers are needed, override invokeHeadMap in AnalysisEventListener, but readability suffers. Prefer a fixed template with a header row and an example row, then start data at row 3 using headRowNumber(2).

EasyExcel.read(inputStream)
    .head(UserImportDTO.class)
    .headRowNumber(2)
    .sheet()
    .doRead();

Streaming Parsing with ReadListener

Implement ReadListener (or extend AnalysisEventListener). invoke(T data, AnalysisContext context) fires per row; doAfterAllAnalysed(AnalysisContext context) fires at end. Avoid putting business logic directly in invoke — it causes per-row DB calls and loses batch advantages. Instead, accumulate rows in a buffer and hand off batches via a Consumer<List<T>>.

public class BatchDataListener<T> extends AnalysisEventListener<T> {
    private final int batchSize;
    private final List<T> cachedList;
    private final Consumer<List<T>> batchConsumer;

    public BatchDataListener(Consumer<List<T>> batchConsumer) {
        this(batchConsumer, 1000);
    }
    public BatchDataListener(Consumer<List<T>> batchConsumer, int batchSize) {
        this.batchConsumer = batchConsumer;
        this.batchSize = batchSize;
        this.cachedList = new ArrayList<>(batchSize);
    }

    @Override
    public void invoke(T data, AnalysisContext context) {
        cachedList.add(data);
        if (cachedList.size() >= batchSize) {
            batchConsumer.accept(new ArrayList<>(cachedList));
            cachedList.clear();
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        if (!cachedList.isEmpty()) {
            batchConsumer.accept(new ArrayList<>(cachedList));
            cachedList.clear();
        }
    }
}

Batch size of 1000 balances memory and DB load. Larger batches increase memory peaks and SQL execution time, raising transaction risk. To retain original Excel row numbers for error reporting, wrap each row in a RowData<T> carrying rowIndex (data-row number) and the DTO, or store a map from object reference to row number in the listener.

public class RowData<T> {
    private final int rowIndex;
    private final T data;
}

Validation: Allow Partial Success, Not Fail-Fast

Fail-fast (throw on first error) forces users to fix and re-upload repeatedly. Instead, collect errors per row, continue processing valid rows, and return an error file listing row number, column, reason, and original data.

Use Bean Validation annotations ( @NotBlank, @Email, @Size) on the DTO and validate via Validator:

Set<ConstraintViolation<T>> violations = validator.validate(obj);

For uniqueness checks (phone, username), do not query DB per row. Collect distinct values from the batch, run one batch query ( userMapper.countByPhoneList(phones)), load results into a Map, then check each row against the map. This reduces DB round-trips from O(N) to O(1) per batch.

List<String> phones = batch.stream()
    .map(UserImportDTO::getPhone)
    .filter(Objects::nonNull)
    .distinct()
    .collect(Collectors.toList());

Map<String, Integer> existsMap = userMapper.countByPhoneList(phones);

for (UserImportDTO dto : batch) {
    Integer count = existsMap.get(dto.getPhone());
    if (count != null && count > 0) {
        errorCollector.collect(dto.getRowIndex(), "手机号已存在");
        continue;
    }
    // other validations...
}

Generate error files as .xlsx using EasyExcel's ExcelWriter streamed per batch to keep memory low.

Async Processing: Offload from Request Thread

Never run import in the controller thread. Return a task ID immediately; process in a dedicated thread pool. Configure @EnableAsync with a custom ThreadPoolTaskExecutor:

@EnableAsync
@Configuration
public class ImportExecutorConfig {
    @Bean("importExecutor")
    public Executor importExecutor() {
        ThreadPoolTaskExecutor executor = new ThreadPoolTaskExecutor();
        executor.setCorePoolSize(2);
        executor.setMaxPoolSize(4);
        executor.setQueueCapacity(100);
        executor.setThreadNamePrefix("import-task-");
        executor.setRejectedExecutionHandler(new ThreadPoolExecutor.CallerRunsPolicy());
        executor.initialize();
        return executor;
    }
}

Core 2–4 threads; each import consumes significant memory and DB connections. CallerRunsPolicy falls back to caller thread when queue is full — prefer rejecting with "system busy" to avoid synchronous fallback. Track tasks in an import_task table:

CREATE TABLE `import_task` (
  `id` bigint PRIMARY KEY AUTO_INCREMENT,
  `batch_no` varchar(32) NOT NULL COMMENT '业务批次号',
  `file_name` varchar(255) NOT NULL,
  `total_count` int NULL COMMENT '总行数(不含表头)',
  `success_count` int NULL,
  `fail_count` int NULL,
  `status` tinyint NOT NULL COMMENT '1处理中 2成功 3失败 4部分失败',
  `error_file_url` varchar(500) NULL COMMENT '错误文件路径',
  `error_message` varchar(1000) NULL,
  `create_time` datetime NOT NULL,
  `finish_time` datetime NULL,
  UNIQUE KEY `uk_batch_no` (`batch_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Controller receives MultipartFile and a frontend-generated clientToken (used as batch_no). Check existence; if present, return existing task. Save file to temp disk (input stream may not be re-readable), insert task record, then launch async import.

@PostMapping("/import/users")
public Result<String> importUsers(@RequestParam("file") MultipartFile file,
                                   @RequestParam("clientToken") String clientToken) {
    if (file.isEmpty()) return Result.fail("文件为空");
    if (importTaskService.existByBatchNo(clientToken)) {
        return Result.success("重复提交，任务已存在：" + clientToken);
    }
    String batchNo = importTaskService.createTask(file, clientToken);
    return Result.success(batchNo);
}

Async service updates task status through defined states (1=processing, 2=success, 3=failed, 4=partial). Never revert from terminal states back to processing.

Idempotency & Duplicate Prevention

Three layers: (1) Frontend disables button after click, generates unique token per session. (2) Backend unique index on import_task.batch_no rejects duplicate task creation. (3) Business-level: DB unique index on phone/username catches duplicates at insert. Catch DuplicateKeyException, mark offending rows as errors rather than failing the whole task. Pre-check via batch query reduces conflicts but cannot eliminate race windows; DB unique index remains the ultimate guard.

Production Details Often Overlooked

1. Max Row Limit

Streaming solves memory, not time. A 2M-row file may run 1–2 hours, straining task stability and DB connections. Enforce a limit (e.g., 500k rows). In the listener, count rows and throw ExcelAnalysisStopException to halt parsing early.

2. Error File Generation

Write errors incrementally via ExcelWriter + WriteSheet per batch; do not accumulate in memory.

3. Batch Insert Selection

With MyBatis, avoid per-row insert in a loop. Use MyBatis batch SQL or SqlSessionTemplate batch mode. Keep batch size 500–1000; watch MySQL's max_allowed_packet limit.

4. Log Correlation (MDC)

Async tasks run in separate threads; standard logs lose context. Put batchNo into MDC at task start, remove in finally. Configure Logback pattern with %X{batchNo} to grep a full trace.

try {
    MDC.put("batchNo", batchNo);
    // business logic
} finally {
    MDC.remove("batchNo");
}

5. Temp File Cleanup

Delete temp files after parsing (success or failure). If source is OSS, download to temp, process, then delete.

Reference Async Service Implementation

Core @Async("importExecutor") method:

@Async("importExecutor")
public void doImport(Long taskId, String batchNo, File tmpFile) {
    ImportTask task = importTaskMapper.selectById(taskId);
    if (task == null || task.getStatus() != 1) return;

    ErrorRecordCollector errorCollector = new ErrorRecordCollector();
    AtomicInteger totalCount = new AtomicInteger();
    AtomicInteger successCount = new AtomicInteger();

    try {
        BatchDataListener<UserImportDTO> listener = new BatchDataListener<>(batch -> {
            List<UserImportDTO> validList = new ArrayList<>();
            for (UserImportDTO dto : batch) {
                Map<String, String> fieldErrors = validateBean(dto);
                if (!fieldErrors.isEmpty()) {
                    fieldErrors.forEach((field, msg) ->
                        errorCollector.collect(dto.getRowNum(), field, msg));
                    continue;
                }
                validList.add(dto);
            }
            if (!validList.isEmpty()) {
                successCount.addAndGet(userService.batchInsert(validList));
            }
            totalCount.addAndGet(batch.size());
        });

        EasyExcel.read(tmpFile)
            .head(UserImportDTO.class)
            .headRowNumber(1)
            .registerReadListener(listener)
            .sheet()
            .doRead();

        String errorFileUrl = null;
        if (!errorCollector.isEmpty()) {
            File errorFile = genErrorFile(errorCollector);
            errorFileUrl = uploadToOss(errorFile);
        }

        int success = successCount.get();
        int fail = totalCount.get() - success;
        task.setSuccessCount(success);
        task.setFailCount(fail);
        task.setErrorFileUrl(errorFileUrl);
        task.setFinishTime(new Date());
        task.setStatus(fail == 0 ? 2 : 4);
        importTaskMapper.updateById(task);
    } catch (Exception e) {
        log.error("导入任务执行失败, batchNo={}", batchNo, e);
        task.setStatus(3);
        task.setErrorMessage(e.getMessage());
        task.setFinishTime(new Date());
        importTaskMapper.updateById(task);
    } finally {
        if (tmpFile != null) tmpFile.delete();
    }
}

Note: BatchDataListener does not pass row numbers automatically. Wrap rows in RowData<T> or record row index into a DTO field during invoke; wrapper class is cleaner.

Summary

Robust large-file Excel import isn't solved by a single framework. EasyExcel handles memory-safe streaming, but you must design: file storage, task tracking, error feedback, deduplication, async thread pool, batch sizing, validation strategy, logging correlation, and cleanup. A well-built import endpoint exposes progress, status, error reports, and validation — giving users confidence and ops visibility. Once this skeleton is solid, new import needs become field-and-rule changes on the same foundation.