Close Filehandle for Workbook (apache poi)

Question

I constructed a new Workbook using WorkbookFactory.create(new File("path/to/xlsx")). However, when I try to edit the File in Excel after starting the application, I get an error message that the file is in use. Do I have to free the file up, and if so, how? (I could not find anything like Workbook.close() in the api docs) Or do I have to look in other places?

I have no clue where else to look; the application does not cause these issues with csv and for excel files I simply call the converter (xls => csv) which is the only difference.

(I am using POI 3.8)

Answer 1

It seems to work just fine to maintain a handle on the InputStream passed to WorkbookFactory.create(), and to simply close the InputStream when you're done with the Workbook. For example:

    InputStream is = // initialize
    try {
        Workbook wb = WorkbookFactory.create(is);
        // use the workbook
    } finally {
        if (is != null) is.close()
    }

Answer 2

If you need full control of when the resources get closed, you should create the OPCPackage yourself up front, and pass that into WorkbookFactory. OPCPackage provides the close method you're after. A Workbook will remain open until garbage collection

Your code would look something like:

     File f = new File("/path/to/excel/file");
     Workbook wb = null;

     NPOIFSFileSystem npoifs = null;
     OPCPackage pkg = null;
     try {
       npoifs = new NPOIFSFileSystem(f);
       wb = WorkbookFactory.create(npoifs);
     } catch(OfficeXmlFileException ofe) {
       pkg = OPCPackage.open(f);
       wb = WorkbookFactory.create(pkg);
     }

     // Use it

     if (npoifs != null) { npoifs.close(); }
     if (pkg != null) { pkg.close(); }

Answer 3

How to close an instance of a POI workbook in Java:

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


try{
    File workbookFile = new File("C:\\repo\\yourfile.xslx");
    FileInputStream file = new FileInputStream(workbookFile);
    Workbook wb = WorkbookFactory.create(file);
    Sheet sheet = wb.getSheetAt(0);

    //use the instance of wb.

    file.close();

}
catch(Exception e){
    System.out.println("Fail");
}

Answer 4

First you need to close the stream which is written to the workBook:

workBook.write(outputStream);

outputStream.close();

After that u should close the workBook (for no further operations) and dispose the temporary files:

//Close the workBook
workBook.close();

//deleting the temporary files
workBook.dispose();

Answer 5

I am using poi api version 5.0.0. The Workbook in this version has a close() method which as per the API document says:

"Close the underlying input resource (File or Stream),from which the Workbook was read."

//Using try-catch-finally:

Workbook workbook = null;
try {
    workbook = WorkbookFactory.create(inputStream);
    //....
} catch (IOException ex) {
    //...
}finally {
    workbook.close();
}

//Using try-with-resources:

try(Workbook workbook = WorkbookFactory.create(fileStream)) {
    //....
} catch (IOException ex) {
    //...
}

Answer 6

Just suffer from the same problem, as of 2021. :(, when using poi version 3.9.

I just find that if we upgrade the poi version to 3.17 (in case you are still using Java 7, which poi version 4 or above only Support Java 8 or above),

Then the Workbook will implement AutoCloseable.

Therefore one can use Try-With-Resources block to auto close the File.

Here is the demo code.

try (Workbook workbook = WorkbookFactory.create(new File(fileLocation))) {
   // operate on the workbook here...
}

Which I think the above code is better than @Gagravarr solution, as it is copying what the create method do outside, which developer not familiar with poi library does not know what is it.

---

P.S. Indeed this question is placed in bugzilla as a bug, see https://bz.apache.org/bugzilla/show_bug.cgi?id=56537, and is resolved and the code is merged in poi 3.11 or later. Therefore anyone having the same issue as we do, can have a look at it, and close the resources when finish using it.