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:

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:

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.

Answer 1

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.

Answer 2

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