Bug 50331 - Writing HSSFWorkbook with macro causes lost macro project
Summary: Writing HSSFWorkbook with macro causes lost macro project
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.7-FINAL
Hardware: PC Windows XP
: P2 major (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2010-11-24 17:12 UTC by Steve Biggs
Modified: 2010-12-12 07:25 UTC (History)
1 user (show)

Sample Excel file with macro (21.00 KB, application/octet-stream)
2010-11-24 17:12 UTC, Steve Biggs

Note You need to log in before you can comment on or make changes to this bug.
Description Steve Biggs 2010-11-24 17:12:43 UTC
Created attachment 26336 [details]
Sample Excel file with macro


Opening existing HSSFWorkbook that was created using Excel and contains a macro
and then writing the workbook to a new file causes the macro project to be lost when opening the spreadsheet in Excel.

Steps to Reproduce:

1. Create a workbook using Excel application.  Create a macro within the workbook.
2. Access workbook in Java using new HSSFWorkbook().
3. Write the workbook (no changes need to be made) to a new file.
4. Open newly saved workbook in Excel application.  Select enabled macros when prompted.

Actual Results: 

Excel opens and presents security warning asking whether to enable or disable macros (this is normal).  After selecting enable macros Excel presents a dialog that says "File not found".  Click ok on this dialog and Excel presents a new dialog that says "Excel found unreadable content in "test out.xls".  Do you want to recover the contents of this workbook?  If you trust the source of this workbook, click Yes."  After clicking yes Excel presents another security warning.  Click enable macros and a final dialog reports "Errors were detected in "test out.xls" but Microsoft Office Excel was able to open the file by making the repairs listed below.".  The repair listed in the dialog is "Lost Visual Basic project".  Close this dialog.

The contents of the worksheet appear correct, but go to Tools->Macro->Visual Basic Editor and the VBA project is gone.

Expected Results:

Excel should open the file without any errors.  The VBA project should be accessible.

Build Date & Platform: 

POI 3.7 beta3 2010-09-24
Windows XP Service Pack 3
Microsoft Office Excel 2003 SP3 (11.8324.8324)

Additional Information: 

Attached sample Excel file.

Java sample code:

public class TestHSSF {
   public static void main(String[] args) throws IOException {
      InputStream inputFile=null;
      FileOutputStream outputFile=null;
      inputFile=new FileInputStream("C:\\Documents and Settings\\Administrator\\Desktop\\test.xls");
      HSSFWorkbook wb=new HSSFWorkbook(new POIFSFileSystem(inputFile),false);
      outputFile=new FileOutputStream("C:\\Documents and Settings\\Administrator\\Desktop\\test out.xls");
Comment 1 Yegor Kozlov 2010-12-12 07:25:59 UTC
Pass 'true' as the second parameter in the constructor HSSFWorkbook(InputStream is, boolean preserveNodes) and you will be good. Or better use the default constructor HSSFWorkbook(InputStream is).

Excel file is a compound OLE2 document and the workbook stream and macros are nodes of the OLE2 document.  The second boolean parameter tells POI whether to preserve nodes other than Workbook, such as  macros. Passing 'false' truncates the OLE2 file system to only the Workbook stream and document properties which is exactly what is happening in your case.