接手这种“Excel导入数据库”的需求时我第一反应永远是先别急着写代码。很多开发同学拿到这种需求上来就加POI依赖写个循环逐行读再拼接个INSERT语句往库里怼。看似很快但等会儿测试一跑内存爆了、数据错位、格式拉胯返工两次就老实了。这篇就围绕“在Spring Boot项目里读取Excel并存入数据库”这件事完整走一遍从选型到落地的实操路径把我自己踩过的坑、调整过的方案、梳理过的细节都摆出来。我之前接手过一个真实的业务模块运营同事每天导出一份Excel报表里面是商品基础信息和渠道价格大概三五千行。我需要把这份表解析出来通过接口接收文件校验后写入MySQL。整个流程看起来不复杂但涉及文件上传、解析策略、监听回调、批量写入、异常兜底、日期格式兼容等一堆细节。这次就以这个场景为主线把从零到一的实现步骤拆开讲清楚。这篇适合的人群刚接触Spring Boot、用过但没深入做过数据导入模块的后端同学以及想了解EasyExcel和POI选型差异、想把数据导入做得更稳的初级/中级开发。不是为了炫技就是一套能落地、能应对真实业务压力的方案。1. 方案选型与核心设计思路1.1 解析Excel为什么最后选了EasyExcel而不是POI但凡搜过Java解析Excel的方案基本绕不开两大阵营Apache POI和阿里的EasyExcel。POI是底层操作库功能全面但对内存极不友好。它的DOM解析模式会把整个工作簿的每个Sheet、每行、每格全量加载到内存生成一个Excel对象树一个10万行的Excel文件能轻松吃掉1G以上的JVM堆内存。项目里如果解析量大很容易引发频繁Full GC甚至OOM。EasyExcel则采用SAX模式逐行读取。它不会一次性把所有行对象都加载到内存而是边扫描边回调按指定行数攒一批就触发一次监听方法。这样无论Excel有多少行内存占用都能压在几十MB以内。这一点在数据量上到几万行时优势是决定性的。我实际对比过两个方案的代码量POI处理要你手动管理Sheet、Row、Cell的遍历还要自己判断单元格类型字符串、数字、日期、公式处理空值、合并单元格的代码极其啰嗦。EasyExcel只需要定义好实体类写一个监听器再把校验和入库逻辑挂在监听器回调里核心代码能少写一大半。所以我的结论很明确常规业务场景下EasyExcel就是最稳的起点。POI更适合做那种需要精确控制Excel格式、要生成复杂模板、要操作图表和样式的高级场景。1.2 架构分层清空Controller里的“野代码”很多人习惯把上传、解析、校验、入库全堆在Controller里写一个方法几十行两周后自己都看不懂。我在这个项目里做了明确的分层Controller层只负责接收MultipartFile和返回结果Service层负责校验、解析、数据入库的事务编排Listener层EasyExcel的监听器只做行数据解析和逐行收集Mapper/DAO层只做数据库写入。这样做的核心原因是解析逻辑和业务校验必须解耦。如果监听器里直接调UserService或者OrderService写库一旦校验逻辑调整或者事务边界需要变化你会改得很难受。把监听器设计成纯数据转换器只做“Excel行 - 业务DTO”的转换入库动作交由Service统一处理等于给后续维护留了一条活路。1.3 技术栈底座我用的是这些以这个商品价格导入项目为例我选的技术组合如下全部是常见主流组件没有冷门工具各位可以直接照搬Java 8实际用了JDK 17下面配置是兼容的Spring Boot 2.7.xMyBatis-Plus 3.5.xMySQL 8.x配InnoDButf8mb4字符集EasyExcel 3.xLombok纯为减少样板代码需要说明一下MyBatis-Plus不是必须的JdbcTemplate甚至纯MyBatis都完全可行。我选择MyBatis-Plus主要是为了用saveBatch批量插入的便利方法减少手写SQL标签的繁琐。2. 环境准备与核心依赖配置2.1 Maven依赖少了两个包容易踩坑先上pom.xml里的核心依赖这是我自己跑通后精简下来的版本别缺缺了会出各种诡异问题。!-- Excel解析核心 -- dependency groupIdcom.alibaba/groupId artifactIdeasyexcel/artifactId version3.3.2/version /dependency !-- Web上传支持 -- dependency groupIdorg.springframework.boot/groupId artifactIdspring-boot-starter-web/artifactId /dependency !-- MyBatis-Plus -- dependency groupIdcom.baomidou/groupId artifactIdmybatis-plus-boot-starter/artifactId version3.5.3/version /dependency !-- MySQL驱动 -- dependency groupIdcom.mysql/groupId artifactIdmysql-connector-java/artifactId version8.0.33/version /dependency !-- Lombok -- dependency groupIdorg.projectlombok/groupId artifactIdlombok/artifactId optionaltrue/optional /dependency这里要特别提醒一句EasyExcel 3.x依赖的POI版本是5.1.2左右如果你项目里还引了其他版本的POI一定要检查版本冲突。我遇到过因为项目里某个老模块引了POI 3.17导致运行时抛NoSuchMethodError的情况。解决办法很简单要么统一升级到5.x要么在用EasyExcel的模块里排除冲突的传递依赖。2.2 数据库表结构与初始化SQL假设这个项目要把每行商品数据写入一张商品价格表表结构大概长这样真实项目里我一般会再加create_time、update_time、版本号等基础审计字段。CREATE TABLE product_price ( id bigint(20) NOT NULL AUTO_INCREMENT, product_code varchar(64) NOT NULL COMMENT 商品编码, product_name varchar(128) NOT NULL COMMENT 商品名称, channel_type varchar(32) DEFAULT NULL COMMENT 渠道类型, price decimal(10,2) NOT NULL COMMENT 价格, sale_status tinyint(4) NOT NULL DEFAULT 1 COMMENT 销售状态 1在售 0停售, import_date date DEFAULT NULL COMMENT 数据日期, create_time datetime DEFAULT CURRENT_TIMESTAMP, update_time datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_product_channel (product_code,channel_type) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT商品价格导入表;加唯一键这个动作非常重要。真实场景中运营同学很可能重复上传同一份Excel如果用代码层去判断是否存在再决定插入还是更新性能会非常拉胯。直接在数据库上建唯一索引配合INSERT ... ON DUPLICATE KEY UPDATE或者MyBatis-Plus的saveOrUpdateBatch才是高性价比的方案这个后面代码部分会具体说。2.3 核心配置这些参数建议写上application.yml里除了常规的数据源信息我还会额外加上下面这些配置前两个是生产环境里实打实遇到过的坑spring: servlet: multipart: max-file-size: 10MB max-request-size: 10MB datasource: url: jdbc:mysql://localhost:3306/business_db?useUnicodetruecharacterEncodingutf8useSSLfalseserverTimezoneAsia/ShanghairewriteBatchedStatementstrue username: root password: your_password hikari: maximum-pool-size: 20 minimum-idle: 5 mybatis-plus: configuration: map-underscore-to-camel-case: true global-config: db-config: id-type: auto重点说rewriteBatchedStatementstrue这一项。很多同学批量插入慢到怀疑人生问题就出在没开这个参数。MySQL对普通的批量INSERT语句如果不做重写每条记录都是独立执行性能损失极大。开启之后MySQL驱动会把多条INSERT合并成一条多VALUES的单语句批量写入的速度能提升好几倍尤其数据量大时感知极其明显。3. 核心代码实现与关键细节3.1 定义实体和业务模型这一步没什么花哨的就是把Excel里的每一行映射成一个Java对象。需要注意EasyExcel用ExcelProperty注解指定列对应关系这个注解里的value值必须和Excel表头文字完全一致index属性则需要从0开始指定列序号。两种方式二选一我更推荐用index原因很简单运营同事经常调表头文案按文字匹配极易闪崩按位置匹配更抗干扰。Data public class ProductPriceExcelModel { ExcelProperty(index 0) private String productCode; ExcelProperty(index 1) private String productName; ExcelProperty(index 2) private String channelType; ExcelProperty(index 3) private BigDecimal price; ExcelProperty(index 4) private String saleStatus; ExcelProperty(index 5) private String importDate; }写到这里插一句Excel里的日期列不要直接映射成Date类型再指望EasyExcel自动转。EasyExcel的日期转换依赖单元格格式如果Excel里那个格子是文本型的日期字符串“2024-05-11”直接映射Date类型会抛转换异常。稳妥做法是先全都映射成String在监听器里统一用DateTimeFormatter转一遍控制权全握在自己手里。3.2 写一个Listener但不让它碰数据库EasyExcel的ReadListener是解析的核心钩子。它会随着文件的读取逐行被回调。我的设计原则监听器只负责把Excel行转成业务实体对象收集进一个list每攒够1000条就调用一次Service的批量入库方法然后清空list。public class ProductPriceExcelListener extends AnalysisEventListenerProductPriceExcelModel { private static final int BATCH_COUNT 1000; private final ListProductPriceExcelModel cachedList new ArrayList(); private final ImportService importService; public ProductPriceExcelListener(ImportService importService) { this.importService importService; } Override public void invoke(ProductPriceExcelModel rowData, AnalysisContext context) { cachedList.add(rowData); if (cachedList.size() BATCH_COUNT) { importService.saveBatchFromExcel(cachedList); cachedList.clear(); } } Override public void doAfterAllAnalysed(AnalysisContext context) { if (!cachedList.isEmpty()) { importService.saveBatchFromExcel(cachedList); cachedList.clear(); } } }为什么每攒1000条就要入库而不是最后统一存因为如果你一次解析5万行全部攒在list里等于变相把Excel加载进了内存内存优势就归零了。批量1000条入库list随存随清上限可控。当然BATCH_COUNT也可以根据你的机器内存放宽到2000、3000但没必要过度追求大1000是比较稳的甜点值。还有一个细节doAfterAllAnalysed方法一定要记得处理剩余数据比如Excel总共998行前面攒了999行没有到1000最后9条就留在list里了。不加这段兜底你会莫名其妙丢数据。3.3 批量入库saveOrUpdateBatch还是手写SQL到了入库这步就有分叉了。MyBatis-Plus自带的IService.saveOrUpdateBatch通过逐条判断主键/唯一键是否存在来执行update或insert写法极简但内部是逐条判断数据量大时性能一般。在我的这个场景里有唯一键product_codechannel_type我更倾向于放弃MyBatis-Plus的saveOrUpdateBatch改用自定义SQL配合ON DUPLICATE KEY UPDATE。Mapper里写一个方法void batchUpsertPriceData(Param(list) ListProductPriceExcelModel list);对应的XMLinsert idbatchUpsertPriceData INSERT INTO product_price ( product_code, product_name, channel_type, price, sale_status, import_date, create_time, update_time ) VALUES foreach collectionlist itemitem separator, ( #{item.productCode}, #{item.productName}, #{item.channelType}, #{item.price}, #{item.saleStatus}, #{item.importDate}, NOW(), NOW() ) /foreach ON DUPLICATE KEY UPDATE product_name VALUES(product_name), price VALUES(price), sale_status VALUES(sale_status), import_date VALUES(import_date), update_time NOW() /insert这里有个隐患如果list特别大比如一次性传5万条MySQL的max_allowed_packet可能扛不住这么大的单条SQL。所以我前面的监听器每满1000条就入库一次即天然控制了这个SQL的长度。每批1000条SQL体积稳定可控。3.4 Controller接口文件上传就这么写接口层面没什么玄机就是一个接收MultipartFile的POST端点。RestController RequestMapping(/api/import) public class ImportFileController { PostMapping(/product-price) public ResultString importProductPrice(RequestParam(file) MultipartFile file) { try { importService.importFromExcel(file.getInputStream()); return Result.success(导入成功); } catch (Exception e) { log.error(商品价格导入异常, e); return Result.fail(导入失败 e.getMessage()); } } }但底层Service里就要多做几层检查了。文件是否为空的判断、文件后缀是否为.xlsx的校验、InputStream的try-with-resources都放在这里。还有一点很多人会忽略解析前一定要确认Excel里有没有数据如果Sheet是空的EasyExcel不会抛异常你会默默入库一个空list浪费一次调用。读完整个文件后我还一口气把解析结果统计总行数、成功行数、失败行数封装返回这样前端可以给运营一个明确的反馈而不是笼统的“成功”。这个统计逻辑放在Service里通过监听器暴露的计数属性获取。3.5 事务和异常这层不做会出事批量插入如果中途失败到底要不要回滚我的方案是同一批次内部完全事务化批次之间独立提交。什么意思第1批1000条入库失败就回滚这1000条但已经成功入库的第2批、第3批不会跟着回滚。这样有些同学觉得不严谨但如果业务上确实存在脏数据混在文件里全表回滚会导致“一条坏数据拖死全文件”。更合理的做法是先把数据解析出来做一次全量校验有问题的行收集成错误清单没问题的行统一入库。如果这次需求对数据质量要求极高那就不应该入库时逐批提交而应该在解析阶段把所有异常行都拦截下来。事务边界设在Service的saveBatchFromExcel方法上加上Transactional注意是org.springframework.transaction.annotation.Transactional别引入错了。Transactional(rollbackFor Exception.class) public void saveBatchFromExcel(ListProductPriceExcelModel batchList) { productPriceMapper.batchUpsertPriceData(batchList); }3.6 数据校验别让Excel脏数据直接进库真实环境里的Excel从来不会像模板那么干净。我在解析阶段写了一个ValidateHelper专门做这几件事非空校验必填列如果为空直接计入错误行留待人工处理 数据类型校验价格列如果填了非数字解析成BigDecimal失败会立即抛异常 枚举校验销售状态列的值应该是“在售/停售”遇到其他值转成对应数字时直接标记异常 日期格式归一化统一处理“2024/5/11”“2024-05-11”“20240511”几种常见书写。校验失败的读取逻辑不直接break整个流程而是记录到错误列表里继续读下一行。这样运营拿到的反馈里既有“成功导入多少条”也有“失败多少条以及失败原因”这个导入体验比闷头全量成功要专业得多。4. 完整实测过程从Excel到数据库的现场记录4.1 准备一份用于测试的Excel文件我准备了一份测试文件包含5列分别是商品编码、商品名称、渠道类型、价格、销售状态、数据日期共6列混进了一行特殊数据用于看异常处理效果。测试数据大致如下A001, 手机壳, 线上, 29.90, 在售, 2024-05-11A002, 钢化膜, 线上, 19.90, 在售, 2024-05-11A003, 数据线, 线下, 空值, 在售, 2024-05-11A004, 充电器, 线下, 79.00, 停售, 2024/05/11注意第三行的价格列是空值用于观察校验逻辑的走向。4.2 启动项目发送上传请求本地启动Spring Boot项目后我用接口调试工具模拟了文件上传请求发送multipart/form-data文件字段名和Controller里的RequestParam(file)一致。核心代码跑起来后的日志输出大致如下开始解析文件: product_price_202405.xlsx 解析到第1行: A001 解析到第2行: A002 解析到第3行时发现异常: 第3行价格为空值跳过 解析到第4行: A004 文件解析完成: 总行数4, 成功3, 失败1 批次入库完成: 成功写入3条4.3 核对数据库结果登录MySQL查一下product_price表SELECT product_code, product_name, price, sale_status, import_date FROM product_price;查询结果显示三条数据正常写入动态的日期格式也顺利归一化成了日期类型。空行被拦截没有产生脏数据。4.4 这个流程暴露出来的问题第一次完整跑这个流程时我最先翻车的是Date列的映射。测试Excel里“2024/05/11”这种带斜杠的日期EasyExcel映射成String后是我那个转化器没处理好的格式它直接抛了一个DateTimeParseException。后来在转换器里追加了多格式匹配分支问题才消除。这也正是我前面反复强调“日期列一律用String接收程序判断”的原因。Excel里的日期格式不受控指望EasyExcel自动搞定一切是不现实的。5. 常见问题与调优实录5.1 问题速查表症状根因解决办法解析后数据量莫名其妙少了几条没有在doAfterAllAnalysed中处理剩余缓存补上最后的flush逻辑大批量导入时内存持续走高监听器一次性缓存所有行BATCH_COUNT设为1000随存随清日期列报类型转换异常Excel里日期是文本型表头映射失效实体类统一用String接收手动格式化批量插入极慢数据库URL缺少rewriteBatchedStatementsURL追加rewriteBatchedStatementstrue文件上传被Spring拦截multipart.max-file-size未配置或太小按业务情况调大上限启动报POI NoSuchMethodErroreasyexcel依赖的POI版本和项目冲突排除旧POI统一升级到5.x大量数据写入造成主从延迟单批数据量偏大、SQL过长单批控制在1000条左右分批提交重复上传导致唯一键冲突没有处理upsert语义用INSERT ... ON DUPLICATE KEY UPDATE5.2 踩坑项EasyExcel 3.x对xls和xlsx的兼容性EasyExcel对老格式xls的支持没有xlsx那么完善。如果你的业务里还会收到.xls格式的Excel务必在代码里做后缀判断或者统一要求上传.csv和.xlsx两种格式。旧版.xls文件里的日期格式处理得更差有些单元格甚至会把日期串成数字序列。真遇到这类文件我建议还是走转换工具先转成xlsx再导入别在代码层面硬耗。5.3 性能调优的三个抓手如果你导入量在几万行这个级别下面三处优化是最立竿见影的MySQL连接串加rewriteBatchedStatementstrue这一步对批量写入性能的影响最大甚至能快出数倍。 单批数据量控制在1000到2000宁少勿多避免单条SQL过大触达max_allowed_packet阈值。 使用异步导入。文件上传接口里不要同步解析入库先把文件保存到临时目录返回“导入任务已提交”再通过线程池或者消息队列异步执行解析入库。这样用户不需要在页面上等白屏也不怕接口超时。异步导入这块我曾经吃过一次亏上线第一周运营传了个10MB的Excel后台同步解析入库耗时接近20秒前端等不住直接点了两遍。后来改成异步任务再加一个简易进度查询接口这个体验问题才彻底解决。5.4 关于文件安全检查Controller里接收MultipartFile后不要急着解析。先做两件事一是拿文件大小判断是否超限二是校验文件后缀。如果有安全上的硬要求可以进一步用文件头魔数判断真实文件类型防止篡改后缀上传恶意文件。当然一般业务后台系统里并不会严格到魔数级别但至少后缀和大小这层要守住。6. 额外复盘如果从零重新做我会改进什么第一点模板先行。给运营提供统一模板模板里预置表头、数据格式下拉选项、并锁定列宽。源头干净了后端解析工作能减少一半。很多导入模块的维护成本不在代码而在上游五花八门的Excel格式。第二点解析和业务入库要分层更彻底。现在的监听器虽然不直接操作Mapper但Service里还是直接把模型数据映射到了实体表。如果表格字段和实体字段出入较大中间加一层DTO转换会更稳。第三点给导入过程加个任务状态记录。用一张import_task表记录导入任务的批次号、处理状态、成功总数、失败总数、失败文件的下载地址。这样既能支持异步化处理又能给用户一个完整的“导入报告”。我这次没做任务表只是靠内存计数返回一次性结果后续如果业务量变大一定会往这个方向演进。第四点数据校验可以再往前走一步。现在是逐行校验一次性把所有异常行全部收集出来给用户下载。而更顺滑的体验是支持用户在线修改这些异常行再重新提交。不过这个改动涉及前端交互属于二期范畴了。最后再分享一个小细节在开发阶段我习惯在解析完一个Excel文件后把解析出的前20行数据用日志打出来人工对一遍“机器看到的内容”和“Excel里的真实内容”。人眼扫一眼比任何单元测试都来得快尤其是日期、金额这类容易出格式问题的字段。这个习惯帮我至少提前发现了三次格式匹配错误算是性价比最高的一步检查。