ILIAS  release_5-3 Revision v5.3.23-19-g915713cf615
FormulaParser.php
Go to the documentation of this file.
1<?php
29/*
30PARTLY 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
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
83 private $_formula;
84
90 private $_tokens = array();
91
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
116 public function getFormula() {
117 return $this->_formula;
118 }
119
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
140 public function getTokenCount() {
141 return count($this->_tokens);
142 }
143
149 public function getTokens() {
150 return $this->_tokens;
151 }
152
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) {
183 if ((($index + 2) <= $formulaLength) && ($this->_formula{$index + 1} == PHPExcel_Calculation_FormulaParser::QUOTE_DOUBLE)) {
185 ++$index;
186 } else {
187 $inString = false;
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) {
203 if ((($index + 2) <= $formulaLength) && ($this->_formula{$index + 1} == 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) {
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;
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
256 if (strlen($value > 0)) { // unexpected
258 $value = "";
259 }
260 $inString = true;
261 ++$index;
262 continue;
263 }
264
266 if (strlen($value) > 0) { // unexpected
268 $value = "";
269 }
270 $inPath = true;
271 ++$index;
272 continue;
273 }
274
276 $inRange = true;
278 ++$index;
279 continue;
280 }
281
283 if (strlen($value) > 0) { // unexpected
285 $value = "";
286 }
287 $inError = true;
289 ++$index;
290 continue;
291 }
292
293 // mark start and end of arrays and array rows
295 if (strlen($value) > 0) { // unexpected
297 $value = "";
298 }
299
301 $tokens1[] = $tmp;
302 $stack[] = clone $tmp;
303
305 $tokens1[] = $tmp;
306 $stack[] = clone $tmp;
307
308 ++$index;
309 continue;
310 }
311
313 if (strlen($value) > 0) {
315 $value = "";
316 }
317
318 $tmp = array_pop($stack);
319 $tmp->setValue("");
321 $tokens1[] = $tmp;
322
324 $tokens1[] = $tmp;
325
327 $tokens1[] = $tmp;
328 $stack[] = clone $tmp;
329
330 ++$index;
331 continue;
332 }
333
335 if (strlen($value) > 0) {
337 $value = "";
338 }
339
340 $tmp = array_pop($stack);
341 $tmp->setValue("");
343 $tokens1[] = $tmp;
344
345 $tmp = array_pop($stack);
346 $tmp->setValue("");
348 $tokens1[] = $tmp;
349
350 ++$index;
351 continue;
352 }
353
354 // trim white-space
356 if (strlen($value) > 0) {
358 $value = "";
359 }
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) {
373 $value = "";
374 }
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) {
385 $value = "";
386 }
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) {
396 $value = "";
397 }
399 ++$index;
400 continue;
401 }
402
403 // start subexpression or function
405 if (strlen($value) > 0) {
407 $tokens1[] = $tmp;
408 $stack[] = clone $tmp;
409 $value = "";
410 } else {
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) {
423 $value = "";
424 }
425
426 $tmp = array_pop($stack);
427 $tmp->setValue("");
429 $stack[] = $tmp;
430
431 if ($tmp->getTokenType() == PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_FUNCTION) {
433 } else {
435 }
436 ++$index;
437 continue;
438 }
439
440 // stop subexpression
442 if (strlen($value) > 0) {
444 $value = "";
445 }
446
447 $tmp = array_pop($stack);
448 $tmp->setValue("");
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) {
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
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) {
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 ) {
549 } else {
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 ) {
567 } else {
568 continue;
569 }
570
571 $this->_tokens[] = $token;
572 continue;
573 }
574
576 if (strpos("<>=", substr($token->getValue(), 0, 1)) !== false) {
578 } else if ($token->getValue() == "&") {
580 } else {
582 }
583
584 $this->_tokens[] = $token;
585 continue;
586 }
587
589 if (!is_numeric($token->getValue())) {
590 if (strtoupper($token->getValue()) == "TRUE" || strtoupper($token->getValue() == "FALSE")) {
592 } else {
594 }
595 } else {
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}
An exception for terminatinating execution or to throw for unit testing.
__construct($pFormula='')
Create a new PHPExcel_Calculation_FormulaParser.
$i
Definition: disco.tpl.php:19
$index
Definition: metadata.php:60