<?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;
|
|
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.
|
*/
|
protected static string $compatibilityMode = self::COMPATIBILITY_EXCEL;
|
|
/**
|
* Data Type to use when returning date values.
|
*/
|
protected static string $returnDateType = self::RETURNDATE_EXCEL;
|
|
/**
|
* Set the Compatibility Mode.
|
*
|
* @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(string $compatibilityMode): bool
|
{
|
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.
|
*
|
* @return string Compatibility Mode
|
* Possible Return values are:
|
* Functions::COMPATIBILITY_EXCEL 'Excel'
|
* Functions::COMPATIBILITY_GNUMERIC 'Gnumeric'
|
* Functions::COMPATIBILITY_OPENOFFICE 'OpenOfficeCalc'
|
*/
|
public static function getCompatibilityMode(): string
|
{
|
return self::$compatibilityMode;
|
}
|
|
/**
|
* 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(string $returnDateType): bool
|
{
|
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).
|
*
|
* @return string Return Date Format
|
* Possible Return values are:
|
* Functions::RETURNDATE_UNIX_TIMESTAMP 'P'
|
* Functions::RETURNDATE_PHP_DATETIME_OBJECT 'O'
|
* Functions::RETURNDATE_EXCEL ' 'E'
|
*/
|
public static function getReturnDateType(): string
|
{
|
return self::$returnDateType;
|
}
|
|
/**
|
* DUMMY.
|
*
|
* @return string #Not Yet Implemented
|
*/
|
public static function DUMMY(): string
|
{
|
return '#Not Yet Implemented';
|
}
|
|
public static function isMatrixValue(mixed $idx): bool
|
{
|
return (substr_count($idx, '.') <= 1) || (preg_match('/\.[A-Z]/', $idx) > 0);
|
}
|
|
public static function isValue(mixed $idx): bool
|
{
|
return substr_count($idx, '.') === 0;
|
}
|
|
public static function isCellValue(mixed $idx): bool
|
{
|
return substr_count($idx, '.') > 1;
|
}
|
|
public static function ifCondition(mixed $condition): string
|
{
|
$condition = self::flattenSingleValue($condition);
|
|
if ($condition === '' || $condition === null) {
|
return '=""';
|
}
|
if (!is_string($condition) || !in_array($condition[0], ['>', '<', '='], true)) {
|
$condition = self::operandSpecialHandling($condition);
|
if (is_bool($condition)) {
|
return '=' . ($condition ? 'TRUE' : 'FALSE');
|
} elseif (!is_numeric($condition)) {
|
if ($condition !== '""') { // Not an empty string
|
// Escape any quotes in the string value
|
$condition = (string) preg_replace('/"/ui', '""', $condition);
|
}
|
$condition = Calculation::wrapResult(strtoupper($condition));
|
}
|
|
return str_replace('""""', '""', '=' . $condition);
|
}
|
preg_match('/(=|<[>=]?|>=?)(.*)/', $condition, $matches);
|
[, $operator, $operand] = $matches;
|
|
$operand = self::operandSpecialHandling($operand);
|
if (is_numeric(trim($operand, '"'))) {
|
$operand = trim($operand, '"');
|
} elseif (!is_numeric($operand) && $operand !== 'FALSE' && $operand !== 'TRUE') {
|
$operand = str_replace('"', '""', $operand);
|
$operand = Calculation::wrapResult(strtoupper($operand));
|
}
|
|
return str_replace('""""', '""', $operator . $operand);
|
}
|
|
private static function operandSpecialHandling(mixed $operand): mixed
|
{
|
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;
|
}
|
|
/**
|
* Convert a multi-dimensional array to a simple 1-dimensional array.
|
*
|
* @param mixed $array Array to be flattened
|
*
|
* @return array Flattened array
|
*/
|
public static function flattenArray(mixed $array): array
|
{
|
if (!is_array($array)) {
|
return (array) $array;
|
}
|
|
$flattened = [];
|
$stack = array_values($array);
|
|
while (!empty($stack)) {
|
$value = array_shift($stack);
|
|
if (is_array($value)) {
|
array_unshift($stack, ...array_values($value));
|
} else {
|
$flattened[] = $value;
|
}
|
}
|
|
return $flattened;
|
}
|
|
public static function scalar(mixed $value): mixed
|
{
|
if (!is_array($value)) {
|
return $value;
|
}
|
|
do {
|
$value = array_pop($value);
|
} while (is_array($value));
|
|
return $value;
|
}
|
|
/**
|
* Convert a multi-dimensional array to a simple 1-dimensional array, but retain an element of indexing.
|
*
|
* @param array|mixed $array Array to be flattened
|
*
|
* @return array Flattened array
|
*/
|
public static function flattenArrayIndexed($array): 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
|
*/
|
public static function flattenSingleValue(mixed $value): mixed
|
{
|
while (is_array($value)) {
|
$value = array_shift($value);
|
}
|
|
return $value;
|
}
|
|
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()));
|
}
|
}
|
|
return $coordinate;
|
}
|
|
public static function trimTrailingRange(string $coordinate): string
|
{
|
return (string) preg_replace('/:[\\w\$]+$/', '', $coordinate);
|
}
|
|
public static function trimSheetFromCellReference(string $coordinate): string
|
{
|
if (str_contains($coordinate, '!')) {
|
$coordinate = substr($coordinate, strrpos($coordinate, '!') + 1);
|
}
|
|
return $coordinate;
|
}
|
}
|
