Issue with excel sheet while trying to fetch the v

Question

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

Answer 1

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

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

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

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

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

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 :)

Answer 2

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")