[Summary view]

Differences Between: [Versions 39 and 311] [Versions 39 and 400] [Versions 39 and 401] [Versions 39 and 402] [Versions 39 and 403]

   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              [$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  }