[Summary view]

Differences Between: [Versions 400 and 403] [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  
  43          try {
  44              // Input validation
  45              self::validateLookupValue($lookupValue);
  46              $matchType = self::validateMatchType($matchType);
  47              self::validateLookupArray($lookupArray);
  48  
  49              $keySet = array_keys($lookupArray);
  50              if ($matchType == self::MATCHTYPE_LARGEST_VALUE) {
  51                  // If match_type is 1 the list has to be processed from last to first
  52                  $lookupArray = array_reverse($lookupArray);
  53                  $keySet = array_reverse($keySet);
  54              }
  55  
  56              $lookupArray = self::prepareLookupArray($lookupArray, $matchType);
  57          } catch (Exception $e) {
  58              return $e->getMessage();
  59          }
  60  
  61          // MATCH() is not case sensitive, so we convert lookup value to be lower cased if it's a string type.
  62          if (is_string($lookupValue)) {
  63              $lookupValue = StringHelper::strToLower($lookupValue);
  64          }
  65  
  66          $valueKey = null;
  67          switch ($matchType) {
  68              case self::MATCHTYPE_LARGEST_VALUE:
  69                  $valueKey = self::matchLargestValue($lookupArray, $lookupValue, $keySet);
  70  
  71                  break;
  72              case self::MATCHTYPE_FIRST_VALUE:
  73                  $valueKey = self::matchFirstValue($lookupArray, $lookupValue);
  74  
  75                  break;
  76              case self::MATCHTYPE_SMALLEST_VALUE:
  77              default:
  78                  $valueKey = self::matchSmallestValue($lookupArray, $lookupValue);
  79          }
  80  
  81          if ($valueKey !== null) {
  82              return ++$valueKey;
  83          }
  84  
  85          // Unsuccessful in finding a match, return #N/A error value
  86          return ExcelError::NA();
  87      }
  88  
  89      /**
  90       * @param mixed $lookupValue
  91       *
  92       * @return mixed
  93       */
  94      private static function matchFirstValue(array $lookupArray, $lookupValue)
  95      {
  96          if (is_string($lookupValue)) {
  97              $valueIsString = true;
  98              $wildcard = WildcardMatch::wildcard($lookupValue);
  99          } else {
 100              $valueIsString = false;
 101              $wildcard = '';
 102          }
 103  
 104          $valueIsNumeric = is_int($lookupValue) || is_float($lookupValue);
 105          foreach ($lookupArray as $i => $lookupArrayValue) {
 106              if (
 107                  $valueIsString
 108                  && is_string($lookupArrayValue)
 109              ) {
 110                  if (WildcardMatch::compare($lookupArrayValue, $wildcard)) {
 111                      return $i; // wildcard match
 112                  }
 113              } else {
 114                  if ($lookupArrayValue === $lookupValue) {
 115                      return $i; // exact match
 116                  }
 117                  if (
 118                      $valueIsNumeric
 119                      && (is_float($lookupArrayValue) || is_int($lookupArrayValue))
 120                      && $lookupArrayValue == $lookupValue
 121                  ) {
 122                      return $i; // exact match
 123                  }
 124              }
 125          }
 126  
 127          return null;
 128      }
 129  
 130      /**
 131       * @param mixed $lookupValue
 132       *
 133       * @return mixed
 134       */
 135      private static function matchLargestValue(array $lookupArray, $lookupValue, array $keySet)
 136      {
 137          if (is_string($lookupValue)) {
 138              if (Functions::getCompatibilityMode() === Functions::COMPATIBILITY_OPENOFFICE) {
 139                  $wildcard = WildcardMatch::wildcard($lookupValue);
 140                  foreach (array_reverse($lookupArray) as $i => $lookupArrayValue) {
 141                      if (is_string($lookupArrayValue) && WildcardMatch::compare($lookupArrayValue, $wildcard)) {
 142                          return $i;
 143                      }
 144                  }
 145              } else {
 146                  foreach ($lookupArray as $i => $lookupArrayValue) {
 147                      if ($lookupArrayValue === $lookupValue) {
 148                          return $keySet[$i];
 149                      }
 150                  }
 151              }
 152          }
 153          $valueIsNumeric = is_int($lookupValue) || is_float($lookupValue);
 154          foreach ($lookupArray as $i => $lookupArrayValue) {
 155              if ($valueIsNumeric && (is_int($lookupArrayValue) || is_float($lookupArrayValue))) {
 156                  if ($lookupArrayValue <= $lookupValue) {
 157                      return array_search($i, $keySet);
 158                  }
 159              }
 160              $typeMatch = gettype($lookupValue) === gettype($lookupArrayValue);
 161              if ($typeMatch && ($lookupArrayValue <= $lookupValue)) {
 162                  return array_search($i, $keySet);
 163              }
 164          }
 165  
 166          return null;
 167      }
 168  
 169      /**
 170       * @param mixed $lookupValue
 171       *
 172       * @return mixed
 173       */
 174      private static function matchSmallestValue(array $lookupArray, $lookupValue)
 175      {
 176          $valueKey = null;
 177          if (is_string($lookupValue)) {
 178              if (Functions::getCompatibilityMode() === Functions::COMPATIBILITY_OPENOFFICE) {
 179                  $wildcard = WildcardMatch::wildcard($lookupValue);
 180                  foreach ($lookupArray as $i => $lookupArrayValue) {
 181                      if (is_string($lookupArrayValue) && WildcardMatch::compare($lookupArrayValue, $wildcard)) {
 182                          return $i;
 183                      }
 184                  }
 185              }
 186          }
 187  
 188          $valueIsNumeric = is_int($lookupValue) || is_float($lookupValue);
 189          // The basic algorithm is:
 190          // Iterate and keep the highest match until the next element is smaller than the searched value.
 191          // Return immediately if perfect match is found
 192          foreach ($lookupArray as $i => $lookupArrayValue) {
 193              $typeMatch = gettype($lookupValue) === gettype($lookupArrayValue);
 194              $bothNumeric = $valueIsNumeric && (is_int($lookupArrayValue) || is_float($lookupArrayValue));
 195  
 196              if ($lookupArrayValue === $lookupValue) {
 197                  // Another "special" case. If a perfect match is found,
 198                  // the algorithm gives up immediately
 199                  return $i;
 200              }
 201              if ($bothNumeric && $lookupValue == $lookupArrayValue) {
 202                  return $i; // exact match, as above
 203              }
 204              if (($typeMatch || $bothNumeric) && $lookupArrayValue >= $lookupValue) {
 205                  $valueKey = $i;
 206              } elseif ($typeMatch && $lookupArrayValue < $lookupValue) {
 207                  //Excel algorithm gives up immediately if the first element is smaller than the searched value
 208                  break;
 209              }
 210          }
 211  
 212          return $valueKey;
 213      }
 214  
 215      /**
 216       * @param mixed $lookupValue
 217       */
 218      private static function validateLookupValue($lookupValue): void
 219      {
 220          // Lookup_value type has to be number, text, or logical values
 221          if ((!is_numeric($lookupValue)) && (!is_string($lookupValue)) && (!is_bool($lookupValue))) {
 222              throw new Exception(ExcelError::NA());
 223          }
 224      }
 225  
 226      /**
 227       * @param mixed $matchType
 228       */
 229      private static function validateMatchType($matchType): int
 230      {
 231          // Match_type is 0, 1 or -1
 232          // However Excel accepts other numeric values,
 233          //  including numeric strings and floats.
 234          //  It seems to just be interested in the sign.
 235          if (!is_numeric($matchType)) {
 236              throw new Exception(ExcelError::Value());
 237          }
 238          if ($matchType > 0) {
 239              return self::MATCHTYPE_LARGEST_VALUE;
 240          }
 241          if ($matchType < 0) {
 242              return self::MATCHTYPE_SMALLEST_VALUE;
 243          }
 244  
 245          return self::MATCHTYPE_FIRST_VALUE;
 246      }
 247  
 248      private static function validateLookupArray(array $lookupArray): void
 249      {
 250          // Lookup_array should not be empty
 251          $lookupArraySize = count($lookupArray);
 252          if ($lookupArraySize <= 0) {
 253              throw new Exception(ExcelError::NA());
 254          }
 255      }
 256  
 257      /**
 258       * @param mixed $matchType
 259       */
 260      private static function prepareLookupArray(array $lookupArray, $matchType): array
 261      {
 262          // Lookup_array should contain only number, text, or logical values, or empty (null) cells
 263          foreach ($lookupArray as $i => $value) {
 264              //    check the type of the value
 265              if ((!is_numeric($value)) && (!is_string($value)) && (!is_bool($value)) && ($value !== null)) {
 266                  throw new Exception(ExcelError::NA());
 267              }
 268              // Convert strings to lowercase for case-insensitive testing
 269              if (is_string($value)) {
 270                  $lookupArray[$i] = StringHelper::strToLower($value);
 271              }
 272              if (
 273                  ($value === null) &&
 274                  (($matchType == self::MATCHTYPE_LARGEST_VALUE) || ($matchType == self::MATCHTYPE_SMALLEST_VALUE))
 275              ) {
 276                  unset($lookupArray[$i]);
 277              }
 278          }
 279  
 280          return $lookupArray;
 281      }
 282  }