Re: Need help getting HSSF to read a xlsx file.

On 07-Nov-2015 09:27 -0600, Darryl Freinkel wrote:

I have resolved some issues but now have a hard halt when trying to
execute ss_open().

I am trying to get my first program to work reading a spreadsheet
using Scott's examples and guidance.

Sometimes helpful to give the link(s) from which that is gleaned; a reader can then see whence what is being presented, was essentially derived\mimicked.

I think I have it mostly correct but I am unable to debug through the
HSSFR4 service program.

It fails on the ss_open(). Here is the error.


Additional Message Information

Message ID . . . : RNX0301 Severity . . . . : 50
Message type . . : Escape
Date sent . . . : 11/07/15 Time sent . . . : 08:30:27
Message . . . . : Java exception received when calling Java method.
Cause . . . . . : RPG procedure SS_OPEN in program POI36/HSSFR4
received Java exception "java.lang.NoClassDefFoundError:
org/apache/poi/ss/usermodel/WorkbookFactory" when calling method
"create" with signature
"(Ljava.io.InputStream;)Lorg.apache.poi.ss.usermodel.Workbook;" in
class "org.apache.poi.ss.usermodel.WorkbookFactory".
Recovery . . . : Contact the person responsible for program
maintenance to determine the cause of the problem. Technical
description . . . . . . . . : If the exception indicates that the
Java class was not found, ensure the class for the method is in the
class path. If the exception indicates that the Java method was not
found, check ...

The is the code:

pgm

DCL VAR(&IFS_PATH) TYPE(*CHAR) LEN(256) +
VALUE('"/ax_on_i/i_Brands and Prod Family/"')
dcl &return_Cod *char 2

chgvar &ifs_path '/ax_on_i/test/'

ADDENVVAR ENVVAR(CLASSPATH) +
VALUE('.:/Java/dom4j-1.6.1:/Java/poi-3.6:/Java/xmlbeans')
monmsg cpf0000

addlible poi36 *last
monmsg cpf0000

addlible xlparser4
monmsg cpf0000

call AX_XTRACT1 parm(&IFS_path &return_Cod)

if (&return_Cod = ' ') then(do)
call ax_brands1
enddo

rclrsc

RMVENVVAR ENVVAR(CLASSPATH)

endpgm

H*
H* Program : AX_BRANDS2
H* Purpose : Read spreadsheet and write out data to file AX_BRANDS2.
H*
H DFTACTGRP(*NO)
H OPTION(*SRCSTMT: *NODEBUGIO: *NOSHOWCPY)
H THREAD(*SERIALIZE)
H BNDDIR('HSSF')
/copy QSYSINC/QRPGLESRC,JNI
/copy POI36/qrpglesrc,hssf_h
D* book s like(HSSFWorkbook)
D book s like(XSSFWorkbook)
D sheet s like(HSSFSheet)
D row s like(HSSFRow)
D cell s like(HSSFCell)
D TempStr s like(jString)
D StrVal s 52A varying
D NumVal s 8F
D type s 10I 0
d content_rcd s 256a
D Spreadsheet_Name...
D s 256a
D Spreadsheet_rcd...
D s 1024a varying
D Full_Path s 256a
D email_message s 32767a varying
d crlf c x'0D25'
D Error_Exists s n Inz(*off)
D End s 10i 0
D Start s 10i 0
D String_getBytes...
D pr 1024A varying
D extproc(*JAVA:
D 'java.lang.String':
D 'getBytes')
d validate_header_Row...
d pr
d get_spreadsheet_Name...
d pr
d Retrieve_Cell pr
/free
Exec Sql set option naming = *SYS,
commit = *none,
usrprf = *USER,
dynusrprf = *USER,
DATFMT = *ISO,
CloSqlCsr = *ENDMOD;
hssf_begin_object_group(100);
// Retrieve the xlsx file name from the file CONTENTS
get_Spreadsheet_Name();
//
// Load an existing spreadsheet into memory
//
// Full_Path = '/ax_on_i/i_Brands and Prod Family/' +
// %trim(Spreadsheet_Name);
// book = hssf_open(Full_Path);
monitor;
book = ss_open(Full_Path);
on-error;
*INLR = *on;
Return;
endmon;
sheet = hssf_getSheet(book: 'Active Products');
//
// Validate the header row
//
Validate_header_row();

TIA.

Not that there is any relationship to the issue, but abstaining from the horrible practice of MONMSG CPF0000 should really be a priority in coding CL.

See "Environment Starters" in the following article for a CLP that is coded more appropriately [though in my browser the source appears as\on one long line], used to "set up an environment for the new 3.6 version of POI" [and probably the rest of the article, for what might be relevant to the issue; perhaps mimicking much more closely what is shown there, and heeding any /warnings/ or other tidbits of import noted throughout, might better help to resolve the issue]:
[http://iprodeveloper.com/system-i-network/new-poi-release-brings-excels-xlsx-support-rpg]

FWiW, one such note of import, one of the "some important things to note about these environments", and I infer is apparently quite commonly a stumbling-block for anyone new to JNI:

"• If you want to switch environments, you'll need to end the job (sign off if it's an interactive job) and start a new job in order to pick up the new settings."

And to be clear, "switch environments" is not explicitly referring to between POI3.1 and POI3.6, but any environment-change for which the JVM already being started in the job precludes those changes from going into effect for the already-started JVM; notably, the CLASSPATH, which is one of the "environment variables [that] are checked only when the JVM first loads ... [so] you must set up your environment before Java is used in a given job" and is noted as another of the "important things" in that article.