/**
* Calculate the value of a cell formula
*
* @access public
* @param PHPExcel_Cell $pCell Cell to calculate
* @param Boolean $resetLog Flag indicating whether the debug log should be reset or not
* @return mixed
* @throws Exception
*/
public function calculateCellValue(PHPExcel_Cell $pCell = null, $resetLog = true)
{
if ($resetLog) {
// Initialise the logging settings if requested
$this->formulaError = null;
$this->debugLog = $this->debugLogStack = array();
$this->_cyclicFormulaCount = 1;
$returnArrayAsType = self::$returnArrayAsType;
self::$returnArrayAsType = self::RETURN_ARRAY_AS_ARRAY;
}
// Read the formula from the cell
if (is_null($pCell)) {
return null;
}
if ($resetLog) {
self::$returnArrayAsType = $returnArrayAsType;
}
// Execute the calculation for the cell formula
try {
$result = self::_unwrapResult($this->_calculateFormulaValue($pCell->getValue(), $pCell->getCoordinate(), $pCell));
} catch (Exception $e) {
throw new Exception($e->getMessage());
}
if (is_array($result) && self::$returnArrayAsType != self::RETURN_ARRAY_AS_ARRAY) {
$testResult = PHPExcel_Calculation_Functions::flattenArray($result);
if (self::$returnArrayAsType == self::RETURN_ARRAY_AS_ERROR) {
return PHPExcel_Calculation_Functions::VALUE();
}
// If there's only a single cell in the array, then we allow it
if (count($testResult) != 1) {
// If keys are numeric, then it's a matrix result rather than a cell range result, so we permit it
$r = array_keys($result);
$r = array_shift($r);
if (!is_numeric($r)) {
return PHPExcel_Calculation_Functions::VALUE();
}
if (is_array($result[$r])) {
$c = array_keys($result[$r]);
$c = array_shift($c);
if (!is_numeric($c)) {
return PHPExcel_Calculation_Functions::VALUE();
}
}
}
$result = array_shift($testResult);
}
if (is_null($result)) {
return 0;
} elseif (is_float($result) && (is_nan($result) || is_infinite($result))) {
return PHPExcel_Calculation_Functions::NaN();
}
return $result;
}
/**
* Calculate the value of a cell formula
*
* @access public
* @param PHPExcel_Cell $pCell Cell to calculate
* @param Boolean $resetLog Flag indicating whether the debug log should be reset or not
* @return mixed
* @throws PHPExcel_Calculation_Exception
*/
public function calculateCellValue(PHPExcel_Cell $pCell = NULL, $resetLog = TRUE)
{
if ($pCell === NULL) {
return NULL;
}
$returnArrayAsType = self::$returnArrayAsType;
if ($resetLog) {
// Initialise the logging settings if requested
$this->formulaError = null;
$this->_debugLog->clearLog();
$this->_cyclicReferenceStack->clear();
$this->_cyclicFormulaCount = 1;
self::$returnArrayAsType = self::RETURN_ARRAY_AS_ARRAY;
}
// Execute the calculation for the cell formula
$this->_cellStack[] = array('sheet' => $pCell->getWorksheet()->getTitle(), 'cell' => $pCell->getCoordinate());
try {
$result = self::_unwrapResult($this->_calculateFormulaValue($pCell->getValue(), $pCell->getCoordinate(), $pCell));
$cellAddress = array_pop($this->_cellStack);
$this->_workbook->getSheetByName($cellAddress['sheet'])->getCell($cellAddress['cell']);
} catch (PHPExcel_Exception $e) {
$cellAddress = array_pop($this->_cellStack);
$this->_workbook->getSheetByName($cellAddress['sheet'])->getCell($cellAddress['cell']);
throw new PHPExcel_Calculation_Exception($e->getMessage());
}
if (is_array($result) && self::$returnArrayAsType != self::RETURN_ARRAY_AS_ARRAY) {
self::$returnArrayAsType = $returnArrayAsType;
$testResult = PHPExcel_Calculation_Functions::flattenArray($result);
if (self::$returnArrayAsType == self::RETURN_ARRAY_AS_ERROR) {
return PHPExcel_Calculation_Functions::VALUE();
}
// If there's only a single cell in the array, then we allow it
if (count($testResult) != 1) {
// If keys are numeric, then it's a matrix result rather than a cell range result, so we permit it
$r = array_keys($result);
$r = array_shift($r);
if (!is_numeric($r)) {
return PHPExcel_Calculation_Functions::VALUE();
}
if (is_array($result[$r])) {
$c = array_keys($result[$r]);
$c = array_shift($c);
if (!is_numeric($c)) {
return PHPExcel_Calculation_Functions::VALUE();
}
}
}
$result = array_shift($testResult);
}
self::$returnArrayAsType = $returnArrayAsType;
if ($result === NULL) {
return 0;
} elseif (is_float($result) && (is_nan($result) || is_infinite($result))) {
return PHPExcel_Calculation_Functions::NaN();
}
return $result;
}
/**
* Set the Array Return Type (Array or Value of first element in the array)
*
* @param string $returnType Array return type
* @return boolean Success or failure
*/
public static function setArrayReturnType($returnType)
{
if ($returnType == self::RETURN_ARRAY_AS_VALUE || $returnType == self::RETURN_ARRAY_AS_ARRAY) {
self::$returnArrayAsType = $returnType;
return True;
}
return False;
}
