Differences Between: [Versions 310 and 311] [Versions 311 and 400] [Versions 311 and 401] [Versions 311 and 402] [Versions 311 and 403] [Versions 39 and 311]
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|string The number of columns in cellAddress, or a string if arguments are invalid 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|mixed[]|string 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|string The number of rows in cellAddress, or a string if arguments are invalid 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 * @param string $linkURL Value to check, is also the value returned when no error 234 * @param string $displayName Value to return when testValue is an error condition 235 * @param Cell $pCell The cell to set the hyperlink in 236 * 237 * @return mixed The value of $displayName (or $linkURL if $displayName was blank) 238 */ 239 public static function HYPERLINK($linkURL = '', $displayName = null, ?Cell $pCell = null) 240 { 241 $linkURL = ($linkURL === null) ? '' : Functions::flattenSingleValue($linkURL); 242 $displayName = ($displayName === null) ? '' : Functions::flattenSingleValue($displayName); 243 244 if ((!is_object($pCell)) || (trim($linkURL) == '')) { 245 return Functions::REF(); 246 } 247 248 if ((is_object($displayName)) || trim($displayName) == '') { 249 $displayName = $linkURL; 250 } 251 252 $pCell->getHyperlink()->setUrl($linkURL); 253 $pCell->getHyperlink()->setTooltip($displayName); 254 255 return $displayName; 256 } 257 258 /** 259 * INDIRECT. 260 * 261 * Returns the reference specified by a text string. 262 * References are immediately evaluated to display their contents. 263 * 264 * Excel Function: 265 * =INDIRECT(cellAddress) 266 * 267 * NOTE - INDIRECT() does not yet support the optional a1 parameter introduced in Excel 2010 268 * 269 * @param null|array|string $cellAddress $cellAddress The cell address of the current cell (containing this formula) 270 * @param Cell $pCell The current cell (containing this formula) 271 * 272 * @return mixed The cells referenced by cellAddress 273 * 274 * @TODO Support for the optional a1 parameter introduced in Excel 2010 275 */ 276 public static function INDIRECT($cellAddress = null, ?Cell $pCell = null) 277 { 278 $cellAddress = Functions::flattenSingleValue($cellAddress); 279 if ($cellAddress === null || $cellAddress === '') { 280 return Functions::REF(); 281 } 282 283 $cellAddress1 = $cellAddress; 284 $cellAddress2 = null; 285 if (strpos($cellAddress, ':') !== false) { 286 [$cellAddress1, $cellAddress2] = explode(':', $cellAddress); 287 } 288 289 if ( 290 (!preg_match('/^' . Calculation::CALCULATION_REGEXP_CELLREF . '$/i', $cellAddress1, $matches)) || 291 (($cellAddress2 !== null) && (!preg_match('/^' . Calculation::CALCULATION_REGEXP_CELLREF . '$/i', $cellAddress2, $matches))) 292 ) { 293 if (!preg_match('/^' . Calculation::CALCULATION_REGEXP_DEFINEDNAME . '$/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 * 343 * @return string A reference to a cell or range of cells 344 */ 345 public static function OFFSET($cellAddress = null, $rows = 0, $columns = 0, $height = null, $width = null, ?Cell $pCell = null) 346 { 347 $rows = Functions::flattenSingleValue($rows); 348 $columns = Functions::flattenSingleValue($columns); 349 $height = Functions::flattenSingleValue($height); 350 $width = Functions::flattenSingleValue($width); 351 if ($cellAddress === null) { 352 return 0; 353 } 354 355 if (!is_object($pCell)) { 356 return Functions::REF(); 357 } 358 359 $sheetName = null; 360 if (strpos($cellAddress, '!')) { 361 [$sheetName, $cellAddress] = Worksheet::extractSheetTitle($cellAddress, true); 362 $sheetName = trim($sheetName, "'"); 363 } 364 if (strpos($cellAddress, ':')) { 365 [$startCell, $endCell] = explode(':', $cellAddress); 366 } else { 367 $startCell = $endCell = $cellAddress; 368 } 369 [$startCellColumn, $startCellRow] = Coordinate::coordinateFromString($startCell); 370 [$endCellColumn, $endCellRow] = Coordinate::coordinateFromString($endCell); 371 372 $startCellRow += $rows; 373 $startCellColumn = Coordinate::columnIndexFromString($startCellColumn) - 1; 374 $startCellColumn += $columns; 375 376 if (($startCellRow <= 0) || ($startCellColumn < 0)) { 377 return Functions::REF(); 378 } 379 $endCellColumn = Coordinate::columnIndexFromString($endCellColumn) - 1; 380 if (($width != null) && (!is_object($width))) { 381 $endCellColumn = $startCellColumn + $width - 1; 382 } else { 383 $endCellColumn += $columns; 384 } 385 $startCellColumn = Coordinate::stringFromColumnIndex($startCellColumn + 1); 386 387 if (($height != null) && (!is_object($height))) { 388 $endCellRow = $startCellRow + $height - 1; 389 } else { 390 $endCellRow += $rows; 391 } 392 393 if (($endCellRow <= 0) || ($endCellColumn < 0)) { 394 return Functions::REF(); 395 } 396 $endCellColumn = Coordinate::stringFromColumnIndex($endCellColumn + 1); 397 398 $cellAddress = $startCellColumn . $startCellRow; 399 if (($startCellColumn != $endCellColumn) || ($startCellRow != $endCellRow)) { 400 $cellAddress .= ':' . $endCellColumn . $endCellRow; 401 } 402 403 if ($sheetName !== null) { 404 $pSheet = $pCell->getWorksheet()->getParent()->getSheetByName($sheetName); 405 } else { 406 $pSheet = $pCell->getWorksheet(); 407 } 408 409 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 * @return mixed The selected value 422 */ 423 public static function CHOOSE(...$chooseArgs) 424 { 425 $chosenEntry = Functions::flattenArray(array_shift($chooseArgs)); 426 $entryCount = count($chooseArgs) - 1; 427 428 if (is_array($chosenEntry)) { 429 $chosenEntry = array_shift($chosenEntry); 430 } 431 if ((is_numeric($chosenEntry)) && (!is_bool($chosenEntry))) { 432 --$chosenEntry; 433 } else { 434 return Functions::VALUE(); 435 } 436 $chosenEntry = floor($chosenEntry); 437 if (($chosenEntry < 0) || ($chosenEntry > $entryCount)) { 438 return Functions::VALUE(); 439 } 440 441 if (is_array($chooseArgs[$chosenEntry])) { 442 return Functions::flattenArray($chooseArgs[$chosenEntry]); 443 } 444 445 return $chooseArgs[$chosenEntry]; 446 } 447 448 /** 449 * MATCH. 450 * 451 * The MATCH function searches for a specified item in a range of cells 452 * 453 * Excel Function: 454 * =MATCH(lookup_value, lookup_array, [match_type]) 455 * 456 * @param mixed $lookupValue The value that you want to match in lookup_array 457 * @param mixed $lookupArray The range of cells being searched 458 * @param mixed $matchType The number -1, 0, or 1. -1 means above, 0 means exact match, 1 means below. 459 * If match_type is 1 or -1, the list has to be ordered. 460 * 461 * @return int|string The relative position of the found item 462 */ 463 public static function MATCH($lookupValue, $lookupArray, $matchType = 1) 464 { 465 $lookupArray = Functions::flattenArray($lookupArray); 466 $lookupValue = Functions::flattenSingleValue($lookupValue); 467 $matchType = ($matchType === null) ? 1 : (int) Functions::flattenSingleValue($matchType); 468 469 // MATCH is not case sensitive, so we convert lookup value to be lower cased in case it's string type. 470 if (is_string($lookupValue)) { 471 $lookupValue = StringHelper::strToLower($lookupValue); 472 } 473 474 // Lookup_value type has to be number, text, or logical values 475 if ((!is_numeric($lookupValue)) && (!is_string($lookupValue)) && (!is_bool($lookupValue))) { 476 return Functions::NA(); 477 } 478 479 // Match_type is 0, 1 or -1 480 if (($matchType !== 0) && ($matchType !== -1) && ($matchType !== 1)) { 481 return Functions::NA(); 482 } 483 484 // Lookup_array should not be empty 485 $lookupArraySize = count($lookupArray); 486 if ($lookupArraySize <= 0) { 487 return Functions::NA(); 488 } 489 490 if ($matchType == 1) { 491 // If match_type is 1 the list has to be processed from last to first 492 493 $lookupArray = array_reverse($lookupArray); 494 $keySet = array_reverse(array_keys($lookupArray)); 495 } 496 497 // Lookup_array should contain only number, text, or logical values, or empty (null) cells 498 foreach ($lookupArray as $i => $lookupArrayValue) { 499 // check the type of the value 500 if ( 501 (!is_numeric($lookupArrayValue)) && (!is_string($lookupArrayValue)) && 502 (!is_bool($lookupArrayValue)) && ($lookupArrayValue !== null) 503 ) { 504 return Functions::NA(); 505 } 506 // Convert strings to lowercase for case-insensitive testing 507 if (is_string($lookupArrayValue)) { 508 $lookupArray[$i] = StringHelper::strToLower($lookupArrayValue); 509 } 510 if (($lookupArrayValue === null) && (($matchType == 1) || ($matchType == -1))) { 511 unset($lookupArray[$i]); 512 } 513 } 514 515 // ** 516 // find the match 517 // ** 518 519 if ($matchType === 0 || $matchType === 1) { 520 foreach ($lookupArray as $i => $lookupArrayValue) { 521 $typeMatch = ((gettype($lookupValue) === gettype($lookupArrayValue)) || (is_numeric($lookupValue) && is_numeric($lookupArrayValue))); 522 $exactTypeMatch = $typeMatch && $lookupArrayValue === $lookupValue; 523 $nonOnlyNumericExactMatch = !$typeMatch && $lookupArrayValue === $lookupValue; 524 $exactMatch = $exactTypeMatch || $nonOnlyNumericExactMatch; 525 526 if ($matchType === 0) { 527 if ($typeMatch && is_string($lookupValue) && (bool) preg_match('/([\?\*])/', $lookupValue)) { 528 $splitString = $lookupValue; 529 $chars = array_map(function ($i) use ($splitString) { 530 return mb_substr($splitString, $i, 1); 531 }, range(0, mb_strlen($splitString) - 1)); 532 533 $length = count($chars); 534 $pattern = '/^'; 535 for ($j = 0; $j < $length; ++$j) { 536 if ($chars[$j] === '~') { 537 if (isset($chars[$j + 1])) { 538 if ($chars[$j + 1] === '*') { 539 $pattern .= preg_quote($chars[$j + 1], '/'); 540 ++$j; 541 } elseif ($chars[$j + 1] === '?') { 542 $pattern .= preg_quote($chars[$j + 1], '/'); 543 ++$j; 544 } 545 } else { 546 $pattern .= preg_quote($chars[$j], '/'); 547 } 548 } elseif ($chars[$j] === '*') { 549 $pattern .= '.*'; 550 } elseif ($chars[$j] === '?') { 551 $pattern .= '.{1}'; 552 } else { 553 $pattern .= preg_quote($chars[$j], '/'); 554 } 555 } 556 557 $pattern .= '$/'; 558 if ((bool) preg_match($pattern, $lookupArrayValue)) { 559 // exact match 560 return $i + 1; 561 } 562 } elseif ($exactMatch) { 563 // exact match 564 return $i + 1; 565 } 566 } elseif (($matchType === 1) && $typeMatch && ($lookupArrayValue <= $lookupValue)) { 567 $i = array_search($i, $keySet); 568 569 // The current value is the (first) match 570 return $i + 1; 571 } 572 } 573 } else { 574 $maxValueKey = null; 575 576 // The basic algorithm is: 577 // Iterate and keep the highest match until the next element is smaller than the searched value. 578 // Return immediately if perfect match is found 579 foreach ($lookupArray as $i => $lookupArrayValue) { 580 $typeMatch = gettype($lookupValue) === gettype($lookupArrayValue); 581 $exactTypeMatch = $typeMatch && $lookupArrayValue === $lookupValue; 582 $nonOnlyNumericExactMatch = !$typeMatch && $lookupArrayValue === $lookupValue; 583 $exactMatch = $exactTypeMatch || $nonOnlyNumericExactMatch; 584 585 if ($exactMatch) { 586 // Another "special" case. If a perfect match is found, 587 // the algorithm gives up immediately 588 return $i + 1; 589 } elseif ($typeMatch & $lookupArrayValue >= $lookupValue) { 590 $maxValueKey = $i + 1; 591 } elseif ($typeMatch & $lookupArrayValue < $lookupValue) { 592 //Excel algorithm gives up immediately if the first element is smaller than the searched value 593 break; 594 } 595 } 596 597 if ($maxValueKey !== null) { 598 return $maxValueKey; 599 } 600 } 601 602 // Unsuccessful in finding a match, return #N/A error value 603 return Functions::NA(); 604 } 605 606 /** 607 * INDEX. 608 * 609 * Uses an index to choose a value from a reference or array 610 * 611 * Excel Function: 612 * =INDEX(range_array, row_num, [column_num]) 613 * 614 * @param mixed $arrayValues A range of cells or an array constant 615 * @param mixed $rowNum The row in array from which to return a value. If row_num is omitted, column_num is required. 616 * @param mixed $columnNum The column in array from which to return a value. If column_num is omitted, row_num is required. 617 * 618 * @return mixed the value of a specified cell or array of cells 619 */ 620 public static function INDEX($arrayValues, $rowNum = 0, $columnNum = 0) 621 { 622 $rowNum = Functions::flattenSingleValue($rowNum); 623 $columnNum = Functions::flattenSingleValue($columnNum); 624 625 if (($rowNum < 0) || ($columnNum < 0)) { 626 return Functions::VALUE(); 627 } 628 629 if (!is_array($arrayValues) || ($rowNum > count($arrayValues))) { 630 return Functions::REF(); 631 } 632 633 $rowKeys = array_keys($arrayValues); 634 $columnKeys = @array_keys($arrayValues[$rowKeys[0]]); 635 636 if ($columnNum > count($columnKeys)) { 637 return Functions::VALUE(); 638 } elseif ($columnNum == 0) { 639 if ($rowNum == 0) { 640 return $arrayValues; 641 } 642 $rowNum = $rowKeys[--$rowNum]; 643 $returnArray = []; 644 foreach ($arrayValues as $arrayColumn) { 645 if (is_array($arrayColumn)) { 646 if (isset($arrayColumn[$rowNum])) { 647 $returnArray[] = $arrayColumn[$rowNum]; 648 } else { 649 return [$rowNum => $arrayValues[$rowNum]]; 650 } 651 } else { 652 return $arrayValues[$rowNum]; 653 } 654 } 655 656 return $returnArray; 657 } 658 $columnNum = $columnKeys[--$columnNum]; 659 if ($rowNum > count($rowKeys)) { 660 return Functions::VALUE(); 661 } elseif ($rowNum == 0) { 662 return $arrayValues[$columnNum]; 663 } 664 $rowNum = $rowKeys[--$rowNum]; 665 666 return $arrayValues[$rowNum][$columnNum]; 667 } 668 669 /** 670 * TRANSPOSE. 671 * 672 * @param array $matrixData A matrix of values 673 * 674 * @return array 675 * 676 * Unlike the Excel TRANSPOSE function, which will only work on a single row or column, this function will transpose a full matrix 677 */ 678 public static function TRANSPOSE($matrixData) 679 { 680 $returnMatrix = []; 681 if (!is_array($matrixData)) { 682 $matrixData = [[$matrixData]]; 683 } 684 685 $column = 0; 686 foreach ($matrixData as $matrixRow) { 687 $row = 0; 688 foreach ($matrixRow as $matrixCell) { 689 $returnMatrix[$row][$column] = $matrixCell; 690 ++$row; 691 } 692 ++$column; 693 } 694 695 return $returnMatrix; 696 } 697 698 private static function vlookupSort($a, $b) 699 { 700 reset($a); 701 $firstColumn = key($a); 702 $aLower = StringHelper::strToLower($a[$firstColumn]); 703 $bLower = StringHelper::strToLower($b[$firstColumn]); 704 if ($aLower == $bLower) { 705 return 0; 706 } 707 708 return ($aLower < $bLower) ? -1 : 1; 709 } 710 711 /** 712 * VLOOKUP 713 * 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. 714 * 715 * @param mixed $lookup_value The value that you want to match in lookup_array 716 * @param mixed $lookup_array The range of cells being searched 717 * @param mixed $index_number The column number in table_array from which the matching value must be returned. The first column is 1. 718 * @param mixed $not_exact_match determines if you are looking for an exact match based on lookup_value 719 * 720 * @return mixed The value of the found cell 721 */ 722 public static function VLOOKUP($lookup_value, $lookup_array, $index_number, $not_exact_match = true) 723 { 724 $lookup_value = Functions::flattenSingleValue($lookup_value); 725 $index_number = Functions::flattenSingleValue($index_number); 726 $not_exact_match = Functions::flattenSingleValue($not_exact_match); 727 728 // index_number must be greater than or equal to 1 729 if ($index_number < 1) { 730 return Functions::VALUE(); 731 } 732 733 // index_number must be less than or equal to the number of columns in lookup_array 734 if ((!is_array($lookup_array)) || (empty($lookup_array))) { 735 return Functions::REF(); 736 } 737 $f = array_keys($lookup_array); 738 $firstRow = array_pop($f); 739 if ((!is_array($lookup_array[$firstRow])) || ($index_number > count($lookup_array[$firstRow]))) { 740 return Functions::REF(); 741 } 742 $columnKeys = array_keys($lookup_array[$firstRow]); 743 $returnColumn = $columnKeys[--$index_number]; 744 $firstColumn = array_shift($columnKeys); 745 746 if (!$not_exact_match) { 747 uasort($lookup_array, ['self', 'vlookupSort']); 748 } 749 750 $lookupLower = StringHelper::strToLower($lookup_value); 751 $rowNumber = $rowValue = false; 752 foreach ($lookup_array as $rowKey => $rowData) { 753 $firstLower = StringHelper::strToLower($rowData[$firstColumn]); 754 755 // break if we have passed possible keys 756 if ( 757 (is_numeric($lookup_value) && is_numeric($rowData[$firstColumn]) && ($rowData[$firstColumn] > $lookup_value)) || 758 (!is_numeric($lookup_value) && !is_numeric($rowData[$firstColumn]) && ($firstLower > $lookupLower)) 759 ) { 760 break; 761 } 762 // remember the last key, but only if datatypes match 763 if ( 764 (is_numeric($lookup_value) && is_numeric($rowData[$firstColumn])) || 765 (!is_numeric($lookup_value) && !is_numeric($rowData[$firstColumn])) 766 ) { 767 if ($not_exact_match) { 768 $rowNumber = $rowKey; 769 770 continue; 771 } elseif ( 772 ($firstLower == $lookupLower) 773 // Spreadsheets software returns first exact match, 774 // we have sorted and we might have broken key orders 775 // we want the first one (by its initial index) 776 && (($rowNumber == false) || ($rowKey < $rowNumber)) 777 ) { 778 $rowNumber = $rowKey; 779 } 780 } 781 } 782 783 if ($rowNumber !== false) { 784 // return the appropriate value 785 return $lookup_array[$rowNumber][$returnColumn]; 786 } 787 788 return Functions::NA(); 789 } 790 791 /** 792 * HLOOKUP 793 * 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. 794 * 795 * @param mixed $lookup_value The value that you want to match in lookup_array 796 * @param mixed $lookup_array The range of cells being searched 797 * @param mixed $index_number The row number in table_array from which the matching value must be returned. The first row is 1. 798 * @param mixed $not_exact_match determines if you are looking for an exact match based on lookup_value 799 * 800 * @return mixed The value of the found cell 801 */ 802 public static function HLOOKUP($lookup_value, $lookup_array, $index_number, $not_exact_match = true) 803 { 804 $lookup_value = Functions::flattenSingleValue($lookup_value); 805 $index_number = Functions::flattenSingleValue($index_number); 806 $not_exact_match = Functions::flattenSingleValue($not_exact_match); 807 808 // index_number must be greater than or equal to 1 809 if ($index_number < 1) { 810 return Functions::VALUE(); 811 } 812 813 // index_number must be less than or equal to the number of columns in lookup_array 814 if ((!is_array($lookup_array)) || (empty($lookup_array))) { 815 return Functions::REF(); 816 } 817 $f = array_keys($lookup_array); 818 $firstRow = reset($f); 819 if ((!is_array($lookup_array[$firstRow])) || ($index_number > count($lookup_array))) { 820 return Functions::REF(); 821 } 822 823 $firstkey = $f[0] - 1; 824 $returnColumn = $firstkey + $index_number; 825 $firstColumn = array_shift($f); 826 $rowNumber = null; 827 foreach ($lookup_array[$firstColumn] as $rowKey => $rowData) { 828 // break if we have passed possible keys 829 $bothNumeric = is_numeric($lookup_value) && is_numeric($rowData); 830 $bothNotNumeric = !is_numeric($lookup_value) && !is_numeric($rowData); 831 $lookupLower = StringHelper::strToLower($lookup_value); 832 $rowDataLower = StringHelper::strToLower($rowData); 833 834 if ( 835 $not_exact_match && ( 836 ($bothNumeric && $rowData > $lookup_value) || 837 ($bothNotNumeric && $rowDataLower > $lookupLower) 838 ) 839 ) { 840 break; 841 } 842 843 // Remember the last key, but only if datatypes match (as in VLOOKUP) 844 if ($bothNumeric || $bothNotNumeric) { 845 if ($not_exact_match) { 846 $rowNumber = $rowKey; 847 848 continue; 849 } elseif ( 850 $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 }
title
Description
Body
title
Description
Body
title
Description
Body
title
Body