XSSFWorkbook Apache POI Does not free memory

Question

XSSFWorkbook Apache POI Does not free memory

Navigation

#1 by (0 votes)

0

I read an excel *.xls of about 10 megabytes, in the next step the process increases javaw.exe in 1.5 Gb.

public void importExcelMedidas(FileBean fBean) throws IOException {
    InputStream input = new ByteArrayInputStream(fichero.getBytes());
    XSSFWorkbook workbook = new XSSFWorkbook(input); //<- Incrementa 1,5gb

    input.close();
    workbook.close();
    input = null;
    workbook=null;
    fBean=null;
    System.gc();
    System.out.println("FIN"); <- Depurando, aqui sigue con el mismo consumo.
}

Then I close the input.close(); , workbook.close() and execute System.gc(); .

But when looking at the process javaw.exe continues with the same size.

How can I free the memory?

PS: I have also closed connections and assigned to null.

java apache-poi

asked by nachfren 26.04.2018 в 17:15

source

1 answer

Problems connecting from apache (ubuntu) to sql server using PHP Question Prestashop 1.7

score 0 · Answer 1

As J. Rodríguez tells you, it's the way to do it but I want to give you an example how to use it.

There you can define that when you finish reading the last row empty the memory

The following example writes a sheet with a window of 100 rows. When the row count reaches 101, the row with rownum = 0 is emptied into the disk and removed from memory, when rownum reaches 102, the row with rownum = 1 is emptied, etc.

import junit.framework.Assert;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

public static void main(String[] args) throws Throwable {
    SXSSFWorkbook wb = new SXSSFWorkbook(100); // keep 100 rows in memory, exceeding rows will be flushed to disk
    Sheet sh = wb.createSheet();
    for(int rownum = 0; rownum < 1000; rownum++){
        Row row = sh.createRow(rownum);
        for(int cellnum = 0; cellnum < 10; cellnum++){
            Cell cell = row.createCell(cellnum);
            String address = new CellReference(cell).formatAsString();
            cell.setCellValue(address);
        }

    }

    // Rows with rownum < 900 are flushed and not accessible
    for(int rownum = 0; rownum < 900; rownum++){
      Assert.assertNull(sh.getRow(rownum));
    }

    // ther last 100 rows are still in memory
    for(int rownum = 900; rownum < 1000; rownum++){
        Assert.assertNotNull(sh.getRow(rownum));
    }

    FileOutputStream out = new FileOutputStream("/temp/sxssf.xlsx");
    wb.write(out);
    out.close();

    // dispose of temporary files backing this workbook on disk
    wb.dispose();
}

In this link that I leave you can see more examples and it is explained more poi

This is the concept of the function:

SXSSF (paquete: org.apache.poi.xssf.streaming) is a transmission extension of XSSF compatible with API that will be used when very large spreadsheets must be produced, and the dynamic storage space is limited. SXSSF achieves its low memory footprint by limiting access to rows within a sliding window, while XSSF gives access to all rows of the document. Older rows that are no longer in the window become inaccessible, since they are written to disk.