cc-streaming-export-safety

Streaming export / large object memory safety

When building data in memory all at once exceeds 10,000 rows / 10 MB, or the data volume is determined by user filtering and its upper bound cannot be estimated, you must switch to a streaming / paginated API. Otherwise the common consequences are:OOM, Full GC storms, leftover temporary files, HTTP timeouts.

---

Trigger boundaries

Applicable:

• User-driven full export, report download, backup export, batch serialization
• Excel / CSV / JSON / JSONL / PDF files may exceed 10,000 rows or 10 MB
• The code contains SXSSFWorkbook、XSSFWorkbook、EasyExcel、openpyxl、excelize、ExcelJS、PDFKit、ReportLab、JsonGenerator and other export libraries

Not applicable:

• Fixed small file downloads, static resource downloads, avatar/attachment forwarding
• Ordinary Writer / Report / Download naming but not involving large data export
• Single JSON response or regular paginated API

---

Decision: when must you stream?

Data volume	Recommended mode	Notes
≤ 10,000 rows / ≤ 5 MB	In-memory construction OK	XSSFWorkbook / pandas / ordinary JSON serialization
10,000 ~ 100,000 rows	Strongly recommendedstreaming	A single machine is enough, but there is OOM risk
> 100,000 rows / > 50 MB	Muststreaming + asynchronous	The in-memory model will blow up
Unknown data volume (user-driven)	Muststreaming + upper-bound protection	Always design for the worst case

Core judgment: can you estimate the upper bound? If the estimate exceeds 10,000 rows, use streaming.

---

Trap #1: Excel large data → XSSFWorkbook full load OOM

Smell signals

In code review, immediately suspect when you see the following patterns:

• new XSSFWorkbook() + loop createRow more than 10,000 times
• EasyExcel.write(...).sheet().doWrite(data) in data is already fully loaded and exceeds 10,000 entries
• openpyxl.Workbook()(without write_only=True) + loop append
• excelize.NewFile() + SetCellValue a large number of loops
• ExcelJS.Workbook()(without using the stream submodule)

Correct approach: Java + Apache POI SXSSF

// ❌ XSSF 全量内存（10w 行直接 OOM）
XSSFWorkbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet();
for (int i = 0; i < 100_000; i++) {
    Row row = sheet.createRow(i);
    row.createCell(0).setCellValue("data");
}

// ✅ SXSSF 流式（windowSize=100，超出窗口写临时文件）
SXSSFWorkbook wb = null;
try {
    wb = new SXSSFWorkbook(100);
    wb.setCompressTempFiles(true);
    Sheet sheet = wb.createSheet();
    for (int i = 0; i < 100_000; i++) {
        Row row = sheet.createRow(i);
        row.createCell(0).setCellValue("data");
    }
    try (OutputStream os = new FileOutputStream("out.xlsx")) {
        wb.write(os);
    }
} finally {
    if (wb != null) {
        wb.dispose();
    }
}

SXSSF limitations (mustbe communicated to callers)

• ❌ Cannot randomly access rows that have been flushed (after exceeding windowSize, that row is unreadable)
• ❌ Does not support automatic formula calculation (requires pre- evaluator.evaluateAll()）
• ❌ Does not support cloneSheet, and does not support merging multiple already-written Sheets
• ⚠️ Temporary files are by default in java.io.tmpdir; in container environments, pay attention to disk quotas
• ⚠️ Must be in finally in dispose(), otherwise SXSSF backing temp files will remain

EasyExcel (Alibaba) streaming write

// ✅ 已使用 EasyExcel 的项目：用 ExcelWriter 分批写，不要先构建全量 data
ExcelWriter writer = null;
try {
    writer = EasyExcel.write("out.xlsx", DataDTO.class).build();
    WriteSheet sheet = EasyExcel.writerSheet("data")
            .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
            .build();
    int pageNo = 0;
    while (true) {
        List<DataDTO> batch = loadBatch(pageNo++, BATCH_SIZE);
        if (batch.isEmpty()) {
            break;
        }
        writer.write(batch, sheet);
    }
} finally {
    if (writer != null) {
        writer.finish();
    }
}

EasyExcel has entered maintenance mode. For new projects, prioritize evaluating Apache POI SXSSF, FastExcel / Apache Fesod, or the project's existing export stack.

Core APIs for other languages

Language	Streaming API	Key differences
Python	openpyxl.Workbook(write_only=True)	The writer cannot use ws.cell(), only ws.append(row)，Cannot write back
Python	openpyxl.load_workbook(read_only=True)	The reader iterates line by line; for very large data, prefer CSV / JSONL
Go	f.NewStreamWriter("Sheet1") (excelize v2.x)	Must sw.Flush(), otherwise data is lost
Node.js	new ExcelJS.stream.xlsx.WorkbookWriter({filename})	For each row row.commit(), finally workbook.commit()

For a complete code example, see references/multi-lang-examples.md

---

Pitfall #2: Large CSV file → readlines / List concatenation of the whole file

Incorrect example

// ❌ 一次性读入全部行
List<String> lines = Files.readAllLines(Paths.get("huge.csv"));

// ❌ 一次性构建全部字符串
StringBuilder sb = new StringBuilder();
for (Record r : records) sb.append(r.toCsv()).append("\n");
Files.write(path, sb.toString().getBytes());

Correct approach

// ✅ 读：BufferedReader 逐行
try (BufferedReader reader = Files.newBufferedReader(path)) {
    String line;
    while ((line = reader.readLine()) != null) {
        processLine(line);
    }
}

// ✅ 写：OpenCSV CSVWriter 逐行 flush
try (CSVWriter writer = new CSVWriter(new FileWriter(path))) {
    for (Record r : records) {
        writer.writeNext(r.toArray());
    }
}

Core APIs in other languages

Language	Write	Read
Python	csv.writer(f).writerows(iter)	csv.reader(f) Iterator / pandas.read_csv(chunksize=N)
Go	csv.NewWriter(f) + w.Flush() Must call	csv.NewReader(f).Read() line by line
Node.js	csv-stringify stream	csv-parse stream

Pitfall: Go's csv.Writer does not automatically flush,If you forget to call w.Flush() the data all stays in the buffer。

---

Pitfall #3: Large JSON array export → ObjectMapper.writeValueAsString

Incorrect example

// ❌ 整数组先序列化为 String，再写文件
List<Order> orders = repo.findAll(); // 100w 行
String json = objectMapper.writeValueAsString(orders);  // OOM
Files.write(path, json.getBytes());

Correct approach: JsonGenerator streaming

// ✅ Jackson JsonGenerator 流式写
try (JsonGenerator gen = objectMapper.getFactory()
        .createGenerator(new FileOutputStream(path), JsonEncoding.UTF8)) {
    gen.writeStartArray();
    int page = 0;
    Page<Order> orderPage;
    do {
        orderPage = repo.findAll(PageRequest.of(page++, 1000));
        for (Order o : orderPage.getContent()) {
            objectMapper.writeValue(gen, o);
        }
    } while (orderPage.hasNext());
    gen.writeEndArray();
}

Recommended alternative: JSONL (line-delimited JSON)

When the data volume is extremely large, choose the output format JSONL(one JSON object per line) is friendlier than a standard JSON array:

{"id":1,"name":"a"}
{"id":2,"name":"b"}

Downstream can parse it line by line, without needing to read the entire file first.

Core APIs in other languages

Language	Streaming API
Python	orjson single dumps + write .jsonl; or ijson read streaming
Go	json.NewEncoder(w).Encode(v) object by object (naturally produces JSONL)
Node.js	JSONStream.stringify()

---

Pitfall #4: Large PDF reports → fake streaming / full-document prerendering

Incorrect example

// ❌ 一次性把所有 Page 加到 Document，再 save
PDDocument doc = new PDDocument();
for (int i = 0; i < 10_000; i++) {
    doc.addPage(buildPage(data.get(i)));  // 全部 Page 在内存
}
doc.save(path);

Correct approach: first verify whether the library truly writes incrementally

// ✅ 写到文件流，分批取数；但仍需压测验证库内部是否保留完整文档状态
try (PdfWriter writer = new PdfWriter(path);
     PdfDocument pdf = new PdfDocument(writer);
     Document layout = new Document(pdf)) {
    for (Order o : orders) {
        layout.add(buildParagraph(o));
    }
}

Core APIs in other languages

Language	Streaming API	Key operations
Python	ReportLab Canvas	Draw in pages, on page break c.showPage(), finally c.save()
Go	gofpdf.New(...) + pdf.AddPage()	OutputFileAndClose(path) write out at once (when memory is large, consider unidoc streaming)
Node.js	PDFKit	doc.pipe(fs.createWriteStream(path))

Note: A PDF library's "writing to a file stream" does not equal constant memory. Many libraries still retain the complete document state before saving. When the data volume is large, consider:

1. Split into multiple PDFs
2. Switch to a verified incremental-write library
3. Use asynchronous + file storage

---

Pitfall #5: CSV / Excel formula injection

Problem

If a user-controllable field starts with =、+、-、@ , some spreadsheet software will interpret it as a formula. When the exported file is opened, it may trigger a malicious formula or external link.

Correct approach

• Escape formula prefixes on user-controllable text cells, for example by adding a single quote or tab prefix according to project conventions
• Only process text fields; do not break structured fields such as numbers, dates, and amounts
• Handle it centrally at the DTO / cell writer layer, to avoid each export point assembling its own rules

---

Pitfall #6: Large data export synchronously blocks the HTTP thread

Problem

A synchronous export > 30s will be cut off by Nginx / the gateway with 504; the HTTP thread is occupied for a long time, affecting other requests.

Incorrect example

// ❌ Controller 同步生成 + 返回
@GetMapping("/export/orders")
public ResponseEntity<byte[]> exportOrders() {
    byte[] excel = buildExcelInMemory();  // 占用 HTTP 线程 60s+
    return ResponseEntity.ok().body(excel);
}

Correct approach: taskify + polling/notification

// ✅ 接口立即返回任务 ID
@PostMapping("/export/orders")
public ExportTaskDTO export() {
    String taskId = exportTaskService.submit(...);
    return new ExportTaskDTO(taskId, "PROCESSING");
}

// ✅ 异步执行 + 写到对象存储
@Async("exportExecutor")
public void execute(String taskId, ...) {
    File temp = null;
    SXSSFWorkbook wb = null;
    try {
        temp = File.createTempFile("export-", ".xlsx");
        wb = new SXSSFWorkbook(100);
        wb.setCompressTempFiles(true);
        streamWriteExcel(wb, ...);
        try (OutputStream os = new FileOutputStream(temp)) {
            wb.write(os);
        }
        String url = ossClient.upload(temp);
        taskRepo.complete(taskId, url);
    } catch (Exception e) {
        taskRepo.fail(taskId, e.getMessage());
    } finally {
        if (wb != null) {
            wb.dispose();
        }
        if (temp != null && !temp.delete()) {
            log.warn("Failed to delete export temp file: {}", temp);
        }
    }
}

// ✅ 查询接口
@GetMapping("/export/tasks/{id}")
public ExportTaskDTO query(@PathVariable String id) { ... }

See cc-async-task-pattern skill —— state machine, timeouts, retries, and idempotency for asynchronous tasks

---

General checklist

• For exports estimated to exceed 10,000 rows, is a streaming API used
• Is temporary file cleanup performed in try-finally / defer / with / using (SXSSF must dispose()）
• Is the disk quota checked for the temporary directory (in container environments /tmp may be very small)
• Have the limitations of the streaming API been communicated to the caller (no write-back / formulas not supported / must flush)
• Has the PDF library been stress-tested to confirm actual memory usage, rather than only seeing the file write stream
• Are user-controllable CSV / Excel text fields protected against formula injection prefixes
• Is the export interface made asynchronous (task ID + polling / WebSocket notification)
• Error recovery: are temporary files cleaned up when streaming writes are interrupted
• Is a data volume limit set (to prevent users from passing pageSize=999999）
• Are large files uploaded to object storage instead of being returned in memory

---

Related skills

• cc-async-task-pattern —— state machine, timeouts, and idempotency for asynchronous tasks
• cc-query-performance-safety —— prevent N+1 and overly long IN clauses during export
• cc-field-mapping-safety —— field mapping consistency during DTO conversion

---

Rule traceability

> 📋 本回复遵循：`cc-streaming-export-safety` - [章节]