1. 为什么“数据库到Excel”这个需求比想象中更棘手但凡做过企业级后台系统的人几乎都绕不开一个需求把MySQL里的数据导出成Excel给业务方看或者把业务方填好的Excel导回数据库。乍一听这事简单得不行——查出来、写进去两个动作而已。但我带过的项目里这个功能翻车的次数远比想象中多。有人导出十万行直接把内存打爆有人导入时因为一个空单元格抛了空指针还有人被日期格式折磨到怀疑人生。这个需求的核心难点从来不在“能不能跑通”而在于数据量、格式兼容、内存控制、事务一致性这四个维度的平衡。小数据量下随便怎么写都能work一旦数据量上去、业务方给的Excel五花八门问题就全冒出来了。所以这篇内容我打算把“MySQL到Excel的导出与导入”这件事从头到尾拆开讲既讲清楚技术选型的逻辑也把我在实际项目里踩过的坑和总结的套路一并交出来。适合阅读的人群很明确做Java后端、需要处理数据导入导出功能的开发者尤其是那些正在被POI内存问题或者Excel格式问题困扰的人。如果你只是偶尔导个几百行数据看完选型部分就够用了如果你要处理几十万行甚至更多那内存优化和流式处理那几节一定要仔细看。先说结论性的选型判断后面再展开导出场景优先考虑EasyExcel或POI的SXSSF模式导入场景优先考虑EasyExcel的监听器模式。原因很简单Apache POI的XSSF全量加载模式在处理大文件时内存占用是灾难级的而EasyExcel本质上是对POI的封装在读写两端都做了流式优化API也更友好。但EasyExcel不是银弹某些复杂格式场景还是得回到原生POI。2. 导出方案选型POI、EasyExcel到底怎么选2.1 三种主流方案的底层差异Java生态里操作Excel绕不开Apache POI这个基础库。POI提供了两套核心实现HSSF用于处理.xls格式Excel 2003最大65536行XSSF用于处理.xlsx格式Excel 2007理论上百万行。这两套都是把整个工作簿加载到内存里操作的XSSF尤其吃内存一个十万行的xlsx文件JVM堆内存占用轻松上到几百MB甚至GB级别。为了解决内存问题POI后来又提供了SXSSFStreaming Usermodel API它的思路是只在内存里保留固定数量的行默认100行超出的部分刷写到临时文件。这样内存占用就变得可控了。但SXSSF有个限制它只能写不能读而且不支持对已写出的行做修改。EasyExcel是阿里开源的封装库底层基于POI的SXSSF做写、基于SAX解析做读。它的核心卖点就是低内存和注解式API。你定义一个Java类字段上打ExcelProperty注解一行代码就能完成读写。对于绝大多数业务场景这个抽象层级刚刚好。方案内存占用API友好度格式支持适用场景HSSF高中.xls老系统兼容XSSF极高中.xlsx全功能小数据量、需复杂格式SXSSF低较低.xlsx写为主大数据量导出EasyExcel低高.xlsx为主通用导入导出2.2 我的实际选型逻辑我自己的判断标准是这样的如果导出数据量稳定在五千行以内且需要复杂的单元格样式合并单元格、条件格式、图表直接用XSSF省心。这个量级下内存完全不是问题POI的完整功能能让你少写很多代码。如果数据量可能上万甚至几十万且格式要求不复杂就是表头加数据行用EasyExcel。它的写入性能很好内存占用稳定在几十MB不会因为数据量增长而线性膨胀。如果数据量巨大但格式极其简单且你不想引入EasyExcel依赖用SXSSF。它是POI原生的流式方案少一层封装少一层开销。有个细节很多人忽略EasyExcel在写的时候如果数据量特别大建议手动设置writeCache相关的参数或者分批写入。默认配置下它已经比XSSF好很多但极端场景还是需要调优。2.3 依赖引入的注意事项用Maven的话EasyExcel的坐标要注意版本。较新的版本已经改名为easyexcel-core老版本是easyexcel。如果你项目里同时有POI的其他依赖要小心版本冲突——EasyExcel内部依赖了特定版本的POI版本不匹配可能导致NoSuchMethodError。dependency groupIdcom.alibaba/groupId artifactIdeasyexcel/artifactId version3.3.2/version /dependency提示引入EasyExcel后建议用mvn dependency:tree检查一下POI的版本确保没有多个版本共存。我遇到过因为POI版本冲突导致导出文件打不开的情况排查了大半天。3. 导出实战从Mapper查询到文件落地的完整链路3.1 数据查询层的分页与流式读取导出功能的第一步是查数据。这里有个关键决策一次性查出来还是分页查。如果数据量在几万行以内一次性查出来问题不大MyBatis的selectList直接返回。但如果数据量很大一次性查出来会同时占用数据库连接和JVM内存风险很高。我的做法是分页查询配合流式写入。比如每批查5000条查完一批就写一批到Excel写完再查下一批。这样内存里始终只有一批数据。MyBatis配合RowBounds或者手写limit分页都可以但要注意排序字段必须稳定否则分页可能出现重复或遗漏。int pageSize 5000; int pageNum 0; ListExportData batch; do { batch dataMapper.selectByPage(pageNum * pageSize, pageSize); // 写入Excel excelWriter.write(batch, writeSheet); pageNum; } while (batch.size() pageSize);这段代码里while的条件是batch.size() pageSize意思是如果最后一批不足一页说明数据取完了。这个判断逻辑比查总数再算页数要高效省了一次count查询。3.2 用EasyExcel写出的标准姿势EasyExcel的写出核心是ExcelWriter和WriteSheet两个对象。ExcelWriter负责管理整个写出过程WriteSheet定义写到哪个sheet、用什么表头。下面是一个完整的导出方法骨架public void exportData(HttpServletResponse response) throws IOException { response.setContentType(application/vnd.openxmlformats-officedocument.spreadsheetml.sheet); response.setCharacterEncoding(utf-8); String fileName URLEncoder.encode(导出数据, UTF-8); response.setHeader(Content-Disposition, attachment;filename*utf-8 fileName .xlsx); ExcelWriter excelWriter EasyExcel.write(response.getOutputStream(), ExportData.class).build(); WriteSheet writeSheet EasyExcel.writerSheet(数据).build(); int pageSize 5000; int pageNum 0; ListExportData batch; do { batch dataMapper.selectByPage(pageNum * pageSize, pageSize); excelWriter.write(batch, writeSheet); pageNum; } while (batch.size() pageSize); excelWriter.finish(); }这里有几个容易出错的点。第一response的Content-Type必须设置正确xlsx对应的是application/vnd.openxmlformats-officedocument.spreadsheetml.sheet写错了浏览器可能不识别。第二文件名要做URL编码否则中文文件名在某些浏览器下会乱码。第三excelWriter.finish()必须调用否则数据可能没有完全刷写到输出流。3.3 表头与字段的映射控制EasyExcel通过ExcelProperty注解来建立Java字段和Excel列的映射关系。注解的value属性指定表头名称index属性指定列顺序。如果不指定index则按字段声明顺序排列。public class ExportData { ExcelProperty(value 用户编号, index 0) private Long userId; ExcelProperty(value 用户名称, index 1) private String userName; ExcelProperty(value 注册时间, index 2) DateTimeFormat(yyyy-MM-dd HH:mm:ss) private Date registerTime; ExcelProperty(value 账户余额, index 3) NumberFormat(#,##0.00) private BigDecimal balance; }日期和数字的格式化用DateTimeFormat和NumberFormat注解就能搞定不需要手动转字符串。但要注意DateTimeFormat只对Date和LocalDateTime等类型生效如果你字段本身是String它不会做任何转换。注意如果某个字段不想导出到Excel用ExcelIgnore注解标记即可。如果表头需要多级联动比如“基本信息-姓名”这种ExcelProperty的value可以传数组但多级表头的处理在导入时会复杂很多后面导入部分会细说。3.4 大数据量导出的内存调优即便用了EasyExcel如果单批数据量设置得太大或者Java对象本身很重内存还是可能出问题。我的经验是每批5000到10000条比较合适太小了数据库交互次数多太大了内存压力大。另外ExcelWriter在写出过程中会占用临时文件空间。SXSSF默认在系统临时目录创建临时文件如果导出量特别大要确保临时目录有足够空间。可以通过EasyExcel.write().inMemory(false)来强制使用临时文件模式默认就是false但显式设置更保险。还有一个容易被忽略的点导出时如果某个字段为nullEasyExcel默认会写空单元格。如果业务方希望null显示为“-”或者空字符串需要在导出前做数据清洗或者自定义转换器。4. 导入实战把Excel数据安全地写回MySQL4.1 导入的核心风险与应对策略导入比导出更容易出问题因为数据来源不可控。业务方给的Excel可能列顺序不对、单元格类型不对、有空行、有合并单元格、有公式。如果代码不够健壮一个格式问题就能让整个导入失败。我的应对策略是三层校验第一层在读取时做类型校验第二层在业务逻辑里做数据校验第三层在写库时做约束校验。任何一层发现问题都要记录具体是哪一行哪一列出错方便业务方修正。EasyExcel的监听器模式天然适合这个场景。你实现一个ReadListener每读一行数据就回调一次invoke方法你可以在里面做校验、转换、收集。读完之后回调doAfterAllAnalysed在里面做批量入库。4.2 监听器模式的完整实现先定义导入的数据模型和导出类似用ExcelProperty标注字段映射public class ImportData { ExcelProperty(用户名称) private String userName; ExcelProperty(手机号码) private String phone; ExcelProperty(注册时间) private String registerTimeStr; }然后写监听器。这里的关键是不要每读一行就写一次数据库那样性能极差。正确的做法是在监听器里累积一批数据达到阈值后批量插入最后在doAfterAllAnalysed里把剩余的数据插入。public class DataImportListener extends AnalysisEventListenerImportData { private static final int BATCH_SIZE 1000; private ListImportData cache new ArrayList(BATCH_SIZE); private DataMapper dataMapper; private ListString errorMessages new ArrayList(); public DataImportListener(DataMapper dataMapper) { this.dataMapper dataMapper; } Override public void invoke(ImportData data, AnalysisContext context) { int rowIndex context.readRowHolder().getRowIndex(); // 校验逻辑 if (data.getUserName() null || data.getUserName().trim().isEmpty()) { errorMessages.add(第 (rowIndex 1) 行用户名称为空); return; } cache.add(data); if (cache.size() BATCH_SIZE) { saveBatch(); } } private void saveBatch() { if (!cache.isEmpty()) { dataMapper.batchInsert(cache); cache.clear(); } } Override public void doAfterAllAnalysed(AnalysisContext context) { saveBatch(); } public ListString getErrorMessages() { return errorMessages; } }调用的时候DataImportListener listener new DataImportListener(dataMapper); EasyExcel.read(inputStream, ImportData.class, listener).sheet().doRead(); if (!listener.getErrorMessages().isEmpty()) { // 返回错误信息给前端 }4.3 日期和数字格式的坑导入时最头疼的就是日期。业务方在Excel里填的日期可能是2024-01-15也可能是2024/1/15还可能是Excel的日期序列号比如45306。如果你用String接收拿到的是格式化后的文本如果你用Date接收EasyExcel会尝试转换但格式不匹配就会抛异常。我的建议是用String接收然后在业务逻辑里自己解析。这样你能控制解析逻辑也能给出更友好的错误提示。解析的时候用DateTimeFormatter配合多种模式尝试private LocalDate parseDate(String dateStr) { String[] patterns {yyyy-MM-dd, yyyy/MM/dd, yyyy.MM.dd}; for (String pattern : patterns) { try { return LocalDate.parse(dateStr, DateTimeFormatter.ofPattern(pattern)); } catch (DateTimeParseException ignored) { } } throw new IllegalArgumentException(无法解析的日期格式 dateStr); }数字也是类似的问题。Excel里的数字可能带千分位逗号可能是科学计数法可能是文本格式的数字。用String接收再自己转BigDecimal是最稳妥的。4.4 导入的事务与幂等性导入操作通常需要事务保证。如果一批数据插入到一半失败了应该整体回滚。但这里有个矛盾如果数据量很大一个长事务会占用大量数据库资源还可能锁表。我的做法是分批事务。每批1000条用一个独立事务失败了只回滚这一批前面的批次已经入库。然后在返回结果里告诉业务方哪些行成功了、哪些行失败了。这样既保证了性能又给了业务方明确的反馈。幂等性方面如果同一个Excel被重复导入应该怎么处理常见方案有三种忽略重复根据唯一键判断存在就跳过、覆盖更新存在就update、全部插入允许重复。具体用哪种取决于业务场景但一定要在导入前和业务方确认清楚。5. 那些年我踩过的坑与排查思路5.1 内存溢出从现象到根因的完整排查有一次线上导出功能突然报OOM日志里是java.lang.OutOfMemoryError: Java heap space。当时的导出量大概是八万行用的是XSSF。排查过程是这样的第一步看堆转储文件发现XSSFWorkbook对象占用了超过1.5GB内存。这证实了是Excel对象本身的问题不是数据查询的问题。第二步确认代码逻辑发现确实是new XSSFWorkbook()然后逐行createRow。这种写法在八万行时内存占用是灾难性的。第三步改成EasyExcel的流式写出内存占用降到50MB左右问题解决。这个坑给我的教训是不要用XSSF处理超过一万行的数据。哪怕你觉得“应该没问题”内存增长往往是非线性的临界点很难预测。5.2 文件打不开一个容易被忽略的流关闭问题还有一次导出的文件下载下来后Excel提示“文件格式无效”。排查后发现是response.getOutputStream()被提前关闭了。原因是代码里用了try-with-resources在excelWriter.finish()之前就关闭了输出流。正确的做法是让EasyExcel自己管理输出流的关闭你只需要调用finish()。如果用了try-with-resources要确保finish()在流关闭之前执行。或者更简单不要用try-with-resources包裹输出流让框架处理。5.3 导入时的空行与合并单元格业务方给的Excel经常有这种情况数据中间夹着空行或者表头是合并单元格。空行会导致EasyExcel读到一个所有字段都为null的对象如果你不做判断直接入库就会插入脏数据。处理空行的逻辑很简单在invoke方法里判断如果所有字段都为null直接return。但要注意有些行可能只有部分字段为null这种不能当空行处理。合并单元格更麻烦。EasyExcel读取合并单元格时只有第一个单元格有值其余为null。如果你的表头是合并的需要用headRowNumber指定表头占几行并且用ExcelProperty的数组形式来映射多级表头。这块建议在导入模板里就避免使用合并单元格从源头减少问题。5.4 并发导出导致的临时文件冲突SXSSF和EasyExcel在写出时会在临时目录创建文件。如果多个用户同时导出临时文件可能冲突。虽然框架一般会生成唯一文件名但在某些配置下还是可能出问题。我的做法是为每次导出创建独立的临时目录通过EasyExcel.write().withTempFileDir()指定。导出完成后删除临时目录。这样即使并发量很大也不会互相干扰。6. 从能用到好用几个提升体验的细节6.1 导出进度反馈大数据量导出时用户点完按钮后可能等几十秒甚至几分钟期间页面没有任何反馈体验很差。我的做法是用异步任务加进度查询。导出请求提交后立即返回一个任务ID后台异步执行导出前端轮询进度。进度信息可以存在内存缓存里key是任务IDvalue是已处理行数和总行数。EasyExcel的监听器里可以拿到当前读取的行号导出时虽然没有直接的回调但你可以在每批写入后更新进度。这样用户能看到“已导出5000/50000行”这样的提示心里有底。6.2 导入模板的下载导入功能最好配一个模板下载。模板里预置好表头和填写说明业务方按模板填能避免大量格式问题。模板本身可以用EasyExcel生成写一个只有表头没有数据的Excel即可。填写说明可以写在单独的sheet里或者用批注的形式加在表头单元格上。6.3 错误数据的回显导入失败时如果只告诉用户“第5行数据有误”用户还得自己去Excel里找第5行。更好的做法是把错误行号和错误原因一起返回并且生成一个带错误标记的Excel供下载。错误Excel里保留原始数据在最后一列加上错误原因。这样用户改完再传一次就行。这个功能实现起来不复杂在监听器里收集错误行数据导入结束后用EasyExcel把错误数据写到一个新文件里返回。用户体验会好很多。6.4 字段转换器的复用导出和导入经常需要做字段转换比如性别字段在数据库里存的是0和1在Excel里要显示“男”和“女”。EasyExcel提供了Converter接口你可以自定义转换器在导出时把0转成“男”导入时把“男”转回0。public class GenderConverter implements ConverterInteger { Override public ClassInteger supportJavaTypeKey() { return Integer.class; } Override public CellDataTypeEnum supportExcelTypeKey() { return CellDataTypeEnum.STRING; } Override public Integer convertToJavaData(ReadConverterContext? context) { String value context.getReadCellData().getStringValue(); return 男.equals(value) ? 1 : 0; } Override public WriteCellData? convertToExcelData(WriteConverterContextInteger context) { return new WriteCellData(context.getValue() 1 ? 男 : 女); } }转换器用ExcelProperty(converter GenderConverter.class)标注在字段上即可。这样导出导入的转换逻辑就统一了不用在业务代码里到处写if-else。7. 一些关于性能和稳定的个人体会做了这么多导入导出功能我最大的体会是这个功能的复杂度不在于技术本身而在于对边界情况的处理。正常流程谁都能写出来但真正区分水平的是异常情况下的表现——数据量大了会不会崩、格式不对会不会给出清晰提示、并发高了会不会互相干扰。性能方面我实测下来的数据是EasyExcel导出十万行数据内存占用稳定在80MB左右耗时约15秒取决于数据库查询速度。导入十万行数据批量插入每批1000条总耗时约20秒。这个性能对于绝大多数业务场景都够用了。稳定性方面我建议所有导入操作都要有日志记录记录谁在什么时间导入了什么文件、成功多少条、失败多少条。出了问题能追溯。导出操作如果涉及敏感数据还要考虑权限控制和数据脱敏。最后说一个容易被忽略的点Excel文件本身是有大小限制的。xlsx格式理论上支持1048576行、16384列但实际使用中超过五十万行的文件打开就会很卡。如果数据量真的到了这个级别建议考虑CSV格式或者拆分多个文件导出。CSV没有格式兼容问题生成速度也快得多缺点是丢失样式和公式但对于纯数据导出场景完全够用。我在实际项目里通常会同时提供Excel和CSV两种导出选项让用户根据用途自己选。需要看格式和样式的用Excel需要做数据分析或者导入其他系统的用CSV。这个小小的选择权能减少很多“为什么导出的文件打不开”的客诉。