大数据量 Excel 导出性能优化：SXSSFWorkbook 流式写入实战

大数据量 Excel 导出性能优化：SXSSFWorkbook 流式写入实战

一、问题背景

导出10万+行数据到 Excel 时，常见的性能问题：

注：

博客：

https://blog.csdn.net/badao_liumang_qizhi

二、POI 的三种 Workbook 对比

2.1 HSSFWorkbook（.xls 格式）

• 文件格式：Excel 97-2003（.xls）
• 行数上限：65536 行
• 内存模型：全部在内存
• 适用：小数据量、兼容旧系统

2.2 XSSFWorkbook（.xlsx 格式）

• 文件格式：Excel 2007+（.xlsx，本质是 ZIP 包裹的 XML）
• 行数上限：1048576 行
• 内存模型：所有行的 DOM 对象全部在堆内存中
• 内存公式：行数 × 列数 × 每个Cell对象大小(约200~500字节)

12万行 × 15列 × 300字节 ≈ 540MB 堆内存

2.3 SXSSFWorkbook（流式 .xlsx）

• 文件格式：同 .xlsx
• 行数上限：同 1048576 行
• 内存模型：滑动窗口，只保留最近 N 行在内存，超出的自动刷到磁盘临时文件
• 内存公式：窗口大小 × 列数 × 每个Cell大小

窗口200行 × 15列 × 300字节 ≈ 900KB 堆内存（几乎忽略不计）

2.4 对比表

三、SXSSFWorkbook 工作原理

创建 SXSSFWorkbook(windowSize=200) │ ├── 写入第 1 行 → 内存中保留 ├── 写入第 2 行 → 内存中保留 ├── ... ├── 写入第 200 行 → 内存中保留（窗口已满） ├── 写入第 201 行 → 第 1 行从内存刷到磁盘临时文件 ├── 写入第 202 行 → 第 2 行从内存刷到磁盘临时文件 ├── ... ├── 写入第 120000 行 → 第 119800 行刷出 │ │ 此时内存中只有第 119801~120000 行（200行） │ 磁盘临时文件中有第 1~119800 行 │ ├── workbook.write(outputStream) │ → 将内存中的行 + 临时文件合并 │ → 压缩为 ZIP（.xlsx） │ → 输出到 OutputStream │ └── workbook.dispose() → 删除磁盘临时文件

3.1 临时文件位置

默认在java.io.tmpdir（通常是/tmp或C:\Users\xxx\AppData\Local\Temp）。

可以自定义：

SXSSFWorkbookworkbook=newSXSSFWorkbook(newXSSFWorkbook(),200,true,true// compressTmpFiles=true 压缩临时文件);

3.2 窗口大小选择

四、分页查询的必要性

即使用了 SXSSFWorkbook 解决了写入端的内存问题，如果一次性从数据库查出12万条数据，这些 Java 对象仍然全部在堆内存中：

12万条 × 每条约1KB = 120MB 堆内存（仅数据对象）

分页查询将这 120MB 分摊到多次查询中，每次只有 5000 条（~5MB）在内存中：

第1次查询：5000条 → 写入 Excel → 被 GC 回收 第2次查询：5000条 → 写入 Excel → 被 GC 回收 ... 第24次查询：5000条 → 写入 Excel → 被 GC 回收

总内存峰值 = 5000条数据对象 + SXSSFWorkbook 窗口 ≈10~20MB

五、完整示例

5.1 实体

packagecom.example.entity;importcom.baomidou.mybatisplus.annotation.IdType;importcom.baomidou.mybatisplus.annotation.TableId;importcom.baomidou.mybatisplus.annotation.TableName;importlombok.Data;importjava.util.Date;@Data@TableName("order_record")publicclassOrderRecord{@TableId(type=IdType.AUTO)privateLongid;privateStringorderCode;privateStringproductName;privateStringcustomerName;privateIntegerquantity;privateDoubleamount;privateStringstatus;privateDatecreateTime;}

5.2 Mapper

packagecom.example.mapper;importcom.baomidou.mybatisplus.core.mapper.BaseMapper;importcom.example.entity.OrderRecord;importorg.apache.ibatis.annotations.Mapper;@MapperpublicinterfaceOrderRecordMapperextendsBaseMapper<OrderRecord>{}

5.3 导出 Service（优化版）

packagecom.example.service;importcom.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;importcom.baomidou.mybatisplus.extension.plugins.pagination.Page;importcom.example.entity.OrderRecord;importcom.example.mapper.OrderRecordMapper;importjakarta.annotation.Resource;importjakarta.servlet.http.HttpServletResponse;importjava.io.OutputStream;importjava.net.URLEncoder;importjava.nio.charset.StandardCharsets;importjava.text.SimpleDateFormat;importjava.time.LocalDate;importjava.time.format.DateTimeFormatter;importjava.util.List;importlombok.extern.slf4j.Slf4j;importorg.apache.poi.ss.usermodel.Cell;importorg.apache.poi.ss.usermodel.CellStyle;importorg.apache.poi.ss.usermodel.Font;importorg.apache.poi.ss.usermodel.Row;importorg.apache.poi.ss.usermodel.Sheet;importorg.apache.poi.xssf.streaming.SXSSFWorkbook;importorg.springframework.stereotype.Service;/** * 大数据量导出服务. */@Slf4j@ServicepublicclassExportService{/** 流式写入窗口大小. */privatestaticfinalintSXSSF_WINDOW_SIZE=200;/** 每次分页查询的条数. */privatestaticfinalintEXPORT_PAGE_SIZE=5000;/** 表头. */privatestaticfinalString[]HEADERS={"订单编号","商品名称","客户名称","数量","金额","状态","创建时间"};@ResourceprivateOrderRecordMapperorderRecordMapper;/** * 导出订单数据（流式写入 + 分页查询）. * * @param status 筛选状态（可选） * @param response HTTP响应 */publicvoidexportOrders(Stringstatus,HttpServletResponseresponse){longstartTime=System.currentTimeMillis();// 创建流式 WorkbookSXSSFWorkbookworkbook=newSXSSFWorkbook(SXSSF_WINDOW_SIZE);try{Sheetsheet=workbook.createSheet("订单数据");// ========== 写入表头 ==========CellStyleheaderStyle=createHeaderStyle(workbook);RowheaderRow=sheet.createRow(0);for(inti=0;i<HEADERS.length;i++){Cellcell=headerRow.createCell(i);cell.setCellValue(HEADERS[i]);cell.setCellStyle(headerStyle);}// ========== 分页查询并逐批写入 ==========SimpleDateFormatsdf=newSimpleDateFormat("yyyy-MM-dd HH:mm:ss");intpageNum=1;introwIndex=1;inttotalExported=0;while(true){// 构造查询条件LambdaQueryWrapper<OrderRecord>wrapper=newLambdaQueryWrapper<>();wrapper.eq(status!=null,OrderRecord::getStatus,status);wrapper.orderByDesc(OrderRecord::getCreateTime);// 分页查询Page<OrderRecord>page=newPage<>(pageNum,EXPORT_PAGE_SIZE);Page<OrderRecord>pageResult=orderRecordMapper.selectPage(page,wrapper);List<OrderRecord>records=pageResult.getRecords();// 无数据则结束if(records.isEmpty()){break;}// 写入当前批次的数据for(OrderRecordrecord:records){Rowrow=sheet.createRow(rowIndex++);row.createCell(0).setCellValue(record.getOrderCode());row.createCell(1).setCellValue(record.getProductName());row.createCell(2).setCellValue(record.getCustomerName());row.createCell(3).setCellValue(record.getQuantity()!=null?record.getQuantity():0);row.createCell(4).setCellValue(record.getAmount()!=null?record.getAmount():0);row.createCell(5).setCellValue(record.getStatus());row.createCell(6).setCellValue(record.getCreateTime()!=null?sdf.format(record.getCreateTime()):"");}totalExported+=records.size();// 最后一页（不足一页）则结束if(records.size()<EXPORT_PAGE_SIZE){break;}pageNum++;}// ========== 设置响应头并输出 ==========StringdateStr=LocalDate.now().format(DateTimeFormatter.ofPattern("yyyyMMdd"));StringfileName="订单导出-"+dateStr+".xlsx";response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");response.setHeader("Content-Disposition","attachment;filename="+URLEncoder.encode(fileName,StandardCharsets.UTF_8));OutputStreamos=response.getOutputStream();workbook.write(os);os.flush();longcost=System.currentTimeMillis()-startTime;log.info("导出完成，共{}条数据，耗时{}ms",totalExported,cost);}catch(Exceptione){log.error("导出异常",e);thrownewRuntimeException("导出失败");}finally{// 必须调用 dispose() 清理临时文件workbook.dispose();}}/** * 创建表头样式. */privateCellStylecreateHeaderStyle(SXSSFWorkbookworkbook){CellStylestyle=workbook.createCellStyle();Fontfont=workbook.createFont();font.setBold(true);style.setFont(font);returnstyle;}}

5.4 Controller

packagecom.example.controller;importcom.example.service.ExportService;importjakarta.annotation.Resource;importjakarta.servlet.http.HttpServletResponse;importorg.springframework.web.bind.annotation.GetMapping;importorg.springframework.web.bind.annotation.RequestMapping;importorg.springframework.web.bind.annotation.RequestParam;importorg.springframework.web.bind.annotation.RestController;@RestController@RequestMapping("/api/order")publicclassOrderController{@ResourceprivateExportServiceexportService;@GetMapping("/export")publicvoidexport(@RequestParam(required=false)Stringstatus,HttpServletResponseresponse){exportService.exportOrders(status,response);}}

六、关键注意事项

6.1 必须调用 dispose()

SXSSFWorkbook 在磁盘上创建了临时文件，如果不调用dispose()，临时文件会一直留在磁盘上，最终耗尽磁盘空间。

SXSSFWorkbookworkbook=newSXSSFWorkbook(200);try{// ... 写入和输出}finally{workbook.dispose();// 删除临时文件}

不能用 try-with-resources：SXSSFWorkbook.close()不会自动调用dispose()，需要显式调用。

6.2 不能回溯已刷出的行

// 错误！已超出窗口的行不能再访问Rowrow0=sheet.getRow(0);// 如果第0行已被刷出，返回 nullrow0.createCell(5).setCellValue("修改");// NullPointerException

如果需要修改表头样式，确保在写数据之前完成（表头在窗口内）。

6.3 合并单元格的限制

// 合并单元格需要在窗口范围内操作// 如果跨度超过窗口大小（如合并第1行到第300行），不可行sheet.addMergedRegion(newCellRangeAddress(0,0,0,5));// 只合并表头列，没问题

6.4 分页查询的边界条件

// 判断是否还有下一页if(records.size()<EXPORT_PAGE_SIZE){break;// 最后一页，不满一页说明没有更多数据了}

不要用pageResult.getTotal()来判断，因为每次都执行 COUNT 会额外增加查询开销。如果确实不想每次都 COUNT：

// 禁用 COUNT 查询，提升分页查询性能Page<OrderRecord>page=newPage<>(pageNum,EXPORT_PAGE_SIZE,false);

6.5 导出过程中数据变化

分页导出期间如果数据被修改（新增/删除），可能出现：

• 重复数据（新插入的行被后续分页查到）
• 遗漏数据（删除的行导致分页偏移）

解决方案：按主键 ID 范围查询而非 OFFSET 分页：

LonglastId=0L;while(true){wrapper.gt(OrderRecord::getId,lastId);wrapper.last("LIMIT "+EXPORT_PAGE_SIZE);List<OrderRecord>records=orderRecordMapper.selectList(wrapper);if(records.isEmpty())break;lastId=records.get(records.size()-1).getId();// 写入...}

七、性能基准测试参考

八、总结

优化策略 = SXSSFWorkbook（解决写入端内存） + 分页查询（解决查询端内存） ┌── 写入端 ──┐ ┌── 查询端 ──┐ 优化前： │ 全在内存 │ + │ 一次全查 │ = 600MB+ 内存 优化后： │ 窗口200行 │ + │ 每次5000条│ = 20MB 内存 关键代码： new SXSSFWorkbook(200) → 限制写入端内存 new Page<>(pageNum, 5000) → 限制查询端内存 workbook.dispose() → 清理临时文件（必须）