Forum Discussion

sonya_m's avatar
sonya_m
SmartBear Alumni (Retired)
4 years ago
Solved

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

Hi everyone,

 

Thank you for your TechCorner input! It has been awesome an extremely helpful. I've got more challenges for you🙂

 

A request body can be dynamic, and it is not always possible to use the existing ReadyAPI functionality to create it. One use case example could be changing data before sending a request. This is when users need to come up with a custom script.


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.

Difficulty

 

Tips:

 

https://smartbear-cc.force.com/portal/KbArticleViewer?name=Example-of-using-third-party-Java-libraries-in-Groovy-script-reading-from-Excel-file-using-Apache-POI&sp=all

 

https://community.smartbear.com/t5/SoapUI-Open-Source/Dynamic-request-creation-with-Groovy-element-Create/m-p/127999/thread-id/21964#M24011

 

Excel example:

 

 

 

 

 

 

 

 

 

Request body example:

 

 

 

 

 

 

 

<?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>
		<CATALOG>
			<CD>
				<TITLE>Empire Burlesque</TITLE>
				<ARTIST>Bob Dylan</ARTIST>
				<PRICE>10.90</PRICE>
			</CD>
			<CD>
				<TITLE>Hide your heart</TITLE>
				<ARTIST>Bonnie Tyler</ARTIST>
				<PRICE>9.90</PRICE>
			</CD>
			<CD>
				<TITLE>Greatest Hits</TITLE>
				<ARTIST>Dolly Parton</ARTIST>
				<PRICE>9.90</PRICE>
			</CD>
			<CD>
				<TITLE>Still got the blues</TITLE>
				<ARTIST>Gary Moore</ARTIST>
				<PRICE>10.20</PRICE>
			</CD>
		</CATALOG>
	</soap:Body>
</soap:Envelope>

 

 

 

 

 

 

 

 

Have fun when solving this!😊

  • 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.

3 Replies

  • 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

     

     

     

  • nmrao's avatar
    nmrao
    Champion Level 3

    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.

    • sonya_m's avatar
      sonya_m
      SmartBear Alumni (Retired)

      Rao, Himanshu, thank you for the job well-done!