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

一、创建一个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 {@DataProviderpublic 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);}
}

五、运行输出结果：

{"code":"200","msg":"登录成功！","model":{}}
{"code":"400","msg":"登录密码不正确！","model":{}}