[TechCorner Challenge #4] How to Generate a Request Body Based on Data from Datasource

Task: Your task for today is to write a script that will read data from an Excel file and generate a request body for the SOAP TestStep based on it. The number of rows in the Excel file is dynamic.

This is a solution created for [TechCorner Challenge #4]

Hi sonya_m ,

Here is my code which will generate XML out of Data present in Excel file, not matter how many rows are there, it will append the XML according to number of Rows be dynamic.

Pre-requisite : Need to add Groovy Script and Properties Step inside Test Case

import java.io.*
import java.text.SimpleDateFormat;  
import java.util.Calendar;  
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 groovy.xml.MarkupBuilder
import groovy.xml.StreamingMarkupBuilder

def fs = new FileInputStream("F:\\temp.xlsx")

Workbook wb = WorkbookFactory.create(fs);
def ws = wb.getSheet("Sheet1");
int r = ws.getLastRowNum();
testRunner.testCase.getTestStepByName("Properties").setPropertyValue("DataRowCount", r.toString());
for(def i = 1 ; i <= r ; i++ ){
	def row = ws.getRow(i)
	def headerRow = ws.getRow(0)
	def noOfCell = row.getLastCellNum();
	for (def j=0;j<noOfCell;j++){
		def headertext = ws.getRow(0).getCell(j).getStringCellValue()
		Cell cell = row.getCell(j)
		if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK) {
			value = "";
		}
		else if(cell.getCellType() == Cell.CELL_TYPE_BOOLEAN){
			value = (cell.getBooleanValue());
		}
		else if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
			value = (cell.getNumericCellValue());
		}
		else if(cell.getCellType() == Cell.CELL_TYPE_STRING){
			value = (cell.getStringCellValue());
		}
		else if(cell.getCellType() == Cell.CELL_TYPE_ERROR){
		}
		else if(cell.getCellType() == Cell.CELL_TYPE_FORMULA){
			value = String.valueOf(cell.getStringCellValue())
		}
 		testRunner.testCase.getTestStepByName("Properties").setPropertyValue(headertext.toString()+""+i.toString(), value.toString())
	}
}

def start = '''<?xml version="1.0"?>
<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope/" soap:encodingStyle="http://www.w3.org/2003/05/soap-encoding">
	<soap:Header>
		<m:Trans xmlns:m="https://www.w3schools.com/transaction/" soap:actor="https://www.w3schools.com/code/">234
		</m:Trans>
	</soap:Header>
	<soap:Body>
	'''
def end = '''
</soap:Body>
</soap:Envelope>'''

def writer = new StringWriter()
def xml = new MarkupBuilder(writer) 

xml.CATALOG() {
	for(int i = 1 ; i <= r ; i++){		
		CD(){
			TITLE(context.expand('${Properties#Title'+i.toString()+'}'))
			ARTIST(context.expand('${Properties#Artist'+i.toString()+'}'))
			PRICE(context.expand('${Properties#Price'+i.toString()+'}'))
		}
	}
}

log.info start + writer + end

Task: Your task for today is to write a script that will read data from an Excel file and generate a request body for the SOAP TestStep based on it. The number of rows in the Excel file is dynamic.

This is a solution created for [TechCorner Challenge #4]

Script Features & Handle additional data validations and groovy way of building xml such as

- Does not require any additional test steps.

- Zero changes in the script, yet provide different output based on the input data, including file path (handled thru custom properties).

- Empty rows (shown in the picture)

- Empty cells (shown in the picture)

- User can choose the sheet name (default is Sheet1)

- User can optionally consider or ignore first row as data

- Handles errors such as File existence in the given path, Sheet name, if rows present or not before building xml. Throws error otherwise.

- Supports .xlsx (poi-3.8.0 & tested in SoapUI 5.4) format

- Uses StreamingMarkupBuilder for building xml (a native way to build xmls easily)

Just add test case custom properties as mentioned below

DATA_FILE and add the file name, mandatory

SHEET_NAME and add name of the sheet in the excel file, optional

IGNORE_FIRST_ROW and add boolean value true or false; optional

Here is the(groovified) script (and follow in-line comments)

Contents of Groovy script, i.e., CreateRequestFromDataSource test step.

import org.apache.poi.xssf.usermodel.XSSFWorkbook
import static org.apache.poi.ss.usermodel.Cell.*
import groovy.xml.StreamingMarkupBuilder
import groovy.xml.XmlUtil

//Used to create the data map from excel cells 
def columnMap = [0: 'title', 1: 'artist', 2: 'price']

//Validate if the data file exists and only supports .xlsx files as input
def isFileValid = { 	
	assert null != it, 'File path is empty'
	def xFile = new File(it)
	assert xFile.exists(), "${it} file doesn't exists"
	assert it.endsWith('.xlsx'), 'Only allows file extension .xlsx'
	true
}

// A closure that creates data (list of rows; and each row as map) from input file
// User can choose the sheet name (default is Sheet 1), throws if sheet does not exists
// And whether to ignore first row or not
// Also handles empty rows and empty cells
// Support apache-poi 3.8 and tested in SoapUI 5.4
def getData = { filepath, isIgnoreFirstRow = true, sheetName = 'Sheet1' ->
	def data = [] 
	if (isFileValid(filepath)) {
		def sheet = new XSSFWorkbook(filepath).getSheet sheetName
		assert null != sheet, "${sheetName} does not existing in the ${filepath}"		
		sheet.rowIterator().eachWithIndex { row, rowId ->
			def map = [:]
			if (!isIgnoreFirstRow || rowId != 0) {
				for (index = 0; index <row.lastCellNum;index++){
					cell = row.getCell(index, row.CREATE_NULL_AS_BLANK);
					switch (cell.cellType) {
            				case CELL_TYPE_STRING: map.put(columnMap[index], cell.stringCellValue); break
            				case CELL_TYPE_NUMERIC: map.put(columnMap[index], cell.numericCellValue); break
            				case CELL_TYPE_BOOLEAN: map.put(columnMap[index], cell.booleanCellValue); break
            				case CELL_TYPE_FORMULA: map.put(columnMap[index], cell.getCachedFormulaResultType()); break
            				default: map.put(columnMap[index], ''); break
					}
        			}
        			data << map
			}				
		}
	}	
	assert data.size(), 'No data created from file'
	data
}

//A closure that builds the xml request based on the user input
def createRequest = { cds, trans ->
	def nameSpacesMap = [
            soap: 'http://schemas.xmlsoap.org/soap/envelope/',
            m: 'https://www.w3schools.com/transaction/'
    ]
    def builder = new StreamingMarkupBuilder()
    builder.encoding ='utf-8'
    def soapRequest = builder.bind {
        namespaces << nameSpacesMap
        soap.Envelope {
            soap.Header {
            	m.Trans('soap:actor': 'https://www.w3schools.com/code/', trans)
            }
            soap.Body {
               CATALOG {
               	cds.each  { cd ->
               		CD {
               			TITLE cd.title
               			ARTIST cd.artist
               			PRICE cd.price
               		}
               	}
               }
            }
        }
    }
    XmlUtil.serialize soapRequest 
}

//Actuall flow of the script starts here
def file = context.testCase.getPropertyValue('DATA_FILE')
def sheet =  context.testCase.getPropertyValue('SHEET_NAME') ?: 'Sheet1'
def ignoreFirstRow = context.testCase.getPropertyValue('IGNORE_FIRST_ROW')?.toBoolean() ?: true

//Call to the closures
log.info createRequest(getData(file, ignoreFirstRow, sheet), 234)

ADD-ON

Realized that usually, user not only wants to create / build the SOAP request, but also like to update the request in the following SOAP Request content in the test case.

Let us assume that the test case has Request step, say MyRequest  step in which user wants to update the content dynamically from the data source file using groovy script. You can see the steps in the test case from the pictures above.

So in order to complete the use case, needed to append below script at the end of the above in order to achieve the same.

Here user can add an additional test case custom property for Request step name (default is SOAP Request), say REQUEST_STEP_NAME and value as you needed, MyRequest in this case as per picture.

//Call to the closures
def request = createRequest(getData(file, ignoreFirstRow, sheet), 234)

//Get the next test step request
def nextStepName = context.testCase.getPropertyValue('REQUEST_STEP_NAME') ?: 'SOAP Request'
//Get the next test step request
def nextStepRequest = context.testCase.testSteps[nextStepName].httpRequest
//Update the request details in the next request test step
nextStepRequest.requestContent = request

Hope this ADD-ON adds value.