File Source for Functions.php

PHPExcel_Calculation

# Source for file Functions.php

Documentation is available at Functions.php

1. <?php
2. /**
3. * PHPExcel
4. *
5. * Copyright (c) 2006 - 2011 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 - 2011 PHPExcel (http://www.codeplex.com/PHPExcel)
24. * @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL
25. * @version 1.7.6, 2011-02-27
26. */
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. }
39. /** MAX_VALUE */
40. define('MAX_VALUE', 1.2e308);
42. /** 2 / PI */
43. define('M_2DIVPI', 0.63661977236758134307553505349006);
45. /** MAX_ITERATIONS */
46. define('MAX_ITERATIONS', 256);
48. /** PRECISION */
49. define('PRECISION', 8.88E-016);
52. /**
53. * PHPExcel_Calculation_Functions
54. *
55. * @category PHPExcel
56. * @package PHPExcel_Calculation
57. * @copyright Copyright (c) 2006 - 2011 PHPExcel (http://www.codeplex.com/PHPExcel)
58. */
59. class PHPExcel_Calculation_Functions {
61. /** constants */
62. const COMPATIBILITY_EXCEL = 'Excel';
63. const COMPATIBILITY_GNUMERIC = 'Gnumeric';
64. const COMPATIBILITY_OPENOFFICE = 'OpenOfficeCalc';
66. const RETURNDATE_PHP_NUMERIC = 'P';
67. const RETURNDATE_PHP_OBJECT = 'O';
68. const RETURNDATE_EXCEL = 'E';
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;
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;
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. );
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()
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()
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()
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()
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()
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()
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()
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()
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()
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()
266. /**
267. * NULL
268. *
269. * Returns the error value #NULL!
270. *
271. * @access public
272. * @category Error Returns
273. * @return string #REF!
274. */
275. public static function NULL() {
276. return self::$_errorCodes['null'];
277. } // function NULL()
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()
294. public static function isMatrixValue($idx) {
295. return ((substr_count($idx,'.') <= 1) || ( preg_match('/\.[A-Z]/',$idx) > 0));
296. }
299. public static function isValue($idx) {
300. return ( substr_count($idx,'.') == 0);
301. }
304. public static function isCellValue($idx) {
305. return ( substr_count($idx,'.') > 1);
306. }
309. public static function _ifCondition($condition) {
310. $condition = PHPExcel_Calculation_Functions:: flattenSingleValue($condition);
311. if (! in_array($condition{0},array('>', '<', '='))) {
312. if (! is_numeric($condition)) { $condition = PHPExcel_Calculation::_wrapResult( strtoupper($condition)); }
313. return '='.$condition;
314. } else {
315. preg_match('/([<>=]+)(.*)/',$condition,$matches);
316. list(,$operator,$operand) = $matches;
317. if (! is_numeric($operand)) { $operand = PHPExcel_Calculation::_wrapResult( strtoupper($operand)); }
318. return $operator.$operand;
319. }
320. } // function _ifCondition()
323. /**
324. * ERROR_TYPE
325. *
326. * @param mixed $value Value to check
327. * @return boolean
328. */
329. public static function ERROR_TYPE($value = '') {
330. $value = self:: flattenSingleValue($value);
332. $i = 1;
333. foreach(self::$_errorCodes as $errorCode) {
334. if ($value == $errorCode) {
335. return $i;
336. }
337. ++$i;
338. }
339. return self::$_errorCodes['na'];
340. } // function ERROR_TYPE()
343. /**
344. * IS_BLANK
345. *
346. * @param mixed $value Value to check
347. * @return boolean
348. */
349. public static function IS_BLANK($value=null) {
350. if (! is_null($value)) {
351. $value = self:: flattenSingleValue($value);
352. }
354. return is_null($value);
355. } // function IS_BLANK()
358. /**
359. * IS_ERR
360. *
361. * @param mixed $value Value to check
362. * @return boolean
363. */
364. public static function IS_ERR($value = '') {
365. $value = self:: flattenSingleValue($value);
367. return self:: IS_ERROR($value) && (! self:: IS_NA($value));
368. } // function IS_ERR()
371. /**
372. * IS_ERROR
373. *
374. * @param mixed $value Value to check
375. * @return boolean
376. */
377. public static function IS_ERROR($value = '') {
378. $value = self:: flattenSingleValue($value);
380. return in_array($value, array_values(self::$_errorCodes));
381. } // function IS_ERROR()
384. /**
385. * IS_NA
386. *
387. * @param mixed $value Value to check
388. * @return boolean
389. */
390. public static function IS_NA($value = '') {
391. $value = self:: flattenSingleValue($value);
393. return ($value === self::$_errorCodes['na']);
394. } // function IS_NA()
397. /**
398. * IS_EVEN
399. *
400. * @param mixed $value Value to check
401. * @return boolean
402. */
403. public static function IS_EVEN($value = 0) {
404. $value = self:: flattenSingleValue($value);
406. if (( is_bool($value)) || (( is_string($value)) && (! is_numeric($value)))) {
407. return self::$_errorCodes['value'];
408. }
409. return ($value % 2 == 0);
410. } // function IS_EVEN()
413. /**
414. * IS_ODD
415. *
416. * @param mixed $value Value to check
417. * @return boolean
418. */
419. public static function IS_ODD($value = null) {
420. $value = self:: flattenSingleValue($value);
422. if (( is_bool($value)) || (( is_string($value)) && (! is_numeric($value)))) {
423. return self::$_errorCodes['value'];
424. }
425. return (abs($value) % 2 == 1);
426. } // function IS_ODD()
429. /**
430. * IS_NUMBER
431. *
432. * @param mixed $value Value to check
433. * @return boolean
434. */
435. public static function IS_NUMBER($value = 0) {
436. $value = self:: flattenSingleValue($value);
438. if ( is_string($value)) {
439. return False;
440. }
441. return is_numeric($value);
442. } // function IS_NUMBER()
445. /**
446. * IS_LOGICAL
447. *
448. * @param mixed $value Value to check
449. * @return boolean
450. */
451. public static function IS_LOGICAL($value = true) {
452. $value = self:: flattenSingleValue($value);
454. return is_bool($value);
455. } // function IS_LOGICAL()
458. /**
459. * IS_TEXT
460. *
461. * @param mixed $value Value to check
462. * @return boolean
463. */
464. public static function IS_TEXT($value = '') {
465. $value = self:: flattenSingleValue($value);
467. return is_string($value);
468. } // function IS_TEXT()
471. /**
472. * IS_NONTEXT
473. *
474. * @param mixed $value Value to check
475. * @return boolean
476. */
477. public static function IS_NONTEXT($value = '') {
478. return ! self:: IS_TEXT($value);
479. } // function IS_NONTEXT()
482. /**
483. * VERSION
484. *
485. * @return string Version information
486. */
487. public static function VERSION() {
488. return 'PHPExcel 1.7.6, 2011-02-27';
489. } // function VERSION()
492. /**
493. * N
494. *
495. * Returns a value converted to a number
496. *
497. * @param value The value you want converted
498. * @return number N converts values listed in the following table
499. * If value is or refers to N returns
500. * A number That number
501. * A date The serial number of that date
502. * TRUE 1
503. * FALSE 0
504. * An error value The error value
505. * Anything else 0
506. */
507. public static function N($value) {
508. while ( is_array($value)) {
509. $value = array_shift($value);
510. }
512. switch ( gettype($value)) {
513. case 'double' :
514. case 'float' :
515. case 'integer' :
516. return $value;
517. break;
518. case 'boolean' :
519. return (integer) $value;
520. break;
521. case 'string' :
522. // Errors
523. if (( strlen($value) > 0) && ($value{0} == '#')) {
524. return $value;
525. }
526. break;
527. }
528. return 0;
529. } // function N()
532. /**
533. * TYPE
534. *
535. * Returns a number that identifies the type of a value
536. *
537. * @param value The value you want tested
538. * @return number N converts values listed in the following table
539. * If value is or refers to N returns
540. * A number 1
541. * Text 2
542. * Logical Value 4
543. * An error value 16
544. * Array or Matrix 64
545. */
546. public static function TYPE($value) {
547. $value = self:: flattenArrayIndexed($value);
548. if ( is_array($value) && ( count($value) > 1)) {
549. $a = array_keys($value);
550. $a = array_pop($a);
551. // Range of cells is an error
552. if (self:: isCellValue($a)) {
553. return 16;
554. // Test for Matrix
555. } elseif (self:: isMatrixValue($a)) {
556. return 64;
557. }
558. } elseif( count($value) == 0) {
559. // Empty Cell
560. return 1;
561. }
562. $value = self:: flattenSingleValue($value);
564. if (( is_float($value)) || ( is_int($value))) {
565. return 1;
566. } elseif( is_bool($value)) {
567. return 4;
568. } elseif( is_array($value)) {
569. return 64;
570. break;
571. } elseif( is_string($value)) {
572. // Errors
573. if (( strlen($value) > 0) && ($value{0} == '#')) {
574. return 16;
575. }
576. return 2;
577. }
578. return 0;
579. } // function TYPE()
582. /**
583. * Convert a multi-dimensional array to a simple 1-dimensional array
584. *
585. * @param array $array Array to be flattened
586. * @return array Flattened array
587. */
588. public static function flattenArray($array) {
589. if (! is_array($array)) {
590. return (array) $array;
591. }
593. $arrayValues = array();
594. foreach ($array as $value) {
595. if ( is_array($value)) {
596. foreach ($value as $val) {
597. if ( is_array($val)) {
598. foreach ($val as $v) {
599. $arrayValues[] = $v;
600. }
601. } else {
602. $arrayValues[] = $val;
603. }
604. }
605. } else {
606. $arrayValues[] = $value;
607. }
608. }
610. return $arrayValues;
611. } // function flattenArray()
614. /**
615. * Convert a multi-dimensional array to a simple 1-dimensional array, but retain an element of indexing
616. *
617. * @param array $array Array to be flattened
618. * @return array Flattened array
619. */
620. public static function flattenArrayIndexed($array) {
621. if (! is_array($array)) {
622. return (array) $array;
623. }
625. $arrayValues = array();
626. foreach ($array as $k1 => $value) {
627. if ( is_array($value)) {
628. foreach ($value as $k2 => $val) {
629. if ( is_array($val)) {
630. foreach ($val as $k3 => $v) {
631. $arrayValues[$k1.'.'.$k2.'.'.$k3] = $v;
632. }
633. } else {
634. $arrayValues[$k1.'.'.$k2] = $val;
635. }
636. }
637. } else {
638. $arrayValues[$k1] = $value;
639. }
640. }
642. return $arrayValues;
643. } // function flattenArrayIndexed()
646. /**
647. * Convert an array to a single scalar value by extracting the first element
648. *
649. * @param mixed $value Array or scalar value
650. * @return mixed
651. */
652. public static function flattenSingleValue($value = '') {
653. while ( is_array($value)) {
654. $value = array_pop($value);
655. }
657. return $value;
658. } // function flattenSingleValue()
660. } // class PHPExcel_Calculation_Functions
663. //
664. // There are a few mathematical functions that aren't available on all versions of PHP for all platforms
665. // These functions aren't available in Windows implementations of PHP prior to version 5.3.0
666. // So we test if they do exist for this version of PHP/operating platform; and if not we create them
667. //
668. if (! function_exists('acosh')) {
669. function acosh($x) {
670. return 2 * log( sqrt(($x + 1) / 2) + sqrt(($x - 1) / 2));
671. } // function acosh()
672. }
674. if (! function_exists('asinh')) {
675. function asinh($x) {
676. return log($x + sqrt(1 + $x * $x));
677. } // function asinh()
678. }
680. if (! function_exists('atanh')) {
681. function atanh($x) {
682. return ( log(1 + $x) - log(1 - $x)) / 2;
683. } // function atanh()
684. }
686. if (! function_exists('money_format')) {
687. function money_format($format, $number) {
688. $regex = array( '/%((?:[\^!\-]|\+|\(|\=.)*)([0-9]+)?(?:#([0-9]+))?',
689. '(?:\.([0-9]+))?([in%])/'
690. );
691. $regex = implode('', $regex);
692. if ( setlocale(LC_MONETARY, null) == '') {
693. setlocale(LC_MONETARY, '');
694. }
695. $locale = localeconv();
696. $number = floatval($number);
697. if (! preg_match($regex, $format, $fmatch)) {
698. trigger_error("No format specified or invalid format", E_USER_WARNING);
699. return $number;
700. }
701. $flags = array( 'fillchar' => preg_match('/\=(.)/', $fmatch[1], $match) ? $match[1] : ' ',
702. 'nogroup' => preg_match('/\^/', $fmatch[1]) > 0,
703. 'usesignal' => preg_match('/\+|\(/', $fmatch[1], $match) ? $match[0] : '+',
704. 'nosimbol' => preg_match('/\!/', $fmatch[1]) > 0,
705. 'isleft' => preg_match('/\-/', $fmatch[1]) > 0
706. );
707. $width = trim($fmatch[2]) ? (int)$fmatch[2] : 0;
708. $left = trim($fmatch[3]) ? (int)$fmatch[3] : 0;
709. $right = trim($fmatch[4]) ? (int)$fmatch[4] : $locale['int_frac_digits'];
710. $conversion = $fmatch[5];
711. $positive = true;
712. if ($number < 0) {
713. $positive = false;
714. $number *= -1;
715. }
716. $letter = $positive ? 'p' : 'n';
717. $prefix = $suffix = $cprefix = $csuffix = $signal = '';
718. if (!$positive) {
719. $signal = $locale['negative_sign'];
720. switch (true) {
721. case $locale['n_sign_posn'] == 0 || $flags['usesignal'] == '(':
722. $prefix = '(';
723. $suffix = ')';
724. break;
725. case $locale['n_sign_posn'] == 1:
726. $prefix = $signal;
727. break;
728. case $locale['n_sign_posn'] == 2:
729. $suffix = $signal;
730. break;
731. case $locale['n_sign_posn'] == 3:
732. $cprefix = $signal;
733. break;
734. case $locale['n_sign_posn'] == 4:
735. $csuffix = $signal;
736. break;
737. }
738. }
739. if (!$flags['nosimbol']) {
740. $currency = $cprefix;
741. $currency .= ($conversion == 'i' ? $locale['int_curr_symbol'] : $locale['currency_symbol']);
742. $currency .= $csuffix;
743. $currency = iconv('ISO-8859-1','UTF-8',$currency);
744. } else {
745. $currency = '';
746. }
747. $space = $locale["{$letter}_sep_by_space"] ? ' ' : '';
749. $number = number_format($number, $right, $locale['mon_decimal_point'], $flags['nogroup'] ? '' : $locale['mon_thousands_sep'] );
750. $number = explode($locale['mon_decimal_point'], $number);
752. $n = strlen($prefix) + strlen($currency);
753. if ($left > 0 && $left > $n) {
754. if ($flags['isleft']) {
755. $number[0] .= str_repeat($flags['fillchar'], $left - $n);
756. } else {
757. $number[0] = str_repeat($flags['fillchar'], $left - $n) . $number[0];
758. }
759. }
760. $number = implode($locale['mon_decimal_point'], $number);
761. if ($locale["{$letter}_cs_precedes"]) {
762. $number = $prefix . $currency . $space . $number . $suffix;
763. } else {
764. $number = $prefix . $number . $space . $currency . $suffix;
765. }
766. if ($width > 0) {
767. $number = str_pad($number, $width, $flags['fillchar'], $flags['isleft'] ? STR_PAD_RIGHT : STR_PAD_LEFT);
768. }
769. $format = str_replace($fmatch[0], $number, $format);
770. return $format;
771. } // function money_format()
772. }
775. //
776. // Strangely, PHP doesn't have a mb_str_replace multibyte function
777. // As we'll only ever use this function with UTF-8 characters, we can simply "hard-code" the character set
778. //
779. if ((! function_exists('mb_str_replace')) &&
780. ( function_exists('mb_substr')) && ( function_exists('mb_strlen')) && ( function_exists('mb_strpos'))) {
781. function mb_str_replace($search, $replace, $subject) {
782. if( is_array($subject)) {
783. $ret = array();
784. foreach($subject as $key => $val) {
785. $ret[$key] = mb_str_replace($search, $replace, $val);
786. }
787. return $ret;
788. }
790. foreach((array) $search as $key => $s) {
791. if($s == '') {
792. continue;
793. }
794. $r = ! is_array($replace) ? $replace : ( array_key_exists($key, $replace) ? $replace[$key] : '');
795. $pos = mb_strpos($subject, $s, 0, 'UTF-8');
796. while($pos !== false) {
797. $subject = mb_substr($subject, 0, $pos, 'UTF-8') . $r . mb_substr($subject, $pos + mb_strlen($s, 'UTF-8'), 65535, 'UTF-8');
798. $pos = mb_strpos($subject, $s, $pos + mb_strlen($r, 'UTF-8'), 'UTF-8');
799. }
800. }
801. return $subject;
802. }
803. }

Documentation generated on Sun, 27 Feb 2011 16:32:08 -0800 by phpDocumentor 1.4.3