[SoapUI] 从测试套件，测试用例，测试步骤，测试数据各个级别控制是否执行

自动保存

# -*- coding: utf-8 -*-  

import java.awt.Color

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.XSSFSheet
import org.apache.poi.xssf.usermodel.XSSFWorkbook
import org.apache.poi.xssf.usermodel.XSSFDataValidationHelper
import org.apache.poi.xssf.usermodel.XSSFDataValidation
import org.apache.poi.xssf.usermodel.XSSFDataValidationConstraint
import org.apache.poi.ss.util.CellRangeAddressList

/*  1.获得所有测试套件，测试用例，测试步骤（可只选择发送Rest请求的步骤）的名字以及层级关系
**  2.分3个sheet页保存测试套件，测试用例，测试步骤的名字到Excel
**  3.这3个sheet页分别从测试套件，测试用例，测试步骤的级别增加一个Enable列，用来解析,以便通过SoapUI控制其是否执行
*/


String filePath = context.expand( ‘${projectDir}‘ ) + "/TestData/" + "TestSuiteTestCaseTestStepList.xlsx"
//String filePath = "D:/Automation_Develop_Work/SelectTestCaseToRunInSoapUI/TestSuiteTestCaseTestStepList.xlsx"

//获取所有测试套件，测试用例，测试步骤的名称，存为linkedHashMap
LinkedHashMap<String, String>  testSetMap = getTestSetMap(log,testRunner)

//写入Excel
writeTestSetIntoExcel(filePath,testSetMap)
log.info "Save Success!"

///////////////////////////////////////////以下为封装的基础方法///////////////////////////////////////////////
// 获取所有的测试套件，测试用例，测试步骤的名称
static def getTestSetMap(log,testRunner){
	def testSuiteList =  testRunner.testCase.testSuite.project.getTestSuiteList()
	def testCaseList
	def testStepList
	def testStepName
	
	LinkedHashMap<String, String> testSetMap = new LinkedHashMap<String, String>()
	for(testSuite in testSuiteList){
		//log.info "Test Suite : "+testSuite.name
		testCaseList =  testSuite.getTestCaseList()
	
		def testCaseMap = [:]
		for(testCase in testCaseList){
			//log.info "Test Case : "+testCase.name
			// 只获取发送Rest请求的步骤,如果要获取所有测试步骤，改为testStepList = testCase.getTestStepList()
			testStepList = testCase.getTestStepsOfType(com.eviware.soapui.impl.wsdl.teststeps.RestTestRequestStep)
			def testStepNameList = []
			for (testStep in testStepList){
				testStepName = testStep.name
				testStepNameList.add(testStepName)
				//log.info "Test Step : "+testStepName
			}
			testCaseMap.put(testCase.name,testStepNameList)
		}
		testSetMap.put(testSuite.name,testCaseMap)
	}
	//log.info testSetMap
	return testSetMap
}

// 将所有的测试套件，测试用例，测试步骤的名称写入Excel
static def writeTestSetIntoExcel(filePath,testSetMap){
	XSSFWorkbook workbook = new XSSFWorkbook()

	XSSFSheet sheet1 = workbook.createSheet("Test Suite")
	XSSFSheet sheet2 = workbook.createSheet("Test Case")
	XSSFSheet sheet3 = workbook.createSheet("Test Step")

	//写第一个sheet页
	def topRow1 = ["Test Suite","Enable"]
	writeTitleIntoExcel(topRow1,workbook,sheet1)
	writeTestSuiteIntoExcel(testSetMap,sheet1)
	int columnNum1 = topRow1.size()
	setSheetStyle(sheet1,columnNum1)
	setAutoSizeColumn(sheet1,columnNum1)
	
	//写第二个sheet页
	def topRow2 = ["Test Suite","Test Case","Enable"]
	sheet2.setColumnWidth(0, 50*256)
	sheet2.setColumnWidth(1, 80*256)
	sheet2.setColumnWidth(2, 7*256)
	writeTitleIntoExcel(topRow2,workbook,sheet2)
	writeTestSuiteAndTestCaseIntoExcel(testSetMap,workbook,sheet2)
	int columnNum2 = topRow2.size()
	setSheetStyle(sheet2,columnNum2)
	
	//写第三个sheet页
	def topRow3 = ["Test Suite","Test Case","Test Step","Enable"]
	sheet3.setColumnWidth(0, 50*256)
	sheet3.setColumnWidth(1, 50*256)
	sheet3.setColumnWidth(2, 50*256)
	sheet3.setColumnWidth(3, 7*256)
	writeTitleIntoExcel(topRow3,workbook,sheet3)
	writeTestSuiteTestCaseTestStepIntoExcel(testSetMap,workbook,sheet3)
	int columnNum3 = topRow3.size()
	setSheetStyle(sheet3,columnNum3)
	
	
	FileOutputStream fileOut = new FileOutputStream(filePath)
	workbook.write(fileOut)
	fileOut.flush()
	fileOut.close()
}

// 写title行
static def writeTitleIntoExcel(topRow,workbook,sheet){
	XSSFCellStyle cellStyleTitle = setCellStyleTitle(workbook)
	Row row = sheet.createRow(0)
	int cellnum = 0
	for (String cellString : topRow) {
	  Cell cell = row.createCell(cellnum++)
	  cell.setCellStyle(cellStyleTitle)
	  cell.setCellValue(cellString)
	}
}

// 写测试套件级别
static def writeTestSuiteIntoExcel(testSetMap,sheet){
	String[] enableTextList = ["Y","N"]
	int rownum = 1
	def testSuitesList = testSetMap.keySet()
	sheet.addValidationData(setDataValidation(sheet,enableTextList,1,testSuitesList.size(),1,1))
	for (String testSuite : testSuitesList) {
	  int cellnum = 0
	  def row = sheet.createRow(rownum++)
	  Cell cell = row.createCell(cellnum++)
	  cell.setCellValue(testSuite)
	  Cell cellEnable = row.createCell(cellnum++)
	  cellEnable.setCellValue("Y")
	}
}

// 写测试用例级别 及其层级关系
static def writeTestSuiteAndTestCaseIntoExcel(testSetMap,workbook,sheet){
   // 设置自动换行
   XSSFCellStyle cellStyle = workbook.createCellStyle()
   cellStyle.setWrapText(true)
   	
   String[] enableTextList = ["Y","N"]
   int rownum = 1
   int testCasesTotalNumber = 0
   def testSuitesList = testSetMap.keySet()
   for (String testSuite : testSuitesList) {
       def testCaseMap = testSetMap.get(testSuite)
       def testCasesList = testCaseMap.keySet()
	  testCasesTotalNumber += testCasesList.size()
       int i=0
       for (String testCase : testCasesList) {
           def testRow
           if((i++)==0){
               testRow = [testSuite,testCase,"Y"]
           }
           else{
               testRow = ["",testCase,"Y"]
           }
           Row row = sheet.createRow(rownum++)
           int cellnum = 0
           for (String cellString : testRow) {
               Cell cell = row.createCell(cellnum++)
               cell.setCellValue(cellString)
               cell.setCellStyle(cellStyle)
           }
       }
   }
   sheet.addValidationData(setDataValidation(sheet,enableTextList,1,testCasesTotalNumber,2,2))
}

// 写测试步骤级别 及其层级关系
static def writeTestSuiteTestCaseTestStepIntoExcel(testSetMap,workbook,sheet){
	// 设置自动换行
	XSSFCellStyle cellStyle = workbook.createCellStyle()
	cellStyle.setWrapText(true)
	
	String[] enableTextList = ["Y","N"]
	int rownum = 1
	int testStepsTotalNumber = 0
	def testSuitesList = testSetMap.keySet()
	for (String testSuite : testSuitesList){
	  def testCaseMap = testSetMap.get(testSuite)
	  def testCasesList = testCaseMap.keySet()
	  int i=0
	  for (String testCase : testCasesList){
	      def testStepsList = testCaseMap.get(testCase)
	      testStepsTotalNumber += testStepsList.size()
	      int j=0
	      for(String testStep : testStepsList){
	          def testRow
	          if(i==0){
	              testRow = [testSuite,testCase,testStep,"Y"]
	          }
	          else if (j==0){
	          	testRow = ["",testCase,testStep,"Y"]
	          }
	          else{
	              testRow = ["","",testStep,"Y"]
	          }
	          Row row = sheet.createRow(rownum++)
	          int cellnum = 0
	          for (String cellString : testRow) {
	              Cell cell = row.createCell(cellnum++)
	              cell.setCellValue(cellString)
	              cell.setCellStyle(cellStyle)
	          }
	          j++
	          i++
	      }
	  }
	}
	sheet.addValidationData(setDataValidation(sheet,enableTextList,1,testStepsTotalNumber,3,3))
}

// 设置sheet的样式
static def setSheetStyle(XSSFSheet sheet, int columnsNum) {
   sheet.createFreezePane(0, 1, 0, 1)  //冻结第一行
   String columnRange = "A1:" + (char) (65 + (columnsNum-1)) + "1" 
   sheet.setAutoFilter(CellRangeAddress.valueOf(columnRange)) //设置自动筛选
//   for (int i = 0; i <= columnsNum; i++)
//       sheet.autoSizeColumn(i)   //自动调整列宽
   return sheet
}

// 自动调整列宽
static def setAutoSizeColumn(XSSFSheet sheet, int columnsNum) {
   for (int i = 0; i <= columnsNum; i++)
       sheet.autoSizeColumn(i)   //自动调整列宽
   return sheet
}

// 设置Title行的样式
static def setCellStyleTitle(XSSFWorkbook workbook) {
   XSSFCellStyle cellStyle = workbook.createCellStyle()
   cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND)
   cellStyle.setFillForegroundColor(new XSSFColor(new Color(34, 139, 34)))  //设为绿色背景
   return cellStyle
}

static def setDataValidation(sheet,textList,firstRow,endRow,firstCol,endCol) {    
        
   XSSFDataValidationHelper helper = sheet.getDataValidationHelper()
   // 加载下拉列表内容
   XSSFDataValidationConstraint constraint = helper.createExplicitListConstraint(textList)
   constraint.setExplicitListValues(textList)
   
   // 设置数据有效性加载在哪个单元格上
   // 四个参数分别是：起始行、终止行、起始列、终止列
   CellRangeAddressList regions = new CellRangeAddressList((short) firstRow, (short) endRow, (short) firstCol, (short) endCol)
   
   // 数据有效性对象
   XSSFDataValidation dataValidation = helper.createValidation(constraint, regions)
   return dataValidation
}   

　　控制测试套件，测试用例，测试步骤的执行

# -*- coding: utf-8 -*-  


import org.apache.poi.xssf.usermodel.XSSFRow
import org.apache.poi.xssf.usermodel.XSSFSheet
import org.apache.poi.xssf.usermodel.XSSFWorkbook

String filePath = context.expand( ‘${projectDir}‘ ) + "/TestData/" + "TestSuiteTestCaseTestStepList.xlsx"
//String filePath = "D:/Automation_Develop_Work/SelectTestCaseToRunInSoapUI/TestSuiteTestCaseTestStepList4.xlsx"
FileInputStream fileInputStream = new FileInputStream(filePath)
XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream)

def testSuiteMap = getInfoFromExcel(workbook)

//遍历Test Suite级别
def testSuiteList = testRunner.testCase.testSuite.project.getTestSuiteList()
for(testSuite in testSuiteList){
	def testSuiteName = testSuite.getName()
	log.info "Test Suite Name : "+testSuiteName
	def testSuiteValue = testSuiteMap.get(testSuiteName)
	def testSuiteEnable = testSuiteValue.get("enable")
	log.info "Test Suite Enable : "+testSuiteEnable
	if(testSuiteEnable == "Y"){
		testSuite.setDisabled(false)
	}
	if(testSuiteEnable == "N"){
		testSuite.setDisabled(true)
	}
	def testCaseMap = testSuiteValue

	 //遍历Test Case级别
	def testCaseList = testSuite.getTestCaseList()
	for(testCase in testCaseList){
		def testCaseName = testCase.getName()
		
		def testCaseValue = testCaseMap.get(testCaseName)

		log.info "Test Case Name : "+testCaseName
		log.info  "Test Case Value : "+testCaseValue
		def testCaseEnable = "Y"
		try{
			testCaseEnable = testCaseValue.get("enable")
		}
		catch (Exception e){
			testCaseEnable = "N"
		}
		log.info "Test Case Enable : "+testCaseEnable
		if(testCaseEnable == "Y"){
			testCase.setDisabled(false)
		}
		if(testCaseEnable == "N"){
			testCase.setDisabled(true)
		}
		def testStepMap = testCaseValue

		//遍历Test Step级别
		def testStepList = testCase.getTestStepList()
		def testStepEnableNumForRestRequest = 0
		// Rest请求的enable状态根据excel里面的填的进行设置
		for(testStep in testStepList){
			if (testStep instanceof com.eviware.soapui.impl.wsdl.teststeps.RestTestRequestStep){
				def testStepName = testStep.getName()
				def testStepValue
				try{
					testStepValue = testStepMap.get(testStepName)
				}
				catch (Exception e){
					testStepValue = ["enable":"N"]
				}
				log.info "Test Step Name : "+testStepName
				log.info  "Test Step Value : "+testStepValue
				def testStepEnableForRestRequest = testStepValue.get("enable")
				log.info "Test Step Enable : "+testStepEnableForRestRequest
				if(testStepEnableForRestRequest == "Y"){
					testStep.setDisabled(false)
					testStepEnableNumForRestRequest += 1
				}
				if(testStepEnableForRestRequest == "N"){
					testStep.setDisabled(true)
				}
			}			
		}

		//非Rest请求的状态需要特殊处理，只要当前test case下面有一个Rest 请求要求是enable的，所有非Rest请求都设为Enable的
		def testStepEnableForNonRestRequest = "N"
		if(testStepEnableNumForRestRequest>0){
			testStepEnableForNonRestRequest = "Y"
		}
		for(testStep in testStepList){
			if (!(testStep instanceof com.eviware.soapui.impl.wsdl.teststeps.RestTestRequestStep)){
				if(testStepEnableForNonRestRequest == "Y"){
					testStep.setDisabled(false)
				}
				if(testStepEnableForNonRestRequest == "N"){
					testStep.setDisabled(true)
				}
			}
		}
	}
}

///////////////////////////////////////////以下为封装的基础方法///////////////////////////////////////////////
// 从三个sheet页获取test suite,test case,test step的信息
private def getInfoFromExcel(XSSFWorkbook workbook) {
    def testSuiteMap = [:]
    // 先处理第3个sheet页，再处理第2个，最后处理第1个，谁的信息最多，就先处理谁
    getInfoFromThirdSheet(workbook, testSuiteMap)
    getInfoFromSecondSheet(workbook, testSuiteMap)
    getInfoFromFirstSheet(workbook, testSuiteMap)
    return testSuiteMap
}

// 处理第三个sheet页
private void getInfoFromThirdSheet(XSSFWorkbook workbook, LinkedHashMap testSuiteMap) {
    def testCaseMap = [:]
    def testStepMap = [:]

    XSSFSheet sheet = workbook.getSheetAt(2)
    int lastRowNum = sheet.getLastRowNum()
    String testSuite = ""
    String testCase = ""
    for (int rowNum = 1; rowNum <= lastRowNum; rowNum++) {

        XSSFRow row = sheet.getRow(rowNum)
        String firstCellInRow = row.getCell(0).getStringCellValue()
        String secondCellInRow = row.getCell(1).getStringCellValue()
        if (firstCellInRow != "") {
            testSuite = firstCellInRow
            testCaseMap = [:]
        }
        if (secondCellInRow != "") {
            testCase = secondCellInRow
            testStepMap = [:]
        }
        String testStep = row.getCell(2).getStringCellValue()
        String enable = row.getCell(3).getStringCellValue()

        def enableMap = ["enable": enable]
        testStepMap.put(testStep, enableMap)
        testCaseMap.put(testCase, testStepMap)
        testSuiteMap.put(testSuite, testCaseMap)
    }
}

// 处理第二个sheet页
private void getInfoFromSecondSheet(XSSFWorkbook workbook, LinkedHashMap testSuiteMap) {
    XSSFSheet sheet = workbook.getSheetAt(1)
    int lastRowNum = sheet.getLastRowNum()
    String testSuite = ""
    for (int rowNum = 1; rowNum <= lastRowNum; rowNum++) {
        XSSFRow row = sheet.getRow(rowNum)
        String firstCellInRow =""
        try{
            firstCellInRow = row.getCell(0).getStringCellValue()
        }
        catch (NullPointerException e){
            // 第一个单元格为null时，不去通过getStringCellValue()获取单元格的内容，而是直接引用初始值
        }
        // 因为test suite和前面的一样时，excel单元格那里是空着的，所以需要保存前面test suite的名字用于填充map
        if (firstCellInRow != "") {
            testSuite = firstCellInRow
        }
        String testCase = row.getCell(1).getStringCellValue()
        String enable = row.getCell(2).getStringCellValue()

        // 当我们只将发送Rest请求的test step写入excel时，有可能某些test suite或test case下面一个发送rest请求的步骤都没有
        // 这种情况在处理第一个和第二个sheet页的时候会出现找不到对应此test suite和test case的key，因为我们是从第3个sheet页开始处理生成map的
        if(!testSuiteMap.containsKey(testSuite)){
            def testCaseMap = [:]
            testCaseMap.put(testCase,["enable": enable])
            testSuiteMap.put(testSuite, testCaseMap)
        }
        if(!testSuiteMap.get(testSuite).containsKey(testCase)){
            testSuiteMap.get(testSuite).put(testCase,["enable": enable])
        }
        else{
            testSuiteMap.get(testSuite).get(testCase).put("enable",enable)
        }
    }
}

// 处理第一个sheet页
private void getInfoFromFirstSheet(XSSFWorkbook workbook, LinkedHashMap testSuiteMap) {
    XSSFSheet sheet = workbook.getSheetAt(0)
    int lastRowNum = sheet.getLastRowNum()
    for (int rowNum = 1; rowNum <= lastRowNum; rowNum++) {
        XSSFRow row = sheet.getRow(rowNum)
        String testSuite = row.getCell(0).getStringCellValue()
        String enable = row.getCell(1).getStringCellValue()
        // 第1个sheet页存在的test suite在第2个和第3个sheet页都不存在时，需要做特殊处理
        if(!testSuiteMap.containsKey(testSuite)){
            testSuiteMap.put(testSuite, ["enable": enable])
        }
        else{
            testSuiteMap.get(testSuite).put("enable", enable)
        }
    }
}

　　控制测试数据的执行

# -*- coding: utf-8 -*-  

/*  1.假定Data Source格式为Excel
**  2.在Excel中增加一列Enable
**  3.读取这个Enable列的值，如果为Y，运行，如果为N，不运行
*/

def ifEnable = context.expand(‘${DataSource#Enable}‘)
def testRunnerResult = testRunner.results[testRunner.results.size()-1].status

//如果此行ifEnable=="N"，不运行此行测试数据，将DataSink中result设置为SKIP,并跳转到DataSink运行
if(ifEnable=="N"){
	context.testCase.getTestStepByName("DataSink").setPropertyValue("result", "SKIP")
	testRunner.gotoStepByName("DataSink")
}
//如果此行ifEnable=="Y",运行此行测试数据，将DataSink中result设置为当前testRunner的结果
else
	context.testCase.getTestStepByName("DataSink").setPropertyValue("result", testRunnerResult)