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: Unexpected end of ZLIB input stream

问题与GZIPinStream和GZIPOutputStream无关。

更新2019年6月4日。

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

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>

Answer 1

几乎每次您在打开通过代码创建的工作簿时偶然发现错误，原因都是某些未正确关闭的资源。不幸的是，我经历过很多次;-)

就我而言，我可以通过以下操作顺序解决问题(顺序问题):

1. 编写工作簿:workbook.write(out);
2. 强制FileOutputStream清空所有使用的缓冲区:out.flush();
3. 关闭FileOutputStream:out.close();
4. 关闭工作簿:workbook.close();

这就是我将缺少的操作添加到您的代码中的方法:

    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.

这对我有用，我希望它也对你有用。

编辑(OP在下面的评论中询问我的示例代码)
这是读取和扩展 XSSFWorkbook 的独立示例。这一切都在 main 方法中，但至少有注释;-)
您必须更改工作簿的路径，在本例中它基本上是一个 String，然后变成 java.nio.Path。

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();
        }
}