EXcel Sheet POI Validation : Out Of Memory Error

Use Event API (HSSF Only).

The event API is newer than the User API. It is intended for intermediate developers who are willing to learn a little bit of the low level API structures. Its relatively simple to use, but requires a basic understanding of the parts of an Excel file (or willingness to learn). The advantage provided is that you can read an XLS with a relatively small memory footprint.

There are two options available to you. Option #1 - increase the size of your JVM Heap, so that Java has more memory available to it. Processing Excel files in POI using the UserModel code is DOM based, so the whole file (including parsed form) needs to be buffered into memory. Try a question like this one for advice on how to increase the help.

Option #2, which is more work - switch to event based (SAX) processing. This only processes part of the file at a time, so needs much much less memory. However, it requires more work from you, which is why you might be better throwing a few more GB of memory at the problem - memory is cheap while programmers aren't! The SpreadSheet howto page has instructions on how to do SAX parsing of .xlsx files, and there are various example files provided by POI you can look at for advice.

.

Also, another thing - you seem to be loading a File via a stream, which is bad as it means even more stuff needs buffering into memory. See the POI Documentation for more on this, including instructions on how to work with the File directly.

You can use SXSSF workbook from POI for memory related issues. Refer here

I faced the similar issue while reading and merging multiple CSVs into a single XLSX file. I had a total of 3 csv sheets each with 30k rows totalling to 90k.

It got resolved by using SXSFF as below,

    public static void mergeCSVsToXLSX(Long jobExecutionId, Map<String, String> csvSheetNameAndFile, String xlsxFile) {
    try (SXSSFWorkbook wb = new SXSSFWorkbook(100);) { // keep 100 rows in memory, exceeding rows will be flushed to
                                                       // disk
      csvSheetNameAndFile.forEach((sheetName, csv) -> {
        try (CSVReader reader = new CSVReader(new FileReader(csv))) {
          wb.setCompressTempFiles(true);
          SXSSFSheet sheet = wb.createSheet(sheetName);
          sheet.setRandomAccessWindowSize(100);

          String[] nextLine;
          int r = 0;
          while ((nextLine = reader.readNext()) != null) {
            Row row = sheet.createRow((short) r++);
            for (int i = 0; i < nextLine.length; i++) {
              Cell cell = row.createCell(i);
              cell.setCellValue(nextLine[i]);
            }
          }
        } catch (IOException ioException) {
          logger.error("Error in reading CSV file {} for jobId {} with exception {}", csv, jobExecutionId,
              ioException.getMessage());
        }
      });

      FileOutputStream out = new FileOutputStream(xlsxFile);
      wb.write(out);
      wb.dispose();
    } catch (IOException ioException) {
      logger.error("Error in creating workbook for jobId {} with exception {}", jobExecutionId,
          ioException.getMessage());
    }
  }