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

引言

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

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

• Apache 的 POI
• 阿里巴巴 的 EasyExcel

今天我们来了解了解 Apache POI

---

一、简介

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

结构：

• 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

2、引入依赖

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

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

<dependencies>
        <!-- xls03 -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.9</version>
        </dependency>
    
        <!-- xlsx07 -->
        <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>

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

3、步骤

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

• Excel表中的对象

  • 工作簿
  • 工作表
  • 行
  • 单元格

  

我们首先编写03版本的代码：

注：

• 03 | 07 版本的写操作，就是对象不一样，方法都是一样的。

• 2003 版本和 2007 版本存在兼容性问题， 03 版本最多只有 65535 行。

1. 创建工作簿

首先要创建一个工作簿Workbook，他有三个实现类

• 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文件

我们打开查看结果，正确

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，速度很快

查看我们生成的表

如果我们超过了65536，则报错，提示范围为0~65535

大文件写 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，耗时很慢

大文件写 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有了很大的提升

三、POI-Excel 读

实验表格会员消费商品明细表.xls：

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();
}

运行结果：

07版本

实验表格会员消费商品明细表.xlsx：

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();
}

运行结果：

不同的数据类型的读取

实验表格会员消费商品明细表.xls：

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();
}

运行结果：

含计算公式的单元格读取

实验表格计算公式.xls：

@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();
    }

运行测试，结果与表格相同：