快速参考

问题：怎么将10万条数据导出到Excel？

核心解决方案：

• xls格式最多可以存65536行数据，而xlsx格式最多可以存1048576行数据
• 用SXSSFWorkbook代替XSSFWorkbook，即可避免大量数据导出到Excel导致的内存溢出问题（OOM）

---

问题背景

有同事在做导出Excel功能时，在数据量达到10万左右的时候，遇到了OOM（OutOfMemory，内存溢出）的问题，经分析是对象占用内存过大导致了堆内存空间不足。

问题分析

内存占用分析

那么究竟什么对象占用的内存过大呢？

是在List中存储的10万个对象么？为了搞清楚这个问题，我们需要知道在Java中一个对象大致占用多大的内存。这个问题相对来讲比较复杂，有不少文章都讨论了这一问题，我们在这里略过细节，只说一个比较粗糙的结论：一个不太复杂的对象在内存中占用的空间大概是100B~1KB之间。那么也就是说10万个对象的内存消耗大约是10MB~100MB。这点内存对于现代计算机来说可以说是洒洒水。也就是说，并非10万个对象本身导致了OOM的发生。

POI内存模型分析

接下来我们就应该考虑是Excel导出过程中发生了OOM。通过查阅资料，我们了解到Excel是一种基于XML的文档结构，POI在处理Excel文档（xlsx格式）时，其内存模型决定了其对于Excel的大多数关键组件（行、单元格、样式等等），都需要有一个对应的XSSF对象以及一个辅助的XMLBeans对象。所有的XSSF类的读写都是通过读写底层的XMLBeans对象来实现的。因此，在使用POI进行Excel读写的过程中，会产生大量的XMLBeans对象。这可能才是导致OOM的真凶。

内存分析验证

接下来我们希望借助工具来验证上面的假设。这里我们采用VisualVM组件来实时观察应用运行过程中的内存占用情况。在JDK8版本之前，VisualVM随JDK包一起发布，在JDK8之后，需要单独进行下载。在应用执行过程中，我们通过采样器对内存进行采样，结果如下：

采样结果分析：

通过采样结果，我们可以看到：

• org.apache.xmlbeans.impl.store.Xobj和AttrXobj对象占用的内存最多，分别占用了1.2G和0.9G多的内存
• 它们都是POI在进行Excel读写过程中产生的对象，存活对象的数量为上千万个
• 由于采样时间是发生在OOM之前，这里内存占用还没有达到很夸张的级别，但是这两个对象的数量和内存占用的确在迅猛的上涨

由此可以断定，OOM是POI处理大量Excel数据写入的过程中发生的。

解决方案

SXSSFWorkbook介绍

定位到了问题，其实解决问题特别容易。通过查阅POI的文档，我们了解到POI专门创建了一个用于大数据量读写的流式版本的XSSFWorkbook，称之为SXSSFWorkbook。

工作原理：

• 以硬盘空间换内存，只在内存中保留一小部分数据
• 其余数据都写入硬盘文件中
• 对于合并单元格、注释等信息，仍然存放在内存中

代码实现

代码层面修改更简单了，只要创建Workbook时把实现类由XSSFWorkbook替换为SXSSFWorkbook即可。示例代码如下：

Instant start = Instant.now();

// 生成测试数据
List<Student> students = new ArrayList<>();
Random random = new Random();
for (int i = 0; i < 1000_000; i++) {
    final Student student = Student.builder()
            .id(String.valueOf(i))
            .name("stu" + i)
            .age(random.nextInt(5) + 20)
            .gender(random.nextInt(2))
            .score((double) (50 + random.nextInt(50)))
            .classNumber(random.nextInt(10) + 1)
            .build();
    students.add(student);
}
System.out.println("当前耗时：" + Duration.between(start, Instant.now()).toMillis() + "ms");
System.out.println(students.size());

// 使用SXSSFWorkbook替代XSSFWorkbook
Workbook workbook = new SXSSFWorkbook();
final Sheet sheet = workbook.createSheet("students");

// 创建表头
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
cell.setCellValue("ID");

cell = row.createCell(1);
cell.setCellValue("姓名");

cell = row.createCell(2);
cell.setCellValue("年龄");

cell = row.createCell(3);
cell.setCellValue("性别");

cell = row.createCell(4);
cell.setCellValue("成绩");

cell = row.createCell(5);
cell.setCellValue("班级");

// 填充数据
for (int i = 0; i < students.size(); i++) {
    row = sheet.createRow(i + 1);
    cell = row.createCell(0);
    cell.setCellValue(students.get(i).getId());

    cell = row.createCell(1);
    cell.setCellValue(students.get(i).getName());

    cell = row.createCell(2);
    cell.setCellValue(students.get(i).getAge());

    cell = row.createCell(3);
    cell.setCellValue(students.get(i).getGender());

    cell = row.createCell(4);
    cell.setCellValue(students.get(i).getScore());

    cell = row.createCell(5);
    cell.setCellValue(students.get(i).getClassNumber());
}

System.out.println("当前耗时：" + Duration.between(start, Instant.now()).toMillis() + "ms");

// 写入文件
workbook.write(Files.newOutputStream(Paths.get("src", "main", "resources", "students.xlsx")));
System.out.println("当前耗时：" + Duration.between(start, Instant.now()).toMillis() + "ms");

最佳实践

Excel格式选择

• xls格式（POI中对应HSSF实现）：最多支持65536行数据
• xlsx格式（POI中对应XSSF实现）：最多支持1048576行数据

建议：如果不是有特殊要求，建议无脑选择xlsx格式。

POI使用习惯

使用POI处理Excel时，一个好的习惯是只有构造Workbook的时候使用实现类的构造器，其余一律使用接口，因为POI会自动为我们匹配对应版本的实现类，这样后续代码修改更加方便。

内存管理建议

虽然List等数据结构中可以存放下10万条甚至100万条数据，但是大多数情况下不推荐这样做，因为被List引用的数据都没有办法被GC回收掉。像导出Excel这类的需求，使用到较大数据量时，我们可以根据实际情况考虑分多次完成。