1. 为什么“数据库到Excel”这个需求值得认真对待但凡做过企业级应用的人几乎都绕不开一个场景业务方跑过来说“能不能把系统里的数据导出来给我我要在Excel里做分析”。或者反过来“我这边整理了一份Excel能不能批量导入到系统里”。这个需求听起来简单得不像个技术问题但真正动过手的人都知道从MySQL到Excel的导出与导入坑远比想象中多。我做过不少类似的项目从最早用JDBC拼字符串生成CSV到后来用POI直接写xlsx再到用EasyExcel处理百万行级别的数据每一次都在踩坑中积累经验。这篇文章就把我这些年在这个场景下积累的东西完整梳理一遍从方案选型、核心实现、性能优化到常见问题排查尽量做到你拿着就能用。先说清楚这个内容适合谁看如果你是一个Java后端开发者手头有MySQL数据库需要实现数据的Excel导出和导入功能不管你是刚入行的新手还是有一定经验的工程师这篇文章都能给你提供可直接参考的方案。我会从最基础的实现讲起逐步深入到性能优化和异常处理中间会穿插大量实际项目中总结的经验和注意事项。核心关键词就几个Java、MySQL、Excel、导出、导入、Apache POI、EasyExcel。这些词会贯穿全文但我不会为了堆砌而堆砌每个技术点的出现都是因为实际需要。2. 方案选型用什么库、走什么路线2.1 主流Excel处理库对比Java生态里处理Excel的库不算多主流的就是那么几个。我整理了一个对比表格方便你根据自己项目的实际情况做选择库名称优势劣势适用场景Apache POI功能最全支持xls和xlsx社区成熟API偏底层代码量大内存占用高复杂格式、图表、公式等高级需求EasyExcelAPI简洁内存占用低适合大数据量功能相对POI少一些复杂格式支持有限大数据量导入导出格式要求不极端JXL轻量API简单只支持xls已停止维护老项目维护新项目不推荐CSV方案极简无依赖性能最好不支持格式、多sheet编码问题多纯数据交换无格式要求我个人的选择逻辑是这样的如果数据量在10万行以内格式要求不高直接用EasyExcel如果数据量超过50万行优先考虑EasyExcel的流式处理或者CSV方案如果业务方要求复杂的表头合并、单元格样式、公式计算那就老老实实用Apache POI。这里要特别说一个误区很多人觉得POI性能差就完全不用其实POI的SXSSF模式流式写入在处理大数据量时表现也不差只是API用起来确实比EasyExcel啰嗦不少。2.2 导出方案的核心考量导出这个方向核心要解决的问题就三个数据量、格式要求、响应方式。数据量决定了你用哪种写入模式。POI的XSSF模式是把整个工作簿放在内存里10万行以上就可能OOMSXSSF模式是流式写入只在内存里保留一定行数默认100行适合百万级数据。EasyExcel底层也是基于流式写入的思路但封装得更好用。格式要求决定了你选哪个库。如果只是简单的表头加数据行EasyExcel足够如果需要合并单元格、设置条件格式、插入图片那POI是唯一选择。响应方式决定了你的接口设计。如果是同步导出用户点击按钮后等待文件生成然后下载那要注意HTTP超时和内存控制如果是异步导出生成文件后存到文件服务器再通知用户下载那要考虑文件清理和并发控制。2.3 导入方案的核心考量导入比导出更复杂因为你要处理用户输入的不确定性。用户可能传一个空文件、可能传一个格式不对的文件、可能传一个列顺序乱掉的文件、可能传一个包含非法字符的文件。导入功能的核心不是“读Excel”而是“校验和容错”。我的经验是导入功能至少要考虑以下几层校验文件格式校验是不是xlsx/xls、文件大小校验防止恶意大文件、表头校验列名和顺序是否匹配、数据类型校验数字列是不是数字、业务规则校验必填项、唯一性、关联关系。每一层校验都要给用户明确的错误提示最好能定位到具体是哪一行哪一列出了问题。3. 从MySQL导出到Excel的完整实现3.1 环境准备与依赖引入先假设你用的是Maven项目需要引入的依赖如下。这里我同时给出POI和EasyExcel的依赖你可以根据实际选型决定用哪个!-- Apache POI 核心依赖 -- dependency groupIdorg.apache.poi/groupId artifactIdpoi/artifactId version5.2.3/version /dependency dependency groupIdorg.apache.poi/groupId artifactIdpoi-ooxml/artifactId version5.2.3/version /dependency !-- EasyExcel 依赖 -- dependency groupIdcom.alibaba/groupId artifactIdeasyexcel/artifactId version3.3.2/version /dependency !-- MySQL 驱动 -- dependency groupIdmysql/groupId artifactIdmysql-connector-java/artifactId version8.0.33/version /dependency注意POI 5.x版本和4.x版本在API上有一些差异网上很多老教程用的是4.x的写法直接复制可能会编译报错。建议统一用5.x版本API更稳定。数据库连接池我推荐用HikariCPSpring Boot项目默认就是它这里不展开。关键是要确保你的数据源配置里fetchSize设置合理导出大数据量时这个参数直接影响内存占用。3.2 基于POI的导出实现先看一个最基础的POI导出实现把MySQL中的一张表导出为xlsx文件。假设我们有一张用户表字段包括id、name、email、create_timepublic void exportWithPoi(Connection conn, String outputPath) throws Exception { String sql SELECT id, name, email, create_time FROM user; try (PreparedStatement ps conn.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY)) { // 关键设置流式读取结果集避免一次性加载所有数据到内存 ps.setFetchSize(Integer.MIN_VALUE); // MySQL驱动需要设置为流式模式 ps conn.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); ps.setFetchSize(Integer.MIN_VALUE); try (ResultSet rs ps.executeQuery(); SXSSFWorkbook workbook new SXSSFWorkbook(100)) { // 内存中保留100行 Sheet sheet workbook.createSheet(用户数据); // 创建表头 Row headerRow sheet.createRow(0); String[] headers {ID, 姓名, 邮箱, 创建时间}; for (int i 0; i headers.length; i) { Cell cell headerRow.createCell(i); cell.setCellValue(headers[i]); } // 填充数据 int rowIndex 1; while (rs.next()) { Row row sheet.createRow(rowIndex); row.createCell(0).setCellValue(rs.getLong(id)); row.createCell(1).setCellValue(rs.getString(name)); row.createCell(2).setCellValue(rs.getString(email)); Timestamp ts rs.getTimestamp(create_time); if (ts ! null) { row.createCell(3).setCellValue( new SimpleDateFormat(yyyy-MM-dd HH:mm:ss).format(ts)); } } // 写入文件 try (FileOutputStream fos new FileOutputStream(outputPath)) { workbook.write(fos); } // 清理临时文件 workbook.dispose(); } } }这段代码有几个关键点需要解释。第一SXSSFWorkbook的构造参数100表示内存中只保留100行数据超出的部分会写入临时文件这样即使导出100万行也不会OOM。第二ps.setFetchSize(Integer.MIN_VALUE)是MySQL驱动特有的流式读取设置不加这个的话驱动会一次性把所有结果集加载到内存数据量大时同样会OOM。第三workbook.dispose()一定要调用否则临时文件不会被清理磁盘空间会被慢慢吃掉。3.3 基于EasyExcel的导出实现同样的需求用EasyExcel来实现会简洁很多。先定义一个数据对象Data public class UserExcelDTO { ExcelProperty(ID) private Long id; ExcelProperty(姓名) private String name; ExcelProperty(邮箱) private String email; ExcelProperty(创建时间) DateTimeFormat(yyyy-MM-dd HH:mm:ss) private Date createTime; }然后导出逻辑public void exportWithEasyExcel(Connection conn, String outputPath) throws Exception { String sql SELECT id, name, email, create_time FROM user; try (PreparedStatement ps conn.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY)) { ps.setFetchSize(Integer.MIN_VALUE); try (ResultSet rs ps.executeQuery()) { // 构建数据列表实际项目中建议分批查询避免一次性加载 ListUserExcelDTO dataList new ArrayList(); while (rs.next()) { UserExcelDTO dto new UserExcelDTO(); dto.setId(rs.getLong(id)); dto.setName(rs.getString(name)); dto.setEmail(rs.getString(email)); dto.setCreateTime(rs.getTimestamp(create_time)); dataList.add(dto); } EasyExcel.write(outputPath, UserExcelDTO.class) .sheet(用户数据) .doWrite(dataList); } } }EasyExcel的代码量明显少很多而且ExcelProperty注解直接定义了表头DateTimeFormat处理了日期格式化非常直观。但要注意上面这种写法是把所有数据先加载到List里再写入数据量特别大时内存会有压力。EasyExcel也支持分批写入后面性能优化部分会讲。3.4 导出功能的接口设计实际项目中导出通常是一个HTTP接口。这里给出一个Spring Boot的Controller示例GetMapping(/export/users) public void exportUsers(HttpServletResponse response) throws IOException { response.setContentType(application/vnd.openxmlformats-officedocument.spreadsheetml.sheet); response.setCharacterEncoding(utf-8); String fileName URLEncoder.encode(用户数据, UTF-8).replaceAll(\\, %20); response.setHeader(Content-Disposition, attachment;filename*utf-8 fileName .xlsx); // 使用EasyExcel直接写入response的输出流 EasyExcel.write(response.getOutputStream(), UserExcelDTO.class) .sheet(用户数据) .doWrite(queryUserList()); }提示文件名一定要做URL编码否则中文文件名在部分浏览器上会乱码。filename*utf-8这种写法是RFC 5987规定的兼容性最好。4. 从Excel导入到MySQL的完整实现4.1 导入功能的设计思路导入功能我一般会拆成三个阶段解析阶段、校验阶段、入库阶段。解析阶段负责把Excel文件读成Java对象列表校验阶段负责检查数据的合法性入库阶段负责批量写入数据库。这三个阶段分开的好处是每个阶段都可以独立处理异常给用户精确的错误反馈。解析阶段用EasyExcel的监听器模式是最合适的因为它支持逐行读取不需要一次性加载整个文件。校验阶段可以定义一个校验器接口针对不同的业务规则实现不同的校验逻辑。入库阶段用JDBC的批量插入配合事务控制。4.2 基于EasyExcel的导入实现先定义一个导入用的监听器public class UserImportListener extends AnalysisEventListenerUserExcelDTO { private static final int BATCH_SIZE 500; private ListUserExcelDTO batchList new ArrayList(BATCH_SIZE); private ListString errorMessages new ArrayList(); private int rowIndex 1; // 从第2行开始是数据第1行是表头 private UserService userService; public UserImportListener(UserService userService) { this.userService userService; } Override public void invoke(UserExcelDTO data, AnalysisContext context) { rowIndex; // 单行校验 String error validateRow(data); if (error ! null) { errorMessages.add(第 rowIndex 行 error); return; } batchList.add(data); // 达到批次大小就入库 if (batchList.size() BATCH_SIZE) { saveBatch(); } } Override public void doAfterAllAnalysed(AnalysisContext context) { // 处理最后一批 if (!batchList.isEmpty()) { saveBatch(); } } private void saveBatch() { try { userService.batchInsert(batchList); } catch (Exception e) { errorMessages.add(批次入库失败 e.getMessage()); } batchList.clear(); } private String validateRow(UserExcelDTO data) { if (data.getName() null || data.getName().trim().isEmpty()) { return 姓名不能为空; } if (data.getEmail() null || !data.getEmail().matches(^[\\w.-][\\w.-]\\.[a-zA-Z]{2,}$)) { return 邮箱格式不正确; } return null; } public ListString getErrorMessages() { return errorMessages; } }调用方式public ImportResult importUsers(MultipartFile file) throws IOException { UserImportListener listener new UserImportListener(userService); EasyExcel.read(file.getInputStream(), UserExcelDTO.class, listener) .sheet() .doRead(); ImportResult result new ImportResult(); result.setSuccessCount(/* 成功条数 */); result.setErrorMessages(listener.getErrorMessages()); return result; }这个实现有几个值得注意的地方。第一BATCH_SIZE设为500是一个经验值太小会导致频繁的数据库交互太大会占用过多内存500到1000之间比较合适。第二错误信息记录了具体的行号用户拿到反馈后可以直接定位到Excel中的问题行。第三doAfterAllAnalysed方法里处理最后一批数据这个容易被遗漏。4.3 批量入库的SQL优化批量插入的性能差异非常大我做过测试逐条插入和批量插入在1万条数据上的耗时差距可以达到几十倍。下面是批量插入的示例public void batchInsert(ListUserExcelDTO list) { String sql INSERT INTO user (name, email, create_time) VALUES (?, ?, ?); try (Connection conn dataSource.getConnection(); PreparedStatement ps conn.prepareStatement(sql)) { conn.setAutoCommit(false); for (UserExcelDTO dto : list) { ps.setString(1, dto.getName()); ps.setString(2, dto.getEmail()); ps.setTimestamp(3, new Timestamp(dto.getCreateTime().getTime())); ps.addBatch(); } ps.executeBatch(); conn.commit(); } catch (SQLException e) { // 异常处理 } }注意MySQL的JDBC连接URL上要加上rewriteBatchedStatementstrue参数否则addBatch()和executeBatch()不会真正合并成一条SQL性能提升非常有限。这个参数是MySQL驱动特有的优化开关很多人不知道。4.4 导入功能的容错设计导入功能最怕的就是用户传了一个乱七八糟的文件然后系统抛出一堆看不懂的异常。我的做法是在解析之前先做一轮文件级别的校验public void validateFile(MultipartFile file) { if (file.isEmpty()) { throw new BusinessException(文件不能为空); } String fileName file.getOriginalFilename(); if (fileName null || (!fileName.endsWith(.xlsx) !fileName.endsWith(.xls))) { throw new BusinessException(只支持xlsx和xls格式的文件); } if (file.getSize() 10 * 1024 * 1024) { throw new BusinessException(文件大小不能超过10MB); } }然后在解析过程中对于格式错误、类型转换失败等异常EasyExcel会抛出ExcelAnalysisException需要捕获并转换成用户能理解的提示。我一般会自定义一个异常处理器EasyExcel.read(inputStream, UserExcelDTO.class, listener) .registerConverter(new CustomStringConverter()) .sheet() .doRead();自定义Converter可以处理一些特殊的数据转换场景比如把Excel中的“是/否”转换成Java的Boolean或者把“2024/1/1”这种非标准日期格式解析成Date对象。5. 性能优化与大数据量处理5.1 导出性能优化的几个关键点大数据量导出核心就一个字流。从数据库查询到Excel写入整条链路都要做到流式处理不能在任何环节把全量数据加载到内存。数据库层面前面提到的setFetchSize(Integer.MIN_VALUE)是必须的。但这里有个坑MySQL驱动在流式读取模式下同一个连接不能同时执行其他查询否则会报“Streaming result set is still active”的错误。所以导出操作最好使用独立的数据库连接不要和业务查询共用。Excel写入层面POI的SXSSF模式已经做了流式处理但要注意SXSSFWorkbook的构造参数不要设得太大100到1000之间比较合适。设得太大内存占用高设得太小临时文件IO频繁。还有一个容易被忽略的点是单元格样式的创建。POI中每创建一个CellStyle都会占用内存如果每行都创建新样式数据量大时内存会暴涨。正确的做法是把样式创建在循环外面所有行复用同一个样式对象CellStyle style workbook.createCellStyle(); Font font workbook.createFont(); font.setBold(true); style.setFont(font); // 在循环外面创建循环里面复用 for (int i 0; i headers.length; i) { Cell cell headerRow.createCell(i); cell.setCellValue(headers[i]); cell.setCellStyle(style); // 复用样式 }5.2 导入性能优化的几个关键点导入的性能瓶颈通常在数据库写入上。除了前面说的批量插入和rewriteBatchedStatementstrue还有几个优化点第一关闭自动提交。每次executeBatch()之前手动setAutoCommit(false)批量执行完再commit()可以减少事务开销。第二合理设置批次大小。我实测下来500到1000条一批比较均衡。批次太小数据库交互频繁批次太大内存占用高且单次事务时间长。第三使用多线程解析。EasyExcel支持多线程读取但要注意线程安全问题。如果业务允许可以把Excel按行拆分成多个片段用线程池并行解析和入库。不过这个方案实现复杂度较高数据量不是特别大的话没必要。第四数据库连接池配置。导入操作会占用数据库连接较长时间连接池的maxPoolSize要设置合理避免导入操作把连接池占满导致其他业务请求无法处理。5.3 内存监控与OOM预防大数据量导出最容易出的问题就是OOM。除了用流式处理还建议在代码里加一些内存监控的逻辑Runtime runtime Runtime.getRuntime(); long usedMemory runtime.totalMemory() - runtime.freeMemory(); if (usedMemory runtime.maxMemory() * 0.8) { // 内存使用超过80%触发GC或者暂停处理 System.gc(); // 如果还是高考虑分批处理或者抛出提示 }当然System.gc()只是建议JVM执行GC不保证立即执行。更可靠的做法是在JVM启动参数里设置合理的堆大小和GC策略比如-Xmx2g -Xms2g -XX:UseG1GC。6. 常见问题与排查技巧实录6.1 导出常见问题速查表问题现象可能原因解决方案导出文件打不开提示损坏输出流未正确关闭或写入不完整确保workbook.write()后正确关闭流使用try-with-resources中文文件名乱码未做URL编码使用URLEncoder.encode()并设置正确的Content-Disposition导出数据量大了就OOM使用了XSSFWorkbook或未设置fetchSize改用SXSSFWorkbook设置setFetchSize(Integer.MIN_VALUE)日期格式不对未设置日期格式或时区问题使用DateTimeFormat注解或手动SimpleDateFormat导出速度特别慢逐条查询数据库或频繁创建样式批量查询样式复用使用流式写入数字变成了科学计数法Excel自动格式化设置单元格格式为文本或使用BigDecimal并设置DataFormat6.2 导入常见问题速查表问题现象可能原因解决方案读取不到数据表头行号设置错误检查headRowNumber参数默认是1日期解析失败Excel中的日期格式不标准自定义Converter处理多种日期格式数字精度丢失Excel中数字被存为double使用String接收再转换或使用BigDecimal批量插入慢未开启rewriteBatchedStatementsJDBC URL添加该参数部分数据入库失败未做事务控制使用事务失败时回滚或记录错误行内存溢出一次性读取所有数据使用监听器模式逐行读取分批入库6.3 几个我踩过的坑第一个坑MySQL流式读取的副作用。前面提到setFetchSize(Integer.MIN_VALUE)可以实现流式读取但很多人不知道的是在流式读取模式下如果结果集没有读完就关闭了StatementMySQL驱动会抛出异常。所以一定要确保ResultSet被完整遍历或者正确关闭。第二个坑EasyExcel的并发写入问题。EasyExcel的doWrite()方法不是线程安全的如果多个线程同时往同一个文件写入会出现数据错乱。如果确实需要并发导出每个线程应该写入不同的文件最后再合并。第三个坑Excel的单元格类型判断。用POI读取Excel时cell.getCellType()返回的类型可能和预期不一致。比如用户输入的数字“123”Excel可能存为NUMERIC类型也可能存为STRING类型。稳妥的做法是用DataFormatter来统一处理DataFormatter formatter new DataFormatter(); String value formatter.formatCellValue(cell);第四个坑大文件导入时的超时问题。如果导入的文件很大HTTP请求可能会超时。解决方案是把导入操作改成异步的用户上传文件后立即返回一个任务ID后台异步处理用户通过任务ID查询进度和结果。第五个坑Excel中的空行处理。用户可能在数据末尾留了很多空行EasyExcel默认会跳过完全空白的行但如果空行中有格式比如边框就可能被当成有效数据。建议在监听器的invoke()方法里加一个判断如果所有字段都为空就跳过。7. 一些进阶思路与扩展方向7.1 动态表头与多Sheet导出实际项目中经常遇到需要动态生成表头的场景比如用户可以选择导出哪些字段。EasyExcel支持动态表头ListListString head new ArrayList(); head.add(Collections.singletonList(ID)); head.add(Collections.singletonList(姓名)); // 根据用户选择动态添加 EasyExcel.write(outputPath) .head(head) .sheet(数据) .doWrite(dataList);多Sheet导出也很简单多次调用sheet()方法即可ExcelWriter writer EasyExcel.write(outputPath).build(); WriteSheet sheet1 EasyExcel.writerSheet(0, 用户数据).head(UserExcelDTO.class).build(); WriteSheet sheet2 EasyExcel.writerSheet(1, 订单数据).head(OrderExcelDTO.class).build(); writer.write(userList, sheet1); writer.write(orderList, sheet2); writer.finish();7.2 导入时的数据去重与更新策略导入数据时经常需要处理“已存在的数据是更新还是跳过”的问题。我的做法是在校验阶段查询数据库根据业务主键判断数据是否已存在然后分别走插入或更新逻辑。批量场景下可以先把所有主键查出来放到一个Set里避免逐条查询数据库。7.3 导出文件的存储与清理如果是异步导出生成的文件需要临时存储。我一般会在服务器上建一个临时目录文件名用UUID避免冲突然后设置一个定时任务定期清理超过24小时的文件。如果文件比较大还可以考虑压缩后再提供下载。8. 个人经验总结做了这么多导出导入的功能我最大的体会是这个看似简单的需求实际上考验的是你对整个数据链路的掌控能力。从数据库查询、内存管理、Excel写入、HTTP响应到异常处理每一个环节都可能出问题。如果让我给新手一个建议那就是先用最简单的方案把功能跑通然后再根据实际的数据量和性能要求逐步优化。不要一上来就追求完美方案很多时候业务方的数据量根本没那么大过度设计反而增加了维护成本。另外导入功能的错误提示一定要做得足够友好。用户传了一个有问题的文件你要告诉他具体是哪一行哪一列出了什么问题而不是抛一个“系统异常”就完事了。这一点在实际项目中得到的正面反馈最多也是最容易被开发者忽略的地方。最后分享一个小技巧在导出功能的测试阶段可以用LIMIT子句限制查询条数先验证格式和逻辑是否正确确认无误后再去掉限制做全量导出。这样可以避免每次测试都等待大量数据查询和写入的时间。
1. 为什么“数据库到Excel”这个需求值得认真对待但凡做过企业级应用的人几乎都绕不开一个场景业务方跑过来说“能不能把系统里的数据导出来给我我要在Excel里做分析”。或者反过来“我这边整理了一份Excel能不能批量导入到系统里”。这个需求听起来简单得不像个技术问题但真正动过手的人都知道从MySQL到Excel的导出与导入坑远比想象中多。我做过不少类似的项目从最早用JDBC拼字符串生成CSV到后来用POI直接写xlsx再到用EasyExcel处理百万行级别的数据每一次都在踩坑中积累经验。这篇文章就把我这些年在这个场景下积累的东西完整梳理一遍从方案选型、核心实现、性能优化到常见问题排查尽量做到你拿着就能用。先说清楚这个内容适合谁看如果你是一个Java后端开发者手头有MySQL数据库需要实现数据的Excel导出和导入功能不管你是刚入行的新手还是有一定经验的工程师这篇文章都能给你提供可直接参考的方案。我会从最基础的实现讲起逐步深入到性能优化和异常处理中间会穿插大量实际项目中总结的经验和注意事项。核心关键词就几个Java、MySQL、Excel、导出、导入、Apache POI、EasyExcel。这些词会贯穿全文但我不会为了堆砌而堆砌每个技术点的出现都是因为实际需要。2. 方案选型用什么库、走什么路线2.1 主流Excel处理库对比Java生态里处理Excel的库不算多主流的就是那么几个。我整理了一个对比表格方便你根据自己项目的实际情况做选择库名称优势劣势适用场景Apache POI功能最全支持xls和xlsx社区成熟API偏底层代码量大内存占用高复杂格式、图表、公式等高级需求EasyExcelAPI简洁内存占用低适合大数据量功能相对POI少一些复杂格式支持有限大数据量导入导出格式要求不极端JXL轻量API简单只支持xls已停止维护老项目维护新项目不推荐CSV方案极简无依赖性能最好不支持格式、多sheet编码问题多纯数据交换无格式要求我个人的选择逻辑是这样的如果数据量在10万行以内格式要求不高直接用EasyExcel如果数据量超过50万行优先考虑EasyExcel的流式处理或者CSV方案如果业务方要求复杂的表头合并、单元格样式、公式计算那就老老实实用Apache POI。这里要特别说一个误区很多人觉得POI性能差就完全不用其实POI的SXSSF模式流式写入在处理大数据量时表现也不差只是API用起来确实比EasyExcel啰嗦不少。2.2 导出方案的核心考量导出这个方向核心要解决的问题就三个数据量、格式要求、响应方式。数据量决定了你用哪种写入模式。POI的XSSF模式是把整个工作簿放在内存里10万行以上就可能OOMSXSSF模式是流式写入只在内存里保留一定行数默认100行适合百万级数据。EasyExcel底层也是基于流式写入的思路但封装得更好用。格式要求决定了你选哪个库。如果只是简单的表头加数据行EasyExcel足够如果需要合并单元格、设置条件格式、插入图片那POI是唯一选择。响应方式决定了你的接口设计。如果是同步导出用户点击按钮后等待文件生成然后下载那要注意HTTP超时和内存控制如果是异步导出生成文件后存到文件服务器再通知用户下载那要考虑文件清理和并发控制。2.3 导入方案的核心考量导入比导出更复杂因为你要处理用户输入的不确定性。用户可能传一个空文件、可能传一个格式不对的文件、可能传一个列顺序乱掉的文件、可能传一个包含非法字符的文件。导入功能的核心不是“读Excel”而是“校验和容错”。我的经验是导入功能至少要考虑以下几层校验文件格式校验是不是xlsx/xls、文件大小校验防止恶意大文件、表头校验列名和顺序是否匹配、数据类型校验数字列是不是数字、业务规则校验必填项、唯一性、关联关系。每一层校验都要给用户明确的错误提示最好能定位到具体是哪一行哪一列出了问题。3. 从MySQL导出到Excel的完整实现3.1 环境准备与依赖引入先假设你用的是Maven项目需要引入的依赖如下。这里我同时给出POI和EasyExcel的依赖你可以根据实际选型决定用哪个!-- Apache POI 核心依赖 -- dependency groupIdorg.apache.poi/groupId artifactIdpoi/artifactId version5.2.3/version /dependency dependency groupIdorg.apache.poi/groupId artifactIdpoi-ooxml/artifactId version5.2.3/version /dependency !-- EasyExcel 依赖 -- dependency groupIdcom.alibaba/groupId artifactIdeasyexcel/artifactId version3.3.2/version /dependency !-- MySQL 驱动 -- dependency groupIdmysql/groupId artifactIdmysql-connector-java/artifactId version8.0.33/version /dependency注意POI 5.x版本和4.x版本在API上有一些差异网上很多老教程用的是4.x的写法直接复制可能会编译报错。建议统一用5.x版本API更稳定。数据库连接池我推荐用HikariCPSpring Boot项目默认就是它这里不展开。关键是要确保你的数据源配置里fetchSize设置合理导出大数据量时这个参数直接影响内存占用。3.2 基于POI的导出实现先看一个最基础的POI导出实现把MySQL中的一张表导出为xlsx文件。假设我们有一张用户表字段包括id、name、email、create_timepublic void exportWithPoi(Connection conn, String outputPath) throws Exception { String sql SELECT id, name, email, create_time FROM user; try (PreparedStatement ps conn.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY)) { // 关键设置流式读取结果集避免一次性加载所有数据到内存 ps.setFetchSize(Integer.MIN_VALUE); // MySQL驱动需要设置为流式模式 ps conn.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); ps.setFetchSize(Integer.MIN_VALUE); try (ResultSet rs ps.executeQuery(); SXSSFWorkbook workbook new SXSSFWorkbook(100)) { // 内存中保留100行 Sheet sheet workbook.createSheet(用户数据); // 创建表头 Row headerRow sheet.createRow(0); String[] headers {ID, 姓名, 邮箱, 创建时间}; for (int i 0; i headers.length; i) { Cell cell headerRow.createCell(i); cell.setCellValue(headers[i]); } // 填充数据 int rowIndex 1; while (rs.next()) { Row row sheet.createRow(rowIndex); row.createCell(0).setCellValue(rs.getLong(id)); row.createCell(1).setCellValue(rs.getString(name)); row.createCell(2).setCellValue(rs.getString(email)); Timestamp ts rs.getTimestamp(create_time); if (ts ! null) { row.createCell(3).setCellValue( new SimpleDateFormat(yyyy-MM-dd HH:mm:ss).format(ts)); } } // 写入文件 try (FileOutputStream fos new FileOutputStream(outputPath)) { workbook.write(fos); } // 清理临时文件 workbook.dispose(); } } }这段代码有几个关键点需要解释。第一SXSSFWorkbook的构造参数100表示内存中只保留100行数据超出的部分会写入临时文件这样即使导出100万行也不会OOM。第二ps.setFetchSize(Integer.MIN_VALUE)是MySQL驱动特有的流式读取设置不加这个的话驱动会一次性把所有结果集加载到内存数据量大时同样会OOM。第三workbook.dispose()一定要调用否则临时文件不会被清理磁盘空间会被慢慢吃掉。3.3 基于EasyExcel的导出实现同样的需求用EasyExcel来实现会简洁很多。先定义一个数据对象Data public class UserExcelDTO { ExcelProperty(ID) private Long id; ExcelProperty(姓名) private String name; ExcelProperty(邮箱) private String email; ExcelProperty(创建时间) DateTimeFormat(yyyy-MM-dd HH:mm:ss) private Date createTime; }然后导出逻辑public void exportWithEasyExcel(Connection conn, String outputPath) throws Exception { String sql SELECT id, name, email, create_time FROM user; try (PreparedStatement ps conn.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY)) { ps.setFetchSize(Integer.MIN_VALUE); try (ResultSet rs ps.executeQuery()) { // 构建数据列表实际项目中建议分批查询避免一次性加载 ListUserExcelDTO dataList new ArrayList(); while (rs.next()) { UserExcelDTO dto new UserExcelDTO(); dto.setId(rs.getLong(id)); dto.setName(rs.getString(name)); dto.setEmail(rs.getString(email)); dto.setCreateTime(rs.getTimestamp(create_time)); dataList.add(dto); } EasyExcel.write(outputPath, UserExcelDTO.class) .sheet(用户数据) .doWrite(dataList); } } }EasyExcel的代码量明显少很多而且ExcelProperty注解直接定义了表头DateTimeFormat处理了日期格式化非常直观。但要注意上面这种写法是把所有数据先加载到List里再写入数据量特别大时内存会有压力。EasyExcel也支持分批写入后面性能优化部分会讲。3.4 导出功能的接口设计实际项目中导出通常是一个HTTP接口。这里给出一个Spring Boot的Controller示例GetMapping(/export/users) public void exportUsers(HttpServletResponse response) throws IOException { response.setContentType(application/vnd.openxmlformats-officedocument.spreadsheetml.sheet); response.setCharacterEncoding(utf-8); String fileName URLEncoder.encode(用户数据, UTF-8).replaceAll(\\, %20); response.setHeader(Content-Disposition, attachment;filename*utf-8 fileName .xlsx); // 使用EasyExcel直接写入response的输出流 EasyExcel.write(response.getOutputStream(), UserExcelDTO.class) .sheet(用户数据) .doWrite(queryUserList()); }提示文件名一定要做URL编码否则中文文件名在部分浏览器上会乱码。filename*utf-8这种写法是RFC 5987规定的兼容性最好。4. 从Excel导入到MySQL的完整实现4.1 导入功能的设计思路导入功能我一般会拆成三个阶段解析阶段、校验阶段、入库阶段。解析阶段负责把Excel文件读成Java对象列表校验阶段负责检查数据的合法性入库阶段负责批量写入数据库。这三个阶段分开的好处是每个阶段都可以独立处理异常给用户精确的错误反馈。解析阶段用EasyExcel的监听器模式是最合适的因为它支持逐行读取不需要一次性加载整个文件。校验阶段可以定义一个校验器接口针对不同的业务规则实现不同的校验逻辑。入库阶段用JDBC的批量插入配合事务控制。4.2 基于EasyExcel的导入实现先定义一个导入用的监听器public class UserImportListener extends AnalysisEventListenerUserExcelDTO { private static final int BATCH_SIZE 500; private ListUserExcelDTO batchList new ArrayList(BATCH_SIZE); private ListString errorMessages new ArrayList(); private int rowIndex 1; // 从第2行开始是数据第1行是表头 private UserService userService; public UserImportListener(UserService userService) { this.userService userService; } Override public void invoke(UserExcelDTO data, AnalysisContext context) { rowIndex; // 单行校验 String error validateRow(data); if (error ! null) { errorMessages.add(第 rowIndex 行 error); return; } batchList.add(data); // 达到批次大小就入库 if (batchList.size() BATCH_SIZE) { saveBatch(); } } Override public void doAfterAllAnalysed(AnalysisContext context) { // 处理最后一批 if (!batchList.isEmpty()) { saveBatch(); } } private void saveBatch() { try { userService.batchInsert(batchList); } catch (Exception e) { errorMessages.add(批次入库失败 e.getMessage()); } batchList.clear(); } private String validateRow(UserExcelDTO data) { if (data.getName() null || data.getName().trim().isEmpty()) { return 姓名不能为空; } if (data.getEmail() null || !data.getEmail().matches(^[\\w.-][\\w.-]\\.[a-zA-Z]{2,}$)) { return 邮箱格式不正确; } return null; } public ListString getErrorMessages() { return errorMessages; } }调用方式public ImportResult importUsers(MultipartFile file) throws IOException { UserImportListener listener new UserImportListener(userService); EasyExcel.read(file.getInputStream(), UserExcelDTO.class, listener) .sheet() .doRead(); ImportResult result new ImportResult(); result.setSuccessCount(/* 成功条数 */); result.setErrorMessages(listener.getErrorMessages()); return result; }这个实现有几个值得注意的地方。第一BATCH_SIZE设为500是一个经验值太小会导致频繁的数据库交互太大会占用过多内存500到1000之间比较合适。第二错误信息记录了具体的行号用户拿到反馈后可以直接定位到Excel中的问题行。第三doAfterAllAnalysed方法里处理最后一批数据这个容易被遗漏。4.3 批量入库的SQL优化批量插入的性能差异非常大我做过测试逐条插入和批量插入在1万条数据上的耗时差距可以达到几十倍。下面是批量插入的示例public void batchInsert(ListUserExcelDTO list) { String sql INSERT INTO user (name, email, create_time) VALUES (?, ?, ?); try (Connection conn dataSource.getConnection(); PreparedStatement ps conn.prepareStatement(sql)) { conn.setAutoCommit(false); for (UserExcelDTO dto : list) { ps.setString(1, dto.getName()); ps.setString(2, dto.getEmail()); ps.setTimestamp(3, new Timestamp(dto.getCreateTime().getTime())); ps.addBatch(); } ps.executeBatch(); conn.commit(); } catch (SQLException e) { // 异常处理 } }注意MySQL的JDBC连接URL上要加上rewriteBatchedStatementstrue参数否则addBatch()和executeBatch()不会真正合并成一条SQL性能提升非常有限。这个参数是MySQL驱动特有的优化开关很多人不知道。4.4 导入功能的容错设计导入功能最怕的就是用户传了一个乱七八糟的文件然后系统抛出一堆看不懂的异常。我的做法是在解析之前先做一轮文件级别的校验public void validateFile(MultipartFile file) { if (file.isEmpty()) { throw new BusinessException(文件不能为空); } String fileName file.getOriginalFilename(); if (fileName null || (!fileName.endsWith(.xlsx) !fileName.endsWith(.xls))) { throw new BusinessException(只支持xlsx和xls格式的文件); } if (file.getSize() 10 * 1024 * 1024) { throw new BusinessException(文件大小不能超过10MB); } }然后在解析过程中对于格式错误、类型转换失败等异常EasyExcel会抛出ExcelAnalysisException需要捕获并转换成用户能理解的提示。我一般会自定义一个异常处理器EasyExcel.read(inputStream, UserExcelDTO.class, listener) .registerConverter(new CustomStringConverter()) .sheet() .doRead();自定义Converter可以处理一些特殊的数据转换场景比如把Excel中的“是/否”转换成Java的Boolean或者把“2024/1/1”这种非标准日期格式解析成Date对象。5. 性能优化与大数据量处理5.1 导出性能优化的几个关键点大数据量导出核心就一个字流。从数据库查询到Excel写入整条链路都要做到流式处理不能在任何环节把全量数据加载到内存。数据库层面前面提到的setFetchSize(Integer.MIN_VALUE)是必须的。但这里有个坑MySQL驱动在流式读取模式下同一个连接不能同时执行其他查询否则会报“Streaming result set is still active”的错误。所以导出操作最好使用独立的数据库连接不要和业务查询共用。Excel写入层面POI的SXSSF模式已经做了流式处理但要注意SXSSFWorkbook的构造参数不要设得太大100到1000之间比较合适。设得太大内存占用高设得太小临时文件IO频繁。还有一个容易被忽略的点是单元格样式的创建。POI中每创建一个CellStyle都会占用内存如果每行都创建新样式数据量大时内存会暴涨。正确的做法是把样式创建在循环外面所有行复用同一个样式对象CellStyle style workbook.createCellStyle(); Font font workbook.createFont(); font.setBold(true); style.setFont(font); // 在循环外面创建循环里面复用 for (int i 0; i headers.length; i) { Cell cell headerRow.createCell(i); cell.setCellValue(headers[i]); cell.setCellStyle(style); // 复用样式 }5.2 导入性能优化的几个关键点导入的性能瓶颈通常在数据库写入上。除了前面说的批量插入和rewriteBatchedStatementstrue还有几个优化点第一关闭自动提交。每次executeBatch()之前手动setAutoCommit(false)批量执行完再commit()可以减少事务开销。第二合理设置批次大小。我实测下来500到1000条一批比较均衡。批次太小数据库交互频繁批次太大内存占用高且单次事务时间长。第三使用多线程解析。EasyExcel支持多线程读取但要注意线程安全问题。如果业务允许可以把Excel按行拆分成多个片段用线程池并行解析和入库。不过这个方案实现复杂度较高数据量不是特别大的话没必要。第四数据库连接池配置。导入操作会占用数据库连接较长时间连接池的maxPoolSize要设置合理避免导入操作把连接池占满导致其他业务请求无法处理。5.3 内存监控与OOM预防大数据量导出最容易出的问题就是OOM。除了用流式处理还建议在代码里加一些内存监控的逻辑Runtime runtime Runtime.getRuntime(); long usedMemory runtime.totalMemory() - runtime.freeMemory(); if (usedMemory runtime.maxMemory() * 0.8) { // 内存使用超过80%触发GC或者暂停处理 System.gc(); // 如果还是高考虑分批处理或者抛出提示 }当然System.gc()只是建议JVM执行GC不保证立即执行。更可靠的做法是在JVM启动参数里设置合理的堆大小和GC策略比如-Xmx2g -Xms2g -XX:UseG1GC。6. 常见问题与排查技巧实录6.1 导出常见问题速查表问题现象可能原因解决方案导出文件打不开提示损坏输出流未正确关闭或写入不完整确保workbook.write()后正确关闭流使用try-with-resources中文文件名乱码未做URL编码使用URLEncoder.encode()并设置正确的Content-Disposition导出数据量大了就OOM使用了XSSFWorkbook或未设置fetchSize改用SXSSFWorkbook设置setFetchSize(Integer.MIN_VALUE)日期格式不对未设置日期格式或时区问题使用DateTimeFormat注解或手动SimpleDateFormat导出速度特别慢逐条查询数据库或频繁创建样式批量查询样式复用使用流式写入数字变成了科学计数法Excel自动格式化设置单元格格式为文本或使用BigDecimal并设置DataFormat6.2 导入常见问题速查表问题现象可能原因解决方案读取不到数据表头行号设置错误检查headRowNumber参数默认是1日期解析失败Excel中的日期格式不标准自定义Converter处理多种日期格式数字精度丢失Excel中数字被存为double使用String接收再转换或使用BigDecimal批量插入慢未开启rewriteBatchedStatementsJDBC URL添加该参数部分数据入库失败未做事务控制使用事务失败时回滚或记录错误行内存溢出一次性读取所有数据使用监听器模式逐行读取分批入库6.3 几个我踩过的坑第一个坑MySQL流式读取的副作用。前面提到setFetchSize(Integer.MIN_VALUE)可以实现流式读取但很多人不知道的是在流式读取模式下如果结果集没有读完就关闭了StatementMySQL驱动会抛出异常。所以一定要确保ResultSet被完整遍历或者正确关闭。第二个坑EasyExcel的并发写入问题。EasyExcel的doWrite()方法不是线程安全的如果多个线程同时往同一个文件写入会出现数据错乱。如果确实需要并发导出每个线程应该写入不同的文件最后再合并。第三个坑Excel的单元格类型判断。用POI读取Excel时cell.getCellType()返回的类型可能和预期不一致。比如用户输入的数字“123”Excel可能存为NUMERIC类型也可能存为STRING类型。稳妥的做法是用DataFormatter来统一处理DataFormatter formatter new DataFormatter(); String value formatter.formatCellValue(cell);第四个坑大文件导入时的超时问题。如果导入的文件很大HTTP请求可能会超时。解决方案是把导入操作改成异步的用户上传文件后立即返回一个任务ID后台异步处理用户通过任务ID查询进度和结果。第五个坑Excel中的空行处理。用户可能在数据末尾留了很多空行EasyExcel默认会跳过完全空白的行但如果空行中有格式比如边框就可能被当成有效数据。建议在监听器的invoke()方法里加一个判断如果所有字段都为空就跳过。7. 一些进阶思路与扩展方向7.1 动态表头与多Sheet导出实际项目中经常遇到需要动态生成表头的场景比如用户可以选择导出哪些字段。EasyExcel支持动态表头ListListString head new ArrayList(); head.add(Collections.singletonList(ID)); head.add(Collections.singletonList(姓名)); // 根据用户选择动态添加 EasyExcel.write(outputPath) .head(head) .sheet(数据) .doWrite(dataList);多Sheet导出也很简单多次调用sheet()方法即可ExcelWriter writer EasyExcel.write(outputPath).build(); WriteSheet sheet1 EasyExcel.writerSheet(0, 用户数据).head(UserExcelDTO.class).build(); WriteSheet sheet2 EasyExcel.writerSheet(1, 订单数据).head(OrderExcelDTO.class).build(); writer.write(userList, sheet1); writer.write(orderList, sheet2); writer.finish();7.2 导入时的数据去重与更新策略导入数据时经常需要处理“已存在的数据是更新还是跳过”的问题。我的做法是在校验阶段查询数据库根据业务主键判断数据是否已存在然后分别走插入或更新逻辑。批量场景下可以先把所有主键查出来放到一个Set里避免逐条查询数据库。7.3 导出文件的存储与清理如果是异步导出生成的文件需要临时存储。我一般会在服务器上建一个临时目录文件名用UUID避免冲突然后设置一个定时任务定期清理超过24小时的文件。如果文件比较大还可以考虑压缩后再提供下载。8. 个人经验总结做了这么多导出导入的功能我最大的体会是这个看似简单的需求实际上考验的是你对整个数据链路的掌控能力。从数据库查询、内存管理、Excel写入、HTTP响应到异常处理每一个环节都可能出问题。如果让我给新手一个建议那就是先用最简单的方案把功能跑通然后再根据实际的数据量和性能要求逐步优化。不要一上来就追求完美方案很多时候业务方的数据量根本没那么大过度设计反而增加了维护成本。另外导入功能的错误提示一定要做得足够友好。用户传了一个有问题的文件你要告诉他具体是哪一行哪一列出了什么问题而不是抛一个“系统异常”就完事了。这一点在实际项目中得到的正面反馈最多也是最容易被开发者忽略的地方。最后分享一个小技巧在导出功能的测试阶段可以用LIMIT子句限制查询条数先验证格式和逻辑是否正确确认无误后再去掉限制做全量导出。这样可以避免每次测试都等待大量数据查询和写入的时间。
