This article is intended for students who have a basic knowledge of Java and have done business development. We start from “Why does the export of 1 million rows OOM”, and proceed step by step to SXSSF, EasyExcel, CSV, cursor paging, asynchronous export, and finally provide complete engineering code and interview test points that can be directly run.
Table of contents
- Let’s look at the question first: Why does millions of exports cause OOM?
- Panorama of solution evolution
- Environment preparation: Runnable project
- Step 1: Cursor paging query (foundation)
- Step 2: EasyExcel streaming writing
- Step 3: Native POI SXSSF
- Step 4: CSV – the fastest solution
- Step 5: Asynchronous export + progress query
- Complete runnable entry
- Production landing checklist
- Common interview test points and answers
1. Let’s look at the question first: Why does millions of exports cause OOM?
The code most commonly written by novices looks like this:
// ❌ 危险示范
List list = userMapper.selectAll(); // 100 万条全捞进内存
XSSFWorkbook workbook = new XSSFWorkbook(); // 全内存工作簿
Sheet sheet = workbook.createSheet();
for (int i = 0; i < list.size(); i++) {
Row row = sheet.createRow(i);
row.createCell(0).setCellValue(list.get(i).getName());
// ...
}
workbook.write(response.getOutputStream());
This code has Two memory bombs：
| bomb | illustrate | Approximate memory |
|---|---|---|
selectAll() |
1 million entities are entered into the heap at one time | 1 million × 200B ≈ 200MB |
XSSFWorkbook |
POI’s Full memory DOM modeleach line XSSFRoweach grid XSSFCell They are all objects |
1 million rows × 5 columns ≈ 5 million Cells ≈ 500MB~1GB |
workbook.write() |
Built in-house OPCPackagemake another copy |
The peak value doubles again |
in conclusion:By default, 4G cannot handle it and will cause OOM.。
The root cause is:XSSF is a “build all into memory first, serialize last” model. To solve it, we must replace the “memory model” with a “streaming model”.
2. Panoramic view of program evolution
| plan | Memory usage | Single Sheet upper limit | speed | Applicable scenarios |
|---|---|---|---|---|
| THEN XSSF | Extremely high (full memory) | 1,048,576 | slow | Small data volume (<10,000) |
| THEN SXSSF | Low (sliding window + disk temp files) | 1,048,576 | middle | Medium to large data volume |
| EasyExcel | Low (based on SXSSF package) | 1,048,576 | middle | Recommended, high development efficiency |
| CSV | Very low (pure flow) | none | fastest | Data export, no strong requirements on format |
| Asynchronous + File Center | Low | — | — | Production standard plan |
The core idea is just one sentence: change “all at once” to “batch + streaming”, and change “synchronous blocking” to “asynchronous task”.
3. Environment preparation: runnable project
3.1 Maven dependencies
4.0.0
com.example
million-export
1.0.0
17
17
UTF-8
3.3.4
5.2.5
2.2.224
com.alibaba
easyexcel
${easyexcel.version}
org.apache.poi
poi-ooxml
${poi.version}
com.h2database
h2
${h2.version}
org.slf4j
slf4j-simple
1.7.36
org.projectlombok
lombok
1.18.32
provided
Recommended operating parameters：
-Xmx1g -Xms1g. An H2 memory table with 1 million entries occupies approximately 200~300MB, so just leave enough heap space.
3.2 Package structure
src/main/java/com/example/export/
├── DataSourceUtil.java # H2 建表 + 造 100 万数据
├── UserExcelRow.java # Excel 行模型（EasyExcel 注解）
├── ExportService.java # 三种同步导出实现
├── AsyncExportService.java # 异步导出 + 进度查询
└── ExportBenchmark.java # main 入口，一键跑通全部方案
4. Step 1: Cursor paging query (foundation)
Why can’t it be used? LIMIT offset, size？
-- 第 1 页很快，第 200 页开始变慢
SELECT * FROM t_user LIMIT 1000000, 5000; -- 要扫描并丢弃前 100 万行
The time complexity of deep paging is O(offset), which becomes slower as the page goes through.
Correct posture: Seek Method
-- 每次记住上一批的最大 id，下一页直接走主键索引定位
SELECT * FROM t_user WHERE id > ? ORDER BY id ASC LIMIT 5000;
Each page is O(log n) positioned,It takes almost constant time to turn through 200 pages of millions of data。
DataSourceUtil.java
package com.example.export;
import java.sql.*;
/**
* H2 内存数据库工具类：建表 + 灌数据。
* 生产环境请替换为你的 DataSource / MyBatis Mapper。
*/
public final class DataSourceUtil {
public static final String URL = "jdbc:h2:mem:exportdb;DB_CLOSE_DELAY=-1;MODE=MySQL";
public static final String USER = "sa";
public static final String PASSWORD = "";
private DataSourceUtil() {
}
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(URL, USER, PASSWORD);
}
/**
* 建表并灌入 total 条测试数据
*/
public static void initData(int total) throws SQLException {
try (Connection conn = getConnection()) {
conn.setAutoCommit(false);
try (Statement st = conn.createStatement()) {
st.execute("DROP TABLE IF EXISTS t_user");
st.execute("CREATE TABLE t_user (" +
" id BIGINT PRIMARY KEY," +
" name VARCHAR(64)," +
" age INT," +
" email VARCHAR(128)," +
" create_time TIMESTAMP)");
}
String sql = "INSERT INTO t_user(id,name,age,email,create_time) VALUES(?,?,?,?,?)";
long now = System.currentTimeMillis();
try (PreparedStatement ps = conn.prepareStatement(sql)) {
for (int i = 1; i <= total; i++) {
ps.setLong(1, i);
ps.setString(2, "用户_" + i);
ps.setInt(3, 18 + (i % 50));
ps.setString(4, "user" + i + "@example.com");
ps.setTimestamp(5, new Timestamp(now - i * 1000L));
ps.addBatch();
if (i % 10_000 == 0) {
ps.executeBatch();
conn.commit();
System.out.printf(" 已灌入 %,d 条%n", i);
}
}
ps.executeBatch();
conn.commit();
}
}
}
}
UserExcelRow.java
package com.example.export;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import lombok.Getter;
import lombok.Setter;
import java.util.Date;
@Getter
@Setter
public class UserExcelRow {
@ExcelProperty(value = "ID", index = 0)
private Long id;
@ExcelProperty(value = "姓名", index = 1)
private String name;
@ExcelProperty(value = "年龄", index = 2)
private Integer age;
@ExcelProperty(value = "邮箱", index = 3)
private String email;
@ExcelProperty(value = "创建时间", index = 4)
@DateTimeFormat("yyyy-MM-dd HH:mm:ss")
private Date createTime;
}
ExportService.java – cursor paging query
package com.example.export;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public final class ExportService {
private ExportService() {
}
/**
* 游标分页：id > lastId，走主键索引，避免深分页
*
* @param lastId 上一批的最大 id（首批传 0）
* @param pageSize 每批条数，建议 5000 ~ 10000
*/
public static List queryPage(long lastId, int pageSize) throws SQLException {
String sql = "SELECT id, name, age, email, create_time " +
"FROM t_user WHERE id > ? ORDER BY id ASC LIMIT ?";
List list = new ArrayList<>(pageSize);
try (Connection conn = DataSourceUtil.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setLong(1, lastId);
ps.setInt(2, pageSize);
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
UserExcelRow row = new UserExcelRow();
row.setId(rs.getLong("id"));
row.setName(rs.getString("name"));
row.setAge(rs.getInt("age"));
row.setEmail(rs.getString("email"));
row.setCreateTime(rs.getTimestamp("create_time"));
list.add(row);
}
}
}
return list;
}
}
Production Tips: In real projects, do not create a new Connection in each batch. You should use a connection pool (HikariCP), or directly add a
ConnectionPassed in for reuse.
5. Step 2: EasyExcel flow writing
The default writing mode of EasyExcel is SXSSF streamingthe API is extremely simple:ExcelWriter + WriteSheetcan be repeated write() repeatedly.
/**
* 方案一：EasyExcel 分批流式写
*/
public static void exportByEasyExcel(File file, int pageSize) throws SQLException {
long t0 = System.currentTimeMillis();
long lastId = 0L;
long count = 0L;
// try-with-resources 会自动 finish()，把剩余数据刷盘并关闭流
try (ExcelWriter writer = EasyExcel.write(file, UserExcelRow.class).build()) {
WriteSheet sheet = EasyExcel.writerSheet("用户数据").build();
while (true) {
List list = queryPage(lastId, pageSize);
if (list.isEmpty()) {
break;
}
writer.write(list, sheet); // 写一批，内存立刻可回收
lastId = list.get(list.size() - 1).getId();
count += list.size();
}
}
System.out.printf("[EasyExcel] 导出 %,d 条 -> %s，耗时 %,d ms%n",
count, file.getName(), System.currentTimeMillis() - t0);
}
memory curve: each batch list Throw away after use,ExcelWriter Only 500 lines of windows are reserved internally.Heap memory is stable at tens of MB。
6. Step 3: Native POI SXSSF
If you want to thoroughly understand the bottom layer of EasyExcel, you must write SXSSF by hand once.
SXSSF principle：
内存窗口（默认 100 行）
┌──────────────────────────────┐
│ Row N-99 ... Row N │ ← 可读可写
└──────────────────────────────┘
│ 超出窗口，自动 flush
▼
/tmp/poi-sxssf-xxxx/row_*.xml ← 磁盘临时文件
│
▼ write()
最终 .xlsx 文件
/**
* 方案二：原生 POI SXSSF
*/
public static void exportBySxssf(File file, int pageSize) throws Exception {
long t0 = System.currentTimeMillis();
long lastId = 0L;
long count = 0L;
// 关键：内存中只保留 500 行
SXSSFWorkbook workbook = new SXSSFWorkbook(500);
workbook.setCompressTempFiles(true); // 临时文件 gzip 压缩，省磁盘、费 CPU
try {
CellStyle dateStyle = workbook.createCellStyle();
dateStyle.setDataFormat(workbook.createDataFormat().getFormat("yyyy-mm-dd hh:mm:ss"));
Sheet sheet = workbook.createSheet("用户数据_1");
writeHeader(sheet);
int rowIdx = 1;
int sheetIndex = 1;
while (true) {
List list = queryPage(lastId, pageSize);
if (list.isEmpty()) {
break;
}
for (UserExcelRow u : list) {
// xlsx 单 Sheet 上限 1048576 行，超了自动开新 Sheet
if (rowIdx >= 1_048_576) {
sheetIndex++;
sheet = workbook.createSheet("用户数据_" + sheetIndex);
writeHeader(sheet);
rowIdx = 1;
}
Row row = sheet.createRow(rowIdx++);
row.createCell(0).setCellValue(u.getId());
row.createCell(1).setCellValue(u.getName());
row.createCell(2).setCellValue(u.getAge());
row.createCell(3).setCellValue(u.getEmail());
Cell dateCell = row.createCell(4);
dateCell.setCellValue(u.getCreateTime());
dateCell.setCellStyle(dateStyle);
}
lastId = list.get(list.size() - 1).getId();
count += list.size();
}
try (FileOutputStream fos = new FileOutputStream(file)) {
workbook.write(fos);
}
} finally {
// ⚠️ 必须 dispose()，否则 /tmp 会堆积大量 poi-sxssf-* 临时目录
workbook.dispose();
workbook.close();
}
System.out.printf("[SXSSF] 导出 %,d 条 -> %s，耗时 %,d ms%n",
count, file.getName(), System.currentTimeMillis() - t0);
}
private static void writeHeader(Sheet sheet) {
String[] headers = {"ID", "姓名", "年龄", "邮箱", "创建时间"};
Row header = sheet.createRow(0);
for (int i = 0; i < headers.length; i++) {
header.createCell(i).setCellValue(headers[i]);
}
}
4 pitfalls of SXSSF：
- Can only be written sequentially：
createRow(n)The line number must be incremented, and flushed lines cannot be changed back. - Can’t read: SXSSF is a write-only model, and you need to use XSSF/SAX to read files.
- Temporary files must be disposed: Otherwise the disk will be full.
- There is an upper limit on the number of rows in a single Sheet: 1,048,576 rows (xls is 65,536 rows).
7. Step 4: CSV – the fastest solution
If the business has no hard requirements for “must be in .xlsx format”,CSV is the king of performance: No XML serialization, no temporary files, no Sheet upper limit, pure streaming, the speed is usually 3~10 times that of EasyExcel.
/**
* 方案三：CSV 流式导出（性能最优）
*/
public static void exportByCsv(File file, int pageSize) throws Exception {
long t0 = System.currentTimeMillis();
long lastId = 0L;
long count = 0L;
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
try (BufferedWriter bw = new BufferedWriter(
new OutputStreamWriter(new FileOutputStream(file), StandardCharsets.UTF_8), 1 << 16)) {
// ⚠️ BOM 头：让 Excel 双击打开时正确识别 UTF-8，否则中文乱码
bw.write('\uFEFF');
bw.write("ID,姓名,年龄,邮箱,创建时间\n");
while (true) {
List list = queryPage(lastId, pageSize);
if (list.isEmpty()) {
break;
}
for (UserExcelRow u : list) {
bw.write(u.getId() + ","
+ escapeCsv(u.getName()) + ","
+ u.getAge() + ","
+ escapeCsv(u.getEmail()) + ","
+ sdf.format(u.getCreateTime()));
bw.write('\n');
}
lastId = list.get(list.size() - 1).getId();
count += list.size();
}
}
System.out.printf("[CSV] 导出 %,d 条 -> %s，耗时 %,d ms%n",
count, file.getName(), System.currentTimeMillis() - t0);
}
/**
* CSV 字段转义：含逗号、引号、换行时用双引号包裹，内部引号翻倍
*/
private static String escapeCsv(String s) {
if (s == null) {
return "";
}
if (s.contains(",") || s.contains("\"") || s.contains("\n") || s.contains("\r")) {
return "\"" + s.replace("\"", "\"\"") + "\"";
}
return s;
}
Two pitfalls of CSV：
- UTF-8 BOM: Without adding BOM, Chinese characters will be garbled when opened in Excel.
- CSV injection: If the cell starts with
=、+、-、@Initially, Excel will execute it as a formula. In production, prefix escaping is required (add in front'）。
8. Step 5: Asynchronous export + progress query
Why does it have to be asynchronous?
It usually takes 30 to 120 seconds to export 1 million rows simultaneously, and Nginx defaults to proxy_read_timeout=60sgateway 30s, Tomcat connection timeout…The HTTP request was terminated early, and the user saw 502, but the backend was still running.。
Production standard plan:
用户点击导出
│
▼
① 提交任务 → 返回 taskId（毫秒级响应）
│
▼
② 线程池异步执行导出 → 写入本地磁盘 / OSS
│
▼
③ 前端轮询 /api/export/progress?taskId=xxx
│
▼
④ 状态为 SUCCESS → 返回下载 URL
AsyncExportService.java
package com.example.export;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import java.io.File;
import java.util.List;
import java.util.Map;
import java.util.concurrent.*;
public class AsyncExportService {
public enum Status {RUNNING, SUCCESS, FAILED}
/**
* 导出任务：状态、进度、结果文件
*/
public static class ExportTask {
private final String taskId;
private final long total;
private volatile Status status = Status.RUNNING;
private volatile long written;
private volatile String filePath;
private volatile String error;
public ExportTask(String taskId, long total) {
this.taskId = taskId;
this.total = total;
}
public int getPercent() {
if (total <= 0) {
return 0;
}
return (int) Math.min(100, written * 100 / total);
}
public String getTaskId() { return taskId; }
public long getTotal() { return total; }
public long getWritten() { return written; }
public Status getStatus() { return status; }
public String getFilePath() { return filePath; }
public String getError() { return error; }
void setWritten(long written) { this.written = written; }
void setStatus(Status status) { this.status = status; }
void setFilePath(String filePath) { this.filePath = filePath; }
void setError(String error) { this.error = error; }
@Override
public String toString() {
return "ExportTask{id='" + taskId + "', status=" + status
+ ", progress=" + getPercent() + "%}";
}
}
/** 任务注册表：生产环境请换成 Redis，否则多实例部署查不到任务 */
private static final Map TASKS = new ConcurrentHashMap<>();
/** 导出专用线程池：隔离业务线程池，避免导出把主业务拖垮 */
private static final ExecutorService POOL = new ThreadPoolExecutor(
2, 2, 60L, TimeUnit.SECONDS,
new LinkedBlockingQueue<>(100),
r -> {
Thread t = new Thread(r, "excel-export-worker");
t.setDaemon(true);
return t;
},
new ThreadPoolExecutor.AbortPolicy());
private AsyncExportService() {
}
/**
* 提交异步导出任务，立即返回 taskId
*/
public static String submit(String taskId, File target, long total, int pageSize) {
ExportTask task = new ExportTask(taskId, total);
TASKS.put(taskId, task);
POOL.submit(() -> {
try {
doExport(task, target, pageSize);
task.setFilePath(target.getAbsolutePath());
task.setStatus(Status.SUCCESS);
} catch (Exception e) {
task.setError(e.getMessage());
task.setStatus(Status.FAILED);
e.printStackTrace();
}
});
return taskId;
}
public static ExportTask get(String taskId) {
return TASKS.get(taskId);
}
private static void doExport(ExportTask task, File file, int pageSize) throws Exception {
long lastId = 0L;
long written = 0L;
try (ExcelWriter writer = EasyExcel.write(file, UserExcelRow.class).build()) {
WriteSheet sheet = EasyExcel.writerSheet("用户数据").build();
while (true) {
List list = ExportService.queryPage(lastId, pageSize);
if (list.isEmpty()) {
break;
}
writer.write(list, sheet);
lastId = list.get(list.size() - 1).getId();
written += list.size();
task.setWritten(written); // 更新进度，前端轮询可见
}
}
}
}
Production enhancement points(This article will not expand on this, but the interview will ask):
- Task status storage Redissupports multi-instance deployment and automatic TTL expiration
- File storage US / S3returns a signed temporary URL instead of a local path
- add Distributed lock/idempotent keyto prevent users from crashing the database by clicking the export button
- add Scheduled cleaning tasksdelete export files older than 7 days
- add Current limiting(Each user can only have 1 export task at the same time)
9. Complete operational entrance
ExportBenchmark.java
package com.example.export;
import java.io.File;
import java.util.concurrent.TimeUnit;
public class ExportBenchmark {
public static void main(String[] args) throws Exception {
int total = args.length > 0 ? Integer.parseInt(args[0]) : 1_000_000;
int pageSize = args.length > 1 ? Integer.parseInt(args[1]) : 5_000;
System.out.printf("==== 准备测试数据：%,d 条 ====%n", total);
DataSourceUtil.initData(total);
printMemory("灌数完成");
File dir = new File("output");
if (!dir.exists() && !dir.mkdirs()) {
throw new IllegalStateException("无法创建输出目录");
}
// ---------- 方案一：EasyExcel ----------
ExportService.exportByEasyExcel(new File(dir, "easyexcel.xlsx"), pageSize);
printMemory("EasyExcel 导出完成");
// ---------- 方案二：原生 SXSSF ----------
ExportService.exportBySxssf(new File(dir, "sxssf.xlsx"), pageSize);
printMemory("SXSSF 导出完成");
// ---------- 方案三：CSV ----------
ExportService.exportByCsv(new File(dir, "users.csv"), pageSize);
printMemory("CSV 导出完成");
// ---------- 方案四：异步导出 + 进度轮询 ----------
String taskId = AsyncExportService.submit(
"task-" + System.currentTimeMillis(),
new File(dir, "async.xlsx"),
total,
pageSize);
while (true) {
AsyncExportService.ExportTask task = AsyncExportService.get(taskId);
System.out.printf("异步任务进度：%d%%（%,d/%,d）%n",
task.getPercent(), task.getWritten(), task.getTotal());
if (task.getStatus() != AsyncExportService.Status.RUNNING) {
System.out.println("最终状态：" + task.getStatus()
+ "，文件：" + task.getFilePath()
+ (task.getError() != null ? "，错误：" + task.getError() : ""));
break;
}
TimeUnit.MILLISECONDS.sleep(800);
}
System.out.println("\n==== 全部完成，输出目录：" + dir.getAbsolutePath() + " ====");
}
private static void printMemory(String tag) {
Runtime rt = Runtime.getRuntime();
long usedMb = (rt.totalMemory() - rt.freeMemory()) / 1024 / 1024;
System.out.printf("[内存] %s：已用 %d MB%n", tag, usedMb);
}
}
Operation mode
# 打包
mvn clean package
# 跑 100 万条，每批 5000
java -Xmx1g -Xms1g -cp target/classes:target/dependency/* com.example.export.ExportBenchmark 1000000 5000
# 或者用 maven exec
mvn exec:java -Dexec.mainClass=com.example.export.ExportBenchmark -Dexec.args="1000000 5000"
Actual measurement reference of my own machine (for reference only, may vary from machine to machine)
| plan | 1 million messages takes time | peak heap memory | file size |
|---|---|---|---|
| EasyExcel | ~35 s | ~120 MB | ~22 MB |
| nativeSXSSF | ~32 s | ~110 MB | ~21 MB |
| CSV | ~9 s | ~60 MB | ~18 MB |
| Asynchronous EasyExcel | ~35 s (without blocking the main thread) | ~120 MB | ~22 MB |
Key takeaways:CSV is 3~4 times faster and takes up half the memory; the Excel series consumes mainly XML serialization and temporary file IO.
10. Production Implementation Checklist
Before the export function goes online, check it one by one:
11. Common interview test points and answers
Q1: Why does exporting millions of data to Excel cause OOM?
answer:POI’s XSSFWorkbook yesFull memory DOM model. Each line corresponds to one XSSFRow Object, one for each grid XSSFCell Objects, 1 million rows × 5 columns are 5 million Cell objects + 1 million Row objects, and the heap memory occupies 500MB at first; and these objects are write() BeforeCannot be recycled by GC。write() A complete OPCPackagethe peak value doubles again. Plus if used before selectAll() Put all the data into the List, double pressure will inevitably cause OOM.
solve: Use streaming writing (SXSSF/EasyExcel) + batch query instead.
Q2: What is the difference between SXSSF and XSSF?
answer：
| Dimensions | XSSF | SXSSF |
|---|---|---|
| memory model | full memory | Sliding window (default 100 lines) + disk temporary files |
| Can it be read | able | cannot(write only) |
| Can I write randomly? | able | cannotcan only be appended sequentially, flushed lines cannot be changed |
| Memory usage | extremely high | Low |
| temporary files | none | have,must dispose() clean up |
| Applicable scenarios | Small files, need to read and write | Export large amounts of data |
SXSSF is a wrapper for XSSF, which internally serializes rows beyond the window into XML. java.io.tmpdir Next, finally write() When spliced into zip in sequence.
Q3: What is the underlying principle of EasyExcel?
answer：
- read: Use SAX to parse xlsx
sheet.xmlline-by-line callback, does not build DOM in memory, so it can read very large files. - Write: The bottom layer encapsulates SXSSF, defaults to sliding window streaming writing, and annotations (
@ExcelProperty) to do field mapping. - Compared with native POI, EasyExcel also does: smaller object overhead, shared string optimization, and simpler API.
- pay attention to itNot a silver bullet: It still takes tens of seconds to write 1 million rows. The essential bottleneck is XML serialization and disk IO.
Q4: Why can’t I do deep paging? How to optimize?
answer：LIMIT 1000000, 5000 It is necessary to scan and discard the first 1 million rows, and the time consuming increases linearly with offset.
Optimization plan:
- Cursor method (preferred)：
WHERE id > lastId ORDER BY id LIMIT 5000using the primary key index, O(log n) per page. - Delayed correlation：
SELECT t.* FROM t JOIN (SELECT id FROM t ORDER BY id LIMIT 1000000,5000) x ON t.id = x.idfirst turn the page on the covering index. - business restrictions: If there are more than N pages, the export/asynchronous task will be forced to exit and the user will not be allowed to browse.
Q5: How to choose between EasyExcel and CSV?
answer：
| EasyExcel | CSV | |
|---|---|---|
| speed | middle | Fastest (3~4 times) |
| Memory | Low | extremely low |
| Maximum number of rows | Single Sheet 1.04 million | none |
| style/formula | support | Not supported |
| Chinese compatible | good | Need to add BOM |
| Line wrap within cell | support | Need to escape |
in conclusion: “Data export” in the operation backend, 99% of scenarios only require the data itself.Directly to CSV is the fastest and most trouble-free; If you require formats, multiple sheets, and reports for customers, use Excel.
Q6: What should I do if the export request times out?
answer: Change synchronization toAsynchronous tasking：
- The user clicks Export → drop a task record (DB or Redis) in the backend, status
RUNNINGreturn taskId immediately. - The thread pool executes the export asynchronously, writes files to OSS, and updates the progress during the process.
- Frontend polling
/progress?taskId=xxx(or WebSocket push). - state change
SUCCESSThen return the signed URL for download.
Also handle:Current limiting(1 per user at the same time),Idempotent(Repeated submission returns the same taskId),clean up(Delete expired files regularly).
Q7: Can multi-threading speed up export? What should I pay attention to?
answer: Yes, butMultiple threads cannot write to the same Workbook——SXSSFWorkbook Not thread safeand it requires appending rows sequentially.
There are two correct ways to do this:
Plan A: Write multiple files in fragments, and finally make a ZIP package
线程1: id ∈ [1, 250000] → part1.xlsx
线程2: id ∈ [250001, 500000] → part2.xlsx
...
打包 → result.zip
Plan B: Single-threaded reading + multi-threaded conversion(Not significant, the bottleneck is IO)
Plan C: Write multiple Sheets in slices(No, the same workbook cannot be written concurrently; but each thread can write a workbook, and finally use EasyExcel to read it back and merge it – the cost is high, not recommended)
In practice,The benefits of export acceleration usually come from: CSV replacing Excel, reducing query columns, adding indexes, and asynchronousizationinstead of multi-threading writing Excel.
Q8: SXSSF dispose() and close() What’s the difference?
answer：
close(): Close the workbook and release the POI object in memory, butDisk temporary files will not be deleted。dispose()：delete/tmp/poi-sxssf-*Temporary files under。- Correct order:
workbook.write(out)→workbook.dispose()→workbook.close()(orclose()backdispose(),butdispose()Must be adjusted).
Not called dispose() This will cause the disk to be filled up with temporary files, which is a common cause of online accidents.
Q9: How to avoid Full GC/memory thrashing when exporting?
answer：
- Discard each batch of data (5000~10000) immediately after use, and do not accumulate it into a large List.
- Don’t create a lot of temporary objects in a loop (e.g. each row
new SimpleDateFormatshould be mentioned outside the loop). - Set the batch size appropriately: too small → there are many SQL queries; too large → there are many objects in a single batch and frequent Young GC.
- Don’t use
LongestMatchColumnWidthStyleStrategyThis type of strategy requires caching the full amount of data (it will save the maximum width of each column, although the memory is controllable, but there is still additional overhead). -XmxGive enough (starting from 1G), don’t let export and business compete for it.
Q10: What is the limit on the number of rows in Excel?
answer：
.xlsx(Excel 2007+): Single Sheet 1,048,576 rows × 16,384 columns.xls(Excel 2003): Single Sheet 65,536 rows × 256 columns- More than requiredDivide Sheet(like the SXSSF example in this article) orPackage ZIP into files。
- CSV has no row limit (disk dependent).
Q11: How to prevent the export from being maliciously brushed?
answer：
- Current limiting: Guava RateLimiter / Sentinel, based on user dimensions, such as 1 time per minute.
- Concurrency limit: Each user can only have 1 RUNNING task at the same time, and repeated submission will return the existing taskId.
- Data volume check: The upper limit for a single export (such as 500,000 rows), if it exceeds the limit, you will be prompted to reduce the time range.
- Permission verification: The export condition must have data permissions to prevent unauthorized export of the entire table.
- Audit log: Record who exported how much data and when.
Q12: How does MyBatis perform streaming queries?
answer：
Method 1: Cursor
@Select("SELECT * FROM t_user WHERE id > #{lastId} ORDER BY id LIMIT #{size}")
Cursor selectByCursor(@Param("lastId") long lastId, @Param("size") int size);
try (Cursor cursor = mapper.selectByCursor(0, 5000)) {
cursor.forEach(user -> { /* 处理 */ });
}
Notice Cursor It must be placed in a transaction and closed after use.
Method 2: ResultHandler
mapper.selectAll(new ResultHandler() {
@Override
public void handleResult(ResultContext extends User> ctx) {
// 逐条处理，但注意这里整个查询期间会占用连接
}
});
Method three: MySQL true streaming
statement.setFetchSize(Integer.MIN_VALUE); // MySQL 专属
connection.setAutoCommit(false);
Cooperate useCursorFetch=true The effect is better.
⚠️ During streaming query,Exclusive database connectionmust not be mixed with “open new connections in each batch”, otherwise the connection pool will be exploded.
Q13: If you were asked to design a universal export component, how would you design it?
answer：
接口层：POST /export/submit → {taskId}
GET /export/progress → {status, percent, url}
抽象层：ExportHandler
├── buildQuery() 查询条件 & 游标
├── convert(T) 实体 → Excel 行模型
└── getFileName() 文件名规则
执行层：ExportTemplate
├── 游标分页拉数据
├── 流式写（策略：EasyExcel / CSV / SXSSF）
├── 进度上报
└── 上传 OSS
存储层：任务表（MySQL）+ 状态缓存（Redis）+ 文件（OSS）
Key abstract points:Decouple “query” and “write out”the business side only needs to care about “what data to give me” and “what is the column header called”, and the export method is determined by the framework.
Complete text.
Three core sentences to remember:
- The query requires a cursor, not an offset;
- When writing Excel, you need to use streaming (SXSSF/EasyExcel), and never use XSSF to import big data;
- Synchronization must time out, and export must be asynchronous + task-based.
