1: <?php
2: /**
3: * PHPExcel
4: *
5: * Copyright (c) 2006 - 2014 PHPExcel
6: *
7: * This library is free software; you can redistribute it and/or
8: * modify it under the terms of the GNU Lesser General Public
9: * License as published by the Free Software Foundation; either
10: * version 2.1 of the License, or (at your option) any later version.
11: *
12: * This library is distributed in the hope that it will be useful,
13: * but WITHOUT ANY WARRANTY; without even the implied warranty of
14: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
15: * Lesser General Public License for more details.
16: *
17: * You should have received a copy of the GNU Lesser General Public
18: * License along with this library; if not, write to the Free Software
19: * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
20: *
21: * @category PHPExcel
22: * @package PHPExcel_Calculation
23: * @copyright Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel)
24: * @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL
25: * @version 1.8.0, 2014-03-02
26: */
27:
28:
29: /** PHPExcel root directory */
30: if (!defined('PHPEXCEL_ROOT')) {
31: /**
32: * @ignore
33: */
34: define('PHPEXCEL_ROOT', dirname(__FILE__) . '/../../');
35: require(PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php');
36: }
37:
38:
39: /** MAX_VALUE */
40: define('MAX_VALUE', 1.2e308);
41:
42: /** 2 / PI */
43: define('M_2DIVPI', 0.63661977236758134307553505349006);
44:
45: /** MAX_ITERATIONS */
46: define('MAX_ITERATIONS', 256);
47:
48: /** PRECISION */
49: define('PRECISION', 8.88E-016);
50:
51:
52: /**
53: * PHPExcel_Calculation_Functions
54: *
55: * @category PHPExcel
56: * @package PHPExcel_Calculation
57: * @copyright Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel)
58: */
59: class PHPExcel_Calculation_Functions {
60:
61: /** constants */
62: const COMPATIBILITY_EXCEL = 'Excel';
63: const COMPATIBILITY_GNUMERIC = 'Gnumeric';
64: const COMPATIBILITY_OPENOFFICE = 'OpenOfficeCalc';
65:
66: const RETURNDATE_PHP_NUMERIC = 'P';
67: const RETURNDATE_PHP_OBJECT = 'O';
68: const RETURNDATE_EXCEL = 'E';
69:
70:
71: /**
72: * Compatibility mode to use for error checking and responses
73: *
74: * @access private
75: * @var string
76: */
77: protected static $compatibilityMode = self::COMPATIBILITY_EXCEL;
78:
79: /**
80: * Data Type to use when returning date values
81: *
82: * @access private
83: * @var string
84: */
85: protected static $ReturnDateType = self::RETURNDATE_EXCEL;
86:
87: /**
88: * List of error codes
89: *
90: * @access private
91: * @var array
92: */
93: protected static $_errorCodes = array( 'null' => '#NULL!',
94: 'divisionbyzero' => '#DIV/0!',
95: 'value' => '#VALUE!',
96: 'reference' => '#REF!',
97: 'name' => '#NAME?',
98: 'num' => '#NUM!',
99: 'na' => '#N/A',
100: 'gettingdata' => '#GETTING_DATA'
101: );
102:
103:
104: /**
105: * Set the Compatibility Mode
106: *
107: * @access public
108: * @category Function Configuration
109: * @param string $compatibilityMode Compatibility Mode
110: * Permitted values are:
111: * PHPExcel_Calculation_Functions::COMPATIBILITY_EXCEL 'Excel'
112: * PHPExcel_Calculation_Functions::COMPATIBILITY_GNUMERIC 'Gnumeric'
113: * PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE 'OpenOfficeCalc'
114: * @return boolean (Success or Failure)
115: */
116: public static function setCompatibilityMode($compatibilityMode) {
117: if (($compatibilityMode == self::COMPATIBILITY_EXCEL) ||
118: ($compatibilityMode == self::COMPATIBILITY_GNUMERIC) ||
119: ($compatibilityMode == self::COMPATIBILITY_OPENOFFICE)) {
120: self::$compatibilityMode = $compatibilityMode;
121: return True;
122: }
123: return False;
124: } // function setCompatibilityMode()
125:
126:
127: /**
128: * Return the current Compatibility Mode
129: *
130: * @access public
131: * @category Function Configuration
132: * @return string Compatibility Mode
133: * Possible Return values are:
134: * PHPExcel_Calculation_Functions::COMPATIBILITY_EXCEL 'Excel'
135: * PHPExcel_Calculation_Functions::COMPATIBILITY_GNUMERIC 'Gnumeric'
136: * PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE 'OpenOfficeCalc'
137: */
138: public static function getCompatibilityMode() {
139: return self::$compatibilityMode;
140: } // function getCompatibilityMode()
141:
142:
143: /**
144: * Set the Return Date Format used by functions that return a date/time (Excel, PHP Serialized Numeric or PHP Object)
145: *
146: * @access public
147: * @category Function Configuration
148: * @param string $returnDateType Return Date Format
149: * Permitted values are:
150: * PHPExcel_Calculation_Functions::RETURNDATE_PHP_NUMERIC 'P'
151: * PHPExcel_Calculation_Functions::RETURNDATE_PHP_OBJECT 'O'
152: * PHPExcel_Calculation_Functions::RETURNDATE_EXCEL 'E'
153: * @return boolean Success or failure
154: */
155: public static function setReturnDateType($returnDateType) {
156: if (($returnDateType == self::RETURNDATE_PHP_NUMERIC) ||
157: ($returnDateType == self::RETURNDATE_PHP_OBJECT) ||
158: ($returnDateType == self::RETURNDATE_EXCEL)) {
159: self::$ReturnDateType = $returnDateType;
160: return True;
161: }
162: return False;
163: } // function setReturnDateType()
164:
165:
166: /**
167: * Return the current Return Date Format for functions that return a date/time (Excel, PHP Serialized Numeric or PHP Object)
168: *
169: * @access public
170: * @category Function Configuration
171: * @return string Return Date Format
172: * Possible Return values are:
173: * PHPExcel_Calculation_Functions::RETURNDATE_PHP_NUMERIC 'P'
174: * PHPExcel_Calculation_Functions::RETURNDATE_PHP_OBJECT 'O'
175: * PHPExcel_Calculation_Functions::RETURNDATE_EXCEL 'E'
176: */
177: public static function getReturnDateType() {
178: return self::$ReturnDateType;
179: } // function getReturnDateType()
180:
181:
182: /**
183: * DUMMY
184: *
185: * @access public
186: * @category Error Returns
187: * @return string #Not Yet Implemented
188: */
189: public static function DUMMY() {
190: return '#Not Yet Implemented';
191: } // function DUMMY()
192:
193:
194: /**
195: * DIV0
196: *
197: * @access public
198: * @category Error Returns
199: * @return string #Not Yet Implemented
200: */
201: public static function DIV0() {
202: return self::$_errorCodes['divisionbyzero'];
203: } // function DIV0()
204:
205:
206: /**
207: * NA
208: *
209: * Excel Function:
210: * =NA()
211: *
212: * Returns the error value #N/A
213: * #N/A is the error value that means "no value is available."
214: *
215: * @access public
216: * @category Logical Functions
217: * @return string #N/A!
218: */
219: public static function NA() {
220: return self::$_errorCodes['na'];
221: } // function NA()
222:
223:
224: /**
225: * NaN
226: *
227: * Returns the error value #NUM!
228: *
229: * @access public
230: * @category Error Returns
231: * @return string #NUM!
232: */
233: public static function NaN() {
234: return self::$_errorCodes['num'];
235: } // function NaN()
236:
237:
238: /**
239: * NAME
240: *
241: * Returns the error value #NAME?
242: *
243: * @access public
244: * @category Error Returns
245: * @return string #NAME?
246: */
247: public static function NAME() {
248: return self::$_errorCodes['name'];
249: } // function NAME()
250:
251:
252: /**
253: * REF
254: *
255: * Returns the error value #REF!
256: *
257: * @access public
258: * @category Error Returns
259: * @return string #REF!
260: */
261: public static function REF() {
262: return self::$_errorCodes['reference'];
263: } // function REF()
264:
265:
266: /**
267: * NULL
268: *
269: * Returns the error value #NULL!
270: *
271: * @access public
272: * @category Error Returns
273: * @return string #NULL!
274: */
275: public static function NULL() {
276: return self::$_errorCodes['null'];
277: } // function NULL()
278:
279:
280: /**
281: * VALUE
282: *
283: * Returns the error value #VALUE!
284: *
285: * @access public
286: * @category Error Returns
287: * @return string #VALUE!
288: */
289: public static function VALUE() {
290: return self::$_errorCodes['value'];
291: } // function VALUE()
292:
293:
294: public static function isMatrixValue($idx) {
295: return ((substr_count($idx,'.') <= 1) || (preg_match('/\.[A-Z]/',$idx) > 0));
296: }
297:
298:
299: public static function isValue($idx) {
300: return (substr_count($idx,'.') == 0);
301: }
302:
303:
304: public static function isCellValue($idx) {
305: return (substr_count($idx,'.') > 1);
306: }
307:
308:
309: public static function _ifCondition($condition) {
310: $condition = PHPExcel_Calculation_Functions::flattenSingleValue($condition);
311: if (!isset($condition{0}))
312: $condition = '=""';
313: if (!in_array($condition{0},array('>', '<', '='))) {
314: if (!is_numeric($condition)) { $condition = PHPExcel_Calculation::_wrapResult(strtoupper($condition)); }
315: return '='.$condition;
316: } else {
317: preg_match('/([<>=]+)(.*)/',$condition,$matches);
318: list(,$operator,$operand) = $matches;
319:
320: if (!is_numeric($operand)) {
321: $operand = str_replace('"', '""', $operand);
322: $operand = PHPExcel_Calculation::_wrapResult(strtoupper($operand));
323: }
324:
325: return $operator.$operand;
326: }
327: } // function _ifCondition()
328:
329:
330: /**
331: * ERROR_TYPE
332: *
333: * @param mixed $value Value to check
334: * @return boolean
335: */
336: public static function ERROR_TYPE($value = '') {
337: $value = self::flattenSingleValue($value);
338:
339: $i = 1;
340: foreach(self::$_errorCodes as $errorCode) {
341: if ($value === $errorCode) {
342: return $i;
343: }
344: ++$i;
345: }
346: return self::NA();
347: } // function ERROR_TYPE()
348:
349:
350: /**
351: * IS_BLANK
352: *
353: * @param mixed $value Value to check
354: * @return boolean
355: */
356: public static function IS_BLANK($value = NULL) {
357: if (!is_null($value)) {
358: $value = self::flattenSingleValue($value);
359: }
360:
361: return is_null($value);
362: } // function IS_BLANK()
363:
364:
365: /**
366: * IS_ERR
367: *
368: * @param mixed $value Value to check
369: * @return boolean
370: */
371: public static function IS_ERR($value = '') {
372: $value = self::flattenSingleValue($value);
373:
374: return self::IS_ERROR($value) && (!self::IS_NA($value));
375: } // function IS_ERR()
376:
377:
378: /**
379: * IS_ERROR
380: *
381: * @param mixed $value Value to check
382: * @return boolean
383: */
384: public static function IS_ERROR($value = '') {
385: $value = self::flattenSingleValue($value);
386:
387: if (!is_string($value))
388: return false;
389: return in_array($value, array_values(self::$_errorCodes));
390: } // function IS_ERROR()
391:
392:
393: /**
394: * IS_NA
395: *
396: * @param mixed $value Value to check
397: * @return boolean
398: */
399: public static function IS_NA($value = '') {
400: $value = self::flattenSingleValue($value);
401:
402: return ($value === self::NA());
403: } // function IS_NA()
404:
405:
406: /**
407: * IS_EVEN
408: *
409: * @param mixed $value Value to check
410: * @return boolean
411: */
412: public static function IS_EVEN($value = NULL) {
413: $value = self::flattenSingleValue($value);
414:
415: if ($value === NULL)
416: return self::NAME();
417: if ((is_bool($value)) || ((is_string($value)) && (!is_numeric($value))))
418: return self::VALUE();
419: return ($value % 2 == 0);
420: } // function IS_EVEN()
421:
422:
423: /**
424: * IS_ODD
425: *
426: * @param mixed $value Value to check
427: * @return boolean
428: */
429: public static function IS_ODD($value = NULL) {
430: $value = self::flattenSingleValue($value);
431:
432: if ($value === NULL)
433: return self::NAME();
434: if ((is_bool($value)) || ((is_string($value)) && (!is_numeric($value))))
435: return self::VALUE();
436: return (abs($value) % 2 == 1);
437: } // function IS_ODD()
438:
439:
440: /**
441: * IS_NUMBER
442: *
443: * @param mixed $value Value to check
444: * @return boolean
445: */
446: public static function IS_NUMBER($value = NULL) {
447: $value = self::flattenSingleValue($value);
448:
449: if (is_string($value)) {
450: return False;
451: }
452: return is_numeric($value);
453: } // function IS_NUMBER()
454:
455:
456: /**
457: * IS_LOGICAL
458: *
459: * @param mixed $value Value to check
460: * @return boolean
461: */
462: public static function IS_LOGICAL($value = NULL) {
463: $value = self::flattenSingleValue($value);
464:
465: return is_bool($value);
466: } // function IS_LOGICAL()
467:
468:
469: /**
470: * IS_TEXT
471: *
472: * @param mixed $value Value to check
473: * @return boolean
474: */
475: public static function IS_TEXT($value = NULL) {
476: $value = self::flattenSingleValue($value);
477:
478: return (is_string($value) && !self::IS_ERROR($value));
479: } // function IS_TEXT()
480:
481:
482: /**
483: * IS_NONTEXT
484: *
485: * @param mixed $value Value to check
486: * @return boolean
487: */
488: public static function IS_NONTEXT($value = NULL) {
489: return !self::IS_TEXT($value);
490: } // function IS_NONTEXT()
491:
492:
493: /**
494: * VERSION
495: *
496: * @return string Version information
497: */
498: public static function VERSION() {
499: return 'PHPExcel 1.8.0, 2014-03-02';
500: } // function VERSION()
501:
502:
503: /**
504: * N
505: *
506: * Returns a value converted to a number
507: *
508: * @param value The value you want converted
509: * @return number N converts values listed in the following table
510: * If value is or refers to N returns
511: * A number That number
512: * A date The serial number of that date
513: * TRUE 1
514: * FALSE 0
515: * An error value The error value
516: * Anything else 0
517: */
518: public static function N($value = NULL) {
519: while (is_array($value)) {
520: $value = array_shift($value);
521: }
522:
523: switch (gettype($value)) {
524: case 'double' :
525: case 'float' :
526: case 'integer' :
527: return $value;
528: break;
529: case 'boolean' :
530: return (integer) $value;
531: break;
532: case 'string' :
533: // Errors
534: if ((strlen($value) > 0) && ($value{0} == '#')) {
535: return $value;
536: }
537: break;
538: }
539: return 0;
540: } // function N()
541:
542:
543: /**
544: * TYPE
545: *
546: * Returns a number that identifies the type of a value
547: *
548: * @param value The value you want tested
549: * @return number N converts values listed in the following table
550: * If value is or refers to N returns
551: * A number 1
552: * Text 2
553: * Logical Value 4
554: * An error value 16
555: * Array or Matrix 64
556: */
557: public static function TYPE($value = NULL) {
558: $value = self::flattenArrayIndexed($value);
559: if (is_array($value) && (count($value) > 1)) {
560: $a = array_keys($value);
561: $a = array_pop($a);
562: // Range of cells is an error
563: if (self::isCellValue($a)) {
564: return 16;
565: // Test for Matrix
566: } elseif (self::isMatrixValue($a)) {
567: return 64;
568: }
569: } elseif(empty($value)) {
570: // Empty Cell
571: return 1;
572: }
573: $value = self::flattenSingleValue($value);
574:
575: if (($value === NULL) || (is_float($value)) || (is_int($value))) {
576: return 1;
577: } elseif(is_bool($value)) {
578: return 4;
579: } elseif(is_array($value)) {
580: return 64;
581: break;
582: } elseif(is_string($value)) {
583: // Errors
584: if ((strlen($value) > 0) && ($value{0} == '#')) {
585: return 16;
586: }
587: return 2;
588: }
589: return 0;
590: } // function TYPE()
591:
592:
593: /**
594: * Convert a multi-dimensional array to a simple 1-dimensional array
595: *
596: * @param array $array Array to be flattened
597: * @return array Flattened array
598: */
599: public static function flattenArray($array) {
600: if (!is_array($array)) {
601: return (array) $array;
602: }
603:
604: $arrayValues = array();
605: foreach ($array as $value) {
606: if (is_array($value)) {
607: foreach ($value as $val) {
608: if (is_array($val)) {
609: foreach ($val as $v) {
610: $arrayValues[] = $v;
611: }
612: } else {
613: $arrayValues[] = $val;
614: }
615: }
616: } else {
617: $arrayValues[] = $value;
618: }
619: }
620:
621: return $arrayValues;
622: } // function flattenArray()
623:
624:
625: /**
626: * Convert a multi-dimensional array to a simple 1-dimensional array, but retain an element of indexing
627: *
628: * @param array $array Array to be flattened
629: * @return array Flattened array
630: */
631: public static function flattenArrayIndexed($array) {
632: if (!is_array($array)) {
633: return (array) $array;
634: }
635:
636: $arrayValues = array();
637: foreach ($array as $k1 => $value) {
638: if (is_array($value)) {
639: foreach ($value as $k2 => $val) {
640: if (is_array($val)) {
641: foreach ($val as $k3 => $v) {
642: $arrayValues[$k1.'.'.$k2.'.'.$k3] = $v;
643: }
644: } else {
645: $arrayValues[$k1.'.'.$k2] = $val;
646: }
647: }
648: } else {
649: $arrayValues[$k1] = $value;
650: }
651: }
652:
653: return $arrayValues;
654: } // function flattenArrayIndexed()
655:
656:
657: /**
658: * Convert an array to a single scalar value by extracting the first element
659: *
660: * @param mixed $value Array or scalar value
661: * @return mixed
662: */
663: public static function flattenSingleValue($value = '') {
664: while (is_array($value)) {
665: $value = array_pop($value);
666: }
667:
668: return $value;
669: } // function flattenSingleValue()
670:
671: } // class PHPExcel_Calculation_Functions
672:
673:
674: //
675: // There are a few mathematical functions that aren't available on all versions of PHP for all platforms
676: // These functions aren't available in Windows implementations of PHP prior to version 5.3.0
677: // So we test if they do exist for this version of PHP/operating platform; and if not we create them
678: //
679: if (!function_exists('acosh')) {
680: function acosh($x) {
681: return 2 * log(sqrt(($x + 1) / 2) + sqrt(($x - 1) / 2));
682: } // function acosh()
683: }
684:
685: if (!function_exists('asinh')) {
686: function asinh($x) {
687: return log($x + sqrt(1 + $x * $x));
688: } // function asinh()
689: }
690:
691: if (!function_exists('atanh')) {
692: function atanh($x) {
693: return (log(1 + $x) - log(1 - $x)) / 2;
694: } // function atanh()
695: }
696:
697:
698: //
699: // Strangely, PHP doesn't have a mb_str_replace multibyte function
700: // As we'll only ever use this function with UTF-8 characters, we can simply "hard-code" the character set
701: //
702: if ((!function_exists('mb_str_replace')) &&
703: (function_exists('mb_substr')) && (function_exists('mb_strlen')) && (function_exists('mb_strpos'))) {
704: function mb_str_replace($search, $replace, $subject) {
705: if(is_array($subject)) {
706: $ret = array();
707: foreach($subject as $key => $val) {
708: $ret[$key] = mb_str_replace($search, $replace, $val);
709: }
710: return $ret;
711: }
712:
713: foreach((array) $search as $key => $s) {
714: if($s == '') {
715: continue;
716: }
717: $r = !is_array($replace) ? $replace : (array_key_exists($key, $replace) ? $replace[$key] : '');
718: $pos = mb_strpos($subject, $s, 0, 'UTF-8');
719: while($pos !== false) {
720: $subject = mb_substr($subject, 0, $pos, 'UTF-8') . $r . mb_substr($subject, $pos + mb_strlen($s, 'UTF-8'), 65535, 'UTF-8');
721: $pos = mb_strpos($subject, $s, $pos + mb_strlen($r, 'UTF-8'), 'UTF-8');
722: }
723: }
724: return $subject;
725: }
726: }
727: