1. 为什么这么简单的导出需求会变成项目里最耗时的活前阵子接了个财务对账系统的导出改造需求看起来一句话就能说清把一张流水明细表导成Excel数据量一百多万行文件必须带水印。拆开看就是两个关键词easyexcel分批导出及适配水印。但真正落地的时候光这两个点就折腾了小一周中间还踩了好几个网上资料写得含糊的坑。所以这篇文章我打算把完整思路、代码和实测数据都留下来给后面接类似活儿的兄弟一个能直接抄作业的版本。先说清楚这篇文章适合谁看。如果你是那种项目里导出数据超过几十万行、直接用原生POI写导致内存溢出或者被业务方要求“Excel里必须带公司名称水印”的后端开发这篇文章应该能帮你在半天内把方案定下来。如果你只是几千行数据的小导出那直接看EasyExcel官方文档的一键API就够了这篇文章对你来说属于冗余。1.1 需求的真实面目一百多万行数据加防泄密水印财务系统这个需求麻烦在哪两个点叠在一起就难办了。第一一百多万行数据如果一次性全部load到内存里JVM基本直接躺平。第二所谓水印不是导出一个Excel后让业务自己拿办公软件去加水印而是导出动作本身就得把水印做进去否则数据一旦流转出去泄了密连源头都查不到。项目原来的实现更原始用原生POI自己建Workbook循环往Sheet里塞Cell跑一次导出任务200多万行数据直接把生产环境的内存干到90%以上最后任务被监控系统杀掉。后来换成EasyExcel的一键导出API情况好一些但只要数据量一超五十万行内存还是会抖动得很厉害。1.2 EasyExcel帮我们解决了大问题但没解决全部EasyExcel最大的价值在于它重写了POI底层的事件模型写入逻辑。传统POI写Excel是把所有单元格先建好放进内存再整体写到磁盘EasyExcel则是边走边写写一批数据就flush一批出去所以单从写文件这个动作看它已经不占太多内存了。但问题在于很多人用EasyExcel仍然习惯先把全量数据查出来放List里然后一次性传给write。这一步就把EasyExcel的省内存优势全部抵消了——查询结果集本身已经把堆内存吃满了。所以在第一批优化里我做的核心动作就是把“查询全量”改成“分批查询”同时配合EasyExcel的ExcelWriter多次写入同一Sheet。1.3 把需求拆成两个独立问题事情才变得可控我在接手后的第一件事是把“导出”这个需求拆成两个完全独立的技术问题。第一个问题是分批核心矛盾在内存和查询性能跟Excel格式无关。第二个问题是水印核心矛盾在POI层的画布渲染和页面设置跟数据量无关。这个拆分非常关键。分批导出的代码可以单独写、单独压测水印方案也可以先用一万行小数据验证确认效果后再和大批量导出合并。两个问题不要混在一起调试否则出了Bug你根本分不清是内存问题还是水印渲染问题。后面所有内容我都会按这两个主线分别展开最后再给出合流后的完整实现。2. 分批导出先搞明白EasyExcel到底在什么时候吃内存做分批之前得先把EasyExcel写文件的内存模型弄清楚。很多优化方案写出来像是玄学就是因为没搞明白“写Excel”和“查数据库”两个环节谁才是内存消耗大户。2.1 一键导出API背后发生了什么EasyExcel最常用的写法是EasyExcel.write(fileName, DemoData.class) .sheet(数据) .doWrite(() - queryAll());这行代码看着轻松但doWrite接收的那个Supplier返回的List在写入完成前会一直驻留在内存里。如果你在查询侧一次性load了一百万行对象每个对象就算只有20个字段、每个字段占几十字节算下来也轻松超过两三百MB堆内存。这时候EasyExcel再省内存也救不了你。所以我的第一个结论是EasyExcel的省内存是针对“写文件”这部分的查询侧的内存开销必须自己在数据访问层解决。这也是分批导出的第一层含义——查询分批。2.2 查询侧分批从数据源开始控制流量查询分批的核心思路很简单不要一次查出全量数据而是按页循环查询查一批、写一批、清一批。我的做法是直接按主键ID做分页游标long lastId 0L; int pageSize 5000; ListDemoData batch; while (true) { batch demoMapper.selectPageByCursor(lastId, pageSize); if (batch.isEmpty()) { break; } lastId batch.get(batch.size() - 1).getId(); excelWriter.write(batch, writeSheet); batch.clear(); }为什么用ID游标分页而不是用LIMIT/OFFSET因为OFFSET分页有一个很恶心的特性页数越深数据库扫描的偏移行越多SQL执行时间会越来越长。一百万行数据如果每页5000行要查200页越往后越慢最后几页可能一次查询就要好几秒。ID游标虽然不完美但它利用主键索引做范围扫描每一页的查询速度基本恒定对数据库压力也稳定。2.3 写盘侧分批ExcelWriter与WriteSheet的正确搭配查询侧分批了写盘侧也得跟上。EasyExcel的一键API只适合写一次分批场景必须手动管理ExcelWriter和WriteSheet的生命周期ExcelWriter excelWriter EasyExcel.write(fileName, DemoData.class) .inMemory(false) .build(); WriteSheet writeSheet EasyExcel.writerSheet(对账流水).build(); // 循环查、循环写 excelWriter.write(batch, writeSheet); // 全部写完必须手动finish excelWriter.finish();这里有两个值得注意的细节。第一千万不要在循环里重复调用EasyExcel.write()那会反复创建Workbook对象每次都是全新的文件最后只保留下最后一次写入的数据。第二finish()必须放在循环外面而且最好放在finally块里因为finish()执行的工作是刷缓存、写临时文件、关闭输出流漏掉它会导致文件不完整甚至打不开。关于.inMemory(false)这个参数的意思是写入过程中允许使用磁盘临时文件而不是把所有数据都缓存到内存。对于百万行级别的导出这个参数建议显式设置避免EasyExcel在某些版本里默认走全内存缓存。2.4 超过104万行怎么办自动切割Sheet与页码管理第二个坑是Excel本身的硬限制一个Sheet最多1048576行。超过这个数要么报错要么数据被截断。所以如果业务数据可能超过一百万行必须在写入时按Sheet切分。这里我用了一个简单可靠的方案在分页循环外面套一个计数器每攒够一百万行就换一个新的WriteSheet继续写int sheetIndex 1; String sheetName 对账流水; long rowCount 0; while (true) { batch queryByCursor(lastId, pageSize); if (batch.isEmpty()) break; if (rowCount batch.size() MAX_SHEET_ROWS) { sheetIndex; sheetName 对账流水 sheetIndex; writeSheet EasyExcel.writerSheet(sheetName).build(); rowCount 0; } excelWriter.write(batch, writeSheet); rowCount batch.size(); lastId batch.get(batch.size() - 1).getId(); batch.clear(); }这里注意一点Sheet名称不能超过31个字符也不能包含/:*?等特殊字符用“对账流水2”这种命名方式最省心。我见过有人直接在Sheet名里放时间戳带冒号的导出后文件在WPS和Office里都打不开。3. 加水印的三种思路以及最后我选了哪一种水印这件事EasyExcel官方一直没提供开箱即用的API所以网上能搜到的方案基本都是自己基于POI底层接口做的。我调研的时候整理出三种主流思路各有取舍这里逐个说清楚。3.1 思路一页眉图片水印打印可见但编辑界面无感第一种思路是把水印PNG图片塞进Excel的页眉区域。具体做法是利用POI的XSSFHeaderFooter在页眉中插入一张图片这样打印的时候每一页都会带上水印。这个方案的优点是代码量最小只要往页眉里放一张图即可。缺点是它只在打印预览时能看到在Excel编辑界面完全不显示。对于业务方来说如果他们要的是“打开文件就能看到水印防止截图泄密”页眉水印等于白做——人家截个图发给外面水印根本看不到。3.2 思路二整张PNG作为工作表背景编辑界面可见但不打印第二种思路是生成一张足够大的透明PNG把水印文字平铺在图片上然后用POI的Sheet.setBackgroundPicture方法把这张PNG设置为工作表背景。这是我最推荐的方案水印在编辑界面看得清清楚楚截图出去也能追溯到人。但它的限制也很明显工作表背景图片在Excel里默认不打印。如果业务还要求纸质打印出来也有水印就得用前面的页眉方案做补充。我的最终实现里把两个方案合在一起用了背景水印负责防截图页眉水印负责防打印这样从使用场景上才算完整覆盖。3.3 思路三浮动图片盖在数据区交互体验最差但最直观第三种思路是直接在工作表的数据区域上方放置一张大的半透明PNG图片图片浮在单元格上看起来像水印。这个方案实现起来也简单但有一个致命问题浮动图片会挡住鼠标点击用户没法正常选中单元格、没法复制数据业务方一上手就会骂人。我测试完这个方案后第一时间就放弃了因为它破坏表格的基本可用性。除非你有办法让图片完全不响应鼠标事件否则不要在生产环境用这个思路。3.4 多行多列文字水印真正难的是坐标平铺计算选定了背景PNG方案后真正花时间的是把“多行多列文字水印”画出来。如果只是画一行字谁都会。但水印要覆盖整个Sheet需要把文字按固定间距平铺铺满整张图片而且文字还要带一定倾斜角度否则视觉上不明显。这里有个很关键的细节如果直接把Graphics2D的坐标系旋转45度然后在旋转后的坐标系里循环画文字很容易在画布边缘留下大片空白因为旋转后的文字排版区域和画布矩形并不重合。我一开始就这样画的结果生成的PNG四角都是空的铺到Excel背景里就变成中间有水印、角落没水印的难看的鬼样子。解决办法是先把画布扩大在扩大后的画布上旋转并平铺文字最后再把中间区域裁剪回目标尺寸。具体实现我在下一章给出完整代码。4. 完整实现分批导出与文字水印合流的代码这一章是全文的核心我会按依赖、工具类、ServiceImpl、WriteHandler四层给出可运行的代码。这套代码我实际用在了生产项目里导出一百多万行带水印的Excel文件没有出现问题。4.1 依赖版本与工程目录首先确认依赖版本。EasyExcel的3.x版本迭代较快建议使用较新的稳定版dependency groupIdcom.alibaba/groupId artifactIdeasyexcel/artifactId version3.3.4/version /dependencyEasyExcel本身依赖了POI的XSSF相关模块所以不用额外引入POI的XSSF包。但如果工程里其他地方用了低版本POI可能出现类冲突建议在依赖树里查一下实际生效的POI版本。水印部分的setBackgroundPicture方法需要POI支持建议确认版本不低于4.1.2。工程里只需要一个工具类生成水印PNG一个导出Service以及一个实现了WriteHandler接口的水印注入处理器。所有代码加起来两百行左右。4.2 水印PNG生成工具类文字、角度、透明度、间距都可调水印工具类的完整代码如下public class WatermarkUtil { public static byte[] createWatermarkPng(String text, int width, int height) { int fontSize 28; int canvasWidth (int) (width * 1.6); int canvasHeight (int) (height * 1.6); BufferedImage canvas new BufferedImage(canvasWidth, canvasHeight, BufferedImage.TYPE_INT_ARGB); Graphics2D g canvas.createGraphics(); // 抗锯齿 g.setRenderingHint(RenderingHints.KEY_ANTIALIASING, RenderingHints.VALUE_ANTIALIAS_ON); g.setRenderingHint(RenderingHints.KEY_TEXT_ANTIALIASING, RenderingHints.VALUE_TEXT_ANTIALIAS_ON); // 半透明灰度 g.setComposite(AlphaComposite.getInstance(AlphaComposite.SRC_OVER, 0.18f)); g.setColor(new Color(90, 90, 90)); g.setFont(new Font(微软雅黑, Font.BOLD, fontSize)); g.rotate(Math.toRadians(45), canvasWidth / 2.0, canvasHeight / 2.0); FontMetrics fm g.getFontMetrics(); int textWidth fm.stringWidth(text); int xGap textWidth 120; int yGap 180; // 在旋转坐标系中平铺范围要足够大才能覆盖所有边角 for (int x -canvasHeight; x canvasWidth canvasHeight; x xGap) { for (int y -canvasHeight; y canvasHeight canvasHeight; y yGap) { g.drawString(text, x, y); } } g.dispose(); // 裁剪中间目标区域 BufferedImage target canvas.getSubimage( (canvasWidth - width) / 2, (canvasHeight - height) / 2, width, height); ByteArrayOutputStream bos new ByteArrayOutputStream(); try { ImageIO.write(target, png, bos); } catch (IOException e) { throw new RuntimeException(水印图片生成失败, e); } return bos.toByteArray(); } }这个方法里的width和height建议按A4纸比例设置我用的值是一千二乘一千七左右。水印密度可以通过xGap和yGap调透明度通过AlphaComposite里的0.18f调业务方如果觉得太淡或太浓直接改这两个值就可以。4.3 分批写入ExcelWriter的Service实现Service层的核心是管理好ExcelWriter、WriteSheet和分页查询。我一个简化但完整的示例Service public class ExportService { Resource private DemoMapper demoMapper; public void exportWithBatchAndWatermark(HttpServletResponse response) throws IOException { String fileName URLEncoder.encode(对账流水.xlsx, UTF-8); response.setContentType(application/vnd.openxmlformats-officedocument.spreadsheetml.sheet); response.setHeader(Content-Disposition, attachment;filename*UTF-8 fileName); byte[] watermarkBytes WatermarkUtil.createWatermarkPng(内部资料禁止外传, 1200, 1700); ExcelWriter excelWriter EasyExcel.write(response.getOutputStream(), DemoData.class) .inMemory(false) .registerWriteHandler(new WatermarkWriteHandler(watermarkBytes)) .build(); try { WriteSheet writeSheet EasyExcel.writerSheet(对账流水).build(); long lastId 0L; int pageSize 5000; long currentSheetRows 0L; while (true) { ListDemoData batch demoMapper.selectPageByCursor(lastId, pageSize); if (batch.isEmpty()) { break; } if (currentSheetRows batch.size() 1048576L) { writeSheet EasyExcel.writerSheet(对账流水2).build(); currentSheetRows 0L; } excelWriter.write(batch, writeSheet); lastId batch.get(batch.size() - 1).getId(); currentSheetRows batch.size(); batch.clear(); } } finally { excelWriter.finish(); response.getOutputStream().flush(); } } }这里要强调finally块里调用finish()这样即使中间查询抛异常文件流也能正确关闭不至于把半截文件当成成功响应返回给前端。4.4 通过WriteHandler挂载水印避开类转换陷阱水印怎么跟EasyExcel合流最干净的方式是注册WriteHandler在Sheet创建完成之后拿到POI的Sheets对象再设置背景图片。EasyExcel 3.x的WriteHandler接口签名和早期版本不同为了兼容我建议直接继承AbstractWriteHandler重写afterSheetCreate方法public class WatermarkWriteHandler extends AbstractWriteHandler { private final byte[] watermarkBytes; public WatermarkWriteHandler(byte[] watermarkBytes) { this.watermarkBytes watermarkBytes; } Override public void afterSheetCreate(WriteSheetHolder writeSheetHolder, WriteWorkbookHolder writeWorkbookHolder) { try { Workbook workbook writeWorkbookHolder.getWorkbook(); if (workbook instanceof XSSFWorkbook) { XSSFWorkbook xssfWorkbook (XSSFWorkbook) workbook; XSSFSheet sheet xssfWorkbook.getSheetAt(0); // 背景水印编辑界面可见 sheet.setBackgroundPicture(new ByteArrayInputStream(watermarkBytes)); // 页眉水印打印时每页顶部可见 XSSFHeaderFooter header sheet.getHeader(); int pictureIdx xssfWorkbook.addPicture(watermarkBytes, XSSFWorkbook.PICTURE_TYPE_PNG); header.setPicture(pictureIdx); header.setCenter(G); } else { log.warn(当前Workbook类型不支持水印注入: {}, workbook.getClass().getName()); } } catch (Exception e) { // 水印失败不应该阻断导出生产环境记录日志继续导出 log.error(水印注入失败, e); } } }几个容易出问题的地方我提一下。第一EasyExcel的Workbook类型在非模板模式下通常是XSSFWorkbook但如果你用了某些特殊写模式类型会不同所以代码里做了instanceof判断避免强转ClassCastException。第二水印注入失败不应该让整个导出任务失败所以catch住异常并记录日志业务方拿到没水印的文件总比拿不到文件强。第三header.setCenter(G)表示把插入的图片放到页眉中间位置如果你想页眉同时有文字可以写“G 机密资料”G就是图片占位符。5. 热搜词里那些高频衍生需求冻结、锁定、下拉校验写完主体功能后我又顺手把导出相关的几个高频需求都过了一遍。这些需求在搜索指数里反复出现说明大家做完基础导出后马上都会遇到这些周边功能。我在这里统一整理出来省得你们再挨个搜。5.1 protectSheet锁定全局后怎么只放开指定列有段时间很多人搜easyexcel sheet.protectSheet()锁定全局然后发现整个表都动不了了。这就是因为protectSheet的默认行为是把所有单元格设为锁定状态用户权限如果没有额外放开任何单元格都改不了。如果你只想锁定表头和某些敏感列而允许用户编辑指定的业务列需要先把目标列的单元格样式设为setLocked(false)然后再保护Sheet。顺序不能反先解锁再保护CellStyle unlockStyle workbook.createCellStyle(); unlockStyle.setLocked(false); // 比如格式化成第3列C列可编辑不要锁 XSSFSheet sheet workbook.getSheetAt(0); for (int row 1; row 1000; row) { Cell cell sheet.getRow(row).getCell(2); if (cell null) { cell sheet.getRow(row).createCell(2); } cell.setCellStyle(unlockStyle); } sheet.protectSheet(123456);如果是在EasyExcel的WriteHandler里做这个操作注意行的遍历范围要跟实际数据量匹配不要硬编码1000行。更好的方式是在导出循环里给每条数据对应的行创建单元格时就把样式带上。5.2 冻结表头与冻结指定列冻结列的用法很简单但有个细节容易漏。POI的createFreezePane有两个参数第一个是冻结左侧列数第二个是冻结顶部行数。如果只冻结首行第一个参数要写0sheet.createFreezePane(0, 1); // 只冻结第一行 sheet.createFreezePane(2, 1); // 左侧两列 第一行都冻结冻结和protectSheet可以共存冻结不依赖于单元格的锁定状态所以放心用。5.3 下拉框校验EasyExcel要支持下拉框常见的做法还是走POI的DataValidation。特别是热词里有人搜easyexcel支持下拉框复选吗答案是目前官方不直接支持复选下拉这是Excel原生功能的限制。单选下拉可以这样加DataValidationHelper helper sheet.getDataValidationHelper(); DataValidationConstraint constraint helper.createExplicitListConstraint( new String[]{已核对, 未核对, 异常}); CellRangeAddressList regions new CellRangeAddressList(1, 5000, 3, 3); DataValidation validation helper.createValidation(constraint, regions); sheet.addValidationData(validation);这里CellRangeAddressList的参数依次是起始行、结束行、起始列、结束列。下拉框对性能有一定影响如果给十万行都加上校验文件打开速度会明显变慢。我的建议是下拉区域控制在几千行以内超出的部分不做校验。5.4 复杂表头导出与导入的注意事项热词里还有easyexcel复杂表头导入这个和导出一样是高频需求。导出一侧EasyExcel的ExcelProperty注解天然支持两级表头比如value{一级表头, 二级表头}直接用就行。麻烦的是复杂表头的导入。EasyExcel默认对合并单元格的处理是合并区域只有第一个单元格有值其余单元格都是null。这就导致复杂表头导入后后面的行读不到合并单元格的值字段对不上。解决思路通常是在ReadListener里拿到表头信息后自己维护一个“当前所属一级表头”的上下文遇到null就用上一次的值补上。如果表头层级经常变动建议做成模板导入先把表头结构解析成配置再按配置逐层填充。6. 实测数据与踩坑记录最后分享一些我在实际项目中测出来的数据以及几个印象很深的坑。这些内容常规文档里不会写但都是能让你少加班两天的经验。6.1 一百万行数据导出实测内存与耗时测试环境是8G内存的Linux服务器JVM堆设为512MMySQL数据库。数据量一百万行出头每条记录大概十几个字段。查询分批每批5000行全部导出完成耗时大约五十秒其中数据库查询占了大头写Excel本身不到二十秒。内存峰值我通过监控平台看到的数字大概在280MB左右没有出现OOM。作为对比改成分批前的老代码同样条件下内存直接冲到1.2G以上任务直接被杀掉。所以分不分批差距就是这么大。如果你觉得五十秒还是太慢可以考虑两个优化点。第一是把pageSize调大到10000减少查询次数但要注意单次查询的List对象占用内存会上升建议实测一下。第二是给数据库查询的游标字段加上联合索引确保每页查询都走索引这个优化收益非常明显。6.2 加了水印之后多花多少时间水印PNG生成是一次性的一万行和一百万行都是同一张图所以不会有随着数据量增长的水印耗时。我实测水印PNG生成大约耗时两百毫秒左右Excel写入过程中设置背景图片也是毫秒级操作整体增加的时间可以忽略不计。真正影响体验的是加了背景水印后Excel软件打开文件时的渲染速度。我用办公软件打开同一个文件无水印版本大概一秒内就能显示带水印版本要两到三秒。这个属于客户端渲染开销服务端优化不了但业务方能接受。6.3 踩坑1保护工作表和水印一起开页面显示异常我最初在同一个Sheet上同时做了protectSheet和背景水印结果在办公软件里打开后水印非常淡甚至某些区域完全看不到。排查了半天才发现是protectSheet和背景图片的叠加在某些客户端版本里有渲染冲突尤其是旧版办公软件。解决办法是水印背景照设protectSheet只对需要保护的Sheet生效。如果业务确实要求整个文件带水印又要求单元格锁定我的建议是水印放在背景图层锁定逻辑尽量只锁表头和关键列不要锁全Sheet这样渲染冲突的概率会低很多。6.4 踩坑2导出文件打不开多半是流关闭顺序另一个高发问题是导出完成后文件损坏办公软件提示“文件已损坏无法打开”。我排查过的案例里八成原因是输出流的关闭顺序不对。正确的顺序是先excelWriter.finish()让EasyExcel把Workbook完整写入输出流然后再关闭或flushHttpServletResponse的OutputStream。如果先关流数据还没写完就被截断了。有些同学会在finally里顺手关所有流这时候ExcelWriter内部的流和response的输出流可能已经是同一个对象重复关闭会出问题。稳妥的做法是只在finally里调用finish()response的输出流交给容器或上层框架管理不要手动重复关闭。6.5 我的几个操作习惯写这套代码的过程中我养成了几个习惯现在分享给你。第一水印文字和参数配置不要写死在代码里放到配置中心或properties里业务方改文字、改透明度不用再发一次版。第二每次导出任务建议加一个traceId关联日志方便出问题时快速定位是查询慢还是写文件慢。第三大批量导出建议做成异步任务前端轮询下载链接而不是让HTTP请求一直挂着不然网关超时你都不知道该骂谁。做完这个项目我最大的体会是EasyExcel虽然好用但它给的是积木不是成品。真正决定一个导出功能能不能扛住生产压力的还是你对内存模型和POI底层行为的理解。希望这篇文章能帮你在做类似需求的时候少走点弯路。
EasyExcel百万级数据分批导出与自定义水印实战
1. 为什么这么简单的导出需求会变成项目里最耗时的活前阵子接了个财务对账系统的导出改造需求看起来一句话就能说清把一张流水明细表导成Excel数据量一百多万行文件必须带水印。拆开看就是两个关键词easyexcel分批导出及适配水印。但真正落地的时候光这两个点就折腾了小一周中间还踩了好几个网上资料写得含糊的坑。所以这篇文章我打算把完整思路、代码和实测数据都留下来给后面接类似活儿的兄弟一个能直接抄作业的版本。先说清楚这篇文章适合谁看。如果你是那种项目里导出数据超过几十万行、直接用原生POI写导致内存溢出或者被业务方要求“Excel里必须带公司名称水印”的后端开发这篇文章应该能帮你在半天内把方案定下来。如果你只是几千行数据的小导出那直接看EasyExcel官方文档的一键API就够了这篇文章对你来说属于冗余。1.1 需求的真实面目一百多万行数据加防泄密水印财务系统这个需求麻烦在哪两个点叠在一起就难办了。第一一百多万行数据如果一次性全部load到内存里JVM基本直接躺平。第二所谓水印不是导出一个Excel后让业务自己拿办公软件去加水印而是导出动作本身就得把水印做进去否则数据一旦流转出去泄了密连源头都查不到。项目原来的实现更原始用原生POI自己建Workbook循环往Sheet里塞Cell跑一次导出任务200多万行数据直接把生产环境的内存干到90%以上最后任务被监控系统杀掉。后来换成EasyExcel的一键导出API情况好一些但只要数据量一超五十万行内存还是会抖动得很厉害。1.2 EasyExcel帮我们解决了大问题但没解决全部EasyExcel最大的价值在于它重写了POI底层的事件模型写入逻辑。传统POI写Excel是把所有单元格先建好放进内存再整体写到磁盘EasyExcel则是边走边写写一批数据就flush一批出去所以单从写文件这个动作看它已经不占太多内存了。但问题在于很多人用EasyExcel仍然习惯先把全量数据查出来放List里然后一次性传给write。这一步就把EasyExcel的省内存优势全部抵消了——查询结果集本身已经把堆内存吃满了。所以在第一批优化里我做的核心动作就是把“查询全量”改成“分批查询”同时配合EasyExcel的ExcelWriter多次写入同一Sheet。1.3 把需求拆成两个独立问题事情才变得可控我在接手后的第一件事是把“导出”这个需求拆成两个完全独立的技术问题。第一个问题是分批核心矛盾在内存和查询性能跟Excel格式无关。第二个问题是水印核心矛盾在POI层的画布渲染和页面设置跟数据量无关。这个拆分非常关键。分批导出的代码可以单独写、单独压测水印方案也可以先用一万行小数据验证确认效果后再和大批量导出合并。两个问题不要混在一起调试否则出了Bug你根本分不清是内存问题还是水印渲染问题。后面所有内容我都会按这两个主线分别展开最后再给出合流后的完整实现。2. 分批导出先搞明白EasyExcel到底在什么时候吃内存做分批之前得先把EasyExcel写文件的内存模型弄清楚。很多优化方案写出来像是玄学就是因为没搞明白“写Excel”和“查数据库”两个环节谁才是内存消耗大户。2.1 一键导出API背后发生了什么EasyExcel最常用的写法是EasyExcel.write(fileName, DemoData.class) .sheet(数据) .doWrite(() - queryAll());这行代码看着轻松但doWrite接收的那个Supplier返回的List在写入完成前会一直驻留在内存里。如果你在查询侧一次性load了一百万行对象每个对象就算只有20个字段、每个字段占几十字节算下来也轻松超过两三百MB堆内存。这时候EasyExcel再省内存也救不了你。所以我的第一个结论是EasyExcel的省内存是针对“写文件”这部分的查询侧的内存开销必须自己在数据访问层解决。这也是分批导出的第一层含义——查询分批。2.2 查询侧分批从数据源开始控制流量查询分批的核心思路很简单不要一次查出全量数据而是按页循环查询查一批、写一批、清一批。我的做法是直接按主键ID做分页游标long lastId 0L; int pageSize 5000; ListDemoData batch; while (true) { batch demoMapper.selectPageByCursor(lastId, pageSize); if (batch.isEmpty()) { break; } lastId batch.get(batch.size() - 1).getId(); excelWriter.write(batch, writeSheet); batch.clear(); }为什么用ID游标分页而不是用LIMIT/OFFSET因为OFFSET分页有一个很恶心的特性页数越深数据库扫描的偏移行越多SQL执行时间会越来越长。一百万行数据如果每页5000行要查200页越往后越慢最后几页可能一次查询就要好几秒。ID游标虽然不完美但它利用主键索引做范围扫描每一页的查询速度基本恒定对数据库压力也稳定。2.3 写盘侧分批ExcelWriter与WriteSheet的正确搭配查询侧分批了写盘侧也得跟上。EasyExcel的一键API只适合写一次分批场景必须手动管理ExcelWriter和WriteSheet的生命周期ExcelWriter excelWriter EasyExcel.write(fileName, DemoData.class) .inMemory(false) .build(); WriteSheet writeSheet EasyExcel.writerSheet(对账流水).build(); // 循环查、循环写 excelWriter.write(batch, writeSheet); // 全部写完必须手动finish excelWriter.finish();这里有两个值得注意的细节。第一千万不要在循环里重复调用EasyExcel.write()那会反复创建Workbook对象每次都是全新的文件最后只保留下最后一次写入的数据。第二finish()必须放在循环外面而且最好放在finally块里因为finish()执行的工作是刷缓存、写临时文件、关闭输出流漏掉它会导致文件不完整甚至打不开。关于.inMemory(false)这个参数的意思是写入过程中允许使用磁盘临时文件而不是把所有数据都缓存到内存。对于百万行级别的导出这个参数建议显式设置避免EasyExcel在某些版本里默认走全内存缓存。2.4 超过104万行怎么办自动切割Sheet与页码管理第二个坑是Excel本身的硬限制一个Sheet最多1048576行。超过这个数要么报错要么数据被截断。所以如果业务数据可能超过一百万行必须在写入时按Sheet切分。这里我用了一个简单可靠的方案在分页循环外面套一个计数器每攒够一百万行就换一个新的WriteSheet继续写int sheetIndex 1; String sheetName 对账流水; long rowCount 0; while (true) { batch queryByCursor(lastId, pageSize); if (batch.isEmpty()) break; if (rowCount batch.size() MAX_SHEET_ROWS) { sheetIndex; sheetName 对账流水 sheetIndex; writeSheet EasyExcel.writerSheet(sheetName).build(); rowCount 0; } excelWriter.write(batch, writeSheet); rowCount batch.size(); lastId batch.get(batch.size() - 1).getId(); batch.clear(); }这里注意一点Sheet名称不能超过31个字符也不能包含/:*?等特殊字符用“对账流水2”这种命名方式最省心。我见过有人直接在Sheet名里放时间戳带冒号的导出后文件在WPS和Office里都打不开。3. 加水印的三种思路以及最后我选了哪一种水印这件事EasyExcel官方一直没提供开箱即用的API所以网上能搜到的方案基本都是自己基于POI底层接口做的。我调研的时候整理出三种主流思路各有取舍这里逐个说清楚。3.1 思路一页眉图片水印打印可见但编辑界面无感第一种思路是把水印PNG图片塞进Excel的页眉区域。具体做法是利用POI的XSSFHeaderFooter在页眉中插入一张图片这样打印的时候每一页都会带上水印。这个方案的优点是代码量最小只要往页眉里放一张图即可。缺点是它只在打印预览时能看到在Excel编辑界面完全不显示。对于业务方来说如果他们要的是“打开文件就能看到水印防止截图泄密”页眉水印等于白做——人家截个图发给外面水印根本看不到。3.2 思路二整张PNG作为工作表背景编辑界面可见但不打印第二种思路是生成一张足够大的透明PNG把水印文字平铺在图片上然后用POI的Sheet.setBackgroundPicture方法把这张PNG设置为工作表背景。这是我最推荐的方案水印在编辑界面看得清清楚楚截图出去也能追溯到人。但它的限制也很明显工作表背景图片在Excel里默认不打印。如果业务还要求纸质打印出来也有水印就得用前面的页眉方案做补充。我的最终实现里把两个方案合在一起用了背景水印负责防截图页眉水印负责防打印这样从使用场景上才算完整覆盖。3.3 思路三浮动图片盖在数据区交互体验最差但最直观第三种思路是直接在工作表的数据区域上方放置一张大的半透明PNG图片图片浮在单元格上看起来像水印。这个方案实现起来也简单但有一个致命问题浮动图片会挡住鼠标点击用户没法正常选中单元格、没法复制数据业务方一上手就会骂人。我测试完这个方案后第一时间就放弃了因为它破坏表格的基本可用性。除非你有办法让图片完全不响应鼠标事件否则不要在生产环境用这个思路。3.4 多行多列文字水印真正难的是坐标平铺计算选定了背景PNG方案后真正花时间的是把“多行多列文字水印”画出来。如果只是画一行字谁都会。但水印要覆盖整个Sheet需要把文字按固定间距平铺铺满整张图片而且文字还要带一定倾斜角度否则视觉上不明显。这里有个很关键的细节如果直接把Graphics2D的坐标系旋转45度然后在旋转后的坐标系里循环画文字很容易在画布边缘留下大片空白因为旋转后的文字排版区域和画布矩形并不重合。我一开始就这样画的结果生成的PNG四角都是空的铺到Excel背景里就变成中间有水印、角落没水印的难看的鬼样子。解决办法是先把画布扩大在扩大后的画布上旋转并平铺文字最后再把中间区域裁剪回目标尺寸。具体实现我在下一章给出完整代码。4. 完整实现分批导出与文字水印合流的代码这一章是全文的核心我会按依赖、工具类、ServiceImpl、WriteHandler四层给出可运行的代码。这套代码我实际用在了生产项目里导出一百多万行带水印的Excel文件没有出现问题。4.1 依赖版本与工程目录首先确认依赖版本。EasyExcel的3.x版本迭代较快建议使用较新的稳定版dependency groupIdcom.alibaba/groupId artifactIdeasyexcel/artifactId version3.3.4/version /dependencyEasyExcel本身依赖了POI的XSSF相关模块所以不用额外引入POI的XSSF包。但如果工程里其他地方用了低版本POI可能出现类冲突建议在依赖树里查一下实际生效的POI版本。水印部分的setBackgroundPicture方法需要POI支持建议确认版本不低于4.1.2。工程里只需要一个工具类生成水印PNG一个导出Service以及一个实现了WriteHandler接口的水印注入处理器。所有代码加起来两百行左右。4.2 水印PNG生成工具类文字、角度、透明度、间距都可调水印工具类的完整代码如下public class WatermarkUtil { public static byte[] createWatermarkPng(String text, int width, int height) { int fontSize 28; int canvasWidth (int) (width * 1.6); int canvasHeight (int) (height * 1.6); BufferedImage canvas new BufferedImage(canvasWidth, canvasHeight, BufferedImage.TYPE_INT_ARGB); Graphics2D g canvas.createGraphics(); // 抗锯齿 g.setRenderingHint(RenderingHints.KEY_ANTIALIASING, RenderingHints.VALUE_ANTIALIAS_ON); g.setRenderingHint(RenderingHints.KEY_TEXT_ANTIALIASING, RenderingHints.VALUE_TEXT_ANTIALIAS_ON); // 半透明灰度 g.setComposite(AlphaComposite.getInstance(AlphaComposite.SRC_OVER, 0.18f)); g.setColor(new Color(90, 90, 90)); g.setFont(new Font(微软雅黑, Font.BOLD, fontSize)); g.rotate(Math.toRadians(45), canvasWidth / 2.0, canvasHeight / 2.0); FontMetrics fm g.getFontMetrics(); int textWidth fm.stringWidth(text); int xGap textWidth 120; int yGap 180; // 在旋转坐标系中平铺范围要足够大才能覆盖所有边角 for (int x -canvasHeight; x canvasWidth canvasHeight; x xGap) { for (int y -canvasHeight; y canvasHeight canvasHeight; y yGap) { g.drawString(text, x, y); } } g.dispose(); // 裁剪中间目标区域 BufferedImage target canvas.getSubimage( (canvasWidth - width) / 2, (canvasHeight - height) / 2, width, height); ByteArrayOutputStream bos new ByteArrayOutputStream(); try { ImageIO.write(target, png, bos); } catch (IOException e) { throw new RuntimeException(水印图片生成失败, e); } return bos.toByteArray(); } }这个方法里的width和height建议按A4纸比例设置我用的值是一千二乘一千七左右。水印密度可以通过xGap和yGap调透明度通过AlphaComposite里的0.18f调业务方如果觉得太淡或太浓直接改这两个值就可以。4.3 分批写入ExcelWriter的Service实现Service层的核心是管理好ExcelWriter、WriteSheet和分页查询。我一个简化但完整的示例Service public class ExportService { Resource private DemoMapper demoMapper; public void exportWithBatchAndWatermark(HttpServletResponse response) throws IOException { String fileName URLEncoder.encode(对账流水.xlsx, UTF-8); response.setContentType(application/vnd.openxmlformats-officedocument.spreadsheetml.sheet); response.setHeader(Content-Disposition, attachment;filename*UTF-8 fileName); byte[] watermarkBytes WatermarkUtil.createWatermarkPng(内部资料禁止外传, 1200, 1700); ExcelWriter excelWriter EasyExcel.write(response.getOutputStream(), DemoData.class) .inMemory(false) .registerWriteHandler(new WatermarkWriteHandler(watermarkBytes)) .build(); try { WriteSheet writeSheet EasyExcel.writerSheet(对账流水).build(); long lastId 0L; int pageSize 5000; long currentSheetRows 0L; while (true) { ListDemoData batch demoMapper.selectPageByCursor(lastId, pageSize); if (batch.isEmpty()) { break; } if (currentSheetRows batch.size() 1048576L) { writeSheet EasyExcel.writerSheet(对账流水2).build(); currentSheetRows 0L; } excelWriter.write(batch, writeSheet); lastId batch.get(batch.size() - 1).getId(); currentSheetRows batch.size(); batch.clear(); } } finally { excelWriter.finish(); response.getOutputStream().flush(); } } }这里要强调finally块里调用finish()这样即使中间查询抛异常文件流也能正确关闭不至于把半截文件当成成功响应返回给前端。4.4 通过WriteHandler挂载水印避开类转换陷阱水印怎么跟EasyExcel合流最干净的方式是注册WriteHandler在Sheet创建完成之后拿到POI的Sheets对象再设置背景图片。EasyExcel 3.x的WriteHandler接口签名和早期版本不同为了兼容我建议直接继承AbstractWriteHandler重写afterSheetCreate方法public class WatermarkWriteHandler extends AbstractWriteHandler { private final byte[] watermarkBytes; public WatermarkWriteHandler(byte[] watermarkBytes) { this.watermarkBytes watermarkBytes; } Override public void afterSheetCreate(WriteSheetHolder writeSheetHolder, WriteWorkbookHolder writeWorkbookHolder) { try { Workbook workbook writeWorkbookHolder.getWorkbook(); if (workbook instanceof XSSFWorkbook) { XSSFWorkbook xssfWorkbook (XSSFWorkbook) workbook; XSSFSheet sheet xssfWorkbook.getSheetAt(0); // 背景水印编辑界面可见 sheet.setBackgroundPicture(new ByteArrayInputStream(watermarkBytes)); // 页眉水印打印时每页顶部可见 XSSFHeaderFooter header sheet.getHeader(); int pictureIdx xssfWorkbook.addPicture(watermarkBytes, XSSFWorkbook.PICTURE_TYPE_PNG); header.setPicture(pictureIdx); header.setCenter(G); } else { log.warn(当前Workbook类型不支持水印注入: {}, workbook.getClass().getName()); } } catch (Exception e) { // 水印失败不应该阻断导出生产环境记录日志继续导出 log.error(水印注入失败, e); } } }几个容易出问题的地方我提一下。第一EasyExcel的Workbook类型在非模板模式下通常是XSSFWorkbook但如果你用了某些特殊写模式类型会不同所以代码里做了instanceof判断避免强转ClassCastException。第二水印注入失败不应该让整个导出任务失败所以catch住异常并记录日志业务方拿到没水印的文件总比拿不到文件强。第三header.setCenter(G)表示把插入的图片放到页眉中间位置如果你想页眉同时有文字可以写“G 机密资料”G就是图片占位符。5. 热搜词里那些高频衍生需求冻结、锁定、下拉校验写完主体功能后我又顺手把导出相关的几个高频需求都过了一遍。这些需求在搜索指数里反复出现说明大家做完基础导出后马上都会遇到这些周边功能。我在这里统一整理出来省得你们再挨个搜。5.1 protectSheet锁定全局后怎么只放开指定列有段时间很多人搜easyexcel sheet.protectSheet()锁定全局然后发现整个表都动不了了。这就是因为protectSheet的默认行为是把所有单元格设为锁定状态用户权限如果没有额外放开任何单元格都改不了。如果你只想锁定表头和某些敏感列而允许用户编辑指定的业务列需要先把目标列的单元格样式设为setLocked(false)然后再保护Sheet。顺序不能反先解锁再保护CellStyle unlockStyle workbook.createCellStyle(); unlockStyle.setLocked(false); // 比如格式化成第3列C列可编辑不要锁 XSSFSheet sheet workbook.getSheetAt(0); for (int row 1; row 1000; row) { Cell cell sheet.getRow(row).getCell(2); if (cell null) { cell sheet.getRow(row).createCell(2); } cell.setCellStyle(unlockStyle); } sheet.protectSheet(123456);如果是在EasyExcel的WriteHandler里做这个操作注意行的遍历范围要跟实际数据量匹配不要硬编码1000行。更好的方式是在导出循环里给每条数据对应的行创建单元格时就把样式带上。5.2 冻结表头与冻结指定列冻结列的用法很简单但有个细节容易漏。POI的createFreezePane有两个参数第一个是冻结左侧列数第二个是冻结顶部行数。如果只冻结首行第一个参数要写0sheet.createFreezePane(0, 1); // 只冻结第一行 sheet.createFreezePane(2, 1); // 左侧两列 第一行都冻结冻结和protectSheet可以共存冻结不依赖于单元格的锁定状态所以放心用。5.3 下拉框校验EasyExcel要支持下拉框常见的做法还是走POI的DataValidation。特别是热词里有人搜easyexcel支持下拉框复选吗答案是目前官方不直接支持复选下拉这是Excel原生功能的限制。单选下拉可以这样加DataValidationHelper helper sheet.getDataValidationHelper(); DataValidationConstraint constraint helper.createExplicitListConstraint( new String[]{已核对, 未核对, 异常}); CellRangeAddressList regions new CellRangeAddressList(1, 5000, 3, 3); DataValidation validation helper.createValidation(constraint, regions); sheet.addValidationData(validation);这里CellRangeAddressList的参数依次是起始行、结束行、起始列、结束列。下拉框对性能有一定影响如果给十万行都加上校验文件打开速度会明显变慢。我的建议是下拉区域控制在几千行以内超出的部分不做校验。5.4 复杂表头导出与导入的注意事项热词里还有easyexcel复杂表头导入这个和导出一样是高频需求。导出一侧EasyExcel的ExcelProperty注解天然支持两级表头比如value{一级表头, 二级表头}直接用就行。麻烦的是复杂表头的导入。EasyExcel默认对合并单元格的处理是合并区域只有第一个单元格有值其余单元格都是null。这就导致复杂表头导入后后面的行读不到合并单元格的值字段对不上。解决思路通常是在ReadListener里拿到表头信息后自己维护一个“当前所属一级表头”的上下文遇到null就用上一次的值补上。如果表头层级经常变动建议做成模板导入先把表头结构解析成配置再按配置逐层填充。6. 实测数据与踩坑记录最后分享一些我在实际项目中测出来的数据以及几个印象很深的坑。这些内容常规文档里不会写但都是能让你少加班两天的经验。6.1 一百万行数据导出实测内存与耗时测试环境是8G内存的Linux服务器JVM堆设为512MMySQL数据库。数据量一百万行出头每条记录大概十几个字段。查询分批每批5000行全部导出完成耗时大约五十秒其中数据库查询占了大头写Excel本身不到二十秒。内存峰值我通过监控平台看到的数字大概在280MB左右没有出现OOM。作为对比改成分批前的老代码同样条件下内存直接冲到1.2G以上任务直接被杀掉。所以分不分批差距就是这么大。如果你觉得五十秒还是太慢可以考虑两个优化点。第一是把pageSize调大到10000减少查询次数但要注意单次查询的List对象占用内存会上升建议实测一下。第二是给数据库查询的游标字段加上联合索引确保每页查询都走索引这个优化收益非常明显。6.2 加了水印之后多花多少时间水印PNG生成是一次性的一万行和一百万行都是同一张图所以不会有随着数据量增长的水印耗时。我实测水印PNG生成大约耗时两百毫秒左右Excel写入过程中设置背景图片也是毫秒级操作整体增加的时间可以忽略不计。真正影响体验的是加了背景水印后Excel软件打开文件时的渲染速度。我用办公软件打开同一个文件无水印版本大概一秒内就能显示带水印版本要两到三秒。这个属于客户端渲染开销服务端优化不了但业务方能接受。6.3 踩坑1保护工作表和水印一起开页面显示异常我最初在同一个Sheet上同时做了protectSheet和背景水印结果在办公软件里打开后水印非常淡甚至某些区域完全看不到。排查了半天才发现是protectSheet和背景图片的叠加在某些客户端版本里有渲染冲突尤其是旧版办公软件。解决办法是水印背景照设protectSheet只对需要保护的Sheet生效。如果业务确实要求整个文件带水印又要求单元格锁定我的建议是水印放在背景图层锁定逻辑尽量只锁表头和关键列不要锁全Sheet这样渲染冲突的概率会低很多。6.4 踩坑2导出文件打不开多半是流关闭顺序另一个高发问题是导出完成后文件损坏办公软件提示“文件已损坏无法打开”。我排查过的案例里八成原因是输出流的关闭顺序不对。正确的顺序是先excelWriter.finish()让EasyExcel把Workbook完整写入输出流然后再关闭或flushHttpServletResponse的OutputStream。如果先关流数据还没写完就被截断了。有些同学会在finally里顺手关所有流这时候ExcelWriter内部的流和response的输出流可能已经是同一个对象重复关闭会出问题。稳妥的做法是只在finally里调用finish()response的输出流交给容器或上层框架管理不要手动重复关闭。6.5 我的几个操作习惯写这套代码的过程中我养成了几个习惯现在分享给你。第一水印文字和参数配置不要写死在代码里放到配置中心或properties里业务方改文字、改透明度不用再发一次版。第二每次导出任务建议加一个traceId关联日志方便出问题时快速定位是查询慢还是写文件慢。第三大批量导出建议做成异步任务前端轮询下载链接而不是让HTTP请求一直挂着不然网关超时你都不知道该骂谁。做完这个项目我最大的体会是EasyExcel虽然好用但它给的是积木不是成品。真正决定一个导出功能能不能扛住生产压力的还是你对内存模型和POI底层行为的理解。希望这篇文章能帮你在做类似需求的时候少走点弯路。