groovy 从excel文件读取并加载到SOAP UI请求中

hmae6n7t  于 2023-04-29  发布在  其他
关注(0)|答案(2)|浏览(127)

我对Java和Groovy还很陌生。但是,我需要从excel文件中读取数据,并将它们加载到SOAPUI测试套件中。我跟随谷歌的几个链接,并试图实现相同的,但面临错误。
下载了POI 41.0 jar文件并将它们复制到SOAPUI/bin/ext文件夹。在SOAPUI中,我为groovy脚本添加了一个步骤(如下所述)。

import org.apache.poi.ss.usermodel.*                                                  
import org.apache.poi.hssf.usermodel.*

import org.apache.poi.xssf.usermodel.*

impot org.apache.poi.ss.util.*

def fs = new FileInputStream("FILE LOCATION")

Workbook wb = WorkbookFactory.create(fs);

def ws = wb.getSheet("Sheet1")

def r = ws.getPhysicalNumberOfRows()

log.info "==========="+r

它应该会返回行数。但我得到了一个错误。

org.apache.poi.ooxml.POIXMLException: Could not initialize calss org.apache.poi.ooxml.POIXMLTypeLoader    Error at line 7

先谢谢你的帮助

zz2j4svz

zz2j4svz1#

在SmartBear论坛和其他地方有很多这类错误,但在soapUI和POI的不同版本中没有多少一致的解决方案。当在soapUI之外运行时,您的代码可以正常工作,这表明在soapUI和POI之间存在某种JAR版本冲突。
所以,有几个选择:

  • 如果您能负担得起,SoapUI Pro在其数据循环测试步骤中内置了Excel功能。
  • 尝试降级到POI和soapUI的早期版本。代码与您的代码基本相同worked with soapUI 5.3 and POI 3.14
  • 使用简单的东西。由于您使用的是Excel,因此您应该能够将数据作为CSV获取,并使用诸如public Object splitEachLine(String regex,Closure closure)之类的东西来仅使用Groovy处理CSV数据。
  • 创建您自己的Groovy或Java项目,其中包含POI依赖项沿着一个简单的接口来调用您所需的功能。例如,请参见Library conflict in SoapUI
rdlzhqv9

rdlzhqv92#

我有一个很好用的变通方法。xls文件(有问题。xlsx文件)。
首先下载jxl.jar通过Groovy脚本访问excel文件。来源:https://sourceforge.net/projects/jexcelapi/files/jexcelapi
下载最新版本(当前版本:2.6.12).解压缩文件,复制JXL。两个人的jar:

Contents/java/app/bin/ext
Contents/java/app/lib

现在添加一个测试步骤-〉属性并声明要读取的数据为变量(property 1,property 2)。
使用Groovy脚本测试步骤中的脚本(添加了将响应结果写入另一个excel文件的功能):

import jxl.*
import jxl.write.*
import jxl.write.Number

// Specify the path to the input Excel File
def inpFile = new File("path/inputFile.xls")

// Specify the path to the output Excel File
def outFile = new File("path/outputFile.xls")
// Create a workbook object for the input Excel file
Workbook inpWorkbook = Workbook.getWorkbook(inpFile)
// Get the sheet containing the property values, 1st sheet here
Sheet propSheet = inpWorkbook.getSheet(0)
// Get no of rows
def numRows = propSheet.getRows()
// Create Workbook obj for output Excel File
WritableWorkbook outWorkbook = Workbook.createWorkbook(outFile)
// Create a "Res" sheet for results
Writable resSheet = outWorkbook.createSheet("Res", 0)

// Create labels for the result
def label = new Label(0, 0, "Test Step")
resSheet.addCell(label)
def label = new Label(1, 0, "Status")
resSheet.addCell(label)

// Loop through each row of the sheet containing data to be read
for (i in 1..numRows-1) {
    // Read the property values from current row of excel sheet 
    // 1st column has nodeVal1, 2nd column has nodeVal2
    def property1 = propSheet.getCell(0, i).getContents()
    def property2 = propSheet.getCell(1, i).getContents()
    
    // Get the test case and test step
    def testCase = testRunner.testCase
    def testStep = testCase.getTestStepByName("Properties")
    def testRequestStep = testCase.getTestStepByName("test_request name")
   
    // Set property values for the test step
    testStep.setPropertyValue("property1", property1)
    testStep.setPropertyValue("property2", property2)

    // Execute the test request
    testRequestStep.run(testRunner, context)

    // Get the SOAP response of the test request
    def resp = testRequestStep.testRequest.response

    // Create a cell for the test step
    def cell = new Label(0, i, testRequestStep.name)
    resSheet.addCell(cell)
    // Create columns for retrieving response Node values
    cell = new Label(2, 0, "Node Val1")
    resSheet.addCell(cell)
    cell = new Label(3, 0, "Node Val2")
    resSheet.addCell(cell)

    if (resp.responseHeaders.toString().contains("HTTP/1.1 200 OK")) {
        label = new Label(1, i, "Pass")
        resSheet.addCell(label)
        def xmlResp = new XmlSlurper().parseText(response.getContentAsString())
        // NodeName1 and NodeName2 are the reqd nodes from the successful response
        def nodeVal1 = xmlResp.'**'.find { it.name() == 'NodeName1' }?.text()
        def nodeVal2 = xmlResp.'**'.find { it.name() == 'NodeName2' }?.text()
        cell = new Label(2, i, nodeVal1)
        resSheet.addCell(cell)
        cell = new Label(3, i, nodeVal2)
        resSheet.addCell(cell)
    } else if (resp.responseHeaders.toString().contains("HTTP/1.1 500 Internal Server Error")) {
        label = new Label(1, i, "Fail")
        resSheet.addCell(label)
    }
}
// Write and close the Workbooks
outWorkbook.write()
outWorkbook.close()
inpWorkbook.close()

您可以修改脚本以检查各种响应类型。

相关问题