2

I have checked every possible question on here with a similar problem, and none of the solutions have worked. I feel like I am missing something really obvious, but I just can't see it.

I am on Windows 10, using Eclipse 2019, and the newest Apache POI jar files (not maven) with xmlbeans version 3.1.0. I want to read an xlsx file, but every time I run my code (Which is a basic example I've seen used and run successfully on multiple websites and videos), I receive a "NullPointerException" at the line where I create my workbook:

import java.io.File;
//import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
//import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class excelImportTest {


    public static void main (String args[]) throws IOException, InvalidFormatException {

      // Linking file to Workbook
      // ****Where exception is called every time****
      XSSFWorkbook wb = new XSSFWorkbook(new File("C:\\Users\\Owner\\Downloads\\TestData.xlsx"));

      // Pulling sheet from Workbook
      XSSFSheet sheet = wb.getSheetAt(0);

      // Get iterator to all the rows in current sheet
      Iterator<Row> rowIterator = sheet.iterator();

      // Traversing over each row of XLSX file
      while (rowIterator.hasNext()) {
          Row row = rowIterator.next();

          // For each row, iterate through each columns
          Iterator<Cell> cellIterator = row.cellIterator();

          while (cellIterator.hasNext()) {
              Cell cell = cellIterator.next();
              switch(cell.getCellType()) {
              case STRING:
                  System.out.print(cell.getStringCellValue() + "\t");
                  break;
              case NUMERIC:
                  System.out.print(cell.getNumericCellValue() + "\t");
                  break;
              case BOOLEAN:
                  System.out.print(cell.getBooleanCellValue() + "\t");
                  break;
              default :
              }
          }
      }
      wb.close();

Here is the exception I receive:

Exception in thread "main" java.lang.ExceptionInInitializerError
    at sun.misc.Unsafe.ensureClassInitialized(Native Method)
    at sun.reflect.UnsafeFieldAccessorFactory.newFieldAccessor(Unknown Source)
    at sun.reflect.ReflectionFactory.newFieldAccessor(Unknown Source)
    at java.lang.reflect.Field.acquireFieldAccessor(Unknown Source)
    at java.lang.reflect.Field.getFieldAccessor(Unknown Source)
    at java.lang.reflect.Field.get(Unknown Source)
    at org.apache.xmlbeans.XmlBeans.typeSystemForClassLoader(XmlBeans.java:775)
    at org.openxmlformats.schemas.drawingml.x2006.main.ThemeDocument.<clinit>(Unknown Source)
    at org.openxmlformats.schemas.drawingml.x2006.main.ThemeDocument$Factory.parse(Unknown Source)
    at org.apache.poi.xssf.model.ThemesTable.<init>(ThemesTable.java:86)
    at org.apache.poi.ooxml.POIXMLFactory.createDocumentPart(POIXMLFactory.java:61)
    at org.apache.poi.ooxml.POIXMLDocumentPart.read(POIXMLDocumentPart.java:684)
    at org.apache.poi.ooxml.POIXMLDocument.load(POIXMLDocument.java:180)
    at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:288)
    at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:325)
    at excelImportTest.main(excelImportTest.java:62)
Caused by: java.lang.RuntimeException: Could not instantiate SchemaTypeSystemImpl (java.lang.reflect.InvocationTargetException): is the version of xbean.jar correct?
    at schemaorg_apache_xmlbeans.system.sD023D6490046BA0250A839A9AD24C443.TypeSystemHolder.loadTypeSystem(Unknown Source)
    at schemaorg_apache_xmlbeans.system.sD023D6490046BA0250A839A9AD24C443.TypeSystemHolder.<clinit>(Unknown Source)
    ... 16 more
Caused by: java.lang.reflect.InvocationTargetException
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
    at java.lang.reflect.Constructor.newInstance(Unknown Source)
    ... 18 more
Caused by: java.lang.NullPointerException
    at org.apache.xmlbeans.impl.schema.ClassLoaderResourceLoader.getResourceAsStream(ClassLoaderResourceLoader.java:33)
    at org.apache.xmlbeans.impl.schema.SchemaTypeSystemImpl$XsbReader.getLoaderStream(SchemaTypeSystemImpl.java:2249)
    at org.apache.xmlbeans.impl.schema.SchemaTypeSystemImpl$XsbReader.<init>(SchemaTypeSystemImpl.java:1522)
    at org.apache.xmlbeans.impl.schema.SchemaTypeSystemImpl.initFromHeader(SchemaTypeSystemImpl.java:273)
    at org.apache.xmlbeans.impl.schema.SchemaTypeSystemImpl.<init>(SchemaTypeSystemImpl.java:185)
    ... 22 more

I have tried every combination of options I can think of and suggestions I have found online:

  • I created a separate FileInputStream(new File(...)), and ran an if statement to see if it was null. This returned "not null", but I still got the exception when it got to the line above
  • I tried running the line with a nested new FileInputStream(new File(...)) format, and received the same error
  • I have been using a try-catch block for the exception, but it does not seem to flag the exception before aborting the program
  • I have tried using the Apache-POI-included OCPPackage.open(new File(...)) approach, and get the same error

Is there any obvious approach I am missing, or does this stem from some other issue in my setup?

Edit

Since it seems my problem may be in the .jar files I am referencing, here is what I have as part of the file's build path:

enter image description here

halfer
  • 19,824
  • 17
  • 99
  • 186
Katherine Bowers
  • 31
  • 5
  • Which version of apache poi are you using ? – Anish B. Apr 15 '20 at 18:13
  • 1
    @AnishB. I am using Apache POI 4.1.2 – Katherine Bowers Apr 15 '20 at 18:22
  • 1
    @AxelRichter I added the ClassLoader code, but am now receiving this: 'Exception in thread "main" java.lang.NullPointerException' at the 'java.net.URL res = classloader.getResource("org/apache/xmlbeans/impl/schema/SchemaTypeSystemImpl.class");' line. Does this mean I am not connecting to any sort of xmlBeans files at all? – Katherine Bowers Apr 15 '20 at 18:26
  • Are you running any code apart from poi in the project ? – Anish B. Apr 15 '20 at 18:29
  • 1
    @AnishB. No, I am not running any other code -I wanted to get this connection set up before adding more functionality. – Katherine Bowers Apr 15 '20 at 18:35
  • 2
    Can you please try: `ClassLoader classloader = org.apache.xmlbeans.XmlBeans.class.getClassLoader(); java.net.URL res = classloader.getResource("org/apache/xmlbeans/XmlBeans.class"); String path = res.getPath(); System.out.println("XmlBeans came from " + path);` as first code lines in `main` method? What does it print? Where is `XmlBeans` class coming from in your system? – Axel Richter Apr 16 '20 at 06:09
  • @AxelRichter I added the code, but when I run it, I get a NullPointerException at the lin where I declare java.net.URL res – Katherine Bowers Apr 16 '20 at 18:36
  • 1
    Sorry, then I can't help further. Seems either there is not any `xmlbeans*.jar` in class path while running or there is no class loader available for `org.apache.xmlbeans.XmlBeans.class`. I don't know what miss-configuration of `Eclipse` leads to that. Maybe some `Eclipse` specialist knows. – Axel Richter Apr 16 '20 at 19:02
  • 1
    (Aside: a general tip for posting on Stack Overflow. When questions are posted on Stack Overflow, they sometimes come with statements of woe and suffering intended to elicit great sympathy in the reader. This is a risky ploy, since some readers find this manipulative, and will downvote in response. I suspect your question escaped that treatment because it was otherwise detailed and answerable. However please try to refrain from adding this sort of thing - volunteer burnt-out is a thing, and some of them spend more time on this site than is healthy anyway!). – halfer Apr 21 '20 at 15:21
  • I had the exact same problem you were having, but with Microsoft Word. I managed to solve it without using maven, please see my post [https://stackoverflow.com/questions/61673221/nullpointerexception-when-opening-a-docx-file-with-xwpfdocument/61701776#61701776](https://stackoverflow.com/questions/61673221/nullpointerexception-when-opening-a-docx-file-with-xwpfdocument/61701776#61701776) – Ephraim May 09 '20 at 19:49
  • 1
    @KatherineBowers The java build path is an incredibly complex thing. Its like million little switches and if any of them are out of order you are going to see problems like this. This is why using Maven is important. It gives you a set of jars that are known to work together. I'd advise you to follow a getting started tutorial to the letter. https://www.geeksforgeeks.org/apache-poi-getting-started/ – nsfyn55 May 10 '20 at 14:13

2 Answers2

2

I think you have not added the jars properly.

First go to this url : https://archive.apache.org/dist/poi/release/bin/ and download poi-bin-4.1.2-20200217.zip from there.

Now extract the zip and add this jars to your project marked with red.

enter image description here

enter image description here

enter image description here

I have tested your code and its working.

import java.io.File;
import java.io.IOException;
import java.util.Iterator;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class Testing {
    public static void main(String args[]) throws IOException, InvalidFormatException {

        // Linking file to Workbook
        // ****Where exception is called every time****
        XSSFWorkbook wb = new XSSFWorkbook(new File("C:\\Users\\Anish\\Downloads\\TestData.xlsx"));

        System.out.println("Workbook loaded");

        // Pulling sheet from Workbook
        XSSFSheet sheet = wb.getSheetAt(0);

        // Get iterator to all the rows in current sheet
        Iterator<Row> rowIterator = sheet.iterator();

        // Traversing over each row of XLSX file
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();

            // For each row, iterate through each columns
            Iterator<Cell> cellIterator = row.cellIterator();

            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                switch (cell.getCellType()) {
                case STRING:
                    System.out.print(cell.getStringCellValue() + "\t");
                    break;
                case NUMERIC:
                    System.out.print(cell.getNumericCellValue() + "\t");
                    break;
                case BOOLEAN:
                    System.out.print(cell.getBooleanCellValue() + "\t");
                    break;
                default:
                }
            }
        }
        wb.close();

    }
}

Output :

enter image description here

Now, if you are using maven in the project.Then, you don't need to add jars on the classpath.

Simply, add this dependency in the pom.xml.

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.2</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.2</version>
</dependency>
Anish B.
  • 9,111
  • 3
  • 21
  • 41
  • 1
    Unfortunately, no, this did not fix the problem. I even removed the previous apache poi files and re-downloaded the .zip of jars from the website, and re-added them back to the file's build path, and am still receiving the exact same error. – Katherine Bowers Apr 15 '20 at 20:30
  • 1
    @KatherineBowers I can see in the screenshot that you are using maven. Are you using maven ? I have updated my answer for maven if you are using maven in the project. – Anish B. Apr 16 '20 at 03:29
  • @KatherineBowers Can you add your pom.xml ? – Anish B. Apr 16 '20 at 03:33
  • 1
    I have never used maven before, and to my knowledge am not currently using it - I'm not sure where you see that on my screenshot? And it is still not working - I extracted the zip file, and added the JARs to the build path exactly in the order you did - I'm thinking somehow whatever I am doing in eclipse is not making a connection, so I am going to try with maven now to see what happens. – Katherine Bowers Apr 16 '20 at 18:32
  • 1
    @KatherineBowers Try by creating a new fresh project and check for its working. – Anish B. Apr 16 '20 at 18:54
  • 1
    I started fresh with a brand new project, and now have a different error - `java.lang.NoClassDefFoundError: org/apache/poi/openxml4j/exceptions/InvalidFormatException` It looks like the exceptions info is not included in the jars I have? – Katherine Bowers Apr 16 '20 at 19:34
0

So after much troubleshoooting, I abandoned using the Apache POI jar files, and switched to using maven and adding apache poi as a dependancy, and my code now runs successfully.

Thank you too everyone who helped and made suggestions, though I am still not sure of the origin of the communication problem between Eclipse and the POI jar files.

Katherine Bowers
  • 31
  • 5