一、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>
        <!-- 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、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用时

excel

2、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()清除生成的临时文件
在这里插入图片描述

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

二、EasyExcel

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

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、以什么格式写入

@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
用时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条
在这里插入图片描述
更多功能移步:EasyExcel

最后修改:2020 年 10 月 09 日 10 : 02 PM
如果觉得我的文章对你有用,请随意赞赏