ILIAS  release_5-3 Revision v5.3.23-19-g915713cf615
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.

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

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

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

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
PHPExcel_Calculation_Functions\VALUE
static VALUE()
Definition: Functions.php:289
+ 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.

References array, and PHPExcel_Cell\columnIndexFromString().

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()
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
+ 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.

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

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

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

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

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

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_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\getHyperlink
getHyperlink()
Get Hyperlink.
Definition: Cell.php:447
PHPExcel_Calculation_Functions\REF
static REF()
Definition: Functions.php:261
+ 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.

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

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()
PHPExcel_Calculation_Functions\VALUE
static VALUE()
Definition: Functions.php:289
array
Create styles array
The data for the language used.
Definition: 40duplicateStyle.php:19
PHPExcel_Calculation_Functions\REF
static REF()
Definition: Functions.php:261
+ 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.

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

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_Functions\flattenSingleValue
static flattenSingleValue($value='')
Convert an array to a single scalar value by extracting the first element.
Definition: Functions.php:662
PHPExcel_Calculation\getInstance
static getInstance(PHPExcel $workbook=NULL)
Get an instance of this class.
Definition: Calculation.php:1765
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\getWorksheet
getWorksheet()
Get parent worksheet.
Definition: Cell.php:488
PHPExcel_Calculation\CALCULATION_REGEXP_NAMEDRANGE
const CALCULATION_REGEXP_NAMEDRANGE
Definition: Calculation.php:77
+ 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.

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

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()
PHPExcel_Calculation_Functions\flattenSingleValue
static flattenSingleValue($value='')
Convert an array to a single scalar value by extracting the first element.
Definition: Functions.php:662
$r
$r
Definition: example_031.php:79
PHPExcel_Calculation_Functions\NA
static NA()
Definition: Functions.php:219
array
Create styles array
The data for the language used.
Definition: 40duplicateStyle.php:19
$l
global $l
Definition: afr.php:30
+ 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.

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

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()
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\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
$i
$i
Definition: disco.tpl.php:19
+ 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.

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

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_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
$rows
$rows
Definition: xhr_table.php:10
PHPExcel_Cell\columnIndexFromString
static columnIndexFromString($pString='A')
Column index from string.
Definition: Cell.php:782
PHPExcel_Calculation\getInstance
static getInstance(PHPExcel $workbook=NULL)
Get an instance of this class.
Definition: Calculation.php:1765
PHPExcel_Calculation_Functions\REF
static REF()
Definition: Functions.php:261
PHPExcel_Cell\stringFromColumnIndex
static stringFromColumnIndex($pColumnIndex=0)
String from columnindex.
Definition: Cell.php:825
$columns
if(! $in) $columns
Definition: Utf8Test.php:45
+ 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.

References array.

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()
array
Create styles array
The data for the language used.
Definition: 40duplicateStyle.php:19

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

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

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()
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
$rows
$rows
Definition: xhr_table.php:10
$i
$i
Definition: disco.tpl.php:19
$columns
if(! $in) $columns
Definition: Utf8Test.php:45
+ 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.

References $column, $row, and array.

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()
$column
$column
Definition: 39dropdown.php:62
array
Create styles array
The data for the language used.
Definition: 40duplicateStyle.php:19

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

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

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()
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_Calculation_Functions\NA
static NA()
Definition: Functions.php:219
array
Create styles array
The data for the language used.
Definition: 40duplicateStyle.php:19
PHPExcel_Calculation_Functions\REF
static REF()
Definition: Functions.php:261
+ Here is the call graph for this function:
The documentation for this class was generated from the following file: