1.Excel数据格式
2.读取Excel数据并转化为list
package base;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
public class BaseExcel {
public static void main(String[] args) {
// TODO 自动生成的方法存根
//readExcel("D:\ME\workplace\jiekou.xls");
//outData();
}
/*
* 读取Excel，返回二维数组
*/
public static String[][] readExcel(String filepath,int a){
String[][] requestData = null;
try {
//创建输入流
InputStream stream = new FileInputStream(filepath);
//获取文件对象
Workbook rwb = Workbook.getWorkbook(stream);
//获取文件的指定工作表，默认是第一个
Sheet sheet = rwb.getSheet(a);
//初始化二维数组
requestData = new String[sheet.getRows()][sheet.getColumns()];
//行数 sheet.getRows()/列数sheet.getColumns()
for(int i = 0 ; i < sheet.getRows() ; i++){
for(int j = 0;j < sheet.getColumns();j++){
requestData[i][j] = sheet.getCell(j,i).getContents();
//System.out.print(i+"-"+j+"=="+requestData[i][j]+" ");
}
//System.out.println("
");
}
} catch (FileNotFoundException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
} catch (BiffException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
} catch (IOException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}
return requestData;
}
/*
* 读取Excel，返回list
*/
public static List<Map<String, String>> readExcelList(String filepath,int a){
List<Map<String, String>> list = new ArrayList<Map<String, String>>();
try {
//创建输入流
InputStream stream = new FileInputStream(filepath);
//获取文件对象
Workbook rwb = Workbook.getWorkbook(stream);
//获取文件的指定工作表，默认是第一个
Sheet sheet = rwb.getSheet(a);
//行数 sheet.getRows()/列数sheet.getColumns()
for(int i = 0 ; i < sheet.getRows() ; i++){
Map<String, String> map = new HashMap<String, String>();
for(int j = 0;j < sheet.getColumns();j++){
map.put(sheet.getCell(j,0).getContents(), sheet.getCell(j,i).getContents());
//System.out.println(sheet.getCell(j,0).getContents());
//System.out.println("=======");
//System.out.println(sheet.getCell(j,i).getContents());
//System.out.print(i+"-"+j+"=="+requestData[i][j]+" ");
}
//System.out.println("
");
list.add(map);
}
} catch (FileNotFoundException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
} catch (BiffException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
} catch (IOException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}
return list;
}
/*
* 将数据写入Excel
* xlsFilename是Excel地址
* a是Excel里面的表的序列号，从0开始
* column是Excel的列数
* Row是Excel的行数
* result是要写入的数据
*/
public static void writeExcel(String xlsFilename,int a,int column,int Row,String result){
try {
Workbook wb=Workbook.getWorkbook(new File(xlsFilename));
WritableWorkbook book=
Workbook.createWorkbook(new File(xlsFilename),wb);
//WritableSheet sheet0=book.createSheet("First Sheet",0);
WritableSheet sheet = book.getSheet(a);
//sheet.addCell(new Label(5,0,"mytest---------ok"));
Label filename1 = new Label(column,Row,result);
//System.out.println(filename1.getContents());
sheet.addCell(filename1);
book.write();
book.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
catch (RowsExceededException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (WriteException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (BiffException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
3.将list数据转化为Object[][]
（1）testng的DataProvider注解方法
@DataProvider
public Object[][] dp() {
Object[][] myObj = null;
String filepath = "D:\ME\接口自动化\接口模板\接口1.xls";
BaseExcel baseExcel = new BaseExcel();
CaseHelper caseHelper = new CaseHelper();
List<Map<String,String>> list = baseExcel.readExcelList(filepath, 5); //读取Excel数据
myObj = caseHelper.getObjArrByList(list); //转化为Object[][]类型
return myObj;
}
（2）
package base;
import java.util.Map;
public class CaseInfo {
/*
* 用例编号
*/
private Map<String, String> useCaseNumber;
/*
* 用例名
*/
private Map<String, String> useCaseName;
/*
* 是否执行 Function
*/
private Map<String, String> function;
/*
* 请求方式
*/
private Map<String, String> requestMode;
/*
* 请求uri http://。。。。
*/
private Map<String, String> requestUri;
/*
* 请求端口
*/
private Map<String, String> port;
/*
* 请求地址
*/
private Map<String, String> requestAddress;
/*
* 请求头 在excel中以{$A}开头
*/
private Map<String, String> requestHeader;
/*
* 请求参数 在excel中以{$B}开头
*/
private Map<String, String> requestParameter;
/*
* post方式请求参数 在excel中以{$C}开头
*/
private Map<String, String> requestBody;
/*
* 验证结果String类型，在excel中以{$D}开头
*/
private Map<String, String> requestResultStr;
/*
* 验证结果int类型，在excel中以{$E}开头
*/
private Map<String, String> requestResultInt;
public Map<String, String> getRequestResultInt() {
return requestResultInt;
}
public void setRequestResultInt(Map<String, String> requestResultInt) {
this.requestResultInt = requestResultInt;
}
public Map<String, String> getRequestResultStr() {
return requestResultStr;
}
public void setRequestResultStr(Map<String, String> requestResultStr) {
this.requestResultStr = requestResultStr;
}
public Map<String, String> getUseCaseNumber() {
return useCaseNumber;
}
public void setUseCaseNumber(Map<String, String> useCaseNumber) {
this.useCaseNumber = useCaseNumber;
}
public Map<String, String> getUseCaseName() {
return useCaseName;
}
public void setUseCaseName(Map<String, String> useCaseName) {
this.useCaseName = useCaseName;
}
public Map<String, String> getFunction() {
return function;
}
public void setFunction(Map<String, String> function) {
this.function = function;
}
public Map<String, String> getRequestMode() {
return requestMode;
}
public void setRequestMode(Map<String, String> requestMode) {
this.requestMode = requestMode;
}
public Map<String, String> getRequestUri() {
return requestUri;
}
public void setRequestUri(Map<String, String> requestUri) {
this.requestUri = requestUri;
}
public Map<String, String> getPort() {
return port;
}
public void setPort(Map<String, String> port) {
this.port = port;
}
public Map<String, String> getRequestAddress() {
return requestAddress;
}
public void setRequestAddress(Map<String, String> requestAddress) {
this.requestAddress = requestAddress;
}
public Map<String, String> getRequestHeader() {
return requestHeader;
}
public void setRequestHeader(Map<String, String> requestHeader) {
this.requestHeader = requestHeader;
}
public Map<String, String> getRequestParameter() {
return requestParameter;
}
public void setRequestParameter(Map<String, String> requestParameter) {
this.requestParameter = requestParameter;
}
public Map<String, String> getRequestBody() {
return requestBody;
}
public void setRequestBody(Map<String, String> requestBody) {
this.requestBody = requestBody;
}
}
（3）
package base;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class CaseHelper {
public static void main(String[] args) {
// TODO 自动生成的方法存根
}
//根据excel的map 转换为数组 第一个为 入参 map 第二个为用例说明,第三个参数为执行用例的预置条件
public static Object[] getObjArrByMap(Map<String,String> caseExcelMap){
/*
* 用例编号
*/
Map<String,String> useCaseNumber = new HashMap<String,String>();
/*
* 用例名
*/
Map<String,String> useCaseName = new HashMap<String,String>();
/*
* 是否执行 Function
*/
Map<String,String> function =new HashMap<String,String>();
/*
* 请求方式
*/
Map<String,String> requestMode = new HashMap<String,String>();
/*
* 请求uri http://。。。。
*/
Map<String,String> requestUri = new HashMap<String,String>();
/*
* 请求端口
*/
Map<String,String> port =new HashMap<String,String>();
/*
* 请求地址
*/
Map<String,String> requestAddress = new HashMap<String,String>();
/*
* 请求头 在excel中以{$A}开头
*/
Map<String,String> requestHeader = new HashMap<String,String>();
/*
* 请求参数 在excel中以{$B}开头
*/
Map<String,String> requestParameter =new HashMap<String,String>();
/*
* post方式请求参数 在excel中以{$C}开头
*/
Map<String,String> requestBody = new HashMap<String,String>();
/*
* post方式请求参数 在excel中以{$D}开头
*/
Map<String,String> requestResultStr = new HashMap<String,String>();
/*
* 验证结果int类型，在excel中以{$E}开头
*/
Map<String, String> requestResultInt = new HashMap<String,String>();
CaseInfo ci = new CaseInfo();
for (String key : caseExcelMap.keySet()) {
if(key.indexOf("用例编号")== 0){
useCaseNumber.put(key, caseExcelMap.get(key));
}else if (key.indexOf("用例名")== 0) {
useCaseName.put(key, caseExcelMap.get(key));
}else if (key.indexOf("是否执行")== 0) {
function.put(key, caseExcelMap.get(key));
}else if (key.indexOf("请求方式")== 0) {
requestMode.put(key, caseExcelMap.get(key));
}else if (key.indexOf("URI")== 0) {
requestUri.put(key, caseExcelMap.get(key));
}else if (key.indexOf("端口")== 0) {
port.put(key, caseExcelMap.get(key));
}else if (key.indexOf("地址")== 0) {
requestAddress.put(key, caseExcelMap.get(key));
}else if (key.indexOf("{$A}")== 0){
requestHeader.put(key.replace("{$A}", ""), caseExcelMap.get(key));
}else if(key.indexOf("{$B}") == 0){
requestParameter.put(key.replace("{$B}", ""), caseExcelMap.get(key));
}else if(key.indexOf("{$C}") == 0){
requestBody.put(key.replace("{$C}", ""), caseExcelMap.get(key));
}else if(key.indexOf("{$D}") == 0){
requestResultStr.put(key.replace("{$D}", ""), caseExcelMap.get(key));
}else if(key.indexOf("{$E}") == 0){
requestResultInt.put(key.replace("{$E}", ""), caseExcelMap.get(key));
}
}
ci.setUseCaseName(useCaseName);
ci.setUseCaseNumber(useCaseNumber);
ci.setFunction(function);
ci.setRequestMode(requestMode);
ci.setRequestUri(requestUri);
ci.setPort(port);
ci.setRequestAddress(requestAddress);
ci.setRequestHeader(requestHeader);
ci.setRequestParameter(requestParameter);
ci.setRequestBody(requestBody);
ci.setRequestResultStr(requestResultStr);
ci.setRequestResultInt(requestResultInt);
return new Object[]{ci};
}
///根据excel获取的list转换为 Object[][]
public Object[][] getObjArrByList(List<Map<String,String>> caseExcelList){
List<Map<String,String>> caseExcuteList = getExcuteList(caseExcelList);
//List<Map<String,String>> caseExcuteList = caseExcelList;
Object[][] objArray = new Object[caseExcuteList.size()][];
for(int i = 0;i<caseExcuteList.size();i++){
objArray[i]=getObjArrByMap(caseExcuteList.get(i));
//System.out.println(objArray[0][i]);
}
return objArray;
}
///赛选出需要执行的用例
private List<Map<String,String>> getExcuteList(List<Map<String,String>> caseExcelList){
List<Map<String,String>> list = new ArrayList<Map<String,String>>();
for( Map<String,String> m : caseExcelList){
String str = m.get("是否执行").trim().toLowerCase();
if (str.equals("y")){
list.add(m);
}
}
return list;
}
}