I have a need to read properties from soapUI using groovy and writing them to Excel. I initially had a problem with writing to Excel, but I was able to solve that. Now I want to know if my solution is correct from a best practice perspective and can it be optimized to work faster.
I use Groovy and scriptom to write and read data from Excel. The logical flow:
- construct a column name and property name map 2.loop through all test cases in a test suite 3.for each test case, construct a property and property value map for all property test steps.
- Construct a col name and col id map
- construct a col name and prop value map
- construct a col id and prop value map
- write data to Excel
/*
* Function to write data to excel
* tcmap = [#:[col num:col value]]
* xlPath = complete path to the excel file
* dtSheet = data sheet name
*/
def write2Excel(tcMap, xlPath, dtSheet){
def xl
def wb
def rng
if(tcMap == null || tcMap.size() == 0){
return false
}
if(xlPath.size() != 0){
def f = new File(xlPath)
if(!(f.exists() && f.isFile())){
return false
}
}else{
return false
}
if(dtSheet.size() == 0 ){
return false
}
xl = new ActiveXObject('Excel.Application')
if(xl == null){
return false
}
wb = xl.Workbooks.Open(xlPath)
if(wb == null){
return false
}
rng = wb.Sheets(dtSheet)
int rowMax = rng.UsedRange.Rows.Count.toInteger()
tcMap.eachWithIndex(){row, i -> //one key for each property test step
rowMax++
row.value.eachWithIndex(){ data, j -> //col num to value mapping
log.info data.key + ":" + data.value
log.info "data.value == \"\": " + (data.value == "")
//write data to excel
def val = data.value
rng.Cells(rowMax,data.key.toInteger()).Value = data.value
}
}
//save and close
xl.DisplayAlerts = false
wb.Save
xl.DisplayAlerts = true
wb.Close(false,null,false)
xl.Quit()
Scriptom.releaseApartment()
}
/*
* Function to generate a map with data for a test case, including
* data from multiple property test steps
* colHeadMap = [col name: col#]
* colPropMap = [step#:[col name: prop vale]]
* rowData = [[#:[col#:prop value]]
*/
def genRowData(testCaseName, colHeadMap,colPropMap){
def rowData = [:]
int mKey = 1
if(testCaseName == null || colHeadMap == null || colPropMap == null){
log.info "null check"
return rowData
}
if(testCaseName.size() == 0 || colHeadMap.size() == 0 || colPropMap.size() == 0){
log.info "size check"
return rowData
}
colPropMap.eachWithIndex(){ pRow, k -> //loop through all test steps which have properties
def tmp = [:]
pRow.value.eachWithIndex() { p, j -> //loop through all properties in a test step
def colNum = colHeadMap[p.key]
tmp.put(colNum,p.value)
}
//handle test case, stepnumber columns
colNum = colHeadMap["TestCase_Name"]
tmp.put(colNum,testCaseName)
colNum = colHeadMap["Step_No"]
int stpNo = 1 + k.toInteger()
tmp.put(colNum,stpNo)
rowData.put(k.toString(),tmp)
}
return rowData
}
/*
* Function to generate a property and property value map for a test case
*/
def genPropMap(tCase,propStep){
def propMap = [:]
def tcType = "class com.eviware.soapui.impl.wsdl.testcase.WsdlTestCase"
int k = 1
if(tCase == null){
log.info "tCase null"
return propMap
}
def tCaseClass = tCase.getClass().toString()
if(!tCaseClass.equalsIgnoreCase(tcType)){
return propMap
}
def tSteps = tCase.getTestStepsOfType((java.lang.Class)propStep)
if(tSteps.size() == 0){
return propMap
}
for(ts in tSteps){
def tStepProp = ts.getPropertyList()
if(tStepProp.size() == 0){
return propMap
}
def tMap = [:]
for(prop in tStepProp){
tMap.put(prop.getName(),prop.getValue())
}
propMap.put(k.toString(),tMap)
k = k+1
}
return propMap
}
/*
* Function to create a map of col header and col number
*/
def genColHeadingMap(xlPath, dtSheet){
def colHead = [:]
def xl = new ActiveXObject('Excel.Application')
assert xl != null, "Excel object not initalized"
//open excel
def wb = xl.Workbooks.Open(xlPath)
//get column name in a list
def rng = wb.Sheets(dtSheet).UsedRange
//get Column count
int iColumn = rng.Columns.Count.toInteger()
for(int i = 1;i<=iColumn;i++){
def cValue = rng.Cells(1,i).Value
if(cValue != null){
colHead.put(cValue,i.toString())
}
}
xl.DisplayAlerts = false
wb.Save
xl.DisplayAlerts = true
wb.Close(false,null,false)
xl.Quit()
Scriptom.releaseApartment()
return colHead
}
/*
* Function generates a map of excel col heading and property value
* colMap = [colname:propname]
* propMap = [stepNo:[propName:propValue]]
*/
def genColPropMap(colMap,propMap){
def rowMap = [:]
if(colMap == null){
return rowMap
}
if(colMap.size() == 0){
return rowMap
}
if(propMap == null){
return rowMap
}
//loop through all the property steps
int stp = 1
def tmp = [:]
for(prop in propMap){
//loop through all the properties
for(p in prop.value){
//find col name for the property name
def colName = colMap.find{ it.value == p.key}?.key
if(colName != null){
tmp.put(colName,p.value)
}
}
rowMap.put(stp.toString(),tmp)
stp++
}
return rowMap
}