|
1
|
|
|
<?php |
|
2
|
|
|
|
|
3
|
|
|
namespace PhpOffice\PhpSpreadsheet\Calculation\LookupRef; |
|
4
|
|
|
|
|
5
|
|
|
use PhpOffice\PhpSpreadsheet\Calculation\ArrayEnabled; |
|
6
|
|
|
use PhpOffice\PhpSpreadsheet\Calculation\Information\ExcelError; |
|
7
|
|
|
use PhpOffice\PhpSpreadsheet\Calculation\LookupRef; |
|
8
|
|
|
|
|
9
|
|
|
class Lookup |
|
10
|
|
|
{ |
|
11
|
|
|
use ArrayEnabled; |
|
12
|
|
|
|
|
13
|
|
|
/** |
|
14
|
|
|
* LOOKUP |
|
15
|
|
|
* The LOOKUP function searches for value either from a one-row or one-column range or from an array. |
|
16
|
|
|
* |
|
17
|
|
|
* @param mixed $lookupValue The value that you want to match in lookup_array |
|
18
|
|
|
* @param mixed $lookupVector The range of cells being searched |
|
19
|
|
|
* @param null|mixed $resultVector The column from which the matching value must be returned |
|
20
|
|
|
* |
|
21
|
|
|
* @return mixed The value of the found cell |
|
22
|
|
|
*/ |
|
23
|
12 |
|
public static function lookup($lookupValue, $lookupVector, $resultVector = null) |
|
24
|
|
|
{ |
|
25
|
12 |
|
if (is_array($lookupValue)) { |
|
26
|
1 |
|
return self::evaluateArrayArgumentsSubset([self::class, __FUNCTION__], 1, $lookupValue, $lookupVector, $resultVector); |
|
27
|
|
|
} |
|
28
|
|
|
|
|
29
|
12 |
|
if (!is_array($lookupVector)) { |
|
30
|
1 |
|
return ExcelError::NA(); |
|
31
|
|
|
} |
|
32
|
11 |
|
$hasResultVector = isset($resultVector); |
|
33
|
11 |
|
$lookupRows = self::rowCount($lookupVector); |
|
34
|
11 |
|
$lookupColumns = self::columnCount($lookupVector); |
|
35
|
|
|
// we correctly orient our results |
|
36
|
11 |
|
if (($lookupRows === 1 && $lookupColumns > 1) || (!$hasResultVector && $lookupRows === 2 && $lookupColumns !== 2)) { |
|
37
|
2 |
|
$lookupVector = LookupRef\Matrix::transpose($lookupVector); |
|
38
|
2 |
|
$lookupRows = self::rowCount($lookupVector); |
|
39
|
2 |
|
$lookupColumns = self::columnCount($lookupVector); |
|
40
|
|
|
} |
|
41
|
|
|
|
|
42
|
11 |
|
$resultVector = self::verifyResultVector($resultVector ?? $lookupVector); |
|
43
|
|
|
|
|
44
|
11 |
|
if ($lookupRows === 2 && !$hasResultVector) { |
|
45
|
|
|
$resultVector = array_pop($lookupVector); |
|
46
|
|
|
$lookupVector = array_shift($lookupVector); |
|
47
|
|
|
} |
|
48
|
|
|
|
|
49
|
11 |
|
if ($lookupColumns !== 2) { |
|
50
|
8 |
|
$lookupVector = self::verifyLookupValues($lookupVector, $resultVector); |
|
51
|
|
|
} |
|
52
|
|
|
|
|
53
|
11 |
|
return VLookup::lookup($lookupValue, $lookupVector, 2); |
|
54
|
|
|
} |
|
55
|
|
|
|
|
56
|
8 |
|
private static function verifyLookupValues(array $lookupVector, array $resultVector): array |
|
57
|
|
|
{ |
|
58
|
8 |
|
foreach ($lookupVector as &$value) { |
|
59
|
8 |
|
if (is_array($value)) { |
|
60
|
8 |
|
$k = array_keys($value); |
|
61
|
8 |
|
$key1 = $key2 = array_shift($k); |
|
62
|
8 |
|
++$key2; |
|
63
|
8 |
|
$dataValue1 = $value[$key1]; |
|
64
|
|
|
} else { |
|
65
|
|
|
$key1 = 0; |
|
66
|
|
|
$key2 = 1; |
|
67
|
|
|
$dataValue1 = $value; |
|
68
|
|
|
} |
|
69
|
|
|
|
|
70
|
8 |
|
$dataValue2 = array_shift($resultVector); |
|
71
|
8 |
|
if (is_array($dataValue2)) { |
|
72
|
8 |
|
$dataValue2 = array_shift($dataValue2); |
|
73
|
|
|
} |
|
74
|
8 |
|
$value = [$key1 => $dataValue1, $key2 => $dataValue2]; |
|
75
|
|
|
} |
|
76
|
8 |
|
unset($value); |
|
77
|
|
|
|
|
78
|
8 |
|
return $lookupVector; |
|
79
|
|
|
} |
|
80
|
|
|
|
|
81
|
11 |
|
private static function verifyResultVector(array $resultVector): array |
|
82
|
|
|
{ |
|
83
|
11 |
|
$resultRows = self::rowCount($resultVector); |
|
84
|
11 |
|
$resultColumns = self::columnCount($resultVector); |
|
85
|
|
|
|
|
86
|
|
|
// we correctly orient our results |
|
87
|
11 |
|
if ($resultRows === 1 && $resultColumns > 1) { |
|
88
|
2 |
|
$resultVector = LookupRef\Matrix::transpose($resultVector); |
|
89
|
|
|
} |
|
90
|
|
|
|
|
91
|
11 |
|
return $resultVector; |
|
92
|
|
|
} |
|
93
|
|
|
|
|
94
|
11 |
|
private static function rowCount(array $dataArray): int |
|
95
|
|
|
{ |
|
96
|
11 |
|
return count($dataArray); |
|
97
|
|
|
} |
|
98
|
|
|
|
|
99
|
11 |
|
private static function columnCount(array $dataArray): int |
|
100
|
|
|
{ |
|
101
|
11 |
|
$rowKeys = array_keys($dataArray); |
|
102
|
11 |
|
$row = array_shift($rowKeys); |
|
103
|
|
|
|
|
104
|
11 |
|
return count($dataArray[$row]); |
|
105
|
|
|
} |
|
106
|
|
|
} |
|
107
|
|
|
|
PHPOffice /
PhpSpreadsheet
