使用java读写Excel表格文档

前言: 在我们实际开发中, 难免会遇到类似把数据库中的数据生成excel文档, 或者读取excel中的数据存入到数据库等需求, 所以我们才会学习POI, 阿里云基于POI封装了EasyExcel, 方便了开发人员的使用以及弥补一些POI的安全和性能问题

POI

POI是Apache软件基金会的，POI为“Poor Obfuscation Implementation”的首字母缩写，意为“简洁版的模糊实现”。
所以POI的主要功能是可以用Java操作Microsoft Office的相关文件，这里我们主要讲Excel

1. 写入

1.1 pom依赖

<!--2003(xls)-->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.17</version>
</dependency>
<!--2007(xlsx)版本-->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.17</version>
</dependency>

所谓03版和07版最大的区别在于 03版表格最多支持65536行, 而07版则没有上限

1.2 基本功能

​ 结构

• HSSF - 提供读写Microsoft Excel格式文档的功能 (03版)
• XSSF - 提供读写Microsoft Excel OOXML格式文档的功能(07版)
• HWPF - 提供读写Microsoft Word格式文档的功能(word文档)
• HSLF - 提供读写Microsoft PowerPoint格式文档的功能(PPT幻灯片)
• HDGF - 提供读写Microsoft Visio格式文档的功能

1.3 Workbook接口的三个实现类

实现类	描述
HSSFWorkbook()	03版写入
XSSFWorkbook()	07版写入 (把所有的数据保存到内存 ,再写入到文件. 可能发生内存泄露)
SXSSFWorkbook()	写入速度快, 占用更少的内存. 中间会产生一个临时文件, 需要清理临时文件, 默认由100条记录被保存到内存中, 如果超出这个范围,则被写入到临时文件中,如果想自 定义内存中的数量 ,可以 new SXSSFWorkbook(数量) 来进行定义

1.4 写入文件演示 (03版)

// 生成文件路径定义
String PATH = "/home/zpk/DEV/idea/workspace/poi/";

@Test
public void testWrite03() throws Exception {
    // 1. 创建一个工作簿
    Workbook workbook = new HSSFWorkbook();
    // 2. 创建一个工作表
    Sheet sheet = workbook.createSheet("统计表");

    // 3. 创建一个行 (第一行)
    Row row1 = sheet.createRow(0);
    // 创建单元格(1,1)
    Cell cell11 = row1.createCell(0);
    // 单元格(1,1)写入内容
    cell11.setCellValue("今日新增观众");
    // 创建单元格(1,2)
    Cell cell12 = row1.createCell(1);
    // 单元格(1,2)写入内容
    cell12.setCellValue(666);

    // 第二行
    Row row2 = sheet.createRow(1);
    Cell cell21 = row2.createCell(0);
    cell21.setCellValue("统计时间");
    Cell cell22 = row2.createCell(1);
    String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
    cell22.setCellValue(time);

    // 生成一张表(IO流) 03 版本就是使用xls结尾
    FileOutputStream fileOutputStream = new FileOutputStream(PATH + "统计表03版.xls");
    // 写出文件
    workbook.write(fileOutputStream);
    // 关闭流
    fileOutputStream.close();

    System.out.println("文件生成完毕");

}

1.5 写入文件演示 (07版)

// 生成文件路径定义
String PATH = "/home/zpk/DEV/idea/workspace/poi/";

@Test
public void testWrite07() throws Exception {
    // 1. 创建一个工作簿
    Workbook workbook = new XSSFWorkbook();
    // 2. 创建一个工作表
    Sheet sheet = workbook.createSheet("统计表");

    // 3. 创建一个行 (第一行)
    Row row1 = sheet.createRow(0);
    // 创建单元格(1,1)
    Cell cell11 = row1.createCell(0);
    // 单元格(1,1)写入内容
    cell11.setCellValue("今日新增观众");
    // 创建单元格(1,2)
    Cell cell12 = row1.createCell(1);
    // 单元格(1,2)写入内容
    cell12.setCellValue(666);

    // 第二行
    Row row2 = sheet.createRow(1);
    Cell cell21 = row2.createCell(0);
    cell21.setCellValue("统计时间");
    Cell cell22 = row2.createCell(1);
    String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
    cell22.setCellValue(time);

    // 生成一张表(IO流) 03 版本就是使用xls结尾
    FileOutputStream fileOutputStream = new FileOutputStream(PATH + "统计表07版.xlsx");
    // 写出文件
    workbook.write(fileOutputStream);
    // 关闭流
    fileOutputStream.close();

    System.out.println("文件生成完毕");

}

可见 若生成07版只需要把Workbook workbook = new HSSFWorkbook();改为 Workbook workbook = new XSSFWorkbook(); 并且生成的文件后缀改为xlsx就可以了 ,这就是面向借口编程的好处

1.6 (HSSF) 03版写入大量数据

默认写入65536行数据, 查看所需要的时间

// 生成文件路径定义
String PATH = "/home/zpk/DEV/idea/workspace/poi/";

@Test
public void testWrite03BigData() throws Exception {

    long begin = System.currentTimeMillis(); // 开始时间

    // 创建一个簿
    Workbook workbook = new HSSFWorkbook();
    // 创建表
    Sheet sheet = workbook.createSheet();
    // 写入数据
    for (int rowNum = 0; rowNum < 65536; rowNum++) {
        // 一共65536行
        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 fileOutputStream = new FileOutputStream(PATH + "bigData03");
    // 输出
    workbook.write(fileOutputStream);
    // 关闭流
    fileOutputStream.close();

    long end = System.currentTimeMillis(); // 结束时间
    // 时间结算
    System.out.println((double)(end - begin)/1000 + " s");
}

执行结果: 总共耗时2.036秒

over
2.036 s

注意: 03版如果行数大于65536则抛出异常

1.7 (XSSF) 07版写入大量数据

// 生成文件路径定义
String PATH = "/home/zpk/DEV/idea/workspace/poi/";

@Test
public void testWrite07BigData() throws Exception {
    // 时间
    long begin = System.currentTimeMillis();

    // 创建一个簿
    Workbook workbook = new XSSFWorkbook();
    // 创建表
    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 fileOutputStream = new FileOutputStream(PATH + "bigData07.xlsx");
    workbook.write(fileOutputStream);
    fileOutputStream.close();

    long end = System.currentTimeMillis();
    System.out.println((double)(end - begin)/1000 + " s");
}

执行结果: 总共耗时8.015 秒

over
8.015 s

1.8 (SXSSF) 写入大量数据

// 生成文件路径定义
String PATH = "/home/zpk/DEV/idea/workspace/poi/";

@Test
public void testWriteSXSSFBigData() throws Exception {
    // 时间
    long begin = System.currentTimeMillis();

    // 创建一个簿
    Workbook workbook = new SXSSFWorkbook();
    // 创建表
    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 fileOutputStream = new FileOutputStream(PATH + "bigDataSXSSF.xlsx");
    workbook.write(fileOutputStream);
    fileOutputStream.close();

    // 清除临时文件
    ((SXSSFWorkbook) workbook).dispose();

    long end = System.currentTimeMillis();
    System.out.println((double)(end - begin)/1000 + " s");
}

结果: 2.192 秒

over
2.192 s

注意: 使用SXSSFWorkbook时需要清理临时文件

// 清除临时文件
((SXSSFWorkbook) workbook).dispose();

1.9 写入大量数据结论

• HSSF 写入大量数据v

  • 优点: 可以写入快速写入数据
  • 缺点: 只能写入65536行数据, 非常有限

• XSSF 写入大量数据

  • 优点: 可以写入任意条数据

  • 缺点: 写入速度慢的一批

• SXSSF 写入大量数据

  • 优点: 快速而且占用内存少
  • 缺点: 仍然可能会消耗大量内存, 这些内存基于正在使用的功能,例如合并区域, 注释仍然会储存在内存中, 因此如果广泛使用, 可能需要大量的内存

2. 读取

2.1读取文件演示(03版)

// 路径
String PATH = "/home/zpk/DEV/idea/workspace/poi/";

@Test
public void testRead03() throws Exception {
    // 获取文件流
    FileInputStream inputStream = new FileInputStream(PATH + "统计表03版.xls");
    // 1. 创建一个工作簿
    Workbook workbook = new HSSFWorkbook(inputStream);
    // 2. 获取一个工作表, getSheetAt()方法是根据下标获取
    Sheet sheet = workbook.getSheetAt(0);
    // 3. 得到行
    Row row = sheet.getRow(0);
    // 4. 得到单元格
    Cell cell = row.getCell(0);

    // 显示(1,1)的内容, 获取字符串
    System.out.println(cell.getStringCellValue());

    // 关闭流
    inputStream.close();
}

结果: (获取到了上面写入中的1.4中生成的文件)

今日新增观众

2.2 读取文件演示(07版)

// 路径
String PATH = "/home/zpk/DEV/idea/workspace/poi/";

@Test
public void testRead07() throws Exception {
    // 获取文件流
    FileInputStream inputStream = new FileInputStream(PATH + "统计表07版.xlsx");
    // 1. 创建一个工作簿
    Workbook workbook = new XSSFWorkbook(inputStream);
    // 2. 获取一个工作表, getSheetAt()方法是根据下标获取
    Sheet sheet = workbook.getSheetAt(0);
    // 3. 得到行
    Row row = sheet.getRow(0);
    // 4. 得到单元格
    Cell cell = row.getCell(1);

    // 显示(1,1)的内容, 获取数字类型
    System.out.println(cell.getNumericCellValue());

    // 关闭流
    inputStream.close();
}

结果: (获取到了上面写入中的1.5中生成的文文件)

666.0

2.3 获取一行的数据(重点)

Row rows = sheet.getRow(0); // 获取第一行
if (rows != null) {
    int cellCount = rows.getPhysicalNumberOfCells(); // 计数,次行多少列存入cellCount中
    for (int cellNum = 0; cellNum < cellCount; cellNum++){ // 遍历列
        Cell cell = rows.getCell(cellNum); // 获取每一列
        if (cell != null) {
            int cellType = cell.getCellType(); // 每一列的数据类型(枚举)
            String cellValue = cell.getStringCellValue(); // 获取字符串内容
            System.out.print(cellValue + " | "); // 分割线
        }
    }
    System.out.println() // 换行
}

2.4 获取整张表的内容

(工具类), 只需要传入输入流即可

// 提取为工具类
public void read07Excel(FileInputStream inputStream) throws Exception {
    // 1. 创建一个工作簿
    Workbook workbook = new XSSFWorkbook(inputStream);
    // 2. 获取一个工作表, getSheetAt()方法是根据下标获取
    Sheet sheet = workbook.getSheetAt(0);


    Row rows = sheet.getRow(0); // 获取第一行
    if (rows != null) {
        int cellCount = rows.getPhysicalNumberOfCells(); // 计数,次行多少列存入cellCount中
        for (int cellNum = 0; cellNum < cellCount; cellNum++){ // 遍历列
            Cell cell = rows.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 = rows.getPhysicalNumberOfCells();
            for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                System.out.println("[" + (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.getStringCellValue());
                            break;
                        case HSSFCell.CELL_TYPE_BLANK : // 空
                            System.out.print("[Blank]");
                            break;
                        case HSSFCell.CELL_TYPE_NUMERIC : // 数字(日期和普通数字
                            System.out.print("[Number]");
                            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 = String.valueOf(cell.getStringCellValue());
                                cellValue = cell.toString();
                            }
                            break;
                        case HSSFCell.CELL_TYPE_ERROR : // 布尔型
                            System.out.print("[数据类型错误]");
                            break;
                    }
                    System.out.println(cellValue);
                }
            }
        }
    }

    // 关闭流
    inputStream.close();
}

EasyExcel

这是阿里云的EasyExcel, 性能和读写速度都很不错

官方文档: https://www.yuque.com/easyexcel/doc/easyexcel

github地址:https://github.com/alibaba/easyexcel

pom依赖

<dependency>   
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.2.0-beta2</version>
</dependency>

官方文档已经写的很详细, 这里就不一一列举了. ^v