vendor/phpoffice/phpexcel/Classes/PHPExcel/Calculation/FormulaParser.php

   1 <?php
   2 /**
   3  * PHPExcel
   4  *
   5  * Copyright (c) 2006 - 2014 PHPExcel
   6  *
   7  * This library is free software; you can redistribute it and/or
   8  * modify it under the terms of the GNU Lesser General Public
   9  * License as published by the Free Software Foundation; either
  10  * version 2.1 of the License, or (at your option) any later version.
  11  *
  12  * This library is distributed in the hope that it will be useful,
  13  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  14  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
  15  * Lesser General Public License for more details.
  16  *
  17  * You should have received a copy of the GNU Lesser General Public
  18  * License along with this library; if not, write to the Free Software
  19  * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
  20  *
  21  * @category   PHPExcel
  22  * @package    PHPExcel_Calculation
  23  * @copyright  Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel)
  24  * @license    http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt    LGPL
  25  * @version    ##VERSION##, ##DATE##
  26  */
  27
  28
  29 /*
  30 PARTLY BASED ON:
  31         Copyright (c) 2007 E. W. Bachtal, Inc.
  32
  33         Permission is hereby granted, free of charge, to any person obtaining a copy of this software
  34         and associated documentation files (the "Software"), to deal in the Software without restriction,
  35         including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense,
  36         and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so,
  37         subject to the following conditions:
  38
  39           The above copyright notice and this permission notice shall be included in all copies or substantial
  40           portions of the Software.
  41
  42         The software is provided "as is", without warranty of any kind, express or implied, including but not
  43         limited to the warranties of merchantability, fitness for a particular purpose and noninfringement. In
  44         no event shall the authors or copyright holders be liable for any claim, damages or other liability,
  45         whether in an action of contract, tort or otherwise, arising from, out of or in connection with the
  46         software or the use or other dealings in the software.
  47
  48         http://ewbi.blogs.com/develops/2007/03/excel_formula_p.html
  49         http://ewbi.blogs.com/develops/2004/12/excel_formula_p.html
  50 */
  51
  52 /**
  53  * PHPExcel_Calculation_FormulaParser
  54  *
  55  * @category   PHPExcel
  56  * @package    PHPExcel_Calculation
  57  * @copyright  Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel)
  58  */
  59 class PHPExcel_Calculation_FormulaParser {
  60         /* Character constants */
  61         const QUOTE_DOUBLE  = '"';
  62         const QUOTE_SINGLE  = '\'';
  63         const BRACKET_CLOSE = ']';
  64         const BRACKET_OPEN  = '[';
  65         const BRACE_OPEN    = '{';
  66         const BRACE_CLOSE   = '}';
  67         const PAREN_OPEN    = '(';
  68         const PAREN_CLOSE   = ')';
  69         const SEMICOLON     = ';';
  70         const WHITESPACE    = ' ';
  71         const COMMA         = ',';
  72         const ERROR_START   = '#';
  73
  74         const OPERATORS_SN                      = "+-";
  75         const OPERATORS_INFIX           = "+-*/^&=><";
  76         const OPERATORS_POSTFIX         = "%";
  77
  78         /**
  79          * Formula
  80          *
  81          * @var string
  82          */
  83         private $_formula;
  84
  85         /**
  86          * Tokens
  87          *
  88          * @var PHPExcel_Calculation_FormulaToken[]
  89          */
  90         private $_tokens = array();
  91
  92     /**
  93      * Create a new PHPExcel_Calculation_FormulaParser
  94      *
  95      * @param   string          $pFormula       Formula to parse
  96      * @throws  PHPExcel_Calculation_Exception
  97      */
  98     public function __construct($pFormula = '')
  99     {
 100         // Check parameters
 101         if (is_null($pFormula)) {
 102                 throw new PHPExcel_Calculation_Exception("Invalid parameter passed: formula");
 103         }
 104
 105         // Initialise values
 106         $this->_formula = trim($pFormula);
 107         // Parse!
 108         $this->_parseToTokens();
 109     }
 110
 111     /**
 112      * Get Formula
 113      *
 114      * @return string
 115      */
 116     public function getFormula() {
 117         return $this->_formula;
 118     }
 119
 120     /**
 121      * Get Token
 122      *
 123      * @param   int             $pId    Token id
 124      * @return  string
 125      * @throws  PHPExcel_Calculation_Exception
 126      */
 127     public function getToken($pId = 0) {
 128         if (isset($this->_tokens[$pId])) {
 129                 return $this->_tokens[$pId];
 130         } else {
 131                 throw new PHPExcel_Calculation_Exception("Token with id $pId does not exist.");
 132         }
 133     }
 134
 135     /**
 136      * Get Token count
 137      *
 138      * @return string
 139      */
 140     public function getTokenCount() {
 141         return count($this->_tokens);
 142     }
 143
 144     /**
 145      * Get Tokens
 146      *
 147      * @return PHPExcel_Calculation_FormulaToken[]
 148      */
 149     public function getTokens() {
 150         return $this->_tokens;
 151     }
 152
 153     /**
 154      * Parse to tokens
 155      */
 156     private function _parseToTokens() {
 157                 // No attempt is made to verify formulas; assumes formulas are derived from Excel, where
 158                 // they can only exist if valid; stack overflows/underflows sunk as nulls without exceptions.
 159
 160                 // Check if the formula has a valid starting =
 161                 $formulaLength = strlen($this->_formula);
 162                 if ($formulaLength < 2 || $this->_formula{0} != '=') return;
 163
 164                 // Helper variables
 165                 $tokens1        = $tokens2      = $stack = array();
 166                 $inString       = $inPath       = $inRange      = $inError = false;
 167                 $token          = $previousToken        = $nextToken    = null;
 168
 169                 $index  = 1;
 170                 $value  = '';
 171
 172                 $ERRORS                         = array("#NULL!", "#DIV/0!", "#VALUE!", "#REF!", "#NAME?", "#NUM!", "#N/A");
 173                 $COMPARATORS_MULTI      = array(">=", "<=", "<>");
 174
 175                 while ($index < $formulaLength) {
 176                         // state-dependent character evaluation (order is important)
 177
 178                         // double-quoted strings
 179                         // embeds are doubled
 180                         // end marks token
 181                         if ($inString) {
 182                                 if ($this->_formula{$index} == PHPExcel_Calculation_FormulaParser::QUOTE_DOUBLE) {
 183                                         if ((($index + 2) <= $formulaLength) && ($this->_formula{$index + 1} == PHPExcel_Calculation_FormulaParser::QUOTE_DOUBLE)) {
 184                                                 $value .= PHPExcel_Calculation_FormulaParser::QUOTE_DOUBLE;
 185                                                 ++$index;
 186                                         } else {
 187                                                 $inString = false;
 188                                                 $tokens1[] = new PHPExcel_Calculation_FormulaToken($value, PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERAND, PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_TEXT);
 189                                                 $value = "";
 190                                         }
 191                                 } else {
 192                                         $value .= $this->_formula{$index};
 193                                 }
 194                                 ++$index;
 195                                 continue;
 196                         }
 197
 198                         // single-quoted strings (links)
 199                         // embeds are double
 200                         // end does not mark a token
 201                         if ($inPath) {
 202                                 if ($this->_formula{$index} == PHPExcel_Calculation_FormulaParser::QUOTE_SINGLE) {
 203                                         if ((($index + 2) <= $formulaLength) && ($this->_formula{$index + 1} == PHPExcel_Calculation_FormulaParser::QUOTE_SINGLE)) {
 204                                                 $value .= PHPExcel_Calculation_FormulaParser::QUOTE_SINGLE;
 205                                                 ++$index;
 206                                         } else {
 207                                                 $inPath = false;
 208                                         }
 209                                 } else {
 210                                         $value .= $this->_formula{$index};
 211                                 }
 212                                 ++$index;
 213                                 continue;
 214                         }
 215
 216                         // bracked strings (R1C1 range index or linked workbook name)
 217                         // no embeds (changed to "()" by Excel)
 218                         // end does not mark a token
 219                         if ($inRange) {
 220                                 if ($this->_formula{$index} == PHPExcel_Calculation_FormulaParser::BRACKET_CLOSE) {
 221                                         $inRange = false;
 222                                 }
 223                                 $value .= $this->_formula{$index};
 224                                 ++$index;
 225                                 continue;
 226                         }
 227
 228                         // error values
 229                         // end marks a token, determined from absolute list of values
 230                         if ($inError) {
 231                                 $value .= $this->_formula{$index};
 232                                 ++$index;
 233                                 if (in_array($value, $ERRORS)) {
 234                                         $inError = false;
 235                                         $tokens1[] = new PHPExcel_Calculation_FormulaToken($value, PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERAND, PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_ERROR);
 236                                         $value = "";
 237                                 }
 238                                 continue;
 239                         }
 240
 241                         // scientific notation check
 242                         if (strpos(PHPExcel_Calculation_FormulaParser::OPERATORS_SN, $this->_formula{$index}) !== false) {
 243                                 if (strlen($value) > 1) {
 244                                         if (preg_match("/^[1-9]{1}(\.[0-9]+)?E{1}$/", $this->_formula{$index}) != 0) {
 245                                                 $value .= $this->_formula{$index};
 246                                                 ++$index;
 247                                                 continue;
 248                                         }
 249                                 }
 250                         }
 251
 252                         // independent character evaluation (order not important)
 253
 254                         // establish state-dependent character evaluations
 255                         if ($this->_formula{$index} == PHPExcel_Calculation_FormulaParser::QUOTE_DOUBLE) {
 256                                 if (strlen($value > 0)) {  // unexpected
 257                                         $tokens1[] = new PHPExcel_Calculation_FormulaToken($value, PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_UNKNOWN);
 258                                         $value = "";
 259                                 }
 260                                 $inString = true;
 261                                 ++$index;
 262                                 continue;
 263                         }
 264
 265                         if ($this->_formula{$index} == PHPExcel_Calculation_FormulaParser::QUOTE_SINGLE) {
 266                                 if (strlen($value) > 0) { // unexpected
 267                                         $tokens1[] = new PHPExcel_Calculation_FormulaToken($value, PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_UNKNOWN);
 268                                         $value = "";
 269                                 }
 270                                 $inPath = true;
 271                                 ++$index;
 272                                 continue;
 273                         }
 274
 275                         if ($this->_formula{$index} == PHPExcel_Calculation_FormulaParser::BRACKET_OPEN) {
 276                                 $inRange = true;
 277                                 $value .= PHPExcel_Calculation_FormulaParser::BRACKET_OPEN;
 278                                 ++$index;
 279                                 continue;
 280                         }
 281
 282                         if ($this->_formula{$index} == PHPExcel_Calculation_FormulaParser::ERROR_START) {
 283                                 if (strlen($value) > 0) { // unexpected
 284                                         $tokens1[] = new PHPExcel_Calculation_FormulaToken($value, PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_UNKNOWN);
 285                                         $value = "";
 286                                 }
 287                                 $inError = true;
 288                                 $value .= PHPExcel_Calculation_FormulaParser::ERROR_START;
 289                                 ++$index;
 290                                 continue;
 291                         }
 292
 293                         // mark start and end of arrays and array rows
 294                         if ($this->_formula{$index} == PHPExcel_Calculation_FormulaParser::BRACE_OPEN) {
 295                                 if (strlen($value) > 0) { // unexpected
 296                                         $tokens1[] = new PHPExcel_Calculation_FormulaToken($value, PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_UNKNOWN);
 297                                         $value = "";
 298                                 }
 299
 300                                 $tmp = new PHPExcel_Calculation_FormulaToken("ARRAY", PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_FUNCTION, PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_START);
 301                                 $tokens1[] = $tmp;
 302                                 $stack[] = clone $tmp;
 303
 304                                 $tmp = new PHPExcel_Calculation_FormulaToken("ARRAYROW", PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_FUNCTION, PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_START);
 305                                 $tokens1[] = $tmp;
 306                                 $stack[] = clone $tmp;
 307
 308                                 ++$index;
 309                                 continue;
 310                         }
 311
 312                         if ($this->_formula{$index} == PHPExcel_Calculation_FormulaParser::SEMICOLON) {
 313                                 if (strlen($value) > 0) {
 314                                         $tokens1[] = new PHPExcel_Calculation_FormulaToken($value, PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERAND);
 315                                         $value = "";
 316                                 }
 317
 318                                 $tmp = array_pop($stack);
 319                                 $tmp->setValue("");
 320                                 $tmp->setTokenSubType(PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_STOP);
 321                                 $tokens1[] = $tmp;
 322
 323                                 $tmp = new PHPExcel_Calculation_FormulaToken(",", PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_ARGUMENT);
 324                                 $tokens1[] = $tmp;
 325
 326                                 $tmp = new PHPExcel_Calculation_FormulaToken("ARRAYROW", PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_FUNCTION, PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_START);
 327                                 $tokens1[] = $tmp;
 328                                 $stack[] = clone $tmp;
 329
 330                                 ++$index;
 331                                 continue;
 332                         }
 333
 334                         if ($this->_formula{$index} == PHPExcel_Calculation_FormulaParser::BRACE_CLOSE) {
 335                                 if (strlen($value) > 0) {
 336                                         $tokens1[] = new PHPExcel_Calculation_FormulaToken($value, PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERAND);
 337                                         $value = "";
 338                                 }
 339
 340                                 $tmp = array_pop($stack);
 341                                 $tmp->setValue("");
 342                                 $tmp->setTokenSubType(PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_STOP);
 343                                 $tokens1[] = $tmp;
 344
 345                                 $tmp = array_pop($stack);
 346                                 $tmp->setValue("");
 347                                 $tmp->setTokenSubType(PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_STOP);
 348                                 $tokens1[] = $tmp;
 349
 350                                 ++$index;
 351                                 continue;
 352                         }
 353
 354                         // trim white-space
 355                         if ($this->_formula{$index} == PHPExcel_Calculation_FormulaParser::WHITESPACE) {
 356                                 if (strlen($value) > 0) {
 357                                         $tokens1[] = new PHPExcel_Calculation_FormulaToken($value, PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERAND);
 358                                         $value = "";
 359                                 }
 360                                 $tokens1[] = new PHPExcel_Calculation_FormulaToken("", PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_WHITESPACE);
 361                                 ++$index;
 362                                 while (($this->_formula{$index} == PHPExcel_Calculation_FormulaParser::WHITESPACE) && ($index < $formulaLength)) {
 363                                         ++$index;
 364                                 }
 365                                 continue;
 366                         }
 367
 368                         // multi-character comparators
 369                         if (($index + 2) <= $formulaLength) {
 370                                 if (in_array(substr($this->_formula, $index, 2), $COMPARATORS_MULTI)) {
 371                                         if (strlen($value) > 0) {
 372                                                 $tokens1[] = new PHPExcel_Calculation_FormulaToken($value, PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERAND);
 373                                                 $value = "";
 374                                         }
 375                                         $tokens1[] = new PHPExcel_Calculation_FormulaToken(substr($this->_formula, $index, 2), PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERATORINFIX, PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_LOGICAL);
 376                                         $index += 2;
 377                                         continue;
 378                                 }
 379                         }
 380
 381                         // standard infix operators
 382                         if (strpos(PHPExcel_Calculation_FormulaParser::OPERATORS_INFIX, $this->_formula{$index}) !== false) {
 383                                 if (strlen($value) > 0) {
 384                                         $tokens1[] =new PHPExcel_Calculation_FormulaToken($value, PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERAND);
 385                                         $value = "";
 386                                 }
 387                                 $tokens1[] = new PHPExcel_Calculation_FormulaToken($this->_formula{$index}, PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERATORINFIX);
 388                                 ++$index;
 389                                 continue;
 390                         }
 391
 392                         // standard postfix operators (only one)
 393                         if (strpos(PHPExcel_Calculation_FormulaParser::OPERATORS_POSTFIX, $this->_formula{$index}) !== false) {
 394                                 if (strlen($value) > 0) {
 395                                         $tokens1[] = new PHPExcel_Calculation_FormulaToken($value, PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERAND);
 396                                         $value = "";
 397                                 }
 398                                 $tokens1[] = new PHPExcel_Calculation_FormulaToken($this->_formula{$index}, PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERATORPOSTFIX);
 399                                 ++$index;
 400                                 continue;
 401                         }
 402
 403                         // start subexpression or function
 404                         if ($this->_formula{$index} == PHPExcel_Calculation_FormulaParser::PAREN_OPEN) {
 405                                 if (strlen($value) > 0) {
 406                                         $tmp = new PHPExcel_Calculation_FormulaToken($value, PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_FUNCTION, PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_START);
 407                                         $tokens1[] = $tmp;
 408                                         $stack[] = clone $tmp;
 409                                         $value = "";
 410                                 } else {
 411                                         $tmp = new PHPExcel_Calculation_FormulaToken("", PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_SUBEXPRESSION, PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_START);
 412                                         $tokens1[] = $tmp;
 413                                         $stack[] = clone $tmp;
 414                                 }
 415                                 ++$index;
 416                                 continue;
 417                         }
 418
 419                         // function, subexpression, or array parameters, or operand unions
 420                         if ($this->_formula{$index} == PHPExcel_Calculation_FormulaParser::COMMA) {
 421                                 if (strlen($value) > 0) {
 422                                         $tokens1[] = new PHPExcel_Calculation_FormulaToken($value, PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERAND);
 423                                         $value = "";
 424                                 }
 425
 426                                 $tmp = array_pop($stack);
 427                                 $tmp->setValue("");
 428                                 $tmp->setTokenSubType(PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_STOP);
 429                                 $stack[] = $tmp;
 430
 431                                 if ($tmp->getTokenType() == PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_FUNCTION) {
 432                                         $tokens1[] = new PHPExcel_Calculation_FormulaToken(",", PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERATORINFIX, PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_UNION);
 433                                 } else {
 434                                         $tokens1[] = new PHPExcel_Calculation_FormulaToken(",", PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_ARGUMENT);
 435                                 }
 436                                 ++$index;
 437                                 continue;
 438                         }
 439
 440                         // stop subexpression
 441                         if ($this->_formula{$index} == PHPExcel_Calculation_FormulaParser::PAREN_CLOSE) {
 442                                 if (strlen($value) > 0) {
 443                                         $tokens1[] = new PHPExcel_Calculation_FormulaToken($value, PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERAND);
 444                                         $value = "";
 445                                 }
 446
 447                                 $tmp = array_pop($stack);
 448                                 $tmp->setValue("");
 449                                 $tmp->setTokenSubType(PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_STOP);
 450                                 $tokens1[] = $tmp;
 451
 452                                 ++$index;
 453                                 continue;
 454                         }
 455
 456                 // token accumulation
 457                         $value .= $this->_formula{$index};
 458                         ++$index;
 459                 }
 460
 461                 // dump remaining accumulation
 462                 if (strlen($value) > 0) {
 463                         $tokens1[] = new PHPExcel_Calculation_FormulaToken($value, PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERAND);
 464                 }
 465
 466                 // move tokenList to new set, excluding unnecessary white-space tokens and converting necessary ones to intersections
 467                 $tokenCount = count($tokens1);
 468                 for ($i = 0; $i < $tokenCount; ++$i) {
 469                         $token = $tokens1[$i];
 470                         if (isset($tokens1[$i - 1])) {
 471                                 $previousToken = $tokens1[$i - 1];
 472                         } else {
 473                                 $previousToken = null;
 474                         }
 475                         if (isset($tokens1[$i + 1])) {
 476                                 $nextToken = $tokens1[$i + 1];
 477                         } else {
 478                                 $nextToken = null;
 479                         }
 480
 481                         if (is_null($token)) {
 482                                 continue;
 483                         }
 484
 485                         if ($token->getTokenType() != PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_WHITESPACE) {
 486                                 $tokens2[] = $token;
 487                                 continue;
 488                         }
 489
 490                         if (is_null($previousToken)) {
 491                                 continue;
 492                         }
 493
 494                         if (! (
 495                                         (($previousToken->getTokenType() == PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_FUNCTION) && ($previousToken->getTokenSubType() == PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_STOP)) ||
 496                                         (($previousToken->getTokenType() == PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_SUBEXPRESSION) && ($previousToken->getTokenSubType() == PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_STOP)) ||
 497                                         ($previousToken->getTokenType() == PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERAND)
 498                                   ) ) {
 499                                 continue;
 500                         }
 501
 502                         if (is_null($nextToken)) {
 503                                 continue;
 504                         }
 505
 506                         if (! (
 507                                         (($nextToken->getTokenType() == PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_FUNCTION) && ($nextToken->getTokenSubType() == PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_START)) ||
 508                                         (($nextToken->getTokenType() == PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_SUBEXPRESSION) && ($nextToken->getTokenSubType() == PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_START)) ||
 509                                         ($nextToken->getTokenType() == PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERAND)
 510                                   ) ) {
 511                                 continue;
 512                         }
 513
 514                         $tokens2[] = new PHPExcel_Calculation_FormulaToken($value, PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERATORINFIX, PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_INTERSECTION);
 515                 }
 516
 517                 // move tokens to final list, switching infix "-" operators to prefix when appropriate, switching infix "+" operators
 518                 // to noop when appropriate, identifying operand and infix-operator subtypes, and pulling "@" from function names
 519                 $this->_tokens = array();
 520
 521                 $tokenCount = count($tokens2);
 522                 for ($i = 0; $i < $tokenCount; ++$i) {
 523                         $token = $tokens2[$i];
 524                         if (isset($tokens2[$i - 1])) {
 525                                 $previousToken = $tokens2[$i - 1];
 526                         } else {
 527                                 $previousToken = null;
 528                         }
 529                         if (isset($tokens2[$i + 1])) {
 530                                 $nextToken = $tokens2[$i + 1];
 531                         } else {
 532                                 $nextToken = null;
 533                         }
 534
 535                         if (is_null($token)) {
 536                                 continue;
 537                         }
 538
 539                         if ($token->getTokenType() == PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERATORINFIX && $token->getValue() == "-") {
 540                                 if ($i == 0) {
 541                                         $token->setTokenType(PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERATORPREFIX);
 542                                 } else if (
 543                                                         (($previousToken->getTokenType() == PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_FUNCTION) && ($previousToken->getTokenSubType() == PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_STOP)) ||
 544                                                         (($previousToken->getTokenType() == PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_SUBEXPRESSION) && ($previousToken->getTokenSubType() == PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_STOP)) ||
 545                                                         ($previousToken->getTokenType() == PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERATORPOSTFIX) ||
 546                                                         ($previousToken->getTokenType() == PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERAND)
 547                                                 ) {
 548                                         $token->setTokenSubType(PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_MATH);
 549                                 } else {
 550                                         $token->setTokenType(PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERATORPREFIX);
 551                                 }
 552
 553                                 $this->_tokens[] = $token;
 554                                 continue;
 555                         }
 556
 557                         if ($token->getTokenType() == PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERATORINFIX && $token->getValue() == "+") {
 558                                 if ($i == 0) {
 559                                         continue;
 560                                 } else if (
 561                                                         (($previousToken->getTokenType() == PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_FUNCTION) && ($previousToken->getTokenSubType() == PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_STOP)) ||
 562                                                         (($previousToken->getTokenType() == PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_SUBEXPRESSION) && ($previousToken->getTokenSubType() == PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_STOP)) ||
 563                                                         ($previousToken->getTokenType() == PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERATORPOSTFIX) ||
 564                                                         ($previousToken->getTokenType() == PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERAND)
 565                                                 ) {
 566                                         $token->setTokenSubType(PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_MATH);
 567                                 } else {
 568                                         continue;
 569                                 }
 570
 571                                 $this->_tokens[] = $token;
 572                                 continue;
 573                         }
 574
 575                         if ($token->getTokenType() == PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERATORINFIX && $token->getTokenSubType() == PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_NOTHING) {
 576                                 if (strpos("<>=", substr($token->getValue(), 0, 1)) !== false) {
 577                                         $token->setTokenSubType(PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_LOGICAL);
 578                                 } else if ($token->getValue() == "&") {
 579                                         $token->setTokenSubType(PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_CONCATENATION);
 580                                 } else {
 581                                         $token->setTokenSubType(PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_MATH);
 582                                 }
 583
 584                                 $this->_tokens[] = $token;
 585                                 continue;
 586                         }
 587
 588                         if ($token->getTokenType() == PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERAND && $token->getTokenSubType() == PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_NOTHING) {
 589                                 if (!is_numeric($token->getValue())) {
 590                                         if (strtoupper($token->getValue()) == "TRUE" || strtoupper($token->getValue() == "FALSE")) {
 591                                                 $token->setTokenSubType(PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_LOGICAL);
 592                                         } else {
 593                                                 $token->setTokenSubType(PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_RANGE);
 594                                         }
 595                                 } else {
 596                                         $token->setTokenSubType(PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_NUMBER);
 597                                 }
 598
 599                                 $this->_tokens[] = $token;
 600                                 continue;
 601                         }
 602
 603                         if ($token->getTokenType() == PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_FUNCTION) {
 604                                 if (strlen($token->getValue() > 0)) {
 605                                         if (substr($token->getValue(), 0, 1) == "@") {
 606                                                 $token->setValue(substr($token->getValue(), 1));
 607                                         }
 608                                 }
 609                         }
 610
 611                 $this->_tokens[] = $token;
 612                 }
 613     }
 614 }