package com.common;
import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.write.*;
import org.apache.log4j.Logger;
import org.testng.Assert;
import java.io.*;
import java.util.*;
/**
* Excel放在Data文件夹下</p>
* Sheet名称为接口名称
*/
public class ExcelDataProvider {
Map<String, Object> Input = new HashMap<String, Object>();
private static Logger logger = Logger.getLogger(ExcelDataProvider.class.getName());
public ExcelDataProvider() {
}
public ExcelDataProvider(String classname, String testName, String projectPath) {
try {
int dotNum = classname.indexOf(".");
if (dotNum > 0) {
classname = classname.substring(classname.lastIndexOf(".") + 1,
classname.length());
}
String xlFilePath = "testcase/";
String fileName = xlFilePath + projectPath + ".xls";
String excelPath = GlobalSettings.USER_DIR + "/" + fileName;
String excelName = FileUtil.getName(excelPath);
String projectFullPath = FileUtil.getParent(excelPath);
String projectName = FileUtil.getName(projectFullPath);
String productFullPath = FileUtil.getParent(projectFullPath);
String productName = FileUtil.getName(productFullPath);
String projectFullType = FileUtil.getParent(productFullPath);
String projectType = FileUtil.getName(projectFullType);
GlobalSettings.PRJ_NAME = projectName;
GlobalSettings.REPORT_PRJ_NAME = excelName.replace(".xls", "");
WorkbookSettings wbSettings = new WorkbookSettings();
wbSettings.setSuppressWarnings(true);
Workbook book = Workbook.getWorkbook(new File(fileName), wbSettings);
Sheet sheet = book.getSheet("Menu");
boolean blfg = false;
boolean readSQL = false;
boolean isReadExpect = false;
for (int i = 0; i < sheet.getRows(); i++) {
if (sheet.getCell(1, i).getContents().toString().trim().equals(classname)) {
Input.put("Ishttp", sheet.getCell(4, i).getContents().toString());
Input.put("Method", sheet.getCell(5, i).getContents().toString());
if (null != sheet.getCell(6, i) && CommonUtil.matchValues(sheet.getCell(6, i).getContents(), "json")) {//Json格式请求
String tmpUrl = sheet.getCell(2, i).getContents().toString() + sheet.getCell(3, i).getContents().toString();
Input.put("URL", tmpUrl.replace("{{url}}", GlobalSettings.EOS_URL));
Sheet sheet2 = book.getSheet(classname);
for (int j = 0; j < sheet2.getRows(); j++) {
if (sheet2.getCell(1, j).getContents().toString().trim().equals(testName)) {
Input.put("Description", sheet2.getCell(2, j).getContents());
Input.put("Parameters", sheet2.getCell(3, j).getContents().toString());
Input.put("Expect", sheet2.getCell(5, j).getContents().toString());
String readSql = sheet2.getCell(4, j).getContents().toString();
Input.put("ReadSql", readSql);
if (readSql.equals("Y")) {
readSQL = true;
}
if (sheet2.getColumns() > 6) {
String readExpect = sheet2.getCell(6, j).getContents().toString();
Input.put("ReadExpect", readExpect);
if (readExpect.equals("Y")) {
isReadExpect = true;
}
}
blfg = true;
break;
}
}
} else {//表单提交类型或其他
String tempURL = sheet.getCell(2, i).getContents().toString() + sheet.getCell(3, i).getContents().toString();
tempURL = tempURL.replace("{{url}}", GlobalSettings.EOS_URL);
Sheet sheet2 = book.getSheet(classname);
for (int j = 0; j < sheet2.getRows(); j++) {
if (sheet2.getCell(1, j).getContents().toString().trim().equals(testName)) {
Input.put("URL", tempURL + "?" + sheet2.getCell(3, j).getContents());
Input.put("Description", sheet2.getCell(2, j).getContents());
Input.put("Parameters", sheet2.getCell(3, j).getContents());
Input.put("Expect", sheet2.getCell(5, j).getContents());
String readSql = sheet2.getCell(4, j).getContents();
Input.put("ReadSql", readSql);
if (null != sheet2.getCell(4, j) && CommonUtil.matchValues(sheet2.getCell(4, j).getContents(), "Y")) {
readSQL = true;
}
if (sheet2.getColumns() > 6) {
String readExpect = sheet2.getCell(6, j).getContents();
Input.put("ReadExpect", readExpect);
if (CommonUtil.matchValues(readExpect, "Y")) {
isReadExpect = true;
}
}
blfg = true;
break;
}
}
}
break;
}
}
if (!blfg) {
logger.info("获取" + classname + "数据失败，错误原因为：未找到接口或对应案例");
}
if (readSQL) {
try {
String filePath = "./src/main/java/envision/data/" + projectType + "/sql/" + productName + "/" + GlobalSettings.PRJ_NAME + "/" + classname + "/" + testName + ".txt";
String fpath = "";
File f = new File(filePath);
if (f.exists()) {
fpath = f.getAbsolutePath();
FileInputStream in = new FileInputStream(fpath);
// 指定读取文件时以UTF-8的格式读取
BufferedReader br = new BufferedReader(new InputStreamReader(in,
"UTF-8"));
String str;
String tempKey = "";
String tempValue = "";
List<Map<String, String>> listSql = new ArrayList<Map<String, String>>();
while ((str = br.readLine()) != null) {
if (str.contains("={")) {
tempKey = str.replace("={", "").trim();
tempValue = "";
} else if (str.trim().equals("}")) {
Input.put(tempKey, tempValue);
Map<String, String> tempMap = new HashMap<String, String>();
tempMap.put(tempKey, tempValue);
listSql.add(tempMap);
} else {
tempValue = tempValue + str;
}
}
Input.put("listSql", listSql);
}
} catch (Exception e) {
e.printStackTrace();// 打印错误信息
}
}
if (isReadExpect) {
try {
String filePath = "./src/main/java/envision/data/" + projectType + "/expect/" + productName + "/" + GlobalSettings.PRJ_NAME + "/" + classname + "/" + testName + ".txt";
String fpath = "";
File f = new File(filePath);
if (f.exists()) {
fpath = f.getAbsolutePath();
FileInputStream in = new FileInputStream(fpath);
// 指定读取文件时以UTF-8的格式读取
BufferedReader br = new BufferedReader(new InputStreamReader(in,
"UTF-8"));
String str;
String tempValue = "";
while ((str = br.readLine()) != null) {
tempValue += str;
}
Input.put("ExpectTxt", tempValue);
}
} catch (Exception e) {
e.printStackTrace();// 打印错误信息
}
}
} catch (Exception e) {
e.printStackTrace();
logger.info("获取" + classname + "数据失败，错误原因为：" + e.toString());
Assert.fail("unable to read excel data");
}
}
public Iterator<Object[]> getData() {
List<Object[]> toIter = new ArrayList<>();
Object[] arrObj = new Object[]{Input};
toIter.add(arrObj);
return toIter.iterator();
}
public void bakCaseExcel(String excelPath, String projectType, String productName, String projectName, String excelName) throws IOException {
if (GlobalSettings.TIMESTAMP.isEmpty()) {
long time = DateFormat.getCurrentTimeMillis();
String timeT = DateFormat.getDate("yyyyMMddHHmmss", time);
GlobalSettings.TIMESTAMP = timeT;
if (!FileUtil.exists(GlobalSettings.RESULT_PATH)) {
FileUtil.createFloder(GlobalSettings.RESULT_PATH);
}
GlobalSettings.RESULT_TYPE_PATH = GlobalSettings.RESULT_PATH + "/" + projectType;
if (!FileUtil.exists(GlobalSettings.RESULT_TYPE_PATH)) {
FileUtil.createFloder(GlobalSettings.RESULT_TYPE_PATH);
}
GlobalSettings.RESULT_TYPE_PRODUCT_PATH = GlobalSettings.RESULT_TYPE_PATH + "/" + productName;
if (!FileUtil.exists(GlobalSettings.RESULT_TYPE_PRODUCT_PATH)) {
FileUtil.createFloder(GlobalSettings.RESULT_TYPE_PRODUCT_PATH);
}
GlobalSettings.RESULT_TYPE_PRODUCT_PRJ_PATH = GlobalSettings.RESULT_TYPE_PRODUCT_PATH + "/" + projectName;
if (!FileUtil.exists(GlobalSettings.RESULT_TYPE_PRODUCT_PRJ_PATH)) {
FileUtil.createFloder(GlobalSettings.RESULT_TYPE_PRODUCT_PRJ_PATH);
}
GlobalSettings.RESULT_TYPE_PRODUCT_PRJ_TIME_PATH = GlobalSettings.RESULT_TYPE_PRODUCT_PRJ_PATH + "/" + timeT;
FileUtil.createFloder(GlobalSettings.RESULT_TYPE_PRODUCT_PRJ_TIME_PATH);
GlobalSettings.RESULT_TYPE_PRODUCT_PRJ_TIME_TESTCASE_PATH = GlobalSettings.RESULT_TYPE_PRODUCT_PRJ_TIME_PATH + "/" + excelName;
// 备份测试用例excel文件
FileUtil.copyFile(excelPath, GlobalSettings.RESULT_TYPE_PRODUCT_PRJ_TIME_TESTCASE_PATH);
}
}
public void returnData(Map<String, String> mReturn, String classname) {
if (mReturn == null || mReturn.size() == 0) {
return;
}
WritableWorkbook wwb = null;
Workbook rwb = null;
try {
int dotNum = classname.indexOf(".");
if (dotNum > 0) {
classname = classname.substring(classname.lastIndexOf(".") + 1,
classname.length());
}
String fileName = GlobalSettings.RESULT_TYPE_PRODUCT_PRJ_TIME_TESTCASE_PATH;
File f = new File(fileName);
rwb = Workbook.getWorkbook(f);
//打开一个文件的副本，并且指定数据写回到原文件
wwb = Workbook.createWorkbook(f, rwb);//copy
WritableSheet wSheet = wwb.getSheet(classname);
WritableFont font1 = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLUE);
WritableCellFormat cf1 = new WritableCellFormat(font1);
boolean blfg = false;
for (String str : mReturn.keySet()) {
for (int i = 1; i < wSheet.getRows(); i++) {
if (wSheet.getCell(1, i).getContents().toString().equals(str)) {
Label cv = new Label(5, i, mReturn.get(str), cf1);
try {
wSheet.addCell(cv);
break;
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
wwb.write();
wwb.close();
wwb = null;
logger.info("finish rebase : " + classname);
} catch (Exception e) {
e.printStackTrace();
logger.info("获取" + classname + "数据失败，错误原因为：" + e.toString());
Assert.fail("unable to read excel data");
} finally {
// 关闭 Excel 工作薄对象
try {
if (wwb != null) {
wwb.close();
}
} catch (WriteException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
