예제 #1
0
    /**
     * 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);
    }
예제 #2
0
 /**
  * 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;
 }