POI学习

POI ===>Apache

EasyExcel ===>Alibaba

使用场景

1. 把用户信息导出为Excel表格
2. 把Excel表中的信息录入到网站数据库(例如习题上传)
3. 目前比较流行的就是Apache POI和阿里巴巴的EasyExcel

Apache POI

官网: https://poi.apache.org/

原生的,操作起来会比较麻烦, 十分消耗内存

EasyExcel

官网:https://github.com/alibaba/easyexcel

基本会出现内存溢出,不论Excel有多大

内存问题
POI   一次性全部读到内存中
EasyExcel   一行一行的读取返回

创建项目

引入依赖

<dependencies>
    <!--xls(03) -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.9</version>
    </dependency>

    <!--xls(07) -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>3.9</version>
    </dependency>

    <!--日期格式化工具 -->
    <dependency>
        <groupId>joda-time</groupId>
        <artifactId>joda-time</artifactId>
        <version>2.10.5</version>
    </dependency>

    <!--test -->
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.12</version>
    </dependency>

</dependencies>

创建两个文件,一个是03版,一个是07版的Excel文件

两个版本的区别:

03.xls    ===>  最多只有65536行
07.xlsx  ===>  理论上有无限多行

1. 工作簿:
2. 工作表:
3. 行:
4. 列:

POI写操作

    @Test
    public void testWrite03() throws Exception {
        //1. 创建工作簿
        Workbook workbook = new HSSFWorkbook();
        //2. 创建工作表
        Sheet sheet = workbook.createSheet("统计表1");
        //3. 创建一行
        Row row1 = sheet.createRow(0);
        //4. 创建单元格   (1,1)
        Cell cell1 = row1.createCell(0);
        cell1.setCellValue("日期");
        Cell cell2 = row1.createCell(1);
        cell2.setCellValue("收入金额");

        //3. 创建一行
        Row row2 = sheet.createRow(1);
        //4. 创建单元格   (2,1)
        Cell cell3 = row2.createCell(0);
        String s = new DateTime().toString();
        cell3.setCellValue(s);
        Cell cell4 = row2.createCell(1);
        cell4.setCellValue(998);

        //生成一张表  03版本使用xls结尾
        FileOutputStream stream = new FileOutputStream(PATH + "\\统计表03.xls");
        workbook.write(stream);
        //关闭流
        stream.close();
        System.out.println("生成完毕");
    }

    @Test
    public void testWrite07() throws Exception {
        //1. 创建工作簿
        Workbook workbook = new XSSFWorkbook();
        //2. 创建工作表
        Sheet sheet = workbook.createSheet("统计表1");
        //3. 创建一行
        Row row1 = sheet.createRow(0);
        //4. 创建单元格   (1,1)
        Cell cell1 = row1.createCell(0);
        cell1.setCellValue("日期");
        Cell cell2 = row1.createCell(1);
        cell2.setCellValue("收入金额");

        //3. 创建一行
        Row row2 = sheet.createRow(1);
        //4. 创建单元格   (2,1)
        Cell cell3 = row2.createCell(0);
        String s = new DateTime().toString();
        cell3.setCellValue(s);
        Cell cell4 = row2.createCell(1);
        cell4.setCellValue(998);

        //生成一张表  03版本使用xls结尾
        FileOutputStream stream = new FileOutputStream(PATH + "\\统计表07.xlsx");
        workbook.write(stream);
        //关闭流
        stream.close();
        System.out.println("生成完毕");
    }
}

对象的区别

03: HSSFWorkbook

07: XSSFWorkbook

注意事项

1. 大文件写HSSF

   • 缺点:只能处理65536行,否则会抛出异常
   • 优点:过程写入缓存,不操作磁盘,最后一次性写入磁盘,速度快

2. 大文件写XSSF

   • 缺点:读取很慢,很耗内存,容易内存溢出 100w
   • 可以写入较大的数据量 20w

3. 大文件写SXSSF

   • 有点: 可以读很大的数据量,占用内存少

     注意:

     • 过程中会产生临时文件,需要及时清理
     • 默认由100w条数据被保存在内存中,如果超过这个数量,则最前面的数据被写入临时文件中
     • 如果想要自定义内存中数据的量,可以使用 new SXSSFWorkbook(数量)

    @Test
    public void testWrite03BigData() throws Exception {
        long begin = System.currentTimeMillis();

        //创建一个工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();
        //创建表
        HSSFSheet sheet = workbook.createSheet();
        //创建表,写入数据
        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("我爱岳小宝");
            }
        }
        System.out.println("=========运行完毕========");
        FileOutputStream outputStream = new FileOutputStream(PATH + "\\testWrite03BigData.xls");
        workbook.write(outputStream);
        outputStream.close();
        long end = System.currentTimeMillis();
        System.out.println((double) (end-begin)/1000 + "s");  // 2.233
    }

    @Test  //耗时较长,使用缓存
    public void testWrite07BigData() throws Exception {
        long begin = System.currentTimeMillis();

        //创建一个工作簿
        Workbook workbook = new XSSFWorkbook();
        //创建表
        Sheet sheet = workbook.createSheet();
        //创建表,写入数据
        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("我爱岳小宝");
            }
        }
        System.out.println("=========运行完毕========");
        FileOutputStream outputStream = new FileOutputStream(PATH + "\\testWrite07BigData.xlsx");
        workbook.write(outputStream);
        outputStream.close();
        long end = System.currentTimeMillis();
        System.out.println((double) (end-begin)/1000 + "s");  // 35.401s
    }

    @Test  //使用SXSSF
    public void testWrite07BigData2() throws Exception {
        long begin = System.currentTimeMillis();

        //创建一个工作簿
        SXSSFWorkbook workbook = new SXSSFWorkbook();
        //创建表
        Sheet sheet = workbook.createSheet();
        //创建表,写入数据
        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("我爱岳小宝");
            }
        }
        System.out.println("=========运行完毕========");
        FileOutputStream outputStream = new FileOutputStream(PATH + "\\testWrite07BigData加速版.xlsx");
        workbook.write(outputStream);
        outputStream.close();
        //清楚临时文件
        workbook.dispose();
        long end = System.currentTimeMillis();
        System.out.println((double) (end-begin)/1000 + "s");  // 7.507s
    }

POI读

03/07

03版本

    @Test
    public void testRead03() throws Exception {
        //获取文件流
        FileInputStream inputStream = new FileInputStream(PATH + "统计表03--写出.xls");
        //创建工作簿  excel能操作的,java代码都能做
        HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
        Sheet sheet = workbook.getSheet("统计表1");
        Row row = sheet.getRow(1);
        Cell cell = row.getCell(1);
        //获取到的字符串
        //System.out.println(cell.getStringCellValue());
        //获取数字类型
        System.out.println(cell.getNumericCellValue());
        //关闭流
        inputStream.close();
    }

07版本(和03版本的基本一致)

读取不同的数据类型

… 省略 太麻烦

EasyExcel操作

导入依赖

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

写入测试

    @Test
    public void simpleWrite() {
        String fileName = PATH + "EasyTest.xlsx";
        // 这里 需要指定写用哪个class去读，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        // 如果这里想使用03 则 传入excelType参数即可
        EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data());
    }

读取测试

test.java

    @Test
    public void simpleRead() {
        String fileName = PATH + "EasyTest.xlsx";
        // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
        EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead();
    }

DemoDataListener.java

public class DemoDataListener extends AnalysisEventListener<DemoData> {
    private static final Logger LOGGER = LoggerFactory.getLogger(DemoDataListener.class);
    /**
     * 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 5;
    List<DemoData> list = new ArrayList<DemoData>();
    /**
     * 假设这个是一个DAO，当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。
     */
    private DemoDAO demoDAO;

    public DemoDataListener() {
        // 这里是demo，所以随便new一个。实际使用如果到了spring,请使用下面的有参构造函数
        demoDAO = new DemoDAO();
    }

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

    //读取数据要执行的方法
    @Override
    public void invoke(DemoData data, AnalysisContext context) {
        System.out.println(JSON.toJSONString(data));
        list.add(data);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (list.size() >= BATCH_COUNT) {
            saveData(); //持久化逻辑
            // 存储完成清理 list
            list.clear();
        }
    }

    /**
     * 所有数据解析完成了 都会来调用
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();
        LOGGER.info("所有数据解析完成！");
    }

    /**
     * 加上存储数据库
     */
    private void saveData() {
        LOGGER.info("{}条数据，开始存储数据库！", list.size());
        demoDAO.save(list);
        LOGGER.info("存储数据库成功！");
    }
}

DemoDAO.java

public class DemoDAO {

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

固定套路:

1. 写入,根据固定格式写入
2. 读取,根据监听器设置的规则进行读取