/**
* Update references within formulas
*
* @param string $pFormula Formula to update
* @param int $pBefore Insert before this one
* @param int $pNumCols Number of columns to insert
* @param int $pNumRows Number of rows to insert
* @return string Updated formula
* @throws Exception
*/
public function updateFormulaReferences($pFormula = '', $pBefore = 'A1', $pNumCols = 0, $pNumRows = 0) {
// Formula stack
$executableFormulaArray = array();
// Parse formula into a tree of tokens
$objParser = new PHPExcel_Calculation_FormulaParser($pFormula);
// Loop trough parsed tokens and create an executable formula
$inFunction = false;
$token = null;
for ($i = 0; $i < $objParser->getTokenCount(); $i++) {
$token = $objParser->getToken($i);
// Is it a cell reference? Not a cell range?
if ( ($token->getTokenType() == PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERAND) &&
($token->getTokenSubType() == PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_RANGE) ) {
// New cell reference
$newCellReference = $this->updateCellReference($token->getValue(), $pBefore, $pNumCols, $pNumRows);
// Add adjusted cell coordinate to executable formula array
array_push($executableFormulaArray, $newCellReference);
continue;
}
// Is it a subexpression?
if ($token->getTokenType() == PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_SUBEXPRESSION) {
// Temporary variable
$tmp = '';
switch($token->getTokenSubType()) {
case PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_START:
$tmp = '(';
break;
case PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_STOP:
$tmp = ')';
break;
}
// Add to executable formula array
array_push($executableFormulaArray, $tmp);
continue;
}
// Is it a function?
if ($token->getTokenType() == PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_FUNCTION) {
// Temporary variable
$tmp = '';
// Check the function type
if ($token->getValue() == 'ARRAY' || $token->getValue() == 'ARRAYROW') {
// An array or an array row...
$tmp = '(';
} else {
// A regular function call...
switch($token->getTokenSubType()) {
case PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_START:
$tmp = strtoupper($token->getValue()) . '(';
$inFunction = true;
break;
case PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_STOP:
$tmp = ')';
break;
}
}
// Add to executable formula array
array_push($executableFormulaArray, $tmp);
continue;
}
// Is it text?
if ( ($token->getTokenType() == PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERAND) &&
($token->getTokenSubType() == PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_TEXT) ) {
// Add to executable formula array
array_push($executableFormulaArray, '"' . $token->getValue() . '"');
continue;
}
// Is it something else?
array_push($executableFormulaArray, $token->getValue());
}
// Return result
return '=' . implode(' ', $executableFormulaArray);
}
/**
* Calculate cell value (using formula)
*
* @param PHPExcel_Cell $pCell Cell to calculate
* @return mixed
* @throws Exception
*/
public function calculate(PHPExcel_Cell $pCell = null)
{
// Return value
$returnValue = '';
// Is the value present in calculation cache?
if ($this->getCalculationCacheEnabled()) {
if (isset($this->_calculationCache[$pCell->getParent()->getTitle()][$pCell->getCoordinate()])) {
if (time() + microtime() - $this->_calculationCache[$pCell->getParent()->getTitle()][$pCell->getCoordinate()]['time'] < $this->_calculationCacheExpirationTime) {
// Return result
$returnValue = $this->_calculationCache[$pCell->getParent()->getTitle()][$pCell->getCoordinate()]['data'];
if (is_array($returnValue) && self::$returnArrayAsType == self::RETURN_ARRAY_AS_VALUE) {
return array_shift(PHPExcel_Calculation_Functions::flattenArray($returnValue));
}
return $returnValue;
} else {
unset($this->_calculationCache[$pCell->getParent()->getTitle()][$pCell->getCoordinate()]);
}
}
}
// Formula
$formula = $pCell->getValue();
// Executable formula array
$executableFormulaArray = array();
// Parse formula into a tree of tokens
$objParser = new PHPExcel_Calculation_FormulaParser($formula);
// Loop trough parsed tokens and create an executable formula
$inFunction = false;
$token = null;
$tokenCount = $objParser->getTokenCount();
for ($i = 0; $i < $tokenCount; ++$i) {
$token = $objParser->getToken($i);
$tokenType = $token->getTokenType();
$tokenSubType = $token->getTokenSubType();
$tokenValue = $token->getValue();
// Is it a cell reference?
if ($tokenType == PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERAND && $tokenSubType == PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_RANGE) {
// Adjust reference
$reference = str_replace('$', '', $tokenValue);
// Add to executable formula array
$executableFormulaArray[] = '$this->extractRange("' . $reference . '", $pCell->getParent())';
continue;
}
// Is it a concatenation operator?
if ($tokenType == PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERATORINFIX && $tokenSubType == PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_CONCATENATION) {
// Add to executable formula array
$executableFormulaArray[] = '.';
continue;
}
// Is it a logical operator?
if ($tokenType == PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERATORINFIX && $tokenSubType == PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_LOGICAL) {
// Temporary variable
$tmp = '';
switch ($tokenValue) {
case '=':
$tmp = '==';
break;
case '<>':
$tmp = '!=';
break;
default:
$tmp = $tokenValue;
}
// Add to executable formula array
$executableFormulaArray[] = $tmp;
continue;
}
// Is it a subexpression?
if ($tokenType == PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_SUBEXPRESSION) {
// Temporary variable
$tmp = '';
switch ($tokenSubType) {
case PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_START:
$tmp = '(';
break;
case PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_STOP:
$tmp = ')';
break;
}
// Add to executable formula array
$executableFormulaArray[] = $tmp;
continue;
}
// Is it a function?
if ($tokenType == PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_FUNCTION) {
// Temporary variable
$tmp = '';
// Check the function type
if ($tokenValue == 'ARRAY' || $tokenValue == 'ARRAYROW') {
// An array or an array row...
$tmp = 'array(';
} else {
// A regular function call...
switch ($tokenSubType) {
case PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_START:
// Check if the function call is allowed...
if (!isset($this->_functionMappings[strtoupper($tokenValue)])) {
return '#NAME?';
}
// Map the function call
$tmp = $this->_functionMappings[strtoupper($tokenValue)]->getPHPExcelName() . '(';
$inFunction = true;
break;
case PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_STOP:
$tmp = ')';
break;
}
}
// Add to executable formula array
$executableFormulaArray[] = $tmp;
continue;
}
// Is it text?
if ($tokenType == PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERAND && $tokenSubType == PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_TEXT) {
// Temporary variable
$tmp = $tokenValue;
$tmp = str_replace('"', '\\"', $tmp);
// Add to executable formula array
$executableFormulaArray[] = '"' . $tmp . '"';
continue;
}
// Is it a number?
if ($tokenType == PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERAND && $tokenSubType == PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_NUMBER) {
// Add to executable formula array
$executableFormulaArray[] = $tokenValue;
continue;
}
// Is it an error? Add it as text...
if ($tokenType == PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERAND && $tokenSubType == PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_ERROR) {
// Add to executable formula array
$executableFormulaArray[] = '"' . $tokenValue . '"';
continue;
}
// Is it something else?
$executableFormulaArray[] = $tokenValue;
}
$fromArray = array('(,', ',,', ',)', '( ,', ', ,', ', )', '$this');
$toArray = array('(null,', ',null,', ',null)', '(null,', ',null,', ',null)', '$pThat');
// Evaluate formula
try {
$formula = implode(' ', $executableFormulaArray);
$formula = str_replace($fromArray, $toArray, $formula);
/*
* The following code block can cause an error like:
* Fatal error: Unsupported operand types in ...: runtime-created function on line 1
*
* This is due to the fact that a FATAL error is an E_ERROR,
* and it can not be caught using try/catch or any other
* Exception/error handling feature in PHP.
*
* A feature request seems to be made once, but it has been
* closed without any deliverables:
* http://bugs.php.net/bug.php?id=40014
*/
$temporaryCalculationFunction = @create_function('$pThat, $pCell', "return {$formula};");
if ($temporaryCalculationFunction === FALSE) {
$returnValue = '#N/A';
} else {
$calculationExceptionHandler = new PHPExcel_Calculation_ExceptionHandler();
$returnValue = $temporaryCalculationFunction($this, $pCell);
}
} catch (Exception $ex) {
$returnValue = '#N/A';
}
// Save to calculation cache
if ($this->getCalculationCacheEnabled()) {
$this->_calculationCache[$pCell->getParent()->getTitle()][$pCell->getCoordinate()]['time'] = time() + microtime();
$this->_calculationCache[$pCell->getParent()->getTitle()][$pCell->getCoordinate()]['data'] = $returnValue;
}
// Return result
if (is_array($returnValue) && self::$returnArrayAsType == self::RETURN_ARRAY_AS_VALUE) {
return array_shift(PHPExcel_Calculation_Functions::flattenArray($returnValue));
}
return $returnValue;
}