java.lang.NoSuchMethodError: org.apache.poi.ss.usermodel.Workbook.sheetIterator()Ljava/util/Iterator;

Question

please i need some help on the error. I have a javafx project with the following jar files

• fontawesome-fx-8.1.jar
• sqlite-jdbc-3.8.10.1.jar
• controlsfx-8.40.11.jar
• sqljdbc42.jar
• jfoenix-1.0.0.jar
• POI-3.17.jar
• poi-examples-3.17.jar
• poi-excelant-3.17.jar
• poi-ooxml-3.17.jar
• poi-ooxml-schemas-3.17.jar
• poi-scratchpad-3.17.jar

And what i need is to import some excel data to it. I have imported the above jar files

But unfortunately when i try to run the project i get the error:

Caused by: java.lang.NoSuchMethodError: org.apache.poi.ss.usermodel.Workbook.sheetIterator()Ljava/util/Iterator;
at app.controllers.ContentAreaController.nextActionReport(ContentAreaController.java:734)
... 62 more

I have tried googling and what suggested is i change the versions of poi lib files but no luck .Can anyone suggest me the solution as i have spent enough time on the issue

Answer 1

Promoting some comments to an answer - you have older Apache POI jars on your classpath. As per this POI FAQ - mixing POI jars between versions is not supported

What you need to do is just remove the older POI jars. I say just, since you didn't know you had them... Luckily, if you follow the code in this Apache POI FAQ it'll help you find where the older jars are coming from. Something like this when run on your problematic system should print out the names and locations of the older jars:

ClassLoader classloader =
     org.apache.poi.poifs.filesystem.POIFSFileSystem.class.getClassLoader();
URL res = classloader.getResource(
         "org/apache/poi/poifs/filesystem/POIFSFileSystem.class");
String path = res.getPath();
System.out.println("POI Core came from " + path);

classloader = org.apache.poi.POIXMLDocument.class.getClassLoader();
res = classloader.getResource("org/apache/poi/POIXMLDocument.class");
path = res.getPath();

System.out.println("POI OOXML came from " + path);

classloader = org.apache.poi.hslf.usermodel.HSLFSlideShow.class.getClassLoader();
res = classloader.getResource("org/apache/poi/hslf/usermodel/HSLFSlideShow.class");
path = res.getPath();
System.out.println("POI Scratchpad came from " + path);

Just identify the older jars you don't want, remove, and you should be set!

Answer 2

Gagravarr's answer https://stackoverflow.com/a/50472754/1497139 pointed to http://poi.apache.org/help/faq.html#faq-N10006

I modified the code to be used the JUnit Test to fix the same base issue with the following error messages:

java.lang.NoSuchMethodError: org.apache.poi.xssf.usermodel.XSSFCell.getCellTypeEnum()Lorg/apache/poi/ss/usermodel/CellType;

Which if first thought was an issue of deprecation. After fixing the deprecation i got:

java.lang.NoSuchMethodError: org.apache.poi.xssf.usermodel.XSSFCell.getCellType()Lorg/apache/poi/ss/usermodel/CellType;

which was caused by mixing Apache POI 3.12 and Apache POI 4.0.1 jars in the same project.

To avoid this for the future I created the following Unit test. You might want to adapt the version to your needs or skip the assertion at all while still debugging the issue.

JUnit Test to check POI versions of used classes.

  /**
   * get the path the given class was loaded from
   * 
   * @param clazz
   * @return the path
   */
  public String getClassLoaderPath(Class<?> clazz) {
    ClassLoader classloader = clazz.getClassLoader();
    String resource = clazz.getName().replaceAll("\\.", "/") + ".class";
    URL res = classloader.getResource(resource);
    String path = res.getPath();
    return path;
  }

  @Test
  public void testPOI() {
    Class<?>[] classes = {
        org.apache.poi.poifs.filesystem.POIFSFileSystem.class,
        org.apache.poi.ooxml.POIXMLDocument.class,
        org.apache.poi.hslf.usermodel.HSLFSlideShow.class,
        org.apache.poi.xssf.usermodel.XSSFCell.class,
        org.apache.poi.ss.usermodel.CellType.class};
    for (Class<?> clazz : classes) {
      String path = getClassLoaderPath(clazz);
      if (debug)
        System.out.println(
            String.format("%s came from %s", clazz.getSimpleName(), path));
      assertTrue(path.contains("4.0.1"));
    }
  }