POI - 简单读写Excel

1. 简介

Apache POI是Apache软件基金会的开源项目，POI提供API给Java程序对Microsoft Office格式文档读和写的功能。

1.1 官网地址

[https://poi.apache.org/]

1.2 基本功能

• HSSF — 提供读写Microsoft Excel格式文档的功能。（2003版，最多只能存65536行数据）
• XSSF — 提供读写Microsoft Excel OOXML格式文档的功能。（2007版，可存放的数据无限制）
• HWPF — 提供读写Microsoft Word DOC格式文档的功能。
• HSLF — 提供读写Microsoft PowerPoint格式文档的功能。
• HDGF — 提供读Microsoft Visio格式文档的功能。
• HPBF — 提供读Microsoft Publisher格式文档的功能。
• HSMF — 提供读Microsoft Outlook格式文档的功能。

2. POI - Excel写

2.1 相关依赖

<!-- xls(2003) -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.9</version>
</dependency>

<!-- xlsx(2007) -->
<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>

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

说明；2003版本和2007版本存在兼容性问题，2003版本最多只能有65536行！

2003|2007版本的写，只是对象不同，方法是一样的！

读写Excel文件目录：

private static final String PATH = "E:\\IdeaProjects\\POI\\excel\\";

2.2 写-2003版本

后缀：xls

对象：HSSFWorkbook

@Test
public void writeExcel2003() throws Exception {
    // 1.创建一个工作簿  2003版本
    Workbook workbook = new HSSFWorkbook();
    // 2.创建一个工作表
    Sheet sheet = workbook.createSheet("图书销售表");
    // 3.创建第一行
    Row row1 = sheet.createRow(0);

    // 4.创建单元格
    // (1,1)
    Cell cell11 = row1.createCell(0);
    cell11.setCellValue("三国演义");
    // (1,2)
    Cell cell12 = row1.createCell(1);
    cell12.setCellValue(88);

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

    // 生成一张Excel表（IO流），2003版本是使用xls结尾
    FileOutputStream fileOutputStream = new FileOutputStream(PATH + "图书销售表03.xls");
    // 输出
    workbook.write(fileOutputStream);
    // 关流
    fileOutputStream.close();

    System.out.println("图书销售表 生成完毕！");
}

2.3 写-2007版本

后缀：xlsx

对象：XSSFWorkbook

@Test
public void writeExcel2007() throws Exception {
    // 1.创建一个工作簿  2007版本
    Workbook workbook = new XSSFWorkbook();
    // 2.创建一个工作表
    Sheet sheet = workbook.createSheet("图书销售表");
    // 3.创建第一行
    Row row1 = sheet.createRow(0);

    // 4.创建单元格
    // (1,1)
    Cell cell11 = row1.createCell(0);
    cell11.setCellValue("水浒传");
    // (1,2)
    Cell cell12 = row1.createCell(1);
    cell12.setCellValue(76);

    // 创建第二行
    Row row2 = sheet.createRow(1);
    // (2,1)
    Cell cell21 = row2.createCell(0);
    cell21.setCellValue("销售日期");
    // (2,2)
    Cell cell22 = row2.createCell(1);
    String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
    cell22.setCellValue(time);

    // 生成一张Excel表（IO流），2007版本是使用xlsx结尾
    FileOutputStream fileOutputStream = new FileOutputStream(PATH + "图书销售表07.xlsx");
    // 输出
    workbook.write(fileOutputStream);
    // 关流
    fileOutputStream.close();

    System.out.println("图书销售表 生成完毕！");
}

2.4 大文件写HSSF

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

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

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

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

    // 创建工作簿
    Workbook workbook = new HSSFWorkbook();

    // 创建工作表
    Sheet sheet = workbook.createSheet();

    // 写入数据
    int totalRowNum = 65536;
    for (int rowNum = 0; rowNum < totalRowNum; 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 + "writeExcel2003BigData.xls");
    workbook.write(fileOutputStream);
    fileOutputStream.close();

    // 结束时间
    long end = System.currentTimeMillis();

    // 批量写入65536条数据耗时：1.35s
    System.out.println("批量写入" + totalRowNum + "条数据耗时：" + (double) (end - begin) / 1000 + "s");
}

2.5 大文件写XSSF

缺点： 写文件时速度非常慢，非常耗内存，也会发生内存溢出（如100万条）

优点： 可以写较大的数据量（如20万条）

// 耗时较长！
@Test
public void writeExcel2007BigData() throws Exception {
    // 开始时间
    long begin = System.currentTimeMillis();

    // 创建工作簿
    Workbook workbook = new XSSFWorkbook();

    // 创建工作表
    Sheet sheet = workbook.createSheet();

    // 写入数据
    int totalRowNum = 100000;
    for (int rowNum = 0; rowNum < 100000; 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 + "writeExcel2007BigData.xlsX");
    workbook.write(fileOutputStream);
    fileOutputStream.close();

    // 结束时间
    long end = System.currentTimeMillis();

    // 批量写入100000条数据耗时：11.229s
    System.out.println("批量写入" + totalRowNum + "条数据耗时：" + (double) (end - begin) / 1000 + "s");
}

2.6 大文件写SXSSF

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

注意：

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

默认保存100条数据到内存中，如果超出100条，则最前面的数据被写入临时文件；

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

// 使用缓存优化！
@Test
public void writeExcel2007BigDataS() throws Exception {
    // 开始时间
    long begin = System.currentTimeMillis();

    // 创建工作簿
    Workbook workbook = new SXSSFWorkbook();

    // 创建工作表
    Sheet sheet = workbook.createSheet();

    // 写入数据
    int totalRowNum = 500000;
    for (int rowNum = 0; rowNum < 500000; 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 + "writeExcel2007BigDataS.xlsX");
    workbook.write(fileOutputStream);
    fileOutputStream.close();

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

    // 结束时间
    long end = System.currentTimeMillis();

    // 批量写入500000条数据耗时：5.216s
    System.out.println("批量写入" + totalRowNum + "条数据耗时：" + (double) (end - begin) / 1000 + "s");
}

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

请注意，SXSSFWorkbook仍然可能会消耗大量内存，这些内存基于您正在使用的功能。例如合并区域，注释…仍然只存储在内存中，因此如果广泛使用，可能需要大量内存。（在使用POI的时候，内存问题——Jprofile）

3. POI - Excel读

读取值的时候，类型需要相匹配

getNumericCellValue()：获取数字类型

getStringCellValue()：获取字符串类型

…

3.1 读-2003版本

@Test
public void readExcel2003() throws Exception {
    // 获取文件流
    FileInputStream inputStream = new FileInputStream(PATH + "图书销售表03.xls");

    // 1.创建一个工作簿。使用Excel能操作的，这个类都可以操作
    Workbook workbook = new HSSFWorkbook(inputStream);
    // 2.得到工作表
    Sheet sheet = workbook.getSheetAt(0);
    // 3.得到行
    Row row = sheet.getRow(0);
    // 4.得到列
    Cell cell = row.getCell(1);

    // 读取值的时候，要注意类型相匹配  getStringCellValue()：获取字符串类型
    System.out.println(cell.getNumericCellValue());
    inputStream.close();
}

3.2 读-2007版本

@Test
public void readExcel2007() throws Exception {
    // 获取文件流
    FileInputStream inputStream = new FileInputStream(PATH + "图书销售表07.xlsx");

    // 1.创建一个工作簿。使用Excel能操作的，这个类都可以操作
    Workbook workbook = new XSSFWorkbook(inputStream);
    // 2.得到工作表
    Sheet sheet = workbook.getSheetAt(0);
    // 3.得到行
    Row row = sheet.getRow(0);
    // 4.得到列
    Cell cell = row.getCell(1);

    // 读取值的时候，要注意类型相匹配  getStringCellValue()：获取字符串类型
    System.out.println(cell.getNumericCellValue());
    inputStream.close();
}

3.3 读取不同的数据类型

注意类型转换问题

// 读取不同的数据类型
@Test
public void testCellType() throws Exception {
    FileInputStream inputStream = new FileInputStream(PATH + "明细表.xlsx");

    // 创建一个工作簿。使用Excel能操作的，这个类都可以操作
    Workbook workbook = new XSSFWorkbook(inputStream);
    // 得到工作表
    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 XSSFCell.CELL_TYPE_STRING:  // 字符串
                            System.out.print("【STRING】");
                            cellValue = cell.getStringCellValue();
                            break;
                        case XSSFCell.CELL_TYPE_BOOLEAN:  // 布尔
                            System.out.print("【BOOLEAN】");
                            cellValue = String.valueOf(cell.getBooleanCellValue());
                            break;
                        case XSSFCell.CELL_TYPE_BLANK:  // 空
                            System.out.print("【BLANK】");
                            break;
                        case XSSFCell.CELL_TYPE_NUMERIC:  // 数字（日期、普通数字）
                            System.out.print("【NUMERIC】");
                            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(XSSFCell.CELL_TYPE_STRING);
                                cellValue = cell.toString();
                            }
                            break;
                        case XSSFCell.CELL_TYPE_ERROR:
                            System.out.print("【数据类型错误】");
                            break;
                    }
                    System.out.println(cellValue);
                }
            }
        }
    }

    inputStream.close();
}

3.4 计算公式

// 公式计算
@Test
public void testFormula() throws Exception {
    FileInputStream inputStream = new FileInputStream(PATH + "公式.xlsx");
    Workbook workbook = new XSSFWorkbook(inputStream);
    Sheet sheet = workbook.getSheetAt(0);

    // 单元格位置
    Row row = sheet.getRow(6);
    Cell cell = row.getCell(1);

    // 拿到计算公式
    FormulaEvaluator formulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);

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

    // 输出单元格的内容（即输出计算公式）
    int cellType = cell.getCellType();
    switch (cellType) {
        case Cell.CELL_TYPE_FORMULA:  // 公式
            String cellFormula = cell.getCellFormula();
            System.out.println(cellFormula);
            break;
    }
}