[Summary view] [Print] [Text view]

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