POI 3.6 setCellFormula gets NoSuchMethodError -- JAVA400-L

After happily using POI 2.0 with Scott Klement's HSSFR4 service program for several years, I finally got a project that requires upgrading to a version that can handle .xlsx spreadsheets. Luckily, Scott has been busy keeping up with this, and has an upgraded HSSFR4.

I installed Scott's service program, and POI 3.6, then set the environment for 3.6 with the following environment variables:

Name . . . . . . . . . : QIBM_RPG_JAVA_PROPERTIES
Value . . . . . . . . . : '-Djava.awt.headless=true;-Dos400.awt.native=true;'

Name . . . . . . . . . : JAVA_HOME
Value . . . . . . . . . : '/QOpenSys/QIBM/ProdData/JavaVM/jdk50/32bit'

Name . . . . . . . . . : CLASSPATH
Value . . . . . . . . . : '/java/poi3.6/poi-contrib-3.6-20091214.jar:/java/po
i3.6/poi-examples-3.6-20091214.jar:/java/poi3.6/poi-ooxml-schemas-3.6-20091214.j
ar:/java/poi3.6/poi-ooxml-3.6-20091214.jar:/java/poi3.6/poi-scratchpad-3.6-20091
214.jar:/java/poi3.6/poi-3.6-20091214.jar:/java/poi3.6/lib/commons-logging-1.1.j
ar:/java/poi3.6/lib/junit-3.8.1.jar:/java/poi3.6/lib/log4j-1.2.13.jar:/java/poi3
.6/ooxml-lib/dom4j-1.6.1.jar:/java/poi3.6/ooxml-lib/geronimo-stax-api_1.0_spec-1
.0.jar:/java/poi3.6/ooxml-lib/xmlbeans-2.3.0.jar'

CHGCURLIB POI36

and did

CALL XLCRTDEMO '1'

which got the following:

Message ID . . . . . . : RNX0301 Severity . . . . . . . : 50
Message type . . . . . : Escape
Date sent . . . . . . : 06/23/11 Time sent . . . . . . : 16:34:37

Message . . . . : Java exception received when calling Java method.
Cause . . . . . : RPG procedure SS_FORMULA in program POI36/HSSFR4 received
Java exception "java.lang.NoSuchMethodError:
org/apache/poi/hssf/record/formula/AbstractFunctionPtg.isBuiltInFunctio"
when calling method "setCellFormula" with signature "(Ljava.lang.String;)V"
in class "org.apache.poi.ss.usermodel.Cell".

In HSSFR4, the procedure SS_FORMULA does this:

0580.00 SSCell_setCellFormula(wwCell: wwStr);

And SSCell_setCellFormula has the following prototype in HSSF_H:

0793.00 D SSCell_setCellFormula...
0794.00 D PR EXTPROC(*JAVA
0795.00 D : CELL_CLASS
0796.00 D : 'setCellFormula')
0797.00 D cell_formula like(jString)

where CELL_CLASS is defined as

0172.00 D CELL_CLASS...
0173.00 D C 'org.apache.poi.ss.usermodel-
0174.00 D .Cell'

A couple of things jumped out at me:

- in the error message, AbstractFunctionPtg.isBuiltInFunctio" looks chopped off, so the question is, what's the full name that it cannot find? isBuiltInFunction?

- the error message refers to a class in poi/hssf which is the old .xls spreadsheet stuff, not the newer .xlsx stuff which is in poi/ss.

I did some googling and saw that someone else had a similar error, and Scott noted that the problem appears to be in the POI Java code. Not being a Java programmer, I nevertheless did some poking around -- I got Java Decompiler jd-gui.exe version 0.3.3 installed on my PC and used it to look at

org.apache.poi.ss.usermodel.Cell.setCellFormula

and all I see is

public abstract void setCellFormula(String paramString)
throws FormulaParseException;

Since this is an "abstract" class, there is no code; presumably a non-abstract subclass implements the SetCellFormula method. I'm guessing here that Java looks in every .jar file in the class path and manages to find AbstractFunctionPtg.isBuiltInFunctio" in org/apache/poi/hssf/record/formula. At this point I'm getting kind of lost in the Java code, but it seems ok to me.

Can any of you Java experts see anything obvious that I'm doing wrong? Or is this a POI problem?

--
*Peter Dow* /
Dow Software Services, Inc.
909 793-9050
pdow@xxxxxxxxxxxxxxx <mailto:pdow@xxxxxxxxxxxxxxx> /