TestNG+Excel+(HTTP+JSON) 简单接口测试

package main.java; import org.apache.poi.ss.usermodel.*; import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map; public class ExcelReader {
private String filePath;
private String sheetName;
private Workbook workBook;
private Sheet sheet;
private List<String> columnHeaderList;
private List<List<String>> listData;
private List<Map<String, String>> mapData;
private boolean flag;
public Object[][] results; public ExcelReader(String filePath, String sheetName) {
this.filePath = filePath;
this.sheetName = sheetName;
this.flag = false;
this.load();
} private void load() {
FileInputStream inStream = null;
try {
inStream = new FileInputStream(new File(filePath));
workBook = WorkbookFactory.create(inStream);
sheet = workBook.getSheet(sheetName);
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (inStream != null) {
inStream.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
} private String getCellValue(Cell cell) {
String cellValue = "";
DataFormatter formatter = new DataFormatter();
if (cell != null) {
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
cellValue = formatter.formatCellValue(cell);
} else {
double value = cell.getNumericCellValue();
int intValue = (int) value;
cellValue = value - intValue == 0 ? String.valueOf(intValue) : String.valueOf(value);
}
break;
case Cell.CELL_TYPE_STRING:
cellValue = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_BOOLEAN:
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_FORMULA:
cellValue = String.valueOf(cell.getCellFormula());
break;
case Cell.CELL_TYPE_BLANK:
cellValue = "";
break;
case Cell.CELL_TYPE_ERROR:
cellValue = "";
break;
default:
cellValue = cell.toString().trim();
break;
}
}
return cellValue.trim();
} private void getSheetData() { listData = new ArrayList<>();
mapData = new ArrayList<>();
columnHeaderList = new ArrayList<>();
int numOfRows = sheet.getLastRowNum() + 1;
for (int i = 0; i < numOfRows; i++) {
Row row = sheet.getRow(i);
Map<String, String> map = new HashMap<>();
List<String> list = new ArrayList<>(); if (row != null) {
for (int j = 0; j < row.getLastCellNum(); j++) {
Cell cell = row.getCell(j);
if (i == 0) {
columnHeaderList.add(getCellValue(cell));
} else { map.put(columnHeaderList.get(j), this.getCellValue(cell)); }
list.add(this.getCellValue(cell));
}
}
if (i > 0) {
mapData.add(map);
}
listData.add(list); } flag = true; for (int i = 0; i < listData.size(); i++) {
for (int j = 0; j < listData.get(i).size(); j++) {
System.out.println(listData.get(i).get(j).toString());
}
} } public String getCellData(int row, int col) {
if (row <= 0 || col <= 0) {
return null;
}
if (!flag) {
this.getSheetData();
}
if (listData.size() >= row && listData.get(row - 1).size() >= col) {
return listData.get(row - 1).get(col - 1);
} else {
return null;
}
} public String getCellData(int row, String headerName) {
if (row <= 0) {
return null;
}
if (!flag) {
this.getSheetData();
}
if (mapData.size() >= row && mapData.get(row - 1).containsKey(headerName)) {
return mapData.get(row - 1).get(headerName);
} else {
return null;
}
} public Object[][] getSheetData2() { List<Object[]> result = new ArrayList<>();
listData = new ArrayList<>();
mapData = new ArrayList<>();
columnHeaderList = new ArrayList<>(); int numOfRows = sheet.getLastRowNum() + 1;
System.out.println("总共有 " + numOfRows + "行 ！");
for (int i = 0; i < numOfRows; i++) {
Row row = sheet.getRow(i);
Map<String, String> map = new HashMap<>();
List<String> list = new ArrayList<>();
Object[] o1 = new Object[row.getLastCellNum()]; if (row != null) {
for (int j = 0; j < row.getLastCellNum(); j++) {
// System.out.println("第 "+i+" 行--- row.getLastCellNum()===="+row.getLastCellNum());
Cell cell = row.getCell(j);
if (i == 0) {
o1[j] = this.getCellValue(cell);
// System.out.println(j+"------this.getCellValue(cell)="+this.getCellValue(cell));
columnHeaderList.add(getCellValue(cell));
} else {
o1[j] = this.getCellValue(cell);
// System.out.println(j+"------this.getCellValue(cell)="+this.getCellValue(cell));
map.put(columnHeaderList.get(j), this.getCellValue(cell)); }
list.add(this.getCellValue(cell));
}
}
if (i > 0) {
mapData.add(map);
}
result.add(o1);
listData.add(list);
}
// 测试数据excel数据用 ；
/* for (int i = 0; i < result.size(); i++) {
for (int j = 0; j < result.get(i).length; j++) {
System.out.print(result.get(i)[j]+" | ");
}
System.out.println();
}*/
results = new Object[result.size()][]; for (int i = 0; i < result.size(); i++) {
results[i] = result.get(i);
}
flag = true; System.out.println("results.length==" + results.length);
return results;
} public static void main(String[] args) {
/* Object[][] obj1;
ExcelReader eh = new ExcelReader("C:\\TEST.xlsx", "Sheet1");
Object[][] sheetData2 = eh.getSheetData2();
System.out.println(sheetData2.length + "------------");
for (int i = 1; i < sheetData2.length; i++) {
for (int j = 0; j < sheetData2[i].length; j++) {
System.out.print(sheetData2[i][j] + " | ");
}
System.out.println();
}*/ }
}

package main.java; import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.List; import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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.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 ExcleUtil {
private static XSSFSheet ExcelWSheet;
private static XSSFWorkbook ExcelWBook;
private static XSSFCell Cell;
private static XSSFRow Row;
private static String ExcelFilePath="C:\\TEST.xlsx"; // 设定要设置的Excel的文件路径和Excel 中Sheet名；
// 在读/写Excel 的时候先要调用此方法
public static void setExcleFile(String FilePath, String sheetName) throws Exception {
FileInputStream ExcleFile;
try {
// 实例化Excle文件的FileInputStream 对象；
ExcleFile = new FileInputStream(FilePath);
// 实例化Excle文件的XSSFWorkbook 对象；
ExcelWBook = new XSSFWorkbook(ExcleFile);
/*
* 实例化XSSFSheet 对象，指定ExcelFile中的sheet名称，用于后续对sheet中行和列的操作；
*
*/
ExcelWSheet = ExcelWBook.getSheet(sheetName); } catch (Exception e) {
e.getStackTrace();
} }
/*
* 读取excle文件指定单元格的函数 ；
*
*/ public static String getCell(int row, int col) throws Exception { try {
// 通过函数参数指定单元格的行号和列，获取指定单元格的对象；
Cell = ExcelWSheet.getRow(row).getCell(col);
/*
* 1.如果单元格的类型为字符串类型，使用getStringCellValue();来获取单元格的内容；
* 2.如果单元格的类型为数字类型，使用getNumberricCellValue();来获取单元格的内容；
* 注意：getNumberricCellValue();返回的值为double类型，转为为字符串类型，必须在
* getNumberricCellValue();前面加上（" "）双引号，用于强制转换为String类型，不加双引号
* 则会抛错；double类型无法转换为String类型的异常；
*
*/
String CellData = Cell.getCellType() == XSSFCell.CELL_TYPE_STRING ? Cell.getStringCellValue() + ""
: String.valueOf(Math.round(Cell.getNumericCellValue()));
return CellData;
} catch (Exception e) {
e.getStackTrace();
return "";
} }
/*
* 在Excle中执行单元格写入数据；
*
*
*/ public static void setCellData(int rownum, int colnum, String Result) throws Exception { try {
// 获取excle文件的中行对象；
Row = ExcelWSheet.getRow(rownum);
// 如果单元格为空则返回null；
Cell = Row.getCell(colnum, Row.RETURN_BLANK_AS_NULL);
if (Cell == null) {
// 当单元格为空是则创建单元格
// 如果单元格为空无法调用单元格对象的setCellValue方法设定单元格的值 ；
Cell = Row.createCell(colnum);
// 创建单元格和后可以通过调用单元格对象的setCellValue方法设置单元格的值了；
Cell.setCellValue(Result);
} else {
// 单元格中有内容，则可以直接调用单元格对象的 setCellValue 方法来设置单元格的值；
Cell.setCellValue(Result);
}
FileOutputStream fileout = new FileOutputStream(ExcelFilePath);
// 将内容写到Excel文件中 ；
ExcelWBook.write(fileout);
// j调用flush方法强制刷新写入文件；
fileout.flush();
fileout.close();
System.out.println("-----写入成功！------");
} catch (Exception e) {
System.out.println(e.getMessage() + e.getStackTrace());
throw (e);
} } public static void TangsetCellData(int RowNum, int ColNum, String Result) {
try {
// 获取行对象
Row = ExcelWSheet.getRow(RowNum);
// 如果单元格为空，则返回null
Cell = Row.getCell(ColNum, Row.RETURN_BLANK_AS_NULL);
if (Cell == null) {
// 当单元格对象是Null时，则创建单元格
// 如果单元格为空，无法直接调用单元格的setCellValue方法设定单元格的值
Cell = Row.createCell(RowNum);
// 调用setCellValue方法设定单元格的值
Cell.setCellValue(Result);
} else {
// 单元格中有内容，则可以直接调用seCellValue方法设定单元格的值
Cell.setCellValue(Result);
}
// 实例化写入Excel文件的文件输出流对象
FileOutputStream fileOut = new FileOutputStream(ExcelFilePath);
// 将内容写入Excel中
ExcelWBook.write(fileOut);
fileOut.flush();
fileOut.close();
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
} // 从excel 文件中获取测试数据的静态方法；
public static Object[][] getTestData(String excelFilePath, String sheetName) throws Exception {
// 根据参数传入的数据文件路径和文件名称，组合出Excel 数据文件的绝对路径
// 声明一个文件；
File file = new File(excelFilePath);
// 创建FileInputStream 来读取Excel文件内容；
FileInputStream inputStream = new FileInputStream(file);
// 声明Workbook 对象；
Workbook workbook = null;
// 获取文件名参数的扩展名，判断是“.xlsx” 还是 “.xls” ；
String fileExtensionName = excelFilePath.substring(excelFilePath.indexOf('.'));
if (fileExtensionName.equals(".xlsx")) {
workbook = new XSSFWorkbook(inputStream); } else if (fileExtensionName.equals(".xls")) {
workbook = new HSSFWorkbook(inputStream); }
Sheet sheet = workbook.getSheet(sheetName);
// 获取Excel 数据文件Sheet1 中数据的行数，getLastRowNum 方法获取数据的最后一行的行号，
// getFistRowNum 获取第一行 最后一行减去第一行就是总行数了
// 注意excle 的行和列都是从0开始的；
int rowCount = sheet.getLastRowNum() - sheet.getFirstRowNum();
// 创建名为records 的List对象来存储从Excel文件中读取的数据；
List<Object[]> records = new ArrayList<Object[]>();
// 使用for循环遍历Excel 数据文件的所有数据（除了第一行，第一行为标题行），所以i从1开始而不是从0开始； for (int i = 1; i < rowCount + 1; i++) {
// 使用getRow来获取行对象；
Row row = sheet.getRow(i);
/*
* 声明一个数据，用来存储Excel数据文件每行中的测试用例和数据，数据的大小用getLastCellNum-2
* 来进行动态声明，实现测试数据个数和数组大小一致，
* 因为Excel数据文件中的测试数据行的最后一个单元格是测试执行结果，倒数第二个单元格为此测试数据行是否运行的状态位，
* 所以最后俩列的单元格数据并
* 不需要传入测试方法中，所以是用getLastCellNum-2的方式去掉每行中的最后俩个单元格数据，计算出需要存储的测试数据个数，
* 并作为测试数据数组的初始化大小
*
*/
String fields[] = new String[row.getLastCellNum() - 2]; /*
* 判断数据行是否要参与测试的执行，Excel 文件的倒数第二列为数据行的状态位， 标记为“y”
* 表示此数据行要被测试脚本执行，标记为非“y”的数据行均被认为不会参数测试脚本执行，会被跳过；
*/ if (row.getCell(row.getLastCellNum() - 2).getStringCellValue().equals("y")) {
for (int j = 0; j < row.getLastCellNum() - 2; j++) {
/*
* 判断Excel 单元格的内容是数字还是字符， 字符格式调用：
* row.getCell(j).getStringCellValue()；
* 数字格式调用：row.getCell(j).getNumericCellValue()；
*/
fields[j] = (String) (row.getCell(j).getCellType() == XSSFCell.CELL_TYPE_STRING
? row.getCell(j).getStringCellValue() : "" + row.getCell(j).getNumericCellValue()); }
// fields 存储到数组当中；
records.add(fields); }
} /*
* 定义函数的返回值，即Object[] [] 将存储测试数据的list 转换为一个Object 的二维数组；
*/
Object[][] results = new Object[records.size()][];
for (int i = 0; i < records.size(); i++) {
results[i] = records.get(i);
} return results; } public static int getLastColumnNum() { return ExcelWSheet.getRow(0).getLastCellNum() - 1;
} }

package main.java; import net.sf.json.JSON;
import net.sf.json.JSONObject; import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.PrintWriter;
import java.net.URL;
import java.net.URLConnection;
import java.util.List;
import java.util.Map; /**
* Created by ty on 2017/8/17.
*/
public class HttpInterfaceTest { public String sendGet(String url, String param) {
String result = "";
BufferedReader in = null;
try {
String urlName = url + "?" + param;
System.out.println("Get请求接口:" + urlName);
URL realUrl = new URL(urlName);
// 打开和URL之间的连接
URLConnection conn = realUrl.openConnection();
// 设置通用的请求属性
conn.setRequestProperty("accept", "*/*");
conn.setRequestProperty("connection", "Keep-Alive");
conn.setRequestProperty("user-agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1)");
// 建立实际的连接
conn.connect();
// 获取所有响应头字段
Map<String, List<String>> map = conn.getHeaderFields();
// 遍历所有的响应头字段
for (String key : map.keySet()) {
System.out.println(key + "--->" + map.get(key));
}
// 定义BufferedReader输入流来读取URL的响应
in = new BufferedReader(new InputStreamReader(conn.getInputStream()));
String line;
while ((line = in.readLine()) != null) {
result += "\n" + line;
}
} catch (Exception e) {
System.out.println("发送GET请求出现异常！" + e);
e.printStackTrace();
}
// 使用finally块来关闭输入流
finally {
try {
if (in != null) {
in.close();
}
} catch (IOException ex) {
ex.printStackTrace();
}
}
return result;
} /**
* 向指定URL发送POST方法的请求
*
* @param url 发送请求的URL
* @param param 请求参数，请求参数应该是name1=value1&name2=value2的形式或者是json。
* @return URL所代表远程资源的响应
*/
public String sendPost(String url, String param) {
PrintWriter out = null;
BufferedReader in = null;
String result = "";
try {
URL realUrl = new URL(url);
// 打开和URL之间的连接
URLConnection conn = realUrl.openConnection();
// 设置通用的请求属性
conn.setRequestProperty("accept", "*/*");
conn.setRequestProperty("connection", "Keep-Alive");
conn.setRequestProperty("user-agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1)");
conn.setRequestProperty("Content-Type", "application/json; charset=utf-8");
// 发送POST请求必须设置如下两行
conn.setDoOutput(true);
conn.setDoInput(true); // 获取URLConnection对象对应的输出流
out = new PrintWriter(conn.getOutputStream());
// 发送请求参数
out.print(param);
// flush输出流的缓冲
out.flush();
// 定义BufferedReader输入流来读取URL的响应
in = new BufferedReader(new InputStreamReader(conn.getInputStream()));
String line;
while ((line = in.readLine()) != null) {
result += "\n" + line;
}
} catch (Exception e) {
System.out.println("发送POST请求出现异常！" + e);
e.printStackTrace();
}
// 使用finally块来关闭输出流、输入流
finally {
try {
if (out != null) {
out.close();
}
if (in != null) {
in.close();
}
} catch (IOException ex) {
ex.printStackTrace();
}
}
return result;
} public static void main(String[] args) { HttpInterfaceTest httpInterfaceTest = new HttpInterfaceTest(); // 调用天气预报接口请求参数方式一
String postUrl = "http://op.juhe.cn/onebox/weather/query";
String postParamsOne = "&cityname=上海市" + "&key=1234567890";
// 调用天气预报接口请求参数方式二
String postParamsTwo = "{'cityname':'上海市'," + "'key':'1234567890'}";
JSONObject jsonPostParamsTwo = JSONObject.fromObject(postParamsTwo);
System.out.println("----------------");
// 发送POST请求
String postResultOne = httpInterfaceTest.sendPost(postUrl, postParamsOne);
System.out.println("POST请求参数一：" + postParamsOne);
System.out.println("POST请求响应结果：" + postResultOne);
// 发送POST请求
String postResultTwo = httpInterfaceTest.sendPost(postUrl, jsonPostParamsTwo.toString());
System.out.println("POST请求参数二：" + jsonPostParamsTwo);
System.out.println("POST请求响应结果：" + postResultTwo); JSONObject jsonObject = JSONObject.fromObject(postResultTwo);
Object resultcode = jsonObject.get("resultcode");
Object reason = jsonObject.get("reason");
Object error_code = jsonObject.get("error_code");
System.out.println("resultcode==" + resultcode+
"| reason="+reason+" | error_code= "+error_code);
}
}

package main.test; import main.java.ExcelReader;
import main.java.ExcleUtil;
import main.java.HttpInterfaceTest;
import net.sf.json.JSONException;
import net.sf.json.JSONObject;
import org.testng.annotations.BeforeTest;
import org.testng.annotations.DataProvider;
import org.testng.annotations.Test; import static org.testng.Assert.*; /**
* Created by linbo.yang on 2017/9/5.
*/
public class TestHttpInterfaceTest {
public static HttpInterfaceTest ht ;
ExcelReader ex ;
static ExcleUtil excleUtil;
@BeforeTest
public void init(){
String ExcelFilePath="C:\\TEST.xlsx";
String sheetName="Sheet1";
ht=new HttpInterfaceTest();
ex = new ExcelReader(ExcelFilePath, sheetName);
try {
excleUtil.setExcleFile( ExcelFilePath,sheetName);
} catch (Exception e) {
e.printStackTrace();
}
} @Test(dataProvider = "dp")
public void testSendPost(String rowNum,String Url,String paras) throws Exception {
System.out.println("rowNum="+rowNum+"； URL="+Url+" ; paras="+paras);
Integer it = new Integer(rowNum);
int row=it.intValue();
if (paras.contains("&")){
String s1 = ht.sendPost(Url,paras );
excleUtil.setCellData(row,3,s1);
System.out.println(s1);
}else {
try {
JSONObject jsonObject = JSONObject.fromObject(paras);
String s = ht.sendPost(Url, jsonObject.toString());
excleUtil.setCellData(row,3,s);
System.out.println(s);
}catch (JSONException jsonException){ System.out.println("标题行不能进行转换！");
} } }
@DataProvider
public Object[][] dp(){
Object[][] sheetData2 = ex.getSheetData2();
/* System.out.println(sheetData2.length + "------------");
for (int i = 1; i < sheetData2.length; i++) {
for (int j = 0; j < sheetData2[i].length; j++) {
System.out.print(sheetData2[i][j] + " | ");
}
System.out.println();
}*/ return sheetData2 ; } }

package main.java; import java.util.*;
public class DateUtil {
public static String format="yyyy/MM/dd HH:mm:ss";
public static Date date = new Date();
/*
* 格式化输出日期 ；
*
* @return 一个字符型日期；
*/
public static String format(Date date ,String format) {
String result = "";
try {
if (date != null) {
java.text.DateFormat df = new java.text.SimpleDateFormat(format);
result = df.format(date); }
} catch (Exception e) {
e.printStackTrace();
}
return result;
}
public static String format() {
String result = "";
try {
if (date != null) {
java.text.DateFormat df = new java.text.SimpleDateFormat(format);
result = df.format(date); }
} catch (Exception e) {
e.printStackTrace();
}
return result;
} public String getYear() {
java.util.Calendar c= java.util.Calendar.getInstance();
c.setTime(date);
return String.valueOf(c.get(java.util.Calendar.YEAR)) ;
} //返回年份 ；
public static String getYear(java.util.Date date){
java.util.Calendar c= java.util.Calendar.getInstance();
c.setTime(date);
return String.valueOf(c.get(java.util.Calendar.YEAR)) ;
}
//返回月份 ；
public static String getMonth(){
java.util.Calendar calendar =java.util.Calendar.getInstance();
calendar.setTime(date);
return String.valueOf(calendar.get(java.util.Calendar.MONTH)+1);
}
public static String getMonth(java.util.Date date){
java.util.Calendar calendar =java.util.Calendar.getInstance();
calendar.setTime(date);
return String.valueOf(calendar.get(java.util.Calendar.MONTH)+1);
}
//返回月份中的第几天； public static String getDay(){
java.util.Calendar calendar =java.util.Calendar.getInstance();
calendar.setTime(date);
return String.valueOf(calendar.get(java.util.Calendar.DAY_OF_MONTH));
}
public static String getDay(java.util.Date date){
java.util.Calendar calendar =java.util.Calendar.getInstance();
calendar.setTime(date);
return String.valueOf(calendar.get(java.util.Calendar.DAY_OF_MONTH));
}
//返回小时；
public static String getHour(){
Calendar calendar=Calendar.getInstance();
calendar.setTime(date);
return String.valueOf(calendar.get(Calendar.HOUR));
}
public static String getHour(Date date){
Calendar calendar=Calendar.getInstance();
calendar.setTime(date);
return String.valueOf(calendar.get(Calendar.HOUR));
}
//返回分钟 ；
public static String getMinute( ){
Calendar calendar = Calendar.getInstance();
calendar.setTime(date);
return String.valueOf(calendar.get(Calendar.MINUTE));
}
public static String getMinute(Date date ){
Calendar calendar = Calendar.getInstance();
calendar.setTime(date);
return String.valueOf(calendar.get(Calendar.MINUTE));
}
//返回秒
public static String getSecond( ){
Calendar calendar = Calendar.getInstance();
calendar.setTime(date);
return String.valueOf(calendar.get(Calendar.SECOND));
}
public static String getSecond(Date date ){
Calendar calendar = Calendar.getInstance();
calendar.setTime(date);
return String.valueOf(calendar.get(Calendar.SECOND));
}
}