Bug 45431 - XSSFWorkbook cannot save a macro-enabled Excel (.xlsm)
Summary: XSSFWorkbook cannot save a macro-enabled Excel (.xlsm)
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: unspecified
Hardware: PC Windows XP
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2008-07-18 02:42 UTC by Matthew
Modified: 2016-03-31 06:08 UTC (History)
0 users

Input Excel File (10.81 KB, application/zip)
2008-07-18 02:44 UTC, Matthew
Macro-enabled workbook package structure: vmlDrawing, activeX... (18.32 KB, image/jpeg)
2008-07-20 18:54 UTC, Matthew
My patch to solve "save macro-enabled workbook" issue, temporary solution (24.49 KB, application/octet-stream)
2008-07-20 18:55 UTC, Matthew

Note You need to log in before you can comment on or make changes to this bug.
Description Matthew 2008-07-18 02:42:57 UTC
I am using POI 3.5 Beta 1.
When I open a macro-enabled Excel (.xlsm) and save it as .xslm again.
The result file cannot be opened by MS Excel 2007.

Following is my sample codes:

XSSFWorkbook wb = new XSSFWorkbook("input.xlsm");
FileOutputStream outStream = new FileOutputStream("output.xlsm");

Is that currently POI not support a macro-enabled Excel file? Thanks a lot.
Comment 1 Matthew 2008-07-18 02:44:02 UTC
Created attachment 22280 [details]
Input Excel File
Comment 2 Nick Burch 2008-07-18 11:24:36 UTC
We weren't copying the vba stream over into the new file, which wasn't helping

I've updated poi to copy over the vba stream if there is one. However, excel still refuses to load the file, so I don't know what else we're supposed to do. Clearly there is a secret hidden reference somewhere that we're not updating or something

It may be worth closely comparing the un-zipped contents of the pre-poi and post-poi files, to see if you can spot any important bits that differ
Comment 3 Matthew 2008-07-20 18:48:36 UTC
I compared the pre-poi and post-poi files, I have following findings:

(1) In [Content_Types].xml:

Excel expected output:
<Override PartName="/xl/workbook.xml" ContentType="application/vnd.ms-excel.sheet.macroEnabled.main+xml"/>

POI actual output:
<Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>

(2) In [Content_Types].xml:

Excel expected output:
<Default Extension="vml" ContentType="application/vnd.openxmlformats-officedocument.vmlDrawing"/>
<Override PartName="/xl/vbaProject0.bin" ContentType="application/vnd.ms-office.vbaProject"/>

POI actual output (the case difference):
<Default Extension="vml" ContentType="application/vnd.openxmlformats-officedocument.vmldrawing"/>
<Override PartName="/xl/vbaProject0.bin" ContentType="application/vnd.ms-office.vbaproject"/>

I found that this problem is caused by OpenXml4J (http://people.apache.org/~nick/openxml4j-bin-alpha-080407.jar), I tried to use the library with a newer version, it fix this issue but I encountered another problem which doesn't happen in older version.

(3) Package contents:
Some .xlsm file contains not only vbaProject.bin, it also includes ActiveX controls and some .emf image files linked. If only copy the vbaProject.bin without the linked ActiveX controls and image, Excel will prompt error also. Please check my attached image.

I have created a patch which solve above problems (assume OpenXML4J (2) issue is fixed), but I know the patch just only solve my problem temporary (I hard code the output is macro-enabled workbook, image is .emf etc...) and it is not a generic solution for the POI project. I hope it helps the POI team to figure out the perfect solution some ways.
Comment 4 Matthew 2008-07-20 18:54:17 UTC
Created attachment 22289 [details]
Macro-enabled workbook package structure: vmlDrawing, activeX...
Comment 5 Matthew 2008-07-20 18:55:25 UTC
Created attachment 22290 [details]
My patch to solve "save macro-enabled workbook" issue, temporary solution
Comment 6 Nick Burch 2008-07-21 02:12:00 UTC
Thanks for the patch, and all the digging that went with it!

I'll try to get us onto a newer version of openxml4j, and once the file part case issue is sorted, then I'll try to apply your patch :)
Comment 7 Nick Burch 2008-07-28 16:37:47 UTC
I've got poi using the latest openxml4j, so hopefully things will now work fine for you

I'll try to review + commit your patch in the near future
Comment 8 Nick Burch 2008-07-29 16:04:04 UTC
Could you upload a file similar to the one your image shows? I'd like one like that so I can write some more comprehensive unit tests for this stuff
Comment 9 Nick Burch 2008-07-29 17:31:19 UTC
Thanks for you patch. I've applied it to the svn branch, with some changes. I decided it'd make sense if we pushed a lot of the fiddly bits around reading and writing the sub-streams to XSSFRelation, rather than duplicating it each time. So, the code in XSSFWorkbook is very different, the others less so

I'm able to open your initial file, save it, and have excel load it with no issues. Hopefully your complex file will work too, but please upload it so we can write unit tests for it :)
Comment 10 Matthew 2008-12-30 01:47:18 UTC
This problem is solved in latest build.