groovy - Groovy 使用 Apache POI 从 xlsx 获取值
问题描述
我尝试将 xlsx 文件中的值读取到 SoapUI 5.4.0 中的属性。
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.util.*;
import org.apache.poi.ss.usermodel.*;
import java.io.*;
class ExcelReader {
def readData() {
def path = "C:\\docs\\data.xlsx";
InputStream inputStream = new FileInputStream(path);
Workbook workbook = WorkbookFactory.create(inputStream);
Sheet sheet = workbook.getSheetAt(0);
Iterator rowIterator = sheet.rowIterator();
rowIterator.next()
Row row;
def rowsData = []
while(rowIterator.hasNext()) {
row = rowIterator.next()
def rowIndex = row.getRowNum()
def colIndex;
def rowData = []
for (Cell cell : row) {
colIndex = cell.getColumnIndex()
rowData[colIndex] = cell.getRichStringCellValue().getString();
}
rowsData << rowData
}
rowsData
}
}
def groovyUtils = new com.eviware.soapui.support.GroovyUtils(context)
def myTestCase = context.testCase
ExcelReader excelReader = new ExcelReader();
List rows = excelReader.readData();
def d = []
Iterator i = rows.iterator();
while( i.hasNext()){
d = i.next();
myTestCase.setPropertyValue("From", d[0])
myTestCase.setPropertyValue("To", d[1])
testRunner.runTestStepByName( "ConversionRate")
}
我将以下依赖项 jar 文件复制到/bin/ext
SoapUI 根目录中的文件夹:
commons-collections4-4.2-javadoc.jar
commons-collections4-4.2.jar
commons-compress-1.18.jar
dom4j-1.6.1.jar
poi-4.0.1.jar
poi-examples-4.0.1.jar
poi-excelant-4.0.1.jar
poi-ooxml-4.0.1.jar
poi-ooxml-schemas-4.0.1.jar
poi-scratchpad-4.0.1.jar
xmlbeans-3.0.2.jar
执行脚本时收到错误消息:
org.apache.poi.ooxml.POIXMLException: org.apache.xmlbeans.XmlOptions.setEntityExpansionLimit(I)Lorg/apache/xmlbeans/XmlOptions; 行错误:39
第 39 行:List rows = excelReader.readData();
解决方案
我的建议是 - 利用可用的 Fillo jar 并连接到 XLS 以获取数据。 菲洛罐
需要以下导入:
import com.codoid.products.exception.FilloException;
import com.codoid.products.fillo.Connection;
import com.codoid.products.fillo.Fillo;
import com.codoid.products.fillo.Recordset;