123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622 |
- <?php
- class PHPExcel_Calculation_FormulaParser
- {
-
- const QUOTE_DOUBLE = '"';
- const QUOTE_SINGLE = '\'';
- const BRACKET_CLOSE = ']';
- const BRACKET_OPEN = '[';
- const BRACE_OPEN = '{';
- const BRACE_CLOSE = '}';
- const PAREN_OPEN = '(';
- const PAREN_CLOSE = ')';
- const SEMICOLON = ';';
- const WHITESPACE = ' ';
- const COMMA = ',';
- const ERROR_START = '#';
- const OPERATORS_SN = "+-";
- const OPERATORS_INFIX = "+-*/^&=><";
- const OPERATORS_POSTFIX = "%";
-
- private $formula;
-
- private $tokens = array();
-
- public function __construct($pFormula = '')
- {
-
- if (is_null($pFormula)) {
- throw new PHPExcel_Calculation_Exception("Invalid parameter passed: formula");
- }
-
- $this->formula = trim($pFormula);
-
- $this->parseToTokens();
- }
-
- public function getFormula()
- {
- return $this->formula;
- }
-
- public function getToken($pId = 0)
- {
- if (isset($this->tokens[$pId])) {
- return $this->tokens[$pId];
- } else {
- throw new PHPExcel_Calculation_Exception("Token with id $pId does not exist.");
- }
- }
-
- public function getTokenCount()
- {
- return count($this->tokens);
- }
-
- public function getTokens()
- {
- return $this->tokens;
- }
-
- private function parseToTokens()
- {
-
-
-
- $formulaLength = strlen($this->formula);
- if ($formulaLength < 2 || $this->formula{0} != '=') {
- return;
- }
-
- $tokens1 = $tokens2 = $stack = array();
- $inString = $inPath = $inRange = $inError = false;
- $token = $previousToken = $nextToken = null;
- $index = 1;
- $value = '';
- $ERRORS = array("#NULL!", "#DIV/0!", "#VALUE!", "#REF!", "#NAME?", "#NUM!", "#N/A");
- $COMPARATORS_MULTI = array(">=", "<=", "<>");
- while ($index < $formulaLength) {
-
-
-
-
- if ($inString) {
- if ($this->formula{$index} == PHPExcel_Calculation_FormulaParser::QUOTE_DOUBLE) {
- if ((($index + 2) <= $formulaLength) && ($this->formula{$index + 1} == PHPExcel_Calculation_FormulaParser::QUOTE_DOUBLE)) {
- $value .= PHPExcel_Calculation_FormulaParser::QUOTE_DOUBLE;
- ++$index;
- } else {
- $inString = false;
- $tokens1[] = new PHPExcel_Calculation_FormulaToken($value, PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERAND, PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_TEXT);
- $value = "";
- }
- } else {
- $value .= $this->formula{$index};
- }
- ++$index;
- continue;
- }
-
-
-
- if ($inPath) {
- if ($this->formula{$index} == PHPExcel_Calculation_FormulaParser::QUOTE_SINGLE) {
- if ((($index + 2) <= $formulaLength) && ($this->formula{$index + 1} == PHPExcel_Calculation_FormulaParser::QUOTE_SINGLE)) {
- $value .= PHPExcel_Calculation_FormulaParser::QUOTE_SINGLE;
- ++$index;
- } else {
- $inPath = false;
- }
- } else {
- $value .= $this->formula{$index};
- }
- ++$index;
- continue;
- }
-
-
-
- if ($inRange) {
- if ($this->formula{$index} == PHPExcel_Calculation_FormulaParser::BRACKET_CLOSE) {
- $inRange = false;
- }
- $value .= $this->formula{$index};
- ++$index;
- continue;
- }
-
-
- if ($inError) {
- $value .= $this->formula{$index};
- ++$index;
- if (in_array($value, $ERRORS)) {
- $inError = false;
- $tokens1[] = new PHPExcel_Calculation_FormulaToken($value, PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERAND, PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_ERROR);
- $value = "";
- }
- continue;
- }
-
- if (strpos(PHPExcel_Calculation_FormulaParser::OPERATORS_SN, $this->formula{$index}) !== false) {
- if (strlen($value) > 1) {
- if (preg_match("/^[1-9]{1}(\.[0-9]+)?E{1}$/", $this->formula{$index}) != 0) {
- $value .= $this->formula{$index};
- ++$index;
- continue;
- }
- }
- }
-
-
- if ($this->formula{$index} == PHPExcel_Calculation_FormulaParser::QUOTE_DOUBLE) {
- if (strlen($value > 0)) {
-
- $tokens1[] = new PHPExcel_Calculation_FormulaToken($value, PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_UNKNOWN);
- $value = "";
- }
- $inString = true;
- ++$index;
- continue;
- }
- if ($this->formula{$index} == PHPExcel_Calculation_FormulaParser::QUOTE_SINGLE) {
- if (strlen($value) > 0) {
-
- $tokens1[] = new PHPExcel_Calculation_FormulaToken($value, PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_UNKNOWN);
- $value = "";
- }
- $inPath = true;
- ++$index;
- continue;
- }
- if ($this->formula{$index} == PHPExcel_Calculation_FormulaParser::BRACKET_OPEN) {
- $inRange = true;
- $value .= PHPExcel_Calculation_FormulaParser::BRACKET_OPEN;
- ++$index;
- continue;
- }
- if ($this->formula{$index} == PHPExcel_Calculation_FormulaParser::ERROR_START) {
- if (strlen($value) > 0) {
-
- $tokens1[] = new PHPExcel_Calculation_FormulaToken($value, PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_UNKNOWN);
- $value = "";
- }
- $inError = true;
- $value .= PHPExcel_Calculation_FormulaParser::ERROR_START;
- ++$index;
- continue;
- }
-
- if ($this->formula{$index} == PHPExcel_Calculation_FormulaParser::BRACE_OPEN) {
- if (strlen($value) > 0) {
-
- $tokens1[] = new PHPExcel_Calculation_FormulaToken($value, PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_UNKNOWN);
- $value = "";
- }
- $tmp = new PHPExcel_Calculation_FormulaToken("ARRAY", PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_FUNCTION, PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_START);
- $tokens1[] = $tmp;
- $stack[] = clone $tmp;
- $tmp = new PHPExcel_Calculation_FormulaToken("ARRAYROW", PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_FUNCTION, PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_START);
- $tokens1[] = $tmp;
- $stack[] = clone $tmp;
- ++$index;
- continue;
- }
- if ($this->formula{$index} == PHPExcel_Calculation_FormulaParser::SEMICOLON) {
- if (strlen($value) > 0) {
- $tokens1[] = new PHPExcel_Calculation_FormulaToken($value, PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERAND);
- $value = "";
- }
- $tmp = array_pop($stack);
- $tmp->setValue("");
- $tmp->setTokenSubType(PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_STOP);
- $tokens1[] = $tmp;
- $tmp = new PHPExcel_Calculation_FormulaToken(",", PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_ARGUMENT);
- $tokens1[] = $tmp;
- $tmp = new PHPExcel_Calculation_FormulaToken("ARRAYROW", PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_FUNCTION, PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_START);
- $tokens1[] = $tmp;
- $stack[] = clone $tmp;
- ++$index;
- continue;
- }
- if ($this->formula{$index} == PHPExcel_Calculation_FormulaParser::BRACE_CLOSE) {
- if (strlen($value) > 0) {
- $tokens1[] = new PHPExcel_Calculation_FormulaToken($value, PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERAND);
- $value = "";
- }
- $tmp = array_pop($stack);
- $tmp->setValue("");
- $tmp->setTokenSubType(PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_STOP);
- $tokens1[] = $tmp;
- $tmp = array_pop($stack);
- $tmp->setValue("");
- $tmp->setTokenSubType(PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_STOP);
- $tokens1[] = $tmp;
- ++$index;
- continue;
- }
-
- if ($this->formula{$index} == PHPExcel_Calculation_FormulaParser::WHITESPACE) {
- if (strlen($value) > 0) {
- $tokens1[] = new PHPExcel_Calculation_FormulaToken($value, PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERAND);
- $value = "";
- }
- $tokens1[] = new PHPExcel_Calculation_FormulaToken("", PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_WHITESPACE);
- ++$index;
- while (($this->formula{$index} == PHPExcel_Calculation_FormulaParser::WHITESPACE) && ($index < $formulaLength)) {
- ++$index;
- }
- continue;
- }
-
- if (($index + 2) <= $formulaLength) {
- if (in_array(substr($this->formula, $index, 2), $COMPARATORS_MULTI)) {
- if (strlen($value) > 0) {
- $tokens1[] = new PHPExcel_Calculation_FormulaToken($value, PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERAND);
- $value = "";
- }
- $tokens1[] = new PHPExcel_Calculation_FormulaToken(substr($this->formula, $index, 2), PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERATORINFIX, PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_LOGICAL);
- $index += 2;
- continue;
- }
- }
-
- if (strpos(PHPExcel_Calculation_FormulaParser::OPERATORS_INFIX, $this->formula{$index}) !== false) {
- if (strlen($value) > 0) {
- $tokens1[] =new PHPExcel_Calculation_FormulaToken($value, PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERAND);
- $value = "";
- }
- $tokens1[] = new PHPExcel_Calculation_FormulaToken($this->formula{$index}, PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERATORINFIX);
- ++$index;
- continue;
- }
-
- if (strpos(PHPExcel_Calculation_FormulaParser::OPERATORS_POSTFIX, $this->formula{$index}) !== false) {
- if (strlen($value) > 0) {
- $tokens1[] = new PHPExcel_Calculation_FormulaToken($value, PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERAND);
- $value = "";
- }
- $tokens1[] = new PHPExcel_Calculation_FormulaToken($this->formula{$index}, PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERATORPOSTFIX);
- ++$index;
- continue;
- }
-
- if ($this->formula{$index} == PHPExcel_Calculation_FormulaParser::PAREN_OPEN) {
- if (strlen($value) > 0) {
- $tmp = new PHPExcel_Calculation_FormulaToken($value, PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_FUNCTION, PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_START);
- $tokens1[] = $tmp;
- $stack[] = clone $tmp;
- $value = "";
- } else {
- $tmp = new PHPExcel_Calculation_FormulaToken("", PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_SUBEXPRESSION, PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_START);
- $tokens1[] = $tmp;
- $stack[] = clone $tmp;
- }
- ++$index;
- continue;
- }
-
- if ($this->formula{$index} == PHPExcel_Calculation_FormulaParser::COMMA) {
- if (strlen($value) > 0) {
- $tokens1[] = new PHPExcel_Calculation_FormulaToken($value, PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERAND);
- $value = "";
- }
- $tmp = array_pop($stack);
- $tmp->setValue("");
- $tmp->setTokenSubType(PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_STOP);
- $stack[] = $tmp;
- if ($tmp->getTokenType() == PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_FUNCTION) {
- $tokens1[] = new PHPExcel_Calculation_FormulaToken(",", PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERATORINFIX, PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_UNION);
- } else {
- $tokens1[] = new PHPExcel_Calculation_FormulaToken(",", PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_ARGUMENT);
- }
- ++$index;
- continue;
- }
-
- if ($this->formula{$index} == PHPExcel_Calculation_FormulaParser::PAREN_CLOSE) {
- if (strlen($value) > 0) {
- $tokens1[] = new PHPExcel_Calculation_FormulaToken($value, PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERAND);
- $value = "";
- }
- $tmp = array_pop($stack);
- $tmp->setValue("");
- $tmp->setTokenSubType(PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_STOP);
- $tokens1[] = $tmp;
- ++$index;
- continue;
- }
-
- $value .= $this->formula{$index};
- ++$index;
- }
-
- if (strlen($value) > 0) {
- $tokens1[] = new PHPExcel_Calculation_FormulaToken($value, PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERAND);
- }
-
- $tokenCount = count($tokens1);
- for ($i = 0; $i < $tokenCount; ++$i) {
- $token = $tokens1[$i];
- if (isset($tokens1[$i - 1])) {
- $previousToken = $tokens1[$i - 1];
- } else {
- $previousToken = null;
- }
- if (isset($tokens1[$i + 1])) {
- $nextToken = $tokens1[$i + 1];
- } else {
- $nextToken = null;
- }
- if (is_null($token)) {
- continue;
- }
- if ($token->getTokenType() != PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_WHITESPACE) {
- $tokens2[] = $token;
- continue;
- }
- if (is_null($previousToken)) {
- continue;
- }
- if (! (
- (($previousToken->getTokenType() == PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_FUNCTION) && ($previousToken->getTokenSubType() == PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_STOP)) ||
- (($previousToken->getTokenType() == PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_SUBEXPRESSION) && ($previousToken->getTokenSubType() == PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_STOP)) ||
- ($previousToken->getTokenType() == PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERAND)
- ) ) {
- continue;
- }
- if (is_null($nextToken)) {
- continue;
- }
- if (! (
- (($nextToken->getTokenType() == PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_FUNCTION) && ($nextToken->getTokenSubType() == PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_START)) ||
- (($nextToken->getTokenType() == PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_SUBEXPRESSION) && ($nextToken->getTokenSubType() == PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_START)) ||
- ($nextToken->getTokenType() == PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERAND)
- ) ) {
- continue;
- }
- $tokens2[] = new PHPExcel_Calculation_FormulaToken($value, PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERATORINFIX, PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_INTERSECTION);
- }
-
-
- $this->tokens = array();
- $tokenCount = count($tokens2);
- for ($i = 0; $i < $tokenCount; ++$i) {
- $token = $tokens2[$i];
- if (isset($tokens2[$i - 1])) {
- $previousToken = $tokens2[$i - 1];
- } else {
- $previousToken = null;
- }
- if (isset($tokens2[$i + 1])) {
- $nextToken = $tokens2[$i + 1];
- } else {
- $nextToken = null;
- }
- if (is_null($token)) {
- continue;
- }
- if ($token->getTokenType() == PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERATORINFIX && $token->getValue() == "-") {
- if ($i == 0) {
- $token->setTokenType(PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERATORPREFIX);
- } elseif ((($previousToken->getTokenType() == PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_FUNCTION) &&
- ($previousToken->getTokenSubType() == PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_STOP)) ||
- (($previousToken->getTokenType() == PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_SUBEXPRESSION) &&
- ($previousToken->getTokenSubType() == PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_STOP)) ||
- ($previousToken->getTokenType() == PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERATORPOSTFIX) ||
- ($previousToken->getTokenType() == PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERAND)) {
- $token->setTokenSubType(PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_MATH);
- } else {
- $token->setTokenType(PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERATORPREFIX);
- }
- $this->tokens[] = $token;
- continue;
- }
- if ($token->getTokenType() == PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERATORINFIX && $token->getValue() == "+") {
- if ($i == 0) {
- continue;
- } elseif ((($previousToken->getTokenType() == PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_FUNCTION) &&
- ($previousToken->getTokenSubType() == PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_STOP)) ||
- (($previousToken->getTokenType() == PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_SUBEXPRESSION) &&
- ($previousToken->getTokenSubType() == PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_STOP)) ||
- ($previousToken->getTokenType() == PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERATORPOSTFIX) ||
- ($previousToken->getTokenType() == PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERAND)) {
- $token->setTokenSubType(PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_MATH);
- } else {
- continue;
- }
- $this->tokens[] = $token;
- continue;
- }
- if ($token->getTokenType() == PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERATORINFIX &&
- $token->getTokenSubType() == PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_NOTHING) {
- if (strpos("<>=", substr($token->getValue(), 0, 1)) !== false) {
- $token->setTokenSubType(PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_LOGICAL);
- } elseif ($token->getValue() == "&") {
- $token->setTokenSubType(PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_CONCATENATION);
- } else {
- $token->setTokenSubType(PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_MATH);
- }
- $this->tokens[] = $token;
- continue;
- }
- if ($token->getTokenType() == PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERAND &&
- $token->getTokenSubType() == PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_NOTHING) {
- if (!is_numeric($token->getValue())) {
- if (strtoupper($token->getValue()) == "TRUE" || strtoupper($token->getValue() == "FALSE")) {
- $token->setTokenSubType(PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_LOGICAL);
- } else {
- $token->setTokenSubType(PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_RANGE);
- }
- } else {
- $token->setTokenSubType(PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_NUMBER);
- }
- $this->tokens[] = $token;
- continue;
- }
- if ($token->getTokenType() == PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_FUNCTION) {
- if (strlen($token->getValue() > 0)) {
- if (substr($token->getValue(), 0, 1) == "@") {
- $token->setValue(substr($token->getValue(), 1));
- }
- }
- }
- $this->tokens[] = $token;
- }
- }
- }
|