Refactoring Spring Boot Excel Import: Async Tasks, Streaming POI Parsing & SSE Progress

The author describes refactoring a Spring Boot admin Excel import feature that originally processed files synchronously in a single HTTP request. With small files (hundreds to thousands of rows) it worked, but a 100,000+ row upload caused a 504 Gateway Timeout after tens of seconds. Worse, the user retried, creating duplicate rows because the first background task continued running after the gateway timed out.

Problems with the Original Synchronous Design

The original controller read the entire Excel into a List, converted rows to entities, and called repository.saveAll(). At 100k rows this caused:

All Excel data loaded into memory as 100k Java objects

HTTP request blocked until database finished

No strategy for row-level errors — one bad row could fail the whole batch

Zero progress visibility for the user

Gateway timeout did not stop the backend task, leading to duplicates on retry

New Async Task Model

The author replaced the synchronous flow with an async task pattern. The upload endpoint now does only three things:

Save the uploaded file to a controlled directory ( /data/import/{taskId}.xlsx) using Files.copy() — critical because MultipartFile is tied to the HTTP request and its temp storage may be cleaned up when the request ends

Create an import_task record with status WAITING Return a taskId immediately

A background thread pool ( ThreadPoolTaskExecutor with core=2, max=4, queue=20, prefix excel-import-) executes the actual import. The pool is kept small because Excel import is I/O + validation + DB writes; more threads would only saturate the database connection pool.

Streaming Excel Parsing with Apache POI SAX/Event Model

Instead of loading the whole workbook (which creates millions of objects), the author uses POI's low-level SAX API via XSSFReader, ReadOnlySharedStringsTable, StylesTable, and XSSFSheetXMLHandler. A custom ProductSheetHandler implements SheetContentsHandler: startRow() clears a per-row column map cell() stores each cell's formatted value by column index endRow() builds a ProductImportRow and passes it to a Consumer — one row at a time, then discards it

This ensures only the current batch (500 rows) lives in memory, regardless of file size.

Batch Processing & JDBC Batch Inserts

The ImportProcessor accumulates validated rows into a batch of 500, then calls ProductBatchWriter.write(batch) which uses JdbcTemplate.batchUpdate() with a parameterized INSERT. The writer returns the count of successful inserts. After each batch the batch list is cleared. Remaining rows are flushed at the end. The author emphasizes that one INSERT per row is the real bottleneck — 100k rows = 100k round-trips — not Excel parsing.

Row-Level vs Task-Level Error Handling

Errors are classified:

Row-level (SKU empty, price format, negative stock, invalid phone): log to import_error table with task_id, row_no, raw data, error message; increment failed counter; continue next row

Task-level (corrupt file, DB unavailable, disk read failure, header mismatch): mark task FAILED and stop

Final task status can be PARTIAL_SUCCESS (e.g., 99,872 success, 128 failed). A "download failed rows" feature lets ops fix only the bad rows without re-processing the whole file.

SSE Real-Time Progress

For progress visibility the author uses Server-Sent Events (SSE) via Spring MVC's SseEmitter. The ImportProgressPublisher maintains a ConcurrentHashMap<String, List<SseEmitter>> keyed by taskId. It exposes: subscribe(taskId) — creates emitter with 30-min timeout, registers completion/timeout callbacks to clean up publish(taskId, processed, success, failed) — sends progress event complete(taskId) — sends complete event and closes emitters error(taskId, message) — sends error event and closes emitters

The frontend uses EventSource with listeners for progress, complete, error events, updating UI with live counts (e.g., "Processed: 63,500 | Success: 63,421 | Failed: 79").

Multi-Instance Considerations

The in-memory emitter map works only for a single instance. In a clustered deployment (Spring Boot A, B, C), an upload may hit instance A while the SSE connection lands on B, which has no progress data. The solution: persist task state in the database ( processed_rows, success_rows, failed_rows, status) as the source of truth. On page load, the browser first calls GET /api/imports/{taskId} to fetch the latest persisted state, then subscribes to SSE for real-time updates. For cross-node real-time events, publish progress events to Redis Pub/Sub or a message queue; each instance consumes and forwards to its local emitters.

Page Refresh & Recovery

Because the browser holds the taskId, a refresh simply re-fetches the task state from the database and re-subscribes to SSE. The import continues unaffected. The author stresses: SSE is for real-time UX; the database is the task's factual state. Progress is persisted every 500 rows via

UPDATE import_task SET processed_rows=?, success_rows=?, failed_rows=? WHERE id=?

. Even if the browser disconnects, the server restarts, or SSE fails, the user can recover to the last persisted checkpoint.

Core Architectural Shift

The fundamental change is stopping the pretense that a minutes-long batch job is a regular HTTP request. The pattern applies to any long-running backend task: report generation, bulk messaging, data sync, image processing, AI batch jobs. Instead of increasing timeouts ( nginx proxy_read_timeout 600, spring.mvc.async.request-timeout, -Xmx4g), the correct approach is:

HTTP request submits task → returns taskId Background thread executes task

Database persists authoritative state

SSE pushes real-time progress

Row-level failures isolated; successful rows commit

The original synchronous code isn't wrong for small, bounded datasets (e.g., 500 rows). The problem arises when a design for hundreds of rows silently scales to 100k without architectural adaptation.