ILIAS  release_5-2 Revision v5.2.25-18-g3f80b828510
LookupRef.php
Go to the documentation of this file.
1 <?php
30 if (!defined('PHPEXCEL_ROOT')) {
34  define('PHPEXCEL_ROOT', dirname(__FILE__) . '/../../');
35  require(PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php');
36 }
37 
38 
46 class PHPExcel_Calculation_LookupRef {
47 
48 
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 
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 
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 
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 
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 
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 
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 
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 
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 
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 
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 
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 
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  return $lookup_array[$rowNumber][$returnColumn];
739  }
740  }
741 
742  return PHPExcel_Calculation_Functions::NA();
743  } // function VLOOKUP()
744 
745 
755  public static function HLOOKUP($lookup_value, $lookup_array, $index_number, $not_exact_match=true) {
756  $lookup_value = PHPExcel_Calculation_Functions::flattenSingleValue($lookup_value);
757  $index_number = PHPExcel_Calculation_Functions::flattenSingleValue($index_number);
758  $not_exact_match = PHPExcel_Calculation_Functions::flattenSingleValue($not_exact_match);
759 
760  // index_number must be greater than or equal to 1
761  if ($index_number < 1) {
762  return PHPExcel_Calculation_Functions::VALUE();
763  }
764 
765  // index_number must be less than or equal to the number of columns in lookup_array
766  if ((!is_array($lookup_array)) || (empty($lookup_array))) {
767  return PHPExcel_Calculation_Functions::REF();
768  } else {
769  $f = array_keys($lookup_array);
770  $firstRow = array_pop($f);
771  if ((!is_array($lookup_array[$firstRow])) || ($index_number > count($lookup_array[$firstRow]))) {
772  return PHPExcel_Calculation_Functions::REF();
773  } else {
774  $columnKeys = array_keys($lookup_array[$firstRow]);
775  $firstkey = $f[0] - 1;
776  $returnColumn = $firstkey + $index_number;
777  $firstColumn = array_shift($f);
778  }
779  }
780 
781  if (!$not_exact_match) {
782  $firstRowH = asort($lookup_array[$firstColumn]);
783  }
784 
785  $rowNumber = $rowValue = False;
786  foreach($lookup_array[$firstColumn] as $rowKey => $rowData) {
787  if ((is_numeric($lookup_value) && is_numeric($rowData) && ($rowData > $lookup_value)) ||
788  (!is_numeric($lookup_value) && !is_numeric($rowData) && (strtolower($rowData) > strtolower($lookup_value)))) {
789  break;
790  }
791  $rowNumber = $rowKey;
792  $rowValue = $rowData;
793  }
794 
795  if ($rowNumber !== false) {
796  if ((!$not_exact_match) && ($rowValue != $lookup_value)) {
797  // if an exact match is required, we have what we need to return an appropriate response
798  return PHPExcel_Calculation_Functions::NA();
799  } else {
800  // otherwise return the appropriate value
801  return $lookup_array[$returnColumn][$rowNumber];
802  }
803  }
804 
805  return PHPExcel_Calculation_Functions::NA();
806  } // function HLOOKUP()
807 
808 
817  public static function LOOKUP($lookup_value, $lookup_vector, $result_vector=null) {
818  $lookup_value = PHPExcel_Calculation_Functions::flattenSingleValue($lookup_value);
819 
820  if (!is_array($lookup_vector)) {
821  return PHPExcel_Calculation_Functions::NA();
822  }
823  $lookupRows = count($lookup_vector);
824  $l = array_keys($lookup_vector);
825  $l = array_shift($l);
826  $lookupColumns = count($lookup_vector[$l]);
827  if ((($lookupRows == 1) && ($lookupColumns > 1)) || (($lookupRows == 2) && ($lookupColumns != 2))) {
828  $lookup_vector = self::TRANSPOSE($lookup_vector);
829  $lookupRows = count($lookup_vector);
830  $l = array_keys($lookup_vector);
831  $lookupColumns = count($lookup_vector[array_shift($l)]);
832  }
833 
834  if (is_null($result_vector)) {
835  $result_vector = $lookup_vector;
836  }
837  $resultRows = count($result_vector);
838  $l = array_keys($result_vector);
839  $l = array_shift($l);
840  $resultColumns = count($result_vector[$l]);
841  if ((($resultRows == 1) && ($resultColumns > 1)) || (($resultRows == 2) && ($resultColumns != 2))) {
842  $result_vector = self::TRANSPOSE($result_vector);
843  $resultRows = count($result_vector);
844  $r = array_keys($result_vector);
845  $resultColumns = count($result_vector[array_shift($r)]);
846  }
847 
848  if ($lookupRows == 2) {
849  $result_vector = array_pop($lookup_vector);
850  $lookup_vector = array_shift($lookup_vector);
851  }
852  if ($lookupColumns != 2) {
853  foreach($lookup_vector as &$value) {
854  if (is_array($value)) {
855  $k = array_keys($value);
856  $key1 = $key2 = array_shift($k);
857  $key2++;
858  $dataValue1 = $value[$key1];
859  } else {
860  $key1 = 0;
861  $key2 = 1;
862  $dataValue1 = $value;
863  }
864  $dataValue2 = array_shift($result_vector);
865  if (is_array($dataValue2)) {
866  $dataValue2 = array_shift($dataValue2);
867  }
868  $value = array($key1 => $dataValue1, $key2 => $dataValue2);
869  }
870  unset($value);
871  }
872 
873  return self::VLOOKUP($lookup_value,$lookup_vector,2);
874  } // function LOOKUP()
875 
876 } // class PHPExcel_Calculation_LookupRef
PHPExcel_Calculation_LookupRef\MATCH
static MATCH($lookup_value, $lookup_array, $match_type=1)
MATCH.
Definition: LookupRef.php:490
PHPExcel_Calculation_LookupRef\ROW
static ROW($cellAddress=Null)
ROW.
Definition: LookupRef.php:186
PHPExcel_Calculation_Functions\flattenSingleValue
static flattenSingleValue($value='')
Convert an array to a single scalar value by extracting the first element.
Definition: Functions.php:662
PHPExcel_Cell\coordinateFromString
static coordinateFromString($pCoordinateString='A1')
Coordinate from string.
Definition: Cell.php:580
$x
$x
Definition: example_009.php:98
PHPExcel_Calculation_LookupRef\CHOOSE
static CHOOSE()
CHOOSE.
Definition: LookupRef.php:451
PHPExcel_Calculation_LookupRef\INDEX
static INDEX($arrayValues, $rowNum=0, $columnNum=0)
INDEX.
Definition: LookupRef.php:603
PHPExcel_Calculation_LookupRef\_vlookupSort
static _vlookupSort($a, $b)
Definition: LookupRef.php:674
PHPExcel_Calculation_LookupRef\INDIRECT
static INDIRECT($cellAddress=NULL, PHPExcel_Cell $pCell=NULL)
INDIRECT.
Definition: LookupRef.php:298
PHPExcel_Calculation_LookupRef\LOOKUP
static LOOKUP($lookup_value, $lookup_vector, $result_vector=null)
LOOKUP The LOOKUP function searches for value either from a one-row or one-column range or from an ar...
Definition: LookupRef.php:817
PHPExcel_Calculation_Functions\flattenArray
static flattenArray($array)
Convert a multi-dimensional array to a simple 1-dimensional array.
Definition: Functions.php:598
PHPExcel_Calculation_LookupRef\OFFSET
static OFFSET($cellAddress=Null, $rows=0, $columns=0, $height=null, $width=null)
OFFSET.
Definition: LookupRef.php:364
PHPExcel_Calculation_Functions\VALUE
static VALUE()
Definition: Functions.php:289
PHPExcel_Calculation\_getMatrixDimensions
static _getMatrixDimensions(&$matrix)
Read the dimensions of a matrix, and re-index it with straight numeric keys starting from row 0...
Definition: Calculation.php:2474
PHPExcel_Calculation_LookupRef\TRANSPOSE
static TRANSPOSE($matrixData)
TRANSPOSE.
Definition: LookupRef.php:657
PHPExcel_Calculation_LookupRef
Definition: LookupRef.php:46
$r
$r
Definition: example_031.php:79
$column
$column
Definition: 39dropdown.php:62
PHPExcel_Calculation_LookupRef\CELL_ADDRESS
static CELL_ADDRESS($row, $column, $relativity=1, $referenceStyle=True, $sheetText='')
CELL_ADDRESS.
Definition: LookupRef.php:70
PHPExcel_Calculation_LookupRef\COLUMN
static COLUMN($cellAddress=Null)
COLUMN.
Definition: LookupRef.php:112
PHPExcel_Calculation_LookupRef\VLOOKUP
static VLOOKUP($lookup_value, $lookup_array, $index_number, $not_exact_match=true)
VLOOKUP The VLOOKUP function searches for value in the left-most column of lookup_array and returns t...
Definition: LookupRef.php:693
PHPExcel_Calculation_LookupRef\ROWS
static ROWS($cellAddress=Null)
ROWS.
Definition: LookupRef.php:227
PHPExcel_Calculation_LookupRef\COLUMNS
static COLUMNS($cellAddress=Null)
COLUMNS.
Definition: LookupRef.php:152
PHPExcel_Calculation_Functions\NA
static NA()
Definition: Functions.php:219
array
Create styles array
The data for the language used.
Definition: 40duplicateStyle.php:19
PHPExcel_Cell\columnIndexFromString
static columnIndexFromString($pString='A')
Column index from string.
Definition: Cell.php:782
PHPExcel_Cell
PHPExcel_Calculation\getInstance
static getInstance(PHPExcel $workbook=NULL)
Get an instance of this class.
Definition: Calculation.php:1765
$l
global $l
Definition: afr.php:30
PHPExcel_Calculation_Functions\REF
static REF()
Definition: Functions.php:261
PHPExcel_Calculation\CALCULATION_REGEXP_CELLREF
const CALCULATION_REGEXP_CELLREF
Definition: Calculation.php:75
PHPExcel_Cell\stringFromColumnIndex
static stringFromColumnIndex($pColumnIndex=0)
String from columnindex.
Definition: Cell.php:825
PHPExcel_Calculation_LookupRef\HLOOKUP
static HLOOKUP($lookup_value, $lookup_array, $index_number, $not_exact_match=true)
HLOOKUP The HLOOKUP function searches for value in the top-most row of lookup_array and returns the v...
Definition: LookupRef.php:755
defined
defined( 'APPLICATION_ENV')||define( 'APPLICATION_ENV'
Definition: bootstrap.php:27
$columns
if(! $in) $columns
Definition: Utf8Test.php:45
PHPExcel_Calculation\CALCULATION_REGEXP_NAMEDRANGE
const CALCULATION_REGEXP_NAMEDRANGE
Definition: Calculation.php:77
PHPExcel_Calculation_LookupRef\HYPERLINK
static HYPERLINK($linkURL='', $displayName=null, PHPExcel_Cell $pCell=null)
Definition: LookupRef.php:259