★ FEATURED ARTICLE 后端做到第三年我发现自己经手的每一个项目最后都会长出同一个需求导Excel。有的是运营要日报有的是财务要对账有的是客户要求系统里必须带数据导出。前两次我用CSV硬拼第三次换个封装工具应付直到碰上一个复杂到需要合并单元格、下拉选项、多级表头的报表我才老老实实开始啃Apache POI。现在回头看POI不是最难学的库但它是后端绕不开的基本功这篇就当作学习笔记整理一下。这篇文章适合所有后端开发尤其是做报表平台、数据导出导入、运维工具的同学。哪怕你之前没有碰过POI跟着思路走一遍也能知道报表导出到底是怎么回事。文章内容基于我平时的大量实操不是教科书式的API罗列更多是踩坑后的经验总结。1. 后端为什么绕不开Apache POI场景决定技术选型1.1 99%的后端都会遇到的报表需求做后端的人对导出Excel这件事应该有很深的体感。运营需要各种周报月报、管理层要看经营数据、客户需要订单明细这些数据都存在数据库里最直观的交付方式就是Excel文件。可能有人会觉得直接把页面表格截图或者CSV导出就好了但真实业务里没这么简单。CSV倒不是不能用列内容里如果出现换行、英文逗号、特殊符号解析就乱了再碰上金额前面要保留两位小数、日期要固定的中文格式、某些字段要有下拉选择CSV完全扛不住。真正到线上需求往往变成这样表头要固定颜色、某些行要合并、数字列要千分位、日期列要用指定的日期格式、还要有数据校验和公式。这种需求就需要一个能直接操作Excel内部结构的库Apache POI就是这个角色。POI是Apache基金会下面的开源项目它用自己的模型去读写Microsoft Office文档格式。对后端来说最常用的模块就是HSSF和XSSF分别对应老版本的.xls和新版本的.xlsx。工作方式很直白你在Java代码里创建Workbook、Sheet、Row、Cell对象给它们设置样式和数据最后写到文件里反过来读Excel的时候也是从文件解析出这些对象再取数据。听起来简单但里面涉及的内存模型、API版本、性能和样式问题只有真正自己写一套才会发现水很深。1.2 凭什么选POI和CSV、EasyExcel、Hutool的一轮对比先回答一个很多人纠结的问题现在网上明明有EasyExcel、Hutool的ExcelUtil这些封装好的工具还有必要学Apache POI原生API吗我的答案是有必要而且很有必要。封装库简化了操作但也隐藏了原理一旦遇到复杂需求还是在POI层面处理更可靠。拿EasyExcel举例它基于POI做了二次封装读写大文件时自动走SAX模式内存表现通常优于直接new XSSFWorkbook。如果你的项目只是简单的数据导入导出用起来确实舒服。但EasyExcel不支持或者支持得不好的场景也存在比如复杂的单元格样式、合并单元格的大量动态处理、数据验证下拉选项、嵌入图片、图表操作这个时候原生POI更灵活。还有版本适配问题项目里已经有了老版本POI依赖再引入EasyExcel依赖版本冲突能让人调一天。Hutool的ExcelUtil属于工具属性适合快速生成简单报表但是追求复杂格式时会发现不够用。我做技术选型时一般按这个标准来方案优势劣势适用场景Apache POI原生API功能最全、可控性最强、样式细节到位代码量偏多、学习成本高复杂报表、需要精细控制格式EasyExcel封装简单、大文件读写性能好复杂样式支持受限、依赖升级有坑标准列表导出、数据导入解析Hutool ExcelUtil上手极快、代码简洁适合简单场景复杂定制能力弱临时工具、简单表格CSV轻量、通用样式全丢、转义麻烦数据交换、日志导出正常的路子是日常简单场景用封装库节省时间遇到复杂需求回到POI层面解决。但为了能回到POI层面底层API最好还是系统学过一遍。这也是我把POI当作必修课的原因。2. 先搞懂这几个概念POI就不难学了2.1 Workbook、Sheet、Row、Cell的父子关系POI的模型和Excel的可见结构是一一对应的。最高层是Workbook一个Workbook对应一个Excel文件。Workbook里可以创建多个Sheet也就是我们看到的底部标签页。每个Sheet包含多行Row每一行由多个Cell组成。这个模型很直观基本照搬DOM结构。很多初学者一上来就写Workbook workbook new XSSFWorkbook()然后开始疯狂写createRow、createCell写到最后文件能出来但代码可维护性很差。我自己的习惯是先想清楚文档结构再写代码。导出报表之前先在纸上画出表格长什么样第几行是标题第几行是表头数据区从哪行开始哪些行需要合并。结构清楚了代码就是按部就班创建对象而已。还有个细节必须要提Workbook是顶层资源用完之后要关闭。POI的Workbook实现了Closeable接口里面可能持有文件句柄、临时文件句柄不关闭很容易引起资源泄漏比如文件被占用删除不掉、内存一直在涨。我一般用try-with-resources语法或者finally块保证关闭。try (Workbook wb new XSSFWorkbook()) { Sheet sheet wb.createSheet(报表); // 各种创建Row、Cell、样式操作 try (OutputStream out new FileOutputStream(report.xlsx)) { wb.write(out); } }这段代码看着简单但能保证文件写完、资源释放。很多线上问题根因就是没关Workbook或没关输出流。2.2 三种Workbook的选择HSSF、XSSF、SXSSF这是POI里最核心的选型问题。HSSFWorkbook操作的是老的.xls格式文件本质是OLE2复合文档XSSFWorkbook操作的是.xlsx格式底层是ZIP包里面装着一堆XML文件SXSSFWorkbook是XSSF的流式版本专门为了解决大文件导出内存暴涨的问题。很多教程只教你用XSSFWorkbook这是因为现在.xlsx才是主流。但HSSF并没有完全退出历史舞台老系统里可能还有.xls的上传接口要兼容。我在项目中遇到的“文件扩展名和实际格式不一致”问题往往就需要同时考虑HSSF和XSSF。Workbook实现支持格式写模式内存模型适用场景HSSFWorkbook.xls一次性写入全部加载到内存老格式兼容XSSFWorkbook.xlsx一次性写入全部加载到内存中小文件SXSSFWorkbook.xlsx流式写出滑动窗口大文件XSSFWorkbook的问题在于它把Excel文件解析成DOM树放到内存里一个几十MB的Excel在被POI解析后内存占用可能比文件本身大十倍。要导出几十万行数据用XSSFWorkbook直接创建这么多Cell对象堆内存直接告急。SXSSFWorkbook引入了“滑动窗口”机制内存里只保留最近N行其余行刷到磁盘临时文件这样导出百万行数据才有实现基础。2.3 读写两条路线UserModel和EventModelPOI还提供了两套截然不同的解析方式。普通开发时用的是UserModel也就是Workbook、Sheet、Row、Cell那套对象模型。它的好处是API简单逻辑清晰缺点是加载大文件时消耗大量内存。另一条路线是EventModel专门针对xlsx文件的底层XML结构做SAX解析。xlsx文件实际上是一堆XML文件压缩成的ZIP包里面的sheet数据以标签形式存储。SAX方式是边读边解析不会把整棵树放进内存。XSSFReader就是走EventModel思路的一个入口。理解UserModel和EventModel的差异主要在“读大文件”场景有实际价值。如果只是导出我们更多用的是UserModel加SXSSF如果要解析一个几个GB的Excel还用XSSFWorkbook加载大概率直接OOM这时候EventModel才是正解。这个分开记后面实操落地方案时就不会搞混。3. 实操用POI封装一个可直接抄的导出工具3.1 确定表结构以“团队周报汇总”为例做导出前最忌讳的就是不规划就直接写。我拿一个最常见的团队周报汇总表举例需求是导出一个包含日期、人员姓名、所属模块、任务内容、任务状态、工时。表头要有浅色背景加粗字体第一行是大标题“团队周报汇总”跨整张表合并第二行是列头第三行开始是数据。最后还要加一个自动筛选和冻结首行方便运营筛选查看。先去Maven引入POI依赖。POI从5.x开始核心包和ooxml包分开了要同时引入poi和poi-ooxml。dependency groupIdorg.apache.poi/groupId artifactIdpoi/artifactId version5.2.5/version /dependency dependency groupIdorg.apache.poi/groupId artifactIdpoi-ooxml/artifactId version5.2.5/version /dependency顺带说一句POI 5.x要求JDK 8以上老一点的项目如果在JDK 7上挣扎就得用POI 3.x或者4.x这两个大版本的API差异不小。建议新项目直接从5.x开始。3.2 写代码从新建Workbook到设置单元格样式我习惯把导出逻辑封装成独立方法。创建Workbook、创建Sheet、创建标题行和表头行、给对应的Cell设置样式数据行循环创建。核心代码如下Workbook wb new XSSFWorkbook(); Sheet sheet wb.createSheet(周报汇总); // 第一行合并标题 Row titleRow sheet.createRow(0); Cell titleCell titleRow.createCell(0); titleCell.setCellValue(团队周报汇总); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 5)); // 标题样式 CellStyle titleStyle wb.createCellStyle(); Font titleFont wb.createFont(); titleFont.setBold(true); titleFont.setFontHeightInPoints((short) 14); titleStyle.setFont(titleFont); titleStyle.setAlignment(HorizontalAlignment.CENTER); titleStyle.setVerticalAlignment(VerticalAlignment.CENTER); titleCell.setCellStyle(titleStyle); // 第二行表头 String[] headers new String[]{日期, 人员, 所属模块, 任务内容, 任务状态, 工时}; Row headerRow sheet.createRow(1); CellStyle headerStyle wb.createCellStyle(); headerStyle.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex()); headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); Font headerFont wb.createFont(); headerFont.setBold(true); headerStyle.setFont(headerFont); headerStyle.setAlignment(HorizontalAlignment.CENTER); for (int i 0; i headers.length; i) { Cell cell headerRow.createCell(i); cell.setCellValue(headers[i]); cell.setCellStyle(headerStyle); }这段代码看着不长但里面有个很容易忽略的事情样式对象必须从当前Workbook创建不能把一个Workbook里的CellStyle直接塞给另一个Workbook的Cell。我见过有人把样式定义为静态变量导致导出并发时互相污染后面的“坑”里详细说。数据行的写法是循环创建Row再逐个创建Cell。日期列不能直接用LocalDateTimePOI的老版本对LocalDateTime支持不算好我通常把LocalDateTime转换成java.util.Date再放进去配合DataFormat自定义格式。DataFormat format wb.createDataFormat(); CellStyle dateStyle wb.createCellStyle(); dateStyle.setDataFormat(format.getFormat(yyyy-MM-dd)); for (int i 0; i dataList.size(); i) { Row row sheet.createRow(2 i); row.createCell(0).setCellValue(Date.from(dataList.get(i).getDate().atStartOfDay(ZoneId.systemDefault()).toInstant())); row.getCell(0).setCellStyle(dateStyle); row.createCell(1).setCellValue(dataList.get(i).getPersonName()); // 其余列按类型写入 }3.3 进阶技巧合并单元格、冻结窗格、自动筛选、下拉列表基础的写入会了之后真正的难点是那些看起来不起眼的需求。合并单元格用addMergedRegion参数是CellRangeAddress起始行、结束行、起始列、结束列四个值。这个操作特别容易写错因为行号从0开始而且直接写裸数字很容易和需求对不上。建议定义一个常量或者加注释。冻结首行用sheet.createFreezePane(0, 1)前两个参数分别代表列和行。比如希望首行固定不动行数就传1。自动筛选用setAutoFilter参数同样是一个CellRangeAddress指定筛选范围。这三个功能搭配起来报表就有点正经报表的样子了。sheet.createFreezePane(0, 1); sheet.setAutoFilter(new CellRangeAddress(1, dataList.size() 1, 0, 5));下拉列表是另一个经典需求。让“任务状态”列只允许填“已完成、进行中、未开始”三个选项。做法是通过DataValidationHelper创建DataValidationConstraint然后绑定到某个列的数据区域。DataValidationHelper helper sheet.getDataValidationHelper(); DataValidationConstraint constraint helper.createExplicitListConstraint( new String[]{已完成, 进行中, 未开始}); CellRangeAddressList addressList new CellRangeAddressList(2, 1000, 4, 4); DataValidation validation helper.createValidation(constraint, addressList); validation.setShowErrorBox(true); sheet.addValidationData(validation);这里我踩过两个坑。第一下拉列表区域要覆盖足够多的行如果客户后续要录入几千条你只约束到1000行后面就没有下拉了。第二如果目标列数据本身已经有值下拉约束依然生效但用户在程序里写入的值不会自动校验Excel在用户编辑时才会触发校验这个和Excel的行为保持一致不用慌。3.4 模板导出为什么容易翻车还有一种常见做法是定义一个Excel模板里面提前画好表头、样式、合并单元格代码负责往模板里填数据。优点是样式写在模板里代码不用堆一堆CellStyle缺点是动态数据超过模板里预留的行数时新建行的样式需要自己复制否则新行就是白板非常丑。我印象中翻车最多的场景是模板里把“合计”行预先放在数据区下面但实际数据行数不固定要么覆盖了合计行要么合计行往下挪了但样式错位。比较好的做法是模板里只放表头和样式数据全部在代码中动态追加最后再动态创建合计行不依赖模板的固定位置。如果模板本身只是固定样式用XSSFWorkbook读取模板再填充是可行的。但别直接new SXSSFWorkbook(templateWorkbook)来填充超大模板SXSSF本质上为了大文件流式导出而存在拿来改模板容易把模板里复杂的样式搞丢。我的建议是小模板用XSSFWorkbook慢慢改大模板就别想了直接在代码里按需动态生成样式更可控。4. 大文件导出从OOM到百万行不卡顿4.1 先给数据量分个级聊性能之前先得明确数据量级。1万行以内XSSFWorkbook完全够用代码还简单。10万行左右XSSFWorkbook还能勉强撑住但堆内存会比较紧张GC频繁。到了50万行以上再用XSSFWorkbook就是给自己找麻烦必须上SXSSFWorkbook。分段处理的核心思路是多少行数据用多大的内存机制而不是一上来就追求最底层的方案。还有一个容易被忽略的细节Excel单sheet最大行数是1048576行xlsx格式就这么多行。你以为可以无限写实际上超过这个值Excel直接报错。数据量真的超过100万行优先考虑拆成多个Sheet或者直接走CSV分批导出。4.2 SXSSFWorkbook的windowSize与临时文件SXSSFWorkbook用了滑动窗口机制。构造参数rowAccessWindowSize表示内存中保留的最大行数超过的行会被刷到磁盘临时文件里。我一般传100或者500取较小的内存占用。SXSSFWorkbook wb new SXSSFWorkbook(100); wb.setCompressTempFiles(true); Sheet sheet wb.createSheet(大数据报表); // 后续创建Row、Cell的代码和XSSFWorkbook一样设置setCompressTempFiles(true)可以压缩临时文件磁盘占用减少但稍微增加CPU开销。实际项目里如果导出到本机内存盘压缩可以不开如果导出到普通云主机磁盘建议开。临时文件的默认目录是java.io.tmpdir大批量导出时建议显式设置一个独立目录避免系统临时目录被塞满。SXSSF的坑在于它只是一个写入方案不能读取Excel。而且因为滑动窗口内存中被刷走的行不能通过sheet.getRow再读取如果你导出过程中需要回头改某一行最好在刷出去之前完成修改。我见过有人用SXSSF导出后想遍历一遍所有行做二次校验结果只拿到后面的100行还疑惑了半天。记清楚SXSSF是单向的流式写入不是万能Workbook。还有一个必须注意的SXSSFWorkbook用完不仅要close还要调用dispose方法它在finally块里才能真正清理临时文件。某次我漏了dispose临时目录里堆积了几百MB垃圾线上排查了好久才发现是这个原因。4.3 大文件读取用SAX一个简化版的XSSFReader玩法说到读取其实读Excel比写Excel更考验设计。很多同学做导入功能时直接new XSSFWorkbook(inputStream)等到用户上传一个几十MB的Excel程序就OOM了。这时候该换到XSSFReader走SAX。理解方式很简单xlsx文件的sheet部分在ZIP包里是一个独立XMLXSSFReader能让你以流的方式逐行读这个XML遇到一个行就交付一个行而不是把整个表格铺进内存。try (OPCPackage pkg OPCPackage.open(new File(big.xlsx))) { XSSFReader reader new XSSFReader(pkg); SharedStringsTable sst reader.getSharedStringsTable(); StylesTable styles reader.getStylesTable(); // 拿到sheet对应的输入流后用SAX解析 // 在解析器里通过SharedStringsTable把字符串索引转成实际值 }SharedStringsTable是xlsx文件里共享字符串表。设计逻辑是如果很多单元格存的是相同字符串文件里只存一份Cell里只是引用一个索引。解析时遇到类型是s的单元格就要去SharedStringsTable里查实际字符串。这个细节极其容易踩坑用XSSFWorkbook读取时POI替你做了转换换到SAX方式后必须自己处理。用XSSFReader还要注意样式表StylesTable能帮你把单元格样式对应的日期格式、数字格式还原出来否则日期全是序列号。EventModel代码比UserModel复杂很多但内存占用可能只有后者的十分之一。读大文件的场景这个复杂度是值得的。5. POI高频踩坑记录每一条都真实发生过5.1 日期变成数字或者科学计数法这是POI使用中遇到最多的问题。看到导出后Excel里日期是一串数字比如44900这实际上是Excel内部的日期序列值。原因很简单写入日期时如果单元格没有设置日期格式Excel就默认显示成数字。解决办法就是用DataFormat设置格式。读入时也类似如果单元格存的是日期但数据样式丢失拿到手的就是double值需要用DateUtil.isCellDateFormatted判断再转换。更烦人的是科学计数法。数字比较大时在单元格里显示成1.0E20是因为POI写入时默认用的是double。如果业务值是订单号、身份证号这种本身不该参与计算的值写入时建议设置单元格为字符串类型或者用DataFormatter配合样式。用DataFormatter读取时能按Excel原本的显示格式转换成字符串比getNumericCellValue再toString安全得多。DataFormatter formatter new DataFormatter(); String displayValue formatter.formatCellValue(cell);5.2 打开文件就提示损坏导出成功后Excel打开发现“文件已损坏是否尝试修复”是仅次于日期问题的高频翻车。原因通常有三类第一Workbook类型和扩展名不匹配。用XSSFWorkbook写完却把文件名命名为.xls或者反过来。POI虽然能写文件但文件内部格式和扩展名对不上Excel打开就报警。第二输出流没有flush和close文件内容不完整尤其是写入过程中抛异常导致半截文件落地。第三SXSSFWorkbook用了之后没dispose临时文件缺失或未清理文件在内部引用不完整。我修复这种问题的方式是用Excel或者直接解压xlsx查看内部XML结构是否完整。先把扩展名改对再检查代码里的输出流关闭顺序最后看一下是否所有Workbook都正确关闭。5.3 公式写了不计算下拉列表不显示POI写公式有两种情况。一种是不需要结果缓存只写单元格公式交给Excel打开时计算另一种是程序里要拿到计算后的值这时候必须用FormulaEvaluator。Excel打开文件会重新计算但有些轻量级表格软件不会。为了保证数据完整我在导出带合计行的报表时都会用evaluator先算一遍把计算结果作为缓存值写入。FormulaEvaluator evaluator wb.getCreationHelper().createFormulaEvaluator(); Cell cell row.createCell(6); cell.setCellFormula(SUM(F2:F100)); evaluator.evaluateFormulaCell(cell);下拉列表不显示多半是addValidationData之后没有设置setSuppressDropDownArrow或者区域范围不对。还有某些Excel版本需要在设置下拉前保证目标单元格所在的区域有数据或者至少是非空行否则新建的空sheet上下拉箭头可能不出现。稳妥做法是给数据区域写入一个空字符串占位或者在代码里明确设置区域。5.4 线程并发导出时样式串了Java端多线程导出同一个Workbook本来就危险多线程各自导出不同的Workbook相对安全但有限制CellStyle是绑定在Workbook实例上的不能共享。如果把CellStyle定义成静态全局变量两个线程同时new Workbook再把这个静态样式往各自的Workbook里塞轻则样式丢失重则ClassCastException。正确做法是每个线程、每个Workbook实例内部独立创建样式。如果确实想复用样式创建的代码可以抽一个方法方法内从当前Workbook创建而不是从外面传入。我给导出工具类设计接口时也始终要求样式对象不能跨Workbook实例传递。5.5 临时文件堆积和内存释放问题SXSSF导出之后不dispose是最常见的临时文件堆积原因。还有读场景XSSFWorkbook虽然在close时释放资源但如果代码中反复创建Workbook实例而不关闭堆内存会被大量Cell对象占满。另外在大量导出场景里输出流的关闭时机也很关键。先关闭Workbook再写顺序错了。正确顺序是先wb.write(outputStream)再关输出流最后wb.close()。如果反过来先关Workbook再writeooxml文件会写不完整。这里我踩过一次后来习惯统一用一个包装方法处理对外只暴露导出入口。问题症状排查方向解决方案日期数字单元格显示44221CellStyle缺少DataFormatsetDataFormat指定日期格式文件损坏修复弹窗扩展名、输出流、临时文件修正扩展名、关闭流、dispose公式不计算单元格为空或0缺少FormulaEvaluatorevaluateFormulaCell样式串导出样式异常CellStyle跨Workbook共享从当前Workbook创建样式OOM堆内存溢出数据量超过XSSF上限改用SXSSF或EventModel临时文件堆积磁盘空间爆满未调用dispose显式设置临时目录并清理6. 给后来者的一些建议POI这套东西学习路径其实很清晰先跟着教程写一遍简单导出理解Workbook、Sheet、Row、Cell的关系然后试着加上样式、合并、冻结、筛选再尝试用自己的代码导出10万行数据你会自然遇到性能问题这时候再去接触SXSSF理解滑动窗口等有人让你解析一个巨大的Excel你再回头学XSSFReader整个认知就闭环了。我自己的学习顺序没走好一上来就抄了一堆代码结果不知道Workbook为什么必须关闭、不知道样式为什么不能跨Workbook共享、不知道大文件为什么会OOM。后面踩坑踩多了才明白排查问题时最有效的工具就是打开生成的xlsx文件看看里面的XML结构很多表象问题都能直接看到根源。最后有一个建议特别想说不管你的项目里是不是已经用了EasyExcel都建议抽一个下午把Apache POI的基础API过一遍。封装库能帮你快速交付但底层原理会在突发情况里真正帮你兜底。报表导出这种功能看起来是边角料实际上很影响用户体验值得认真对待。
阅读完成 · 觉得有帮助？
