POI OutOfMemory Exception with xlsx (XSSF)

Question

We am trying to use POI 3.8 for an excel component in our application which has to deal with creation of large excel files. I was happy to use SXSSF streaming approach which was fast and very less memory foot print. However, I am not able to do Data Validation and have to use XSSF.

In XSSF, when I try to open the xlsx file (~5 MB), the memory shoots up and mostly results in OutOfMemory. My questions are,

1. Is it possible to do DataValidation (like select from drop down list) using SXSSF. This would be a blessing for me.

2. Is there a way to use XSSF for datavalidation but with less memory foot print.

3. Is there an alternate java solution for xlsx data validation which is fast and memory efficient.

Thank you in advance..

Answer 1

Reason of OutOfMemory, as seen from debugger, POI 3.10.1:

XSSFWorkbook has ArrayList<XSSFSheet>; 
XSSFSheet has TreeMap<Integer,XSSFRow>;
XSSFRow has field _row._textsource._srcAfter found as char[32768]

counting: 32768 * (number of rows) = complete waste of memory.... More than 1 gb in my case.

Answer 2

It works pretty well with ss.usermodel (http://poi.apache.org/spreadsheet/quick-guide.html#Validation)

Just instead of using XSSF workbook like its in the manual:

Workbook workbook = new XSSFWorkbook();    
Sheet sheet = workbook.createSheet("Data Validation");  
     DataValidationHelper dvHelper = sheet.getDataValidationHelper();

put there your SXSSF workbook so the DataValidationHelper will be pulled from SXSSF sheet

    Workbook workbook = new SXSSFWorkbook();    
    Sheet sheet = workbook.createSheet("Data Validation");  
         DataValidationHelper dvHelper = sheet.getDataValidationHelper();  
//stuff with validation

Answer 3

I too faced the same issue of OOM while parsing xlsx file...after two days of struggle, I finally found out the below code that was really perfect;

This code is based on sjxlsx. It reads the xlsx and stores in a HSSF sheet.

            // read the xlsx file
       SimpleXLSXWorkbook = new SimpleXLSXWorkbook(new File("C:/test.xlsx"));

        HSSFWorkbook hsfWorkbook = new HSSFWorkbook();

        org.apache.poi.ss.usermodel.Sheet hsfSheet = hsfWorkbook.createSheet();

        Sheet sheetToRead = workbook.getSheet(0, false);

        SheetRowReader reader = sheetToRead.newReader();
        Cell[] row;
        int rowPos = 0;
        while ((row = reader.readRow()) != null) {
            org.apache.poi.ss.usermodel.Row hfsRow = hsfSheet.createRow(rowPos);
            int cellPos = 0;
            for (Cell cell : row) {
                if(cell != null){
                    org.apache.poi.ss.usermodel.Cell hfsCell = hfsRow.createCell(cellPos);
                    hfsCell.setCellType(org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING);
                    hfsCell.setCellValue(cell.getValue());
                }
                cellPos++;
            }
            rowPos++;
        }
        return hsfSheet;