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