Apache POI：解决数据库和Excel之间相互转换的烦恼~

Dear 丶 2022-12-03 08:43 189阅读 0赞

### 目录 ###

*  引言
 *  一、简介
 *  二、POI-Excel 写
 *   *  1、创建项目
     *  2、引入依赖
     *  3、步骤
     *   *  1. 创建工作簿
         *  2. 创建工作表
         *  3. 创建行
         *  4. 创建单元格
         *  5. 单元格中填入数据
         *  6. 通过IO流生成表
     *  03版本完整代码
     *  07版本完整代码
     *  大文件写 HSSF（03）
     *  大文件写 XSSF（07）
     *  大文件写 SXSSF（07）
 *  三、POI-Excel 读
 *   *  03版本
     *  07版本
     *  不同的数据类型的读取
     *  含计算公式的单元格读取

# 引言 #

> 开发中经常会设计到 Excel 的处理，如导出 Excel，导入Excel 到数据库中！

关于表格导入导出第三方组件工具，市面上比较知名的有两个：

*  Apache 的 `POI`
 *  阿里巴巴 的 `EasyExcel`

今天我们来了解了解 `Apache POI`

--------------------

# 一、简介 #

**官网**： http://poi.apache.org/

![image-20200831223244029][]

**结构**：

*  HSSF － 提供读写`Microsoft Excel`格式档案的功能。excel 2003 版本
 *  XSSF － 提供读写`Microsoft Excel` [OOXML][]格式档案的功能。excel 2007 版本
 *  HWPF － 提供读写`Microsoft Word`格式档案的功能。
 *  HSLF － 提供读写`Microsoft PowerPoint`格式档案的功能。
 *  HDGF － 提供读写`Microsoft Visio`格式档案的功能。

# 二、POI-Excel 写 #

## 1、创建项目 ##

> 创建一个空项目和一个普通Maven的Module  
> ![image-20200901162319746][]

## 2、引入依赖 ##

> Excel有两种不同的版本，需要导入两种不同的依赖

*  Excel `2003` 版本的后缀名为`.xls`
 *  Excel `2007` 版本的后缀名为`.xlsx`

<dependencies>
            
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
                <version>3.9</version>
            </dependency>
        
            
            <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>
    
            
            <dependency>
                <groupId>junit</groupId>
                <artifactId>junit</artifactId>
                <version>4.12</version>
            </dependency>
        </dependencies>

## 3、步骤 ##

Java万物都是对象，所以用Java操作Excel，就是操作其中一个一个的对象

*  Excel表中的对象
    
     *  工作簿
     *  工作表
     *  行
     *  单元格
    
    ![image-20200901160057075][]

> 我们首先编写03版本的代码：
> 
> 注：
> 
>  *  03 | 07 版本的写操作，就是对象不一样，方法都是一样的。
>  *  2003 版本和 2007 版本存在兼容性问题， 03 版本最多只有 65535 行。

### 1. 创建工作簿 ###

首先要创建一个工作簿`Workbook`，他有三个实现类  
![image-20200901160326275][]

*  `HSSFWorkbook`：操作03版本的Excel
 *  `SXSSFWorkbook`：`XSSFWorkbook`的升级版，加速处理
 *  `XSSFWorkbook`：操作07版本的Excel

操作07版本的Excel时，我们一般使用`SXSSFWorkbook`

//创建一个03版本的工作簿
    Workbook workbook = new HSSFWorkbook();

### 2. 创建工作表 ###

再通过工作簿创建工作表

//创建一个工作表
    Sheet sheet=workbook.createSheet("POI写操作");

### 3. 创建行 ###

再通过工作表创建行

//创建行
    Row row1 = sheet.createRow(0);//第一行
    Row row2 = sheet.createRow(1);//第二行

### 4. 创建单元格 ###

最后通过行创建单元格

//创建单元格
    Cell cell11 = row1.createCell(0);//(1,1)
    Cell cell12 = row1.createCell(1);//(1,2)

### 5. 单元格中填入数据 ###

//向单元格中写入数据
    cell11.setCellValue("POI");
    cell12.setCellValue("first");

### 6. 通过IO流生成表 ###

//生成一张表(IO流)
    String path = "D:\\学习\\IDEA project\\POI";
    FileOutputStream fileOutputStream = new FileOutputStream(path + "03.xls");
    workbook.write(fileOutputStream);
    //关闭流
    fileOutputStream.close();

--------------------

## 03版本完整代码 ##

> 将上述代码汇总

@Test
    public void testWrite03() throws Exception { 
        //创建一个03版本的工作簿
        Workbook workbook = new HSSFWorkbook();
        //创建一个工作表
        Sheet sheet = workbook.createSheet("POI写操作");
        //创建行
        Row row1 = sheet.createRow(0);//第一行
        Row row2 = sheet.createRow(1);//第二行
        //创建单元格
        Cell cell11 = row1.createCell(0);//(1,1)
        Cell cell12 = row1.createCell(1);//(1,2)
        //向单元格中写入数据
        cell11.setCellValue("POI");
        cell12.setCellValue("first");
        //生成一张表(IO流)
        String path = "D:\\学习\\IDEA project\\POI";
        FileOutputStream fileOutputStream = new FileOutputStream(path + "03.xls");
        workbook.write(fileOutputStream);
        //关闭流
        fileOutputStream.close();
        System.out.println("03表生成完毕");
    }

然后点击运行，可以看到左边新生成的`.xls`文件  
![image-20200901164157746][]  
我们打开查看结果，正确  
![image-20200901164229645][]

## 07版本完整代码 ##

> 同样，以此类推，我们写出07版本的代码

@Test
    public void testWrite07() throws Exception { 
        //创建一个07版本的工作簿
        Workbook workbook = new SXSSFWorkbook();
        //创建一个工作表
        Sheet sheet = workbook.createSheet("POI写操作2");
        //创建行
        Row row = sheet.createRow(0);
        //创建单元格
        Cell cell11 = row.createCell(0);
        Cell cell12 = row.createCell(1);
        //向单元格中写入数据
        cell11.setCellValue("POI");
        cell12.setCellValue("second");
        //生成一张表(IO流)
        String path = "D:/学习/IDEA project/POI/";
        FileOutputStream fileOutputStream = new FileOutputStream(path + "07.xlsx");
        workbook.write(fileOutputStream);
        //关闭流
        fileOutputStream.close();
        System.out.println("07表生成完毕");
    }

## 大文件写 HSSF（03） ##

\*\*缺点：\*\*最多只能处理 65536 行，否则会抛出异常

\*\*优点：\*\*过程中写入缓存，不操作磁盘，最后一次性写入磁盘，速度快。

@Test
    public void HSSFWriteBigData() throws Exception { 
        //起始时间
        long begin = System.currentTimeMillis();
        //创建工作簿
        Workbook workbook = new HSSFWorkbook();
        //创建工作表
        Sheet sheet = workbook.createSheet("大数据写HSSF");
        //写入数据
        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);
            }
        }
        //生成表
        String path = "D:/学习/IDEA project/POI/";
        FileOutputStream fileOutputStream = new FileOutputStream(path + "BigData03.xls");
        workbook.write(fileOutputStream);
        fileOutputStream.close();
        //结束时间
        long end = System.currentTimeMillis();
        System.out.println(end - begin + "ms");
        System.out.println("BigData03表生成完毕");
    }

点击运行，整个过程运行时间为1.93s，速度很快  
![image-20200901203939874][]  
查看我们生成的表  
![image-20200901204011750][]  
如果我们超过了65536，则报错，提示范围为`0~65535`  
![image-20200901204115763][]

## 大文件写 XSSF（07） ##

\*\*缺点：\*\*写数据时速度非常慢，非常耗内存，也会发生内存溢出，如 100 万条数据。

\*\*优点：\*\*可以写较大的数据量，如 20 万条。

@Test
    public void XSSFWriteBigData() throws Exception { 
        //起始时间
        long begin = System.currentTimeMillis();
        //创建工作簿
        Workbook workbook = new XSSFWorkbook();
        //创建工作表
        Sheet sheet = workbook.createSheet("大数据写XSSF");
        //写入数据
        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);
            }
        }
        //生成表
        String path = "D:/学习/IDEA project/POI/";
        FileOutputStream fileOutputStream = new FileOutputStream(path + "BigData07.xlsx");
        workbook.write(fileOutputStream);
        fileOutputStream.close();
        //结束时间
        long end = System.currentTimeMillis();
        System.out.println(end - begin + "ms");
        System.out.println("BigData07表生成完毕");
    }

点击运行，整个过程报名时间为7s，耗时很慢  
![image-20200901204634712][]

## 大文件写 SXSSF（07） ##

**优点：**

*  可以写非常大的数据量，如 100 万条甚至更多，写数据速度快，占用更少的内存。

**注意：**

*  过程总会产生`临时文件`，需要清理临时文件。
 *  默认由 100 条记录被保存在内存中，则最前面的数据被写入临时文件。
 *  如果想要自定义内存中数据的数量，可以使用 new SXSSFWorkbook (数量)。

SXSSFWorkbook 来自官方的解释：实现“BigGridDemo” 策略的流式 SXSSFWorkbook 版本。这允许写入非常大的文件而不会耗尽内存，因为任何时候只有可配置的行部分被保存在内存中。

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

@Test
    public void SXSSFWriteBigData() throws Exception { 
        //起始时间
        long begin = System.currentTimeMillis();
        //创建工作簿
        Workbook workbook = new SXSSFWorkbook();
        //创建工作表
        Sheet sheet = workbook.createSheet("大数据写XSSF");
        //写入数据
        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);
            }
        }
        //生成表
        String path = "D:/学习/IDEA project/POI/";
        FileOutputStream fileOutputStream = new FileOutputStream(path + "BigDataS07.xlsx");
        workbook.write(fileOutputStream);
        fileOutputStream.close();
        //清除临时文件
        ((SXSSFWorkbook) workbook).dispose();
        //结束时间
        long end = System.currentTimeMillis();
        System.out.println(end - begin + "ms");
        System.out.println("BigDataS07表生成完毕");
    }

点击运行，整个过程运行时间为2.10s，速度相比`XSSF`有了很大的提升  
![image-20200901205436885][]

# 三、POI-Excel 读 #

实验表格`会员消费商品明细表.xls`：  
![format_png][]

## 03版本 ##

String path = "D:/学习/IDEA project/POI/会员消费商品明细表.xls";
    
    @Test
    public void testRead03() throws Exception { 
        //获取文件流
        FileInputStream fileInputStream = new FileInputStream(path);
        //创建工作簿,传入文件流
        Workbook workbook = new HSSFWorkbook(fileInputStream);
        //得到工作表
        Sheet sheet = workbook.getSheetAt(0);
        //得到行
        Row row = sheet.getRow(0);
        //得到列
        Cell cell = row.getCell(0);
        //注意获取值的类型，不同类型，需要调用不同的方法
        System.out.println(cell.getStringCellValue());
        //关闭流
        fileInputStream.close();
    }

运行结果：  
![image-20200901225846714][]

## 07版本 ##

实验表格`会员消费商品明细表.xlsx`：  
![format_png][]

String path = "D:/学习/IDEA project/POI/会员消费商品明细表.xlsx";
    
    @Test
    public void testRead03() throws Exception { 
        //获取文件流
        FileInputStream fileInputStream = new FileInputStream(path);
        //创建工作簿,传入文件流
        Workbook workbook = new XSSFWorkbook(fileInputStream);
        //得到工作表
        Sheet sheet = workbook.getSheetAt(0);
        //得到行
        Row row = sheet.getRow(0);
        //得到列
        Cell cell = row.getCell(0);
        //注意获取值的类型，不同类型，需要调用不同的方法
        System.out.println(cell.getStringCellValue());
        //关闭流
        fileInputStream.close();
    }

运行结果：  
![image-20200901225846714][]

## 不同的数据类型的读取 ##

实验表格`会员消费商品明细表.xls`：  
![image-20200901225812273][format_png]

String path = "D:/学习/IDEA project/POI/会员消费商品明细表.xls";
    
    @Test
    public void ReadDifferentType() throws Exception { 
        //获取文件流
        FileInputStream fileInputStream = new FileInputStream(path);
        //创建工作簿,传入文件流
        Workbook workbook = new HSSFWorkbook(fileInputStream);
        //得到工作表
        Sheet sheet = workbook.getSheetAt(0);
        //得到行
        //遍历标题行(第一行)每个单元格，输出其内容
        Row row = sheet.getRow(0);
        if (row != null) { 
            int cellCount = row.getPhysicalNumberOfCells();//获取单元格数量
            for (int cellNum = 0; cellNum < cellCount; cellNum++) { //遍历单元格
                Cell cell = row.getCell(cellNum);
                if (cell != null) { 
                    int cellType = cell.getCellType();//获得单元格内容数据类型
                    String cellValue = cell.getStringCellValue();
                    System.out.print(" | " + cellValue);
                }
            }
        }
        //获取表中的内容
        int numberOfRows = sheet.getPhysicalNumberOfRows();//获得总行数
        for (int i = 1; i < numberOfRows; i++) { //从第二行开始遍历所有的行
            Row row2 = sheet.getRow(i);
            if (row2 != null) { 
                int numberOfCells = row2.getPhysicalNumberOfCells();//获取每行单元格数量
                for (int j = 0; j < numberOfCells; j++) { 
                    Cell cell = row2.getCell(j);
                    if (cell != null) { 
                        int cellType = cell.getCellType();
                        String cellValue = "";//单元格内容
                        switch (cellType) { 
                                //字符串
                            case Cell.CELL_TYPE_STRING:
                                System.out.print("【STRING】");
                                cellValue = cell.getStringCellValue();
                                break;
                                //布尔
                            case Cell.CELL_TYPE_BOOLEAN:
                                System.out.print("【BOOLEAN】");
                                cellValue = String.valueOf(cell.getBooleanCellValue());
                                break;
                                //空
                            case Cell.CELL_TYPE_BLANK:
                                System.out.print("【BLANK】");
                                break;
                                //数字
                            case Cell.CELL_TYPE_NUMERIC:
                                System.out.print("【NUMERIC】");
                                if (DateUtil.isCellDateFormatted(cell)) { //日期
                                    System.out.print("【日期】");
                                    Date date = cell.getDateCellValue();
                                    cellValue = new DateTime(date).toString();
                                } else { 
                                    // 不是日期格式，防止数字过长
                                    System.out.print("【装换为字符串输出】");
                                    cell.setCellType(Cell.CELL_TYPE_STRING);
                                    cellValue = cell.toString();
                                }
                                break;
                            case HSSFCell.CELL_TYPE_ERROR:
                                System.out.print("【数据类型错误】");
                                break;
                        }
                        System.out.println(cellValue);
                    }
                }
            }
        }
        //关闭流
        fileInputStream.close();
    }

运行结果：  
![image-20200901232504996][]

## 含计算公式的单元格读取 ##

实验表格`计算公式.xls`：  
![image-20200901232746843][]

@Test
        public void Formula() throws Exception { 
            String path = "D:/学习/IDEA project/POI/计算公式.xls";
            //获取文件流
            FileInputStream fileInputStream = new FileInputStream(path);
            //创建工作簿,传入文件流
            Workbook workbook = new HSSFWorkbook(fileInputStream);
            //得到工作表
            Sheet sheet = workbook.getSheetAt(0);
            //得到行(有公式的那一行)
            Row row = sheet.getRow(4);
            //得到计算公式
            FormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);
            //获取单元格
            Cell cell = row.getCell(0);
            //通过公式计算输出单元格的内容
            int cellType = cell.getCellType();
            if (cellType == Cell.CELL_TYPE_FORMULA) { 
                String cellFormula = cell.getCellFormula();
                System.out.println(cellFormula);
                //计算并打印计算结果
                CellValue value = formulaEvaluator.evaluate(cell);
                String cellValue = value.formatAsString();
                System.out.println(cellValue);
            }
            //关闭流
            fileInputStream.close();
        }

运行测试，结果与表格相同：  
![image-20200901234439359][]

[image-20200831223244029]: /images/20221123/484e6c5237ba4b3ea851fa8eef8b39ce.png
[OOXML]: https://baike.baidu.com/item/OOXML
[image-20200901162319746]: https://gitee.com/zhong_siru/images/raw/master//img/image-20200901162319746.png
[image-20200901160057075]: /images/20221123/05d32751133440a08973b96b1be3ce48.png
[image-20200901160326275]: /images/20221123/1f754088635a403fbf1508fb741ce2ae.png
[image-20200901164157746]: /images/20221123/178c78f59b3742f895d9eba99698e01e.png
[image-20200901164229645]: /images/20221123/77e92781d44d414c8f1e059237eb05bc.png
[image-20200901203939874]: https://gitee.com/zhong_siru/images/raw/master//img/image-20200901203939874.png
[image-20200901204011750]: /images/20221123/c0cc12c5b3fb4504a3de8c4178fc69ff.png
[image-20200901204115763]: /images/20221123/f11c186486444a89a60b4de87f29da61.png
[image-20200901204634712]: https://gitee.com/zhong_siru/images/raw/master//img/image-20200901204634712.png
[image-20200901205436885]: https://gitee.com/zhong_siru/images/raw/master//img/image-20200901205436885.png
[format_png]: /images/20221123/5d12a647fcb047aabed8fa0fd41d6d29.png
[image-20200901225846714]: https://gitee.com/zhong_siru/images/raw/master//img/image-20200901225846714.png
[image-20200901232504996]: /images/20221123/0649e2265d8e42a4821782b776e0b229.png
[image-20200901232746843]: https://gitee.com/zhong_siru/images/raw/master//img/image-20200901232746843.png
[image-20200901234439359]: https://gitee.com/zhong_siru/images/raw/master//img/image-20200901234439359.png