Apache POI: SXSSFWorkbook.dispose() does not exist

Question

I'm using apache's POI API to write XLSX files. Since I need to write big files, I'm using the Streaming API (SXSSF). To do this, I'm following this guide. Note that by the end of the example there's a call to

wb.dispose

This wb instance refers to a SXSSFWorkbook instance. I'm using the same in my code but it complains about the dispose method not existing. I downloaded the source code and the method isn't there. However, going to their SVN and checking that class' code we can see the method there:

https://svn.apache.org/repos/asf/poi/trunk/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFWorkbook.java

I already tried to recompile their code but I get a lot of errors...

Answer 1

The Apache POI 3.8 (latest stable at the time) creates a temporary XML file for each sheet (when using SXSSF) but does not gives the option to delete these files. This fact makes this API not good to use because if I'm exporting 600MB of data then I'll have 2 files with 600MB and one of them will be in the temporary folder until it's deleted.

Digging into the code, we see that the class SXSSFSheet has an instance of SheetDataWriter. This last class is responsible to write and maintain the temporary file that is represented by the File instance. Accessing this object would allow to delete the file. All these instances are private so, theoretically, you cannot access them. However, through reflection, we can access the File instance to delete this useful but annoying files!

The following to methods allow to do this. By calling the deleteSXSSFTempFiles, all temporary files of that workbook are deleted.

/**
 * Returns a private attribute of a class
 * @param containingClass The class that contains the private attribute to retrieve
 * @param fieldToGet The name of the attribute to get
 * @return The private attribute
 * @throws NoSuchFieldException
 * @throws IllegalAccessException 
 */
public static Object getPrivateAttribute(Object containingClass, String fieldToGet) throws NoSuchFieldException, IllegalAccessException {
    //get the field of the containingClass instance
    Field declaredField = containingClass.getClass().getDeclaredField(fieldToGet);
    //set it as accessible
    declaredField.setAccessible(true);
    //access it
    Object get = declaredField.get(containingClass);
    //return it!
    return get;
}

/**
 * Deletes all temporary files of the SXSSFWorkbook instance
 * @param workbook
 * @throws NoSuchFieldException
 * @throws IllegalAccessException 
 */
public static void deleteSXSSFTempFiles(SXSSFWorkbook workbook) throws NoSuchFieldException, IllegalAccessException {

    int numberOfSheets = workbook.getNumberOfSheets();

    //iterate through all sheets (each sheet as a temp file)
    for (int i = 0; i < numberOfSheets; i++) {
        Sheet sheetAt = workbook.getSheetAt(i);

        //delete only if the sheet is written by stream
        if (sheetAt instanceof SXSSFSheet) {
            SheetDataWriter sdw = (SheetDataWriter) getPrivateAttribute(sheetAt, "_writer");
            File f = (File) getPrivateAttribute(sdw, "_fd");

            try {
                f.delete();
            } catch (Exception ex) {
                //could not delete the file
            }
        }
    }
}