java接口自动化系列（04）：读取数据文件并封装对象

 

本系列汇总，请查看这里：https://www.cnblogs.com/uncleyong/p/15867903.html 

实现目标

把excel中数据读取出来，并封装到对象中，这样注入测试数据到测试方法的时候，一次注入一个对象，而不用写很多参数，另外，获取测试数据（比如url等），直接通过对象的get方法就可以很方便的获取到。

 

添加pom依赖

操作excel的依赖

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

 

测试数据文件放caseData目录

caseData.xlsx

 

修改testng.xml

指定参数：数据文件路径、用例sheet名、常用变量sheet名

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE suite SYSTEM "http://testng.org/testng-1.0.dtd">
<suite name="apiAutoTest" verbose="1">
    <parameter name="variableSheetName" value="variables"/>
    <test name="register-login-add-findByName" enabled="true">  <!--test必须有name属性-->
        <parameter name="excelPath" value="caseData/caseData.xlsx"/>
        <parameter name="dataSheetName" value="case"/>
        <classes>
            <class name="com.qzcsbj.autotest.testcase.BaseCase"/>
        </classes>
    </test>
</suite>

 

创建实体类

用例

package com.qzcsbj.autotest.entity;

/**
 * @博客 : www.cnblogs.com/uncleyong
 * @微信 : ren168632201
 * @描述 : <case类>
 */
public class CaseData {
    private String caseId;
    private String apiName;
    private String describe;
    private String url;
    private String requestType;
    private String headers;
    private String cookies;
    private String parameters;
    private String uploadFile;
    private String initSql;
    private String globalVariables;
    private String assertFields;

    public String getCaseId() {
        return caseId;
    }

    public void setCaseId(String caseId) {
        this.caseId = caseId;
    }

    public String getApiName() {
        return apiName;
    }

    public void setApiName(String apiName) {
        this.apiName = apiName;
    }

    public String getDescribe() {
        return describe;
    }

    public void setDescribe(String describe) {
        this.describe = describe;
    }

    public String getUrl() {
        return url;
    }

    public void setUrl(String url) {
        this.url = url;
    }

    public String getRequestType() {
        return requestType;
    }

    public void setRequestType(String requestType) {
        this.requestType = requestType;
    }

    public String getHeaders() {
        return headers;
    }

    public void setHeaders(String headers) {
        this.headers = headers;
    }

    public String getCookies() {
        return cookies;
    }

    public void setCookies(String cookies) {
        this.cookies = cookies;
    }

    public String getParameters() {
        return parameters;
    }

    public void setParameters(String parameters) {
        this.parameters = parameters;
    }

    public String getUploadFile() {
        return uploadFile;
    }

    public void setUploadFile(String uploadFile) {
        this.uploadFile = uploadFile;
    }

    public String getInitSql() {
        return initSql;
    }

    public void setInitSql(String initSql) {
        this.initSql = initSql;
    }

    public String getGlobalVariables() {
        return globalVariables;
    }

    public void setGlobalVariables(String globalVariables) {
        this.globalVariables = globalVariables;
    }

    public String getAssertFields() {
        return assertFields;
    }

    public void setAssertFields(String assertFields) {
        this.assertFields = assertFields;
    }

    @Override
    public String toString() {
        return "CaseData{" +
                "caseId='" + caseId + '\'' +
                ", apiName='" + apiName + '\'' +
                ", describe='" + describe + '\'' +
                ", url='" + url + '\'' +
                ", requestType='" + requestType + '\'' +
                ", headers='" + headers + '\'' +
                ", cookies='" + cookies + '\'' +
                ", parameters='" + parameters + '\'' +
                ", uploadFile='" + uploadFile + '\'' +
                ", initSql='" + initSql + '\'' +
                ", globalVariables='" + globalVariables + '\'' +
                ", assertFields='" + assertFields + '\'' +
                '}';
    }
}

 

常用变量

package com.qzcsbj.autotest.entity;

/**
 * @公众号 : 全栈测试笔记
 * @博客 : www.cnblogs.com/uncleyong
 * @微信 : ren168632201
 * @描述 : <>
 */
public class Variable {
    private String name;
    private String value;
    private String description;

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getValue() {
        return value;
    }

    public void setValue(String value) {
        this.value = value;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    @Override
    public String toString() {
        return "Variable{" +
                "name='" + name + '\'' +
                ", value='" + value + '\'' +
                ", description='" + description + '\'' +
                '}';
    }
}

 

创建工具类

ExcelUtil.java，读取excel文件中的数据

package com.qzcsbj.autotest.utils;

import com.qzcsbj.autotest.entity.CaseData;
import org.apache.log4j.Logger;
import org.apache.poi.ss.usermodel.*;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.List;

/**
 * @博客 : www.cnblogs.com/uncleyong
 * @微信 : ren168632201
 * @描述 : <解析指定excel表单的数据，封装到对象中：使用泛型可以处理不同类型的对象，返回泛型集合>
 */
public class ExcelUtil {
    public static Logger logger = Logger.getLogger(ExcelUtil.class);

    // 解析指定excel表单的数据，封装到对象中【对象类型使用泛型】
    public static <T> List<T> loadExcel(String excelPath, String sheetName, Class<T> clazz){
        logger.info("===================开始读取sheet: " + sheetName);
        // 创建一个list
        List<T> list = new ArrayList<T>();
        InputStream in = null;
        // 创建WorkBook对象
        try {
            File file = new File(excelPath);
            in = new FileInputStream(file);
            // 获取workbook对象
            Workbook workbook = WorkbookFactory.create(in);
            // 获取sheet对象
            Sheet sheet = workbook.getSheet(sheetName);
            // 获取第一行，Row是行对象类型，通过行对象可以操作列
            Row firstRow = sheet.getRow(0);
            // 获取最后一列的列号
            int lastCellNum = firstRow.getLastCellNum(); 
            // System.out.println(lastCellNum);

            // 定义存放表头的数组
            String[] titles = new String[lastCellNum];
            // 将表头放入数组
            for (int i = 0; i < lastCellNum; i++) {
                // 通过行对象和列索引，获取单元格对象
                Cell cell = firstRow.getCell(i, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                // 设置列的类型为字符串
                // cell.setCellType(CellType.STRING);
                // 获取单元格的值
                String title = cell.getStringCellValue();
                // title = title.substring(0,title.indexOf(":")); 
                // 值保存到数组
                titles[i] = title;
            }
            // 打印解析出来的标题
            // logger.info("解析出来的首行标题：" + Arrays.toString(titles));

            // 获取sheet最后一行的行号
            int lastRowNum = sheet.getLastRowNum();
            // System.out.println(lastRowNum);
            // 循环处理每一行数据，从2行开始是数据行
            for (int i = 1; i <= lastRowNum  ; i++) {
                // 每行数据一个对象
                T obj = clazz.newInstance();
                // 获取一行数据
                Row rowData = sheet.getRow(i);
                if (rowData==null || rowDataIsEmpty(rowData)){
                    continue;
                }
                // 获取此行的列数据，封装到caseObject对象中
                for (int j = 0; j < lastCellNum ; j++) {
                    Cell cell = rowData.getCell(j, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                    cell.setCellType(CellType.STRING);
                    String cellValue = cell.getStringCellValue();
                    // 打印获取到的值
                    // System.out.print("【"+ titles[j] + "="+ cellValue+"】");
                    // 获取要反射的方法名
                    String methodName = "set" + titles[j];
                    // 获取要反射的方法对象
                    Method method = clazz.getMethod(methodName, String.class);
                    // 反射调用
                    method.invoke(obj, cellValue);
                }
                // logger.info("封装的第【"+i+"】个对象（也就是第"+i+"行数据）：" + obj);
                list.add(obj);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (in!=null){
                try {
                    in.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        logger.info("===================读取sheet完成: " + sheetName);
        return list;
    }


    // 判断行的单元格数据是否都是空
    public static boolean rowDataIsEmpty(Row rowData) {
        int lastCellNum = rowData.getLastCellNum();
        for (int i = 0; i < lastCellNum; i++) {
            Cell cell = rowData.getCell(i, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
            cell.setCellType(CellType.STRING);
            String cellValue = cell.getStringCellValue();
            if (cellValue!=null && cellValue.trim().length()>0){
                return false;
            }
        }
        return true;
    }
}

　　

修改测试类

执行测试用例前，先从数据文件读取数据

package com.qzcsbj.autotest.testcase;

import com.qzcsbj.autotest.entity.CaseData;
import com.qzcsbj.autotest.entity.Variable;
import com.qzcsbj.autotest.utils.ExcelUtil;
import org.apache.log4j.Logger;
import org.testng.annotations.*;
import org.testng.annotations.Optional;
import java.util.*;

/**
 * @博客 : www.cnblogs.com/uncleyong
 * @微信 : ren168632201
 * @描述 : <BaseCase>
 */

public class BaseCase{
    public static Logger logger = Logger.getLogger(BaseCase.class);

    // 保存所有用例对象
    public static List<CaseData> cases = new ArrayList<CaseData>();

    // 存放变量对象的列表
    public static List<Variable> variables = new ArrayList<Variable>();


    @Parameters({ "excelPath", "dataSheetName", "variableSheetName"})
    @BeforeTest
    public void readDataFromExcel(@Optional("caseData/caseData.xlsx") String excelPath, @Optional("case") String dataSheetName, @Optional("variables") String variableSheetName){
        logger.info("excelPath: " + excelPath);
        logger.info("dataSheetName: " + dataSheetName);
        logger.info("variableSheetName: " + variableSheetName);
        cases = ExcelUtil.loadExcel(excelPath, dataSheetName, CaseData.class);
        variables = ExcelUtil.loadExcel(excelPath, variableSheetName, Variable.class);
        logger.info("读取文件获取到的cases对象：" + cases);
        logger.info("读取文件获取到的variables对象：" + variables);
    }
}

　

结果演示

运行xml文件

 

 

通过上面结果可以看到，数据已经读取成功并保存到对象中。