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    1.8.0, 2014-03-02
 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: }
615: