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.