poi笔记

poi

一、基本功能

• HSSF(03) ==> 读写 Microsoft Excel 格式档案的功能
• XSSF(07) ==> 读写 Microsoft Excel OOXML 格式档案的功能
• HWFF ==> 读写 Microsoft Word 格式档案的功能
• HSLF ==> 读写 Microsoft PowerPoint 格式档案的功能
• HDGF ==> 读写 Microsoft Visio 格式档案的功能

二、Excel对象

工作簿（整个excel文件）：

工作表（每一个sheet）：

行：

列：

三、poi -- Excel写

03版本 xls

@Test
public void testWrite03() throws IOException {
    // 1. 创建工作簿
    Workbook workbook = new HSSFWorkbook();

    // 2.创建工作表
    Sheet sheet = workbook.createSheet("个人信息统计表");

    // 3. 创建行 (1, 1)
    Row row1 = sheet.createRow(0);

    // 4. 创建一个单元格
    Cell cell1 = row1.createCell(0);
    cell1.setCellValue("姓名");

    // (1, 2)
    Cell cell2 = row1.createCell(1);
    cell2.setCellValue("张三");

    // 第二行 (2, 1)
    Row row2 = sheet.createRow(1);
    Cell cell3 = row2.createCell(0);
    cell3.setCellValue("生日");
    // (2, 2)
    Cell cell4 = row2.createCell(1);
    String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
    cell4.setCellValue(time);

    // 生成 一张表 (Io 流) 03 版本是 xls 结尾
    FileOutputStream fileOutputStream = new FileOutputStream(PATH + "个人信息统计表03.xls");
    // 输出
    workbook.write(fileOutputStream);

    // 关闭流
    fileOutputStream.close();
    System.out.println("个人信息统计03 文件生成完毕!");
}

07 版本 xlsx

@Test
public void testWrite07() throws IOException {
    // 1. 创建工作簿 07
    Workbook workbook = new XSSFWorkbook();

    // 2.创建工作表
    Sheet sheet = workbook.createSheet("个人信息统计表");

    // 3. 创建行 (1, 1)
    Row row1 = sheet.createRow(0);

    // 4. 创建一个单元格
    Cell cell1 = row1.createCell(0);
    cell1.setCellValue("姓名");

    // (1, 2)
    Cell cell2 = row1.createCell(1);
    cell2.setCellValue("张三");

    // 第二行 (2, 1)
    Row row2 = sheet.createRow(1);
    Cell cell3 = row2.createCell(0);
    cell3.setCellValue("生日");
    // (2, 2)
    Cell cell4 = row2.createCell(1);
    String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
    cell4.setCellValue(time);

    // 生成 一张表 (Io 流) 07 版本是 xlsx 结尾
    FileOutputStream fileOutputStream = new FileOutputStream(PATH + "个人信息统计表07.xlsx");
    // 输出
    workbook.write(fileOutputStream);

    // 关闭流
    fileOutputStream.close();
    System.out.println("个人信息统计03 文件生成完毕!");
}

数据批量导入

​ 大文件写 HSSF

缺点： 只能处理到65536 行，否则会抛出异常

java.lang.IllegalArgumentException: Invalid row number (65536) outside allowable range (0..65535)

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

@Test
public void testWrite03BigData() throws Exception {
    // 时间
    long start = 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 fileOutputStream = new FileOutputStream(PATH + "testWrite03BigData.xls");
    // 写出
    workbook.write(fileOutputStream);
    // 关闭流
    fileOutputStream.close();

    long end = System.currentTimeMillis();

    System.out.println("花费了" + (double)(end - start)/1000 + "秒");


}

大文件写 XSSF

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

优点：可以写较大的数据量，如 20w条

// 耗时较长
@Test
public void testWrite07BigData() throws Exception {
    // 时间
    long start = 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 fileOutputStream = new FileOutputStream(PATH + "testWrite07BigData.xlsx");
    // 写出
    workbook.write(fileOutputStream);
    // 关闭流
    fileOutputStream.close();

    long end = System.currentTimeMillis();

    System.out.println("花费了" + (double)(end - start)/1000 + "秒");


}

大文件写 SXSSF

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

注意：

过程会产生临时文件，需要清理临时文件

默认由100条被记录保存再内存中，如果超过这数量，则最前面的数据被写入临时文件。

如果想自定义内存中数据的数量，可以使用 new SXSSFWorkbook(数量)

@Test
public void testWrite07BigDataS() throws Exception {
    // 时间
    long start = 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 fileOutputStream = new FileOutputStream(PATH + "testWrite07BigDataS.xlsx");
    // 写出
    workbook.write(fileOutputStream);
    // 关闭流
    fileOutputStream.close();

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

    long end = System.currentTimeMillis();
    System.out.println("花费了" + (double)(end - start)/1000 + "秒");


}

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

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

四、poi -- Excel读

03版本

@Test
public void testRead03() throws IOException {

    // 获取文件流
    FileInputStream fileInputStream = new FileInputStream(PATH + "个人信息统计表03.xls");

    // 1. 创建工作簿
    Workbook workbook = new HSSFWorkbook(fileInputStream);
    // 2. 得到表
    Sheet sheetAt = workbook.getSheetAt(0);
    // 3. 根据表 的到行
    Row row = sheetAt.getRow(0);
    // 4. 根据行 获取单元格
    Cell cell = row.getCell(0);

    // 获取值，要注意类型 getStringCellValue 字符串类型
    //System.out.println(cell.getStringCellValue());
    //System.out.println(cell.getNumericCellValue());


    fileInputStream.close();

}

07 版本

@Test
public void testRead07() throws IOException {

    // 获取文件流
    FileInputStream fileInputStream = new FileInputStream(PATH + "个人信息统计表07.xlsx");

    // 1. 创建工作簿
    Workbook workbook = new XSSFWorkbook(fileInputStream);
    // 2. 得到表
    Sheet sheetAt = workbook.getSheetAt(0);
    // 3. 根据表 的到行
    Row row = sheetAt.getRow(0);
    // 4. 根据行 获取单元格
    Cell cell = row.getCell(0);

    // 获取值，要注意类型 getStringCellValue 字符串类型
    System.out.println(cell.getStringCellValue());
    //System.out.println(cell.getNumericCellValue());


    fileInputStream.close();

}

注意获取值得类型即可

封装工具类

读取不同的数据类型

工具类

public void testCellTypeUtils(FileInputStream fileInputStream) throws Exception {
    // 1. 创建工作簿
    Workbook workbook = new HSSFWorkbook(fileInputStream);
    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.println("字符串类型");
                            cellValue = cell.getStringCellValue();
                            break;
                        case HSSFCell.CELL_TYPE_BOOLEAN:  // 布尔
                            System.out.println("布尔类型");
                            cellValue = String.valueOf(cell.getBooleanCellValue());
                            break;
                        case HSSFCell.CELL_TYPE_BLANK:  // 空
                            System.out.println("空");
                            break;
                        case HSSFCell.CELL_TYPE_NUMERIC:  // 数字(日期、普通数字)
                            System.out.println("数字类型");
                            if (HSSFDateUtil.isCellDateFormatted(cell)){
                                // 日期
                                System.out.println("日期");
                                Date date = cell.getDateCellValue();
                                cellValue = new DateTime(date).toString("yyyy-MM-dd");
                            } else {
                                // 如果不是日期格式，防止数字过长！
                                System.out.println("转换为字符串输出");
                                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                                cellValue = cell.toString();
                            }
                            break;
                        case HSSFCell.CELL_TYPE_ERROR: // 错误
                            System.out.println("数据类型错误");
                            break;
                    }
                    System.out.println(cellValue);
                }
            }
        }
    }
    fileInputStream.close();
}

计算公式

@Test
public void testFormula() throws Exception {
    FileInputStream fileInputStream = new FileInputStream(PATH + "公式.xls");
    Workbook workbook = new HSSFWorkbook(fileInputStream);

    Sheet sheet = workbook.getSheetAt(0);

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

    // 拿到计算公式 eval
    FormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);

    //输出单元格内容
    int cellType = cell.getCellType();
    switch (cellType) {
        case Cell.CELL_TYPE_FORMULA: // 公式
            String cellFormula = cell.getCellFormula();
            System.out.println(cellFormula);

            // 计算
            CellValue evaluate = formulaEvaluator.evaluate(cell);
            String cellValue = evaluate.formatAsString();
            System.out.println(cellValue);
            break;
    }

}

五、EasyExcel 操作

• 阿里巴巴开源的一个 excel 处理框架
• EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel。
• 文档：https://www.yuque.com/easyexcel/doc/easyexcel

导入依赖

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

读操作：https://www.yuque.com/easyexcel/doc/read

写操作：https://www.yuque.com/easyexcel/doc/write