ILIAS  release_5-2 Revision v5.2.25-18-g3f80b828510
Static Public Member Functions | Static Private Member Functions
PHPExcel_Calculation_LookupRef Class Reference
+ Collaboration diagram for PHPExcel_Calculation_LookupRef:

Static Public Member Functions

static CELL_ADDRESS ($row, $column, $relativity=1, $referenceStyle=True, $sheetText='')
 CELL_ADDRESS. More...
 
static COLUMN ($cellAddress=Null)
 COLUMN. More...
 
static COLUMNS ($cellAddress=Null)
 COLUMNS. More...
 
static ROW ($cellAddress=Null)
 ROW. More...
 
static ROWS ($cellAddress=Null)
 ROWS. More...
 
static HYPERLINK ($linkURL='', $displayName=null, PHPExcel_Cell $pCell=null)
 
static INDIRECT ($cellAddress=NULL, PHPExcel_Cell $pCell=NULL)
 INDIRECT. More...
 
static OFFSET ($cellAddress=Null, $rows=0, $columns=0, $height=null, $width=null)
 OFFSET. More...
 
static CHOOSE ()
 CHOOSE. More...
 
static MATCH ($lookup_value, $lookup_array, $match_type=1)
 MATCH. More...
 
static INDEX ($arrayValues, $rowNum=0, $columnNum=0)
 INDEX. More...
 
static TRANSPOSE ($matrixData)
 TRANSPOSE. More...
 
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 the value in the same row based on the index_number. More...
 
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 value in the same column based on the index_number. More...
 
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 array. More...
 

Static Private Member Functions

static _vlookupSort ($a, $b)
 

Detailed Description

Definition at line 46 of file LookupRef.php.

Member Function Documentation

◆ _vlookupSort()

static PHPExcel_Calculation_LookupRef::_vlookupSort (   $a,
  $b 
)
staticprivate

Definition at line 674 of file LookupRef.php.

674 {
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()

◆ CELL_ADDRESS()

static PHPExcel_Calculation_LookupRef::CELL_ADDRESS (   $row,
  $column,
  $relativity = 1,
  $referenceStyle = True,
  $sheetText = '' 
)
static

CELL_ADDRESS.

Creates a cell address as text, given specified row and column numbers.

Excel Function: =ADDRESS(row, column, [relativity], [referenceStyle], [sheetText])

Parameters
rowRow number to use in the cell reference
columnColumn number to use in the cell reference
relativityFlag indicating the type of reference to return 1 or omitted Absolute 2 Absolute row; relative column 3 Relative row; absolute column 4 Relative
referenceStyleA logical value that specifies the A1 or R1C1 reference style. TRUE or omitted CELL_ADDRESS returns an A1-style reference FALSE CELL_ADDRESS returns an R1C1-style reference
sheetTextOptional Name of worksheet to use
Returns
string

Definition at line 70 of file LookupRef.php.

70 {
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()
$column
$column
Definition: 39dropdown.php:62
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\VALUE
static VALUE()
Definition: Functions.php:289
PHPExcel_Cell\stringFromColumnIndex
static stringFromColumnIndex($pColumnIndex=0)
String from columnindex.
Definition: Cell.php:825

References $column, $row, PHPExcel_Calculation_Functions\flattenSingleValue(), PHPExcel_Cell\stringFromColumnIndex(), and PHPExcel_Calculation_Functions\VALUE().

+ Here is the call graph for this function:

◆ CHOOSE()

static PHPExcel_Calculation_LookupRef::CHOOSE ( )
static

CHOOSE.

Uses lookup_value to return a value from the list of value arguments. Use CHOOSE to select one of up to 254 values based on the lookup_value.

Excel Function: =CHOOSE(index_num, value1, [value2], ...)

Parameters
index_numSpecifies which value argument is selected. Index_num must be a number between 1 and 254, or a formula or reference to a cell containing a number between 1 and 254.
value1...Value1 is required, subsequent values are optional. Between 1 to 254 value arguments from which CHOOSE selects a value or an action to perform based on index_num. The arguments can be numbers, cell references, defined names, formulas, functions, or text.
Returns
mixed The selected value

Definition at line 451 of file LookupRef.php.

451 {
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()
PHPExcel_Calculation_Functions\flattenArray
static flattenArray($array)
Convert a multi-dimensional array to a simple 1-dimensional array.
Definition: Functions.php:598

References PHPExcel_Calculation_Functions\flattenArray(), and PHPExcel_Calculation_Functions\VALUE().

+ Here is the call graph for this function:

◆ COLUMN()

static PHPExcel_Calculation_LookupRef::COLUMN (   $cellAddress = Null)
static

COLUMN.

Returns the column number of the given cell reference If the cell reference is a range of cells, COLUMN returns the column numbers of each column in the reference as a horizontal array. If cell reference is omitted, and the function is being called through the calculation engine, then it is assumed to be the reference of the cell in which the COLUMN function appears; otherwise this function returns 0.

Excel Function: =COLUMN([cellAddress])

Parameters
cellAddressA reference to a range of cells for which you want the column numbers
Returns
integer or array of integer

Definition at line 112 of file LookupRef.php.

112 {
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()
PHPExcel_Cell\columnIndexFromString
static columnIndexFromString($pString='A')
Column index from string.
Definition: Cell.php:782

References PHPExcel_Cell\columnIndexFromString().

+ Here is the call graph for this function:

◆ COLUMNS()

static PHPExcel_Calculation_LookupRef::COLUMNS (   $cellAddress = Null)
static

COLUMNS.

Returns the number of columns in an array or reference.

Excel Function: =COLUMNS(cellAddress)

Parameters
cellAddressAn array or array formula, or a reference to a range of cells for which you want the number of columns
Returns
integer The number of columns in cellAddress

Definition at line 152 of file LookupRef.php.

152 {
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()
$columns
if(! $in) $columns
Definition: Utf8Test.php:45
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
$x
$x
Definition: example_009.php:98

References $columns, $x, PHPExcel_Calculation\_getMatrixDimensions(), and PHPExcel_Calculation_Functions\VALUE().

+ Here is the call graph for this function:

◆ HLOOKUP()

static PHPExcel_Calculation_LookupRef::HLOOKUP (   $lookup_value,
  $lookup_array,
  $index_number,
  $not_exact_match = true 
)
static

HLOOKUP 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.

Parameters
lookup_valueThe value that you want to match in lookup_array
lookup_arrayThe range of cells being searched
index_numberThe row number in table_array from which the matching value must be returned. The first row is 1.
not_exact_matchDetermines if you are looking for an exact match based on lookup_value.
Returns
mixed The value of the found cell

Definition at line 755 of file LookupRef.php.

755 {
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()
PHPExcel_Calculation_Functions\REF
static REF()
Definition: Functions.php:261
PHPExcel_Calculation_Functions\NA
static NA()
Definition: Functions.php:219

References PHPExcel_Calculation_Functions\flattenSingleValue(), PHPExcel_Calculation_Functions\NA(), PHPExcel_Calculation_Functions\REF(), and PHPExcel_Calculation_Functions\VALUE().

+ Here is the call graph for this function:

◆ HYPERLINK()

static PHPExcel_Calculation_LookupRef::HYPERLINK (   $linkURL = '',
  $displayName = null,
PHPExcel_Cell  $pCell = null 
)
static

Definition at line 259 of file LookupRef.php.

259 {
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()
PHPExcel_Cell\getHyperlink
getHyperlink()
Get Hyperlink.
Definition: Cell.php:447

References PHPExcel_Calculation_Functions\flattenSingleValue(), and PHPExcel_Calculation_Functions\REF().

+ Here is the call graph for this function:

◆ INDEX()

static PHPExcel_Calculation_LookupRef::INDEX (   $arrayValues,
  $rowNum = 0,
  $columnNum = 0 
)
static

INDEX.

Uses an index to choose a value from a reference or array

Excel Function: =INDEX(range_array, row_num, [column_num])

Parameters
range_arrayA range of cells or an array constant
row_numThe row in array from which to return a value. If row_num is omitted, column_num is required.
column_numThe column in array from which to return a value. If column_num is omitted, row_num is required.
Returns
mixed the value of a specified cell or array of cells

Definition at line 603 of file LookupRef.php.

603 {
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()

References PHPExcel_Calculation_Functions\REF(), and PHPExcel_Calculation_Functions\VALUE().

+ Here is the call graph for this function:

◆ INDIRECT()

static PHPExcel_Calculation_LookupRef::INDIRECT (   $cellAddress = NULL,
PHPExcel_Cell  $pCell = NULL 
)
static

INDIRECT.

Returns the reference specified by a text string. References are immediately evaluated to display their contents.

Excel Function: =INDIRECT(cellAddress)

NOTE - INDIRECT() does not yet support the optional a1 parameter introduced in Excel 2010

Parameters
cellAddress$cellAddressThe cell address of the current cell (containing this formula)
PHPExcel_Cell$pCellThe current cell (containing this formula)
Returns
mixed The cells referenced by cellAddress
Todo:
Support for the optional a1 parameter introduced in Excel 2010

Definition at line 298 of file LookupRef.php.

298 {
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()
PHPExcel_Calculation\getInstance
static getInstance(PHPExcel $workbook=NULL)
Get an instance of this class.
Definition: Calculation.php:1765
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\getWorksheet
getWorksheet()
Get parent worksheet.
Definition: Cell.php:488

References PHPExcel_Calculation\CALCULATION_REGEXP_CELLREF, PHPExcel_Calculation\CALCULATION_REGEXP_NAMEDRANGE, PHPExcel_Calculation_Functions\flattenSingleValue(), PHPExcel_Calculation\getInstance(), and PHPExcel_Calculation_Functions\REF().

+ Here is the call graph for this function:

◆ LOOKUP()

static PHPExcel_Calculation_LookupRef::LOOKUP (   $lookup_value,
  $lookup_vector,
  $result_vector = null 
)
static

LOOKUP The LOOKUP function searches for value either from a one-row or one-column range or from an array.

Parameters
lookup_valueThe value that you want to match in lookup_array
lookup_vectorThe range of cells being searched
result_vectorThe column from which the matching value must be returned
Returns
mixed The value of the found cell

Definition at line 817 of file LookupRef.php.

817 {
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()
$l
global $l
Definition: afr.php:30
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\TRANSPOSE
static TRANSPOSE($matrixData)
TRANSPOSE.
Definition: LookupRef.php:657
$r
$r
Definition: example_031.php:79

References $l, $r, PHPExcel_Calculation_Functions\flattenSingleValue(), PHPExcel_Calculation_Functions\NA(), TRANSPOSE(), and VLOOKUP().

+ Here is the call graph for this function:

◆ MATCH()

static PHPExcel_Calculation_LookupRef::MATCH (   $lookup_value,
  $lookup_array,
  $match_type = 1 
)
static

MATCH.

The MATCH function searches for a specified item in a range of cells

Excel Function: =MATCH(lookup_value, lookup_array, [match_type])

Parameters
lookup_valueThe value that you want to match in lookup_array
lookup_arrayThe range of cells being searched
match_typeThe 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.
Returns
integer The relative position of the found item

Definition at line 490 of file LookupRef.php.

490 {
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()

References PHPExcel_Calculation_Functions\flattenArray(), PHPExcel_Calculation_Functions\flattenSingleValue(), and PHPExcel_Calculation_Functions\NA().

+ Here is the call graph for this function:

◆ OFFSET()

static PHPExcel_Calculation_LookupRef::OFFSET (   $cellAddress = Null,
  $rows = 0,
  $columns = 0,
  $height = null,
  $width = null 
)
static

OFFSET.

Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells. The reference that is returned can be a single cell or a range of cells. You can specify the number of rows and the number of columns to be returned.

Excel Function: =OFFSET(cellAddress, rows, cols, [height], [width])

Parameters
cellAddressThe reference from which you want to base the offset. Reference must refer to a cell or range of adjacent cells; otherwise, OFFSET returns the #VALUE! error value.
rowsThe number of rows, up or down, that you want the upper-left cell to refer to. Using 5 as the rows argument specifies that the upper-left cell in the reference is five rows below reference. Rows can be positive (which means below the starting reference) or negative (which means above the starting reference).
colsThe number of columns, to the left or right, that you want the upper-left cell of the result to refer to. Using 5 as the cols argument specifies that the upper-left cell in the reference is five columns to the right of reference. Cols can be positive (which means to the right of the starting reference) or negative (which means to the left of the starting reference).
heightThe height, in number of rows, that you want the returned reference to be. Height must be a positive number.
widthThe width, in number of columns, that you want the returned reference to be. Width must be a positive number.
Returns
string A reference to a cell or range of cells

Definition at line 364 of file LookupRef.php.

364 {
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()
PHPExcel_Cell\coordinateFromString
static coordinateFromString($pCoordinateString='A1')
Coordinate from string.
Definition: Cell.php:580

References $columns, PHPExcel_Cell\columnIndexFromString(), PHPExcel_Cell\coordinateFromString(), PHPExcel_Calculation_Functions\flattenSingleValue(), PHPExcel_Calculation\getInstance(), PHPExcel_Calculation_Functions\REF(), and PHPExcel_Cell\stringFromColumnIndex().

+ Here is the call graph for this function:

◆ ROW()

static PHPExcel_Calculation_LookupRef::ROW (   $cellAddress = Null)
static

ROW.

Returns the row number of the given cell reference If the cell reference is a range of cells, ROW returns the row numbers of each row in the reference as a vertical array. If cell reference is omitted, and the function is being called through the calculation engine, then it is assumed to be the reference of the cell in which the ROW function appears; otherwise this function returns 0.

Excel Function: =ROW([cellAddress])

Parameters
cellAddressA reference to a range of cells for which you want the row numbers
Returns
integer or array of integer

Definition at line 186 of file LookupRef.php.

186 {
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()

◆ ROWS()

static PHPExcel_Calculation_LookupRef::ROWS (   $cellAddress = Null)
static

ROWS.

Returns the number of rows in an array or reference.

Excel Function: =ROWS(cellAddress)

Parameters
cellAddressAn array or array formula, or a reference to a range of cells for which you want the number of rows
Returns
integer The number of rows in cellAddress

Definition at line 227 of file LookupRef.php.

227 {
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()

References $columns, PHPExcel_Calculation\_getMatrixDimensions(), and PHPExcel_Calculation_Functions\VALUE().

+ Here is the call graph for this function:

◆ TRANSPOSE()

static PHPExcel_Calculation_LookupRef::TRANSPOSE (   $matrixData)
static

TRANSPOSE.

Parameters
array$matrixDataA matrix of values
Returns
array

Unlike the Excel TRANSPOSE function, which will only work on a single row or column, this function will transpose a full matrix.

Definition at line 657 of file LookupRef.php.

657 {
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()

References $column, and $row.

Referenced by LOOKUP().

+ Here is the caller graph for this function:

◆ VLOOKUP()

static PHPExcel_Calculation_LookupRef::VLOOKUP (   $lookup_value,
  $lookup_array,
  $index_number,
  $not_exact_match = true 
)
static

VLOOKUP 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.

Parameters
lookup_valueThe value that you want to match in lookup_array
lookup_arrayThe range of cells being searched
index_numberThe column number in table_array from which the matching value must be returned. The first column is 1.
not_exact_matchDetermines if you are looking for an exact match based on lookup_value.
Returns
mixed The value of the found cell

Definition at line 693 of file LookupRef.php.

693 {
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()

References PHPExcel_Calculation_Functions\flattenSingleValue(), PHPExcel_Calculation_Functions\NA(), PHPExcel_Calculation_Functions\REF(), and PHPExcel_Calculation_Functions\VALUE().

Referenced by LOOKUP().

+ Here is the call graph for this function:
+ Here is the caller graph for this function:
The documentation for this class was generated from the following file: