Forum Discussion
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:-
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.
- JHunt5 years agoCommunity Hero
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.
- mk1075 years agoOccasional Contributor
Hi JHunt, Sorry for delayed reply.
Tried it using your script too but it does not work. XLSX file is just two row data. Could you please try to read data in groovy script as test step in soapUI from attached sheet.
SOAP UI - 5.5.0
Added error log from error output: Not sure if it has something to have with jars placed inside ext and lib folder. Can you share your jars for soap in ext and lib. Thanks
- Thu Aug 08 11:05:09 IST 2019:ERROR:org.apache.poi.ooxml.POIXMLException: org.apache.xmlbeans.XmlOptions.setEntityExpansionLimit(I)Lorg/apache/xmlbeans/XmlOptions;
- org.apache.poi.ooxml.POIXMLException: org.apache.xmlbeans.XmlOptions.setEntityExpansionLimit(I)Lorg/apache/xmlbeans/XmlOptions;
- at org.apache.poi.ooxml.POIXMLFactory.createDocumentPart(POIXMLFactory.java:66)
- at org.apache.poi.ooxml.POIXMLDocumentPart.read(POIXMLDocumentPart.java:657)
- at org.apache.poi.ooxml.POIXMLDocument.load(POIXMLDocument.java:180)
- at org.apache.poi.xssf.usermodel.XSSFWorkbook.(XSSFWorkbook.java:282)
- at org.apache.poi.xssf.usermodel.XSSFWorkbookFactory.createWorkbook(XSSFWorkbookFactory.java:88)
- at org.apache.poi.xssf.usermodel.XSSFWorkbookFactory.createWorkbook(XSSFWorkbookFactory.java:135)
- at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
- at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
- at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
- at java.lang.reflect.Method.invoke(Unknown Source)
- at org.apache.poi.ss.usermodel.WorkbookFactory.createWorkbook(WorkbookFactory.java:339)
- at org.apache.poi.ss.usermodel.WorkbookFactory.createXSSFWorkbook(WorkbookFactory.java:314)
- at org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:232)
- at org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:198)
- at org.apache.poi.ss.usermodel.WorkbookFactory$create.call(Unknown Source)
- at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:48)
- at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:113)
- at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:125)
- at Script1.run(Script1.groovy:17)
- at com.eviware.soapui.support.scripting.groovy.SoapUIGroovyScriptEngine.run(SoapUIGroovyScriptEngine.java:90)
- at com.eviware.soapui.impl.wsdl.teststeps.WsdlGroovyScriptTestStep.run(WsdlGroovyScriptTestStep.java:141)
- at com.eviware.soapui.impl.wsdl.panels.teststeps.GroovyScriptStepDesktopPanel$RunAction$1.run(GroovyScriptStepDesktopPanel.java:250)
- at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
- at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
- at java.lang.Thread.run(Unknown Source)
- Caused by: java.lang.reflect.InvocationTargetException
- at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
- at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
- at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
- at java.lang.reflect.Constructor.newInstance(Unknown Source)
- at org.apache.poi.xssf.usermodel.XSSFFactory.createDocumentPart(XSSFFactory.java:56)
- at org.apache.poi.ooxml.POIXMLFactory.createDocumentPart(POIXMLFactory.java:63)
- ... 24 more
- Caused by: java.lang.NoSuchMethodError: org.apache.xmlbeans.XmlOptions.setEntityExpansionLimit(I)Lorg/apache/xmlbeans/XmlOptions;
- at org.apache.poi.ooxml.POIXMLTypeLoader.(POIXMLTypeLoader.java:43)
- at org.apache.poi.xssf.model.ThemesTable.(ThemesTable.java:86)
- ... 30 more
- JHunt5 years agoCommunity Hero
Your Groovy scripts run in the same JVM as SoapUI.
SoapUI has a dependency on XmlBeans 2.4.0 and loads this before the JARs in bin/ext/, but poi-ooxml-schemas (the latest version) has a dependancy on XmlBeans 3.1.0.
You could try using Apache POI 3.9 because it is looking for XmlBeans 2.3.0 and is more likely to work with SoapUI's version (no guarantees).
Related Content
- 13 years agojitendra_1
- 6 years agosriram_sig
- 10 years agomrezahoseini
Recent Discussions
- 2 days agoruchisingh