Issue in replacing the values from excel sheet into soap request using groovy script

0
votes

The values which I try to pass from the excel sheet to the particular field in the soap request are not working in the test case level. I am able to read the excel sheet and also the execution is happening as expected but the values are not replaced in the soap request from the excel sheet. I have pasted the code below which I am trying to implement for data driven using groovy script.

import com.eviware.soapui.support.XmlHolder
import java.io.File;
import java.io.IOException;
import jxl.*;
import jxl.read.biff.BiffException;
import jxl.write.*;
log.info("Testing Started")
def reqOperationName = "getInsuranceDetails_1_FTC_005";
def inputDataFileName = "D:/SOAP UI Pro/MPI.xls"
def inputDataSheetName = "MPI"
Workbook workbook = Workbook.getWorkbook(new File(inputDataFileName));
WritableWorkbook copy = Workbook.createWorkbook(new File(inputDataFileName),workbook);
WritableSheet sheet1 = copy.getSheet(inputDataSheetName);
log.info("Testing1 Started")
def groovyUtils = new com.eviware.soapui.support.GroovyUtils(context)
String xmlResponse = '${' + reqOperationName + '#Request' +'}'
def reqholder = groovyUtils.getXmlHolder(context.expand(xmlResponse))
try{
    rowcount = sheet1.getRows();
colcount = sheet1.getColumns();
for(Row in 1..rowcount-1){

    String reqTagName = sheet1.getCell(0,0).getContents()
        log.info reqTagName
        def TagCount = reqholder["count(//*:"+reqTagName+")"]
        if(TagCount!=0){
            String reqTagValue = sheet1.getCell(0,Row).getContents()
            reqholder.setNodeValue("//*:"+reqTagName, reqTagValue)
            reqholder.updateProperty()                              
        }

    //test the request
    testRunner.runTestStepByName(reqOperationName)
log.info("Testing3 Started")
def resholder = groovyUtils.getXmlHolder(reqOperationName+"#Response")
resTagValue1= resholder.getNodeValues("//*:productID")
resTagValue2= resholder.getNodeValues("//*:accountNumber")
resTagValue3= resholder.getNodeValues("//*:insuranceCategory")
resTagValue4= resholder.getNodeValues("//*:imei")
resTagValue5= resholder.getNodeValues("//*:handsetMake")
resTagValue6= resholder.getNodeValues("//*:handsetModel")
resTagValue7= resholder.getNodeValues("//*:insurancePolicyName")
resTagValue8= resholder.getNodeValues("//*:insuranceStartTimestamp")        
//Write Response into excel sheet
Label resValue1= new Label(4,Row,resTagValue1);
sheet1.addCell(resValue1);   
Label resValue2= new Label(5,Row,resTagValue2);
sheet1.addCell(resValue2);
Label resValue3= new Label(6,Row,resTagValue3);
sheet1.addCell(resValue3);
Label resValue4= new Label(7,Row,resTagValue4);
sheet1.addCell(resValue4);
Label resValue5= new Label(8,Row,resTagValue5);
sheet1.addCell(resValue5);
Label resValue6= new Label(9,Row,resTagValue6);
sheet1.addCell(resValue6);
Label resValue7= new Label(10,Row,resTagValue7);
sheet1.addCell(resValue7);
Label resValue8= new Label(11,Row,resTagValue8);
sheet1.addCell(resValue8);      
    }   
}catch (Exception e) {log.info(e)}
finally{
    copy.write();
    copy.close();
    workbook.close();
}
log.info("Testing Over")

While I try to execute the above code there are no any errors in the error log. The only thing I am getting in the log output is Wed Dec 06 15:04:00 IST 2017:INFO:groovy.lang.GroovyRuntimeException: Could not find matching constructor for: jxl.write.Label(java.lang.Integer, java.lang.Integer, [Ljava.lang.String;)

Any help on the above issue would be very grateful..

Thank you in advance.

New Edit of the code....

import com.eviware.soapui.support.XmlHolder
import java.io.File;
import java.io.IOException;
import jxl.*;
import jxl.read.biff.BiffException;
import jxl.write.*;
log.info("Testing Started")
def reqOperationName = "getInsuranceDetails_1_FTC_005";
def inputDataFileName = "D:/SOAP UI Pro/MPI.xls"
def inputDataSheetName = "MPI"
Workbook workbook = Workbook.getWorkbook(new File(inputDataFileName));
WritableWorkbook copy = Workbook.createWorkbook(new File(inputDataFileName),workbook);
WritableSheet sheet1 = copy.getSheet(inputDataSheetName);
log.info("Testing1 Started")
def groovyUtils = new com.eviware.soapui.support.GroovyUtils(context)
String xmlResponse = '${' + reqOperationName + '#Request' +'}'
def reqholder = groovyUtils.getXmlHolder(context.expand(xmlResponse))
try{
    rowcount = sheet1.getRows();
colcount = sheet1.getColumns();
for(Row in 1..rowcount-1){

    String reqTagName = sheet1.getCell(0,0).getContents()
        log.info reqTagName
        def TagCount = reqholder["count(//*:"+reqTagName+")"]
        if(TagCount!=0){
            String reqTagValue = sheet1.getCell(0,Row).getContents()
            reqholder.setNodeValue("//*:"+reqTagName, reqTagValue)
            reqholder.updateProperty()                              
        }

    //test the request
    testRunner.runTestStepByName(reqOperationName)
log.info("Testing3 Started")
def resholder = groovyUtils.getXmlHolder(reqOperationName+"#Response")
resTagValue1= resholder.getNodeValue("//*:productID")
resTagValue2= resholder.getNodeValue("//*:accountNumber")
resTagValue3= resholder.getNodeValue("//*:insuranceCategory")
resTagValue4= resholder.getNodeValue("//*:imei")
resTagValue5= resholder.getNodeValue("//*:handsetMake")
resTagValue6= resholder.getNodeValue("//*:handsetModel")
resTagValue7= resholder.getNodeValue("//*:insurancePolicyName")
resTagValue8= resholder.getNodeValue("//*:insuranceStartTimestamp")        
//Write Response into excel sheet
Label resValue1= new Label(2,Row,resTagValue1);
sheet1.addCell(resValue1);   
Label resValue2= new Label(3,Row,resTagValue2);
sheet1.addCell(resValue2);
Label resValue3= new Label(4,Row,resTagValue3);
sheet1.addCell(resValue3);
Label resValue4= new Label(5,Row,resTagValue4);
sheet1.addCell(resValue4);
Label resValue5= new Label(6,Row,resTagValue5);
sheet1.addCell(resValue5);
Label resValue6= new Label(7,Row,resTagValue6);
sheet1.addCell(resValue6);
Label resValue7= new Label(8,Row,resTagValue7);
sheet1.addCell(resValue7);
Label resValue8= new Label(9,Row,resTagValue8);
sheet1.addCell(resValue8);      
    }   
}catch (Exception e) {log.info(e)}
finally{
    copy.write();
    copy.close();
    workbook.close();
}
log.info("Testing Over")
1
excelxpathsoapgroovyscripting
From the documentation for SoapUI and JExcelAPI, it appears that resholder.getNodeValues() returns an array of Strings. You probably want resholder.getNodeValue() instead, since jxl.write.Label has a constructor of the form Label(int, int, String) - Michael Easter
Thank you @MichaelEaster, the issue has been solved now... however still the values are not placed in the soap request from excel sheet and also the response are not added back to the sheet when i trigger the code in groovy script. - Sambid Rijal

1 Answers

0
votes

Try the below code. It will fix your issue :

import com.eviware.soapui.support.XmlHolder
import java.io.File;
import java.io.IOException;
import jxl.*;
import jxl.read.biff.BiffException;
import jxl.write.*;
log.info("Testing Started")
def reqOperationName = "some service"
def inputDataFileName = "D:/sample/temp8.xls"
def inputDataSheetName = "Sheet1"
Workbook workbook = Workbook.getWorkbook(new File(inputDataFileName));
WritableWorkbook copy = Workbook.createWorkbook(new File(inputDataFileName),workbook);
WritableSheet sheet1 = copy.getSheet(inputDataSheetName);
log.info("Testing1 Started")
def groovyUtils = new com.eviware.soapui.support.GroovyUtils(context)
String xmlResponse = reqOperationName+"#Request"
def reqholder = groovyUtils.getXmlHolder(xmlResponse)
try{
    rowcount = sheet1.getRows();
    colcount = sheet1.getColumns();

for(Row in 1..rowcount-1){
       String reqTagName = sheet1.getCell(0,0).getContents()
        def TagCount = reqholder["count(//*:"+reqTagName+")"]
        if(TagCount!=0){
        String reqTagValue = sheet1.getCell(0,Row).getContents()

            if(reqTagValue!=null && !reqTagValue.isEmpty() && reqTagValue!="")
                {

                    reqholder = groovyUtils.getXmlHolder(xmlResponse)
                    reqholder.setNodeValue("//*:"+reqTagName, reqTagValue)
                 reqholder.updateProperty()    
                testRunner.runTestStepByName(reqOperationName)

                def resholder = groovyUtils.getXmlHolder(reqOperationName+"#Response")

                resTagValue1= resholder.getNodeValue("//*:email") 

                //Write Response into excel sheet
                Label resValue1= new Label(2,Row,resTagValue1);
                sheet1.addCell(resValue1);  
                }
            }



    }   
}catch (Exception e) {log.info(e)}
finally{
    copy.write();
    copy.close();
    workbook.close();
}
log.info("Testing Over")