2

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>
  • Have you tried with earlier versions of library? – soulcoder Jul 03 '18 at 09:36
  • I have tried with 3.13 to get a version earlier than 2016 without any luck. – Steffan Sølvsten Jul 03 '18 at 09:38
  • in fact I just try your example but it works, (I'm using Numbers in macOS) – Azarea Jul 03 '18 at 09:39
  • What Java version are you using? Maybe any kind of [Java 10](https://poi.apache.org/faq.html#faq-N102C9) problem? – Axel Richter Jul 03 '18 at 09:52
  • @Azarea Hmm, I have the problem with both Office 365 and Libre Office. How big is the `test_output.xlsx` file you end up with? @Axel Richter We're using Java 8 (`sourceCompatibility = 1.8` in `build.gradle`), from which we cannot upgrade due to use of Spring Boot. – Steffan Sølvsten Jul 03 '18 at 09:53
  • 1
    The lower file size is not any hint to failure here. Excel puts some unnecessary data in new files which POI doesn't. A `*.xlsx` file can simply be unzipped. Do that and do comparing `/[Content_Types].xml` of Excel generated and POI generated file. Because of error "Can't read content types part !". – Axel Richter Jul 03 '18 at 10:03
  • @AxelRichter I have changed the try-with-resources as you suggested though there is no difference when I run it - it still creates the file without any complaints. I also updated the post with the `[Content_Types].xml` file. I can't seem to find a description of a barebones version, but without the `ContentType` value I would guess it is missing information? – Steffan Sølvsten Jul 03 '18 at 10:32
  • Your `XML` in `[Content_Types].xml` definitely is wrong generated. There are only elements and some attribute content but none attribute names. But not reproducible, at least not for me. [ContentTypeManager](https://svn.apache.org/viewvc/poi/tags/REL_3_17_FINAL/src/ooxml/java/org/apache/poi/openxml4j/opc/internal/ContentTypeManager.java?view=markup) uses `org.w3c.dom.Element` for generating XML elements. Maybe there are some jars in your class path which are badly owerwriting this? – Axel Richter Jul 03 '18 at 10:46
  • @AxelRichter: what did you mean by "try-with-resources hides exception"? – Honza Zidek Jul 03 '18 at 10:51
  • @SteffanSølvsten: You wrote that you had tried with HSSF* and it works. What happens if you leave there XSSF on the right side, but properly use the interfaces on the left sides? I mean `Workbook document = new XSSFWorkbook(); Sheet spreadsheet = document.createSheet("Test");` – Honza Zidek Jul 03 '18 at 10:53
  • @AxelRichter: Oh, you mean if both the `write()` and `close()` methods throw an exception... OK. – Honza Zidek Jul 03 '18 at 10:56
  • The `[Content_Types].xml` after "removing dependencies to Spring Boot" looks correct now. And I suspect the `test_output.xlsx` also is no more corrupt now. I think you could get friends if you were able determining what exact dependency leads to the problem since there are others having problems using `apache poi` and `spring boot` together too. Me not because of not using `spring` at all. – Axel Richter Jul 03 '18 at 12:26
  • Yup, I'm also right now onto writing it as the answer, it just took quite a bit of time time from updating the post to actually finding the root cause. – Steffan Sølvsten Jul 03 '18 at 12:27

1 Answers1

3

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.

  • I am curious what exact class is in conflict there. But seems as if `ojdbc8.jar` is not public available, at least not without creating a Oracle account, what I don't want to do. Is there at least a class list of `ojdbc8.jar` public available somewhere? – Axel Richter Jul 04 '18 at 09:20
  • @AxelRichter Maybe not exactly what you were asking for, but I've run a .bat script to extract all the content of the .jar file. https://pastebin.com/9Nzw2REH – Steffan Sølvsten Jul 05 '18 at 11:53
  • Thank you. That is exactly what I was after. But now I also cannot see where the conflict may be. There are involved classes having same name **Element** : `org.w3c.dom.Element` and `oracle.jdbc.pooling.LinkedListPool.Element` and `oracle.jdbc.pooling.PoolIteratorQueue.Element`. But they are fully qualified named and imported. So there should not be any conflict. Mysterious that. – Axel Richter Jul 05 '18 at 13:08
  • @AxelRichter Maybe it is in the dependency of the _xmlparserv2.jar_ also downloaded together with _ojdbc8.jar_? It contains https://pastebin.com/LLEquXVf . It seems especially like there is some rerouting in the _META-INF/services_ folder. – Steffan Sølvsten Jul 06 '18 at 06:36
  • Yes, seems `xmlparserv2.jar` is the problem here. See https://stackoverflow.com/questions/6412736/how-can-i-tell-my-application-that-it-shouldnt-use-an-external-service-provider. Maybe good to know for others who wants using `apache poi` and `Spring Boot` together. – Axel Richter Jul 06 '18 at 08:23