Re: Read excel spreadsheet formula-type cell, return the numeric value calculated.

On 6/4/2013 3:46 PM, John Rusling wrote:

Thanks Buck!

Do you happen to have the d spec types for helper, formulaEvaluator and newCellValue?

Do you have the *java d spec prototypes for

SSWorkbook_getCreationHelper

creationHelper_createFormulaEvaluator

new_CellValue

?

(I'm trying to cut my trial and error time down to hours instead of days <g>)

tfah,

John

// be able to evaluate formulas
helper = SSWorkbook_getCreationHelper(book);
formulaEvaluator = creationHelper_createFormulaEvaluator(helper);
newCellValue = new_CellValue(0);

Whoops! I forgot what bits I extended and what bits I stole :-/

d helper s like(SSCreationHelper)
d newCell s like(SSCell)
d newCellValue s like(CellValue)

d* support objects for formula evaluator
d CELL_VALUE_CLASS...
d c 'org.apache.poi.ss.usermodel-
d .CellValue'
d CellValue...
d s o class(*JAVA: CELL_VALUE_CLASS)
d new_CellValue...
d pr like(CellValue)
d extproc(*JAVA:
d CELL_VALUE_CLASS:
d *constructor)
d value like(jDouble) value
d CellValue_getCellType...
d pr like(jInt)
d extproc(*JAVA:
d CELL_VALUE_CLASS:
d 'getCellType')
d CellValue_getNumberValue...
d pr like(jDouble)
d extproc(*JAVA:
d CELL_VALUE_CLASS:
d 'getNumberValue')

d* evaluate a formula and return the value, leaving the original cell intact
d* because FormulaEvaluator is an interface, can't directly create one
d FORMULA_EVALUATOR_CLASS...
d c 'org.apache.poi.ss.usermodel-
d .FormulaEvaluator'
d FormulaEvaluator...
d s o class(*JAVA: FORMULA_EVALUATOR_CLASS)
d CreationHelper_createFormulaEvaluator...
d pr like(formulaEvaluator)
d extproc(*java:
d CREATIONHELPER_CLASS:
d 'createFormulaEvaluator')
d evaluate...
d pr like(CellValue)
d extproc(*JAVA:
d FORMULA_EVALUATOR_CLASS:
d 'evaluate')
d cell like(SSCell)

SSWorkbook_getCreationHelper is in HSSFCGI/QRPGLESRC2(HSSF_H) but here
it is for reference:

D HSSF_CREATIONHELPER_CLASS...
D C 'org.apache.poi.hssf.usermodel-
D .HSSFCreationHelper'

D HSSFCreationHelper...
D S O CLASS( *JAVA
D : HSSF_CREATIONHELPER_CLASS )

D CREATIONHELPER_CLASS...
D C 'org.apache.poi.ss.usermodel-
D .CreationHelper'

D SSCreationHelper...
D S O CLASS( *JAVA
D : CREATIONHELPER_CLASS )



*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
* SSWorkbook_getCreationHelper():
* Retrieves a Java object that helps create objects in the
* appropriate (HSSF or XSSF) class.
*
* Returns the SSCreationHelper object
*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SSWorkbook_getCreationHelper...
PR like(SSCreationHelper)
EXTPROC(*JAVA
: WORKBOOK_CLASS
:'getCreationHelper')
HSSFWorkbook_getCreationHelper...
PR like(HSSFCreationHelper)
EXTPROC(*JAVA
: HSSF_WORKBOOK_CLASS
:'getCreationHelper')

SSCreationHelper_createClientAnchor...
D PR like(SSClientAnchor)
D EXTPROC(*JAVA
D : CREATIONHELPER_CLASS
D :'createClientAnchor')
D HSSFCreationHelper_createClientAnchor...
D PR like(HSSFClientAnchor)
D EXTPROC(*JAVA
D : HSSF_CREATIONHELPER_CLASS
D :'createClientAnchor')

D SSCreationHelper_createDataFormat...
D PR like(SSDataFormat)
D EXTPROC(*JAVA
D : CREATIONHELPER_CLASS
D :'createDataFormat')
D HSSFCreationHelper_createDataFormat...
D PR like(HSSFDataFormat)
D EXTPROC(*JAVA
D : HSSF_CREATIONHELPER_CLASS
D :'createDataFormat')

I think that's all of it...
--buck