[Summary view]

Differences Between: [Versions 400 and 401] [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\Information\ExcelError;
   8  use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
   9  use PhpOffice\PhpSpreadsheet\Shared\StringHelper;
  10  
  11  class HLookup extends LookupBase
  12  {
  13      use ArrayEnabled;
  14  
  15      /**
  16       * HLOOKUP
  17       * The HLOOKUP function searches for value in the top-most row of lookup_array and returns the value
  18       *     in the same column based on the index_number.
  19       *
  20       * @param mixed $lookupValue The value that you want to match in lookup_array
  21       * @param mixed $lookupArray The range of cells being searched
  22       * @param mixed $indexNumber The row number in table_array from which the matching value must be returned.
  23       *                                The first row is 1.
  24       * @param mixed $notExactMatch determines if you are looking for an exact match based on lookup_value
  25       *
  26       * @return mixed The value of the found cell
  27       */
  28      public static function lookup($lookupValue, $lookupArray, $indexNumber, $notExactMatch = true)
  29      {
  30          if (is_array($lookupValue)) {
  31              return self::evaluateArrayArgumentsIgnore([self::class, __FUNCTION__], 1, $lookupValue, $lookupArray, $indexNumber, $notExactMatch);
  32          }
  33  
  34          $notExactMatch = (bool) ($notExactMatch ?? true);
  35  
  36          try {
  37              self::validateLookupArray($lookupArray);
  38              $lookupArray = self::convertLiteralArray($lookupArray);
  39              $indexNumber = self::validateIndexLookup($lookupArray, $indexNumber);
  40          } catch (Exception $e) {
  41              return $e->getMessage();
  42          }
  43  
  44          $f = array_keys($lookupArray);
  45          $firstRow = reset($f);
  46          if ((!is_array($lookupArray[$firstRow])) || ($indexNumber > count($lookupArray))) {
  47              return ExcelError::REF();
  48          }
  49  
  50          $firstkey = $f[0] - 1;
  51          $returnColumn = $firstkey + $indexNumber;
  52          $firstColumn = array_shift($f) ?? 1;
  53          $rowNumber = self::hLookupSearch($lookupValue, $lookupArray, $firstColumn, $notExactMatch);
  54  
  55          if ($rowNumber !== null) {
  56              //  otherwise return the appropriate value
  57              return $lookupArray[$returnColumn][Coordinate::stringFromColumnIndex($rowNumber)];
  58          }
  59  
  60          return ExcelError::NA();
  61      }
  62  
  63      /**
  64       * @param mixed $lookupValue The value that you want to match in lookup_array
  65       * @param  int|string $column
  66       */
  67      private static function hLookupSearch($lookupValue, array $lookupArray, $column, bool $notExactMatch): ?int
  68      {
  69          $lookupLower = StringHelper::strToLower((string) $lookupValue);
  70  
  71          $rowNumber = null;
  72          foreach ($lookupArray[$column] as $rowKey => $rowData) {
  73              // break if we have passed possible keys
  74              $bothNumeric = is_numeric($lookupValue) && is_numeric($rowData);
  75              $bothNotNumeric = !is_numeric($lookupValue) && !is_numeric($rowData);
  76              $cellDataLower = StringHelper::strToLower((string) $rowData);
  77  
  78              if (
  79                  $notExactMatch &&
  80                  (($bothNumeric && $rowData > $lookupValue) || ($bothNotNumeric && $cellDataLower > $lookupLower))
  81              ) {
  82                  break;
  83              }
  84  
  85              $rowNumber = self::checkMatch(
  86                  $bothNumeric,
  87                  $bothNotNumeric,
  88                  $notExactMatch,
  89                  Coordinate::columnIndexFromString($rowKey),
  90                  $cellDataLower,
  91                  $lookupLower,
  92                  $rowNumber
  93              );
  94          }
  95  
  96          return $rowNumber;
  97      }
  98  
  99      private static function convertLiteralArray(array $lookupArray): array
 100      {
 101          if (array_key_exists(0, $lookupArray)) {
 102              $lookupArray2 = [];
 103              $row = 0;
 104              foreach ($lookupArray as $arrayVal) {
 105                  ++$row;
 106                  if (!is_array($arrayVal)) {
 107                      $arrayVal = [$arrayVal];
 108                  }
 109                  $arrayVal2 = [];
 110                  foreach ($arrayVal as $key2 => $val2) {
 111                      $index = Coordinate::stringFromColumnIndex($key2 + 1);
 112                      $arrayVal2[$index] = $val2;
 113                  }
 114                  $lookupArray2[$row] = $arrayVal2;
 115              }
 116              $lookupArray = $lookupArray2;
 117          }
 118  
 119          return $lookupArray;
 120      }
 121  }