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

Answer 1

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.