Forum Discussion

SoapUser2012's avatar
SoapUser2012
Occasional Contributor
12 years ago

Get xls data in loop in groovy script

Hi ,

I have a testsuite with many test cases . I run this test suite of tcs from groovy script step in a loop .
I want to have testcase ids in a xls(external data sheet ) and for each iteration of for loop want to read one row from xls .

Want to read xls data in a loop in groovy.How can i do this ?

Is it possible to do the same thing thro data grid ? meaning i want to read one grid row per loop iteration.


Thanks in advance

Thanks

4 Replies

  • faizmuhammad's avatar
    faizmuhammad
    Occasional Contributor

    you can use jxl jre for this, put it in your ext and lib folder and call excel sheet 

    • vi's avatar
      vi
      Occasional Contributor

      can you please provide the steps how to do this

      • faizmuhammad's avatar
        faizmuhammad
        Occasional Contributor

        try following:

         

        Download jxl jar from http://www.java2s.com/Code/Jar/j/Downloadjxljar.htm

        Place copy of this jar file in following folders of your soapui installation:

        • SoapUI-5.2.1\lib
        • SoapUI-5.2.1\Bin\ext

        Create a sample excel sheet at any location (in this case I am placing my excel sheet in C drive folder name Excel i.e C:\\Excel) with any name (in this case I am using Temp.xls)

        Let’s say your excel sheet has following values

        @ A1 value1 is “SOAPUI1”

        @B1 value2 is “Smartbear1”

        @C1 value3 is “20161”

         

        @ A2 value1 is “SOAPUI2”

        @B2 value2 is “Smartbear2”

        @C2 value3 is “20162”

         

         

        And you would like to load these values in your soapui testsuite properties.

         

        Create three properties in testsuite properties

        • “RowSelector”
        • “Start” //enter the start row value of your excel sheet in this case it will be 0
        • “End” //enter the end row value of your excel sheet in this case it will be 2

         

        Create following groovy test step (test step name GroovySetup)at the beginning of all test steps

        import jxl.*

        import jxl.write.*

         

        def value1

        def value2

        def value3

         

        //pull value from test suite properties

         

        def RowSelector = testRunner.testCase.testSuite.getPropertyValue( "RowSelector" )

        //Read Excel

        Workbook workbook = Workbook.getWorkbook(new File("C:\\Excel\\Temp.xls"))

        //creating a copy to save results in

        WritableWorkbook copy = Workbook.createWorkbook(new File("C:\\Excel \\Results\\Output.xls"),workbook)

        File datasheet = new File("C:\\Program Files\\SmartBear\\SoapUI-5.2.1\\bin\\ext\\dataSheets\\ResultTemp.xls")

         

        if (datasheet.exists()){

           log.info("Exist")

        }

        else {

        log.info("Blank")

        }

         

        // loading Values from Excel (A1) to TestSuite properties

                                                value1 = wb.getSheet(0).getCell(0, RowSelector).getContents() //cell A1

                                                testRunner.testCase.testSuite.setPropertyValue( "TestSuitevalue1", value1 )

                                                log.info("Value1 Is: " + value1)

         

        // loading Values from Excel (B1) to TestSuite properties

                                                Value2 = wb.getSheet(0).getCell(1, RowSelector).getContents() //Cell B1

                                                testRunner.testCase.testSuite.setPropertyValue( "TestSuitevalue2", value2 )

                                                log.info("Value2 Is: " + value2)

         

        // loading Values from Excel (C1) to TestSuite properties

                                                Value3 = wb.getSheet(0).getCell(2, RowSelector).getContents() //Cell C1

                                                testRunner.testCase.testSuite.setPropertyValue( "TestSuitevalue3", value3 )

                                                log.info("Value3 Is: " + value3)

                                                wb.close()

        //End Groovy step

         

        ---------------------------------------------------------------------------------------------------------------------

        This groovy will load value from excel to your testsuite property.

         

        Now you can use these values as they are now available in your testsuite properties. If you would like to apply loop then at the end of the testcase add another groove script apply a loop counter code

         

        It should be something like

         -----------------------------------------------------------------------------------------------------------------------

        //Start Groovy step (say name GroovyLoop)

        def RowSelector = testRunner.testCase.testSuite.getPropertyValue( " RowSelector " )

        def Start = testRunner.testCase.testSuite.getPropertyValue( " Start " )

        def End = testRunner.testCase.testSuite.getPropertyValue( "End" )

         

        def start = ‘0’ //Loop start value u can use it for defining row or coloumn

         

        def end = ‘0’ //Loop end value u can use it for defining row or coloumn

        //setting up loop

         

        For (RowSelector = Start; RowSelector<=End; RowSelector++){

        testRunner.testCase.testSuite.setPropertyValue( " RowSelector ", RowSelector)

        //Reference https://www.soapui.org/Functional-Testing/controlling-flow.html

        testRunner.runTestStepByName( "GroovySetup") //calling first groovy step where we loaded excel and etc

        //if you have other test steps and you would like them to be part of this loop then place them too like sample below.

        //testRunner.runTestStepByName( "TestStepName")

         

        }