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.

.xlsm

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.

main

.xlsx

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?

test_output.xlsx

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.

XSSF*

HSSF*

.xls

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

[Content_Types].xml

.xlsx

<?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

[Content_Types].xml

.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>


*.xlsx

/[Content_Types].xml

1 Answer
1

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

build.gradle

<?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.

ojdbc8.jar

ojdbc8.jar

org.w3c.dom.Element

oracle.jdbc.pooling.LinkedListPool.Element

oracle.jdbc.pooling.PoolIteratorQueue.Element

xmlparserv2.jar

apache poi

Spring Boot

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.