如何使用Groovy脚本在现有的Excel表单中编写响应 [英] How to write the response in existing excel sheet using Groovy script

问题描述

最初我已经提出了几个问题。这与我的要求的提高有关。我试图在Excel表格中保存响应。然而，答复正在写两次。

 导入jxl。* 
导入jxl.write 。* 
 
 def groovyUtils = new com.eviware.soapui.support.GroovyUtils（context）
 def holder = groovyUtils.getXmlHolder（Step2＃Response）
 def request = groovyUtils.getXmlHolder（Step2＃Request）
 
 
文件fr = new File（C：\\ Users \\Documents\\Groovy Scripts\\\ \\响应\\输出.xls）
 
工作簿wk = Workbook.getWorkbook（fr）; 
 WritableWorkbook wr = Workbook.createWorkbook（fr，wk）; 
 
 WritableSheet sheet = wr.getSheet（0）; 
 
 
 def r = sheet.getRows（）; 
 
 log.inforows：$ {r}
 
 xPath1 =// *：description / text（）
 xPath2 =// * ：OrderId / text（）
 xPath3 =// *：m / text（）
 
标签orderid =新标签（0，r，request.getNodeValue（xPath2））; 
 sheet.addCell（orderid）; 
 Label mode = new Label（1，r，request.getNodeValue（xPath3））; 
 sheet.addCell（m）; 
标签描述=新标签（2，r，holder.getNodeValue（xPath1））; 
 sheet.addCell（description）; 
 Label response = new Label（3，r，context.expand（'$ {Step2＃Response}'））; 
 sheet.addCell（response）; 
 
 wr.write（）; 
 
 wr.close（）; 
  

链接到之前的帖子： Groovy脚本读取一个xml文件并用文件内容更新下一步请求

解决方案

您可以尝试下面的代码读取和写入。它工作的很好......你所要做的就是将你的excel表格保存为.xls格式。

  import com.eviware .soapui.support.XmlHolder 
 import java.io.File 
 import java.io.IOException 
 import jxl。* 
 import jxl.read.biff.BiffException 
导入jxl.write。* 
导入jxl.write.Label 
 log.info（Testing Started）
 def reqOperationName =操作名称
 def inputDataFileName =FileLocation / filename.xls
 def inputDataSheetName =数据表名称
 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）
尝试{
 rowcount = sheet1.getRows（）
 colcount = sheet1.getColumns（）
 
（1..rowcount-1中的行）{
 
字符串reqTagName = sheet1.getCell（0,0）.getContents（）
 
 def TagCount = reqholder [count（// *：+ reqTagName +）] 
 $ b $如果（TagCount！= 0）{
 String reqTagValue = sheet1.getCell（0，Row）.getContents（）
 if（reqTagValue！= null&&！reqTagValue.isEmpty（）& & reqTagValue！=）
 {
 reqholder = groovyUtils.getXmlHolder（xmlResponse）
 log.infoextracted value：+ reqTagValue 
 reqholder.setNodeValue（// *：+ reqTagName，reqTagValue）
 reqholder.updateProperty（）
 log.infonode value：+ reqholder.getNodeValue（// *：+ reqTagName）
 //测试请求
 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）; 
 
尝试
 {
 WritableSheet sheetToEdit = workbookCopy.getSheet（inputDataSheetName）; 
 WritableCell单元; 
 for（int i = 1; i< myList.size（）; i ++）
 {
 def resholder = groovyUtils.getXmlHolder（myList [i]）
 
 resTagValue1 = resholder.getNodeValue（// *：Response Field Element1）
 Label l = new Label（2，i + 1，resTagValue1.toString（））; 
 cell =（WritableCell）l; 
 sheetToEdit.addCell（cell）; 
 
 resTagValue2 = resholder.getNodeValue（// *：Response Field Element2）
 Label m = new Label（3，i + 1，resTagValue2.toString（））; 
 cell =（WritableCell）m; 
 sheetToEdit.addCell（cell）; 
 
 resTagValue3 = resholder.getNodeValue（// *：Response Field Element3）
 Label n = new Label（4，i + 1，resTagValue3.toString（））; 
 cell =（WritableCell）n; 
 sheetToEdit.addCell（cell）; 
 
 resTagValue4 = resholder.getNodeValue（// *：Response Field Element4）
标签o =新标签（5，i + 1，resTagValue4.toString（））; 
 cell =（WritableCell）o; 
 sheetToEdit.addCell（cell）; 
 
 
 $ b catch（Exception e）{log.info（e）} 
 finally {
 workbookCopy.write（）; 
 workbookCopy.close（）; 
 existingWorkbook.close（）; 
 
 log.info（Testing Over）
  

让我知道如果它有效。 ：）

Initially I have placed couple of questions. This is related to then enhancement of my requirement. I am trying to save the response in excel sheet. However responses are getting written twice. Kindly help me where I am lagging to prevent the duplicate records writing.

import jxl.*
import jxl.write.*

def groovyUtils = new com.eviware.soapui.support.GroovyUtils( context )
def holder = groovyUtils.getXmlHolder("Step2#Response")
def request=groovyUtils.getXmlHolder("Step2#Request")


File fr = new File("C:\\Users\\Documents\\Groovy Scripts\\response\\output.xls")

Workbook wk = Workbook.getWorkbook(fr);
WritableWorkbook wr = Workbook.createWorkbook(fr, wk);

WritableSheet sheet= wr.getSheet(0);


def r = sheet.getRows(); 

log.info "rows: ${r}"

xPath1 = "//*:description/text()"     
xPath2 = "//*:OrderId/text()"
xPath3 = "//*:m/text()"

Label orderid = new Label(0,r ,request.getNodeValue(xPath2)); 
sheet.addCell(orderid);
Label mode = new Label(1,r ,request.getNodeValue(xPath3)); 
sheet.addCell(m);
Label description = new Label(2,r , holder.getNodeValue(xPath1));
sheet.addCell(description);
Label response = new Label(3, r, context.expand('${Step2#Response}')); 
sheet.addCell(response); 

wr.write();

wr.close();

Link to earlier post: Groovy script to Read an xml file and update next step request with file contents

解决方案

You can try the below code for both reading and writing. It works pretty well... All you have to do is save your excel sheet in .xls format.

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 = "Operation Name"
def inputDataFileName = "FileLocation/filename.xls"
def inputDataSheetName = "Datasheet name"
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("//*:Response Field Element1")
    Label l = new Label(2, i+1, resTagValue1.toString());
    cell = (WritableCell) l;
    sheetToEdit.addCell(cell);

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

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

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

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

Let me know if it works. :)

这篇关于如何使用Groovy脚本在现有的Excel表单中编写响应的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！