POI和EasyExcel处理大批量数据比较_easyexcel写入两百万数据需要多久

公众号上线啦！
搜一搜【国服冰】
使命：尽自己所能给自学后端开发的小伙伴提供一个少有弯路的平台
回复：国服冰，即可领取我为大家准备的资料，里面包含整体的Java学习路线，电子书，以及史上最全的面试题！

一、POI

Apache POI 官网：POI

POI功能结构：
HSSF － 提供读写Microsoft Excel格式档案的功能。
XSSF － 提供读写Microsoft Excel OOXML格式档案的功能。
HWPF － 提供读写Microsoft Word格式档案的功能。
HSLF － 提供读写Microsoft PowerPoint格式档案的功能。
HDGF － 提供读写Microsoft Visio格式档案的功能。

这里要说的是03版和07版的excel存在差异问题，03版的最多只能插入65536行！！！

下面来测试POI同时写入20W行数据的速度如何

先设定好文件存入的路径，这里我放到项目的src目录下

private String PATH = "D:\\IDEA_workspace\\kexing-POI\\src";
1

pom依赖

<dependencies>
        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<!--        xls(03)-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<!--        xlsx(07)-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/junit/junit -->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
    </dependencies>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

03版写入

    //xls写入65536条数据
    @Test
    public void testBigData03() throws Exception {
    	//新建一个工作簿
        Workbook workbook = new HSSFWorkbook();
        //创建一个sheet
        Sheet sheet = workbook.createSheet("测试03excel");
        //写入前时间
        long before = System.currentTimeMillis();
        for(int i=0;i<65536;i++){
        	//创建行
            Row row = sheet.createRow(i);
            for(int j=0;j<10;j++){
            	//创建列，形成单元格
                Cell cell = row.createCell(j);
                //set值
                cell.setCellValue("第"+i+"行"+":"+j);
            }
        }
        //创建输出流,03版后缀xls
        FileOutputStream fos = new FileOutputStream(PATH+"WriteBigData03.xls");
        //工作簿写入流
        workbook.write(fos);
        //写入后时间
        long after = System.currentTimeMillis();
        long time = (after-before)/1000;
        System.out.println("用时"+time+"秒");
        //关闭流
        fos.close();
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

07版写入

xlsx可写入大批量数据，如十万级、百万级等等，但是这种方式是直接操作内存，当数据量太大时会导致OOM（内存溢出）

07版写入65536行数据

    @Test
    //xlsx写入65536万条数据
    //速度慢
    public void testBigData07() throws Exception {
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("测试07excel");
        long before = System.currentTimeMillis();
        for(int i=0;i<65536;i++){
            Row row = sheet.createRow(i);
            for(int j=0;j<10;j++){
                Cell cell = row.createCell(j);
                cell.setCellValue("第"+i+"行"+":"+j);
            }
        }
        FileOutputStream fos = new FileOutputStream(PATH+"WriteBigData07.xlsx");
        workbook.write(fos);
        long after = System.currentTimeMillis();
        long time = (after-before)/1000;
        System.out.println("用时"+time+"秒");
        fos.close();
    }

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

用时17秒！！！ 当插入大批量数据不建议使用XSSFWorkbook，而使用官网推荐的SXSSFWorkbook，会产生临时文件，效率提高

SXSSFWorkbook写入20W数据：

@Test
    //xlsx写入20万条数据，优化速度
    public void testBigData07S() throws Exception {
        Workbook workbook = new SXSSFWorkbook();
        Sheet sheet = workbook.createSheet("测试07excel优化写入");
        long before = System.currentTimeMillis();
        for(int i=0;i<200000;i++){
            Row row = sheet.createRow(i);
            for(int j=0;j<10;j++){
                Cell cell = row.createCell(j);
                cell.setCellValue("第"+i+"行"+":"+j);

            }
        }
        FileOutputStream fos = new FileOutputStream(PATH+"testBigData07S.xlsx");
        workbook.write(fos);
        //清除临时文件
        ((SXSSFWorkbook)workbook).dispose();
        long after = System.currentTimeMillis();
        long time = (after-before)/1000;
        System.out.println("用时"+time+"秒");
        fos.close();
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

这里测试了20W数据量，在写入后记得dispose()清除生成的临时文件

• 过程中仍然会产生临时文件，需要清理这些文件！
• 默认由100条记录保存在内存中，如果超过这数量，则前面的数据被写入临时文件;
• 这里用时了5s，感觉速度虽然优化了，但还是不够快！！！ 下面测试阿里爸爸的EasyExcel

二、EasyExcel

EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel。 github地址:easyexcel

pom依赖

<dependencies>
        <!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.6</version>
        </dependency>

        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.8</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/com.alibaba/fastjson -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.73</version>
        </dependency>
    </dependencies>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

一、写入

1、以什么格式写入

@Data
public class DemoData {
    @ExcelProperty("姓名")
    private String name;
    @ExcelProperty("入职日期")
    private Date date;
    @ExcelProperty("工资")
    private Double sal;
    /**
     * 忽略这个字段
     */
    @ExcelIgnore
    private String ignore;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14

2、推荐写法一、一行代码搞定

public class EasyWriteDemo1 {
    /**
     * 最简单的写
     * <p>1. 创建excel对应的实体对象 参照{@link DemoData}
     * <p>2. 直接写即可
     */
    @Test
    public void simpleWrite() {
        // 写法1
        String fileName = "D:\\IDEA_workspace\\easyExcel\\srceasyExcel.xlsx";
        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        // 如果这里想使用03 则 传入excelType参数即可
        EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data());
    }

	//写入数据、这里可以通过视图层获取数据写入到excel
	//这里测试的是20w数据量
    private List<DemoData> data() {
        List<DemoData> list = new ArrayList<DemoData>();
        long before = System.currentTimeMillis();
        for (int i = 0; i < 200000; i++) {
            DemoData data = new DemoData();
            data.setName("国服冰");
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-mm-dd HH:mm:ss");
            data.setDate(new Date());
            data.setSal(10000.0);
            list.add(data);
        }
        long after = System.currentTimeMillis();
        System.out.println("用时："+(after-before)+"ms");
        return list;
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

用时0.6s

二、读取

1、读取规范

//读取规范
@Data
public class User {
    private String name;
    private Date date;
    private Double sal;
}
1
2
3
4
5
6
7

2、Dao层
这里只是测试、不涉及到数据层

public class DemoDAO {
    public void save(List<User> list) {
        // 如果是mybatis,尽量别直接调用多次insert,自己写一个mapper里面新增一个方法batchInsert,所有数据一次性插入
    }
}
1
2
3
4
5

3、监听器

package site.kexing.read;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import java.util.ArrayList;
import java.util.List;

public class ExcelListener extends AnalysisEventListener {

    /**
     * 假设这个是一个DAO，当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。
     */
    private DemoDAO demoDAO;
    public ExcelListener() {
        // 这里是demo，所以随便new一个。实际使用如果到了spring,请使用下面的有参构造函数
        demoDAO = new DemoDAO();
    }

    /**
     * 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
     *
     * @param demoDAO
     */
//    public ExcelListener(DemoDAO demoDAO) {
//        this.demoDAO = demoDAO;
//    }

	//设置一次读取5条数据
    private int BATCH_COUNT  = 5;
    private List<User> datas = new ArrayList<>(BATCH_COUNT);

	//反射调用
    public void invoke(Object user, AnalysisContext analysisContext) {
        System.out.println("解析到一条数据"+JSON.toJSONString(user));
        datas.add((User) user);
        //判断插入数据是否超过预缓存
        //若超过，则先存储，然后继续解析
        if(datas.size() >= BATCH_COUNT){
            savaData();
            datas.clear();
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        savaData();
        System.out.println("监听完毕");
    }

    public void savaData(){
        System.out.println("共"+datas.size()+"条数据");
        demoDAO.save(datas);
        System.out.println("已存储到数据库");
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56

4、读取

public class EasyReadDemo1 {
    @Test
    public void easyRead01(){
        // 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
        // 写法1：
        String fileName = "D:\\IDEA_workspace\\easyExcel\\srceasyExcel.xlsx";
        // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
        EasyExcel.read(fileName, User.class, new ExcelListener()).sheet().doRead();
    }
}

1
2
3
4
5
6
7
8
9
10
11

这里测试的文件只有10条

更多功能移步：EasyExcel