Re: HSSFR4 POI36 Create XLSX -- RPG400-L

We’ve been on POI 4.1.2 (and POI 4.1.x) since the beginning of the year in a production environment after almost six months of part-time conversion from POI 3.17 to POI 4.1.x. In addition to the enumerated fields, quite a few methods have changed or deprecated, with the new/replacement methods having to be implemented. It was tons of work and testing, but my upgrade from POI 4.1.1 to 4.1.2 back in February required no changes on my part. I started with the simplest Excel documents we use under POI3.6 and resolved every compile issue one-by-one. Once that was done, I gained enough knowledge to tackle the most difficult Excel program we have in production (line-by-line .JPG images and formulas) until those were done. The graphics subsystem has changed the most and was the most difficult part of the migration.

It’s doable, but requires a big commitment.

I created a CL program that gets called prior to the POI jobs to set the Java paths for the job.

This is the classpath I use for our “older” POI 3.17 environment.

ADDENVVAR ENVVAR(CLASSPATH) +
VALUE('/java/poi-3.7/poi-3.7-20101029.jar+
:/java/poi-3.7/poi-ooxml-3.7-20101029.jar+
:/java/poi-3.7/poi-ooxml-schemas-3.7-20101029.jar+
:/java/poi-3.7/poi-scratchpad-3.7-20101029.jar+
:/java/poi-3.7/ooxml-lib/dom4j-1.6.1.jar+
:/java/poi-3.7/ooxml-lib/jsr173_1.0_api.jar+
:/java/poi-3.7/ooxml-lib/xbean-2.3.0.jar') +
LEVEL(*JOB) REPLACE(*YES)

ADDENVVAR ENVVAR(QIBM_RPG_JAVA_PROPERTIES) +
VALUE('-Djava.awt.headless=true;+
-Dos400.awt.native=true;') +
REPLACE(*YES)

ADDENVVAR ENVVAR(JAVA_HOME) +
VALUE('/QOpenSys/QIBM/ProdData/JavaVM/jdk71+
/64bit') REPLACE(*YES)

This is the new CL program I created for programs using the POI 4.1.x framework.
Also note that POI 4.1.x required JAVA8

ADDENVVAR ENVVAR(CLASSPATH) +
VALUE('/java/poi-4.1.2/poi-4.1.2.jar+
:/java/poi-4.1.2/poi-ooxml-4.1.2.jar+
:/java/poi-4.1.2/poi-ooxml-schemas-4.1.2.jar+
:/java/poi-4.1.2/poi-scratchpad-4.1.2.jar+
:/java/poi-4.1.2/lib/commons-collections4-4.4.jar+
:/java/poi-4.1.2/lib/commons-compress-1.19.jar+
:/java/poi-4.1.2/ooxml-lib/xmlbeans-3.1.0.jar') +
LEVEL(*JOB) REPLACE(*YES)

ADDENVVAR ENVVAR(QIBM_RPG_JAVA_PROPERTIES) +
VALUE('-Djava.awt.headless=true;+
-Dos400.awt.native=true;') +
REPLACE(*YES)

ADDENVVAR ENVVAR(JAVA_HOME) +
VALUE('/QOpenSys/QIBM/ProdData/JavaVM/jdk80+
/64bit') REPLACE(*YES)

On Jun 24, 2020, at 2:23 PM, Jordan Waterhouse <JordanWaterhouse@xxxxxxxxxxxx> wrote:

I tried getting the latest version, POI 4.1.2, to work with SK's but couldn't because of the enum problem. I have been able to use POI 3.17 successfully although I haven't tried it with all methods yet.

To get your downloads, go to archive.apache.org/dist/poi/release/bin. Use POI 3.6 or later. I've been using the 3.6 "version" of dom4j (dom4j-1.6.1.jar). The xmlbeans is in a directory within the POI directory you download. Just copy it to the same main directory of your jars.

Make sure you have your CLASSPATH set to where your POI jars are located in the IFS. Also, if you are using a separate IFS directory for the POI jars, make sure you set your CLASSPATH for your job first before running anything using them. Once you run your program to use POI, the CLASSPATH will not change the path the JVM is using. Any program using the jars will start the JVM with the current CLASSPATH. I change my job CLASSPATH immediately after signing on to my 5250 session. I use a job level CLASSPATH since I'm still testing POI 3.17.

I use the following CLASSPATH:

CHGENVVAR ENVVAR(CLASSPATH) +
VALUE('/java/poi-3.17/poi-3.17.jar:+
/java/poi-3.17/poi-ooxml-3.17.jar:+
/java/poi-3.17/poi-ooxml-schemas-3.17.jar:+
/java/poi-3.17/poi-excelant-3.17.jar:+
/java/poi3.6/dom4j-1.6.1.jar:+
/java/poi-3.17/xmlbeans-2.6.0.jar:+
/java/poi-3.17/commons-collections4-4.1.jar')

I hope this helps...

-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf Of Kevin Bucknum
Sent: Wednesday, June 24, 2020 3:50 PM
To: rpg400-l@xxxxxxxxxxxxxxxxxx
Subject: Re: HSSFR4 POI36 Create XLSX

The POI download page has a link to archives. https://poi.apache.org/download.html#archive Somewhere in the 3.1x range they started changing some classes to use enums. That requires changes on the RPG side. I think 3.14 may be the last safe version.

On Wed, 2020-06-24 at 15:42 -0500, K Crawford wrote:

I am trying to get the sample program POI36/XLCRTDEMO to work.

I am getting this message:

RNQ0301

Message . . . . : Java exception received when calling Java method (C G D

F).

Cause . . . . . : RPG procedure XLCRTDEMO in program POI36/XLCRTDEMO

received Java exception "java.lang.NoClassDefFoundError:

org.apache.poi.xssf.usermodel.XSSFWorkbook" when calling method "<init>"

with signature "()V" in class

"org.apache.poi.xssf.usermodel.XSSFWorkbook".

I saw in the archives someone with this same issue. The solution was to

make sure the class path was set correctly. It talks about

'/java/poi-3.11/poi-3.11-20141221.jar'.

on the Apache POI the version that is available is 'poi-4.1.2/poi-4.1.2.jar'

I have this in '/java/poi-4.1.2/poi-4.1.2.jar'

I have signed off and on.

Entered the command ADDENVVAR ENVVAR(classpath)

VALUE('/java/poi-4.1.2/poi-4.1.2.jar')

then called the XlCrtDemo program.

Is my issue it must be poi-3.11? If so, where do I get it?

I have not installed DOM4J or XMlBeans. Could that be the issue? I was

unclear if they were needed.

--

Kerwin Crawford

[https://www.medtronsoftware.com/img/MedtronMinilogo.bmp] Kevin Bucknum
Senior Programmer Analyst
MEDDATA / MEDTRON
120 Innwood Drive
Covington LA 70433
Local: 985-893-2550
Toll Free: 877-893-2550
https://www.medtronsoftware.com

CONFIDENTIALITY NOTICE

This document and any accompanying this email transmission contain confidential information, belonging to the sender that is legally privileged. This information is intended only for the use of the individual or entity named above. The authorized recipient of this information is prohibited from disclosing this information to any other party and is required to destroy the information after its stated need has been fulfilled. If you are not the intended recipient, or the employee of agent responsible to deliver it to the intended recipient, you are hereby notified that any disclosure, copying, distribution or action taken in reliance on the contents of these documents is STRICTLY PROHIBITED. If you have received this email in error, please notify the sender immediately to arrange for return or destruction of these documents.
--
This is the RPG programming on IBM i (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://amazon.midrange.com
--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://amazon.midrange.com