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