POI 3.6 setCellFormula gets NoSuchMethodError

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.

Should I post this on the Java400 list? Or over at POI?

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