做了这么多年后台开发，我几乎在每个项目里都碰到过同一个需求：把数据库里的数据导成 Excel，或者反过来把 Excel 里的数据导进数据库。很多同学第一次接触这个任务时，第一反应是写个 CSV 文件让客户凑合用，结果客户打开一看中文乱码，格式全崩；第二反应是上网找库，A 依赖 B、B 又依赖 C，还没写业务代码就把时间花在踩依赖坑上了。Apache POI 就是解决这类“数据库和 Excel 之间相互转换”需求最经典的方案之一。

这篇文章不打算讲理论，直接把我在实际项目中用到的思路、代码片段、踩坑记录和性能优化方案摊开来说。无论你是刚入行的后端工程师，还是突然被产品经理塞了一个“导入导出功能”的半路出家人，只要你需要在 Java 项目里和 Excel 打交道，这篇文章应该能让你少走几周弯路。

1. 为什么“数据库 ↔ Excel”看似简单，做起来却总踩坑

1.1 业务场景里最常见的四种转换需求

先别急着写代码，我们盘一下日常工作中最常见的四种需求，你可以对照自己的项目看看属于哪一种。

第一种是“表结构导出”。运营同事说：“帮我把用户表导出来，我要做数据分析。”这种需求通常要的是全量字段，最好带表头、带筛选、带冻结首行，文件后缀还得是.xlsx。

第二种是“报表导出”。比如某订单管理系统里的月度销售报表，需要按日期汇总、按渠道分组、金额保留两位小数、表头合并单元格、还有一行合计。这种需求对 Excel 的格式要求很高，CSV 完全满足不了。

第三种是“模板导入”。某公司的批量开票系统，财务人员从银行拿到一份 Excel 对账单，需要解析每一行的日期、金额、商户号，校验后落到数据库里。这里面的坑在于：Excel 里的日期可能是文本、可能是自定义格式，金额可能带着千分位逗号，还有可能混着公式单元格。

第四种是“批量更新”。运维人员维护了一份“设备 IP 对照表”，里面改了十几条记录，需要根据设备编号更新数据库。这种场景通常不止一个 Sheet，还要跳过前几行说明文字，读取指定区域的数据。

你会发现，这四种需求没有一种能用简单的一行一行读文件来解决。格式、类型、大文件、异常数据全部混在一起，这才是“数据库和 Excel 互转”真正麻烦的地方。

1.2 用 POI 而不是 CSV / 手工导出的原因

很多新手会问：为什么不用 CSV？CSV 本质上就是逗号分隔的纯文本，写起来快，但业务上根本没法用。第一，编码问题：UTF-8 的 CSV 用 Excel 打开经常乱码，加 BOM 又会被某些程序解析出错。第二，格式问题：CSV 不支持单元格合并、不支持列宽设置、不支持批注、不支持数据验证下拉框，更不可能做到“金额红色标出来”。第三，大文件问题：CSV 没有流式写入的概念，一个 10 万行的文件写出来可能几百 MB，内存直接爆炸。

那手工导入导出呢？比如用 Navicat 自带导出向导，看起来能导出 Excel，但很多字段类型导出后会变成文本，日期格式还会自动变成yyyy/MM/dd，客户拿到手后还得手动改。更重要的是，这种“手工操作”没法集成到业务系统里，产品经理不可能每天拿数据库客户端去导数据。

Apache POI 解决的核心问题，就是让你用 Java 代码直接操作 Excel 文件（.xls和.xlsx），既能控制单元格的每一个细节，又能做到流式读写。它虽然 API 啰嗦了点，但胜在稳定、成熟、资料多，是 Java 生态里做 Office 文件操作绕不开的基础设施。

2. 环境准备与核心模型：先搞懂这几个类

2.1 项目依赖和版本选择

使用 POI 时，版本选择是第一道门槛。我见过不少项目用了老旧的 3.17，结果和新版 JDK 一起报NoClassDefFoundError。个人建议直接用 POI 5.x，它统一了包名、移除了很多旧 API，并且在处理.xlsx时性能更好。

Maven 项目只需要引入两个核心依赖：

<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>5.2.5</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>5.2.5</version> </dependency>

如果代码里要读取旧版.xls文件，poi和poi-ooxml已经包含支持了。另外还需要注意，POI 5.x 默认依赖commons-io、commons-compress、log4j-api等，Maven 会自动传递进来。如果公司内网私服没有这些依赖，打包时会出现ClassNotFoundException，提前确认一下。

提示：如果项目使用的是 JDK 17，不要再用 POI 3.x，很多内部 API 已经移除，会出现模块访问异常。

2.2 必须理解的 POI 核心对象

在没有真正写代码之前，先带着下面的几个类去理解 POI 的模型，后面读代码会轻松很多。

Workbook是操作 Excel 的顶层接口，对应一个完整的 Excel 文件。Sheet对应文件里的一个工作表，Row对应一行，Cell对应单元格。打开文件、创建文件、读写所有操作都从Workbook开始。

在实际开发中，我们通常使用WorkbookFactory.create(InputStream)来打开一个已有的文件，它会根据文件头自动判断是.xls还是.xlsx，然后创建对应的HSSFWorkbook或XSSFWorkbook。这样我们就不用写两套逻辑了。

创建新文件时，如果输出.xlsx，用new XSSFWorkbook()；如果输出.xls，用new HSSFWorkbook()。现在业务上基本都要求.xlsx，所以我很少再碰 HSSF。

还有一个很容易忽略的类叫DataFormatter。它能把单元格里的值按 Excel 显示格式转换回字符串，比如日期显示成2024-03-15，数字显示成1,234.56。在导入场景里，这个类特别有用，可以省掉大量手写类型判断的代码。

理解这几个对象之后，再去看 POI 的 API，就不会觉得陌生了：一切都是围绕“工作簿 - 工作表 - 行 - 单元格”的层级展开的。

3. 数据库导出到 Excel：一条可复用的完整链路

3.1 查询结果集的通用处理

导出的第一步，是把数据库查出来的数据整理成一个统一的结构。我常用的做法是：先查List<Map<String, Object>>，每一行 Map 对应一行数据，Key 是列名，Value 是 Java 对象。这样做的好处是，不管表结构怎么变，导出组件都不用改，只要传入列名列表即可。

比如某订单报表导出，我写了一个通用方法：

// 伪代码：从数据库查询数据 String sql = "SELECT order_no, user_name, total_amount, create_time FROM t_order WHERE create_time >= ?"; List<Map<String, Object>> dataList = jdbcTemplate.queryForList(sql, startTime); // 定义表头 List<String> headers = Arrays.asList("订单号", "用户名", "金额", "创建时间"); // 定义对应列名 List<String> columns = Arrays.asList("order_no", "user_name", "total_amount", "create_time");

这里需要注意：查询结果里的BigDecimal、Date、null都要在导出时区分处理。建议不要直接拿数据库原始值往 Excel 里塞，先统一转成字符串或原生类型，避免 POI 把某些类型识别成公式。

把数据映射成统一结构之后，下一步就是真正生成 Excel 文件了。

3.2 生成带格式的 Excel 文件

POI 生成 Excel 的步骤非常固定：创建工作簿 -> 创建工作表 -> 创建表头行 -> 设置样式 -> 逐行写入数据 -> 自动调整列宽 -> 写到输出流。

下面这段代码是导出一个简单报表的最基本写法，我尽量保留项目里能直接用的内容：

try (Workbook workbook = new XSSFWorkbook()) { Sheet sheet = workbook.createSheet("订单报表"); // 表头样式 CellStyle headerStyle = workbook.createCellStyle(); Font headerFont = workbook.createFont(); headerFont.setBold(true); headerFont.setFontHeightInPoints((short) 12); headerStyle.setFont(headerFont); headerStyle.setAlignment(HorizontalAlignment.CENTER); headerStyle.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex()); headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); // 创建表头 Row headerRow = sheet.createRow(0); for (int i = 0; i < headers.size(); i++) { Cell cell = headerRow.createCell(i); cell.setCellValue(headers.get(i)); cell.setCellStyle(headerStyle); } // 写入数据 for (int i = 0; i < dataList.size(); i++) { Row row = sheet.createRow(i + 1); Map<String, Object> data = dataList.get(i); for (int j = 0; j < columns.size(); j++) { Object value = data.get(columns.get(j)); Cell cell = row.createCell(j); if (value == null) { cell.setCellValue(""); } else if (value instanceof Number) { cell.setCellValue(((Number) value).doubleValue()); } else if (value instanceof Date) { // 日期单元格写入日期值，并设置格式，避免变成一串数字 cell.setCellValue((Date) value); cell.setCellStyle(dateCellStyle); } else { cell.setCellValue(value.toString()); } } } // 列宽 for (int i = 0; i < headers.size(); i++) { sheet.setColumnWidth(i, 20 * 256); } // 输出到响应或文件 try (FileOutputStream fos = new FileOutputStream("order.xlsx")) { workbook.write(fos); } }

这里有几个很关键的小细节。第一个是单元格类型：POI 里setCellValue(String)和setCellValue(double)是不同类型，如果先写入字符串再改成数字，格式可能会错乱。第二个是日期：如果直接把java.util.Date塞进单元格而不设置日期样式，Excel 会显示成441234.56789这种数值，用户基本看不懂。所以需要单独准备一个日期样式的CellStyle，并设置setDataFormat。第三个是try-with-resources：Workbook必须关闭，否则文件流不会 flush，内存里的对象也一直占着。

3.3 大批量数据的写入优化

如果你要导出的数据超过几万行，甚至几十万行，用上面这种XSSFWorkbook就有风险了。因为XSSFWorkbook会把整个文档结构保存在内存里，10 万行 * 20 列就可能把堆内存吃满，导出时系统直接卡死。

这时的标准解法是使用 POI 的流式版本SXSSFWorkbook。它的工作原理很简单：只在内存中保留最近的 N 行，窗口之外的行会刷新到磁盘临时文件，从而用较低的 IO 成本换取可控的内存占用。我实际测试过一个 30 万行、25 列的报表，使用SXSSFWorkbook后内存稳定在 300MB 左右，而普通XSSFWorkbook早就 OOM 了。

用法几乎不需要改动，只需要把new XSSFWorkbook()换成：

SXSSFWorkbook workbook = new SXSSFWorkbook(100);

这里的100表示内存中最多保留 100 行，其余行自动写入临时文件。用完以后，还要调用workbook.dispose()清理临时文件，最好放在finally里。

但要注意，SXSSFWorkbook有一些局限。比如它不支持对已刷新到磁盘的行再次修改样式，也不支持读取已存在的工作簿。它只适合“创建新文件 + 写入”的场景，正好完美匹配我们的导出需求。

在实际项目中，我还会给导出功能加一个阈值：超过 5000 行自动切换SXSSFWorkbook，小于 5000 行用普通XSSFWorkbook，兼顾性能和简单性。

4. Excel 导入数据库：从文件到数据的逆向流程

4.1 读取 Excel 的两种姿势

导入比导出更麻烦的地方在于：你永远不知道用户给的 Excel 是什么样子。可能第一行是标题，第二行才是表头；可能某个单元格合并了；可能末尾有一行“备注”。所以我处理导入时，第一件事不是写解析逻辑，而是先做“文件预检”：确认 Sheet 数量、确认文件行数、确认表头位置。

POI 读取 Excel 有两种常见姿势，第一种适合普通小文件：

try (Workbook workbook = WorkbookFactory.create(inputStream)) { Sheet sheet = workbook.getSheetAt(0); // 取第一个 Sheet for (Row row : sheet) { // 遍历每一行 } }

第二种是使用SheetDataFormatter把每个单元格转成字符串，然后统一处理：

DataFormatter formatter = new DataFormatter(); for (Row row : sheet) { for (Cell cell : row) { String value = formatter.formatCellValue(cell); } }

用DataFormatter的好处是，单元格显示成什么样，取出来的字符串就是什么样。比如日期单元格显示为2024-03-15，取出来的就是2024-03-15，不会出现需要自己把45200反算成日期的情况。不过它也有个坑：如果单元格里是公式，默认取出来的是公式计算后的值，但需要额外配置。

对于超大 Excel 文件，POI 官方提供了XSSFReader配合 SAX 方式的流式读取，但代码非常复杂。我一般只在小文件解析时使用普通 API，大文件导入干脆直接建议运营同事拆分成多个小文件，或者用 Python 脚本预处理后再导入。在 Java 后端强行解析几百 MB 的 Excel，往往得不偿失。

4.2 数据校验与类型转换

从 Excel 读到的数据永远是字符串，而数据库里的字段可能是数字、日期、布尔值。这是导入功能最容易出 bug 的地方，没有之一。

我总结了一套“先清洗、再转换、后入库”的流程：

清洗阶段：去除字符串两端的空格，把全角逗号替换为半角逗号，去掉金额里的千分位逗号，处理#N/A等错误值。这一步看着简单，实际效果巨大。我遇到过一个问题：用户在某列输入了1,200.00，数据库字段是DECIMAL，直接把字符串塞进去就会报错，去掉逗号后就正常了。

转换阶段：判断每个字段的目标类型。

private Object convertValue(String cellValue, String targetType) { if (cellValue == null || cellValue.trim().isEmpty()) { return null; } String v = cellValue.trim(); switch (targetType) { case "int": return Integer.parseInt(v); case "decimal": return new BigDecimal(v.replace(",", "")); case "date": // 兼容多种格式 DateTimeFormatter[] formatters = new DateTimeFormatter[]{ DateTimeFormatter.ofPattern("yyyy-MM-dd"), DateTimeFormatter.ofPattern("yyyy/MM/dd"), DateTimeFormatter.ofPattern("yyyyMMdd") }; for (DateTimeFormatter f : formatters) { try { return LocalDate.parse(v, f); } catch (DateTimeParseException ignored) { } } throw new IllegalArgumentException("无法解析日期: " + v); default: return v; } }

校验阶段：逐行检查必填字段、唯一性约束、长度限制，把错误结果收集起来。我习惯的做法是，不采用“解析过程中发现错误就立即中断”，而是把所有错误行都记录下来，最后统一返回给前端。这样用户不用一遍遍上传文件去试错。

注意：Excel 里一个单元格最多可以放下 32767 个字符，但数据库字段可能只有VARCHAR(50)。导入时一定要做长度校验，否则会在批量插入时得到 “Data too long for column” 的错误。

4.3 批量插入的落库方式

数据解析完成后，下一步是写入数据库。这里强烈建议使用 JDBC 的批量插入PreparedStatement+addBatch()，而不是逐条insert。批量插入的耗时是逐条插入的几十分之一，尤其是数据量上千行时，差距非常明显。

我用的核心逻辑类似下面这段：

String sql = "INSERT INTO t_import_detail (order_no, user_name, amount, create_time) VALUES (?, ?, ?, ?)"; try (Connection conn = dataSource.getConnection(); PreparedStatement ps = conn.prepareStatement(sql)) { int batchSize = 500; int count = 0; for (Map<String, Object> data : parsedData) { ps.setString(1, (String) data.get("order_no")); ps.setString(2, (String) data.get("user_name")); ps.setBigDecimal(3, (BigDecimal) data.get("amount")); ps.setDate(4, Date.valueOf((LocalDate) data.get("create_time"))); ps.addBatch(); count++; if (count % batchSize == 0) { ps.executeBatch(); ps.clearBatch(); } } ps.executeBatch(); // 处理剩余批次 }

批量大小我一般设置在 200 ~ 500 之间。太大会导致数据库端内存压力升高，太小又发挥不出批量插入的优势。另外，导入操作必须要放在事务里。这样当中间某一行因为脏数据报错时，前面已经插入成功的数据可以回滚，不会出现“导了一半，数据半新半旧”的灾难现场。

为了保证导入任务不阻塞主线程，我还会把导入过程放到一个带线程池的异步任务里，前端提交文件后立即返回任务 ID，任务状态通过后台查询或者 WebSocket 推送。不过这个属于工程架构范畴，对于单机小项目，同步导入也够用。

5. 常见问题与排查技巧

5.1 内存溢出：OOM 到底是谁的锅

最常见的报错是java.lang.OutOfMemoryError: GC overhead limit exceeded或者Java heap space。这种情况绝大多数发生在导出大文件或导入大文件时。

排查思路分三步。第一步，确认是不是XSSFWorkbook在持有整个文件对象。大数据导出请在第一步就换SXSSFWorkbook。第二步，确认是不是导入时把整个文件读成了byte[]。如果调用方把MultipartFile.getBytes()塞进WorkbookFactory.create()，10MB 的 Excel 可能膨胀成几百 MB 的内存对象，而且这个对象要等 GC 很久。正确做法是直接用MultipartFile.getInputStream()传给 POI。第三步，确认是不是解析时把每一个单元格的样式对象都保留了。POI 里创建CellStyle是有数量上限的，默认约 65535 个样式，如果样式创建过多，内存会疯涨。

5.2 日期和数字格式错乱

我在项目中遇到最多的问题是：数字单元格读出来变成了科学计数法，比如9.95276E8；日期单元格读出来是45200。这两个问题其实都源于单元格类型判断不对。

POI 里判断日期不能用instanceof，因为日期也是一个“数字”单元格，只是它设置了日期格式。正确判断方式是DateUtil.isCellDateFormatted(cell)。如果是日期格式，再取cell.getDateCellValue()。数字的问题则可以通过cell.setCellType或者DecimalFormat来解决。

如果是用DataFormatter读取，这两个问题通常会消失，但也会引入新问题：它读出来的是“显示值”，如果单元格宽度太窄，显示值会被截断。

5.3 公式单元格读出来是空

用户在 Excel 里写了一个公式，比如=C2*D2，但 POI 默认读取公式的结果是0或者空。这是因为 Excel 文件不一定缓存了计算结果。

解决办法是在打开工作簿时开启公式计算模式：

try (InputStream is = new FileInputStream("xxx.xlsx")) { Workbook workbook = WorkbookFactory.create(is); // 使用 FormulaEvaluator 计算 }

但更稳妥的方案是：在导入需求评审时直接告诉业务方，公式单元格不参与导入，请先粘贴成数值。否则你的代码不仅要维护公式解析，还要处理各种 Excel 函数，那工作量会失控。

5.4 并发导出导致临时文件堆积

SXSSFWorkbook会把超过窗口的数据写入临时目录。如果系统并发导出量很大，且没有及时调用dispose()，临时文件会一直累积，最终打爆磁盘。

我一般会在导出工具类里做两件事：一是用try-with-resources包装SXSSFWorkbook，确保close()时自动删除临时文件；二是定期清理系统临时目录里的poi-sxssf-*文件。同时，在导出接口层加一个信号量控制并发导出数量，比如最多允许 3 个任务同时跑，超过的排队等待。这个信号量的上限取决于服务器配置，经验值是任务内存峰值不超过堆内存的 1/3。

6. 我在实际项目中沉淀的几点经验

写到这里，其实已经把数据库和 Excel 互转的主要环节都覆盖了。最后聊几点文档里不太会写的体会，都是踩过坑之后才明白的。

第一，导入和导出功能一定要提前定义好“模板”。导出时，第一行写标题，第二行写表头；导入时，接收方先下载一个标准模板，然后上传。没有模板的导入导出，本质上是让后端去背所有意外的锅。

第二，凡是涉及金额、日期、身份证号这类特殊格式的数据，导出时千万不能走默认格式。金额要用setCellValue写入数字并设置#,##0.00格式；日期要设置yyyy-MM-dd HH:mm:ss；身份证号要设置单元格格式为文本再写入字符串，否则一旦超过 15 位，Excel 会把后面的数字变成 0。

第三，POI 的上手门槛不高，但坑都在细节里。我见过太多人抄了一段代码就跑，结果线上文件打不开、数据错位、内存爆掉。真正靠谱的做法是，花半天时间写一个覆盖表头、日期、金额、空值、特殊字符的单元测试，把各种边界情况都跑一遍，再上业务。

第四，如果只是偶尔转换一次文件，不要硬塞一个复杂的功能到项目里。手工用数据库客户端导一次，可能比写这个功能更快。但如果是长期反复使用、每天都有新的导出需求，那 POI 这套能力就值得做成一个通用组件，统一表头导出、统一导入校验、统一异步任务，后续所有模块都能复用。

说到底，Apache POI 解决的从来不只是“读写文件”这个技术问题，它解决的是业务系统里“数据如何从内部走向外部、从外部回到内部”的完整链路。把这条链路做好，比多写几个 CRUD 接口有价值得多。