Java菜谱（四）——怎么将10万条数据导出到excel？

今天的菜品是这样的：

怎么将10万条数据导出到excel？

太长不看版

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

下面是正文：

起因

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

进一步分析问题

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

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

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

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

通过采样结果，我们可以看到：
org.apache.xmlbeans.impl.store.XobjAttrXobj对象占用的内存最多，分别占用了1.2G和0.9G多的内存，它们都是poi在进行excel读写过程中产生的对象，存活对象的数量为上千万个。由于采样时间是发生在OOM之前，这里内存占用还没有达到很夸张的级别，但是这两个对象的数量和内存占用的确在迅猛的上涨。由此可以断定，OOM是poi处理大量excel数据写入的过程中发生的。

问题解决

定位到了问题，其实解决问题特别容易。通过查阅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());

        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");

最后说明几点

• xls格式（poi中对应HSSF实现）最多支持65536行数据，xlsx格式（poi中对应XSSF实现）最多支持1048576行数据。如果不是有特殊要求，建议无脑选择xlsx格式。
• 使用poi处理excel时，一个好的习惯是只有构造Workbook的时候使用实现类的构造器，其余一律使用接口，因为poi会自动为我们匹配对应版本的实现类，这样后续代码修改更加方便。
• 虽然List等数据结构中可以存放下10万条甚至100万条数据，但是大多数情况下不推荐这样做，因为被List引用的数据都没有办法被GC回收掉。像导出excel这类的需求，使用到较大数据量时，我们可以根据实际情况考虑分多次完成。