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