InvocationTargetException while reading xlsx file using poi

Hi all,

my goal is to simply read a .xlsx file to later put it into a data table for further usage.

i have attached the package structure. there shouldn't be nothing wrong.

this is my java code:

	File existingXlsx = new File("D:\\somewhere\\Test.xls");
		System.out.println("File Exists: " + existingXlsx.exists());


		Workbook workbook = new XSSFWorkbook(new FileInputStream(
				existingXlsx));

		Sheet worksheet = workbook.getSheet("Sheet1");
		Row row1 = worksheet.getRow(0);
		Cell cellA1 = row1.getCell((short) 0);
		String a1Val = cellA1.getStringCellValue();


		System.out.println("A1x: " + a1Val);

 

I executed this java code in a java appliction and it works. so the java code is right. but when i am integrating this into KNIME i get the following exception. btw. when i use the given xls-reader provided from KNIME and try to browse for .xlsx files, i also get an InvocationTargetException. Please help! Thx in advance!

org.apache.poi.POIXMLException: java.lang.reflect.InvocationTargetException
    at org.apache.poi.xssf.usermodel.XSSFFactory.createDocumentPart(XSSFFactory.java:62)
    at org.apache.poi.POIXMLDocumentPart.read(POIXMLDocumentPart.java:403)
    at org.apache.poi.POIXMLDocument.load(POIXMLDocument.java:155)
    at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:207)
    at siemenst.ct.test.Test123NodeModel.execute(Test123NodeModel.java:121)
    at org.knime.core.node.NodeModel.execute(NodeModel.java:732)
    at org.knime.core.node.NodeModel.executeModel(NodeModel.java:566)
    at org.knime.core.node.Node.invokeFullyNodeModelExecute(Node.java:1128)
    at org.knime.core.node.Node.execute(Node.java:915)
    at org.knime.core.node.workflow.NativeNodeContainer.performExecuteNode(NativeNodeContainer.java:556)
    at org.knime.core.node.exec.LocalNodeExecutionJob.mainExecute(LocalNodeExecutionJob.java:95)
    at org.knime.core.node.workflow.NodeExecutionJob.internalRun(NodeExecutionJob.java:179)
    at org.knime.core.node.workflow.NodeExecutionJob.run(NodeExecutionJob.java:110)
    at org.knime.core.util.ThreadUtils$RunnableWithContextImpl.runWithContext(ThreadUtils.java:328)
    at org.knime.core.util.ThreadUtils$RunnableWithContext.run(ThreadUtils.java:204)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at org.knime.core.util.ThreadPool$MyFuture.run(ThreadPool.java:123)
    at org.knime.core.util.ThreadPool$Worker.run(ThreadPool.java:246)
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:422)
    at org.apache.poi.xssf.usermodel.XSSFFactory.createDocumentPart(XSSFFactory.java:60)
    ... 18 more
Caused by: java.lang.ClassCastException: org.openxmlformats.schemas.drawingml.x2006.main.impl.ThemeDocumentImpl cannot be cast to org.openxmlformats.schemas.drawingml.x2006.main.ThemeDocument
    at org.openxmlformats.schemas.drawingml.x2006.main.ThemeDocument$Factory.parse(Unknown Source)
    at org.apache.poi.xssf.model.ThemesTable.<init>(ThemesTable.java:44)
    ... 23 more

Maybe there is a conflict among the Apache POI libraries and other libraries your test plugin is importing.

Instead of manually addingthe POI library jars , my suggestion is to use the POI library already present in KNIME, by adding to your test plugin a dependency to the KNIME POI Plug-in (org.knime.ext.poi).

 

Actually, the POI classes are in org.apache.poi and not org.knime.ext.poi. The latter only contains the nodes and itself has a dependency to org.apache.poi.

Thank you for the answer!

Unfortunately the error stays the same. I attached a screenshot of the dependency section. What is weired that the org.knime.ext.poi is useless to eclipse.. did I configure something wrong?

Thx for the help!

@thor: I have found a similar thread at https://tech.knime.org/forum/knime-users/evaluation-of-java-snippet-failed-for-row-row-0-orgdom4jdocumentexception

What makes it similar? My InvocationTargetException is caused by

Caused by: java.lang.ClassCastException: org.openxmlformats.schemas.spreadsheetml.x2006.main.impl.SstDocumentImpl cannot be cast to org.openxmlformats.schemas.spreadsheetml.x2006.main.SstDocument

So what do I have to do in order to read .xlsx files?

I tried adding both the manual jars and the poi provided by KNIME. The same error appears. Do you have any suggestions?

You have two different sources for this class in your plug-ins classpath. You can add a breakpoint for a ClassCastException and then inspect the two class objects and determine their class loaders. One of the class loader's members tells you for which plug-in it is responsible. Then you can check why your plug-in has access to both providers. Maybe you need to set dependency ranges.

Oh sorry no I didn't mean that I apply both the imported poi and the Knime poi at the same time. I meant I tried both either or.. But unfortunately the error didn't change.

It is the same error as in the attached pic which is a screenshot of the xls reader. and it is NOT possible to read .xlsx files with the xls reader. So that was the initial reason make an own node. But in the end the same error appears which seems to me that Knime has a maybe a problem with .xlsx files?

Is there any small example that works? I am thankful for any suggestions..

 


 

KNIME doesn't have problems reading (small) xslx files. As I said, there are at least two sources of the class org.openxmlformats.schemas.spreadsheetml.x2006.main.SstDocument in your installation. First try to find these two sources (plug-in and/or Jar).

ok thanks. well, since I tried the normal xls reader node from Knime for now I just searched within Knime for the plugin. I have found something in the installed plugins. But I cannot uninstall anything of them as you can see in the attachments..

Are there more possibilities to find plugins/jars?

ok I got it. Actually I don't know how. But I just reinstalled KNIME and then it works. It seems so that I have installed some nonsense..

But thank you all for the great support!

Dear all,

I ran into the same problem. I have a node that used to work with the dependency org.apache.poi.ooxml. But since late december '16 it stopped working. Its dependencies: org.apache.poi.ooxml 3.9 and org.apache.poi.ooxml.schemas 3.9 are distributed by Eclipse Orbit while org.apache.xmlbeans 2.6 is by KNIME. To work around it I embedded the whole POI 3.15 distribution  as in https://poi.apache.org/download.html

I created today a test node (attached) that loads any Excel spreadsheet using the org.apache.poi.ooxml dependency and got the error described earlier in Windows and Linux. It works in Mac.

Best,

Miguel

 

You shouldn't use your own POI library but instead rely on the one that is already part of KNIME. If you really want to use your own version, put it into a separate plug-in that doesn't have a dependency on org.knime.core.

Hi Thor,

From 2016 I have been using the POI library provided by KNIME (org.apache.poi) but since last week I had to include it manually since it stopped working around December-January (I suspect some dependencies are missing). I intend to revert back to org.apache.poi as soon as it is fixed. Please notice that one of the featured changes of the KNIME 3.3 release (dec'16) is "Excel Integration Reworked", https://tech.knime.org/whats-new-in-knime-33#XLSBackendChanges-Scal

I even included in my earlier post a minimal example node using the POI library provided by KNIME in order to reproduce the error.

Best,

Miguel

Since December KNIME is using (and distributing) org.apache.servicemix.bundles.poi (version 3.14). Can you see if it helps if you declare a dependency to it?

Please do not distribute your own version of POI with your bundle. It makes your bundle overly large and it might have undesired side effects (which you will notice half a year later and then you say... hm.. :-) ) 

Thanks Wiswedel! That dependency fixed it.