|Summary:||Could not open macro enabled xlsm file after writing using POI3.11beta2 version.|
|Product:||POI||Reporter:||Ranjay Mishra <rmishra>|
|Component:||OPC||Assignee:||POI Developers List <dev>|
Error when opening exported xlsm file
[Content_type].xml of not working xlsm file
[Content_type].xml of working xlsm file
Sample xlsm file
Exported xlsm file , which is not working
Patch that fixes (hoepfully) all missing namespaces
Description Ranjay Mishra 2014-10-29 10:15:20 UTC
Created attachment 32162 [details] Error when opening exported xlsm file Hi, I am using poi3.11beta2 version to write a macro enabled .xlsm file.There is no error/exception during writing the while. After successful exception when I try to open the exported xlsm file It is is giving below error. It was working fine with poi3.9 version.The issue is occurring after upgrading it to poi3.11beta2 version. Thanks in advance. Regards, Ranjay Mishra
Comment 1 Nick Burch 2014-10-29 10:31:37 UTC
How do the files differ when written from the two versions of POI?
Comment 2 Ranjay Mishra 2014-10-29 10:55:16 UTC
There is no changes in file. Using the same xlsm file in both the cases and code is also same.Here is the xlsm related code for creating workbook. String filePath="c:\\test\\bomTableTemplate.xlsm" OPCPackage pkg = OPCPackage.open(new File(filePath)); XSSFWorkbook wbTemplate = new XSSFWorkbook(pkg); SXSSFWorkbook sxWb = new SXSSFWorkbook(wbTemplate); sxWb.setCompressTempFiles(true); Workbook wb=sxWb; Note: there is no issue with xls and xlsx file export.
Comment 3 Nick Burch 2014-10-29 11:02:11 UTC
There clearly is a difference in the written files, otherwise Excel wouldn't have started complaining! You'll need to unzip the two .xlsx files, and compare files present and the xml in them
Comment 4 Ranjay Mishra 2014-10-30 10:12:51 UTC
Created attachment 32166 [details] [Content_type].xml of not working xlsm file
Comment 5 Ranjay Mishra 2014-10-30 10:13:25 UTC
Created attachment 32167 [details] [Content_type].xml of working xlsm file
Comment 6 Ranjay Mishra 2014-10-30 10:16:26 UTC
Hi Nick, I compared both working and not working xlsm file and found that contents are same , workbook.xml is same. Only different I saw if [Content_Type].xml I have attached both the content type. Could you please have a look and let me know what making content_type.xml to be different and the solution for the same. Thanks, Ranjay Mishra
Comment 7 Andreas Beeker 2014-10-31 21:42:53 UTC
I can't reproduce it with the trunk, an arbitrary .xlsx and your sample code. Please add your .xlsx and the exact code. I guess this happens because of the marshalling changes in StreamHelper.saveXmlInStream.
Comment 9 Ranjay Mishra 2014-11-02 13:15:58 UTC
Hi, I have attached sample xlsm file(before export). Please see below for exact code. The same piece of code is working if I use only below 4 jars along with jre1.7 poi.jar, poi-ooxml.jar, poi-ooxlm-schema.jar and xmlbean2.6.0.jar for standalone project. But when I use the same code with all above 4 jars along with other jar in build path which ever was earlier , then it stop working. After comparing the generated xlsm file I found content-type is corrupt. Please note that same xlsm file , same code was working with same jars in build path with poi3.9. If this is happens because of the marshalling changes in StreamHelper.saveXmlInStream could you please let me know the work around or fix for that. I am also attaching the generated xlsm file(the corrupt file). CODE Used ---------- Workbook wb; OPCPackage pkg = OPCPackage.open("D:\\Users\\rmishra\\Desktop\\sample_template.xlsm"); XSSFWorkbook workbook=new XSSFWorkbook(pkg); SXSSFWorkbook sxWb = new SXSSFWorkbook(workbook); sxWb.setCompressTempFiles(false); FileOutputStream out = new FileOutputStream("D:\\DescribeLink\\sample_template.xlsm"); wb = sxWb; sxWb.write(out); out.close();
Comment 10 Ranjay Mishra 2014-11-02 13:17:38 UTC
Created attachment 32179 [details] Exported xlsm file , which is not working
Comment 11 Uwe Schindler (ASF) 2014-11-02 15:11:47 UTC
> I guess this happens because of the marshalling changes in StreamHelper.saveXmlInStream. I don't think this is the case. The difference in the Content-Types files is caused by a broken namespace in the elements contained in the root element. The DOM tree that is serialized is missing the correct namespace, so it get overwritten with "" (empty namespace). It looks like the code that creates the DOM tree for the content-types file is missing to attach the correct namespace to some elements. Could it be that this is fixed already in trunk?
Comment 12 Uwe Schindler (ASF) 2014-11-02 15:36:40 UTC
Created attachment 32180 [details] Patch that fixes (hoepfully) all missing namespaces This patch should fix the problem with the ContentTypes file. I also found another problem with relationships. The main issue was: The parant element was correctly created with createElementNS(), but the childs where created without namespace using createElement(). I fixed this, can somebody verify this (I have no time at the moment). If POI would use the forbidden-apis Maven/ant plugin, we could forbid to use createElement (without * NS). I will later look into the remaining code of OOXML to find other violations (just search for calls to Document#createElement).
Comment 13 Uwe Schindler (ASF) 2014-11-02 15:46:00 UTC
This was caused by #56814. Unfortunately, no test is failing because of this (the reason for this is that POI can read the file also with wrong namespaces, because the parser is very lenient...).
Comment 14 Uwe Schindler (ASF) 2014-11-02 16:59:42 UTC
I verified, the relationships file ".rels" was also broken in your provided file. So the issue that is fixed by my patch is also fixed there. Can you apply the patch to the source package of POI, compile the JARs and try again? Uwe
Comment 15 Uwe Schindler (ASF) 2014-11-02 17:13:43 UTC
I verified the example, works now. I checked Content_Types.xml and .rels file, both have correct namespaces (and same content as provided file). So I think the attached patch fixes the bug. I will commit it later today.
Comment 16 Uwe Schindler (ASF) 2014-11-02 17:15:06 UTC
File also opens with Excel after applying the fix.
Comment 17 Uwe Schindler (ASF) 2014-11-02 20:57:05 UTC
I fixed this in http://svn.apache.org/r1636188 If you want to try out the fix, you may use a nightly snapshot, once it is built: https://builds.apache.org/job/POI/lastSuccessfulBuild/artifact/build/dist/
Comment 18 Ranjay Mishra 2014-11-03 05:59:02 UTC
Thanks a lot!! I verifier with the latest jar distribution. Issue is resolved now.