如何使用groovy编辑现有的Excel表格，而无需创建相同的Excel的副本 [英] How to edit the existing excel sheet using groovy, without creating a copy of the same excel

问题描述

想要使用groovy编辑现有的excel工作表，而不需要创建相同的excel
的副本想要更新结果中的数据被提取的同一个excel，只有输出应该写入不同的单元格。

Want To edit the existing excel sheet using groovy, without creating a copy of the same excel Want to Update the Results in the same excel from which the data is fetched only the output should be written in different cells.

我是新来的Soap用户界面。

I m new to Soap UI.

请找到下面的代码。

import java.io.*
import jxl.*
import jxl.write.*
// Read data from excel

def f=new File("D:\\Soap.xls");
def wk= Workbook.getWorkbook(f)
def ws=wk.getSheet("Sheet1")

def count = ws.getRows()
for (int i=1;i<count;i++) {       

      Cell c1=ws.getCell(0,i)

      A=Integer.parseInt(c1.getContents())

      Cell c2=ws.getCell(1,i)

      B=Integer.parseInt(c2.getContents())
      sum = A+B

      log.info sum 

 //---------For Writing the data-------------- 

    WritableWorkbook workbook1 = Workbook.getWorkbook(f) 
    WritableSheet sheet1 = workbook1.getSheet("Sheet1")
    log.info(sheet1.isHidden())
    Label label = new Label(3,i,"Addition")
    sheet1.addCell(label)
    log.info label
    workbook1.write()
    }

推荐答案

您可以通过先从Excel工作簿中读取数据然后再执行写入完成阅读操作后的任务，如下所示：

You can do that with reading data from excel workbook first and then doing the write related tasks after you complete reading operations like 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 = "someservice"
def inputDataFileName = "D:/sample/temp6.xls"

def inputDataSheetName = "Sheet1"
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)
def reqholder = groovyUtils.getXmlHolder(reqOperationName+"#Request")
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+")"]
        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("Sheet1");
    WritableCell cell;
    for (int i =0;i<Responses.size();i++)
    {
    def resholder = groovyUtils.getXmlHolder(Responses[i])
    resTagValue1= resholder.getNodeValue("//*:phoneNumber")
    Label l = new Label(3, i, resTagValue1.toString());
    cell = (WritableCell) l;
    sheetToEdit.addCell(cell);
    }

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

}

log.info("Testing Over")

这篇关于如何使用groovy编辑现有的Excel表格，而无需创建相同的Excel的副本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！