ILIAS  release_5-2 Revision v5.2.25-18-g3f80b828510
Calculation.php
Go to the documentation of this file.
1<?php
30if (!defined('PHPEXCEL_ROOT')) {
34 define('PHPEXCEL_ROOT', dirname(__FILE__) . '/../');
35 require(PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php');
36}
37
38
39if (!defined('CALCULATION_REGEXP_CELLREF')) {
40 // Test for support of \P (multibyte options) in PCRE
41 if(defined('PREG_BAD_UTF8_ERROR')) {
42 // Cell reference (cell or range of cells, with or without a sheet reference)
43 define('CALCULATION_REGEXP_CELLREF','((([^\s,!&%^\/\*\+<>=-]*)|(\'[^\']*\')|(\"[^\"]*\"))!)?\$?([a-z]{1,3})\$?(\d{1,7})');
44 // Named Range of cells
45 define('CALCULATION_REGEXP_NAMEDRANGE','((([^\s,!&%^\/\*\+<>=-]*)|(\'[^\']*\')|(\"[^\"]*\"))!)?([_A-Z][_A-Z0-9\.]*)');
46 } else {
47 // Cell reference (cell or range of cells, with or without a sheet reference)
48 define('CALCULATION_REGEXP_CELLREF','(((\w*)|(\'[^\']*\')|(\"[^\"]*\"))!)?\$?([a-z]{1,3})\$?(\d+)');
49 // Named Range of cells
50 define('CALCULATION_REGEXP_NAMEDRANGE','(((\w*)|(\'.*\')|(\".*\"))!)?([_A-Z][_A-Z0-9\.]*)');
51 }
52}
53
54
63
66 // Numeric operand
67 const CALCULATION_REGEXP_NUMBER = '[-+]?\d*\.?\d+(e[-+]?\d+)?';
68 // String operand
69 const CALCULATION_REGEXP_STRING = '"(?:[^"]|"")*"';
70 // Opening bracket
72 // Function (allow for the old @ symbol that could be used to prefix a function, but we'll ignore it)
73 const CALCULATION_REGEXP_FUNCTION = '@?([A-Z][A-Z0-9\.]*)[\s]*\‍(';
74 // Cell reference (cell or range of cells, with or without a sheet reference)
76 // Named Range of cells
78 // Error
79 const CALCULATION_REGEXP_ERROR = '\#[A-Z][A-Z0_\/]*[!\?]?';
80
81
83 const RETURN_ARRAY_AS_ERROR = 'error';
84 const RETURN_ARRAY_AS_VALUE = 'value';
85 const RETURN_ARRAY_AS_ARRAY = 'array';
86
88
89
96 private static $_instance;
97
98
105 private $_workbook;
106
113 private static $_workbookSets;
114
121 private $_calculationCache = array ();
122
123
131
132
140 private static $_operators = array('+' => TRUE, '-' => TRUE, '*' => TRUE, '/' => TRUE,
141 '^' => TRUE, '&' => TRUE, '%' => FALSE, '~' => FALSE,
142 '>' => TRUE, '<' => TRUE, '=' => TRUE, '>=' => TRUE,
143 '<=' => TRUE, '<>' => TRUE, '|' => TRUE, ':' => TRUE
144 );
145
146
153 private static $_binaryOperators = array('+' => TRUE, '-' => TRUE, '*' => TRUE, '/' => TRUE,
154 '^' => TRUE, '&' => TRUE, '>' => TRUE, '<' => TRUE,
155 '=' => TRUE, '>=' => TRUE, '<=' => TRUE, '<>' => TRUE,
156 '|' => TRUE, ':' => TRUE
157 );
158
166 private $debugLog;
167
178
186 public $formulaError = NULL;
187
196
197 private $_cellStack = array();
198
208
210
218
225 private $_savedPrecision = 14;
226
227
234 private static $_localeLanguage = 'en_us'; // US English (default locale)
235
243 private static $_validLocaleLanguages = array( 'en' // English (default language)
244 );
251 private static $_localeArgumentSeparator = ',';
252 private static $_localeFunctions = array();
253
260 public static $_localeBoolean = array( 'TRUE' => 'TRUE',
261 'FALSE' => 'FALSE',
262 'NULL' => 'NULL'
263 );
264
265
273 private static $_ExcelConstants = array('TRUE' => TRUE,
274 'FALSE' => FALSE,
275 'NULL' => NULL
276 );
277
278 // PHPExcel functions
279 private static $_PHPExcelFunctions = array( // PHPExcel functions
281 'functionCall' => 'abs',
282 'argumentCount' => '1'
283 ),
284 'ACCRINT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
285 'functionCall' => 'PHPExcel_Calculation_Financial::ACCRINT',
286 'argumentCount' => '4-7'
287 ),
288 'ACCRINTM' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
289 'functionCall' => 'PHPExcel_Calculation_Financial::ACCRINTM',
290 'argumentCount' => '3-5'
291 ),
292 'ACOS' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
293 'functionCall' => 'acos',
294 'argumentCount' => '1'
295 ),
296 'ACOSH' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
297 'functionCall' => 'acosh',
298 'argumentCount' => '1'
299 ),
301 'functionCall' => 'PHPExcel_Calculation_LookupRef::CELL_ADDRESS',
302 'argumentCount' => '2-5'
303 ),
304 'AMORDEGRC' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
305 'functionCall' => 'PHPExcel_Calculation_Financial::AMORDEGRC',
306 'argumentCount' => '6,7'
307 ),
308 'AMORLINC' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
309 'functionCall' => 'PHPExcel_Calculation_Financial::AMORLINC',
310 'argumentCount' => '6,7'
311 ),
312 'AND' => array('category' => PHPExcel_Calculation_Function::CATEGORY_LOGICAL,
313 'functionCall' => 'PHPExcel_Calculation_Logical::LOGICAL_AND',
314 'argumentCount' => '1+'
315 ),
317 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
318 'argumentCount' => '1'
319 ),
321 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
322 'argumentCount' => '1'
323 ),
324 'ASIN' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
325 'functionCall' => 'asin',
326 'argumentCount' => '1'
327 ),
328 'ASINH' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
329 'functionCall' => 'asinh',
330 'argumentCount' => '1'
331 ),
332 'ATAN' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
333 'functionCall' => 'atan',
334 'argumentCount' => '1'
335 ),
336 'ATAN2' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
337 'functionCall' => 'PHPExcel_Calculation_MathTrig::ATAN2',
338 'argumentCount' => '2'
339 ),
340 'ATANH' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
341 'functionCall' => 'atanh',
342 'argumentCount' => '1'
343 ),
344 'AVEDEV' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
345 'functionCall' => 'PHPExcel_Calculation_Statistical::AVEDEV',
346 'argumentCount' => '1+'
347 ),
348 'AVERAGE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
349 'functionCall' => 'PHPExcel_Calculation_Statistical::AVERAGE',
350 'argumentCount' => '1+'
351 ),
352 'AVERAGEA' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
353 'functionCall' => 'PHPExcel_Calculation_Statistical::AVERAGEA',
354 'argumentCount' => '1+'
355 ),
356 'AVERAGEIF' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
357 'functionCall' => 'PHPExcel_Calculation_Statistical::AVERAGEIF',
358 'argumentCount' => '2,3'
359 ),
360 'AVERAGEIFS' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
361 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
362 'argumentCount' => '3+'
363 ),
364 'BAHTTEXT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_TEXT_AND_DATA,
365 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
366 'argumentCount' => '1'
367 ),
368 'BESSELI' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
369 'functionCall' => 'PHPExcel_Calculation_Engineering::BESSELI',
370 'argumentCount' => '2'
371 ),
372 'BESSELJ' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
373 'functionCall' => 'PHPExcel_Calculation_Engineering::BESSELJ',
374 'argumentCount' => '2'
375 ),
376 'BESSELK' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
377 'functionCall' => 'PHPExcel_Calculation_Engineering::BESSELK',
378 'argumentCount' => '2'
379 ),
380 'BESSELY' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
381 'functionCall' => 'PHPExcel_Calculation_Engineering::BESSELY',
382 'argumentCount' => '2'
383 ),
384 'BETADIST' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
385 'functionCall' => 'PHPExcel_Calculation_Statistical::BETADIST',
386 'argumentCount' => '3-5'
387 ),
388 'BETAINV' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
389 'functionCall' => 'PHPExcel_Calculation_Statistical::BETAINV',
390 'argumentCount' => '3-5'
391 ),
392 'BIN2DEC' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
393 'functionCall' => 'PHPExcel_Calculation_Engineering::BINTODEC',
394 'argumentCount' => '1'
395 ),
396 'BIN2HEX' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
397 'functionCall' => 'PHPExcel_Calculation_Engineering::BINTOHEX',
398 'argumentCount' => '1,2'
399 ),
400 'BIN2OCT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
401 'functionCall' => 'PHPExcel_Calculation_Engineering::BINTOOCT',
402 'argumentCount' => '1,2'
403 ),
404 'BINOMDIST' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
405 'functionCall' => 'PHPExcel_Calculation_Statistical::BINOMDIST',
406 'argumentCount' => '4'
407 ),
408 'CEILING' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
409 'functionCall' => 'PHPExcel_Calculation_MathTrig::CEILING',
410 'argumentCount' => '2'
411 ),
412 'CELL' => array('category' => PHPExcel_Calculation_Function::CATEGORY_INFORMATION,
413 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
414 'argumentCount' => '1,2'
415 ),
416 'CHAR' => array('category' => PHPExcel_Calculation_Function::CATEGORY_TEXT_AND_DATA,
417 'functionCall' => 'PHPExcel_Calculation_TextData::CHARACTER',
418 'argumentCount' => '1'
419 ),
420 'CHIDIST' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
421 'functionCall' => 'PHPExcel_Calculation_Statistical::CHIDIST',
422 'argumentCount' => '2'
423 ),
424 'CHIINV' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
425 'functionCall' => 'PHPExcel_Calculation_Statistical::CHIINV',
426 'argumentCount' => '2'
427 ),
428 'CHITEST' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
429 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
430 'argumentCount' => '2'
431 ),
433 'functionCall' => 'PHPExcel_Calculation_LookupRef::CHOOSE',
434 'argumentCount' => '2+'
435 ),
436 'CLEAN' => array('category' => PHPExcel_Calculation_Function::CATEGORY_TEXT_AND_DATA,
437 'functionCall' => 'PHPExcel_Calculation_TextData::TRIMNONPRINTABLE',
438 'argumentCount' => '1'
439 ),
440 'CODE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_TEXT_AND_DATA,
441 'functionCall' => 'PHPExcel_Calculation_TextData::ASCIICODE',
442 'argumentCount' => '1'
443 ),
445 'functionCall' => 'PHPExcel_Calculation_LookupRef::COLUMN',
446 'argumentCount' => '-1',
447 'passByReference' => array(TRUE)
448 ),
450 'functionCall' => 'PHPExcel_Calculation_LookupRef::COLUMNS',
451 'argumentCount' => '1'
452 ),
453 'COMBIN' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
454 'functionCall' => 'PHPExcel_Calculation_MathTrig::COMBIN',
455 'argumentCount' => '2'
456 ),
457 'COMPLEX' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
458 'functionCall' => 'PHPExcel_Calculation_Engineering::COMPLEX',
459 'argumentCount' => '2,3'
460 ),
461 'CONCATENATE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_TEXT_AND_DATA,
462 'functionCall' => 'PHPExcel_Calculation_TextData::CONCATENATE',
463 'argumentCount' => '1+'
464 ),
465 'CONFIDENCE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
466 'functionCall' => 'PHPExcel_Calculation_Statistical::CONFIDENCE',
467 'argumentCount' => '3'
468 ),
469 'CONVERT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
470 'functionCall' => 'PHPExcel_Calculation_Engineering::CONVERTUOM',
471 'argumentCount' => '3'
472 ),
473 'CORREL' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
474 'functionCall' => 'PHPExcel_Calculation_Statistical::CORREL',
475 'argumentCount' => '2'
476 ),
478 'functionCall' => 'cos',
479 'argumentCount' => '1'
480 ),
481 'COSH' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
482 'functionCall' => 'cosh',
483 'argumentCount' => '1'
484 ),
485 'COUNT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
486 'functionCall' => 'PHPExcel_Calculation_Statistical::COUNT',
487 'argumentCount' => '1+'
488 ),
489 'COUNTA' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
490 'functionCall' => 'PHPExcel_Calculation_Statistical::COUNTA',
491 'argumentCount' => '1+'
492 ),
493 'COUNTBLANK' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
494 'functionCall' => 'PHPExcel_Calculation_Statistical::COUNTBLANK',
495 'argumentCount' => '1'
496 ),
497 'COUNTIF' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
498 'functionCall' => 'PHPExcel_Calculation_Statistical::COUNTIF',
499 'argumentCount' => '2'
500 ),
501 'COUNTIFS' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
502 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
503 'argumentCount' => '2'
504 ),
505 'COUPDAYBS' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
506 'functionCall' => 'PHPExcel_Calculation_Financial::COUPDAYBS',
507 'argumentCount' => '3,4'
508 ),
509 'COUPDAYS' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
510 'functionCall' => 'PHPExcel_Calculation_Financial::COUPDAYS',
511 'argumentCount' => '3,4'
512 ),
513 'COUPDAYSNC' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
514 'functionCall' => 'PHPExcel_Calculation_Financial::COUPDAYSNC',
515 'argumentCount' => '3,4'
516 ),
517 'COUPNCD' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
518 'functionCall' => 'PHPExcel_Calculation_Financial::COUPNCD',
519 'argumentCount' => '3,4'
520 ),
521 'COUPNUM' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
522 'functionCall' => 'PHPExcel_Calculation_Financial::COUPNUM',
523 'argumentCount' => '3,4'
524 ),
525 'COUPPCD' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
526 'functionCall' => 'PHPExcel_Calculation_Financial::COUPPCD',
527 'argumentCount' => '3,4'
528 ),
529 'COVAR' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
530 'functionCall' => 'PHPExcel_Calculation_Statistical::COVAR',
531 'argumentCount' => '2'
532 ),
533 'CRITBINOM' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
534 'functionCall' => 'PHPExcel_Calculation_Statistical::CRITBINOM',
535 'argumentCount' => '3'
536 ),
537 'CUBEKPIMEMBER' => array('category' => PHPExcel_Calculation_Function::CATEGORY_CUBE,
538 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
539 'argumentCount' => '?'
540 ),
541 'CUBEMEMBER' => array('category' => PHPExcel_Calculation_Function::CATEGORY_CUBE,
542 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
543 'argumentCount' => '?'
544 ),
545 'CUBEMEMBERPROPERTY' => array('category' => PHPExcel_Calculation_Function::CATEGORY_CUBE,
546 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
547 'argumentCount' => '?'
548 ),
549 'CUBERANKEDMEMBER' => array('category' => PHPExcel_Calculation_Function::CATEGORY_CUBE,
550 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
551 'argumentCount' => '?'
552 ),
553 'CUBESET' => array('category' => PHPExcel_Calculation_Function::CATEGORY_CUBE,
554 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
555 'argumentCount' => '?'
556 ),
557 'CUBESETCOUNT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_CUBE,
558 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
559 'argumentCount' => '?'
560 ),
561 'CUBEVALUE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_CUBE,
562 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
563 'argumentCount' => '?'
564 ),
565 'CUMIPMT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
566 'functionCall' => 'PHPExcel_Calculation_Financial::CUMIPMT',
567 'argumentCount' => '6'
568 ),
569 'CUMPRINC' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
570 'functionCall' => 'PHPExcel_Calculation_Financial::CUMPRINC',
571 'argumentCount' => '6'
572 ),
573 'DATE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_DATE_AND_TIME,
574 'functionCall' => 'PHPExcel_Calculation_DateTime::DATE',
575 'argumentCount' => '3'
576 ),
577 'DATEDIF' => array('category' => PHPExcel_Calculation_Function::CATEGORY_DATE_AND_TIME,
578 'functionCall' => 'PHPExcel_Calculation_DateTime::DATEDIF',
579 'argumentCount' => '2,3'
580 ),
581 'DATEVALUE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_DATE_AND_TIME,
582 'functionCall' => 'PHPExcel_Calculation_DateTime::DATEVALUE',
583 'argumentCount' => '1'
584 ),
585 'DAVERAGE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_DATABASE,
586 'functionCall' => 'PHPExcel_Calculation_Database::DAVERAGE',
587 'argumentCount' => '3'
588 ),
590 'functionCall' => 'PHPExcel_Calculation_DateTime::DAYOFMONTH',
591 'argumentCount' => '1'
592 ),
593 'DAYS360' => array('category' => PHPExcel_Calculation_Function::CATEGORY_DATE_AND_TIME,
594 'functionCall' => 'PHPExcel_Calculation_DateTime::DAYS360',
595 'argumentCount' => '2,3'
596 ),
597 'DB' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
598 'functionCall' => 'PHPExcel_Calculation_Financial::DB',
599 'argumentCount' => '4,5'
600 ),
601 'DCOUNT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_DATABASE,
602 'functionCall' => 'PHPExcel_Calculation_Database::DCOUNT',
603 'argumentCount' => '3'
604 ),
605 'DCOUNTA' => array('category' => PHPExcel_Calculation_Function::CATEGORY_DATABASE,
606 'functionCall' => 'PHPExcel_Calculation_Database::DCOUNTA',
607 'argumentCount' => '3'
608 ),
609 'DDB' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
610 'functionCall' => 'PHPExcel_Calculation_Financial::DDB',
611 'argumentCount' => '4,5'
612 ),
613 'DEC2BIN' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
614 'functionCall' => 'PHPExcel_Calculation_Engineering::DECTOBIN',
615 'argumentCount' => '1,2'
616 ),
617 'DEC2HEX' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
618 'functionCall' => 'PHPExcel_Calculation_Engineering::DECTOHEX',
619 'argumentCount' => '1,2'
620 ),
621 'DEC2OCT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
622 'functionCall' => 'PHPExcel_Calculation_Engineering::DECTOOCT',
623 'argumentCount' => '1,2'
624 ),
625 'DEGREES' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
626 'functionCall' => 'rad2deg',
627 'argumentCount' => '1'
628 ),
629 'DELTA' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
630 'functionCall' => 'PHPExcel_Calculation_Engineering::DELTA',
631 'argumentCount' => '1,2'
632 ),
633 'DEVSQ' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
634 'functionCall' => 'PHPExcel_Calculation_Statistical::DEVSQ',
635 'argumentCount' => '1+'
636 ),
637 'DGET' => array('category' => PHPExcel_Calculation_Function::CATEGORY_DATABASE,
638 'functionCall' => 'PHPExcel_Calculation_Database::DGET',
639 'argumentCount' => '3'
640 ),
641 'DISC' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
642 'functionCall' => 'PHPExcel_Calculation_Financial::DISC',
643 'argumentCount' => '4,5'
644 ),
645 'DMAX' => array('category' => PHPExcel_Calculation_Function::CATEGORY_DATABASE,
646 'functionCall' => 'PHPExcel_Calculation_Database::DMAX',
647 'argumentCount' => '3'
648 ),
649 'DMIN' => array('category' => PHPExcel_Calculation_Function::CATEGORY_DATABASE,
650 'functionCall' => 'PHPExcel_Calculation_Database::DMIN',
651 'argumentCount' => '3'
652 ),
653 'DOLLAR' => array('category' => PHPExcel_Calculation_Function::CATEGORY_TEXT_AND_DATA,
654 'functionCall' => 'PHPExcel_Calculation_TextData::DOLLAR',
655 'argumentCount' => '1,2'
656 ),
657 'DOLLARDE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
658 'functionCall' => 'PHPExcel_Calculation_Financial::DOLLARDE',
659 'argumentCount' => '2'
660 ),
661 'DOLLARFR' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
662 'functionCall' => 'PHPExcel_Calculation_Financial::DOLLARFR',
663 'argumentCount' => '2'
664 ),
665 'DPRODUCT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_DATABASE,
666 'functionCall' => 'PHPExcel_Calculation_Database::DPRODUCT',
667 'argumentCount' => '3'
668 ),
669 'DSTDEV' => array('category' => PHPExcel_Calculation_Function::CATEGORY_DATABASE,
670 'functionCall' => 'PHPExcel_Calculation_Database::DSTDEV',
671 'argumentCount' => '3'
672 ),
673 'DSTDEVP' => array('category' => PHPExcel_Calculation_Function::CATEGORY_DATABASE,
674 'functionCall' => 'PHPExcel_Calculation_Database::DSTDEVP',
675 'argumentCount' => '3'
676 ),
677 'DSUM' => array('category' => PHPExcel_Calculation_Function::CATEGORY_DATABASE,
678 'functionCall' => 'PHPExcel_Calculation_Database::DSUM',
679 'argumentCount' => '3'
680 ),
681 'DURATION' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
682 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
683 'argumentCount' => '5,6'
684 ),
685 'DVAR' => array('category' => PHPExcel_Calculation_Function::CATEGORY_DATABASE,
686 'functionCall' => 'PHPExcel_Calculation_Database::DVAR',
687 'argumentCount' => '3'
688 ),
689 'DVARP' => array('category' => PHPExcel_Calculation_Function::CATEGORY_DATABASE,
690 'functionCall' => 'PHPExcel_Calculation_Database::DVARP',
691 'argumentCount' => '3'
692 ),
693 'EDATE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_DATE_AND_TIME,
694 'functionCall' => 'PHPExcel_Calculation_DateTime::EDATE',
695 'argumentCount' => '2'
696 ),
697 'EFFECT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
698 'functionCall' => 'PHPExcel_Calculation_Financial::EFFECT',
699 'argumentCount' => '2'
700 ),
701 'EOMONTH' => array('category' => PHPExcel_Calculation_Function::CATEGORY_DATE_AND_TIME,
702 'functionCall' => 'PHPExcel_Calculation_DateTime::EOMONTH',
703 'argumentCount' => '2'
704 ),
705 'ERF' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
706 'functionCall' => 'PHPExcel_Calculation_Engineering::ERF',
707 'argumentCount' => '1,2'
708 ),
709 'ERFC' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
710 'functionCall' => 'PHPExcel_Calculation_Engineering::ERFC',
711 'argumentCount' => '1'
712 ),
713 'ERROR.TYPE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_INFORMATION,
714 'functionCall' => 'PHPExcel_Calculation_Functions::ERROR_TYPE',
715 'argumentCount' => '1'
716 ),
717 'EVEN' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
718 'functionCall' => 'PHPExcel_Calculation_MathTrig::EVEN',
719 'argumentCount' => '1'
720 ),
721 'EXACT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_TEXT_AND_DATA,
722 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
723 'argumentCount' => '2'
724 ),
726 'functionCall' => 'exp',
727 'argumentCount' => '1'
728 ),
729 'EXPONDIST' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
730 'functionCall' => 'PHPExcel_Calculation_Statistical::EXPONDIST',
731 'argumentCount' => '3'
732 ),
733 'FACT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
734 'functionCall' => 'PHPExcel_Calculation_MathTrig::FACT',
735 'argumentCount' => '1'
736 ),
737 'FACTDOUBLE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
738 'functionCall' => 'PHPExcel_Calculation_MathTrig::FACTDOUBLE',
739 'argumentCount' => '1'
740 ),
741 'FALSE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_LOGICAL,
742 'functionCall' => 'PHPExcel_Calculation_Logical::FALSE',
743 'argumentCount' => '0'
744 ),
745 'FDIST' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
746 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
747 'argumentCount' => '3'
748 ),
749 'FIND' => array('category' => PHPExcel_Calculation_Function::CATEGORY_TEXT_AND_DATA,
750 'functionCall' => 'PHPExcel_Calculation_TextData::SEARCHSENSITIVE',
751 'argumentCount' => '2,3'
752 ),
753 'FINDB' => array('category' => PHPExcel_Calculation_Function::CATEGORY_TEXT_AND_DATA,
754 'functionCall' => 'PHPExcel_Calculation_TextData::SEARCHSENSITIVE',
755 'argumentCount' => '2,3'
756 ),
757 'FINV' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
758 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
759 'argumentCount' => '3'
760 ),
761 'FISHER' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
762 'functionCall' => 'PHPExcel_Calculation_Statistical::FISHER',
763 'argumentCount' => '1'
764 ),
765 'FISHERINV' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
766 'functionCall' => 'PHPExcel_Calculation_Statistical::FISHERINV',
767 'argumentCount' => '1'
768 ),
769 'FIXED' => array('category' => PHPExcel_Calculation_Function::CATEGORY_TEXT_AND_DATA,
770 'functionCall' => 'PHPExcel_Calculation_TextData::FIXEDFORMAT',
771 'argumentCount' => '1-3'
772 ),
773 'FLOOR' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
774 'functionCall' => 'PHPExcel_Calculation_MathTrig::FLOOR',
775 'argumentCount' => '2'
776 ),
777 'FORECAST' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
778 'functionCall' => 'PHPExcel_Calculation_Statistical::FORECAST',
779 'argumentCount' => '3'
780 ),
781 'FREQUENCY' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
782 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
783 'argumentCount' => '2'
784 ),
785 'FTEST' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
786 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
787 'argumentCount' => '2'
788 ),
789 'FV' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
790 'functionCall' => 'PHPExcel_Calculation_Financial::FV',
791 'argumentCount' => '3-5'
792 ),
793 'FVSCHEDULE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
794 'functionCall' => 'PHPExcel_Calculation_Financial::FVSCHEDULE',
795 'argumentCount' => '2'
796 ),
797 'GAMMADIST' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
798 'functionCall' => 'PHPExcel_Calculation_Statistical::GAMMADIST',
799 'argumentCount' => '4'
800 ),
801 'GAMMAINV' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
802 'functionCall' => 'PHPExcel_Calculation_Statistical::GAMMAINV',
803 'argumentCount' => '3'
804 ),
805 'GAMMALN' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
806 'functionCall' => 'PHPExcel_Calculation_Statistical::GAMMALN',
807 'argumentCount' => '1'
808 ),
810 'functionCall' => 'PHPExcel_Calculation_MathTrig::GCD',
811 'argumentCount' => '1+'
812 ),
813 'GEOMEAN' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
814 'functionCall' => 'PHPExcel_Calculation_Statistical::GEOMEAN',
815 'argumentCount' => '1+'
816 ),
817 'GESTEP' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
818 'functionCall' => 'PHPExcel_Calculation_Engineering::GESTEP',
819 'argumentCount' => '1,2'
820 ),
821 'GETPIVOTDATA' => array('category' => PHPExcel_Calculation_Function::CATEGORY_LOOKUP_AND_REFERENCE,
822 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
823 'argumentCount' => '2+'
824 ),
825 'GROWTH' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
826 'functionCall' => 'PHPExcel_Calculation_Statistical::GROWTH',
827 'argumentCount' => '1-4'
828 ),
829 'HARMEAN' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
830 'functionCall' => 'PHPExcel_Calculation_Statistical::HARMEAN',
831 'argumentCount' => '1+'
832 ),
833 'HEX2BIN' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
834 'functionCall' => 'PHPExcel_Calculation_Engineering::HEXTOBIN',
835 'argumentCount' => '1,2'
836 ),
837 'HEX2DEC' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
838 'functionCall' => 'PHPExcel_Calculation_Engineering::HEXTODEC',
839 'argumentCount' => '1'
840 ),
841 'HEX2OCT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
842 'functionCall' => 'PHPExcel_Calculation_Engineering::HEXTOOCT',
843 'argumentCount' => '1,2'
844 ),
846 'functionCall' => 'PHPExcel_Calculation_LookupRef::HLOOKUP',
847 'argumentCount' => '3,4'
848 ),
849 'HOUR' => array('category' => PHPExcel_Calculation_Function::CATEGORY_DATE_AND_TIME,
850 'functionCall' => 'PHPExcel_Calculation_DateTime::HOUROFDAY',
851 'argumentCount' => '1'
852 ),
853 'HYPERLINK' => array('category' => PHPExcel_Calculation_Function::CATEGORY_LOOKUP_AND_REFERENCE,
854 'functionCall' => 'PHPExcel_Calculation_LookupRef::HYPERLINK',
855 'argumentCount' => '1,2',
856 'passCellReference'=> TRUE
857 ),
858 'HYPGEOMDIST' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
859 'functionCall' => 'PHPExcel_Calculation_Statistical::HYPGEOMDIST',
860 'argumentCount' => '4'
861 ),
862 'IF' => array('category' => PHPExcel_Calculation_Function::CATEGORY_LOGICAL,
863 'functionCall' => 'PHPExcel_Calculation_Logical::STATEMENT_IF',
864 'argumentCount' => '1-3'
865 ),
866 'IFERROR' => array('category' => PHPExcel_Calculation_Function::CATEGORY_LOGICAL,
867 'functionCall' => 'PHPExcel_Calculation_Logical::IFERROR',
868 'argumentCount' => '2'
869 ),
870 'IMABS' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
871 'functionCall' => 'PHPExcel_Calculation_Engineering::IMABS',
872 'argumentCount' => '1'
873 ),
874 'IMAGINARY' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
875 'functionCall' => 'PHPExcel_Calculation_Engineering::IMAGINARY',
876 'argumentCount' => '1'
877 ),
878 'IMARGUMENT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
879 'functionCall' => 'PHPExcel_Calculation_Engineering::IMARGUMENT',
880 'argumentCount' => '1'
881 ),
882 'IMCONJUGATE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
883 'functionCall' => 'PHPExcel_Calculation_Engineering::IMCONJUGATE',
884 'argumentCount' => '1'
885 ),
886 'IMCOS' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
887 'functionCall' => 'PHPExcel_Calculation_Engineering::IMCOS',
888 'argumentCount' => '1'
889 ),
890 'IMDIV' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
891 'functionCall' => 'PHPExcel_Calculation_Engineering::IMDIV',
892 'argumentCount' => '2'
893 ),
894 'IMEXP' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
895 'functionCall' => 'PHPExcel_Calculation_Engineering::IMEXP',
896 'argumentCount' => '1'
897 ),
898 'IMLN' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
899 'functionCall' => 'PHPExcel_Calculation_Engineering::IMLN',
900 'argumentCount' => '1'
901 ),
902 'IMLOG10' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
903 'functionCall' => 'PHPExcel_Calculation_Engineering::IMLOG10',
904 'argumentCount' => '1'
905 ),
906 'IMLOG2' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
907 'functionCall' => 'PHPExcel_Calculation_Engineering::IMLOG2',
908 'argumentCount' => '1'
909 ),
910 'IMPOWER' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
911 'functionCall' => 'PHPExcel_Calculation_Engineering::IMPOWER',
912 'argumentCount' => '2'
913 ),
914 'IMPRODUCT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
915 'functionCall' => 'PHPExcel_Calculation_Engineering::IMPRODUCT',
916 'argumentCount' => '1+'
917 ),
918 'IMREAL' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
919 'functionCall' => 'PHPExcel_Calculation_Engineering::IMREAL',
920 'argumentCount' => '1'
921 ),
922 'IMSIN' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
923 'functionCall' => 'PHPExcel_Calculation_Engineering::IMSIN',
924 'argumentCount' => '1'
925 ),
926 'IMSQRT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
927 'functionCall' => 'PHPExcel_Calculation_Engineering::IMSQRT',
928 'argumentCount' => '1'
929 ),
930 'IMSUB' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
931 'functionCall' => 'PHPExcel_Calculation_Engineering::IMSUB',
932 'argumentCount' => '2'
933 ),
934 'IMSUM' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
935 'functionCall' => 'PHPExcel_Calculation_Engineering::IMSUM',
936 'argumentCount' => '1+'
937 ),
939 'functionCall' => 'PHPExcel_Calculation_LookupRef::INDEX',
940 'argumentCount' => '1-4'
941 ),
942 'INDIRECT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_LOOKUP_AND_REFERENCE,
943 'functionCall' => 'PHPExcel_Calculation_LookupRef::INDIRECT',
944 'argumentCount' => '1,2',
945 'passCellReference'=> TRUE
946 ),
947 'INFO' => array('category' => PHPExcel_Calculation_Function::CATEGORY_INFORMATION,
948 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
949 'argumentCount' => '1'
950 ),
952 'functionCall' => 'PHPExcel_Calculation_MathTrig::INT',
953 'argumentCount' => '1'
954 ),
955 'INTERCEPT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
956 'functionCall' => 'PHPExcel_Calculation_Statistical::INTERCEPT',
957 'argumentCount' => '2'
958 ),
959 'INTRATE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
960 'functionCall' => 'PHPExcel_Calculation_Financial::INTRATE',
961 'argumentCount' => '4,5'
962 ),
963 'IPMT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
964 'functionCall' => 'PHPExcel_Calculation_Financial::IPMT',
965 'argumentCount' => '4-6'
966 ),
967 'IRR' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
968 'functionCall' => 'PHPExcel_Calculation_Financial::IRR',
969 'argumentCount' => '1,2'
970 ),
971 'ISBLANK' => array('category' => PHPExcel_Calculation_Function::CATEGORY_INFORMATION,
972 'functionCall' => 'PHPExcel_Calculation_Functions::IS_BLANK',
973 'argumentCount' => '1'
974 ),
975 'ISERR' => array('category' => PHPExcel_Calculation_Function::CATEGORY_INFORMATION,
976 'functionCall' => 'PHPExcel_Calculation_Functions::IS_ERR',
977 'argumentCount' => '1'
978 ),
979 'ISERROR' => array('category' => PHPExcel_Calculation_Function::CATEGORY_INFORMATION,
980 'functionCall' => 'PHPExcel_Calculation_Functions::IS_ERROR',
981 'argumentCount' => '1'
982 ),
983 'ISEVEN' => array('category' => PHPExcel_Calculation_Function::CATEGORY_INFORMATION,
984 'functionCall' => 'PHPExcel_Calculation_Functions::IS_EVEN',
985 'argumentCount' => '1'
986 ),
987 'ISLOGICAL' => array('category' => PHPExcel_Calculation_Function::CATEGORY_INFORMATION,
988 'functionCall' => 'PHPExcel_Calculation_Functions::IS_LOGICAL',
989 'argumentCount' => '1'
990 ),
991 'ISNA' => array('category' => PHPExcel_Calculation_Function::CATEGORY_INFORMATION,
992 'functionCall' => 'PHPExcel_Calculation_Functions::IS_NA',
993 'argumentCount' => '1'
994 ),
995 'ISNONTEXT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_INFORMATION,
996 'functionCall' => 'PHPExcel_Calculation_Functions::IS_NONTEXT',
997 'argumentCount' => '1'
998 ),
999 'ISNUMBER' => array('category' => PHPExcel_Calculation_Function::CATEGORY_INFORMATION,
1000 'functionCall' => 'PHPExcel_Calculation_Functions::IS_NUMBER',
1001 'argumentCount' => '1'
1002 ),
1003 'ISODD' => array('category' => PHPExcel_Calculation_Function::CATEGORY_INFORMATION,
1004 'functionCall' => 'PHPExcel_Calculation_Functions::IS_ODD',
1005 'argumentCount' => '1'
1006 ),
1007 'ISPMT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
1008 'functionCall' => 'PHPExcel_Calculation_Financial::ISPMT',
1009 'argumentCount' => '4'
1010 ),
1011 'ISREF' => array('category' => PHPExcel_Calculation_Function::CATEGORY_INFORMATION,
1012 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
1013 'argumentCount' => '1'
1014 ),
1015 'ISTEXT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_INFORMATION,
1016 'functionCall' => 'PHPExcel_Calculation_Functions::IS_TEXT',
1017 'argumentCount' => '1'
1018 ),
1019 'JIS' => array('category' => PHPExcel_Calculation_Function::CATEGORY_TEXT_AND_DATA,
1020 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
1021 'argumentCount' => '1'
1022 ),
1023 'KURT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
1024 'functionCall' => 'PHPExcel_Calculation_Statistical::KURT',
1025 'argumentCount' => '1+'
1026 ),
1027 'LARGE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
1028 'functionCall' => 'PHPExcel_Calculation_Statistical::LARGE',
1029 'argumentCount' => '2'
1030 ),
1031 'LCM' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
1032 'functionCall' => 'PHPExcel_Calculation_MathTrig::LCM',
1033 'argumentCount' => '1+'
1034 ),
1035 'LEFT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_TEXT_AND_DATA,
1036 'functionCall' => 'PHPExcel_Calculation_TextData::LEFT',
1037 'argumentCount' => '1,2'
1038 ),
1039 'LEFTB' => array('category' => PHPExcel_Calculation_Function::CATEGORY_TEXT_AND_DATA,
1040 'functionCall' => 'PHPExcel_Calculation_TextData::LEFT',
1041 'argumentCount' => '1,2'
1042 ),
1043 'LEN' => array('category' => PHPExcel_Calculation_Function::CATEGORY_TEXT_AND_DATA,
1044 'functionCall' => 'PHPExcel_Calculation_TextData::STRINGLENGTH',
1045 'argumentCount' => '1'
1046 ),
1047 'LENB' => array('category' => PHPExcel_Calculation_Function::CATEGORY_TEXT_AND_DATA,
1048 'functionCall' => 'PHPExcel_Calculation_TextData::STRINGLENGTH',
1049 'argumentCount' => '1'
1050 ),
1051 'LINEST' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
1052 'functionCall' => 'PHPExcel_Calculation_Statistical::LINEST',
1053 'argumentCount' => '1-4'
1054 ),
1056 'functionCall' => 'log',
1057 'argumentCount' => '1'
1058 ),
1059 'LOG' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
1060 'functionCall' => 'PHPExcel_Calculation_MathTrig::LOG_BASE',
1061 'argumentCount' => '1,2'
1062 ),
1063 'LOG10' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
1064 'functionCall' => 'log10',
1065 'argumentCount' => '1'
1066 ),
1067 'LOGEST' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
1068 'functionCall' => 'PHPExcel_Calculation_Statistical::LOGEST',
1069 'argumentCount' => '1-4'
1070 ),
1071 'LOGINV' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
1072 'functionCall' => 'PHPExcel_Calculation_Statistical::LOGINV',
1073 'argumentCount' => '3'
1074 ),
1075 'LOGNORMDIST' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
1076 'functionCall' => 'PHPExcel_Calculation_Statistical::LOGNORMDIST',
1077 'argumentCount' => '3'
1078 ),
1080 'functionCall' => 'PHPExcel_Calculation_LookupRef::LOOKUP',
1081 'argumentCount' => '2,3'
1082 ),
1083 'LOWER' => array('category' => PHPExcel_Calculation_Function::CATEGORY_TEXT_AND_DATA,
1084 'functionCall' => 'PHPExcel_Calculation_TextData::LOWERCASE',
1085 'argumentCount' => '1'
1086 ),
1088 'functionCall' => 'PHPExcel_Calculation_LookupRef::MATCH',
1089 'argumentCount' => '2,3'
1090 ),
1091 'MAX' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
1092 'functionCall' => 'PHPExcel_Calculation_Statistical::MAX',
1093 'argumentCount' => '1+'
1094 ),
1095 'MAXA' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
1096 'functionCall' => 'PHPExcel_Calculation_Statistical::MAXA',
1097 'argumentCount' => '1+'
1098 ),
1099 'MAXIF' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
1100 'functionCall' => 'PHPExcel_Calculation_Statistical::MAXIF',
1101 'argumentCount' => '2+'
1102 ),
1103 'MDETERM' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
1104 'functionCall' => 'PHPExcel_Calculation_MathTrig::MDETERM',
1105 'argumentCount' => '1'
1106 ),
1107 'MDURATION' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
1108 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
1109 'argumentCount' => '5,6'
1110 ),
1111 'MEDIAN' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
1112 'functionCall' => 'PHPExcel_Calculation_Statistical::MEDIAN',
1113 'argumentCount' => '1+'
1114 ),
1115 'MEDIANIF' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
1116 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
1117 'argumentCount' => '2+'
1118 ),
1119 'MID' => array('category' => PHPExcel_Calculation_Function::CATEGORY_TEXT_AND_DATA,
1120 'functionCall' => 'PHPExcel_Calculation_TextData::MID',
1121 'argumentCount' => '3'
1122 ),
1123 'MIDB' => array('category' => PHPExcel_Calculation_Function::CATEGORY_TEXT_AND_DATA,
1124 'functionCall' => 'PHPExcel_Calculation_TextData::MID',
1125 'argumentCount' => '3'
1126 ),
1127 'MIN' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
1128 'functionCall' => 'PHPExcel_Calculation_Statistical::MIN',
1129 'argumentCount' => '1+'
1130 ),
1131 'MINA' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
1132 'functionCall' => 'PHPExcel_Calculation_Statistical::MINA',
1133 'argumentCount' => '1+'
1134 ),
1135 'MINIF' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
1136 'functionCall' => 'PHPExcel_Calculation_Statistical::MINIF',
1137 'argumentCount' => '2+'
1138 ),
1139 'MINUTE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_DATE_AND_TIME,
1140 'functionCall' => 'PHPExcel_Calculation_DateTime::MINUTEOFHOUR',
1141 'argumentCount' => '1'
1142 ),
1143 'MINVERSE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
1144 'functionCall' => 'PHPExcel_Calculation_MathTrig::MINVERSE',
1145 'argumentCount' => '1'
1146 ),
1147 'MIRR' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
1148 'functionCall' => 'PHPExcel_Calculation_Financial::MIRR',
1149 'argumentCount' => '3'
1150 ),
1151 'MMULT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
1152 'functionCall' => 'PHPExcel_Calculation_MathTrig::MMULT',
1153 'argumentCount' => '2'
1154 ),
1155 'MOD' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
1156 'functionCall' => 'PHPExcel_Calculation_MathTrig::MOD',
1157 'argumentCount' => '2'
1158 ),
1159 'MODE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
1160 'functionCall' => 'PHPExcel_Calculation_Statistical::MODE',
1161 'argumentCount' => '1+'
1162 ),
1163 'MONTH' => array('category' => PHPExcel_Calculation_Function::CATEGORY_DATE_AND_TIME,
1164 'functionCall' => 'PHPExcel_Calculation_DateTime::MONTHOFYEAR',
1165 'argumentCount' => '1'
1166 ),
1167 'MROUND' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
1168 'functionCall' => 'PHPExcel_Calculation_MathTrig::MROUND',
1169 'argumentCount' => '2'
1170 ),
1171 'MULTINOMIAL' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
1172 'functionCall' => 'PHPExcel_Calculation_MathTrig::MULTINOMIAL',
1173 'argumentCount' => '1+'
1174 ),
1175 'N' => array('category' => PHPExcel_Calculation_Function::CATEGORY_INFORMATION,
1176 'functionCall' => 'PHPExcel_Calculation_Functions::N',
1177 'argumentCount' => '1'
1178 ),
1179 'NA' => array('category' => PHPExcel_Calculation_Function::CATEGORY_INFORMATION,
1180 'functionCall' => 'PHPExcel_Calculation_Functions::NA',
1181 'argumentCount' => '0'
1182 ),
1183 'NEGBINOMDIST' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
1184 'functionCall' => 'PHPExcel_Calculation_Statistical::NEGBINOMDIST',
1185 'argumentCount' => '3'
1186 ),
1187 'NETWORKDAYS' => array('category' => PHPExcel_Calculation_Function::CATEGORY_DATE_AND_TIME,
1188 'functionCall' => 'PHPExcel_Calculation_DateTime::NETWORKDAYS',
1189 'argumentCount' => '2+'
1190 ),
1191 'NOMINAL' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
1192 'functionCall' => 'PHPExcel_Calculation_Financial::NOMINAL',
1193 'argumentCount' => '2'
1194 ),
1195 'NORMDIST' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
1196 'functionCall' => 'PHPExcel_Calculation_Statistical::NORMDIST',
1197 'argumentCount' => '4'
1198 ),
1199 'NORMINV' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
1200 'functionCall' => 'PHPExcel_Calculation_Statistical::NORMINV',
1201 'argumentCount' => '3'
1202 ),
1203 'NORMSDIST' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
1204 'functionCall' => 'PHPExcel_Calculation_Statistical::NORMSDIST',
1205 'argumentCount' => '1'
1206 ),
1207 'NORMSINV' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
1208 'functionCall' => 'PHPExcel_Calculation_Statistical::NORMSINV',
1209 'argumentCount' => '1'
1210 ),
1211 'NOT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_LOGICAL,
1212 'functionCall' => 'PHPExcel_Calculation_Logical::NOT',
1213 'argumentCount' => '1'
1214 ),
1215 'NOW' => array('category' => PHPExcel_Calculation_Function::CATEGORY_DATE_AND_TIME,
1216 'functionCall' => 'PHPExcel_Calculation_DateTime::DATETIMENOW',
1217 'argumentCount' => '0'
1218 ),
1219 'NPER' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
1220 'functionCall' => 'PHPExcel_Calculation_Financial::NPER',
1221 'argumentCount' => '3-5'
1222 ),
1223 'NPV' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
1224 'functionCall' => 'PHPExcel_Calculation_Financial::NPV',
1225 'argumentCount' => '2+'
1226 ),
1227 'OCT2BIN' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
1228 'functionCall' => 'PHPExcel_Calculation_Engineering::OCTTOBIN',
1229 'argumentCount' => '1,2'
1230 ),
1231 'OCT2DEC' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
1232 'functionCall' => 'PHPExcel_Calculation_Engineering::OCTTODEC',
1233 'argumentCount' => '1'
1234 ),
1235 'OCT2HEX' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
1236 'functionCall' => 'PHPExcel_Calculation_Engineering::OCTTOHEX',
1237 'argumentCount' => '1,2'
1238 ),
1239 'ODD' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
1240 'functionCall' => 'PHPExcel_Calculation_MathTrig::ODD',
1241 'argumentCount' => '1'
1242 ),
1243 'ODDFPRICE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
1244 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
1245 'argumentCount' => '8,9'
1246 ),
1247 'ODDFYIELD' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
1248 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
1249 'argumentCount' => '8,9'
1250 ),
1251 'ODDLPRICE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
1252 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
1253 'argumentCount' => '7,8'
1254 ),
1255 'ODDLYIELD' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
1256 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
1257 'argumentCount' => '7,8'
1258 ),
1260 'functionCall' => 'PHPExcel_Calculation_LookupRef::OFFSET',
1261 'argumentCount' => '3,5',
1262 'passCellReference'=> TRUE,
1263 'passByReference' => array(TRUE)
1264 ),
1265 'OR' => array('category' => PHPExcel_Calculation_Function::CATEGORY_LOGICAL,
1266 'functionCall' => 'PHPExcel_Calculation_Logical::LOGICAL_OR',
1267 'argumentCount' => '1+'
1268 ),
1269 'PEARSON' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
1270 'functionCall' => 'PHPExcel_Calculation_Statistical::CORREL',
1271 'argumentCount' => '2'
1272 ),
1273 'PERCENTILE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
1274 'functionCall' => 'PHPExcel_Calculation_Statistical::PERCENTILE',
1275 'argumentCount' => '2'
1276 ),
1277 'PERCENTRANK' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
1278 'functionCall' => 'PHPExcel_Calculation_Statistical::PERCENTRANK',
1279 'argumentCount' => '2,3'
1280 ),
1281 'PERMUT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
1282 'functionCall' => 'PHPExcel_Calculation_Statistical::PERMUT',
1283 'argumentCount' => '2'
1284 ),
1285 'PHONETIC' => array('category' => PHPExcel_Calculation_Function::CATEGORY_TEXT_AND_DATA,
1286 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
1287 'argumentCount' => '1'
1288 ),
1290 'functionCall' => 'pi',
1291 'argumentCount' => '0'
1292 ),
1293 'PMT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
1294 'functionCall' => 'PHPExcel_Calculation_Financial::PMT',
1295 'argumentCount' => '3-5'
1296 ),
1297 'POISSON' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
1298 'functionCall' => 'PHPExcel_Calculation_Statistical::POISSON',
1299 'argumentCount' => '3'
1300 ),
1301 'POWER' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
1302 'functionCall' => 'PHPExcel_Calculation_MathTrig::POWER',
1303 'argumentCount' => '2'
1304 ),
1305 'PPMT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
1306 'functionCall' => 'PHPExcel_Calculation_Financial::PPMT',
1307 'argumentCount' => '4-6'
1308 ),
1309 'PRICE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
1310 'functionCall' => 'PHPExcel_Calculation_Financial::PRICE',
1311 'argumentCount' => '6,7'
1312 ),
1313 'PRICEDISC' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
1314 'functionCall' => 'PHPExcel_Calculation_Financial::PRICEDISC',
1315 'argumentCount' => '4,5'
1316 ),
1317 'PRICEMAT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
1318 'functionCall' => 'PHPExcel_Calculation_Financial::PRICEMAT',
1319 'argumentCount' => '5,6'
1320 ),
1321 'PROB' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
1322 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
1323 'argumentCount' => '3,4'
1324 ),
1325 'PRODUCT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
1326 'functionCall' => 'PHPExcel_Calculation_MathTrig::PRODUCT',
1327 'argumentCount' => '1+'
1328 ),
1329 'PROPER' => array('category' => PHPExcel_Calculation_Function::CATEGORY_TEXT_AND_DATA,
1330 'functionCall' => 'PHPExcel_Calculation_TextData::PROPERCASE',
1331 'argumentCount' => '1'
1332 ),
1333 'PV' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
1334 'functionCall' => 'PHPExcel_Calculation_Financial::PV',
1335 'argumentCount' => '3-5'
1336 ),
1337 'QUARTILE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
1338 'functionCall' => 'PHPExcel_Calculation_Statistical::QUARTILE',
1339 'argumentCount' => '2'
1340 ),
1341 'QUOTIENT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
1342 'functionCall' => 'PHPExcel_Calculation_MathTrig::QUOTIENT',
1343 'argumentCount' => '2'
1344 ),
1345 'RADIANS' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
1346 'functionCall' => 'deg2rad',
1347 'argumentCount' => '1'
1348 ),
1349 'RAND' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
1350 'functionCall' => 'PHPExcel_Calculation_MathTrig::RAND',
1351 'argumentCount' => '0'
1352 ),
1353 'RANDBETWEEN' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
1354 'functionCall' => 'PHPExcel_Calculation_MathTrig::RAND',
1355 'argumentCount' => '2'
1356 ),
1357 'RANK' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
1358 'functionCall' => 'PHPExcel_Calculation_Statistical::RANK',
1359 'argumentCount' => '2,3'
1360 ),
1361 'RATE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
1362 'functionCall' => 'PHPExcel_Calculation_Financial::RATE',
1363 'argumentCount' => '3-6'
1364 ),
1365 'RECEIVED' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
1366 'functionCall' => 'PHPExcel_Calculation_Financial::RECEIVED',
1367 'argumentCount' => '4-5'
1368 ),
1369 'REPLACE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_TEXT_AND_DATA,
1370 'functionCall' => 'PHPExcel_Calculation_TextData::REPLACE',
1371 'argumentCount' => '4'
1372 ),
1373 'REPLACEB' => array('category' => PHPExcel_Calculation_Function::CATEGORY_TEXT_AND_DATA,
1374 'functionCall' => 'PHPExcel_Calculation_TextData::REPLACE',
1375 'argumentCount' => '4'
1376 ),
1377 'REPT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_TEXT_AND_DATA,
1378 'functionCall' => 'str_repeat',
1379 'argumentCount' => '2'
1380 ),
1381 'RIGHT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_TEXT_AND_DATA,
1382 'functionCall' => 'PHPExcel_Calculation_TextData::RIGHT',
1383 'argumentCount' => '1,2'
1384 ),
1385 'RIGHTB' => array('category' => PHPExcel_Calculation_Function::CATEGORY_TEXT_AND_DATA,
1386 'functionCall' => 'PHPExcel_Calculation_TextData::RIGHT',
1387 'argumentCount' => '1,2'
1388 ),
1389 'ROMAN' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
1390 'functionCall' => 'PHPExcel_Calculation_MathTrig::ROMAN',
1391 'argumentCount' => '1,2'
1392 ),
1393 'ROUND' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
1394 'functionCall' => 'round',
1395 'argumentCount' => '2'
1396 ),
1397 'ROUNDDOWN' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
1398 'functionCall' => 'PHPExcel_Calculation_MathTrig::ROUNDDOWN',
1399 'argumentCount' => '2'
1400 ),
1401 'ROUNDUP' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
1402 'functionCall' => 'PHPExcel_Calculation_MathTrig::ROUNDUP',
1403 'argumentCount' => '2'
1404 ),
1406 'functionCall' => 'PHPExcel_Calculation_LookupRef::ROW',
1407 'argumentCount' => '-1',
1408 'passByReference' => array(TRUE)
1409 ),
1411 'functionCall' => 'PHPExcel_Calculation_LookupRef::ROWS',
1412 'argumentCount' => '1'
1413 ),
1414 'RSQ' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
1415 'functionCall' => 'PHPExcel_Calculation_Statistical::RSQ',
1416 'argumentCount' => '2'
1417 ),
1419 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
1420 'argumentCount' => '1+'
1421 ),
1422 'SEARCH' => array('category' => PHPExcel_Calculation_Function::CATEGORY_TEXT_AND_DATA,
1423 'functionCall' => 'PHPExcel_Calculation_TextData::SEARCHINSENSITIVE',
1424 'argumentCount' => '2,3'
1425 ),
1426 'SEARCHB' => array('category' => PHPExcel_Calculation_Function::CATEGORY_TEXT_AND_DATA,
1427 'functionCall' => 'PHPExcel_Calculation_TextData::SEARCHINSENSITIVE',
1428 'argumentCount' => '2,3'
1429 ),
1430 'SECOND' => array('category' => PHPExcel_Calculation_Function::CATEGORY_DATE_AND_TIME,
1431 'functionCall' => 'PHPExcel_Calculation_DateTime::SECONDOFMINUTE',
1432 'argumentCount' => '1'
1433 ),
1434 'SERIESSUM' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
1435 'functionCall' => 'PHPExcel_Calculation_MathTrig::SERIESSUM',
1436 'argumentCount' => '4'
1437 ),
1438 'SIGN' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
1439 'functionCall' => 'PHPExcel_Calculation_MathTrig::SIGN',
1440 'argumentCount' => '1'
1441 ),
1442 'SIN' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
1443 'functionCall' => 'sin',
1444 'argumentCount' => '1'
1445 ),
1446 'SINH' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
1447 'functionCall' => 'sinh',
1448 'argumentCount' => '1'
1449 ),
1450 'SKEW' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
1451 'functionCall' => 'PHPExcel_Calculation_Statistical::SKEW',
1452 'argumentCount' => '1+'
1453 ),
1454 'SLN' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
1455 'functionCall' => 'PHPExcel_Calculation_Financial::SLN',
1456 'argumentCount' => '3'
1457 ),
1458 'SLOPE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
1459 'functionCall' => 'PHPExcel_Calculation_Statistical::SLOPE',
1460 'argumentCount' => '2'
1461 ),
1462 'SMALL' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
1463 'functionCall' => 'PHPExcel_Calculation_Statistical::SMALL',
1464 'argumentCount' => '2'
1465 ),
1466 'SQRT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
1467 'functionCall' => 'sqrt',
1468 'argumentCount' => '1'
1469 ),
1470 'SQRTPI' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
1471 'functionCall' => 'PHPExcel_Calculation_MathTrig::SQRTPI',
1472 'argumentCount' => '1'
1473 ),
1474 'STANDARDIZE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
1475 'functionCall' => 'PHPExcel_Calculation_Statistical::STANDARDIZE',
1476 'argumentCount' => '3'
1477 ),
1478 'STDEV' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
1479 'functionCall' => 'PHPExcel_Calculation_Statistical::STDEV',
1480 'argumentCount' => '1+'
1481 ),
1482 'STDEVA' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
1483 'functionCall' => 'PHPExcel_Calculation_Statistical::STDEVA',
1484 'argumentCount' => '1+'
1485 ),
1486 'STDEVP' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
1487 'functionCall' => 'PHPExcel_Calculation_Statistical::STDEVP',
1488 'argumentCount' => '1+'
1489 ),
1490 'STDEVPA' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
1491 'functionCall' => 'PHPExcel_Calculation_Statistical::STDEVPA',
1492 'argumentCount' => '1+'
1493 ),
1494 'STEYX' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
1495 'functionCall' => 'PHPExcel_Calculation_Statistical::STEYX',
1496 'argumentCount' => '2'
1497 ),
1498 'SUBSTITUTE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_TEXT_AND_DATA,
1499 'functionCall' => 'PHPExcel_Calculation_TextData::SUBSTITUTE',
1500 'argumentCount' => '3,4'
1501 ),
1502 'SUBTOTAL' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
1503 'functionCall' => 'PHPExcel_Calculation_MathTrig::SUBTOTAL',
1504 'argumentCount' => '2+'
1505 ),
1506 'SUM' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
1507 'functionCall' => 'PHPExcel_Calculation_MathTrig::SUM',
1508 'argumentCount' => '1+'
1509 ),
1510 'SUMIF' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
1511 'functionCall' => 'PHPExcel_Calculation_MathTrig::SUMIF',
1512 'argumentCount' => '2,3'
1513 ),
1514 'SUMIFS' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
1515 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
1516 'argumentCount' => '?'
1517 ),
1518 'SUMPRODUCT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
1519 'functionCall' => 'PHPExcel_Calculation_MathTrig::SUMPRODUCT',
1520 'argumentCount' => '1+'
1521 ),
1522 'SUMSQ' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
1523 'functionCall' => 'PHPExcel_Calculation_MathTrig::SUMSQ',
1524 'argumentCount' => '1+'
1525 ),
1526 'SUMX2MY2' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
1527 'functionCall' => 'PHPExcel_Calculation_MathTrig::SUMX2MY2',
1528 'argumentCount' => '2'
1529 ),
1530 'SUMX2PY2' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
1531 'functionCall' => 'PHPExcel_Calculation_MathTrig::SUMX2PY2',
1532 'argumentCount' => '2'
1533 ),
1534 'SUMXMY2' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
1535 'functionCall' => 'PHPExcel_Calculation_MathTrig::SUMXMY2',
1536 'argumentCount' => '2'
1537 ),
1538 'SYD' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
1539 'functionCall' => 'PHPExcel_Calculation_Financial::SYD',
1540 'argumentCount' => '4'
1541 ),
1543 'functionCall' => 'PHPExcel_Calculation_TextData::RETURNSTRING',
1544 'argumentCount' => '1'
1545 ),
1546 'TAN' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
1547 'functionCall' => 'tan',
1548 'argumentCount' => '1'
1549 ),
1550 'TANH' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
1551 'functionCall' => 'tanh',
1552 'argumentCount' => '1'
1553 ),
1554 'TBILLEQ' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
1555 'functionCall' => 'PHPExcel_Calculation_Financial::TBILLEQ',
1556 'argumentCount' => '3'
1557 ),
1558 'TBILLPRICE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
1559 'functionCall' => 'PHPExcel_Calculation_Financial::TBILLPRICE',
1560 'argumentCount' => '3'
1561 ),
1562 'TBILLYIELD' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
1563 'functionCall' => 'PHPExcel_Calculation_Financial::TBILLYIELD',
1564 'argumentCount' => '3'
1565 ),
1566 'TDIST' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
1567 'functionCall' => 'PHPExcel_Calculation_Statistical::TDIST',
1568 'argumentCount' => '3'
1569 ),
1570 'TEXT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_TEXT_AND_DATA,
1571 'functionCall' => 'PHPExcel_Calculation_TextData::TEXTFORMAT',
1572 'argumentCount' => '2'
1573 ),
1574 'TIME' => array('category' => PHPExcel_Calculation_Function::CATEGORY_DATE_AND_TIME,
1575 'functionCall' => 'PHPExcel_Calculation_DateTime::TIME',
1576 'argumentCount' => '3'
1577 ),
1578 'TIMEVALUE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_DATE_AND_TIME,
1579 'functionCall' => 'PHPExcel_Calculation_DateTime::TIMEVALUE',
1580 'argumentCount' => '1'
1581 ),
1582 'TINV' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
1583 'functionCall' => 'PHPExcel_Calculation_Statistical::TINV',
1584 'argumentCount' => '2'
1585 ),
1586 'TODAY' => array('category' => PHPExcel_Calculation_Function::CATEGORY_DATE_AND_TIME,
1587 'functionCall' => 'PHPExcel_Calculation_DateTime::DATENOW',
1588 'argumentCount' => '0'
1589 ),
1590 'TRANSPOSE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_LOOKUP_AND_REFERENCE,
1591 'functionCall' => 'PHPExcel_Calculation_LookupRef::TRANSPOSE',
1592 'argumentCount' => '1'
1593 ),
1594 'TREND' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
1595 'functionCall' => 'PHPExcel_Calculation_Statistical::TREND',
1596 'argumentCount' => '1-4'
1597 ),
1598 'TRIM' => array('category' => PHPExcel_Calculation_Function::CATEGORY_TEXT_AND_DATA,
1599 'functionCall' => 'PHPExcel_Calculation_TextData::TRIMSPACES',
1600 'argumentCount' => '1'
1601 ),
1602 'TRIMMEAN' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
1603 'functionCall' => 'PHPExcel_Calculation_Statistical::TRIMMEAN',
1604 'argumentCount' => '2'
1605 ),
1606 'TRUE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_LOGICAL,
1607 'functionCall' => 'PHPExcel_Calculation_Logical::TRUE',
1608 'argumentCount' => '0'
1609 ),
1610 'TRUNC' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
1611 'functionCall' => 'PHPExcel_Calculation_MathTrig::TRUNC',
1612 'argumentCount' => '1,2'
1613 ),
1614 'TTEST' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
1615 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
1616 'argumentCount' => '4'
1617 ),
1618 'TYPE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_INFORMATION,
1619 'functionCall' => 'PHPExcel_Calculation_Functions::TYPE',
1620 'argumentCount' => '1'
1621 ),
1622 'UPPER' => array('category' => PHPExcel_Calculation_Function::CATEGORY_TEXT_AND_DATA,
1623 'functionCall' => 'PHPExcel_Calculation_TextData::UPPERCASE',
1624 'argumentCount' => '1'
1625 ),
1626 'USDOLLAR' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
1627 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
1628 'argumentCount' => '2'
1629 ),
1630 'VALUE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_TEXT_AND_DATA,
1631 'functionCall' => 'PHPExcel_Calculation_TextData::VALUE',
1632 'argumentCount' => '1'
1633 ),
1634 'VAR' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
1635 'functionCall' => 'PHPExcel_Calculation_Statistical::VARFunc',
1636 'argumentCount' => '1+'
1637 ),
1638 'VARA' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
1639 'functionCall' => 'PHPExcel_Calculation_Statistical::VARA',
1640 'argumentCount' => '1+'
1641 ),
1642 'VARP' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
1643 'functionCall' => 'PHPExcel_Calculation_Statistical::VARP',
1644 'argumentCount' => '1+'
1645 ),
1646 'VARPA' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
1647 'functionCall' => 'PHPExcel_Calculation_Statistical::VARPA',
1648 'argumentCount' => '1+'
1649 ),
1650 'VDB' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
1651 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
1652 'argumentCount' => '5-7'
1653 ),
1654 'VERSION' => array('category' => PHPExcel_Calculation_Function::CATEGORY_INFORMATION,
1655 'functionCall' => 'PHPExcel_Calculation_Functions::VERSION',
1656 'argumentCount' => '0'
1657 ),
1658 'VLOOKUP' => array('category' => PHPExcel_Calculation_Function::CATEGORY_LOOKUP_AND_REFERENCE,
1659 'functionCall' => 'PHPExcel_Calculation_LookupRef::VLOOKUP',
1660 'argumentCount' => '3,4'
1661 ),
1662 'WEEKDAY' => array('category' => PHPExcel_Calculation_Function::CATEGORY_DATE_AND_TIME,
1663 'functionCall' => 'PHPExcel_Calculation_DateTime::DAYOFWEEK',
1664 'argumentCount' => '1,2'
1665 ),
1666 'WEEKNUM' => array('category' => PHPExcel_Calculation_Function::CATEGORY_DATE_AND_TIME,
1667 'functionCall' => 'PHPExcel_Calculation_DateTime::WEEKOFYEAR',
1668 'argumentCount' => '1,2'
1669 ),
1670 'WEIBULL' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
1671 'functionCall' => 'PHPExcel_Calculation_Statistical::WEIBULL',
1672 'argumentCount' => '4'
1673 ),
1674 'WORKDAY' => array('category' => PHPExcel_Calculation_Function::CATEGORY_DATE_AND_TIME,
1675 'functionCall' => 'PHPExcel_Calculation_DateTime::WORKDAY',
1676 'argumentCount' => '2+'
1677 ),
1678 'XIRR' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
1679 'functionCall' => 'PHPExcel_Calculation_Financial::XIRR',
1680 'argumentCount' => '2,3'
1681 ),
1682 'XNPV' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
1683 'functionCall' => 'PHPExcel_Calculation_Financial::XNPV',
1684 'argumentCount' => '3'
1685 ),
1686 'YEAR' => array('category' => PHPExcel_Calculation_Function::CATEGORY_DATE_AND_TIME,
1687 'functionCall' => 'PHPExcel_Calculation_DateTime::YEAR',
1688 'argumentCount' => '1'
1689 ),
1690 'YEARFRAC' => array('category' => PHPExcel_Calculation_Function::CATEGORY_DATE_AND_TIME,
1691 'functionCall' => 'PHPExcel_Calculation_DateTime::YEARFRAC',
1692 'argumentCount' => '2,3'
1693 ),
1694 'YIELD' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
1695 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
1696 'argumentCount' => '6,7'
1697 ),
1698 'YIELDDISC' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
1699 'functionCall' => 'PHPExcel_Calculation_Financial::YIELDDISC',
1700 'argumentCount' => '4,5'
1701 ),
1702 'YIELDMAT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
1703 'functionCall' => 'PHPExcel_Calculation_Financial::YIELDMAT',
1704 'argumentCount' => '5,6'
1705 ),
1706 'ZTEST' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
1707 'functionCall' => 'PHPExcel_Calculation_Statistical::ZTEST',
1708 'argumentCount' => '2-3'
1709 )
1710 );
1711
1712
1713 // Internal functions used for special control purposes
1714 private static $_controlFunctions = array(
1715 'MKMATRIX' => array('argumentCount' => '*',
1716 'functionCall' => 'self::_mkMatrix'
1717 )
1718 );
1719
1720
1721
1722
1723 private function __construct(PHPExcel $workbook = NULL) {
1724 $setPrecision = (PHP_INT_SIZE == 4) ? 14 : 16;
1725 $this->_savedPrecision = ini_get('precision');
1726 if ($this->_savedPrecision < $setPrecision) {
1727 ini_set('precision',$setPrecision);
1728 }
1729 $this->delta = 1 * pow(10, -$setPrecision);
1730
1731 if ($workbook !== NULL) {
1732 self::$_workbookSets[$workbook->getID()] = $this;
1733 }
1734
1735 $this->_workbook = $workbook;
1736 $this->_cyclicReferenceStack = new PHPExcel_CalcEngine_CyclicReferenceStack();
1737 $this->_debugLog = new PHPExcel_CalcEngine_Logger($this->_cyclicReferenceStack);
1738 } // function __construct()
1739
1740
1741 public function __destruct() {
1742 if ($this->_savedPrecision != ini_get('precision')) {
1743 ini_set('precision',$this->_savedPrecision);
1744 }
1745 }
1746
1747 private static function _loadLocales() {
1748 $localeFileDirectory = PHPEXCEL_ROOT.'PHPExcel/locale/';
1749 foreach (glob($localeFileDirectory.'/*',GLOB_ONLYDIR) as $filename) {
1750 $filename = substr($filename,strlen($localeFileDirectory)+1);
1751 if ($filename != 'en') {
1752 self::$_validLocaleLanguages[] = $filename;
1753 }
1754 }
1755 }
1756
1765 public static function getInstance(PHPExcel $workbook = NULL) {
1766 if ($workbook !== NULL) {
1767 if (isset(self::$_workbookSets[$workbook->getID()])) {
1768 return self::$_workbookSets[$workbook->getID()];
1769 }
1770 return new PHPExcel_Calculation($workbook);
1771 }
1772
1773 if (!isset(self::$_instance) || (self::$_instance === NULL)) {
1774 self::$_instance = new PHPExcel_Calculation();
1775 }
1776
1777 return self::$_instance;
1778 } // function getInstance()
1779
1786 public static function unsetInstance(PHPExcel $workbook = NULL) {
1787 if ($workbook !== NULL) {
1788 if (isset(self::$_workbookSets[$workbook->getID()])) {
1789 unset(self::$_workbookSets[$workbook->getID()]);
1790 }
1791 }
1792 }
1793
1801 public function flushInstance() {
1802 $this->clearCalculationCache();
1803 } // function flushInstance()
1804
1805
1812 public function getDebugLog() {
1813 return $this->_debugLog;
1814 }
1815
1822 public final function __clone() {
1823 throw new PHPExcel_Calculation_Exception ('Cloning the calculation engine is not allowed!');
1824 } // function __clone()
1825
1826
1833 public static function getTRUE() {
1834 return self::$_localeBoolean['TRUE'];
1835 }
1836
1843 public static function getFALSE() {
1844 return self::$_localeBoolean['FALSE'];
1845 }
1846
1854 public static function setArrayReturnType($returnType) {
1855 if (($returnType == self::RETURN_ARRAY_AS_VALUE) ||
1856 ($returnType == self::RETURN_ARRAY_AS_ERROR) ||
1857 ($returnType == self::RETURN_ARRAY_AS_ARRAY)) {
1858 self::$returnArrayAsType = $returnType;
1859 return TRUE;
1860 }
1861 return FALSE;
1862 } // function setArrayReturnType()
1863
1864
1871 public static function getArrayReturnType() {
1873 } // function getArrayReturnType()
1874
1875
1882 public function getCalculationCacheEnabled() {
1884 } // function getCalculationCacheEnabled()
1885
1892 public function setCalculationCacheEnabled($pValue = TRUE) {
1893 $this->_calculationCacheEnabled = $pValue;
1894 $this->clearCalculationCache();
1895 } // function setCalculationCacheEnabled()
1896
1897
1901 public function enableCalculationCache() {
1902 $this->setCalculationCacheEnabled(TRUE);
1903 } // function enableCalculationCache()
1904
1905
1909 public function disableCalculationCache() {
1910 $this->setCalculationCacheEnabled(FALSE);
1911 } // function disableCalculationCache()
1912
1913
1917 public function clearCalculationCache() {
1918 $this->_calculationCache = array();
1919 } // function clearCalculationCache()
1920
1926 public function clearCalculationCacheForWorksheet($worksheetName) {
1927 if (isset($this->_calculationCache[$worksheetName])) {
1928 unset($this->_calculationCache[$worksheetName]);
1929 }
1930 } // function clearCalculationCacheForWorksheet()
1931
1938 public function renameCalculationCacheForWorksheet($fromWorksheetName, $toWorksheetName) {
1939 if (isset($this->_calculationCache[$fromWorksheetName])) {
1940 $this->_calculationCache[$toWorksheetName] = &$this->_calculationCache[$fromWorksheetName];
1941 unset($this->_calculationCache[$fromWorksheetName]);
1942 }
1943 } // function renameCalculationCacheForWorksheet()
1944
1945
1951 public function getLocale() {
1953 } // function getLocale()
1954
1955
1962 public function setLocale($locale = 'en_us') {
1963 // Identify our locale and language
1964 $language = $locale = strtolower($locale);
1965 if (strpos($locale,'_') !== FALSE) {
1966 list($language) = explode('_',$locale);
1967 }
1968
1969 if (count(self::$_validLocaleLanguages) == 1)
1971
1972 // Test whether we have any language data for this language (any locale)
1973 if (in_array($language,self::$_validLocaleLanguages)) {
1974 // initialise language/locale settings
1975 self::$_localeFunctions = array();
1976 self::$_localeArgumentSeparator = ',';
1977 self::$_localeBoolean = array('TRUE' => 'TRUE', 'FALSE' => 'FALSE', 'NULL' => 'NULL');
1978 // Default is English, if user isn't requesting english, then read the necessary data from the locale files
1979 if ($locale != 'en_us') {
1980 // Search for a file with a list of function names for locale
1981 $functionNamesFile = PHPEXCEL_ROOT . 'PHPExcel'.DIRECTORY_SEPARATOR.'locale'.DIRECTORY_SEPARATOR.str_replace('_',DIRECTORY_SEPARATOR,$locale).DIRECTORY_SEPARATOR.'functions';
1982 if (!file_exists($functionNamesFile)) {
1983 // If there isn't a locale specific function file, look for a language specific function file
1984 $functionNamesFile = PHPEXCEL_ROOT . 'PHPExcel'.DIRECTORY_SEPARATOR.'locale'.DIRECTORY_SEPARATOR.$language.DIRECTORY_SEPARATOR.'functions';
1985 if (!file_exists($functionNamesFile)) {
1986 return FALSE;
1987 }
1988 }
1989 // Retrieve the list of locale or language specific function names
1990 $localeFunctions = file($functionNamesFile,FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
1991 foreach ($localeFunctions as $localeFunction) {
1992 list($localeFunction) = explode('##',$localeFunction); // Strip out comments
1993 if (strpos($localeFunction,'=') !== FALSE) {
1994 list($fName,$lfName) = explode('=',$localeFunction);
1995 $fName = trim($fName);
1996 $lfName = trim($lfName);
1997 if ((isset(self::$_PHPExcelFunctions[$fName])) && ($lfName != '') && ($fName != $lfName)) {
1998 self::$_localeFunctions[$fName] = $lfName;
1999 }
2000 }
2001 }
2002 // Default the TRUE and FALSE constants to the locale names of the TRUE() and FALSE() functions
2003 if (isset(self::$_localeFunctions['TRUE'])) { self::$_localeBoolean['TRUE'] = self::$_localeFunctions['TRUE']; }
2004 if (isset(self::$_localeFunctions['FALSE'])) { self::$_localeBoolean['FALSE'] = self::$_localeFunctions['FALSE']; }
2005
2006 $configFile = PHPEXCEL_ROOT . 'PHPExcel'.DIRECTORY_SEPARATOR.'locale'.DIRECTORY_SEPARATOR.str_replace('_',DIRECTORY_SEPARATOR,$locale).DIRECTORY_SEPARATOR.'config';
2007 if (!file_exists($configFile)) {
2008 $configFile = PHPEXCEL_ROOT . 'PHPExcel'.DIRECTORY_SEPARATOR.'locale'.DIRECTORY_SEPARATOR.$language.DIRECTORY_SEPARATOR.'config';
2009 }
2010 if (file_exists($configFile)) {
2011 $localeSettings = file($configFile,FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
2012 foreach ($localeSettings as $localeSetting) {
2013 list($localeSetting) = explode('##',$localeSetting); // Strip out comments
2014 if (strpos($localeSetting,'=') !== FALSE) {
2015 list($settingName,$settingValue) = explode('=',$localeSetting);
2016 $settingName = strtoupper(trim($settingName));
2017 switch ($settingName) {
2018 case 'ARGUMENTSEPARATOR' :
2019 self::$_localeArgumentSeparator = trim($settingValue);
2020 break;
2021 }
2022 }
2023 }
2024 }
2025 }
2026
2027 self::$functionReplaceFromExcel = self::$functionReplaceToExcel =
2028 self::$functionReplaceFromLocale = self::$functionReplaceToLocale = NULL;
2029 self::$_localeLanguage = $locale;
2030 return TRUE;
2031 }
2032 return FALSE;
2033 } // function setLocale()
2034
2035
2036
2037 public static function _translateSeparator($fromSeparator,$toSeparator,$formula,&$inBraces) {
2038 $strlen = mb_strlen($formula);
2039 for ($i = 0; $i < $strlen; ++$i) {
2040 $chr = mb_substr($formula,$i,1);
2041 switch ($chr) {
2042 case '{' : $inBraces = TRUE;
2043 break;
2044 case '}' : $inBraces = FALSE;
2045 break;
2046 case $fromSeparator :
2047 if (!$inBraces) {
2048 $formula = mb_substr($formula,0,$i).$toSeparator.mb_substr($formula,$i+1);
2049 }
2050 }
2051 }
2052 return $formula;
2053 }
2054
2055 private static function _translateFormula($from,$to,$formula,$fromSeparator,$toSeparator) {
2056 // Convert any Excel function names to the required language
2057 if (self::$_localeLanguage !== 'en_us') {
2058 $inBraces = FALSE;
2059 // If there is the possibility of braces within a quoted string, then we don't treat those as matrix indicators
2060 if (strpos($formula,'"') !== FALSE) {
2061 // So instead we skip replacing in any quoted strings by only replacing in every other array element after we've exploded
2062 // the formula
2063 $temp = explode('"',$formula);
2064 $i = FALSE;
2065 foreach($temp as &$value) {
2066 // Only count/replace in alternating array entries
2067 if ($i = !$i) {
2068 $value = preg_replace($from,$to,$value);
2069 $value = self::_translateSeparator($fromSeparator,$toSeparator,$value,$inBraces);
2070 }
2071 }
2072 unset($value);
2073 // Then rebuild the formula string
2074 $formula = implode('"',$temp);
2075 } else {
2076 // If there's no quoted strings, then we do a simple count/replace
2077 $formula = preg_replace($from,$to,$formula);
2078 $formula = self::_translateSeparator($fromSeparator,$toSeparator,$formula,$inBraces);
2079 }
2080 }
2081
2082 return $formula;
2083 }
2084
2085 private static $functionReplaceFromExcel = NULL;
2086 private static $functionReplaceToLocale = NULL;
2087
2088 public function _translateFormulaToLocale($formula) {
2089 if (self::$functionReplaceFromExcel === NULL) {
2090 self::$functionReplaceFromExcel = array();
2091 foreach(array_keys(self::$_localeFunctions) as $excelFunctionName) {
2092 self::$functionReplaceFromExcel[] = '/(@?[^\w\.])'.preg_quote($excelFunctionName).'([\s]*\‍()/Ui';
2093 }
2094 foreach(array_keys(self::$_localeBoolean) as $excelBoolean) {
2095 self::$functionReplaceFromExcel[] = '/(@?[^\w\.])'.preg_quote($excelBoolean).'([^\w\.])/Ui';
2096 }
2097
2098 }
2099
2100 if (self::$functionReplaceToLocale === NULL) {
2101 self::$functionReplaceToLocale = array();
2102 foreach(array_values(self::$_localeFunctions) as $localeFunctionName) {
2103 self::$functionReplaceToLocale[] = '$1'.trim($localeFunctionName).'$2';
2104 }
2105 foreach(array_values(self::$_localeBoolean) as $localeBoolean) {
2106 self::$functionReplaceToLocale[] = '$1'.trim($localeBoolean).'$2';
2107 }
2108 }
2109
2110 return self::_translateFormula(self::$functionReplaceFromExcel,self::$functionReplaceToLocale,$formula,',',self::$_localeArgumentSeparator);
2111 } // function _translateFormulaToLocale()
2112
2113
2114 private static $functionReplaceFromLocale = NULL;
2115 private static $functionReplaceToExcel = NULL;
2116
2117 public function _translateFormulaToEnglish($formula) {
2118 if (self::$functionReplaceFromLocale === NULL) {
2119 self::$functionReplaceFromLocale = array();
2120 foreach(array_values(self::$_localeFunctions) as $localeFunctionName) {
2121 self::$functionReplaceFromLocale[] = '/(@?[^\w\.])'.preg_quote($localeFunctionName).'([\s]*\‍()/Ui';
2122 }
2123 foreach(array_values(self::$_localeBoolean) as $excelBoolean) {
2124 self::$functionReplaceFromLocale[] = '/(@?[^\w\.])'.preg_quote($excelBoolean).'([^\w\.])/Ui';
2125 }
2126 }
2127
2128 if (self::$functionReplaceToExcel === NULL) {
2129 self::$functionReplaceToExcel = array();
2130 foreach(array_keys(self::$_localeFunctions) as $excelFunctionName) {
2131 self::$functionReplaceToExcel[] = '$1'.trim($excelFunctionName).'$2';
2132 }
2133 foreach(array_keys(self::$_localeBoolean) as $excelBoolean) {
2134 self::$functionReplaceToExcel[] = '$1'.trim($excelBoolean).'$2';
2135 }
2136 }
2137
2138 return self::_translateFormula(self::$functionReplaceFromLocale,self::$functionReplaceToExcel,$formula,self::$_localeArgumentSeparator,',');
2139 } // function _translateFormulaToEnglish()
2140
2141
2142 public static function _localeFunc($function) {
2143 if (self::$_localeLanguage !== 'en_us') {
2144 $functionName = trim($function,'(');
2145 if (isset(self::$_localeFunctions[$functionName])) {
2146 $brace = ($functionName != $function);
2147 $function = self::$_localeFunctions[$functionName];
2148 if ($brace) { $function .= '('; }
2149 }
2150 }
2151 return $function;
2152 }
2153
2154
2155
2156
2163 public static function _wrapResult($value) {
2164 if (is_string($value)) {
2165 // Error values cannot be "wrapped"
2166 if (preg_match('/^'.self::CALCULATION_REGEXP_ERROR.'$/i', $value, $match)) {
2167 // Return Excel errors "as is"
2168 return $value;
2169 }
2170 // Return strings wrapped in quotes
2171 return '"'.$value.'"';
2172 // Convert numeric errors to NaN error
2173 } else if((is_float($value)) && ((is_nan($value)) || (is_infinite($value)))) {
2175 }
2176
2177 return $value;
2178 } // function _wrapResult()
2179
2180
2187 public static function _unwrapResult($value) {
2188 if (is_string($value)) {
2189 if ((isset($value{0})) && ($value{0} == '"') && (substr($value,-1) == '"')) {
2190 return substr($value,1,-1);
2191 }
2192 // Convert numeric errors to NaN error
2193 } else if((is_float($value)) && ((is_nan($value)) || (is_infinite($value)))) {
2195 }
2196 return $value;
2197 } // function _unwrapResult()
2198
2199
2200
2201
2211 public function calculate(PHPExcel_Cell $pCell = NULL) {
2212 try {
2213 return $this->calculateCellValue($pCell);
2214 } catch (PHPExcel_Exception $e) {
2215 throw new PHPExcel_Calculation_Exception($e->getMessage());
2216 }
2217 } // function calculate()
2218
2219
2229 public function calculateCellValue(PHPExcel_Cell $pCell = NULL, $resetLog = TRUE) {
2230 if ($pCell === NULL) {
2231 return NULL;
2232 }
2233
2235 if ($resetLog) {
2236 // Initialise the logging settings if requested
2237 $this->formulaError = null;
2238 $this->_debugLog->clearLog();
2239 $this->_cyclicReferenceStack->clear();
2240 $this->_cyclicFormulaCount = 1;
2241
2242 self::$returnArrayAsType = self::RETURN_ARRAY_AS_ARRAY;
2243 }
2244
2245 // Execute the calculation for the cell formula
2246 $this->_cellStack[] = array(
2247 'sheet' => $pCell->getWorksheet()->getTitle(),
2248 'cell' => $pCell->getCoordinate(),
2249 );
2250 try {
2251 $result = self::_unwrapResult($this->_calculateFormulaValue($pCell->getValue(), $pCell->getCoordinate(), $pCell));
2252 $cellAddress = array_pop($this->_cellStack);
2253 $this->_workbook->getSheetByName($cellAddress['sheet'])->getCell($cellAddress['cell']);
2254 } catch (PHPExcel_Exception $e) {
2255 $cellAddress = array_pop($this->_cellStack);
2256 $this->_workbook->getSheetByName($cellAddress['sheet'])->getCell($cellAddress['cell']);
2257 throw new PHPExcel_Calculation_Exception($e->getMessage());
2258 }
2259
2260 if ((is_array($result)) && (self::$returnArrayAsType != self::RETURN_ARRAY_AS_ARRAY)) {
2261 self::$returnArrayAsType = $returnArrayAsType;
2263 if (self::$returnArrayAsType == self::RETURN_ARRAY_AS_ERROR) {
2265 }
2266 // If there's only a single cell in the array, then we allow it
2267 if (count($testResult) != 1) {
2268 // If keys are numeric, then it's a matrix result rather than a cell range result, so we permit it
2269 $r = array_keys($result);
2270 $r = array_shift($r);
2271 if (!is_numeric($r)) { return PHPExcel_Calculation_Functions::VALUE(); }
2272 if (is_array($result[$r])) {
2273 $c = array_keys($result[$r]);
2274 $c = array_shift($c);
2275 if (!is_numeric($c)) {
2277 }
2278 }
2279 }
2280 $result = array_shift($testResult);
2281 }
2282 self::$returnArrayAsType = $returnArrayAsType;
2283
2284
2285 if ($result === NULL) {
2286 return 0;
2287 } elseif((is_float($result)) && ((is_nan($result)) || (is_infinite($result)))) {
2289 }
2290 return $result;
2291 } // function calculateCellValue(
2292
2293
2301 public function parseFormula($formula) {
2302 // Basic validation that this is indeed a formula
2303 // We return an empty array if not
2304 $formula = trim($formula);
2305 if ((!isset($formula{0})) || ($formula{0} != '=')) return array();
2306 $formula = ltrim(substr($formula,1));
2307 if (!isset($formula{0})) return array();
2308
2309 // Parse the formula and return the token stack
2310 return $this->_parseFormula($formula);
2311 } // function parseFormula()
2312
2313
2323 public function calculateFormula($formula, $cellID=NULL, PHPExcel_Cell $pCell = NULL) {
2324 // Initialise the logging settings
2325 $this->formulaError = null;
2326 $this->_debugLog->clearLog();
2327 $this->_cyclicReferenceStack->clear();
2328
2329 // Disable calculation cacheing because it only applies to cell calculations, not straight formulae
2330 // But don't actually flush any cache
2331 $resetCache = $this->getCalculationCacheEnabled();
2332 $this->_calculationCacheEnabled = FALSE;
2333 // Execute the calculation
2334 try {
2335 $result = self::_unwrapResult($this->_calculateFormulaValue($formula, $cellID, $pCell));
2336 } catch (PHPExcel_Exception $e) {
2337 throw new PHPExcel_Calculation_Exception($e->getMessage());
2338 }
2339
2340 // Reset calculation cacheing to its previous state
2341 $this->_calculationCacheEnabled = $resetCache;
2342
2343 return $result;
2344 } // function calculateFormula()
2345
2346
2347 public function getValueFromCache($cellReference, &$cellValue) {
2348 // Is calculation cacheing enabled?
2349 // Is the value present in calculation cache?
2350 $this->_debugLog->writeDebugLog('Testing cache value for cell ', $cellReference);
2351 if (($this->_calculationCacheEnabled) && (isset($this->_calculationCache[$cellReference]))) {
2352 $this->_debugLog->writeDebugLog('Retrieving value for cell ', $cellReference, ' from cache');
2353 // Return the cached result
2354 $cellValue = $this->_calculationCache[$cellReference];
2355 return TRUE;
2356 }
2357 return FALSE;
2358 }
2359
2360 public function saveValueToCache($cellReference, $cellValue) {
2361 if ($this->_calculationCacheEnabled) {
2362 $this->_calculationCache[$cellReference] = $cellValue;
2363 }
2364 }
2365
2375 public function _calculateFormulaValue($formula, $cellID=null, PHPExcel_Cell $pCell = null) {
2376 $cellValue = null;
2377
2378 // Basic validation that this is indeed a formula
2379 // We simply return the cell value if not
2380 $formula = trim($formula);
2381 if ($formula{0} != '=') return self::_wrapResult($formula);
2382 $formula = ltrim(substr($formula, 1));
2383 if (!isset($formula{0})) return self::_wrapResult($formula);
2384
2385 $pCellParent = ($pCell !== NULL) ? $pCell->getWorksheet() : NULL;
2386 $wsTitle = ($pCellParent !== NULL) ? $pCellParent->getTitle() : "\x00Wrk";
2387 $wsCellReference = $wsTitle . '!' . $cellID;
2388
2389 if (($cellID !== NULL) && ($this->getValueFromCache($wsCellReference, $cellValue))) {
2390 return $cellValue;
2391 }
2392
2393 if (($wsTitle{0} !== "\x00") && ($this->_cyclicReferenceStack->onStack($wsCellReference))) {
2394 if ($this->cyclicFormulaCount <= 0) {
2395 $this->_cyclicFormulaCell = '';
2396 return $this->_raiseFormulaError('Cyclic Reference in Formula');
2397 } elseif ($this->_cyclicFormulaCell === $wsCellReference) {
2399 if ($this->_cyclicFormulaCount >= $this->cyclicFormulaCount) {
2400 $this->_cyclicFormulaCell = '';
2401 return $cellValue;
2402 }
2403 } elseif ($this->_cyclicFormulaCell == '') {
2404 if ($this->_cyclicFormulaCount >= $this->cyclicFormulaCount) {
2405 return $cellValue;
2406 }
2407 $this->_cyclicFormulaCell = $wsCellReference;
2408 }
2409 }
2410
2411 // Parse the formula onto the token stack and calculate the value
2412 $this->_cyclicReferenceStack->push($wsCellReference);
2413 $cellValue = $this->_processTokenStack($this->_parseFormula($formula, $pCell), $cellID, $pCell);
2414 $this->_cyclicReferenceStack->pop();
2415
2416 // Save to calculation cache
2417 if ($cellID !== NULL) {
2418 $this->saveValueToCache($wsCellReference, $cellValue);
2419 }
2420
2421 // Return the calculated value
2422 return $cellValue;
2423 } // function _calculateFormulaValue()
2424
2425
2438 private static function _checkMatrixOperands(&$operand1,&$operand2,$resize = 1) {
2439 // Examine each of the two operands, and turn them into an array if they aren't one already
2440 // Note that this function should only be called if one or both of the operand is already an array
2441 if (!is_array($operand1)) {
2442 list($matrixRows,$matrixColumns) = self::_getMatrixDimensions($operand2);
2443 $operand1 = array_fill(0,$matrixRows,array_fill(0,$matrixColumns,$operand1));
2444 $resize = 0;
2445 } elseif (!is_array($operand2)) {
2446 list($matrixRows,$matrixColumns) = self::_getMatrixDimensions($operand1);
2447 $operand2 = array_fill(0,$matrixRows,array_fill(0,$matrixColumns,$operand2));
2448 $resize = 0;
2449 }
2450
2451 list($matrix1Rows,$matrix1Columns) = self::_getMatrixDimensions($operand1);
2452 list($matrix2Rows,$matrix2Columns) = self::_getMatrixDimensions($operand2);
2453 if (($matrix1Rows == $matrix2Columns) && ($matrix2Rows == $matrix1Columns)) {
2454 $resize = 1;
2455 }
2456
2457 if ($resize == 2) {
2458 // Given two matrices of (potentially) unequal size, convert the smaller in each dimension to match the larger
2459 self::_resizeMatricesExtend($operand1,$operand2,$matrix1Rows,$matrix1Columns,$matrix2Rows,$matrix2Columns);
2460 } elseif ($resize == 1) {
2461 // Given two matrices of (potentially) unequal size, convert the larger in each dimension to match the smaller
2462 self::_resizeMatricesShrink($operand1,$operand2,$matrix1Rows,$matrix1Columns,$matrix2Rows,$matrix2Columns);
2463 }
2464 return array( $matrix1Rows,$matrix1Columns,$matrix2Rows,$matrix2Columns);
2465 } // function _checkMatrixOperands()
2466
2467
2474 public static function _getMatrixDimensions(&$matrix) {
2475 $matrixRows = count($matrix);
2476 $matrixColumns = 0;
2477 foreach($matrix as $rowKey => $rowValue) {
2478 $matrixColumns = max(count($rowValue),$matrixColumns);
2479 if (!is_array($rowValue)) {
2480 $matrix[$rowKey] = array($rowValue);
2481 } else {
2482 $matrix[$rowKey] = array_values($rowValue);
2483 }
2484 }
2485 $matrix = array_values($matrix);
2486 return array($matrixRows,$matrixColumns);
2487 } // function _getMatrixDimensions()
2488
2489
2500 private static function _resizeMatricesShrink(&$matrix1,&$matrix2,$matrix1Rows,$matrix1Columns,$matrix2Rows,$matrix2Columns) {
2501 if (($matrix2Columns < $matrix1Columns) || ($matrix2Rows < $matrix1Rows)) {
2502 if ($matrix2Rows < $matrix1Rows) {
2503 for ($i = $matrix2Rows; $i < $matrix1Rows; ++$i) {
2504 unset($matrix1[$i]);
2505 }
2506 }
2507 if ($matrix2Columns < $matrix1Columns) {
2508 for ($i = 0; $i < $matrix1Rows; ++$i) {
2509 for ($j = $matrix2Columns; $j < $matrix1Columns; ++$j) {
2510 unset($matrix1[$i][$j]);
2511 }
2512 }
2513 }
2514 }
2515
2516 if (($matrix1Columns < $matrix2Columns) || ($matrix1Rows < $matrix2Rows)) {
2517 if ($matrix1Rows < $matrix2Rows) {
2518 for ($i = $matrix1Rows; $i < $matrix2Rows; ++$i) {
2519 unset($matrix2[$i]);
2520 }
2521 }
2522 if ($matrix1Columns < $matrix2Columns) {
2523 for ($i = 0; $i < $matrix2Rows; ++$i) {
2524 for ($j = $matrix1Columns; $j < $matrix2Columns; ++$j) {
2525 unset($matrix2[$i][$j]);
2526 }
2527 }
2528 }
2529 }
2530 } // function _resizeMatricesShrink()
2531
2532
2543 private static function _resizeMatricesExtend(&$matrix1,&$matrix2,$matrix1Rows,$matrix1Columns,$matrix2Rows,$matrix2Columns) {
2544 if (($matrix2Columns < $matrix1Columns) || ($matrix2Rows < $matrix1Rows)) {
2545 if ($matrix2Columns < $matrix1Columns) {
2546 for ($i = 0; $i < $matrix2Rows; ++$i) {
2547 $x = $matrix2[$i][$matrix2Columns-1];
2548 for ($j = $matrix2Columns; $j < $matrix1Columns; ++$j) {
2549 $matrix2[$i][$j] = $x;
2550 }
2551 }
2552 }
2553 if ($matrix2Rows < $matrix1Rows) {
2554 $x = $matrix2[$matrix2Rows-1];
2555 for ($i = 0; $i < $matrix1Rows; ++$i) {
2556 $matrix2[$i] = $x;
2557 }
2558 }
2559 }
2560
2561 if (($matrix1Columns < $matrix2Columns) || ($matrix1Rows < $matrix2Rows)) {
2562 if ($matrix1Columns < $matrix2Columns) {
2563 for ($i = 0; $i < $matrix1Rows; ++$i) {
2564 $x = $matrix1[$i][$matrix1Columns-1];
2565 for ($j = $matrix1Columns; $j < $matrix2Columns; ++$j) {
2566 $matrix1[$i][$j] = $x;
2567 }
2568 }
2569 }
2570 if ($matrix1Rows < $matrix2Rows) {
2571 $x = $matrix1[$matrix1Rows-1];
2572 for ($i = 0; $i < $matrix2Rows; ++$i) {
2573 $matrix1[$i] = $x;
2574 }
2575 }
2576 }
2577 } // function _resizeMatricesExtend()
2578
2579
2586 private function _showValue($value) {
2587 if ($this->_debugLog->getWriteDebugLog()) {
2589 if (count($testArray) == 1) {
2590 $value = array_pop($testArray);
2591 }
2592
2593 if (is_array($value)) {
2594 $returnMatrix = array();
2595 $pad = $rpad = ', ';
2596 foreach($value as $row) {
2597 if (is_array($row)) {
2598 $returnMatrix[] = implode($pad,array_map(array($this,'_showValue'),$row));
2599 $rpad = '; ';
2600 } else {
2601 $returnMatrix[] = $this->_showValue($row);
2602 }
2603 }
2604 return '{ '.implode($rpad,$returnMatrix).' }';
2605 } elseif(is_string($value) && (trim($value,'"') == $value)) {
2606 return '"'.$value.'"';
2607 } elseif(is_bool($value)) {
2608 return ($value) ? self::$_localeBoolean['TRUE'] : self::$_localeBoolean['FALSE'];
2609 }
2610 }
2612 } // function _showValue()
2613
2614
2621 private function _showTypeDetails($value) {
2622 if ($this->_debugLog->getWriteDebugLog()) {
2624 if (count($testArray) == 1) {
2625 $value = array_pop($testArray);
2626 }
2627
2628 if ($value === NULL) {
2629 return 'a NULL value';
2630 } elseif (is_float($value)) {
2631 $typeString = 'a floating point number';
2632 } elseif(is_int($value)) {
2633 $typeString = 'an integer number';
2634 } elseif(is_bool($value)) {
2635 $typeString = 'a boolean';
2636 } elseif(is_array($value)) {
2637 $typeString = 'a matrix';
2638 } else {
2639 if ($value == '') {
2640 return 'an empty string';
2641 } elseif ($value{0} == '#') {
2642 return 'a '.$value.' error';
2643 } else {
2644 $typeString = 'a string';
2645 }
2646 }
2647 return $typeString.' with a value of '.$this->_showValue($value);
2648 }
2649 } // function _showTypeDetails()
2650
2651
2652 private function _convertMatrixReferences($formula) {
2653 static $matrixReplaceFrom = array('{',';','}');
2654 static $matrixReplaceTo = array('MKMATRIX(MKMATRIX(','),MKMATRIX(','))');
2655
2656 // Convert any Excel matrix references to the MKMATRIX() function
2657 if (strpos($formula,'{') !== FALSE) {
2658 // If there is the possibility of braces within a quoted string, then we don't treat those as matrix indicators
2659 if (strpos($formula,'"') !== FALSE) {
2660 // So instead we skip replacing in any quoted strings by only replacing in every other array element after we've exploded
2661 // the formula
2662 $temp = explode('"',$formula);
2663 // Open and Closed counts used for trapping mismatched braces in the formula
2664 $openCount = $closeCount = 0;
2665 $i = FALSE;
2666 foreach($temp as &$value) {
2667 // Only count/replace in alternating array entries
2668 if ($i = !$i) {
2669 $openCount += substr_count($value,'{');
2670 $closeCount += substr_count($value,'}');
2671 $value = str_replace($matrixReplaceFrom,$matrixReplaceTo,$value);
2672 }
2673 }
2674 unset($value);
2675 // Then rebuild the formula string
2676 $formula = implode('"',$temp);
2677 } else {
2678 // If there's no quoted strings, then we do a simple count/replace
2679 $openCount = substr_count($formula,'{');
2680 $closeCount = substr_count($formula,'}');
2681 $formula = str_replace($matrixReplaceFrom,$matrixReplaceTo,$formula);
2682 }
2683 // Trap for mismatched braces and trigger an appropriate error
2684 if ($openCount < $closeCount) {
2685 if ($openCount > 0) {
2686 return $this->_raiseFormulaError("Formula Error: Mismatched matrix braces '}'");
2687 } else {
2688 return $this->_raiseFormulaError("Formula Error: Unexpected '}' encountered");
2689 }
2690 } elseif ($openCount > $closeCount) {
2691 if ($closeCount > 0) {
2692 return $this->_raiseFormulaError("Formula Error: Mismatched matrix braces '{'");
2693 } else {
2694 return $this->_raiseFormulaError("Formula Error: Unexpected '{' encountered");
2695 }
2696 }
2697 }
2698
2699 return $formula;
2700 } // function _convertMatrixReferences()
2701
2702
2703 private static function _mkMatrix() {
2704 return func_get_args();
2705 } // function _mkMatrix()
2706
2707
2708 // Binary Operators
2709 // These operators always work on two values
2710 // Array key is the operator, the value indicates whether this is a left or right associative operator
2711 private static $_operatorAssociativity = array(
2712 '^' => 0, // Exponentiation
2713 '*' => 0, '/' => 0, // Multiplication and Division
2714 '+' => 0, '-' => 0, // Addition and Subtraction
2715 '&' => 0, // Concatenation
2716 '|' => 0, ':' => 0, // Intersect and Range
2717 '>' => 0, '<' => 0, '=' => 0, '>=' => 0, '<=' => 0, '<>' => 0 // Comparison
2718 );
2719
2720 // Comparison (Boolean) Operators
2721 // These operators work on two values, but always return a boolean result
2722 private static $_comparisonOperators = array('>' => TRUE, '<' => TRUE, '=' => TRUE, '>=' => TRUE, '<=' => TRUE, '<>' => TRUE);
2723
2724 // Operator Precedence
2725 // This list includes all valid operators, whether binary (including boolean) or unary (such as %)
2726 // Array key is the operator, the value is its precedence
2727 private static $_operatorPrecedence = array(
2728 ':' => 8, // Range
2729 '|' => 7, // Intersect
2730 '~' => 6, // Negation
2731 '%' => 5, // Percentage
2732 '^' => 4, // Exponentiation
2733 '*' => 3, '/' => 3, // Multiplication and Division
2734 '+' => 2, '-' => 2, // Addition and Subtraction
2735 '&' => 1, // Concatenation
2736 '>' => 0, '<' => 0, '=' => 0, '>=' => 0, '<=' => 0, '<>' => 0 // Comparison
2737 );
2738
2739 // Convert infix to postfix notation
2740 private function _parseFormula($formula, PHPExcel_Cell $pCell = NULL) {
2741 if (($formula = $this->_convertMatrixReferences(trim($formula))) === FALSE) {
2742 return FALSE;
2743 }
2744
2745 // If we're using cell caching, then $pCell may well be flushed back to the cache (which detaches the parent worksheet),
2746 // so we store the parent worksheet so that we can re-attach it when necessary
2747 $pCellParent = ($pCell !== NULL) ? $pCell->getWorksheet() : NULL;
2748
2749 $regexpMatchString = '/^('.self::CALCULATION_REGEXP_FUNCTION.
2750 '|'.self::CALCULATION_REGEXP_CELLREF.
2751 '|'.self::CALCULATION_REGEXP_NUMBER.
2752 '|'.self::CALCULATION_REGEXP_STRING.
2753 '|'.self::CALCULATION_REGEXP_OPENBRACE.
2754 '|'.self::CALCULATION_REGEXP_NAMEDRANGE.
2755 '|'.self::CALCULATION_REGEXP_ERROR.
2756 ')/si';
2757
2758 // Start with initialisation
2759 $index = 0;
2761 $output = array();
2762 $expectingOperator = FALSE; // We use this test in syntax-checking the expression to determine when a
2763 // - is a negation or + is a positive operator rather than an operation
2764 $expectingOperand = FALSE; // We use this test in syntax-checking the expression to determine whether an operand
2765 // should be null in a function call
2766 // The guts of the lexical parser
2767 // Loop through the formula extracting each operator and operand in turn
2768 while(TRUE) {
2769//echo 'Assessing Expression '.substr($formula, $index),PHP_EOL;
2770 $opCharacter = $formula{$index}; // Get the first character of the value at the current index position
2771//echo 'Initial character of expression block is '.$opCharacter,PHP_EOL;
2772 if ((isset(self::$_comparisonOperators[$opCharacter])) && (strlen($formula) > $index) && (isset(self::$_comparisonOperators[$formula{$index+1}]))) {
2773 $opCharacter .= $formula{++$index};
2774//echo 'Initial character of expression block is comparison operator '.$opCharacter.PHP_EOL;
2775 }
2776
2777 // Find out if we're currently at the beginning of a number, variable, cell reference, function, parenthesis or operand
2778 $isOperandOrFunction = preg_match($regexpMatchString, substr($formula, $index), $match);
2779//echo '$isOperandOrFunction is '.(($isOperandOrFunction) ? 'True' : 'False').PHP_EOL;
2780//var_dump($match);
2781
2782 if ($opCharacter == '-' && !$expectingOperator) { // Is it a negation instead of a minus?
2783//echo 'Element is a Negation operator',PHP_EOL;
2784 $stack->push('Unary Operator','~'); // Put a negation on the stack
2785 ++$index; // and drop the negation symbol
2786 } elseif ($opCharacter == '%' && $expectingOperator) {
2787//echo 'Element is a Percentage operator',PHP_EOL;
2788 $stack->push('Unary Operator','%'); // Put a percentage on the stack
2789 ++$index;
2790 } elseif ($opCharacter == '+' && !$expectingOperator) { // Positive (unary plus rather than binary operator plus) can be discarded?
2791//echo 'Element is a Positive number, not Plus operator',PHP_EOL;
2792 ++$index; // Drop the redundant plus symbol
2793 } elseif ((($opCharacter == '~') || ($opCharacter == '|')) && (!$isOperandOrFunction)) { // We have to explicitly deny a tilde or pipe, because they are legal
2794 return $this->_raiseFormulaError("Formula Error: Illegal character '~'"); // on the stack but not in the input expression
2795
2796 } elseif ((isset(self::$_operators[$opCharacter]) or $isOperandOrFunction) && $expectingOperator) { // Are we putting an operator on the stack?
2797//echo 'Element with value '.$opCharacter.' is an Operator',PHP_EOL;
2798 while($stack->count() > 0 &&
2799 ($o2 = $stack->last()) &&
2800 isset(self::$_operators[$o2['value']]) &&
2801 @(self::$_operatorAssociativity[$opCharacter] ? self::$_operatorPrecedence[$opCharacter] < self::$_operatorPrecedence[$o2['value']] : self::$_operatorPrecedence[$opCharacter] <= self::$_operatorPrecedence[$o2['value']])) {
2802 $output[] = $stack->pop(); // Swap operands and higher precedence operators from the stack to the output
2803 }
2804 $stack->push('Binary Operator',$opCharacter); // Finally put our current operator onto the stack
2805 ++$index;
2806 $expectingOperator = FALSE;
2807
2808 } elseif ($opCharacter == ')' && $expectingOperator) { // Are we expecting to close a parenthesis?
2809//echo 'Element is a Closing bracket',PHP_EOL;
2810 $expectingOperand = FALSE;
2811 while (($o2 = $stack->pop()) && $o2['value'] != '(') { // Pop off the stack back to the last (
2812 if ($o2 === NULL) return $this->_raiseFormulaError('Formula Error: Unexpected closing brace ")"');
2813 else $output[] = $o2;
2814 }
2815 $d = $stack->last(2);
2816 if (preg_match('/^'.self::CALCULATION_REGEXP_FUNCTION.'$/i', $d['value'], $matches)) { // Did this parenthesis just close a function?
2817 $functionName = $matches[1]; // Get the function name
2818//echo 'Closed Function is '.$functionName,PHP_EOL;
2819 $d = $stack->pop();
2820 $argumentCount = $d['value']; // See how many arguments there were (argument count is the next value stored on the stack)
2821//if ($argumentCount == 0) {
2822// echo 'With no arguments',PHP_EOL;
2823//} elseif ($argumentCount == 1) {
2824// echo 'With 1 argument',PHP_EOL;
2825//} else {
2826// echo 'With '.$argumentCount.' arguments',PHP_EOL;
2827//}
2828 $output[] = $d; // Dump the argument count on the output
2829 $output[] = $stack->pop(); // Pop the function and push onto the output
2830 if (isset(self::$_controlFunctions[$functionName])) {
2831//echo 'Built-in function '.$functionName,PHP_EOL;
2832 $expectedArgumentCount = self::$_controlFunctions[$functionName]['argumentCount'];
2833 $functionCall = self::$_controlFunctions[$functionName]['functionCall'];
2834 } elseif (isset(self::$_PHPExcelFunctions[$functionName])) {
2835//echo 'PHPExcel function '.$functionName,PHP_EOL;
2836 $expectedArgumentCount = self::$_PHPExcelFunctions[$functionName]['argumentCount'];
2837 $functionCall = self::$_PHPExcelFunctions[$functionName]['functionCall'];
2838 } else { // did we somehow push a non-function on the stack? this should never happen
2839 return $this->_raiseFormulaError("Formula Error: Internal error, non-function on stack");
2840 }
2841 // Check the argument count
2842 $argumentCountError = FALSE;
2843 if (is_numeric($expectedArgumentCount)) {
2844 if ($expectedArgumentCount < 0) {
2845//echo '$expectedArgumentCount is between 0 and '.abs($expectedArgumentCount),PHP_EOL;
2846 if ($argumentCount > abs($expectedArgumentCount)) {
2847 $argumentCountError = TRUE;
2848 $expectedArgumentCountString = 'no more than '.abs($expectedArgumentCount);
2849 }
2850 } else {
2851//echo '$expectedArgumentCount is numeric '.$expectedArgumentCount,PHP_EOL;
2852 if ($argumentCount != $expectedArgumentCount) {
2853 $argumentCountError = TRUE;
2854 $expectedArgumentCountString = $expectedArgumentCount;
2855 }
2856 }
2857 } elseif ($expectedArgumentCount != '*') {
2858 $isOperandOrFunction = preg_match('/(\d*)([-+,])(\d*)/',$expectedArgumentCount,$argMatch);
2859//print_r($argMatch);
2860//echo PHP_EOL;
2861 switch ($argMatch[2]) {
2862 case '+' :
2863 if ($argumentCount < $argMatch[1]) {
2864 $argumentCountError = TRUE;
2865 $expectedArgumentCountString = $argMatch[1].' or more ';
2866 }
2867 break;
2868 case '-' :
2869 if (($argumentCount < $argMatch[1]) || ($argumentCount > $argMatch[3])) {
2870 $argumentCountError = TRUE;
2871 $expectedArgumentCountString = 'between '.$argMatch[1].' and '.$argMatch[3];
2872 }
2873 break;
2874 case ',' :
2875 if (($argumentCount != $argMatch[1]) && ($argumentCount != $argMatch[3])) {
2876 $argumentCountError = TRUE;
2877 $expectedArgumentCountString = 'either '.$argMatch[1].' or '.$argMatch[3];
2878 }
2879 break;
2880 }
2881 }
2882 if ($argumentCountError) {
2883 return $this->_raiseFormulaError("Formula Error: Wrong number of arguments for $functionName() function: $argumentCount given, ".$expectedArgumentCountString." expected");
2884 }
2885 }
2886 ++$index;
2887
2888 } elseif ($opCharacter == ',') { // Is this the separator for function arguments?
2889//echo 'Element is a Function argument separator',PHP_EOL;
2890 while (($o2 = $stack->pop()) && $o2['value'] != '(') { // Pop off the stack back to the last (
2891 if ($o2 === NULL) return $this->_raiseFormulaError("Formula Error: Unexpected ,");
2892 else $output[] = $o2; // pop the argument expression stuff and push onto the output
2893 }
2894 // If we've a comma when we're expecting an operand, then what we actually have is a null operand;
2895 // so push a null onto the stack
2896 if (($expectingOperand) || (!$expectingOperator)) {
2897 $output[] = array('type' => 'NULL Value', 'value' => self::$_ExcelConstants['NULL'], 'reference' => NULL);
2898 }
2899 // make sure there was a function
2900 $d = $stack->last(2);
2901 if (!preg_match('/^'.self::CALCULATION_REGEXP_FUNCTION.'$/i', $d['value'], $matches))
2902 return $this->_raiseFormulaError("Formula Error: Unexpected ,");
2903 $d = $stack->pop();
2904 $stack->push($d['type'],++$d['value'],$d['reference']); // increment the argument count
2905 $stack->push('Brace', '('); // put the ( back on, we'll need to pop back to it again
2906 $expectingOperator = FALSE;
2907 $expectingOperand = TRUE;
2908 ++$index;
2909
2910 } elseif ($opCharacter == '(' && !$expectingOperator) {
2911// echo 'Element is an Opening Bracket<br />';
2912 $stack->push('Brace', '(');
2913 ++$index;
2914
2915 } elseif ($isOperandOrFunction && !$expectingOperator) { // do we now have a function/variable/number?
2916 $expectingOperator = TRUE;
2917 $expectingOperand = FALSE;
2918 $val = $match[1];
2919 $length = strlen($val);
2920// echo 'Element with value '.$val.' is an Operand, Variable, Constant, String, Number, Cell Reference or Function<br />';
2921
2922 if (preg_match('/^'.self::CALCULATION_REGEXP_FUNCTION.'$/i', $val, $matches)) {
2923 $val = preg_replace('/\s/u','',$val);
2924// echo 'Element '.$val.' is a Function<br />';
2925 if (isset(self::$_PHPExcelFunctions[strtoupper($matches[1])]) || isset(self::$_controlFunctions[strtoupper($matches[1])])) { // it's a function
2926 $stack->push('Function', strtoupper($val));
2927 $ax = preg_match('/^\s*(\s*\‍))/ui', substr($formula, $index+$length), $amatch);
2928 if ($ax) {
2929 $stack->push('Operand Count for Function '.strtoupper($val).')', 0);
2930 $expectingOperator = TRUE;
2931 } else {
2932 $stack->push('Operand Count for Function '.strtoupper($val).')', 1);
2933 $expectingOperator = FALSE;
2934 }
2935 $stack->push('Brace', '(');
2936 } else { // it's a var w/ implicit multiplication
2937 $output[] = array('type' => 'Value', 'value' => $matches[1], 'reference' => NULL);
2938 }
2939 } elseif (preg_match('/^'.self::CALCULATION_REGEXP_CELLREF.'$/i', $val, $matches)) {
2940// echo 'Element '.$val.' is a Cell reference<br />';
2941 // Watch for this case-change when modifying to allow cell references in different worksheets...
2942 // Should only be applied to the actual cell column, not the worksheet name
2943
2944 // If the last entry on the stack was a : operator, then we have a cell range reference
2945 $testPrevOp = $stack->last(1);
2946 if ($testPrevOp['value'] == ':') {
2947 // If we have a worksheet reference, then we're playing with a 3D reference
2948 if ($matches[2] == '') {
2949 // Otherwise, we 'inherit' the worksheet reference from the start cell reference
2950 // The start of the cell range reference should be the last entry in $output
2951 $startCellRef = $output[count($output)-1]['value'];
2952 preg_match('/^'.self::CALCULATION_REGEXP_CELLREF.'$/i', $startCellRef, $startMatches);
2953 if ($startMatches[2] > '') {
2954 $val = $startMatches[2].'!'.$val;
2955 }
2956 } else {
2957 return $this->_raiseFormulaError("3D Range references are not yet supported");
2958 }
2959 }
2960
2961 $output[] = array('type' => 'Cell Reference', 'value' => $val, 'reference' => $val);
2962// $expectingOperator = FALSE;
2963 } else { // it's a variable, constant, string, number or boolean
2964// echo 'Element is a Variable, Constant, String, Number or Boolean<br />';
2965 // If the last entry on the stack was a : operator, then we may have a row or column range reference
2966 $testPrevOp = $stack->last(1);
2967 if ($testPrevOp['value'] == ':') {
2968 $startRowColRef = $output[count($output)-1]['value'];
2969 $rangeWS1 = '';
2970 if (strpos('!',$startRowColRef) !== FALSE) {
2971 list($rangeWS1,$startRowColRef) = explode('!',$startRowColRef);
2972 }
2973 if ($rangeWS1 != '') $rangeWS1 .= '!';
2974 $rangeWS2 = $rangeWS1;
2975 if (strpos('!',$val) !== FALSE) {
2976 list($rangeWS2,$val) = explode('!',$val);
2977 }
2978 if ($rangeWS2 != '') $rangeWS2 .= '!';
2979 if ((is_integer($startRowColRef)) && (ctype_digit($val)) &&
2980 ($startRowColRef <= 1048576) && ($val <= 1048576)) {
2981 // Row range
2982 $endRowColRef = ($pCellParent !== NULL) ? $pCellParent->getHighestColumn() : 'XFD'; // Max 16,384 columns for Excel2007
2983 $output[count($output)-1]['value'] = $rangeWS1.'A'.$startRowColRef;
2984 $val = $rangeWS2.$endRowColRef.$val;
2985 } elseif ((ctype_alpha($startRowColRef)) && (ctype_alpha($val)) &&
2986 (strlen($startRowColRef) <= 3) && (strlen($val) <= 3)) {
2987 // Column range
2988 $endRowColRef = ($pCellParent !== NULL) ? $pCellParent->getHighestRow() : 1048576; // Max 1,048,576 rows for Excel2007
2989 $output[count($output)-1]['value'] = $rangeWS1.strtoupper($startRowColRef).'1';
2990 $val = $rangeWS2.$val.$endRowColRef;
2991 }
2992 }
2993
2994 $localeConstant = FALSE;
2995 if ($opCharacter == '"') {
2996// echo 'Element is a String<br />';
2997 // UnEscape any quotes within the string
2998 $val = self::_wrapResult(str_replace('""','"',self::_unwrapResult($val)));
2999 } elseif (is_numeric($val)) {
3000// echo 'Element is a Number<br />';
3001 if ((strpos($val,'.') !== FALSE) || (stripos($val,'e') !== FALSE) || ($val > PHP_INT_MAX) || ($val < -PHP_INT_MAX)) {
3002// echo 'Casting '.$val.' to float<br />';
3003 $val = (float) $val;
3004 } else {
3005// echo 'Casting '.$val.' to integer<br />';
3006 $val = (integer) $val;
3007 }
3008 } elseif (isset(self::$_ExcelConstants[trim(strtoupper($val))])) {
3009 $excelConstant = trim(strtoupper($val));
3010// echo 'Element '.$excelConstant.' is an Excel Constant<br />';
3011 $val = self::$_ExcelConstants[$excelConstant];
3012 } elseif (($localeConstant = array_search(trim(strtoupper($val)), self::$_localeBoolean)) !== FALSE) {
3013// echo 'Element '.$localeConstant.' is an Excel Constant<br />';
3014 $val = self::$_ExcelConstants[$localeConstant];
3015 }
3016 $details = array('type' => 'Value', 'value' => $val, 'reference' => NULL);
3017 if ($localeConstant) { $details['localeValue'] = $localeConstant; }
3018 $output[] = $details;
3019 }
3020 $index += $length;
3021
3022 } elseif ($opCharacter == '$') { // absolute row or column range
3023 ++$index;
3024 } elseif ($opCharacter == ')') { // miscellaneous error checking
3025 if ($expectingOperand) {
3026 $output[] = array('type' => 'NULL Value', 'value' => self::$_ExcelConstants['NULL'], 'reference' => NULL);
3027 $expectingOperand = FALSE;
3028 $expectingOperator = TRUE;
3029 } else {
3030 return $this->_raiseFormulaError("Formula Error: Unexpected ')'");
3031 }
3032 } elseif (isset(self::$_operators[$opCharacter]) && !$expectingOperator) {
3033 return $this->_raiseFormulaError("Formula Error: Unexpected operator '$opCharacter'");
3034 } else { // I don't even want to know what you did to get here
3035 return $this->_raiseFormulaError("Formula Error: An unexpected error occured");
3036 }
3037 // Test for end of formula string
3038 if ($index == strlen($formula)) {
3039 // Did we end with an operator?.
3040 // Only valid for the % unary operator
3041 if ((isset(self::$_operators[$opCharacter])) && ($opCharacter != '%')) {
3042 return $this->_raiseFormulaError("Formula Error: Operator '$opCharacter' has no operands");
3043 } else {
3044 break;
3045 }
3046 }
3047 // Ignore white space
3048 while (($formula{$index} == "\n") || ($formula{$index} == "\r")) {
3049 ++$index;
3050 }
3051 if ($formula{$index} == ' ') {
3052 while ($formula{$index} == ' ') {
3053 ++$index;
3054 }
3055 // If we're expecting an operator, but only have a space between the previous and next operands (and both are
3056 // Cell References) then we have an INTERSECTION operator
3057// echo 'Possible Intersect Operator<br />';
3058 if (($expectingOperator) && (preg_match('/^'.self::CALCULATION_REGEXP_CELLREF.'.*/Ui', substr($formula, $index), $match)) &&
3059 ($output[count($output)-1]['type'] == 'Cell Reference')) {
3060// echo 'Element is an Intersect Operator<br />';
3061 while($stack->count() > 0 &&
3062 ($o2 = $stack->last()) &&
3063 isset(self::$_operators[$o2['value']]) &&
3064 @(self::$_operatorAssociativity[$opCharacter] ? self::$_operatorPrecedence[$opCharacter] < self::$_operatorPrecedence[$o2['value']] : self::$_operatorPrecedence[$opCharacter] <= self::$_operatorPrecedence[$o2['value']])) {
3065 $output[] = $stack->pop(); // Swap operands and higher precedence operators from the stack to the output
3066 }
3067 $stack->push('Binary Operator','|'); // Put an Intersect Operator on the stack
3068 $expectingOperator = FALSE;
3069 }
3070 }
3071 }
3072
3073 while (($op = $stack->pop()) !== NULL) { // pop everything off the stack and push onto output
3074 if ((is_array($op) && $op['value'] == '(') || ($op === '('))
3075 return $this->_raiseFormulaError("Formula Error: Expecting ')'"); // if there are any opening braces on the stack, then braces were unbalanced
3076 $output[] = $op;
3077 }
3078 return $output;
3079 } // function _parseFormula()
3080
3081
3082 private static function _dataTestReference(&$operandData)
3083 {
3084 $operand = $operandData['value'];
3085 if (($operandData['reference'] === NULL) && (is_array($operand))) {
3086 $rKeys = array_keys($operand);
3087 $rowKey = array_shift($rKeys);
3088 $cKeys = array_keys(array_keys($operand[$rowKey]));
3089 $colKey = array_shift($cKeys);
3090 if (ctype_upper($colKey)) {
3091 $operandData['reference'] = $colKey.$rowKey;
3092 }
3093 }
3094 return $operand;
3095 }
3096
3097 // evaluate postfix notation
3098 private function _processTokenStack($tokens, $cellID = NULL, PHPExcel_Cell $pCell = NULL) {
3099 if ($tokens == FALSE) return FALSE;
3100
3101 // If we're using cell caching, then $pCell may well be flushed back to the cache (which detaches the parent cell collection),
3102 // so we store the parent cell collection so that we can re-attach it when necessary
3103 $pCellWorksheet = ($pCell !== NULL) ? $pCell->getWorksheet() : NULL;
3104 $pCellParent = ($pCell !== NULL) ? $pCell->getParent() : null;
3106
3107 // Loop through each token in turn
3108 foreach ($tokens as $tokenData) {
3109// print_r($tokenData);
3110// echo '<br />';
3111 $token = $tokenData['value'];
3112// echo '<b>Token is '.$token.'</b><br />';
3113 // if the token is a binary operator, pop the top two values off the stack, do the operation, and push the result back on the stack
3114 if (isset(self::$_binaryOperators[$token])) {
3115// echo 'Token is a binary operator<br />';
3116 // We must have two operands, error if we don't
3117 if (($operand2Data = $stack->pop()) === NULL) return $this->_raiseFormulaError('Internal error - Operand value missing from stack');
3118 if (($operand1Data = $stack->pop()) === NULL) return $this->_raiseFormulaError('Internal error - Operand value missing from stack');
3119
3120 $operand1 = self::_dataTestReference($operand1Data);
3121 $operand2 = self::_dataTestReference($operand2Data);
3122
3123 // Log what we're doing
3124 if ($token == ':') {
3125 $this->_debugLog->writeDebugLog('Evaluating Range ', $this->_showValue($operand1Data['reference']), ' ', $token, ' ', $this->_showValue($operand2Data['reference']));
3126 } else {
3127 $this->_debugLog->writeDebugLog('Evaluating ', $this->_showValue($operand1), ' ', $token, ' ', $this->_showValue($operand2));
3128 }
3129
3130 // Process the operation in the appropriate manner
3131 switch ($token) {
3132 // Comparison (Boolean) Operators
3133 case '>' : // Greater than
3134 case '<' : // Less than
3135 case '>=' : // Greater than or Equal to
3136 case '<=' : // Less than or Equal to
3137 case '=' : // Equality
3138 case '<>' : // Inequality
3139 $this->_executeBinaryComparisonOperation($cellID,$operand1,$operand2,$token,$stack);
3140 break;
3141 // Binary Operators
3142 case ':' : // Range
3143 $sheet1 = $sheet2 = '';
3144 if (strpos($operand1Data['reference'],'!') !== FALSE) {
3145 list($sheet1,$operand1Data['reference']) = explode('!',$operand1Data['reference']);
3146 } else {
3147 $sheet1 = ($pCellParent !== NULL) ? $pCellWorksheet->getTitle() : '';
3148 }
3149 if (strpos($operand2Data['reference'],'!') !== FALSE) {
3150 list($sheet2,$operand2Data['reference']) = explode('!',$operand2Data['reference']);
3151 } else {
3152 $sheet2 = $sheet1;
3153 }
3154 if ($sheet1 == $sheet2) {
3155 if ($operand1Data['reference'] === NULL) {
3156 if ((trim($operand1Data['value']) != '') && (is_numeric($operand1Data['value']))) {
3157 $operand1Data['reference'] = $pCell->getColumn().$operand1Data['value'];
3158 } elseif (trim($operand1Data['reference']) == '') {
3159 $operand1Data['reference'] = $pCell->getCoordinate();
3160 } else {
3161 $operand1Data['reference'] = $operand1Data['value'].$pCell->getRow();
3162 }
3163 }
3164 if ($operand2Data['reference'] === NULL) {
3165 if ((trim($operand2Data['value']) != '') && (is_numeric($operand2Data['value']))) {
3166 $operand2Data['reference'] = $pCell->getColumn().$operand2Data['value'];
3167 } elseif (trim($operand2Data['reference']) == '') {
3168 $operand2Data['reference'] = $pCell->getCoordinate();
3169 } else {
3170 $operand2Data['reference'] = $operand2Data['value'].$pCell->getRow();
3171 }
3172 }
3173
3174 $oData = array_merge(explode(':',$operand1Data['reference']),explode(':',$operand2Data['reference']));
3175 $oCol = $oRow = array();
3176 foreach($oData as $oDatum) {
3178 $oCol[] = PHPExcel_Cell::columnIndexFromString($oCR[0]) - 1;
3179 $oRow[] = $oCR[1];
3180 }
3181 $cellRef = PHPExcel_Cell::stringFromColumnIndex(min($oCol)).min($oRow).':'.PHPExcel_Cell::stringFromColumnIndex(max($oCol)).max($oRow);
3182 if ($pCellParent !== NULL) {
3183 $cellValue = $this->extractCellRange($cellRef, $this->_workbook->getSheetByName($sheet1), FALSE);
3184 } else {
3185 return $this->_raiseFormulaError('Unable to access Cell Reference');
3186 }
3187 $stack->push('Cell Reference',$cellValue,$cellRef);
3188 } else {
3189 $stack->push('Error',PHPExcel_Calculation_Functions::REF(),NULL);
3190 }
3191
3192 break;
3193 case '+' : // Addition
3194 $this->_executeNumericBinaryOperation($cellID,$operand1,$operand2,$token,'plusEquals',$stack);
3195 break;
3196 case '-' : // Subtraction
3197 $this->_executeNumericBinaryOperation($cellID,$operand1,$operand2,$token,'minusEquals',$stack);
3198 break;
3199 case '*' : // Multiplication
3200 $this->_executeNumericBinaryOperation($cellID,$operand1,$operand2,$token,'arrayTimesEquals',$stack);
3201 break;
3202 case '/' : // Division
3203 $this->_executeNumericBinaryOperation($cellID,$operand1,$operand2,$token,'arrayRightDivide',$stack);
3204 break;
3205 case '^' : // Exponential
3206 $this->_executeNumericBinaryOperation($cellID,$operand1,$operand2,$token,'power',$stack);
3207 break;
3208 case '&' : // Concatenation
3209 // If either of the operands is a matrix, we need to treat them both as matrices
3210 // (converting the other operand to a matrix if need be); then perform the required
3211 // matrix operation
3212 if (is_bool($operand1)) {
3213 $operand1 = ($operand1) ? self::$_localeBoolean['TRUE'] : self::$_localeBoolean['FALSE'];
3214 }
3215 if (is_bool($operand2)) {
3216 $operand2 = ($operand2) ? self::$_localeBoolean['TRUE'] : self::$_localeBoolean['FALSE'];
3217 }
3218 if ((is_array($operand1)) || (is_array($operand2))) {
3219 // Ensure that both operands are arrays/matrices
3220 self::_checkMatrixOperands($operand1,$operand2,2);
3221 try {
3222 // Convert operand 1 from a PHP array to a matrix
3223 $matrix = new PHPExcel_Shared_JAMA_Matrix($operand1);
3224 // Perform the required operation against the operand 1 matrix, passing in operand 2
3225 $matrixResult = $matrix->concat($operand2);
3226 $result = $matrixResult->getArray();
3227 } catch (PHPExcel_Exception $ex) {
3228 $this->_debugLog->writeDebugLog('JAMA Matrix Exception: ', $ex->getMessage());
3229 $result = '#VALUE!';
3230 }
3231 } else {
3232 $result = '"'.str_replace('""','"',self::_unwrapResult($operand1,'"').self::_unwrapResult($operand2,'"')).'"';
3233 }
3234 $this->_debugLog->writeDebugLog('Evaluation Result is ', $this->_showTypeDetails($result));
3235 $stack->push('Value',$result);
3236 break;
3237 case '|' : // Intersect
3238 $rowIntersect = array_intersect_key($operand1,$operand2);
3239 $cellIntersect = $oCol = $oRow = array();
3240 foreach(array_keys($rowIntersect) as $row) {
3241 $oRow[] = $row;
3242 foreach($rowIntersect[$row] as $col => $data) {
3243 $oCol[] = PHPExcel_Cell::columnIndexFromString($col) - 1;
3244 $cellIntersect[$row] = array_intersect_key($operand1[$row],$operand2[$row]);
3245 }
3246 }
3247 $cellRef = PHPExcel_Cell::stringFromColumnIndex(min($oCol)).min($oRow).':'.PHPExcel_Cell::stringFromColumnIndex(max($oCol)).max($oRow);
3248 $this->_debugLog->writeDebugLog('Evaluation Result is ', $this->_showTypeDetails($cellIntersect));
3249 $stack->push('Value',$cellIntersect,$cellRef);
3250 break;
3251 }
3252
3253 // if the token is a unary operator, pop one value off the stack, do the operation, and push it back on
3254 } elseif (($token === '~') || ($token === '%')) {
3255// echo 'Token is a unary operator<br />';
3256 if (($arg = $stack->pop()) === NULL) return $this->_raiseFormulaError('Internal error - Operand value missing from stack');
3257 $arg = $arg['value'];
3258 if ($token === '~') {
3259// echo 'Token is a negation operator<br />';
3260 $this->_debugLog->writeDebugLog('Evaluating Negation of ', $this->_showValue($arg));
3261 $multiplier = -1;
3262 } else {
3263// echo 'Token is a percentile operator<br />';
3264 $this->_debugLog->writeDebugLog('Evaluating Percentile of ', $this->_showValue($arg));
3265 $multiplier = 0.01;
3266 }
3267 if (is_array($arg)) {
3268 self::_checkMatrixOperands($arg,$multiplier,2);
3269 try {
3270 $matrix1 = new PHPExcel_Shared_JAMA_Matrix($arg);
3271 $matrixResult = $matrix1->arrayTimesEquals($multiplier);
3272 $result = $matrixResult->getArray();
3273 } catch (PHPExcel_Exception $ex) {
3274 $this->_debugLog->writeDebugLog('JAMA Matrix Exception: ', $ex->getMessage());
3275 $result = '#VALUE!';
3276 }
3277 $this->_debugLog->writeDebugLog('Evaluation Result is ', $this->_showTypeDetails($result));
3278 $stack->push('Value',$result);
3279 } else {
3280 $this->_executeNumericBinaryOperation($cellID,$multiplier,$arg,'*','arrayTimesEquals',$stack);
3281 }
3282
3283 } elseif (preg_match('/^'.self::CALCULATION_REGEXP_CELLREF.'$/i', $token, $matches)) {
3284 $cellRef = NULL;
3285// echo 'Element '.$token.' is a Cell reference<br />';
3286 if (isset($matches[8])) {
3287// echo 'Reference is a Range of cells<br />';
3288 if ($pCell === NULL) {
3289// We can't access the range, so return a REF error
3291 } else {
3292 $cellRef = $matches[6].$matches[7].':'.$matches[9].$matches[10];
3293 if ($matches[2] > '') {
3294 $matches[2] = trim($matches[2],"\"'");
3295 if ((strpos($matches[2],'[') !== FALSE) || (strpos($matches[2],']') !== FALSE)) {
3296 // It's a Reference to an external workbook (not currently supported)
3297 return $this->_raiseFormulaError('Unable to access External Workbook');
3298 }
3299 $matches[2] = trim($matches[2],"\"'");
3300// echo '$cellRef='.$cellRef.' in worksheet '.$matches[2].'<br />';
3301 $this->_debugLog->writeDebugLog('Evaluating Cell Range ', $cellRef, ' in worksheet ', $matches[2]);
3302 if ($pCellParent !== NULL) {
3303 $cellValue = $this->extractCellRange($cellRef, $this->_workbook->getSheetByName($matches[2]), FALSE);
3304 } else {
3305 return $this->_raiseFormulaError('Unable to access Cell Reference');
3306 }
3307 $this->_debugLog->writeDebugLog('Evaluation Result for cells ', $cellRef, ' in worksheet ', $matches[2], ' is ', $this->_showTypeDetails($cellValue));
3308// $cellRef = $matches[2].'!'.$cellRef;
3309 } else {
3310// echo '$cellRef='.$cellRef.' in current worksheet<br />';
3311 $this->_debugLog->writeDebugLog('Evaluating Cell Range ', $cellRef, ' in current worksheet');
3312 if ($pCellParent !== NULL) {
3313 $cellValue = $this->extractCellRange($cellRef, $pCellWorksheet, FALSE);
3314 } else {
3315 return $this->_raiseFormulaError('Unable to access Cell Reference');
3316 }
3317 $this->_debugLog->writeDebugLog('Evaluation Result for cells ', $cellRef, ' is ', $this->_showTypeDetails($cellValue));
3318 }
3319 }
3320 } else {
3321// echo 'Reference is a single Cell<br />';
3322 if ($pCell === NULL) {
3323// We can't access the cell, so return a REF error
3325 } else {
3326 $cellRef = $matches[6].$matches[7];
3327 if ($matches[2] > '') {
3328 $matches[2] = trim($matches[2],"\"'");
3329 if ((strpos($matches[2],'[') !== FALSE) || (strpos($matches[2],']') !== FALSE)) {
3330 // It's a Reference to an external workbook (not currently supported)
3331 return $this->_raiseFormulaError('Unable to access External Workbook');
3332 }
3333// echo '$cellRef='.$cellRef.' in worksheet '.$matches[2].'<br />';
3334 $this->_debugLog->writeDebugLog('Evaluating Cell ', $cellRef, ' in worksheet ', $matches[2]);
3335 if ($pCellParent !== NULL) {
3336 $cellSheet = $this->_workbook->getSheetByName($matches[2]);
3337 if ($cellSheet && $cellSheet->cellExists($cellRef)) {
3338 $cellValue = $this->extractCellRange($cellRef, $this->_workbook->getSheetByName($matches[2]), FALSE);
3339 $pCell->attach($pCellParent);
3340 } else {
3341 $cellValue = NULL;
3342 }
3343 } else {
3344 return $this->_raiseFormulaError('Unable to access Cell Reference');
3345 }
3346 $this->_debugLog->writeDebugLog('Evaluation Result for cell ', $cellRef, ' in worksheet ', $matches[2], ' is ', $this->_showTypeDetails($cellValue));
3347// $cellRef = $matches[2].'!'.$cellRef;
3348 } else {
3349// echo '$cellRef='.$cellRef.' in current worksheet<br />';
3350 $this->_debugLog->writeDebugLog('Evaluating Cell ', $cellRef, ' in current worksheet');
3351 if ($pCellParent->isDataSet($cellRef)) {
3352 $cellValue = $this->extractCellRange($cellRef, $pCellWorksheet, FALSE);
3353 $pCell->attach($pCellParent);
3354 } else {
3355 $cellValue = NULL;
3356 }
3357 $this->_debugLog->writeDebugLog('Evaluation Result for cell ', $cellRef, ' is ', $this->_showTypeDetails($cellValue));
3358 }
3359 }
3360 }
3361 $stack->push('Value',$cellValue,$cellRef);
3362
3363 // if the token is a function, pop arguments off the stack, hand them to the function, and push the result back on
3364 } elseif (preg_match('/^'.self::CALCULATION_REGEXP_FUNCTION.'$/i', $token, $matches)) {
3365// echo 'Token is a function<br />';
3366 $functionName = $matches[1];
3367 $argCount = $stack->pop();
3368 $argCount = $argCount['value'];
3369 if ($functionName != 'MKMATRIX') {
3370 $this->_debugLog->writeDebugLog('Evaluating Function ', self::_localeFunc($functionName), '() with ', (($argCount == 0) ? 'no' : $argCount), ' argument', (($argCount == 1) ? '' : 's'));
3371 }
3372 if ((isset(self::$_PHPExcelFunctions[$functionName])) || (isset(self::$_controlFunctions[$functionName]))) { // function
3373 if (isset(self::$_PHPExcelFunctions[$functionName])) {
3374 $functionCall = self::$_PHPExcelFunctions[$functionName]['functionCall'];
3375 $passByReference = isset(self::$_PHPExcelFunctions[$functionName]['passByReference']);
3376 $passCellReference = isset(self::$_PHPExcelFunctions[$functionName]['passCellReference']);
3377 } elseif (isset(self::$_controlFunctions[$functionName])) {
3378 $functionCall = self::$_controlFunctions[$functionName]['functionCall'];
3379 $passByReference = isset(self::$_controlFunctions[$functionName]['passByReference']);
3380 $passCellReference = isset(self::$_controlFunctions[$functionName]['passCellReference']);
3381 }
3382 // get the arguments for this function
3383// echo 'Function '.$functionName.' expects '.$argCount.' arguments<br />';
3384 $args = $argArrayVals = array();
3385 for ($i = 0; $i < $argCount; ++$i) {
3386 $arg = $stack->pop();
3387 $a = $argCount - $i - 1;
3388 if (($passByReference) &&
3389 (isset(self::$_PHPExcelFunctions[$functionName]['passByReference'][$a])) &&
3390 (self::$_PHPExcelFunctions[$functionName]['passByReference'][$a])) {
3391 if ($arg['reference'] === NULL) {
3392 $args[] = $cellID;
3393 if ($functionName != 'MKMATRIX') { $argArrayVals[] = $this->_showValue($cellID); }
3394 } else {
3395 $args[] = $arg['reference'];
3396 if ($functionName != 'MKMATRIX') { $argArrayVals[] = $this->_showValue($arg['reference']); }
3397 }
3398 } else {
3399 $args[] = self::_unwrapResult($arg['value']);
3400 if ($functionName != 'MKMATRIX') { $argArrayVals[] = $this->_showValue($arg['value']); }
3401 }
3402 }
3403 // Reverse the order of the arguments
3404 krsort($args);
3405 if (($passByReference) && ($argCount == 0)) {
3406 $args[] = $cellID;
3407 $argArrayVals[] = $this->_showValue($cellID);
3408 }
3409// echo 'Arguments are: ';
3410// print_r($args);
3411// echo '<br />';
3412 if ($functionName != 'MKMATRIX') {
3413 if ($this->_debugLog->getWriteDebugLog()) {
3414 krsort($argArrayVals);
3415 $this->_debugLog->writeDebugLog('Evaluating ', self::_localeFunc($functionName), '( ', implode(self::$_localeArgumentSeparator.' ',PHPExcel_Calculation_Functions::flattenArray($argArrayVals)), ' )');
3416 }
3417 }
3418 // Process each argument in turn, building the return value as an array
3419// if (($argCount == 1) && (is_array($args[1])) && ($functionName != 'MKMATRIX')) {
3420// $operand1 = $args[1];
3421// $this->_debugLog->writeDebugLog('Argument is a matrix: ', $this->_showValue($operand1));
3422// $result = array();
3423// $row = 0;
3424// foreach($operand1 as $args) {
3425// if (is_array($args)) {
3426// foreach($args as $arg) {
3427// $this->_debugLog->writeDebugLog('Evaluating ', self::_localeFunc($functionName), '( ', $this->_showValue($arg), ' )');
3428// $r = call_user_func_array($functionCall,$arg);
3429// $this->_debugLog->writeDebugLog('Evaluation Result for ', self::_localeFunc($functionName), '() function call is ', $this->_showTypeDetails($r));
3430// $result[$row][] = $r;
3431// }
3432// ++$row;
3433// } else {
3434// $this->_debugLog->writeDebugLog('Evaluating ', self::_localeFunc($functionName), '( ', $this->_showValue($args), ' )');
3435// $r = call_user_func_array($functionCall,$args);
3436// $this->_debugLog->writeDebugLog('Evaluation Result for ', self::_localeFunc($functionName), '() function call is ', $this->_showTypeDetails($r));
3437// $result[] = $r;
3438// }
3439// }
3440// } else {
3441 // Process the argument with the appropriate function call
3442 if ($passCellReference) {
3443 $args[] = $pCell;
3444 }
3445 if (strpos($functionCall,'::') !== FALSE) {
3446 $result = call_user_func_array(explode('::',$functionCall),$args);
3447 } else {
3448 foreach($args as &$arg) {
3450 }
3451 unset($arg);
3452 $result = call_user_func_array($functionCall,$args);
3453 }
3454// }
3455 if ($functionName != 'MKMATRIX') {
3456 $this->_debugLog->writeDebugLog('Evaluation Result for ', self::_localeFunc($functionName), '() function call is ', $this->_showTypeDetails($result));
3457 }
3458 $stack->push('Value',self::_wrapResult($result));
3459 }
3460
3461 } else {
3462 // if the token is a number, boolean, string or an Excel error, push it onto the stack
3463 if (isset(self::$_ExcelConstants[strtoupper($token)])) {
3464 $excelConstant = strtoupper($token);
3465// echo 'Token is a PHPExcel constant: '.$excelConstant.'<br />';
3466 $stack->push('Constant Value',self::$_ExcelConstants[$excelConstant]);
3467 $this->_debugLog->writeDebugLog('Evaluating Constant ', $excelConstant, ' as ', $this->_showTypeDetails(self::$_ExcelConstants[$excelConstant]));
3468 } elseif ((is_numeric($token)) || ($token === NULL) || (is_bool($token)) || ($token == '') || ($token{0} == '"') || ($token{0} == '#')) {
3469// echo 'Token is a number, boolean, string, null or an Excel error<br />';
3470 $stack->push('Value',$token);
3471 // if the token is a named range, push the named range name onto the stack
3472 } elseif (preg_match('/^'.self::CALCULATION_REGEXP_NAMEDRANGE.'$/i', $token, $matches)) {
3473// echo 'Token is a named range<br />';
3474 $namedRange = $matches[6];
3475// echo 'Named Range is '.$namedRange.'<br />';
3476 $this->_debugLog->writeDebugLog('Evaluating Named Range ', $namedRange);
3477 $cellValue = $this->extractNamedRange($namedRange, ((NULL !== $pCell) ? $pCellWorksheet : NULL), FALSE);
3478 $pCell->attach($pCellParent);
3479 $this->_debugLog->writeDebugLog('Evaluation Result for named range ', $namedRange, ' is ', $this->_showTypeDetails($cellValue));
3480 $stack->push('Named Range',$cellValue,$namedRange);
3481 } else {
3482 return $this->_raiseFormulaError("undefined variable '$token'");
3483 }
3484 }
3485 }
3486 // when we're out of tokens, the stack should have a single element, the final result
3487 if ($stack->count() != 1) return $this->_raiseFormulaError("internal error");
3488 $output = $stack->pop();
3489 $output = $output['value'];
3490
3491// if ((is_array($output)) && (self::$returnArrayAsType != self::RETURN_ARRAY_AS_ARRAY)) {
3492// return array_shift(PHPExcel_Calculation_Functions::flattenArray($output));
3493// }
3494 return $output;
3495 } // function _processTokenStack()
3496
3497
3498 private function _validateBinaryOperand($cellID, &$operand, &$stack) {
3499 if (is_array($operand)) {
3500 if ((count($operand, COUNT_RECURSIVE) - count($operand)) == 1) {
3501 do {
3502 $operand = array_pop($operand);
3503 } while (is_array($operand));
3504 }
3505 }
3506 // Numbers, matrices and booleans can pass straight through, as they're already valid
3507 if (is_string($operand)) {
3508 // We only need special validations for the operand if it is a string
3509 // Start by stripping off the quotation marks we use to identify true excel string values internally
3510 if ($operand > '' && $operand{0} == '"') { $operand = self::_unwrapResult($operand); }
3511 // If the string is a numeric value, we treat it as a numeric, so no further testing
3512 if (!is_numeric($operand)) {
3513 // If not a numeric, test to see if the value is an Excel error, and so can't be used in normal binary operations
3514 if ($operand > '' && $operand{0} == '#') {
3515 $stack->push('Value', $operand);
3516 $this->_debugLog->writeDebugLog('Evaluation Result is ', $this->_showTypeDetails($operand));
3517 return FALSE;
3519 // If not a numeric or a fraction, then it's a text string, and so can't be used in mathematical binary operations
3520 $stack->push('Value', '#VALUE!');
3521 $this->_debugLog->writeDebugLog('Evaluation Result is a ', $this->_showTypeDetails('#VALUE!'));
3522 return FALSE;
3523 }
3524 }
3525 }
3526
3527 // return a true if the value of the operand is one that we can use in normal binary operations
3528 return TRUE;
3529 } // function _validateBinaryOperand()
3530
3531
3532 private function _executeBinaryComparisonOperation($cellID, $operand1, $operand2, $operation, &$stack, $recursingArrays=FALSE) {
3533 // If we're dealing with matrix operations, we want a matrix result
3534 if ((is_array($operand1)) || (is_array($operand2))) {
3535 $result = array();
3536 if ((is_array($operand1)) && (!is_array($operand2))) {
3537 foreach($operand1 as $x => $operandData) {
3538 $this->_debugLog->writeDebugLog('Evaluating Comparison ', $this->_showValue($operandData), ' ', $operation, ' ', $this->_showValue($operand2));
3539 $this->_executeBinaryComparisonOperation($cellID,$operandData,$operand2,$operation,$stack);
3540 $r = $stack->pop();
3541 $result[$x] = $r['value'];
3542 }
3543 } elseif ((!is_array($operand1)) && (is_array($operand2))) {
3544 foreach($operand2 as $x => $operandData) {
3545 $this->_debugLog->writeDebugLog('Evaluating Comparison ', $this->_showValue($operand1), ' ', $operation, ' ', $this->_showValue($operandData));
3546 $this->_executeBinaryComparisonOperation($cellID,$operand1,$operandData,$operation,$stack);
3547 $r = $stack->pop();
3548 $result[$x] = $r['value'];
3549 }
3550 } else {
3551 if (!$recursingArrays) { self::_checkMatrixOperands($operand1,$operand2,2); }
3552 foreach($operand1 as $x => $operandData) {
3553 $this->_debugLog->writeDebugLog('Evaluating Comparison ', $this->_showValue($operandData), ' ', $operation, ' ', $this->_showValue($operand2[$x]));
3554 $this->_executeBinaryComparisonOperation($cellID,$operandData,$operand2[$x],$operation,$stack,TRUE);
3555 $r = $stack->pop();
3556 $result[$x] = $r['value'];
3557 }
3558 }
3559 // Log the result details
3560 $this->_debugLog->writeDebugLog('Comparison Evaluation Result is ', $this->_showTypeDetails($result));
3561 // And push the result onto the stack
3562 $stack->push('Array',$result);
3563 return TRUE;
3564 }
3565
3566 // Simple validate the two operands if they are string values
3567 if (is_string($operand1) && $operand1 > '' && $operand1{0} == '"') { $operand1 = self::_unwrapResult($operand1); }
3568 if (is_string($operand2) && $operand2 > '' && $operand2{0} == '"') { $operand2 = self::_unwrapResult($operand2); }
3569
3570 // Use case insensitive comparaison if not OpenOffice mode
3572 {
3573 if (is_string($operand1)) {
3574 $operand1 = strtoupper($operand1);
3575 }
3576
3577 if (is_string($operand2)) {
3578 $operand2 = strtoupper($operand2);
3579 }
3580 }
3581
3582 $useLowercaseFirstComparison = is_string($operand1) && is_string($operand2) && PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE;
3583
3584 // execute the necessary operation
3585 switch ($operation) {
3586 // Greater than
3587 case '>':
3588 if ($useLowercaseFirstComparison) {
3589 $result = $this->strcmpLowercaseFirst($operand1, $operand2) > 0;
3590 } else {
3591 $result = ($operand1 > $operand2);
3592 }
3593 break;
3594 // Less than
3595 case '<':
3596 if ($useLowercaseFirstComparison) {
3597 $result = $this->strcmpLowercaseFirst($operand1, $operand2) < 0;
3598 } else {
3599 $result = ($operand1 < $operand2);
3600 }
3601 break;
3602 // Equality
3603 case '=':
3604 if (is_numeric($operand1) && is_numeric($operand2)) {
3605 $result = (abs($operand1 - $operand2) < $this->delta);
3606 } else {
3607 $result = strcmp($operand1, $operand2) == 0;
3608 }
3609 break;
3610 // Greater than or equal
3611 case '>=':
3612 if (is_numeric($operand1) && is_numeric($operand2)) {
3613 $result = ((abs($operand1 - $operand2) < $this->delta) || ($operand1 > $operand2));
3614 } elseif ($useLowercaseFirstComparison) {
3615 $result = $this->strcmpLowercaseFirst($operand1, $operand2) >= 0;
3616 } else {
3617 $result = strcmp($operand1, $operand2) >= 0;
3618 }
3619 break;
3620 // Less than or equal
3621 case '<=':
3622 if (is_numeric($operand1) && is_numeric($operand2)) {
3623 $result = ((abs($operand1 - $operand2) < $this->delta) || ($operand1 < $operand2));
3624 } elseif ($useLowercaseFirstComparison) {
3625 $result = $this->strcmpLowercaseFirst($operand1, $operand2) <= 0;
3626 } else {
3627 $result = strcmp($operand1, $operand2) <= 0;
3628 }
3629 break;
3630 // Inequality
3631 case '<>':
3632 if (is_numeric($operand1) && is_numeric($operand2)) {
3633 $result = (abs($operand1 - $operand2) > 1E-14);
3634 } else {
3635 $result = strcmp($operand1, $operand2) != 0;
3636 }
3637 break;
3638 }
3639
3640 // Log the result details
3641 $this->_debugLog->writeDebugLog('Evaluation Result is ', $this->_showTypeDetails($result));
3642 // And push the result onto the stack
3643 $stack->push('Value',$result);
3644 return true;
3645 }
3646
3653 private function strcmpLowercaseFirst($str1, $str2)
3654 {
3655 $inversedStr1 = PHPExcel_Shared_String::StrCaseReverse($str1);
3656 $inversedStr2 = PHPExcel_Shared_String::StrCaseReverse($str2);
3657
3658 return strcmp($inversedStr1, $inversedStr2);
3659 }
3660
3661 private function _executeNumericBinaryOperation($cellID,$operand1,$operand2,$operation,$matrixFunction,&$stack) {
3662 // Validate the two operands
3663 if (!$this->_validateBinaryOperand($cellID,$operand1,$stack)) return FALSE;
3664 if (!$this->_validateBinaryOperand($cellID,$operand2,$stack)) return FALSE;
3665
3666 // If either of the operands is a matrix, we need to treat them both as matrices
3667 // (converting the other operand to a matrix if need be); then perform the required
3668 // matrix operation
3669 if ((is_array($operand1)) || (is_array($operand2))) {
3670 // Ensure that both operands are arrays/matrices of the same size
3671 self::_checkMatrixOperands($operand1, $operand2, 2);
3672
3673 try {
3674 // Convert operand 1 from a PHP array to a matrix
3675 $matrix = new PHPExcel_Shared_JAMA_Matrix($operand1);
3676 // Perform the required operation against the operand 1 matrix, passing in operand 2
3677 $matrixResult = $matrix->$matrixFunction($operand2);
3678 $result = $matrixResult->getArray();
3679 } catch (PHPExcel_Exception $ex) {
3680 $this->_debugLog->writeDebugLog('JAMA Matrix Exception: ', $ex->getMessage());
3681 $result = '#VALUE!';
3682 }
3683 } else {
3685 ((is_string($operand1) && !is_numeric($operand1) && strlen($operand1)>0) ||
3686 (is_string($operand2) && !is_numeric($operand2) && strlen($operand2)>0))) {
3688 } else {
3689 // If we're dealing with non-matrix operations, execute the necessary operation
3690 switch ($operation) {
3691 // Addition
3692 case '+':
3693 $result = $operand1 + $operand2;
3694 break;
3695 // Subtraction
3696 case '-':
3697 $result = $operand1 - $operand2;
3698 break;
3699 // Multiplication
3700 case '*':
3701 $result = $operand1 * $operand2;
3702 break;
3703 // Division
3704 case '/':
3705 if ($operand2 == 0) {
3706 // Trap for Divide by Zero error
3707 $stack->push('Value','#DIV/0!');
3708 $this->_debugLog->writeDebugLog('Evaluation Result is ', $this->_showTypeDetails('#DIV/0!'));
3709 return FALSE;
3710 } else {
3711 $result = $operand1 / $operand2;
3712 }
3713 break;
3714 // Power
3715 case '^':
3716 $result = pow($operand1, $operand2);
3717 break;
3718 }
3719 }
3720 }
3721
3722 // Log the result details
3723 $this->_debugLog->writeDebugLog('Evaluation Result is ', $this->_showTypeDetails($result));
3724 // And push the result onto the stack
3725 $stack->push('Value',$result);
3726 return TRUE;
3727 } // function _executeNumericBinaryOperation()
3728
3729
3730 // trigger an error, but nicely, if need be
3731 protected function _raiseFormulaError($errorMessage) {
3732 $this->formulaError = $errorMessage;
3733 $this->_cyclicReferenceStack->clear();
3734 if (!$this->suppressFormulaErrors) throw new PHPExcel_Calculation_Exception($errorMessage);
3735 trigger_error($errorMessage, E_USER_ERROR);
3736 } // function _raiseFormulaError()
3737
3738
3748 public function extractCellRange(&$pRange = 'A1', PHPExcel_Worksheet $pSheet = NULL, $resetLog = TRUE) {
3749 // Return value
3750 $returnValue = array ();
3751
3752// echo 'extractCellRange('.$pRange.')',PHP_EOL;
3753 if ($pSheet !== NULL) {
3754 $pSheetName = $pSheet->getTitle();
3755// echo 'Passed sheet name is '.$pSheetName.PHP_EOL;
3756// echo 'Range reference is '.$pRange.PHP_EOL;
3757 if (strpos ($pRange, '!') !== false) {
3758// echo '$pRange reference includes sheet reference',PHP_EOL;
3759 list($pSheetName,$pRange) = PHPExcel_Worksheet::extractSheetTitle($pRange, true);
3760// echo 'New sheet name is '.$pSheetName,PHP_EOL;
3761// echo 'Adjusted Range reference is '.$pRange,PHP_EOL;
3762 $pSheet = $this->_workbook->getSheetByName($pSheetName);
3763 }
3764
3765 // Extract range
3766 $aReferences = PHPExcel_Cell::extractAllCellReferencesInRange($pRange);
3767 $pRange = $pSheetName.'!'.$pRange;
3768 if (!isset($aReferences[1])) {
3769 // Single cell in range
3770 sscanf($aReferences[0],'%[A-Z]%d', $currentCol, $currentRow);
3771 $cellValue = NULL;
3772 if ($pSheet->cellExists($aReferences[0])) {
3773 $returnValue[$currentRow][$currentCol] = $pSheet->getCell($aReferences[0])->getCalculatedValue($resetLog);
3774 } else {
3775 $returnValue[$currentRow][$currentCol] = NULL;
3776 }
3777 } else {
3778 // Extract cell data for all cells in the range
3779 foreach ($aReferences as $reference) {
3780 // Extract range
3781 sscanf($reference,'%[A-Z]%d', $currentCol, $currentRow);
3782 $cellValue = NULL;
3783 if ($pSheet->cellExists($reference)) {
3784 $returnValue[$currentRow][$currentCol] = $pSheet->getCell($reference)->getCalculatedValue($resetLog);
3785 } else {
3786 $returnValue[$currentRow][$currentCol] = NULL;
3787 }
3788 }
3789 }
3790 }
3791
3792 // Return
3793 return $returnValue;
3794 } // function extractCellRange()
3795
3796
3806 public function extractNamedRange(&$pRange = 'A1', PHPExcel_Worksheet $pSheet = NULL, $resetLog = TRUE) {
3807 // Return value
3808 $returnValue = array ();
3809
3810// echo 'extractNamedRange('.$pRange.')<br />';
3811 if ($pSheet !== NULL) {
3812 $pSheetName = $pSheet->getTitle();
3813// echo 'Current sheet name is '.$pSheetName.'<br />';
3814// echo 'Range reference is '.$pRange.'<br />';
3815 if (strpos ($pRange, '!') !== false) {
3816// echo '$pRange reference includes sheet reference',PHP_EOL;
3817 list($pSheetName,$pRange) = PHPExcel_Worksheet::extractSheetTitle($pRange, true);
3818// echo 'New sheet name is '.$pSheetName,PHP_EOL;
3819// echo 'Adjusted Range reference is '.$pRange,PHP_EOL;
3820 $pSheet = $this->_workbook->getSheetByName($pSheetName);
3821 }
3822
3823 // Named range?
3824 $namedRange = PHPExcel_NamedRange::resolveRange($pRange, $pSheet);
3825 if ($namedRange !== NULL) {
3826 $pSheet = $namedRange->getWorksheet();
3827// echo 'Named Range '.$pRange.' (';
3828 $pRange = $namedRange->getRange();
3829 $splitRange = PHPExcel_Cell::splitRange($pRange);
3830 // Convert row and column references
3831 if (ctype_alpha($splitRange[0][0])) {
3832 $pRange = $splitRange[0][0] . '1:' . $splitRange[0][1] . $namedRange->getWorksheet()->getHighestRow();
3833 } elseif(ctype_digit($splitRange[0][0])) {
3834 $pRange = 'A' . $splitRange[0][0] . ':' . $namedRange->getWorksheet()->getHighestColumn() . $splitRange[0][1];
3835 }
3836// echo $pRange.') is in sheet '.$namedRange->getWorksheet()->getTitle().'<br />';
3837
3838// if ($pSheet->getTitle() != $namedRange->getWorksheet()->getTitle()) {
3839// if (!$namedRange->getLocalOnly()) {
3840// $pSheet = $namedRange->getWorksheet();
3841// } else {
3842// return $returnValue;
3843// }
3844// }
3845 } else {
3847 }
3848
3849 // Extract range
3850 $aReferences = PHPExcel_Cell::extractAllCellReferencesInRange($pRange);
3851// var_dump($aReferences);
3852 if (!isset($aReferences[1])) {
3853 // Single cell (or single column or row) in range
3854 list($currentCol,$currentRow) = PHPExcel_Cell::coordinateFromString($aReferences[0]);
3855 $cellValue = NULL;
3856 if ($pSheet->cellExists($aReferences[0])) {
3857 $returnValue[$currentRow][$currentCol] = $pSheet->getCell($aReferences[0])->getCalculatedValue($resetLog);
3858 } else {
3859 $returnValue[$currentRow][$currentCol] = NULL;
3860 }
3861 } else {
3862 // Extract cell data for all cells in the range
3863 foreach ($aReferences as $reference) {
3864 // Extract range
3865 list($currentCol,$currentRow) = PHPExcel_Cell::coordinateFromString($reference);
3866// echo 'NAMED RANGE: $currentCol='.$currentCol.' $currentRow='.$currentRow.'<br />';
3867 $cellValue = NULL;
3868 if ($pSheet->cellExists($reference)) {
3869 $returnValue[$currentRow][$currentCol] = $pSheet->getCell($reference)->getCalculatedValue($resetLog);
3870 } else {
3871 $returnValue[$currentRow][$currentCol] = NULL;
3872 }
3873 }
3874 }
3875// print_r($returnValue);
3876// echo '<br />';
3877 }
3878
3879 // Return
3880 return $returnValue;
3881 } // function extractNamedRange()
3882
3883
3890 public function isImplemented($pFunction = '') {
3891 $pFunction = strtoupper ($pFunction);
3892 if (isset(self::$_PHPExcelFunctions[$pFunction])) {
3893 return (self::$_PHPExcelFunctions[$pFunction]['functionCall'] != 'PHPExcel_Calculation_Functions::DUMMY');
3894 } else {
3895 return FALSE;
3896 }
3897 } // function isImplemented()
3898
3899
3905 public function listFunctions() {
3906 // Return value
3907 $returnValue = array();
3908 // Loop functions
3909 foreach(self::$_PHPExcelFunctions as $functionName => $function) {
3910 if ($function['functionCall'] != 'PHPExcel_Calculation_Functions::DUMMY') {
3911 $returnValue[$functionName] = new PHPExcel_Calculation_Function($function['category'],
3912 $functionName,
3913 $function['functionCall']
3914 );
3915 }
3916 }
3917
3918 // Return
3919 return $returnValue;
3920 } // function listFunctions()
3921
3922
3928 public function listAllFunctionNames() {
3929 return array_keys(self::$_PHPExcelFunctions);
3930 } // function listAllFunctionNames()
3931
3937 public function listFunctionNames() {
3938 // Return value
3939 $returnValue = array();
3940 // Loop functions
3941 foreach(self::$_PHPExcelFunctions as $functionName => $function) {
3942 if ($function['functionCall'] != 'PHPExcel_Calculation_Functions::DUMMY') {
3943 $returnValue[] = $functionName;
3944 }
3945 }
3946
3947 // Return
3948 return $returnValue;
3949 } // function listFunctionNames()
3950
3951} // class PHPExcel_Calculation
3952
for($col=0; $col< 50; $col++) $d
$result
An exception for terminatinating execution or to throw for unit testing.
static flattenSingleValue($value='')
Convert an array to a single scalar value by extracting the first element.
Definition: Functions.php:662
static flattenArray($array)
Convert a multi-dimensional array to a simple 1-dimensional array.
Definition: Functions.php:598
static $functionReplaceFromExcel
static getFALSE()
Return the locale-specific translation of FALSE.
static _resizeMatricesExtend(&$matrix1, &$matrix2, $matrix1Rows, $matrix1Columns, $matrix2Rows, $matrix2Columns)
Ensure that paired matrix operands are both matrices of the same size.
__clone()
__clone implementation.
isImplemented($pFunction='')
Is a specific function implemented?
static _checkMatrixOperands(&$operand1, &$operand2, $resize=1)
Ensure that paired matrix operands are both matrices and of the same size.
renameCalculationCacheForWorksheet($fromWorksheetName, $toWorksheetName)
Rename calculation cache for a specified worksheet.
_raiseFormulaError($errorMessage)
setLocale($locale='en_us')
Set the locale code.
const CALCULATION_REGEXP_STRING
Definition: Calculation.php:69
_convertMatrixReferences($formula)
__construct(PHPExcel $workbook=NULL)
disableCalculationCache()
Disable calculation cache.
listAllFunctionNames()
Get a list of all Excel function names.
_processTokenStack($tokens, $cellID=NULL, PHPExcel_Cell $pCell=NULL)
static getInstance(PHPExcel $workbook=NULL)
Get an instance of this class.
static _unwrapResult($value)
Remove quotes used as a wrapper to identify string values.
const RETURN_ARRAY_AS_ERROR
constants
Definition: Calculation.php:83
_validateBinaryOperand($cellID, &$operand, &$stack)
parseFormula($formula)
Validate and parse a formula string.
calculate(PHPExcel_Cell $pCell=NULL)
Calculate cell value (using formula from a cell ID) Retained for backward compatibility.
_translateFormulaToLocale($formula)
static _translateSeparator($fromSeparator, $toSeparator, $formula, &$inBraces)
const CALCULATION_REGEXP_OPENBRACE
Definition: Calculation.php:71
static _resizeMatricesShrink(&$matrix1, &$matrix2, $matrix1Rows, $matrix1Columns, $matrix2Rows, $matrix2Columns)
Ensure that paired matrix operands are both matrices of the same size.
getDebugLog()
Get the debuglog for this claculation engine instance.
static _getMatrixDimensions(&$matrix)
Read the dimensions of a matrix, and re-index it with straight numeric keys starting from row 0,...
setCalculationCacheEnabled($pValue=TRUE)
Enable/disable calculation cache.
static _dataTestReference(&$operandData)
const CALCULATION_REGEXP_CELLREF
Definition: Calculation.php:75
saveValueToCache($cellReference, $cellValue)
extractNamedRange(&$pRange='A1', PHPExcel_Worksheet $pSheet=NULL, $resetLog=TRUE)
Extract range values.
static $functionReplaceFromLocale
_calculateFormulaValue($formula, $cellID=null, PHPExcel_Cell $pCell=null)
Parse a cell formula and calculate its value.
_executeBinaryComparisonOperation($cellID, $operand1, $operand2, $operation, &$stack, $recursingArrays=FALSE)
enableCalculationCache()
Enable calculation cache.
static getArrayReturnType()
Return the Array Return Type (Array or Value of first element in the array)
static setArrayReturnType($returnType)
Set the Array Return Type (Array or Value of first element in the array)
_translateFormulaToEnglish($formula)
flushInstance()
Flush the calculation cache for any existing instance of this class but only if a PHPExcel_Calculatio...
const CALCULATION_REGEXP_FUNCTION
Definition: Calculation.php:73
listFunctions()
Get a list of all implemented functions as an array of function objects.
static unsetInstance(PHPExcel $workbook=NULL)
Unset an instance of this class.
static getTRUE()
Return the locale-specific translation of TRUE.
static $_localeArgumentSeparator
const CALCULATION_REGEXP_NAMEDRANGE
Definition: Calculation.php:77
static _wrapResult($value)
Wrap string values in quotes.
strcmpLowercaseFirst($str1, $str2)
Compare two strings in the same way as strcmp() except that lowercase come before uppercase letters.
_parseFormula($formula, PHPExcel_Cell $pCell=NULL)
_showTypeDetails($value)
Format type and details of an operand for display in the log (based on operand type)
getCalculationCacheEnabled()
Is calculation caching enabled?
clearCalculationCache()
Clear calculation cache.
const CALCULATION_REGEXP_NUMBER
Constants
Definition: Calculation.php:67
static _translateFormula($from, $to, $formula, $fromSeparator, $toSeparator)
calculateCellValue(PHPExcel_Cell $pCell=NULL, $resetLog=TRUE)
Calculate the value of a cell formula.
getValueFromCache($cellReference, &$cellValue)
extractCellRange(&$pRange='A1', PHPExcel_Worksheet $pSheet=NULL, $resetLog=TRUE)
Extract range values.
clearCalculationCacheForWorksheet($worksheetName)
Clear calculation cache for a specified worksheet.
_showValue($value)
Format details of an operand for display in the log (based on operand type)
const CALCULATION_REGEXP_ERROR
Definition: Calculation.php:79
listFunctionNames()
Get a list of implemented Excel function names.
static _localeFunc($function)
calculateFormula($formula, $cellID=NULL, PHPExcel_Cell $pCell=NULL)
Calculate the value of a formula.
getLocale()
Get the currently defined locale code.
_executeNumericBinaryOperation($cellID, $operand1, $operand2, $operation, $matrixFunction, &$stack)
static extractAllCellReferencesInRange($pRange='A1')
Extract all cell references in range.
Definition: Cell.php:854
static stringFromColumnIndex($pColumnIndex=0)
String from columnindex.
Definition: Cell.php:825
static coordinateFromString($pCoordinateString='A1')
Coordinate from string.
Definition: Cell.php:580
static splitRange($pRange='A1:A1')
Split range into coordinate strings.
Definition: Cell.php:660
static columnIndexFromString($pString='A')
Column index from string.
Definition: Cell.php:782
static resolveRange($pNamedRange='', PHPExcel_Worksheet $pSheet)
Resolve a named range to a regular cell range.
Definition: NamedRange.php:229
PHPExcel root directory.
Definition: Matrix.php:27
static convertToNumberIfFraction(&$operand)
Identify whether a string contains a fractional numeric value, and convert it to a numeric if it is.
Definition: String.php:671
static StrCaseReverse($pValue='')
Reverse the case of a string, so that all uppercase characters become lowercase and all lowercase cha...
Definition: String.php:648
static extractSheetTitle($pRange, $returnRange=false)
Extract worksheet title from range.
Definition: Worksheet.php:2654
$x
Definition: example_009.php:98
$r
Definition: example_031.php:79
if(!is_dir( $entity_dir)) exit("Fatal Error ([A-Za-z0-9]+)\s+" &#(? foreach( $entity_files as $file) $output
defined( 'APPLICATION_ENV')||define( 'APPLICATION_ENV'
Definition: bootstrap.php:27