cancel
Showing results for 
Search instead for 
Did you mean: 

Handling POIXML Exception in Groovy Scripting

Occasional Contributor

Handling POIXML Exception in Groovy Scripting

We are trying to read data from xlsx file using apache POI, we have all the jars files placed under soap/bin/ext. It gives us below error when running our groovy script.


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

 

Code:

 

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.usermodel.CellStyle;

FileInputStream inputStream = new FileInputStream("D:\\SOAP\\ExcelPOI.xlsx")

Workbook wb = WorkbookFactory.create(inputStream)
Sheet sheet1 = wb.getSheet("Sheet Name")
int rows = sheet1.getLastRowNum();
log.info(rows)

Tags (4)
11 REPLIES 11
Super Contributor

Re: Handling POIXML Exception in Groovy Scripting

poi.jar version?

Occasional Contributor

Re: Handling POIXML Exception in Groovy Scripting

Poi version is 4.1.0
Super Contributor

Re: Handling POIXML Exception in Groovy Scripting

Add this jar file also in bin/ext folder - ooxml-schemas-1.4.jar and then restart your soap ui. If you have already added some version of ooxml-schemas_version_name.jar.. Then remove that. for poi version 4.1.0 you have to use ooxml-schemas-1.4.jar.

Occasional Contributor

Re: Handling POIXML Exception in Groovy Scripting

Adding ooxml-schemas 1.4 in bin does not work. Do we need to keep same copy of jar in soap/bin/ext.

Restarted soap after placing jars but it gives same error.
Super Contributor

Re: Handling POIXML Exception in Groovy Scripting

Yeah.. actually, you need to keep it in bin/ext folder.. please try again.

Occasional Contributor

Re: Handling POIXML Exception in Groovy Scripting

It did not work by placing jar file in ext folder. We have added jars files in soap/bin/ext folder for us. We are using SOAP 5.5.0. Thanks

 

activation-1.1.1

commons-codec-1.12

commons-collections4-4.3

commons-compress-1.18

commons-logging-1.2

commons-math3-3.6.1

curvesapi-1.06

jaxb-api-2.3.0

jaxb-core-2.3.0.1

jaxb-impl-2.3.0.1

junit-4.12

log4j-1.2.17

ooxml-schemas-1.4

poi-4.1.0

poi-examples-4.1.0

poi-excelant-4.1.0

poi-ooxml-4.1.0

poi-scratchpad-4.1.0

xmlbeans-3.1.0

Super Contributor

Re: Handling POIXML Exception in Groovy Scripting

Here is my solution for this problem. Please go through this and try once more please. Remove al the jars form bin/ext folder and add whatever is mentioned in the problem:-

 

https://community.smartbear.com/t5/SoapUI-Open-Source/HOW-TO-READ-EXCEL-FILE-IN-GROOVY/m-p/178248#M2...

Highlighted
Occasional Contributor

Re: Handling POIXML Exception in Groovy Scripting

Thanks, Yes this work. We use jlx jars for reading from xls file. So csv and XLS are covered.

 

We want to read xlsx data using Apache POI using groovy scripting. Any solution for it.

 

 

Community Hero

Re: Handling POIXML Exception in Groovy Scripting

Hi,

 

Your script works ok in the GroovyConsole with my own xlsx file.

 

@Grab(group='org.apache.poi', module='poi-ooxml', version='4.1.0')

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.usermodel.CellStyle;

FileInputStream inputStream = new FileInputStream("C:/Users/hunti/My Lesson Plan.xlsx")

Workbook wb = WorkbookFactory.create(inputStream)
Sheet sheet1 = wb.getSheet("Sheet1")
int rows = sheet1.getLastRowNum();
println rows //12

Can you post as an attachment the xlsx file you are having trouble with, or just let me know if you have the same problem with an empty/test sheet.

 

Posting more of the stack trace (as an attachment, please) may also help.

 

New Here?
Join us and watch the welcome video:
API Testing Mistake #2
APITestingMistake#2
Top Kudoed Authors