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

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

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