In this blog post, we’ll understand how to perform data driven testing using SoapUI’s open source version with Groovy and Microsoft Excel as datasource. To automate web services, we are using SoapUI, which is one of the world’s most widely used open source API testing tool. Additionally, users can download SoapUI Pro, which is a licensed tool that provides great options and simpler ways to automate data driven tests. Here in this case, we’re automating data driven tests with options that are available in the open source version of SoapUI.

Prerequisites

• Download and install the open source version of SoapUI from their website.
• Download jxl.jar and place it in lib folder of SoapUI.
• For example: ‘C:\Program Files\SmartBear\SOAP UI\lib’

‘jxl.jar’ is the jar file which supports all Excel operations, this jar supports only ‘.xls’ format.

Procedure for Data Driven Testing using SOAP Request

• Open SoapUI editor and use the navigation File > New > Functional Test and provide the .wsdl url as shown below and click on the ‘Finish’ button.

• Right click on ‘Project’ and click on ‘New Test Suite’ to create a test suite. Now right click on ‘Test Suite’ and then ‘New Test Case’ to create a test case under test suite. Test case consists of test steps where the actual automation takes place.

Note: We can create any number of test cases under a test suite.

• Right click on ‘Test Case’ and select Add Step > Properties, these properties are there to hold the values of defined variables.

• Right click on ‘Test Case’ and select Add Step > SOAP Request (which consists of actual request to parameterize). The one marked in yellow is getting parameterized.

• Right click on ‘Test Case’ and select Add Step > Groovy Script. Now, place the below script and click on ‘Save’ option.

[java]import jxl.*
import jxl.write.*
import java.io.File;
import com.eviware.soapui.support.*;
import java.util.*;
import java.lang.*;
import jxl.read.biff.BiffException;
Workbook wk;
WritableWorkbook workbookCopy;
def fr;
try
{
fr = new File("C:\\SOAP Projects CSC\\My Own Projects\\Check Domain Groovy\\CheckDomainTestData1.xls")
wk = Workbook.getWorkbook(fr);
def s1 = wk.getSheet(0);
workbookCopy = Workbook.createWorkbook(fr, wk);
WritableSheet sheetToEdit = workbookCopy.getSheet(0);
def r = s1.getRows();
// -------------------------------------------------------------
// Defining object “fr” for Excel workbook
// Assigning Workbook Object to “wk”
// Getting Sheet1 into variable “s1”
// Creating and Assigning workbook to “WorkbookCopy”
// Defining writable sheet and getting Sheet1 to “sheetToEdit”
// Getting row count to “r”
// -------------------------------------------------------------
for(def i=1;i<r;i++)
{
def c1 = s1.getCell(0, i)
def c2 = s1.getCell(1, i)
// -------------------------------------------------------------
// Iterating through Excel rows
// Assigning cell “A2” to “c1”
// Assigning cell “A3” to “c2”
// -------------------------------------------------------------
testRunner.testCase.testSteps["GroovyProperties"].setPropertyValue("DomainName",c1.getContents())
testRunner.testCase.testSteps["GroovyProperties"].setPropertyValue("TLD",c2.getContents())
// -------------------------------------------------------------
// Using testRunner object we are assigning content in cell “A2” to property “DomainName” and cell “A3” to Property “TLD”
// -------------------------------------------------------------
testRunner.runTestStepByName("CheckDomainCommand")
def EndPointURL = testRunner.testCase.testSteps["GroovyProperties"].getPropertyValue("EndPointURL")
// -------------------------------------------------------------
// Using “testRunner.run” we are executing the actual command
// “EndPointURL” defines in which environment the test need to execute
// -------------------------------------------------------------
def groovyUtils = new com.eviware.soapui.support.GroovyUtils(context)
// -------------------------------------------------------------
// Defining “groovyUtils” to access the utility methods of GroovyUtils class
// -------------------------------------------------------------
def holder = groovyUtils.getXmlHolder("CheckDomainCommand#Response")
// -------------------------------------------------------------
// Defining “holder” to capture the XML response
// -------------------------------------------------------------
soapReturn = holder.getNodeValue( "//soapExecuteReturn" )
soapResponse = groovyUtils.getXmlHolder(soapReturn)
responseStatus = soapResponse.getNodeValue("//ResponseStatus")
// -------------------------------------------------------------
// Capturing SOAP response to “soapResponse” and “Status” to “responseStatus”
// -------------------------------------------------------------
if ("FAILURE".equalsIgnoreCase(responseStatus))
{
log.info("Check Dmoain Status ////////////////: " + responseStatus)
ErrorResponse = soapResponse.getNodeValue("//ErrorMsg")
log.info("Check Dmoain Status ////////////////: " + ErrorResponse)
Label label1 = new Label(2, i, responseStatus.toString());
cell1 = (WritableCell) label1;
sheetToEdit.addCell(cell1);
Label label3 = new Label(4, i, EndPointURL.toString());
cell3 = (WritableCell) label3;
sheetToEdit.addCell(cell3);
Label label4 = new Label(5, i, ErrorResponse.toString());
cell4 = (WritableCell) label4;
sheetToEdit.addCell(cell4);
}
else
{
isAvailable = soapResponse.getNodeValue("//IsAvail")
log.info("Check Dmoain IsAvail ////////////////: " + isAvailable)
Label label1 = new Label(2, i, responseStatus.toString());
cell1 = (WritableCell) label1;
sheetToEdit.addCell(cell1);
Label label2 = new Label(3, i, isAvailable.toString());
cell2 = (WritableCell) label2;
sheetToEdit.addCell(cell2);
Label label3 = new Label(4, i, EndPointURL.toString());
cell3 = (WritableCell) label3;
sheetToEdit.addCell(cell3);
}
// -------------------------------------------------------------
// Based on the response status “FAILURE” or “SUCCESS” writing error message to log file using “log.info”
// Defining label and capturing the required response field from complete response
// Assigning the captured label value to a cell
// Finally writing it on sheet which is editable
// -------------------------------------------------------------
}
workbookCopy.write();
}
catch (Exception e)
// -------------------------------------------------------------
// Catching exception and writing it to error log
// -------------------------------------------------------------
{
log.error(e)
}
finally
{
if(workbookCopy != null)
{
workbookCopy.close();
}
if(wk != null)
{
wk.close();
}
}
log.info("Testing Completed")
// -------------------------------------------------------------
Catching final exception using “finally” block
Closing workbook if it is not null
Closing Worksheet if it is not null
Writing a note “Test Completed” to log file using “log.info”
// -------------------------------------------------------------[/java]

Sample Input Sheet Appears as Follows

Sample Output Sheet When we get FAILURE Response

Sample Output Sheet when we get SUCCESS Response

Conclusion

We can perform data driven testing related to database, and any SQL operation with a customized query using Groovy script and Excel as a data source. We can also capture test results in the same spreadsheet to generate user-friendly reports. Additionally, test results can be presented based on our requirements using the various features available in Microsoft Excel.

Evoke’s – Software Testing Services

We provide world-class quality assurance services that are designed with a greater focus on quality, performance, and security. Our mature software testing frameworks coupled with smart testing methodologies enable enterprises to meet the expectations of both business users and customers. To learn about our leading-edge testing services contact us today.

Author

Deva Prasad Gajawada is a Test Lead at Evoke Technologies. He is a specialized web service tester with over 9 years of experience in manual and automation testing. In his free time, he likes to read and explore emerging tools & technologies in the software testing domain.