Monday, June 16, 2014

DataDriven Testing in SOAPUI Open Source WebService Test Tool using Excel File.


DataDriven testing is always comes first when evaluating any test automation tool, and other features depends on the needs for the project. And most of the Test Automation tools which supports DataDriven testing as paid tools I mean to say strictly Licensed, some of the tools are less in License price and some of them are very high, due to this reason all some of the test automation tools creators provides Open Source tools which are less in the features.

Now in this post I am going to describe some technique to DataDriven testing on SoapUI Open Source tool, in which there is no DataDriven feature present. Which helps you in saving License price. (After publishing this post one team will find me for sure, I Am Sorry Team).

So let us start, as you all know how to create project, Test Suite and Test Case (or refer my other post for the same).

In below screen you can see the Project Test Suite and Test Case created. In right pane, you can see the xml, which is actual request xml for the “SendSMSToIndia” request.













Also observe there are totally three fields are present in which we need to enter the data to it from the excel sheet.

        <web:MobileNumber>?</web:MobileNumber>
         <web:FromEmailAddress>?</web:FromEmailAddress>
         <web:Message>?</web:Message>

Now move on to preparing the Input data for the request fields from the excel file, to do that just follow the steps,

Open excel file and create header as same as the fields name, here in our test request we have fields name as above so here header is as below in excel file, ’MobileNumber’, ‘FromEmailAddress’ and ‘Message’.











Now it’s time to prepare the input excel file with required test data, may be Valid or invalid based on the project requirement. After successful update of the test data into the excel file, the file will looks like below.




















Now its time to add import this test data to the request xml by using Groovy script. Write Groovy/Java code to read the Excel file and use the SOAPUI inbuilt library functions and some groovy scripting function to read and assign value to the xml tag in the request xml.
   
To do so add Groovy Script to Test Step to the Test Suite, once you add then your request looks as below,






















Now write the Groovy/Java code to read the excel file data and Groovy. Refer the below screen shot for the code.


Once you done with the writing above code in your Groovy Script in the Test Step, then now its time to execute the Groovy script. When we run the Groovy script this script will read, excel sheet data and assigns the value to the xml respective xml tag in to the Request xml and run or execute the “SendSMSToIndia” request.

Now its time to read the Request’s Response xml. When we execute the “SendSMSToIndia” request then below response is returned by the system,












In the response xml we can see ‘FromEmailAddress’ ,’MobileNumber’, ’Provider’ ,’State’, and ’Status’ are the tags which need to verify in the response. For this need to below lines code to get value from response.







Keep these lines of code inside the for loop, why system reads the each row till last row of the excel sheet input data and assigns to respective tag of the request xml, and once the request executes then system generates the response xml then above code reads the respective or required tags from the response xml. Once you add above code to the Groovy script code then finally script looks like below.



In short Script will read the excel sheet data as cell value (row, column), this values is as same as tag name in the request xml.
Script will read completely content of the xml and stores in the holder. This holder will take above excel cell value to verify the tag is exist in the xml or not, if exist then enter the value and update the xml.
Script will also execute Request for each record in excel until the last row of excel.

Now you can use these parameters which you read from the response xml, and update into the excel sheet just as Report.

Once you done with this then you have saved some more budget to your organization, which you are spending on buying License every year.

 With this…

That’s all Folks.


132 comments:

  1. Sunny...Good blog...very useful...keep posting

    ReplyDelete
  2. One query.....
    When i am trying to execute the request manually by providing mob.no, email & msg...in the Response, it is giving this......
    Please enter correct from email address to send this message
    I have provided correct email id, still....what is this issue & what is the solution?

    ReplyDelete
  3. hi I am getting biff exception in line where you have written same file name with copy variable

    ReplyDelete
  4. Could you please let me know how to read the data from SOAP UI response and write to excel. This is required to compare the SOAP UI response data with another source in EXCEL, please Help.

    ReplyDelete
    Replies
    1. Hi Steve,

      Thanks for your time for reading my blog.

      In the last screenshot i have provided the code to read value from SoapUI Response under "//Read Response XML" and to write to Excel sheet follow the steps under "//Write Response Value to Excel Sheet". Hope i have cleared your query.

      Regards,
      Sandeep S S

      Delete
  5. This comment has been removed by the author.

    ReplyDelete
  6. Hi sunny,

    if we don't get the expected response then how we can write that failed response value to excel.

    ReplyDelete
    Replies
    1. Hello,

      Thanks for reading my blog,

      As you can see above last screenshot, there response values are taking into a variable, in that variable you will have actual value. Now you have to compare expected value and actual value by using If...Then condition.

      To write back to excel refer last screenshot of this blog

      please refer below link for sample groovy script, there you will find If..Then condition with example.

      http://lgsofttest.blogspot.no/2016/03/useful-groovy-scripts-for-soapui.html




      I hope this solves your query, if not let me know.

      Regards,
      Sandeep S S

      Delete
  7. This comment has been removed by the author.

    ReplyDelete
  8. This comment has been removed by the author.

    ReplyDelete
  9. Hi Sunny,
    thank you very much for your reply that is solved my issue.

    I have another question when I pass wrong input I am getting below fault response and I want get the value of "longDescription" tag. please tell me how can I get this value
    I tried with your code but it is returning "null"

    soapenv:Server
    WebServiceException


    ValidationError
    MBR0005
    < longDescription>No record matched search criteria
    errorFault
    </detail
    /soapenv:Fault
    /soapenv:Body
    /soapenv:Envelope


    ReplyDelete
    Replies
    1. Hello,

      It's good to hear that your query resolved.

      For this query you can use same line of code that is used for valid value. Like below;

      resholder.getNodeValues("//*:longDescription")


      This will read value from "longDescription" tag/xmlnode, same value you can store in variable or update in report.

      Regards,
      Sandeep S S

      Delete
  10. Hi Sunny,

    I tried executing the first script which is to get data from an excel.
    But it gives me the following compilation error.

    org.codehaus.groovy.control.MultipleCompilationErrorsException: startup failed: Script22.groovy: 26: unexpected token: ) @ line 26, column 54. holder["count(//*:"+reqTagName")"] ^ org.codehaus.groovy.syntax.SyntaxException: unexpected token: ) @ line 26, column 54. at

    Could you pls tell me what is wrong with that.

    -Mary

    ReplyDelete
    Replies
    1. Hello,

      Thanks for reading my blog, can you please share your code here if possible.

      OR else just check any variable you have provided in this line of code is defined or not.

      Regards,
      Sandeep S S

      Delete
    2. Hi Mary,

      I found a issue in your script,see at the line of code

      def tagCount = reqholder("count(//*:"+reqTagName")")

      in this code "+" sign is missing at the end of "reqTagName" variable.

      Correct the code as below and run the script.

      def tagCount = reqholder("count(//*:"+reqTagName+")")

      Let me if its working fine or not.


      Regards,
      Sandeep S S

      Delete
  11. Hi Sunny,

    I have a REST request as url then how to make it data driven.
    Could you please provide me groovy scripting of Data DrivenFramework - SOAPUI Free version with REST Protocol.


    Because this above code will not work for REST service from URI.

    Please help me out.

    ReplyDelete
    Replies
    1. Hello Brijendra,

      Thanks for your time in reading my blog,

      I am not sure the below solution will work or not as i have not worked on REST services, but you can give a try.

      Create REST project as usual in SOAPUI OS.

      1. Create Test Suite and Test Case along with REST Request at Test Step
      2. Add Groovy Script to Test Step
      3. Click on REST request, at bottom you can see properties tab as "REST TestRequest Properties" and "Custom Properties". Just click on "Custom Properties" here you can see all your target or test parameters along with values you provided.
      4. Now in groovy script step read data from excel or other source.
      5. Now pass your values from excel to your test/target parameters by using below line of code
      "testRunner.testCase.getTestStepByName("Sqlrest - Request 1").properties['Test'].setValue("71184")"
      6. RUN the REST request "testRunner.runTestStepByName("Sqlrest - Request 1") "
      7. Keep above lines of code inside loop, so script can iterate.

      Hope this will helps you


      Regards,
      Sandeep S S

      Delete
  12. Hi,
    This really works. Thank you. I would also like to know how the data mapping from excel is done to the tag names respectively. If its tag name and col name match ,kindly let me know how do you handle when there are same or repetitive child tag name for several parent nodes.

    ReplyDelete
    Replies
    1. Hi Master,

      Thanks for your time to read my blog.

      please find my answer below for you query.
      1. how the data mapping from excel is done to the tag names respectively,If its tag name and col name match ,kindly let me know

      Sandeep :- Here, what we take is excel column header name is same as "TagName",because we are searching and couting tagname in request xml "def tagCount = reqholder("count(//*:"+reqTagName+")")". This will help us knowing tag is present or not.


      2. how do you handle when there are same or repetitive child tag name for several parent nodes.

      Sandeep :- Irrespective of parent and child name script will search for tagname in the xml.
      For same tagname we need to give suffix as "_0","_1" like that and in script remove these suffix values. If tagnames are same by default tag count of 1st tag is "0" zero.


      Regards,
      Sandeep S S

      Delete
    2. Hi Sunny,

      Your blog is very useful.. I am beginner for SOAPUI and groovy.

      I tried with above example and it is working fine but I have multiple child tags with same name.

      Please help me to handle this.

      Delete
    3. Hello,

      Thanks fr reading my blog, glad to hear its working fine for you.

      To work with Child tags with same name, follow the steps below.

      Sample Request









      Take count of tags from below line of code,
      "sTagCount = reqHolder["count(//*:"+reqFieldName+")"]"

      now loop through it and and assign values.


      Hope this clarifies your issue.


      Regards,
      Sandeep S S

      Delete
  13. Hi Sunny,

    It is working now. Thank you very much.

    Mary :)

    ReplyDelete
    Replies
    1. Happy To Hear its work for you


      Regards,
      Sandeep S S

      Delete
  14. TC Name Receipt Amount CustRef UserName Password
    1 123456 1000 761 aaa bbb
    2 789012 2000 762 ccc ddd
    3 1000 3000 763 ddd fff
    4 111111 4000 764 eee ggg

    ReplyDelete
    Replies
    1. Hello,

      String reqTagName = sheet.getCell(col,row).getContents()

      Just check what value is coming this variable "reqTagName"
      if values are coming instead column header then correct the row or col number

      Regards,
      Sandeep S S

      Delete
  15. Hi Sunny,
    Do we need to download any jxl file separately?

    ReplyDelete
    Replies
    1. Hello,

      Thanks for your time for reading my blog,

      Yes you need to download the jxl jar file and place it in the soapui folder under "bin\ext\" folder and restart the SoapUI.

      Regards,
      Sandeep S S

      Delete
  16. Can you pls share any qtp link if you have available

    ReplyDelete
  17. Hi

    I am getting the following error when i executed your code for reading the excel data


    org.codehaus.groovy.control.MultipleCompilationErrorsException: startup failed: Script9.groovy: 5: unable to resolve class jxl.read.biff.BiffException @ line 5, column 1. import jxl.read.biff.BiffException; ^ org.codehaus.groovy.syntax.SyntaxException: unable to resolve class jxl.read.biff.BiffException @ line 5, column 1. at org.codehaus.groovy.ast.ClassCodeVisitorSupport.addError(ClassCodeVisitorSupport.java:146) at

    ReplyDelete
    Replies
    1. Hello,

      Thanks for your time for reading my blog,

      Yes you need to download the jxl jar file and place it in the soapui folder under "bin\ext\" folder and restart the SoapUI.

      And try again, if still face same error do let me know.

      Regards,
      Sandeep S S

      Delete
    2. Hello - I created a Groovy program using your example above and I am getting "unable to resolve class java.io.file" among other errors.

      Any help is appreciated, I am not a PC guy, more of a mainframer.

      Thanks!!!!!

      Delete
    3. Hello - I created a Groovy program using your example above and I am getting "unable to resolve class java.io.file" among other errors.

      Any help is appreciated, I am not a PC guy, more of a mainframer.

      Thanks!!!!!

      Delete
    4. Hello SR,

      Can you please share code, so can i have a look.

      Just one small confirmation, have you added imported all packages as mentioned in the script.

      Regards,
      Sandeep S S

      Delete
    5. Hello SR,

      I have received your code and verified, and got to know where exactly issue is.

      can you please change the import statement at line number 2 as "import java.io.file;" to "import java.io.File;"


      make sure import statement is correct as mentioned below.

      import com.eviware.soapui.support.XmlHolder
      import java.io.File;
      import java.io.IOException;
      import jxl.*;
      import jxl.read.biff.BiffException;
      import jxl.write.*;


      Let me know if any issues.


      Regards,
      Sandeep S S

      Delete
    6. Hi Sonny

      I am getting the error too and have added the jxl jar to soapui bin folder and restarted soapui. Is there a specific version of jxl that I need to download?

      Delete
  18. hi sandeephow to do datadriven tests for rest get and post services using soap ui pro.

    i have done it but when i test log i cant see any response files in it.please assist me

    Regards,
    kk

    ReplyDelete
    Replies
    1. Hello,

      Thanks for reading my blog,

      Please follow the steps mentioned in the below post,

      http://lgsofttest.blogspot.no/2013/04/datadriven-testing-from-soapui-using.html

      you need to change only Soap to REST service and rest of the things are same only.

      Delete
  19. This comment has been removed by the author.

    ReplyDelete
  20. This comment has been removed by the author.

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
  21. Hi Sunny,

    I have two datas to be read .where the first data is not getting read and the second one is getting read as 1.1842597E7 for 11842597. Can you please let me know where I went wrong.
    Thank you.

    ReplyDelete
    Replies
    1. Hello,

      Thanks for you time to reading my blog,

      Please change the format of excel cell where your data is and try again.

      Regards,
      Sandeep S S

      Delete
    2. Hi Sunny
      Now receving info:groovy.lang.missingPropertyException:no such property:sheetl for class.
      Can you please tell how to correct this?
      Thank you.

      Delete
    3. Hi Sunny
      Now receving info:groovy.lang.missingPropertyException:no such property:sheetl for class.
      Can you please tell how to correct this?
      Thank you.

      Delete
    4. Hello,

      Make sure sheet name in excel file and in the script both are same.

      Regards,
      Sandeep S S

      Delete
    5. Hi Sunny,
      I am able to read the data now.Thank you! But while trying to write the response getting error "info:groovy.lang.missingPropertyException:no such property:row for class".

      Delete
    6. Hi Sunny,
      I am able to read the data now.Thank you! But while trying to write the response getting error "info:groovy.lang.missingPropertyException:no such property:row for class".

      Delete
    7. Hello,

      Its good to hear that, Can you please check variable "row" is created. Or Create variable before for loop as def row=0

      Regards,
      Sandeep S S

      Delete
    8. Hi,
      Even after creating a variable as u mentioned i am still receving the same error.

      Delete
    9. Hello,

      Please post your script here, if possible.

      Regards,
      Sandeep S S

      Delete
    10. Hi, I cannot post it as it is in my secured network. I can mail you a picture of it. Is that ok?

      Delete
  22. @Anaka,

    In the XL cell, add apostrophe ' ... so type '11842597 in your cell. This will enable correct numerical value. Thats how I do all the time.

    ReplyDelete
    Replies
    1. Thank you so much.. I am able to read the excel now. :)

      Delete
  23. @Sunnyboy,

    Can you please tell me how to read variable values from 1 teststep in another teststep under same testcase. So, I am reading jsonResponse into teststep'Read'. I want to make available into another teststep 'Write' under same testcase. Can this be done? I am trying ${Read:varName}, but I am getting error message:

    Script2.groovy: 35: unexpected char: '#' @ line 35, column 31.

    ReplyDelete
    Replies
    1. Hello,

      Thanks for your time in reading my blog and suggestions.

      You need to use property transfer test step, in this step you can get values from one test step and use it in any of the test step in the same test case.

      Regards,
      Sandeep S S

      Delete
  24. Hi Sunnyboy,

    pls help me

    org.codehaus.groovy.control.MultipleCompilationErrorsException: startup failed: Script11.groovy: 7: unexpected token: Sheet @ line 7, column 1. Sheet sheet1 = workbook.getSheet("DataDriven"); ^ org.codehaus.groovy.syntax.SyntaxException: unexpected token: Sheet @ line 7, column 1. at org.codehaus.groovy.antlr.AntlrParserPlugin.transformCSTIntoAST(AntlrParserPlugin.java:140) at org.codehaus.groovy.antlr.AntlrParserPlugin.parseCST(AntlrParserPlugin.java:108) at org.codehaus.groovy.control.SourceUnit.parse(SourceUnit.java:236) at org.codehaus.groovy.control.CompilationUnit$1.call(CompilationUnit.java:162) at org.codehaus.groovy.control.CompilationUnit.applyToSourceUnits(CompilationUnit.java:912) at org.codehaus.groovy.control.CompilationUnit.doPhaseOperation(CompilationUnit.java:574) at org.codehaus.groovy.control.CompilationUnit.processPhaseOperations(CompilationUnit.java:550) at org.codehaus.groovy.control.CompilationUnit.compile(CompilationUnit.java:527) at groovy.lang.GroovyClassLoader.doParseClass(GroovyClassLoader.java:279) at groovy.lang.GroovyClassLoader.parseClass(GroovyClassLoader.java:258) at groovy.lang.GroovyShell.parseClass(GroovyShell.java:613) at groovy.lang.GroovyShell.parse(GroovyShell.java:625) at groovy.lang.GroovyShell.parse(GroovyShell.java:652) at groovy.lang.GroovyShell.parse(GroovyShell.java:643) at com.eviware.soapui.support.scripting.groovy.SoapUIGroovyScriptEngine.compile(SoapUIGroovyScriptEngine.java:138) at com.eviware.soapui.support.scripting.groovy.SoapUIGroovyScriptEngine.run(SoapUIGroovyScriptEngine.java:89) at com.eviware.soapui.impl.wsdl.teststeps.WsdlGroovyScriptTestStep.run(WsdlGroovyScriptTestStep.java:141) at com.eviware.soapui.impl.wsdl.panels.teststeps.GroovyScriptStepDesktopPanel$RunAction$1.run(GroovyScriptStepDesktopPanel.java:250) at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) at java.lang.Thread.run(Unknown Source) Caused by: Script11.groovy:7:1: unexpected token: Sheet at org.codehaus.groovy.antlr.parser.GroovyRecognizer.argList(GroovyRecognizer.java:7001) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.methodCallArgs(GroovyRecognizer.java:11910) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.pathElement(GroovyRecognizer.java:11491) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.pathExpression(GroovyRecognizer.java:11621) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.postfixExpression(GroovyRecognizer.java:13332) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.unaryExpressionNotPlusMinus(GroovyRecognizer.java:13301) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.powerExpressionNotPlusMinus(GroovyRecognizer.java:13005) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.multiplicativeExpression(GroovyRecognizer.java:12937) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.additiveExpression(GroovyRecognizer.java:12607) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.shiftExpression(GroovyRecognizer.java:9824) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.relationalExpression(GroovyRecognizer.java:12512) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.equalityExpression(GroovyRecognizer.java:12436) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.regexExpression(GroovyRecognizer.java:12384) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.andExpression(GroovyRecognizer.java:12352) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.exclusiveOrExpression(GroovyRecognizer.java:12320) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.inclusiveOrExpression(GroovyRecognizer.java:12288) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.logicalAndExpression(GroovyRecognizer.java:12256) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.logicalOrExpression(GroovyRecognizer.java:12224) at

    ReplyDelete
  25. My code:

    package org.codehaus.groovy.antlr.parser;
    import java.io.*;
    import jxl.*;
    import org.codehaus.groovy.antlr.parser.GroovyRecognizer;

    Workbook workbook = Workbook.getWorkbook(new File("D:\\SOAPUI\\ConversionRate.xlsx")
    Sheet sheet1 = workbook.getSheet("DataDriven");

    row = sheet1.getRow();
    col = sheet1.getColumns();

    log.info "Row Count =" +row
    log.info "Column Count =" +col

    for(i=1;i<row;i++)
    {
    for(j=0;;j<col;j++)
    {
    def celsius = testRunner.tetCase.getStepByName("Properties")
    cell var = sheet1.getCell(i,j)
    log.info var.getContents()
    }
    }

    ReplyDelete
    Replies
    1. Hello,

      Thanks for reading my blog.

      Please do not use ".xlsx" for any excel file for jxl jar file.

      While creating excel save excel file as ".xls" by selecting "Save as type" as "Excel 97-2003 Workbook (*.xls)".

      try above and let me know if any issues.


      Regards,
      Sandeep S S

      Delete
  26. tried with .xls format but facing the same issue

    ReplyDelete
  27. Hi Sunny,

    Am getting this error logs for this 5 lines of code..

    package org.codehaus.groovy.antlr.parser;
    import java.io.*;
    import jxl.*;
    import org.codehaus.groovy.antlr.parser.GroovyRecognizer;
    import com.eviware.soapui.support.XmlHolder

    Workbook workbook = Workbook.getWorkbook(new File("D:\\SOAPUI\\ConversionRate.xls")

    ReplyDelete
  28. Error Logs:
    org.codehaus.groovy.control.MultipleCompilationErrorsException: startup failed: Script15.groovy: 9: unexpected token: @ line 9, column 1.org.codehaus.groovy.syntax.SyntaxException: unexpected token: @ line 9, column 1. at org.codehaus.groovy.antlr.AntlrParserPlugin.transformCSTIntoAST(AntlrParserPlugin.java:140) at org.codehaus.groovy.antlr.AntlrParserPlugin.parseCST(AntlrParserPlugin.java:108) at org.codehaus.groovy.control.SourceUnit.parse(SourceUnit.java:236) at org.codehaus.groovy.control.CompilationUnit$1.call(CompilationUnit.java:162) at org.codehaus.groovy.control.CompilationUnit.applyToSourceUnits(CompilationUnit.java:912) at org.codehaus.groovy.control.CompilationUnit.doPhaseOperation(CompilationUnit.java:574) at org.codehaus.groovy.control.CompilationUnit.processPhaseOperations(CompilationUnit.java:550) at org.codehaus.groovy.control.CompilationUnit.compile(CompilationUnit.java:527) at groovy.lang.GroovyClassLoader.doParseClass(GroovyClassLoader.java:279) at groovy.lang.GroovyClassLoader.parseClass(GroovyClassLoader.java:258) at groovy.lang.GroovyShell.parseClass(GroovyShell.java:613) at groovy.lang.GroovyShell.parse(GroovyShell.java:625) at groovy.lang.GroovyShell.parse(GroovyShell.java:652) at groovy.lang.GroovyShell.parse(GroovyShell.java:643) at com.eviware.soapui.support.scripting.groovy.SoapUIGroovyScriptEngine.compile(SoapUIGroovyScriptEngine.java:138) at com.eviware.soapui.support.scripting.groovy.SoapUIGroovyScriptEngine.run(SoapUIGroovyScriptEngine.java:89) at com.eviware.soapui.impl.wsdl.teststeps.WsdlGroovyScriptTestStep.run(WsdlGroovyScriptTestStep.java:141) at com.eviware.soapui.impl.wsdl.panels.teststeps.GroovyScriptStepDesktopPanel$RunAction$1.run(GroovyScriptStepDesktopPanel.java:250) at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) at java.lang.Thread.run(Unknown Source) Caused by: Script15.groovy:9:1: unexpected token: at org.codehaus.groovy.antlr.parser.GroovyRecognizer.argList(GroovyRecognizer.java:7001) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.methodCallArgs(GroovyRecognizer.java:11910) at org.codehaus.groovy.antlr.AntlrParserPlugin.transformCSTIntoAST(AntlrParserPlugin.java:131) ... 20 more 1 error

    ReplyDelete
  29. pl help me..

    placed jxl-2.6.9 in bin/ext too..

    ReplyDelete
    Replies
    1. Hello,

      Can you please provide your code/script here, which you have written.

      Regards,
      Sandeep S S

      Delete
    2. package org.codehaus.groovy.antlr.parser;
      import java.io.*;
      import jxl.*;
      import org.codehaus.groovy.antlr.parser.GroovyRecognizer;

      Workbook workbook = Workbook.getWorkbook(new File("D:\\SOAPUI\\ConversionRate.xlsx")
      Sheet sheet1 = workbook.getSheet("DataDriven");

      row = sheet1.getRow();
      col = sheet1.getColumns();

      log.info "Row Count =" +row
      log.info "Column Count =" +col

      for(i=1;i<row;i++)
      {
      for(j=0;;j<col;j++)
      {
      def celsius = testRunner.tetCase.getStepByName("Properties")
      cell var = sheet1.getCell(i,j)
      log.info var.getContents()
      }
      }

      Delete
    3. Hello,


      Please do not use ".xlsx" for any excel file for jxl jar file.

      While creating excel save excel file as ".xls" by selecting "Save as type" as "Excel 97-2003 Workbook (*.xls)".

      try above and let me know if any issues.


      Regards,
      Sandeep S S

      Delete
  30. Thanks for your reply Sandeep..

    As per your comments, placed .xls workbook and escaped from all the above errors.

    Now am getting the below errors

    groovy.lang.MissingMethodException: No signature of method: jxl.read.biff.SheetImpl.getRow() is applicable for argument types: () values: [] Possible solutions: getRow(int), getRows(), getPLS(), getAt(java.lang.String), getName(), getCell(java.lang.String) error at line: 8

    ReplyDelete
    Replies
    1. Hello,

      Can you repost your script if possible. or else make sure you have defined all the variables you used.

      Regards,
      Sandeep S S

      Delete
    2. Hi Sunny,

      I got the issue now..

      Delete
    3. What was the issue here and how was it resolved? I am getting the same error.

      Delete
    4. Hello,

      Please use .xls file instead of .xlsx.
      it will solve your issue.

      Regards,
      Sandeep S S

      Delete
  31. Now tried with your code..

    import com.eviware.soapui.support.XmlHolder
    import java.io.File;
    import java.io.IOException;
    import jxl.read.biff.BiffException;
    import jxl.*;

    log.info ("Testing Started")

    def reqOpName = "ConversionRate"
    Workbook workbook = Workbook.getWorkbook(new File("D:\\SOAPUI\\ConversionRate.xls"))
    Sheet sheet1 = workbook.getSheet("DataDriven");

    def groovyUtils = new com.eviware.soapui.support.GroovyUtils(context)
    def reqholder = groovyUtils.getXmlHolder(reqOpName+ "#Request")
    try
    {
    row = sheet1.getRows();
    col = sheet1.getColumns();

    for(Row in 1..row-1)
    {
    for(Col in 2..col-1)
    {
    String reqTagName = sheet1.getCell(Col,0).getContents()
    def tagCount = reqholder("count(//*:"+reqTagName+")")

    if(tagCount!=0)
    {
    String reqTagValue = sheet1.getCell(Col,Row).getcontents()
    reqholder.setNodeValue("//*:"+reTagName, reqTagValue)
    reqholder.updateProperty()
    }
    }
    testRunner.runTestStepByName(reqOpName)
    }
    catch (Exception e) {log.info(e) }
    }

    ReplyDelete
  32. Am getting the below error logs,


    org.codehaus.groovy.control.MultipleCompilationErrorsException: startup failed: Script9.groovy: 36: unexpected token: catch @ line 36, column 1. catch (Exception e) {log.info(e) } ^ org.codehaus.groovy.syntax.SyntaxException: unexpected token: catch @ line 36, column 1. at org.codehaus.groovy.antlr.AntlrParserPlugin.transformCSTIntoAST(AntlrParserPlugin.java:140) at org.codehaus.groovy.antlr.AntlrParserPlugin.parseCST(AntlrParserPlugin.java:108) at org.codehaus.groovy.control.SourceUnit.parse(SourceUnit.java:236) at org.codehaus.groovy.control.CompilationUnit$1.call(CompilationUnit.java:162) at org.codehaus.groovy.control.CompilationUnit.applyToSourceUnits(CompilationUnit.java:912) at org.codehaus.groovy.control.CompilationUnit.doPhaseOperation(CompilationUnit.java:574) at org.codehaus.groovy.control.CompilationUnit.processPhaseOperations(CompilationUnit.java:550) at org.codehaus.groovy.control.CompilationUnit.compile(CompilationUnit.java:527) at groovy.lang.GroovyClassLoader.doParseClass(GroovyClassLoader.java:279) at groovy.lang.GroovyClassLoader.parseClass(GroovyClassLoader.java:258) at groovy.lang.GroovyShell.parseClass(GroovyShell.java:613) at groovy.lang.GroovyShell.parse(GroovyShell.java:625) at groovy.lang.GroovyShell.parse(GroovyShell.java:652) at groovy.lang.GroovyShell.parse(GroovyShell.java:643) at com.eviware.soapui.support.scripting.groovy.SoapUIGroovyScriptEngine.compile(SoapUIGroovyScriptEngine.java:138) at com.eviware.soapui.support.scripting.groovy.SoapUIGroovyScriptEngine.run(SoapUIGroovyScriptEngine.java:89) at com.eviware.soapui.impl.wsdl.teststeps.WsdlGroovyScriptTestStep.run(WsdlGroovyScriptTestStep.java:141) at com.eviware.soapui.impl.wsdl.panels.teststeps.GroovyScriptStepDesktopPanel$RunAction$1.run(GroovyScriptStepDesktopPanel.java:250) at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) at java.lang.Thread.run(Unknown Source) Caused by: Script9.groovy:36:1: unexpected token: catch at org.codehaus.groovy.antlr.parser.GroovyRecognizer.blockBody(GroovyRecognizer.java:1594) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.openBlock(GroovyRecognizer.java:8315) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.compoundStatement(GroovyRecognizer.java:7518) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.tryBlock(GroovyRecognizer.java:9316) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.statement(GroovyRecognizer.java:1130) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.compilationUnit(GroovyRecognizer.java:757) at org.codehaus.groovy.antlr.AntlrParserPlugin.transformCSTIntoAST(AntlrParserPlugin.java:131) ... 20 more 1 error


    Pls help me

    ReplyDelete
    Replies
    1. corrected this line.

      reqholder.setNodeValue("//*:"+regTagName, reqTagValue)

      Delete
    2. Hello,

      Using the above script: Am still getting an exception as below:

      org.codehaus.groovy.control.MultipleCompilationErrorsException: startup failed: Script48.groovy: 36: unexpected token: catch @ line 36, column 1. catch (Exception e) {log.info(e) } ^ org.codehaus.groovy.syntax.SyntaxException: unexpected token: catch @ line 36, column 1. at org.codehaus.groovy.antlr.AntlrParserPlugin.transformCSTIntoAST(AntlrParserPlugin.java:140) at org.codehaus.groovy.antlr.AntlrParserPlugin.parseCST(AntlrParserPlugin.java:108) at org.codehaus.groovy.control.SourceUnit.parse(SourceUnit.java:236) at org.codehaus.groovy.control.CompilationUnit$1.call(CompilationUnit.java:162) at org.codehaus.groovy.control.CompilationUnit.applyToSourceUnits(CompilationUnit.java:912) at org.codehaus.groovy.control.CompilationUnit.doPhaseOperation(CompilationUnit.java:574) at org.codehaus.groovy.control.CompilationUnit.processPhaseOperations(CompilationUnit.java:550) at org.codehaus.groovy.control.CompilationUnit.compile(CompilationUnit.java:527) at groovy.lang.GroovyClassLoader.doParseClass(GroovyClassLoader.java:279) at groovy.lang.GroovyClassLoader.parseClass(GroovyClassLoader.java:258) at groovy.lang.GroovyShell.parseClass(GroovyShell.java:613) at groovy.lang.GroovyShell.parse(GroovyShell.java:625) at groovy.lang.GroovyShell.parse(GroovyShell.java:652) at groovy.lang.GroovyShell.parse(GroovyShell.java:643) at com.eviware.soapui.support.scripting.groovy.SoapUIGroovyScriptEngine.compile(SoapUIGroovyScriptEngine.java:138) at com.eviware.soapui.support.scripting.groovy.SoapUIGroovyScriptEngine.run(SoapUIGroovyScriptEngine.java:89) at com.eviware.soapui.impl.wsdl.teststeps.WsdlGroovyScriptTestStep.run(WsdlGroovyScriptTestStep.java:141) at com.eviware.soapui.impl.wsdl.panels.teststeps.GroovyScriptStepDesktopPanel$RunAction$1.run(GroovyScriptStepDesktopPanel.java:250) at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) at java.lang.Thread.run(Unknown Source) Caused by: Script48.groovy:36:1: unexpected token: catch at org.codehaus.groovy.antlr.parser.GroovyRecognizer.blockBody(GroovyRecognizer.java:1594) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.openBlock(GroovyRecognizer.java:8315) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.compoundStatement(GroovyRecognizer.java:7518) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.tryBlock(GroovyRecognizer.java:9316) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.statement(GroovyRecognizer.java:1130) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.compilationUnit(GroovyRecognizer.java:757) at org.codehaus.groovy.antlr.AntlrParserPlugin.transformCSTIntoAST(AntlrParserPlugin.java:131) ... 20 more 1 error


      Pls help..

      Delete
    3. Here is the script used:

      import com.eviware.soapui.support.XmlHolder
      import java.io.File;
      import java.io.IOException;
      import jxl.read.biff.BiffException;
      import jxl.*;

      log.info ("Testing Started")

      def reqOpName = "GET - Sample Request - JSON"
      Workbook workbook = Workbook.getWorkbook(new File("C:/Users/E002892/Downloads/countryInfo.xls"))
      Sheet sheet1 = workbook.getSheet("Names");

      def groovyUtils = new com.eviware.soapui.support.GroovyUtils(context)
      def reqholder = groovyUtils.getXmlHolder(reqOpName+ "#Request")
      try
      {
      row = sheet1.getRows();
      col = sheet1.getColumns();

      for(Row in 1..row-1)
      {
      for(Col in 2..col-1)
      {
      String reqTagName = sheet1.getCell(Col,0).getContents()
      def tagCount = reqholder("count(//*:"+reqTagName+")")

      if(tagCount!=0)
      {
      String reqTagValue = sheet1.getCell(Col,Row).getcontents()
      reqholder.setNodeValue("//*:"+reqTagName, reqTagValue)
      reqholder.updateProperty()
      }
      }
      testRunner.runTestStepByName(reqOpName)
      }
      catch (Exception e) {log.info(e) }
      }

      Delete
    4. Hello Ravi,

      Thanks for your interest and time to reading my blog,

      I just had look at the script which you have written seems good, but i found one minor misplacement of closing curly braces. Remove "}" after catch (Exception e) {log.info(e) } and try again.

      Regards,
      Sandeep S S

      Delete
  33. Hello Sunny,

    Thanks for your Response !!!


    Now, I'm getting a strange exception stating like "XmlException: Unexpected end of file after null error at line:14"

    I tried with enabling and disabling the Http Mock log settings in the global properties as well, Still am facing this issue.

    ReplyDelete
    Replies
    1. Hello Ravi,

      Please confirm is your request is REST (JSON Based) request or Soap(XML based) request.

      The above groovy script is for only Soap request and not for JSON request.

      Regards,
      Sandeep S S

      Delete
    2. Hello Sunny,

      My Request is based on JSON Request. Could you pls. share me the Groovy Script for Json request.

      Delete
    3. Hello,

      Sure, will do create new post on Groovy Script for JSON requests.

      Regards,
      Sandeep S S

      Delete
  34. Hi Sunny,

    Could you please check the below code and update ?
    I am trying to parameterize label3 and label4 for multiple output values. But it prints only for the first value in excel.

    Label label3 = new Label(i, 0, PolicyNum);
    Label label4 = new Label(i, 1, Effdt);

    Sheet2.addCell(label3)
    Sheet2.addCell(label4)

    ReplyDelete
    Replies
    1. Hello,

      Please make sure you are passing same datatype values as mentioned in below syntax,

      Syntax:- Label label3 = new Label(Column, Row, String);

      Label label3 = new Label(int, int, String);
      Sheet2.addCell(label3)

      Delete
    2. Thanks Sunny for the response.

      In my earlier code, I am passing the values to "i" from the loop.. I guess, as per the syntax you have mentioned, both column and row has to an integer....

      Label label3 = new Label(i, 0, PolicyNum);
      Label label4 = new Label(i, 1, Effdt);

      Is there any other way to pass column and row values during run time ?


      Thanks,
      Bharath

      Delete
    3. Hello,

      If want to update row by row in excel, then we need to pass value from loop itself. We can also pass hardcore value but it is not good practice.

      Regards,
      Sandeep S S

      Delete
  35. Could you help with the groovy script for json requests ?

    ReplyDelete
  36. when i submit a REST CALL request
    error response: org.apache.xmlbeans.XmlException

    could any one suggest me ?
    </body does not close tag..

    ReplyDelete
  37. Hi Sunny,

    I tried your code to read data from excel. Getting below errors.pls help .

    org.codehaus.groovy.control.MultipleCompilationErrorsException: startup failed: Script2.groovy: 65: unexpected token: } @ line 65, column 1. }catch(Exception e){log.info(e)} ^ org.codehaus.groovy.syntax.SyntaxException: unexpected token: } @ line 65, column 1. at org.codehaus.groovy.antlr.AntlrParserPlugin.transformCSTIntoAST(AntlrParserPlugin.java:140) at org.codehaus.groovy.antlr.AntlrParserPlugin.parseCST(AntlrParserPlugin.java:108) at org.codehaus.groovy.control.SourceUnit.parse(SourceUnit.java:236) at org.codehaus.groovy.control.CompilationUnit$1.call(CompilationUnit.java:162) at org.codehaus.groovy.control.CompilationUnit.applyToSourceUnits(CompilationUnit.java:912) at org.codehaus.groovy.control.CompilationUnit.doPhaseOperation(CompilationUnit.java:574) at org.codehaus.groovy.control.CompilationUnit.processPhaseOperations(CompilationUnit.java:550) at org.codehaus.groovy.control.CompilationUnit.compile(CompilationUnit.java:527) at groovy.lang.GroovyClassLoader.doParseClass(GroovyClassLoader.java:279) at groovy.lang.GroovyClassLoader.parseClass(GroovyClassLoader.java:258) at groovy.lang.GroovyShell.parseClass(GroovyShell.java:613) at groovy.lang.GroovyShell.parse(GroovyShell.java:625) at groovy.lang.GroovyShell.parse(GroovyShell.java:652) at groovy.lang.GroovyShell.parse(GroovyShell.java:643) at com.eviware.soapui.support.scripting.groovy.SoapUIGroovyScriptEngine.compile(SoapUIGroovyScriptEngine.java:138) at com.eviware.soapui.support.scripting.groovy.SoapUIGroovyScriptEngine.run(SoapUIGroovyScriptEngine.java:89) at com.eviware.soapui.impl.wsdl.teststeps.WsdlGroovyScriptTestStep.run(WsdlGroovyScriptTestStep.java:141) at com.eviware.soapui.impl.wsdl.panels.teststeps.GroovyScriptStepDesktopPanel$RunAction$1.run(GroovyScriptStepDesktopPanel.java:250) at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) at java.lang.Thread.run(Unknown Source) Caused by: Script2.groovy:65:1: unexpected token: } at org.codehaus.groovy.antlr.parser.GroovyRecognizer.compilationUnit(GroovyRecognizer.java:769) at org.codehaus.groovy.antlr.AntlrParserPlugin.transformCSTIntoAST(AntlrParserPlugin.java:131) ... 20 more 1 error

    ReplyDelete
    Replies
    1. Hello,

      Thanks for reading my blog,

      i have gone through your code and looks fine. In some places curly braces are missing i have added and modified your code.. try with this, if any issues let me know.





      import com.eviware.soapui.support.XmlHolder
      import java.io.File;
      import java.io.IOException;
      import jxl.*;
      import jxl.read.biff.BiffException;
      import jxl.write.*;

      // DECLARE THE VARIABLES
      log.info("Service testing satrted")
      def reqOperationName="ProcessPayment";
      def inputDataFileName="E:/ProcessRequest.xls"
      def inputDataSheetName="ProcessRequest";

      //def myTestCase = context.testCase //myTestCase contains the test case

      //def counter,next,previous,size //Variables used to handle the loop and to move inside the file

      Workbook workbook = Workbook.getWorkbook(new File(inputDataFileName)) //file containing the data
      WritableWorkbook copy= Workbook.createWorkbook(new File(inputDataFileName),Workbook);
      WritableSheet sheet1=copy.getSheet(inputDataSheetName);


      def groovyUtils=new com.eviware.soapui.support.GroovyUtils(context)
      def reqholder=groovyUtils.getXmlHolder(reqOperationName+"#Request")
      try
      {
      rowcount=sheet1.getRows();
      colcount=sheet1.getColumns();

      for(Row in 1..rowcount-1){
      for (Col in 2..colcount-1){
      String reqTagName = sheet1.getCell(Col,0).getContents()
      def TagCount = reqholder["count(//*:"+reqTagName+")"]
      if(TagCount!=0)

      String reqTagName = sheet1.getCell(Col,Row).getcontents()
      reqholder.setNodeValue("//*:"+reqTagName,reqTagValue)
      reqholder.updateProperty()
      }

      //To run test request
      testRunner.runTestStepByName(reqOperationName)


      //Read response Xml
      def resholder = groovyUtils.getXmlHolder(reqOperationName+"#Response")
      resTagValue1=resholder.getNodeValues("//*:clientRequestId")
      resTagValue2=resholder.getNodeValues("//*:Amount")
      resTagValue3=resholder.getNodeValues("//*:cardComments1")
      resTagValue4=resholder.getNodeValues("//*:State")
      resTagValue5=resholder.getNodeValues("//*:Status")
      //Write response value in xls
      Label resValue1=new Label (4,Row,resTagValue1);
      sheet1.addCell(resValue1);
      Label resValue2=new Label (5,Row,resTagValue2);
      sheet1.addCell(resValue2);
      Label resValue3=new Label (6,Row,resTagValue3);
      sheet1.addCell(resValue3);
      Label resValue4=new Label (7,Row,resTagValue4);
      sheet1.addCell(resValue4);
      Label resValue5=new Label (8,Row,resTagValue5);
      sheet1.addCell(resValue5);
      }//Row loop end here
      }catch(Exception e){log.info(e)}
      finally{
      copy.write();
      copy.close();
      workbook.close();
      }
      log.info("service testing finished")

      Delete
    2. Hi Sunny,

      I have tried your code and got below issue, plz help me out.


      groovy.lang.MissingMethodException: No signature of method: com.eviware.soapui.support.GroovyUtils.getXm1Holder() is applicable for argument types: (java.lang.String) values: [SendSMSTolndia#Request] Possible solutions: getXmlHolder(java.lang.String) error at line: 15

      Line 15 is : def reqholder = groovyUtils.getXm1Holder(reqOperationName+"#Request")

      Regards
      Narendra.Gaaja

      Delete
  38. Hi Sunny,

    I have tried your code and got below issue, plz help me out.


    groovy.lang.MissingMethodException: No signature of method: com.eviware.soapui.support.GroovyUtils.getXm1Holder() is applicable for argument types: (java.lang.String) values: [SendSMSTolndia#Request] Possible solutions: getXmlHolder(java.lang.String) error at line: 15

    Line 15 is : def reqholder = groovyUtils.getXm1Holder(reqOperationName+"#Request")

    Regards
    Narendra.Gaaja

    ReplyDelete
  39. import com.eviware.soapui.support.XmlHolder
    import java.io.File;
    import java.io.IOException;
    import jxl.*;
    import jxl.read.biff.BiffException;
    import jxl.write.*;
    log.info("Service Testing Started")
    def reqOperationName = "SendSMSTolndia";
    def inputDataFileName = "C:/Users/NareN/Desktop/123.xls"
    def inputDataSheetName = "Sheet1"
    Workbook workbook = Workbook.getWorkbook(new File(inputDataFileName));
    WritableWorkbook copy = Workbook.createWorkbook(new File(inputDataFileName),workbook);
    WritableSheet sheetl = copy.getSheet(inputDataSheetName);
    def groovyUtils = new com.eviware.soapui.support.GroovyUtils(context)
    def reqholder = groovyUtils.getXm1Holder(reqOperationName+"#Request")
    try{
    rowcount = sheetl.getRows();
    colcount = sheetl.getColumns();
    for(Row in 1..rowcount-1){
    for(Col in 2..colcount-1){
    String reqTagName = sheetl.getCell(Co1,0).getContents()
    def TagCount = regholder("count://':"+reqTagName+")")
    if(Tagcount!=0){
    String reqTagValue = sheetl.getCell(Col,Row).getContents()
    regholder.setNodeValue("//': "+reqTagName, reqTagValue) regholder.updateProperty()
    }
    }
    // To Test Request
    testRunner.runTestStepHyName(reqOperationName)
    // Read Response XML
    def resholder = groovyUtils.getXmlHolder(reqOperationName+";Response")
    resTagValuel = resholder.getNodeValues("//':FromimailAddress")
    resTagValue2 = resholder.getNodeValues("//*:MobileNumber")
    resTagValue3 = resholder.getNodeValues("//*:Provider")
    resTagValue4 = resholder.getNodeValues("//*:State")
    resTagValue5 = resholder.getNodeValues("//*:Status")
    //Write Response value to Excel Sheet
    Label resValuel = new Label(4,Row,resTagValuel); sheetl.addCell(resValuel);
    Label resValue2 = new Label(5,Row,resTagValue2); sheetl.addCell(resValue2);
    Label resValue3 = new Label(6,Row,resTagValue3); sheetl.addCell(resValue3);
    Label resValue4 = new Label(7,RoweresTagValue4); sheetl.addCell(resValue4);
    Label resValue5 = new Label(8,Row,resTagValue5); sheetl.addCell(resValue5);
    }
    // Row loop Ends Here
    }catch (Exception e)
    {
    log.info(e)
    }
    finally{
    copy.write();
    copy.close();
    workbook.close();
    }
    log.info("Service Testing Finished")

    ReplyDelete
    Replies
    1. Hello,

      Thanks for reading my blog,

      Please change the below line of code at Line 15
      from :-
      def groovyUtils = new com.eviware.soapui.support.GroovyUtils(context)
      def reqholder = groovyUtils.getXm1Holder(reqOperationName+"#Request")

      To :-

      def groovyUtils = new com.eviware.soapui.support.GroovyUtils(context)
      def reqholder = groovyUtils.getXmlHolder(reqOperationName+"#Request")

      and try again.

      let me know if any issues.

      Regards,
      Sunny

      Delete
    2. Hi Sunny,

      Thanks for the valuable reply,

      I have tried the same but got the below issue.

      "org.apache.xmlbeans.xmlException:error: Unexpected element:CDATA error at line 15"

      Please help.

      Regrads,
      Narendra.Gaaja

      Delete






  40. Plz find below updated script
    import com.eviware.soapui.support.XmlHolder
    import java.io.File;
    import java.io.IOException;
    import jxl.*;
    import jxl.read.biff.BiffException;
    import jxl.write.*;
    log.info("Service Testing Started")
    def reqOperationName = "SendSMSTolndia";
    def inputDataFileName = "C:/Users/NareN/Desktop/123.xls"
    def inputDataSheetName = "Sheet1"
    Workbook workbook = Workbook.getWorkbook(new File(inputDataFileName));
    WritableWorkbook copy = Workbook.createWorkbook(new File(inputDataFileName),workbook);
    WritableSheet sheetl = copy.getSheet(inputDataSheetName);
    def groovyUtils = new com.eviware.soapui.support.GroovyUtils(context)
    def reqholder = groovyUtils.getXmlHolder(reqOperationName+"#Request")
    try{
    rowcount = sheetl.getRows();
    colcount = sheetl.getColumns();
    for(Row in 1..rowcount-1){
    for(Col in 2..colcount-1){
    String reqTagName = sheetl.getCell(Co1,0).getContents()
    def TagCount = resholder("count://':"+reqTagName+")")
    if(Tagcount!=0){
    String reqTagValue = sheetl.getCell(Col,Row).getContents()
    resholder.setNodeValue("//': "+reqTagName, reqTagValue)
    resholder.updateProperty()
    }
    }
    // To Test Request
    testRunner.runTestStepByName(reqOperationName)
    // Read Response XML
    def resholder = groovyUtils.getXmlHolder(messageExchange.responseContent)
    resTagValuel = resholder.getNodeValues("//':FromimailAddress")
    resTagValue2 = resholder.getNodeValues("//*:MobileNumber")
    resTagValue3 = resholder.getNodeValues("//*:Provider")
    resTagValue4 = resholder.getNodeValues("//*:State")
    resTagValue5 = resholder.getNodeValues("//*:Status")
    //Write Response value to Excel Sheet
    Label resValuel = new Label(4,Row,resTagValuel); sheetl.addCell(resValuel);
    Label resValue2 = new Label(5,Row,resTagValue2); sheetl.addCell(resValue2);
    Label resValue3 = new Label(6,Row,resTagValue3); sheetl.addCell(resValue3);
    Label resValue4 = new Label(7,RoweresTagValue4); sheetl.addCell(resValue4);
    Label resValue5 = new Label(8,Row,resTagValue5); sheetl.addCell(resValue5);
    }
    // Row loop Ends Here
    }catch (Exception e)
    {
    log.info(e)
    }
    finally{
    copy.write();
    copy.close();
    workbook.close();
    }
    log.info("Service Testing Finished")

    ReplyDelete
  41. Hi sunny,

    do i need to add any jar files to soap directory in order to execute above script

    Plz share the final xls sheet(output) after executing above script.

    Thank you
    Narendra.gaaja

    ReplyDelete
  42. Hi Sunny,

    Could you plz tell me which version of soapui you are using.

    do i need to add any jar files to soap directory in order to execute above script

    Plz share the final xls sheet(output) after executing above script.


    Note: I have added "jxl" jar in soapui directory, bin/ext

    Thank you
    Narendra.gaaja

    ReplyDelete
  43. Hi sunny,

    Below is my code for which I am getting error :

    java.lang.NullPointerException: Cannot invoke method getRows() on null object



    Code:


    /***
    *
    *
    * Read & Write data from Excel file
    *
    *
    *
    *
    *
    *
    *
    *
    */


    import com.eviware.soapui.support.XmlHolder
    import java.io.File;
    import java.io.IOException;
    import jxl.*;
    import jxl.read.biff.BiffException;
    import jxl.write.*;


    log.info ("Get Accounts Testing Started")
    def reqOperationName = "GetAccDetails";
    def inputDataFilename = "C:/Webservices/SoapUI/AccountNumber1.xls"
    def inputDataSheetname = "sheet1"


    Workbook workbook = Workbook.getWorkbook(new File(inputDataFilename));
    WritableWorkbook copy = Workbook.createWorkbook(new File(inputDataFilename) ,workbook);
    WritableSheet sheet1 = copy.getSheet(inputDataFilename);

    def groovyUtils=new com.eviware.soapui.support.GroovyUtils(context)
    def reqholder=groovyUtils.getXmlHolder(reqOperationName+"#Request")
    try
    {
    rowcount=sheet1.getRows();
    colcount=sheet1.getColumns();

    for(Row in 1..rowcount-1){
    for (Col in 2..colcount-1){
    String reqTagName = sheet1.getCell(Col,0).getContents()
    def TagCount = reqholder["count(//*:"+reqTagName+")"]
    if(TagCount!=0)

    String reqTagName1 = sheet1.getCell(Col,Row).getcontents()
    reqholder.setNodeValue("//*:"+reqTagName,reqTagValue)
    reqholder.updateProperty()
    }
    // To Run Test Request
    testRunner.runTestStepByName(reqOperationName)


    // Read Resposne XML
    def resholder = groovyUtlis.getXmlHolder(reqOperationName+"#Resposne")

    resTagValue1 = resholder.getnodeValues("//*:username")
    resTagValue2 = resholder.getnodeValues("//*:firstName")
    resTagValue3 = resholder.getnodeValues("//*:lastName")
    resTagValue4 = resholder.getnodeValues("//*:email")
    resTagValue5 = resholder.getnodeValues("//*:accountType")
    resTagValue6 = resholder.getnodeValues("//*:foxtelAccountId")
    resTagValue7 = resholder.getnodeValues("//*:foxtelAccountStatus")
    resTagValue8 = resholder.getnodeValues("//*:emailOptIn")


    // Write Response to Excel Sheet

    Label resValue1 = new Label (4, Row,resTagValue1);
    sheet1.addCell(resValue1);
    Label resValue2 = new Label (5, Row,resTagValue2);
    sheet1.addCell(resValue2);
    Label resValue3 = new Label (6, Row,resTagValue3);
    sheet1.addCell(resValue3);
    Label resValue4 = new Label (7, Row,resTagValue4);
    sheet1.addCell(resValue4);
    Label resValue5 = new Label (8, Row,resTagValue5);
    sheet1.addCell(resValue5);
    Label resValue6 = new Label (8, Row,resTagValue6);
    sheet1.addCell(resValue6);
    Label resValue7 = new Label (8, Row,resTagValue7);
    sheet1.addCell(resValue7);
    Label resValue8 = new Label (8, Row,resTagValue8);
    sheet1.addCell(resValue8);

    } //Row loop Ends here

    } catch (Exception e) {log.info(e) }
    finally {
    copy.write();
    copy.close();
    workbook.close();
    }
    log.info ("Get Accounts Testing Completed")

    log.info ("Please Verfiy Updated Workbook")








    i have updated C:\Program Files (x86)\SmartBear\SoapUI-5.0.0\bin\ext with jxl jar file

    ReplyDelete
  44. I'm getting Label class exception when i'm writing to the excel file

    Thu Mar 30 12:23:47 IST 2017:INFO:groovy.lang.GroovyRuntimeException: Could not find matching constructor for: jxl.write.Label(java.lang.Integer, java.lang.Integer, [Ljava.lang.String;)

    ReplyDelete
  45. Hi, While using Label function to write response to Excel sheet, i'm seeing "Sun Apr 09 23:25:01 EDT 2017:INFO:groovy.lang.GroovyRuntimeException: Could not find matching constructor for: jxl.write.Label(java.lang.Integer, java.lang.Integer, [Ljava.lang.String;)".

    Can you please let me know what could be the issue?

    Thanks,
    Jay

    ReplyDelete
  46. Hi Sunny,

    Thanks for Explaining DataDriven Framework in detail manner.It really helpful.Could you please give an example of parameterization concept

    ReplyDelete
  47. Hi,

    Can some one help me to delete row data in excl sheet using Groovy script.

    Thanks

    ReplyDelete
    Replies
    1. Hello,

      for your query, i can say like just pass null or empty string so row data will get erase.

      You can use below code.

      Label value1 = new Label (Col, Row,"");
      sheet1.addCell(value1 );



      Regards,
      Sandeep S S

      Delete
  48. Hi Sunny,

    Thanks for the detailed explanation. Could you please help me to handle child tags with same name and pass the values for it.

    Thanks

    ReplyDelete
    Replies
    1. Thanks for your time to read my blog.

      please find my answer below for you query.

      Irrespective of parent and child name script will search for tagname in the xml.
      For same tagname we need to give suffix as "_0","_1" like that and in script remove these suffix values. If tagnames are same by default tag count of 1st tag is "0" zero.


      Regards,
      Sandeep S S

      Delete
  49. Hello,

    For line def TagCount = reqholder["count(//*:"+reqTagName+")"]
    I am getting error as
    java.lang.RuntimeException: net.sf.saxon.trans.XPathException: XPath syntax error at char 11 on line 2 in {\ncount(//*:)}: expected " ", found ")" error at line: 3

    -Yogesh

    ReplyDelete
  50. Hi Sunney,

    import java.io.*
    import jxl.*
    import com.eviware.soapui.support.XmlHolder
    import jxl.write.*;
    import jxl.write.Label
    import java.util.regex.Pattern
    import java.util.regex.PatternSyntaxException
    import groovy.json.JsonSlurper
    import jxl.read.biff.BiffException

    def f = new File("D:\\SoapUI\\RestInput.xls")
    Workbook wb = Workbook.getWorkbook(f)
    WritableWorkbook copy = Workbook.createWorkbook(f,wb)
    WritableSheet ws = copy.getSheet("input")
    //def ws = wb.getSheet("input")
    def r = ws.getRows()
    for(def i =1;i<r;i++)
    {
    Cell c0 = ws.getCell(1,i)
    Cell c1 = ws.getCell(2,i)
    Cell c2 = ws.getCell(3,i)
    Cell c3 = ws.getCell(4,i)
    Cell c4 = ws.getCell(5,i)
    Cell c5 = ws.getCell(6,i)
    Cell c6 = ws.getCell(7,i)


    testRunner.testCase.testSuite.setPropertyValue("project",c0.getContents())
    testRunner.testCase.testSuite.setPropertyValue("Description",c1.getContents())
    testRunner.testCase.testSuite.setPropertyValue("priority",c2.getContents())
    testRunner.testCase.testSuite.setPropertyValue("reporter",c3.getContents())
    testRunner.testCase.testSuite.setPropertyValue("assignee",c4.getContents())
    testRunner.testCase.testSuite.setPropertyValue("issuetype",c5.getContents())
    testRunner.testCase.testSuite.setPropertyValue("Summary",c6.getContents())
    testRunner.runTestStepByName("REST Request")
    responseContent = testRunner.testCase.getTestStepByName("REST Request").getPropertyValue("response")

    //wait(50000)
    Thread.sleep(5000)

    def groovyUtils = new com.eviware.soapui.support.GroovyUtils( context )
    //def holder = groovyUtils.getXmlHolder(messageExchange.responseContent)
    //def responseXML = context.expand('${getRate - Request 1#Response}')
    //def holder = groovyUtils.getPrettyXml()
    def slurper = new JsonSlurper()
    //def response = context.expand( '${RequestName#Response}')
    def jsonRes = slurper.parseText(responseContent)

    log.info jsonRes
    CL = 8

    Label label = new Label(CL,i,jsonRes)
    //jxl.write.Label# (int, int, jxl.write.Label)
    //Label label1= new Label(0, 0, “SITE ID”);
    ws.addCell(label)


    }
    copy.write()
    copy.close()
    wb.close()


    I am getting below error message while using the above code:

    groovy.lang.GroovyRuntimeException: Could not find matching constructor for: jxl.write.Label(java.lang.Integer, java.lang.Integer, java.util.HashMap

    ReplyDelete
    Replies
    1. Please help me to solve this issue

      Delete
    2. I think you get this issue while updating value into xls but value is not in expected format.
      I solved this by adding .toString()
      Eg: resTagValue3 = resholder.getnodeValues("//*:lastName").toString().
      In your case, not sure as you are using slurper

      Delete
  51. Can you tell me why the XML result values are enclosed with brackets? For example, the State in the response is showing as [Not Covered] in the final Excel spreadsheet. Thanks!

    ReplyDelete
  52. I have rest api so how can use your code for data driven

    ReplyDelete
  53. Hi Sunney,

    Have tried your code.. but I am facing "groovy.lang.GroovyRuntimeException: Could not find matching constructor for: jxl.write.Label(java.lang.Integer, java.lang.Integer, [Ljava.lang.String;)"
    Could you please help me to rectify this error.

    ReplyDelete
  54. Thanks for the code

    ReplyDelete
  55. Hello Sunnay
    I had tryed code but its not working getting error as below,
    org.codehaus.groovy.control.MultipleCompilationErrorsException: startup failed: Script4.groovy: 17: unable to resolve class Workbook @ line 17, column 10. Workbook workbook = Workbook.getWorkbook(new File(outPutFileName)); ^ org.codehaus.groovy.syntax.SyntaxException: unable to resolve class Workbook @ line 17, column 10. at

    ReplyDelete
  56. Hi Sunny,

    I have tried using your code and there are no errors in error log, however when i try to execute the script, it's not executing my test case and throwing the following error in the console:

    Wed Nov 29 15:23:46 IST 2017:INFO:Testing Started
    Wed Nov 29 15:23:48 IST 2017:INFO:java.lang.RuntimeException: net.sf.saxon.trans.XPathException: XPath syntax error at char 11 on line 2 in {\ncount(//*:)}:
    expected "", found ")"
    Wed Nov 29 15:23:49 IST 2017:INFO:Testing Over

    In the below code if I replace "*:" with mandatory value then it works well.

    String reqTagName = sheet1.getCell(Col,0).getContents()
    def TagCount = reqholder["count(//*:"+reqTagName+")"]
    if(TagCount!=0){
    String reqTagValue = sheet1.getCell(Col,Row).getContents()
    reqholder.setNodeValue("//*:"+reqTagName, reqTagValue)
    reqholder.updateProperty()

    Also, The below code reads the file but it is not replacing the values from the worksheet(saved worksheet in .xls format) into the request.

    I have attached the code which was developed by you:

    import com.eviware.soapui.support.XmlHolder
    import java.io.File;
    import java.io.IOException;
    import jxl.*;
    import jxl.read.biff.BiffException;
    import jxl.write.*;

    log.info("Testing Started")
    def reqOperationName = "getInsuranceDetails_1_FTC_005";
    def inputDataFileName = "D:/SOAP UI Pro/MPI.xls"
    def inputDataSheetName = "MPI"

    Workbook workbook = Workbook.getWorkbook(new File(inputDataFileName));
    WritableWorkbook copy = Workbook.createWorkbook(new File(inputDataFileName),workbook);
    WritableSheet sheet1 = copy.getSheet(inputDataSheetName);

    def groovyUtils = new com.eviware.soapui.support.GroovyUtils(context)
    def reqholder = groovyUtils.getXmlHolder(reqOperationName+"#Request")
    try{
    rowcount = sheet1.getRows();
    colcount = sheet1.getColumns();
    for(Row in 1..rowcount-1){
    for(Col in 2..colcount-1){
    String reqTagName = sheet1.getCell(Col,0).getContents()
    def TagCount = reqholder["count(//*:"+reqTagName+")"]
    if(TagCount!=0){
    String reqTagValue = sheet1.getCell(Col,Row).getContents()
    reqholder.setNodeValue("//*:"+reqTagName, reqTagValue)
    reqholder.updateProperty()
    }
    }
    //test the request
    testRunner.runTestStepByName(reqOperationName)
    //Read Response xml

    def resholder = groovyUtils.getXmlHolder(reqOperationName+"#Response")
    resTagValue1= resholder.getNodeValues("//*:productID")
    resTagValue2= resholder.getNodeValues("//*:accountNumber")
    resTagValue3= resholder.getNodeValues("//*:insuranceCategory")
    resTagValue4= resholder.getNodeValues("//*:imei")
    resTagValue5= resholder.getNodeValues("//*:handsetMake")
    resTagValue6= resholder.getNodeValues("//*:handsetModel")
    resTagValue7= resholder.getNodeValues("//*:insurancePolicyName")
    resTagValue8= resholder.getNodeValues("//*:insuranceStartTimestamp")
    //Write Response into excel sheet

    Label resValue1= new Label(4,Row,resTagValue1);
    sheet1.addCell(resValue1);
    Label resValue2= new Label(5,Row,resTagValue1);
    sheet1.addCell(resValue1);
    Label resValue3= new Label(6,Row,resTagValue1);
    sheet1.addCell(resValue1);
    Label resValue4= new Label(7,Row,resTagValue1);
    sheet1.addCell(resValue1);
    Label resValue5= new Label(8,Row,resTagValue1);
    sheet1.addCell(resValue1);
    Label resValue6= new Label(9,Row,resTagValue1);
    sheet1.addCell(resValue1);
    Label resValue7= new Label(10,Row,resTagValue1);
    sheet1.addCell(resValue1);
    Label resValue8= new Label(11,Row,resTagValue1);
    sheet1.addCell(resValue1);

    }

    }catch (Exception e) {log.info(e)}
    finally{
    copy.write();
    copy.close();
    workbook.close();
    }
    log.info("Testing Over")

    I am very much badly stuck with this issue, please you can help me out.
    Looking forward for a quick response.
    Thanks and Regards,

    ReplyDelete
    Replies
    1. Hello,

      If possible can you pass request sample so i can rearrange the code for you.

      Delete
  57. Hi,

    Below is my soap request.I have a field called insurance ID to which data has to be inserted from excel sheet.



    556688



    awaiting your quick response.
    Thank You

    ReplyDelete
  58. Oops sorry Sunny, I tried sending the request but it is displaying empty.... I even can't add screenshot.. However you have added 3 fields in the request but i have only one i.e. phone number and in response I need 5 elements. If there is any other source where i can share you the request then please let me know... I am very much stuck with the issue and need to resolve it ASAP. Thank you

    ReplyDelete
  59. Hi Sunny,

    I'm very new to Soap UI and I was struggling through the area that you've done above, so thank you very much for the demo, it was very helpful. :)
    I have few questions to ask.

    import jxl.*;
    import jxl.read.biff.BiffException;
    import jxl.write.*;
    The above jxl imports are given for .xls right ?

    So here goes my questions.
    1. For .xlsx we need to import POI ? If yes, can you please give the specific imports. (just like the above mentioned)
    2. The jar files (if there's a specific version) that we want put inside the smartbear/bin/ext
    (I'm using Soap UI 5.4.0)

    ReplyDelete
    Replies
    1. Hello,

      Thanks for your time reading my blog.

      If you take .xlsx and want to use apache POI then you need to write too many lines of code. So better your .xls only for few lines code and easy coding.

      Thanks,
      Sunny

      Delete
  60. This is the code i have used but getting error at line no 28
    NO Signature of method jxl.read.biff.LabelSSTRecord.getContents()

    import com.eviware.soapui.support.XmlHolder
    import java.io.File;
    import java.io.IOException;
    import jxl.read.biff.BiffException;
    import jxl.*;

    log.info ("Testing Started")

    def reqOpName = "ConvertTemp"
    Workbook workbook = Workbook.getWorkbook(new File("/home/to-ou-01/Documents/Convert.xls"))
    Sheet sheet1 = workbook.getSheet("Worksheet 1");

    def groovyUtils = new com.eviware.soapui.support.GroovyUtils(context)
    def reqholder = groovyUtils.getXmlHolder(reqOpName+ "#Request")

    row = sheet1.getRows();
    col = sheet1.getColumns();

    for(Row in 1..row-1)
    {
    for(Col in 2..col-1)
    {
    String reqTagName = sheet1.getCell(Col,0).getContents()
    def tagCount = reqholder["count(//*:"+reqTagName+")"]

    if(tagCount!=0)
    {
    String reqTagValue = sheet1.getCell(Col,Row).getcontents()
    reqholder.setNodeValue("//*:"+reTagName, reqTagValue)
    reqholder.updateProperty()
    }
    }
    testRunner.runTestStepByName(reqOpName)


    }

    ReplyDelete
    Replies
    1. Hello,

      Thanks for your time to reading my blog.

      Here is the code what i found a minor issue that need to be change. At the line No-28 code is as below,

      String reqTagValue = sheet1.getCell(Col,Row).getcontents()

      In the above line of code getcontents's letter "C" should be capital as ..getContents(). Rewrite the above line of code as ,

      String reqTagValue = sheet1.getCell(Col,Row).getContents() and try again. Let me know the result.

      Regards,
      Sandeep S S

      Delete
  61. This comment has been removed by the author.

    ReplyDelete
  62. Hi,

    I used below script but got error: jxl.read.biff.BiffException:unable to recognize OLE stream error

    import com.eviware.soapui.support.XmlHolder
    import java.io.File;
    import java.io.IOException;
    import jxl.*;
    import jxl.read.biff.BiffException;
    import jxl.write.*;

    // DECLARE THE VARIABLES
    log.info("Service testing satrted")
    def reqOperationName="process";
    def inputDataFileName="/Users/10651786/desktop/EmpData.xls"
    def inputDataSheetName="sheet1";

    //def myTestCase = context.testCase //myTestCase contains the test case

    //def counter,next,previous,size //Variables used to handle the loop and to move inside the file

    Workbook workbook = Workbook.getWorkbook(new File(inputDataFileName)) //file containing the data
    WritableWorkbook copy= Workbook.createWorkbook(new File(inputDataFileName),Workbook);
    WritableSheet sheet1=copy.getSheet(inputDataSheetName);


    def groovyUtils=new com.eviware.soapui.support.GroovyUtils(context)
    def reqholder=groovyUtils.getXmlHolder(reqOperationName+"#Request")
    try
    {
    rowcount=sheet1.getRows();
    colcount=sheet1.getColumns();

    for(Row in 1..rowcount-1){
    for (Col in 2..colcount-1){
    String reqTagName = sheet1.getCell(Col,0).getContents()
    def TagCount = reqholder["count(//*:"+reqTagName+")"]
    if(TagCount!=0)

    String reqTagValue = sheet1.getCell(Col,Row).getcontents()
    reqholder.setNodeValue("//*:"+reqTagName,reqTagValue)
    reqholder.updateProperty()
    }

    //To run test request
    testRunner.runTestStepByName(reqOperationName)



    }//Row loop end here
    }catch(Exception e){log.info(e)}
    finally{
    copy.write();
    copy.close();
    workbook.close();
    }
    log.info("service testing finished")

    ReplyDelete
  63. I am using soaupui 5.3.0 and have placed jxl2.6.1 jar in bin/ext folder

    ReplyDelete
    Replies
    1. Hello
      Thanks for reading my blog.

      Can you make sure is the Input file present in the location. If present also it may be got corrupted can you please create again and try.

      Regards,
      Sandeep S S

      Delete
  64. Hi- Thank you - i could run the script successfully. But after executing few requests i am seeing this error --Wed Feb 20 19:41:54 IST 2019:ERROR:An error occurred [error: Unexpected element: CDATA], see error log for details
    Wed Feb 20 19:42:27 IST 2019:DEBUG:Connection closed- Would you please help. Thanks very much

    ReplyDelete
  65. Hi,

    Thank you for the post.. it really helped me to the extent.

    i faced the issue when i passed the null value from excel, its deleting the request field.

    ReplyDelete
  66. It's only running one row of data, Is there a loop ending somewhere in middle.

    ReplyDelete
    Replies
    1. Hello Naveen,

      Thanks for your interest in my blog.

      You can use this line of code to run the number of rows from or till which row you want to run. Below are some examples.



      To run from 1 to 50 rows = for(Row in 1..50-1)
      To run from 30 to 45 rows = for(Row in 30..45-1)
      To run from 1 to all rows = for(Row in 1..rowcount-1)
      To run from 35 to all rows = for(Row in 35..rowcount-1)


      Let me know if this help you.

      Delete