See Release Notes
Long Term Support Release
<?php namespace PhpOffice\PhpSpreadsheet\Calculation; use PhpOffice\PhpSpreadsheet\Cell\Cell;> use PhpOffice\PhpSpreadsheet\Shared\Date;class Functions { const PRECISION = 8.88E-016; /** * 2 / PI. */ const M_2DIVPI = 0.63661977236758134307553505349006;< /** constants */const COMPATIBILITY_EXCEL = 'Excel'; const COMPATIBILITY_GNUMERIC = 'Gnumeric'; const COMPATIBILITY_OPENOFFICE = 'OpenOfficeCalc';> /** Use of RETURNDATE_PHP_NUMERIC is discouraged - not 32-bit Y2038-safe, no timezone. */const RETURNDATE_PHP_NUMERIC = 'P';> /** Use of RETURNDATE_UNIX_TIMESTAMP is discouraged - not 32-bit Y2038-safe, no timezone. */const RETURNDATE_UNIX_TIMESTAMP = 'P'; const RETURNDATE_PHP_OBJECT = 'O'; const RETURNDATE_PHP_DATETIME_OBJECT = 'O'; const RETURNDATE_EXCEL = 'E'; /** * Compatibility mode to use for error checking and responses. * * @var string */ protected static $compatibilityMode = self::COMPATIBILITY_EXCEL; /** * Data Type to use when returning date values. * * @var string */ protected static $returnDateType = self::RETURNDATE_EXCEL; /**< * List of error codes. < * < * @var array < */ < protected static $errorCodes = [ < 'null' => '#NULL!', < 'divisionbyzero' => '#DIV/0!', < 'value' => '#VALUE!', < 'reference' => '#REF!', < 'name' => '#NAME?', < 'num' => '#NUM!', < 'na' => '#N/A', < 'gettingdata' => '#GETTING_DATA', < ]; < < /*** Set the Compatibility Mode. *< * @category Function Configuration < ** @param string $compatibilityMode Compatibility Mode * Permitted values are: * Functions::COMPATIBILITY_EXCEL 'Excel' * Functions::COMPATIBILITY_GNUMERIC 'Gnumeric' * Functions::COMPATIBILITY_OPENOFFICE 'OpenOfficeCalc' * * @return bool (Success or Failure) */ public static function setCompatibilityMode($compatibilityMode) {< if (($compatibilityMode == self::COMPATIBILITY_EXCEL) ||> if ( > ($compatibilityMode == self::COMPATIBILITY_EXCEL) ||($compatibilityMode == self::COMPATIBILITY_GNUMERIC) || ($compatibilityMode == self::COMPATIBILITY_OPENOFFICE) ) { self::$compatibilityMode = $compatibilityMode; return true; } return false; } /** * Return the current Compatibility Mode. *< * @category Function Configuration < ** @return string Compatibility Mode * Possible Return values are: * Functions::COMPATIBILITY_EXCEL 'Excel' * Functions::COMPATIBILITY_GNUMERIC 'Gnumeric' * Functions::COMPATIBILITY_OPENOFFICE 'OpenOfficeCalc' */ public static function getCompatibilityMode() { return self::$compatibilityMode; } /**< * Set the Return Date Format used by functions that return a date/time (Excel, PHP Serialized Numeric or PHP Object). < * < * @category Function Configuration> * Set the Return Date Format used by functions that return a date/time (Excel, PHP Serialized Numeric or PHP DateTime Object).* * @param string $returnDateType Return Date Format * Permitted values are: * Functions::RETURNDATE_UNIX_TIMESTAMP 'P' * Functions::RETURNDATE_PHP_DATETIME_OBJECT 'O' * Functions::RETURNDATE_EXCEL 'E' * * @return bool Success or failure */ public static function setReturnDateType($returnDateType) {< if (($returnDateType == self::RETURNDATE_UNIX_TIMESTAMP) ||> if ( > ($returnDateType == self::RETURNDATE_UNIX_TIMESTAMP) ||($returnDateType == self::RETURNDATE_PHP_DATETIME_OBJECT) || ($returnDateType == self::RETURNDATE_EXCEL) ) { self::$returnDateType = $returnDateType; return true; } return false; } /** * Return the current Return Date Format for functions that return a date/time (Excel, PHP Serialized Numeric or PHP Object). *< * @category Function Configuration < ** @return string Return Date Format * Possible Return values are: * Functions::RETURNDATE_UNIX_TIMESTAMP 'P' * Functions::RETURNDATE_PHP_DATETIME_OBJECT 'O'< * Functions::RETURNDATE_EXCEL 'E'> * Functions::RETURNDATE_EXCEL ' 'E'*/ public static function getReturnDateType() { return self::$returnDateType; } /** * DUMMY. *< * @category Error Returns < ** @return string #Not Yet Implemented */ public static function DUMMY() { return '#Not Yet Implemented'; }> /** @param mixed $idx */ /** > public static function isMatrixValue($idx): bool * DIV0. > { * > return (substr_count($idx, '.') <= 1) || (preg_match('/\.[A-Z]/', $idx) > 0); * @category Error Returns > } * > * @return string #Not Yet Implemented > /** @param mixed $idx */ */ > public static function isValue($idx): bool public static function DIV0() > { { > return substr_count($idx, '.') === 0; return self::$errorCodes['divisionbyzero']; > } } > > /** @param mixed $idx */ /** > public static function isCellValue($idx): bool * NA. > { * > return substr_count($idx, '.') > 1; * Excel Function: > } * =NA() > * > /** @param mixed $condition */ * Returns the error value #N/A > public static function ifCondition($condition): string * #N/A is the error value that means "no value is available." > { * > $condition = self::flattenSingleValue($condition); * @category Logical Functions > * > if ($condition === '') { * @return string #N/A! > return '=""'; */ > } public static function NA() > if (!is_string($condition) || !in_array($condition[0], ['>', '<', '='], true)) { { > $condition = self::operandSpecialHandling($condition); return self::$errorCodes['na']; > if (is_bool($condition)) { } > return '=' . ($condition ? 'TRUE' : 'FALSE'); > } elseif (!is_numeric($condition)) { /** > if ($condition !== '""') { // Not an empty string * NaN. > // Escape any quotes in the string value * > $condition = (string) preg_replace('/"/ui', '""', $condition); * Returns the error value #NUM! > } * > $condition = Calculation::wrapResult(strtoupper($condition)); * @category Error Returns > } * > * @return string #NUM! > return str_replace('""""', '""', '=' . $condition); */ > } public static function NAN() > preg_match('/(=|<[>=]?|>=?)(.*)/', $condition, $matches); { > [, $operator, $operand] = $matches; return self::$errorCodes['num']; > } > $operand = self::operandSpecialHandling($operand); > if (is_numeric(trim($operand, '"'))) { /** > $operand = trim($operand, '"'); * NAME. > } elseif (!is_numeric($operand) && $operand !== 'FALSE' && $operand !== 'TRUE') { * > $operand = str_replace('"', '""', $operand); * Returns the error value #NAME? > $operand = Calculation::wrapResult(strtoupper($operand)); * > } * @category Error Returns > * > return str_replace('""""', '""', $operator . $operand); * @return string #NAME? > } */ >< * DIV0.> * @param mixed $operand< * @category Error Returns < * < * @return string #Not Yet Implemented> * @return mixed< public static function DIV0()> private static function operandSpecialHandling($operand)< return self::$errorCodes['divisionbyzero'];> if (is_numeric($operand) || is_bool($operand)) { > return $operand; > } elseif (strtoupper($operand) === Calculation::getTRUE() || strtoupper($operand) === Calculation::getFALSE()) { > return strtoupper($operand); > } > > // Check for percentage > if (preg_match('/^\-?\d*\.?\d*\s?\%$/', $operand)) { > return ((float) rtrim($operand, '%')) / 100; > } > > // Check for dates > if (($dateValueOperand = Date::stringToExcel($operand)) !== false) { > return $dateValueOperand; > } > > return $operand;< * NA. < * < * Excel Function: < * =NA()> * NULL.< * Returns the error value #N/A < * #N/A is the error value that means "no value is available."> * Returns the error value #NULL!< * @category Logical Functions> * @deprecated 1.23.0 Use the null() method in the Information\ExcelError class instead > * @see Information\ExcelError::null()< * @return string #N/A!> * @return string #NULL!< public static function NA()> public static function null()< return self::$errorCodes['na'];> return Information\ExcelError::null();< * @category Error Returns> * @deprecated 1.23.0 Use the NAN() method in the Information\Error class instead > * @see Information\ExcelError::NAN()< return self::$errorCodes['num']; < } < < /** < * NAME. < * < * Returns the error value #NAME? < * < * @category Error Returns < * < * @return string #NAME? < */ < public static function NAME() < { < return self::$errorCodes['name'];> return Information\ExcelError::NAN();< * @category Error Returns> * @deprecated 1.23.0 Use the REF() method in the Information\ExcelError class instead > * @see Information\ExcelError::REF()< return self::$errorCodes['reference'];> return Information\ExcelError::REF();< * NULL.> * NA.< * Returns the error value #NULL!> * Excel Function: > * =NA()< * @category Error Returns> * Returns the error value #N/A > * #N/A is the error value that means "no value is available."< * @return string #NULL!> * @deprecated 1.23.0 Use the NA() method in the Information\ExcelError class instead > * @see Information\ExcelError::NA() > * > * @return string #N/A!< public static function null()> public static function NA()< return self::$errorCodes['null'];> return Information\ExcelError::NA();< * @category Error Returns> * @deprecated 1.23.0 Use the VALUE() method in the Information\ExcelError class instead > * @see Information\ExcelError::VALUE()*/ public static function VALUE() {< return self::$errorCodes['value']; < } < < public static function isMatrixValue($idx) < { < return (substr_count($idx, '.') <= 1) || (preg_match('/\.[A-Z]/', $idx) > 0);> return Information\ExcelError::VALUE();}< public static function isValue($idx) < { < return substr_count($idx, '.') == 0; < } < < public static function isCellValue($idx)> /** > * NAME. > * > * Returns the error value #NAME? > * > * @deprecated 1.23.0 Use the NAME() method in the Information\ExcelError class instead > * @see Information\ExcelError::NAME() > * > * @return string #NAME? > */ > public static function NAME(){< return substr_count($idx, '.') > 1;> return Information\ExcelError::NAME();}< public static function ifCondition($condition)> /** > * DIV0. > * > * @deprecated 1.23.0 Use the DIV0() method in the Information\ExcelError class instead > * @see Information\ExcelError::DIV0() > * > * @return string #Not Yet Implemented > */ > public static function DIV0(){< $condition = self::flattenSingleValue($condition); < < if ($condition === '') { < $condition = '=""'; < } < < if (!is_string($condition) || !in_array($condition[0], ['>', '<', '='])) { < if (!is_numeric($condition)) { < $condition = Calculation::wrapResult(strtoupper($condition)); < } < < return str_replace('""""', '""', '=' . $condition); < } < preg_match('/(=|<[>=]?|>=?)(.*)/', $condition, $matches); < [, $operator, $operand] = $matches; < < if (is_numeric(trim($operand, '"'))) { < $operand = trim($operand, '"'); < } elseif (!is_numeric($operand)) { < $operand = str_replace('"', '""', $operand); < $operand = Calculation::wrapResult(strtoupper($operand)); < } < < return str_replace('""""', '""', $operator . $operand);> return Information\ExcelError::DIV0();} /** * ERROR_TYPE. * * @param mixed $value Value to check *< * @return bool> * @deprecated 1.23.0 Use the type() method in the Information\ExcelError class instead > * @see Information\ExcelError::type() > * > * @return array|int|string*/ public static function errorType($value = '') {< $value = self::flattenSingleValue($value); < < $i = 1; < foreach (self::$errorCodes as $errorCode) { < if ($value === $errorCode) { < return $i; < } < ++$i; < } < < return self::NA();> return Information\ExcelError::type($value);} /** * IS_BLANK. * * @param mixed $value Value to check *< * @return bool> * @deprecated 1.23.0 Use the isBlank() method in the Information\Value class instead > * @see Information\Value::isBlank() > * > * @return array|bool*/ public static function isBlank($value = null) {< if ($value !== null) { < $value = self::flattenSingleValue($value); < } < < return $value === null;> return Information\Value::isBlank($value);} /** * IS_ERR. * * @param mixed $value Value to check *< * @return bool> * @deprecated 1.23.0 Use the isErr() method in the Information\ErrorValue class instead > * @see Information\ErrorValue::isErr() > * > * @return array|bool*/ public static function isErr($value = '') {< $value = self::flattenSingleValue($value); < < return self::isError($value) && (!self::isNa(($value)));> return Information\ErrorValue::isErr($value);} /** * IS_ERROR. * * @param mixed $value Value to check *< * @return bool> * @deprecated 1.23.0 Use the isError() method in the Information\ErrorValue class instead > * @see Information\ErrorValue::isError() > * > * @return array|bool*/ public static function isError($value = '') {< $value = self::flattenSingleValue($value); < < if (!is_string($value)) { < return false; < } < < return in_array($value, self::$errorCodes);> return Information\ErrorValue::isError($value);} /** * IS_NA. * * @param mixed $value Value to check *< * @return bool> * @deprecated 1.23.0 Use the isNa() method in the Information\ErrorValue class instead > * @see Information\ErrorValue::isNa() > * > * @return array|bool*/ public static function isNa($value = '') {< $value = self::flattenSingleValue($value); < < return $value === self::NA();> return Information\ErrorValue::isNa($value);} /** * IS_EVEN. * * @param mixed $value Value to check *< * @return bool|string> * @deprecated 1.23.0 Use the isEven() method in the Information\Value class instead > * @see Information\Value::isEven() > * > * @return array|bool|string*/ public static function isEven($value = null) {< $value = self::flattenSingleValue($value); < < if ($value === null) { < return self::NAME(); < } elseif ((is_bool($value)) || ((is_string($value)) && (!is_numeric($value)))) { < return self::VALUE(); < } < < return $value % 2 == 0;> return Information\Value::isEven($value);} /** * IS_ODD. * * @param mixed $value Value to check *< * @return bool|string> * @deprecated 1.23.0 Use the isOdd() method in the Information\Value class instead > * @see Information\Value::isOdd() > * > * @return array|bool|string*/ public static function isOdd($value = null) {< $value = self::flattenSingleValue($value); < < if ($value === null) { < return self::NAME(); < } elseif ((is_bool($value)) || ((is_string($value)) && (!is_numeric($value)))) { < return self::VALUE(); < } < < return abs($value) % 2 == 1;> return Information\Value::isOdd($value);} /** * IS_NUMBER. * * @param mixed $value Value to check *< * @return bool> * @deprecated 1.23.0 Use the isNumber() method in the Information\Value class instead > * @see Information\Value::isNumber() > * > * @return array|bool*/ public static function isNumber($value = null) {< $value = self::flattenSingleValue($value); < < if (is_string($value)) { < return false; < } < < return is_numeric($value);> return Information\Value::isNumber($value);} /** * IS_LOGICAL. * * @param mixed $value Value to check *< * @return bool> * @deprecated 1.23.0 Use the isLogical() method in the Information\Value class instead > * @see Information\Value::isLogical() > * > * @return array|bool*/ public static function isLogical($value = null) {< $value = self::flattenSingleValue($value); < < return is_bool($value);> return Information\Value::isLogical($value);} /** * IS_TEXT. * * @param mixed $value Value to check *< * @return bool> * @deprecated 1.23.0 Use the isText() method in the Information\Value class instead > * @see Information\Value::isText() > * > * @return array|bool*/ public static function isText($value = null) {< $value = self::flattenSingleValue($value); < < return is_string($value) && !self::isError($value);> return Information\Value::isText($value);} /** * IS_NONTEXT. * * @param mixed $value Value to check *< * @return bool> * @deprecated 1.23.0 Use the isNonText() method in the Information\Value class instead > * @see Information\Value::isNonText() > * > * @return array|bool*/ public static function isNonText($value = null) {< return !self::isText($value);> return Information\Value::isNonText($value);} /** * N. * * Returns a value converted to a number *> * @deprecated 1.23.0 Use the asNumber() method in the Information\Value class instead * @param null|mixed $value The value you want converted > * @see Information\Value::asNumber() * > *< * @return number N converts values listed in the following table> * @return number|string N converts values listed in the following table* If value is or refers to N returns * A number That number * A date The serial number of that date * TRUE 1 * FALSE 0 * An error value The error value * Anything else 0 */ public static function n($value = null) {< while (is_array($value)) { < $value = array_shift($value); < } < < switch (gettype($value)) { < case 'double': < case 'float': < case 'integer': < return $value; < case 'boolean': < return (int) $value; < case 'string': < // Errors < if ((strlen($value) > 0) && ($value[0] == '#')) { < return $value; < } < < break; < } < < return 0;> return Information\Value::asNumber($value);} /** * TYPE. * * Returns a number that identifies the type of a value *> * @deprecated 1.23.0 Use the type() method in the Information\Value class instead * @param null|mixed $value The value you want tested > * @see Information\Value::type() * > ** @return number N converts values listed in the following table * If value is or refers to N returns * A number 1 * Text 2 * Logical Value 4 * An error value 16 * Array or Matrix 64 */ public static function TYPE($value = null) {< $value = self::flattenArrayIndexed($value); < if (is_array($value) && (count($value) > 1)) { < end($value); < $a = key($value); < // Range of cells is an error < if (self::isCellValue($a)) { < return 16; < // Test for Matrix < } elseif (self::isMatrixValue($a)) { < return 64; < } < } elseif (empty($value)) { < // Empty Cell < return 1; < } < $value = self::flattenSingleValue($value); < < if (($value === null) || (is_float($value)) || (is_int($value))) { < return 1; < } elseif (is_bool($value)) { < return 4; < } elseif (is_array($value)) { < return 64; < } elseif (is_string($value)) { < // Errors < if ((strlen($value) > 0) && ($value[0] == '#')) { < return 16; < } < < return 2; < } < < return 0;> return Information\Value::type($value);} /** * Convert a multi-dimensional array to a simple 1-dimensional array. *< * @param array $array Array to be flattened> * @param array|mixed $array Array to be flattened* * @return array Flattened array */ public static function flattenArray($array) { if (!is_array($array)) { return (array) $array; }< $arrayValues = []; < foreach ($array as $value) {> $flattened = []; > $stack = array_values($array); > > while (!empty($stack)) { > $value = array_shift($stack); >if (is_array($value)) {< foreach ($value as $val) { < if (is_array($val)) { < foreach ($val as $v) { < $arrayValues[] = $v; < }> array_unshift($stack, ...array_values($value));} else {< $arrayValues[] = $val;> $flattened[] = $value;} }< } else { < $arrayValues[] = $value;> > return $flattened;}> } > /** > * @param mixed $value return $arrayValues; > * } > * @return null|mixed > */ /** > public static function scalar($value) * Convert a multi-dimensional array to a simple 1-dimensional array, but retain an element of indexing. > { * > if (!is_array($value)) { * @param array $array Array to be flattened > return $value;< return $arrayValues;> do { > $value = array_pop($value); > } while (is_array($value)); > > return $value;< * @param array $array Array to be flattened> * @param array|mixed $array Array to be flattened*/ public static function flattenArrayIndexed($array) { if (!is_array($array)) { return (array) $array; } $arrayValues = []; foreach ($array as $k1 => $value) { if (is_array($value)) { foreach ($value as $k2 => $val) { if (is_array($val)) { foreach ($val as $k3 => $v) { $arrayValues[$k1 . '.' . $k2 . '.' . $k3] = $v; } } else { $arrayValues[$k1 . '.' . $k2] = $val; } } } else { $arrayValues[$k1] = $value; } } return $arrayValues; } /** * Convert an array to a single scalar value by extracting the first element. * * @param mixed $value Array or scalar value * * @return mixed */ public static function flattenSingleValue($value = '') { while (is_array($value)) {< $value = array_pop($value);> $value = array_shift($value);} return $value; } /** * ISFORMULA. *> * @deprecated 1.23.0 Use the isFormula() method in the Information\Value class instead * @param mixed $cellReference The cell to check > * @see Information\Value::isFormula() * @param Cell $pCell The current cell (containing this formula) > *< * @param Cell $pCell The current cell (containing this formula)> * @param ?Cell $cell The current cell (containing this formula)< * @return bool|string> * @return array|bool|string*/< public static function isFormula($cellReference = '', Cell $pCell = null)> public static function isFormula($cellReference = '', ?Cell $cell = null){< if ($pCell === null) { < return self::REF();> return Information\Value::isFormula($cellReference, $cell);}< preg_match('/^' . Calculation::CALCULATION_REGEXP_CELLREF . '$/i', $cellReference, $matches);> public static function expandDefinedName(string $coordinate, Cell $cell): string > { > $worksheet = $cell->getWorksheet(); > $spreadsheet = $worksheet->getParentOrThrow(); > // Uppercase coordinate > $pCoordinatex = strtoupper($coordinate); > // Eliminate leading equal sign > $pCoordinatex = (string) preg_replace('/^=/', '', $pCoordinatex); > $defined = $spreadsheet->getDefinedName($pCoordinatex, $worksheet); > if ($defined !== null) { > $worksheet2 = $defined->getWorkSheet(); > if (!$defined->isFormula() && $worksheet2 !== null) { > $coordinate = "'" . $worksheet2->getTitle() . "'!" . > (string) preg_replace('/^=/', '', str_replace('$', '', $defined->getValue())); > } > }< $cellReference = $matches[6] . $matches[7]; < $worksheetName = trim($matches[3], "'");> return $coordinate; > } > > public static function trimTrailingRange(string $coordinate): string > { > return (string) preg_replace('/:[\\w\$]+$/', '', $coordinate); > }< $worksheet = (!empty($worksheetName)) < ? $pCell->getWorksheet()->getParent()->getSheetByName($worksheetName) < : $pCell->getWorksheet();> public static function trimSheetFromCellReference(string $coordinate): string > { > if (strpos($coordinate, '!') !== false) { > $coordinate = substr($coordinate, strrpos($coordinate, '!') + 1); > }< return $worksheet->getCell($cellReference)->isFormula();> return $coordinate;} }