杰霸霸博客

主题

POI ​

1. 简介 ​

Apache POl是基于Office Open XML标准(O0XML)和Microsot的OLE2复合文档格式(OLE2)处理各种文件格式的开源项目。简而言之，您可以使用java读写MS Excel文件，可以使用java读写MS Word和MS PowerPoint文件。

2. 模块 ​

  1. HSSF：提供读写Microsoft Excel XLS格式 (Microsoft Excel 97(-2003))档案的功能。
  2. XSSF：提供读写Microsoft Excel OOXML XLSX格式(Microsoft Excel XML(2007+))档案的功能。
  3. SXSSF：提供低内存占用量读写 Microsoft Excel OOXML XLSX 格式档案的功能。
  4. HWPF：提供读写Microsoft Word DOC97格式 (Microsoft Word 97 (-2003)档案的功能。
  5. XWPF：提供读写Microsoft Word DOC2003格式(WordprocessingML(2007+))档案的功能。
  6. HSLF/XSLF：提供读写Microsoft PowerPoint格式档案的功能。
  7. HDGF/XDGF：提供读Microsoft Visio格式档案的功能。
  8. HPBF：提供读Microsoft Publisher格式档案的功能。
  9. HSMF：提供读Microsoft Outlook格式档案的功能。

3. POI存在的问题 ​

Java解析、生成Excel比较有名的框架有PO1、JXL。但是它们都存在一个严重的问题，就是非常损耗内存，也就是数据量比较大的时候有可能会出现OOM的问题，但是POI有一套SAX模式的API可以一定程度的解决一些内存溢出的问题，但是依旧没有完全的解决内存消耗过大的问题。

4. Excel03与07版本区别 ​

POI可以支持不同版本Excel表格的操作，对应的版本有03和07+版本，那么这两种表格的区别如下: 03版本:最多行数支持:65536，并且后缀为.xls。
07+版本:理论上没有限制，但实际支持行数为:1048576，并且后缀为.xlsx
Alt text

5. POI操作Excel ​

添加pom依赖：

xml
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.4.0</version>
</dependency>

5.1 读写03版本Excel ​

  1. 写文件
java
String filePath = "D:\\03excelData.xls";
// 创建工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
// 创建工作表
HSSFSheet sheet = workbook.createSheet("测试");
// 创建行
HSSFRow headRow = sheet.createRow(0);
// 创建单元格
headRow.createCell(0, CellType.STRING).setCellValue("商品ID");
headRow.createCell(1, CellType.STRING).setCellValue("商品名称");

HSSFRow dataRow = sheet.createRow(1);
dataRow.createCell(0, CellType.NUMERIC).setCellValue(1.123);
dataRow.createCell(1, CellType.STRING).setCellValue("test01");
try(FileOutputStream outputStream = new FileOutputStream(filePath)){
    workbook.write(outputStream);
} catch (IOException e) {
    throw new RuntimeException(e);
}
  1. 读文件
java
public static void main(String[] args) throws IOException {
    String filePath = "D:\\03excelData.xls";
    FileInputStream inputStream = new FileInputStream(filePath);
    HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
    // 通过索引下标或者sheet的名称
    HSSFSheet sheet = workbook.getSheetAt(0);
    int lastRowNum = sheet.getLastRowNum();
    for (int i = 0; i <= lastRowNum; i++) {
        HSSFRow row = sheet.getRow(i);
        int cellCount = row.getPhysicalNumberOfCells();
        for (int j = 0; j < cellCount; j++) {
            HSSFCell cell = row.getCell(j);
            CellType cellType = cell.getCellType();
            if(cellType.equals(CellType.STRING)){
                System.out.println(cell.getStringCellValue());
            } else if (cellType.equals(CellType.NUMERIC)) {
                System.out.println(cell.getNumericCellValue());
            }else{
                System.out.println(cell.getStringCellValue());
            }
        }
    }
}

5.2 读写07版本Excel ​

  1. 写文件
java
public static void main(String[] args) throws IOException {
    String filePath = "D:\\07excelData.xlsx";
    // 创建工作簿
    XSSFWorkbook workbook = new XSSFWorkbook();
    // 创建工作表
    XSSFSheet sheet = workbook.createSheet("测试");
    // 创建行
    XSSFRow headRow = sheet.createRow(0);
    // 创建单元格
    headRow.createCell(0, CellType.STRING).setCellValue("商品ID");
    headRow.createCell(1, CellType.STRING).setCellValue("商品名称");

    XSSFRow dataRow = sheet.createRow(1);
    dataRow.createCell(0, CellType.NUMERIC).setCellValue(1.123);
    dataRow.createCell(1, CellType.STRING).setCellValue("test01");
    try(FileOutputStream outputStream = new FileOutputStream(filePath)){
        workbook.write(outputStream);
    } catch (IOException e) {
        throw new RuntimeException(e);
    }
}
  1. 读文件
java
public static void main(String[] args) throws IOException {
    String filePath = "D:\\07excelData.xlsx";
    FileInputStream inputStream = new FileInputStream(filePath);
    XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
    // 通过索引下标或者sheet的名称
    XSSFSheet sheet = workbook.getSheetAt(0);
    int rowNum = sheet.getPhysicalNumberOfRows();
    for (int i = 0; i <= rowNum; i++) {
        XSSFRow row = sheet.getRow(i);
        int cellCount = row.getPhysicalNumberOfCells();
        for (int j = 0; j < cellCount; j++) {
            XSSFCell cell = row.getCell(j);
            CellType cellType = cell.getCellType();
            /**
             * CellType中定义了不同的枚举类型，来作为表格数据的接收类型
             * NONE 未知类型
             * NUMERIC 数值类型(整数、小数、日期)
             * STRING 字符串
             * FORMULA 公式
             * BLANK 空字符串(没有值)，但是有单元格样式
             * BOOLEAN 布尔值
             * ERROR 错误单元格
             */
            if(cellType.equals(CellType.STRING)){
                System.out.println(cell.getStringCellValue());
            } else if (cellType.equals(CellType.NUMERIC)) {
                System.out.println(cell.getNumericCellValue());
            }else{
                System.out.println(cell.getStringCellValue());
            }
        }
    }
}

6. 批量数据写入 ​

6.1 03版本HSSF ​

最多支持65536行数据的写入，超出会抛出异常。
操作方式为，先将所有的数据放入到缓存中，最后一次性写入磁盘，写入速度快。

6.2 07+版本XSSF ​

数据的写入速度会比较慢，因为XSSF是获取全部行的数据，因此会消耗大量内存，数据量庞大时就有可能会发现内存溢出。可以写入较大的数据，比如10W+条数据。
最后我们还明显的发现，XSSF的速度明显要慢，大于9秒才完成数据的写入，但是它可以写入更多的数据

6.3 使用SXSSF ​

为了解决大数据量写入的问题，所以官方提供了SXSSF来解决大文件写入问题，所以它可以写入非常大量的数据，比如上百万条数据，并且写入速度更快，占用内存更少。
SXSSF在兼容XSSF的同时，能够应对大数据量和内存空间有限的情况。SXSSF每次获取的行数是在一个数值范围内，这个范围被称为“滑动窗口"，在这个窗口内的数据均存在于内存中，超出这个窗口大小时，数据会被写入磁盘，由此控制内存使用，相比较而言，XSSF则每次都是获取全部行。
窗口默认大小为100(可以自定义通过构造函数传入)，如果数据为101行，那么此时超出了窗口限制，索引值最小的行会被“刷入”磁盘，需要注意，SXSSF会自动分配临时文件，这些临时文件需要我们手动清除，清除的方式是使用dispose()方法中。

java
 public static void main(String[] args) throws IOException {
    List<UserInfo> list = new ArrayList<>();
    // 最大1048576
    for (int i = 0; i < 1000000; i++) {
        UserInfo userInfo = new UserInfo();
        userInfo.setName("jack"+(i+1));
        userInfo.setAge(i+1);
        list.add(userInfo);
    }

    String filePath = "D:\\batchExcelData.xlsx";
    // 创建工作簿
    SXSSFWorkbook workbook = new SXSSFWorkbook(2000);
    // 创建工作表
    SXSSFSheet sheet = workbook.createSheet("测试");
    // 创建行
    SXSSFRow headRow = sheet.createRow(0);
    // 创建单元格
    headRow.createCell(0, CellType.STRING).setCellValue("用户名");
    headRow.createCell(1, CellType.STRING).setCellValue("年龄");

    for (int i = 0; i < list.size(); i++) {
        UserInfo userInfo = list.get(i);
        SXSSFRow dataRow = sheet.createRow(i+1);
        dataRow.createCell(0, CellType.STRING).setCellValue(userInfo.getName());
        dataRow.createCell(1, CellType.NUMERIC).setCellValue(userInfo.getAge());
    }
    try(FileOutputStream outputStream = new FileOutputStream(filePath)){
        workbook.write(outputStream);
    } catch (IOException e) {
        throw new RuntimeException(e);
    }
}

7. 使用公式 ​

一般比较少用，因为公式的计算也可以放到我们的java代码中去做，公式的结果通过POI读取到： Alt text

java
public static void main(String[] args) throws IOException {
    String filePath = "D:\\07excelData.xlsx";
    FileInputStream inputStream = new FileInputStream(filePath);
    XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
    // 通过索引下标或者sheet的名称
    XSSFSheet sheet = workbook.getSheetAt(1);
    XSSFRow row1 = sheet.getRow(2);
    XSSFCell cell = row1.getCell(0);
    XSSFFormulaEvaluator formulaEvaluator = new XSSFFormulaEvaluator(workbook);
    if(cell.getCellType().equals(CellType.FORMULA)){
        // 获取计算公式
        String cellFormula = cell.getCellFormula();
        System.out.println(cellFormula);
        // 计算公式进行计算
        CellValue value = formulaEvaluator.evaluate(cell);
        System.out.println(value.formatAsString());
    }
}

运行结果：
Alt text