java - 无法使用 Apache POI 写入和保存 XLSM 文件

Question

我有一个现有XLSM文件，我尝试在其中使用Apache POI.

String File = GlobalVariables.XLSM;

try {
    Workbook workbook;
    workbook = new XSSFWorkbook(OPCPackage.open(GlobalVariables.XLSM));
    Sheet sheet = workbook.getSheetAt(0);
    Row row = sheet.createRow(recordcount+5);
    Cell cell;

    cell = row.createCell(GlobalVariables.testID);
    cell.setCellValue(recordcount);

    FileOutputStream out = new FileOutputStream(new File(File));
    workbook.write(out);
    out.close();
    System.out.println("Data was written in XLSM");

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

我收到错误消息：

org.apache.poi.ooxml.POIXMLException: java.io.EOFException: ZLIB 输入流的意外结束

GZIPinStream与和无关的问题GZIPOutputStream。

更新 2019.06.04。

我修改了代码，但仍然出现错误：

try {
    FileInputStream file = new FileInputStream(GlobalVariables.XLSM);
    XSSFWorkbook workbook = new XSSFWorkbook(file);
    XSSFSheet sheet = workbook.getSheetAt(0);
    XSSFRow row = sheet.createRow(recordcount+4);
    Cell cell;

    cell = row.createCell(GlobalVariables.testID);
    cell.setCellValue(recordcount+1);

    file.close();

    FileOutputStream out = new FileOutputStream(new File(GlobalVariables.XLSM));
    workbook.write(out);
    out.flush();
    out.close();
    workbook.close();
    System.out.println("Data was written");

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

错误信息：

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
    <logFileName>error066080_01.xml</logFileName>
    <summary>Error in file (C:\_privat\completeReport.xlsm)</summary>
    <removedRecords>
        <removedRecord>/xl/worksheets/sheet1.xml</removedRecord>
        <removedRecord>/xl/calcChain.xml</removedRecord>
    </removedRecords>
</recoveryLog>

标签： javaapache-poixssfeofexceptionxlsm

Answer 1

Nearly every time you stumble upon an error opening a workbook that you created by code, the reason is some not properly closed resource. Unfortunately, I experienced that a lot of times ;-)

In my cases, I could resolve the issue by the following sequence of actions (order matters):

1. write the workbook: workbook.write(out);
2. force the FileOutputStream to empty all used buffers: out.flush();
3. close the FileOutputStream: out.close();
4. close the workbook: workbook.close();

This is how I would add the missing actions to your code:

    try {
        Workbook workbook;
        workbook = new XSSFWorkbook(OPCPackage.open(GlobalVariables.XLSM));
        Sheet sheet = workbook.getSheetAt(0);
        Row row = sheet.createRow(recordcount + 5);
        Cell cell;

        cell = row.createCell(GlobalVariables.testID);
        cell.setCellValue(recordcount);

        FileOutputStream out = new FileOutputStream(new File(File));
        // 1. write the workbook
        workbook.write(out);
        // 2. force the FileOutputStream to write everything out before closing it
        out.flush();
        // 3. then close the FileOutputStream
        out.close();
        // 4. finally close the workbook
        workbook.close();
        System.out.println("Data was written in XLSM");
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (InvalidFormatException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

Please note that flushing the FileOutputStream might not always be necessary, but it was mentioned in several best-practice tutorials concerning apache-poi, so I decided to use it my code.

This works for me, I hope it will do so for you as well.

EDIT (OP asked for my example code in a comment below)
This is an independent example for reading and extending an XSSFWorkbook. It is all just in the main method, but at least commented ;-)
You have to change the path to the workbook, which is basically a String in this example and becomes a java.nio.Path afterwards.

public class PoiMain {

    public static void main(String[] args) {
        /*
         * Setup:
         * An existing xlsx file with a first sheet containing 6 columns and 1 row.
         * The row has 6 filled cells with the values
         * cell 1 (index 0): There
         * cell 2 (index 1): is
         * cell 3 (index 2): a
         * cell 4 (index 3): house
         * cell 5 (index 4): in
         * cell 6 (index 5): New Orleans
         * 
         * Task:
         * Write the words "they", "call", "it", "the", "rising", "sun"
         * in the cells below.
         */

        // define the (correct) path to the workbook
        String pathToFile = "Y:\\our\\path\\to\\the\\Test-Workbook.xlsx"; // you can use an xlsm here, too
        // create a Path object
        Path filePath = Paths.get(pathToFile);
        // declare a workbook
        XSSFWorkbook workbook;

        try {
            /*
             * READING from the .xlsx file:
             */

            FileInputStream in = new FileInputStream(filePath.toFile());
            workbook = XSSFWorkbookFactory.createWorkbook(in);
            XSSFSheet sheet = workbook.getSheetAt(0);

            // read all the cells of the first row and print their content
            for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) {
                XSSFRow row = sheet.getRow(i);
                for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
                    XSSFCell cell = row.getCell(j);
                    System.out.println(cell.getStringCellValue());
                }
            }

            /*
             * WRITING to the .xlsx file already read
             */

            // create some meaningful words to be added to some cells in the workbook
            List<String> wordsToBeWritten = Arrays.asList("they", "call", "it", "the", "rising", "sun");

            FileOutputStream out = new FileOutputStream(filePath.toAbsolutePath().toString());
            sheet = workbook.getSheetAt(0);
            XSSFRow row = sheet.createRow(sheet.getPhysicalNumberOfRows());
            // create new cells and write the words into them
            for (int i = 0; i < wordsToBeWritten.size(); i++) {
                XSSFCell cell = row.createCell(i);
                cell.setCellValue(wordsToBeWritten.get(i));
            }
            // close the FileInputStream
            in.close();
            // write the workbook using the FileOutputStream
            workbook.write(out);
            // force the FileOutputStream to write everything until it is empty
            out.flush();
            // close the FileOutputStream
            out.close();
            // close the workbook.
            workbook.close();
        } catch (FileNotFoundException e) {
            System.err.println(
                    "The file \"" + filePath.toAbsolutePath().toString() + "\" could not be found.");
            e.printStackTrace();
        } catch (IOException e) {
            System.err.println("Error while reading the file \"" + filePath.toAbsolutePath().toString() + "\"");
            e.printStackTrace();
        } catch (InvalidFormatException e) {
            System.out.println(
                    "The file \"" + filePath.toAbsolutePath().toString() + "\" has an invalid format(ting)");
            e.printStackTrace();
        } catch (EmptyFileException e) {
            System.err.println("The supplied file \"" + filePath.toAbsolutePath().toString() + "\" is empty.");
            e.printStackTrace();
        }
}