Created attachment 33969 [details] Error in Excel Excel Files generated using XSSFWorkbook can't be opened using Ms-Excel or OpenOffice. POI can read those files. I used the official BusinessPlan example from the POI website: https://poi.apache.org/spreadsheet/examples.html#business-plan The xls file generated by this example works fine in all versions, but the xlsx file can't be opened when using POI 3.11 or higher (including 3.14). POI 3.10.1 works fine. Even an empty worbook (with just one sheet) cannot be opened when using xssf, but works fine in hssf: String format = "xlsx"; File outputFile = new File("C:/temp/output." + format); Workbook wb = new XSSFWorkbook(); wb.createSheet(); try (FileOutputStream fileOutputStream = new FileOutputStream(outputFile);) { wb.write(fileOutputStream); }
Does it work if you specify a sheet-name in the call to createSheet()?
(In reply to Dominik Stadler from comment #1) > Does it work if you specify a sheet-name in the call to createSheet()? No it does not. I have tried many different variants, and multiple of the official examples and both Ms-Excel and OpenOffice can't open any xlsx files that were created by a poi version 3.11 or above.
Are you using the same version of poi, poi-ooxml, and poi-ooxml-schemas? Does wb.write throw an exception?
(In reply to Javen O'Neal from comment #3) > Are you using the same version of poi, poi-ooxml, and poi-ooxml-schemas? > > Does wb.write throw an exception? I am using v3.14 of poi-ooxml and v1.3 of ooxml-shemas. I use Gradle to resolve transitive dependencies. I also tried all major versions between 3.9 and 3.14, but only 3.9 and 3.10.1 are working. I don't get any Exceptions in Java, only Ms-Excel and OpenOffice are telling me that the generated xlsx files are corrupted. I don't think it is an issue with Ms-Excel or OpenOffice, because they can open other xlsx files.
First of all, this sounded too fantastic, that all versions since 3.11 can't generate usual .xlsx files - ... and actually, I've just tried the businessplan with LO/Excel/ExcelViewer, just to be sure ... To investigate this we could either look what's wrong with the gradle build or maybe we can find the error, if you attach your resulting businessplan .xlsx of POI 3.10 and 3.14. My guess is, that there are either duplicates jars in the classpath / dependencies or the wrong ooxml-schemas (ooxml-schemas-1.3.jar for POI 3.14 or later, ooxml-schemas-1.1.jar for POI 3.7 up to POI 3.13). Can you package your example application and list the dependencies? (maybe we can spot something like a wrong version of xerces/saxon/xmlbeans/xmlbeans-xpath ..)
(In reply to Andreas Beeker from comment #5) > First of all, this sounded too fantastic, that all versions since 3.11 can't > generate usual .xlsx files - ... and actually, I've just tried the > businessplan with LO/Excel/ExcelViewer, just to be sure ... > > To investigate this we could either look what's wrong with the gradle build > or maybe we can find the error, if you attach your resulting businessplan > .xlsx of POI 3.10 and 3.14. > > My guess is, that there are either duplicates jars in the classpath / > dependencies or the wrong ooxml-schemas (ooxml-schemas-1.3.jar for POI 3.14 > or later, ooxml-schemas-1.1.jar for POI 3.7 up to POI 3.13). > > Can you package your example application and list the dependencies? > > (maybe we can spot something like a wrong version of > xerces/saxon/xmlbeans/xmlbeans-xpath ..) I can send you a full example project including gradle wrapper tomorrow. Did you not manage to reproduce my error?
(In reply to Andreas Beeker from comment #5) > First of all, this sounded too fantastic, that all versions since 3.11 can't > generate usual .xlsx files - ... and actually, I've just tried the > businessplan with LO/Excel/ExcelViewer, just to be sure ... > > To investigate this we could either look what's wrong with the gradle build > or maybe we can find the error, if you attach your resulting businessplan > .xlsx of POI 3.10 and 3.14. > > My guess is, that there are either duplicates jars in the classpath / > dependencies or the wrong ooxml-schemas (ooxml-schemas-1.3.jar for POI 3.14 > or later, ooxml-schemas-1.1.jar for POI 3.7 up to POI 3.13). > > Can you package your example application and list the dependencies? > > (maybe we can spot something like a wrong version of > xerces/saxon/xmlbeans/xmlbeans-xpath ..) Thanks, you were right :) I was able to track down the problem to my xalan library. I had 'xalan:xalan:2.4.0' in my classpath, because that is used in our application server. Luckily I don't actually need that dependency and can just remove it. I still created a git repository containing everything to create corrupted xlsx files, if you want to investigate. All you have to do is open a command line and run 'gradlew run' or './gradlew run' to create a new businessplan.xlsx. A corupted one is already checked into the repo aswell. Please tell me when you no longer need the repo so I can delete it.
Where can the project be accessed? You can also zip it up and attach it here if you want.
(In reply to Dominik Stadler from comment #8) > Where can the project be accessed? You can also zip it up and attach it here > if you want. Ups I forgot to post the link: https://github.com/Adrodoc55/poi-xalan-bug
Created attachment 34061 [details] Difference that causes Excel to report the file as "broken" It seems with Xalan you get some different XML parser as well and this way the namespace handling is broken, see the attached image.
I did some more investigation and it looks like in your case there was an old version of the Xml Parser pulled in which does not support namespaces. See class DocumentHelper.createDocument(), where we use classes from javax.xml.parsers to create an DOM XML Document. I could not find an easy way to check or force the use of namespaces here, so for now I don't think we can do much inside POI here. Therefore I am closing this as WONTFIX for now, please reopen if you have an idea how we can at least fail with a better error message in such cases.
I am fine with this beeing closed as wontfix. Thanks to your help I was able to fix the classpath. Even if there was a way to check the namespace handling of the XML parser it's not really subject to the poi implementation to check whether the classpath is correct. I think something like notabug would be even more appropriate than wontfix.