Cfspreadsheet InvocationTargetException

benser · October 12, 2023, 12:04pm

Hey All,

I am currently using Lucee version 5.3.7.48 and attempting to upgrade to version 5.4.3.15. After upgrading, I am getting an error related to cfspreadsheet “lucee.runtime.exp.NativeException: java.lang.reflect.InvocationTargetException”. I will paste the full StackTrace below.
As a note, when downgrading my Lucee version back to 5.3.7.48 it works just fine.

I appreciate any help!

lucee.runtime.exp.NativeException: java.lang.reflect.InvocationTargetException
at org.apache.poi.POIXMLFactory.createDocumentPart(POIXMLFactory.java:63)
at org.apache.poi.POIXMLDocumentPart.read(POIXMLDocumentPart.java:625)
at org.apache.poi.POIXMLDocument.load(POIXMLDocument.java:186)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.(XSSFWorkbook.java:260)
at org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:181)
at org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:140)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at lucee.runtime.reflection.pairs.MethodInstance.invoke(MethodInstance.java:56)
at lucee.runtime.java.JavaObject.call(JavaObject.java:265)
at lucee.runtime.java.JavaObject.call(JavaObject.java:287)
at lucee.runtime.util.VariableUtilImpl.callFunctionWithoutNamedValues(VariableUtilImpl.java:787)
at lucee.runtime.PageContextImpl.getFunction(PageContextImpl.java:1775)
at org.cfpoi.spreadsheet.spreadsheet_cfc$cf.udfCall8(/org/cfpoi/spreadsheet/Spreadsheet.cfc:2343)
at org.cfpoi.spreadsheet.spreadsheet_cfc$cf.udfCall(/org/cfpoi/spreadsheet/Spreadsheet.cfc)
at lucee.runtime.type.UDFImpl.implementation(UDFImpl.java:112)
at lucee.runtime.type.UDFImpl._call(UDFImpl.java:350)
at lucee.runtime.type.UDFImpl.callWithNamedValues(UDFImpl.java:213)
at lucee.runtime.type.scope.UndefinedImpl.callWithNamedValues(UndefinedImpl.java:804)
at lucee.runtime.util.VariableUtilImpl.callFunctionWithNamedValues(VariableUtilImpl.java:866)
at lucee.runtime.PageContextImpl.getFunctionWithNamedValues(PageContextImpl.java:1794)
at org.cfpoi.spreadsheet.spreadsheet_cfc$cf.udfCall1(/org/cfpoi/spreadsheet/Spreadsheet.cfc:32)
at org.cfpoi.spreadsheet.spreadsheet_cfc$cf.udfCall(/org/cfpoi/spreadsheet/Spreadsheet.cfc)
at lucee.runtime.type.UDFImpl.implementation(UDFImpl.java:112)
at lucee.runtime.type.UDFImpl._call(UDFImpl.java:350)
at lucee.runtime.type.UDFImpl.callWithNamedValues(UDFImpl.java:213)
at lucee.runtime.ComponentImpl._call(ComponentImpl.java:665)
at lucee.runtime.ComponentImpl._call(ComponentImpl.java:586)
at lucee.runtime.ComponentImpl.callWithNamedValues(ComponentImpl.java:1952)
at lucee.runtime.util.VariableUtilImpl.callFunctionWithNamedValues(VariableUtilImpl.java:866)
at lucee.runtime.PageContextImpl.getFunctionWithNamedValues(PageContextImpl.java:1794)
at spreadsheetread_cfm$cf.udfCall(/SpreadsheetRead.cfm:29)
at lucee.runtime.type.UDFImpl.implementation(UDFImpl.java:112)
at lucee.runtime.type.UDFImpl._call(UDFImpl.java:350)
at lucee.runtime.type.UDFImpl.call(UDFImpl.java:223)
at lucee.runtime.functions.system.CFFunction.call(CFFunction.java:106)
at module.marketplace.productadmin.layout_cfc$cf.udfCall2(/module/marketplace/productAdmin/layout.cfc:2161)
at module.marketplace.productadmin.layout_cfc$cf.udfCall(/module/marketplace/productAdmin/layout.cfc)
at lucee.runtime.type.UDFImpl.implementation(UDFImpl.java:112)
at lucee.runtime.type.UDFImpl._call(UDFImpl.java:350)
at lucee.runtime.type.UDFImpl.callWithNamedValues(UDFImpl.java:213)
at lucee.runtime.ComponentImpl._call(ComponentImpl.java:665)
at lucee.runtime.ComponentImpl._call(ComponentImpl.java:586)
at lucee.runtime.ComponentImpl.callWithNamedValues(ComponentImpl.java:1947)
at lucee.runtime.tag.Invoke.doComponent(Invoke.java:209)
at lucee.runtime.tag.Invoke.doEndTag(Invoke.java:186)
at module.common_cfc$cf.udfCall(/module/common.cfc:242)
at lucee.runtime.type.UDFImpl.implementation(UDFImpl.java:112)
at lucee.runtime.type.UDFImpl._call(UDFImpl.java:350)
at lucee.runtime.type.UDFImpl.call(UDFImpl.java:223)
at lucee.runtime.ComponentImpl._call(ComponentImpl.java:664)
at lucee.runtime.ComponentImpl._call(ComponentImpl.java:586)
at lucee.runtime.ComponentImpl.call(ComponentImpl.java:1933)
at lucee.runtime.util.VariableUtilImpl.callFunctionWithoutNamedValues(VariableUtilImpl.java:787)
at lucee.runtime.PageContextImpl.getFunction(PageContextImpl.java:1775)
at application.hystrixmedical.hcmarketplace.container.main_cfc$cf.udfCall(/application/hystrixMedical/hcMarketplace/container/main.cfc:83)
at lucee.runtime.type.UDFImpl.implementation(UDFImpl.java:112)
at lucee.runtime.type.UDFImpl._call(UDFImpl.java:350)
at lucee.runtime.type.UDFImpl.call(UDFImpl.java:218)
at lucee.runtime.util.PageContextUtil.getFunction(PageContextUtil.java:275)
at application.hystrixmedical.hcmarketplace.layout_cfc$cf.udfCall(/application/hystrixMedical/hcMarketplace/layout.cfc:84)
at lucee.runtime.type.UDFImpl.implementation(UDFImpl.java:112)
at lucee.runtime.type.UDFImpl._call(UDFImpl.java:350)
at lucee.runtime.type.UDFImpl.call(UDFImpl.java:223)
at lucee.runtime.ComponentImpl._call(ComponentImpl.java:664)
at lucee.runtime.ComponentImpl._call(ComponentImpl.java:586)
at lucee.runtime.ComponentImpl.call(ComponentImpl.java:1933)
at lucee.runtime.util.VariableUtilImpl.callFunctionWithoutNamedValues(VariableUtilImpl.java:787)
at lucee.runtime.PageContextImpl.getFunction(PageContextImpl.java:1775)
at application_cfc$cf.udfCall(/Application.cfc:222)
at lucee.runtime.type.UDFImpl.implementation(UDFImpl.java:112)
at lucee.runtime.type.UDFImpl._call(UDFImpl.java:350)
at lucee.runtime.type.UDFImpl.call(UDFImpl.java:223)
at lucee.runtime.ComponentImpl._call(ComponentImpl.java:664)
at lucee.runtime.ComponentImpl._call(ComponentImpl.java:586)
at lucee.runtime.ComponentImpl.call(ComponentImpl.java:1933)
at lucee.runtime.listener.ModernAppListener.call(ModernAppListener.java:444)
at lucee.runtime.listener.ModernAppListener._onRequest(ModernAppListener.java:218)
at lucee.runtime.listener.MixedAppListener.onRequest(MixedAppListener.java:44)
at lucee.runtime.PageContextImpl.execute(PageContextImpl.java:2493)
at lucee.runtime.PageContextImpl._execute(PageContextImpl.java:2478)
at lucee.runtime.PageContextImpl.executeCFML(PageContextImpl.java:2449)
at lucee.runtime.engine.Request.exe(Request.java:45)
at lucee.runtime.engine.CFMLEngineImpl._service(CFMLEngineImpl.java:1216)
at lucee.runtime.engine.CFMLEngineImpl.serviceCFML(CFMLEngineImpl.java:1162)
at lucee.loader.engine.CFMLEngineWrapper.serviceCFML(CFMLEngineWrapper.java:102)
at lucee.loader.servlet.CFMLServlet.service(CFMLServlet.java:51)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:742)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:198)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:493)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:81)
at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:650)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342)
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:800)
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:806)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1498)
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:748)
Caused by: org.apache.poi.POIXMLException: java.lang.reflect.InvocationTargetException
… 112 more
Caused by: java.lang.reflect.InvocationTargetException
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at org.apache.poi.xssf.usermodel.XSSFFactory.createDocumentPart(XSSFFactory.java:56)
at org.apache.poi.POIXMLFactory.createDocumentPart(POIXMLFactory.java:60)
… 111 more
Caused by: java.lang.NullPointerException

TonyMonast · October 12, 2023, 12:50pm

Hi!

What does your code look like for calling cfspreadsheet? If it is complex, try to simplify the code as much as possible to see if even basic functions cause the error.

benser · October 12, 2023, 1:04pm

Hi @TonyMonast,

Thanks for the reply! The code itself is quite simple. In this particular case, it is failing when I call the CF function SpreadSheetRead. What is strange about this, is once I log in to my application, I am able to perform the spreadsheet actions with no issues. But when I try to do it a 2nd time (staying logged in), I receive the error

TonyMonast · October 12, 2023, 1:20pm

Create a page completely separate from your application and try to:

Create a basic spreadsheet file
Read a basic spreadsheet file

If it works, the problem must come from your code which is no longer compatible with the new version of Lucee. You’ll have to look at what’s different when you call cfspreadsheet the second time. Are there session variables or cookies involved to process the file? Ideally if you can share your code, that would help.

martin · October 12, 2023, 2:34pm

@benser You may want to consider using the excellent spreadsheet-cfml instead by @Julian_Halliwell . It is simple to get up and running, is rock solid and is constantly getting updated. You won’t look back!

benser · October 12, 2023, 2:44pm

Thanks for sending this! I will take a deeper look into it

benser · October 12, 2023, 2:46pm

Thanks for the suggestion! I will be attempting this tomorrow

benser · October 16, 2023, 9:16am

I am attempting your suggestion with the following code, and still getting the same error as before:

Any other suggestions?

TonyMonast · October 16, 2023, 1:26pm

Hi benser,

Bad news. I tried your code and I get the same error on SpreadsheetRead. I’m able to create the xls file, but can’t read it.

Here the code. It’s better the paste the code in the forum than doing a screenshot.

<cfset data = [
    ["Name", "Age", "City"],
    ["John Doe", 30, "New York"],
    ["Jane Smith", 25, "Los Angeles"]
]>

<!--- Create a query object from the data --->
<cfset queryData = QueryNew("Name,Age,City")>
<cfloop array="#data#" index="row">
    <cfset QueryAddRow(queryData, row)>
</cfloop>

<!--- Write the query data to an Excel file in the current folder --->
<cfspreadsheet action="write" query="queryData" filename="data.xlsx" overwrite="true">

<!--- The path to the Excel file in the current folder --->
<cfset excelFilePath = "data.xlsx">

<!--- Read the Excel file --->
<cfset excelData = SpreadsheetRead(excelFilePath)>
<cfdump var="#excelData#" label="data">

When I restart Tomcat/Lucee and execute your code, I get this :

When I execute it a second time, I get the error java.lang.reflect.InvocationTargetException

TonyMonast · October 16, 2023, 3:33pm

Hi,

Note that with the .xls extension, it works fine :

<!--- The path to the Excel file in the current folder --->
<cfset excelFilePath = getDirectoryFromPath(getCurrentTemplatePath()) & "data.xls">

<cfset data = [
    ["John Doe", 30, "New York"],
    ["Jane Smith", 25, "Los Angeles"]
]>

<!--- Create a query object from the data --->
<cfset queryData = QueryNew("Name,Age,City")>
<cfloop array="#data#" index="row">
    <cfset QueryAddRow(queryData, row)>
</cfloop>

<cfdump var="#queryData#" label="Data to insert">

<!--- Write the query data to an Excel file in the current folder --->
<cfspreadsheet action="write" query="queryData" filename="#excelFilePath#" overwrite="true">

<!--- Read the Excel file into a query object named "excelData" --->
<cfspreadsheet action="read" src="#excelFilePath#" query="excelData" headerrow="1">

<cfdump var="#excelData#" label="Data in the Excel file">

If you go this way, pay special attention to the column headers. With headerrow, the variable column names are correct, but the Excel row that contains the column header names is also returned as part of the data. Not sure if it’s a normal behavior :

headers

David_Anderson · October 17, 2023, 1:06pm

+1 for this suggestion. It’s great.

benser · October 17, 2023, 1:16pm

Hi @TonyMonast , thank you for this solution! Using the ‘xls’ is also working fine for me with my simple version. I am going to try and use this same syntax/logic in my application where this was failing.

I will update here, either way, so that others can see an update.

Thanks again

benser · October 20, 2023, 1:06pm

Update: Changing the file format to ‘xls’ is now working, and the spreadsheet can be properly read without any errors. I am unsure as to why in the Lucee version 5.3.7.48 that ‘xlsx’ can be read fine, but in newer versions it cannot.

If anyone knows why this is, or has a solution, please let me know.

benser · October 23, 2023, 8:59am

@martin Thanks again for this suggestion. I ended up integrating this, and it works amazing! So glad that this exists

TonyMonast · November 14, 2023, 2:48pm

Hi!

Still satisfied with the new cfspreadsheet library you are using? I plan to make the move too.

benser · November 15, 2023, 2:15pm

Hey @TonyMonast,

Yeah, very satisfied with it! It was very easy to set up and use. Also, making the syntax changes to our existing code was quite simple as well. I can only recommend making the switch.