Forum Discussion

plaidshirt's avatar
plaidshirt
Contributor
6 years ago

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();

    • plaidshirt's avatar
      plaidshirt
      Contributor

      I try that code, but got same error:


      org.apache.poi.ooxml.POIXMLException: org.apache.xmlbeans.XmlOptions.setEntityExpansionLimit(I)Lorg/apache/xmlbeans/XmlOptions;

       

      Currently imported dependencies:

      commons-collections4-4.2-javadoc.jar
      commons-collections4-4.2.jar
      commons-compress-1.18.jar
      dom4j-1.6.1.jar
      ooxml-schemas-1.4.jar
      ooxml-security-1.1.jar
      openxml4j-1.0-beta.jar
      poi-4.0.1.jar
      poi-contrib-3.6.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

       

  • nmrao's avatar
    nmrao
    Champion Level 3
    Does your data in multiple sheets of xlsx?
    If the data is simple, then you can easily deal with in groovy with or without any additional libraries.
    if you use below library it is **bleep** simple to read it. Also sample available there.
    https://github.com/xlson/groovycsv
    • plaidshirt's avatar
      plaidshirt
      Contributor

      Apache Ivy jar is imported, but groovycsv throws noclassdef error message.

      Could you offer me a native solution please?

      • nmrao's avatar
        nmrao
        Champion Level 3
        plaidshirt,

        You can download the groovy-csv jar file (available in github) along with dependencies, then copy thenm under SOAPUI_HOME/bin/ext and restart soapUI.

        Then try the given samples. You might have already observiced how simple to access the data from the sample.