Million-Row Excel Import/Export: Streaming, Batching & Async Patterns for Zero-OOM Performance
This guide details a production-grade architecture for million-row Excel import/export using EasyExcel streaming reads, SXSSF streaming writes, JDBC batch inserts with rewriteBatchedStatements, async MQ processing, and resource isolation — achieving 10-100x speedups and sub-100MB memory.
Core Conclusion
Never use traditional POI UserModel for million-row Excel — it causes OOM. The standard large-scale pattern is streaming + batching + async + resource isolation .
Overall Architecture
Import Solution (Optimizing Read)
Technology Selection
POI UserModel : applicable to <10k rows, very high memory (full load), slow, rating ⭐
POI SAX Parsing : applicable to 100k-1M rows, very low memory (row-by-row), fast, rating ⭐⭐⭐⭐⭐
EasyExcel : applicable to all scenarios, very low memory (SAX wrapper), fast, rating ⭐⭐⭐⭐⭐
Alibaba EasyExcel : recommended first choice, 50% lower memory than native SAX, fastest, rating ⭐⭐⭐⭐⭐
Key Optimizations
Streaming read : EasyExcel read() callbacks row-by-row, never loads entire file.
Batch insert : Every 1,000-5,000 rows execute JDBC batch ( rewriteBatchedStatements=true).
Multi-thread sharding : Large files split by row count, processed in parallel via thread pool.
Data validation : JSR-380 annotations filter dirty data early, avoiding DB rollbacks.
Async task : Frontend returns immediately; backend uses MQ + scheduler to avoid HTTP timeout.
Failure retry : Record failed row numbers and reasons; support single/batch retry.
Progress display : Real-time task progress updated, frontend polls percentage.
Pitfalls to Avoid
❌ Don't use XSSFWorkbook for large files — 100k rows can consume 2GB.
❌ Don't insert row-by-row — 1M rows = 1M SQL statements, taking hours.
❌ Don't process import on main thread — timeout causes duplicate submissions.
❌ Don't ignore type conversion errors (dates, numbers).
Export Solution (Optimizing Write)
Technology Selection
Preferred : Alibaba EasyExcel + SXSSFWorkbook.
Alternative : POI SXSSFWorkbook (manual memory control).
Not recommended : XSSFWorkbook, HSSFWorkbook (memory explosion).
Key Optimizations
Streaming write : SXSSFWorkbook writes rows beyond 100 to temp files, keeping only recent data in memory.
Batch query : Fetch 1,000-5,000 rows at a time, write immediately, release memory.
Async export : User clicks export → task created → email/SMS notification on completion.
File compression : Generate ZIP to reduce download time and bandwidth.
OSS storage : Upload to Alibaba Cloud OSS / Tencent Cloud COS, avoiding app server disk usage.
Resume support : Large file resume capability improves UX.
Advanced Tips
Template export : Pre-made Excel templates, fill data only — 30% faster than building from scratch.
Multi-sheet export : Auto-split beyond 1,048,576 rows per sheet (Excel limit).
Dynamic column hiding : Show/hide columns based on user permissions.
Formula calculation : Compute in DB layer, avoid Excel formulas.
Performance Benchmarks (Real Data)
100k rows: Traditional 30 sec → Optimized 3 sec (10x speedup)
500k rows: Traditional 150 sec → Optimized 12 sec (12.5x speedup)
1M rows: Traditional OOM crash → Optimized 25 sec (infinite speedup)
5M rows: Traditional — → Optimized 120 sec
Test environment: 8-core 16GB, MySQL 8.0, JDK 17, JVM -Xms4g -Xmx4g.
Safeguards & Monitoring
Resource isolation : Dedicated thread pool for import/export, protecting core business.
Circuit breaker : Auto-reject new requests under high load.
Monitoring & alerting : Track execution time, failure rate, memory; alert on anomalies.
Data backup : Backup tables before import for quick rollback.
Interview Bonus Points
Explain EasyExcel internals: SAX parsing + ASM bytecode proxies to avoid reflection overhead.
DB optimization: disable auto-commit, use rewriteBatchedStatements, temporarily drop non-critical indexes.
Distributed scenario: XXL-Job for multi-node parallel processing.
UX: Generate error-report Excel with row numbers and causes on import failure.
Security: Limit file size/upload frequency to prevent abuse.
Summary
Core of million-row Excel is not the framework but "streaming" and "batching" — never load entire file into memory, never write all data to DB in one shot. Combined with async tasks and resource isolation, the problem becomes trivial.
Core Code Implementations
1. Global Config (Resource Isolation + Tuning)
/**
* 🔹 Highlight 1: Resource isolation - dedicated thread pool for import/export
* 🔹 Highlight 2: Parameter tuning - configure pool based on CPU cores & IO-intensive nature
*/
@Configuration
public class ExcelThreadPoolConfig {
@Bean("excelTaskExecutor")
public ThreadPoolTaskExecutor excelTaskExecutor() {
ThreadPoolTaskExecutor executor = new ThreadPoolTaskExecutor();
// IO-intensive: core = CPU cores * 2
executor.setCorePoolSize(Runtime.getRuntime().availableProcessors() * 2);
executor.setMaxPoolSize(Runtime.getRuntime().availableProcessors() * 4);
executor.setQueueCapacity(1000);
executor.setThreadNamePrefix("excel-task-");
// Rejection: caller runs, prevents task loss
executor.setRejectedExecutionHandler(new ThreadPoolExecutor.CallerRunsPolicy());
executor.initialize();
return executor;
}
}2. Import Core (EasyExcel + Multi-thread Sharding + Batch Insert)
/**
* 🔹 Highlight 3: Streaming read - EasyExcel SAX parsing, memory <100MB
* 🔹 Highlight 4: Multi-thread sharding - large file split per 1000 rows, parallel processing
* 🔹 Highlight 5: True JDBC batch - enable rewriteBatchedStatements
*/
@Service
public class ExcelImportService {
@Autowired private UserMapper userMapper;
@Autowired @Qualifier("excelTaskExecutor") private ThreadPoolTaskExecutor executor;
private static final int BATCH_SIZE = 2000;
@Async("excelTaskExecutor")
public void importExcel(MultipartFile file, Long taskId) {
try {
List<ExcelError> errorList = Collections.synchronizedList(new ArrayList<>());
EasyExcel.read(file.getInputStream(), UserExcelDTO.class,
new ReadListener<UserExcelDTO>() {
private final List<UserExcelDTO> cache = Collections.synchronizedList(new ArrayList<>(BATCH_SIZE));
@Override
public void invoke(UserExcelDTO data, AnalysisContext context) {
// 1. Single-row validation (JSR-380)
Set<ConstraintViolation<UserExcelDTO>> violations = Validation.buildDefaultValidatorFactory().getValidator().validate(data);
if (!violations.isEmpty()) {
Integer rowNum = context.readRowHolder().getRowIndex() + 1;
String errorMsg = violations.stream().map(ConstraintViolation::getMessage).collect(Collectors.joining(", "));
errorList.add(new ExcelError(rowNum, errorMsg, data));
return;
}
// 2. Accumulate batch, submit when threshold reached
cache.add(data);
if (cache.size() >= BATCH_SIZE) {
executor.submit(() -> batchInsert(new ArrayList<>(cache)));
cache.clear();
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
if (!cache.isEmpty()) batchInsert(cache);
if (!errorList.isEmpty()) generateErrorReport(taskId, errorList);
updateTaskStatus(taskId, TaskStatus.COMPLETED, errorList.size());
}
}).sheet().doRead();
} catch (Exception e) {
updateTaskStatus(taskId, TaskStatus.FAILED, 0);
log.error("Excel import failed, taskId: {}", taskId, e);
}
}
/**
* 🔹 Highlight 6: Batch insert optimization - disable auto-commit + rewriteBatchedStatements
* Performance: 1M rows from 30 min → 25 sec
*/
@Transactional(rollbackFor = Exception.class)
public void batchInsert(List<UserExcelDTO> list) {
List<UserDO> userDOList = list.stream().map(this::convertToDO).collect(Collectors.toList());
userMapper.batchInsert(userDOList); // MyBatis batch insert with JDBC param rewriteBatchedStatements=true
}
}3. Export Core (SXSSF Streaming Write + Batch Query)
/**
* 🔹 Highlight 7: Streaming write - SXSSF writes rows beyond 100 to temp files
* 🔹 Highlight 8: Batch query - fetch one batch, write one batch, avoid full memory load
*/
@Service
public class ExcelExportService {
@Autowired private UserMapper userMapper;
@Autowired private OSSService ossService;
private static final int BATCH_SIZE = 2000;
private static final int WINDOW_SIZE = 100; // SXSSF in-memory row window
@Async("excelTaskExecutor")
public void exportExcel(Long taskId, UserQueryDTO query) {
SXSSFWorkbook workbook = null;
try {
workbook = new SXSSFWorkbook(WINDOW_SIZE);
Sheet sheet = workbook.createSheet("用户数据");
writeHeader(sheet);
int offset = 0;
List<UserDO> batch;
do {
batch = userMapper.selectByPage(query, offset, BATCH_SIZE);
if (!batch.isEmpty()) {
writeData(sheet, batch);
offset += BATCH_SIZE;
}
} while (!batch.isEmpty());
String fileName = "用户数据_" + System.currentTimeMillis() + ".xlsx";
ByteArrayOutputStream bos = new ByteArrayOutputStream();
workbook.write(bos);
String downloadUrl = ossService.uploadFile(fileName, bos.toByteArray());
updateTaskStatus(taskId, TaskStatus.COMPLETED, downloadUrl);
} catch (Exception e) {
updateTaskStatus(taskId, TaskStatus.FAILED, null);
log.error("Excel export failed, taskId: {}", taskId, e);
} finally {
// 🔹 Highlight 9: Clean temp files - prevent disk leak
if (workbook != null) workbook.dispose();
}
}
}4. Database Config (Critical Parameters)
# application.yml
spring:
datasource:
url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=false
&rewriteBatchedStatements=true # 🔹 Must enable! Enables true JDBC batching
&cachePrepStmts=true
&useServerPrepStmts=trueTechnical Challenges & Solutions Mapping
💥 OOM : Symptom — 100k+ rows cause heap spike, OutOfMemoryError. Solution — 1. EasyExcel SAX streaming read 2. SXSSF streaming write 3. Batch processing 4. Timely temp file cleanup. Highlight — Memory 2GB+ → <100MB, supports 10M+ rows.
🐌 Import bottleneck : Symptom — Single-thread 1M rows >30 min. Solution — 1. Multi-thread sharding 2. JDBC batch 3. Temporarily disable non-critical indexes 4. Disable auto-commit. Highlight — 10-100x speedup, 1M rows ≤25 sec.
🔄 Data consistency : Symptom — Partial success on exception. Solution — 1. Per-batch transaction control 2. Rollback only current batch 3. Record failures for retry 4. Pre-import backup. Highlight — Atomicity guaranteed, resume support.
⏱️ HTTP timeout : Symptom — Long-running request times out. Solution — 1. Async + MQ decoupling 2. Frontend polling progress 3. Task persisted to DB 4. Support cancel/pause. Highlight — UX from "frozen" → real-time progress.
❌ Unfriendly errors : Symptom — User doesn't know which row failed. Solution — 1. Record row number & cause 2. Generate error Excel 3. Single/batch retry 4. Error report download link. Highlight — User fixes data directly from report.
📦 Distributed limits : Symptom — Single node capacity, single point of failure. Solution — 1. XXL-Job distributed scheduling 2. Sharding broadcast, multi-node parallel 3. Idempotent design 4. Task state persistence. Highlight — Horizontal scaling, linear throughput.
📋 Format compatibility : Symptom — xls/xlsx parse failures, merged cells/formula errors. Solution — 1. EasyExcel auto-compat xls/xlsx 2. Ignore formulas, use computed values 3. Custom merged-cell handler. Highlight — 99%+ format compatibility.
🛡️ Security : Symptom — Malicious large uploads exhaust disk/memory. Solution — 1. Max file size (100MB) 2. Daily upload limit per user 3. File type whitelist 4. Rate limiting & circuit breaker. Highlight — Attack prevention, system stability.
Interview Simulation
Scenario Design Question
Interviewer asks for million-row Excel import/export design. Candidate outlines three pillars: async peak-shaving, streaming processing, batch persistence .
Import Flow
No local file storage : Controller streams directly to OSS, publishes MQ message, returns taskId immediately. Frontend polls progress.
Streaming read : Never use POI XSSFWorkbook. Use EasyExcel ReadListener or POI SAX for row-by-row callbacks, memory stays in tens of MB.
Batch write : Accumulate 1,000 rows, submit via JDBC batch, commit promptly to avoid long transactions.
Validation & error collection : Validate in-memory batch; invalid rows logged to t_import_error (row number, cause, raw data), later exportable as error Excel.
Export Flow
Streaming query : MyBatis resultSetType=FORWARD_ONLY + fetchSize=Integer.MIN_VALUE for MySQL cursor streaming, or cursor-based pagination (5,000 rows/page).
Streaming write : EasyExcel multiple doWrite calls append batches; OutputStream piped directly to OSS upload stream, no local temp files.
OSS download link : Hundred-MB files served via OSS signed URL; optional server-side compression.
Performance Estimate (1M rows, ~30 columns)
EasyExcel streaming parse: 10-15 sec
DB batch write (1,000/batch): 20-30 sec
End-to-end : ~30-40 sec
All async — user perceives only upload seconds + progress bar. Further speedup via row-range sharding across workers, using temp tables then merge.
Production Stack & Pitfalls
Stack: EasyExcel + RocketMQ + Alibaba Cloud OSS .
🐞 Date format inconsistency : Excel dates as strings/numbers; force LocalDateTime with custom converter.
🐞 Memory leaks : Forgot to close Workbook streams or clear MyBatis L1 cache; must clear per batch or use BATCH executor.
🐞 Large transactions : Tens of thousands per commit → huge undo log, replication lag; commit every 1,000 rows.
🐞 Error Excel generation : Preserve original error reasons; EasyExcel dynamic header write for better UX.
One-Sentence Summary
Async peak-shaving accepts tasks, streaming read/write saves memory, batch persistence stabilizes DB, error collection closes the loop.
Additional Core Code Snippets
1. EasyExcel Streaming Import + Batch Persist + Error Collection
@Slf4j
@Component
public class ExcelImportWorker {
private static final int BATCH_SIZE = 1000;
@Autowired private JdbcTemplate jdbcTemplate;
@Autowired private ImportErrorService errorService;
public void process(String fileKey, Long taskId) {
try (InputStream inputStream = ossService.getObject(fileKey)) {
List<ImportDTO> batch = new ArrayList<>(BATCH_SIZE);
EasyExcel.read(inputStream, ImportDTO.class,
new ReadListener<ImportDTO>() {
@Override
public void invoke(ImportDTO data, AnalysisContext context) {
List<String> errors = validate(data);
if (!errors.isEmpty()) {
errorService.saveError(taskId, context.readRowHolder().getRowIndex(), data, errors);
} else {
batch.add(data);
}
if (batch.size() >= BATCH_SIZE) {
batchInsert(batch);
batch.clear();
taskService.updateProgress(taskId, context.readRowHolder().getRowIndex());
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
if (!batch.isEmpty()) batchInsert(batch);
taskService.finish(taskId);
}
}).sheet().doRead();
} catch (Exception e) {
log.error("Import exception, taskId={}", taskId, e);
taskService.markFailed(taskId);
}
}
// Highlight: JDBC batch + manual transaction, avoids ORM L1 cache bloat
@Transactional(propagation = Propagation.REQUIRES_NEW)
public void batchInsert(List<ImportDTO> list) {
String sql = "INSERT INTO t_biz_data (...) VALUES (?, ?, ...)";
jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
ImportDTO dto = list.get(i);
ps.setString(1, dto.getField1());
// ... set other params
}
@Override
public int getBatchSize() { return list.size(); }
});
}
}Highlights
ReadListenerrow-by-row callback, memory holds only one batch.
Validation failures don't throw; recorded to error table, import continues.
Per-batch manual commit via REQUIRES_NEW, avoids large transactions. JdbcTemplate.batchUpdate bypasses MyBatis L1 cache, prevents heap buildup.
2. Million-Row Export: Streaming Query + Streaming Excel Write
@Slf4j
public class ExcelExportWorker {
private static final int QUERY_PAGE_SIZE = 5000;
public void export(Long taskId, ExportQuery query) {
try (OutputStream outputStream = ossService.putObjectStream(exportKey)) {
ExcelWriter excelWriter = EasyExcel.write(outputStream, ExportVO.class).build();
WriteSheet writeSheet = EasyExcel.writerSheet("数据页").build();
Long lastId = 0L;
List<ExportVO> pageList;
do {
pageList = exportMapper.selectByPage(lastId, QUERY_PAGE_SIZE, query);
if (!pageList.isEmpty()) {
excelWriter.write(pageList, writeSheet);
lastId = pageList.get(pageList.size() - 1).getId();
}
taskService.updateProgress(taskId, lastId);
} while (pageList.size() == QUERY_PAGE_SIZE);
excelWriter.finish();
taskService.finish(taskId, exportKey);
} catch (Exception e) {
log.error("Export exception, taskId={}", taskId, e);
taskService.markFailed(taskId);
}
}
}MyBatis Streaming Query Config
@Select("SELECT * FROM t_biz_data WHERE id > #{lastId} AND ... ORDER BY id LIMIT #{size}")
@Options(resultSetType = ResultSetType.FORWARD_ONLY, fetchSize = Integer.MIN_VALUE)
List<ExportVO> selectByPage(@Param("lastId") Long lastId, @Param("size") int size, @Param("query") ExportQuery query);Highlights
Cursor-based pagination via lastId , avoids OFFSET deep-paging penalty.
MyBatis FORWARD_ONLY + Integer.MIN_VALUE forces driver to stream rows from ResultSet, no client-side buffering.
EasyExcel OutputStream wired directly to OSS upload stream, zero local temp files.
3. Async Task & Progress (Controller + MQ)
@RestController
public class ImportController {
@PostMapping("/import/upload")
public Result<Long> upload(@RequestParam("file") MultipartFile file) {
String fileKey = ossService.upload(FileType.EXCEL_IMPORT, file.getInputStream());
Long taskId = taskService.createTask(TaskTypeEnum.IMPORT, fileKey);
rocketMQTemplate.asyncSend("excel-import-topic", new ImportMsg(taskId, fileKey), null);
return Result.ok(taskId);
}
@GetMapping("/import/progress/{taskId}")
public Result<TaskProgressVO> progress(@PathVariable Long taskId) {
return Result.ok(taskService.getProgress(taskId));
}
}Technical Difficulties & Solutions Summary
Large upload OOM : Root cause — Frontend loads whole file or backend buffers all bytes. Solution — 1. Optional frontend chunked upload 2. Backend streams directly to OSS 3. Immediate taskId return, async processing.
Excel parse memory explosion : Root cause — POI XSSFWorkbook full DOM load. Solution — EasyExcel / POI SAX event-driven, row-by-row, constant memory.
Slow DB writes + transaction bloat : Root cause — Single-row inserts, long transaction undo accumulation. Solution — 1. JdbcTemplate.batchUpdate 2. Split every 1,000 rows into independent tx ( REQUIRES_NEW) 3. Disable MyBatis L1 cache or use BATCH executor.
Large export OOM : Root cause — SELECT * loads all into List before writing. Solution — 1. MyBatis streaming cursor pagination ( FORWARD_ONLY + fetchSize) 2. EasyExcel multiple doWrite batches.
Import failure untraceable : Root cause — One bad row aborts entire job, user clueless. Solution — 1. Validation errors don't break flow, collected to error table 2. Record row, cause, raw data 3. Export error Excel on completion.
Long connection timeout : Root cause — Sync processing triggers 504 Gateway Timeout. Solution — Fully async: Controller only accepts request, background worker finishes, notifies frontend via polling/WebSocket.
Concurrent import DB hotspots : Root cause — Multiple tasks writing same table, lock contention. Solution — 1. Partition/shard target table 2. Workers acquire sequence via SELECT ... FOR UPDATE SKIP LOCKED 3. Stage into temp table then merge.
Large file download interruption : Root cause — Network drop mid-download requires restart. Solution — 1. OSS range requests for resume 2. Frontend Range header chunked download 3. Server-side split + ZIP for retry efficiency.
Memory leaks : Root cause — Unclosed Workbook/InputStream on exception. Solution — Strict try-with-resources; EasyExcel ReadListener cleanup in onException / doAfterAllAnalysed.
Inaccurate progress : Root cause — Only notified on completion, user anxiety. Solution — Update processed_count every batch; frontend polls, estimates remaining time.
Interviewer rates the solution S-tier . Candidate adds monitoring tip: task timeout alert (>10 min) to catch silent failures.
Signed-in readers can open the original source through BestHub's protected redirect.
This article has been distilled and summarized from source material, then republished for learning and reference. If you believe it infringes your rights, please contactand we will review it promptly.
How this landed with the community
Was this worth your time?
0 Comments
Thoughtful readers leave field notes, pushback, and hard-won operational detail here.
