Java+Excel+POI+testNG基于数据驱动做一个简单的接口测试【杭州多测师

一、创建一个apicases.xlsx放入到eclipse的resource里面，然后refresh刷新一下

二、在pom.xml文件中加入poi和testng的mvn repository、然后在eclipse的对应目录下放入features和plugins，重启eclipse就可以看到testNG了

        <!--poi excel解析 -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.15</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.testng/testng -->
        <dependency>
            <groupId>org.testng</groupId>
            <artifactId>testng</artifactId>
            <version>6.9.8</version>
            <scope>test</scope>
        </dependency>

三、封装一个读取Excel表格的工具类

package com.duoceshi.test;

import java.io.InputStream;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.usermodel.Row.MissingCellPolicy;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;;

public class ExcelUtil1 {
    
    public static Object[][] readExcel1(String excelPath) throws Exception {
        InputStream is = null;
        Workbook workbook = null;
        try {
            is = ExcelUtil1.class.getResourceAsStream(excelPath);
            workbook = WorkbookFactory.create(is);
            Sheet sheet = workbook.getSheetAt(0);  //获取到具体的sheet
            //通过sheet获取行数
            int lastRowNum = sheet.getLastRowNum();
            Object[][] allData = new Object[lastRowNum][];  //创建二维数组存Excel表格数据
            System.out.println(lastRowNum);
            for (int i = 1; i <= lastRowNum; i++) {
                Row row = sheet.getRow(i);
                
                int lastColumn = row.getLastCellNum();
                Object[] objects = new Object[lastColumn];
                for (int j = 0; j < lastColumn; j++) {
                    Cell cell = row.getCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK);
                    cell.setCellType(CellType.STRING);  //把cell当成字符串处理
                    String value = cell.getStringCellValue();
                    System.out.println(value);
                    objects[j] = value;
                }
                allData[i-1] = objects;
            }
            return allData;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            is.close();
            workbook.close();
        }
        return null;
    }

    public static void main(String[] args) throws Exception {
        readExcel1("/apicases.xlsx");
    }

}

四、通过testng创建一个testng类，引入@DataProvider数据提供者

package com.duoceshi.test;

import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Set;

import org.apache.http.HttpEntity;
import org.apache.http.NameValuePair;
import org.apache.http.client.methods.CloseableHttpResponse;
import org.apache.http.client.methods.HttpPost;
import org.apache.http.client.utils.URLEncodedUtils;
import org.apache.http.impl.client.CloseableHttpClient;
import org.apache.http.impl.client.HttpClients;
import org.apache.http.message.BasicNameValuePair;
import org.apache.http.util.EntityUtils;
import org.testng.Assert;
import org.testng.annotations.DataProvider;
import org.testng.annotations.Test;

import com.alibaba.fastjson.JSON;

/**
 * 读取Excel表格数据 做数据驱动
 * 
 * @author 多测师王sir
 *
 */
public class LoginTest1 {

    @DataProvider
    public Object[][] dp() throws Exception {
        Object[][] allData = ExcelUtil1.readExcel1("/apicases.xlsx");
        for (int i = 0; i < allData.length; i++) {
            Object[] objects = allData[i];
            for (int j = 0; j < objects.length; j++) {
                Object object = objects[j];
                System.out.println(object);
            }
        }
        return allData;
    }

    @SuppressWarnings("unchecked")
    @Test(dataProvider = "dp")
    public void loginTest(String url, String requestBody, String result) throws Exception {
        List<NameValuePair> allData = new ArrayList<NameValuePair>();
        LinkedHashMap<String, String> map = new LinkedHashMap<String, String>();
        map = JSON.parseObject(requestBody, map.getClass());  //把字符串转换为map类型
        Set<String> ketSet = map.keySet();
        for (String key : ketSet) {
            String value =  map.get(key);
            allData.add(new BasicNameValuePair(key, value));
        }
        String requestBodyStr = URLEncodedUtils.format(allData, "utf-8");
        String baseUrl = url + "?" + requestBodyStr;
        CloseableHttpClient client =  HttpClients.createDefault();
        HttpPost httpPost = new HttpPost(baseUrl);
        CloseableHttpResponse reponseStr = client.execute(httpPost);
        HttpEntity httpEntity =  reponseStr.getEntity();
        String responseEntity = EntityUtils.toString(httpEntity);
        //对响应文本进行断言
        Assert.assertTrue(responseEntity.contains(result));
        System.out.println(responseEntity);
    }
}