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

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

281 lines 8.6 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 TablePress\PhpOffice\PhpSpreadsheet\Calculation\Internal\WildcardMatch;
10	use TablePress\PhpOffice\PhpSpreadsheet\Shared\StringHelper;
11
12	class ExcelMatch
13	{
14	use ArrayEnabled;
15
16	public const MATCHTYPE_SMALLEST_VALUE = -1;
17	public const MATCHTYPE_FIRST_VALUE = 0;
18	public const MATCHTYPE_LARGEST_VALUE = 1;
19
20	/**
21	* MATCH.
22	*
23	* The MATCH function searches for a specified item in a range of cells
24	*
25	* Excel Function:
26	* =MATCH(lookup_value, lookup_array, [match_type])
27	*
28	* @param mixed $lookupValue The value that you want to match in lookup_array
29	* @param mixed $lookupArray The range of cells being searched
30	* @param mixed $matchType The number -1, 0, or 1. -1 means above, 0 means exact match, 1 means below.
31	* If match_type is 1 or -1, the list has to be ordered.
32	*
33	* @return array<mixed>\|float\|int\|string The relative position of the found item
34	*/
35	public static function MATCH($lookupValue, $lookupArray, $matchType = self::MATCHTYPE_LARGEST_VALUE)
36	{
37	if (is_array($lookupValue)) {
38	return self::evaluateArrayArgumentsIgnore([self::class, __FUNCTION__], 1, $lookupValue, $lookupArray, $matchType);
39	}
40
41	$lookupArray = Functions::flattenArray($lookupArray);
42
43	try {
44	// Input validation
45	self::validateLookupValue($lookupValue);
46	$matchType = self::validateMatchType($matchType);
47	self::validateLookupArray($lookupArray);
48
49	$keySet = array_keys($lookupArray);
50	if ($matchType == self::MATCHTYPE_LARGEST_VALUE) {
51	// If match_type is 1 the list has to be processed from last to first
52	$lookupArray = array_reverse($lookupArray);
53	$keySet = array_reverse($keySet);
54	}
55
56	$lookupArray = self::prepareLookupArray($lookupArray, $matchType);
57	} catch (Exception $e) {
58	return $e->getMessage();
59	}
60
61	// MATCH() is not case-sensitive, so we convert lookup value to be lower cased if it's a string type.
62	if (is_string($lookupValue)) {
63	$lookupValue = StringHelper::strToLower($lookupValue);
64	}
65
66	switch ($matchType) {
67	case self::MATCHTYPE_LARGEST_VALUE:
68	$valueKey = self::matchLargestValue($lookupArray, $lookupValue, $keySet);
69	break;
70	case self::MATCHTYPE_FIRST_VALUE:
71	$valueKey = self::matchFirstValue($lookupArray, $lookupValue);
72	break;
73	default:
74	$valueKey = self::matchSmallestValue($lookupArray, $lookupValue);
75	break;
76	}
77
78	if ($valueKey !== null) {
79	return ++$valueKey; //* @phpstan-ignore preInc.type ($valueKey can be mixed not numeric)
80	}
81
82	// Unsuccessful in finding a match, return #N/A error value
83	return ExcelError::NA();
84	}
85
86	/** @param mixed[] $lookupArray
87	* @return int\|string\|null
88	* @param mixed $lookupValue */
89	private static function matchFirstValue(array $lookupArray, $lookupValue)
90	{
91	if (is_string($lookupValue)) {
92	$valueIsString = true;
93	$wildcard = WildcardMatch::wildcard($lookupValue);
94	} else {
95	$valueIsString = false;
96	$wildcard = '';
97	}
98
99	$valueIsNumeric = is_int($lookupValue) \|\| is_float($lookupValue);
100	foreach ($lookupArray as $i => $lookupArrayValue) {
101	if (
102	$valueIsString
103	&& is_string($lookupArrayValue)
104	) {
105	if (WildcardMatch::compare($lookupArrayValue, $wildcard)) {
106	return $i; // wildcard match
107	}
108	} else {
109	if ($lookupArrayValue === $lookupValue) {
110	return $i; // exact match
111	}
112	if (
113	$valueIsNumeric
114	&& (is_float($lookupArrayValue) \|\| is_int($lookupArrayValue))
115	&& $lookupArrayValue == $lookupValue
116	) {
117	return $i; // exact match
118	}
119	}
120	}
121
122	return null;
123	}
124
125	/**
126	* @param mixed[] $lookupArray
127	* @param mixed[] $keySet
128	* @param mixed $lookupValue
129	* @return mixed
130	*/
131	private static function matchLargestValue(array $lookupArray, $lookupValue, array $keySet)
132	{
133	if (is_string($lookupValue)) {
134	if (Functions::getCompatibilityMode() === Functions::COMPATIBILITY_OPENOFFICE) {
135	$wildcard = WildcardMatch::wildcard($lookupValue);
136	foreach (array_reverse($lookupArray) as $i => $lookupArrayValue) {
137	if (is_string($lookupArrayValue) && WildcardMatch::compare($lookupArrayValue, $wildcard)) {
138	return $i;
139	}
140	}
141	} else {
142	foreach ($lookupArray as $i => $lookupArrayValue) {
143	if ($lookupArrayValue === $lookupValue) {
144	return $keySet[$i];
145	}
146	}
147	}
148	}
149	$valueIsNumeric = is_int($lookupValue) \|\| is_float($lookupValue);
150	foreach ($lookupArray as $i => $lookupArrayValue) {
151	if ($valueIsNumeric && (is_int($lookupArrayValue) \|\| is_float($lookupArrayValue))) {
152	if ($lookupArrayValue <= $lookupValue) {
153	return array_search($i, $keySet);
154	}
155	}
156	$typeMatch = gettype($lookupValue) === gettype($lookupArrayValue);
157	if ($typeMatch && ($lookupArrayValue <= $lookupValue)) {
158	return array_search($i, $keySet);
159	}
160	}
161
162	return null;
163	}
164
165	/** @param mixed[] $lookupArray
166	* @return int\|string\|null
167	* @param mixed $lookupValue */
168	private static function matchSmallestValue(array $lookupArray, $lookupValue)
169	{
170	$valueKey = null;
171	if (is_string($lookupValue)) {
172	if (Functions::getCompatibilityMode() === Functions::COMPATIBILITY_OPENOFFICE) {
173	$wildcard = WildcardMatch::wildcard($lookupValue);
174	foreach ($lookupArray as $i => $lookupArrayValue) {
175	if (is_string($lookupArrayValue) && WildcardMatch::compare($lookupArrayValue, $wildcard)) {
176	return $i;
177	}
178	}
179	}
180	}
181
182	$valueIsNumeric = is_int($lookupValue) \|\| is_float($lookupValue);
183	// The basic algorithm is:
184	// Iterate and keep the highest match until the next element is smaller than the searched value.
185	// Return immediately if perfect match is found
186	foreach ($lookupArray as $i => $lookupArrayValue) {
187	$typeMatch = gettype($lookupValue) === gettype($lookupArrayValue);
188	$bothNumeric = $valueIsNumeric && (is_int($lookupArrayValue) \|\| is_float($lookupArrayValue));
189
190	if ($lookupArrayValue === $lookupValue) {
191	// Another "special" case. If a perfect match is found,
192	// the algorithm gives up immediately
193	return $i;
194	}
195	if ($bothNumeric && $lookupValue == $lookupArrayValue) {
196	return $i; // exact match, as above
197	}
198	if (($typeMatch \|\| $bothNumeric) && $lookupArrayValue >= $lookupValue) {
199	$valueKey = $i;
200	} elseif ($typeMatch && $lookupArrayValue < $lookupValue) {
201	//Excel algorithm gives up immediately if the first element is smaller than the searched value
202	break;
203	}
204	}
205
206	return $valueKey;
207	}
208
209	/**
210	* @param mixed $lookupValue
211	*/
212	private static function validateLookupValue($lookupValue): void
213	{
214	// Lookup_value type has to be number, text, or logical values
215	if ((!is_numeric($lookupValue)) && (!is_string($lookupValue)) && (!is_bool($lookupValue))) {
216	throw new Exception(ExcelError::NA());
217	}
218	}
219
220	/**
221	* @param mixed $matchType
222	*/
223	private static function validateMatchType($matchType): int
224	{
225	// Match_type is 0, 1 or -1
226	// However Excel accepts other numeric values,
227	// including numeric strings and floats.
228	// It seems to just be interested in the sign.
229	if (!is_numeric($matchType)) {
230	throw new Exception(ExcelError::Value());
231	}
232	if ($matchType > 0) {
233	return self::MATCHTYPE_LARGEST_VALUE;
234	}
235	if ($matchType < 0) {
236	return self::MATCHTYPE_SMALLEST_VALUE;
237	}
238
239	return self::MATCHTYPE_FIRST_VALUE;
240	}
241
242	/** @param mixed[] $lookupArray */
243	private static function validateLookupArray(array $lookupArray): void
244	{
245	// Lookup_array should not be empty
246	$lookupArraySize = count($lookupArray);
247	if ($lookupArraySize <= 0) {
248	throw new Exception(ExcelError::NA());
249	}
250	}
251
252	/**
253	* @param mixed[] $lookupArray
254	*
255	* @return mixed[]
256	* @param mixed $matchType
257	*/
258	private static function prepareLookupArray(array $lookupArray, $matchType): array
259	{
260	// Lookup_array should contain only number, text, or logical values, or empty (null) cells
261	foreach ($lookupArray as $i => $value) {
262	// check the type of the value
263	if ((!is_numeric($value)) && (!is_string($value)) && (!is_bool($value)) && ($value !== null)) {
264	throw new Exception(ExcelError::NA());
265	}
266	// Convert strings to lowercase for case-insensitive testing
267	if (is_string($value)) {
268	$lookupArray[$i] = StringHelper::strToLower($value);
269	}
270	if (
271	($value === null)
272	&& (($matchType == self::MATCHTYPE_LARGEST_VALUE) \|\| ($matchType == self::MATCHTYPE_SMALLEST_VALUE))
273	) {
274	unset($lookupArray[$i]);
275	}
276	}
277
278	return $lookupArray;
279	}
280	}
281