can't read xlsx file using apache poi

Question

I've been searching this issue for hours but the solutions given didn't work. I'm working in Intellij IDEA 12.0.4 trying to read a Microsoft 2007 xlsx file. The error I receive is:

Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/xmlbeans/XmlException
    at org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:91)
    at Main.main(Main.java:157)
Caused by: java.lang.ClassNotFoundException: org.apache.xmlbeans.XmlException
    at java.net.URLClassLoader$1.run(URLClassLoader.java:366)
    at java.net.URLClassLoader$1.run(URLClassLoader.java:355)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.net.URLClassLoader.findClass(URLClassLoader.java:354)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:423)
    at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:308)

The code is:

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.w3c.dom.Document;
import org.w3c.dom.NodeList;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import java.io.File;
import java.util.Iterator;

public class Main {
    public static void main(String[] args) {
        try {

            File file = new File(root_dir + "2013-03-13 iom diff.xlsx");
            Workbook workbook = WorkbookFactory.create(file); //fails here

            //...

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

In IDEA I have the following project settings:

modsource projdepen projpath projlib projstruct

I have no Facets or Artifacts and under Platform Settings in IDEA I have no Global Libraries. Under Platform Settings I have the following SDKs: sdkclasspath sdkannotations sdksrcpath

I'm on Windows 7 SP1 x64 and under System Properties -> Environment Variables I have: Under system variables->CLASSPATH:

C:\xmlbeans-2.5.0\lib\xbean.jar;C:\xmlbeans-2.5.0\lib\jsr173_1.0_api.jar

Under system variables->Path:

...;%XMLBEANS_HOME%\bin;C:\poi-3.9\ooxml-lib\xmlbeans-2.3.0.jar;C:\poi-3.9\ooxml-lib\stax-api-1.0.1.jar;C:\poi-3.9\ooxml-lib\xmlbeans-2.3.0 where XMLBEANS_HOME is defined under variables as C:\xmlbeans-2.5.0 and ... denotes other unrelated files.

I also have the following installed, as far as Java goes:

Java 7 Update 17
Java 7 Update 17 (64-bit)
Java SE Development Kit 7 Update 17
Java SE Development Kit 7 Update 17 (64-bit)

Explicitly my question is "How do I load an xlsx file using apache POI when I'm receiving this error." Any help is much appreciated.

why are you using sources for xmlbeans, instead of the compiled jar? — Peter Elliott
– Peter Elliott, Commented Mar 21, 2013 at 16:21
I have both under the Path variable. I tried removing the sources from the path variable and still have the same error. (I closed and reopened IDEA to be sure) — thed0ctor
– thed0ctor, Commented Mar 21, 2013 at 16:30
Now it says: Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/xmlbeans/XmlObject — thed0ctor
– thed0ctor, Commented Mar 22, 2013 at 12:16

Vickym · Accepted Answer · 2013-03-21 17:03:18Z

1

Use XSSF implementation or sxssf implementation of POI.

http://poi.apache.org/spreadsheet/index.html

Here is an example http://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/ss/examples/BusinessPlan.java

answered Mar 21, 2013 at 17:03

Vickym

1372 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:04:56Z

I noticed that I didn't save the excel file as OOXLM xlsx but was saving as just normal xlsx which I believe was part of the problem. Both have the same extension so I suppose it's easy to make that mistake. I looked at the output of the compiler and at the very top it listed the jars in the classpath it was using. However it seems that Intellij IDEA 12 doesn't use the classpath of the system but rather the project. I had added the directories to the classpath in the project (by going to Project Structure->Dependencies-> and then clicking the + icon to add jars or directories) but the correct jar files weren't being called (I could tell from the first lines of output from the compiler). I searched the project directory in .idea->libraries and discovered xml configuration files for the directory paths I had added under the dependency tab that should contain the jar files needed to run the program. I opened one of the xml files up and saw this:

<component name="libraryTable">
  <library name="poi-3">
    <CLASSES>
      <root url="file://C:/poi-3.9" />
    </CLASSES>
    <JAVADOC>
      <root url="file://C:/poi-3.9/docs/apidocs" />
    </JAVADOC>
    <SOURCES />
    <jarDirectory url="file://C:/poi-3.9" recursive="false" />
  </library>
</component>

I then changed recursive to true so it read

<component name="libraryTable">
  <library name="poi-3">
    <CLASSES>
      <root url="file://C:/poi-3.9" />
    </CLASSES>
    <JAVADOC>
      <root url="file://C:/poi-3.9/docs/apidocs" />
    </JAVADOC>
    <SOURCES />
    <jarDirectory url="file://C:/poi-3.9" recursive="true" />
  </library>
</component>

This removed this error (but now I'm getting a nullPointer exception). Just wanted to share in case this helps anyone using the same IDE in the future.

Collectives™ on Stack Overflow

can't read xlsx file using apache poi

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related