Source for file Calculation.php
Documentation is available at Calculation.php
* Copyright (c) 2006 - 2009 PHPExcel * This library is free software; you can redistribute it and/or * modify it under the terms of the GNU Lesser General Public * License as published by the Free Software Foundation; either * version 2.1 of the License, or (at your option) any later version. * This library is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU * Lesser General Public License for more details. * You should have received a copy of the GNU Lesser General Public * License along with this library; if not, write to the Free Software * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA * @package PHPExcel_Calculation * @copyright Copyright (c) 2006 - 2009 PHPExcel (http://www.codeplex.com/PHPExcel) * @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL * @version 1.6.5, 2009-01-05 /** PHPExcel_Worksheet */ require_once 'PHPExcel/Worksheet.php'; require_once 'PHPExcel/Cell.php'; /** PHPExcel_Cell_DataType */ require_once 'PHPExcel/Cell/DataType.php'; require_once 'PHPExcel/RichText.php'; /** PHPExcel_NamedRange */ require_once 'PHPExcel/NamedRange.php'; /** PHPExcel_Calculation_FormulaParser */ require_once 'PHPExcel/Calculation/FormulaParser.php'; /** PHPExcel_Calculation_FormulaToken */ require_once 'PHPExcel/Calculation/FormulaToken.php'; /** PHPExcel_Calculation_Functions */ require_once 'PHPExcel/Calculation/Functions.php'; /** PHPExcel_Calculation_Function */ require_once 'PHPExcel/Calculation/Function.php'; * PHPExcel_Calculation (Singleton) * @package PHPExcel_Calculation * @copyright Copyright (c) 2006 - 2009 PHPExcel (http://www.codeplex.com/PHPExcel) const RETURN_ARRAY_AS_VALUE = 'value'; const RETURN_ARRAY_AS_ARRAY = 'array'; private static $returnArrayAsType = self::RETURN_ARRAY_AS_ARRAY; * Function mappings (from Excel to PHPExcel) * Calculation cache enabled * Calculation cache expiration time * @var PHPExcel_Calculation private static $_instance; * Get an instance of this class * @return PHPExcel_Calculation if (! isset ( self::$_instance ) || is_null ( self::$_instance )) { self::$_instance = new PHPExcel_Calculation ( ); * 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 if (($returnType == self::RETURN_ARRAY_AS_VALUE) || ($returnType == self::RETURN_ARRAY_AS_ARRAY)) { self::$returnArrayAsType = $returnType; } // function setExcelCalendar() * Return the Array Return Type (Array or Value of first element in the array) * @return string $returnType Array return type return self::$returnArrayAsType; } // function getExcelCalendar() * Create a new PHPExcel_Calculation // Assign function mappings * Is calculation caching enabled? * Enable/disable calculation cache * Clear calculation cache * Get calculation cache expiration time * Set calculation cache expiration time * Calculate cell value (using formula) * @param PHPExcel_Cell $pCell Cell to calculate public function calculate(PHPExcel_Cell $pCell = null) { // Is the value present in calculation cache? if (isset ( $this->_calculationCache [$pCell->getParent ()->getTitle ()] [$pCell->getCoordinate ()] )) { $returnValue = $this->_calculationCache [$pCell->getParent ()->getTitle ()] [$pCell->getCoordinate ()] ['data']; if ((is_array($returnValue)) && (self::$returnArrayAsType == self::RETURN_ARRAY_AS_VALUE)) { unset ( $this->_calculationCache [$pCell->getParent ()->getTitle ()] [$pCell->getCoordinate ()] ); $formula = $pCell->getValue (); // Executable formula array $executableFormulaArray = array ( ); // Parse formula into a tree of tokens // Loop trough parsed tokens and create an executable formula $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? Not in a function? // Add to executable formula array $executableFormulaArray[] = '$this->extractCell("'. $reference. '", $pCell)'; // Is it a cell reference? In a function? // Add to executable formula array $executableFormulaArray[] = '$this->extractRange("'. $reference. '", $pCell->getParent())'; // Is it a concatenation operator? // Add to executable formula array $executableFormulaArray[] = '.'; // Is it a logical operator? // Add to executable formula array $executableFormulaArray[] = $tmp; // Is it a subexpression? // Add to executable formula array $executableFormulaArray[] = $tmp; // Check the function type if ($tokenValue == 'ARRAY' || $tokenValue == 'ARRAYROW') { // An array or an array row... // A regular function call... // Check if the function call is allowed... // Add to executable formula array $executableFormulaArray[] = $tmp; // Add to executable formula array $executableFormulaArray[] = '"'. $tmp. '"'; // Add to executable formula array $executableFormulaArray[] = $tokenValue; // Is it an error? Add it as text... // Add to executable formula array $executableFormulaArray[] = '"'. $tokenValue. '"'; $executableFormulaArray[] = $tokenValue; $fromArray = array('(,', ',,', ',)', '( ,', ', ,', ', )', '$this'); $toArray = array('(null,', ',null,', ',null)', '(null,', ',null,', ',null)', '$pThat'); $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 = @$temporaryCalculationFunction ( $this , $pCell ); } catch ( Exception $ex ) { // Save to calculation cache $this->_calculationCache [$pCell->getParent ()->getTitle ()] [$pCell->getCoordinate ()] ['data'] = $returnValue; if ((is_array($returnValue)) && (self::$returnArrayAsType == self::RETURN_ARRAY_AS_VALUE)) { * __clone implementation. Cloning should not be allowed in a Singleton! throw new Exception ( "Cloning a Singleton is not allowed!" ); * @param string $pRange String based range representation * @param PHPExcel_Worksheet $pSheet Worksheet * @return mixed Array of values in range if range contains more than one element. Otherwise, a single value is returned. public function extractRange($pRange = 'A1', PHPExcel_Worksheet $pSheet = null) { $returnValue = array ( ); if (strpos ( $pRange, '!' ) !== false) { $pSheet = $pSheet->getParent ()->getSheetByName ( $worksheetReference [0] ); $pRange = $worksheetReference [1]; $pRange = $namedRange->getRange (); if ($pSheet->getHashCode () != $namedRange->getWorksheet ()->getHashCode ()) { if (! $namedRange->getLocalOnly ()) { $pSheet = $namedRange->getWorksheet (); if( count ( $aReferences ) == 1 ){ return $pSheet->getCell ( $aReferences[0] )->getCalculatedValue (); foreach ( $aReferences as $reference ) { $returnValue [$currentCol] [$currentRow] = $pSheet->getCell ( $reference )->getCalculatedValue (); * @param string $reference * @param PHPExcel_Cell $pCell public function extractCell($reference = 'A1', PHPExcel_Cell $pCell = null) { $cell = $pCell->getParent ()->getCell ( $reference ); return $cell->getValue ()->getPlainText (); return $cell->getCalculatedValue (); * Is a specific function implemented? * @param string $pFunction Function return $this->_functionMappings [$pFunction]->getPHPExcelName () == 'PHPExcel_Calculation_Functions::DUMMY'; * Get a list of implemented functions foreach ($aFunctions as $function) { if ($function->getPHPExcelName() != 'PHPExcel_Calculation_Functions::DUMMY') { $returnValue[] = $function; * Get a list of implemented Excel function names foreach ($aFunctions as $function) { $returnValue[] = $function->getExcelName();
|
