POI Excel 导入导出操作

,

一、基本思想

面向对象思想，工作簿、行、列都是一个对象。

xls 和 xlsx 的区别

  • 最大行列得数量不同：

    • xls： 65536 行 (2 的 16 次方）、256 列
    • xlsx： 1048576 行 (2 的 20 次方）、16384 列

  • 对应的列的读写时长不一样，注意优化，xlsx 类型推荐用 SXSSFWorkbook 类写，边写边存。

二、Maven 配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
<!-- 导入依赖 -->
<dependencies>
    <!-- xls(03) -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.9</version>
    </dependency>

    <!-- xlsx(07) -->
    <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.14</version>
    </dependency>

    <!-- test -->
    <dependency>
        <groupId>org.junit.jupiter</groupId>
        <artifactId>junit-jupiter</artifactId>
        <version>RELEASE</version>
        <scope>compile</scope>
    </dependency>
</dependencies>

三、相关代码

公共路径（成员变量）

1
String PATH = "路径";

1. 写操作

1.1 Java 操作 xls 文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
@Test
public void ExcelWriteTest03() throws Exception {

    // 1.创建一个工作簿
    Workbook workbook = new HSSFWorkbook();
    // 2.创建一个工作表
    Sheet sheet = workbook.createSheet("我的工作表1");
    // 3.创建一个行
    Row row = sheet.createRow(0);
    // 4.创建一个单元格
    // (1,1)
    Cell cell11 = row.createCell(0);
    cell11.setCellValue("今日学习情况");
    // (1,2)
    Cell cell12 = row.createCell(1);
    cell12.setCellValue("poi学习");

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

    // 生成一张表 -> IO流 -> 03 版本使用 .xls 结尾
    FileOutputStream fileOutputStream = new FileOutputStream(PATH + "今日学习情况03.xls");
    // 输出
    workbook.write(fileOutputStream);
    // 关闭流
    fileOutputStream.close();

    System.out.println("今日学习情况03.xls 生成完毕！");
}

1.2 Java 操作 xlsx 文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
@Test
public void ExcelWriteTest07() throws Exception {

    // 1.创建一个工作簿
    Workbook workbook = new XSSFWorkbook();
    // 2.创建一个工作表
    Sheet sheet = workbook.createSheet("我的工作表1");
    // 3.创建一个行
    Row row = sheet.createRow(0);
    // 4.创建一个单元格
    // (1,1)
    Cell cell11 = row.createCell(0);
    cell11.setCellValue("今日学习情况");
    // (1,2)
    Cell cell12 = row.createCell(1);
    cell12.setCellValue("poi学习");

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

    // 生成一张表 -> IO流 -> 03 版本使用 .xlsx 结尾
    FileOutputStream fileOutputStream = new FileOutputStream(PATH + "今日学习情况07.xlsx");
    // 输出
    workbook.write(fileOutputStream);
    // 关闭流
    fileOutputStream.close();

    System.out.println("今日学习情况07.xlsx 生成完毕！");
}

1.3 写大量数据对比与优化

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
@Test
public void ExcelWriteTest03BigDate() throws Exception {

    long begin = System.currentTimeMillis();
    // 创建工作簿
    Workbook workbook = new HSSFWorkbook();
    // 创建工作表
    Sheet sheet = workbook.createSheet("我的工作簿");
    // 写入数据
    for (int i = 0; i < 65536; i++) {

        Row row = sheet.createRow(i);
        for (int j = 0; j < 10; j++) {

            Cell cell = row.createCell(j);
            cell.setCellValue(j);
        }
    }
    System.out.println("over");
    FileOutputStream fileOutputStream = new FileOutputStream(PATH + "ExcelWriteTest03BigDate.xls");
    workbook.write(fileOutputStream);
    fileOutputStream.close();
    long end = System.currentTimeMillis();
    System.out.println((double) (end - begin) / 1000); // 3.831
}

// 耗时较长 优化 => 缓存
@Test
public void ExcelWriteTest07BigDate() throws Exception {

    long begin = System.currentTimeMillis();
    // 创建工作簿
    Workbook workbook = new XSSFWorkbook();
    // 创建工作表
    Sheet sheet = workbook.createSheet("我的工作簿");
    // 写入数据
    for (int i = 0; i < 65536; i++) {

        Row row = sheet.createRow(i);
        for (int j = 0; j < 10; j++) {

            Cell cell = row.createCell(j);
            cell.setCellValue(j);
        }
    }
    System.out.println("over");
    FileOutputStream fileOutputStream = new FileOutputStream(PATH + "ExcelWriteTest07BigDate.xlsx");
    workbook.write(fileOutputStream);
    fileOutputStream.close();
    long end = System.currentTimeMillis();
    System.out.println((double) (end - begin) / 1000); // 26.024
}

@Test
public void ExcelWriteTest07BigDateS() throws Exception {

    long begin = System.currentTimeMillis();
    // 创建工作簿
    Workbook workbook = new SXSSFWorkbook();
    // 创建工作表
    Sheet sheet = workbook.createSheet("我的工作簿");
    // 写入数据
    for (int i = 0; i < 65536; i++) {

        Row row = sheet.createRow(i);
        for (int j = 0; j < 10; j++) {

            Cell cell = row.createCell(j);
            cell.setCellValue(j);
        }
    }
    System.out.println("over");
    FileOutputStream fileOutputStream = new FileOutputStream(PATH + "ExcelWriteTest07BigDateS.xlsx");
    workbook.write(fileOutputStream);
    fileOutputStream.close();
    // 清除临时文件
    ((SXSSFWorkbook) workbook).dispose();
    long end = System.currentTimeMillis();
    System.out.println((double) (end - begin) / 1000); // 5.029
}

2. 读操作

2.1 Java 操作 xls 文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
@Test
public void ExcelReadTest03() throws Exception {

    // 0.获取文件流
    FileInputStream inputStream = new FileInputStream(PATH + "今日学习情况03.xls");

    // 1.得到一个工作簿
    Workbook workbook = new HSSFWorkbook(inputStream);
    // 2.得到工作表
    Sheet sheet = workbook.getSheetAt(0);
    // 3.得到行
    Row row = sheet.getRow(0);
    // 4.读取列
    Cell cell = row.getCell(0);

    // 读取值的时候，一定要注意类型！
    System.out.println(cell.getStringCellValue());

    inputStream.close();
}

2.2 Java 操作 xlsx 文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
@Test
public void ExcelReadTest07() throws Exception {

    // 0.获取文件流
    FileInputStream inputStream = new FileInputStream(PATH + "今日学习情况07.xlsx");

    // 1.得到一个工作簿
    Workbook workbook = new XSSFWorkbook(inputStream);
    // 2.得到工作表
    Sheet sheet = workbook.getSheetAt(0);
    // 3.得到行
    Row row = sheet.getRow(0);
    // 4.读取列
    Cell cell = row.getCell(0);

    // 读取值的时候，一定要注意类型！
    System.out.println(cell.getStringCellValue());

    inputStream.close();
}

2.3 获取单元格类型

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
@Test
public void testCellType() throws Exception {

    // 0.获取文件流
    FileInputStream inputStream = new FileInputStream(PATH + "工资申报表.xls");

    // 1.得到一个工作簿
    Workbook workbook = new HSSFWorkbook(inputStream);
    Sheet sheet = workbook.getSheetAt(0);
    // 2.获取标题内容
    Row rowTitle = sheet.getRow(0);
    if (rowTitle != null) {

        // 一定要掌握 要使用 getLastCellNum()
        // getLastCellNum() => 获取最后一个索引;
        // getPhysicalNumberOfCells() => 获取不为空的总数，有空会遍历不完
        int cellCount = rowTitle.getLastCellNum();
        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 = rowData.getLastCellNum();
            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.print("【STRING】");
                            cellValue = cell.getStringCellValue();
                            break;
                        case HSSFCell.CELL_TYPE_BOOLEAN: // 布尔
                            System.out.print("【BOOLEAN】");
                            cellValue = String.valueOf(cell.getBooleanCellValue());
                            break;
                        case HSSFCell.CELL_TYPE_BLANK: // 空
                            System.out.print("【BLANK】");
                            break;
                        case HSSFCell.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(HSSFCell.CELL_TYPE_STRING);
                                cellValue = cell.toString();
                            }
                            break;
                        case HSSFCell.CELL_TYPE_ERROR: // 空
                            System.out.print("【数据类型错误】");
                            break;
                    }
                    System.out.println(cellValue);
                }
            }
        }
    }


    inputStream.close();
}

2.4 读取公式

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
@Test
public void testFormula() throws Exception {

    FileInputStream inputStream = new FileInputStream(PATH + "公式.xls");
    Workbook workbook = new HSSFWorkbook(inputStream);
    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;
        default:
            System.out.println("其余");
    }
    inputStream.close();
}