第五步：創(chuàng)建TestNg測試用例并使用Data Provider從Excel讀取數(shù)據(jù)
　　1.創(chuàng)建TestNG類：DataProviderWithExcel，從excel中導(dǎo)入數(shù)據(jù)，并執(zhí)行query操作
package testData;
import org.apache.http.HttpEntity;
import org.apache.http.client.methods.CloseableHttpResponse;
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 org.json.JSONObject;
import org.testng.annotations.DataProvider;
import org.testng.annotations.Test;
public class DataProviderWithExcel {
@DataProvider
public Object[][] Authentication() throws Exception{
Object[][] testObjArray=ExcelUtils.getTableArray("F://Users//Tangxi//workspace//apiTest//src//testData//testData.xlsx"， "Sheet1");
return (testObjArray);
}
@Test(dataProvider="Authentication")
public void QuickStart(String p1，String pd) throws Exception {
CloseableHttpClient httpclient = HttpClients.createDefault();
try {
HttpPost httpPost = new HttpPost(p1);
JSONObject jsonParam=new JSONObject(pd);
StringEntity entity = new StringEntity(jsonParam.toString()，"utf-8");
entity.setContentEncoding("UTF-8");
entity.setContentType("application/json");
httpPost.setEntity(entity);
CloseableHttpResponse response2 = httpclient.execute(httpPost);
try {
System.out.println(response2.getStatusLine());
HttpEntity entity2 = response2.getEntity();
System.out.println("Response content: " + EntityUtils.toString(entity2));
// do something useful with the response body
// and ensure it is fully consumed
EntityUtils.consume(entity2);
} finally {
response2.close();
}
} finally {
httpclient.close();
}
}
}
　　第六步：根據(jù)測試用例名稱運行測試
package testData;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
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;
public class ExcelUtils2 {
private static XSSFSheet ExcelWSheet;
private static XSSFWorkbook ExcelWBook;
private static XSSFCell Cell;
private static XSSFRow Row;
//This method is to set the File path and to open the Excel file， Pass Excel Path and Sheetname as Arguments to this method
public static void setExcelFile(String Path，String SheetName) throws Exception {
try {
// Open the Excel file
FileInputStream ExcelFile = new FileInputStream(Path);
// Access the required test data sheet
ExcelWBook = new XSSFWorkbook(ExcelFile);
ExcelWSheet = ExcelWBook.getSheet(SheetName);
} catch (Exception e){
throw (e);
}
}
public static Object[][] getTableArray(String FilePath， String SheetName， int iTestCaseRow)    throws Exception
{
String[][] tabArray = null;
try{
FileInputStream ExcelFile = new FileInputStream(FilePath);
// Access the required test data sheet
ExcelWBook = new XSSFWorkbook(ExcelFile);
ExcelWSheet = ExcelWBook.getSheet(SheetName);
int startCol = 1;
int ci=0，cj=0;
int totalRows = 1;
int totalCols = 2;
tabArray=new String[totalRows][totalCols];
for (int j=startCol;j<=totalCols;j++， cj++)
{
tabArray[ci][cj]=getCellData(iTestCaseRow，j);
System.out.println(tabArray[ci][cj]);
}
}
catch (FileNotFoundException e)
{
System.out.println("Could not read the Excel sheet");
e.printStackTrace();
}
catch (IOException e)
{
System.out.println("Could not read the Excel sheet");
e.printStackTrace();
}
return(tabArray);
}
//This method is to read the test data from the Excel cell， in this we are passing parameters as Row num and Col num
public static String getCellData(int RowNum， int ColNum) throws Exception{
try{
Cell = ExcelWSheet.getRow(RowNum).getCell(ColNum);
String CellData = Cell.getStringCellValue();
return CellData;
}catch (Exception e){
return"";
}
}
public static String getTestCaseName(String sTestCase)throws Exception{
String value = sTestCase;
try{
int posi = value.indexOf("@");
value = value.substring(0， posi);
posi = value.lastIndexOf(".");
value = value.substring(posi + 1);
System.out.println(value);
return value;
}catch (Exception e){
throw (e);
}
}
public static int getRowContains(String sTestCaseName， int colNum) throws Exception{
int i;
try {
int rowCount = ExcelUtils2.getRowUsed();
for ( i=0 ; i<rowCount; i++){
if  (ExcelUtils2.getCellData(i，colNum).equalsIgnoreCase(sTestCaseName)){
break;
}
}
return i;
}catch (Exception e){
throw(e);
}
}
public static int getRowUsed() throws Exception {
try{
int RowCount = ExcelWSheet.getLastRowNum();
return RowCount;
}catch (Exception e){
System.out.println(e.getMessage());
throw (e);
}
}
}
　　后的測試用例
　　1.獲取測試用例名稱
　　2.根據(jù)測試用例名稱獲取對應(yīng)的行號
　　3.從對應(yīng)的行中獲取測試數(shù)據(jù)
package testData;
import java.util.concurrent.TimeUnit;
import org.testng.annotations.Test;
import org.testng.annotations.BeforeMethod;
import org.apache.http.HttpEntity;
import org.apache.http.client.methods.CloseableHttpResponse;
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 org.json.JSONObject;
import org.testng.annotations.AfterMethod;
import org.testng.annotations.DataProvider;
import testData.ExcelUtils2;
public class DataProviderWithExcel_002 {
private String sTestCaseName;
private int iTestCaseRow;
@Test(dataProvider = "Authentication")
public void QuickStart(String p1，String pd) throws Exception {
CloseableHttpClient httpclient = HttpClients.createDefault();
try {
HttpPost httpPost = new HttpPost(p1);
JSONObject jsonParam=new JSONObject(pd);
StringEntity entity = new StringEntity(jsonParam.toString()，"utf-8");
entity.setContentEncoding("UTF-8");
entity.setContentType("application/json");
httpPost.setEntity(entity);
CloseableHttpResponse response2 = httpclient.execute(httpPost);
try {
System.out.println(response2.getStatusLine());
HttpEntity entity2 = response2.getEntity();
System.out.println("Response content: " + EntityUtils.toString(entity2));
// do something useful with the response body
// and ensure it is fully consumed
EntityUtils.consume(entity2);
} finally {
response2.close();
}
} finally {
httpclient.close();
}
}
@DataProvider
public Object[][] Authentication() throws Exception{
// Setting up the Test Data Excel file
ExcelUtils2.setExcelFile("F://Users//Tangxi//workspace//apiTest//src//testData//testData.xlsx"，"Sheet1");
sTestCaseName = this.toString();
// From above method we get long test case name including package and class name etc.
// The below method will refine your test case name， exactly the name use have used
sTestCaseName = ExcelUtils2.getTestCaseName(this.toString());
System.out.println(sTestCaseName);
// Fetching the Test Case row number from the Test Data Sheet
// Getting the Test Case name to get the TestCase row from the Test Data Excel sheet
iTestCaseRow = ExcelUtils2.getRowContains(sTestCaseName，0);
System.out.println(iTestCaseRow);
Object[][] testObjArray = ExcelUtils2.getTableArray("F://Users//Tangxi//workspace//apiTest//src//testData//testData.xlsx"，"Sheet1"，iTestCaseRow);
return (testObjArray);
}
}