📁 PHP Dosya Yöneticisi
/
/
home
/
demodesigncom
/
belediyescripti.demodesign.com.tr
/
_class
/
Classes
/
PHPExcel
📝
Calculation.php
← Geri Dön
<?php if (!defined("PHPEXCEL_ROOT")) { define("PHPEXCEL_ROOT", dirname(__FILE__) . "/../"); require PHPEXCEL_ROOT . "PHPExcel/Autoloader.php"; } if (!defined("CALCULATION_REGEXP_CELLREF")) { if (defined("PREG_BAD_UTF8_ERROR")) { define("CALCULATION_REGEXP_CELLREF", "((([^\\s,!&%^\\/\\*\\+<>=-]*)|('[^']*')|(\\\"[^\\\"]*\\\"))!)?\\\$?([a-z]{1,3})\\\$?(\\d{1,7})"); define("CALCULATION_REGEXP_NAMEDRANGE", "((([^\\s,!&%^\\/\\*\\+<>=-]*)|('[^']*')|(\\\"[^\\\"]*\\\"))!)?([_A-Z][_A-Z0-9\\.]*)"); } else { define("CALCULATION_REGEXP_CELLREF", "(((\\w*)|('[^']*')|(\\\"[^\\\"]*\\\"))!)?\\\$?([a-z]{1,3})\\\$?(\\d+)"); define("CALCULATION_REGEXP_NAMEDRANGE", "(((\\w*)|('.*')|(\\\".*\\\"))!)?([_A-Z][_A-Z0-9\\.]*)"); } } class PHPExcel_Calculation { private $workbook = NULL; private static $workbookSets = NULL; private $calculationCache = true; private $calculationCacheEnabled = NULL; private static $operators = ["+" => true, "-" => true, "*" => true, "/" => true, "^" => true, "&" => true, "%" => false, "~" => false, ">" => true, "<" => true, "=" => true, ">=" => true, "<=" => true, "<>" => true, "|" => true, ":" => true]; private static $binaryOperators = ["+" => true, "-" => true, "*" => true, "/" => true, "^" => true, "&" => true, ">" => true, "<" => true, "=" => true, ">=" => true, "<=" => true, "<>" => true, "|" => true, ":" => true]; private $debugLog = NULL; public $suppressFormulaErrors = []; public $formulaError = 1; private $cyclicReferenceStack = ""; private $cellStack = 1; private $cyclicFormulaCounter = 1.0E-13; private $cyclicFormulaCell = 1.0E-13; public $cyclicFormulaCount = 1.0E-13; private $delta; private static $localeLanguage = "en_us"; private static $validLocaleLanguages = ["en"]; private static $localeArgumentSeparator = ","; private static $localeFunctions = []; public static $localeBoolean = ["TRUE" => "TRUE", "FALSE" => "FALSE", "NULL" => "NULL"]; private static $excelConstants = ["TRUE" => true, "FALSE" => false, "NULL" => NULL]; private static $PHPExcelFunctions = NULL; private static $controlFunctions = ["MKMATRIX" => ["argumentCount" => "*", "functionCall" => "self::mkMatrix"]]; private static $functionReplaceFromExcel = NULL; private static $functionReplaceToLocale = NULL; private static $functionReplaceFromLocale = NULL; private static $functionReplaceToExcel = NULL; private static $operatorAssociativity = ["^" => 0, "*" => 0, "/" => 0, "+" => 0, "-" => 0, "&" => 0, "|" => 0, ":" => 0, ">" => 0, "<" => 0, "=" => 0, ">=" => 0, "<=" => 0, "<>" => 0]; private static $comparisonOperators = [">" => true, "<" => true, "=" => true, ">=" => true, "<=" => true, "<>" => true]; private static $operatorPrecedence = [":" => 8, "|" => 7, "~" => 6, "%" => 5, "^" => 4, "*" => 3, "/" => 3, "+" => 2, "-" => 2, "&" => 1, ">" => 0, "<" => 0, "=" => 0, ">=" => 0, "<=" => 0, "<>" => 0]; private static $returnArrayAsType = "value"; private static $instance = NULL; const CALCULATION_REGEXP_NUMBER = "[-+]?\\d*\\.?\\d+(e[-+]?\\d+)?"; const CALCULATION_REGEXP_STRING = "\"(?:[^\"]|\"\")*\""; const CALCULATION_REGEXP_OPENBRACE = "\\("; const CALCULATION_REGEXP_FUNCTION = "@?([A-Z][A-Z0-9\\.]*)[\\s]*\\("; const CALCULATION_REGEXP_CELLREF = CALCULATION_REGEXP_CELLREF; const CALCULATION_REGEXP_NAMEDRANGE = CALCULATION_REGEXP_NAMEDRANGE; const CALCULATION_REGEXP_ERROR = "\\#[A-Z][A-Z0_\\/]*[!\\?]?"; const RETURN_ARRAY_AS_ERROR = "error"; const RETURN_ARRAY_AS_VALUE = "value"; const RETURN_ARRAY_AS_ARRAY = "array"; public function __construct(PHPExcel $workbook = NULL) { $this->delta = 1 * pow(10, 0 - ini_get("precision")); $this->workbook = $workbook; $this->cyclicReferenceStack = new PHPExcel_CalcEngine_CyclicReferenceStack(); $this->_debugLog = new PHPExcel_CalcEngine_Logger($this->cyclicReferenceStack); } private static function loadLocales() { $localeFileDirectory = PHPEXCEL_ROOT . "PHPExcel/locale/"; foreach (glob($localeFileDirectory . "/*", GLOB_ONLYDIR) as $filename) { $filename = substr($filename, strlen($localeFileDirectory) + 1); if ($filename != "en") { self::$validLocaleLanguages[] = $filename; } } } public static function getInstance(PHPExcel $workbook = NULL) { if ($workbook !== NULL) { $instance = $workbook->getCalculationEngine(); if (isset($instance)) { return $instance; } } if (self::$instance === NULL) { self::$instance = new PHPExcel_Calculation(); } return self::$instance; } public function __destruct() { $this->workbook = NULL; } public function flushInstance() { $this->clearCalculationCache(); } public function getDebugLog() { return $this->_debugLog; } public final function __clone() { throw new PHPExcel_Calculation_Exception("Cloning the calculation engine is not allowed!"); } public static function getTRUE() { return self::$localeBoolean["TRUE"]; } public static function getFALSE() { return self::$localeBoolean["FALSE"]; } public static function setArrayReturnType($returnType) { if ($returnType == "value" || $returnType == "error" || $returnType == "array") { self::$returnArrayAsType = $returnType; return true; } return false; } public static function getArrayReturnType() { return self::$returnArrayAsType; } public function getCalculationCacheEnabled() { return $this->calculationCacheEnabled; } public function setCalculationCacheEnabled($pValue = true) { $this->calculationCacheEnabled = $pValue; $this->clearCalculationCache(); } public function enableCalculationCache() { $this->setCalculationCacheEnabled(true); } public function disableCalculationCache() { $this->setCalculationCacheEnabled(false); } public function clearCalculationCache() { $this->calculationCache = []; } public function clearCalculationCacheForWorksheet($worksheetName) { if (isset($this->calculationCache[$worksheetName])) { unset($this->calculationCache[$worksheetName]); } } public function renameCalculationCacheForWorksheet($fromWorksheetName, $toWorksheetName) { if (isset($this->calculationCache[$fromWorksheetName])) { $this->calculationCache[$fromWorksheetName]; $this->calculationCache[$toWorksheetName] =& $this->calculationCache[$fromWorksheetName]; unset($this->calculationCache[$fromWorksheetName]); } } public function getLocale() { return self::$localeLanguage; } public function setLocale($locale = "en_us") { $language = $locale = strtolower($locale); if (strpos($locale, "_") !== false) { list($language) = explode("_", $locale); } if (count(self::$validLocaleLanguages) == 1) { self::loadLocales(); } if (in_array($language, self::$validLocaleLanguages)) { self::$localeFunctions = []; self::$localeArgumentSeparator = ","; self::$localeBoolean = ["TRUE" => "TRUE", "FALSE" => "FALSE", "NULL" => "NULL"]; if ($locale != "en_us") { $functionNamesFile = PHPEXCEL_ROOT . "PHPExcel" . DIRECTORY_SEPARATOR . "locale" . DIRECTORY_SEPARATOR . str_replace("_", DIRECTORY_SEPARATOR, $locale) . DIRECTORY_SEPARATOR . "functions"; if (!file_exists($functionNamesFile)) { $functionNamesFile = PHPEXCEL_ROOT . "PHPExcel" . DIRECTORY_SEPARATOR . "locale" . DIRECTORY_SEPARATOR . $language . DIRECTORY_SEPARATOR . "functions"; if (!file_exists($functionNamesFile)) { return false; } } $localeFunctions = file($functionNamesFile, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES); foreach ($localeFunctions as $localeFunction) { list($localeFunction) = explode("##", $localeFunction); if (strpos($localeFunction, "=") !== false) { list($fName, $lfName) = explode("=", $localeFunction); $fName = trim($fName); $lfName = trim($lfName); if (isset(self::$PHPExcelFunctions[$fName]) && $lfName != "" && $fName != $lfName) { self::$localeFunctions[$fName] = $lfName; } } } if (isset(self::$localeFunctions["TRUE"])) { self::$localeBoolean["TRUE"] = self::$localeFunctions["TRUE"]; } if (isset(self::$localeFunctions["FALSE"])) { self::$localeBoolean["FALSE"] = self::$localeFunctions["FALSE"]; } $configFile = PHPEXCEL_ROOT . "PHPExcel" . DIRECTORY_SEPARATOR . "locale" . DIRECTORY_SEPARATOR . str_replace("_", DIRECTORY_SEPARATOR, $locale) . DIRECTORY_SEPARATOR . "config"; if (!file_exists($configFile)) { $configFile = PHPEXCEL_ROOT . "PHPExcel" . DIRECTORY_SEPARATOR . "locale" . DIRECTORY_SEPARATOR . $language . DIRECTORY_SEPARATOR . "config"; } if (file_exists($configFile)) { $localeSettings = file($configFile, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES); foreach ($localeSettings as $localeSetting) { list($localeSetting) = explode("##", $localeSetting); if (strpos($localeSetting, "=") !== false) { list($settingName, $settingValue) = explode("=", $localeSetting); $settingName = strtoupper(trim($settingName)); switch ($settingName) { case "ARGUMENTSEPARATOR": self::$localeArgumentSeparator = trim($settingValue); break; } } } } } self::$functionReplaceFromExcel = self::$functionReplaceToExcel = self::$functionReplaceFromLocale = self::$functionReplaceToLocale = NULL; self::$localeLanguage = $locale; return true; } else { return false; } } public static function translateSeparator($fromSeparator, $toSeparator, $formula, &$inBraces) { $strlen = mb_strlen($formula); $i = 0; while ($i < $strlen) { $chr = mb_substr($formula, $i, 1); switch ($chr) { case "{": $inBraces = true; break; case "}": $inBraces = false; break; case $fromSeparator: if (!$inBraces) { $formula = mb_substr($formula, 0, $i) . $toSeparator . mb_substr($formula, $i + 1); } break; default: $i++; } } return $formula; } private static function translateFormula($from, $to, $formula, $fromSeparator, $toSeparator) { if (self::$localeLanguage !== "en_us") { $inBraces = false; if (strpos($formula, "\"") !== false) { $temp = explode("\"", $formula); $i = false; if ($i = !$i) { $value = preg_replace($from, $to, $value); $value = self::translateSeparator($fromSeparator, $toSeparator, $value, $inBraces); } unset($value); $formula = implode("\"", $temp); } else { $formula = preg_replace($from, $to, $formula); $formula = self::translateSeparator($fromSeparator, $toSeparator, $formula, $inBraces); } } return $formula; } public function _translateFormulaToLocale($formula) { if (self::$functionReplaceFromExcel === NULL) { self::$functionReplaceFromExcel = []; foreach (array_keys(self::$localeFunctions) as $excelFunctionName) { self::$functionReplaceFromExcel[] = "/(@?[^\\w\\.])" . preg_quote($excelFunctionName) . "([\\s]*\\()/Ui"; } foreach (array_keys(self::$localeBoolean) as $excelBoolean) { self::$functionReplaceFromExcel[] = "/(@?[^\\w\\.])" . preg_quote($excelBoolean) . "([^\\w\\.])/Ui"; } } if (self::$functionReplaceToLocale === NULL) { self::$functionReplaceToLocale = []; foreach (array_values(self::$localeFunctions) as $localeFunctionName) { self::$functionReplaceToLocale[] = "\$1" . trim($localeFunctionName) . "\$2"; } foreach (array_values(self::$localeBoolean) as $localeBoolean) { self::$functionReplaceToLocale[] = "\$1" . trim($localeBoolean) . "\$2"; } } return self::translateFormula(self::$functionReplaceFromExcel, self::$functionReplaceToLocale, $formula, ",", self::$localeArgumentSeparator); } public function _translateFormulaToEnglish($formula) { if (self::$functionReplaceFromLocale === NULL) { self::$functionReplaceFromLocale = []; foreach (array_values(self::$localeFunctions) as $localeFunctionName) { self::$functionReplaceFromLocale[] = "/(@?[^\\w\\.])" . preg_quote($localeFunctionName) . "([\\s]*\\()/Ui"; } foreach (array_values(self::$localeBoolean) as $excelBoolean) { self::$functionReplaceFromLocale[] = "/(@?[^\\w\\.])" . preg_quote($excelBoolean) . "([^\\w\\.])/Ui"; } } if (self::$functionReplaceToExcel === NULL) { self::$functionReplaceToExcel = []; foreach (array_keys(self::$localeFunctions) as $excelFunctionName) { self::$functionReplaceToExcel[] = "\$1" . trim($excelFunctionName) . "\$2"; } foreach (array_keys(self::$localeBoolean) as $excelBoolean) { self::$functionReplaceToExcel[] = "\$1" . trim($excelBoolean) . "\$2"; } } return self::translateFormula(self::$functionReplaceFromLocale, self::$functionReplaceToExcel, $formula, self::$localeArgumentSeparator, ","); } public static function localeFunc($function) { if (self::$localeLanguage !== "en_us") { $functionName = trim($function, "("); if (isset(self::$localeFunctions[$functionName])) { $brace = $functionName != $function; $function = self::$localeFunctions[$functionName]; if ($brace) { $function .= "("; } } } return $function; } public static function wrapResult($value) { if (is_string($value)) { if (preg_match("/^\\#[A-Z][A-Z0_\\/]*[!\\?]?\$/i", $value, $match)) { return $value; } return "\"" . $value . "\""; } if (is_float($value) && (is_nan($value) || is_infinite($value))) { return PHPExcel_Calculation_Functions::NaN(); } return $value; } public static function unwrapResult($value) { if (is_string($value)) { if (isset($value[0]) && $value[0] == "\"" && substr($value, -1) == "\"") { return substr($value, 1, -1); } } else { if (is_float($value) && (is_nan($value) || is_infinite($value))) { return PHPExcel_Calculation_Functions::NaN(); } } return $value; } public function calculate(PHPExcel_Cell $pCell = NULL) { try { return $this->calculateCellValue($pCell); } catch (PHPExcel_Exception $e) { throw new PHPExcel_Calculation_Exception($e->getMessage()); } } public function calculateCellValue(PHPExcel_Cell $pCell = NULL, $resetLog = true) { while ($pCell === NULL) { $returnArrayAsType = self::$returnArrayAsType; if ($resetLog) { $this->formulaError = NULL; $this->_debugLog->clearLog(); $this->cyclicReferenceStack->clear(); $this->cyclicFormulaCounter = 1; self::$returnArrayAsType = "array"; } $this->cellStack[] = ["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"]); if (is_array($result) && self::$returnArrayAsType != "array") { self::$returnArrayAsType = $returnArrayAsType; $testResult = PHPExcel_Calculation_Functions::flattenArray($result); if (self::$returnArrayAsType == "error") { return PHPExcel_Calculation_Functions::VALUE(); } if (count($testResult) != 1) { $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; } if (is_float($result) && (is_nan($result) || is_infinite($result))) { return PHPExcel_Calculation_Functions::NaN(); } return $result; } catch (PHPExcel_Exception $e) { $cellAddress = array_pop($this->cellStack); $this->workbook->getSheetByName($cellAddress["sheet"])->getCell($cellAddress["cell"]); throw new PHPExcel_Calculation_Exception($e->getMessage()); } } } public function parseFormula($formula) { $formula = trim($formula); if (!isset($formula[0]) || $formula[0] != "=") { return []; } $formula = ltrim(substr($formula, 1)); if (!isset($formula[0])) { return []; } return $this->_parseFormula($formula); } public function calculateFormula($formula, $cellID = NULL, PHPExcel_Cell $pCell = NULL) { $this->formulaError = NULL; $this->_debugLog->clearLog(); $this->cyclicReferenceStack->clear(); if ($this->workbook !== NULL && $cellID === NULL && $pCell === NULL) { $cellID = "A1"; $pCell = $this->workbook->getActiveSheet()->getCell($cellID); } else { $resetCache = $this->getCalculationCacheEnabled(); $this->calculationCacheEnabled = false; } try { $result = self::unwrapResult($this->_calculateFormulaValue($formula, $cellID, $pCell)); if ($this->workbook === NULL) { $this->calculationCacheEnabled = $resetCache; } return $result; } catch (PHPExcel_Exception $e) { throw new PHPExcel_Calculation_Exception($e->getMessage()); } } public function getValueFromCache($cellReference, &$cellValue) { $this->_debugLog->writeDebugLog("Testing cache value for cell ", $cellReference); if ($this->calculationCacheEnabled && isset($this->calculationCache[$cellReference])) { $this->_debugLog->writeDebugLog("Retrieving value for cell ", $cellReference, " from cache"); $cellValue = $this->calculationCache[$cellReference]; return true; } return false; } public function saveValueToCache($cellReference, $cellValue) { if ($this->calculationCacheEnabled) { $this->calculationCache[$cellReference] = $cellValue; } } public function _calculateFormulaValue($formula, $cellID = NULL, PHPExcel_Cell $pCell = NULL) { $cellValue = NULL; $formula = trim($formula); if ($formula[0] != "=") { return self::wrapResult($formula); } $formula = ltrim(substr($formula, 1)); if (!isset($formula[0])) { return self::wrapResult($formula); } $pCellParent = $pCell !== NULL ? $pCell->getWorksheet() : NULL; $wsTitle = $pCellParent !== NULL ? $pCellParent->getTitle() : "\0Wrk"; $wsCellReference = $wsTitle . "!" . $cellID; if ($cellID !== NULL && $this->getValueFromCache($wsCellReference, $cellValue)) { return $cellValue; } if ($wsTitle[0] !== "\0" && $this->cyclicReferenceStack->onStack($wsCellReference)) { if ($this->cyclicFormulaCount <= 0) { $this->cyclicFormulaCell = ""; return $this->raiseFormulaError("Cyclic Reference in Formula"); } if ($this->cyclicFormulaCell === $wsCellReference) { $this->cyclicFormulaCounter++; if ($this->cyclicFormulaCount <= $this->cyclicFormulaCounter) { $this->cyclicFormulaCell = ""; return $cellValue; } } else { if ($this->cyclicFormulaCell == "") { if ($this->cyclicFormulaCount <= $this->cyclicFormulaCounter) { return $cellValue; } $this->cyclicFormulaCell = $wsCellReference; } } } $this->cyclicReferenceStack->push($wsCellReference); $cellValue = $this->processTokenStack($this->_parseFormula($formula, $pCell), $cellID, $pCell); $this->cyclicReferenceStack->pop(); if ($cellID !== NULL) { $this->saveValueToCache($wsCellReference, $cellValue); } return $cellValue; } private static function checkMatrixOperands(&$operand1, &$operand2, $resize = 1) { if (!is_array($operand1)) { list($matrixRows, $matrixColumns) = self::getMatrixDimensions($operand2); $operand1 = array_fill(0, $matrixRows, array_fill(0, $matrixColumns, $operand1)); $resize = 0; } else { if (!is_array($operand2)) { list($matrixRows, $matrixColumns) = self::getMatrixDimensions($operand1); $operand2 = array_fill(0, $matrixRows, array_fill(0, $matrixColumns, $operand2)); $resize = 0; } } list($matrix1Rows, $matrix1Columns) = self::getMatrixDimensions($operand1); list($matrix2Rows, $matrix2Columns) = self::getMatrixDimensions($operand2); if ($matrix1Rows == $matrix2Columns && $matrix2Rows == $matrix1Columns) { $resize = 1; } if ($resize == 2) { self::resizeMatricesExtend($operand1, $operand2, $matrix1Rows, $matrix1Columns, $matrix2Rows, $matrix2Columns); } else { if ($resize == 1) { self::resizeMatricesShrink($operand1, $operand2, $matrix1Rows, $matrix1Columns, $matrix2Rows, $matrix2Columns); } } return [$matrix1Rows, $matrix1Columns, $matrix2Rows, $matrix2Columns]; } private static function getMatrixDimensions(&$matrix) { $matrixRows = count($matrix); $matrixColumns = 0; foreach ($matrix as $rowKey => $rowValue) { $matrixColumns = max(count($rowValue), $matrixColumns); if (!is_array($rowValue)) { $matrix[$rowKey] = [$rowValue]; } else { $matrix[$rowKey] = array_values($rowValue); } } $matrix = array_values($matrix); return [$matrixRows, $matrixColumns]; } private static function resizeMatricesShrink(&$matrix1, &$matrix2, $matrix1Rows, $matrix1Columns, $matrix2Rows, $matrix2Columns) { if ($matrix2Columns < $matrix1Columns || $matrix2Rows < $matrix1Rows) { if ($matrix2Rows < $matrix1Rows) { for ($i = $matrix2Rows; $i < $matrix1Rows; $i++) { unset($matrix1[$i]); } } if ($matrix2Columns < $matrix1Columns) { for ($i = 0; $i < $matrix1Rows; $i++) { for ($j = $matrix2Columns; $j < $matrix1Columns; $j++) { unset($matrix1[$i][$j]); } } } } if ($matrix1Columns < $matrix2Columns || $matrix1Rows < $matrix2Rows) { if ($matrix1Rows < $matrix2Rows) { for ($i = $matrix1Rows; $i < $matrix2Rows; $i++) { unset($matrix2[$i]); } } if ($matrix1Columns < $matrix2Columns) { for ($i = 0; $i < $matrix2Rows; $i++) { for ($j = $matrix1Columns; $j < $matrix2Columns; $j++) { unset($matrix2[$i][$j]); } } } } } private static function resizeMatricesExtend(&$matrix1, &$matrix2, $matrix1Rows, $matrix1Columns, $matrix2Rows, $matrix2Columns) { if ($matrix2Columns < $matrix1Columns || $matrix2Rows < $matrix1Rows) { if ($matrix2Columns < $matrix1Columns) { for ($i = 0; $i < $matrix2Rows; $i++) { $x = $matrix2[$i][$matrix2Columns - 1]; for ($j = $matrix2Columns; $j < $matrix1Columns; $j++) { $matrix2[$i][$j] = $x; } } } if ($matrix2Rows < $matrix1Rows) { $x = $matrix2[$matrix2Rows - 1]; for ($i = 0; $i < $matrix1Rows; $i++) { $matrix2[$i] = $x; } } } if ($matrix1Columns < $matrix2Columns || $matrix1Rows < $matrix2Rows) { if ($matrix1Columns < $matrix2Columns) { for ($i = 0; $i < $matrix1Rows; $i++) { $x = $matrix1[$i][$matrix1Columns - 1]; for ($j = $matrix1Columns; $j < $matrix2Columns; $j++) { $matrix1[$i][$j] = $x; } } } if ($matrix1Rows < $matrix2Rows) { $x = $matrix1[$matrix1Rows - 1]; for ($i = 0; $i < $matrix2Rows; $i++) { $matrix1[$i] = $x; } } } } private function showValue($value) { if ($this->_debugLog->getWriteDebugLog()) { $testArray = PHPExcel_Calculation_Functions::flattenArray($value); if (count($testArray) == 1) { $value = array_pop($testArray); } if (is_array($value)) { $returnMatrix = []; $pad = $rpad = ", "; foreach ($value as $row) { if (is_array($row)) { $returnMatrix[] = implode($pad, array_map([$this, "showValue"], $row)); $rpad = "; "; } else { $returnMatrix[] = $this->showValue($row); } } return "{ " . implode($rpad, $returnMatrix) . " }"; } else { if (is_string($value) && trim($value, "\"") == $value) { return "\"" . $value . "\""; } if (is_bool($value)) { return $value ? self::$localeBoolean["TRUE"] : self::$localeBoolean["FALSE"]; } } } return PHPExcel_Calculation_Functions::flattenSingleValue($value); } private function showTypeDetails($value) { if ($this->_debugLog->getWriteDebugLog()) { $testArray = PHPExcel_Calculation_Functions::flattenArray($value); if (count($testArray) == 1) { $value = array_pop($testArray); } if ($value === NULL) { return "a NULL value"; } if (is_float($value)) { $typeString = "a floating point number"; } else { if (is_int($value)) { $typeString = "an integer number"; } else { if (is_bool($value)) { $typeString = "a boolean"; } else { if (is_array($value)) { $typeString = "a matrix"; } else { if ($value == "") { return "an empty string"; } if ($value[0] == "#") { return "a " . $value . " error"; } $typeString = "a string"; } } } } return $typeString . " with a value of " . $this->showValue($value); } } private function convertMatrixReferences($formula) { if (strpos($formula, "{") !== false) { if (strpos($formula, "\"") !== false) { $temp = explode("\"", $formula); $openCount = $closeCount = 0; $i = false; if ($i = !$i) { $openCount += substr_count($value, "{"); $closeCount += substr_count($value, "}"); $value = str_replace($matrixReplaceFrom, $matrixReplaceTo, $value); } unset($value); $formula = implode("\"", $temp); } else { $openCount = substr_count($formula, "{"); $closeCount = substr_count($formula, "}"); $formula = str_replace($matrixReplaceFrom, $matrixReplaceTo, $formula); } if ($openCount < $closeCount) { if (0 < $openCount) { return $this->raiseFormulaError("Formula Error: Mismatched matrix braces '}'"); } return $this->raiseFormulaError("Formula Error: Unexpected '}' encountered"); } if ($closeCount < $openCount) { if (0 < $closeCount) { return $this->raiseFormulaError("Formula Error: Mismatched matrix braces '{'"); } return $this->raiseFormulaError("Formula Error: Unexpected '{' encountered"); } } return $formula; } private static function mkMatrix() { return func_get_args(); } private function _parseFormula($formula, PHPExcel_Cell $pCell = NULL) { if (($formula = $this->convertMatrixReferences(trim($formula))) === false) { return false; } $pCellParent = $pCell !== NULL ? $pCell->getWorksheet() : NULL; $regexpMatchString = "/^(@?([A-Z][A-Z0-9\\.]*)[\\s]*\\(|" . CALCULATION_REGEXP_CELLREF . "|" . "[-+]?\\d*\\.?\\d+(e[-+]?\\d+)?" . "|" . "\"(?:[^\"]|\"\")*\"" . "|" . "\\(" . "|" . CALCULATION_REGEXP_NAMEDRANGE . "|" . "\\#[A-Z][A-Z0_\\/]*[!\\?]?" . ")/si"; $index = 0; $stack = new PHPExcel_Calculation_Token_Stack(); $output = []; $expectingOperator = false; $expectingOperand = false; while (true) { $opCharacter = $formula[$index]; if (isset(self::$comparisonOperators[$opCharacter]) && $index < strlen($formula) && isset(self::$comparisonOperators[$formula[$index + 1]])) { $opCharacter .= $formula[++$index]; } $isOperandOrFunction = preg_match($regexpMatchString, substr($formula, $index), $match); if ($opCharacter == "-" && !$expectingOperator) { $stack->push("Unary Operator", "~"); $index++; } else { if ($opCharacter == "%" && $expectingOperator) { $stack->push("Unary Operator", "%"); $index++; } else { if ($opCharacter == "+" && !$expectingOperator) { $index++; } else { if (($opCharacter == "~" || $opCharacter == "|") && !$isOperandOrFunction) { return $this->raiseFormulaError("Formula Error: Illegal character '~'"); } if ((isset(self::$operators[$opCharacter]) || $isOperandOrFunction) && $expectingOperator) { while (0 < $stack->count() && ($o2 = $stack->last()) && isset(self::$operators[$o2["value"]]) && (self::$operatorAssociativity[$opCharacter] ? self::$operatorPrecedence[$opCharacter] < self::$operatorPrecedence[$o2["value"]] : self::$operatorPrecedence[$opCharacter] <= self::$operatorPrecedence[$o2["value"]])) { $output[] = $stack->pop(); } $stack->push("Binary Operator", $opCharacter); $index++; $expectingOperator = false; } else { if ($opCharacter == ")" && $expectingOperator) { $expectingOperand = false; while (($o2 = $stack->pop()) && $o2["value"] != "(") { if ($o2 === NULL) { return $this->raiseFormulaError("Formula Error: Unexpected closing brace \")\""); } $output[] = $o2; } $d = $stack->last(2); if (preg_match("/^@?([A-Z][A-Z0-9\\.]*)[\\s]*\\(\$/i", $d["value"], $matches)) { $functionName = $matches[1]; $d = $stack->pop(); $argumentCount = $d["value"]; $output[] = $d; $output[] = $stack->pop(); if (isset(self::$controlFunctions[$functionName])) { $expectedArgumentCount = self::$controlFunctions[$functionName]["argumentCount"]; $functionCall = self::$controlFunctions[$functionName]["functionCall"]; } else { if (isset(self::$PHPExcelFunctions[$functionName])) { $expectedArgumentCount = self::$PHPExcelFunctions[$functionName]["argumentCount"]; $functionCall = self::$PHPExcelFunctions[$functionName]["functionCall"]; } else { return $this->raiseFormulaError("Formula Error: Internal error, non-function on stack"); } } $argumentCountError = false; if (is_numeric($expectedArgumentCount)) { if ($expectedArgumentCount < 0) { if (abs($expectedArgumentCount) < $argumentCount) { $argumentCountError = true; $expectedArgumentCountString = "no more than " . abs($expectedArgumentCount); } } else { if ($argumentCount != $expectedArgumentCount) { $argumentCountError = true; $expectedArgumentCountString = $expectedArgumentCount; } } } else { if ($expectedArgumentCount != "*") { $isOperandOrFunction = preg_match("/(\\d*)([-+,])(\\d*)/", $expectedArgumentCount, $argMatch); switch ($argMatch[2]) { case "+": if ($argumentCount < $argMatch[1]) { $argumentCountError = true; $expectedArgumentCountString = $argMatch[1] . " or more "; } break; case "-": if ($argumentCount < $argMatch[1] || $argMatch[3] < $argumentCount) { $argumentCountError = true; $expectedArgumentCountString = "between " . $argMatch[1] . " and " . $argMatch[3]; } break; case ",": if ($argumentCount != $argMatch[1] && $argumentCount != $argMatch[3]) { $argumentCountError = true; $expectedArgumentCountString = "either " . $argMatch[1] . " or " . $argMatch[3]; } break; } } } if ($argumentCountError) { return $this->raiseFormulaError("Formula Error: Wrong number of arguments for " . $functionName . "() function: " . $argumentCount . " given, " . $expectedArgumentCountString . " expected"); } } $index++; } else { if ($opCharacter == ",") { while (($o2 = $stack->pop()) && $o2["value"] != "(") { if ($o2 === NULL) { return $this->raiseFormulaError("Formula Error: Unexpected ,"); } $output[] = $o2; } if ($expectingOperand || !$expectingOperator) { $output[] = ["type" => "NULL Value", "value" => self::$excelConstants["NULL"], "reference" => NULL]; } $d = $stack->last(2); if (!preg_match("/^@?([A-Z][A-Z0-9\\.]*)[\\s]*\\(\$/i", $d["value"], $matches)) { return $this->raiseFormulaError("Formula Error: Unexpected ,"); } $d = $stack->pop(); $stack->push($d["type"], ++$d["value"], $d["reference"]); $stack->push("Brace", "("); $expectingOperator = false; $expectingOperand = true; $index++; } else { if ($opCharacter == "(" && !$expectingOperator) { $stack->push("Brace", "("); $index++; } else { if ($isOperandOrFunction && !$expectingOperator) { $expectingOperator = true; $expectingOperand = false; $val = $match[1]; $length = strlen($val); if (preg_match("/^@?([A-Z][A-Z0-9\\.]*)[\\s]*\\(\$/i", $val, $matches)) { $val = preg_replace("/\\s/u", "", $val); if (isset(self::$PHPExcelFunctions[strtoupper($matches[1])]) || isset(self::$controlFunctions[strtoupper($matches[1])])) { $stack->push("Function", strtoupper($val)); $ax = preg_match("/^\\s*(\\s*\\))/ui", substr($formula, $index + $length), $amatch); if ($ax) { $stack->push("Operand Count for Function " . strtoupper($val) . ")", 0); $expectingOperator = true; } else { $stack->push("Operand Count for Function " . strtoupper($val) . ")", 1); $expectingOperator = false; } $stack->push("Brace", "("); } else { $output[] = ["type" => "Value", "value" => $matches[1], "reference" => NULL]; } } else { if (preg_match("/^" . CALCULATION_REGEXP_CELLREF . "\$/i", $val, $matches)) { $testPrevOp = $stack->last(1); if ($testPrevOp["value"] == ":") { if ($matches[2] == "") { $startCellRef = $output[count($output) - 1]["value"]; preg_match("/^" . CALCULATION_REGEXP_CELLREF . "\$/i", $startCellRef, $startMatches); if ("" < $startMatches[2]) { $val = $startMatches[2] . "!" . $val; } } else { return $this->raiseFormulaError("3D Range references are not yet supported"); } } $output[] = ["type" => "Cell Reference", "value" => $val, "reference" => $val]; } else { $testPrevOp = $stack->last(1); if ($testPrevOp["value"] == ":") { $startRowColRef = $output[count($output) - 1]["value"]; $rangeWS1 = ""; if (strpos("!", $startRowColRef) !== false) { list($rangeWS1, $startRowColRef) = explode("!", $startRowColRef); } if ($rangeWS1 != "") { $rangeWS1 .= "!"; } $rangeWS2 = $rangeWS1; if (strpos("!", $val) !== false) { list($rangeWS2, $val) = explode("!", $val); } if ($rangeWS2 != "") { $rangeWS2 .= "!"; } if (is_integer($startRowColRef) && ctype_digit($val) && $startRowColRef <= 1048576 && $val <= 1048576) { $endRowColRef = $pCellParent !== NULL ? $pCellParent->getHighestColumn() : "XFD"; $output[count($output) - 1]["value"] = $rangeWS1 . "A" . $startRowColRef; $val = $rangeWS2 . $endRowColRef . $val; } else { if (ctype_alpha($startRowColRef) && ctype_alpha($val) && strlen($startRowColRef) <= 3 && strlen($val) <= 3) { $endRowColRef = $pCellParent !== NULL ? $pCellParent->getHighestRow() : 1048576; $output[count($output) - 1]["value"] = $rangeWS1 . strtoupper($startRowColRef) . "1"; $val = $rangeWS2 . $val . $endRowColRef; } } } $localeConstant = false; if ($opCharacter == "\"") { $val = self::wrapResult(str_replace("\"\"", "\"", self::unwrapResult($val))); } else { if (is_numeric($val)) { if (strpos($val, ".") !== false || stripos($val, "e") !== false || PHP_INT_MAX < $val || $val < -1 * PHP_INT_MAX) { $val = (int) $val; } else { $val = (int) $val; } } else { if (isset(self::$excelConstants[trim(strtoupper($val))])) { $excelConstant = trim(strtoupper($val)); $val = self::$excelConstants[$excelConstant]; } else { if (($localeConstant = array_search(trim(strtoupper($val)), self::$localeBoolean)) !== false) { $val = self::$excelConstants[$localeConstant]; } } } } $details = ["type" => "Value", "value" => $val, "reference" => NULL]; if ($localeConstant) { $details["localeValue"] = $localeConstant; } $output[] = $details; } } $index += $length; } else { if ($opCharacter == "\$") { $index++; } else { if ($opCharacter == ")") { if ($expectingOperand) { $output[] = ["type" => "NULL Value", "value" => self::$excelConstants["NULL"], "reference" => NULL]; $expectingOperand = false; $expectingOperator = true; } else { return $this->raiseFormulaError("Formula Error: Unexpected ')'"); } } else { if (isset(self::$operators[$opCharacter]) && !$expectingOperator) { return $this->raiseFormulaError("Formula Error: Unexpected operator '" . $opCharacter . "'"); } return $this->raiseFormulaError("Formula Error: An unexpected error occured"); } } } } } } } } } } if ($index == strlen($formula)) { if (isset(self::$operators[$opCharacter]) && $opCharacter != "%") { return $this->raiseFormulaError("Formula Error: Operator '" . $opCharacter . "' has no operands"); } } else { while ($formula[$index] == "\n" || $formula[$index] == "\r") { $index++; } if ($formula[$index] == " ") { while ($formula[$index] == " ") { $index++; } if ($expectingOperator && preg_match("/^" . CALCULATION_REGEXP_CELLREF . ".*/Ui", substr($formula, $index), $match) && $output[count($output) - 1]["type"] == "Cell Reference") { while (0 < $stack->count() && ($o2 = $stack->last()) && isset(self::$operators[$o2["value"]]) && (self::$operatorAssociativity[$opCharacter] ? self::$operatorPrecedence[$opCharacter] < self::$operatorPrecedence[$o2["value"]] : self::$operatorPrecedence[$opCharacter] <= self::$operatorPrecedence[$o2["value"]])) { $output[] = $stack->pop(); } $stack->push("Binary Operator", "|"); $expectingOperator = false; } } } while (($op = $stack->pop()) !== NULL) { } return $output; } if (is_array($op) && $op["value"] == "(" || $op === "(") { return $this->raiseFormulaError("Formula Error: Expecting ')'"); } $output[] = $op; } private static function dataTestReference(&$operandData) { $operand = $operandData["value"]; if ($operandData["reference"] === NULL && is_array($operand)) { $rKeys = array_keys($operand); $rowKey = array_shift($rKeys); $cKeys = array_keys(array_keys($operand[$rowKey])); $colKey = array_shift($cKeys); if (ctype_upper($colKey)) { $operandData["reference"] = $colKey . $rowKey; } } return $operand; } private function processTokenStack($tokens, $cellID = NULL, PHPExcel_Cell $pCell = NULL) { while (!$tokens) { $pCellWorksheet = $pCell !== NULL ? $pCell->getWorksheet() : NULL; $pCellParent = $pCell !== NULL ? $pCell->getParent() : NULL; $stack = new PHPExcel_Calculation_Token_Stack(); foreach ($tokens as $tokenData) { $token = $tokenData["value"]; if (isset(self::$binaryOperators[$token])) { if (($operand2Data = $stack->pop()) === NULL) { return $this->raiseFormulaError("Internal error - Operand value missing from stack"); } if (($operand1Data = $stack->pop()) === NULL) { return $this->raiseFormulaError("Internal error - Operand value missing from stack"); } $operand1 = self::dataTestReference($operand1Data); $operand2 = self::dataTestReference($operand2Data); if ($token == ":") { $this->_debugLog->writeDebugLog("Evaluating Range ", $this->showValue($operand1Data["reference"]), " ", $token, " ", $this->showValue($operand2Data["reference"])); } else { $this->_debugLog->writeDebugLog("Evaluating ", $this->showValue($operand1), " ", $token, " ", $this->showValue($operand2)); } switch ($token) { case ">": case "<": case ">=": case "<=": case "=": case "<>": $this->executeBinaryComparisonOperation($cellID, $operand1, $operand2, $token, $stack); break; case ":": $sheet1 = $sheet2 = ""; if (strpos($operand1Data["reference"], "!") !== false) { list($sheet1, $operand1Data["reference"]) = explode("!", $operand1Data["reference"]); } else { $sheet1 = $pCellParent !== NULL ? $pCellWorksheet->getTitle() : ""; } if (strpos($operand2Data["reference"], "!") !== false) { list($sheet2, $operand2Data["reference"]) = explode("!", $operand2Data["reference"]); } else { $sheet2 = $sheet1; } if ($sheet1 == $sheet2) { if ($operand1Data["reference"] === NULL) { if (trim($operand1Data["value"]) != "" && is_numeric($operand1Data["value"])) { $operand1Data["reference"] = $pCell->getColumn() . $operand1Data["value"]; } else { if (trim($operand1Data["reference"]) == "") { $operand1Data["reference"] = $pCell->getCoordinate(); } else { $operand1Data["reference"] = $operand1Data["value"] . $pCell->getRow(); } } } if ($operand2Data["reference"] === NULL) { if (trim($operand2Data["value"]) != "" && is_numeric($operand2Data["value"])) { $operand2Data["reference"] = $pCell->getColumn() . $operand2Data["value"]; } else { if (trim($operand2Data["reference"]) == "") { $operand2Data["reference"] = $pCell->getCoordinate(); } else { $operand2Data["reference"] = $operand2Data["value"] . $pCell->getRow(); } } } $oData = array_merge(explode(":", $operand1Data["reference"]), explode(":", $operand2Data["reference"])); $oCol = $oRow = []; foreach ($oData as $oDatum) { $oCR = PHPExcel_Cell::coordinateFromString($oDatum); $oCol[] = PHPExcel_Cell::columnIndexFromString($oCR[0]) - 1; $oRow[] = $oCR[1]; } $cellRef = PHPExcel_Cell::stringFromColumnIndex(min($oCol)) . min($oRow) . ":" . PHPExcel_Cell::stringFromColumnIndex(max($oCol)) . max($oRow); if ($pCellParent !== NULL) { $cellValue = $this->extractCellRange($cellRef, $this->workbook->getSheetByName($sheet1), false); $stack->push("Cell Reference", $cellValue, $cellRef); } else { return $this->raiseFormulaError("Unable to access Cell Reference"); } } else { $stack->push("Error", PHPExcel_Calculation_Functions::REF(), NULL); } break; case "+": $this->executeNumericBinaryOperation($cellID, $operand1, $operand2, $token, "plusEquals", $stack); break; case "-": $this->executeNumericBinaryOperation($cellID, $operand1, $operand2, $token, "minusEquals", $stack); break; case "*": $this->executeNumericBinaryOperation($cellID, $operand1, $operand2, $token, "arrayTimesEquals", $stack); break; case "/": $this->executeNumericBinaryOperation($cellID, $operand1, $operand2, $token, "arrayRightDivide", $stack); break; case "^": $this->executeNumericBinaryOperation($cellID, $operand1, $operand2, $token, "power", $stack); break; case "&": if (is_bool($operand1)) { $operand1 = $operand1 ? self::$localeBoolean["TRUE"] : self::$localeBoolean["FALSE"]; } if (is_bool($operand2)) { $operand2 = $operand2 ? self::$localeBoolean["TRUE"] : self::$localeBoolean["FALSE"]; } if (is_array($operand1) || is_array($operand2)) { self::checkMatrixOperands($operand1, $operand2, 2); try { $matrix = new PHPExcel_Shared_JAMA_Matrix($operand1); $matrixResult = $matrix->concat($operand2); $result = $matrixResult->getArray(); } catch (PHPExcel_Exception $ex) { $this->_debugLog->writeDebugLog("JAMA Matrix Exception: ", $ex->getMessage()); $result = "#VALUE!"; } } else { $result = "\"" . str_replace("\"\"", "\"", self::unwrapResult($operand1, "\"") . self::unwrapResult($operand2, "\"")) . "\""; } $this->_debugLog->writeDebugLog("Evaluation Result is ", $this->showTypeDetails($result)); $stack->push("Value", $result); break; case "|": $rowIntersect = array_intersect_key($operand1, $operand2); $cellIntersect = $oCol = $oRow = []; foreach (array_keys($rowIntersect) as $row) { $oRow[] = $row; foreach ($rowIntersect[$row] as $col => $data) { $oCol[] = PHPExcel_Cell::columnIndexFromString($col) - 1; $cellIntersect[$row] = array_intersect_key($operand1[$row], $operand2[$row]); } } $cellRef = PHPExcel_Cell::stringFromColumnIndex(min($oCol)) . min($oRow) . ":" . PHPExcel_Cell::stringFromColumnIndex(max($oCol)) . max($oRow); $this->_debugLog->writeDebugLog("Evaluation Result is ", $this->showTypeDetails($cellIntersect)); $stack->push("Value", $cellIntersect, $cellRef); break; } } else { if ($token === "~" || $token === "%") { if (($arg = $stack->pop()) === NULL) { return $this->raiseFormulaError("Internal error - Operand value missing from stack"); } $arg = $arg["value"]; if ($token === "~") { $this->_debugLog->writeDebugLog("Evaluating Negation of ", $this->showValue($arg)); $multiplier = -1; } else { $this->_debugLog->writeDebugLog("Evaluating Percentile of ", $this->showValue($arg)); $multiplier = 0; } if (is_array($arg)) { self::checkMatrixOperands($arg, $multiplier, 2); try { $matrix1 = new PHPExcel_Shared_JAMA_Matrix($arg); $matrixResult = $matrix1->arrayTimesEquals($multiplier); $result = $matrixResult->getArray(); } catch (PHPExcel_Exception $ex) { $this->_debugLog->writeDebugLog("JAMA Matrix Exception: ", $ex->getMessage()); $result = "#VALUE!"; $this->_debugLog->writeDebugLog("Evaluation Result is ", $this->showTypeDetails($result)); $stack->push("Value", $result); } } else { $this->executeNumericBinaryOperation($cellID, $multiplier, $arg, "*", "arrayTimesEquals", $stack); } } else { if (preg_match("/^" . CALCULATION_REGEXP_CELLREF . "\$/i", $token, $matches)) { $cellRef = NULL; if (isset($matches[8])) { if ($pCell === NULL) { $cellValue = PHPExcel_Calculation_Functions::REF(); } else { $cellRef = $matches[6] . $matches[7] . ":" . $matches[9] . $matches[10]; if ("" < $matches[2]) { $matches[2] = trim($matches[2], "\"'"); if (strpos($matches[2], "[") !== false || strpos($matches[2], "]") !== false) { return $this->raiseFormulaError("Unable to access External Workbook"); } $matches[2] = trim($matches[2], "\"'"); $this->_debugLog->writeDebugLog("Evaluating Cell Range ", $cellRef, " in worksheet ", $matches[2]); if ($pCellParent !== NULL) { $cellValue = $this->extractCellRange($cellRef, $this->workbook->getSheetByName($matches[2]), false); $this->_debugLog->writeDebugLog("Evaluation Result for cells ", $cellRef, " in worksheet ", $matches[2], " is ", $this->showTypeDetails($cellValue)); } else { return $this->raiseFormulaError("Unable to access Cell Reference"); } } else { $this->_debugLog->writeDebugLog("Evaluating Cell Range ", $cellRef, " in current worksheet"); if ($pCellParent !== NULL) { $cellValue = $this->extractCellRange($cellRef, $pCellWorksheet, false); $this->_debugLog->writeDebugLog("Evaluation Result for cells ", $cellRef, " is ", $this->showTypeDetails($cellValue)); } else { return $this->raiseFormulaError("Unable to access Cell Reference"); } } } } else { if ($pCell === NULL) { $cellValue = PHPExcel_Calculation_Functions::REF(); } else { $cellRef = $matches[6] . $matches[7]; if ("" < $matches[2]) { $matches[2] = trim($matches[2], "\"'"); if (strpos($matches[2], "[") !== false || strpos($matches[2], "]") !== false) { return $this->raiseFormulaError("Unable to access External Workbook"); } $this->_debugLog->writeDebugLog("Evaluating Cell ", $cellRef, " in worksheet ", $matches[2]); if ($pCellParent !== NULL) { $cellSheet = $this->workbook->getSheetByName($matches[2]); if ($cellSheet && $cellSheet->cellExists($cellRef)) { $cellValue = $this->extractCellRange($cellRef, $this->workbook->getSheetByName($matches[2]), false); $pCell->attach($pCellParent); } else { $cellValue = NULL; } $this->_debugLog->writeDebugLog("Evaluation Result for cell ", $cellRef, " in worksheet ", $matches[2], " is ", $this->showTypeDetails($cellValue)); } else { return $this->raiseFormulaError("Unable to access Cell Reference"); } } else { $this->_debugLog->writeDebugLog("Evaluating Cell ", $cellRef, " in current worksheet"); if ($pCellParent->isDataSet($cellRef)) { $cellValue = $this->extractCellRange($cellRef, $pCellWorksheet, false); $pCell->attach($pCellParent); } else { $cellValue = NULL; } $this->_debugLog->writeDebugLog("Evaluation Result for cell ", $cellRef, " is ", $this->showTypeDetails($cellValue)); } } } $stack->push("Value", $cellValue, $cellRef); } else { if (preg_match("/^@?([A-Z][A-Z0-9\\.]*)[\\s]*\\(\$/i", $token, $matches)) { $functionName = $matches[1]; $argCount = $stack->pop(); $argCount = $argCount["value"]; if ($functionName != "MKMATRIX") { $this->_debugLog->writeDebugLog("Evaluating Function ", self::localeFunc($functionName), "() with ", $argCount == 0 ? "no" : $argCount, " argument", $argCount == 1 ? "" : "s"); } if (isset(self::$PHPExcelFunctions[$functionName]) || isset(self::$controlFunctions[$functionName])) { if (isset(self::$PHPExcelFunctions[$functionName])) { $functionCall = self::$PHPExcelFunctions[$functionName]["functionCall"]; $passByReference = isset(self::$PHPExcelFunctions[$functionName]["passByReference"]); $passCellReference = isset(self::$PHPExcelFunctions[$functionName]["passCellReference"]); } else { if (isset(self::$controlFunctions[$functionName])) { $functionCall = self::$controlFunctions[$functionName]["functionCall"]; $passByReference = isset(self::$controlFunctions[$functionName]["passByReference"]); $passCellReference = isset(self::$controlFunctions[$functionName]["passCellReference"]); } } $args = $argArrayVals = []; for ($i = 0; $i < $argCount; $i++) { $arg = $stack->pop(); $a = $argCount - $i - 1; if ($passByReference && isset(self::$PHPExcelFunctions[$functionName]["passByReference"][$a]) && self::$PHPExcelFunctions[$functionName]["passByReference"][$a]) { if ($arg["reference"] === NULL) { $args[] = $cellID; if ($functionName != "MKMATRIX") { $argArrayVals[] = $this->showValue($cellID); } } else { $args[] = $arg["reference"]; if ($functionName != "MKMATRIX") { $argArrayVals[] = $this->showValue($arg["reference"]); } } } else { $args[] = self::unwrapResult($arg["value"]); if ($functionName != "MKMATRIX") { $argArrayVals[] = $this->showValue($arg["value"]); } } } krsort($args); if ($passByReference && $argCount == 0) { $args[] = $cellID; $argArrayVals[] = $this->showValue($cellID); } if ($functionName != "MKMATRIX" && $this->_debugLog->getWriteDebugLog()) { krsort($argArrayVals); $this->_debugLog->writeDebugLog("Evaluating ", self::localeFunc($functionName), "( ", implode(self::$localeArgumentSeparator . " ", PHPExcel_Calculation_Functions::flattenArray($argArrayVals)), " )"); } if ($passCellReference) { $args[] = $pCell; } if (strpos($functionCall, "::") !== false) { $result = call_user_func_array(explode("::", $functionCall), $args); } else { $arg = PHPExcel_Calculation_Functions::flattenSingleValue($arg); unset($arg); $result = call_user_func_array($functionCall, $args); } if ($functionName != "MKMATRIX") { $this->_debugLog->writeDebugLog("Evaluation Result for ", self::localeFunc($functionName), "() function call is ", $this->showTypeDetails($result)); } $stack->push("Value", self::wrapResult($result)); } } else { if (isset(self::$excelConstants[strtoupper($token)])) { $excelConstant = strtoupper($token); $stack->push("Constant Value", self::$excelConstants[$excelConstant]); $this->_debugLog->writeDebugLog("Evaluating Constant ", $excelConstant, " as ", $this->showTypeDetails(self::$excelConstants[$excelConstant])); } else { if (is_numeric($token) || $token === NULL || is_bool($token) || $token == "" || $token[0] == "\"" || $token[0] == "#") { $stack->push("Value", $token); } else { if (preg_match("/^" . CALCULATION_REGEXP_NAMEDRANGE . "\$/i", $token, $matches)) { $namedRange = $matches[6]; $this->_debugLog->writeDebugLog("Evaluating Named Range ", $namedRange); $cellValue = $this->extractNamedRange($namedRange, NULL !== $pCell ? $pCellWorksheet : NULL, false); $pCell->attach($pCellParent); $this->_debugLog->writeDebugLog("Evaluation Result for named range ", $namedRange, " is ", $this->showTypeDetails($cellValue)); $stack->push("Named Range", $cellValue, $namedRange); } else { return $this->raiseFormulaError("undefined variable '" . $token . "'"); } } } } } } } } if ($stack->count() != 1) { return $this->raiseFormulaError("internal error"); } $output = $stack->pop(); $output = $output["value"]; return $output; } return false; } private function validateBinaryOperand($cellID, &$operand, &$stack) { if (is_array($operand) && count($operand, COUNT_RECURSIVE) - count($operand) == 1) { do { $operand = array_pop($operand); } while (!is_array($operand)); } if (is_string($operand)) { if ("" < $operand && $operand[0] == "\"") { $operand = self::unwrapResult($operand); } if (!is_numeric($operand)) { if ("" < $operand && $operand[0] == "#") { $stack->push("Value", $operand); $this->_debugLog->writeDebugLog("Evaluation Result is ", $this->showTypeDetails($operand)); return false; } if (!PHPExcel_Shared_String::convertToNumberIfFraction($operand)) { $stack->push("Value", "#VALUE!"); $this->_debugLog->writeDebugLog("Evaluation Result is a ", $this->showTypeDetails("#VALUE!")); return false; } } } return true; } private function executeBinaryComparisonOperation($cellID, $operand1, $operand2, $operation, &$stack, $recursingArrays = false) { if (is_array($operand1) || is_array($operand2)) { $result = []; if (is_array($operand1) && !is_array($operand2)) { foreach ($operand1 as $x => $operandData) { $this->_debugLog->writeDebugLog("Evaluating Comparison ", $this->showValue($operandData), " ", $operation, " ", $this->showValue($operand2)); $this->executeBinaryComparisonOperation($cellID, $operandData, $operand2, $operation, $stack); $r = $stack->pop(); $result[$x] = $r["value"]; } } else { if (!is_array($operand1) && is_array($operand2)) { foreach ($operand2 as $x => $operandData) { $this->_debugLog->writeDebugLog("Evaluating Comparison ", $this->showValue($operand1), " ", $operation, " ", $this->showValue($operandData)); $this->executeBinaryComparisonOperation($cellID, $operand1, $operandData, $operation, $stack); $r = $stack->pop(); $result[$x] = $r["value"]; } } else { if (!$recursingArrays) { self::checkMatrixOperands($operand1, $operand2, 2); } foreach ($operand1 as $x => $operandData) { $this->_debugLog->writeDebugLog("Evaluating Comparison ", $this->showValue($operandData), " ", $operation, " ", $this->showValue($operand2[$x])); $this->executeBinaryComparisonOperation($cellID, $operandData, $operand2[$x], $operation, $stack, true); $r = $stack->pop(); $result[$x] = $r["value"]; } } } $this->_debugLog->writeDebugLog("Comparison Evaluation Result is ", $this->showTypeDetails($result)); $stack->push("Array", $result); return true; } else { if (is_string($operand1) && "" < $operand1 && $operand1[0] == "\"") { $operand1 = self::unwrapResult($operand1); } if (is_string($operand2) && "" < $operand2 && $operand2[0] == "\"") { $operand2 = self::unwrapResult($operand2); } if (PHPExcel_Calculation_Functions::getCompatibilityMode() != PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE) { if (is_string($operand1)) { $operand1 = strtoupper($operand1); } if (is_string($operand2)) { $operand2 = strtoupper($operand2); } } $useLowercaseFirstComparison = is_string($operand1) && is_string($operand2) && PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE; switch ($operation) { case ">": if ($useLowercaseFirstComparison) { $result = 0 < $this->strcmpLowercaseFirst($operand1, $operand2); } else { $result = $operand2 < $operand1; } break; case "<": if ($useLowercaseFirstComparison) { $result = $this->strcmpLowercaseFirst($operand1, $operand2) < 0; } else { $result = $operand1 < $operand2; } break; case "=": if (is_numeric($operand1) && is_numeric($operand2)) { $result = abs($operand1 - $operand2) < $this->delta; } else { $result = strcmp($operand1, $operand2) == 0; } break; case ">=": if (is_numeric($operand1) && is_numeric($operand2)) { $result = abs($operand1 - $operand2) < $this->delta || $operand2 < $operand1; } else { if ($useLowercaseFirstComparison) { $result = 0 <= $this->strcmpLowercaseFirst($operand1, $operand2); } else { $result = 0 <= strcmp($operand1, $operand2); } } break; case "<=": if (is_numeric($operand1) && is_numeric($operand2)) { $result = abs($operand1 - $operand2) < $this->delta || $operand1 < $operand2; } else { if ($useLowercaseFirstComparison) { $result = $this->strcmpLowercaseFirst($operand1, $operand2) <= 0; } else { $result = strcmp($operand1, $operand2) <= 0; } } break; case "<>": if (is_numeric($operand1) && is_numeric($operand2)) { $result = 0 < abs($operand1 - $operand2); } else { $result = strcmp($operand1, $operand2) != 0; } break; default: $this->_debugLog->writeDebugLog("Evaluation Result is ", $this->showTypeDetails($result)); $stack->push("Value", $result); return true; } } } private function strcmpLowercaseFirst($str1, $str2) { $inversedStr1 = PHPExcel_Shared_String::StrCaseReverse($str1); $inversedStr2 = PHPExcel_Shared_String::StrCaseReverse($str2); return strcmp($inversedStr1, $inversedStr2); } private function executeNumericBinaryOperation($cellID, $operand1, $operand2, $operation, $matrixFunction, &$stack) { while (!$this->validateBinaryOperand($cellID, $operand1, $stack)) { if (!$this->validateBinaryOperand($cellID, $operand2, $stack)) { return false; } if (is_array($operand1) || is_array($operand2)) { self::checkMatrixOperands($operand1, $operand2, 2); try { $matrix = new PHPExcel_Shared_JAMA_Matrix($operand1); $matrixResult = $matrix->{$matrixFunction}($operand2); $result = $matrixResult->getArray(); } catch (PHPExcel_Exception $ex) { $this->_debugLog->writeDebugLog("JAMA Matrix Exception: ", $ex->getMessage()); $result = "#VALUE!"; } } else { if (PHPExcel_Calculation_Functions::getCompatibilityMode() != PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE && (is_string($operand1) && !is_numeric($operand1) && 0 < strlen($operand1) || is_string($operand2) && !is_numeric($operand2) && 0 < strlen($operand2))) { $result = PHPExcel_Calculation_Functions::VALUE(); } else { switch ($operation) { case "+": $result = $operand1 + $operand2; break; case "-": $result = $operand1 - $operand2; break; case "*": $result = $operand1 * $operand2; break; case "/": if ($operand2 == 0) { $stack->push("Value", "#DIV/0!"); $this->_debugLog->writeDebugLog("Evaluation Result is ", $this->showTypeDetails("#DIV/0!")); return false; } $result = $operand1 / $operand2; break; case "^": $result = pow($operand1, $operand2); break; } } } $this->_debugLog->writeDebugLog("Evaluation Result is ", $this->showTypeDetails($result)); $stack->push("Value", $result); return true; } return false; } protected function raiseFormulaError($errorMessage) { $this->formulaError = $errorMessage; $this->cyclicReferenceStack->clear(); if (!$this->suppressFormulaErrors) { throw new PHPExcel_Calculation_Exception($errorMessage); } trigger_error($errorMessage, 256); } public function extractCellRange(&$pRange = "A1", PHPExcel_Worksheet $pSheet = NULL, $resetLog = true) { $returnValue = []; if ($pSheet !== NULL) { $pSheetName = $pSheet->getTitle(); if (strpos($pRange, "!") !== false) { list($pSheetName, $pRange) = PHPExcel_Worksheet::extractSheetTitle($pRange, true); $pSheet = $this->workbook->getSheetByName($pSheetName); } $aReferences = PHPExcel_Cell::extractAllCellReferencesInRange($pRange); $pRange = $pSheetName . "!" . $pRange; if (!isset($aReferences[1])) { sscanf($aReferences[0], "%[A-Z]%d", $currentCol, $currentRow); $cellValue = NULL; if ($pSheet->cellExists($aReferences[0])) { $returnValue[$currentRow][$currentCol] = $pSheet->getCell($aReferences[0])->getCalculatedValue($resetLog); } else { $returnValue[$currentRow][$currentCol] = NULL; } } else { foreach ($aReferences as $reference) { sscanf($reference, "%[A-Z]%d", $currentCol, $currentRow); $cellValue = NULL; if ($pSheet->cellExists($reference)) { $returnValue[$currentRow][$currentCol] = $pSheet->getCell($reference)->getCalculatedValue($resetLog); } else { $returnValue[$currentRow][$currentCol] = NULL; } } } } return $returnValue; } public function extractNamedRange(&$pRange = "A1", PHPExcel_Worksheet $pSheet = NULL, $resetLog = true) { $returnValue = []; if ($pSheet !== NULL) { $pSheetName = $pSheet->getTitle(); if (strpos($pRange, "!") !== false) { list($pSheetName, $pRange) = PHPExcel_Worksheet::extractSheetTitle($pRange, true); $pSheet = $this->workbook->getSheetByName($pSheetName); } $namedRange = PHPExcel_NamedRange::resolveRange($pRange, $pSheet); if ($namedRange !== NULL) { $pSheet = $namedRange->getWorksheet(); $pRange = $namedRange->getRange(); $splitRange = PHPExcel_Cell::splitRange($pRange); if (ctype_alpha($splitRange[0][0])) { $pRange = $splitRange[0][0] . "1:" . $splitRange[0][1] . $namedRange->getWorksheet()->getHighestRow(); } else { if (ctype_digit($splitRange[0][0])) { $pRange = "A" . $splitRange[0][0] . ":" . $namedRange->getWorksheet()->getHighestColumn() . $splitRange[0][1]; } } $aReferences = PHPExcel_Cell::extractAllCellReferencesInRange($pRange); if (!isset($aReferences[1])) { list($currentCol, $currentRow) = PHPExcel_Cell::coordinateFromString($aReferences[0]); $cellValue = NULL; if ($pSheet->cellExists($aReferences[0])) { $returnValue[$currentRow][$currentCol] = $pSheet->getCell($aReferences[0])->getCalculatedValue($resetLog); } else { $returnValue[$currentRow][$currentCol] = NULL; } } else { foreach ($aReferences as $reference) { list($currentCol, $currentRow) = PHPExcel_Cell::coordinateFromString($reference); $cellValue = NULL; if ($pSheet->cellExists($reference)) { $returnValue[$currentRow][$currentCol] = $pSheet->getCell($reference)->getCalculatedValue($resetLog); } else { $returnValue[$currentRow][$currentCol] = NULL; } } } } else { return PHPExcel_Calculation_Functions::REF(); } } return $returnValue; } public function isImplemented($pFunction = "") { $pFunction = strtoupper($pFunction); if (isset(self::$PHPExcelFunctions[$pFunction])) { return self::$PHPExcelFunctions[$pFunction]["functionCall"] != "PHPExcel_Calculation_Functions::DUMMY"; } return false; } public function listFunctions() { $returnValue = []; foreach (self::$PHPExcelFunctions as $functionName => $function) { if ($function["functionCall"] != "PHPExcel_Calculation_Functions::DUMMY") { $returnValue[$functionName] = new PHPExcel_Calculation_Function($function["category"], $functionName, $function["functionCall"]); } } return $returnValue; } public function listAllFunctionNames() { return array_keys(self::$PHPExcelFunctions); } public function listFunctionNames() { $returnValue = []; foreach (self::$PHPExcelFunctions as $functionName => $function) { if ($function["functionCall"] != "PHPExcel_Calculation_Functions::DUMMY") { $returnValue[] = $functionName; } } return $returnValue; } } ?>
💾 Kaydet
İptal
📝 Yeniden Adlandır
İptal
Kaydet
🔐 Dosya İzinleri (chmod)
İzin Değeri:
Hızlı Seçim:
777
755
644
600
777
= Herkes okur/yazar/çalıştırır
755
= Sahip tam, diğerleri okur/çalıştırır
644
= Sahip okur/yazar, diğerleri okur
600
= Sadece sahip okur/yazar
İptal
Uygula