Java 操作 excel - 《JAVA》

POI方式操作 excel
easyExcel

既然 io 流都学了，那不顺便看看 java 怎么操作excel吗？
不过这些不用担心我们自己来，毕竟我们能想到的，早就有大佬造好轮子。

需要注意的是 excel 分为03版本和07版本。两个版本有着很大的差别。
03版本的 excel 以 xls 后缀结尾，最多只能存放 65536行数据
07版本的 excel 以 xlsx 后缀结尾，理论上不限制行数。

POI方式操作 excel

Apache POI是一个开源的Java读写Excel、WORD等微软OLE2组件文档的项目。

我们可以通过 poi 来达到我们的目的

环境搭建

毫无疑问，在pom 文件中引入 poi 依赖即可。

    <dependencies>
        <!--xls(03)-->
        <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.9</version>
        </dependency>
        <!--xlsx(07)-->
        <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>3.9</version>
        </dependency>
        <!--test-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
    </dependencies>

代码实现前的简单分析

要知道在 java中，一切都为对象。毫无疑问，excel 也可以被看成一个对象。那我们来分析一下这个 excel
excel 里有 sheet，也就是工作表，
工作表里有一行一行的数据，也就是 row
一行中有很多个列在 poi 里叫做cell，这个列就是单元格，我们的数据，就是放在这个 cell 中。

简单的写入

其实这两个版本在代码方面，并无太大的差别。因为 POI 提供了我们一个接口 Workbook。
03 版本和 07 版本都实现了这个接口。所以我们只需要改一下实现类即可。
当然，生成的文件后缀名也是要改的。

03版本的写入 HSSFWorkbook

    //测试 03 版本的 excel.xls
    @Test
    public void testXls03() throws IOException {
        //1.创建一个工作簿，也就是excel
        Workbook workbook = new HSSFWorkbook();
        //2.创建一个sheet，也就是工作表
        Sheet sheet = workbook.createSheet("今日份的学习");
        //3.创建行,下标从0开始，0就是excel 的第一行
        Row row1 = sheet.createRow(0);
        //4.创建列(单元格)
        //现在 这个列 就是 (1,1) 单元格
        Cell Cell11 = row1.createCell(0);
        //5.填充数据
        Cell11.setCellValue("POI的学习进度");
        //创建第一行第二列，也就是(1,2) 单元格
        Cell Cell12 = row1.createCell(1);
        Cell12.setCellValue("刚开始");
        //创建第二行
        Row row2 = sheet.createRow(1);
        Cell cell21 = row2.createCell(0);
        Cell cell22 = row2.createCell(1);
        cell21.setCellValue("esayExcel的学习进度");
        cell22.setCellValue("还没开始");
        //就在项目路径下创建一个 xxx.xls
        FileOutputStream outputStream = new FileOutputStream(LocalDate.now().toString()+"的03excel.xls");
        workbook.write(outputStream);
        outputStream.close();
    }

07版本的写入 XSSFWorkbook

    @Test
    public void test07() throws IOException {
        //1.创建一个工作簿，也就是excel
        Workbook workbook = new XSSFWorkbook();
        //2.创建一个sheet，也就是工作表
        Sheet sheet = workbook.createSheet("今日份的学习");
        //3.创建行,下标从0开始，0就是excel 的第一行
        Row row1 = sheet.createRow(0);
        //4.创建列(单元格)
        //现在 这个列 就是 (1,1) 单元格
        Cell Cell11 = row1.createCell(0);
        //5.填充数据
        Cell11.setCellValue("POI的学习进度");
        //创建第一行第二列，也就是(1,2) 单元格
        Cell Cell12 = row1.createCell(1);
        Cell12.setCellValue("刚开始");
        //创建第二行
        Row row2 = sheet.createRow(1);
        Cell cell21 = row2.createCell(0);
        Cell cell22 = row2.createCell(1);
        cell21.setCellValue("esayExcel的学习进度");
        cell22.setCellValue("还没开始");
        //就在项目路径下创建一个 xxx.xlsx
        FileOutputStream outputStream = new FileOutputStream(LocalDate.now().toString()+"的07excel.xlsx");
        workbook.write(outputStream);
        outputStream.close();
    }

区别 HSSFWorkbook 和 XSSFWorkbook

HSSF 方式，也就是03版本，只能存放 65536行数据，如果超过这个限制，会抛异常。
但他的优点是速度快，因为它会将数据直接读取到内存中，然后在最后一次性将所有数据写入硬盘。
不过在操作大数据的时候，可能会导致内存溢出。

而 XSSF 方式，也就是 07版本。写数据的时候非常缓慢，非常耗内存，但是可以写入更多的数据。
如果数据过多，也会导致内存溢出。为了解决这个问题，推荐使用 SXSSFWorkbook

SXSSFWorkbook

SXSSFWorkbook，是 POI 为了解决 XSSF 写入数据过慢而提供的一个Workbook实现类。
它的优点是速度快，也可以写入多量的数据，占用的内存也很少。
SXSSFWorkbook 采用临时文件的方式。
默认会将100条数据存放到内存中，如果超过100条，写入这个临时文件，然后继续读取。
这个临时文件需要我们清理。

但是需要注意的是，如果在 excel 中广泛使用合并单元格等操作的话，同样会占用大量的内存。

大数据的写入

通过上边讲述的HSSFWorkbook，XSSFWorkbook 和 SXSSFWorkbook的区别，我们来进行一下大数据的测试

HSSFWorkbook

因为我的这个代码存放的是机械硬盘，所以读写速度过慢。
不过也可以看到，HSSF 速度还是很快的只花费了2.229 秒
测试一下 100000 条数据，会发现抛了异常。

    //测试一下 03版本的大数据
    @Test
    public void test03BigData() throws IOException {
        long start = System.currentTimeMillis();
        //1.创建一个工作簿，也就是excel
        Workbook workbook = new HSSFWorkbook();
        //2.创建一个sheet，也就是工作表
        Sheet sheet = workbook.createSheet("大数据测试");
        // 03 版本最多只能有 65536行数据，否则会报异常
        for (int i = 0; i < 65536; i++) {
            Row row = sheet.createRow(i);
            for (int j = 0; j < 10; j++) {
                row.createCell(j).setCellValue(i);
            }
        }
        System.out.println("over");
        //就在项目路径下创建一个 xxx.xls
        FileOutputStream outputStream = new FileOutputStream(LocalDate.now().toString()+"的03bigdata.xls");
        workbook.write(outputStream);
        outputStream.close();
        long end = System.currentTimeMillis();
        System.out.println((double) (end - start) / 1000);//2.229
    }

XSSFWorkbook

在 XSSF 的方式下，10万条数据成功写入。
但是速度非常的慢，花费了11.206秒。

    @Test
    public void test07BigData() throws IOException {
        long start = System.currentTimeMillis();
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("大数据测试");
        // 07 版本 理论上是无限大的，可以存取很多行
        for (int i = 0; i < 100000; i++) {
            Row row = sheet.createRow(i);
            for (int j = 0; j < 10; j++) {
                row.createCell(j).setCellValue(i);
            }
        }
        System.out.println("over");
        //就在项目路径下创建一个 xxx.xls
        FileOutputStream outputStream = new FileOutputStream(LocalDate.now().toString()+"的07bigdata.xlsx");
        workbook.write(outputStream);
        outputStream.close();
        long end = System.currentTimeMillis();
        System.out.println((double) (end - start) / 1000);//11.206
        //可以发现，速度很慢，那有没有什么办法改善一下呢。可以使用 SXSSFWorkbook
    }

SXSSFWorkbook

使用 SXSSFWorkBook 来给 10万条数据加速。
会发现，速度提升了很多，只花费了2.806秒

    @Test
    public void test07SXSSFWorkbookBigData() throws IOException {
        long start = System.currentTimeMillis();
        Workbook workbook = new SXSSFWorkbook();
        Sheet sheet = workbook.createSheet("大数据测试");
        // 07 版本 理论上是无限大的，可以存取很多行
        for (int i = 0; i < 100000; i++) {
            Row row = sheet.createRow(i);
            for (int j = 0; j < 10; j++) {
                row.createCell(j).setCellValue(i);
            }
        }
        System.out.println("over");
        //就在项目路径下创建一个 xxx.xls
        FileOutputStream outputStream = new FileOutputStream(LocalDate.now().toString()+"SXSSFWorkbook.xlsx");
        workbook.write(outputStream);
        outputStream.close();
        long end = System.currentTimeMillis();
        //清楚临时文件
        ((SXSSFWorkbook)workbook).dispose();
        //这次就会发现，速度快了很多。
        System.out.println((double) (end - start) / 1000);//2.806
    }

读取excel

使用 iputStream 来读取刚才我们写入的数据。
要注意的是读取数据的类型。这也是读取数据时最麻烦的一点。

    @Test
    public void testRead() throws IOException {
        FileInputStream inputStream = new FileInputStream("2020-04-25的07excel.xlsx");
//        Workbook workbook = new HSSFWorkbook(inputStream);
        Workbook workbook = new XSSFWorkbook(inputStream);
        //获取 excel 的 第一张表
        Sheet sheet = workbook.getSheetAt(0);
        //获取第一行
        Row row = sheet.getRow(0);
        //获取第一个单元格
        Cell cell = row.getCell(0);
        //这里知道第一个单元格的数据为 String 类型，所以获取String 类型
        System.out.println(cell.getStringCellValue());
        inputStream.close();
    }

读取任意类型的数据

其实也很简单，因为数据类型，并不受我们控制（我们不知到excel 当前数据到底是 String 还是 int 还是 boolean）。
所以我们也没办法，只能通过 switch 来判断。
以下以 XSSFWorkbook 示例：
HSSF 同理

    @Test
    public void testReadByType() throws IOException {
        FileInputStream inputStream = new FileInputStream("2020-04-25的07excel.xlsx");
        Workbook workbook = new XSSFWorkbook(inputStream);
        //获取 excel 的 第一张表
        Sheet sheet = workbook.getSheetAt(0);
        //获取该 表 有多少行
        int rowsCount = sheet.getPhysicalNumberOfRows();
        for (int rowNum = 0; rowNum < rowsCount; rowNum++) {
            Row row = sheet.getRow(rowNum);
            //获取 该行有多少列中有数据
            int cellsCount = row.getPhysicalNumberOfCells();
            for (int cellNum = 0; cellNum < cellsCount; cellNum++) {
                Cell cell = row.getCell(cellNum);
                if (cell == null) {
                    //说明，该单元格为 空单元格，没有数据
                    cell = row.createCell(cellNum);
                }
                //判断数据的类型。
                switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_STRING :
                        //字符串类型
                        System.out.println(cell.getStringCellValue());
                        break;
                    case Cell.CELL_TYPE_BOOLEAN :
                        //boolean类型
                        System.out.println(cell.getBooleanCellValue());
                        break;
                    case Cell.CELL_TYPE_BLANK :
                        System.out.println("这是一个空的单元格");
                        break;
                    case Cell.CELL_TYPE_NUMERIC :
                        //当type是numberic的时候，又分为数字格式和日期格式。
                        boolean isDate = DateUtil.isCellDateFormatted(cell);
                        if (isDate) {
                            //时间类型
                            Date date = cell.getDateCellValue();
                            LocalDate localDate = date.toInstant().atZone(ZoneId.systemDefault()).toLocalDate();
                            System.out.println(localDate);
                        }else {
                            //数字类型
                            System.out.println(cell.getNumericCellValue());
                        }
                        break;
                }
            }
        }
        inputStream.close();
    }

关于 excel 中的公式

有的时候 excel 单元格里存放的是公式
比如：

这个时候我们怎么获取并且得到结果呢？
我们在 POI 中也可以获取计算公式 FormulaEvaluator
然后通过 evaluate方法获取计算结果

    @Test
    public void testFormula() throws IOException {
        FileInputStream inputStream = new FileInputStream("2020-04-25的07excel.xlsx");

        Workbook workbook = new XSSFWorkbook(inputStream);
        //获取 excel 的 第一张表
        Sheet sheet = workbook.getSheetAt(0);

        Row row = sheet.getRow(4);
        Cell cell = row.getCell(1);

        //拿到计算公式
        FormulaEvaluator formulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
        switch (cell.getCellType()) {
            //可以这样写特定的类型XSSFCell
            //case XSSFCell.CELL_TYPE_FORMULA
            //不过最好建议这样写
            case Cell.CELL_TYPE_FORMULA :
                System.out.println("这是一个公式类型");
                //执行公式
                CellValue cellValue = formulaEvaluator.evaluate(cell);
                //输出执行结果
                System.out.println(cellValue.formatAsString());
                break;
        }
        inputStream.close();
    }

easyExcel

easyExcel 是阿里巴巴开发的用来解析 excel的工具，比 POI 更快，更简单，而且一定不发产生你内存溢出的状况
easyExcel github地址
 easyExcel 语雀文档
非常建议了解一下。
文档很详细。
建议直接看文档

引入相关依赖

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