Java+TestNG+Excel 接口自动化测试框架

思路：Excel管理测试用例，JSONPath做预期结果数据处理，测试结果回写Excel

import com.alibaba.fastjson.JSONObject;
 
import com.qa.utils.*;
import org.testng.Assert;
import org.testng.annotations.BeforeClass;
import org.testng.annotations.DataProvider;
import org.testng.annotations.Test;
 
public class AutoTest {
 
    @DataProvider(name="testData")
    public static Object[][] data() throws Exception{
        return ExcelUtils.getTestData(Constant.FilePath,Constant.FileSheet);
    }
 
 
    @Test(dataProvider="testData",description="测试接口")
    public void testApi(
            String rowNumber,
            String caseRowNumber,
            String testCaseName,
            String priority,
            String apiName,
            String url,
            String type,
            String parmsType,
            String parms,
            String assertKeyWord
    ) throws Exception{
        Log.startTestCase(testCaseName);
        JSONObject responseObject = null;
        if ("post".equalsIgnoreCase(type)||"json".equalsIgnoreCase(parmsType)){
            responseObject= RestClient.post(url,parms);
            Log.info("Responce: "+responseObject.toString());
            //WriteResponce.write(Constant.ResponseSheet,responseObject.toString(),Integer.parseInt(rowNumber.split("[.]")[0]),0);
            System.out.println("response: "+responseObject);
        }else {
            //TODO
        }
 
        Log.info("断言Response是否与预期结果一致: "+assertKeyWord);
        try {
            //Assert.assertTrue(responseObject.toString().contains(assertKeyWord));
            Assert.assertTrue(JSONPathUtil.checkPoint(responseObject.toString(),assertKeyWord));
        } catch (AssertionError  error){
            Log.info("断言Response是否与预期结果一致: "+assertKeyWord +" ---> 断言失败");
            ExcelUtils.setCellData(Integer.parseInt(rowNumber.split("[.]")[0]), ExcelUtils.getLastColumnNum(), "Fail");
            Log.info("测试结果成功写入excel数据文件中的测试执行结果列");
            Assert.fail("断言Response是否与预期结果一致: "+assertKeyWord +" 失败");
        }
 
        //System.out.println("**** "+Integer.parseInt(rowNumber.split("[.]")[0]));
        //ExcelUtils.setCellData(Integer.parseInt(rowNumber.split("[.]")[0]),10,"测试执行成功");
        Log.info("断言Response是否与预期结果一致: "+assertKeyWord +" ---> 断言成功");
        ExcelUtils.setCellData(Integer.parseInt(rowNumber.split("[.]")[0]),ExcelUtils.getLastColumnNum(),"Pass");
        Log.info("测试结果成功写入excel数据文件中的测试执行结果列");
        Log.endTestCase(testCaseName);
    }
 
 
    @BeforeClass
    public void beforeClass() throws Exception{
        ExcelUtils.setExcelFile(Constant.FilePath,Constant.FileSheet);
    }
}

import com.alibaba.fastjson.JSONPath;
import com.qa.utils.Log;
 
import java.util.HashMap;
import java.util.Map;
 
public class JSONPathUtil {
 
    public static void main(String[] args) {
 
        String json = "{"store":{"book":[{"title":"高效Java","price":10.2},{"title":"设计模式a","price":12.21},{"title":"重构","isbn":"553","price":8},{"title":"虚拟机","isbn":"395","price":22}],"bicycle":{"color":"red","price":19}}}";
        //String expression1  = (String) JSONPath.read(json,"$.store.book[0].title");
        //int expression2 = (int) JSONPath.read(json,"$.store.book[0].price");
 
        // 设置的检查点,多个检查点用;分隔
        String params = "$.store.book[0].price=10.2;$.store.book[1].title=设计模式";
        String[] data = params.split(";");
        // 定义测试结果的标记
        Boolean flag = false;
 
        //遍历数组，获取每一个检查点在json中对应的数据，存在map中
        Map<String, Object> map = new HashMap<>();
        for (int i = 0; i < data.length; i++) {
            map.put(data[i].split("=")[0], data[i].split("=")[1]);
            System.out.println("检查点"+ (i+1) +"返回的数据：" + JSONPath.read(json, data[i].split("=")[0]));
            System.out.println("检查点"+ (i+1) +"断言的数据：" + map.get(data[i].split("=")[0]));
 
            //判断检查点数据与返回的json数据是否一致
            if (JSONPath.read(json, data[i].split("=")[0]) instanceof String) {
                if (JSONPath.read(json, data[i].split("=")[0]).equals(map.get(data[i].split("=")[0]))) {
                    //System.out.println("Pass A");
                    flag = true;
                } else {
                    //System.out.println("Fail A");
                    flag = false;
                    break;
                }
            } else { // Object转String
                if ((JSONPath.read(json, data[i].split("=")[0]).toString()).equals((map.get(data[i].split("=")[0])))) {
                    //System.out.println("Pass B");
                    flag = true;
                } else {
                    //System.out.println("Fail B");
                    flag = false;
                    break;
                }
            }
        }
        if (flag) {
            System.out.println("【测试执行结果：通过】");
        } else {
            System.out.println("【测试执行结果：失败】");
        }
    }
 
    /**
     * 预期结果校验
     * @param response
     * @param assertKeyWord
     * @return
     */
    public static Boolean checkPoint(String response,String assertKeyWord){
        //分隔检查点
        String[] data = assertKeyWord.split(";");
        // 定义测试结果的标记
        Boolean flag = false;
 
        //遍历数组，获取每一个检查点在json中对应的数据，存在map中
        Map<String, Object> map = new HashMap<>();
        for (int i = 0; i < data.length; i++) {
            map.put(data[i].split("=")[0], data[i].split("=")[1]);
            System.out.println("检查点"+ (i+1) +"返回的数据：" + JSONPath.read(response, data[i].split("=")[0]));
            System.out.println("检查点"+ (i+1) +"断言的数据：" + map.get(data[i].split("=")[0]));
 
            Log.info("检查点"+ (i+1) +"返回的数据：" + JSONPath.read(response, data[i].split("=")[0]));
            Log.info("检查点"+ (i+1) +"断言的数据：" + map.get(data[i].split("=")[0]));
 
            //判断检查点数据与返回的json数据是否一致
            if (JSONPath.read(response, data[i].split("=")[0]) instanceof String) {
                if (JSONPath.read(response, data[i].split("=")[0]).equals(map.get(data[i].split("=")[0]))) {
                    //System.out.println("Pass A");
                    flag = true;
                } else {
                    //System.out.println("Fail A");
                    flag = false;
                    break;
                }
            } else { // Object转String
                if ((JSONPath.read(response, data[i].split("=")[0]).toString()).equals((map.get(data[i].split("=")[0])))) {
                    flag = true;
                } else {
                    flag = false;
                    break;
                }
            }
        }
        if (flag) {
            return true;
        } else {
            return false;
        }
    }
}

public class Constant {
 
 
//public static final String URL = "http://mail.qq.com";
 
//测试数据EXCEL路径
public static final String FilePath = "D:\接口自动化测试.xlsx";
 
// EXCEL测试数据sheet名称
public static final String FileSheet = "测试用例";
 
public static final String ResponseSheet = "response";
 
}

import org.apache.log4j.Logger;
 
public class Log {
 
    // 初始化Log4j日志
    private static Logger Log = Logger.getLogger(com.qa.utils.Log.class.getName());
 
    // 打印测试用例开头的日志
    public static void startTestCase(String sTestCaseName) {
        Log.info("------------------ 测试用例【"+ sTestCaseName + "】" + "开始执行 ------------------");
    }
 
    //打印测试用例结束的日志
    public static void endTestCase(String sTestCaseName) {
        Log.info("------------------ 测试用例【"+ sTestCaseName + "】" + "测试执行结束 ------------------");
 
    }
 
    public static void info(String message) {
        Log.info(message);
    }
 
    public static void warn(String message) {
        Log.warn(message);
    }
 
    public static void error(String message) {
        Log.error(message);
    }
 
    public static void fatal(String message) {
        Log.fatal(message);
    }
 
    public static void debug(String message) {
        Log.debug(message);
    }
 
}

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import org.apache.http.HttpResponse;
import org.apache.http.HttpStatus;
import org.apache.http.client.methods.HttpPost;
import org.apache.http.entity.StringEntity;
import org.apache.http.impl.client.CloseableHttpClient;
import org.apache.http.impl.client.HttpClients;
import org.apache.http.util.EntityUtils;
import java.nio.charset.Charset;
 
public class RestClient {
 
    /**
     * 带json参数的post请求
     * @param url
     * @param parms
     * @return
     */
    public static JSONObject post(String url, String parms) {
 
        CloseableHttpClient httpClient = HttpClients.createDefault();
        HttpPost post = new HttpPost(url);
 
        post.addHeader("content-type", "application/json;charset=utf-8");
        post.setEntity(new StringEntity(parms, Charset.forName("utf-8")));
        HttpResponse response = null;
        String result = null;
        try {
            Log.info("开始发送post请求，请求的URL: " + url);
            Log.info("开始发送post请求，请求的参数: " + parms);
            response = httpClient.execute(post);
            result = EntityUtils.toString(response.getEntity());
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (HttpStatus.SC_OK != response.getStatusLine().getStatusCode()) {
            System.out.println("请求不正确");
        }
 
        JSONObject responseObject = JSON.parseObject(result);
        //System.out.println("response: "+responseObject);
        return responseObject;
    }
}

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
 
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
 
public class ExcelUtils {
 
    private static XSSFSheet sheet;
    private static XSSFWorkbook workbook;
    private static XSSFCell cell;
    private static XSSFRow row;
 
    //指定要操作的excel文件的路径及sheet名称
    public static void setExcelFile(String path,String sheetName) throws Exception{
        try {
            FileInputStream  file = new FileInputStream(path);
            workbook = new XSSFWorkbook(file);
            sheet = workbook.getSheet(sheetName);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
 
    //读取excel文件指定单元格数据(此方法只针对.xlsx后辍的Excel文件)
    public static String getCellData(int rowNum,int colNum) throws Exception{
        try {
            //获取指定单元格对象
            cell = sheet.getRow(rowNum).getCell(colNum);
            //获取单元格的内容
            //如果为字符串类型，使用getStringCellValue()方法获取单元格内容，如果为数字类型，则用getNumericCellValue()获取单元格内容
            String cellData = cell.getStringCellValue();
            return cellData;
        } catch (Exception e) {
            return "";
        }
    }
 
    //在EXCEL的执行单元格中写入数据(此方法只针对.xlsx后辍的Excel文件) rowNum 行号，colNum 列号
    public static void setCellData(int rowNum,int colNum,String Result) throws Exception{
        try {
            //获取行对象
            row = sheet.getRow(rowNum);
            //如果单元格为空，则返回null
            cell = row.getCell(colNum);
            if(cell == null){
                cell=row.createCell(colNum);
                cell.setCellValue(Result);
            }else{
                cell.setCellValue(Result);
            }
            FileOutputStream out = new FileOutputStream(Constant.FilePath);
            //将内容写入excel中
            workbook.write(out);
            out.flush();
            out.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
 
    //从EXCEL文件中获取测试数据
    public static Object[][] getTestData(String excelFilePath,String sheetName) throws IOException {
        //声明一个file文件对象
        File file = new File(excelFilePath);
        //创建一个输入流
        FileInputStream in = new FileInputStream(file);
        //声明workbook对象
        Workbook workbook = null;
        //判断文件扩展名
        String fileExtensionName = excelFilePath.substring(excelFilePath.indexOf("."));
        if(fileExtensionName.equals(".xlsx")){
            workbook = new XSSFWorkbook(in);
        }else {
            workbook = new HSSFWorkbook(in);
        }
 
        //获取sheet对象
        Sheet sheet = workbook.getSheet(sheetName);
        //获取sheet中数据的行数,行号从0始
        int rowCount = sheet.getLastRowNum()-sheet.getFirstRowNum();
 
        List<Object[]> records = new ArrayList<Object[]>();
        //读取数据（省略第一行表头）
        for(int i=1; i<rowCount+1; i++){
            //获取行对象
            Row row = sheet.getRow(i);
            System.out.println(">>>>>>>>>>> "+ row.getLastCellNum());
            //声明一个数组存每行的测试数据,excel最后两列不需传值
            String[] fields = new String[row.getLastCellNum()-2];
            //excel倒数第二列为Y，表示数据行要被测试脚本执行，否则不执行
            if(row.getCell(row.getLastCellNum()-2).getStringCellValue().equals("Y")){
            //if(row.getCell(10).getStringCellValue().equals("Y")){
                for(int j=0; j<row.getLastCellNum()-2; j++){
                    //System.out.println("cell:"+row.getCell(j));
                    //判断单元格数据是数字还是字符
                    switch (row.getCell(j).getCellType()){
                        case XSSFCell.CELL_TYPE_NUMERIC:
                            fields[j] = String.valueOf(row.getCell(j).getNumericCellValue());
                            //System.out.println(fields[j]+":"+String.valueOf(row.getCell(j).getNumericCellValue()));
                            break;
                        case XSSFCell.CELL_TYPE_STRING:
                            fields[j] = row.getCell(j).getStringCellValue();
                            //System.out.println(fields[j]+":"+row.getCell(j).getStringCellValue());
                            break;
                        default:
                            fields[j] = row.getCell(j).getStringCellValue();
                            break;
                    }
                }
 
                /*for (int k=0;k<fields.length;k++){
                    System.out.println("*****:"+fields>>>>>>>>>>>[k]);
                }*/
                //System.out.println("********:"+sheet.getRow(0).getLastCellNum());
                records.add(fields);
            }
        }
        //将list转为Object二维数据
        Object[][] results = new Object[records.size()][];
        //设置二维数据每行的值，每行是一个object对象
        for(int i=0; i<records.size(); i++){
            results[i]=records.get(i);
        }
        return results;
    }
 
    public static int getLastColumnNum(){
        //返回数据文件最后一列的列号，如果有12列则返回11
        return sheet.getRow(0).getLastCellNum()-1;
    }
}

Excel管理测试用例