Reading from an Excel (xlsx) file using Apache POI library throws NullPointerException

Question

I 'm building a JSP web application using Eclipse IDE. The web application loads without issue on tomcat server ver 7. We got a new requirement to read from xls and xlsx file. So we have used Apache POI library (4.1.2) to implement this as follows: I have taken out other codes as they are not relevant for this post

FileInputStream file = new FileInputStream(new File(args[0]));

        //Create Workbook instance holding reference to .xlsx file
        XSSFWorkbook workbook = new XSSFWorkbook(file); //<- This is where the exception occurs

        //Get first/desired sheet from the workbook
        XSSFSheet sheet = workbook.getSheetAt(0);

        //Iterate through each rows one by one
        Iterator<Row> rowIterator = sheet.iterator();

But when executing we are getting the below exception. I have tried googling but didn't find relevant help. Can you please assist?

SEVERE: Servlet.service() for servlet [UploadEstimates] in context with path [/AD] threw exception [Servlet execution threw an exception] with root cause
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)
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)
at schemaorg_apache_xmlbeans.system.sD023D6490046BA0250A839A9AD24C443.TypeSystemHolder.loadTypeSystem(Unknown Source)
at schemaorg_apache_xmlbeans.system.sD023D6490046BA0250A839A9AD24C443.TypeSystemHolder.<clinit>(Unknown Source)
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:342)
at com.MyClient.AD.util.UploadEstimateUT.start(UploadEstimateUT.java:85)
at com.MyClient.AD.util.UploadEstimates.doPost(UploadEstimates.java:107)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:754)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:847)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:219)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:110)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:492)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:165)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:104)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:1025)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:452)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1195)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:654)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:319)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Unknown Source)

This is how I referenced the POI files: MyProject -> Right Click -> Build Path -> Configure Built Path -> Libraries tab and added the following jars

And this is how I execute the application Run -> Run Configuration... -> Classpath tab -> Making sure all jar files are referenced here including Apache POI's -> Apply and/or Run.

---

Update 1 (28-Feb-2020)

I tried the same code (that reads from excel using POI library) as java application and it worked as expected.

Another thing I noted when debugging the JSP web application is that the control is transferred directly to the finally block instead of the general Exception block. Not sure why.

---

Update 2 (03-Mar-2020)

I configured the project in my colleagues system who also has Eclipse IDE but with Tomcat server version 9.0.31 and it worked in his machine.

Then I installed Tomcat server version 9.0.31 and configured the project from a separate workspace but when executing I face the same exception. I'm not able to understand what could the difference.

We both are using Eclipse IDE for Enterprise Java Developers. Version: 2019-09 R (4.13.0) Build id: 20190917-1200

---

Update 3 (06-Mar-2020)

magicmn, I get the below exception after updating to factory method

    SEVERE: Servlet.service() for servlet [UploadEstimates] in context with path [/AD] threw exception [Servlet execution threw an exception] with root cause
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)
    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)
    at schemaorg_apache_xmlbeans.system.sD023D6490046BA0250A839A9AD24C443.TypeSystemHolder.loadTypeSystem(Unknown Source)
    at schemaorg_apache_xmlbeans.system.sD023D6490046BA0250A839A9AD24C443.TypeSystemHolder.<clinit>(Unknown Source)
    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.XSSFWorkbookFactory.createWorkbook(XSSFWorkbookFactory.java:97)
    at org.apache.poi.xssf.usermodel.XSSFWorkbookFactory.createWorkbook(XSSFWorkbookFactory.java:125)
    at org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:327)
    at org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:295)
    at org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:276)
    at com.MyClient.AD.util.UploadEstimateUT.start(UploadEstimateUT.java:90)
    at com.MyClient.AD.util.UploadEstimates.doPost(UploadEstimates.java:108)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:660)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:741)
    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:53)
    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:202)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:541)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
    at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:688)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343)
    at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:367)
    at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
    at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:868)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1639)
    at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    at java.lang.Thread.run(Unknown Source)

Answer 1

The NullPointerException is thrown at line 33 of ClassLoaderResourceLoader. The line is:

return _classLoader.getResourceAsStream(resourceName);

Which means that _classLoader is null. _classLoader is passed to the constructor.

The ClassLoaderResourceLoader object is created at line 182 of SchemaTypeSystemImpl:

_classloader = indexclass.getClassLoader();
...
_resourceLoader = new ClassLoaderResourceLoader(_classloader);

Which means that _classloader is null, which means that indexclass.getClassLoader() is null.

When can getClassLoader() return null? The documentation says:

Some implementations may use null to represent the bootstrap class loader. This method will return null in such implementations if this class was loaded by the bootstrap class loader.

Bootstrap? Does it ring a bell? Yes, you put all your jars in the Bootstrap Entries, which means that they are loaded by the bootstrap class loader. As I said in a comment (which you seem to have ignored), the correct location for jars is the WEB-INF\lib directory of the application. So just put them in there and it will work.

Answer 2

I know I had a similar problem with POI when I received a Resource from a Spring rest service, before saving and then reopening it.

Have you tried the following things?

• Write the file somewhere else. Then read it.

• Use an xls Workbook. Generally I recommend to use Workbook workbook = WorkbookFactory.create(input); to create your Workbook. One of the great strengths of POI is that it offers the Workbook interface to have standardized operations for XLS and XLSX files.

Answer 3

Are you sure upload is finished before you try to read file? because from stacktrace: at com.MyClient.AD.util.UploadEstimateUT.start(UploadEstimateUT.java:85) it's on start of upload

Answer 4

I tried posting a comment but my rank does not allow me to do so. This is not an answer(so please don't be harsh) but it is what I would've done in your case(I have been working with java and excel files for the last month and I am quite good).

File myFile=new File(args[0]);
if(myFile==null)System.out.println("my file is null");else System.out.println("my file is NOT null");
FileInputStream file = new FileInputStream(new File(args[0]));

You can post your results and I will take a stab at it.