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

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 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

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