Search code examples
javaexcelapache-poixssf

XSSFWorkbook when written creates a corrupted .xlsx document in Spring Boot application using JDBC

For a project I need to create an .xlsm excel document automatically filling out a template file. Problem is, that the output is corrupted and cannot be read by Excel 365 nor by Apache POI.

I have distilled it down to the following minimal example, that can run in a main method. To be completely safe it is using the .xlsx format.

public static void main(String[] args) {
    XSSFWorkbook document = new XSSFWorkbook();
    XSSFSheet spreadsheet = document.createSheet("Test");
    spreadsheet.createRow(0).createCell(0).setCellValue("Testie test");

    // Output .xlsx file
    FileOutputStream stream;
    try {
        stream = new FileOutputStream("test_output.xlsx");
        document.write(stream);
        stream.flush();
        stream.close();

    } catch (IOException e) {
        System.err.println("Error" + e.getMessage());
        e.printStackTrace();
    }
    ...

The created file test_output.xlsx cannot be opened by Excel 365 and has a size of only 4kb, while a manually created one would take up 9kb, so there must be something missing in the output that I have not specified?

I'm using Apache POI version 3.17 imported via Gradle using

compile('org.apache.poi:poi-ooxml:3.17')

and also with Apache POI version 3.13 to have a version from before 2016. No luck in both cases.

When the main method is extended to also reopen the same file, that was just created, as shown below

    ...
    // Try to read it again
    try {
        document = new XSSFWorkbook("test_output.xlsx");
        System.out.println(document.getSheetAt(0).getRow(0).getCell(0).getStringCellValue());

     } catch (IOException e) {
         e.printStackTrace();
     }
}

then I end up in the following exception

java.io.IOException: org.apache.poi.openxml4j.exceptions.InvalidFormatException: Can't read content types part !
    at org.apache.poi.POIXMLDocument.openPackage(POIXMLDocument.java:91)
    at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:340)
    ...

If on the other hand all XSSF* is replaced with HSSF* and the file type made into a .xls file, then the document outputted is fine, yet I need to create a working Excel 365 document, not an Excel 2003 one.

The following is [Content_Types].xml file in the manually made .xlsx document

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">
    <Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>
    <Default Extension="xml" ContentType="application/xml"/>
    <Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>
    <Override PartName="/xl/worksheets/sheet1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>
    <Override PartName="/xl/theme/theme1.xml" ContentType="application/vnd.openxmlformats-officedocument.theme+xml"/>
    <Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/>
    <Override PartName="/xl/sharedStrings.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml"/>
    <Override PartName="/docProps/core.xml" ContentType="application/vnd.openxmlformats-package.core-properties+xml"/>
    <Override PartName="/docProps/app.xml" ContentType="application/vnd.openxmlformats-officedocument.extended-properties+xml"/>
</Types>

While the following is [Content_Types].xml in the POI created file .xlsx

<?xml version = '1.0' encoding = 'UTF-8' standalone = 'yes'?>
<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">
    <Default ="rels"/>
    <Default ="xml"/>
    <Override ="/docProps/app.xml"/>
    <Override ="/docProps/core.xml"/>
    <Override ="/xl/sharedStrings.xml"/>
    <Override ="/xl/styles.xml"/>
    <Override ="/xl/workbook.xml"/>
    <Override ="/xl/worksheets/sheet1.xml"/>
</Types>
Solution

  • Based on the suggestions of @AxelRichter when all other dependencies are removed in build.gradle then [Content_Types].xml looks as follows.

    <?xml version="1.0" encoding="UTF-8" standalone="no"?>
<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">
    <Default ContentType="application/vnd.openxmlformats-package.relationships+xml" Extension="rels"/>
    <Default ContentType="application/xml" Extension="xml"/>
    <Override ContentType="application/vnd.openxmlformats-officedocument.extended-properties+xml" PartName="/docProps/app.xml"/>
<Override ContentType="application/vnd.openxmlformats-package.core-properties+xml" PartName="/docProps/core.xml"/>
    <Override ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml" PartName="/xl/sharedStrings.xml"/>
    <Override ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml" PartName="/xl/styles.xml"/>
    <Override ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml" PartName="/xl/workbook.xml"/>
    <Override ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml" PartName="/xl/worksheets/sheet1.xml"/>
</Types>

    And the above code runs without issues and the file can be opened in Office 365. The problem lies in the import of

    compile('com.oracle.jdbc:ojdbc8:12.2.0.1')

    Which creates a conflict in the classpath for the XML parser used by Apache POI, meaning either the Excel part needs to be done in another project with Gradle handling the dependency issues.