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

Excel操作

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

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

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

<!-- 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

将excel放到： src/test/resources

读取excel步骤

1. 打开excel

2. 获取所有sheet

3. 获取指定sheet

4. 获取指定row

5. 指定的cell单元格

6. 获取单元格内容

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中全部内容

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

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

普通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();
}

excel 写入

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

修改操作=读取+写入

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语法

声明

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

根元素

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

所有元素有开始就有结束

大小写敏感

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

<?xml version="1.0" encoding="UTF-8"?>
<sutdent>
    <name>张三</name>
    <age>18</age>
    <score>100</score>
</sutdent>

dom4j

<!-- https://mvnrepository.com/artifact/dom4j/dom4j -->
<dependency>
    <groupId>dom4j</groupId>
    <artifactId>dom4j</artifactId>
    <version>1.6.1</version>
</dependency>

xml读取(了解)

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