Problem reading file . xlsx too big in Java

Question

I am working in a web application using Java, where I have a method that should read a file .xlsx, using the apache-poi:

public static void xlsx(String arquivo) throws IOException{

     try {
        FileInputStream file = new FileInputStream(new File(arquivo));
        XSSFWorkbook workbook = new XSSFWorkbook(file);
        XSSFSheet sheet = workbook.getSheetAt(0);
        Iterator<Row> rowIterator = sheet.iterator();
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            Iterator<Cell> cellIterator = row.cellIterator();
            while (cellIterator.hasNext()) {
                Cell celula = cellIterator.next();
                /*aqui faço a leitura de cada
                celula, fazendo o tratamento adequado 
                a cada campo.
                */
            }
        }
        file.close();

    } catch (IOException e) {
        e.printStackTrace();
        throw new IOException("Erro ao processar arquivo.",e.getCause());
    }
}

The method works correctly, however as this method will probably process files with thousands of rows of records, for example, about 25 to 300 thousand lines. While processing a very large file I take the following Exception:

(http-localhost-127.0.0.1-8080-4) Servlet.service() for servlet RestServlet threw exception: org.jboss.resteasy.spi.UnhandledException: java.lang.OutOfMemoryError: Java heap space

I wanted to know how I can avoid this type of error. If you have how to read and process the file. xlsx of 1000 in 1000 lines, or some other solution.

Answer 1

1 Read the full file:

Increase the heap size of the JVM:

java Xmx80m seuprograma //80 megabytes (só para mostrar a sintaxe)
java -Xmx6g seuprograma // 6 gigabytes

Note that JVM is limited to approximately 2GB on computers with 32bit architecture

2 Wide input and partial reading

To handle a wide and short-term input the best option I know is the apache SXSSF.

SXSSFWorkbook wb = new SXSSFWorkbook(-1); // cancela o "flushing" automatico e leitura do arquivo completo
// Depois faça o controle manual:
if(rownum % NOR == 0) {
((SXSSFSheet)sh).flushRows(NOR); // mantém NOR linhas e "descarta" o restante
//...

For more details I suggest you see and understand the example on link posted above.