Bug 57162 - Could not open macro enabled xlsm file after writing using POI3.11beta2 version.
Summary: Could not open macro enabled xlsm file after writing using POI3.11beta2 version.
Alias: None
Product: POI
Classification: Unclassified
Component: OPC (show other bugs)
Version: 3.11-dev
Hardware: PC All
: P2 blocker (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2014-10-29 10:15 UTC by Ranjay Mishra
Modified: 2014-11-03 05:59 UTC (History)
0 users

Error when opening exported xlsm file (31.65 KB, image/jpeg)
2014-10-29 10:15 UTC, Ranjay Mishra
[Content_type].xml of not working xlsm file (3.38 KB, text/xml)
2014-10-30 10:12 UTC, Ranjay Mishra
[Content_type].xml of working xlsm file (2.76 KB, text/xml)
2014-10-30 10:13 UTC, Ranjay Mishra
Sample xlsm file (16.89 KB, application/vnd.ms-excel.sheet.macroenabled.12)
2014-11-02 13:04 UTC, Ranjay Mishra
Exported xlsm file , which is not working (14.28 KB, application/vnd.ms-excel.sheet.macroenabled.12)
2014-11-02 13:17 UTC, Ranjay Mishra
Patch that fixes (hoepfully) all missing namespaces (2.52 KB, patch)
2014-11-02 15:36 UTC, Uwe Schindler (ASF)
Details | Diff

Note You need to log in before you can comment on or make changes to this bug.
Description Ranjay Mishra 2014-10-29 10:15:20 UTC
Created attachment 32162 [details]
Error when opening exported xlsm file


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.

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

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 8 Ranjay Mishra 2014-11-02 13:04:40 UTC
Created attachment 32178 [details]
Sample xlsm file
Comment 9 Ranjay Mishra 2014-11-02 13:15:58 UTC
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).


 Workbook wb;
 OPCPackage pkg = OPCPackage.open("D:\\Users\\rmishra\\Desktop\\sample_template.xlsm");
 XSSFWorkbook workbook=new XSSFWorkbook(pkg);
 SXSSFWorkbook sxWb = new SXSSFWorkbook(workbook);
 FileOutputStream out = new FileOutputStream("D:\\DescribeLink\\sample_template.xlsm");
 wb = sxWb;
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?

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.