• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Using Apache POI to create an Excel .xlsx spreadsheet

 
Ranch Hand
Posts: 74
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I am trying to write a Java program that will convert database table data to an Excel "*.xlsx" spreadsheet, but I have run into nothing but problems.

Lots of problems.

I apparently am running into an endless array of jar dependencies that poi-5.0.0.jar, poi-ooxml-full.jar, ooxml-schemas-1.4.jar, XMLBeans, Saxon, Log4J, etc., can't solve.

my error:

Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/poi/POIXMLTypeLoader
at org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorkbook$Factory.newInstance(Unknown Source)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.onWorkbookCreate(XSSFWorkbook.java:452)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:255)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:249)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:237)
at com.dandylabs.DandyLabsGalleryViewsXLSGenerator.createHeader(DandyLabsGalleryViewsXLSGenerator.java:126)
at com.dandylabs.DandyLabsGalleryViewsXLSGenerator.toXLS(DandyLabsGalleryViewsXLSGenerator.java:324)
at com.dandylabs.DandyLabsGalleryViewsXLSGenerator.perform(DandyLabsGalleryViewsXLSGenerator.java:259)
at com.dandylabs.DandyLabsGalleryViewsXLSGenerator.main(DandyLabsGalleryViewsXLSGenerator.java:359)
Caused by: java.lang.ClassNotFoundException: org.apache.poi.POIXMLTypeLoader
at java.base/jdk.internal.loader.BuiltinClassLoader.loadClass(BuiltinClassLoader.java:602)
at java.base/jdk.internal.loader.ClassLoaders$AppClassLoader.loadClass(ClassLoaders.java:178)
at java.base/java.lang.ClassLoader.loadClass(ClassLoader.java:522)
... 9 more



It's just a simple method so far to create the spreadsheet workbook, sheet, and header, but it fails:



Should I give up and revert to an ".xls" Excel 97 format instead? Or not bother trying at all?

Thanks
 
Saloon Keeper
Posts: 10804
86
Eclipse IDE Firefox Browser MySQL Database VI Editor Java Windows ChatGPT
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
This worked for me so I suspect DandyLabs. What is that?
 
Phillip Powell
Ranch Hand
Posts: 74
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Carey Brown wrote:This worked for me so I suspect DandyLabs. What is that?

What jar files were you obligated to include in your classpath?

 
Phillip Powell
Ranch Hand
Posts: 74
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Phillip Powell wrote:

Carey Brown wrote:This worked for me so I suspect DandyLabs. What is that?

What jar files were you obligated to include in your classpath?



Because I am including a plethora of Apache POI jar files, and it still never seems enough.  If I use HSSFWorkbook instead of XSSFWorkbook, things work better, but otherwise I get this error, and I have this class in my poi-5.0.0.jar file:


Caused by: java.lang.ClassNotFoundException: org.apache.poi.POIXMLTypeLoader
at java.base/jdk.internal.loader.BuiltinClassLoader.loadClass(BuiltinClassLoader.java:602)
at java.base/jdk.internal.loader.ClassLoaders$AppClassLoader.loadClass(ClassLoaders.java:178)
at java.base/java.lang.ClassLoader.loadClass(ClassLoader.java:522)
... 8 more

 
Marshal
Posts: 28263
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
One post I found elsewhere said

Stack Overflow wrote:... it seems that in new versions of Apache POI, a reference to org.apache.poi.POIXMLTypeLoader persists, in new versions this class was moved to org.apache.poi.ooxml.POIXMLTypeLoader...



However, if you're using an IDE then usually it will tell you that "POIXMLTypeLoader" isn't imported (remove its import statement) and you can click something to have it find the correct package from the classpath.
 
Carey Brown
Saloon Keeper
Posts: 10804
86
Eclipse IDE Firefox Browser MySQL Database VI Editor Java Windows ChatGPT
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
commons-collections4-4.1.jar
poi-3.17.jar
poi-3.8-20120326.jar
poi-ooxml-3.17.jar
poi-ooxml-schemas-3.17.jar
xmlbeans-5.1.3.jar

granted these are somewhat dated
 
Phillip Powell
Ranch Hand
Posts: 74
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Carey Brown wrote:commons-collections4-4.1.jar
poi-3.17.jar
poi-3.8-20120326.jar
poi-ooxml-3.17.jar
poi-ooxml-schemas-3.17.jar
xmlbeans-5.1.3.jar

granted these are somewhat dated



The exact same jar files (mine being newer versions, but might scale back to older versions), and yet I am riddled with NoClassDefFoundError for everything from com.zaxxer.sparsebits.SparseBitSet to POIXMLTypeLoader to whatever happens next..
 
Phillip Powell
Ranch Hand
Posts: 74
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Paul Clapham wrote:One post I found elsewhere said

Stack Overflow wrote:... it seems that in new versions of Apache POI, a reference to org.apache.poi.POIXMLTypeLoader persists, in new versions this class was moved to org.apache.poi.ooxml.POIXMLTypeLoader...



However, if you're using an IDE then usually it will tell you that "POIXMLTypeLoader" isn't imported (remove its import statement) and you can click something to have it find the correct package from the classpath.



I gave up for now and reverted to using HSSFWorkbook and "*.xls", because I will never master this. And now I have this

class org.apache.poi.hssf.usermodel.HSSFPatriarch cannot be cast to class org.apache.poi.xssf.usermodel.XSSFDrawing (org.apache.poi.hssf.usermodel.HSSFPatriarch and org.apache.poi.xssf.usermodel.XSSFDrawing are in unnamed module of loader 'app')

 
Carey Brown
Saloon Keeper
Posts: 10804
86
Eclipse IDE Firefox Browser MySQL Database VI Editor Java Windows ChatGPT
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I did swap in "DEFAULT_TYPE_NAME". Don't know what difference that would have made.
 
Phillip Powell
Ranch Hand
Posts: 74
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Carey Brown wrote:I did swap in "DEFAULT_TYPE_NAME". Don't know what difference that would have made.



In my case it made no difference; no columns were resized, and I had to comment out large blocks of code to get it to work at all:



 
Paul Clapham
Marshal
Posts: 28263
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Phillip Powell wrote:... and yet I am riddled with NoClassDefFoundError for everything from com.zaxxer.sparsebits.SparseBitSet to POIXMLTypeLoader to whatever happens next..


But this just sounds like your classpath hasn't been set correctly (or at all).
 
Phillip Powell
Ranch Hand
Posts: 74
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Paul Clapham wrote:

Phillip Powell wrote:... and yet I am riddled with NoClassDefFoundError for everything from com.zaxxer.sparsebits.SparseBitSet to POIXMLTypeLoader to whatever happens next..


But this just sounds like your classpath hasn't been set correctly (or at all).



I am using Eclipse, and my classpath has the following:


commons-collections4-4.3.jar
commons-compress-1.26.1.jar
dom4j-1.6.jar
HikariCP-5.1.0.jar
poi-5.0.0.jar
poi-ooxml-5.0.0.jar
poi-ooxml-schemas-3.6.jar
slf4j-api-1.7.36.jar
SparseBitSet-1.3.jar
stax-api-1.0.1.jar
xml-apis-1.4.01.wso2v1.jar
xmlbeans-5.2.0.jar



I don't know what I'm missing
 
Paul Clapham
Marshal
Posts: 28263
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Phillip Powell wrote:I am using Eclipse



Good. Then delete all of the import statements from your code. This will produce red lines under all of those POI classes. Hovering over the red lines will produce a drop-down which may include the option to add an import statement for the class. If there are two such options then you may have too much stuff in your classpath, and if there are none then your classpath needs the jar file which has that class. Or else its name is misspelled.
 
Phillip Powell
Ranch Hand
Posts: 74
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Good. Then delete all of the import statements from your code. This will produce red lines under all of those POI classes. Hovering over the red lines will produce a drop-down which may include the option to add an import statement for the class. If there are two such options then you may have too much stuff in your classpath, and if there are none then your classpath needs the jar file which has that class. Or else its name is misspelled.



Did all of that:

  • Deleted all import statements
  • Hovered over red lines to obtain required imports
  • Paired down all jar files in classpath to the bare minimum required


  • At this point it will compile, however, it still fails when attempting to do simply this:




    Caused by: java.lang.ClassNotFoundException: org.apache.poi.POIXMLTypeLoader
    at java.base/jdk.internal.loader.BuiltinClassLoader.loadClass(BuiltinClassLoader.java:602)
    at java.base/jdk.internal.loader.ClassLoaders$AppClassLoader.loadClass(ClassLoaders.java:178)
    at java.base/java.lang.ClassLoader.loadClass(ClassLoader.java:522)
    ... 8 more

     
    Paul Clapham
    Marshal
    Posts: 28263
    95
    Eclipse IDE Firefox Browser MySQL Database
    • Likes 1
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator

    Stack Overflow wrote:... it seems that in new versions of Apache POI, a reference to org.apache.poi.POIXMLTypeLoader persists, in new versions this class was moved to org.apache.poi.ooxml.POIXMLTypeLoader...



    So it looks like that could be a problem in the POI jars themselves. I'm using poi-4.1.2.jar and poi-ooxml-4.1.2.jar and poi-ooxml-schemas-4.1.2.jar myself, installed in my system in March 2020, with no problems. Maybe you could try those?

    (I update my OSS jars periodically, but not that often. I updated Commons CSV last year and that was a pain because they improved the design and I had to change my code to match their changes. And they didn't improve the documentation to match.)
     
    Phillip Powell
    Ranch Hand
    Posts: 74
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator

    Paul Clapham wrote:

    Stack Overflow wrote:... it seems that in new versions of Apache POI, a reference to org.apache.poi.POIXMLTypeLoader persists, in new versions this class was moved to org.apache.poi.ooxml.POIXMLTypeLoader...



    So it looks like that could be a problem in the POI jars themselves. I'm using poi-4.1.2.jar and poi-ooxml-4.1.2.jar and poi-ooxml-schemas-4.1.2.jar myself, installed in my system in March 2020, with no problems. Maybe you could try those?

    (I update my OSS jars periodically, but not that often. I updated Commons CSV last year and that was a pain because they improved the design and I had to change my code to match their changes. And they didn't improve the documentation to match.)



    I wound up scaling back to older versions of poi, poi-ooxml, poi-ooxml-schemas, and xmlbeans, and no more errors!  However, I am getting warnings, but it does successfully run:


    WARNING: An illegal reflective access operation has occurred
    WARNING: Illegal reflective access by org.apache.poi.util.DocumentHelper (file:/C:/Users/phill/OneDrive/Desktop/jar/poi-ooxml-3.17.jar) to constructor com.sun.org.apache.xerces.internal.util.SecurityManager()
    WARNING: Please consider reporting this to the maintainers of org.apache.poi.util.DocumentHelper
    WARNING: Use --illegal-access=warn to enable warnings of further illegal reflective access operations
    WARNING: All illegal access operations will be denied in a future release



    I am using


    poi-3.17.jar
    poi-ooxml-3.17.jar
    poi-ooxml-schemas-3.17.jar
    xmlbeans-2.3.0.jar



    Not sure what I need to do about the console warnings other than, for now, live with it.  I still have a lot of cleanup to do with the class file, but thanks
     
    Paul Clapham
    Marshal
    Posts: 28263
    95
    Eclipse IDE Firefox Browser MySQL Database
    • Likes 1
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    In Java 9 the "reflection" access methods were restricted as part of the "modularization" of Java. So far, this just produces warnings, which are designed to prompt you to fix your code to do reflection differently.

    Java 9 Illegal Reflective Access Warning

    It's not your code so you can't do anything , except to upgrade to the later version in which this issue has presumably been fixed. Catch-22! But your other option is to ignore the warnings.

     
    Saloon Keeper
    Posts: 7601
    177
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    These days any large library will have numerous dependencies. Trying to handle those manually is a sizeable task (as you have discovered), and I would strongly recommend not to do that. Instead, use a build system like Gradle or Maven (or even Ant with Ivy, although that's seriously outdated) that can do it for you.

    For POI specifically, mixing jar files from different versions will very likely lead to problems.

    I have used many version of POI over the last 20+ years, and have found it to have excellent backward compatibility. So in general there's no reason not to use the latest release version.
     
    Phillip Powell
    Ranch Hand
    Posts: 74
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator

    Paul Clapham wrote:In Java 9 the "reflection" access methods were restricted as part of the "modularization" of Java. So far, this just produces warnings, which are designed to prompt you to fix your code to do reflection differently.

    Java 9 Illegal Reflective Access Warning

    It's not your code so you can't do anything , except to upgrade to the later version in which this issue has presumably been fixed. Catch-22! But your other option is to ignore the warnings.



    I finally got it stable enough to not throw Runtime errors (just warnings), however, the sheet doesn't auto resize to accommodate for long data, and the images embedded are overlapping over each other and subsequent rows and columns.  In short, it's a mess.

     
    That is a really big piece of pie for such a tiny ad:
    a bit of art, as a gift, the permaculture playing cards
    https://gardener-gift.com
    reply
      Bookmark Topic Watch Topic
    • New Topic