Error on XLCRTDEMO

Scott Klement's open source interface to the POI HSSF/XSSF Spreadsheet Project for RPG Programmers. http://www.scottklement.com/poi/
wdwisser
Posts: 30
Joined: Tue Feb 15, 2022 5:12 pm

Error on XLCRTDEMO

Post by wdwisser »

Hello all... I am new to the board and I just downloaded and compiled the HSSF from SK website. I am trying to run XLCRTDEMO and I am getting the following error message (RNX0301):

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 did download the Apache POI 3.0 (poi-bin-3.0-FINAL-20070503.zip) and put it in the /QIBM/UserData/Java400/ext folder in the IFS. Can anyone tell me what I am missing to get this to run?

Thanks in advance.
Bill
Scott Klement
Site Admin
Posts: 653
Joined: Sun Jul 04, 2021 5:12 am

Re: Error on XLCRTDEMO

Post by Scott Klement »

It's hard to remember this far back, but I don't think POI 3.0 included XSSF (support for XLSX documents) functionality. It sounds like you're trying to use 2009 RPG code with 2007 versions of POI, and they aren't compatible.

I would suggest trying to get the 3.6 version of POI and other related classes (such as dom4j and xmlbeans) so that it's from the same 2009 version of the RPG code.

I haven't worked with POI any more recently than that, so I can't promise that it is compatible. I know other people have had luck with somewhat newer versions -- but, nobody has taken the time to update the RPG code for the latest version. (Apparently, if I don't do it, nobody will.)

Please don't put copies in /QIBM/UserData/Java400/ext. That is a very bad practice. Put them in your CLASSPATH.
wdwisser
Posts: 30
Joined: Tue Feb 15, 2022 5:12 pm

Re: Error on XLCRTDEMO

Post by wdwisser »

Thanks for the reply. I was going off of the instructions I had that said to put the jar files in the ext folder. I'm very new to the Java realm so I'm not even sure how the ClassPath works. Is it just a folder where your jar files exist?

I will download the 3.6 version of POI but I'm not sure what beans and dom4j are really?
wdwisser
Posts: 30
Joined: Tue Feb 15, 2022 5:12 pm

Re: Error on XLCRTDEMO

Post by wdwisser »

Also, do I need all the folders from the 3.6 version or just the main folder to be there (e.g. lib, docs, ooxml-lib)? I've attached an image of the folder I downloaded.

Thanks again.
Attachments
POI 3_6.JPG
POI 3_6.JPG (28.21 KiB) Viewed 11221 times
Scott Klement
Site Admin
Posts: 653
Joined: Sun Jul 04, 2021 5:12 am

Re: Error on XLCRTDEMO

Post by Scott Klement »

I don't think you need anything from those directories, but as I mentioned before, I have not used this stuff in more than 10 years -- I don't remember what's in those directories.

Back in those days, I had a CL program that ran when I logged on that set up the CLASSPATH. CLASSPATH is a variable that contains a list, separated by colons, of all of the JAR files (or directories containing .CLASS files -- though there are none of these in POI) that you will need. You can't reliably change the CLASSPATH during the job, so it is important to think ahead to all Java files you'll need.

This is an excerpt from the CL program:

Code: Select all

    ADDENVVAR ENVVAR(CLASSPATH) +
              VALUE('/home/sklement/java/poi3.6/xlparse.jar+
                    :/home/sklement/java/poi3.6/poi-3.6-20091214.jar+
                    :/home/sklement/java/poi3.6/poi-ooxml-3.6-20091214.jar+
                    :/home/sklement/java/poi3.6/+
                        poi-ooxml-schemas-3.6-20091214.jar+
                    :/home/sklement/java/dom4j/dom4j-1.6.1.jar+
                    :/home/sklement/java/xmlbeans/jsr173_1.0_api.jar+
                    :/home/sklement/java/xmlbeans/xbean.jar') +
              LEVEL(*JOB) +
              REPLACE(*YES)
Java will use the CLASSPATH variable to find the JAR files and the classes that are inside them. Note that it is case sensitive -- it must be CLASSPATH (never classpath or ClassPath). Obviously the IFS paths above will be different on your system -- be careful to get them right (it won't warn you if they are wrong) and be also be careful not to add any extra blanks.

I can't tell you what xmlbeans or dom4j are -- all I can tell you is that the XLSX format uses XML internally, and the POI classes use those to process the XML.

The advantage to using CLASSPATH (instead of the "ext" directory) is that each user can set their CLASSPATH differently and therefore work with different Java classes or different versions of different Java classes. If you put it in "ext", everyone on the system is forced to use the Java code and version that you put there.
wdwisser
Posts: 30
Joined: Tue Feb 15, 2022 5:12 pm

Re: Error on XLCRTDEMO

Post by wdwisser »

Thanks Dr. Klement... I really appreciate the input. I did download the POI 3.6 and the programs did run successfully so a step in the right direction. I'm assuming, after doing all this, that the POI36 library meant that you had this working with the POI version downloaded. A little slow on the uptake here I guess.

Again, thanks for the help and the tool here is pretty cool. Thanks for putting the time into creating it. Now I need to play with it to see how to get it into my programs as needed.
wdwisser
Posts: 30
Joined: Tue Feb 15, 2022 5:12 pm

Re: Error on XLCRTDEMO

Post by wdwisser »

I entered the following command after putting the poi36 files in the IFS... I'm now getting an error that it cannot find the jar files.

ADDENVVAR ENVVAR(CLASSPATH) +
VALUE('/Java/HSSF/poi-3.6-20091214.jar+
:/Java/HSSF/poi-ooxml-3.6-20091214.jar+
:/Java/HSSF/poi-ooxml-schemas-3.6-20091214.jar+
:/Java/HSSF/ooxml-lib/dom4j-1.6.1.jar+
') +
LEVEL(*JOB) +
REPLACE(*YES)

I'm confused why this is happening since all I did was move those files from "/QIBM/UserData/Java400/ext" to "/Java/HSSF"

Bill
Scott Klement
Site Admin
Posts: 653
Joined: Sun Jul 04, 2021 5:12 am

Re: Error on XLCRTDEMO

Post by Scott Klement »

Did you ensure that you ran that statement before running any Java in your job?

What is the exact message? (Java will never say it can't find the jar files -- it doesn't know that they're in a jar until it finds them.)
wdwisser
Posts: 30
Joined: Tue Feb 15, 2022 5:12 pm

Re: Error on XLCRTDEMO

Post by wdwisser »

I didn't have my java path setup correctly. I had a friend help me out and now it works just fine... my mistake from learning as I go.
j.lanham
Posts: 1
Joined: Wed Jul 28, 2021 3:11 pm

Re: Error on XLCRTDEMO

Post by j.lanham »

I know this is an old thread, but Scott, in your
ADDENVVAR ENVVAR(CLASSPATH) +
VALUE('/home/sklement/java/poi3.6/xlparse.jar+
:/home/sklement/java/poi3.6/poi-3.6-20091214.jar+
:/home/sklement/java/poi3.6/poi-ooxml-3.6-20091214.jar+
:/home/sklement/java/poi3.6/+
poi-ooxml-schemas-3.6-20091214.jar+
:/home/sklement/java/dom4j/dom4j-1.6.1.jar+
:/home/sklement/java/xmlbeans/jsr173_1.0_api.jar+
:/home/sklement/java/xmlbeans/xbean.jar') +
LEVEL(*JOB) +
REPLACE(*YES)
where are you getting the xlparse.jar from? It's not in the old poi3.6 zip file from apache.
Post Reply