Issue with excel sheet while trying to fetch the values from groovy script

0
votes

If i place getCell(0,0) in String reqTagName = sheet1.getCell(0,0).getContents() for the below code then, it is only executing the first value from the sheet. But, if I place getCell(0,Row) for the same String reqTagName = sheet1.getCell(0,0).getContents() and change the values in the excel sheet from number to string then it works as expected. But it is not working when I pass number as request value in the sheet. I have tried changing the data type from String to any other still its not working....

I have pasted the code below:

import com.eviware.soapui.support.XmlHolder
import java.io.File
import java.io.IOException
import jxl.*
import jxl.read.biff.BiffException
import jxl.write.*
import jxl.write.Label
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))
Sheet  sheet1 = workbook.getSheet(inputDataSheetName)
def responsesCount = 0

String[] Responses = null
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()
     Responses = new String[rowcount-1]
    responsesCount = rowcount-1
    for(Row in 1..rowcount-1){

    String reqTagName = sheet1.getCell(0,Row).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)
    reqholder = groovyUtils.getXmlHolder(reqOperationName+"#Response")
    Responses[Row-1] = reqholder.getPrettyXml().toString()
    log.info Responses[Row-1]

    }
}
catch (Exception e) {log.info(e)}
finally{
    workbook.close()
}
Workbook existingWorkbook = Workbook.getWorkbook(new File(inputDataFileName));
WritableWorkbook workbookCopy = Workbook.createWorkbook(new File(inputDataFileName), existingWorkbook);

try
{
    WritableSheet sheetToEdit = workbookCopy.getSheet("MPI");
    WritableCell cell;
    for (int i =1;i<Responses.size();i++)
    {
    def resholder = groovyUtils.getXmlHolder(Responses[i])

    resTagValue1= resholder.getNodeValue("//*:productID")
    Label l = new Label(2, i, resTagValue1.toString());
    cell = (WritableCell) l;
    sheetToEdit.addCell(cell);

    resTagValue2= resholder.getNodeValue("//*:accountNumber")
    Label m = new Label(3, i, resTagValue2.toString());
    cell = (WritableCell) m;
    sheetToEdit.addCell(cell);

    resTagValue3= resholder.getNodeValue("//*:insuranceCategory")
    Label n = new Label(4, i, resTagValue3.toString());
    cell = (WritableCell) n;
    sheetToEdit.addCell(cell);

    resTagValue4= resholder.getNodeValue("//*:imei")
    Label o = new Label(5, i, resTagValue4.toString());
    cell = (WritableCell) o;
    sheetToEdit.addCell(cell);

    resTagValue5= resholder.getNodeValue("//*:handsetMake")
    Label p = new Label(6, i, resTagValue5.toString());
    cell = (WritableCell) p;
    sheetToEdit.addCell(cell);

    resTagValue6= resholder.getNodeValue("//*:handsetModel")
    Label q = new Label(7, i, resTagValue6.toString());
    cell = (WritableCell) q;
    sheetToEdit.addCell(cell);

    resTagValue7= resholder.getNodeValue("//*:insurancePolicyName")
    Label r = new Label(8, i, resTagValue7.toString());
    cell = (WritableCell) r;
    sheetToEdit.addCell(cell);

    resTagValue8= resholder.getNodeValue("//*:insuranceStartTimestamp")
    Label s = new Label(9, i, resTagValue8.toString());
    cell = (WritableCell) s;
    sheetToEdit.addCell(cell);

    resTagValue9= resholder.getNodeValue("//*:insuranceEndTimestamp")
    Label t = new Label(10, i, resTagValue9.toString());
    cell = (WritableCell) t;
    sheetToEdit.addCell(cell);
    }
}
catch (Exception e) {log.info(e)}
finally{
     workbookCopy.write();
 workbookCopy.close();
 existingWorkbook.close();
}
log.info("Testing Over")

I got to know that the error is with the excel sheet. Should there be any change in the code or some implementation with the excel sheet??

Any help would be highly appreciated.

Thank you,

Edited code after changes implemented

import com.eviware.soapui.support.XmlHolder
import java.io.File
import java.io.IOException
import jxl.*
import jxl.read.biff.BiffException
import jxl.write.*
import jxl.write.Label
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))
Sheet  sheet1 = workbook.getSheet(inputDataSheetName)
def responsesCount = 0


String[] Responses = null
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()
     Responses = new String[rowcount-1]
    responsesCount = rowcount-1
    for(Row in 1..rowcount-1){

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

    //test the request
    testRunner.runTestStepByName(reqOperationName)
    reqholder = groovyUtils.getXmlHolder(reqOperationName+"#Response")
    Responses[Row-1] = reqholder.getPrettyXml().toString()
    log.info Responses[Row-1]

    }
}
catch (Exception e) {log.info(e)}
finally{
    workbook.close()
}
Workbook existingWorkbook = Workbook.getWorkbook(new File(inputDataFileName));
WritableWorkbook workbookCopy = Workbook.createWorkbook(new File(inputDataFileName), existingWorkbook);

try
{
    WritableSheet sheetToEdit = workbookCopy.getSheet("MPI");
    WritableCell cell;
    for (int i =1;i<Responses.size();i++)
    {
    def resholder = groovyUtils.getXmlHolder(Responses[i])

    resTagValue1= resholder.getNodeValue("//*:productID")
    Label l = new Label(2, i, resTagValue1.toString());
    cell = (WritableCell) l;
    sheetToEdit.addCell(cell);

    resTagValue2= resholder.getNodeValue("//*:accountNumber")
    Label m = new Label(3, i, resTagValue2.toString());
    cell = (WritableCell) m;
    sheetToEdit.addCell(cell);

    resTagValue3= resholder.getNodeValue("//*:insuranceCategory")
    Label n = new Label(4, i, resTagValue3.toString());
    cell = (WritableCell) n;
    sheetToEdit.addCell(cell);

    resTagValue4= resholder.getNodeValue("//*:imei")
    Label o = new Label(5, i, resTagValue4.toString());
    cell = (WritableCell) o;
    sheetToEdit.addCell(cell);

    resTagValue5= resholder.getNodeValue("//*:handsetMake")
    Label p = new Label(6, i, resTagValue5.toString());
    cell = (WritableCell) p;
    sheetToEdit.addCell(cell);

    resTagValue6= resholder.getNodeValue("//*:handsetModel")
    Label q = new Label(7, i, resTagValue6.toString());
    cell = (WritableCell) q;
    sheetToEdit.addCell(cell);

    resTagValue7= resholder.getNodeValue("//*:insurancePolicyName")
    Label r = new Label(8, i, resTagValue7.toString());
    cell = (WritableCell) r;
    sheetToEdit.addCell(cell);

    resTagValue8= resholder.getNodeValue("//*:insuranceStartTimestamp")
    Label s = new Label(9, i, resTagValue8.toString());
    cell = (WritableCell) s;
    sheetToEdit.addCell(cell);

    resTagValue9= resholder.getNodeValue("//*:insuranceEndTimestamp")
    Label t = new Label(10, i, resTagValue9.toString());
    cell = (WritableCell) t;
    sheetToEdit.addCell(cell);
    }
}
catch (Exception e) {log.info(e)}
finally{
     workbookCopy.write();
 workbookCopy.close();
 existingWorkbook.close();
}
log.info("Testing Over")

In out put log I can see that it is taking the first MSISDN but after that it is taking some junk fields.

Log Output is mentioned below for every execution.

Mon Dec 11 16:44:18 IST 2017:INFO:447803000269

Mon Dec 11 16:44:18 IST 2017:INFO:[Ljava.lang.String;@8ddf43

Mon Dec 11 16:44:18 IST 2017:INFO:[Ljava.lang.String;@7bd8d3

Mon Dec 11 16:44:19 IST 2017:INFO:[Ljava.lang.String;@12dda68

2
excelgroovysoapui
I think you have a formatting problem, but it is hard to be sure when we don't know anything about the data you have in your Excel file and the data you'd like to pass to SOAPUI. - sensei
I prefer .csv format over excel sheet and it is very easy to use. Of course, you can edit or view using excel or even use any text editor as well. Would you mind using .csv and solution based on it? - Rao
Changing to .csv has given me some error which i am never aware of it. I have shared the error log in the edit section with the error code @Rao - Sambid Rijal
Would you mind showing your sample data? Please edit the question, update the changed code and error. - Rao
You can find sample data screenshot and the updated code below at the ending of the page. I have changed the document from .xls to .csv which is not shown in the code but all the other parts are same. The error code and error logs are also pasted below at the ending of the page. - Sambid Rijal

2 Answers

0
votes

Try this code change below and let me know if it worked :

import com.eviware.soapui.support.XmlHolder
import java.io.File
import java.io.IOException
import jxl.*
import jxl.read.biff.BiffException
import jxl.write.*
import jxl.write.Label
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))
Sheet  sheet1 = workbook.getSheet(inputDataSheetName)


def myList = new ArrayList<String>();
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)
                    log.info "extracted value : " + reqTagValue
                reqholder.setNodeValue("//*:"+reqTagName, reqTagValue)
                reqholder.updateProperty()        
                log.info "node value : " + reqholder.getNodeValue("//*:"+reqTagName)
                //test the request
                testRunner.runTestStepByName(reqOperationName)
                reqholder = groovyUtils.getXmlHolder(reqOperationName+"#Response")
                myList.add(reqholder.getPrettyXml().toString())
                log.info myList[Row-1]
                }                      
    }

    }
}
catch (Exception e) {log.info(e)}
finally{
    workbook.close()
}
Workbook existingWorkbook = Workbook.getWorkbook(new File(inputDataFileName));
WritableWorkbook workbookCopy = Workbook.createWorkbook(new File(inputDataFileName), existingWorkbook);

try
{
    WritableSheet sheetToEdit = workbookCopy.getSheet(inputDataSheetName);
    WritableCell cell;
    for (int i =1;i<myList.size();i++)
    {
    def resholder = groovyUtils.getXmlHolder(myList[i])

    resTagValue1= resholder.getNodeValue("//*:productID")
    Label l = new Label(2, i+1, resTagValue1.toString());
    cell = (WritableCell) l;
    sheetToEdit.addCell(cell);

    resTagValue2= resholder.getNodeValue("//*:accountNumber")
    Label m = new Label(3, i+1, resTagValue2.toString());
    cell = (WritableCell) m;
    sheetToEdit.addCell(cell);

    resTagValue3= resholder.getNodeValue("//*:insuranceCategory")
    Label n = new Label(4, i+1, resTagValue3.toString());
    cell = (WritableCell) n;
    sheetToEdit.addCell(cell);

    resTagValue4= resholder.getNodeValue("//*:imei")
    Label o = new Label(5, i+1, resTagValue4.toString());
    cell = (WritableCell) o;
    sheetToEdit.addCell(cell);

    resTagValue5= resholder.getNodeValue("//*:handsetMake")
    Label p = new Label(6, i+1, resTagValue5.toString());
    cell = (WritableCell) p;
    sheetToEdit.addCell(cell);

    resTagValue6= resholder.getNodeValue("//*:handsetModel")
    Label q = new Label(7, i+1, resTagValue6.toString());
    cell = (WritableCell) q;
    sheetToEdit.addCell(cell);

    resTagValue7= resholder.getNodeValue("//*:insurancePolicyName")
    Label r = new Label(8, i+1, resTagValue7.toString());
    cell = (WritableCell) r;
    sheetToEdit.addCell(cell);

    resTagValue8= resholder.getNodeValue("//*:insuranceStartTimestamp")
    Label s = new Label(9, i+1, resTagValue8.toString());
    cell = (WritableCell) s;
    sheetToEdit.addCell(cell);

    resTagValue9= resholder.getNodeValue("//*:insuranceEndTimestamp")
    Label t = new Label(10, i+1, resTagValue9.toString());
    cell = (WritableCell) t;
    sheetToEdit.addCell(cell);
    }
}
catch (Exception e) {log.info(e)}
finally{
     workbookCopy.write();
 workbookCopy.close();
 existingWorkbook.close();
}
log.info("Testing Over")
0
votes

Try these steps and let me know if it fixed the issue :

Step 1 : Open Excel WorkBook

Step 2 : Select the column values which holds numbers as shown in the pic below and do a right click to bring out the options View here

Step 3 : Click on Format Cells and it will open a window view here

Step 4 : Click on Number as shown in the pic below view here

Step 5 : Go to Decimal places on the right hand side and click on the down arrow as shown in the pic below view here

Step 6 : Click on the down arrow until you set the decimal places to 0

Step 7 : Go to the Negative numbers as shown in the pic and select the second option view here

Step 8 : Click on OK and close the window.

Step 9 : Save excel sheet and run the script. You problem should be fixed

Let me know if it worked :)