Re: Worksheet failing when reaching a large number of rows-Using SXSSFWorkbook

This is a continuation of our request for information past 2/4 July. As
recommended by Don Brown (date: Sun, 4 Jul 2021 09:29:17 +1000
from: Don Brown via RPG400-L <rpg400-l@xxxxxxxxxxxxxxxxxx>
subject: Re: Worksheet failing when reaching a large number of rows) we
added SXSSFWorksheet to our definitions (including required methods). At
first it worked, but as long we require a more large worksheet
(approximately 200.000 rows, a year of data) it fails again with same
error, at same point (saving workbook, using SS_save, at same sentence).
We have a difference from Don use: We need to start from an existing XSSF
worksheet (a template with styles and macros). Apart from that, we follow
Don guidelines. It worked well, growing better than our previous schema
using XSSF model. However, as said, when we test with around of
150000/200000 rows, it fails with an Out of Memory JVM error (like our
previous error when working with XSSF).
We added to our process a call to compress temporary files
(setCompressTempFiles). Again, it worked well until the number of rows grew
to around of 200000.
Our sequence at end now is SS_Save => setCompressTempFiles(true) =>
Dispose()...I have seen some StackOverflow examples where original XSSF
workbook is closed at end. I do not close it.
All our operations on creating rows are based on same methods and objects
created previously for XSSF objects.
Same results using OS R7V3 (java7) or R7V4 (java8).
We are using POI 3.9. I can see that Don uses POI 3.14.
Important: we are inheriting PASE environment variables from java starting
system definitions. I´m investigating yet if it implies impact on our java
starting definitions. It is a recent change, without issues (until this,
eventually)
If anyone can say something on this, it will be welcome.
If anyone recommends changes in our process/definitons, it will be welcome.
If anyone considers our version of POI is inadequate, we will listen.
thanks in advance
Jorge Ubeda