tablepress / libraries / vendor / PhpSpreadsheet / Calculation / LookupRef / XLookup.php

XLookup.php in TablePress – Tables in WordPress made easy 3.4, at libraries/vendor/PhpSpreadsheet/Calculation/LookupRef/XLookup.php

293 lines 7.9 KB

Raw Download Zip

1	<?php
2
3	namespace TablePress\PhpOffice\PhpSpreadsheet\Calculation\LookupRef;
4
5	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\ArrayEnabled;
6	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\Exception;
7	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\Functions;
8	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\Information\ExcelError;
9	use UnhandledMatchError;
10
11	class XLookup extends LookupBase
12	{
13	//use ArrayEnabled; // not yet supported
14	/**
15	* XLOOKUP — PHP emulation of Excel's XLOOKUP function.
16	*
17	* @param mixed $lookupValue Value to search for
18	* @param mixed $lookupArray Expect array, Array to search in
19	* @param mixed $returnArray Expect array, Array to return from (must match lookupArray size)
20	* @param mixed $ifNotFound Value to return if no match found (default: #N/A!)
21	* @param mixed $matchMode expect int 0 = exact match (default)
22	* -1 = exact or next smaller
23	* 1 = exact or next larger
24	* 2 = wildcard match (* ? ~)
25	* @param mixed $searchMode expect int 1 = first to last (default)
26	* -1 = last to first
27	* 2 = binary search ascending
28	* -2 = binary search descending
29	* @return mixed
30	*/
31	public static function lookup(
32	$lookupValue,
33	$lookupArray,
34	$returnArray,
35	$ifNotFound = '#N/A!',
36	$matchMode = 0,
37	$searchMode = 1
38	) {
39	if (is_array($lookupValue)) {
40	$lookupValue = Functions::flattenArray($lookupValue);
41	if (count($lookupValue) === 1) {
42	$lookupValue = reset($lookupValue);
43	}
44	}
45	if (is_array($lookupValue)) {
46	$result = [];
47	foreach ($lookupValue as $value) {
48	$result[] = self::lookup($value, $lookupArray, $returnArray, $ifNotFound, $matchMode, $searchMode);
49	}
50
51	return $result;
52	}
53	if (!is_array($lookupArray)) {
54	$lookupArray = [$lookupArray];
55	}
56	if (!is_array($returnArray)) {
57	$returnArray = [$returnArray];
58	}
59	$lookupArray = Functions::flattenArray($lookupArray);
60	if (count($returnArray) === 1) {
61	$returnArray = Functions::flattenArray($returnArray);
62	} else {
63	$oldArray = $returnArray;
64	$returnArray = [];
65	foreach ($oldArray as $row) {
66	$newRow = Functions::flattenArray($row);
67	if (count($newRow) === 1) {
68	$newRow = reset($newRow);
69	}
70	$returnArray[] = $newRow;
71	}
72	}
73	/*if (is_array($lookupValue)) { // not yet supported by ArrayEnabled
74	return self::evaluateArrayArgumentsIgnore([self::class, __FUNCTION__], 1, $lookupValue, $lookupArray, $returnArray, $ifNotFound, $matchMode, $searchMode);
75	}*/
76
77	try {
78	self::validateLookupArray($lookupArray);
79	self::validateLookupArray($returnArray);
80	$matchMode = LookupRefValidations::validateInt($matchMode);
81	$searchMode = LookupRefValidations::validateInt($searchMode);
82	} catch (Exception $e) {
83	return $e->getMessage();
84	}
85
86	if (!in_array($matchMode, [0, -1, 1, 2], true)) {
87	return ExcelError::VALUE();
88	}
89
90	if (count($lookupArray) !== count($returnArray)) {
91	return ExcelError::VALUE();
92	}
93
94	try {
95	switch ($searchMode) {
96	case 1:
97	$index = self::searchLinear($lookupValue, $lookupArray, $matchMode, false);
98	break;
99	case -1:
100	$index = self::searchLinear($lookupValue, $lookupArray, $matchMode, true);
101	break;
102	case 2:
103	$index = self::searchBinary($lookupValue, $lookupArray, $matchMode, true);
104	break;
105	case -2:
106	$index = self::searchBinary($lookupValue, $lookupArray, $matchMode, false);
107	break;
108	}
109	} catch (UnhandledMatchError $exception) {
110	return ExcelError::VALUE();
111	}
112
113	return ($index === null) ? $ifNotFound : $returnArray[$index];
114	}
115
116	// ---------------------------------------------------------------------------
117	// Search strategies
118	// ---------------------------------------------------------------------------
119	/**
120	* Linear search (searchMode 1 and -1).
121	*
122	* @param mixed[] $lookupArray
123	* @param mixed $lookupValue
124	*/
125	private static function searchLinear(
126	$lookupValue,
127	array $lookupArray,
128	int $matchMode,
129	bool $reverse
130	): ?int {
131
132	$keys = array_keys($lookupArray);
133	if ($reverse) {
134	$keys = array_reverse($keys);
135	}
136
137	$bestIdx = null;
138	$bestVal = null;
139
140	foreach ($keys as $i) {
141	/** @var scalar */
142	$candidate = $lookupArray[$i];
143
144	if ($matchMode === 2) {
145	// Wildcard: convert Excel wildcards to PHP regex
146	/** @var scalar $lookupValue */
147	if (self::wildcardMatch((string) $lookupValue, (string) $candidate)) {
148	return $i;
149	}
150
151	continue;
152	}
153
154	$cmp = self::compareValues($candidate, $lookupValue);
155
156	if ($cmp === 0) {
157	return $i; // Exact match — return immediately
158	}
159
160	if ($matchMode === -1 && $cmp < 0) {
161	// Next smaller: track largest value still below lookupValue
162	if ($bestVal === null \|\| self::compareValues($candidate, $bestVal) > 0) {
163	$bestVal = $candidate;
164	$bestIdx = $i;
165	}
166	}
167
168	if ($matchMode === 1 && $cmp > 0) {
169	// Next larger: track smallest value still above lookupValue
170	if ($bestVal === null \|\| self::compareValues($candidate, $bestVal) < 0) {
171	$bestVal = $candidate;
172	$bestIdx = $i;
173	}
174	}
175	}
176	/** @var ?int $bestIdx */
177
178	return $bestIdx;
179	}
180
181	/**
182	* Binary search (searchMode 2 and -2)
183	* Assumes array is sorted ascending (searchMode 2) or descending (searchMode -2).
184	*
185	* @param mixed[] $lookupArray
186	* @param mixed $lookupValue
187	*/
188	private static function searchBinary(
189	$lookupValue,
190	array $lookupArray,
191	int $matchMode,
192	bool $ascending
193	): ?int {
194
195	$values = array_values($lookupArray);
196	$keys = array_keys($lookupArray);
197	$lo = 0;
198	$hi = count($values) - 1;
199	$bestIdx = null;
200
201	while ($lo <= $hi) {
202	$mid = intdiv($lo + $hi, 2);
203	$cmp = self::compareValues($values[$mid], $lookupValue);
204
205	if (!$ascending) {
206	$cmp = -$cmp; // Flip for descending
207	}
208
209	if ($cmp === 0) {
210	return $keys[$mid]; // Exact match
211	}
212
213	if ($cmp < 0) {
214	if ($matchMode === -1) {
215	$bestIdx = $keys[$mid]; // Candidate for next smaller
216	}
217	$lo = $mid + 1;
218	} else {
219	if ($matchMode === 1) {
220	$bestIdx = $keys[$mid]; // Candidate for next larger
221	}
222	$hi = $mid - 1;
223	}
224	}
225	/** @var int $bestIdx */
226
227	return ($matchMode !== 0) ? $bestIdx : null;
228	}
229
230	/**
231	* Compare two values with type coercion matching Excel's behaviour:
232	* numbers < strings < booleans
233	* @param mixed $a
234	* @param mixed $b
235	*/
236	private static function compareValues($a, $b): int
237	{
238	// Numeric comparison
239	if (is_numeric($a) && is_numeric($b)) {
240	return $a <=> $b;
241	}
242	// String comparison (case-insensitive, like Excel)
243	if (is_string($a) && is_string($b)) {
244	return strcasecmp($a, $b);
245	}
246	// Bool comparison
247	if (is_bool($a) && is_bool($b)) {
248	return $a <=> $b;
249	}
250	// Cross-type: number < string < bool
251	$typeOrder = function ($v) {
252	switch (true) {
253	case is_numeric($v):
254	return 0;
255	case is_string($v):
256	return 1;
257	case is_bool($v):
258	return 2;
259	default:
260	return 3;
261	}
262	};
263
264	return $typeOrder($a) <=> $typeOrder($b);
265	}
266
267	/**
268	* Wildcard match (matchMode 2)
269	* Supports Excel wildcards: * (any sequence), ? (any single char), ~ (escape).
270	*/
271	private static function wildcardMatch(string $pattern, string $subject): bool
272	{
273	// Handle ~* and ~? escapes first
274	$regex = '';
275	$len = strlen($pattern);
276	for ($i = 0; $i < $len; ++$i) {
277	$ch = $pattern[$i];
278	if ($ch === '~' && $i + 1 < $len) {
279	$next = $pattern[++$i];
280	$regex .= preg_quote($next, '/');
281	} elseif ($ch === '*') {
282	$regex .= '.*';
283	} elseif ($ch === '?') {
284	$regex .= '.';
285	} else {
286	$regex .= preg_quote($ch, '/');
287	}
288	}
289
290	return (bool) preg_match('/^' . $regex . '$/i', $subject);
291	}
292	}
293