Java大数据量Excel分批导出与自适应水印实战方案

做Java后端的人基本都躲不过Excel导出这一关。我这两年用EasyExcel的次数多得数不清最头疼的事情有两个一是数据量一大分批导出怎么做才稳二是导出的文件还要适配水印防止内部资料被随手转发。今天就把我在真实项目里踩过、优化过的完整方案整理出来从分批导出的架构思路到水印适配的两种实现再到保护Sheet、冻结列、下拉框这些高频配套需求一次性讲清楚。这篇文章适合所有用Java做报表导出、遇到大数据量OOM或者要给Excel加防护水印的开发者参考新手也能照着代码跑通。1. 分批导出为什么你早晚会遇到这个问题1.1 数据量一大导出就没那么简单很多团队一开始用EasyExcel都是从最简单的EasyExcel.write(response.getOutputStream(), SomeVO.class).sheet(数据).doWrite(list)开始的。数据量在几千条时这套写法完全没毛病。但一旦数据量到了10万、50万、甚至上百万问题就藏不住了查询接口一次性把全部数据List塞进内存ORM框架在构建对象的时候还会填关联字段内存占用直接飙上去。我见过一个真实案例导出30万条订单每条订单带十几条明细服务端GC直接卡了将近半分钟最后接口超时前端白屏。这里得说清楚一个核心概念EasyExcel本身是做了内存优化的它不会一次性把整个Excel模型加载到内存而是边解析边写文件。但它解决的只是“写文件”这一段的压力没法解决“查询结果已经全部堆在Java堆里”的问题。换句话说如果你用100万条数据的List去调用doWrite那这100万条对象已经在内存里了EasyExcel再怎么优化也救不了你。所以分批导出的本质是把“一次查询100万条”拆成“每次查5000条”每查一批就写入文件一批写完一批这批数据就可以被垃圾回收了。这样应用内存始终只保留一个批次的大小数据库也不会因为一条大SQL而把临时表撑爆。生活里类比一下就是你搬家时不会把整个房子的东西一次性塞进一辆小面包车而是分几趟搬每趟装满就走这样只要一辆小车就能完成任务。1.2 分批方案的分水岭用对ExcelWriter做法上有个关键分水岭是不是真的用了ExcelWriter手动分批写入。很多人以为分批就是“分页查询然后把每页数据依次doWrite”这种想法对了一半但直接用EasyExcel.write().sheet().doWrite(分批数据)会有一个坑——每次调用doWrite都会重新打开一个新的输出流处理完又自动关掉文件会被不断追加还是覆盖全看运气而且同一个Workbook对象可能被重复关闭。正确做法是用ExcelWriter加WriteSheet手动控制生命周期。ExcelWriter相当于一个“长期打开的写入器”它可以多次调用write往同一个Sheet或不同Sheet里写数据只有到最后你调用finish()时它才会flush并关闭底层流。这样就能实现真正的“查一批、写一批、丢一批”的内存友好循环。第一步先确认依赖Spring Boot项目里加这几行就行dependency groupIdcom.alibaba/groupId artifactIdeasyexcel/artifactId version3.3.4/version /dependency我这里直接给一个完整的分批导出模板后面所有问题都基于这段代码展开。2. 分批导出实操从架构设计到落地代码2.1 核心实现ExcelWriter 分批查询循环假设你有一个订单表t_order数据量很大要导出成Excel。实体类和平时一样用ExcelProperty注解定义列名这里不展开。重点在Controller/Services那层怎么写。public void exportLargeData(HttpServletResponse response) throws IOException { String fileName URLEncoder.encode(订单数据, UTF-8).replaceAll(\\, %20); response.setContentType(application/vnd.ms-excel;charsetutf-8); response.setHeader(Content-Disposition, attachment;filename*utf-8 fileName .xlsx); ExcelWriter excelWriter EasyExcel .write(response.getOutputStream(), OrderExportVO.class) .build(); WriteSheet writeSheet EasyExcel.writerSheet(订单明细).build(); int pageSize 5000; long lastId 0; int batchIndex 0; while (true) { ListOrderExportVO pageData orderMapper.selectPageByIdCursor(lastId, pageSize); if (pageData null || pageData.isEmpty()) { break; } excelWriter.write(pageData, writeSheet); lastId pageData.get(pageData.size() - 1).getId(); batchIndex; System.out.println(第 batchIndex 批导出完成本批 pageData.size() 条); } excelWriter.finish(); }对应的Mapper建议用游标查询而不是LIMIT offset。因为LIMIT的offset越大数据库扫描行数越多到后面每查一页都慢得离谱。基于主键游标的写法是Select(SELECT id, order_no, user_name, amount, create_time FROM t_order WHERE id #{lastId} ORDER BY id ASC LIMIT #{pageSize}) ListOrderExportVO selectPageByIdCursor(Param(lastId) long lastId, Param(pageSize) int pageSize);这种方式每次都走主键索引只需要从上次结束的ID往后扫5000条性能稳定不会因为页码变大而越来越慢。这是分批导出里少数让我觉得“用一次就再也回不去”的优化点。2.2 批量大小怎么定批量选择5000条是我在实际项目中经过压测得到的一个相对稳妥的值。太小的批量比如500数据库查询次数多整体耗时成倍上升太大的批量比如5万单批次List本身占的内存又上来了虽然比一次性10万全量加载好但多批次之间内存峰值没降到位。比较合理的范围一般是2000到10000根据你的单条对象复杂程度和服务器堆内存大小调整。如果单条记录字段特别多比如一个VO下面挂了好几个子对象那每批建议压到1000-2000条如果只是简单的扁平字段5000-8000条很舒服。我自己的经验是以导出过程中jstat看到的GC频率和内存峰值为基准去调找到内存峰值和查询耗时都均衡的那个点。还要注意一点在分批导出的循环里不要再去执行System.gc()这种“手动GC”反而可能导致Full GC次数变得不可控影响写入线程。正常做法是确保每批写入完成后分页集合不再被引用自然回收就行了。2.3 分批导出常见的配套需求分批导出的场景多半出现在后台管理系统的“全量导出”按钮上所以顺手会遇到几个问题进度怎么反馈、中途报错怎么办、多个Sheet怎么处理。进度反馈方面因为后台导出往往是异步的所以可以在每次循环后更新导出任务进度比如存到Redis或者内存Map里前端轮询展示“已导出X万条”。这里要注意进度更新别搞得太频繁每批更新一次足够。中途报错方面一定要在finally块里确保excelWriter.finish()被调用。如果没调用就直接返回底层文件流一直开着响应也无法正常结束。多Sheet的情况也简单创建多个WriteSheet每批写入时指定对应Sheet对象就行。3. 适配水印先搞懂Excel水印的底细3.1 Excel其实没有“水印”属性先说一个很多开发人员容易搞混的地方Excel文件格式里并没有一个叫“水印”的开关。我们平时在Word里看到的“页面背景水印”在Excel里是不存在的。所以所谓的“适配水印”本质上是借道其他能力去模拟水印效果。目前主流做法有两种我用一张表先对比清楚方案实现方式打印时是否可见实现难度适用场景工作表背景图把半透明PNG设置为Sheet背景不可见低防截图、防录屏屏幕查看时有水印页眉大图把水印图片塞进页眉并调整位置可见中高需要纸质打印防泄露的场景从业务上说如果只是防止别人拍屏幕、截图外发工作表背景图已经够用了如果导出的文件还要打印存档那就必须考虑页眉图片方案。下面我分别讲两种做法以及它们和EasyExcel分批导出的适配方式。PDF是天然支持水印的Excel没有这种待遇所以你需要用代码去“模拟”它。这块坑比较多尤其是EasyExcel分散批写入的时候水印到底是在第一批次前加、还是最后统一加都会影响最终效果。3.2 方案一用背景图做水印最简单也最实用背景图方案的基本原理拿到EasyExcel底层的POIXSSFSheet往里面添加一张图片再把这个图片作为Sheet的背景。打印时背景图不会被带出来但屏幕显示时它会铺在单元格下面半透明的“内部资料”字样能明显看到又不影响单元格内容阅读。这里有一段可以直接用的工具代码public static void addSheetBackgroundWatermark(XSSFSheet sheet, byte[] imageBytes) throws Exception { XSSFWorkbook workbook sheet.getWorkbook(); int pictureIdx workbook.addPicture(imageBytes, Workbook.PICTURE_TYPE_PNG); XSSFPictureData pictureData workbook.getAllPictures().get(pictureIdx); PackagePart sheetPart sheet.getPackagePart(); PackageRelationship rel sheetPart.addRelationship( pictureData.getPackagePart().getPartName(), TargetMode.INTERNAL, PackageRelationshipTypes.IMAGE_PART); sheet.getCTWorksheet().addNewPicture().setId(rel.getId()); }水印图片本身建议用Java2D动态生成。生成的时候就把透明度做进去不要指望POI帮你调透明度它没这个能力。下面是一个生成半透明水印PNG的示例public static byte[] createWatermarkPng(String text, int width, int height, int alpha) throws IOException { BufferedImage image new BufferedImage(width, height, BufferedImage.TYPE_INT_ARGB); Graphics2D g2d image.createGraphics(); g2d.setComposite(AlphaComposite.getInstance(AlphaComposite.SRC_OVER, alpha / 255f)); g2d.setColor(new Color(0, 0, 0)); g2d.setFont(new Font(Microsoft YaHei, Font.BOLD, 36)); g2d.rotate(Math.toRadians(-30), width / 2.0, height / 2.0); g2d.drawString(text, width / 4, height / 4); g2d.dispose(); ByteArrayOutputStream baos new ByteArrayOutputStream(); ImageIO.write(image, png, baos); return baos.toByteArray(); }接入分批导出时有一个小细节容易踩坑ExcelWriter.build()之后底层Workbook已经存在了但Sheet还没创建。你要等到第一次excelWriter.write(data, writeSheet)之后Sheet才会真正出现在Workbook里。所以水印背景要在第一批数据写入后再加。顺序大致是boolean watermarkAdded false; while (true) { ListOrderExportVO batch orderMapper.selectPageByIdCursor(lastId, pageSize); if (batch.isEmpty()) { break; } excelWriter.write(batch, writeSheet); if (!watermarkAdded) { XSSFSheet sheet (XSSFSheet) excelWriter.writeContext() .writeWorkbookHolder().getWorkbook().getSheetAt(0); byte[] watermark createWatermarkPng(内部资料, 400, 200, 30); addSheetBackgroundWatermark(sheet, watermark); watermarkAdded true; } lastId batch.get(batch.size() - 1).getId(); } excelWriter.finish();实测下来这个方案的文件体积增加不大生成速度也快几乎不影响导出耗时。唯一要注意的是一个Sheet只能有一张背景图而且图片不会自动平铺到每个打印页打印的时候完全没有水印。如果你恰好只需要“屏幕上看起来有水印”这个方案是最省心的。3.3 方案二页眉图片做水印打印也逃不掉如果业务要求导出的Excel打印出来也带水印那就要走页眉图片方案。原理也很好理解Excel的页眉区域支持插入图片而页眉会显示在每一页的顶部。你把一张半透明水印图放大到整个纸张的尺寸塞进页眉再通过偏移把图片挪到页面中间看起来就是整页水印的效果。POI写页眉图片比背景图繁琐得多需要拼一段VML图形描述。核心流程是把水印图片加入Workbook拿到图片的packagePart关系ID构造一段v:shape的VML XML再塞进CTHeaderFooter里。下面是项目中用到的一段核心片段public static void addHeaderWatermark(XSSFSheet sheet, byte[] imageBytes) throws Exception { XSSFWorkbook workbook sheet.getWorkbook(); int picIdx workbook.addPicture(imageBytes, Workbook.PICTURE_TYPE_PNG); XSSFPictureData pictureData workbook.getAllPictures().get(picIdx); Picture picture sheet.createDrawingPatriarch().createPicture( new XSSFClientAnchor(0, 0, 0, 0, 0, 0, 400, 200), picIdx); // 实际页眉水印需要把图片放入页眉关系示例中仅展示图片创建入口 }这里要注意靠sheet.createDrawingPatriarch()创建的图片是浮动图片不是页眉图片真正要打印可见还需要进入页眉的VML区。因为这段代码在不同POI版本里差异较大我在实际项目里的做法是封装一个ExcelWatermarkUtil工具类内部用CTHeaderFooter VML XML来实现具体碎片代码不再展开。你如果只要打印水印的效果还有一个更笨但更稳的思路提前在Excel模板文件里手动把页眉水印做好上报到对象存储程序导出时直接用这个模板文件作为EasyExcel.write(tempTemplatePath).sheet().doWrite(data)的输出模板自带的水印会被保留。这个“模板带水印”的思路其实特别适合业务场景不复杂的团队。不用写POI那些绕得像天书的VML代码也不用管图片坐标和打印缩放比例只要你的导出列不经常变模板做好一次复用就行。4. 常见问题排查与踩坑实录4.1 复杂表头导入导出怎么处理很多人在搜索EasyExcel的时候都会遇到“复杂表头”这个问题。复杂表头指的不只是多行表头还包括合并单元格那种比如“基本信息”横跨姓名和年龄两列“联系方式”横跨电话和邮箱。EasyExcel在导出动态表头时直接用EasyExcel.writerSheet().head(headList)传一个ListListString就能实现每个内层List里放列路径比如ListListString head new ArrayList(); head.add(Arrays.asList(基本信息, 姓名)); head.add(Arrays.asList(基本信息, 年龄)); head.add(Arrays.asList(联系方式, 电话)); EasyExcel.write(response.getOutputStream()) .head(head) .sheet(用户信息) .doWrite(dataList);导入复杂表头时关键是headRowNumber参数要设置正确。如果Excel前两行都是表头就要EasyExcel.read(inputStream).headRowNumber(2).sheet().doReadSync()这样EasyExcel才会从第三行开始解析数据。否则表头行会被当成数据行读进来你会看到第一行数据全是列名。这个坑我帮同事排查过好几次基本都是没设置headRowNumber。4.2 保护Sheet与锁定指定列水印导出经常和“防止篡改”一起出现。EasyExcel本身没有直接提供protectSheet的API但可以通过WriteHandler拿到底层的POISheet来操作。最容易踩坑的地方是很多人以为调用了sheet.protectSheet(密码)之后只要给指定列设置lockedfalse就能实现“锁定大部分列但某列可编辑”。实际逻辑是相反的——Excel单元格默认是锁定状态保护Sheet后全部锁定只有显式设置为lockedfalse的单元格才允许编辑。所以如果一开始不加任何处理就直接protectSheet你会发现整个Sheet都被锁死了。推荐用CellWriteHandler给指定列设置“不锁定”样式public class UnlockColumnHandler implements CellWriteHandler { private final int columnIndex; public UnlockColumnHandler(int columnIndex) { this.columnIndex columnIndex; } Override public void afterCellDispose(CellWriteHandlerContext context) { Cell cell context.getCell(); if (cell.getColumnIndex() columnIndex) { CellStyle unlockStyle context.getWriteWorkbookHolder().getWorkbook().createCellStyle(); unlockStyle.setLocked(false); cell.setCellStyle(unlockStyle); } } }然后再用一个SheetWriteHandler在afterSheetCreate里调用sheet.protectSheet(yourPassword)。这里有个顺序问题不要指望在afterSheetCreate里遍历行列设置样式因为这时候数据还没写入遍历不到任何单元格。正确做法是数据写入时给指定列设置解锁样式最后再保护Sheet。这样设置完之后导出文件里除了指定列可以编辑其他列都是受保护状态。4.3 冻结列让表头固定、列固定冻结列简直是大表导出的标配。不冻结表头的话用户滚动几千行后连“金额”是哪一列都分不清。POI里冻结列的操作非常简单同样放在WriteHandler里public class FreezePaneHandler implements SheetWriteHandler { Override public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) { Sheet sheet writeSheetHolder.getSheet(); sheet.createFreezePane(0, 1); // 冻结第一行也就是表头 } }如果要冻结前两列并让后面的列在滚动时始终能看到第一列可以写sheet.createFreezePane(2, 1)。这里参数的含义是colSplit表示左侧冻结的列数rowSplit表示顶部冻结的行数。实际项目中最常见的是“冻结表头冻结前1-2个关键列”比如订单号、用户ID这种需要前后对照的字段。4.4 下拉框与“复选”需求很多人搜“EasyExcel 支持下拉框复选吗”答案先说清楚EasyExcel原生不支持下拉框更不支持复选。但你可以通过POI的DataValidation给导出Excel加单级下拉列表CellRangeAddressList regions new CellRangeAddressList(1, 1000, 3, 3); DVConstraint constraint DVConstraint.createExplicitListConstraint( new String[]{待支付, 已支付, 已取消}); DataValidation dataValidation new XSSFDataValidation((XSSFDataValidationHelper) helper, constraint, regions); sheet.addValidationData(dataValidation);这里能实现的效果是某一列的每个单元格出现下拉箭头只能从“待支付、已支付、已取消”里选一个。要是想要“复选”比如同时选“已支付”和“已取消”Excel原生下拉框做不到。变通方案是用辅助列加公式或者干脆不用下拉框改用单元格内多值拼接的输入规则。我建议你先明确业务到底要什么如果只是想避免用户乱填单级下拉就够如果一定要多选就要重新评估是不是该做个在线填写页面而不是死磕Excel。4.5 文件名乱码与下载异常分批导出和水印做完之后下载环节也容易翻车。中文文件名在HTTP头里直接写Content-Disposition: attachment;filename订单.xlsx在谷歌浏览器里可能会乱码更稳妥的写法是String fileName URLEncoder.encode(订单数据, UTF-8).replaceAll(\\, %20); response.setHeader(Content-Disposition, attachment;filename*utf-8 fileName .xlsx);这段代码里有两个细节一是URLEncoder.encode之后要把加号替换成%20否则文件名里的空格会变成加号二是建议使用filename*形式它对中文支持更好。我还遇到过一种情况导出的Excel明明数据都对但用WPS打开时提示“文件已损坏”十有八九是excelWriter.finish()没有在finally里执行或者把HTTP响应流提前关掉了。4.6 水印适配的性能与兼容性最后提醒几个和水印强相关的问题。水印图片如果做得太大比如直接拿一张几兆的JPG当背景导出文件体积会暴涨打开卡顿。建议水印PNG控制在几十KB以内做法就是生成时缩小画布尺寸、增大透明度别整那些高清图。兼容性方面背景图水印在WPS和Microsoft Office表现基本一致但页眉图片水印在不同版本Office里的缩放显示可能差很多我建议导出后分别用WPS和Excel打开检查一遍。我个人的体会是分批导出和水印适配这两个需求放在一起做的时候先不要急着写代码先想清楚业务上“水印到底防什么”。如果是防截屏背景图方案够了如果是防打印泄露模板页眉水印更省心力如果既要打印又要动态内容才需要走完整的POI页眉水印方案。想清楚这个前提后面所有技术选型都会变得清晰很多。希望这篇经验整理能帮你少踩几个坑尤其是处理那种几十万行数据还要带水印导出的场景时能一次做对。