package ScriptLibrary
import java.awt.Color
import java.awt.GraphicsConfiguration.DefaultBufferCapabilities;
import java.io.File;
import java.io.FileOutputStream;
import java.io.FileInputStream;
import java.text.NumberFormat;
import java.text.ParseException;
import java.util.ArrayList;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCellStyle
import org.apache.poi.xssf.usermodel.XSSFColor
import org.apache.poi.xssf.usermodel.XSSFDataFormat
import org.apache.poi.xssf.usermodel.XSSFFont
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.poifs.filesystem.POIFSFileSystem
class WriteExcel {
def testRunner
def context
def log
WriteExcel(testRunner,context,log) {
this.testRunner = testRunner
this.context = context
this.log = log
}
def parseFailMessage(String failMessage,def extraInfoMap){
//获取Test Suite和Test Case的名字
def currentStepIndex = context.currentStepIndex
def testCaseName = testRunner.testCase.getTestStepAt(currentStepIndex).getParent().getName()
def testSuiteName = testRunner.testCase.getTestStepAt(currentStepIndex).getParent().getParent().getName()
//解析extraInfoMap
def valueList = extraInfoMap.values()
//根据;分隔每一条错误信息
ArrayList failMessageList = new ArrayList()
String[] failMessageArray = failMessage.split(";")
//遍历每一条错误信息，解析出每一列的值，便于写入Excel
for(int i=0;i<failMessageArray.size();i++){
//获取第i条错误信息
String oneFailMessage = failMessageArray[i]
//从Expected:处进行分隔
int expectedIndex = oneFailMessage.indexOf("Expected:")
//从got:处进行分隔
int gotIndex = oneFailMessage.indexOf("got:")
//解析错误信息中打印的JsonPath
String detailedJsonPath = oneFailMessage.substring(0,expectedIndex)
//解析JsonPath中的叶子节点
String lastLeafNode = detailedJsonPath.reverse().split("\.")[0].reverse()
//解析Expected的值
String expectedValue = oneFailMessage.substring(expectedIndex,gotIndex).split(":")[1]
//解析Actual的值
String actualValue = oneFailMessage.substring(gotIndex).split(":")[1]
//把需要输出的所有数据点拼接成一个list
def errorMessage = [testSuiteName,testCaseName]
errorMessage = errorMessage+valueList
errorMessage = errorMessage+[lastLeafNode,expectedValue,actualValue,detailedJsonPath]
//把每条单独的list拼接成一个总的list返回
failMessageList.add(errorMessage)
}
return failMessageList
}
def createExcel(File createFile, def topRow) {
String sheetName = "Automation Test";
try {
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFCellStyle cellStyleString = setCellStyleString(workbook)
XSSFCellStyle cellStyleTitle = setCellStyleTitle(workbook)
int columnsNum = topRow.size();// calculate columns size by topRow
XSSFSheet sheet = workbook.createSheet(sheetName);
int rownum = 0;
Row row = sheet.createRow(rownum++);
int cellnum = 0;
for (String cellString : topRow) {
Cell cell = row.createCell(cellnum++);
cell.setCellStyle(cellStyleString);
if (rownum == 1) {
cell.setCellStyle(cellStyleTitle);
}
// remove DIV style
if (cellString != null)
cellString = cellString.replaceAll('<(\S*?)[^>]*>',"")
cell.setCellValue(cellString);// insert value
}
sheet = setSheetStyle(sheet, columnsNum -1);// setting sheet style
FileOutputStream out = new FileOutputStream(createFile);
workbook.write(out);
out.flush();
out.close();
} catch (Exception e) {
e.printStackTrace();
}
}
//向Excel中追加数据
def addExcel(String excelPath, ArrayList<String[]> failMessageList) throws IOException{
int columnsNum = failMessageList[0].size();// calculate columns size first row
FileInputStream fs = new FileInputStream(excelPath);//获取excel
XSSFWorkbook wb = new XSSFWorkbook(fs);
XSSFSheet sheet = wb.getSheetAt(0);//获取工作表
XSSFRow row = sheet.getRow(0);//获取第一行(即:字段列头,便于赋值)
int lastRowNum = sheet.getLastRowNum()
FileOutputStream out = new FileOutputStream(excelPath);//向excel中添加数据
for (int i = 0; i < failMessageList.size(); i++) {
row = sheet.createRow(++lastRowNum);//在现有行号后追加数据
String[] addOneRowData = failMessageList[i];
for(int j=0;j<addOneRowData.size();j++){
String str = addOneRowData[j];
row.createCell(j).setCellValue(str);//设置单元格的数据
}
}
sheet = setSheetStyle(sheet, columnsNum -1);// setting sheet style
wb.write(out);
out.flush();
out.close();
}
def getExcelName( ) {
def currentStepIndex = context.currentStepIndex
def testCaseName = testRunner.testCase.getTestStepAt(currentStepIndex).getParent().getName()
def testSuiteName = testRunner.testCase.getTestStepAt(currentStepIndex).getParent().getParent().getName()
def excelName = testSuiteName + " _ " + testCaseName
excelName = excelName.replace(",","").replace(":","=").replace("/","").replace("*","")
return excelName
}
def fillInFailMessage(File createFile, ArrayList<String[]> failMessageList, String[] topRow) {
String sheetName = "Automation Test";
try {
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFCellStyle cellStyleString = setCellStyleString(workbook)
XSSFCellStyle cellStyleTitle = setCellStyleTitle(workbook)
XSSFCellStyle cellStyleFail = setCellStyleFail(workbook)
XSSFCellStyle cellStyleNull = setCellStyleNull(workbook)
failMessageList.add(0, topRow);
int columnsNum = topRow.length;// calculate columns size by topRow
XSSFSheet sheet = workbook.createSheet(sheetName);
int rownum = 0;
for (int i = 0; i < failMessageList.size(); i++) {
String[] rowStrings = failMessageList.get(i);
Row row = sheet.createRow(rownum++);
int cellnum = 0;
for (String cellString : rowStrings) {
Cell cell = row.createCell(cellnum++);
cell.setCellStyle(cellStyleString);
if (rownum == 1) {
cell.setCellStyle(cellStyleTitle);
} else if (cellString != null && cellString.contains("%") && cellnum == columnsNum) {
Number number = NumberFormat.getInstance().parse(cellString);
// If the actual deviation > 10%, change the cell color to red in the excel
if (number.doubleValue() > 10) {
cell.setCellStyle(cellStyleFail);
}
} else if ("null".equals(cellString) || "not null".equals(cellString)) {
// If cell value is "null" or "not null" change color to yellow in the excel
cell.setCellStyle(cellStyleNull);
}
// remove DIV style
if (cellString != null)
cellString = cellString.replaceAll('<(\S*?)[^>]*>',"")
cell.setCellValue(cellString);// insert value
}
}
sheet = setSheetStyle(sheet, columnsNum -1);// setting sheet style
FileOutputStream out = new FileOutputStream(createFile);
workbook.write(out);
out.flush();
out.close();
} catch (Exception e) {
e.printStackTrace();
}
}
def XSSFSheet setSheetStyle(XSSFSheet sheet, int columnsNum) {
sheet.createFreezePane(0, 1, 0, 1);
String columnRange = "A1:" + (char) (65 + columnsNum) + "1";
sheet.setAutoFilter(CellRangeAddress.valueOf(columnRange));
for (int i = 0; i <= columnsNum; i++)
sheet.autoSizeColumn(i);
return sheet;
}
def setCellStyleString(XSSFWorkbook workbook) {
XSSFCellStyle cellStyle = workbook.createCellStyle();
XSSFDataFormat dataFormat = workbook.createDataFormat();
cellStyle.setDataFormat(dataFormat.getFormat("@"));
return cellStyle;
}
def setCellStyleTitle(XSSFWorkbook workbook) {
XSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
cellStyle.setFillForegroundColor(new XSSFColor(new Color(131, 191, 90)));
return cellStyle;
}
def setCellStyleFail(XSSFWorkbook workbook) {
XSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
cellStyle.setFillForegroundColor(new XSSFColor(new Color(255, 0, 0)));
return cellStyle;
}
def setCellStyleNull(XSSFWorkbook workbook) {
XSSFCellStyle cellStyle = workbook.createCellStyle();
XSSFFont fontStyle = workbook.createFont();
fontStyle.setColor(new XSSFColor(new Color(255, 0, 0)));
fontStyle.setBold(true);
cellStyle.setFont(fontStyle);
return cellStyle;
}
}