Problem with data formula in column

Topics: User Forum
Feb 10, 2011 at 3:05 AM

Hi all, i have a problem when i want get cell, the problem is when the data is a formula, i can't get it, but if the data is a value,this no problem. i use this,

$value = $objPHPExcel->setActiveSheetIndex(0)->getCell('G'.$i)->getCalculatedValue();

Sorry for my bad English, thank you,

Andika

Feb 10, 2011 at 5:46 AM

The formula is like this, =IF(F15=0,"",ROUNDUP(AU15,0))

and like this

=IF(E15=0,"",TEXT((AY16/60)/24,"h:mm"))

Feb 10, 2011 at 7:43 AM

try

$value = $objPHPExcel->setActiveSheetIndex(0)->getCell('G'.$i)->getOldCalculatedValue();

Coordinator
Feb 10, 2011 at 8:31 AM
Edited Feb 10, 2011 at 8:32 AM
$value = $objPHPExcel->setActiveSheetIndex(0)->getCell('G'.$i)->getValue();

Will return the actual formula if the cell contains a formula, or the value if the cell contains a value.

$value = $objPHPExcel->setActiveSheetIndex(0)->getCell('G'.$i)->getCalculatedValue();

Will return the calculated value if the cell contains a formula, or the value if the cell contains a value.

$value = $objPHPExcel->setActiveSheetIndex(0)->getCell('G'.$i)->getOldCalculatedValue();

Will return the previous result of a calculation if the cell contains a formula, or the value if the cell contains a value.

However - Note that when you save a workbook in MS Excel itself, it calculates the result for all formulae and stores the result in the workbook. This is the result returned by getOldCalculatedValue(). However, not all spreadsheet programs do this: OOCalc does; Gnumeric doesn't; Multiplan (SYLK) or CSV files don't hold this old calculated value either. If the loaded workbook hasn't done this calculation on save, then getOldCalculatedValue() will return a null.