第五步：创建TestNg测试用例并使用Data Provider从Excel读取数据
　　1.创建TestNG类：DataProviderWithExcel，从excel中导入数据，并执行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();
}
}
}
　　第六步：根据测试用例名称运行测试
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.根据测试用例名称获取对应的行号
　　3.从对应的行中获取测试数据
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);
}
}