如何使用groovy编辑现有的Excel表格，而无需创建相同的Excel的副本

本文介绍了如何使用groovy编辑现有的Excel表格，而无需创建相同的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")