Apache-POI: Cannot write to existing workbook

Question

I am working on a project that needs to read an Excel workbook, calls the necessary web services, and then takes a response from the web services and enters this information into the same Excel workbook that was read.

Here is the error that I see when I try to write to an 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 opening a file / reading:

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

After that, I read each row and retrieve each cell value.

Once this is done, I will create cells under the headings for the message “Success and Result,” 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 encountered a similar problem?

Answer 1

The current problem is related to an error that has existed since 2010 and can be found @ https://issues.apache.org/bugzilla/show_bug.cgi?id=49940

In the stackoverflow list below, a workaround was found that if you close and open the book again before making another entry to the file, it will work without problems. This is inefficient, but fixes the problem until the Apache-POI Dev Team resolves the problem.

stack overflow

Answer 2

I got the same error message but used different classes. The current version of poi that I'm using is poi-ooxml 3.9, but it still has a problem. Now I have fixed my problem, and I think this problem occurs when you first get an instance of a workbook.

When I write data to a file, I like it (with rules of practice for exceptions and closures):

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

I got the message "Failed to get input from /docProps/app.xml" when I received a workbook instance as follows:

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

When I fixed the problem, the modified code was

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

In my case, it doesn’t matter if you open, read and write to one file or read from one file, and then write to another. If you read the poi source code, you can see that the factory methods that I used can call the open () methods in the OPCPackage class. Try using the method by getting an InputStream as an argument.

Answer 3

I think the problem here is that you are using the same Path xslFile to open and save the file.

Open file,

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

Save file

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

You need an InputStream to read and work with your file, but this stream becomes inaccessible when you create an OutputStream in the same path and file name.

Answer 4

The solution I found for this, and I was looking for a while, is to make sure that you do not open your Workbook with File , which you use to open FileOutputStream to save the Workbook . Instead, use 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); 

Remember to close every open thread and OPCPackage .

Answer 5

Here's how to do it when using OPCPackage for reading (try / catch / finally ommitted for readibility):

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

make changes ... XSSFSheet = 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 a different file path for input and output). Thanks!