使用Apache POI读写带宏.xlsm文件失败，寻求正确解决方案

Hey there! Let's sort out your Apache POI issues step by step. First, let's break down what's going wrong and the right approach for your needs.

Why Your Current Code Isn't Working #

1. The OLE2NotOfficeXmlFileException Error #

This error is telling you that the file you're trying to open with OPCPackage (designed for OOXML files like .xlsm/.xlsx) is actually in the old OLE2 format (used by legacy .xls files). This usually happens in two cases:

• You renamed a .xls file to .xlsm (the file extension doesn't change the underlying format)
• The file is corrupted, or saved in a non-standard variant of Excel format

2. VBAMacroReader/Extractor Aren't Needed for Your Use Case #

Since you don't need to process macro code—only read/write spreadsheet data while keeping macros intact—you don't need to use VBAMacroReader or VBAMacroExtractor at all. Those classes are for extracting or inspecting macro content, not just preserving macros during regular spreadsheet operations.

The Correct Approach: Use WorkbookFactory #

The simplest and most reliable way to handle both .xls and .xlsm files (while preserving macros) is to use WorkbookFactory. It automatically detects the file format and handles opening/saving correctly without needing to manually choose between XSSF and HSSF.

Working Example Code #

Here's how to read, modify, and save your .xlsm file while keeping macros intact:

import org.apache.poi.ss.usermodel.*;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;

public class XlsmWithMacrosHandler {
    public static void main(String[] args) {
        String inputFilePath = "MyMacroExcel.xlsm";
        String outputFilePath = "ModifiedMacroExcel.xlsm";

        try (FileInputStream fis = new FileInputStream(inputFilePath);
             // Pass 'true' to enable macro preservation
             Workbook workbook = WorkbookFactory.create(fis, true);
             FileOutputStream fos = new FileOutputStream(outputFilePath)) {

            // Example: Read and modify a cell
            Sheet sheet = workbook.getSheetAt(0);
            Row row = sheet.getRow(0);
            if (row == null) row = sheet.createRow(0);
            Cell cell = row.getCell(0);
            if (cell == null) cell = row.createCell(0);
            cell.setCellValue("Updated value (macros preserved!)");

            // Save the workbook - macros will remain intact
            workbook.write(fos);
            System.out.println("File saved successfully with macros preserved!");

        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

Key Notes: #

• The WorkbookFactory.create(fis, true) call uses true as the second parameter to enable macro preservation. This ensures any existing macros in the file are retained when you save changes.
• This code works seamlessly for both .xls (HSSF) and .xlsm/.xlsx (XSSF) files—no need to manually handle OPCPackage or specific workbook classes directly.

Verify Your File's Actual Format #

If you still run into the OLE2 error with this code, double-check your file:

• Open it in Excel and re-save it as a .xlsm (File > Save As > Excel Macro-Enabled Workbook (*.xlsm)). This ensures it's properly saved in the OOXML format.
• If you can't save it as .xlsm, it might be an older .xls file—don't worry, the same code above will automatically handle it using HSSF.

Why Your Original XSSF Code Failed #

When you used OPCPackage.open(new File("Test.xlsm")), you're explicitly telling POI to treat the file as an OOXML zip package. If the file is actually an OLE2 .xls file, this will throw the error you saw. WorkbookFactory avoids this by auto-detecting the format under the hood.

内容的提问来源于stack exchange，提问作者Mr Ajay