plaidshirt
6 years agoContributor
Groovy to get values from xlsx with Apache POI
I try to read values from a xlsx file to properties in 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")
}
I copied following dependency jar files to /bin/ext
folder in SoapUI root:
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
I got error message when script is executed:
org.apache.poi.ooxml.POIXMLException: org.apache.xmlbeans.XmlOptions.setEntityExpansionLimit(I)Lorg/apache/xmlbeans/XmlOptions; error at line: 39
Line 39: List rows = excelReader.readData();