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