cancel
Showing results for 
Search instead for 
Did you mean: 

Groovy to get values from xlsx with Apache POI

Occasional Contributor

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

9 REPLIES 9
Super Contributor

Re: Groovy to get values from xlsx with Apache POI

Instead of using groovy here. You can use Java code to get the values from xlsx with Apache POI. You can see several examples on internet for the same.

https://www.mkyong.com/java/apache-poi-reading-and-writing-excel-file-in-java/

Occasional Contributor

Re: Groovy to get values from xlsx with Apache POI

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

 

Community Hero

Re: Groovy to get values from xlsx with Apache POI

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


Regards,
Rao.
Occasional Contributor

Re: Groovy to get values from xlsx with Apache POI

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

Could you offer me a native solution please?

Community Hero

Re: Groovy to get values from xlsx with Apache POI

@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.


Regards,
Rao.
Occasional Contributor

Re: Groovy to get values from xlsx with Apache POI

Sure, I try this, but got error message when executed: http://prntscr.com/m9xh9m

Community Hero

Re: Groovy to get values from xlsx with Apache POI

What files did you copy under ext directory? And the script please


Regards,
Rao.
Occasional Contributor

Re: Groovy to get values from xlsx with Apache POI

I copied these jars: https://mvnrepository.com/artifact/com.opencsv/opencsv/4.4https://mvnrepository.com/artifact/com.xlson.groovycsv/groovycsv/1.3 .

Groovy script:

 

@Grab('com.xlson.groovycsv:groovycsv:1.3')
import static com.xlson.groovycsv.CsvParser.parseCsv
def csv = '''ID,Product
3,Shoe
1,Table'''

def data = parseCsv("C:\\_privat\\myfile.csv")
for(line in data) {
    println "ID=$line.ID, Product=$line.Product"
}

 

Community Hero

Re: Groovy to get values from xlsx with Apache POI

Very strange.

 

So, did a test with below jar files under SOAPUI_HOME/bin/ext directory

 

1. groovycsv 1.0 jar file

2. opencsv 3.0 jar file

you should be able to get both jars online

 

Here is script

First data refers to string data and second one from file reading (column names must match both cases)

 

Selection_024.png

 

Hope this helps!



Regards,
Rao.
New Here?
Join us and watch the welcome video:
Join the exciting event
SeptemberHubBub
Top Kudoed Authors
Join the September Hub-bub to show off, learn and win