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

15 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
  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

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