We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Workbooks with XL4 macros (XLSM) become corrupt after save

Hello,

We’ve observed two related issues in Cells for Java when dealing with workbooks that contain legacy Excel 4 macro sheets (XLSM). To summarize:

  1. The Workbook.hasMacro() API returns ‘false’ for workbooks containing legacy Excel 4 macros. Our expectation is that this API would return ‘true’ if the workbook has VBA and/or Excel 4 macro sheets.

  2. After saving a workbook whose FileFormatType is XLSM the in-memory workbook instance is somehow coerced to a FileFormatType.XLSX.

These 2 issues become problematic because once macro-enabled workbook (XLSM) is coerced into a XLSX - there is no easy way to determine that the workbook should be saved as a XLSM. As a result, if you rely on the Workbook.getFileFormat API and save the workbook as XLSX - it becomes corrupt and cannot be opened in Excel.

The issues can be reproduced using the attached file ‘MacroDialogSheets.xlsm’ and the following code:

try {
 final String fileName = [WB_PATH] + "MacroDialogSheets.xlsm";
 final String ext = fileName.substring(fileName.lastIndexOf('.'));
 final ChoiceFormat fileType = new ChoiceFormat(
           new double[] { FileFormatType.XLSX, FileFormatType.XLSM },
           new String[] { "FileFormatType.XLSX (6)", "FileFormatType.XLSM (7)" });

Workbook wb = new Workbook(fileName);
int fileFmt = wb.getFileFormat();
System.out.println("Workbook's ORIGINAL File Format: " + fileType.format(fileFmt));

// NOTE: We can't use: 
// int fileFmt = (wb.hasMacro()? SaveFormat.XLSM : SaveFormat.XLSX);
// because 'hasMacros()' doesn't recognize the legacy XL4 Macros :
System.out.println("Workbook is macro enabled ? " + wb.hasMacro());

// Save the Workbook with the SaveFormat returned from workbook.getFileFormat
byte[] fileBytes;
SaveOptions options = new OoxmlSaveOptions(fileFmt);
try (ByteArrayOutputStream stream = new ByteArrayOutputStream()) {
    wb.save(stream, options);
    fileBytes = stream.toByteArray();
}

// Something happened during wb.save - all of the sudden the WB type is XLSX
int newFmt = wb.getFileFormat();
System.out.println("Workbook's File Format AFTER save: " + fileType.format(newFmt));
System.out.println("File Format match? " + (fileFmt == newFmt? "YES!" : "NO!?"));

// Write the workbook that was saved as a byte[] - this one is fine
Path goodWb = Paths.get(fileName.replace(ext, "_aspose_good" + ext));
Files.deleteIfExists(goodWb);
Files.write(goodWb, fileBytes);
System.out.println("Saved: " + goodWb);

// Now save the *in-memory* workbook - this one is corrupt and Excel can't open it
Path badWb = Paths.get(fileName.replace(ext, "_aspose_bad" + ext));
Files.deleteIfExists(badWb);
wb.save(badWb.toString(), new OoxmlSaveOptions(wb.getFileFormat()));
System.out.println("Saved: " + badWb);

} catch (Exception ex) {
    System.out.println("Unexpected EXCEPTION: \n" + ex.getMessage());
    ex.printStackTrace();
}

After running the above code, when you try to open the file saved workbook ‘MacroDialogSheets_aspose_bad.xlsm’ within Excel, it shows the following error:

Excel cannot open the file ‘MacroDialogSheets_aspose_bad.xlsm’ because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file.

However, the workbook ‘MacroDialogSheets_aspose_good.xlsm’ opens without any issues.

Attachment: MacroDialogSheets.zip (18.0 KB)

Thanks

@oraspose,

Thanks for the sample code and template file.

After an initial test, I am able to reproduce the issue as you have mentioned. I found Workbooks with XL4 macros (XLSM) become corrupt after save using your sample code. Moreover, The Workbook.hasMacro() API returns ‘false’ for workbooks containing legacy Excel 4 macros.

I have logged a ticket with an id “CELLSJAVA-42658” for your issue. We will look into it soon.

Once we have an update on it, we will let you know here.

@oraspose

Thanks for using Aspose APIs.

This is to inform you that we have fixed your issue CELLSJAVA-42658 now. We will soon provide the fix after performing QA and including other enhancements and fixes.

@oraspose,

Please try our latest version/fix: Aspose.Cells for Java v18.6.4

Your issue should be fixed in it.

Let us know your feedback.

The issues you have found earlier (filed as CELLSJAVA-42658) have been fixed in Aspose.Cells for Java 18.7. Please also see the document for your reference: https://docs.aspose.com/display/cellsjava/Installation