尝试从Groovy脚本中获取值时出现Excel工作表问题 [英] Issue with excel sheet while trying to fetch the values from groovy script
本文介绍了尝试从Groovy脚本中获取值时出现Excel工作表问题的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
如果我将 getCell(0,0)放在以下代码的字符串reqTagName = sheet1.getCell(0,0).getContents()中，则它将仅执行床单.但是，如果我将 getCell(0，Row)放置在相同的字符串reqTagName = sheet1.getCell(0,0).getContents()并将excel工作表中的值从数字更改为字符串，那么它将可以正常工作.但是当我在表中传递数字作为请求值时，它不起作用.我试过将数据类型从String更改为其他类型，但仍然无法正常工作....
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....
我粘贴了以下代码:
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")
我知道错误是由Excel工作表造成的. excel表单中的代码或某些实现应该进行任何更改吗?
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??
任何帮助将不胜感激.
谢谢
实施更改后编辑的代码
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")
在输出日志中，我可以看到它正在使用第一个MSISDN，但是之后它正在使用一些垃圾字段.
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.
周一12月11日16:44:18 IST 2017:INFO:447803000269
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;@8ddf43
Mon Dec 11 16:44:18 IST 2017:INFO:[Ljava.lang.String; @ 7bd8d3
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
Mon Dec 11 16:44:19 IST 2017:INFO:[Ljava.lang.String;@12dda68
推荐答案
在下面尝试更改此代码，让我知道它是否有效:
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")
这篇关于尝试从Groovy脚本中获取值时出现Excel工作表问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
