核心挑战：为什么POI会OOM？
Apache POI提供了两种主要的API来操作Excel：
（图片来源网络，侵删）
-
用户模型：
XSSFWorkbook(.xlsx) 和HSSFWorkbook(.xls)。- 原理：它会将整个Excel文件（包括所有单元格、样式、公式等）全部加载到内存中，形成一个类似DOM树的结构。
- 缺点：对于大文件，内存消耗巨大，一个几MB的Excel文件可能需要几百MB甚至上GB的内存，当数据量超过可用内存时，必然会抛出
OutOfMemoryError。 - 适用场景：小文件处理，或者需要对Excel进行复杂读写操作（如修改样式、公式）的场景。
-
SAX (事件) 模型：
XSSF和SAX结合使用。- 原理：它不会一次性加载整个文件，而是像解析XML一样，按行读取Excel文件，当解析到一行数据时，会触发一个事件，你可以在这个事件中处理当前行的数据，处理完后该行的数据就会被丢弃，从而极大地减少了内存占用。
- 优点：内存占用非常小，几乎是恒定的，非常适合处理大文件。
- 缺点：API相对复杂，不能直接修改或写入Excel（只能读取）。
- 适用场景：大数据导入、导出是它的最佳用武之地。
Apache POI SAX模式 (经典方案)
这是处理大数据最经典、最可靠的方式之一，我们将使用 XSSF 和 SAX 来解析 .xlsx 文件。
添加依赖
确保你的 pom.xml 中有 poi 和 poi-ooxml 依赖。
（图片来源网络，侵删）
<dependencies>
<!-- Apache POI Core -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.2.4</version> <!-- 使用较新版本 -->
</dependency>
<!-- Apache POI for OOXML (.xlsx) format -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.2.4</version>
</dependency>
<!-- 如果需要处理旧版.xls，还需要 poi-scratchpad -->
</dependencies>
实现SAX解析器
我们需要创建一个继承自 org.apache.poi.xssf.eventusermodel.XSSFReader 的解析器，并实现 org.xml.sax.helpers.DefaultHandler 来处理解析事件。
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.eventusermodel.XSSFReader.SheetIterator;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.xml.sax.Attributes;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;
import org.xml.sax.helpers.XMLReaderFactory;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
public class ExcelSaxParser {
private SharedStringsTable sst;
private String lastContents;
private boolean nextIsString;
private int sheetIndex = -1;
private List<String> rowValues = new ArrayList<>();
public void parse(InputStream inputStream) throws Exception {
OPCPackage pkg = OPCPackage.open(inputStream);
XSSFReader r = new XSSFReader(pkg);
sst = r.getSharedStringTable();
XMLReader parser = XMLReaderFactory.createXMLReader();
parser.setContentHandler(new SheetHandler());
// 遍历所有Sheet
SheetIterator sheets = (SheetIterator) r.getSheetsData();
while (sheets.hasNext()) {
sheetIndex++;
InputStream sheetStream = sheets.next();
InputSource sheetSource = new InputSource(sheetStream);
parser.parse(sheetSource);
sheetStream.close();
}
}
private class SheetHandler extends DefaultHandler {
private boolean firstRow = true; // 是否是第一行（表头）
private int currentColumn = -1;
@Override
public void startElement(String uri, String localName, String qName, Attributes attributes) throws SAXException {
// c => 单元格
if (qName.equals("c")) {
// 获取单元格的索引，如 A1, B2
String cellIndex = attributes.getValue("r");
currentColumn = this.getCellIndex(cellIndex);
// 如果下一个元素是 SST 的索引，则说明本单元格的值是字符串
String cellType = attributes.getValue("t");
if (cellType != null && cellType.equals("s")) {
nextIsString = true;
} else {
nextIsString = false;
}
}
// 清空上次内容
lastContents = "";
}
@Override
public void endElement(String uri, String localName, String qName) throws SAXException {
// v => 单元格的值
if (qName.equals("v")) {
if (nextIsString) {
// 如果是 SST 的索引，则从 SST 中获取字符串
int idx = Integer.parseInt(lastContents);
lastContents = new XSSFRichTextString(sst.getItemAt(idx)).getString();
}
// 将单元格的值存入行数据列表
if (currentColumn >= rowValues.size()) {
for (int i = rowValues.size(); i <= currentColumn; i++) {
rowValues.add("");
}
}
rowValues.set(currentColumn, lastContents.trim());
}
// row => 行结束
else if (qName.equals("row")) {
// 在这里处理一行数据
processRow(rowValues, firstRow);
firstRow = false;
// 清空行数据，准备下一行
rowValues.clear();
currentColumn = -1;
}
}
@Override
public void characters(char[] ch, int start, int length) throws SAXException {
lastContents += new String(ch, start, length);
}
// 根据单元格坐标 (如 "A1", "B2") 获取列索引 (0, 1, 2...)
private int getCellIndex(String cellRef) {
String ref = cellRef.replaceAll("\\d", "");
int index = 0;
for (int i = 0; i < ref.length(); i++) {
char c = ref.charAt(i);
index = index * 26 + (c - 'A' + 1);
}
return index - 1;
}
}
// 处理一行数据的回调方法
private void processRow(List<String> rowValues, boolean isHeader) {
if (isHeader) {
System.out.println("处理表头: " + rowValues);
// 这里可以解析表头，建立列名和字段的映射关系
} else {
System.out.println("处理数据行: " + rowValues);
// 在这里将 rowValues 写入数据库
// saveToDatabase(rowValues);
}
}
}
使用示例
import java.io.FileInputStream;
public class Main {
public static void main(String[] args) {
String filePath = "path/to/your/large_file.xlsx";
try (InputStream inputStream = new FileInputStream(filePath)) {
ExcelSaxParser parser = new ExcelSaxParser();
parser.parse(inputStream);
System.out.println("Excel文件解析完成！");
} catch (Exception e) {
e.printStackTrace();
}
}
}
EasyExcel (阿里巴巴开源，强烈推荐)
EasyExcel是阿里巴巴开源的一款针对Excel处理的项目,它解决了POI内存占用高的问题，底层也使用了SAX模式，它的API设计得非常友好，极大地降低了使用门槛。
添加依赖
<dependencies>
<!-- easyexcel 核心依赖 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.3.2</version> <!-- 使用较新版本 -->
</dependency>
</dependencies>
定义数据模型
为了方便数据映射,我们需要创建一个与Excel表头对应的Java类。
import com.alibaba.excel.annotation.ExcelProperty;
// 对应Excel的每一行数据
public class UserData {
// "姓名" 是Excel中的表头
@ExcelProperty("姓名")
private String name;
// "年龄" 是Excel中的表头
@ExcelProperty("年龄")
private Integer age;
// "邮箱" 是Excel中的表头
@ExcelProperty("邮箱")
private String email;
// Getters and Setters (必须要有)
public String getName() { return name; }
public void setName(String name) { this.name = name; }
public Integer getAge() { return age; }
public void setAge(Integer age) { this.age = age; }
public String getEmail() { return email; }
public void setEmail(String email) { this.email = email; }
@Override
public String toString() {
return "UserData{" +
"name='" + name + '\'' +
", age=" + age +
", email='" + email + '\'' +
'}';
}
}
创建监听器并读取
EasyExcel通过监听器模式来逐行读取数据,非常灵活。
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import java.util.ArrayList;
import java.util.List;
public class EasyExcelReadDemo {
// 模拟数据库存储
private static List<UserData> userDataList = new ArrayList<>();
public static void main(String[] args) {
String fileName = "path/to/your/large_file.xlsx";
// 这里 需要指定读用哪个class去读，然后读取第一个sheet
EasyExcel.read(fileName, UserData.class, new UserDataListener()).sheet().doRead();
}
/**
* 模拟一个DAO，DAO暂时不会进行数据库操作
*/
public void saveData(UserData userData) {
userDataList.add(userData);
System.out.println("存储数据: " + userData);
// 实际项目中，这里应该调用你的Service或Mapper将数据存入数据库
// 为了演示，我们每100条提交一次
if (userDataList.size() % 100 == 0) {
// batchSaveToDatabase();
userDataList.clear();
}
}
/**
* 数据的监听器，用于接收解析的数据
*/
public static class UserDataListener extends AnalysisEventListener<UserData> {
// 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
private static final int BATCH_COUNT = 5;
// 缓存的数据
private List<UserData> cachedDataList = new ArrayList<>(BATCH_COUNT);
@Override
public void invoke(UserData data, AnalysisContext context) {
// System.out.println("解析到一条数据: " + data);
cachedDataList.add(data);
// 达到BATCH_COUNT了，需要去存储一次数据库，防止数据量太大导致OOM
if (cachedDataList.size() >= BATCH_COUNT) {
saveData();
// 存储完成清理 list
cachedDataList.clear();
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// 这里也要保存数据，确保最后遗留的数据也存储到数据库
saveData();
System.out.println("所有数据解析完成！");
}
private void saveData() {
for (UserData data : cachedDataList) {
saveData(data);
}
}
}
}
EasyExcel的优势：
- API简单：无需手动编写复杂的SAX解析逻辑，通过注解和监听器就能快速上手。
- 内存优化：底层基于SAX，内存占用极低，官方宣称读取100M的Excel文件内存消耗不到1M。
- 功能强大：支持多种数据格式、自定义转换器、多Sheet读取等。
- 活跃社区：由阿里巴巴维护，文档完善，问题响应快。
数据库直接导入 (最高效)
如果数据量极其庞大（例如千万级），并且对导入性能有极致要求，可以考虑先将Excel文件转换为数据库支持的格式（如CSV），然后利用数据库的批量导入工具。
步骤：
- 转换格式：使用脚本（如Python的
pandas库）或工具将.xlsx文件另存为.csv文件，CSV是纯文本格式，体积更小，处理更快。 - 使用数据库工具：
- MySQL: 使用
LOAD DATA INFILE命令，这是MySQL官方提供的最高效的数据导入方式，速度极快。 - PostgreSQL: 使用
COPY命令。 - Oracle: 使用
SQL*Loader工具。 - SQL Server: 使用
bcp实用工具。
- MySQL: 使用
示例 (MySQL):
假设你有一个 users 表，结构和CSV文件对应。
-- 1. 准备好CSV文件，users.csv -- 2. 执行LOAD DATA INFILE命令 LOAD DATA INFILE '/path/to/users.csv' INTO TABLE users FIELDS TERMINATED BY ',' -- 字段分隔符，CSV通常是逗号 ENCLOSED BY '"' -- 字段包围符，CSV通常是双引号 LINES TERMINATED BY '\n' -- 行分隔符 IGNORE 1 ROWS; -- 忽略第一行（表头）
优点：
- 性能最高：数据库工具经过高度优化，直接在文件系统和数据库之间传输数据，绕过了应用层。
- 减少应用负载：Java应用不需要消耗CPU和内存来解析和插入数据。
缺点：
- 灵活性差：无法在导入前进行复杂的数据校验、清洗或转换，逻辑必须前置到转换脚本中。
- 平台依赖：需要数据库服务器有相应的文件访问权限，且不同数据库语法不同。
性能优化与最佳实践
无论选择哪种方案,以下几点都能显著提升导入性能：
-
数据库批量插入：
- 绝对禁止在循环中执行
INSERT INTO ... VALUES (...);，这是性能杀手。 - 使用
JDBC Batch：在代码中调用addBatch()和executeBatch()。 - 使用
INSERT INTO ... VALUES (...), (...), ...;语法，一次性插入多行数据，这是最有效的方式之一。
- 绝对禁止在循环中执行
-
关闭数据库自动提交： 在批量插入开始前，设置
connection.setAutoCommit(false);)，在批量操作结束后，手动提交connection.commit();。 -
分批次处理： 不要将所有数据都加载到内存中再一次性写入数据库，像EasyExcel的监听器一样，每读取一定数量的行（如500或1000条）就执行一次批量插入，然后清空内存，再继续下一批。
-
数据校验与清洗：
- 在数据写入数据库前,进行必要的格式校验（如日期格式、数字格式）、空值校验、业务规则校验。
- 可以将校验失败的数据记录到日志文件或另一个Excel中,方便后续处理。
-
关闭数据库索引和外键约束： 对于超大数据导入，可以考虑先临时禁用目标表的索引和约束，导入完成后再重建，这能极大提升插入速度。（操作需谨慎，并确保数据质量）
-
多线程处理： 如果数据可以按行或按块独立处理，可以考虑使用多线程来并行处理数据读取和写入，但要注意线程安全和数据库连接池的管理。
总结与选择
| 方案 | 优点 | 缺点 | 适用场景 |
|---|---|---|---|
| POI SAX模式 | 成熟稳定，社区资源多，不依赖第三方库 | API复杂，代码量多，需要自己处理细节 | 对第三方库有洁癖，或已有POI项目需要扩展 |
| EasyExcel | API简单易用，内存占用极低，功能强大 | 依赖第三方库 | 绝大多数Java大数据导入场景的首选 |
| 数据库直接导入 | 性能最高，应用负载最低 | 灵活性差，无法进行复杂业务处理 | 数据量极大（千万级），对导入速度有极致要求，且数据预处理逻辑简单 |
推荐路径：
- 新项目首选：直接使用 EasyExcel，它能让你用最少的代码实现高效、稳定的大数据导入，是业界的最佳实践。
- 现有POI项目：如果现有项目已经深度使用了POI，且遇到OOM问题，可以考虑升级到POI的SAX模式进行重构。
- 极限性能场景：如果数据量大到无法忍受任何应用层开销，且数据预处理逻辑简单，可以考虑 数据库直接导入。
