ILIAS  release_5-3 Revision v5.3.23-19-g915713cf615
LookupRef.php
Go to the documentation of this file.
1<?php
30if (!defined('PHPEXCEL_ROOT')) {
34 define('PHPEXCEL_ROOT', dirname(__FILE__) . '/../../');
35 require(PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php');
36}
37
38
46class 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
$column
$column
Definition: 39dropdown.php:62
$columns
if(! $in) $columns
Definition: Utf8Test.php:45
$l
global $l
Definition: afr.php:30
php
An exception for terminatinating execution or to throw for unit testing.
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_Calculation_Functions\REF
static REF()
Definition: Functions.php:261
PHPExcel_Calculation_Functions\flattenArray
static flattenArray($array)
Convert a multi-dimensional array to a simple 1-dimensional array.
Definition: Functions.php:598
PHPExcel_Calculation_Functions\NA
static NA()
Definition: Functions.php:219
PHPExcel_Calculation_Functions\VALUE
static VALUE()
Definition: Functions.php:289
PHPExcel_Calculation_LookupRef
Definition: LookupRef.php:46
PHPExcel_Calculation_LookupRef\MATCH
static MATCH($lookup_value, $lookup_array, $match_type=1)
MATCH.
Definition: LookupRef.php:490
PHPExcel_Calculation_LookupRef\INDIRECT
static INDIRECT($cellAddress=NULL, PHPExcel_Cell $pCell=NULL)
INDIRECT.
Definition: LookupRef.php:298
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\COLUMN
static COLUMN($cellAddress=Null)
COLUMN.
Definition: LookupRef.php:112
PHPExcel_Calculation_LookupRef\CHOOSE
static CHOOSE()
CHOOSE.
Definition: LookupRef.php:451
PHPExcel_Calculation_LookupRef\_vlookupSort
static _vlookupSort($a, $b)
Definition: LookupRef.php:674
PHPExcel_Calculation_LookupRef\INDEX
static INDEX($arrayValues, $rowNum=0, $columnNum=0)
INDEX.
Definition: LookupRef.php:603
PHPExcel_Calculation_LookupRef\OFFSET
static OFFSET($cellAddress=Null, $rows=0, $columns=0, $height=null, $width=null)
OFFSET.
Definition: LookupRef.php:364
PHPExcel_Calculation_LookupRef\CELL_ADDRESS
static CELL_ADDRESS($row, $column, $relativity=1, $referenceStyle=True, $sheetText='')
CELL_ADDRESS.
Definition: LookupRef.php:70
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_LookupRef\ROW
static ROW($cellAddress=Null)
ROW.
Definition: LookupRef.php:186
PHPExcel_Calculation_LookupRef\HYPERLINK
static HYPERLINK($linkURL='', $displayName=null, PHPExcel_Cell $pCell=null)
Definition: LookupRef.php:259
PHPExcel_Calculation_LookupRef\ROWS
static ROWS($cellAddress=Null)
ROWS.
Definition: LookupRef.php:227
PHPExcel_Calculation_LookupRef\TRANSPOSE
static TRANSPOSE($matrixData)
TRANSPOSE.
Definition: LookupRef.php:657
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
PHPExcel_Calculation_LookupRef\COLUMNS
static COLUMNS($cellAddress=Null)
COLUMNS.
Definition: LookupRef.php:152
PHPExcel_Calculation\getInstance
static getInstance(PHPExcel $workbook=NULL)
Get an instance of this class.
Definition: Calculation.php:1765
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\CALCULATION_REGEXP_CELLREF
const CALCULATION_REGEXP_CELLREF
Definition: Calculation.php:75
PHPExcel_Calculation\CALCULATION_REGEXP_NAMEDRANGE
const CALCULATION_REGEXP_NAMEDRANGE
Definition: Calculation.php:77
PHPExcel_Cell\stringFromColumnIndex
static stringFromColumnIndex($pColumnIndex=0)
String from columnindex.
Definition: Cell.php:825
PHPExcel_Cell\coordinateFromString
static coordinateFromString($pCoordinateString='A1')
Coordinate from string.
Definition: Cell.php:580
PHPExcel_Cell\columnIndexFromString
static columnIndexFromString($pString='A')
Column index from string.
Definition: Cell.php:782
$i
$i
Definition: disco.tpl.php:19
$x
$x
Definition: example_009.php:98
$r
$r
Definition: example_031.php:79
PHPExcel_Cell
defined
defined( 'APPLICATION_ENV')||define( 'APPLICATION_ENV'
Definition: bootstrap.php:27
$rows
$rows
Definition: xhr_table.php:10