class PHPExcel_Calculation_LookupRef in Loft Data Grids 7.2
Same name and namespace in other branches
- 6.2 vendor/phpoffice/phpexcel/Classes/PHPExcel/Calculation/LookupRef.php \PHPExcel_Calculation_LookupRef
PHPExcel_Calculation_LookupRef
@category PHPExcel @package PHPExcel_Calculation @copyright Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel)
Hierarchy
Expanded class hierarchy of PHPExcel_Calculation_LookupRef
2 string references to 'PHPExcel_Calculation_LookupRef'
- LookupRefTest::testHLOOKUP in vendor/
phpoffice/ phpexcel/ unitTests/ Classes/ PHPExcel/ Calculation/ LookupRefTest.php - @dataProvider providerHLOOKUP
- LookupRefTest::testVLOOKUP in vendor/
phpoffice/ phpexcel/ unitTests/ Classes/ PHPExcel/ Calculation/ LookupRefTest.php - @dataProvider providerVLOOKUP
File
- vendor/
phpoffice/ phpexcel/ Classes/ PHPExcel/ Calculation/ LookupRef.php, line 46
View source
class PHPExcel_Calculation_LookupRef {
/**
* CELL_ADDRESS
*
* Creates a cell address as text, given specified row and column numbers.
*
* Excel Function:
* =ADDRESS(row, column, [relativity], [referenceStyle], [sheetText])
*
* @param row Row number to use in the cell reference
* @param column Column number to use in the cell reference
* @param relativity Flag indicating the type of reference to return
* 1 or omitted Absolute
* 2 Absolute row; relative column
* 3 Relative row; absolute column
* 4 Relative
* @param referenceStyle A 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
* @param sheetText Optional Name of worksheet to use
* @return string
*/
public static function CELL_ADDRESS($row, $column, $relativity = 1, $referenceStyle = True, $sheetText = '') {
$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()
/**
* 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])
*
* @param cellAddress A reference to a range of cells for which you want the column numbers
* @return integer or array of integer
*/
public static function COLUMN($cellAddress = Null) {
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 (int) 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[] = (int) PHPExcel_Cell::columnIndexFromString($startAddress);
} while ($startAddress++ != $endAddress);
return $returnValue;
}
else {
$cellAddress = preg_replace('/[^a-z]/i', '', $cellAddress);
return (int) PHPExcel_Cell::columnIndexFromString($cellAddress);
}
}
}
// function COLUMN()
/**
* COLUMNS
*
* Returns the number of columns in an array or reference.
*
* Excel Function:
* =COLUMNS(cellAddress)
*
* @param cellAddress An array or array formula, or a reference to a range of cells for which you want the number of columns
* @return integer The number of columns in cellAddress
*/
public static function COLUMNS($cellAddress = Null) {
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()
/**
* 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])
*
* @param cellAddress A reference to a range of cells for which you want the row numbers
* @return integer or array of integer
*/
public static function ROW($cellAddress = Null) {
if (is_null($cellAddress) || trim($cellAddress) === '') {
return 0;
}
if (is_array($cellAddress)) {
foreach ($cellAddress as $columnKey => $rowValue) {
foreach ($rowValue as $rowKey => $cellValue) {
return (int) 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[][] = (int) $startAddress;
} while ($startAddress++ != $endAddress);
return $returnValue;
}
else {
list($cellAddress) = explode(':', $cellAddress);
return (int) preg_replace('/[^0-9]/', '', $cellAddress);
}
}
}
// function ROW()
/**
* ROWS
*
* Returns the number of rows in an array or reference.
*
* Excel Function:
* =ROWS(cellAddress)
*
* @param cellAddress An array or array formula, or a reference to a range of cells for which you want the number of rows
* @return integer The number of rows in cellAddress
*/
public static function ROWS($cellAddress = Null) {
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()
/**
* HYPERLINK
*
* Excel Function:
* =HYPERLINK(linkURL,displayName)
*
* @access public
* @category Logical Functions
* @param string $linkURL Value to check, is also the value returned when no error
* @param string $displayName Value to return when testValue is an error condition
* @param PHPExcel_Cell $pCell The cell to set the hyperlink in
* @return mixed The value of $displayName (or $linkURL if $displayName was blank)
*/
public static function HYPERLINK($linkURL = '', $displayName = null, PHPExcel_Cell $pCell = null) {
$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()
/**
* 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
*
* @param cellAddress $cellAddress The cell address of the current cell (containing this formula)
* @param PHPExcel_Cell $pCell The current cell (containing this formula)
* @return mixed The cells referenced by cellAddress
*
* @todo Support for the optional a1 parameter introduced in Excel 2010
*
*/
public static function INDIRECT($cellAddress = NULL, PHPExcel_Cell $pCell = NULL) {
$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)) {
if (!preg_match('/^' . PHPExcel_Calculation::CALCULATION_REGEXP_NAMEDRANGE . '$/i', $cellAddress1, $matches)) {
return PHPExcel_Calculation_Functions::REF();
}
if (strpos($cellAddress, '!') !== FALSE) {
list($sheetName, $cellAddress) = explode('!', $cellAddress);
$sheetName = trim($sheetName, "'");
$pSheet = $pCell
->getWorksheet()
->getParent()
->getSheetByName($sheetName);
}
else {
$pSheet = $pCell
->getWorksheet();
}
return PHPExcel_Calculation::getInstance()
->extractNamedRange($cellAddress, $pSheet, FALSE);
}
if (strpos($cellAddress, '!') !== FALSE) {
list($sheetName, $cellAddress) = explode('!', $cellAddress);
$sheetName = trim($sheetName, "'");
$pSheet = $pCell
->getWorksheet()
->getParent()
->getSheetByName($sheetName);
}
else {
$pSheet = $pCell
->getWorksheet();
}
return PHPExcel_Calculation::getInstance()
->extractCellRange($cellAddress, $pSheet, FALSE);
}
// function INDIRECT()
/**
* 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])
*
* @param cellAddress The 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.
* @param rows The 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).
* @param cols The 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).
* @param height The height, in number of rows, that you want the returned reference to be. Height must be a positive number.
* @param width The width, in number of columns, that you want the returned reference to be. Width must be a positive number.
* @return string A reference to a cell or range of cells
*/
public static function OFFSET($cellAddress = Null, $rows = 0, $columns = 0, $height = null, $width = null) {
$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);
$sheetName = trim($sheetName, "'");
}
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
->getWorksheet()
->getParent()
->getSheetByName($sheetName);
}
else {
$pSheet = $pCell
->getWorksheet();
}
return PHPExcel_Calculation::getInstance()
->extractCellRange($cellAddress, $pSheet, False);
}
// function OFFSET()
/**
* 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], ...)
*
* @param index_num Specifies 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.
* @param 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.
* @return mixed The selected value
*/
public static function CHOOSE() {
$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()
/**
* MATCH
*
* The MATCH function searches for a specified item in a range of cells
*
* Excel Function:
* =MATCH(lookup_value, lookup_array, [match_type])
*
* @param lookup_value The value that you want to match in lookup_array
* @param lookup_array The range of cells being searched
* @param match_type The 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.
* @return integer The relative position of the found item
*/
public static function MATCH($lookup_value, $lookup_array, $match_type = 1) {
$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()
/**
* INDEX
*
* Uses an index to choose a value from a reference or array
*
* Excel Function:
* =INDEX(range_array, row_num, [column_num])
*
* @param range_array A range of cells or an array constant
* @param row_num The row in array from which to return a value. If row_num is omitted, column_num is required.
* @param column_num The column in array from which to return a value. If column_num is omitted, row_num is required.
* @return mixed the value of a specified cell or array of cells
*/
public static function INDEX($arrayValues, $rowNum = 0, $columnNum = 0) {
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()
/**
* TRANSPOSE
*
* @param array $matrixData A matrix of values
* @return array
*
* Unlike the Excel TRANSPOSE function, which will only work on a single row or column, this function will transpose a full matrix.
*/
public static function TRANSPOSE($matrixData) {
$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()
private static function _vlookupSort($a, $b) {
$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()
/**
* 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.
* @param lookup_value The value that you want to match in lookup_array
* @param lookup_array The range of cells being searched
* @param index_number The column number in table_array from which the matching value must be returned. The first column is 1.
* @param not_exact_match Determines if you are looking for an exact match based on lookup_value.
* @return mixed The value of the found cell
*/
public static function VLOOKUP($lookup_value, $lookup_array, $index_number, $not_exact_match = true) {
$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) || empty($lookup_array)) {
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 (is_numeric($lookup_value) && is_numeric($rowData[$firstColumn]) && $rowData[$firstColumn] > $lookup_value || !is_numeric($lookup_value) && !is_numeric($rowData[$firstColumn]) && 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()
/**
* 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.
* @param lookup_value The value that you want to match in lookup_array
* @param lookup_array The range of cells being searched
* @param index_number The row number in table_array from which the matching value must be returned. The first row is 1.
* @param not_exact_match Determines if you are looking for an exact match based on lookup_value.
* @return mixed The value of the found cell
*/
public static function HLOOKUP($lookup_value, $lookup_array, $index_number, $not_exact_match = true) {
$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) || empty($lookup_array)) {
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]);
$firstkey = $f[0] - 1;
$returnColumn = $firstkey + $index_number;
$firstColumn = array_shift($f);
}
}
if (!$not_exact_match) {
$firstRowH = asort($lookup_array[$firstColumn]);
}
$rowNumber = $rowValue = False;
foreach ($lookup_array[$firstColumn] as $rowKey => $rowData) {
if (is_numeric($lookup_value) && is_numeric($rowData) && $rowData > $lookup_value || !is_numeric($lookup_value) && !is_numeric($rowData) && strtolower($rowData) > strtolower($lookup_value)) {
break;
}
$rowNumber = $rowKey;
$rowValue = $rowData;
}
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[$returnColumn][$rowNumber];
}
}
return PHPExcel_Calculation_Functions::NA();
}
// function HLOOKUP()
/**
* LOOKUP
* The LOOKUP function searches for value either from a one-row or one-column range or from an array.
* @param lookup_value The value that you want to match in lookup_array
* @param lookup_vector The range of cells being searched
* @param result_vector The column from which the matching value must be returned
* @return mixed The value of the found cell
*/
public static function LOOKUP($lookup_value, $lookup_vector, $result_vector = null) {
$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);
}
}Members
Name |
Modifiers | Type | Description | Overrides |
|---|---|---|---|---|
|
PHPExcel_Calculation_LookupRef:: |
public static | function | * CELL_ADDRESS * * Creates a cell address as text, given specified row and column numbers. * * Excel Function: * =ADDRESS(row, column, [relativity], [referenceStyle], [sheetText]) * * | |
|
PHPExcel_Calculation_LookupRef:: |
public static | function | * 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], ...) * * | |
|
PHPExcel_Calculation_LookupRef:: |
public static | function | * 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… | |
|
PHPExcel_Calculation_LookupRef:: |
public static | function | * COLUMNS * * Returns the number of columns in an array or reference. * * Excel Function: * =COLUMNS(cellAddress) * * | |
|
PHPExcel_Calculation_LookupRef:: |
public static | function | 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. | |
|
PHPExcel_Calculation_LookupRef:: |
public static | function | * HYPERLINK * * Excel Function: * =HYPERLINK(linkURL,displayName) * * @access public * @category Logical Functions * | |
|
PHPExcel_Calculation_LookupRef:: |
public static | function | * INDEX * * Uses an index to choose a value from a reference or array * * Excel Function: * =INDEX(range_array, row_num, [column_num]) * * | |
|
PHPExcel_Calculation_LookupRef:: |
public static | function | * 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… | |
|
PHPExcel_Calculation_LookupRef:: |
public static | function | * LOOKUP * The LOOKUP function searches for value either from a one-row or one-column range or from an array. * | |
|
PHPExcel_Calculation_LookupRef:: |
public static | function | * MATCH * * The MATCH function searches for a specified item in a range of cells * * Excel Function: * =MATCH(lookup_value, lookup_array, [match_type]) * * | |
|
PHPExcel_Calculation_LookupRef:: |
public static | function | * 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… | |
|
PHPExcel_Calculation_LookupRef:: |
public static | function | * 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… | |
|
PHPExcel_Calculation_LookupRef:: |
public static | function | * ROWS * * Returns the number of rows in an array or reference. * * Excel Function: * =ROWS(cellAddress) * * | |
|
PHPExcel_Calculation_LookupRef:: |
public static | function | * TRANSPOSE * * | |
|
PHPExcel_Calculation_LookupRef:: |
public static | function | * 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. * | |
|
PHPExcel_Calculation_LookupRef:: |
private static | function |