ILIAS  eassessment Revision 61809
 All Data Structures Namespaces Files Functions Variables Groups Pages
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.
static COLUMN ($cellAddress=Null)
 COLUMN.
static COLUMNS ($cellAddress=Null)
 COLUMNS.
static ROW ($cellAddress=Null)
 ROW.
static ROWS ($cellAddress=Null)
 ROWS.
static HYPERLINK ($linkURL= '', $displayName=null, PHPExcel_Cell $pCell=null)
static INDIRECT ($cellAddress=Null, PHPExcel_Cell $pCell=null)
 INDIRECT.
static OFFSET ($cellAddress=Null, $rows=0, $columns=0, $height=null, $width=null)
 OFFSET.
static CHOOSE ()
static MATCH ($lookup_value, $lookup_array, $match_type=1)
 MATCH.
static INDEX ($arrayValues, $rowNum=0, $columnNum=0)
 INDEX.
static TRANSPOSE ($matrixData)
 TRANSPOSE.
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.
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.

Static Private Member Functions

static _vlookupSort ($a, $b)

Detailed Description

Definition at line 46 of file LookupRef.php.

Member Function Documentation

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

Definition at line 608 of file LookupRef.php.

References $f.

{
$f = array_keys($a);
$firstColumn = array_shift($f);
if (strtolower($a[$firstColumn]) == strtolower($b[$firstColumn])) {
return 0;
}
return (strtolower($a[$firstColumn]) < strtolower($b[$firstColumn])) ? -1 : 1;
} // function _vlookupSort()
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.

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 67 of file LookupRef.php.

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

{
$row = PHPExcel_Calculation_Functions::flattenSingleValue($row);
$column = PHPExcel_Calculation_Functions::flattenSingleValue($column);
$relativity = PHPExcel_Calculation_Functions::flattenSingleValue($relativity);
$sheetText = PHPExcel_Calculation_Functions::flattenSingleValue($sheetText);
if (($row < 1) || ($column < 1)) {
return PHPExcel_Calculation_Functions::VALUE();
}
if ($sheetText > '') {
if (strpos($sheetText,' ') !== False) { $sheetText = "'".$sheetText."'"; }
$sheetText .='!';
}
if ((!is_bool($referenceStyle)) || $referenceStyle) {
$rowRelative = $columnRelative = '$';
$column = PHPExcel_Cell::stringFromColumnIndex($column-1);
if (($relativity == 2) || ($relativity == 4)) { $columnRelative = ''; }
if (($relativity == 3) || ($relativity == 4)) { $rowRelative = ''; }
return $sheetText.$columnRelative.$column.$rowRelative.$row;
} else {
if (($relativity == 2) || ($relativity == 4)) { $column = '['.$column.']'; }
if (($relativity == 3) || ($relativity == 4)) { $row = '['.$row.']'; }
return $sheetText.'R'.$row.'C'.$column;
}
} // function CELL_ADDRESS()

+ Here is the call graph for this function:

static PHPExcel_Calculation_LookupRef::CHOOSE ( )
static

Definition at line 390 of file LookupRef.php.

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

{
$chooseArgs = func_get_args();
$chosenEntry = PHPExcel_Calculation_Functions::flattenArray(array_shift($chooseArgs));
$entryCount = count($chooseArgs) - 1;
if(is_array($chosenEntry)) {
$chosenEntry = array_shift($chosenEntry);
}
if ((is_numeric($chosenEntry)) && (!is_bool($chosenEntry))) {
--$chosenEntry;
} else {
return PHPExcel_Calculation_Functions::VALUE();
}
$chosenEntry = floor($chosenEntry);
if (($chosenEntry <= 0) || ($chosenEntry > $entryCount)) {
return PHPExcel_Calculation_Functions::VALUE();
}
if (is_array($chooseArgs[$chosenEntry])) {
return PHPExcel_Calculation_Functions::flattenArray($chooseArgs[$chosenEntry]);
} else {
return $chooseArgs[$chosenEntry];
}
} // function CHOOSE()

+ Here is the call graph for this function:

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.

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

Definition at line 106 of file LookupRef.php.

References PHPExcel_Cell\columnIndexFromString().

{
if (is_null($cellAddress) || trim($cellAddress) === '') { return 0; }
if (is_array($cellAddress)) {
foreach($cellAddress as $columnKey => $value) {
$columnKey = preg_replace('/[^a-z]/i','',$columnKey);
return (integer) PHPExcel_Cell::columnIndexFromString($columnKey);
}
} else {
if (strpos($cellAddress,'!') !== false) {
list($sheet,$cellAddress) = explode('!',$cellAddress);
}
if (strpos($cellAddress,':') !== false) {
list($startAddress,$endAddress) = explode(':',$cellAddress);
$startAddress = preg_replace('/[^a-z]/i','',$startAddress);
$endAddress = preg_replace('/[^a-z]/i','',$endAddress);
$returnValue = array();
do {
$returnValue[] = (integer) PHPExcel_Cell::columnIndexFromString($startAddress);
} while ($startAddress++ != $endAddress);
return $returnValue;
} else {
$cellAddress = preg_replace('/[^a-z]/i','',$cellAddress);
return (integer) PHPExcel_Cell::columnIndexFromString($cellAddress);
}
}
} // function COLUMN()

+ Here is the call graph for this function:

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

COLUMNS.

Returns the number of columns in an array or reference.

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

Definition at line 143 of file LookupRef.php.

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

{
if (is_null($cellAddress) || $cellAddress === '') {
return 1;
} elseif (!is_array($cellAddress)) {
return PHPExcel_Calculation_Functions::VALUE();
}
$x = array_keys($cellAddress);
$x = array_shift($x);
$isMatrix = (is_numeric($x));
list($columns,$rows) = PHPExcel_Calculation::_getMatrixDimensions($cellAddress);
if ($isMatrix) {
return $rows;
} else {
return $columns;
}
} // function COLUMNS()

+ Here is the call graph for this function:

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

Definition at line 243 of file LookupRef.php.

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

{
$args = func_get_args();
$pCell = array_pop($args);
$linkURL = (is_null($linkURL)) ? '' : PHPExcel_Calculation_Functions::flattenSingleValue($linkURL);
$displayName = (is_null($displayName)) ? '' : PHPExcel_Calculation_Functions::flattenSingleValue($displayName);
if ((!is_object($pCell)) || (trim($linkURL) == '')) {
return PHPExcel_Calculation_Functions::REF();
}
if ((is_object($displayName)) || trim($displayName) == '') {
$displayName = $linkURL;
}
$pCell->getHyperlink()->setUrl($linkURL);
return $displayName;
} // function HYPERLINK()

+ Here is the call graph for this function:

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

INDEX.

Uses an index to choose a value from a reference or array implemented: Return the value of a specified cell or array of cells Array form not implemented: Return a reference to specified cells Reference form

Parameters
range_arraya range of cells or an array constant
row_numselects the row in array from which to return a value. If row_num is omitted, column_num is required.
column_numselects the column in array from which to return a value. If column_num is omitted, row_num is required.

Definition at line 537 of file LookupRef.php.

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

{
if (($rowNum < 0) || ($columnNum < 0)) {
return PHPExcel_Calculation_Functions::VALUE();
}
if (!is_array($arrayValues)) {
return PHPExcel_Calculation_Functions::REF();
}
$rowKeys = array_keys($arrayValues);
$columnKeys = @array_keys($arrayValues[$rowKeys[0]]);
if ($columnNum > count($columnKeys)) {
return PHPExcel_Calculation_Functions::VALUE();
} elseif ($columnNum == 0) {
if ($rowNum == 0) {
return $arrayValues;
}
$rowNum = $rowKeys[--$rowNum];
$returnArray = array();
foreach($arrayValues as $arrayColumn) {
if (is_array($arrayColumn)) {
if (isset($arrayColumn[$rowNum])) {
$returnArray[] = $arrayColumn[$rowNum];
} else {
return $arrayValues[$rowNum];
}
} else {
return $arrayValues[$rowNum];
}
}
return $returnArray;
}
$columnNum = $columnKeys[--$columnNum];
if ($rowNum > count($rowKeys)) {
return PHPExcel_Calculation_Functions::VALUE();
} elseif ($rowNum == 0) {
return $arrayValues[$columnNum];
}
$rowNum = $rowKeys[--$rowNum];
return $arrayValues[$rowNum][$columnNum];
} // function INDEX()

+ Here is the call graph for this function:

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

INDIRECT.

Returns the number of rows in an array or reference.

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

Definition at line 272 of file LookupRef.php.

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

{
$cellAddress = PHPExcel_Calculation_Functions::flattenSingleValue($cellAddress);
if (is_null($cellAddress) || $cellAddress === '') {
return PHPExcel_Calculation_Functions::REF();
}
$cellAddress1 = $cellAddress;
$cellAddress2 = NULL;
if (strpos($cellAddress,':') !== false) {
list($cellAddress1,$cellAddress2) = explode(':',$cellAddress);
}
if ((!preg_match('/^'.PHPExcel_Calculation::CALCULATION_REGEXP_CELLREF.'$/i', $cellAddress1, $matches)) ||
((!is_null($cellAddress2)) && (!preg_match('/^'.PHPExcel_Calculation::CALCULATION_REGEXP_CELLREF.'$/i', $cellAddress2, $matches)))) {
return PHPExcel_Calculation_Functions::REF();
}
if (strpos($cellAddress,'!') !== false) {
list($sheetName,$cellAddress) = explode('!',$cellAddress);
$pSheet = $pCell->getParent()->getParent()->getSheetByName($sheetName);
} else {
$pSheet = $pCell->getParent();
}
return PHPExcel_Calculation::getInstance()->extractCellRange($cellAddress, $pSheet, False);
} // function INDIRECT()

+ Here is the call graph for this function:

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 687 of file LookupRef.php.

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

{
$lookup_value = PHPExcel_Calculation_Functions::flattenSingleValue($lookup_value);
if (!is_array($lookup_vector)) {
return PHPExcel_Calculation_Functions::NA();
}
$lookupRows = count($lookup_vector);
$l = array_keys($lookup_vector);
$l = array_shift($l);
$lookupColumns = count($lookup_vector[$l]);
if ((($lookupRows == 1) && ($lookupColumns > 1)) || (($lookupRows == 2) && ($lookupColumns != 2))) {
$lookup_vector = self::TRANSPOSE($lookup_vector);
$lookupRows = count($lookup_vector);
$l = array_keys($lookup_vector);
$lookupColumns = count($lookup_vector[array_shift($l)]);
}
if (is_null($result_vector)) {
$result_vector = $lookup_vector;
}
$resultRows = count($result_vector);
$l = array_keys($result_vector);
$l = array_shift($l);
$resultColumns = count($result_vector[$l]);
if ((($resultRows == 1) && ($resultColumns > 1)) || (($resultRows == 2) && ($resultColumns != 2))) {
$result_vector = self::TRANSPOSE($result_vector);
$resultRows = count($result_vector);
$r = array_keys($result_vector);
$resultColumns = count($result_vector[array_shift($r)]);
}
if ($lookupRows == 2) {
$result_vector = array_pop($lookup_vector);
$lookup_vector = array_shift($lookup_vector);
}
if ($lookupColumns != 2) {
foreach($lookup_vector as &$value) {
if (is_array($value)) {
$k = array_keys($value);
$key1 = $key2 = array_shift($k);
$key2++;
$dataValue1 = $value[$key1];
} else {
$key1 = 0;
$key2 = 1;
$dataValue1 = $value;
}
$dataValue2 = array_shift($result_vector);
if (is_array($dataValue2)) {
$dataValue2 = array_shift($dataValue2);
}
$value = array($key1 => $dataValue1, $key2 => $dataValue2);
}
unset($value);
}
return self::VLOOKUP($lookup_value,$lookup_vector,2);
} // function LOOKUP()

+ Here is the call graph for this function:

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

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 426 of file LookupRef.php.

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

{
$lookup_array = PHPExcel_Calculation_Functions::flattenArray($lookup_array);
$lookup_value = PHPExcel_Calculation_Functions::flattenSingleValue($lookup_value);
$match_type = (is_null($match_type)) ? 1 : (int) PHPExcel_Calculation_Functions::flattenSingleValue($match_type);
// MATCH is not case sensitive
$lookup_value = strtolower($lookup_value);
// lookup_value type has to be number, text, or logical values
if ((!is_numeric($lookup_value)) && (!is_string($lookup_value)) && (!is_bool($lookup_value))) {
return PHPExcel_Calculation_Functions::NA();
}
// match_type is 0, 1 or -1
if (($match_type !== 0) && ($match_type !== -1) && ($match_type !== 1)) {
return PHPExcel_Calculation_Functions::NA();
}
// lookup_array should not be empty
$lookupArraySize = count($lookup_array);
if ($lookupArraySize <= 0) {
return PHPExcel_Calculation_Functions::NA();
}
// lookup_array should contain only number, text, or logical values, or empty (null) cells
foreach($lookup_array as $i => $lookupArrayValue) {
// check the type of the value
if ((!is_numeric($lookupArrayValue)) && (!is_string($lookupArrayValue)) &&
(!is_bool($lookupArrayValue)) && (!is_null($lookupArrayValue))) {
return PHPExcel_Calculation_Functions::NA();
}
// convert strings to lowercase for case-insensitive testing
if (is_string($lookupArrayValue)) {
$lookup_array[$i] = strtolower($lookupArrayValue);
}
if ((is_null($lookupArrayValue)) && (($match_type == 1) || ($match_type == -1))) {
$lookup_array = array_slice($lookup_array,0,$i-1);
}
}
// if match_type is 1 or -1, the list has to be ordered
if ($match_type == 1) {
asort($lookup_array);
$keySet = array_keys($lookup_array);
} elseif($match_type == -1) {
arsort($lookup_array);
$keySet = array_keys($lookup_array);
}
// **
// find the match
// **
// loop on the cells
// var_dump($lookup_array);
// echo '<br />';
foreach($lookup_array as $i => $lookupArrayValue) {
if (($match_type == 0) && ($lookupArrayValue == $lookup_value)) {
// exact match
return ++$i;
} elseif (($match_type == -1) && ($lookupArrayValue <= $lookup_value)) {
// echo '$i = '.$i.' => ';
// var_dump($lookupArrayValue);
// echo '<br />';
// echo 'Keyset = ';
// var_dump($keySet);
// echo '<br />';
$i = array_search($i,$keySet);
// echo '$i='.$i.'<br />';
// if match_type is -1 <=> find the smallest value that is greater than or equal to lookup_value
if ($i < 1){
// 1st cell was allready smaller than the lookup_value
break;
} else {
// the previous cell was the match
return $keySet[$i-1]+1;
}
} elseif (($match_type == 1) && ($lookupArrayValue >= $lookup_value)) {
// echo '$i = '.$i.' => ';
// var_dump($lookupArrayValue);
// echo '<br />';
// echo 'Keyset = ';
// var_dump($keySet);
// echo '<br />';
$i = array_search($i,$keySet);
// echo '$i='.$i.'<br />';
// if match_type is 1 <=> find the largest value that is less than or equal to lookup_value
if ($i < 1){
// 1st cell was allready bigger than the lookup_value
break;
} else {
// the previous cell was the match
return $keySet[$i-1]+1;
}
}
}
// unsuccessful in finding a match, return #N/A error value
return PHPExcel_Calculation_Functions::NA();
} // function MATCH()

+ Here is the call graph for this function:

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.

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 322 of file LookupRef.php.

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

{
$rows = PHPExcel_Calculation_Functions::flattenSingleValue($rows);
$columns = PHPExcel_Calculation_Functions::flattenSingleValue($columns);
$height = PHPExcel_Calculation_Functions::flattenSingleValue($height);
$width = PHPExcel_Calculation_Functions::flattenSingleValue($width);
if ($cellAddress == Null) {
return 0;
}
$args = func_get_args();
$pCell = array_pop($args);
if (!is_object($pCell)) {
return PHPExcel_Calculation_Functions::REF();
}
$sheetName = null;
if (strpos($cellAddress,"!")) {
list($sheetName,$cellAddress) = explode("!",$cellAddress);
}
if (strpos($cellAddress,":")) {
list($startCell,$endCell) = explode(":",$cellAddress);
} else {
$startCell = $endCell = $cellAddress;
}
list($startCellColumn,$startCellRow) = PHPExcel_Cell::coordinateFromString($startCell);
list($endCellColumn,$endCellRow) = PHPExcel_Cell::coordinateFromString($endCell);
$startCellRow += $rows;
$startCellColumn = PHPExcel_Cell::columnIndexFromString($startCellColumn) - 1;
$startCellColumn += $columns;
if (($startCellRow <= 0) || ($startCellColumn < 0)) {
return PHPExcel_Calculation_Functions::REF();
}
$endCellColumn = PHPExcel_Cell::columnIndexFromString($endCellColumn) - 1;
if (($width != null) && (!is_object($width))) {
$endCellColumn = $startCellColumn + $width - 1;
} else {
$endCellColumn += $columns;
}
$startCellColumn = PHPExcel_Cell::stringFromColumnIndex($startCellColumn);
if (($height != null) && (!is_object($height))) {
$endCellRow = $startCellRow + $height - 1;
} else {
$endCellRow += $rows;
}
if (($endCellRow <= 0) || ($endCellColumn < 0)) {
return PHPExcel_Calculation_Functions::REF();
}
$endCellColumn = PHPExcel_Cell::stringFromColumnIndex($endCellColumn);
$cellAddress = $startCellColumn.$startCellRow;
if (($startCellColumn != $endCellColumn) || ($startCellRow != $endCellRow)) {
$cellAddress .= ':'.$endCellColumn.$endCellRow;
}
if ($sheetName !== null) {
$pSheet = $pCell->getParent()->getParent()->getSheetByName($sheetName);
} else {
$pSheet = $pCell->getParent();
}
return PHPExcel_Calculation::getInstance()->extractCellRange($cellAddress, $pSheet, False);
} // function OFFSET()

+ Here is the call graph for this function:

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.

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

Definition at line 174 of file LookupRef.php.

{
if (is_null($cellAddress) || trim($cellAddress) === '') { return 0; }
if (is_array($cellAddress)) {
foreach($cellAddress as $columnKey => $rowValue) {
foreach($rowValue as $rowKey => $cellValue) {
return (integer) preg_replace('/[^0-9]/i','',$rowKey);
}
}
} else {
if (strpos($cellAddress,'!') !== false) {
list($sheet,$cellAddress) = explode('!',$cellAddress);
}
if (strpos($cellAddress,':') !== false) {
list($startAddress,$endAddress) = explode(':',$cellAddress);
$startAddress = preg_replace('/[^0-9]/','',$startAddress);
$endAddress = preg_replace('/[^0-9]/','',$endAddress);
$returnValue = array();
do {
$returnValue[][] = (integer) $startAddress;
} while ($startAddress++ != $endAddress);
return $returnValue;
} else {
list($cellAddress) = explode(':',$cellAddress);
return (integer) preg_replace('/[^0-9]/','',$cellAddress);
}
}
} // function ROW()
static PHPExcel_Calculation_LookupRef::ROWS (   $cellAddress = Null)
static

ROWS.

Returns the number of rows in an array or reference.

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

Definition at line 212 of file LookupRef.php.

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

{
if (is_null($cellAddress) || $cellAddress === '') {
return 1;
} elseif (!is_array($cellAddress)) {
return PHPExcel_Calculation_Functions::VALUE();
}
$i = array_keys($cellAddress);
$isMatrix = (is_numeric(array_shift($i)));
list($columns,$rows) = PHPExcel_Calculation::_getMatrixDimensions($cellAddress);
if ($isMatrix) {
return $columns;
} else {
return $rows;
}
} // function ROWS()

+ Here is the call graph for this function:

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 591 of file LookupRef.php.

References $row.

Referenced by LOOKUP().

{
$returnMatrix = array();
if (!is_array($matrixData)) { $matrixData = array(array($matrixData)); }
$column = 0;
foreach($matrixData as $matrixRow) {
$row = 0;
foreach($matrixRow as $matrixCell) {
$returnMatrix[$row][$column] = $matrixCell;
++$row;
}
++$column;
}
return $returnMatrix;
} // function TRANSPOSE()

+ Here is the caller graph for this function:

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 627 of file LookupRef.php.

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

Referenced by LOOKUP().

{
$lookup_value = PHPExcel_Calculation_Functions::flattenSingleValue($lookup_value);
$index_number = PHPExcel_Calculation_Functions::flattenSingleValue($index_number);
$not_exact_match = PHPExcel_Calculation_Functions::flattenSingleValue($not_exact_match);
// index_number must be greater than or equal to 1
if ($index_number < 1) {
return PHPExcel_Calculation_Functions::VALUE();
}
// index_number must be less than or equal to the number of columns in lookup_array
if ((!is_array($lookup_array)) || (count($lookup_array) < 1)) {
return PHPExcel_Calculation_Functions::REF();
} else {
$f = array_keys($lookup_array);
$firstRow = array_pop($f);
if ((!is_array($lookup_array[$firstRow])) || ($index_number > count($lookup_array[$firstRow]))) {
return PHPExcel_Calculation_Functions::REF();
} else {
$columnKeys = array_keys($lookup_array[$firstRow]);
$returnColumn = $columnKeys[--$index_number];
$firstColumn = array_shift($columnKeys);
}
}
if (!$not_exact_match) {
uasort($lookup_array,array('self','_vlookupSort'));
}
$rowNumber = $rowValue = False;
foreach($lookup_array as $rowKey => $rowData) {
if (strtolower($rowData[$firstColumn]) > strtolower($lookup_value)) {
break;
}
$rowNumber = $rowKey;
$rowValue = $rowData[$firstColumn];
}
if ($rowNumber !== false) {
if ((!$not_exact_match) && ($rowValue != $lookup_value)) {
// if an exact match is required, we have what we need to return an appropriate response
return PHPExcel_Calculation_Functions::NA();
} else {
// otherwise return the appropriate value
return $lookup_array[$rowNumber][$returnColumn];
}
}
return PHPExcel_Calculation_Functions::NA();
} // function VLOOKUP()

+ 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: