[Summary view]

Differences Between: [Versions 400 and 401] [Versions 401 and 402] [Versions 401 and 403]

   1  <?php
   2  
   3  namespace PhpOffice\PhpSpreadsheet\Calculation\LookupRef;
   4  
   5  use PhpOffice\PhpSpreadsheet\Calculation\ArrayEnabled;
   6  use PhpOffice\PhpSpreadsheet\Calculation\Exception;
   7  use PhpOffice\PhpSpreadsheet\Calculation\Functions;
   8  use PhpOffice\PhpSpreadsheet\Calculation\Information\ExcelError;
   9  use PhpOffice\PhpSpreadsheet\Calculation\Internal\WildcardMatch;
  10  use PhpOffice\PhpSpreadsheet\Shared\StringHelper;
  11  
  12  class ExcelMatch
  13  {
  14      use ArrayEnabled;
  15  
  16      public const MATCHTYPE_SMALLEST_VALUE = -1;
  17      public const MATCHTYPE_FIRST_VALUE = 0;
  18      public const MATCHTYPE_LARGEST_VALUE = 1;
  19  
  20      /**
  21       * MATCH.
  22       *
  23       * The MATCH function searches for a specified item in a range of cells
  24       *
  25       * Excel Function:
  26       *        =MATCH(lookup_value, lookup_array, [match_type])
  27       *
  28       * @param mixed $lookupValue The value that you want to match in lookup_array
  29       * @param mixed $lookupArray The range of cells being searched
  30       * @param mixed $matchType The number -1, 0, or 1. -1 means above, 0 means exact match, 1 means below.
  31       *                         If match_type is 1 or -1, the list has to be ordered.
  32       *
  33       * @return array|int|string The relative position of the found item
  34       */
  35      public static function MATCH($lookupValue, $lookupArray, $matchType = self::MATCHTYPE_LARGEST_VALUE)
  36      {
  37          if (is_array($lookupValue)) {
  38              return self::evaluateArrayArgumentsIgnore([self::class, __FUNCTION__], 1, $lookupValue, $lookupArray, $matchType);
  39          }
  40  
  41          $lookupArray = Functions::flattenArray($lookupArray);
  42          $matchType = (int) ($matchType ?? self::MATCHTYPE_LARGEST_VALUE);
  43  
  44          try {
  45              // Input validation
  46              self::validateLookupValue($lookupValue);
  47              self::validateMatchType($matchType);
  48              self::validateLookupArray($lookupArray);
  49  
  50              $keySet = array_keys($lookupArray);
  51              if ($matchType == self::MATCHTYPE_LARGEST_VALUE) {
  52                  // If match_type is 1 the list has to be processed from last to first
  53                  $lookupArray = array_reverse($lookupArray);
  54                  $keySet = array_reverse($keySet);
  55              }
  56  
  57              $lookupArray = self::prepareLookupArray($lookupArray, $matchType);
  58          } catch (Exception $e) {
  59              return $e->getMessage();
  60          }
  61  
  62          // MATCH() is not case sensitive, so we convert lookup value to be lower cased if it's a string type.
  63          if (is_string($lookupValue)) {
  64              $lookupValue = StringHelper::strToLower($lookupValue);
  65          }
  66  
  67          $valueKey = null;
  68          switch ($matchType) {
  69              case self::MATCHTYPE_LARGEST_VALUE:
  70                  $valueKey = self::matchLargestValue($lookupArray, $lookupValue, $keySet);
  71  
  72                  break;
  73              case self::MATCHTYPE_FIRST_VALUE:
  74                  $valueKey = self::matchFirstValue($lookupArray, $lookupValue);
  75  
  76                  break;
  77              case self::MATCHTYPE_SMALLEST_VALUE:
  78              default:
  79                  $valueKey = self::matchSmallestValue($lookupArray, $lookupValue);
  80          }
  81  
  82          if ($valueKey !== null) {
  83              return ++$valueKey;
  84          }
  85  
  86          // Unsuccessful in finding a match, return #N/A error value
  87          return ExcelError::NA();
  88      }
  89  
  90      private static function matchFirstValue($lookupArray, $lookupValue)
  91      {
  92          $wildcardLookup = ((bool) preg_match('/([\?\*])/', $lookupValue));
  93          $wildcard = WildcardMatch::wildcard($lookupValue);
  94  
  95          foreach ($lookupArray as $i => $lookupArrayValue) {
  96              $typeMatch = ((gettype($lookupValue) === gettype($lookupArrayValue)) ||
  97                  (is_numeric($lookupValue) && is_numeric($lookupArrayValue)));
  98  
  99              if (
 100                  $typeMatch && is_string($lookupValue) &&
 101                  $wildcardLookup && WildcardMatch::compare($lookupArrayValue, $wildcard)
 102              ) {
 103                  // wildcard match
 104                  return $i;
 105              } elseif ($lookupArrayValue === $lookupValue) {
 106                  // exact match
 107                  return $i;
 108              }
 109          }
 110  
 111          return null;
 112      }
 113  
 114      private static function matchLargestValue($lookupArray, $lookupValue, $keySet)
 115      {
 116          foreach ($lookupArray as $i => $lookupArrayValue) {
 117              $typeMatch = ((gettype($lookupValue) === gettype($lookupArrayValue)) ||
 118                  (is_numeric($lookupValue) && is_numeric($lookupArrayValue)));
 119  
 120              if ($typeMatch && ($lookupArrayValue <= $lookupValue)) {
 121                  return array_search($i, $keySet);
 122              }
 123          }
 124  
 125          return null;
 126      }
 127  
 128      private static function matchSmallestValue($lookupArray, $lookupValue)
 129      {
 130          $valueKey = null;
 131  
 132          // The basic algorithm is:
 133          // Iterate and keep the highest match until the next element is smaller than the searched value.
 134          // Return immediately if perfect match is found
 135          foreach ($lookupArray as $i => $lookupArrayValue) {
 136              $typeMatch = gettype($lookupValue) === gettype($lookupArrayValue);
 137  
 138              if ($lookupArrayValue === $lookupValue) {
 139                  // Another "special" case. If a perfect match is found,
 140                  // the algorithm gives up immediately
 141                  return $i;
 142              } elseif ($typeMatch && $lookupArrayValue >= $lookupValue) {
 143                  $valueKey = $i;
 144              } elseif ($typeMatch && $lookupArrayValue < $lookupValue) {
 145                  //Excel algorithm gives up immediately if the first element is smaller than the searched value
 146                  break;
 147              }
 148          }
 149  
 150          return $valueKey;
 151      }
 152  
 153      private static function validateLookupValue($lookupValue): void
 154      {
 155          // Lookup_value type has to be number, text, or logical values
 156          if ((!is_numeric($lookupValue)) && (!is_string($lookupValue)) && (!is_bool($lookupValue))) {
 157              throw new Exception(ExcelError::NA());
 158          }
 159      }
 160  
 161      private static function validateMatchType($matchType): void
 162      {
 163          // Match_type is 0, 1 or -1
 164          if (
 165              ($matchType !== self::MATCHTYPE_FIRST_VALUE) &&
 166              ($matchType !== self::MATCHTYPE_LARGEST_VALUE) && ($matchType !== self::MATCHTYPE_SMALLEST_VALUE)
 167          ) {
 168              throw new Exception(ExcelError::NA());
 169          }
 170      }
 171  
 172      private static function validateLookupArray($lookupArray): void
 173      {
 174          // Lookup_array should not be empty
 175          $lookupArraySize = count($lookupArray);
 176          if ($lookupArraySize <= 0) {
 177              throw new Exception(ExcelError::NA());
 178          }
 179      }
 180  
 181      private static function prepareLookupArray($lookupArray, $matchType)
 182      {
 183          // Lookup_array should contain only number, text, or logical values, or empty (null) cells
 184          foreach ($lookupArray as $i => $value) {
 185              //    check the type of the value
 186              if ((!is_numeric($value)) && (!is_string($value)) && (!is_bool($value)) && ($value !== null)) {
 187                  throw new Exception(ExcelError::NA());
 188              }
 189              // Convert strings to lowercase for case-insensitive testing
 190              if (is_string($value)) {
 191                  $lookupArray[$i] = StringHelper::strToLower($value);
 192              }
 193              if (
 194                  ($value === null) &&
 195                  (($matchType == self::MATCHTYPE_LARGEST_VALUE) || ($matchType == self::MATCHTYPE_SMALLEST_VALUE))
 196              ) {
 197                  unset($lookupArray[$i]);
 198              }
 199          }
 200  
 201          return $lookupArray;
 202      }
 203  }