★ FEATURED ARTICLE 哪个做后端的人没被 Excel 导出这件事恶心过页面报表明明才几万条数据用户点一下导出后台直接卡死再刷新任务也没了。我印象最深的一次是接手某个计费平台的对账明细导出20万行数据、30列字段用最原始的 POI 写法硬导结果还没把响应写完堆内存先炸了。后来重构方案时改用 EasyPOI配合分批查询、多 Sheet 写入才算真正跑通了大批量 Excel 文件导出。这篇文章就把这套批量导出的完整思路、技术选型和踩坑过程记录下来适合所有被报表导出折磨过的 Java 后端开发者。1. 先看翻车现场20万行报表为什么会把内存打爆1.1 最原始的POI导出现场当时接手这个需求的老同事写的第一版逻辑并不复杂用户点击导出按钮后端收到请求后直接从数据库查出全量数据丢到一个 List 里然后遍历这个 List循环创建 Row 和 Cell一行一行往 Excel 里塞。整个过程最耗时的地方有两块查全量数据以及过内存构建 Excel。这个方案在小数据量下确实没问题几千行、十几列Excel 文件也就几百 KB生成速度可以忽略。但数据量一旦涨到几万行问题立刻就暴露了。我接手时看到的现场是请求进来后接口长时间无响应前端等两分钟直接超时日志刷出一大堆 Full GC最后抛出java.lang.OutOfMemoryError: Java heap space。服务器配置不算差堆内存给了 2GB但 20 万行的报表在原始 POI 的 XSSFWorkbook 模式下根本装不下。1.2 内存消耗到底有多大根本原因在于 POI 的 XSSFWorkbook 实现方式。它会在内存里构建一棵完整的 Excel 对象树每个单元格、每行、每个样式、每种字体都是一个独立的 Java 对象。我们先粗算一笔账20万行乘以 30 列一共是 600 万个单元格对象这还没算 Row 对象、Sheet 对象以及单元格引用的 Style 对象。每个 Cell 对象在 JVM 里占用的内存少说 64 到 100 字节600 万乘下来光 Cell 裸对象就是 360MB 到 600MB。再加上行数据本身的字符串内容、日期对象、数字包装类以及 XSSFWorkbook 内部的样式索引和共享字符串表堆内存轻松越过 1GB。这里还不包括样式放大的因素。如果代码里写得粗糙一点每个单元格都执行cell.setCellStyle(style)并且每次循环都新建一个CellStyle对象那内存会膨胀得更夸张。因为每个 CellStyle 在底层都要和 workBook 的样式表维护一份引用关系样式不是简单的赋值而是一整套索引映射。所以结论很直接用 XSSFWorkbook 硬扛大数据量瓶颈不在数据本身而在 POI 对象模型的内存占用。这也是后面要引入 EasyPOI 并配合分批导出策略的根本原因。2. 为什么最终选了 EasyPOI 而不是手写 POI2.1 EasyPOI 到底帮你干了什么EasyPOI 本质上还是对 Apache POI 的封装它没有创造一个新的 Excel 解析引擎。它的核心价值在两个方面第一用注解把实体字段和 Excel 列映射起来省掉手写 Row/Cell 循环的样板代码第二对大批量导出场景做了封装提供了分批写入多 Sheet 的能力避免开发者自己处理复杂的分流逻辑。当时项目里有几十种导出需求比如订单明细、对账单、用户列表、库存流水。如果用原生 POI 写每种报表都要单独写一段创建表头、遍历数据、填单元格的代码字段一旦变动改起来很痛苦。用 EasyPOI 之后每种报表只需要一个带注解的实体类导出方法里传一个 Class 进去其余工作由框架完成。2.2 和原生 POI、其他封装工具的对比我在选型时把方案列了一张表当时对比的是原生 POI、EasyPOI 和另一款业界常用的封装工具综合结论是这样的维度原生POIEasyPOI另一款常见封装工具开发效率低手写大量样板代码高注解映射高注解映射样式控制能力完全可控常用场景够用复杂模板需走POI底层常用场景够用单Sheet大数据量需自己设计分批和SXSSF提供分批导出封装也有流式写功能学习成本高低中依赖体积小中中从表格可以看出如果项目只有一两个固定模板手写 POI 完全可行如果报表种类多、字段变动频繁封装类工具能节约大量时间。我最终选择 EasyPOI 还有一个非技术原因团队里多数人对 POI 的底层 API 不熟但注解式开发几乎零门槛。2.3 EasyPOI 也不是银弹必须泼一盆冷水EasyPOI 解决的是开发效率和常规批量导出问题不是所有导出问题。Excel 单工作表本身有 104 万行的物理上限超过这个量任何 POI 封装工具都无能为力只能考虑 CSV 或分文件打包。另外如果报表里包含大量合并单元格、复杂图表、自定义打印样式注解式方案也搞不定还是得退回原生 POI 或模板引擎。所以我给自己定的选型边界是字段 50 列以内、单表数据量在 50 万行以内的常规报表优先用 EasyPOI 做再往上走直接考虑 CSV ZIP 或者换专用报表引擎。这个判断在后面几个项目里都被验证过比较好使。3. 实战主流程分批加载、多Sheet写入、安全下载3.1 分批查询的两种写法大批量导出第一条原则绝不能一次查出全量数据塞进内存。你也许会说 MySQL 查 20 万行好像也不慢但问题不是查询速度而是查出来的 20 万行对象会全部驻留在堆内存里和 Excel 对象叠加在一起内存直接翻倍。分批查询有两种常见姿势。第一种是用 MyBatis 分页插件每批查 5000 或 10000 条循环到查不出数据为止。这种写法简单但要注意深分页问题——数据总量大时靠LIMIT offset, size翻页越往后越慢因为数据库还得先跳过前面的行。遇到这种情况我一般会改成第二种方式按主键 ID 范围分段。// 按ID范围分批避免深分页 long lastId 0L; int batchSize 5000; while (true) { ListOrderVO batch orderMapper.selectByRange(lastId, batchSize, queryCondition); if (batch.isEmpty()) { break; } // 写Sheet writeBatchToWorkbook(batch); lastId batch.get(batch.size() - 1).getId(); }这种按主键游标推进的方式每次查询都用id lastId作为条件数据库走主键索引速度稳定而且不受 offset 增长影响。唯一的限制是业务表必须有连续可比较的主键实际项目里绝大多数表都满足。3.2 注解映射与导出参数设置定义实体类时用 EasyPOI 的注解把字段和列对应起来。这里有一些细节值得注意public class OrderVO { Excel(name 订单号, width 25, orderNum 1) private String orderNo; Excel(name 金额, width 15, orderNum 2, type ExcelType.NUMERIC, format 0.00) private BigDecimal amount; Excel(name 状态, width 12, orderNum 3, replace {成功_1, 失败_0}) private Integer status; Excel(name 创建时间, width 20, orderNum 4, format yyyy-MM-dd HH:mm:ss) private Date createTime; }replace属性可以把数据库里的数字状态码直接替换成中文文本省得查询后再做一轮转换format对日期和数字都有效orderNum控制列的顺序字段多的时候比依赖声明顺序可靠。这里建议所有列都显式设置宽度避免个别超长内容把整体布局撑乱。3.3 多Sheet写入的EasyPOI姿势当我们分批拿到数据后不能每批都调用一次exportExcel然后覆盖写流。正确做法是复用同一个 Workbook当数据行数超过单 Sheet 的承载能力或者为了方便查看就自动换一个新 Sheet 继续写入。EasyPOI 对这种情况提供了一个比较省心的 API核心代码如下ExportParams params new ExportParams(对账明细, 明细, ExcelType.XSSF); Workbook workbook null; int page 1; while (true) { ListOrderVO batch orderMapper.selectByPage(page, BATCH_SIZE, condition); if (batch.isEmpty()) { break; } if (workbook null) { workbook ExcelExportUtil.exportBigExcel(params, OrderVO.class, batch); } else { ExcelExportUtil.exportBigExcel(params, OrderVO.class, batch); } page; } ExcelExportUtil.closeExportBigExcel();第一次调用exportBigExcel会返回一个 Workbook后续调用会把数据继续往同一个文件里写当数据量达到一定阈值后自动切 Sheetsheet 名会按明细1、明细2这样递增。这个方案既保证了内存可控也照顾了最终用户的查看体验。需要注意的是不同版本的 EasyPOI 在 API 上有细微差异有的版本要求最后必须调用closeExportBigExcel()来关闭内部资源。我之前因为漏调这个方法出现过导出的文件末尾数据丢失的情况排查半天才发现是收尾没收好。3.4 响应流与文件名的几个细节Excel 文件生成完毕后写回响应流这一段也有讲究。容易踩坑的是文件名中文乱码以及响应 Content-Type 不对导致浏览器把它当文本下载。response.setContentType(application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charsetUTF-8); String fileName URLEncoder.encode(对账明细.xlsx, UTF-8); response.setHeader(Content-Disposition, attachment; filename fileName);这里用URLEncoder.encode对文件名编码并且只放在filename里大部分现代浏览器都能正常识别。老版本 IE 可能还需要filename*标准写法但实际业务里遇到的已经比较少了。另一个容易忽略的细节是输出流用完之后要在 finally 里关闭Workbook 也要调用close()否则临时文件会一直占着磁盘空间。4. 内存爆炸的真凶样式对象而不是数据行本身4.1 每个CellStyle都驻留内存我见过不少开发者做导出优化时把注意力全放在分批查询上结果分完批还是 OutOfMemory。其实大批量导出里真正吃掉内存的大户往往不是 Row 和 Cell而是反复创建的样式对象。POI 的样式机制是每个 CellStyle 都是 Workbook 级别的对象它会被放入样式表里统一索引。如果循环里每行都createCellStyle()10 万行就是 10 万个样式对象每个样式对象内部又包含字体、边框、对齐方式等一堆属性。更坑的是每个单元格引用样式时底层还会维护一个样式索引对象之间相互引用GC 根本回收不掉。4.2 让注解替你处理格式用 EasyPOI 时这个问题大概率不会遇到因为它内部会统一管理常用样式。但前提是你别自己再去画蛇添足。我在代码审查里见过有人用 EasyPOI 导出时又额外拿到 Sheet 对象手动设置单元格背景色每行都 new 一个新样式直接把内存拉爆。正确的做法是需要固定格式的地方在实体注解上处理好需要动态区分的内容用replace或format去实现。如果确实有个别列需要特殊处理比如表头加粗、特定列红字提醒那就提前复用一个静态样式对象不要循环里 new。// 不要这样做 for (Row row : sheet) { CellStyle style workbook.createCellStyle(); // 每个Cell都新建 cell.setCellStyle(style); } // 应该这样 CellStyle commonStyle workbook.createCellStyle(); // 只创建一个 commonStyle.setAlignment(HorizontalAlignment.CENTER); for (Row row : sheet) { cell.setCellStyle(commonStyle); }4.3 数字精度和科学计数法的问题另一个容易翻车的是大数字字段。比如导出订单号、身份证号、银行卡号这类超过 11 位的数字Excel 默认会显示成科学计数法用户打开文件一看全是4.01234E11体验非常差。用 EasyPOI 处理时要么在 VO 字段上用Excel(type ExcelType.STRING)强制按文本导出要么在查询 SQL 里先把数字转成字符串。前者更省事注意实体字段类型如果是 Long注解里加type ExcelType.STRING后EasyPOI 会按字符串渲染从而避免科学计数法。小数精度也要统一指定比如金额用format 0.00否则可能出现0.1 0.2这种浮点误差在 Excel 里显示出一长串小数的情况。因为很多金额字段在数据库里是 BigDecimal但如果查询映射时不注意精度渲染出来就可能不对。5. 并发与超时从同步导出改成异步任务下载5.1 为什么同步导出一定会超时大批量导出这种任务耗时往往在几十秒甚至几分钟。如果在 Controller 里同步生成文件再返回给浏览器会有一个很尴尬的问题哪一层都在等。浏览器等 HTTP 响应网关等上游返回Tomcat 线程被占用。一旦超过网关的超时时间请求被断开后端还在拼命生成文件生成完却发现连接早断了白干一场。有一个更极端的情况多个用户同时点导出每个请求占用一个 Tomcat 线程线程池被打满整个应用的其他接口也跟着不可用。这个我在项目里踩过后来彻底改成了异步任务模式才从根上解决。5.2 异步任务下载中心的实现思路异步导出的核心模型不复杂用户发起导出请求后后端立即返回一个任务 ID后台线程池执行真正的查询和文件生成前端每隔几秒轮询任务状态任务完成后拿到文件下载地址。我通常会建一张导出任务表字段包括任务 ID、状态、文件路径、创建时间、完成时间。导出流程大致是// 注意下面的代码是简化后的业务思路具体融入项目时按实际情况调整 public String submitExportTask(QueryCondition condition) { ExportTask task new ExportTask(); task.setStatus(ExportStatus.RUNNING); exportTaskMapper.insert(task); exportExecutor.submit(() - doExport(task.getId(), condition)); return task.getId(); }前端拿到任务 ID 后每 2 秒请求一次状态接口。运行时返回 0完成返回 1 并附带下载链接。这套方案看起来多写不少代码但换来了非常明显的稳定性收益HTTP 连接快速释放Tomcat 线程不被长期占用用户端也不会一直转圈。5.3 线程池和临时文件的清理异步导出任务比普通接口更吃内存线程池参数不能随便给。我一般把核心线程控制在 2 到 4 个队列默认用有界队列超过队列容量后宁可拒绝新任务也不让任务无限堆积。ThreadPoolExecutor exportExecutor new ThreadPoolExecutor( 2, 4, 30L, TimeUnit.SECONDS, new ArrayBlockingQueue(16), new ThreadPoolExecutor.AbortPolicy() );这里用AbortPolicy是故意的导出任务一旦堆积说明系统已经忙不过来了此时应该让用户稍后再试而不是把请求全部塞进去慢慢排队。宁可明确失败也别让整个应用被拖垮。临时文件的清理同样要重视。导出的文件如果放在服务器本地磁盘时间久了会占满硬盘。我习惯在任务完成后记录文件路径定时任务每天清理三天前的临时文件并在生成文件时加任务 ID 后缀方便定位和删除。6. 踩坑实录几个真实遇到的问题及排查链路6.1 分批导出数据重复与遗漏第一次按 ID 范围分批导出时我遇到了一个诡异的数据问题导出结果里出现了重复的记录但同一批的数据量加起来核对总数又不对。排查后发现问题出现在业务数据在导出过程中被其他系统同时修改。按 ID 范围分批和按 offset 分页一样都存在数据快照不一致的问题。比如第一批查出 ID 1 到 5000此时正好有一条 ID 4500 的数据被删除第二批查询条件id 4500下一批就把原来 5000 到 10000 的数据往前顶了结果的集合就缺了一条。稳妥的解法是导出开始时记录当前业务数据的最大 ID分页时以id BETWEEN lastId AND maxId且严格递增全程只在这一个快照范围内读取或者在查询条件里加上导出一开始的时间点配合数据库的事务隔离级别处理。业务上能接受轻微不一致的话就直接在导出文件的末尾加上导出时间说明兜底避免争议。6.2 时间字段差8小时EasyPOI 的时间格式化有个坑如果实体类里的字段类型是java.util.Date并且服务器时区是 UTC格式化后导出的时间会比北京时间少 8 小时。第一次遇到这个问题时我在本地怎么测都是好的部署上线后用户就反馈时间不对。排查链路是先从数据库确认原始数据正确再打印实体对象的 Date 值发现 JVM 默认时区不对。解决方案有两个方向一是确保 JVM 启动参数里带上-Duser.timezoneGMT8二是在 SQL 查询阶段就用DATE_FORMAT把时间转成字符串返回给 VO这样彻底绕开时区转换。我个人更推荐后者字符串渲染不会受到服务器时区影响。6.3 文件下载到一半损坏还有一次用户反馈下载的 Excel 文件打不开提示文件损坏。排查链路很有意思文件在服务器本地打开是正常的但通过浏览器下载后就不行了。最后定位到是代码里把response.getOutputStream()写在 try-with-resources 外面输出流写完 Excel 后没有 flush 就直接返回了或者更隐蔽的问题是在写流之前服务端先往响应里写了一部分业务日志或错误信息Excel 二进制内容前面被混入了非文件数据文件头就坏了。正确的做法是整个响应流写入过程包在 try 块里最后显式调用workbook.write(outputStream)和outputStream.flush()并且在 finally 里关闭 Workbook。注意顺序不能反先写数据再关闭关闭前别做其他输出。6.4 OOM 的排查与优化顺序最终排查 OOM 时我一般按这个顺序走先看日志确认是堆内存溢出还是直接内存溢出然后看 dump 文件里最大的对象是什么如果发现大量XSSFWorkbook或XSSFCell对象就能确定问题出在 Excel 构建阶段而不是查询阶段。实际操作用到的命令很简单jmap -dump:live,formatb,fileheap.bin pid拿到 dump 文件后用分析工具打开直接查看占用内存最多的类再沿引用链往上找很快就能定位到是哪段代码把数据放进来的。优化顺序上我的经验是先减批次大小降到每批 3000 行试试再检查样式创建逻辑最后再考虑调大堆内存。不要一上来就改 JVM 参数那样只是掩盖问题真正的问题可能还在代码里。最后说点实在的经验到现在为止EasyPOI 已经在我手上处理过不少批量导出的需求几十万行以内的常规报表基本跑得比较稳。但每次接手新的导出需求我还是会先做一件看起来很笨的事先用 100 行数据把模板和下载流程跑通再放大到真实数据量。这个习惯帮我避开了大量低级的格式问题也节省了反复调试 Excel 样式的时间。导出这个功能看着不起眼真正压力上来以后涉及的是查询优化、内存控制、异步任务和文件生命周期管理。EasyPOI 帮我们省掉了样板代码但能不能抗住大批量最后还是取决于分批、样式和释放这些基本功有没有做到位。
阅读完成 · 觉得有帮助？
