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

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

Buck,

I'm using it too, didn't see anything in there that did this particular thing.

I'm guessing that I need the below method, which returns an int value -

evaluateFormulaCell()
http://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.html#evaluateFormulaCell%28org.apache.poi.ss.usermodel.Cell%29

So, I'm trying to piece it together now.

Thanks,

John

I use Scott Klement's wrappers over POI/HSSF:
http://www.scottklement.com/poi/
--buck

OK, I missed that. You're on the right track. I set up the evaluator
when I've opened the sheet:

// open the right tab in the workbook
sheet = ss_getSheet(book: 'Page1_1');
if (sheet = *null);
Complain('No sheet in workbook!');
endif;
maxRow = getLastRowNum(sheet);

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

and then in the loop where I read the columns:

when type = CELL_TYPE_FORMULA;
// evaluate the formula
newCellValue = evaluate(formulaEvaluator: cell);
newType = CellValue_getCellType(newCellValue);
if newType = CELL_TYPE_NUMERIC;
tempFloat = CellValue_getNumberValue(newCellValue);
else;
// handle as error
endif;

Hope this helps. I basically copied one of Scott's examples, but any
errors are mine :-)
--buck