Java自动化测试(Excel文件解析 9)

Java自动化测试(Excel文件解析 9)

Excel操作

Maven中添加读取excel的第三方包POI

官网:https://poi.apache.org/

Maven:https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml

1
2
3
4
5
6
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>

新建一个测试的excel

exceldemo

将excel放到:src/test/resources

读取excel步骤

  1. 打开excel
  2. 获取所有sheet
  3. 获取指定sheet
  4. 获取指定row
  5. 指定的cell单元格
  6. 获取单元格内容
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
package com.zhongxin.day10.excel;

import org.apache.poi.ss.usermodel.*;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;

public class ReadDemo {
public static void main(String[] args) {
//读取excel
FileInputStream fis = null;
try {
// 1. 打开excel
fis = new FileInputStream("src/test/resources/demo.xlsx");
// 2. 获取所有sheet
Workbook sheets = WorkbookFactory.create(fis);
// 3. 获取指定sheet
Sheet sheet = sheets.getSheet("Sheet1"); // 通过名称
Sheet sheet1 = sheets.getSheetAt(0); // 通过索引
// 4. 获取指定row
Row row = sheet.getRow(2);
// 5. 指定的cell单元格
Cell cell = row.getCell(1, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
// 6. 获取单元格内容
String cellValue = cell.getStringCellValue();
System.out.println(cellValue);
fis.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
if (fis != null) {
try {
fis.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}

}

读取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
package com.zhongxin.day10.excel;

import org.apache.poi.ss.usermodel.*;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;

public class ReadDemo2 {
public static void main(String[] args) {
FileInputStream fis = null;
try {
// 1. 打开excel
fis = new FileInputStream("src/test/resources/demo.xlsx");
// 2. 获取所有sheet
Workbook sheets = WorkbookFactory.create(fis);
// 3. 获取指定sheet
Sheet sheet = sheets.getSheet("Sheet1"); // 通过名称
Sheet sheet1 = sheets.getSheetAt(0); // 通过索引
// 4. 获取所有row
// 4.1 增强for
for (Row row : sheet) {
for (Cell cell : row) {
// 强行转换单元格类型
cell.setCellType(CellType.STRING);
String cellValue = cell.getStringCellValue();
System.out.print(cellValue + ",");
}
System.out.println();
}

// 4.2 普通for
int lastRowNum = sheet.getLastRowNum();
for (int i = 0; i <= lastRowNum; i++) {
Row row = sheet.getRow(i);
int lastCellNum = row.getLastCellNum();
for (int j = 0; j < lastCellNum; j++) {
Cell cell = row.getCell(j, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
cell.setCellType(CellType.STRING);
String cellValue = cell.getStringCellValue();
System.out.print(cellValue + ",");
}
System.out.println();
}

fis.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
if (fis != null) {
try {
fis.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
}

增强for

1
2
3
4
5
6
7
8
9
for (Row row : sheet) {
for (Cell cell : row) {
// 强行转换单元格类型
cell.setCellType(CellType.STRING);
String cellValue = cell.getStringCellValue();
System.out.print(cellValue + ",");
}
System.out.println();
}

普通for

1
2
3
4
5
6
7
8
9
10
11
12
int lastRowNum = sheet.getLastRowNum();
for (int i = 0; i <= lastRowNum; i++) {
Row row = sheet.getRow(i);
int lastCellNum = row.getLastCellNum();
for (int j = 0; j < lastCellNum; j++) {
Cell cell = row.getCell(j, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
cell.setCellType(CellType.STRING);
String cellValue = cell.getStringCellValue();
System.out.print(cellValue + ",");
}
System.out.println();
}

excel 写入

写=创建excel+创建sheet+创建row+创建cell

修改操作=读取+写入

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
package com.zhongxin.day10.excel;

import org.apache.poi.ss.usermodel.*;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

public class WriteDemo {
public static void main(String[] args) throws IOException {
// excel 写入
// 1.打开excel
FileInputStream fis = new FileInputStream("src/test/resources/demo.xlsx");
// 2. 获取所有sheet
Workbook sheets = WorkbookFactory.create(fis);
// 3. 获取指定sheet
Sheet sheet = sheets.getSheetAt(0);
// 4. 获取指定row
Row row = sheet.getRow(1);
// 5. 获取指定cell
Cell cell = row.getCell(2, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
// 6. 修改值
cell.setCellValue(99);
// 7. 流输出对象
FileOutputStream fos = new FileOutputStream("src/test/resources/demo.xlsx");
// 8. 把java内存中内容写入excel文件中
sheets.write(fos);
// 9. 关流
fis.close();
fos.close();
}
}

XML操作

Xml:扩展性标记语言

特点:可扩展性,在遵循xml语法的前提下支持自定义和修改

xml语法

声明

1
<?xml version="1.0" encoding="UTF-8"?>

根元素

XML必须包含根元素,它是所有其他元素的父元素

所有元素有开始就有结束

大小写敏感

嵌套使用需要注意嵌套的顺序

1
2
3
4
5
6
<?xml version="1.0" encoding="UTF-8"?>
<sutdent>
<name>张三</name>
<age>18</age>
<score>100</score>
</sutdent>

dom4j

1
2
3
4
5
6
<!-- https://mvnrepository.com/artifact/dom4j/dom4j -->
<dependency>
<groupId>dom4j</groupId>
<artifactId>dom4j</artifactId>
<version>1.6.1</version>
</dependency>

xml读取(了解)

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
package com.zhongxin.day10.xml;

import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;

import java.io.FileInputStream;
import java.io.IOException;
import java.util.List;

public class XmlDemo {
public static void main(String[] args) throws IOException, DocumentException {
// xml读取
// 1. xml读取对象
SAXReader reader = new SAXReader();
FileInputStream fis = new FileInputStream("src/test/resources/Student.xml");

// 2. 整颗dom树
Document document = reader.read(fis);
// 3. 获取root标签
Element rootElement = document.getRootElement();
// 4. 获取root标签下的一级子标签
List<Element> subElements1 = rootElement.elements();
for (Element element : subElements1) {
System.out.println(element.getName() + "====" + element.getData());
// 5. 获取root标签下的二子标签
List<Element> subElement2 = element.elements();
if (subElement2 != null && subElement2.size() > 0) {
for (Element element1 : subElement2) {
System.out.println(element1.getName() + "====" + element1.getData());
}
}
}
fis.close();
}
}
 wechat
欢迎您扫一扫上面的微信公众号,订阅我的博客!
您的支持将鼓励我继续创作!