July 28, 2019

SoapUI Data Driven Testing Groovy

Let us continue with SoapUI testing tutorials. This SoapUI tutorial for beginners is on SoapUI data driven testing with Groovy script. What is data driven testing? It means that you store the test data in some format e.g. in an XML file, an Excel sheet or a database and then use that test data in your tests. The advantage of data driven testing using SoapUI is that you can run your tests with multiple test data values. First view this SoapUI Data Driven Testing Groovy tutorial. Then continue reading.

SoapUI free version does not give the  user interface to create data driven tests. However, SoapUI Pro provides a DataSource test step to get test data from sources like XML files, Excel sheets, files, directories and databases. This test data can be put into SoapUI properties and used in test steps. Also, a DataSource Loop test step is available to loop the previous test steps for each row of test data in the data source.

Note: You can see how to do data driven testing in SoapUI free version in my tutorial on SoapUI Data Driven Testing Groovy.

We can write a Groovy script in Soap UI tool to get the test data and run our test steps. This is how I implemented data-driven testing using Groovy.
  1. There is a library to handle Excel files using Java code, called JExcelApi. I downloaded it from SourceForge. Then I unzipped it. After unzip, the jxl.jar should be copied to the SoapUI lib folder (alternately, it can be copied to the SoapUI bin/ext folder). Then, I re-started Soap UI.
  2. Next, I put my test data in an Excel file. In my case, there were two columns, one for Numbers and the other for the same number in words. I used each Number as a parameter of my test request. SoapUI load test data from file in Excel.
  3. Then, I added a Properties test step. I clicked on the + icon to add properties. There were 5 properties. Number stored the parameter value. Word tested the assertion. Counter, Total and End properties were used in the Groovy script logic. Counter has to have an initial value of 0. End has to have an initial value of False.
  4. Next, I added a Groovy script test step because we have to do data driven testing in SoapUI using groovy script.
    import jxl.* // import Java Excel API library
    def TestCase = context.testCase
    def FilePath = "E:\\Training\\SoapUI\\Files\\NumbersWords.xls"
    def count

    Workbook WorkBook1 = Workbook.getWorkbook(new File(FilePath))
    Sheet Sheet1 = WorkBook1.getSheet(0)
    PropertiesTestStep = TestCase.getTestStepByName("Properties")
    count = PropertiesTestStep.getPropertyValue("Counter").toInteger()

    //If Total records is unknown (at start), get the rowcount from Excel
    if (PropertiesTestStep.getPropertyValue("Total").toString() == "")
        PropertiesTestStep.setPropertyValue("Total", Sheet1.getRows().toString())
    count++

    //Read the Excel test data
    Cell Field1 = Sheet1.getCell(0, count)
    Cell Field2 = Sheet1.getCell(1, count)
    log.info ("Count is " + count.toString() + " Number : " + Field1.getContents() + " Word : " + Field2.getContents())
    WorkBook1.close()

    //Copy the Excel test data to properties in Properties test step
    PropertiesTestStep.setPropertyValue("Number", Field1.getContents())
    PropertiesTestStep.setPropertyValue("Word", Field2.getContents())
    PropertiesTestStep.setPropertyValue("Counter", count.toString())
    if (count == PropertiesTestStep.getPropertyValue("Total").toInteger() - 1)
        PropertiesTestStep.setPropertyValue("End", "True")

  5. Also, I added a Groovy script test step to implement the data loop.
    def TestCase = context.testCase
    PropertiesTestStep = TestCase.getTestStepByName("Properties")
    Stop = PropertiesTestStep.getPropertyValue("End").toString()
    if (Stop=="True")
        log.info("Exit Groovy Script - DataLoop")
    else
        testRunner.gotoStepByName("Groovy Script")
  6. One thing to keep in mind is that the test steps in the test case should have the Groovy script as the first step and Groovy script with data loop as the last step. 
  7. In the request, I put a property expansion as the parameter value. This means that the request read the Number parameter value from the property, Number. In order to test the response, I put an assertion. This assertion also used a property expansion The assertion wa tested against the property, Word.
  8. Ensured that the properties are initialized correctly. Then, I ran the test case. 
  9. After the test case is run, in the Properties test step, Number and Word should have the last row data. Also, in the script log, each Number and Word should have been used.
  10. I also had a cleanup step (disabled in the Step 6 image above) to reset the property values after each run of the test case.
    def TestCase = context.testCase
    PropertiesTestStep = TestCase.getTestStepByName("Properties")
    PropertiesTestStep.setPropertyValue("Number","")
    PropertiesTestStep.setPropertyValue("Word", "")
    PropertiesTestStep.setPropertyValue("Counter", "0")
    PropertiesTestStep.setPropertyValue("Total", "")
    PropertiesTestStep.setPropertyValue("End", "False")
This is how you can also do SoapUI data driven testing with Groovy script. If you want to see this complete Soap UI data driven testing example, it is available in my SoapUI data driven testing tutorial. Thank you.

28 comments:

  1. This script works perfectly but i want to write response values in to excel sheet row by row . How can i do that?
    The problem I am facing is every time i am creating new worksheet for each response.

    ReplyDelete
    Replies
    1. Good to know that this script works perfectly. You can use the log.info method to check the response value that you want to write. Consider my tips to write response values in Excel:
      1) Add the statement import jxl.write.*
      2) Use code like Sheet1.addCell(new jxl.write.Label(2,count,"test response value"))
      3) Add WorkBook1.write() statement before the WorkBook1.close() statement above.

      Delete
    2. I am able to fetch and write response into excel sheet but i want to write every response in excel sheet.how to do this.
      Below code i am using
      import groovy.json.JsonSlurper
      import jxl.*
      import jxl.write.*
      json_s = new JsonSlurper()
      response = context.expand('${CreateEmployee#Response}')
      log.info response
      json_response=json_s.parseText(response)
      log.info json_response
      def age = String.valueOf(json_response.data.age).replaceAll("[\\[\\](){}]","")
      log.info age
      def id = String.valueOf(json_response.data.id).replaceAll("[\\[\\](){}]","")
      log.info id
      def name = String.valueOf(json_response.data.name).replaceAll("[\\[\\](){}]","")
      log.info name
      def salary = String.valueOf(json_response.data.salary).replaceAll("[\\[\\](){}]","")
      log.info salary

      WritableWorkbook workbook = Workbook.createWorkbook(new File("c:\\Users\\Desktop\\TD_Write_SoapUI.xls"))
      WritableSheet sheet = workbook.createSheet("Worksheet 1", 0)

      Label label1 = new Label(0, 1, age);
      sheet.addCell(label1);
      Label label2 = new Label(1, 1, id);
      sheet.addCell(label2);
      Label label3 = new Label(2, 1, name);
      sheet.addCell(label3);
      Label label4 = new Label(3, 1, salary);
      sheet.addCell(label4);

      workbook.write()
      workbook.close()

      Delete
    3. "The problem I am facing is every time i am creating new worksheet for each response. " Note that in my script, 1) I used Workbook.getWorkbook(new File(FilePath)) instead of Workbook.createWorkbook(new File("c:\\Users\\Desktop\\TD_Write_SoapUI.xls"))
      2) I used WorkBook1.getSheet(0) instead of workbook.createSheet("Worksheet 1", 0) in your script.
      Basically, I used an existing Excel workbook with an existing worksheet unlike your script which creates a new Excel file and a new worksheet every time. Hope this helps.

      Delete
  2. Hi,

    I am using the code above in the blog. But get java.io.filenotfound exception.The system cannot find the file at specified location.
    Can you plse advise

    ReplyDelete
    Replies
    1. Hi, you should check the line number where you get the java.io.filenotfound exception. Do you have an xls file? Have you given Excel file location with full and correct file path?

      Delete
  3. Tried on the said code, but it fails processing post 7 rows, allthough the input excel may had 17 rows.[rowcount captured.]

    Wed Jun 03 23:45:21 IST 2020:INFO:Loopcount0
    Wed Jun 03 23:45:21 IST 2020:INFO:RowCount:17
    Wed Jun 03 23:45:21 IST 2020:INFO:Row Count is 1 Number : A2 Word : B2 URL is : C2
    Wed Jun 03 23:45:21 IST 2020:INFO:Loopcount1
    Wed Jun 03 23:45:21 IST 2020:INFO:RowCount:17
    Wed Jun 03 23:45:21 IST 2020:INFO:Row Count is 2 Number : A3 Word : B3 URL is : C3
    Wed Jun 03 23:45:21 IST 2020:INFO:Loopcount2
    Wed Jun 03 23:45:21 IST 2020:INFO:RowCount:17
    Wed Jun 03 23:45:21 IST 2020:INFO:Row Count is 3 Number : A4 Word : B4 URL is : C4
    Wed Jun 03 23:45:22 IST 2020:INFO:Loopcount3
    Wed Jun 03 23:45:22 IST 2020:INFO:RowCount:17
    Wed Jun 03 23:45:22 IST 2020:INFO:Row Count is 4 Number : A5 Word : B5 URL is : C5
    Wed Jun 03 23:45:22 IST 2020:INFO:Loopcount4
    Wed Jun 03 23:45:22 IST 2020:INFO:RowCount:17
    Wed Jun 03 23:45:22 IST 2020:INFO:Row Count is 5 Number : A6 Word : B6 URL is : C6
    Wed Jun 03 23:45:22 IST 2020:INFO:Loopcount5
    Wed Jun 03 23:45:22 IST 2020:INFO:RowCount:17
    Wed Jun 03 23:45:22 IST 2020:INFO:Row Count is 6 Number : A7 Word : B7 URL is : C7
    Wed Jun 03 23:45:22 IST 2020:INFO:Exit Groovy Script - DataLoop
    Wed Jun 03 23:47:07 IST 2020:INFO:Loopcount0
    Wed Jun 03 23:47:07 IST 2020:INFO:RowCount:17
    Wed Jun 03 23:47:07 IST 2020:INFO:Row Count is 1 Number : A2 Word : B2 URL is : C2
    Wed Jun 03 23:47:07 IST 2020:INFO:Loopcount1
    Wed Jun 03 23:47:07 IST 2020:INFO:RowCount:17
    Wed Jun 03 23:47:07 IST 2020:INFO:Row Count is 2 Number : A3 Word : B3 URL is : C3
    Wed Jun 03 23:47:08 IST 2020:INFO:Loopcount2
    Wed Jun 03 23:47:08 IST 2020:INFO:RowCount:17
    Wed Jun 03 23:47:08 IST 2020:INFO:Row Count is 3 Number : A4 Word : B4 URL is : C4
    Wed Jun 03 23:47:08 IST 2020:INFO:Loopcount3
    Wed Jun 03 23:47:08 IST 2020:INFO:RowCount:17
    Wed Jun 03 23:47:08 IST 2020:INFO:Row Count is 4 Number : A5 Word : B5 URL is : C5
    Wed Jun 03 23:47:08 IST 2020:INFO:Loopcount4
    Wed Jun 03 23:47:08 IST 2020:INFO:RowCount:17
    Wed Jun 03 23:47:08 IST 2020:INFO:Row Count is 5 Number : A6 Word : B6 URL is : C6
    Wed Jun 03 23:47:08 IST 2020:INFO:Loopcount5
    Wed Jun 03 23:47:08 IST 2020:INFO:RowCount:17
    Wed Jun 03 23:47:08 IST 2020:INFO:Row Count is 6 Number : A7 Word : B7 URL is : C7
    Wed Jun 03 23:47:08 IST 2020:INFO:Exit Groovy Script - DataLoop..

    ReplyDelete
    Replies
    1. It is not exactly the same code, because my code had no RowCount variable. You should debug your written code and your Excel file. Looking at your script log, it seems that your code is running the DataLoop two times for 6 rows. After running the Groovy Script, did you get the Total property correctly as 17? Remember that you need to run the Groovy Script - CleanUp before running the Groovy Script, every time.

      Delete
  4. Hi..This code supports xlsx format?

    ReplyDelete
    Replies
    1. Hi. No, JExcelApi supports only xls format. If you cannot save your test data to xls format, you can try Apache POI for xlsx format support.

      Thanks,
      Inder

      Delete
    2. Hi Inder, Can you please provide some steps on how to handle .xlsx format using Apache POI?

      Delete
    3. Hi, how do you know me? You should use the XSSF of Apache POI to handle .xlsx format.

      Delete
  5. hi,
    Thanks for explainating very well.I am getting this error. can you please help me out?
    org.codehaus.groovy.control.MultipleCompilationErrorsException: startup failed: Script20.groovy: 5: unable to resolve class Workbook @ line 5, column 10. Workbook WorkBook1 = Workbook.getWorkbook(new File("C:\\repo\\Book1.xls")) ^ org.codehaus.groovy.syntax.SyntaxException: unable to resolve class Workbook @ line 5, column 10. at org.codehaus.groovy.ast.ClassCodeVisitorSupport.addError(ClassCodeVisitorSupport.java:150) at org.codehaus.groovy.control.ResolveVisitor.resolveOrFail(ResolveVisitor.java:326) at org.codehaus.groovy.control.ResolveVisitor.resolveOrFail(ResolveVisitor.java:336) at org.codehaus.groovy.control.ResolveVisitor.transformVariableExpression(ResolveVisitor.java:1026) at org.codehaus.groovy.control.ResolveVisitor.transform(ResolveVisitor.java:758) at org.codehaus.groovy.control.ResolveVisitor.transformDeclarationExpression(ResolveVisitor.java:1165) at org.codehaus.groovy.control.ResolveVisitor.transform(ResolveVisitor.java:762) at org.codehaus.groovy.ast.ClassCodeExpressionTransformer.visitExpressionStatement(ClassCodeExpressionTransformer.java:144) at org.codehaus.groovy.ast.stmt.ExpressionStatement.visit(ExpressionStatement.java:42) at org.codehaus.groovy.ast.CodeVisitorSupport.visitBlockStatement(CodeVisitorSupport.java:88) at org.codehaus.groovy.ast.ClassCodeVisitorSupport.visitBlockStatement(ClassCodeVisitorSupport.java:166) at org.codehaus.groovy.control.ResolveVisitor.visitBlockStatement(ResolveVisitor.java:1402) at org.codehaus.groovy.ast.stmt.BlockStatement.visit(BlockStatement.java:71) at org.codehaus.groovy.ast.ClassCodeVisitorSupport.visitClassCodeContainer(ClassCodeVisitorSupport.java:104) at org.codehaus.groovy.ast.ClassCodeVisitorSupport.visitConstructorOrMethod(ClassCodeVisitorSupport.java:115) at org.codehaus.groovy.ast.ClassCodeExpressionTransformer.visitConstructorOrMethod(ClassCodeExpressionTransformer.java:55) at org.codehaus.groovy.control.ResolveVisitor.visitConstructorOrMethod(ResolveVisitor.java:239) at org.codehaus.groovy.ast.ClassCodeVisitorSupport.visitMethod(ClassCodeVisitorSupport.java:126) at org.codehaus.groovy.ast.ClassNode.visitContents(ClassNode.java:1081) at org.codehaus.groovy.ast.ClassCodeVisitorSupport.visitClass(ClassCodeVisitorSupport.java:53) at org.codehaus.groovy.control.ResolveVisitor.visitClass(ResolveVisitor.java:1345) at org.codehaus.groovy.control.ResolveVisitor.startResolving(ResolveVisitor.java:214) at org.codehaus.groovy.control.CompilationUnit$12.call(CompilationUnit.java:684) at org.codehaus.groovy.control.CompilationUnit.applyToSourceUnits(CompilationUnit.java:966) at org.codehaus.groovy.control.CompilationUnit.doPhaseOperation(CompilationUnit.java:626) at org.codehaus.groovy.control.CompilationUnit.compile(CompilationUnit.java:575) at groovy.lang.GroovyClassLoader.doParseClass(GroovyClassLoader.java:323) at groovy.lang.GroovyClassLoader.parseClass(GroovyClassLoader.java:293) at

    ReplyDelete
    Replies
    1. I am getting the same error. Anyone figuered this out yet? Thanks!

      Delete
  6. Thank you so much @Inder P Singh
    This blog is very helpful.Thanks for your effort and excellent work.
    Would like to ask you one query here that, in my project there are n number of test cases are there. So I want to fetch data from excel sheet by Sheet name not by index.So could you please help, how can we read the excel file by excel sheet name(e.g TestCase-1,TestCase-2 etc.)?

    ReplyDelete
    Replies
    1. Hi @NP. Thank you for your recommendation. Regarding your query, you can try the same getSheet method with Excel sheet name e.g. Sheet Sheet1 = WorkBook1.getSheet("Sheet1"). Further, you could try to get all the Excel sheet names with WorkBook1.getSheetNames() in a string array.

      Delete
    2. Thank you so much🙏. I will try.

      Delete
    3. Truly Thanks again and much appreciated @Inder P Singh
      I am able to fetch data from excelsheet with test case name.
      Could you please to help post - How can we generate a good html report from SOAP UI free version. Currently I'm using Apache Ant to run and generate html report(junit no frame report) . Report looks OK. But in the report, there is no no graphs, no iteration details and many more are missing.Thanks in advance

      Delete
    4. @NP - You are very welcome. I think that your approach is the correct way to generate HTML report. If you want a better report, you can transform the XML. Or you may want to use ReadyAPI. Thanks.

      Delete
  7. Hi Inder,
    Your blog and videos was very helpful in the tough times.
    I have tried using the same code and customized according to the need. Below getting the below error
    "java.lang.ArrayIndexOutOfBoundsException: Index 3 out of bounds for length 3"

    Can you pls let me know in which part of code to be modified to resolve this issue.

    Thanks

    ReplyDelete
    Replies
    1. Hi Vignesh,

      Thank you for your support to my blog and videos in Software and Testing Training channel.
      You should check the line number where you get the Java ArrayIndexOutOfBoundsException in the script log. Also, you need to run the Groovy Script - CleanUp before running the Groovy Script, every time.

      Inder

      Delete
  8. Good day say. Im new in soapui. ive try yourcode somehow the error appear they cant read ToInteger with "java numberformatexception error" can you advice how can this error be solved?

    ReplyDelete
    Replies
    1. You should check the line number where you get this error. But, first view the demonstration of the code in SoapUI in my SoapUI Data Driven Testing Groovy Tutorial at https://youtu.be/MndDpJvzmy4

      Delete
  9. Hi Inder, above code works perfectly and very useful. Can u also provide code for writing the xml response to the same excel sheet from where data is read please?

    ReplyDelete
  10. Hello Inder,

    I am using free soap-ui version . for basic project (Calculator WSDL). for that i want to save soapui response values to excel, I have written groovy script for that like shown below , am passing multi data in request but in excel its saving last response only , i want to get all response values. can any one please help me.

    import jxl.*
    import jxl.write.*

    def groovyUtils = new com.eviware.soapui.support.GroovyUtils( context )
    def holder = groovyUtils.getXmlHolder("Add#Response")

    log.info holder.getXml()

    WritableWorkbook workbook = Workbook.createWorkbook(new File("F:\\Usersdata\\output4.xls"))
    WritableSheet sheet = workbook.createSheet("Worksheet 1", 0)

    log.info(sheet.isHidden())

    xPath1 = "//*:AddResult/text()"

    log.info holder.getNodeValue(xPath1)


    Label label = new Label(0, 1, holder.getNodeValue(xPath1));
    sheet.addCell(label);



    workbook.write();
    workbook.close();

    ReplyDelete
    Replies
    1. Hello, your Groovy script is a data-driven testing script but different from my script above. I don't see any data loop in your script. Best wishes, Inder

      Delete

Note: Only a member of this blog may post a comment.