java.lang.outofmemory exception while reading excel file (xlsx) using POI

Question

I am developing a web application which reads data from excel file (xlsx). I am using POI for reading excel sheet. The problem is when I try to read excel file, the server throws the following error:

The excel file I am trying to read has size of almost 80 MB. Any solution to this problem?

Actually user is uploading file and application after saving file to disk try to read file. The code snippet I am using for testing is:

 File savedFile = new File(file_path);

FileInputStream fis = null;
            try {

                fis = new FileInputStream(savedFile);
                XSSFWorkbook xWorkbook = new XSSFWorkbook(fis);
                XSSFSheet xSheet = xWorkbook.getSheetAt(5);

                Iterator rows = xSheet.rowIterator();
                while (rows.hasNext()) {
                    XSSFRow row = (XSSFRow) rows.next();
                    Iterator cells = row.cellIterator();

                    List data = new ArrayList();
                    while (cells.hasNext()) {
                        XSSFCell cell = (XSSFCell) cells.next();
                        System.out.println(cell.getStringCellValue());
                        data.add(cell);
                    }

                }
            } catch (IOException e) {
                e.printStackTrace();
            } 

Answer 1

One thing that'll make a small difference is when opening the file to start with. If you have a file, then pass that in! Using an InputStream requires buffering of everything into memory, which eats up space. Since you don't need to do that buffering, don't!

If you're running with the latest nightly builds of POI, then it's very easy. Your code becomes:

File file = new File(file_path);
OPCPackage opcPackage = OPCPackage.open(file);
XSSFWorkbook workbook = new XSSFWorkbook(opcPackage);

Otherwise, it's very similar:

File file = new File(file_path);
OPCPackage opcPackage = OPCPackage.open(file.getAbsolutePath());
XSSFWorkbook workbook = new XSSFWorkbook(opcPackage);

That'll free you a bit of memory, which might be enough. If it isn't, and if you can't increase your Java heap space enough to cope, then you'll have to stop using the XSSF UserModel.

In addition to the current, friendly UserModel that you've been using, POI also supports a lower level way to process files. This lower level way is harder to use, as you don't have the various helpers around that require the whole file in memory. However, it's much much more memory efficient, as you process the file in a streaming way. To get started, see the XSSF and SAX (Event API) How-To section on the POI website. Try that out, and also have a look at the various examples.