ILIAS  eassessment Revision 61809
 All Data Structures Namespaces Files Functions Variables Groups Pages
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 
67  public static function CELL_ADDRESS($row, $column, $relativity=1, $referenceStyle=True, $sheetText='') {
68  $row = PHPExcel_Calculation_Functions::flattenSingleValue($row);
69  $column = PHPExcel_Calculation_Functions::flattenSingleValue($column);
70  $relativity = PHPExcel_Calculation_Functions::flattenSingleValue($relativity);
71  $sheetText = PHPExcel_Calculation_Functions::flattenSingleValue($sheetText);
72 
73  if (($row < 1) || ($column < 1)) {
74  return PHPExcel_Calculation_Functions::VALUE();
75  }
76 
77  if ($sheetText > '') {
78  if (strpos($sheetText,' ') !== False) { $sheetText = "'".$sheetText."'"; }
79  $sheetText .='!';
80  }
81  if ((!is_bool($referenceStyle)) || $referenceStyle) {
82  $rowRelative = $columnRelative = '$';
83  $column = PHPExcel_Cell::stringFromColumnIndex($column-1);
84  if (($relativity == 2) || ($relativity == 4)) { $columnRelative = ''; }
85  if (($relativity == 3) || ($relativity == 4)) { $rowRelative = ''; }
86  return $sheetText.$columnRelative.$column.$rowRelative.$row;
87  } else {
88  if (($relativity == 2) || ($relativity == 4)) { $column = '['.$column.']'; }
89  if (($relativity == 3) || ($relativity == 4)) { $row = '['.$row.']'; }
90  return $sheetText.'R'.$row.'C'.$column;
91  }
92  } // function CELL_ADDRESS()
93 
94 
106  public static function COLUMN($cellAddress=Null) {
107  if (is_null($cellAddress) || trim($cellAddress) === '') { return 0; }
108 
109  if (is_array($cellAddress)) {
110  foreach($cellAddress as $columnKey => $value) {
111  $columnKey = preg_replace('/[^a-z]/i','',$columnKey);
112  return (integer) PHPExcel_Cell::columnIndexFromString($columnKey);
113  }
114  } else {
115  if (strpos($cellAddress,'!') !== false) {
116  list($sheet,$cellAddress) = explode('!',$cellAddress);
117  }
118  if (strpos($cellAddress,':') !== false) {
119  list($startAddress,$endAddress) = explode(':',$cellAddress);
120  $startAddress = preg_replace('/[^a-z]/i','',$startAddress);
121  $endAddress = preg_replace('/[^a-z]/i','',$endAddress);
122  $returnValue = array();
123  do {
124  $returnValue[] = (integer) PHPExcel_Cell::columnIndexFromString($startAddress);
125  } while ($startAddress++ != $endAddress);
126  return $returnValue;
127  } else {
128  $cellAddress = preg_replace('/[^a-z]/i','',$cellAddress);
129  return (integer) PHPExcel_Cell::columnIndexFromString($cellAddress);
130  }
131  }
132  } // function COLUMN()
133 
134 
143  public static function COLUMNS($cellAddress=Null) {
144  if (is_null($cellAddress) || $cellAddress === '') {
145  return 1;
146  } elseif (!is_array($cellAddress)) {
147  return PHPExcel_Calculation_Functions::VALUE();
148  }
149 
150  $x = array_keys($cellAddress);
151  $x = array_shift($x);
152  $isMatrix = (is_numeric($x));
153  list($columns,$rows) = PHPExcel_Calculation::_getMatrixDimensions($cellAddress);
154 
155  if ($isMatrix) {
156  return $rows;
157  } else {
158  return $columns;
159  }
160  } // function COLUMNS()
161 
162 
174  public static function ROW($cellAddress=Null) {
175  if (is_null($cellAddress) || trim($cellAddress) === '') { return 0; }
176 
177  if (is_array($cellAddress)) {
178  foreach($cellAddress as $columnKey => $rowValue) {
179  foreach($rowValue as $rowKey => $cellValue) {
180  return (integer) preg_replace('/[^0-9]/i','',$rowKey);
181  }
182  }
183  } else {
184  if (strpos($cellAddress,'!') !== false) {
185  list($sheet,$cellAddress) = explode('!',$cellAddress);
186  }
187  if (strpos($cellAddress,':') !== false) {
188  list($startAddress,$endAddress) = explode(':',$cellAddress);
189  $startAddress = preg_replace('/[^0-9]/','',$startAddress);
190  $endAddress = preg_replace('/[^0-9]/','',$endAddress);
191  $returnValue = array();
192  do {
193  $returnValue[][] = (integer) $startAddress;
194  } while ($startAddress++ != $endAddress);
195  return $returnValue;
196  } else {
197  list($cellAddress) = explode(':',$cellAddress);
198  return (integer) preg_replace('/[^0-9]/','',$cellAddress);
199  }
200  }
201  } // function ROW()
202 
203 
212  public static function ROWS($cellAddress=Null) {
213  if (is_null($cellAddress) || $cellAddress === '') {
214  return 1;
215  } elseif (!is_array($cellAddress)) {
216  return PHPExcel_Calculation_Functions::VALUE();
217  }
218 
219  $i = array_keys($cellAddress);
220  $isMatrix = (is_numeric(array_shift($i)));
221  list($columns,$rows) = PHPExcel_Calculation::_getMatrixDimensions($cellAddress);
222 
223  if ($isMatrix) {
224  return $columns;
225  } else {
226  return $rows;
227  }
228  } // function ROWS()
229 
230 
243  public static function HYPERLINK($linkURL = '', $displayName = null, PHPExcel_Cell $pCell = null) {
244  $args = func_get_args();
245  $pCell = array_pop($args);
246 
247  $linkURL = (is_null($linkURL)) ? '' : PHPExcel_Calculation_Functions::flattenSingleValue($linkURL);
248  $displayName = (is_null($displayName)) ? '' : PHPExcel_Calculation_Functions::flattenSingleValue($displayName);
249 
250  if ((!is_object($pCell)) || (trim($linkURL) == '')) {
251  return PHPExcel_Calculation_Functions::REF();
252  }
253 
254  if ((is_object($displayName)) || trim($displayName) == '') {
255  $displayName = $linkURL;
256  }
257 
258  $pCell->getHyperlink()->setUrl($linkURL);
259 
260  return $displayName;
261  } // function HYPERLINK()
262 
263 
272  public static function INDIRECT($cellAddress=Null, PHPExcel_Cell $pCell = null) {
273  $cellAddress = PHPExcel_Calculation_Functions::flattenSingleValue($cellAddress);
274  if (is_null($cellAddress) || $cellAddress === '') {
275  return PHPExcel_Calculation_Functions::REF();
276  }
277 
278  $cellAddress1 = $cellAddress;
279  $cellAddress2 = NULL;
280  if (strpos($cellAddress,':') !== false) {
281  list($cellAddress1,$cellAddress2) = explode(':',$cellAddress);
282  }
283 
284  if ((!preg_match('/^'.PHPExcel_Calculation::CALCULATION_REGEXP_CELLREF.'$/i', $cellAddress1, $matches)) ||
285  ((!is_null($cellAddress2)) && (!preg_match('/^'.PHPExcel_Calculation::CALCULATION_REGEXP_CELLREF.'$/i', $cellAddress2, $matches)))) {
286  return PHPExcel_Calculation_Functions::REF();
287  }
288 
289  if (strpos($cellAddress,'!') !== false) {
290  list($sheetName,$cellAddress) = explode('!',$cellAddress);
291  $pSheet = $pCell->getParent()->getParent()->getSheetByName($sheetName);
292  } else {
293  $pSheet = $pCell->getParent();
294  }
295 
296  return PHPExcel_Calculation::getInstance()->extractCellRange($cellAddress, $pSheet, False);
297  } // function INDIRECT()
298 
299 
322  public static function OFFSET($cellAddress=Null,$rows=0,$columns=0,$height=null,$width=null) {
323  $rows = PHPExcel_Calculation_Functions::flattenSingleValue($rows);
324  $columns = PHPExcel_Calculation_Functions::flattenSingleValue($columns);
325  $height = PHPExcel_Calculation_Functions::flattenSingleValue($height);
326  $width = PHPExcel_Calculation_Functions::flattenSingleValue($width);
327  if ($cellAddress == Null) {
328  return 0;
329  }
330 
331  $args = func_get_args();
332  $pCell = array_pop($args);
333  if (!is_object($pCell)) {
334  return PHPExcel_Calculation_Functions::REF();
335  }
336 
337  $sheetName = null;
338  if (strpos($cellAddress,"!")) {
339  list($sheetName,$cellAddress) = explode("!",$cellAddress);
340  }
341  if (strpos($cellAddress,":")) {
342  list($startCell,$endCell) = explode(":",$cellAddress);
343  } else {
344  $startCell = $endCell = $cellAddress;
345  }
346  list($startCellColumn,$startCellRow) = PHPExcel_Cell::coordinateFromString($startCell);
347  list($endCellColumn,$endCellRow) = PHPExcel_Cell::coordinateFromString($endCell);
348 
349  $startCellRow += $rows;
350  $startCellColumn = PHPExcel_Cell::columnIndexFromString($startCellColumn) - 1;
351  $startCellColumn += $columns;
352 
353  if (($startCellRow <= 0) || ($startCellColumn < 0)) {
354  return PHPExcel_Calculation_Functions::REF();
355  }
356  $endCellColumn = PHPExcel_Cell::columnIndexFromString($endCellColumn) - 1;
357  if (($width != null) && (!is_object($width))) {
358  $endCellColumn = $startCellColumn + $width - 1;
359  } else {
360  $endCellColumn += $columns;
361  }
362  $startCellColumn = PHPExcel_Cell::stringFromColumnIndex($startCellColumn);
363 
364  if (($height != null) && (!is_object($height))) {
365  $endCellRow = $startCellRow + $height - 1;
366  } else {
367  $endCellRow += $rows;
368  }
369 
370  if (($endCellRow <= 0) || ($endCellColumn < 0)) {
371  return PHPExcel_Calculation_Functions::REF();
372  }
373  $endCellColumn = PHPExcel_Cell::stringFromColumnIndex($endCellColumn);
374 
375  $cellAddress = $startCellColumn.$startCellRow;
376  if (($startCellColumn != $endCellColumn) || ($startCellRow != $endCellRow)) {
377  $cellAddress .= ':'.$endCellColumn.$endCellRow;
378  }
379 
380  if ($sheetName !== null) {
381  $pSheet = $pCell->getParent()->getParent()->getSheetByName($sheetName);
382  } else {
383  $pSheet = $pCell->getParent();
384  }
385 
386  return PHPExcel_Calculation::getInstance()->extractCellRange($cellAddress, $pSheet, False);
387  } // function OFFSET()
388 
389 
390  public static function CHOOSE() {
391  $chooseArgs = func_get_args();
392  $chosenEntry = PHPExcel_Calculation_Functions::flattenArray(array_shift($chooseArgs));
393  $entryCount = count($chooseArgs) - 1;
394 
395  if(is_array($chosenEntry)) {
396  $chosenEntry = array_shift($chosenEntry);
397  }
398  if ((is_numeric($chosenEntry)) && (!is_bool($chosenEntry))) {
399  --$chosenEntry;
400  } else {
401  return PHPExcel_Calculation_Functions::VALUE();
402  }
403  $chosenEntry = floor($chosenEntry);
404  if (($chosenEntry <= 0) || ($chosenEntry > $entryCount)) {
405  return PHPExcel_Calculation_Functions::VALUE();
406  }
407 
408  if (is_array($chooseArgs[$chosenEntry])) {
409  return PHPExcel_Calculation_Functions::flattenArray($chooseArgs[$chosenEntry]);
410  } else {
411  return $chooseArgs[$chosenEntry];
412  }
413  } // function CHOOSE()
414 
415 
426  public static function MATCH($lookup_value, $lookup_array, $match_type=1) {
427  $lookup_array = PHPExcel_Calculation_Functions::flattenArray($lookup_array);
428  $lookup_value = PHPExcel_Calculation_Functions::flattenSingleValue($lookup_value);
429  $match_type = (is_null($match_type)) ? 1 : (int) PHPExcel_Calculation_Functions::flattenSingleValue($match_type);
430  // MATCH is not case sensitive
431  $lookup_value = strtolower($lookup_value);
432 
433  // lookup_value type has to be number, text, or logical values
434  if ((!is_numeric($lookup_value)) && (!is_string($lookup_value)) && (!is_bool($lookup_value))) {
435  return PHPExcel_Calculation_Functions::NA();
436  }
437 
438  // match_type is 0, 1 or -1
439  if (($match_type !== 0) && ($match_type !== -1) && ($match_type !== 1)) {
440  return PHPExcel_Calculation_Functions::NA();
441  }
442 
443  // lookup_array should not be empty
444  $lookupArraySize = count($lookup_array);
445  if ($lookupArraySize <= 0) {
446  return PHPExcel_Calculation_Functions::NA();
447  }
448 
449  // lookup_array should contain only number, text, or logical values, or empty (null) cells
450  foreach($lookup_array as $i => $lookupArrayValue) {
451  // check the type of the value
452  if ((!is_numeric($lookupArrayValue)) && (!is_string($lookupArrayValue)) &&
453  (!is_bool($lookupArrayValue)) && (!is_null($lookupArrayValue))) {
454  return PHPExcel_Calculation_Functions::NA();
455  }
456  // convert strings to lowercase for case-insensitive testing
457  if (is_string($lookupArrayValue)) {
458  $lookup_array[$i] = strtolower($lookupArrayValue);
459  }
460  if ((is_null($lookupArrayValue)) && (($match_type == 1) || ($match_type == -1))) {
461  $lookup_array = array_slice($lookup_array,0,$i-1);
462  }
463  }
464 
465  // if match_type is 1 or -1, the list has to be ordered
466  if ($match_type == 1) {
467  asort($lookup_array);
468  $keySet = array_keys($lookup_array);
469  } elseif($match_type == -1) {
470  arsort($lookup_array);
471  $keySet = array_keys($lookup_array);
472  }
473 
474  // **
475  // find the match
476  // **
477  // loop on the cells
478 // var_dump($lookup_array);
479 // echo '<br />';
480  foreach($lookup_array as $i => $lookupArrayValue) {
481  if (($match_type == 0) && ($lookupArrayValue == $lookup_value)) {
482  // exact match
483  return ++$i;
484  } elseif (($match_type == -1) && ($lookupArrayValue <= $lookup_value)) {
485 // echo '$i = '.$i.' => ';
486 // var_dump($lookupArrayValue);
487 // echo '<br />';
488 // echo 'Keyset = ';
489 // var_dump($keySet);
490 // echo '<br />';
491  $i = array_search($i,$keySet);
492 // echo '$i='.$i.'<br />';
493  // if match_type is -1 <=> find the smallest value that is greater than or equal to lookup_value
494  if ($i < 1){
495  // 1st cell was allready smaller than the lookup_value
496  break;
497  } else {
498  // the previous cell was the match
499  return $keySet[$i-1]+1;
500  }
501  } elseif (($match_type == 1) && ($lookupArrayValue >= $lookup_value)) {
502 // echo '$i = '.$i.' => ';
503 // var_dump($lookupArrayValue);
504 // echo '<br />';
505 // echo 'Keyset = ';
506 // var_dump($keySet);
507 // echo '<br />';
508  $i = array_search($i,$keySet);
509 // echo '$i='.$i.'<br />';
510  // if match_type is 1 <=> find the largest value that is less than or equal to lookup_value
511  if ($i < 1){
512  // 1st cell was allready bigger than the lookup_value
513  break;
514  } else {
515  // the previous cell was the match
516  return $keySet[$i-1]+1;
517  }
518  }
519  }
520 
521  // unsuccessful in finding a match, return #N/A error value
522  return PHPExcel_Calculation_Functions::NA();
523  } // function MATCH()
524 
525 
537  public static function INDEX($arrayValues,$rowNum = 0,$columnNum = 0) {
538 
539  if (($rowNum < 0) || ($columnNum < 0)) {
540  return PHPExcel_Calculation_Functions::VALUE();
541  }
542 
543  if (!is_array($arrayValues)) {
544  return PHPExcel_Calculation_Functions::REF();
545  }
546 
547  $rowKeys = array_keys($arrayValues);
548  $columnKeys = @array_keys($arrayValues[$rowKeys[0]]);
549 
550  if ($columnNum > count($columnKeys)) {
551  return PHPExcel_Calculation_Functions::VALUE();
552  } elseif ($columnNum == 0) {
553  if ($rowNum == 0) {
554  return $arrayValues;
555  }
556  $rowNum = $rowKeys[--$rowNum];
557  $returnArray = array();
558  foreach($arrayValues as $arrayColumn) {
559  if (is_array($arrayColumn)) {
560  if (isset($arrayColumn[$rowNum])) {
561  $returnArray[] = $arrayColumn[$rowNum];
562  } else {
563  return $arrayValues[$rowNum];
564  }
565  } else {
566  return $arrayValues[$rowNum];
567  }
568  }
569  return $returnArray;
570  }
571  $columnNum = $columnKeys[--$columnNum];
572  if ($rowNum > count($rowKeys)) {
573  return PHPExcel_Calculation_Functions::VALUE();
574  } elseif ($rowNum == 0) {
575  return $arrayValues[$columnNum];
576  }
577  $rowNum = $rowKeys[--$rowNum];
578 
579  return $arrayValues[$rowNum][$columnNum];
580  } // function INDEX()
581 
582 
591  public static function TRANSPOSE($matrixData) {
592  $returnMatrix = array();
593  if (!is_array($matrixData)) { $matrixData = array(array($matrixData)); }
594 
595  $column = 0;
596  foreach($matrixData as $matrixRow) {
597  $row = 0;
598  foreach($matrixRow as $matrixCell) {
599  $returnMatrix[$row][$column] = $matrixCell;
600  ++$row;
601  }
602  ++$column;
603  }
604  return $returnMatrix;
605  } // function TRANSPOSE()
606 
607 
608  private static function _vlookupSort($a,$b) {
609  $f = array_keys($a);
610  $firstColumn = array_shift($f);
611  if (strtolower($a[$firstColumn]) == strtolower($b[$firstColumn])) {
612  return 0;
613  }
614  return (strtolower($a[$firstColumn]) < strtolower($b[$firstColumn])) ? -1 : 1;
615  } // function _vlookupSort()
616 
617 
627  public static function VLOOKUP($lookup_value, $lookup_array, $index_number, $not_exact_match=true) {
628  $lookup_value = PHPExcel_Calculation_Functions::flattenSingleValue($lookup_value);
629  $index_number = PHPExcel_Calculation_Functions::flattenSingleValue($index_number);
630  $not_exact_match = PHPExcel_Calculation_Functions::flattenSingleValue($not_exact_match);
631 
632  // index_number must be greater than or equal to 1
633  if ($index_number < 1) {
634  return PHPExcel_Calculation_Functions::VALUE();
635  }
636 
637  // index_number must be less than or equal to the number of columns in lookup_array
638  if ((!is_array($lookup_array)) || (count($lookup_array) < 1)) {
639  return PHPExcel_Calculation_Functions::REF();
640  } else {
641  $f = array_keys($lookup_array);
642  $firstRow = array_pop($f);
643  if ((!is_array($lookup_array[$firstRow])) || ($index_number > count($lookup_array[$firstRow]))) {
644  return PHPExcel_Calculation_Functions::REF();
645  } else {
646  $columnKeys = array_keys($lookup_array[$firstRow]);
647  $returnColumn = $columnKeys[--$index_number];
648  $firstColumn = array_shift($columnKeys);
649  }
650  }
651 
652  if (!$not_exact_match) {
653  uasort($lookup_array,array('self','_vlookupSort'));
654  }
655 
656  $rowNumber = $rowValue = False;
657  foreach($lookup_array as $rowKey => $rowData) {
658  if (strtolower($rowData[$firstColumn]) > strtolower($lookup_value)) {
659  break;
660  }
661  $rowNumber = $rowKey;
662  $rowValue = $rowData[$firstColumn];
663  }
664 
665  if ($rowNumber !== false) {
666  if ((!$not_exact_match) && ($rowValue != $lookup_value)) {
667  // if an exact match is required, we have what we need to return an appropriate response
668  return PHPExcel_Calculation_Functions::NA();
669  } else {
670  // otherwise return the appropriate value
671  return $lookup_array[$rowNumber][$returnColumn];
672  }
673  }
674 
675  return PHPExcel_Calculation_Functions::NA();
676  } // function VLOOKUP()
677 
678 
687  public static function LOOKUP($lookup_value, $lookup_vector, $result_vector=null) {
688  $lookup_value = PHPExcel_Calculation_Functions::flattenSingleValue($lookup_value);
689 
690  if (!is_array($lookup_vector)) {
691  return PHPExcel_Calculation_Functions::NA();
692  }
693  $lookupRows = count($lookup_vector);
694  $l = array_keys($lookup_vector);
695  $l = array_shift($l);
696  $lookupColumns = count($lookup_vector[$l]);
697  if ((($lookupRows == 1) && ($lookupColumns > 1)) || (($lookupRows == 2) && ($lookupColumns != 2))) {
698  $lookup_vector = self::TRANSPOSE($lookup_vector);
699  $lookupRows = count($lookup_vector);
700  $l = array_keys($lookup_vector);
701  $lookupColumns = count($lookup_vector[array_shift($l)]);
702  }
703 
704  if (is_null($result_vector)) {
705  $result_vector = $lookup_vector;
706  }
707  $resultRows = count($result_vector);
708  $l = array_keys($result_vector);
709  $l = array_shift($l);
710  $resultColumns = count($result_vector[$l]);
711  if ((($resultRows == 1) && ($resultColumns > 1)) || (($resultRows == 2) && ($resultColumns != 2))) {
712  $result_vector = self::TRANSPOSE($result_vector);
713  $resultRows = count($result_vector);
714  $r = array_keys($result_vector);
715  $resultColumns = count($result_vector[array_shift($r)]);
716  }
717 
718  if ($lookupRows == 2) {
719  $result_vector = array_pop($lookup_vector);
720  $lookup_vector = array_shift($lookup_vector);
721  }
722  if ($lookupColumns != 2) {
723  foreach($lookup_vector as &$value) {
724  if (is_array($value)) {
725  $k = array_keys($value);
726  $key1 = $key2 = array_shift($k);
727  $key2++;
728  $dataValue1 = $value[$key1];
729  } else {
730  $key1 = 0;
731  $key2 = 1;
732  $dataValue1 = $value;
733  }
734  $dataValue2 = array_shift($result_vector);
735  if (is_array($dataValue2)) {
736  $dataValue2 = array_shift($dataValue2);
737  }
738  $value = array($key1 => $dataValue1, $key2 => $dataValue2);
739  }
740  unset($value);
741  }
742 
743  return self::VLOOKUP($lookup_value,$lookup_vector,2);
744  } // function LOOKUP()
745 
746 } // class PHPExcel_Calculation_LookupRef