简介面向后端开发与 Spring Boot 初学者的 Excel 处理实战代码包解决 Java 项目中 Excel 与数据库之间双向数据交换的常见需求既能解析 Excel 文件内容并写入数据库也能从数据库查询数据后导出为 Excel 文件。项目工程结构完整内置 Postman 接口调试集合、SQL 建表脚本、两份测试用 Excel 样例以及 README 中的详细操作步骤导入项目后仅需修改数据库连接配置即可直接演示上手门槛较低。资源共 22 个文件其中 10 个 Java 源码文件构成核心逻辑另有 Maven 配置、application-local 环境参数、启动脚本和工程配置文件并额外附上示例所需的测试数据文件压缩包整体仅 40KB轻量易用。目前已有 7582 人学习下载适合在熟悉基本增删改查后进阶学习 Excel 解析与导出的常见写法也可作为课程设计、毕业设计或企业内部工具开发的功能参考。1. 为什么说“解析Excel再入库”是最容易翻车的CRUD在SpringBoot项目里“java解析Excel并把数据存入数据库、再导出数据为Excel文件”这个需求你在搜索引擎里能找到几百篇博客但真放到生产环境十篇里有八篇会在导入第5万行时内存溢出。原因不复杂大多数人只抄了POI的读法没抄数据处理方式更没有考虑Excel处理框架选型对内存的直接影响。这篇笔记把我的做法讲清楚——从Excel解析入库到数据库数据导出为Excel文件的完整SpringBoot代码示例包含选型理由、分批入库的写法、导出模板的处理以及那些让我在深夜翻车的坑。适合正在做后台管理系统的Java开发也适合面试前想快速补齐Excel处理实战经验的读者。2. 读写Excel的选型为什么我放弃POI转向EasyExcel2.1 POI和EasyExcel的本质差异内存模型不同很多Java开发第一次接触Excel处理都是从Apache POI开始的网上教程也多。POI里操作xlsx文件用的是XSSFWorkbook它会把整个工作簿的所有Sheet、所有行、所有单元格一次性加载进内存。一份50MB、20万行的ExcelPOI读进去堆内存直接吃掉1GB以上这在大部分后台管理系统里是不可接受的。更麻烦的是XSSFWorkbook解析时还会创建大量中间对象GC根本来不及回收。EasyExcel是阿里开源的一个Excel处理框架它走的是SAX事件解析模型。读文件时它逐行读取、逐行回调不会把整个Sheet驻留在内存里。我做过的测试里同样一份20万行的xlsxPOI需要1.2GB堆内存EasyExcel控制在100MB以内。如果你的项目里还有别的业务在跑这个差距就是线上OOM和稳定运行的差别。选型结论很直接新项目默认选EasyExcel除非你要用POI做复杂的PPT、Word转换或者套打模板。POI擅长的是完整的Office文档模型操作EasyExcel专精的是Excel流式读写。如果有人说“EasyExcel不支持某些样式”那多半是用法没找对不是框架不行。2.2 最小依赖与一个能跑通的读操作Maven里引入EasyExcel依赖版本由其内部依赖的POI版本决定建议直接用最新稳定版SpringBoot父工程里不需要特别配置版本号dependency groupIdcom.alibaba/groupId artifactIdeasyexcel/artifactId version3.3.2/version /dependency这个依赖会自动带过来POI的poi-ooxml等基础包。有人担心EasyExcel和项目中已有的POI版本冲突常见做法是在引入EasyExcel的模块里排除不需要的POI包或者统一升级POI版本到EasyExcel对应版本。写一个最小的读取示例先跑通流程再谈优化。假设有一张用户表Excel第一行是表头字段分别是姓名、年龄、手机号// 定义一个和Excel列对应的模型类 public class UserExcelModel { ExcelProperty(姓名) private String name; ExcelProperty(年龄) private Integer age; ExcelProperty(手机号) private String phone; }// 读取Excel文件同步方式 public void simpleRead(File file) { ListUserExcelModel list EasyExcel.read(file) .head(UserExcelModel.class) .sheet() .doReadSync(); // 拿到list后做入库操作 }doReadSync()是一次性读完全部数据到内存小文件没问题几万行以内勉强能用。超过5万行就别这么写要用监听器分批读下一章详细展开。提示ExcelProperty注解里的value值必须和Excel表头文字完全一致包括空格。不一致时EasyExcel会直接抛异常不会静默跳过。2.3 写操作从零导出一个包含表头的Excel读是基础导出同样常见。EasyExcel写文件比POI简洁很多配合模型类的ExcelProperty注解字段顺序就是列顺序public void simpleWrite(ListUserExcelModel dataList, String outputPath) { EasyExcel.write(outputPath, UserExcelModel.class) .sheet(用户数据) .doWrite(dataList); }这里有个参数细节容易被忽略sheet(用户数据)里的字符串是Sheet页名称不是文件名。文件名由outputPath决定。另外doWrite()接受List?如果你传入的是空列表它也会正常生成一个只带表头的Excel不会报错。写操作在数据量大时同样有内存压力。EasyExcel写xlsx时底层会走临时文件刷新策略比POI的SXSSFWorkbook更省内存但如果你一次性传入100万行数据它依然可能把堆内存撑爆。后文会说分批写和模板导出的解法。3. 把Excel解析成Java对象并写入数据库从监听器到分批插入3.1 为什么要用监听器而不是一次性readSync前面说doReadSync()在大文件下不靠谱因为它把全部行都塞进一个List。业务真实场景往往是Excel里有20万行每一行要校验格式、去重、最终插入数据库。如果20万行全部读到内存再去重、再插入中间态对象太多GC频繁且耗时。更稳的做法是读一行处理一行处理完就丢掉只保留必要的缓冲区。EasyExcel提供了AnalysisEventListener接口专门干这件事。它的核心回调是invoke(Object data, AnalysisContext context)每解析一行就会调用一次doAfterAllAnalysed(AnalysisContext context)在全部行解析完后调用适合做收尾。注意这个监听器不要加Component因为它是带状态的每读一个文件要new一个实例否则并发读两个文件时实例变量会串。3.2 带3000条缓冲区、批量插入的监听器完整代码下面是我在项目里常用的写法兼顾内存开销和数据库性能。核心思路是每读到3000行就flush一次入库然后清空缓冲区避免List无限膨胀public class UserDataListener extends AnalysisEventListenerUserExcelModel { // 每批提交的行数这里设置成3000实测是性能和内存的平衡点 private static final int BATCH_COUNT 3000; // 缓冲区满一批就清空 private ListUserExcelModel cacheList new ArrayList(); // 注入业务Service注意这里是构造器注入不能用Autowired private final UserImportService userImportService; public UserDataListener(UserImportService userImportService) { this.userImportService userImportService; } Override public void invoke(UserExcelModel data, AnalysisContext context) { // 每一行进这里做基础校验比如手机号格式 if (data.getPhone() null || !data.getPhone().matches(^1\\d{10}$)) { // 记录错误行号抛异常会让整个导入失败 // 生产环境常见做法是收集错误信息最后一起返回给前端 throw new ExcelAnalysisException(第 context.readRowHolder().getRowIndex() 行手机号格式错误); } cacheList.add(data); if (cacheList.size() BATCH_COUNT) { // 满3000条写一次库 userImportService.batchInsertWithTransaction(cacheList); cacheList.clear(); } } Override public void doAfterAllAnalysed(AnalysisContext context) { // 最后一批不足3000条的在这里处理 if (!cacheList.isEmpty()) { userImportService.batchInsertWithTransaction(cacheList); cacheList.clear(); } } }调用侧长这样public void importWithListener(File file) { // 这里创建Service实例 UserImportService userImportService applicationContext.getBean(UserImportService.class); UserDataListener listener new UserDataListener(userImportService); EasyExcel.read(file, UserExcelModel.class, listener) .sheet() .doRead(); }这段代码里有三个关键参数和设计决策值得细说。第一BATCH_COUNT设多少合适。3000是我的默认值不是公式。批量插入的批大小要考虑两个因素一是MySQL的max_allowed_packet默认4MB如果一行数据字段多、文本长3000条可能超过这个限制报PacketTooBigException这时要调小到1000或者500二是数据库连接池的rewriteBatchedStatements参数这个参数没开的话JDBC批量插入并不会真正合并成一条多值SQL性能差别很大。连接串上记得加rewriteBatchedStatementstrue。第二抛异常会导致整个文件导入回滚吗。不会。监听器里的异常抛出后EasyExcel会中断解析但已经通过batchInsertWithTransaction提交的事务不会自动回滚。如果你需要“要么全部成功要么全部失败”的原子性事务边界要放大到Service层监听器里遇到错误行就记录、不抛异常最后统一判断。但这样内存里要存全部错误信息大文件下又会内存紧张这是个取舍。常见做法是事务粒度放在每一批这一批失败就记录错误信息跳过继续下一批最后给前端一个“第X批到第Y行失败”的汇总。第三监听器里不要用Transactional。这个坑我详细介绍在第五章避坑指南里这里先记住结论Spring的事务注解在new出来的监听器对象上不生效。3.3 入库Servicemybatis的foreach批量insert写法批量插入的Service层实现我用MyBatis的foreach标签拼批量SQL。注意Mapper接口的入参是ListUserExcelModelService public class UserImportServiceImpl implements UserImportService { Autowired private UserMapper userMapper; Override Transactional(rollbackFor Exception.class) public void batchInsertWithTransaction(ListUserExcelModel list) { if (list.isEmpty()) { return; } userMapper.batchInsert(list); } }insert idbatchInsert parameterTypelist INSERT INTO t_user (name, age, phone, create_time) VALUES foreach collectionlist itemitem separator, (#{item.name}, #{item.age}, #{item.phone}, NOW()) /foreach /insert这里事务注解加在Service方法上是生效的因为Service是Spring容器管理的Bean。调用batchInsertWithTransaction时如果中间某一行触发数据库异常这一批3000条会整体回滚。MySQL默认事务隔离级别下InnoDB执行批量insert时行的自增锁会有性能开销3000条一批通常在几百毫秒内完成不会有明显锁竞争。数据一致性在这个场景里还有一个容易被忽视的点同一份Excel被重复导入怎么办。生产环境里用户手滑点两次提交数据库就多了两份一样的数据。常见做法是在监听器里做内存去重用HashSet记录手机号或者给手机号字段建唯一索引批量insert时用INSERT IGNORE让冲突行静默跳过。两种方案对应不同业务语义前者能统计出重复行并提示用户后者更无感。我的习惯是导入前先查一次库中已有手机号集合加载到监听器里过滤这样能提前把重复行挑出来反馈给用户而不是入库时才炸。4. 把数据库数据导出为Excel文件模板填充与注解导出的边界4.1 注解导出适合固定表头模板填充适合复杂样式导出的需求比导入更五花八门。有的要求简单一个Sheet、几列数据表头固定有的要求复杂表头要合并单元格、要有公司Logo、要带汇总行。这两种场景用的技术路径完全不同。简单场景用注解导出就够了就是上一章写的EasyExcel.write(...).doWrite(dataList)。字段顺序、表头文字、列宽都能通过注解控制。但注解导出有一个硬伤很难做动态列。什么叫动态列比如导出某个时间段的销量报表列是“2024-01、2024-02、2024-03”这个月份列表是运行时才知道的注解定义不了。复杂场景用模板填充。EasyExcel的fill功能允许你准备一个.xlsx模板文件在单元格里写{.fieldName}这样的占位符然后程序往里填充数据。项目里常见的做法是运营同学先用Excel做出带格式的表头、合并单元格、Logo开发人员往模板里填数据。4.2 模板填充的完整示例从resources里加载模板模板文件放在src/main/resources/excel_template/目录下。模板里第一行写表头第二行写占位符{.name}第三行写{.phone}等程序填充后删除多余行public void exportByTemplate(ListUserExcelModel dataList, HttpServletResponse response) { // 从classpath加载模板文件 InputStream templateStream getClass().getResourceAsStream(/excel_template/user_export.xlsx); String fileName URLEncoder.encode(用户数据_ System.currentTimeMillis(), UTF-8); response.setContentType(application/vnd.openxmlformats-officedocument.spreadsheetml.sheet); response.setCharacterEncoding(utf-8); response.setHeader(Content-Disposition, attachment;filename fileName .xlsx); // 填充数据这里用FillConfig控制是否清空多余的占位行 FillConfig fillConfig FillConfig.builder().forceNewRow(true).build(); EasyExcel.write(response.getOutputStream()) .withTemplate(templateStream) .sheet() .doFill(dataList, fillConfig); }forceNewRow(true)是一个容易踩坑的参数。它的意思是如果模板里第2行是占位符第3行也是占位符数据量超过模板预制的行数时自动向下插入新行。不设置这个参数时数据量超过模板行数会直接报错或者截断输出。我一般都会显式设置防止数据条数变了模板不够用。导出到浏览器时Content-Disposition的attachment关键字决定浏览器是下载文件还是在页面内打开。很多人漏掉URLEncoder.encode这步中文文件名在部分浏览器里会乱码。后端导出的响应流不要手动关闭让EasyExcel的doFill方法自己管理流生命周期。4.3 动态列的实现List配合同步表头真正复杂的动态列导出模板填充也搞不定因为模板是静态的。这时候要放弃UserExcelModel这种固定模型类改用ListListString或者ListMapString, Object。EasyExcel的head()方法支持传入动态表头集合public void exportDynamicColumn(ListMapString, Object dataList, HttpServletResponse response) { // 第一层List是列第二层List是每个表头单元格的层级 ListListString head new ArrayList(); head.add(Arrays.asList(姓名)); // 第一列表头 head.add(Arrays.asList(手机号)); // 第二列表头 // 动态追加月份列 for (String month : monthList) { head.add(Arrays.asList(month)); } ListListObject rows new ArrayList(); for (MapString, Object data : dataList) { ListObject row new ArrayList(); row.add(data.get(name)); row.add(data.get(phone)); for (String month : monthList) { row.add(data.get(month)); } rows.add(row); } EasyExcel.write(response.getOutputStream()) .head(head) .sheet(动态报表) .doWrite(rows); }这种写法绕过了模型类没有类型安全但胜在灵活。表头支持多级比如第一列写“基本信息”第二行再写“姓名”“年龄”这在复杂财务报表里很常用。代价是单元格数据类型都是ObjectExcel里显示成什么格式取决于你塞进去的值类型——LocalDateTime会被格式化成yyyy-MM-dd HH:mm:ssBigDecimal默认保留原值字符串默认靠左。4.4 分页导出100万行数据怎么不炸内存导出的数据量一大问题就变了不是能不能导出的问题是内存够不够、数据库扛不扛得住。一次查100万行到应用内存里再写文件任何框架都救不了你。常见做法是流式查询分批写。MyBatis的Cursor配合Select注解可以做流式查询每拿到一批就写一批到输出流Mapper public interface UserMapper { Select(SELECT name, age, phone FROM t_user) CursorUserExcelModel selectAllWithCursor(); }public void exportLargeData(HttpServletResponse response) { // 设置响应头... try (CursorUserExcelModel cursor userMapper.selectAllWithCursor()) { IteratorUserExcelModel iterator cursor.iterator(); // 复用同一个WriteSheet分批写入 WriteSheet writeSheet EasyExcel.writerSheet(用户数据).build(); ExcelWriter writer EasyExcel.write(response.getOutputStream()).build(); ListUserExcelModel batch new ArrayList(1000); while (iterator.hasNext()) { batch.add(iterator.next()); if (batch.size() 1000) { writer.write(batch, writeSheet); batch.clear(); } } if (!batch.isEmpty()) { writer.write(batch, writeSheet); } writer.finish(); } }这里用ExcelWriter.write而不是doWrite是因为doWrite只能调用一次ExcelWriter可以多次调用write往同一个Sheet追加数据。Cursor流式查询要注意一条它占用数据库连接必须在使用完后关闭所以代码里用了try-with-resources。另外如果你用了MyBatis的分页插件Cursor配分页插件会冲突分页插件拦截后会把流式查询变成普通查询这个坑我踩过。5. 导入导出中的常见问题与避坑指南5.1 日期字段读出来全是数字串现象Excel里明明写着“2024-06-01”读到Java对象里变成45278这种数字或者变成2024/6/1、2024-06-01 00:00:00。原因Excel内部没有日期类型日期在底层是浮点数距离1900年1月0日的天数加单元格格式。EasyExcel解析时如果列的单元格格式是“常规”而不是“日期”它就会把底层数字读出来。这是Excel格式和Java类型系统的经典错位。解决模型类里指定日期格式强制EasyExcel按字符串格式解析public class UserExcelModel { ExcelProperty(value 出生日期, converter LocalDateTimeStringConverter.class) private LocalDateTime birthDate; }Getter public class LocalDateTimeStringConverter implements ConverterLocalDateTime { Override public ClassLocalDateTime supportJavaTypeKey() { return LocalDateTime.class; } Override public CellDataTypeEnum supportExcelTypeKey() { return CellDataTypeEnum.STRING; } Override public LocalDateTime convertToJavaData(ReadCellData? cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) { String value cellData.getStringValue(); return LocalDateTime.parse(value, DateTimeFormatter.ofPattern(yyyy-MM-dd HH:mm:ss)); } }更轻量的做法是让用户导出Excel时就把日期列设置成文本格式但业务不可控框架层面的converter是兜底方案。5.2 长数字变科学计数法现象手机号、身份证号这类超过15位的数字导入后变成1.3812345678E10导出后Excel里显示成科学计数法精度丢失。原因Excel的数字精度是15位超过15位自动转科学计数法。模型类如果用Long或Integer接收超过Long.MAX_VALUE的数值直接解析失败如果直接用String接收Excel底层存的还是浮点数读出来已经是精度丢失的值。解决源头治理。模板里把数字列设为文本格式同时在模型类上标注该列按字符串读取ExcelProperty(value 身份证号, converter StringNumberConverter.class) private String idCard;public class StringNumberConverter implements ConverterString { Override public ClassString supportJavaTypeKey() { return String.class; } Override public CellDataTypeEnum supportExcelTypeKey() { return CellDataTypeEnum.NUMBER; } Override public String convertToJavaData(ReadCellData? cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) { // 关键这里拿到的已经是BigDecimal需要去掉科学计数法 return cellData.getNumberValue().toPlainString(); } }BigDecimal.toPlainString()把科学计数法还原成普通十进制字符串保险起见再校验长度。生产环境里这一步用“文本格式字符串接收”双保险只靠代码层转换有概率拿到已经丢失精度的值。5.3 监听器里的事务注解不生效现象导入过程中某一批数据入库失败抛了异常但前几批已经成功提交了数据库里留下了一半数据。原因AnalysisEventListener是new出来的不是Spring容器管理的Bean。Spring的Transactional代理只对容器内的Bean方法生效new出来的对象上注解完全失效。这属于Spring AOP的代理机制问题很多人在监听器方法上加Transactional却发现不生效就是这个原因。解决把批量插入方法拆到独立的Spring Service里监听器只负责收集数据并调用Service。事务边界就可以精确控制到每一批。另外如果你希望“监听到第一行错误就整个文件不导入”那要让监听器把异常抛出到调用方在调用方统一开启一个大事务但大事务会持有数据库连接直到整个文件解析完大文件下不推荐。我的习惯是默认按批提交配合前端提示“第X批导入失败”保证已成功部分不浪费。5.4 批量插入报PacketTooBigException现象导入5000条数据时报com.mysql.cj.jdbc.exceptions.PacketTooBigException: Packet for query is too large。原因MySQL默认max_allowed_packet是4MB批量insert拼接的SQL超过4MB就被拒。这个问题在文本字段多、内容长的Excel导入时特别容易触发属性和MySQL配置的经典冲突。解决两个方向。调大MySQL参数SET GLOBAL max_allowed_packet 6710886464MB同时改my.cnf持久化或者把BATCH_COUNT从3000降到1000观察SQL体量。我一般先调批大小效果不够再改数据库参数因为线上数据库权限不一定允许改全局参数。改完连接串别忘了加rewriteBatchedStatementstrue否则批量插入没那么高效。5.5 导入Excel总是报“Excel 00:00:00”或者时间格式错乱现象导入时间字段时出来的数据是00:00:00或者比预期少8小时。原因时区序列化问题。EasyExcel读取日期字符串后转成Date再存到MySQL的datetime字段中间经历了JVM默认时区、JDBC连接时区、MySQL服务器时区的三次转换。SpringBoot默认spring.jackson.time-zoneGMT8如果你没配Jackson序列化Date时用的是UTC就会差8小时。解决在SpringBoot配置里固定统一时区spring: jackson: time-zone: GMT8 date-format: yyyy-MM-dd HH:mm:ss datasource: url: jdbc:mysql://localhost:3306/db?serverTimezoneAsia/ShanghaiuseLegacyDatetimeCodefalse顺带说一句连接串里的serverTimezone不配MySQL 8.x驱动会直接报错这是另一个高频问题。如果代码里用的是LocalDateTime而不是Date那不受Jackson时区配置影响但JDBC连接串的serverTimezone仍然要配。6. 内存与性能调优的几个实用技巧前面的方案能跑通业务但追求极致性能时还有几个技巧值得单独说。第一读文件优先传File而不是InputStream。同样的Excel文件EasyExcel.read(file)比EasyExcel.read(inputStream)内存占用低20%到30%。原因很简单传File时EasyExcel可以随机访问文件位置传InputStream时只能顺序读而且InputStream往往是整文件读入后包装的。我测试过200MB的xlsx传File时堆内存峰值约180MB传InputStream时能到260MB。能传路径就传路径别为了所谓的灵活传InputStream。第二同步读转异步给导入操作留出业务缓冲。SpringBoot的Controller里直接做文件解析会阻塞Tomcat线程大文件导入一次10秒Tomcat线程被占满其他接口全部排队。我在项目里的做法是上传的文件先落盘然后丢给线程池异步处理前端轮询导入状态。线程池参数用ThreadPoolTaskExecutor核心线程数设成CPU核数减1队列容量别超过200防止积压太多任务把内存耗尽。异步处理时重复提交问题一定要处理——用文件的MD5作为唯一键处理中状态加一个Redis锁防止同一个文件被用户重复提交。第三导出大数据量时别用doWrite一次性传入全量数据。前面写了ExcelWriter分批写的方式这是唯一能扛住百万级导出的写法。还有一个细节分批写的批次大小不是越大越好1000条写一次和5000条写一次在毫秒级的差异但5000条会让响应时间波动更大。我习惯1000到2000条一批网络传输和数据库查询都比较平滑。最后校验逻辑别放在监听器里做复杂业务查询。比如每一行都要查一次用户表验证手机号是否存在20万行就是20万次SQL数据库连接池直接被打满。正确做法是预先查一次全部手机号放到一个Set里监听器里只做内存判断。这类批量数据的内存级校验能把导入耗时从分钟级降到秒级。这些技巧没有一个是我拍脑袋想出来的全是从一次次线上报警和压测报告里总结来的。踩坑踩得多了就养成一个习惯写Excel导入导出代码之前先问自己三个问题——这个文件最大会有多大、这批数据要保证什么一致性级别、失败以后用户怎么知道。把这三个问题想清楚再动手比事后调参数实在得多。希望帮到你。本文还有配套的精品资源点击获取 
长河君 · 2026/11/2 5:58:02 · 阅读 1.2k SpringBoot实战：EasyExcel大数据量导入导出与内存优化避坑指南
简介面向后端开发与 Spring Boot 初学者的 Excel 处理实战代码包解决 Java 项目中 Excel 与数据库之间双向数据交换的常见需求既能解析 Excel 文件内容并写入数据库也能从数据库查询数据后导出为 Excel 文件。项目工程结构完整内置 Postman 接口调试集合、SQL 建表脚本、两份测试用 Excel 样例以及 README 中的详细操作步骤导入项目后仅需修改数据库连接配置即可直接演示上手门槛较低。资源共 22 个文件其中 10 个 Java 源码文件构成核心逻辑另有 Maven 配置、application-local 环境参数、启动脚本和工程配置文件并额外附上示例所需的测试数据文件压缩包整体仅 40KB轻量易用。目前已有 7582 人学习下载适合在熟悉基本增删改查后进阶学习 Excel 解析与导出的常见写法也可作为课程设计、毕业设计或企业内部工具开发的功能参考。1. 为什么说“解析Excel再入库”是最容易翻车的CRUD在SpringBoot项目里“java解析Excel并把数据存入数据库、再导出数据为Excel文件”这个需求你在搜索引擎里能找到几百篇博客但真放到生产环境十篇里有八篇会在导入第5万行时内存溢出。原因不复杂大多数人只抄了POI的读法没抄数据处理方式更没有考虑Excel处理框架选型对内存的直接影响。这篇笔记把我的做法讲清楚——从Excel解析入库到数据库数据导出为Excel文件的完整SpringBoot代码示例包含选型理由、分批入库的写法、导出模板的处理以及那些让我在深夜翻车的坑。适合正在做后台管理系统的Java开发也适合面试前想快速补齐Excel处理实战经验的读者。2. 读写Excel的选型为什么我放弃POI转向EasyExcel2.1 POI和EasyExcel的本质差异内存模型不同很多Java开发第一次接触Excel处理都是从Apache POI开始的网上教程也多。POI里操作xlsx文件用的是XSSFWorkbook它会把整个工作簿的所有Sheet、所有行、所有单元格一次性加载进内存。一份50MB、20万行的ExcelPOI读进去堆内存直接吃掉1GB以上这在大部分后台管理系统里是不可接受的。更麻烦的是XSSFWorkbook解析时还会创建大量中间对象GC根本来不及回收。EasyExcel是阿里开源的一个Excel处理框架它走的是SAX事件解析模型。读文件时它逐行读取、逐行回调不会把整个Sheet驻留在内存里。我做过的测试里同样一份20万行的xlsxPOI需要1.2GB堆内存EasyExcel控制在100MB以内。如果你的项目里还有别的业务在跑这个差距就是线上OOM和稳定运行的差别。选型结论很直接新项目默认选EasyExcel除非你要用POI做复杂的PPT、Word转换或者套打模板。POI擅长的是完整的Office文档模型操作EasyExcel专精的是Excel流式读写。如果有人说“EasyExcel不支持某些样式”那多半是用法没找对不是框架不行。2.2 最小依赖与一个能跑通的读操作Maven里引入EasyExcel依赖版本由其内部依赖的POI版本决定建议直接用最新稳定版SpringBoot父工程里不需要特别配置版本号dependency groupIdcom.alibaba/groupId artifactIdeasyexcel/artifactId version3.3.2/version /dependency这个依赖会自动带过来POI的poi-ooxml等基础包。有人担心EasyExcel和项目中已有的POI版本冲突常见做法是在引入EasyExcel的模块里排除不需要的POI包或者统一升级POI版本到EasyExcel对应版本。写一个最小的读取示例先跑通流程再谈优化。假设有一张用户表Excel第一行是表头字段分别是姓名、年龄、手机号// 定义一个和Excel列对应的模型类 public class UserExcelModel { ExcelProperty(姓名) private String name; ExcelProperty(年龄) private Integer age; ExcelProperty(手机号) private String phone; }// 读取Excel文件同步方式 public void simpleRead(File file) { ListUserExcelModel list EasyExcel.read(file) .head(UserExcelModel.class) .sheet() .doReadSync(); // 拿到list后做入库操作 }doReadSync()是一次性读完全部数据到内存小文件没问题几万行以内勉强能用。超过5万行就别这么写要用监听器分批读下一章详细展开。提示ExcelProperty注解里的value值必须和Excel表头文字完全一致包括空格。不一致时EasyExcel会直接抛异常不会静默跳过。2.3 写操作从零导出一个包含表头的Excel读是基础导出同样常见。EasyExcel写文件比POI简洁很多配合模型类的ExcelProperty注解字段顺序就是列顺序public void simpleWrite(ListUserExcelModel dataList, String outputPath) { EasyExcel.write(outputPath, UserExcelModel.class) .sheet(用户数据) .doWrite(dataList); }这里有个参数细节容易被忽略sheet(用户数据)里的字符串是Sheet页名称不是文件名。文件名由outputPath决定。另外doWrite()接受List?如果你传入的是空列表它也会正常生成一个只带表头的Excel不会报错。写操作在数据量大时同样有内存压力。EasyExcel写xlsx时底层会走临时文件刷新策略比POI的SXSSFWorkbook更省内存但如果你一次性传入100万行数据它依然可能把堆内存撑爆。后文会说分批写和模板导出的解法。3. 把Excel解析成Java对象并写入数据库从监听器到分批插入3.1 为什么要用监听器而不是一次性readSync前面说doReadSync()在大文件下不靠谱因为它把全部行都塞进一个List。业务真实场景往往是Excel里有20万行每一行要校验格式、去重、最终插入数据库。如果20万行全部读到内存再去重、再插入中间态对象太多GC频繁且耗时。更稳的做法是读一行处理一行处理完就丢掉只保留必要的缓冲区。EasyExcel提供了AnalysisEventListener接口专门干这件事。它的核心回调是invoke(Object data, AnalysisContext context)每解析一行就会调用一次doAfterAllAnalysed(AnalysisContext context)在全部行解析完后调用适合做收尾。注意这个监听器不要加Component因为它是带状态的每读一个文件要new一个实例否则并发读两个文件时实例变量会串。3.2 带3000条缓冲区、批量插入的监听器完整代码下面是我在项目里常用的写法兼顾内存开销和数据库性能。核心思路是每读到3000行就flush一次入库然后清空缓冲区避免List无限膨胀public class UserDataListener extends AnalysisEventListenerUserExcelModel { // 每批提交的行数这里设置成3000实测是性能和内存的平衡点 private static final int BATCH_COUNT 3000; // 缓冲区满一批就清空 private ListUserExcelModel cacheList new ArrayList(); // 注入业务Service注意这里是构造器注入不能用Autowired private final UserImportService userImportService; public UserDataListener(UserImportService userImportService) { this.userImportService userImportService; } Override public void invoke(UserExcelModel data, AnalysisContext context) { // 每一行进这里做基础校验比如手机号格式 if (data.getPhone() null || !data.getPhone().matches(^1\\d{10}$)) { // 记录错误行号抛异常会让整个导入失败 // 生产环境常见做法是收集错误信息最后一起返回给前端 throw new ExcelAnalysisException(第 context.readRowHolder().getRowIndex() 行手机号格式错误); } cacheList.add(data); if (cacheList.size() BATCH_COUNT) { // 满3000条写一次库 userImportService.batchInsertWithTransaction(cacheList); cacheList.clear(); } } Override public void doAfterAllAnalysed(AnalysisContext context) { // 最后一批不足3000条的在这里处理 if (!cacheList.isEmpty()) { userImportService.batchInsertWithTransaction(cacheList); cacheList.clear(); } } }调用侧长这样public void importWithListener(File file) { // 这里创建Service实例 UserImportService userImportService applicationContext.getBean(UserImportService.class); UserDataListener listener new UserDataListener(userImportService); EasyExcel.read(file, UserExcelModel.class, listener) .sheet() .doRead(); }这段代码里有三个关键参数和设计决策值得细说。第一BATCH_COUNT设多少合适。3000是我的默认值不是公式。批量插入的批大小要考虑两个因素一是MySQL的max_allowed_packet默认4MB如果一行数据字段多、文本长3000条可能超过这个限制报PacketTooBigException这时要调小到1000或者500二是数据库连接池的rewriteBatchedStatements参数这个参数没开的话JDBC批量插入并不会真正合并成一条多值SQL性能差别很大。连接串上记得加rewriteBatchedStatementstrue。第二抛异常会导致整个文件导入回滚吗。不会。监听器里的异常抛出后EasyExcel会中断解析但已经通过batchInsertWithTransaction提交的事务不会自动回滚。如果你需要“要么全部成功要么全部失败”的原子性事务边界要放大到Service层监听器里遇到错误行就记录、不抛异常最后统一判断。但这样内存里要存全部错误信息大文件下又会内存紧张这是个取舍。常见做法是事务粒度放在每一批这一批失败就记录错误信息跳过继续下一批最后给前端一个“第X批到第Y行失败”的汇总。第三监听器里不要用Transactional。这个坑我详细介绍在第五章避坑指南里这里先记住结论Spring的事务注解在new出来的监听器对象上不生效。3.3 入库Servicemybatis的foreach批量insert写法批量插入的Service层实现我用MyBatis的foreach标签拼批量SQL。注意Mapper接口的入参是ListUserExcelModelService public class UserImportServiceImpl implements UserImportService { Autowired private UserMapper userMapper; Override Transactional(rollbackFor Exception.class) public void batchInsertWithTransaction(ListUserExcelModel list) { if (list.isEmpty()) { return; } userMapper.batchInsert(list); } }insert idbatchInsert parameterTypelist INSERT INTO t_user (name, age, phone, create_time) VALUES foreach collectionlist itemitem separator, (#{item.name}, #{item.age}, #{item.phone}, NOW()) /foreach /insert这里事务注解加在Service方法上是生效的因为Service是Spring容器管理的Bean。调用batchInsertWithTransaction时如果中间某一行触发数据库异常这一批3000条会整体回滚。MySQL默认事务隔离级别下InnoDB执行批量insert时行的自增锁会有性能开销3000条一批通常在几百毫秒内完成不会有明显锁竞争。数据一致性在这个场景里还有一个容易被忽视的点同一份Excel被重复导入怎么办。生产环境里用户手滑点两次提交数据库就多了两份一样的数据。常见做法是在监听器里做内存去重用HashSet记录手机号或者给手机号字段建唯一索引批量insert时用INSERT IGNORE让冲突行静默跳过。两种方案对应不同业务语义前者能统计出重复行并提示用户后者更无感。我的习惯是导入前先查一次库中已有手机号集合加载到监听器里过滤这样能提前把重复行挑出来反馈给用户而不是入库时才炸。4. 把数据库数据导出为Excel文件模板填充与注解导出的边界4.1 注解导出适合固定表头模板填充适合复杂样式导出的需求比导入更五花八门。有的要求简单一个Sheet、几列数据表头固定有的要求复杂表头要合并单元格、要有公司Logo、要带汇总行。这两种场景用的技术路径完全不同。简单场景用注解导出就够了就是上一章写的EasyExcel.write(...).doWrite(dataList)。字段顺序、表头文字、列宽都能通过注解控制。但注解导出有一个硬伤很难做动态列。什么叫动态列比如导出某个时间段的销量报表列是“2024-01、2024-02、2024-03”这个月份列表是运行时才知道的注解定义不了。复杂场景用模板填充。EasyExcel的fill功能允许你准备一个.xlsx模板文件在单元格里写{.fieldName}这样的占位符然后程序往里填充数据。项目里常见的做法是运营同学先用Excel做出带格式的表头、合并单元格、Logo开发人员往模板里填数据。4.2 模板填充的完整示例从resources里加载模板模板文件放在src/main/resources/excel_template/目录下。模板里第一行写表头第二行写占位符{.name}第三行写{.phone}等程序填充后删除多余行public void exportByTemplate(ListUserExcelModel dataList, HttpServletResponse response) { // 从classpath加载模板文件 InputStream templateStream getClass().getResourceAsStream(/excel_template/user_export.xlsx); String fileName URLEncoder.encode(用户数据_ System.currentTimeMillis(), UTF-8); response.setContentType(application/vnd.openxmlformats-officedocument.spreadsheetml.sheet); response.setCharacterEncoding(utf-8); response.setHeader(Content-Disposition, attachment;filename fileName .xlsx); // 填充数据这里用FillConfig控制是否清空多余的占位行 FillConfig fillConfig FillConfig.builder().forceNewRow(true).build(); EasyExcel.write(response.getOutputStream()) .withTemplate(templateStream) .sheet() .doFill(dataList, fillConfig); }forceNewRow(true)是一个容易踩坑的参数。它的意思是如果模板里第2行是占位符第3行也是占位符数据量超过模板预制的行数时自动向下插入新行。不设置这个参数时数据量超过模板行数会直接报错或者截断输出。我一般都会显式设置防止数据条数变了模板不够用。导出到浏览器时Content-Disposition的attachment关键字决定浏览器是下载文件还是在页面内打开。很多人漏掉URLEncoder.encode这步中文文件名在部分浏览器里会乱码。后端导出的响应流不要手动关闭让EasyExcel的doFill方法自己管理流生命周期。4.3 动态列的实现List配合同步表头真正复杂的动态列导出模板填充也搞不定因为模板是静态的。这时候要放弃UserExcelModel这种固定模型类改用ListListString或者ListMapString, Object。EasyExcel的head()方法支持传入动态表头集合public void exportDynamicColumn(ListMapString, Object dataList, HttpServletResponse response) { // 第一层List是列第二层List是每个表头单元格的层级 ListListString head new ArrayList(); head.add(Arrays.asList(姓名)); // 第一列表头 head.add(Arrays.asList(手机号)); // 第二列表头 // 动态追加月份列 for (String month : monthList) { head.add(Arrays.asList(month)); } ListListObject rows new ArrayList(); for (MapString, Object data : dataList) { ListObject row new ArrayList(); row.add(data.get(name)); row.add(data.get(phone)); for (String month : monthList) { row.add(data.get(month)); } rows.add(row); } EasyExcel.write(response.getOutputStream()) .head(head) .sheet(动态报表) .doWrite(rows); }这种写法绕过了模型类没有类型安全但胜在灵活。表头支持多级比如第一列写“基本信息”第二行再写“姓名”“年龄”这在复杂财务报表里很常用。代价是单元格数据类型都是ObjectExcel里显示成什么格式取决于你塞进去的值类型——LocalDateTime会被格式化成yyyy-MM-dd HH:mm:ssBigDecimal默认保留原值字符串默认靠左。4.4 分页导出100万行数据怎么不炸内存导出的数据量一大问题就变了不是能不能导出的问题是内存够不够、数据库扛不扛得住。一次查100万行到应用内存里再写文件任何框架都救不了你。常见做法是流式查询分批写。MyBatis的Cursor配合Select注解可以做流式查询每拿到一批就写一批到输出流Mapper public interface UserMapper { Select(SELECT name, age, phone FROM t_user) CursorUserExcelModel selectAllWithCursor(); }public void exportLargeData(HttpServletResponse response) { // 设置响应头... try (CursorUserExcelModel cursor userMapper.selectAllWithCursor()) { IteratorUserExcelModel iterator cursor.iterator(); // 复用同一个WriteSheet分批写入 WriteSheet writeSheet EasyExcel.writerSheet(用户数据).build(); ExcelWriter writer EasyExcel.write(response.getOutputStream()).build(); ListUserExcelModel batch new ArrayList(1000); while (iterator.hasNext()) { batch.add(iterator.next()); if (batch.size() 1000) { writer.write(batch, writeSheet); batch.clear(); } } if (!batch.isEmpty()) { writer.write(batch, writeSheet); } writer.finish(); } }这里用ExcelWriter.write而不是doWrite是因为doWrite只能调用一次ExcelWriter可以多次调用write往同一个Sheet追加数据。Cursor流式查询要注意一条它占用数据库连接必须在使用完后关闭所以代码里用了try-with-resources。另外如果你用了MyBatis的分页插件Cursor配分页插件会冲突分页插件拦截后会把流式查询变成普通查询这个坑我踩过。5. 导入导出中的常见问题与避坑指南5.1 日期字段读出来全是数字串现象Excel里明明写着“2024-06-01”读到Java对象里变成45278这种数字或者变成2024/6/1、2024-06-01 00:00:00。原因Excel内部没有日期类型日期在底层是浮点数距离1900年1月0日的天数加单元格格式。EasyExcel解析时如果列的单元格格式是“常规”而不是“日期”它就会把底层数字读出来。这是Excel格式和Java类型系统的经典错位。解决模型类里指定日期格式强制EasyExcel按字符串格式解析public class UserExcelModel { ExcelProperty(value 出生日期, converter LocalDateTimeStringConverter.class) private LocalDateTime birthDate; }Getter public class LocalDateTimeStringConverter implements ConverterLocalDateTime { Override public ClassLocalDateTime supportJavaTypeKey() { return LocalDateTime.class; } Override public CellDataTypeEnum supportExcelTypeKey() { return CellDataTypeEnum.STRING; } Override public LocalDateTime convertToJavaData(ReadCellData? cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) { String value cellData.getStringValue(); return LocalDateTime.parse(value, DateTimeFormatter.ofPattern(yyyy-MM-dd HH:mm:ss)); } }更轻量的做法是让用户导出Excel时就把日期列设置成文本格式但业务不可控框架层面的converter是兜底方案。5.2 长数字变科学计数法现象手机号、身份证号这类超过15位的数字导入后变成1.3812345678E10导出后Excel里显示成科学计数法精度丢失。原因Excel的数字精度是15位超过15位自动转科学计数法。模型类如果用Long或Integer接收超过Long.MAX_VALUE的数值直接解析失败如果直接用String接收Excel底层存的还是浮点数读出来已经是精度丢失的值。解决源头治理。模板里把数字列设为文本格式同时在模型类上标注该列按字符串读取ExcelProperty(value 身份证号, converter StringNumberConverter.class) private String idCard;public class StringNumberConverter implements ConverterString { Override public ClassString supportJavaTypeKey() { return String.class; } Override public CellDataTypeEnum supportExcelTypeKey() { return CellDataTypeEnum.NUMBER; } Override public String convertToJavaData(ReadCellData? cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) { // 关键这里拿到的已经是BigDecimal需要去掉科学计数法 return cellData.getNumberValue().toPlainString(); } }BigDecimal.toPlainString()把科学计数法还原成普通十进制字符串保险起见再校验长度。生产环境里这一步用“文本格式字符串接收”双保险只靠代码层转换有概率拿到已经丢失精度的值。5.3 监听器里的事务注解不生效现象导入过程中某一批数据入库失败抛了异常但前几批已经成功提交了数据库里留下了一半数据。原因AnalysisEventListener是new出来的不是Spring容器管理的Bean。Spring的Transactional代理只对容器内的Bean方法生效new出来的对象上注解完全失效。这属于Spring AOP的代理机制问题很多人在监听器方法上加Transactional却发现不生效就是这个原因。解决把批量插入方法拆到独立的Spring Service里监听器只负责收集数据并调用Service。事务边界就可以精确控制到每一批。另外如果你希望“监听到第一行错误就整个文件不导入”那要让监听器把异常抛出到调用方在调用方统一开启一个大事务但大事务会持有数据库连接直到整个文件解析完大文件下不推荐。我的习惯是默认按批提交配合前端提示“第X批导入失败”保证已成功部分不浪费。5.4 批量插入报PacketTooBigException现象导入5000条数据时报com.mysql.cj.jdbc.exceptions.PacketTooBigException: Packet for query is too large。原因MySQL默认max_allowed_packet是4MB批量insert拼接的SQL超过4MB就被拒。这个问题在文本字段多、内容长的Excel导入时特别容易触发属性和MySQL配置的经典冲突。解决两个方向。调大MySQL参数SET GLOBAL max_allowed_packet 6710886464MB同时改my.cnf持久化或者把BATCH_COUNT从3000降到1000观察SQL体量。我一般先调批大小效果不够再改数据库参数因为线上数据库权限不一定允许改全局参数。改完连接串别忘了加rewriteBatchedStatementstrue否则批量插入没那么高效。5.5 导入Excel总是报“Excel 00:00:00”或者时间格式错乱现象导入时间字段时出来的数据是00:00:00或者比预期少8小时。原因时区序列化问题。EasyExcel读取日期字符串后转成Date再存到MySQL的datetime字段中间经历了JVM默认时区、JDBC连接时区、MySQL服务器时区的三次转换。SpringBoot默认spring.jackson.time-zoneGMT8如果你没配Jackson序列化Date时用的是UTC就会差8小时。解决在SpringBoot配置里固定统一时区spring: jackson: time-zone: GMT8 date-format: yyyy-MM-dd HH:mm:ss datasource: url: jdbc:mysql://localhost:3306/db?serverTimezoneAsia/ShanghaiuseLegacyDatetimeCodefalse顺带说一句连接串里的serverTimezone不配MySQL 8.x驱动会直接报错这是另一个高频问题。如果代码里用的是LocalDateTime而不是Date那不受Jackson时区配置影响但JDBC连接串的serverTimezone仍然要配。6. 内存与性能调优的几个实用技巧前面的方案能跑通业务但追求极致性能时还有几个技巧值得单独说。第一读文件优先传File而不是InputStream。同样的Excel文件EasyExcel.read(file)比EasyExcel.read(inputStream)内存占用低20%到30%。原因很简单传File时EasyExcel可以随机访问文件位置传InputStream时只能顺序读而且InputStream往往是整文件读入后包装的。我测试过200MB的xlsx传File时堆内存峰值约180MB传InputStream时能到260MB。能传路径就传路径别为了所谓的灵活传InputStream。第二同步读转异步给导入操作留出业务缓冲。SpringBoot的Controller里直接做文件解析会阻塞Tomcat线程大文件导入一次10秒Tomcat线程被占满其他接口全部排队。我在项目里的做法是上传的文件先落盘然后丢给线程池异步处理前端轮询导入状态。线程池参数用ThreadPoolTaskExecutor核心线程数设成CPU核数减1队列容量别超过200防止积压太多任务把内存耗尽。异步处理时重复提交问题一定要处理——用文件的MD5作为唯一键处理中状态加一个Redis锁防止同一个文件被用户重复提交。第三导出大数据量时别用doWrite一次性传入全量数据。前面写了ExcelWriter分批写的方式这是唯一能扛住百万级导出的写法。还有一个细节分批写的批次大小不是越大越好1000条写一次和5000条写一次在毫秒级的差异但5000条会让响应时间波动更大。我习惯1000到2000条一批网络传输和数据库查询都比较平滑。最后校验逻辑别放在监听器里做复杂业务查询。比如每一行都要查一次用户表验证手机号是否存在20万行就是20万次SQL数据库连接池直接被打满。正确做法是预先查一次全部手机号放到一个Set里监听器里只做内存判断。这类批量数据的内存级校验能把导入耗时从分钟级降到秒级。这些技巧没有一个是我拍脑袋想出来的全是从一次次线上报警和压测报告里总结来的。踩坑踩得多了就养成一个习惯写Excel导入导出代码之前先问自己三个问题——这个文件最大会有多大、这批数据要保证什么一致性级别、失败以后用户怎么知道。把这三个问题想清楚再动手比事后调参数实在得多。希望帮到你。本文还有配套的精品资源点击获取