1 | <?php |
||
2 | |||
3 | namespace PhpOffice\PhpSpreadsheet\Calculation; |
||
4 | |||
5 | use PhpOffice\PhpSpreadsheet\Cell\Cell; |
||
6 | use PhpOffice\PhpSpreadsheet\Cell\Coordinate; |
||
7 | use PhpOffice\PhpSpreadsheet\Shared\StringHelper; |
||
8 | use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet; |
||
9 | |||
10 | class LookupRef |
||
11 | { |
||
12 | /** |
||
13 | * CELL_ADDRESS. |
||
14 | * |
||
15 | * Creates a cell address as text, given specified row and column numbers. |
||
16 | * |
||
17 | * Excel Function: |
||
18 | * =ADDRESS(row, column, [relativity], [referenceStyle], [sheetText]) |
||
19 | * |
||
20 | * @param mixed $row Row number to use in the cell reference |
||
21 | * @param mixed $column Column number to use in the cell reference |
||
22 | * @param int $relativity Flag indicating the type of reference to return |
||
23 | * 1 or omitted Absolute |
||
24 | * 2 Absolute row; relative column |
||
25 | * 3 Relative row; absolute column |
||
26 | * 4 Relative |
||
27 | * @param bool $referenceStyle A logical value that specifies the A1 or R1C1 reference style. |
||
28 | * TRUE or omitted CELL_ADDRESS returns an A1-style reference |
||
29 | * FALSE CELL_ADDRESS returns an R1C1-style reference |
||
30 | * @param string $sheetText Optional Name of worksheet to use |
||
31 | * |
||
32 | * @return string |
||
33 | */ |
||
34 | public static function cellAddress($row, $column, $relativity = 1, $referenceStyle = true, $sheetText = '') |
||
35 | { |
||
36 | $row = Functions::flattenSingleValue($row); |
||
37 | $column = Functions::flattenSingleValue($column); |
||
38 | $relativity = Functions::flattenSingleValue($relativity); |
||
39 | $sheetText = Functions::flattenSingleValue($sheetText); |
||
40 | |||
41 | if (($row < 1) || ($column < 1)) { |
||
42 | return Functions::VALUE(); |
||
43 | } |
||
44 | |||
45 | if ($sheetText > '') { |
||
46 | if (strpos($sheetText, ' ') !== false) { |
||
47 | $sheetText = "'" . $sheetText . "'"; |
||
48 | } |
||
49 | $sheetText .= '!'; |
||
50 | } |
||
51 | if ((!is_bool($referenceStyle)) || $referenceStyle) { |
||
52 | $rowRelative = $columnRelative = '$'; |
||
53 | $column = Coordinate::stringFromColumnIndex($column); |
||
54 | if (($relativity == 2) || ($relativity == 4)) { |
||
55 | $columnRelative = ''; |
||
56 | } |
||
57 | if (($relativity == 3) || ($relativity == 4)) { |
||
58 | $rowRelative = ''; |
||
59 | } |
||
60 | |||
61 | return $sheetText . $columnRelative . $column . $rowRelative . $row; |
||
62 | } |
||
63 | if (($relativity == 2) || ($relativity == 4)) { |
||
64 | $column = '[' . $column . ']'; |
||
65 | } |
||
66 | if (($relativity == 3) || ($relativity == 4)) { |
||
67 | $row = '[' . $row . ']'; |
||
68 | } |
||
69 | |||
70 | return $sheetText . 'R' . $row . 'C' . $column; |
||
71 | } |
||
72 | |||
73 | /** |
||
74 | * COLUMN. |
||
75 | * |
||
76 | * Returns the column number of the given cell reference |
||
77 | * If the cell reference is a range of cells, COLUMN returns the column numbers of each column in the reference as a horizontal array. |
||
78 | * If cell reference is omitted, and the function is being called through the calculation engine, then it is assumed to be the |
||
79 | * reference of the cell in which the COLUMN function appears; otherwise this function returns 0. |
||
80 | * |
||
81 | * Excel Function: |
||
82 | * =COLUMN([cellAddress]) |
||
83 | * |
||
84 | * @param null|array|string $cellAddress A reference to a range of cells for which you want the column numbers |
||
85 | * |
||
86 | * @return int|int[] |
||
87 | */ |
||
88 | public static function COLUMN($cellAddress = null) |
||
89 | { |
||
90 | if ($cellAddress === null || trim($cellAddress) === '') { |
||
91 | return 0; |
||
92 | } |
||
93 | |||
94 | if (is_array($cellAddress)) { |
||
95 | foreach ($cellAddress as $columnKey => $value) { |
||
96 | $columnKey = preg_replace('/[^a-z]/i', '', $columnKey); |
||
97 | |||
98 | return (int) Coordinate::columnIndexFromString($columnKey); |
||
99 | } |
||
100 | } else { |
||
101 | list($sheet, $cellAddress) = Worksheet::extractSheetTitle($cellAddress, true); |
||
102 | if (strpos($cellAddress, ':') !== false) { |
||
103 | list($startAddress, $endAddress) = explode(':', $cellAddress); |
||
104 | $startAddress = preg_replace('/[^a-z]/i', '', $startAddress); |
||
105 | $endAddress = preg_replace('/[^a-z]/i', '', $endAddress); |
||
106 | $returnValue = []; |
||
107 | do { |
||
108 | $returnValue[] = (int) Coordinate::columnIndexFromString($startAddress); |
||
109 | } while ($startAddress++ != $endAddress); |
||
110 | |||
111 | return $returnValue; |
||
112 | } |
||
113 | $cellAddress = preg_replace('/[^a-z]/i', '', $cellAddress); |
||
114 | |||
115 | return (int) Coordinate::columnIndexFromString($cellAddress); |
||
116 | } |
||
117 | } |
||
118 | |||
119 | /** |
||
120 | * COLUMNS. |
||
121 | * |
||
122 | * Returns the number of columns in an array or reference. |
||
123 | * |
||
124 | * Excel Function: |
||
125 | * =COLUMNS(cellAddress) |
||
126 | * |
||
127 | * @param null|array|string $cellAddress An array or array formula, or a reference to a range of cells for which you want the number of columns |
||
128 | * |
||
129 | * @return int The number of columns in cellAddress |
||
130 | */ |
||
131 | 8 | public static function COLUMNS($cellAddress = null) |
|
132 | { |
||
133 | 8 | if ($cellAddress === null || $cellAddress === '') { |
|
134 | 2 | return 1; |
|
135 | 6 | } elseif (!is_array($cellAddress)) { |
|
136 | 1 | return Functions::VALUE(); |
|
137 | } |
||
138 | |||
139 | 5 | reset($cellAddress); |
|
140 | 5 | $isMatrix = (is_numeric(key($cellAddress))); |
|
141 | 5 | list($columns, $rows) = Calculation::getMatrixDimensions($cellAddress); |
|
142 | |||
143 | 5 | if ($isMatrix) { |
|
144 | 3 | return $rows; |
|
145 | } |
||
146 | |||
147 | 2 | return $columns; |
|
148 | } |
||
149 | |||
150 | /** |
||
151 | * ROW. |
||
152 | * |
||
153 | * Returns the row number of the given cell reference |
||
154 | * If the cell reference is a range of cells, ROW returns the row numbers of each row in the reference as a vertical array. |
||
155 | * If cell reference is omitted, and the function is being called through the calculation engine, then it is assumed to be the |
||
156 | * reference of the cell in which the ROW function appears; otherwise this function returns 0. |
||
157 | * |
||
158 | * Excel Function: |
||
159 | * =ROW([cellAddress]) |
||
160 | * |
||
161 | * @param null|array|string $cellAddress A reference to a range of cells for which you want the row numbers |
||
162 | * |
||
163 | * @return int or array of integer |
||
164 | */ |
||
165 | public static function ROW($cellAddress = null) |
||
166 | { |
||
167 | if ($cellAddress === null || trim($cellAddress) === '') { |
||
168 | return 0; |
||
169 | } |
||
170 | |||
171 | if (is_array($cellAddress)) { |
||
172 | foreach ($cellAddress as $columnKey => $rowValue) { |
||
173 | foreach ($rowValue as $rowKey => $cellValue) { |
||
174 | return (int) preg_replace('/\D/', '', $rowKey); |
||
175 | } |
||
176 | } |
||
177 | } else { |
||
178 | list($sheet, $cellAddress) = Worksheet::extractSheetTitle($cellAddress, true); |
||
179 | if (strpos($cellAddress, ':') !== false) { |
||
180 | list($startAddress, $endAddress) = explode(':', $cellAddress); |
||
181 | $startAddress = preg_replace('/\D/', '', $startAddress); |
||
182 | $endAddress = preg_replace('/\D/', '', $endAddress); |
||
183 | $returnValue = []; |
||
184 | do { |
||
185 | $returnValue[][] = (int) $startAddress; |
||
186 | } while ($startAddress++ != $endAddress); |
||
187 | |||
188 | return $returnValue; |
||
189 | } |
||
190 | list($cellAddress) = explode(':', $cellAddress); |
||
191 | |||
192 | return (int) preg_replace('/\D/', '', $cellAddress); |
||
193 | } |
||
194 | } |
||
195 | |||
196 | /** |
||
197 | * ROWS. |
||
198 | * |
||
199 | * Returns the number of rows in an array or reference. |
||
200 | * |
||
201 | * Excel Function: |
||
202 | * =ROWS(cellAddress) |
||
203 | * |
||
204 | * @param null|array|string $cellAddress An array or array formula, or a reference to a range of cells for which you want the number of rows |
||
205 | * |
||
206 | * @return int The number of rows in cellAddress |
||
207 | */ |
||
208 | 8 | public static function ROWS($cellAddress = null) |
|
209 | { |
||
210 | 8 | if ($cellAddress === null || $cellAddress === '') { |
|
211 | 2 | return 1; |
|
212 | 6 | } elseif (!is_array($cellAddress)) { |
|
213 | 1 | return Functions::VALUE(); |
|
214 | } |
||
215 | |||
216 | 5 | reset($cellAddress); |
|
217 | 5 | $isMatrix = (is_numeric(key($cellAddress))); |
|
218 | 5 | list($columns, $rows) = Calculation::getMatrixDimensions($cellAddress); |
|
219 | |||
220 | 5 | if ($isMatrix) { |
|
221 | 3 | return $columns; |
|
222 | } |
||
223 | |||
224 | 2 | return $rows; |
|
225 | } |
||
226 | |||
227 | /** |
||
228 | * HYPERLINK. |
||
229 | * |
||
230 | * Excel Function: |
||
231 | * =HYPERLINK(linkURL,displayName) |
||
232 | * |
||
233 | * @category Logical Functions |
||
234 | * |
||
235 | * @param string $linkURL Value to check, is also the value returned when no error |
||
236 | * @param string $displayName Value to return when testValue is an error condition |
||
237 | * @param Cell $pCell The cell to set the hyperlink in |
||
238 | * |
||
239 | * @return mixed The value of $displayName (or $linkURL if $displayName was blank) |
||
240 | */ |
||
241 | 1 | public static function HYPERLINK($linkURL = '', $displayName = null, Cell $pCell = null) |
|
242 | { |
||
243 | 1 | $linkURL = ($linkURL === null) ? '' : Functions::flattenSingleValue($linkURL); |
|
244 | 1 | $displayName = ($displayName === null) ? '' : Functions::flattenSingleValue($displayName); |
|
245 | |||
246 | 1 | if ((!is_object($pCell)) || (trim($linkURL) == '')) { |
|
247 | return Functions::REF(); |
||
248 | } |
||
249 | |||
250 | 1 | if ((is_object($displayName)) || trim($displayName) == '') { |
|
251 | $displayName = $linkURL; |
||
252 | } |
||
253 | |||
254 | 1 | $pCell->getHyperlink()->setUrl($linkURL); |
|
255 | 1 | $pCell->getHyperlink()->setTooltip($displayName); |
|
256 | |||
257 | 1 | return $displayName; |
|
258 | } |
||
259 | |||
260 | /** |
||
261 | * INDIRECT. |
||
262 | * |
||
263 | * Returns the reference specified by a text string. |
||
264 | * References are immediately evaluated to display their contents. |
||
265 | * |
||
266 | * Excel Function: |
||
267 | * =INDIRECT(cellAddress) |
||
268 | * |
||
269 | * NOTE - INDIRECT() does not yet support the optional a1 parameter introduced in Excel 2010 |
||
270 | * |
||
271 | * @param null|array|string $cellAddress $cellAddress The cell address of the current cell (containing this formula) |
||
272 | * @param Cell $pCell The current cell (containing this formula) |
||
273 | * |
||
274 | * @return mixed The cells referenced by cellAddress |
||
275 | * |
||
276 | * @todo Support for the optional a1 parameter introduced in Excel 2010 |
||
277 | */ |
||
278 | public static function INDIRECT($cellAddress = null, Cell $pCell = null) |
||
279 | { |
||
280 | $cellAddress = Functions::flattenSingleValue($cellAddress); |
||
281 | if ($cellAddress === null || $cellAddress === '') { |
||
282 | return Functions::REF(); |
||
283 | } |
||
284 | |||
285 | $cellAddress1 = $cellAddress; |
||
286 | $cellAddress2 = null; |
||
287 | if (strpos($cellAddress, ':') !== false) { |
||
288 | list($cellAddress1, $cellAddress2) = explode(':', $cellAddress); |
||
289 | } |
||
290 | |||
291 | if ((!preg_match('/^' . Calculation::CALCULATION_REGEXP_CELLREF . '$/i', $cellAddress1, $matches)) || |
||
292 | (($cellAddress2 !== null) && (!preg_match('/^' . Calculation::CALCULATION_REGEXP_CELLREF . '$/i', $cellAddress2, $matches)))) { |
||
293 | if (!preg_match('/^' . Calculation::CALCULATION_REGEXP_NAMEDRANGE . '$/i', $cellAddress1, $matches)) { |
||
294 | return Functions::REF(); |
||
295 | } |
||
296 | |||
297 | if (strpos($cellAddress, '!') !== false) { |
||
298 | list($sheetName, $cellAddress) = Worksheet::extractSheetTitle($cellAddress, true); |
||
299 | $sheetName = trim($sheetName, "'"); |
||
300 | $pSheet = $pCell->getWorksheet()->getParent()->getSheetByName($sheetName); |
||
301 | } else { |
||
302 | $pSheet = $pCell->getWorksheet(); |
||
303 | } |
||
304 | |||
305 | return Calculation::getInstance()->extractNamedRange($cellAddress, $pSheet, false); |
||
306 | } |
||
307 | |||
308 | if (strpos($cellAddress, '!') !== false) { |
||
309 | list($sheetName, $cellAddress) = Worksheet::extractSheetTitle($cellAddress, true); |
||
310 | $sheetName = trim($sheetName, "'"); |
||
311 | $pSheet = $pCell->getWorksheet()->getParent()->getSheetByName($sheetName); |
||
312 | } else { |
||
313 | $pSheet = $pCell->getWorksheet(); |
||
314 | } |
||
315 | |||
316 | return Calculation::getInstance()->extractCellRange($cellAddress, $pSheet, false); |
||
317 | } |
||
318 | |||
319 | /** |
||
320 | * OFFSET. |
||
321 | * |
||
322 | * Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells. |
||
323 | * The reference that is returned can be a single cell or a range of cells. You can specify the number of rows and |
||
324 | * the number of columns to be returned. |
||
325 | * |
||
326 | * Excel Function: |
||
327 | * =OFFSET(cellAddress, rows, cols, [height], [width]) |
||
328 | * |
||
329 | * @param null|string $cellAddress The reference from which you want to base the offset. Reference must refer to a cell or |
||
330 | * range of adjacent cells; otherwise, OFFSET returns the #VALUE! error value. |
||
331 | * @param mixed $rows The number of rows, up or down, that you want the upper-left cell to refer to. |
||
332 | * Using 5 as the rows argument specifies that the upper-left cell in the reference is |
||
333 | * five rows below reference. Rows can be positive (which means below the starting reference) |
||
334 | * or negative (which means above the starting reference). |
||
335 | * @param mixed $columns The number of columns, to the left or right, that you want the upper-left cell of the result |
||
336 | * to refer to. Using 5 as the cols argument specifies that the upper-left cell in the |
||
337 | * reference is five columns to the right of reference. Cols can be positive (which means |
||
338 | * to the right of the starting reference) or negative (which means to the left of the |
||
339 | * starting reference). |
||
340 | * @param mixed $height The height, in number of rows, that you want the returned reference to be. Height must be a positive number. |
||
341 | * @param mixed $width The width, in number of columns, that you want the returned reference to be. Width must be a positive number. |
||
342 | * @param null|Cell $pCell |
||
343 | * |
||
344 | * @return string A reference to a cell or range of cells |
||
345 | */ |
||
346 | 1 | public static function OFFSET($cellAddress = null, $rows = 0, $columns = 0, $height = null, $width = null, Cell $pCell = null) |
|
347 | { |
||
348 | 1 | $rows = Functions::flattenSingleValue($rows); |
|
349 | 1 | $columns = Functions::flattenSingleValue($columns); |
|
350 | 1 | $height = Functions::flattenSingleValue($height); |
|
351 | 1 | $width = Functions::flattenSingleValue($width); |
|
352 | 1 | if ($cellAddress === null) { |
|
353 | return 0; |
||
354 | } |
||
355 | |||
356 | 1 | if (!is_object($pCell)) { |
|
357 | return Functions::REF(); |
||
358 | } |
||
359 | |||
360 | 1 | $sheetName = null; |
|
361 | 1 | if (strpos($cellAddress, '!')) { |
|
362 | list($sheetName, $cellAddress) = Worksheet::extractSheetTitle($cellAddress, true); |
||
363 | $sheetName = trim($sheetName, "'"); |
||
364 | } |
||
365 | 1 | if (strpos($cellAddress, ':')) { |
|
366 | list($startCell, $endCell) = explode(':', $cellAddress); |
||
367 | } else { |
||
368 | 1 | $startCell = $endCell = $cellAddress; |
|
369 | } |
||
370 | 1 | list($startCellColumn, $startCellRow) = Coordinate::coordinateFromString($startCell); |
|
371 | 1 | list($endCellColumn, $endCellRow) = Coordinate::coordinateFromString($endCell); |
|
372 | |||
373 | 1 | $startCellRow += $rows; |
|
374 | 1 | $startCellColumn = Coordinate::columnIndexFromString($startCellColumn) - 1; |
|
375 | 1 | $startCellColumn += $columns; |
|
376 | |||
377 | 1 | if (($startCellRow <= 0) || ($startCellColumn < 0)) { |
|
378 | return Functions::REF(); |
||
379 | } |
||
380 | 1 | $endCellColumn = Coordinate::columnIndexFromString($endCellColumn) - 1; |
|
381 | 1 | if (($width != null) && (!is_object($width))) { |
|
382 | 1 | $endCellColumn = $startCellColumn + $width - 1; |
|
383 | } else { |
||
384 | 1 | $endCellColumn += $columns; |
|
385 | } |
||
386 | 1 | $startCellColumn = Coordinate::stringFromColumnIndex($startCellColumn + 1); |
|
387 | |||
388 | 1 | if (($height != null) && (!is_object($height))) { |
|
389 | 1 | $endCellRow = $startCellRow + $height - 1; |
|
390 | } else { |
||
391 | 1 | $endCellRow += $rows; |
|
392 | } |
||
393 | |||
394 | 1 | if (($endCellRow <= 0) || ($endCellColumn < 0)) { |
|
395 | return Functions::REF(); |
||
396 | } |
||
397 | 1 | $endCellColumn = Coordinate::stringFromColumnIndex($endCellColumn + 1); |
|
398 | |||
399 | 1 | $cellAddress = $startCellColumn . $startCellRow; |
|
400 | 1 | if (($startCellColumn != $endCellColumn) || ($startCellRow != $endCellRow)) { |
|
401 | $cellAddress .= ':' . $endCellColumn . $endCellRow; |
||
402 | } |
||
403 | |||
404 | 1 | if ($sheetName !== null) { |
|
405 | $pSheet = $pCell->getWorksheet()->getParent()->getSheetByName($sheetName); |
||
406 | } else { |
||
407 | 1 | $pSheet = $pCell->getWorksheet(); |
|
408 | } |
||
409 | |||
410 | 1 | return Calculation::getInstance()->extractCellRange($cellAddress, $pSheet, false); |
|
411 | } |
||
412 | |||
413 | /** |
||
414 | * CHOOSE. |
||
415 | * |
||
416 | * Uses lookup_value to return a value from the list of value arguments. |
||
417 | * Use CHOOSE to select one of up to 254 values based on the lookup_value. |
||
418 | * |
||
419 | * Excel Function: |
||
420 | * =CHOOSE(index_num, value1, [value2], ...) |
||
421 | * |
||
422 | * @param mixed $index_num Specifies which value argument is selected. |
||
423 | * Index_num must be a number between 1 and 254, or a formula or reference to a cell containing a number |
||
424 | * between 1 and 254. |
||
425 | * @param mixed $value1 ... Value1 is required, subsequent values are optional. |
||
426 | * Between 1 to 254 value arguments from which CHOOSE selects a value or an action to perform based on |
||
427 | * index_num. The arguments can be numbers, cell references, defined names, formulas, functions, or |
||
428 | * text. |
||
429 | * |
||
430 | * @return mixed The selected value |
||
431 | */ |
||
432 | public static function CHOOSE(...$chooseArgs) |
||
433 | { |
||
434 | $chosenEntry = Functions::flattenArray(array_shift($chooseArgs)); |
||
435 | $entryCount = count($chooseArgs) - 1; |
||
436 | |||
437 | if (is_array($chosenEntry)) { |
||
438 | $chosenEntry = array_shift($chosenEntry); |
||
439 | } |
||
440 | if ((is_numeric($chosenEntry)) && (!is_bool($chosenEntry))) { |
||
441 | --$chosenEntry; |
||
442 | } else { |
||
443 | return Functions::VALUE(); |
||
444 | } |
||
445 | $chosenEntry = floor($chosenEntry); |
||
446 | if (($chosenEntry < 0) || ($chosenEntry > $entryCount)) { |
||
447 | return Functions::VALUE(); |
||
448 | } |
||
449 | |||
450 | if (is_array($chooseArgs[$chosenEntry])) { |
||
451 | return Functions::flattenArray($chooseArgs[$chosenEntry]); |
||
452 | } |
||
453 | |||
454 | return $chooseArgs[$chosenEntry]; |
||
455 | } |
||
456 | |||
457 | /** |
||
458 | * MATCH. |
||
459 | * |
||
460 | * The MATCH function searches for a specified item in a range of cells |
||
461 | * |
||
462 | * Excel Function: |
||
463 | * =MATCH(lookup_value, lookup_array, [match_type]) |
||
464 | * |
||
465 | * @param mixed $lookupValue The value that you want to match in lookup_array |
||
466 | * @param mixed $lookupArray The range of cells being searched |
||
467 | * @param mixed $matchType The number -1, 0, or 1. -1 means above, 0 means exact match, 1 means below. If match_type is 1 or -1, the list has to be ordered. |
||
468 | * |
||
469 | * @return int The relative position of the found item |
||
470 | */ |
||
471 | 17 | public static function MATCH($lookupValue, $lookupArray, $matchType = 1) |
|
472 | { |
||
473 | 17 | $lookupArray = Functions::flattenArray($lookupArray); |
|
474 | 17 | $lookupValue = Functions::flattenSingleValue($lookupValue); |
|
475 | 17 | $matchType = ($matchType === null) ? 1 : (int) Functions::flattenSingleValue($matchType); |
|
476 | |||
477 | 17 | $initialLookupValue = $lookupValue; |
|
478 | // MATCH is not case sensitive |
||
479 | 17 | $lookupValue = StringHelper::strToLower($lookupValue); |
|
480 | |||
481 | // Lookup_value type has to be number, text, or logical values |
||
482 | 17 | if ((!is_numeric($lookupValue)) && (!is_string($lookupValue)) && (!is_bool($lookupValue))) { |
|
0 ignored issues
–
show
introduced
by
Loading history...
|
|||
483 | return Functions::NA(); |
||
484 | } |
||
485 | |||
486 | // Match_type is 0, 1 or -1 |
||
487 | 17 | if (($matchType !== 0) && ($matchType !== -1) && ($matchType !== 1)) { |
|
488 | return Functions::NA(); |
||
489 | } |
||
490 | |||
491 | // Lookup_array should not be empty |
||
492 | 17 | $lookupArraySize = count($lookupArray); |
|
493 | 17 | if ($lookupArraySize <= 0) { |
|
494 | return Functions::NA(); |
||
495 | } |
||
496 | |||
497 | // Lookup_array should contain only number, text, or logical values, or empty (null) cells |
||
498 | 17 | foreach ($lookupArray as $i => $lookupArrayValue) { |
|
499 | // check the type of the value |
||
500 | 17 | if ((!is_numeric($lookupArrayValue)) && (!is_string($lookupArrayValue)) && |
|
501 | 17 | (!is_bool($lookupArrayValue)) && ($lookupArrayValue !== null) |
|
502 | ) { |
||
503 | return Functions::NA(); |
||
504 | } |
||
505 | // Convert strings to lowercase for case-insensitive testing |
||
506 | 17 | if (is_string($lookupArrayValue)) { |
|
507 | 1 | $lookupArray[$i] = StringHelper::strToLower($lookupArrayValue); |
|
508 | } |
||
509 | 17 | if (($lookupArrayValue === null) && (($matchType == 1) || ($matchType == -1))) { |
|
510 | $lookupArray = array_slice($lookupArray, 0, $i - 1); |
||
511 | } |
||
512 | } |
||
513 | |||
514 | 17 | if ($matchType == 1) { |
|
515 | // If match_type is 1 the list has to be processed from last to first |
||
516 | |||
517 | 4 | $lookupArray = array_reverse($lookupArray); |
|
518 | 4 | $keySet = array_reverse(array_keys($lookupArray)); |
|
519 | } |
||
520 | |||
521 | // ** |
||
522 | // find the match |
||
523 | // ** |
||
524 | |||
525 | 17 | if ($matchType == 0 || $matchType == 1) { |
|
526 | 10 | foreach ($lookupArray as $i => $lookupArrayValue) { |
|
527 | 10 | $onlyNumeric = is_numeric($lookupArrayValue) && is_numeric($lookupValue); |
|
528 | 10 | $onlyNumericExactMatch = $onlyNumeric && $lookupArrayValue == $lookupValue; |
|
529 | 10 | $nonOnlyNumericExactMatch = !$onlyNumeric && $lookupArrayValue === $lookupValue; |
|
530 | 10 | $exactMatch = $onlyNumericExactMatch || $nonOnlyNumericExactMatch; |
|
531 | 10 | if (($matchType == 0) && $exactMatch) { |
|
532 | // exact match |
||
533 | 5 | return $i + 1; |
|
534 | 8 | } elseif (($matchType == 1) && ($lookupArrayValue <= $lookupValue)) { |
|
535 | 4 | $i = array_search($i, $keySet); |
|
0 ignored issues
–
show
Comprehensibility
Best Practice
introduced
by
|
|||
536 | |||
537 | // The current value is the (first) match |
||
538 | 4 | return $i + 1; |
|
539 | } |
||
540 | } |
||
541 | } else { |
||
542 | // matchType = -1 |
||
543 | |||
544 | // "Special" case: since the array it's supposed to be ordered in descending order, the |
||
545 | // Excel algorithm gives up immediately if the first element is smaller than the searched value |
||
546 | 7 | if ($lookupArray[0] < $lookupValue) { |
|
547 | 1 | return Functions::NA(); |
|
0 ignored issues
–
show
|
|||
548 | } |
||
549 | |||
550 | 6 | $maxValueKey = null; |
|
551 | |||
552 | // The basic algorithm is: |
||
553 | // Iterate and keep the highest match until the next element is smaller than the searched value. |
||
554 | // Return immediately if perfect match is found |
||
555 | 6 | foreach ($lookupArray as $i => $lookupArrayValue) { |
|
556 | 6 | if ($lookupArrayValue == $lookupValue) { |
|
557 | // Another "special" case. If a perfect match is found, |
||
558 | // the algorithm gives up immediately |
||
559 | 2 | return $i + 1; |
|
560 | 5 | } elseif ($lookupArrayValue >= $lookupValue) { |
|
561 | 5 | $maxValueKey = $i + 1; |
|
562 | } |
||
563 | } |
||
564 | |||
565 | 4 | if ($maxValueKey !== null) { |
|
566 | 4 | return $maxValueKey; |
|
567 | } |
||
568 | } |
||
569 | |||
570 | // Unsuccessful in finding a match, return #N/A error value |
||
571 | 2 | return Functions::NA(); |
|
572 | } |
||
573 | |||
574 | /** |
||
575 | * INDEX. |
||
576 | * |
||
577 | * Uses an index to choose a value from a reference or array |
||
578 | * |
||
579 | * Excel Function: |
||
580 | * =INDEX(range_array, row_num, [column_num]) |
||
581 | * |
||
582 | * @param mixed $arrayValues A range of cells or an array constant |
||
583 | * @param mixed $rowNum The row in array from which to return a value. If row_num is omitted, column_num is required. |
||
584 | * @param mixed $columnNum The column in array from which to return a value. If column_num is omitted, row_num is required. |
||
585 | * |
||
586 | * @return mixed the value of a specified cell or array of cells |
||
587 | */ |
||
588 | 9 | public static function INDEX($arrayValues, $rowNum = 0, $columnNum = 0) |
|
589 | { |
||
590 | 9 | $rowNum = Functions::flattenSingleValue($rowNum); |
|
591 | 9 | $columnNum = Functions::flattenSingleValue($columnNum); |
|
592 | |||
593 | 9 | if (($rowNum < 0) || ($columnNum < 0)) { |
|
594 | 2 | return Functions::VALUE(); |
|
595 | } |
||
596 | |||
597 | 7 | if (!is_array($arrayValues) || ($rowNum > count($arrayValues))) { |
|
598 | 1 | return Functions::REF(); |
|
599 | } |
||
600 | |||
601 | 6 | $rowKeys = array_keys($arrayValues); |
|
602 | 6 | $columnKeys = @array_keys($arrayValues[$rowKeys[0]]); |
|
603 | |||
604 | 6 | if ($columnNum > count($columnKeys)) { |
|
605 | 1 | return Functions::VALUE(); |
|
606 | 5 | } elseif ($columnNum == 0) { |
|
607 | 3 | if ($rowNum == 0) { |
|
608 | 1 | return $arrayValues; |
|
609 | } |
||
610 | 2 | $rowNum = $rowKeys[--$rowNum]; |
|
611 | 2 | $returnArray = []; |
|
612 | 2 | foreach ($arrayValues as $arrayColumn) { |
|
613 | 2 | if (is_array($arrayColumn)) { |
|
614 | 2 | if (isset($arrayColumn[$rowNum])) { |
|
615 | $returnArray[] = $arrayColumn[$rowNum]; |
||
616 | } else { |
||
617 | 2 | return [$rowNum => $arrayValues[$rowNum]]; |
|
618 | } |
||
619 | } else { |
||
620 | return $arrayValues[$rowNum]; |
||
621 | } |
||
622 | } |
||
623 | |||
624 | return $returnArray; |
||
625 | } |
||
626 | 2 | $columnNum = $columnKeys[--$columnNum]; |
|
627 | 2 | if ($rowNum > count($rowKeys)) { |
|
628 | return Functions::VALUE(); |
||
629 | 2 | } elseif ($rowNum == 0) { |
|
630 | return $arrayValues[$columnNum]; |
||
631 | } |
||
632 | 2 | $rowNum = $rowKeys[--$rowNum]; |
|
633 | |||
634 | 2 | return $arrayValues[$rowNum][$columnNum]; |
|
635 | } |
||
636 | |||
637 | /** |
||
638 | * TRANSPOSE. |
||
639 | * |
||
640 | * @param array $matrixData A matrix of values |
||
641 | * |
||
642 | * @return array |
||
643 | * |
||
644 | * Unlike the Excel TRANSPOSE function, which will only work on a single row or column, this function will transpose a full matrix |
||
645 | */ |
||
646 | 1 | public static function TRANSPOSE($matrixData) |
|
647 | { |
||
648 | 1 | $returnMatrix = []; |
|
649 | 1 | if (!is_array($matrixData)) { |
|
650 | $matrixData = [[$matrixData]]; |
||
651 | } |
||
652 | |||
653 | 1 | $column = 0; |
|
654 | 1 | foreach ($matrixData as $matrixRow) { |
|
655 | 1 | $row = 0; |
|
656 | 1 | foreach ($matrixRow as $matrixCell) { |
|
657 | 1 | $returnMatrix[$row][$column] = $matrixCell; |
|
658 | 1 | ++$row; |
|
659 | } |
||
660 | 1 | ++$column; |
|
661 | } |
||
662 | |||
663 | 1 | return $returnMatrix; |
|
664 | } |
||
665 | |||
666 | 3 | private static function vlookupSort($a, $b) |
|
667 | { |
||
668 | 3 | reset($a); |
|
669 | 3 | $firstColumn = key($a); |
|
670 | 3 | $aLower = StringHelper::strToLower($a[$firstColumn]); |
|
671 | 3 | $bLower = StringHelper::strToLower($b[$firstColumn]); |
|
672 | 3 | if ($aLower == $bLower) { |
|
673 | 1 | return 0; |
|
674 | } |
||
675 | |||
676 | 3 | return ($aLower < $bLower) ? -1 : 1; |
|
677 | } |
||
678 | |||
679 | /** |
||
680 | * VLOOKUP |
||
681 | * The VLOOKUP function searches for value in the left-most column of lookup_array and returns the value in the same row based on the index_number. |
||
682 | * |
||
683 | * @param mixed $lookup_value The value that you want to match in lookup_array |
||
684 | * @param mixed $lookup_array The range of cells being searched |
||
685 | * @param mixed $index_number The column number in table_array from which the matching value must be returned. The first column is 1. |
||
686 | * @param mixed $not_exact_match determines if you are looking for an exact match based on lookup_value |
||
687 | * |
||
688 | * @return mixed The value of the found cell |
||
689 | */ |
||
690 | 15 | public static function VLOOKUP($lookup_value, $lookup_array, $index_number, $not_exact_match = true) |
|
691 | { |
||
692 | 15 | $lookup_value = Functions::flattenSingleValue($lookup_value); |
|
693 | 15 | $index_number = Functions::flattenSingleValue($index_number); |
|
694 | 15 | $not_exact_match = Functions::flattenSingleValue($not_exact_match); |
|
695 | |||
696 | // index_number must be greater than or equal to 1 |
||
697 | 15 | if ($index_number < 1) { |
|
698 | return Functions::VALUE(); |
||
699 | } |
||
700 | |||
701 | // index_number must be less than or equal to the number of columns in lookup_array |
||
702 | 15 | if ((!is_array($lookup_array)) || (empty($lookup_array))) { |
|
703 | return Functions::REF(); |
||
704 | } |
||
705 | 15 | $f = array_keys($lookup_array); |
|
706 | 15 | $firstRow = array_pop($f); |
|
707 | 15 | if ((!is_array($lookup_array[$firstRow])) || ($index_number > count($lookup_array[$firstRow]))) { |
|
708 | return Functions::REF(); |
||
709 | } |
||
710 | 15 | $columnKeys = array_keys($lookup_array[$firstRow]); |
|
711 | 15 | $returnColumn = $columnKeys[--$index_number]; |
|
712 | 15 | $firstColumn = array_shift($columnKeys); |
|
713 | |||
714 | 15 | if (!$not_exact_match) { |
|
715 | 3 | uasort($lookup_array, ['self', 'vlookupSort']); |
|
716 | } |
||
717 | |||
718 | 15 | $lookupLower = StringHelper::strToLower($lookup_value); |
|
719 | 15 | $rowNumber = $rowValue = false; |
|
720 | 15 | foreach ($lookup_array as $rowKey => $rowData) { |
|
721 | 15 | $firstLower = StringHelper::strToLower($rowData[$firstColumn]); |
|
722 | |||
723 | // break if we have passed possible keys |
||
724 | 15 | if ((is_numeric($lookup_value) && is_numeric($rowData[$firstColumn]) && ($rowData[$firstColumn] > $lookup_value)) || |
|
725 | 15 | (!is_numeric($lookup_value) && !is_numeric($rowData[$firstColumn]) && ($firstLower > $lookupLower))) { |
|
726 | 11 | break; |
|
727 | } |
||
728 | // remember the last key, but only if datatypes match |
||
729 | 12 | if ((is_numeric($lookup_value) && is_numeric($rowData[$firstColumn])) || |
|
730 | 12 | (!is_numeric($lookup_value) && !is_numeric($rowData[$firstColumn]))) { |
|
731 | 11 | if ($not_exact_match) { |
|
732 | 8 | $rowNumber = $rowKey; |
|
733 | |||
734 | 8 | continue; |
|
735 | 3 | } elseif (($firstLower == $lookupLower) |
|
736 | // Spreadsheets software returns first exact match, |
||
737 | // we have sorted and we might have broken key orders |
||
738 | // we want the first one (by its initial index) |
||
739 | 3 | && (($rowNumber == false) || ($rowKey < $rowNumber)) |
|
740 | ) { |
||
741 | 1 | $rowNumber = $rowKey; |
|
742 | } |
||
743 | } |
||
744 | } |
||
745 | |||
746 | 15 | if ($rowNumber !== false) { |
|
747 | // return the appropriate value |
||
748 | 9 | return $lookup_array[$rowNumber][$returnColumn]; |
|
749 | } |
||
750 | |||
751 | 6 | return Functions::NA(); |
|
752 | } |
||
753 | |||
754 | /** |
||
755 | * HLOOKUP |
||
756 | * The HLOOKUP function searches for value in the top-most row of lookup_array and returns the value in the same column based on the index_number. |
||
757 | * |
||
758 | * @param mixed $lookup_value The value that you want to match in lookup_array |
||
759 | * @param mixed $lookup_array The range of cells being searched |
||
760 | * @param mixed $index_number The row number in table_array from which the matching value must be returned. The first row is 1. |
||
761 | * @param mixed $not_exact_match determines if you are looking for an exact match based on lookup_value |
||
762 | * |
||
763 | * @return mixed The value of the found cell |
||
764 | */ |
||
765 | 10 | public static function HLOOKUP($lookup_value, $lookup_array, $index_number, $not_exact_match = true) |
|
766 | { |
||
767 | 10 | $lookup_value = Functions::flattenSingleValue($lookup_value); |
|
768 | 10 | $index_number = Functions::flattenSingleValue($index_number); |
|
769 | 10 | $not_exact_match = Functions::flattenSingleValue($not_exact_match); |
|
770 | |||
771 | // index_number must be greater than or equal to 1 |
||
772 | 10 | if ($index_number < 1) { |
|
773 | return Functions::VALUE(); |
||
774 | } |
||
775 | |||
776 | // index_number must be less than or equal to the number of columns in lookup_array |
||
777 | 10 | if ((!is_array($lookup_array)) || (empty($lookup_array))) { |
|
778 | return Functions::REF(); |
||
779 | } |
||
780 | 10 | $f = array_keys($lookup_array); |
|
781 | 10 | $firstRow = array_pop($f); |
|
782 | 10 | if ((!is_array($lookup_array[$firstRow])) || ($index_number > count($lookup_array))) { |
|
783 | return Functions::REF(); |
||
784 | } |
||
785 | |||
786 | 10 | $firstkey = $f[0] - 1; |
|
787 | 10 | $returnColumn = $firstkey + $index_number; |
|
788 | 10 | $firstColumn = array_shift($f); |
|
789 | 10 | $rowNumber = null; |
|
790 | 10 | foreach ($lookup_array[$firstColumn] as $rowKey => $rowData) { |
|
791 | // break if we have passed possible keys |
||
792 | 10 | $bothNumeric = is_numeric($lookup_value) && is_numeric($rowData); |
|
793 | 10 | $bothNotNumeric = !is_numeric($lookup_value) && !is_numeric($rowData); |
|
794 | 10 | $lookupLower = StringHelper::strToLower($lookup_value); |
|
795 | 10 | $rowDataLower = StringHelper::strToLower($rowData); |
|
796 | |||
797 | 10 | if (($bothNumeric && $rowData > $lookup_value) || |
|
798 | 10 | ($bothNotNumeric && $rowDataLower > $lookupLower)) { |
|
799 | 7 | break; |
|
800 | } |
||
801 | |||
802 | // Remember the last key, but only if datatypes match (as in VLOOKUP) |
||
803 | 10 | if ($bothNumeric || $bothNotNumeric) { |
|
804 | 10 | if ($not_exact_match) { |
|
805 | 5 | $rowNumber = $rowKey; |
|
806 | |||
807 | 5 | continue; |
|
808 | 5 | } elseif ($rowDataLower === $lookupLower |
|
809 | 5 | && ($rowNumber === null || $rowKey < $rowNumber) |
|
810 | ) { |
||
811 | 4 | $rowNumber = $rowKey; |
|
812 | } |
||
813 | } |
||
814 | } |
||
815 | |||
816 | 10 | if ($rowNumber !== null) { |
|
817 | // otherwise return the appropriate value |
||
818 | 9 | return $lookup_array[$returnColumn][$rowNumber]; |
|
819 | } |
||
820 | |||
821 | 1 | return Functions::NA(); |
|
822 | } |
||
823 | |||
824 | /** |
||
825 | * LOOKUP |
||
826 | * The LOOKUP function searches for value either from a one-row or one-column range or from an array. |
||
827 | * |
||
828 | * @param mixed $lookup_value The value that you want to match in lookup_array |
||
829 | * @param mixed $lookup_vector The range of cells being searched |
||
830 | * @param null|mixed $result_vector The column from which the matching value must be returned |
||
831 | * |
||
832 | * @return mixed The value of the found cell |
||
833 | */ |
||
834 | 8 | public static function LOOKUP($lookup_value, $lookup_vector, $result_vector = null) |
|
835 | { |
||
836 | 8 | $lookup_value = Functions::flattenSingleValue($lookup_value); |
|
837 | |||
838 | 8 | if (!is_array($lookup_vector)) { |
|
839 | return Functions::NA(); |
||
840 | } |
||
841 | 8 | $hasResultVector = isset($result_vector); |
|
842 | 8 | $lookupRows = count($lookup_vector); |
|
843 | 8 | $l = array_keys($lookup_vector); |
|
844 | 8 | $l = array_shift($l); |
|
845 | 8 | $lookupColumns = count($lookup_vector[$l]); |
|
846 | // we correctly orient our results |
||
847 | 8 | if (($lookupRows === 1 && $lookupColumns > 1) || (!$hasResultVector && $lookupRows === 2 && $lookupColumns !== 2)) { |
|
848 | 1 | $lookup_vector = self::TRANSPOSE($lookup_vector); |
|
849 | 1 | $lookupRows = count($lookup_vector); |
|
850 | 1 | $l = array_keys($lookup_vector); |
|
851 | 1 | $lookupColumns = count($lookup_vector[array_shift($l)]); |
|
852 | } |
||
853 | |||
854 | 8 | if ($result_vector === null) { |
|
855 | 2 | $result_vector = $lookup_vector; |
|
856 | } |
||
857 | 8 | $resultRows = count($result_vector); |
|
858 | 8 | $l = array_keys($result_vector); |
|
859 | 8 | $l = array_shift($l); |
|
860 | 8 | $resultColumns = count($result_vector[$l]); |
|
861 | // we correctly orient our results |
||
862 | 8 | if ($resultRows === 1 && $resultColumns > 1) { |
|
863 | $result_vector = self::TRANSPOSE($result_vector); |
||
864 | $resultRows = count($result_vector); |
||
865 | $r = array_keys($result_vector); |
||
866 | $resultColumns = count($result_vector[array_shift($r)]); |
||
867 | } |
||
868 | |||
869 | 8 | if ($lookupRows === 2 && !$hasResultVector) { |
|
870 | $result_vector = array_pop($lookup_vector); |
||
871 | $lookup_vector = array_shift($lookup_vector); |
||
872 | } |
||
873 | |||
874 | 8 | if ($lookupColumns !== 2) { |
|
875 | 6 | foreach ($lookup_vector as &$value) { |
|
876 | 6 | if (is_array($value)) { |
|
877 | 6 | $k = array_keys($value); |
|
878 | 6 | $key1 = $key2 = array_shift($k); |
|
879 | 6 | ++$key2; |
|
880 | 6 | $dataValue1 = $value[$key1]; |
|
881 | } else { |
||
882 | $key1 = 0; |
||
883 | $key2 = 1; |
||
884 | $dataValue1 = $value; |
||
885 | } |
||
886 | 6 | $dataValue2 = array_shift($result_vector); |
|
887 | 6 | if (is_array($dataValue2)) { |
|
888 | 6 | $dataValue2 = array_shift($dataValue2); |
|
889 | } |
||
890 | 6 | $value = [$key1 => $dataValue1, $key2 => $dataValue2]; |
|
891 | } |
||
892 | 6 | unset($value); |
|
893 | } |
||
894 | |||
895 | 8 | return self::VLOOKUP($lookup_value, $lookup_vector, 2); |
|
896 | } |
||
897 | |||
898 | /** |
||
899 | * FORMULATEXT. |
||
900 | * |
||
901 | * @param mixed $cellReference The cell to check |
||
902 | * @param Cell $pCell The current cell (containing this formula) |
||
903 | * |
||
904 | * @return string |
||
905 | */ |
||
906 | 6 | public static function FORMULATEXT($cellReference = '', Cell $pCell = null) |
|
907 | { |
||
908 | 6 | if ($pCell === null) { |
|
909 | return Functions::REF(); |
||
910 | } |
||
911 | |||
912 | 6 | preg_match('/^' . Calculation::CALCULATION_REGEXP_CELLREF . '$/i', $cellReference, $matches); |
|
913 | |||
914 | 6 | $cellReference = $matches[6] . $matches[7]; |
|
915 | 6 | $worksheetName = trim($matches[3], "'"); |
|
916 | 6 | $worksheet = (!empty($worksheetName)) |
|
917 | 2 | ? $pCell->getWorksheet()->getParent()->getSheetByName($worksheetName) |
|
918 | 6 | : $pCell->getWorksheet(); |
|
919 | |||
920 | 6 | if (!$worksheet->getCell($cellReference)->isFormula()) { |
|
921 | 2 | return Functions::NA(); |
|
922 | } |
||
923 | |||
924 | 4 | return $worksheet->getCell($cellReference)->getValue(); |
|
925 | } |
||
926 | } |
||
927 |