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

Hello Jorge,

I think POI 3.9 should work as I believe the streaming was added to 3.8
beta 3.

However if it is failing I would suggest installing the 3.14 version and
adjusting your classpath to test with the later version.

We have successfully created spreadsheets with close to a million records
maybe even over the million, the spreadhseet was fine.

But we did then have a problem emailing the spreadsheet as it was too big.

Hope this helps.

Thanks

Don







From: "Jorge Ubeda" <jubeda@xxxxxxxxx>
To: rpg400-l@xxxxxxxxxxxxxxxxxx
Date: 01/08/2021 06:11 PM
Subject: Re: Worksheet failing when reaching a large number of
rows-Using SXSSFWorkbook
Sent by: "RPG400-L" <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx>



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