Many of the issue come from folks trying to use old versions of POI, and a general lack of current IBM i specific documentation. I would submit that Python suffers from the latter as well, possibly even more so than Java. The current version of POI (3.14) runs well on 7.1 in the 6.26 JVM, is tested with Java 6, 7, and 8, and contains in it's jar all the necessary components. You don't have to get anything elsewhere as the dependencies have changed since Scott's articles were written. I find that it is useful to have a CL that sets up your environment, then you will always be able to set up your jobs quickly and with minimum pain. Here is my setup cl:
PGM
DCLPRCOPT DFTACTGRP(*NO) ACTGRP(*NEW) BNDDIR(JCS)
DCL VAR(&POINTER) TYPE(*PTR)
ADDENVVAR ENVVAR(CLASSPATH) +
VALUE('+
/java/poi-3.14/poi-3.14-20160307.jar:+
/java/poi-3.14/poi-ooxml-3.14-20160307.jar:+
/java/poi-3.14/poi-ooxml-schemas-3.14-20160307.jar:+
/java/poi-3.14/lib/commons-codec-1.10.jar:+
/java/poi-3.14/lib/commons-logging-1.2.jar:+
/java/poi-3.14/lib/log4j-1.2.17.jar:+
/java/poi-3.14/ooxml-lib/xmlbeans-2.6.0.jar:+
/java/xlparse36/xlparse.jar:+
') LEVEL(*JOB) REPLACE(*YES)
ADDENVVAR ENVVAR(QIBM_USE_DESCRIPTOR_STDIO) VALUE('Y')
ADDENVVAR ENVVAR(QIBM_RPG_JAVA_PROPERTIES) +
VALUE('+
-Djava.awt.headless=true;+
-Dos400.awt.native=true;+
-Dos400.stderr=file:/home/murphymdev/stderr.txt;+
') REPLACE(*YES)
ADDENVVAR ENVVAR(JAVA_HOME) +
VALUE('/QOpenSys/QIBM/ProdData/JavaVM/jdk626/32bit'+
) REPLACE(*YES)
ADDENVVAR ENVVAR(QIBM_RPG_JAVA_EXCP_TRACE) VALUE('Y')
CALLPRC PRC(START_JVM) RTNVAL(&POINTER)
out: ENDPGM
Some notes about this CL:
* everything in /poi-3.14/... comes in the POI download, you don't have to go off searching for other dependencies.
* /xlparse36/... is a utility written by Scott to ease reading Excel spreadsheets, you may or may not need this.
* at the end I call a procedure from HSSF4R to manually start the JVM. If you want to do this, you will have to export START_JVM and recompile it. Otherwise you can remove this call from the CLP.
As was mentioned before, If your JVM bombs you have to sign off, then sign back on to get a new JVM. This is mitigated by submitting to batch. In that case call this CL, as a first step, from your batch job.
To get the source into the IFS, I extract onto my PC, then use Windows explorer to drag and drop into the IFS. You may have to set up an appropriate share, and your paths may vary, just make sure they are correct in your CLP. I like to keep the version number in the path so I can download and test new versions of POI without bashing existing programs.
One other note, the current POI developers favor enums over the frequently used shorts and have started changing them out. An Enum can be treated as a class when defining it to RPG. So as long as the enum is defined in Java as a standalone entity rather than an inside class, you can use it. RPG cannot currently access inside classes as far as I know.
Mark Murphy
STAR BASE Consulting, Inc.
mmurphy@xxxxxxxxxxxxxxx
-----John Yeung <gallium.arsenide@xxxxxxxxx> wrote: -----
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
From: John Yeung <gallium.arsenide@xxxxxxxxx>
Date: 05/09/2016 12:39PM
Subject: Re: Error RNQ0202 HSSFR4 HSSF_GETSH
On Mon, May 9, 2016 at 10:03 AM, Buck Calabro <kc2hiz@xxxxxxxxx> wrote:
On 5/6/2016 5:29 PM, pat wrote:
be patient (i'm new in java !!)
The problem is that you are fighting several things all at once, and
it's going to be confusing that way. If I may make a suggestion, don't
start using Java this way. Instead, try it on your PC. Put the POI
classes on your PC, set your CLASSPATH and try the POI examples on your
PC to create (or read) a spreadsheet. I learnt many useful lessons this
way.
I like this suggestion. It's what I would recommend if learning Java
is a specific goal, just as I almost always recommend learning Python
on a PC (or Linux box or Mac), even if ultimately you want to use it
on the i.
However, if learning Java isn't so much an explicit goal as a
necessary step in being able to manipulate Excel spreadsheets on the
i, then I feel it's worth considering iSeriesPython instead of Java.
The whole setting-up-Java-for-POI-on-the-i thing is an oft-recurring
thread, and it just seems so much more susceptible to complications
than necessary. I specifically recommend iSeriesPython instead of Java
(and not even IBM's Python for PASE, unless you already have it
installed) because there are far fewer versions and other moving parts
to worry about. Everyone on V5R3 or later should be using the exact
same iSeriesPython version (namely, python27_revE.zip, as of this
writing), with the exact same setup, which takes just minutes to
achieve with little more than unpacking a couple of save files, then
doing one RSTOBJ and one RST.
If you don't already know Java and don't already have Java and POI
*properly* set up, consider iSeriesPython and either XlsxWriter or
OpenPyXL instead.
John Y.
midrange.com
