POI和EasyExcel

使用场景

1、讲用户的信息导出Excel表格（导出数据…） 2、将Excel表中的信息录入到网站数据库（大量信息上传），减轻网站录入量！开发中经常会设计到Excel的处理，如Excel的导出，Excel导入数据库！目前操作Excel比较流行的就是Apache POI 和阿里巴巴的 easyExcel!

Apache POI

Apache POI官网：https://poi.apache.org/

在这里插入图片描述

easyExcel

easyExcel 官网：https://github.com/alibaba/easyexcel 在这里插入图片描述 EasyExcel是阿里巴巴开源的一个excel处理框架，以使用简单、节省内存著称。 EasyExcel能大大减少占用内存的主要原因是在解析Excel时没有将文件数据一次性全部加载到内存中，而是从磁盘上一行行读取数据，逐个解析。内存问题:POI是全部先加载到内存OOM，在写入文件。EasyExcel 是一行一行读，逐个解析。下图是EasyExcel和POI在解析Excel时的对比图。

在这里插入图片描述

easyExcel 文档：https://alibaba-easyexcel.github.io/ https://www.yuque.com/easyexcel/doc/

POI-Excel写

创建项目 1、创建maven项目 2、导入pom依赖

 <!--导入依赖-->
    <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.1</version>
        </dependency>
        <!--test-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
    </dependencies>

03 | 07版本的写，就是对象不同，方法一样

需要注意：03版本和07版本存在兼容性问题！03最多65535行！07无限制！注意03版本和07版本的后缀不同在这里插入图片描述

在这里插入图片描述测试03版本代码注意path路径使用自己的存放路径

package com.zjb;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.joda.time.DateTime;
import org.junit.Test;

import java.io.FileOutputStream;

public class ExcelWriteTest {

    String path="D:\\分类存放\\2020-2021-2\\POI-EasyExcel";

    @Test
    public void Test03() throws Exception {
        //1.创建工作簿
        Workbook workbook=new HSSFWorkbook();
        //2.创建一个工作表
        Sheet sheet = workbook.createSheet("用户统计表");
        //3.创建一行
        Row row1 =sheet.createRow(0);
        //4.创建一个单元格（1,1）
        Cell cell11 = row1.createCell(0);
        //5.写入数据
        cell11.setCellValue("用户数量");
        //第一行（1,2）
        Cell cell12 = row1.createCell(1);
        cell12.setCellValue(888);
        //第二行
        Row row2 =sheet.createRow(1);
        //第二行（2,1）
        Cell cell21 = row2.createCell(0);
        cell21.setCellValue("统计时间");
        //第二行（2,2）
        Cell cell22 = row2.createCell(0);
        String s = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
        cell22.setCellValue(s);

        //生成一张表(IO流) 03版本使用xls结尾
        FileOutputStream fileOutputStream = new FileOutputStream(path + "/POI03测试.xls");
        workbook.write(fileOutputStream);
        //关闭流
        fileOutputStream.close();
        System.out.println("OI03测试.xls已生成");
    }
}

结果在这里插入图片描述 07版本测试代码上面已经说过了03 | 07版本的写，就是对象不同，方法一样

@Test
    public void Test07() throws Exception {
        //1.创建工作簿 07
        Workbook workbook=new XSSFWorkbook();
        //2.创建一个工作表
        Sheet sheet = workbook.createSheet("用户统计表");
        //3.创建一行
        Row row1 =sheet.createRow(0);
        //4.创建一个单元格（1,1）
        Cell cell11 = row1.createCell(0);
        //5.写入数据
        cell11.setCellValue("用户数量");
        //第一行（1,2）
        Cell cell12 = row1.createCell(1);
        cell12.setCellValue(888);
        //第二行
        Row row2 =sheet.createRow(1);
        //第二行（2,1）
        Cell cell21 = row2.createCell(0);
        cell21.setCellValue("统计时间");
        //第二行（2,2）
        Cell cell22 = row2.createCell(0);
        String s = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
        cell22.setCellValue(s);

        //生成一张表(IO流) 07版本使用xlsx结尾
        FileOutputStream fileOutputStream = new FileOutputStream(path + "/POI07测试.xlsx");
        workbook.write(fileOutputStream);
        //关闭流
        fileOutputStream.close();
        System.out.println("OI07测试.xlsx已生成");
    }

结果在这里插入图片描述

在这里插入图片描述数据批量导入！

大文件写HSSF（03版）

缺点：最多只能处理65536行，否则会抛出异常。 java.lang.IllegalArgumentException: Invalid row number (65536) outside allowable range (0..65535) 优点：过程中写入缓存。不操作磁盘，最后一次性写入磁盘，速度快。

@Test
    public void testWrite03BigData() throws IOException {
        //时间
        long begin=System.currentTimeMillis();
        //创建一个薄
        Workbook workbook = new HSSFWorkbook();
        //创建表
        Sheet sheet = workbook.createSheet();
        //写入数据
        for (int rowNum = 0; rowNum < 65536 ; rowNum++) {
            //创建行
            Row row = sheet.createRow(rowNum);
            for (int cellNum = 0; cellNum < 10; cellNum++) {
                //创建列
                Cell cell = row.createCell(cellNum);
                cell.setCellValue(cellNum);
            }
        }
        System.out.println("over");
        FileOutputStream outputStream = new FileOutputStream(path + "/03版速度测试");
        workbook.write(outputStream);
        outputStream.close();
        long end=System.currentTimeMillis();
        System.out.println((double) (end-begin)/1000);
    }

在这里插入图片描述

大文件写XSSF（07版）

缺点：写数据时速度非常慢，非常耗内存，也会发生内存溢出，如100万条优点：可以写较大的数据量，如20万条

@Test
    public void testWrite07BigData() throws Exception {
        //时间
        long begin=System.currentTimeMillis();
        //创建一个薄
        Workbook workbook = new XSSFWorkbook();
        //创建表
        Sheet sheet = workbook.createSheet();
        //写入数据
        for (int rowNum = 0; rowNum < 65537 ; rowNum++) {
            //创建行
            Row row = sheet.createRow(rowNum);
            for (int cellNum = 0; cellNum < 10; cellNum++) {
                //创建列
                Cell cell = row.createCell(cellNum);
                cell.setCellValue(cellNum);
            }
        }
        System.out.println("over");
        FileOutputStream outputStream = new FileOutputStream(path + "/07版速度测试.xlsx");
        workbook.write(outputStream);
        outputStream.close();
        long end=System.currentTimeMillis();
        System.out.println((double) (end-begin)/1000);
    }

在这里插入图片描述

大文件写SXSSF

优点：可以写非常大的数据量，如100万条甚至更多条，写数据速度快，占用更少的内存。注意： 过程中会产生临时文件，需要清理临时文件 默认由100条记录被保存在内存中，如果超过这数量，则最前面的数据被写入临时文件如果想自定义内存中数据的数量，可以使用new SxsSFWorkbook(数量)

更改下面对象名即可在这里插入图片描述

 @Test
    public void testWrite07BigDataS() throws Exception {
        //时间
        long begin=System.currentTimeMillis();
        //创建一个薄
        Workbook workbook = new SXSSFWorkbook();
        //创建表
        Sheet sheet = workbook.createSheet();
        //写入数据
        for (int rowNum = 0; rowNum < 65537 ; rowNum++) {
            //创建行
            Row row = sheet.createRow(rowNum);
            for (int cellNum = 0; cellNum < 10; cellNum++) {
                //创建列
                Cell cell = row.createCell(cellNum);
                cell.setCellValue(cellNum);
            }
        }
        System.out.println("over");
        FileOutputStream outputStream = new FileOutputStream(path + "/07版速度测试S.xlsx");
        workbook.write(outputStream);
        outputStream.close();
        //清除临时文件
        ((SXSSFWorkbook)workbook).dispose();
        long end=System.currentTimeMillis();
        System.out.println((double) (end-begin)/1000);
    }

在这里插入图片描述 SXSSFWorkbook-来至官方的解释∶实现"BigGridDemo"策略的流式XSSFWorkbook版本。这允许写入非常大的文件而不会耗尽内存，因为任何时候只有可配置的行部分被保存在内存中。

请注意，仍然可能会消耗大量内存，这些内存基于您正在使用的功能，例如合并区域，注解…….仍然只存储在内存中，因此如果广泛使用，可能需要大量内存。

POI-Excel读

03|07

03版本

String path="D:\\分类存放\\2020-2021-2\\POI-EasyExcel";
    @Test
    public void testRead03() throws Exception {
        //获取文件流
        FileInputStream inputStream = new FileInputStream(path+"/POI03测试.xls");
        //创建一个工作簿，使用Excel能操作的这里都有都可以操作
        Workbook workbook=new HSSFWorkbook(inputStream);
        //得到表
        Sheet sheet = workbook.getSheetAt(0);
        //得到行
        Row row = sheet.getRow(0);
        //得到列
        Cell cell = row.getCell(1);
        //读取的时候要注意类型
        //getStringCellValue()字符串
        //getNumericCellValue()数字
        //等等
        //System.out.println(cell.getStringCellValue());
        System.out.println(cell.getNumericCellValue());
        inputStream.close();
    }

07版本还是只需要改一下对象名即可！

	String path="D:\\分类存放\\2020-2021-2\\POI-EasyExcel";
	@Test
    public void testRead07() throws Exception {
        //获取文件流
        FileInputStream inputStream = new FileInputStream(path+"/POI07测试.xlsx");
        //创建一个工作簿，使用Excel能操作的这里都有都可以操作
        Workbook workbook=new XSSFWorkbook(inputStream);
        //得到表
        Sheet sheet = workbook.getSheetAt(0);
        //得到行
        Row row = sheet.getRow(0);
        //得到列
        Cell cell = row.getCell(1);
        //读取的时候要注意类型
        //getStringCellValue()字符串
        //getNumericCellValue()数字
        //等等
        //System.out.println(cell.getStringCellValue());
        System.out.println(cell.getNumericCellValue());
        inputStream.close();
    }

注意数据类型

读取不同的数据类型（非常麻烦）

	@Test
    public void testCellType() throws Exception {
        //获取文件流
        FileInputStream inputStream = new FileInputStream(path + "/数据类型测试.xls");
        //创建一个工作簿
        Workbook workbook=new HSSFWorkbook(inputStream);
        //获取表
        Sheet sheet = workbook.getSheetAt(0);
        //获取标题内容 （也就是第一行）
        Row rowTitle = sheet.getRow(0);
        if (rowTitle!=null){
            //重要 获取一行有多少个单元格
            int cellCount = rowTitle.getPhysicalNumberOfCells();
            for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                Cell cell = rowTitle.getCell(cellNum);
                if (cell!=null){
                    //获取单元格类型
                    int cellType = cell.getCellType();
                    //
                    String cellValue = cell.getStringCellValue();
                    System.out.print(cellValue+" | ");
                }
            }
            System.out.println();
        }
        //获取表中的内容
        //获取总行数
        int rowCount = sheet.getPhysicalNumberOfRows();
        for (int rowNum = 1; rowNum < rowCount; rowNum++) {
            Row rowData = sheet.getRow(rowNum);
            if (rowData!=null){
                //读取列
                int cellCount = rowTitle.getPhysicalNumberOfCells();
                for (int cellNum = 0; cellNum <cellCount ; cellNum++) {
                    System.out.print("["+(rowNum+1)+"-"+(cellNum+1)+"]");
                    Cell cell = rowData.getCell(cellNum);
                    //匹配列数据类型
                    if(cell!=null){
                        int cellType = cell.getCellType();
                        String cellValue="";
                        switch (cellType){
                            case HSSFCell.CELL_TYPE_STRING:
                                System.out.print("【String】");
                                cellValue=cell.getStringCellValue();
                                break;
                            case HSSFCell.CELL_TYPE_BOOLEAN:
                                System.out.print("【BOOLEAN】");
                                cellValue=String.valueOf(cell.getBooleanCellValue());
                                break;
                            case HSSFCell.CELL_TYPE_BLANK:
                                System.out.print("【BLANK】");
                                break;
                            case HSSFCell.CELL_TYPE_NUMERIC:
                                System.out.print("【NUMERIC】");
                                if (HSSFDateUtil.isCellDateFormatted(cell)){
                                    System.out.print("【日期】");
                                    Date date = cell.getDateCellValue();
                                    cellValue= new DateTime(date).toString("yyyy-MM-dd");
                                }else {
                                    //不是日期格式，防止数字过长
                                    System.out.print("【转换为字符串输出】");
                                    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                                    cellValue =cell.toString();
                                }
                                break;
                            case HSSFCell.CELL_TYPE_ERROR :
                                System.out.print("【数据类型错误】");
                        }
                        System.out.print(cellValue);
                    }
                }
            }
        }
        inputStream.close();
    }

计算公式(了解)

@Test
    public void testFormula() throws Exception{
        FileInputStream InputStream = new FileInputStream(path + "/公式.xls");
        Workbook workbook = new HSSFWorkbook(InputStream);
        Sheet sheet = workbook.getSheetAt(0);
        Row row = sheet.getRow(4);
        Cell cell = row.getCell(0);

        //拿到计算公式
        FormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);
        //输出单元格的内容
        int cellType = cell.getCellType();
        switch (cellType){
            case Cell.CELL_TYPE_FORMULA://公式
                String formula = cell.getCellFormula();
                System.out.println(formula);
                //计算
                CellValue evaluate = formulaEvaluator.evaluate(cell);
                String cellValue = evaluate.formatAsString();
                System.out.println(cellValue);
                break;
        }
        InputStream.close();
    }

EasyExcel操作

导入依赖

		<dependency>
		    <groupId>com.alibaba</groupId>
		    <artifactId>easyexcel</artifactId>
			<version>2.2.0-beta2</version>
		</dependency>
		<dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.12</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.62</version>
        </dependency>

读写测试在文档写的非常的详细。 https://www.yuque.com/easyexcel/doc/

POI及EasyExcel使用

POI和EasyExcel

POI-Excel写

POI-Excel读

EasyExcel操作