Search code examples
excelgroovyapache-poisoapuixssf

SOAPUI : unable to resolve class XSSFWorkBook error

I am getting below error while using Apache POI in SoapUI.

unable to resolve class XSSFWorkBook error

I imported POI in SOAPUI and dropped poi jar files in /bin/ext. I tried with version 3.14 as well as 3.15 beta. ( I have attached the error). Am I missing anything here? Any help will be appreciated.

Here is the code:

import org.apache.poi.xssf.usermodel.*
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

def fs = new FileInputStream        ("C:\\Users\\JDoe\\Desktop\\SOAP_Learn\\SOAP_Test_Data.xlsx")
def wb =  new XSSFWorkBook (fs)  //-->> gives error in this line
def ws = wb.getSheet("Sheet1")
def r = ws.getPhysicalNumberOfRows()

enter code here << Stack Trace >>
       org.codehaus.groovy.control.MultipleCompilationErrorsException: startup failed: Script5.groovy: 14: unable to resolve class XSSFWorkBook @ line 14, column 11. def wb = new XSSFWorkBook (fs) ^ org.codehaus.groovy.syntax.SyntaxException: unable to resolve class XSSFWorkBook @ line 14, column 11. at org.codehaus.groovy.ast.ClassCodeVisitorSupport.addError(ClassCodeVisitorSupport.java:146) at org.codehaus.groovy.control.ResolveVisitor.resolveOrFail(ResolveVisitor.java:222) at org.codehaus.groovy.control.ResolveVisitor.resolveOrFail(ResolveVisitor.java:232) at org.codehaus.groovy.control.ResolveVisitor.transformConstructorCallExpression(ResolveVisitor.java:969) at org.codehaus.groovy.control.ResolveVisitor.transform(ResolveVisitor.java:646) at org.codehaus.groovy.control.ResolveVisitor.transformDeclarationExpression(ResolveVisitor.java:1010) at org.codehaus.groovy.control.ResolveVisitor.transform(ResolveVisitor.java:638) at org.codehaus.groovy.ast.ClassCodeExpressionTransformer.visitExpressionStatement(ClassCodeExpressionTransformer.java:139) at org.codehaus.groovy.ast.stmt.ExpressionStatement.visit(ExpressionStatement.java:40) at org.codehaus.groovy.ast.CodeVisitorSupport.visitBlockStatement(CodeVisitorSupport.java:35) at org.codehaus.groovy.ast.ClassCodeVisitorSupport.visitBlockStatement(ClassCodeVisitorSupport.java:163) at org.codehaus.groovy.control.ResolveVisitor.visitBlockStatement(ResolveVisitor.java:1240) at org.codehaus.groovy.ast.stmt.BlockStatement.visit(BlockStatement.java:69) at org.codehaus.groovy.ast.ClassCodeVisitorSupport.visitClassCodeContainer(ClassCodeVisitorSupport.java:101) at org.codehaus.groovy.ast.ClassCodeVisitorSupport.visitConstructorOrMethod(ClassCodeVisitorSupport.java:112) at org.codehaus.groovy.ast.ClassCodeExpressionTransformer.visitConstructorOrMethod(ClassCodeExpressionTransformer.java:50) at org.codehaus.groovy.control.ResolveVisitor.visitConstructorOrMethod(ResolveVisitor.java:166) at org.codehaus.groovy.ast.ClassCodeVisitorSupport.visitMethod(ClassCodeVisitorSupport.java:123) at org.codehaus.groovy.ast.ClassNode.visitContents(ClassNode.java:1055) at org.codehaus.groovy.ast.ClassCodeVisitorSupport.visitClass(ClassCodeVisitorSupport.java:50) at org.codehaus.groovy.control.ResolveVisitor.visitClass(ResolveVisitor.java:1183) at org.codehaus.groovy.control.ResolveVisitor.startResolving(ResolveVisitor.java:141) at org.codehaus.groovy.control.CompilationUnit$10.call(CompilationUnit.java:632) at org.codehaus.groovy.control.CompilationUnit.applyToSourceUnits(CompilationUnit.java:912) at org.codehaus.groovy.control.CompilationUnit.doPhaseOperation(CompilationUnit.java:574) at org.codehaus.groovy.control.CompilationUnit.compile(CompilationUnit.java:523) at groovy.lang.GroovyClassLoader.doParseClass(GroovyClassLoader.java:279) at groovy.lang.GroovyClassLoader.parseClass(GroovyClassLoader.java:258) at groovy.lang.GroovyShell.parseClass(GroovyShell.java:613) at groovy.lang.GroovyShell.parse(GroovyShell.java:625) at groovy.lang.GroovyShell.parse(GroovyShell.java:652) at groovy.lang.GroovyShell.parse(GroovyShell.java:643) at com.eviware.soapui.support.scripting.groovy.SoapUIGroovyScriptEngine.compile(SoapUIGroovyScriptEngine.java:138) at com.eviware.soapui.support.scripting.groovy.SoapUIGroovyScriptEngine.run(SoapUIGroovyScriptEngine.java:89) 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) 1 error[![enter image description here][1]][1]

enter image description here

enter image description here

Solution

  • Add all the jar files that you downloaded from apache poi in soapui/bin/ext folder and restart the soapui

    jars added to bin/ext , in the interest of time i have added all the jars from apache poi download 

     poi-3.14-xxxxxx.jar
 poi-examples-3.14-xxxxx.jar
 poi-excelant-3.14-xxxxx.jar
 poi-ooxml-3.14-xxxxxx.jar
 poi-ooxml-schemas-3.14-xxxxxx.jar
 poi-scratchpad-3.14-xxxxx.jar

 ooxml-lib/curvesapi-1.03.jar
 ooxml-lib/xmlbeans-2.6.0.jar
 lib/commons-codec-1.10.jar
 lib/commons-logging-1.2.jar
 lib/junit-4.12.jar
 lib/log4j-1.2.17.jar

    use the below code to get the work book in to groovy

    import org.apache.poi.ss.usermodel.*
import org.apache.poi.hssf.usermodel.*
import org.apache.poi.xssf.usermodel.*
import org.apache.poi.ss.util.*

def fs = new FileInputStream ("C:\\Users\\JDoe\\Desktop\\SOAP_Learn\\SOAP_Test_Data.xlsx") 

// Use this factory pattern to create/read workbook this is the wrong we are doing and if you find it out in the jarfiles there is no class XSSF
 Workbook wb = WorkbookFactory.create(fs); 
 def ws = wb.getSheet("Sheet1")
 def r = ws.getPhysicalNumberOfRows()
 log.info r 

//enjoy!!!

    ISSUE: You cannot directly instantiate the class like new XSSFWorkbook() we have to use factory patteren to intatntiate it . dont forgot click answered this took much of my time click here too reference groovy that worked in by box