POI和EasyExcel处理大批量数据比较

女爷i 2022-12-13 04:16 592阅读 0赞

> 公众号上线啦！  
> 搜一搜【国服冰】  
> 使命：尽自己所能给自学后端开发的小伙伴提供一个少有弯路的平台  
> 回复：国服冰，即可领取我为大家准备的资料，里面包含整体的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";

**pom依赖**

<dependencies>
            
    
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
                <version>3.17</version>
            </dependency>
    
            
    
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml</artifactId>
                <version>3.17</version>
            </dependency>
    
            
            <dependency>
                <groupId>junit</groupId>
                <artifactId>junit</artifactId>
                <version>4.12</version>
            </dependency>
        </dependencies>

## 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();
        }

![xls用时][xls]

![excel][]

## 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();
        }

![xlsx][]  
**用时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();
        }

这里测试了`20W`数据量，在写入后记得`dispose()`清除生成的临时文件  
![在这里插入图片描述][20201009212603835.jpg_pic_center]

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

# 二、EasyExcel #

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

![easyexcel][easyexcel 1]

## pom依赖 ##

<dependencies>
            
            <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>
    
            
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>fastjson</artifactId>
                <version>1.2.73</version>
            </dependency>
        </dependencies>

## 一、写入 ##

**1、以什么格式写入**

@Data
    public class DemoData { 
        @ExcelProperty("姓名")
        private String name;
        @ExcelProperty("入职日期")
        private Date date;
        @ExcelProperty("工资")
        private Double sal;
        /** * 忽略这个字段 */
        @ExcelIgnore
        private String ignore;
    }

**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;
        }
    }

![easyexcel][easyexcel 2]  
**用时0.6s**

## 二、读取 ##

**1、读取规范**

//读取规范
    @Data
    public class User { 
        private String name;
        private Date date;
        private Double sal;
    }

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

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

**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("已存储到数据库");
        }
    }

**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();
        }
    }

**这里测试的文件只有10条**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNDQyMzM1_size_16_color_FFFFFF_t_70_pic_center]  
**更多功能移步：**[EasyExcel][]

[POI]: https://poi.apache.org/
[xls]: /images/20221123/5c7eb6d8a9e045b7ad101ec1dd509b9b.png
[excel]: /images/20221123/fae916453bb44c64ac7fc6c41cfbd3e2.png
[xlsx]: /images/20221123/91de08be89e44911ba3bffe05d1d4e09.png
[20201009212603835.jpg_pic_center]: /images/20221123/193e515542f64244b9207ad5e925580a.png
[easyexcel]: https://github.com/alibaba/easyexcel
[easyexcel 1]: /images/20221123/11ff58869d174ecda4b05aa25fc6d687.png
[easyexcel 2]: /images/20221123/130076e05fab4fa2930666e9e0d7d0b6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNDQyMzM1_size_16_color_FFFFFF_t_70_pic_center]: /images/20221123/73134addca4e40c38bddb0f666916974.png
[EasyExcel]: https://www.yuque.com/easyexcel/doc/easyexcel