Hello.
I have in our notes information received on this list at the time we had a
similar problem ...
Recently had a problem where we needed close to a million records in a
spreadsheet.
This resulted in an update to POI3.14 and use of the SXSSF option for
streaming the file rather then holding it all in memory.
Also we were advised this option is faster but yet to do any tests.
This is the code we added/changed after the update of the class files.
This did introduce a new problem - the file was too big to be emailed.
Below are the pieces of code that was added to the HSSF_H sourece member.
******************************************************************
* SXSSF (Streaming Usermodel API) Data Types
******************************************************************
dcl-c SXSSF_Workbook_Class 'org.apache.poi.xssf.streaming.SXSSFWorkbook';
dcl-s SXSSFWorkbook object(*JAVA: 'SXSSF_WORKBOOK_CLASS');
*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
* SXSSF_dispose
*
* Dispose of temporary files backing this workbook on disk.
* Calling this method will render the workbook unusable.
*
* Returns TRUE is all temporary files were delete successfully
*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SXSSF_dispose PR N extproc(*java
:
SXSSF_Workbook_Class
:'dispose')
Added the following prototype after the new_XSSFWorkbook prototype
D new_SXSSFWorkbook...
D PR O class(*java
D : SXSSF_Workbook_Class )
D extproc(*java
D : SXSSF_Workbook_Class
D : *constructor)
Then in the program the prototype for new_XSSFWorkbook was changed to
new_SXSSFWorkbook as shown below.
// Create a new workbook.
wkBook = new_SXSSFWorkbook();
Then after the SS_save I added the call to SXSSF_dispose, as shown below.
// Save the workbook to the IFS.
SS_save( wkBook: wkFilename );
SXSSF_dispose( wkBook );
That was all that was needed in the program to allow the streaming save
of the excel file.
Hope this helps
Don
From: "Jorge Ubeda" <jubeda@xxxxxxxxx>
To: RPG400-L@xxxxxxxxxxxxxxxxxx
Date: 02/07/2021 07:21 PM
Subject: Worksheet failing when reaching a large number of rows
Sent by: "RPG400-L" <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx>
Hello all
It is a question related to use of POI APi from Scott Klement
We have an error with a large worksheet. Process works, but is
cumulative,
and fails to span several months. Not for a few months.
We are yet checking the real origin of that incident:
--- We are opening a xslx workbook: We open an existing xlsx template and
write rows on it. At end, we save it with same name. No xsl stuff.
--- We add an existing style to cells when writing.
--- We declared a very small group of objects (100) when initializing, and
destroy it at end of work, after saving. Tomorrow I will test creating a
small group at initializing., and another group starting when a row is
processed, and ending before next row comes.
-Finally, we ask you if exists someone using SXSSF (
http://poi.apache.org/components/spreadsheet/how-to.html#sxssf) or have
tested it, or valued it, in the case our issue be a memory management
error.
We are using default heap from JVM (2GB). I would say it would suffice.
Not thinking yet in changing this value.
Error received:
Message . . . . : Application error. RNX0301 not supervised by HSSFR4
on statement
0000015900, statement X'0000 '. Cause . . . . . : The application
terminated abnormally because it was an exception thrown and not handled.
The
name of the program to which the unhandled exception is sent is HSSFR4
HSSFR4 SS_SAVE. The program has stopped at the high-level language
sentence
number (s) 0000015900 at the time of sending the message.
Working in V7R3M0, java 7 64 bits
Any suggestion will be appreciated
Thanks in advance.
Jorge Ubeda
midrange.com
