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":{}}