Road to data driven testing in SoapUI using groovy script with excel file

SoapUI Pro has a feature to read data from external files like: excel, csv etc. But SoapUI does not provide such feature to read data from excel file. So for reading data from excel file in SoapUI, we need to write some code in groovy script.
I this post I will show you, how to read data from excel file.I am using poi jar files to read data from excel file in groovy, download following jar files and put into SoapUI lib folder.
  • poi-3.8-beta5-20111217.jar
  • poi-examples-3.8-beta5-20111217.jar
  • poi-excelant-3.8-beta5-20111217.jar
  • poi-ooxml-3.8-beta5-20111217.jar
  • poi-ooxml-schemas-3.8-beta5-20111217.jar
  • poi-scratchpad-3.8-beta5-20111217.jar 
  • dom4j-1.6.1.jar
In this post, I have created a SoapUI project for “ConversionRate“ API, and created a test case and teststep with name “ConversionRate”. So here I need to run this test for the set of data, where data is in external excel file.

I have created a “ReadXLSFile” groovy step and write below code to read data from “Book1.xlsx” file for the “ConversionRate” API method. Below is excel file data:

To
From
USD
ALL
AFA
DZD
AWG
BSD
BSD
BDT
GroovyScript Code:

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.util.*;
import org.apache.poi.ss.usermodel.*;
import java.io.*;

class ExcelReader {
 
  def readData() {
        def path = "E:\\Automation-WorkArea\\APITest\\Book1.xlsx";
        InputStream inputStream = new FileInputStream(path);
        Workbook workbook = WorkbookFactory.create(inputStream);
        Sheet sheet = workbook.getSheetAt(0);
                     
        Iterator rowIterator = sheet.rowIterator();
        rowIterator.next()
        Row row;                       
        def rowsData = []
        while(rowIterator.hasNext()) {
             row = rowIterator.next()
             def rowIndex = row.getRowNum()
             def colIndex;
             def rowData = []
             for (Cell cell : row) {
                 colIndex = cell.getColumnIndex()
                  rowData[colIndex] = cell.getRichStringCellValue().getString();
             }                    
             rowsData << rowData
         }
         rowsData
  }
 }

def groovyUtils = new com.eviware.soapui.support.GroovyUtils(context)
def myTestCase = context.testCase

ExcelReader excelReader = new ExcelReader();
List rows = excelReader.readData();
def d = []
Iterator i = rows.iterator();
while( i.hasNext()){
         d = i.next();
         myTestCase.setPropertyValue("From", d[0])
         myTestCase.setPropertyValue("To", d[1])       
         testRunner.runTestStepByName( "ConversionRate")
}
         


Description:
  • ExcelReader class containing a function “readData” to read data from “Book1.xlsx" file.
  • myTestCase.setPropertyValue("From", d[0]) and myTestCase.setPropertyValue("To", d[1])  are  used to set testcase “from” and “To” properties  value.
  • testRunner.runTestStepByName( "ConversionRate") this steps is used to run test step “ConversionRate”
So in this way when I run test “ReadXLSFile” read data from xls file and will execute test “ConversionRate” for each setoff data

27 comments:

  1. After running the script i am getting the following error. could you please help me to resolve the error.




    soap:Client
    System.Web.Services.Protocols.SoapException: Server was unable to read request. ---> System.InvalidOperationException: There is an error in XML document (5, 47). ---> System.InvalidOperationException: Instance validation error: '' is not a valid value for Currency.
    at Microsoft.Xml.Serialization.GeneratedAssembly.XmlSerializationReader1.Read1_Currency(String s)
    at Microsoft.Xml.Serialization.GeneratedAssembly.XmlSerializationReader1.Read2_ConversionRate()
    at Microsoft.Xml.Serialization.GeneratedAssembly.ArrayOfObjectSerializer.Deserialize(XmlSerializationReader reader)
    at System.Xml.Serialization.XmlSerializer.Deserialize(XmlReader xmlReader, String encodingStyle, XmlDeserializationEvents events)
    --- End of inner exception stack trace ---
    at System.Xml.Serialization.XmlSerializer.Deserialize(XmlReader xmlReader, String encodingStyle, XmlDeserializationEvents events)
    at System.Xml.Serialization.XmlSerializer.Deserialize(XmlReader xmlReader, String encodingStyle)
    at System.Web.Services.Protocols.SoapServerProtocol.ReadParameters()
    --- End of inner exception stack trace ---
    at System.Web.Services.Protocols.SoapServerProtocol.ReadParameters()
    at System.Web.Services.Protocols.WebServiceHandler.CoreProcessRequest()



    ReplyDelete
  2. Hi

    do i need to change any thing with request. I am always getting the following error operationException: Instance validation error: '' is not a valid value for Currency. looks like that request not parametrized properly. I really appreciate if you could help me.

    ReplyDelete
    Replies
    1. Can you send me your created test on roadtoautomation@gmail.com I will look into and will resolve the issue.

      Delete
  3. Ur code is very helpful in reading xls. But I am stuck on how to use variables d[0],d[1] to soap request. I tried so much but of no use

    ReplyDelete
  4. Thanks for sharing the Code. it was really helpful. But not working if the rows are more than one. As one gets the last row value in Property. Pls explain how to overcome this.

    ReplyDelete
    Replies
    1. You need to apply Loop after that so that It will execute request multiple times

      Delete
  5. I am new to soapui....my task is to test rest api..i have executed all the methods by giving parameter and values...but the main doubt is i should give values by excel using datasource the soapui is asking for jar files in soapui4.5.1/bin/ext . where can i get these jar files? is there any link for downloading the jar file....i am using soapui pro trial version...plz help me..thank u

    ReplyDelete
  6. I am able to run the above code in one go .. thanks for sharing it !!

    ReplyDelete
    Replies
    1. would you help me its not working for me

      Delete
  7. I am using same script (little bit modified) for accesing data from excel sheet. But I am getting error on line 'while( i.hasNext())'.

    My script:
    import org.apache.poi.ss.usermodel.*;
    import org.apache.poi.hssf.usermodel.*;
    import org.apache.poi.xssf.usermodel.*;
    import org.apache.poi.ss.util.*;
    import org.apache.poi.ss.usermodel.*;
    import java.io.*;

    class ExcelReader {

    def readData() {
    def path = "D:\\Testing\\SoapUi\\BankId.xlsx";
    InputStream inputStream = new FileInputStream(path);
    Workbook workbook = WorkbookFactory.create(inputStream);
    Sheet Sheet1 = workbook.getSheetAt(0);
    Iterator rowIterator = sheet.rowIterator();
    rowIterator.next()
    Row row;
    def rowsData = []
    while(rowIterator.hasNext()) {
    row = rowIterator.next()
    def rowIndex = row.getRowNum()
    def colIndex;
    def rowData = []
    for (Cell cell : row) {
    colIndex = cell.getColumnIndex()
    rowData[colIndex] = cell.getRichStringCellValue().getString();
    }
    rowsData << rowData
    }
    rowsData
    }
    def groovyUtils = new com.eviware.soapui.support.GroovyUtils(context)
    def myTestCase = context.testCase

    ExcelReader excelReader = new ExcelReader();
    List rows = excelReader.readData();
    def d = []
    Iterator i = rows.iterator();
    while( i.hasNext()){
    d = i.next();
    myTestCase.setPropertyValue("BankID", d[0])
    testRunner.runTestStepByName( "GetBankBranchCodes - Request 1")
    }

    ReplyDelete
  8. Hi i need small help here, how to set the property value in the above scenario for the test step. The script is running but am failing to pass data to the step am not sure where do we need to set any property

    ReplyDelete
  9. I use your script and meet some problems below. I'm new in SoapUI, could you pls help me to solve it?

    Fri May 20 11:56:34 ICT 2016:ERROR:An error occurred [startup failed:
    Script7.groovy: 11: unable to resolve class FileIssssnputStream
    @ line 11, column 35.
    InputStream inputStream = new FileIssssnputStream(path);
    ^
    org.codehaus.groovy.syntax.SyntaxException: unable to resolve class FileIssssnputStream
    @ line 11, column 35.
    at org.codehaus.groovy.ast.ClassCodeVisitorSupport.addError(ClassCodeVisitorSupport.java:146)
    at org.codehaus.groovy.control.ResolveVisitor.resolveOrFail(ResolveVisitor.java:222)
    at org.codehaus.groovy.control.ResolveVisitor.resolveOrFail(ResolveVisitor.java:232)
    at org.codehaus.groovy.control.ResolveVisitor.transformConstructorCallExpression(ResolveVisitor.java:969)
    at org.codehaus.groovy.control.ResolveVisitor.transform(ResolveVisitor.java:646)
    at org.codehaus.groovy.control.ResolveVisitor.transformDeclarationExpression(ResolveVisitor.java:1010)
    at org.codehaus.groovy.control.ResolveVisitor.transform(ResolveVisitor.java:638)
    at org.codehaus.groovy.ast.ClassCodeExpressionTransformer.visitExpressionStatement(ClassCodeExpressionTransformer.java:139)
    at org.codehaus.groovy.ast.stmt.ExpressionStatement.visit(ExpressionStatement.java:40)
    at org.codehaus.groovy.ast.CodeVisitorSupport.visitBlockStatement(CodeVisitorSupport.java:35)
    at org.codehaus.groovy.ast.ClassCodeVisitorSupport.visitBlockStatement(ClassCodeVisitorSupport.java:163)
    at org.codehaus.groovy.control.ResolveVisitor.visitBlockStatement(ResolveVisitor.java:1240)
    at org.codehaus.groovy.ast.stmt.BlockStatement.visit(BlockStatement.java:69)
    at org.codehaus.groovy.ast.ClassCodeVisitorSupport.visitClassCodeContainer(ClassCodeVisitorSupport.java:101)
    at org.codehaus.groovy.ast.ClassCodeVisitorSupport.visitConstructorOrMethod(ClassCodeVisitorSupport.java:112)
    at org.codehaus.groovy.ast.ClassCodeExpressionTransformer.visitConstructorOrMethod(ClassCodeExpressionTransformer.java:50)
    at org.codehaus.groovy.control.ResolveVisitor.visitConstructorOrMethod(ResolveVisitor.java:166)
    at org.codehaus.groovy.ast.ClassCodeVisitorSupport.visitMethod(ClassCodeVisitorSupport.java:123)
    at org.codehaus.groovy.ast.ClassNode.visitContents(ClassNode.java:1055)
    at org.codehaus.groovy.ast.ClassCodeVisitorSupport.visitClass(ClassCodeVisitorSupport.java:50)
    at org.codehaus.groovy.control.ResolveVisitor.visitClass(ResolveVisitor.java:1183)
    at org.codehaus.groovy.control.ResolveVisitor.startResolving(ResolveVisitor.java:141)
    at org.codehaus.groovy.control.CompilationUnit$10.call(CompilationUnit.java:632)
    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.compile(CompilationUnit.java:523)
    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)

    1 error
    ], see error log for details

    ReplyDelete
    Replies
    1. please correct the spelling of FileInputStream in your code

      Delete
  10. Please Use below Groovy Code ,not giving any error Before writing this please include all POi jars in your Project

    import org.apache.poi.xssf.usermodel.*
    import org.apache.poi.xssf.usermodel.XSSFWorkbook

    def fs = new FileInputStream("C:\\Users\\admin\\Documents\\DataFile\\CurrencyData.xlsx")
    def wb = new XSSFWorkbook(fs)
    def ws = wb.getSheet("Sheet1")
    def r = ws.getPhysicalNumberOfRows()

    for(def i=0; i<r; i++)
    {
    def row = ws.getRow(i)
    def c = row.getPhysicalNumberOfCells()

    for (def j=0;j<c;j++)
    {
    def cell = row.getCell(j)
    log.info cell.getStringCellValue()

    }
    }

    ReplyDelete
    Replies
    1. THanks Ruby for making things simple. your script is very helpful.

      Delete
    2. unable to import work book error

      Delete
  11. Hi there,
    I am using SOAPUI 5.3.0 and even though i performed everything it was mentioned above, i am receiving the following error when running the script - org.codehaus.groovy.control.MultipleCompilationErrorsException: startup failed: Script6.groovy: 4: unexpected char: '\' @ line 4, column 33. def fs = new FileInputStream("C:\input.xlsx") ^ org.codehaus.groovy.syntax.SyntaxException: unexpected char: '\' @ line 4, column 33. at ...

    Could you please help me? I must mention, i am using for the very first time the soapUi. Thnaks in advance!

    ReplyDelete
    Replies
    1. I had the same error. In the code, one of these ¨\¨ was missing.Try like this: C:\\input.xlsx

      Delete
  12. Hello,
    I have tried to use the last added script(Rubby Gupta7 April 2017 at 11:08) to read from an xls file,but i am receiving an error when trying to execute the script. Could someone please have at the below error and try to guide me in solving it? I must add that this is my first time i am using Soapui. Thanks in advance!

    org.codehaus.groovy.control.MultipleCompilationErrorsException: startup failed: Script6.groovy: 4: unexpected char: '\' @ line 4, column 33. def fs = new FileInputStream("C:\input.xlsx") ^ org.codehaus.groovy.syntax.SyntaxException: unexpected char: '\' @ line 4, column 33. at ...

    ReplyDelete
    Replies
    1. Try to use def fs =new FileInputStream("C:\\input.xlsx") as Java uses double slash for path location, so that's y it's shows error as unexpected char

      Delete
  13. How to write the response in existing excel sheet using Groovy script??

    ReplyDelete
  14. Hello,

    How to write the response in existing excel sheet using Groovy script?

    ReplyDelete
  15. Hey, I am new in SoapUI and for the first time I am trying to import my TestSuite Level properties from an excel file. I attemped to copy your classes in my soapUI library but can’t download them they are read only! Am I doing a silly mistake🙈 could you please help me to sort this out? I appreciate you response.

    ReplyDelete
  16. you have to us "\\" instead of "\" in soapui

    ReplyDelete
  17. not working in soap ui.
    Getting below error:
    ine 13, column 18. Workbook a = WorkbookFactory.create(inputStream); ^ org.codehaus.groovy.syntax.SyntaxException: unable to resolve class Workbook @ line 13, column 18. at

    ReplyDelete

Leave your comments, queries, suggestion I will try to provide solution