1<?php
2
3namespace PhpOffice\PhpSpreadsheet\Calculation;
4
5use PhpOffice\PhpSpreadsheet\Cell\Cell;
6use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
7use PhpOffice\PhpSpreadsheet\Shared\StringHelper;
8use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
9
10class 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            [$sheet, $cellAddress] = Worksheet::extractSheetTitle($cellAddress, true);
102            if (strpos($cellAddress, ':') !== false) {
103                [$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    public static function COLUMNS($cellAddress = null)
132    {
133        if ($cellAddress === null || $cellAddress === '') {
134            return 1;
135        } elseif (!is_array($cellAddress)) {
136            return Functions::VALUE();
137        }
138
139        reset($cellAddress);
140        $isMatrix = (is_numeric(key($cellAddress)));
141        [$columns, $rows] = Calculation::getMatrixDimensions($cellAddress);
142
143        if ($isMatrix) {
144            return $rows;
145        }
146
147        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            [$sheet, $cellAddress] = Worksheet::extractSheetTitle($cellAddress, true);
179            if (strpos($cellAddress, ':') !== false) {
180                [$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            [$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    public static function ROWS($cellAddress = null)
209    {
210        if ($cellAddress === null || $cellAddress === '') {
211            return 1;
212        } elseif (!is_array($cellAddress)) {
213            return Functions::VALUE();
214        }
215
216        reset($cellAddress);
217        $isMatrix = (is_numeric(key($cellAddress)));
218        [$columns, $rows] = Calculation::getMatrixDimensions($cellAddress);
219
220        if ($isMatrix) {
221            return $columns;
222        }
223
224        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    public static function HYPERLINK($linkURL = '', $displayName = null, Cell $pCell = null)
242    {
243        $linkURL = ($linkURL === null) ? '' : Functions::flattenSingleValue($linkURL);
244        $displayName = ($displayName === null) ? '' : Functions::flattenSingleValue($displayName);
245
246        if ((!is_object($pCell)) || (trim($linkURL) == '')) {
247            return Functions::REF();
248        }
249
250        if ((is_object($displayName)) || trim($displayName) == '') {
251            $displayName = $linkURL;
252        }
253
254        $pCell->getHyperlink()->setUrl($linkURL);
255        $pCell->getHyperlink()->setTooltip($displayName);
256
257        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            [$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                [$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            [$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    public static function OFFSET($cellAddress = null, $rows = 0, $columns = 0, $height = null, $width = null, Cell $pCell = null)
347    {
348        $rows = Functions::flattenSingleValue($rows);
349        $columns = Functions::flattenSingleValue($columns);
350        $height = Functions::flattenSingleValue($height);
351        $width = Functions::flattenSingleValue($width);
352        if ($cellAddress === null) {
353            return 0;
354        }
355
356        if (!is_object($pCell)) {
357            return Functions::REF();
358        }
359
360        $sheetName = null;
361        if (strpos($cellAddress, '!')) {
362            [$sheetName, $cellAddress] = Worksheet::extractSheetTitle($cellAddress, true);
363            $sheetName = trim($sheetName, "'");
364        }
365        if (strpos($cellAddress, ':')) {
366            [$startCell, $endCell] = explode(':', $cellAddress);
367        } else {
368            $startCell = $endCell = $cellAddress;
369        }
370        [$startCellColumn, $startCellRow] = Coordinate::coordinateFromString($startCell);
371        [$endCellColumn, $endCellRow] = Coordinate::coordinateFromString($endCell);
372
373        $startCellRow += $rows;
374        $startCellColumn = Coordinate::columnIndexFromString($startCellColumn) - 1;
375        $startCellColumn += $columns;
376
377        if (($startCellRow <= 0) || ($startCellColumn < 0)) {
378            return Functions::REF();
379        }
380        $endCellColumn = Coordinate::columnIndexFromString($endCellColumn) - 1;
381        if (($width != null) && (!is_object($width))) {
382            $endCellColumn = $startCellColumn + $width - 1;
383        } else {
384            $endCellColumn += $columns;
385        }
386        $startCellColumn = Coordinate::stringFromColumnIndex($startCellColumn + 1);
387
388        if (($height != null) && (!is_object($height))) {
389            $endCellRow = $startCellRow + $height - 1;
390        } else {
391            $endCellRow += $rows;
392        }
393
394        if (($endCellRow <= 0) || ($endCellColumn < 0)) {
395            return Functions::REF();
396        }
397        $endCellColumn = Coordinate::stringFromColumnIndex($endCellColumn + 1);
398
399        $cellAddress = $startCellColumn . $startCellRow;
400        if (($startCellColumn != $endCellColumn) || ($startCellRow != $endCellRow)) {
401            $cellAddress .= ':' . $endCellColumn . $endCellRow;
402        }
403
404        if ($sheetName !== null) {
405            $pSheet = $pCell->getWorksheet()->getParent()->getSheetByName($sheetName);
406        } else {
407            $pSheet = $pCell->getWorksheet();
408        }
409
410        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.
468     *                         If match_type is 1 or -1, the list has to be ordered.
469     *
470     * @return int|string The relative position of the found item
471     */
472    public static function MATCH($lookupValue, $lookupArray, $matchType = 1)
473    {
474        $lookupArray = Functions::flattenArray($lookupArray);
475        $lookupValue = Functions::flattenSingleValue($lookupValue);
476        $matchType = ($matchType === null) ? 1 : (int) Functions::flattenSingleValue($matchType);
477
478        // MATCH is not case sensitive, so we convert lookup value to be lower cased in case it's string type.
479        if (is_string($lookupValue)) {
480            $lookupValue = StringHelper::strToLower($lookupValue);
481        }
482
483        // Lookup_value type has to be number, text, or logical values
484        if ((!is_numeric($lookupValue)) && (!is_string($lookupValue)) && (!is_bool($lookupValue))) {
485            return Functions::NA();
486        }
487
488        // Match_type is 0, 1 or -1
489        if (($matchType !== 0) && ($matchType !== -1) && ($matchType !== 1)) {
490            return Functions::NA();
491        }
492
493        // Lookup_array should not be empty
494        $lookupArraySize = count($lookupArray);
495        if ($lookupArraySize <= 0) {
496            return Functions::NA();
497        }
498
499        // Lookup_array should contain only number, text, or logical values, or empty (null) cells
500        foreach ($lookupArray as $i => $lookupArrayValue) {
501            //    check the type of the value
502            if ((!is_numeric($lookupArrayValue)) && (!is_string($lookupArrayValue)) &&
503                (!is_bool($lookupArrayValue)) && ($lookupArrayValue !== null)
504            ) {
505                return Functions::NA();
506            }
507            // Convert strings to lowercase for case-insensitive testing
508            if (is_string($lookupArrayValue)) {
509                $lookupArray[$i] = StringHelper::strToLower($lookupArrayValue);
510            }
511            if (($lookupArrayValue === null) && (($matchType == 1) || ($matchType == -1))) {
512                $lookupArray = array_slice($lookupArray, 0, $i - 1);
513            }
514        }
515
516        if ($matchType == 1) {
517            // If match_type is 1 the list has to be processed from last to first
518
519            $lookupArray = array_reverse($lookupArray);
520            $keySet = array_reverse(array_keys($lookupArray));
521        }
522
523        // **
524        // find the match
525        // **
526
527        if ($matchType === 0 || $matchType === 1) {
528            foreach ($lookupArray as $i => $lookupArrayValue) {
529                $typeMatch = gettype($lookupValue) === gettype($lookupArrayValue);
530                $exactTypeMatch = $typeMatch && $lookupArrayValue === $lookupValue;
531                $nonOnlyNumericExactMatch = !$typeMatch && $lookupArrayValue === $lookupValue;
532                $exactMatch = $exactTypeMatch || $nonOnlyNumericExactMatch;
533
534                if ($matchType === 0) {
535                    if ($typeMatch && is_string($lookupValue) && (bool) preg_match('/([\?\*])/', $lookupValue)) {
536                        $splitString = $lookupValue;
537                        $chars = array_map(function ($i) use ($splitString) {
538                            return mb_substr($splitString, $i, 1);
539                        }, range(0, mb_strlen($splitString) - 1));
540
541                        $length = count($chars);
542                        $pattern = '/^';
543                        for ($j = 0; $j < $length; ++$j) {
544                            if ($chars[$j] === '~') {
545                                if (isset($chars[$j + 1])) {
546                                    if ($chars[$j + 1] === '*') {
547                                        $pattern .= preg_quote($chars[$j + 1], '/');
548                                        ++$j;
549                                    } elseif ($chars[$j + 1] === '?') {
550                                        $pattern .= preg_quote($chars[$j + 1], '/');
551                                        ++$j;
552                                    }
553                                } else {
554                                    $pattern .= preg_quote($chars[$j], '/');
555                                }
556                            } elseif ($chars[$j] === '*') {
557                                $pattern .= '.*';
558                            } elseif ($chars[$j] === '?') {
559                                $pattern .= '.{1}';
560                            } else {
561                                $pattern .= preg_quote($chars[$j], '/');
562                            }
563                        }
564
565                        $pattern .= '$/';
566                        if ((bool) preg_match($pattern, $lookupArrayValue)) {
567                            // exact match
568                            return $i + 1;
569                        }
570                    } elseif ($exactMatch) {
571                        // exact match
572                        return $i + 1;
573                    }
574                } elseif (($matchType === 1) && $typeMatch && ($lookupArrayValue <= $lookupValue)) {
575                    $i = array_search($i, $keySet);
576
577                    // The current value is the (first) match
578                    return $i + 1;
579                }
580            }
581        } else {
582            $maxValueKey = null;
583
584            // The basic algorithm is:
585            // Iterate and keep the highest match until the next element is smaller than the searched value.
586            // Return immediately if perfect match is found
587            foreach ($lookupArray as $i => $lookupArrayValue) {
588                $typeMatch = gettype($lookupValue) === gettype($lookupArrayValue);
589                $exactTypeMatch = $typeMatch && $lookupArrayValue === $lookupValue;
590                $nonOnlyNumericExactMatch = !$typeMatch && $lookupArrayValue === $lookupValue;
591                $exactMatch = $exactTypeMatch || $nonOnlyNumericExactMatch;
592
593                if ($exactMatch) {
594                    // Another "special" case. If a perfect match is found,
595                    // the algorithm gives up immediately
596                    return $i + 1;
597                } elseif ($typeMatch & $lookupArrayValue >= $lookupValue) {
598                    $maxValueKey = $i + 1;
599                } elseif ($typeMatch & $lookupArrayValue < $lookupValue) {
600                    //Excel algorithm gives up immediately if the first element is smaller than the searched value
601                    break;
602                }
603            }
604
605            if ($maxValueKey !== null) {
606                return $maxValueKey;
607            }
608        }
609
610        // Unsuccessful in finding a match, return #N/A error value
611        return Functions::NA();
612    }
613
614    /**
615     * INDEX.
616     *
617     * Uses an index to choose a value from a reference or array
618     *
619     * Excel Function:
620     *        =INDEX(range_array, row_num, [column_num])
621     *
622     * @param mixed $arrayValues A range of cells or an array constant
623     * @param mixed $rowNum The row in array from which to return a value. If row_num is omitted, column_num is required.
624     * @param mixed $columnNum The column in array from which to return a value. If column_num is omitted, row_num is required.
625     *
626     * @return mixed the value of a specified cell or array of cells
627     */
628    public static function INDEX($arrayValues, $rowNum = 0, $columnNum = 0)
629    {
630        $rowNum = Functions::flattenSingleValue($rowNum);
631        $columnNum = Functions::flattenSingleValue($columnNum);
632
633        if (($rowNum < 0) || ($columnNum < 0)) {
634            return Functions::VALUE();
635        }
636
637        if (!is_array($arrayValues) || ($rowNum > count($arrayValues))) {
638            return Functions::REF();
639        }
640
641        $rowKeys = array_keys($arrayValues);
642        $columnKeys = @array_keys($arrayValues[$rowKeys[0]]);
643
644        if ($columnNum > count($columnKeys)) {
645            return Functions::VALUE();
646        } elseif ($columnNum == 0) {
647            if ($rowNum == 0) {
648                return $arrayValues;
649            }
650            $rowNum = $rowKeys[--$rowNum];
651            $returnArray = [];
652            foreach ($arrayValues as $arrayColumn) {
653                if (is_array($arrayColumn)) {
654                    if (isset($arrayColumn[$rowNum])) {
655                        $returnArray[] = $arrayColumn[$rowNum];
656                    } else {
657                        return [$rowNum => $arrayValues[$rowNum]];
658                    }
659                } else {
660                    return $arrayValues[$rowNum];
661                }
662            }
663
664            return $returnArray;
665        }
666        $columnNum = $columnKeys[--$columnNum];
667        if ($rowNum > count($rowKeys)) {
668            return Functions::VALUE();
669        } elseif ($rowNum == 0) {
670            return $arrayValues[$columnNum];
671        }
672        $rowNum = $rowKeys[--$rowNum];
673
674        return $arrayValues[$rowNum][$columnNum];
675    }
676
677    /**
678     * TRANSPOSE.
679     *
680     * @param array $matrixData A matrix of values
681     *
682     * @return array
683     *
684     * Unlike the Excel TRANSPOSE function, which will only work on a single row or column, this function will transpose a full matrix
685     */
686    public static function TRANSPOSE($matrixData)
687    {
688        $returnMatrix = [];
689        if (!is_array($matrixData)) {
690            $matrixData = [[$matrixData]];
691        }
692
693        $column = 0;
694        foreach ($matrixData as $matrixRow) {
695            $row = 0;
696            foreach ($matrixRow as $matrixCell) {
697                $returnMatrix[$row][$column] = $matrixCell;
698                ++$row;
699            }
700            ++$column;
701        }
702
703        return $returnMatrix;
704    }
705
706    private static function vlookupSort($a, $b)
707    {
708        reset($a);
709        $firstColumn = key($a);
710        $aLower = StringHelper::strToLower($a[$firstColumn]);
711        $bLower = StringHelper::strToLower($b[$firstColumn]);
712        if ($aLower == $bLower) {
713            return 0;
714        }
715
716        return ($aLower < $bLower) ? -1 : 1;
717    }
718
719    /**
720     * VLOOKUP
721     * 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.
722     *
723     * @param mixed $lookup_value The value that you want to match in lookup_array
724     * @param mixed $lookup_array The range of cells being searched
725     * @param mixed $index_number The column number in table_array from which the matching value must be returned. The first column is 1.
726     * @param mixed $not_exact_match determines if you are looking for an exact match based on lookup_value
727     *
728     * @return mixed The value of the found cell
729     */
730    public static function VLOOKUP($lookup_value, $lookup_array, $index_number, $not_exact_match = true)
731    {
732        $lookup_value = Functions::flattenSingleValue($lookup_value);
733        $index_number = Functions::flattenSingleValue($index_number);
734        $not_exact_match = Functions::flattenSingleValue($not_exact_match);
735
736        // index_number must be greater than or equal to 1
737        if ($index_number < 1) {
738            return Functions::VALUE();
739        }
740
741        // index_number must be less than or equal to the number of columns in lookup_array
742        if ((!is_array($lookup_array)) || (empty($lookup_array))) {
743            return Functions::REF();
744        }
745        $f = array_keys($lookup_array);
746        $firstRow = array_pop($f);
747        if ((!is_array($lookup_array[$firstRow])) || ($index_number > count($lookup_array[$firstRow]))) {
748            return Functions::REF();
749        }
750        $columnKeys = array_keys($lookup_array[$firstRow]);
751        $returnColumn = $columnKeys[--$index_number];
752        $firstColumn = array_shift($columnKeys);
753
754        if (!$not_exact_match) {
755            uasort($lookup_array, ['self', 'vlookupSort']);
756        }
757
758        $lookupLower = StringHelper::strToLower($lookup_value);
759        $rowNumber = $rowValue = false;
760        foreach ($lookup_array as $rowKey => $rowData) {
761            $firstLower = StringHelper::strToLower($rowData[$firstColumn]);
762
763            // break if we have passed possible keys
764            if ((is_numeric($lookup_value) && is_numeric($rowData[$firstColumn]) && ($rowData[$firstColumn] > $lookup_value)) ||
765                (!is_numeric($lookup_value) && !is_numeric($rowData[$firstColumn]) && ($firstLower > $lookupLower))) {
766                break;
767            }
768            // remember the last key, but only if datatypes match
769            if ((is_numeric($lookup_value) && is_numeric($rowData[$firstColumn])) ||
770                (!is_numeric($lookup_value) && !is_numeric($rowData[$firstColumn]))) {
771                if ($not_exact_match) {
772                    $rowNumber = $rowKey;
773
774                    continue;
775                } elseif (($firstLower == $lookupLower)
776                    // Spreadsheets software returns first exact match,
777                    // we have sorted and we might have broken key orders
778                    // we want the first one (by its initial index)
779                    && (($rowNumber == false) || ($rowKey < $rowNumber))
780                ) {
781                    $rowNumber = $rowKey;
782                }
783            }
784        }
785
786        if ($rowNumber !== false) {
787            // return the appropriate value
788            return $lookup_array[$rowNumber][$returnColumn];
789        }
790
791        return Functions::NA();
792    }
793
794    /**
795     * HLOOKUP
796     * 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.
797     *
798     * @param mixed $lookup_value The value that you want to match in lookup_array
799     * @param mixed $lookup_array The range of cells being searched
800     * @param mixed $index_number The row number in table_array from which the matching value must be returned. The first row is 1.
801     * @param mixed $not_exact_match determines if you are looking for an exact match based on lookup_value
802     *
803     * @return mixed The value of the found cell
804     */
805    public static function HLOOKUP($lookup_value, $lookup_array, $index_number, $not_exact_match = true)
806    {
807        $lookup_value = Functions::flattenSingleValue($lookup_value);
808        $index_number = Functions::flattenSingleValue($index_number);
809        $not_exact_match = Functions::flattenSingleValue($not_exact_match);
810
811        // index_number must be greater than or equal to 1
812        if ($index_number < 1) {
813            return Functions::VALUE();
814        }
815
816        // index_number must be less than or equal to the number of columns in lookup_array
817        if ((!is_array($lookup_array)) || (empty($lookup_array))) {
818            return Functions::REF();
819        }
820        $f = array_keys($lookup_array);
821        $firstRow = array_pop($f);
822        if ((!is_array($lookup_array[$firstRow])) || ($index_number > count($lookup_array))) {
823            return Functions::REF();
824        }
825
826        $firstkey = $f[0] - 1;
827        $returnColumn = $firstkey + $index_number;
828        $firstColumn = array_shift($f);
829        $rowNumber = null;
830        foreach ($lookup_array[$firstColumn] as $rowKey => $rowData) {
831            // break if we have passed possible keys
832            $bothNumeric = is_numeric($lookup_value) && is_numeric($rowData);
833            $bothNotNumeric = !is_numeric($lookup_value) && !is_numeric($rowData);
834            $lookupLower = StringHelper::strToLower($lookup_value);
835            $rowDataLower = StringHelper::strToLower($rowData);
836
837            if ($not_exact_match && (
838                ($bothNumeric && $rowData > $lookup_value) ||
839                ($bothNotNumeric && $rowDataLower > $lookupLower)
840                )) {
841                break;
842            }
843
844            // Remember the last key, but only if datatypes match (as in VLOOKUP)
845            if ($bothNumeric || $bothNotNumeric) {
846                if ($not_exact_match) {
847                    $rowNumber = $rowKey;
848
849                    continue;
850                } elseif ($rowDataLower === $lookupLower
851                    && ($rowNumber === null || $rowKey < $rowNumber)
852                ) {
853                    $rowNumber = $rowKey;
854                }
855            }
856        }
857
858        if ($rowNumber !== null) {
859            //  otherwise return the appropriate value
860            return $lookup_array[$returnColumn][$rowNumber];
861        }
862
863        return Functions::NA();
864    }
865
866    /**
867     * LOOKUP
868     * The LOOKUP function searches for value either from a one-row or one-column range or from an array.
869     *
870     * @param mixed $lookup_value The value that you want to match in lookup_array
871     * @param mixed $lookup_vector The range of cells being searched
872     * @param null|mixed $result_vector The column from which the matching value must be returned
873     *
874     * @return mixed The value of the found cell
875     */
876    public static function LOOKUP($lookup_value, $lookup_vector, $result_vector = null)
877    {
878        $lookup_value = Functions::flattenSingleValue($lookup_value);
879
880        if (!is_array($lookup_vector)) {
881            return Functions::NA();
882        }
883        $hasResultVector = isset($result_vector);
884        $lookupRows = count($lookup_vector);
885        $l = array_keys($lookup_vector);
886        $l = array_shift($l);
887        $lookupColumns = count($lookup_vector[$l]);
888        // we correctly orient our results
889        if (($lookupRows === 1 && $lookupColumns > 1) || (!$hasResultVector && $lookupRows === 2 && $lookupColumns !== 2)) {
890            $lookup_vector = self::TRANSPOSE($lookup_vector);
891            $lookupRows = count($lookup_vector);
892            $l = array_keys($lookup_vector);
893            $lookupColumns = count($lookup_vector[array_shift($l)]);
894        }
895
896        if ($result_vector === null) {
897            $result_vector = $lookup_vector;
898        }
899        $resultRows = count($result_vector);
900        $l = array_keys($result_vector);
901        $l = array_shift($l);
902        $resultColumns = count($result_vector[$l]);
903        // we correctly orient our results
904        if ($resultRows === 1 && $resultColumns > 1) {
905            $result_vector = self::TRANSPOSE($result_vector);
906            $resultRows = count($result_vector);
907            $r = array_keys($result_vector);
908            $resultColumns = count($result_vector[array_shift($r)]);
909        }
910
911        if ($lookupRows === 2 && !$hasResultVector) {
912            $result_vector = array_pop($lookup_vector);
913            $lookup_vector = array_shift($lookup_vector);
914        }
915
916        if ($lookupColumns !== 2) {
917            foreach ($lookup_vector as &$value) {
918                if (is_array($value)) {
919                    $k = array_keys($value);
920                    $key1 = $key2 = array_shift($k);
921                    ++$key2;
922                    $dataValue1 = $value[$key1];
923                } else {
924                    $key1 = 0;
925                    $key2 = 1;
926                    $dataValue1 = $value;
927                }
928                $dataValue2 = array_shift($result_vector);
929                if (is_array($dataValue2)) {
930                    $dataValue2 = array_shift($dataValue2);
931                }
932                $value = [$key1 => $dataValue1, $key2 => $dataValue2];
933            }
934            unset($value);
935        }
936
937        return self::VLOOKUP($lookup_value, $lookup_vector, 2);
938    }
939
940    /**
941     * FORMULATEXT.
942     *
943     * @param mixed $cellReference The cell to check
944     * @param Cell $pCell The current cell (containing this formula)
945     *
946     * @return string
947     */
948    public static function FORMULATEXT($cellReference = '', Cell $pCell = null)
949    {
950        if ($pCell === null) {
951            return Functions::REF();
952        }
953
954        preg_match('/^' . Calculation::CALCULATION_REGEXP_CELLREF . '$/i', $cellReference, $matches);
955
956        $cellReference = $matches[6] . $matches[7];
957        $worksheetName = trim($matches[3], "'");
958        $worksheet = (!empty($worksheetName))
959            ? $pCell->getWorksheet()->getParent()->getSheetByName($worksheetName)
960            : $pCell->getWorksheet();
961
962        if (!$worksheet->getCell($cellReference)->isFormula()) {
963            return Functions::NA();
964        }
965
966        return $worksheet->getCell($cellReference)->getValue();
967    }
968}
969