[大数据]easyexcel替换poi导出大数据量数据时解决内存溢出问题时的一个性能测试

最近在项目中遇到导出大量数据到excel时发生内存溢出问题，然后看网上有人说用阿里开源的easyexcel性能非常好，我就在本地做了一个关于easyexcel和poi导出100w条一样数据时占用内存情况的测试，内存情况及GC情况用JDK自带的visualVM进行监控。

• 首先是poi的导出内存情况及代码示例：

public class PoiDemo {
    public static void main(String[] args) {
        Workbook w = new XSSFWorkbook();
        Sheet sheet = w.createSheet();
        List<Student> students = new ArrayList<>();
        for (int i = 0; i < 1000000; i++) {
            Row row = sheet.createRow(i);
            Student student = new Student();
            student.setAge("age:"+i);
            student.setDay("day:"+i);
            student.setHeight("height:"+i);
            student.setMonth("month:"+i);
            student.setName("name:"+i);
            student.setWeight("weight:"+i);
            student.setYear("year:"+i);
            students.add(student);
            row.createCell(0).setCellValue(student.getAge());
            row.createCell(1).setCellValue(student.getDay());
            row.createCell(2).setCellValue(student.getHeight());
            row.createCell(3).setCellValue(student.getMonth());
            row.createCell(4).setCellValue(student.getName());
            row.createCell(5).setCellValue(student.getWeight());
            row.createCell(6).setCellValue(student.getYear());
        }
        String filePath = "D:\\easyExcel.xlsx";
        try {
            FileOutputStream fileOutputStream = new FileOutputStream(filePath);
            w.write(fileOutputStream);
            fileOutputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }
}

运行情况：

这个是我运行了近两分钟的情况，可以看到运行25秒后堆空间基本已经无法扩容而且GC也回收不了其他对象了，这样运行下去肯定会OOM了。测试结果表明POI导出100w条数据占用堆内存肯定超过3000多M的。

• 再看easyexcel的导出情况和代码示例：

public class EasyDemo {
    public static void main(String[] args) {
        List<Student> students = new ArrayList<>();
        for (int i = 0; i < 1000000; i++) {
            Student student = new Student();
            student.setAge("age:"+i);
            student.setDay("day:"+i);
            student.setHeight("height:"+i);
            student.setMonth("month:"+i);
            student.setName("name:"+i);
            student.setWeight("weight:"+i);
            student.setYear("year:"+i);
            students.add(student);
        }
        String filePath = "D:\\easyExcel.xlsx";
        EasyExcel.write(filePath,Student.class).sheet("测试1").doWrite(students);
    }
}

运行情况：

可以看到只用14秒的时间就完成了100w数据量的导出，并且内存只占用了750M的样子，性能确实非常好。所以导出大数据量数据时可以使用easyexcel，还是比较好用的。

• 最好附上easyexcel的maven仓库地址

<dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.6</version>
</dependency>

实体类代码：

public class Student {
    private String name;
    private String age;
    private String height;
    private String weight;
    private String year;
    private String month;
    private String day;

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getAge() {
        return age;
    }

    public void setAge(String age) {
        this.age = age;
    }

    public String getHeight() {
        return height;
    }

    public void setHeight(String height) {
        this.height = height;
    }

    public String getWeight() {
        return weight;
    }

    public void setWeight(String weight) {
        this.weight = weight;
    }

    public String getYear() {
        return year;
    }

    public void setYear(String year) {
        this.year = year;
    }

    public String getMonth() {
        return month;
    }

    public void setMonth(String month) {
        this.month = month;
    }

    public String getDay() {
        return day;
    }

    public void setDay(String day) {
        this.day = day;
    }
}