[Summary view] [Print] [Text view]

   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  }