EXCEL基本写操作

excel中的对象

对象的对应实现类

导入maven依赖

<!--导入依赖-->
<dependencies>
    <!--xls(03)-->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.10-FINAL</version>
    </dependency>
    <!--xlsx(07)-->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>3.10-FINAL</version>
    </dependency>
    <!--日期格式化工具-->
    <dependency>
        <groupId>joda-time</groupId>
        <artifactId>joda-time</artifactId>
        <version>2.10.10</version>
    </dependency>
</dependencies>

joda-time是一个常用的时间处理类库

大数据量的写入

@Test
public void  testWrite03BigData() throws IOException {
    // 时间
    long begin = System.currentTimeMillis();
    // 创建一个工作簿
    Workbook workbook = new HSSFWorkbook();
    // 创建表
    Sheet sheet = workbook.createSheet();
    // 写入数据
    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("over");
    // 开启IO流
    FileOutputStream fileOutputStream = new FileOutputStream(PATH + "testWrite03BigData.xls");
    // 写出数据
    workbook.write(fileOutputStream);
    // 关闭IO流
    fileOutputStream.close();
    long end = System.currentTimeMillis();

    System.out.print((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
30
31
32
33
34
35

   ![image-20220529124847258](https://s2.loli.net/2022/05/29/KT4JCeHx3RirWLQ.png)

   > xls只能写入65536行数据，否则报错

   ![image-20220529125102389](https://s2.loli.net/2022/05/29/ZNsr6bJ9GVTQ1kg.png)

2. ```java
   @Test
   public void  testWrite07BigData() throws IOException {
       // 时间
       long begin = System.currentTimeMillis();
       // 创建一个工作簿
       Workbook workbook = new XSSFWorkbook();
       // 创建表
       Sheet sheet = workbook.createSheet();
       // 写入数据
       for (int rowNum = 0; rowNum < 65537; 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");
       // 开启IO流
       FileOutputStream fileOutputStream = new FileOutputStream(PATH + "testWrite07BigData.xlsx");
       // 写出数据
       workbook.write(fileOutputStream);
       // 关闭IO流
       fileOutputStream.close();
       long end = System.currentTimeMillis();
   
       System.out.print((double) (end - begin) / 1000);
   }


![image-20220529125443725](https://s2.loli.net/2022/05/29/VzkSL3TiCrIFhYo.png)

> xlsx写入速度较慢，数据无上限

@Test
public void  testWrite07BigDataS() throws IOException {
    // 时间
    long begin = System.currentTimeMillis();
    // 创建一个工作簿
    Workbook workbook = new SXSSFWorkbook();
    // 创建表
    Sheet sheet = workbook.createSheet();
    // 写入数据
    for (int rowNum = 0; rowNum < 65537; 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");
    // 开启IO流
    FileOutputStream fileOutputStream = new FileOutputStream(PATH + "testWrite07BigDataS.xlsx");
    // 写出数据
    workbook.write(fileOutputStream);
    // 关闭IO流
    fileOutputStream.close();
    // 清楚加速版中运用内存缓冲带来的临时文件
    ((SXSSFWorkbook)workbook).dispose();
    long end = System.currentTimeMillis();

    System.out.print((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

   ![image-20220529130020834](https://s2.loli.net/2022/05/29/sp4QLahNcKbzo1R.png)

   > xlsx加速版，SXSSFWorkbook类，结束时需要清除内存缓存。

# Excel基本读取及注意

1. ```java
   @Test
   public void testRead03() throws Exception {
       // 获取文件流
       FileInputStream fileInputStream = new FileInputStream(PATH + "\\kuang-poi狂神观众统计表03.xls");
       // 1.创建一个工作簿
       HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream);
       // 2.得到表
       Sheet sheet = workbook.getSheetAt(0);
       // 3.得到行
       Row row = sheet.getRow(0);
       // 4.得到列
       Cell cell = row.getCell(0);
   
       System.out.println(cell.getStringCellValue());
       // 关闭流
       fileInputStream.close();
   
   }


![image-20220529134938222](https://s2.loli.net/2022/05/29/LGOxZqfeblBVvCu.png)

@Test
public void testRead07() throws Exception {
    // 获取文件流
    FileInputStream fileInputStream = new FileInputStream(PATH + "\\kuang-poi狂神观众统计表07.xlsx");
    // 1.创建一个工作簿
    Workbook workbook = new XSSFWorkbook(fileInputStream);
    // 2.得到表
    Sheet sheet = workbook.getSheetAt(0);
    // 3.得到行
    Row row = sheet.getRow(0);
    // 4.得到列
    Cell cell = row.getCell(0);
    Cell cell1 = row.getCell(1);
    System.out.println(cell.getStringCellValue());
    System.out.println(cell1.getNumericCellValue());
    // 关闭流
    fileInputStream.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

   ![image-20220529135023165](https://s2.loli.net/2022/05/29/RVZH5SewdOo4Wth.png)

# 读取不同类型数据，批量读取，可以直接用

```java
@Test
public void testCellType() throws Exception {
    // 获取文件流
    FileInputStream fileInputStream = new FileInputStream(PATH + "\\kuang-poi狂神观众统计表07.xlsx");
    // 1.创建一个工作簿
    Workbook workbook = new XSSFWorkbook(fileInputStream);
    // 2.得到表
    Sheet sheet = workbook.getSheetAt(0);

    // 获取表中的内容
    // 获取行数
    int rowCount = sheet.getPhysicalNumberOfRows();
    for (int rowNum = 0; rowNum < rowCount; rowNum++) {
        Row rowData = sheet.getRow(rowNum);
        if (rowData != null) {
            // 获取每行列数
            int cellCount = rowData.getPhysicalNumberOfCells();
            // 遍历单元格
            for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                // 读取单元格
                Cell cell = rowData.getCell(cellNum);
                String cellValue = "";
                // 匹配单元格数据类型
                if (cell != null) {
                    int cellType = cell.getCellType();
                    switch (cellType) {
                        case Cell.CELL_TYPE_STRING: // 字符串
                            System.out.print("[String]");
                            cellValue = cell.getStringCellValue();
                            break;
                        case Cell.CELL_TYPE_BOOLEAN: // 布尔值
                            System.out.print("[BOOLEAN]");
                            cellValue = String.valueOf(cell.getBooleanCellValue());
                            break;
                        case Cell.CELL_TYPE_BLANK: // 空
                            System.out.print("[BLANK]");
                            break;
                        case Cell.CELL_TYPE_NUMERIC: // 数字（日期，普通数字）
                            System.out.print("[NUMERIC]");
                            if (DateUtil.isCellDateFormatted(cell)){ // 日期
                                System.out.print("[日期]");
                                Date date = cell.getDateCellValue();
                                cellValue = new DateTime(date).toString("yyyy-MM-dd");
                            } else{
                                System.out.print("[转换为字符串输出]");
                                cell.setCellType(Cell.CELL_TYPE_STRING);
                                cellValue = cell.toString();
                            }
                            break;
                        case Cell.CELL_TYPE_ERROR: // 错误
                            System.out.print("[数据类型错误]");
                            break;
                    }
                    System.out.println(cellValue);
                }
            }
        }
        fileInputStream.close();
    }
}

了解-计算公式

@Test
public void testFormula() throws Exception {
    // 开启IO流
    FileInputStream fileInputStream = new FileInputStream(PATH + "\\公式.xls");
    // 获取工作簿
    Workbook workbook = new HSSFWorkbook(fileInputStream);
    // 获取工作表
    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;
    }
}

easyExcel操作

导入依赖

<!-- easyExcel -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.1.0</version>
</dependency>

固定套路

1.写入根据固定类格式进行写入

2.读取根据监听器设置的规则进行读取