在 SOAPUI Groovy 中的现有 excel 中写入结果失败

Question

我需要在脚本读取 FOR 循环中的参数的同一个 excel 工作表中编写结果（通过/失败）。它抛出一个错误：

org.codehaus.groovy.runtime.typehandling.GroovyCastException: Cannot cast object 'jxl.read.biff.WorkbookParser@3da0525b' with class 'jxl.read.biff.WorkbookParser' to class 'jxl.write.WritableWorkbook' error at line: 16

我的代码：

import jxl.*;
import jxl.write.*;
import java.io.*;
import groovy.json.JsonSlurper
//Get project path
def groovyUtils = new com.eviware.soapui.support.GroovyUtils(context)
def projectPath = groovyUtils.projectPath
def testCaseName = testRunner.testCase.name
//Read excel file and get the input value
	
	WritableWorkbook xlwb = Workbook.getWorkbook(new File ("${projectPath}\${testCaseName}.xls"))
	Sheet inputxlsh = xlwb.getSheet(0)
	inputRowCount = inputxlsh.getRows();
	WritableSheet outputxlsh = xlwb.getSheet(1)
	outputRowCount = outputxlsh.getRows();
	log.info "Executing Test Case " + testCaseName
	log.info "Total records to send API request to webservice from the file : " +  inputRowCount -1
	
	for (i=0;i<inputRowCount-1;i++)
	{
		Cell requestParam1 = inputxlsh.getCell(0,i+1)
		affkey = requestParam1.getContents()
		Cell requestParam2 = inputxlsh.getCell(1,i+1)
		etid = requestParam2.getContents()
				
		def soapTestCase = context.testCase
//Set the request property value (Parameter)
		requestPropertyVariable = soapTestCase.getTestStepByName("requestProperty")
		requestPropertyVariable.setPropertyValue("affkey",affkey)
		requestPropertyVariable.setPropertyValue("etid",etid)
		
		log.info "Reading record " + (i+1) + " from input file"
		log.info "Sending request with affkey " + affkey
		log.info "Sending request with etid " + etid
		
//Post a request to webservice
		def responseContent = testRunner.runTestStepByName("showetidRequest").getResponse()
		def responseText = responseContent.getContentAsString()
//Save the output file
		def fileObj = new File("${projectPath}\API_Response\${testRunner.testCase.name}\${etid}_Response.txt")
		saveToFile(fileObj, responseText)
//Get the response value
		JsonSlurper jsonResponseContent = new JsonSlurper()
		def jsonResponseObject = jsonResponseContent.parseText(responseText)
//Validate results
			Cell headerParam1 = outputxlsh.getCell(0,0)
			Cell headerParam2 = outputxlsh.getCell(1,0)
			Cell headerParam3 = outputxlsh.getCell(2,0)
			Cell headerParam4 = outputxlsh.getCell(3,0)
			Cell headerParam5 = outputxlsh.getCell(4,0)
			Cell headerParam6 = outputxlsh.getCell(5,0)
			
		for (k = 0; k < outputRowCount-1; k++) {	
			Cell responseParam1 = outputxlsh.getCell(0,k+1)
			Cell responseParam2 = outputxlsh.getCell(1,k+1)
			Cell responseParam3 = outputxlsh.getCell(2,k+1)
			Cell responseParam4 = outputxlsh.getCell(3,k+1)
			Cell responseParam5 = outputxlsh.getCell(4,k+1)
			Cell responseParam6 = outputxlsh.getCell(5,k+1)
			
			expectedAffiliatesWithContent = responseParam1.getContents()
			expectedEntityName = responseParam2.getContents()
			expectedName = responseParam3.getContents()
			expectedSaleMessageId = responseParam4.getContents()
			expectedTitle = responseParam5.getContents()
			expectedetid = responseParam6.getContents()
				
			if(etid==expectedetid){
			responseAffiliatesWithContent = jsonResponseObject.AffiliatesWithContent.getAt(0).getAt(0)
			responseEntityName = jsonResponseObject.Genre.EntityName.getAt(0)
			responseName = jsonResponseObject.Genre.Name.getAt(0)
			responseSaleMessageId = jsonResponseObject.SaleMessage.Id.getAt(0)
			responseTitle = jsonResponseObject.Title.getAt(0)
									
				log.info responseAffiliatesWithContent
				log.info responseEntityName
				log.info responseName
				log.info responseSaleMessageId
				log.info responseTitle
				if (responseAffiliatesWithContent==expectedAffiliatesWithContent&&responseEntityName==expectedEntityName&&responseName==expectedName&&responseSaleMessageId==expectedSaleMessageId&&
					responseTitle==expectedTitle)
					{
					log.info "The data is matched for record " + (k +1) + " hence test case passed "
		                   Label l = new Label(7, k +1, "Pass");
		                   outputxlsh.addCell(l);
		                   xlwb.write(); 					
					}
					else {
					log.info "The data is matched for record " + (k +1) + " hence test case failed "
					}
			}
		}
		}
//Clear Property
	requestPropertyVariable.setPropertyValue("affkey","")
	requestPropertyVariable.setPropertyValue("etid","")
	
//Write file method
	def saveToFile(fileObj, content) {
		if (!fileObj.parentFile.exists()) {
	         fileObj.parentFile.mkdirs()
	    }
		fileObj.write(content) 
	     log.info "Response for etid " + etid + " is stored in " + "${etid}_Response.txt"
		assert fileObj.exists(), "${fileObj.name} not created"
	}

Answer 1

如果你想要一个可写的副本调用createWorkbook函数

WritableWorkbook xlwb = Workbook.createWorkbook(new File ("${projectPath}\${testCaseName}.xls"))

如果您不想将可写副本保存在Workbook对象中

Workbook workbook = Workbook.getWorkbook((new File ("${projectPath}\${testCaseName}.xls"))

Answer 2

我已经根据以下代码达到了要求

    def inptDataWb = new HSSFWorkbook(xlwb);
    def inputxlsh = inptDataWb.getSheetAt(2);
    wrtResult = outputxlsh.getRow(k+1).getCell(3);
    wrtResult.setCellValue("P");
    wrtResult = outputxlsh.getRow(k+1).getCell(5);
    wrtResult.setCellValue("");
    inptDataWb.write(xlOwb);