Creating xlsx file using XSSFWorkbook, but getting error while using method createSheet("String")

Question

I have tried to include all the jars needed in Referenced Libraries. There are similar questions on this forum, I have gone through all but unable to resolve the issue.

My Code Snippet:

Workbook workbook = new XSSFWorkbook();
        CreationHelper createHelper = workbook.getCreationHelper();
        Sheet sheet = workbook.createSheet("Gene");
        Font headerFont = workbook.createFont();

All the jars that I am using

I am getting following errors:

Exception stack trace:

  org.apache.poi.ooxml.POIXMLException: org.apache.poi.ooxml.POIXMLException: java.lang.reflect.InvocationTargetException
        at org.apache.poi.ooxml.POIXMLDocumentPart.createRelationship(POIXMLDocumentPart.java:602)
        at org.apache.poi.xssf.usermodel.XSSFWorkbook.createSheet(XSSFWorkbook.java:896)
        at org.apache.poi.xssf.usermodel.XSSFWorkbook.createSheet(XSSFWorkbook.java:807)
        at org.apache.poi.xssf.usermodel.XSSFWorkbook.createSheet(XSSFWorkbook.java:122)
        at DomParser.main(DomParser.java:18)
    Caused by: org.apache.poi.ooxml.POIXMLException: java.lang.reflect.InvocationTargetException
        at org.apache.poi.ooxml.POIXMLFactory.newDocumentPart(POIXMLFactory.java:111)
        at org.apache.poi.ooxml.POIXMLDocumentPart.createRelationship(POIXMLDocumentPart.java:587)
        ... 4 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)
        at org.apache.poi.xssf.usermodel.XSSFFactory.createDocumentPart(XSSFFactory.java:56)
        at org.apache.poi.ooxml.POIXMLFactory.newDocumentPart(POIXMLFactory.java:109)
        ... 5 more
    Caused by: java.lang.NoSuchMethodError: org.openxmlformats.schemas.spreadsheetml.x2006.main.impl.CTWorksheetImpl.generatedSetterHelperImpl(Lorg/apache/xmlbeans/XmlObject;Ljavax/xml/namespace/QName;IS)Lorg/apache/xmlbeans/XmlObject;
        at org.openxmlformats.schemas.spreadsheetml.x2006.main.impl.CTWorksheetImpl.setColsArray(Unknown Source)
        at org.apache.poi.xssf.usermodel.helpers.ColumnHelper.cleanColumns(ColumnHelper.java:66)
        at org.apache.poi.xssf.usermodel.helpers.ColumnHelper.<init>(ColumnHelper.java:46)
        at org.apache.poi.xssf.usermodel.XSSFSheet.onDocumentCreate(XSSFSheet.java:259)
        at org.apache.poi.xssf.usermodel.XSSFSheet.<init>(XSSFSheet.java:187)
        ... 11 more

Is that the complete stacktrace? I had used even less number of jar for something similar and it worked fine for me. [Jars used](https://imgur.com/a/67swgvo) — dShringi, Oct 03 '18 at 14:51
Please try with `xmlbeans-3.0.1` instead of `xmlbeans-5.1.3` and let us know if it helped. — Cos64, Oct 03 '18 at 15:00
Yes, that is complete stacktrace. I have included extra jars to avoid the errors, but had no luck, I just wanted to know that all my jars are compatible or not — Akshay Tenkale, Oct 03 '18 at 15:00
Thank you Cos64. It worked. I had to remove xmlbeans-5.1.3 and use xmlbeans-3.0.1 instead. Could you please elaborate what was issue? Thank you once again — Akshay Tenkale, Oct 03 '18 at 15:05
https://mvnrepository.com/artifact/org.ow2.jonas.osgi/xmlbeans/5.1.3 is not the official xmlbeans jar - it appears to be a copy of xmlbeans made in 2008. — PJ Fanning, Oct 03 '18 at 19:01

Cos64 · Accepted Answer · 2018-10-03T15:24:22.320

You need to use xmlbeans-3.0.1 instead of xmlbeans-5.1.3.

Starting from the error, which was:

Caused by: java.lang.NoSuchMethodError: org.openxmlformats.schemas.spreadsheetml.x2006.main.impl.CTWorksheetImpl.generatedSetterHelperImpl(Lorg/apache/xmlbeans/XmlObject;Ljavax/xml/namespace/QName;IS)Lorg/apache/xmlbeans/XmlObject;

I searched the Maven Central Repository for jars containing the class CTWorksheetImpl: https://search.maven.org/search?q=fc:org.openxmlformats.schemas.spreadsheetml.x2006.main.impl.CTWorksheetImpl

The answer was poi-ooxml-schemas, which you already had, and was the same version as poi. Seemed OK. Then I thought it had to be the method itself. The parameters were from the org.apache.xmlbeans package, so the xmlbeans version was probably wrong.

How can you find out the right version?

Rather than gathering jar files manually, you may want to try Maven for dependency management. In a Maven project, it would be enough to say you want poi-ooxml. This would automatically bring in all the dependencies, recursively, with the right versions.

This is an example Maven pom.xml file you could use your project:

<project xmlns="http://maven.apache.org/POM/4.0.0"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.example</groupId>
    <artifactId>poi-example</artifactId>
    <version>0.0.1-SNAPSHOT</version>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.0.0</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.8.0</version>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

Even if you don't want to (or can't) use Maven in your project, you could at least do a separate project just for finding out the dependencies, like I did.

Thanks for the explanation. – Akshay Tenkale Oct 03 '18 at 15:20 — Akshay Tenkale, Oct 03 '18 at 15:20

Creating xlsx file using XSSFWorkbook, but getting error while using method createSheet("String")

1 Answers1