[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\Exception;
   6  use PhpOffice\PhpSpreadsheet\Calculation\Information\ExcelError;
   7  
   8  abstract class LookupBase
   9  {
  10      /**
  11       * @param mixed $lookup_array
  12       */
  13      protected static function validateLookupArray($lookup_array): void
  14      {
  15          if (!is_array($lookup_array)) {
  16              throw new Exception(ExcelError::REF());
  17          }
  18      }
  19  
  20      protected static function validateIndexLookup(array $lookup_array, $index_number): int
  21      {
  22          // index_number must be a number greater than or equal to 1.
  23          // Excel results are inconsistent when index is non-numeric.
  24          // VLOOKUP(whatever, whatever, SQRT(-1)) yields NUM error, but
  25          // VLOOKUP(whatever, whatever, cellref) yields REF error
  26          //   when cellref is '=SQRT(-1)'. So just try our best here.
  27          // Similar results if string (literal yields VALUE, cellRef REF).
  28          if (!is_numeric($index_number)) {
  29              throw new Exception(ExcelError::throwError($index_number));
  30          }
  31          if ($index_number < 1) {
  32              throw new Exception(ExcelError::VALUE());
  33          }
  34  
  35          // index_number must be less than or equal to the number of columns in lookup_array
  36          if ((!is_array($lookup_array)) || (empty($lookup_array))) {
  37              throw new Exception(ExcelError::REF());
  38          }
  39  
  40          return (int) $index_number;
  41      }
  42  
  43      protected static function checkMatch(
  44          bool $bothNumeric,
  45          bool $bothNotNumeric,
  46          bool $notExactMatch,
  47          int $rowKey,
  48          string $cellDataLower,
  49          string $lookupLower,
  50          ?int $rowNumber
  51      ): ?int {
  52          // remember the last key, but only if datatypes match
  53          if ($bothNumeric || $bothNotNumeric) {
  54              // Spreadsheets software returns first exact match,
  55              // we have sorted and we might have broken key orders
  56              // we want the first one (by its initial index)
  57              if ($notExactMatch) {
  58                  $rowNumber = $rowKey;
  59              } elseif (($cellDataLower == $lookupLower) && (($rowNumber === null) || ($rowKey < $rowNumber))) {
  60                  $rowNumber = $rowKey;
  61              }
  62          }
  63  
  64          return $rowNumber;
  65      }
  66  }