| midrange.com code scratchpad |
|
Name:
John Rusling
|
Scriptlanguage:
Plain Text
|
Tabwidth:
4
|
Date:
05/02/2014 03:08:33 pm
|
IP:
Logged
|
|
|
Description:
Snip of rpg code to use poi36 formula evaluator
|
|
Code:
- // this code is in addition to the hssfr4 code
- // it provides the formula evaluator functionality (i believe)
- eeee // Support objects/prototypes for formula evaluator, these are used
- . // to extract numeric values from a formula type cell that
- . // calculates a numeric value.
- 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')
- // Evaluate a formula and return the value, leaving the original cell intact
- // because FormulaEvaluator is an interface and cant 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 in HSSFCGI/QRPGLESRC2(HSSF_H) 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 )
- eeee d helper s like(SSCreationHelper)
-
- calcs. . .
-
- //*********************************************************************
- begsr @process;
-
- // Subr =
- //*********************************************************************
-
- ss_begin_object_group(1000);
-
- // Open an existing spreadsheet
- book = ss_open(gSSPathFile);
- if (book = *null);
- Complain('Unable to open workbook!');
- endif;
-
- // Set the worksheet to read
- sheet = ss_getSheet(book:gSheet);
- if (sheet = *null);
- Complain('Sheet not in workbook!');
- endif;
-
- eeee // Do this after workbook is opened to be able to evaluate formulas
- eeee helper = SSWorkbook_getCreationHelper(book);
- eeee formulaEvaluator = creationHelper_createFormulaEvaluator(helper);
-
- // set row, col to read
- gCol = col_B;
- gRow = row_4;
- setRow(gRow);
- setCell(gCol);
-
- eeee myNbr = extractNumericValue(cell);
-
- //gResultSS = '/johnr/tspoi8 results.xlsx';
- //ss_save(book:gResultSS);
- ss_end_object_group();
-
- msg_User(psds.User:'Calculated formula value is... ' + %char(myNbr));
-
- endsr;
-
-
-
|
|
|
|