使用代码操作Excel文件(POI)

本文最后更新于：2023年11月9日 晚上

本篇文章将讲述使用POI如何去对Excel文件进行读写

apache的POI文档：https://poi.apache.org

1.Maven导入poi包

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
<!--导入依赖-->
    <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.1</version>
        </dependency>

        <!--junit测试-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
    </dependencies>

这里解释下为什么有两个版本(03版，07版) ？
1. 两者的文件后缀不同，03版本是.xls，07版本是.xlsx
2. 03版的最多只能有65536行 (03版本如果写入数据超过65536行，则会出现异常)，而07版行数可以无限制 (代表07版可以存放的数据更多)

在开始代码之前，先认识下Excel表结构 直接看下图

2.POI进行基本的写入

注：03版本使用 HSSFWorkbook，07版本使用 XSSFWorkbook (07版本大量数据写入使用SXSSFWorkbook)
07版本的XSSFWorkbook 与 SXSSFWorkbook的区别 ？这个疑问下面会讲到。

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
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;

import java.io.FileOutputStream;

/**
 * @author: libo
 * @date: 2020/10/8  15:22
 * @motto: 即使再小的帆也能远航
 */
public class test {

    /*文件生成路径*/
    final String PATH = "D:\\IntelliJ IDEA 2019.1_File\\POIandEasyExcel\\poi\\";

    /*03版本Excel*/
    @Test
    public void poi03() throws Exception {
        /*1.创建工作簿*/
        Workbook workbook = new HSSFWorkbook();
        /*2.创建工作表*/
        Sheet sheet = workbook.createSheet("03版本");
        /*3.创建第一行(0就是第一行,以此类推 就像数组下标一样)*/
        Row row = sheet.createRow(0);
        /*4.创建一列(单元格)(0就是第一列)*/
        Cell cell = row.createCell(0);
        cell.setCellValue("第一行第一列");
        /*4.创建一列(单元格)(1就是第二列)*/
        Cell cell2 = row.createCell(1);
        cell2.setCellValue("第一行第二列");
        /*创建第二行(1就是第二行)*/
        Row row2 = sheet.createRow(1);
        /*创建(单元格)*/
        Cell cell21 = row2.createCell(0);
        cell21.setCellValue("第二行第一列");
        Cell cell22 = row2.createCell(1);
        cell22.setCellValue("第二行第二列");

        /*5.创建输出流(IO流)  03版本文件后缀为：.xls*/
        FileOutputStream fileOutputStream = new FileOutputStream(PATH + "03Excel.xls");
        /*6.生成文件*/
        workbook.write(fileOutputStream);
        /*7.关闭流*/
        fileOutputStream.close();
    }



    /*07版本Excel*/
    @Test
    public void poi07() throws Exception {
        /*1.创建工作簿*/
        Workbook workbook = new XSSFWorkbook();
        /*2.创建工作表*/
        Sheet sheet = workbook.createSheet("07版本");
        /*3.创建第一行*/
        Row row = sheet.createRow(0);
        /*4.创建一列(单元格)*/
        Cell cell = row.createCell(0);
        cell.setCellValue("第一行第一列");
        Cell cell2 = row.createCell(1);
        cell2.setCellValue("第一行第二列");
        /*创建第二行*/
        Row row2 = sheet.createRow(1);
        /*创建(单元格)*/
        Cell cell21 = row2.createCell(0);
        cell21.setCellValue("第二行第一列");
        Cell cell22 = row2.createCell(1);
        cell22.setCellValue("第二行第二列");

        /*5.创建输出流(IO流)  07版本文件后缀为：.xlsx*/
        FileOutputStream fileOutputStream = new FileOutputStream(PATH + "07Excel.xlsx");
        /*6.生成文件*/
        workbook.write(fileOutputStream);
        /*7.关闭流*/
        fileOutputStream.close();
    }
}

3.POI进行大量数据的写入

模拟下大量数据的写入，写入65536行，10列(每行10个单元格)的数据
并且记录程序所耗费的时间

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
/*大量数据写入03版本Excel*/
@Test
public void poi03BigDate() throws Exception {

    /*记录程序开始时间*/
    long begin = System.currentTimeMillis();

    /*1.创建工作簿*/
    Workbook workbook = new HSSFWorkbook();
    /*2.创建工作表*/
    Sheet sheet = workbook.createSheet("03版本大量数据写入");

    /*x:行 y:列(单元格)*/
    for (int x = 0; x < 65536; x++) {
        /*3.创建行*/
        Row row = sheet.createRow(x);
        for (int y = 0; y < 10; y++) {
            /*4.创建列(单元格)*/
            Cell cell = row.createCell(y);
            cell.setCellValue(y);
        }
    }

    /*5.创建输出流(IO流)  03版本文件后缀为：.xls*/
    FileOutputStream fileOutputStream = new FileOutputStream(PATH + "03BIgDate.xls");
    /*6.生成文件*/
    workbook.write(fileOutputStream);
    /*7.关闭流*/
    fileOutputStream.close();

    /*记录程序结束时间*/
    long end = System.currentTimeMillis();

    /*耗时 = 结束时间 - 开始时间 / 1000(毫秒值)*/
    System.out.println("HSSF_03版耗时：" + (double) (end - begin) / 1000);
}


/*大量数据写入07版本Excel*/
@Test
public void poi07BigDate() throws Exception {

    /*记录程序开始时间*/
    long begin = System.currentTimeMillis();

    /*1.创建工作簿*/
    Workbook workbook = new XSSFWorkbook();
    //Workbook workbook = new SXSSFWorkbook();
    
    /*2.创建工作表*/
    Sheet sheet = workbook.createSheet("07版本大量数据写入");

    /*x:行 y:列(单元格)*/
    for (int x = 0; x < 65536; x++) {
        /*3.创建行*/
        Row row = sheet.createRow(x);
        for (int y = 0; y < 10; y++) {
            /*4.创建列(单元格)*/
            Cell cell = row.createCell(y);
            cell.setCellValue(y);
        }
    }

    /*5.创建输出流(IO流)  07版本文件后缀为：.xlsx*/
    FileOutputStream fileOutputStream = new FileOutputStream(PATH + "07BIgDate.xlsx");
    /*6.生成文件*/
    workbook.write(fileOutputStream);
    /*7.关闭流*/
    fileOutputStream.close();

    /*记录程序结束时间*/
    long end = System.currentTimeMillis();

    /*耗时 = 结束时间 - 开始时间 / 1000(毫秒值)*/
    System.out.println("XSSF_07版耗时：" + (double) (end - begin) / 1000);
}

03版如果写入超过65536行数据则会出现异常(这就是03版与07版很大的区别，还有就是后缀名不同，03版本是 .xls，07版本是 .xlsx)：

03版HSSF耗时：
优点：过程中写入缓存，不操作磁盘，最后一次性写入磁盘，速度快
缺点：只能操作65536行数据，否则会抛出异常
(java.lang.IllegalArgumentException: Invalid row number (65536) outside allowable range (0..65535))

07版XSSF耗时：
优点：可以写非常大的数据量，比如几十万
缺点：写数据时非常慢，很耗内存，所有很有可能发生内存溢出(OOM)

07版SXSSF耗时(07版本将 XSSFWorkbook对象 替换为 SXSSFWorkbook 对象)：
优点：在写入庞大数据量时，耗时更少，占用更少的内存
注意：在写入数据时，有一部分会被先写到临时文件，执行完之后需要清理临时文件。如果想自定义内存中的数量可以 new SXSSFWorkbook(传入数量)

Excel写入总结：

4. POI读取数据 (模拟两个案例)

注：
1. 此案例只是03版本的Excel (如果是07版的Excel 换掉工作簿的对象即可，将HSSFWorkbook 变为XSSFWorkbook)
** 2. 读取数据最复杂得点在于 转换Excel表中数据。比如日期、数字、小数、字符串、Excel中得函数公式、空单元格**
** 3. 所以可以使用 switch case 对不同类型进行判断 匹配**

案例一：

下面有一个Excel表，把其中数据取出来，到控制台输出(都到控制台了，还不能存入数据库吗？)：

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
/*读取03版本Excel中多个类型数据*/
@Test
public void poi03read() throws Exception {

    /*获得文件流*/
    FileInputStream FileInputStream = new FileInputStream(PATH + "会员消费商品明细表.xls");

    /*1.创建工作簿*/
    Workbook workbook = new HSSFWorkbook(FileInputStream);

    /*2.得到工作表(一个文件中可能有多个工作表,参数为 索引)*/
    Sheet sheetAt = workbook.getSheetAt(0);

    /*3.获得工作表中所有行*/
    int rowCount = sheetAt.getPhysicalNumberOfRows();

    /*循环所有行*/
    for (int rowNum = 0; rowNum < rowCount; rowNum++) {
        /*读取行*/
        Row row = sheetAt.getRow(rowNum);

        if (row != null) {

            /*4.获得所有列*/
            int cellCount = row.getPhysicalNumberOfCells();

            /*循环所有列*/
            for (int cellNum = 0; cellNum < cellCount; cellNum++) {

                /*获得列(单元格)*/
                Cell cell = row.getCell(cellNum);

                /*判断列不为null*/
                if (cell != null) {
                    /*获得单元格内容的类型*/
                    int cellType = cell.getCellType();

                    /*内容变量*/
                    String cellValue = "";

                    /*先判断类型,再输出*/
                    switch (cellType) {
                            /*String类型*/
                        case Cell.CELL_TYPE_STRING:
                            cellValue = cell.getStringCellValue();
                            System.out.print(cellValue + " ");
                            break;

                            /*boolean类型*/
                        case Cell.CELL_TYPE_BOOLEAN:
                            cellValue = String.valueOf(cell.getBooleanCellValue());
                            System.out.print(cellValue + " ");
                            break;

                            /*数字类型(包括日期,数字)*/
                        case Cell.CELL_TYPE_NUMERIC:
                            /*HSSF工具类判断是否为日期*/
                            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                Date date = cell.getDateCellValue();
                                /*将日期时间转为字符串*/
                                cellValue = new DateTime(date).toString("yyyy-MM-dd");
                                System.out.print(cellValue);
                            } else {
                                /*不是日期类型 代表是数字,再将它转为字符串*/
                                cell.setCellType(Cell.CELL_TYPE_STRING);
                                cellValue = cell.getStringCellValue();
                                System.out.print(cellValue + " ");
                            }
                            break;

                            /*类型错误*/
                        case Cell.CELL_TYPE_ERROR:
                            break;

                            /*空*/
                        case Cell.CELL_TYPE_BLANK:
                            break;
                    }
                }
            }
        }
        /*换行*/
        System.out.println();
    }
    /*5.关闭流*/
    FileInputStream.close();
}

案例二：

下面有一个Excel表，把它读取出来，到控制台输出(获得总和值和单元格中得函数公式)：

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
/*读取03版本Excel中的函数公式(包括结果)*/
@Test
public void poi03readFormula() throws Exception {

    /*获得文件流*/
    FileInputStream FileInputStream = new FileInputStream(PATH + "计算公式.xls");

    /*1.创建工作簿*/
    Workbook workbook = new HSSFWorkbook(FileInputStream);

    /*2.得到工作表(一个文件中可能有多个工作表,参数为 索引)*/
    Sheet sheetAt = workbook.getSheetAt(0);

    /*3.获得工作表中所有行*/
    int rowCount = sheetAt.getPhysicalNumberOfRows();

    /*循环所有行*/
    for (int rowNum = 0; rowNum < rowCount; rowNum++) {
        /*读取行*/
        Row row = sheetAt.getRow(rowNum);
        if (row != null) {
            /*4.获得所有列*/
            int cellCount = row.getPhysicalNumberOfCells();
            /*循环所有列*/
            for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                /*获得列(单元格)*/
                Cell cell = row.getCell(cellNum);
                /*判断列不为null*/
                if (cell != null) {
                    /*获得单元格内容的类型*/
                    int cellType = cell.getCellType();
                    /*内容变量*/
                    String cellValue = "";
                    /*先判断类型,再输出*/
                    switch (cellType) {
                            /*函数公式*/
                        case Cell.CELL_TYPE_FORMULA:

                            cell.setCellType(Cell.CELL_TYPE_FORMULA);

                            /*获得函数公式*/
                            String formula = cell.getCellFormula();

                            System.out.println(formula);

                            /*获得函数公式结果*/
                            /*创建*HSSFFormulaEvaluator对象*/
                            FormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);

                            CellValue evaluate = formulaEvaluator.evaluate(cell);

                            cellValue = evaluate.formatAsString();

                            System.out.println(cellValue);
                            break;
                    }
                }
            }
        }
    }
    /*5.关闭流*/
    FileInputStream.close();
}
因为回忆太痛苦，所有我们要一直往前走。
Java
#JavaSE #Excel #POI
使用代码操作Excel文件(POI)
http://example.com/2020/10/10/使用代码操作Excel文件(POI)/
作者
阿波~
发布于
2020年10月10日
更新于
2023年11月9日
许可协议