自动化测试脚本执行完以后,会有一个测试结果,无论是通过框架还是Jenkins平台生成的结果,可读性都不是太好,为了方便手工测试人员查看结果,测试完成后把结果写入Excel是一个不错的方法,但是脚本多了,通过人工来写,无疑加重了自动化测试人员的工作,为了解决这个问题,我就开发了一个把测试结果写入Excel的工具类,代码如下:
import java.io.File;
import java.util.ArrayList;
import java.util.List;
import jxl.Workbook;
import jxl.common.Logger;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
public class CreateExcel {
private String[] title;
private String sheetName;
private static WritableWorkbook book = null;
private static WritableSheet sheet = null;
private static Logger logger = Logger.getLogger(CreateExcel.class);
private String testRoot = GeneralMethods.getTestRoot().replace("target/test-classes/", "src/test/resources/data/")
+ "Performance/";
private File file;
/*
* titleName[0]表示Excel文件的名字
* titleName后面的值是Excel第一行要填入的值
*/
public CreateExcel(String... titleName) {
sheetName = GeneralMethods.getDate();
file = new File(testRoot + titleName[0]);
List<String> temp = new ArrayList<String>();
for (String s : titleName) {
temp.add(s);
}
temp.remove(0);
titleName = (String[]) temp.toArray(new String[temp.size()]);
create(titleName);
}
private void create(String[] titleNames) {
title = titleNames;
try {
Workbook wb = Workbook.getWorkbook(file);
book = Workbook.createWorkbook(file, wb);
sheet = addTitle();
} catch (Exception e) {
logger.debug("CreateWorkbook failed !");
e.printStackTrace();
}
}
public void addDataToExcel(String testname, double... data) {
try {
Workbook wb = Workbook.getWorkbook(file);
book = Workbook.createWorkbook(file, wb);
sheet = book.getSheet(sheetName);
int length = sheet.getRows();
Label name = new Label(0, length, testname);
sheet.addCell(name);
for (int i = 1; i < data.length + 1; i++) {
Label label = new Label(i, length, Double.toString(data[i - 1]));
sheet.addCell(label);
}
book.write();
book.close();
} catch (Exception e) {
e.printStackTrace();
}
}
private WritableSheet addTitle() {
String nameStrs = "";
String[] sheetNames = book.getSheetNames();
for (String sheetName : sheetNames) {
nameStrs += sheetName;
}
try {
if (nameStrs.contains(sheetName))
sheet = book.getSheet(sheetName);
else {
sheet = book.createSheet(sheetName, 0);
sheet.getSettings().setDefaultColumnWidth(25);
for (int i = 0; i < title.length; i++) {
sheet.addCell(new Label(i, 0, title[i]));
}
}
book.write();
book.close();
} catch (Exception e) {
}
return sheet;
}
}
使用工具类的脚本:
import org.testng.annotations.BeforeClass;
import org.testng.annotations.Test;
import tools.CreateExcel;
import tools.WindowsInfoUtil;
public class testWinMonitor {
private WindowsInfoUtil info;
private CreateExcel create;
@BeforeClass
public void setUp() {
create = new CreateExcel("Windows Monitor.xls", "测试名称", "CPU开始占用(%)", "CPU结束占用(%)", "内存开始占用(MB)", "内存结束占用(MB)");
}
@Test
public void test010() throws InterruptedException {
Thread.sleep(2000);
double startMem = info.getMem();
double startCpu = info.getCpu();
Thread.sleep(2000);
double endMem = info.getMem();
double endCpu = info.getCpu();
create.addDataToExcel("test1", startCpu, endCpu, startMem, endMem);
}
}
注意
1、在执行脚本之前,在目录…\src\test\resources\data\Performance下面新建一个Excel,名字为实例化CreateExcel类的时候传入的第一个参数,例子中为“Windows Monitor.xls”。
2、对Excel进行读写的第三方jar包是jxl,这个包暂时只支持Excel97-2003.
3、脚本执行完成后,点开查看Excel,新建的sheet名字为日期,如:“2016-01-29”
回到最初的话题,把测试结果写入到Excel,如果测试框架选择的是testNG,直接解析testNG的XML报告,按照自己项目的需求选择需要传入到CreateExcel工具类中的参数即可。
补充:
GeneralMethods类:
package tool;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;
import java.util.Map.Entry;
import java.util.Properties;
import org.apache.commons.lang.StringEscapeUtils;
import org.apache.commons.lang.StringUtils;
import org.mozilla.universalchardet.UniversalDetector;
import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.databind.node.ObjectNode;
public class GeneralMethods {
public static String getTimeStamp() {
String timeStamp = GeneralMethods.getDate().replace("-", "")
+ GeneralMethods.getCurrentTime().replace(":", "").replace(".", "");
return timeStamp;
}
private static ObjectMapper jsonMapper = new ObjectMapper();
public static String getConfigValue(JsonNode configObject, String propertyName) {
String returnValue = StringUtils.defaultString(System.getenv(propertyName));
if (StringUtils.isBlank(returnValue)) {
JsonNode targetNode = configObject.path(propertyName);
returnValue = StringUtils.defaultString(targetNode.textValue());
}
while (returnValue.contains("{{") && returnValue.contains("}}")) {
String embeddedPropertyName = StringUtils.substringBetween(returnValue, "{{", "}}");
String embeddedPropertyValue = getConfigValue(configObject, embeddedPropertyName);
returnValue = StringUtils.replace(returnValue, ("{{" + embeddedPropertyName + "}}"), embeddedPropertyValue);
}
return returnValue;
}
public static JsonNode getDataFromConfigFile(String configFileName, String testClass) {
boolean fileFound = true;
String fileName = configFileName;
String testRoot = GeneralMethods.getTestRoot();
JsonNode jsonNode = jsonMapper.createObjectNode();
if (!(new File(fileName)).exists()) {
fileFound = false;
while (!(StringUtils.isBlank(testClass) || fileFound)) {
testClass = StringUtils.substring(testClass, 0, StringUtils.lastIndexOf(testClass, "."));
fileName = testRoot + StringUtils.replace(testClass, ".", "/") + "/data/" + configFileName;
fileFound = (new File(fileName)).exists();
}
}
try {
try {
jsonNode = jsonMapper.readValue(new File(fileName), JsonNode.class);
}
catch (Exception e) {
Properties pro = new Properties();
FileInputStream fis = new FileInputStream(fileName);
InputStreamReader isr = new InputStreamReader(fis, "UTF-8");
BufferedReader brProp = new BufferedReader(isr);
pro.load(brProp);
brProp.close();
ObjectMapper mapper = new ObjectMapper();
ObjectNode node = mapper.createObjectNode();
for (Entry<Object, Object> element : pro.entrySet()) {
node.put(element.getKey().toString(),
StringEscapeUtils.unescapeHtml(element.getValue().toString().replaceAll("\\<.*?>", "")));
}
jsonNode = (JsonNode) node;
}
} catch (IOException e) {
throw new IllegalStateException("Can't locate config file " + fileName, e);
}
return jsonNode;
}
public static JsonNode getDataFromConfigFile(String configFileName) {
return getDataFromConfigFile(configFileName, "");
}
public static String getCurrentTime() {
Date today = new Date();
SimpleDateFormat f = new SimpleDateFormat("HH:mm:ss");
String time = f.format(today);
return time;
}
public static String getDate() {
Date date = new Date();
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
return dateFormat.format(date);
}
public static String getTestRoot() {
String testRoot = StringUtils.defaultString(Thread.currentThread().getContextClassLoader().getResource(".").getPath());
if (StringUtils.contains(testRoot, ":/"))
testRoot = StringUtils.substring(testRoot, 1);
return testRoot;
}
public static JsonNode getInitialConfig() {
String initialConfigFile = StringUtils.defaultString(System.getenv("SeleniumConfigFile"),
"conf/AutotestConfig.properties");
String configFile = getTestRoot().replace("target/test-classes/", "") + "src/test/resources/" + initialConfigFile;
JsonNode initialConfig = getDataFromConfigFile(configFile);
return initialConfig;
}
public static JsonNode getInitialConfig(String initialConfigFileName) {
String configFile = getTestRoot().replace("target/test-classes/", "") + "src/test/resources/" + initialConfigFileName;
JsonNode initialConfig = getDataFromConfigFile(configFile);
return initialConfig;
}
public static String replaceIllegalFileName(String fileName, String newChar) {
if (fileName != null) {
fileName = fileName.replace("\\", newChar);
fileName = fileName.replace("/", newChar);
fileName = fileName.replace(":", newChar);
fileName = fileName.replace("*", newChar);
fileName = fileName.replace("?", newChar);
fileName = fileName.replace("\"", newChar);
fileName = fileName.replace("<", newChar);
fileName = fileName.replace(">", newChar);
fileName = fileName.replace("|", newChar);
}
return fileName;
}
@SuppressWarnings("deprecation")
public static JsonNode merge(JsonNode mainNode, JsonNode updateNode) {
Iterator<String> fieldNames = updateNode.fieldNames();
while (fieldNames.hasNext()) {
String fieldName = fieldNames.next();
JsonNode jsonNode = mainNode.get(fieldName);
if (jsonNode != null && jsonNode.isObject()) {
merge(jsonNode, updateNode.get(fieldName));
} else {
if (mainNode instanceof ObjectNode) {
JsonNode value = updateNode.get(fieldName);
if (jsonNode != null) {
if (jsonNode.isArray() && value.isArray()) {
String temp1 = jsonNode.toString();
String temp2 = value.toString();
temp1 = temp1.substring(1, temp1.length() - 1);
temp2 = temp2.substring(1, temp2.length() - 1);
try {
((ObjectNode) mainNode).put(fieldName,
jsonMapper.readValue("[" + temp1 + "," + temp2 + "]", JsonNode.class));
} catch (Exception e) {
}
} else {
((ObjectNode) mainNode).put(fieldName, value);
}
} else
((ObjectNode) mainNode).put(fieldName, value);
}
}
}
return mainNode;
}
public static JsonNode mergeProperties(JsonNode target, JsonNode... extraProperties) {
for (JsonNode currentContent : extraProperties) {
if (currentContent != null) {
target = (ObjectNode) merge(target, currentContent);
}
}
return target;
}
public static String detectCharset(byte[] buf) {
UniversalDetector detector = new UniversalDetector(null);
detector.handleData(buf, 0, buf.length);
detector.dataEnd();
String encoding = detector.getDetectedCharset();
detector.reset();
return encoding;
}
public static String detectCharset(InputStream in) {
byte[] buf = new byte[4096];
UniversalDetector detector = new UniversalDetector(null);
int nread;
try {
while ((nread = in.read(buf)) > 0 && !detector.isDone()) {
detector.handleData(buf, 0, nread);
}
} catch (IOException e) {
throw new RuntimeException(e);
}
detector.dataEnd();
String encoding = detector.getDetectedCharset();
detector.reset();
return encoding;
}
public static void log(String content, Integer type) {
switch (type) {
case 1: {
System.out.println(getDate() + " " + getCurrentTime() + " INFO - " + content);
break;
}
case 2: {
System.err.println(getDate() + " " + getCurrentTime() + " ERROR - " + content);
break;
}
case 3: {
System.out.println(getDate() + " " + getCurrentTime() + " WARNING - " + content);
break;
}
case 4: {
System.err.println(getDate() + " " + getCurrentTime() + " WARNING - " + content);
break;
}
}
}
public static void log(String content) {
log(content, 1);
}
}