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