Take the 2-minute tour ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.

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:

  1. 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.
  2. Construct a col name and col id map
  3. construct a col name and prop value map
  4. construct a col id and prop value map
  5. 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
}
share|improve this question
1  
Is there a hard requirement to read/write in xsl/xslx format? For instance, Excel can read/write data in CSV format, which is FAR easier to work with, IMO. –  Graham Feb 26 at 23:16
    
I'd have to agree with @Graham on this. Even if Excel file formats are required, it would be far easier to write a CSV, and then just use Excel for a quick import and save file-format conversion. –  Comintern Feb 27 at 1:16
    
I am trying to put related data in a single excel and have designed it such that there are multiple sheets in one excel. CSV is a good option but how do i handle multiple sheets? I would like to make it easier from a non tech person to be able to edit this excel. –  Abhishek Asthana Feb 27 at 10:55

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.