Java使用POI操作Excel_poi3.9创建excel表格

• 作者简介：一名后端开发人员，每天分享后端开发以及人工智能相关技术，行业前沿信息，面试宝典。
• 座右铭：未来是不可确定的，慢慢来是最快的。
• 个人主页：极客李华-CSDN博客
• 合作方式：私聊+
• 这个专栏内容：BAT等大厂常见后端java开发面试题详细讲解，更新数目100道常见大厂java后端开发面试题。
• 我的CSDN社区：https://bbs.csdn.net/forums/99eb3042821a4432868bb5bfc4d513a8
• 微信公众号，抖音，b站等平台统一叫做：极客李华，加入微信公众号领取各种编程资料，加入抖音，b站学习面试技巧，职业规划

Java使用POI操作Excel（把mysql数据库转换成Excel）

POI简介

• 简介
  Apache POI 是用Java编写的免费开源的跨平台的 Java API，Apache POI提供API给Java对Microsoft Office格式档案读和写的功能。

• 常用的包

HSSF － 提供读写Microsoft Excel XLS格式档案的功能。

XSSF － 提供读写Microsoft Excel OOXML XLSX格式档案的功能。

HWPF － 提供读写Microsoft Word DOC格式档案的功能。

HSLF － 提供读写Microsoft PowerPoint格式档案的功能。

HDGF － 提供读Microsoft Visio格式档案的功能。

HPBF － 提供读Microsoft Publisher格式档案的功能。

HSMF － 提供读Microsoft Outlook格式档案的功能。

• 引入的依赖

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

        <!--xlsx-->
        <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.10</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>

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

代码编写

创建项目

删掉最开始的那个模块，父模块，然后就是这个样子了，

引入依赖

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>org.example</groupId>
    <artifactId>POIStudy</artifactId>
    <version>1.0-SNAPSHOT</version>
    <dependencies>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.9</version>
        </dependency>

        <!--xlsx-->
        <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.10</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.junit.jupiter</groupId>
            <artifactId>junit-jupiter</artifactId>
            <version>5.8.1</version>
            <scope>compile</scope>
        </dependency>
    </dependencies>

</project>
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

创建工作蒲（写数据）

对于java操作Excel主要是操作这几个地方
工作薄
工作表
行
列
需要注意的是：2003 版本和 2007 版本存在兼容性的问题！03最多只有 65535 行！

03版本

文件结构

演示代码

public class Main {
    String path = "D:\\系统默认\\桌面\\博客素材\\POIDemo\\";
    @Test
    public void testExcel03() 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 cell12 = row1.createCell(1);
        cell12.setCellValue(233);

        // 创建第二行
        Row row2 = sheet.createRow(1);

        // 创建单元格（col 2-1）
        Cell cell21 = row2.createCell(0);
        cell21.setCellValue("统计时间");

        // 创建第三行单元格
        Cell cell22 = row2.createCell(1);
        // 格式化创建时间
        String dateTime = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
        cell22.setCellValue(dateTime);

        // 新建一输出文件流
        FileOutputStream out = new FileOutputStream(path + "极客李华的观众统计表03.xls");

        // 把相应的Excel工作蒲存盘
        workbook.write(out);

        // 操作结束 关闭文件
        out.close();
        System.out.println("文件生成成功");
    }

}
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

这里就是生成的Excel文件

07版本

07版本相比于03版本它的不同之处在于，这个创建工作薄的时候使用的方法不一样，然后07版与03班的Excel文件的结尾也是不一样的，07是.xlsx。
项目结构

项目代码

@Test
    public void testExcel07() throws IOException{
        // 1.创建一个工作薄
        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 cell12 = row1.createCell(1);
        cell12.setCellValue(233);

        // 创建第二行
        Row row2 = sheet.createRow(1);

        // 创建单元格（col 2-1）
        Cell cell21 = row2.createCell(0);
        cell21.setCellValue("统计时间");

        // 创建第三行单元格
        Cell cell22 = row2.createCell(1);
        // 格式化创建时间
        String dateTime = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
        cell22.setCellValue(dateTime);

        // 新建一输出文件流
        FileOutputStream out = new FileOutputStream(path + "极客李华的观众统计表03.xls");

        // 把相应的Excel工作蒲存盘
        workbook.write(out);

        // 操作结束 关闭文件
        out.close();
        System.out.println("文件生成成功");
    }
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

运行结果

对比

• 对于03版本
  缺点：最多只能处理65536行，否则会抛出异常
  优点：过程中写入缓存，不操作磁盘，最后一次性写入磁盘，速度快

演示代码
这里用的是HSSFWorkbook

@Test
    public void testWrite03BigData() throws IOException {
        //记录开始时间
        long begin = System.currentTimeMillis();
        //创建一个SXSSFWorkbook
        Workbook workbook = new HSSFWorkbook();
        //创建一个sheet
        Sheet sheet = workbook.createSheet();
        //xls文件最大支持65536行
        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("操作结束");
        FileOutputStream out = new FileOutputStream(path+"bigdata03.xls");
        workbook.write(out);
        // 操作结束，关闭文件
        out.close();
        //记录结束时间
        long end = System.currentTimeMillis();
        System.out.println((double)(end - begin)/1000);
    }
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

运行结果
写满数据这个1s不到就写完了，还是很快的。

• 对于07版本
  缺点：写数据时速度非常慢，非常耗内存，也会发生内存溢出，如100万条
  优点：可以写较大的数据量，如20万条

演示代码
这里用的是XSSFWorkbook

@Test
    public void testWrite07BigData() throws IOException {
        //记录开始时间
        long begin = System.currentTimeMillis();
        //创建一个XSSFWorkbook
        Workbook workbook = new XSSFWorkbook();
        //创建一个sheet
        Sheet sheet = workbook.createSheet();
        //xls文件最大支持65536行
        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("done");
        FileOutputStream out = new FileOutputStream(path+"bigdata07.xlsx");
        workbook.write(out);
        // 操作结束，关闭文件
        out.close();
        //记录结束时间
        long end = System.currentTimeMillis();
        System.out.println((double)(end - begin)/1000);
    }
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

运行结果
这个结果花了近10s还是很慢的，虽然可以操作的数据变多了，但是时间消耗更多了

演示代码
这里用的是SXSSFWorkbook
项目结构

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

注意：
过程中会产生临时文件，需要清理临时文件
默认由100条记录被保存在内存中，如果超过这数量，则最前面的数据被写入临时文件
如果想自定义内存中数据的数量，可以使用new SXSSFWorkbook ( 数量 )

@Test
    public void testWrite07BigDataFast() throws IOException {
        //记录开始时间
        long begin = System.currentTimeMillis();
        //创建一个SXSSFWorkbook
        Workbook workbook = new SXSSFWorkbook();
        //创建一个sheet
        Sheet sheet = workbook.createSheet();
        //xls文件最大支持65536行
        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("done");
        FileOutputStream out = new FileOutputStream(path+"bigdata07-fast.xlsx");
        workbook.write(out);
        // 操作结束，关闭文件
        out.close();
        //清除临时文件
        ((SXSSFWorkbook)workbook).dispose();
        //记录结束时间
        long end = System.currentTimeMillis();
        System.out.println((double)(end - begin)/1000);
    }

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

运行结果：
从结果看这个运行结果还是蛮快的

读工作蒲（读数据）

03版本

这里演示的是如何读取03版本的Excel

@Test
    public void testRead03() throws Exception{
        InputStream is = new FileInputStream(path+"极客李华的观众统计表03.xls");
        Workbook workbook = new HSSFWorkbook(is);
        // 这里是读取第一个工作薄
        Sheet sheet = workbook.getSheetAt(0);
        // 读取第一行第一列
        Row row = sheet.getRow(0);
        Cell cell = row.getCell(0);
        // 输出单元内容
        System.out.println(cell.getStringCellValue());
        // 操作结束，关闭文件
        is.close();
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14

运行结果

07版本

这里演示的是如何读取07版本的Excel
演示代码

@Test
    public void testRead07() throws Exception{
        InputStream is = new FileInputStream(path+"极客李华的观众统计表07.xlsx");
        Workbook workbook = new XSSFWorkbook(is);
        Sheet sheet = workbook.getSheetAt(0);
        // 读取第一行第一列
        Row row = sheet.getRow(0);
        Cell cell = row.getCell(0);
        // 输出单元内容
        System.out.println(cell.getStringCellValue());
        // 操作结束，关闭文件
        is.close();
    }
1
2
3
4
5
6
7
8
9
10
11
12
13

运行结果

读这个Excel

@Test
    public void WriteExcel03() throws Exception {
        InputStream is = new FileInputStream(path + "会员消费商品明细表.xls");
        
        // 创建工作薄
        Workbook workbook = new HSSFWorkbook(is);
        
        // 获取第一个工作表
        Sheet sheet = workbook.getSheetAt(0);
        // 读取标题所有内容
        Row rowTitle = sheet.getRow(0);
        if (rowTitle != null) {// 行不为空
            // 读取cell
            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) {// 行不为空
// 读取cell
                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.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 Cell.CELL_TYPE_ERROR:
                                System.out.print("【数据类型错误】");
                                break;
                        }
                        System.out.println(cellValue);
                    }
                }
            }
        }
        is.close();
    }
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

运行结果

公式计算（这个东西了解即可没有很多应用）

现在要对这个Excel表格进行求和处理。

获取A5位置的公式

如果获取的单元格没有公式的话，那么就是打印的结果是没有结果的。

运行结果

@Test
    public void testFormula() throws Exception{
        InputStream is = new FileInputStream(path + "计算公式.xls");
        Workbook workbook = new HSSFWorkbook(is);
        Sheet sheet = workbook.getSheetAt(0);
        // 读取第五行第一列
        // 这个row获取的是
        Row row = sheet.getRow(4);
        Cell cell = row.getCell(0);
        //公式计算器
        FormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);
        // 输出单元内容
        int cellType = cell.getCellType();
        System.out.println(cellType);
        switch (cellType) {
            case Cell.CELL_TYPE_FORMULA://2
        //得到公式
                String formula = cell.getCellFormula();
                System.out.println(formula);
                CellValue evaluate = formulaEvaluator.evaluate(cell);

                String cellValue = evaluate.formatAsString();
                System.out.println(cellValue);
                break;
        }
    }
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

如果大家觉得有用的话，可以关注我下面的微信公众号，极客李华，我会在里面更新更多行业资讯，企业面试内容，编程资源，如何写出可以让大厂面试官眼前一亮的简历等内容，让大家更好学习编程，我的抖音，B站也叫极客李华。