1: <?php
2: 3: 4: 5: 6: 7: 8: 9: 10: 11: 12: 13: 14: 15: 16: 17: 18: 19: 20: 21: 22: 23: 24: 25: 26:
27:
28:
29: 30: 31: 32: 33: 34: 35: 36: 37: 38: 39: 40: 41: 42: 43: 44: 45: 46: 47: 48: 49: 50:
51:
52: 53: 54: 55: 56: 57: 58:
59: class PHPExcel_Calculation_FormulaParser {
60:
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: 80: 81: 82:
83: private $_formula;
84:
85: 86: 87: 88: 89:
90: private $_tokens = array();
91:
92: 93: 94: 95: 96: 97:
98: public function __construct($pFormula = '')
99: {
100:
101: if (is_null($pFormula)) {
102: throw new PHPExcel_Calculation_Exception("Invalid parameter passed: formula");
103: }
104:
105:
106: $this->_formula = trim($pFormula);
107:
108: $this->_parseToTokens();
109: }
110:
111: 112: 113: 114: 115:
116: public function getFormula() {
117: return $this->_formula;
118: }
119:
120: 121: 122: 123: 124: 125: 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: 137: 138: 139:
140: public function getTokenCount() {
141: return count($this->_tokens);
142: }
143:
144: 145: 146: 147: 148:
149: public function getTokens() {
150: return $this->_tokens;
151: }
152:
153: 154: 155:
156: private function _parseToTokens() {
157:
158:
159:
160:
161: $formulaLength = strlen($this->_formula);
162: if ($formulaLength < 2 || $this->_formula{0} != '=') return;
163:
164:
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:
177:
178:
179:
180:
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:
199:
200:
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:
217:
218:
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:
229:
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:
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:
253:
254:
255: if ($this->_formula{$index} == PHPExcel_Calculation_FormulaParser::QUOTE_DOUBLE) {
256: if (strlen($value > 0)) {
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) {
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) {
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:
294: if ($this->_formula{$index} == PHPExcel_Calculation_FormulaParser::BRACE_OPEN) {
295: if (strlen($value) > 0) {
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:
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:
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:
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:
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:
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:
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:
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:
457: $value .= $this->_formula{$index};
458: ++$index;
459: }
460:
461:
462: if (strlen($value) > 0) {
463: $tokens1[] = new PHPExcel_Calculation_FormulaToken($value, PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERAND);
464: }
465:
466:
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:
518:
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: