<?php namespace PhpOffice\PhpSpreadsheet\Calculation\LookupRef; use PhpOffice\PhpSpreadsheet\Calculation\ArrayEnabled; use PhpOffice\PhpSpreadsheet\Calculation\Exception; use PhpOffice\PhpSpreadsheet\Calculation\Functions; use PhpOffice\PhpSpreadsheet\Calculation\Information\ExcelError; use PhpOffice\PhpSpreadsheet\Calculation\Internal\WildcardMatch; use PhpOffice\PhpSpreadsheet\Shared\StringHelper; class ExcelMatch { use ArrayEnabled; public const MATCHTYPE_SMALLEST_VALUE = -1; public const MATCHTYPE_FIRST_VALUE = 0; public const MATCHTYPE_LARGEST_VALUE = 1; /** * MATCH. * * The MATCH function searches for a specified item in a range of cells * * Excel Function: * =MATCH(lookup_value, lookup_array, [match_type]) * * @param mixed $lookupValue The value that you want to match in lookup_array * @param mixed $lookupArray The range of cells being searched * @param mixed $matchType The number -1, 0, or 1. -1 means above, 0 means exact match, 1 means below. * If match_type is 1 or -1, the list has to be ordered. * * @return array|int|string The relative position of the found item */ public static function MATCH($lookupValue, $lookupArray, $matchType = self::MATCHTYPE_LARGEST_VALUE) { if (is_array($lookupValue)) { return self::evaluateArrayArgumentsIgnore([self::class, __FUNCTION__], 1, $lookupValue, $lookupArray, $matchType); } $lookupArray = Functions::flattenArray($lookupArray); try { // Input validation self::validateLookupValue($lookupValue); $matchType = self::validateMatchType($matchType); self::validateLookupArray($lookupArray); $keySet = array_keys($lookupArray); if ($matchType == self::MATCHTYPE_LARGEST_VALUE) { // If match_type is 1 the list has to be processed from last to first $lookupArray = array_reverse($lookupArray); $keySet = array_reverse($keySet); } $lookupArray = self::prepareLookupArray($lookupArray, $matchType); } catch (Exception $e) { return $e->getMessage(); } // MATCH() is not case sensitive, so we convert lookup value to be lower cased if it's a string type. if (is_string($lookupValue)) { $lookupValue = StringHelper::strToLower($lookupValue); } $valueKey = null; switch ($matchType) { case self::MATCHTYPE_LARGEST_VALUE: $valueKey = self::matchLargestValue($lookupArray, $lookupValue, $keySet); break; case self::MATCHTYPE_FIRST_VALUE: $valueKey = self::matchFirstValue($lookupArray, $lookupValue); break; case self::MATCHTYPE_SMALLEST_VALUE: default: $valueKey = self::matchSmallestValue($lookupArray, $lookupValue); } if ($valueKey !== null) { return ++$valueKey; } // Unsuccessful in finding a match, return #N/A error value return ExcelError::NA(); } /** * @param mixed $lookupValue * * @return mixed */ private static function matchFirstValue(array $lookupArray, $lookupValue) { if (is_string($lookupValue)) { $valueIsString = true; $wildcard = WildcardMatch::wildcard($lookupValue); } else { $valueIsString = false; $wildcard = ''; } $valueIsNumeric = is_int($lookupValue) || is_float($lookupValue); foreach ($lookupArray as $i => $lookupArrayValue) { if ( $valueIsString && is_string($lookupArrayValue) ) { if (WildcardMatch::compare($lookupArrayValue, $wildcard)) { return $i; // wildcard match } } else { if ($lookupArrayValue === $lookupValue) { return $i; // exact match } if ( $valueIsNumeric && (is_float($lookupArrayValue) || is_int($lookupArrayValue)) && $lookupArrayValue == $lookupValue ) { return $i; // exact match } } } return null; } /** * @param mixed $lookupValue * * @return mixed */ private static function matchLargestValue(array $lookupArray, $lookupValue, array $keySet) { if (is_string($lookupValue)) { if (Functions::getCompatibilityMode() === Functions::COMPATIBILITY_OPENOFFICE) { $wildcard = WildcardMatch::wildcard($lookupValue); foreach (array_reverse($lookupArray) as $i => $lookupArrayValue) { if (is_string($lookupArrayValue) && WildcardMatch::compare($lookupArrayValue, $wildcard)) { return $i; } } } else { foreach ($lookupArray as $i => $lookupArrayValue) { if ($lookupArrayValue === $lookupValue) { return $keySet[$i]; } } } } $valueIsNumeric = is_int($lookupValue) || is_float($lookupValue); foreach ($lookupArray as $i => $lookupArrayValue) { if ($valueIsNumeric && (is_int($lookupArrayValue) || is_float($lookupArrayValue))) { if ($lookupArrayValue <= $lookupValue) { return array_search($i, $keySet); } } $typeMatch = gettype($lookupValue) === gettype($lookupArrayValue); if ($typeMatch && ($lookupArrayValue <= $lookupValue)) { return array_search($i, $keySet); } } return null; } /** * @param mixed $lookupValue * * @return mixed */ private static function matchSmallestValue(array $lookupArray, $lookupValue) { $valueKey = null; if (is_string($lookupValue)) { if (Functions::getCompatibilityMode() === Functions::COMPATIBILITY_OPENOFFICE) { $wildcard = WildcardMatch::wildcard($lookupValue); foreach ($lookupArray as $i => $lookupArrayValue) { if (is_string($lookupArrayValue) && WildcardMatch::compare($lookupArrayValue, $wildcard)) { return $i; } } } } $valueIsNumeric = is_int($lookupValue) || is_float($lookupValue); // The basic algorithm is: // Iterate and keep the highest match until the next element is smaller than the searched value. // Return immediately if perfect match is found foreach ($lookupArray as $i => $lookupArrayValue) { $typeMatch = gettype($lookupValue) === gettype($lookupArrayValue); $bothNumeric = $valueIsNumeric && (is_int($lookupArrayValue) || is_float($lookupArrayValue)); if ($lookupArrayValue === $lookupValue) { // Another "special" case. If a perfect match is found, // the algorithm gives up immediately return $i; } if ($bothNumeric && $lookupValue == $lookupArrayValue) { return $i; // exact match, as above } if (($typeMatch || $bothNumeric) && $lookupArrayValue >= $lookupValue) { $valueKey = $i; } elseif ($typeMatch && $lookupArrayValue < $lookupValue) { //Excel algorithm gives up immediately if the first element is smaller than the searched value break; } } return $valueKey; } /** * @param mixed $lookupValue */ private static function validateLookupValue($lookupValue): void { // Lookup_value type has to be number, text, or logical values if ((!is_numeric($lookupValue)) && (!is_string($lookupValue)) && (!is_bool($lookupValue))) { throw new Exception(ExcelError::NA()); } } /** * @param mixed $matchType */ private static function validateMatchType($matchType): int { // Match_type is 0, 1 or -1 // However Excel accepts other numeric values, // including numeric strings and floats. // It seems to just be interested in the sign. if (!is_numeric($matchType)) { throw new Exception(ExcelError::Value()); } if ($matchType > 0) { return self::MATCHTYPE_LARGEST_VALUE; } if ($matchType < 0) { return self::MATCHTYPE_SMALLEST_VALUE; } return self::MATCHTYPE_FIRST_VALUE; } private static function validateLookupArray(array $lookupArray): void { // Lookup_array should not be empty $lookupArraySize = count($lookupArray); if ($lookupArraySize <= 0) { throw new Exception(ExcelError::NA()); } } /** * @param mixed $matchType */ private static function prepareLookupArray(array $lookupArray, $matchType): array { // Lookup_array should contain only number, text, or logical values, or empty (null) cells foreach ($lookupArray as $i => $value) { // check the type of the value if ((!is_numeric($value)) && (!is_string($value)) && (!is_bool($value)) && ($value !== null)) { throw new Exception(ExcelError::NA()); } // Convert strings to lowercase for case-insensitive testing if (is_string($value)) { $lookupArray[$i] = StringHelper::strToLower($value); } if ( ($value === null) && (($matchType == self::MATCHTYPE_LARGEST_VALUE) || ($matchType == self::MATCHTYPE_SMALLEST_VALUE)) ) { unset($lookupArray[$i]); } } return $lookupArray; } }