Apache-POI: Unable to write to an existing workboo

问题:

I'm working on a project which needs to read an Excel Workbook, calls the necessary Web Services, and then takes the response from the webservices and enters that information in to the same Excel Workbook that was read.

Here is the error I'm seeing when trying to write to the Excel Workbook:

Exception in thread "main" org.apache.poi.POIXMLException: java.io.IOException: Can't obtain the input stream from /docProps/app.xml
at org.apache.poi.POIXMLDocument.getProperties(POIXMLDocument.java:141)
at org.apache.poi.POIXMLDocument.write(POIXMLDocument.java:177)
at ext.ExcelProcessor.main(ExcelProcessor.java:197)
Caused by: java.io.IOException: Can't obtain the input stream from /docProps/app.xml
    at org.apache.poi.openxml4j.opc.PackagePart.getInputStream(PackagePart.java:500)
    at org.apache.poi.POIXMLProperties.<init>(POIXMLProperties.java:75)
    at org.apache.poi.POIXMLDocument.getProperties(POIXMLDocument.java:139)
    ... 2 more

Here is my code for the opening of the file/reading:

pkg = OPCPackage.open(xslFile);
    theWorkbook = new XSSFWorkbook(pkg);

After this I read each row and extract each cell value.

Once this is done I'll create the cells under the headers for Success and Result Message and then do the following:

String sessionData = sessionKey[1];
                String[] cellValCurrRow = rowCellVals.get(r-1);
                String attachmentData[] = WQSServices.uploadAttachment(sessionData, cellValCurrRow);

                XSSFCell cell = xslRows[r].getCell(7);

                if(cell == null)
                {
                    cell = xslRows[r].createCell(7);
                }

                System.out.println("The Cell: "+cell.getStringCellValue());

                XSSFCell cell2 = xslRows[r].getCell(8);

                if(cell2 == null)
                {
                    cell2 = xslRows[r].createCell(8);
                }

                System.out.println("The Cell: "+cell2.getStringCellValue());

                cell.setCellType(Cell.CELL_TYPE_STRING);
                cell2.setCellType(Cell.CELL_TYPE_STRING);
                cell.setCellValue(attachmentData[0]);
                cell2.setCellValue(attachmentData[1]);

                System.out.println("New Cell Data: 1-"+cell.getStringCellValue()+" 2-"+cell2.getStringCellValue());

                FileOutputStream fos = new FileOutputStream(xslFile);
                theWorkbook.write(fos);
                fos.close();

Has anyone ran in to similar issue?

回答1:

I got the same error message but had used different classes. Current poi version I'm using is poi-ooxml 3.9 but it still has the issue. Now I fixed my problem and I think this problem arises when you obtain Workbook instance at first.

When I write data to the file, I do like this (with practice rules for exceptions and close):

    FileOutputStream fos = new FileOutputStream(filePath);
    wb.write(fos);
    fos.close();

I got "Can't obtain the input stream from /docProps/app.xml" error message, when I obtained Workbook instance like this:

    Workbook wb = WorkbookFactory.create(new File(filePath));

When I fixed the problem, modified code was

    Workbook wb = WorkbookFactory.create(new FileInputStream(filePath));

In my case it doesn't matter whether you open and read from and write to the same file, or read from one file then write to another. If you read the poi source codes, you could see the factory methods I used might call open() methods in OPCPackage class. Try using the method getting InputStream as its argument.

回答2:

I think the problem here is that you are using the same filePath xslFile for opening and saving the file.

Opening the file,

pkg = OPCPackage.open(xslFile);
    theWorkbook = new XSSFWorkbook(pkg);

Saving the file,

FileOutputStream fos = new FileOutputStream(xslFile);
theWorkbook.write(fos);
fos.close();

You need an InputStream for read and work with your file, but this stream becames unaccessible when you create an OutputStream under the same path and file name.

回答3:

The current issue listed is a bug that has been around since 2010 and can be found @ https://issues.apache.org/bugzilla/show_bug.cgi?id=49940

In the stackoverflow listing below a workaround was found that if you close and re-open the book again before doing another write out to the file it will work without issue. This isn't efficient by any means but it does fix the issue until the Apache-POI Dev Team figures out the issue.

https://stackoverflow.com/a/9792406/1231715

回答4:

The solution I've found for this, and I've been looking for a while, is to make sure you don't open your Workbook with the File which you use to open the FileOutputStream to save the Workbook. Instead, use a FileInputStream to open the Workbook.

Something like this will work flawlessly

        File inputFile = new File("Your-Path");
        this.inputStream = new FileInputStream(inputFile);
        this.opc = OPCPackage.open(this.inputStream);
        this.workbook = WorkbookFactory.create(opc);

...

        this.outputStream = new FileOutputStream(inputFile);
        this.workbook.write(this,outputStream);

Don't forget to close every opened stream and the OPCPackage.

回答5:

Here is how this can be done when using OPCPackage to read (try/catch/finally ommitted for readibility):

OPCPackage pkg = OPCPackage.open("existingFile.xlsx");
XSSFWorkbook wb = (XSSFWorkbook) WorkbookFactory.create(pkg);

make your modifications... XSSFSheet sheet = wb.getSheetAt(0); ...

fos = new FileOutputStream("outputFileName.xlsx");
wb.write(fos);
pkg.close();
fos.close();
Faces.sendFile(new File(outputFileName)

The comment above from Jayamohan helped me solve this problem today (using different file path for Input and for Output). Thank you!