class PHPExcel_Calculation_LookupRef

PHPExcelCalculationLookupRef

Copyright (c) 2006 - 2015 PHPExcel

This library is free software; you can redistribute it and/or modify it under the terms of the GNU Lesser General Public License as published by the Free Software Foundation; either version 2.1 of the License, or (at your option) any later version.

This library is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more details.

You should have received a copy of the GNU Lesser General Public License along with this library; if not, write to the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA

Methods

static string
CELL_ADDRESS(row $row, column $column, relativity $relativity = 1, referenceStyle $referenceStyle = true, sheetText $sheetText = '')

CELL_ADDRESS

static integer
COLUMN(cellAddress $cellAddress = null)

COLUMN

static integer
COLUMNS(cellAddress $cellAddress = null)

COLUMNS

static integer
ROW(cellAddress $cellAddress = null)

ROW

static integer
ROWS(cellAddress $cellAddress = null)

ROWS

static mixed
HYPERLINK(string $linkURL = '', string $displayName = null, PHPExcel_Cell $pCell = null)

HYPERLINK

static mixed
INDIRECT(cellAddress $cellAddress = null, PHPExcel_Cell $pCell = null)

INDIRECT

static string
OFFSET(cellAddress $cellAddress = null, rows $rows, cols $columns, height $height = null, width $width = null)

OFFSET

static mixed
CHOOSE()

CHOOSE

static integer
MATCH(lookup_value $lookup_value, lookup_array $lookup_array, match_type $match_type = 1)

MATCH

static mixed
INDEX(range_array $arrayValues, row_num $rowNum, column_num $columnNum)

INDEX

static array
TRANSPOSE(array $matrixData)

TRANSPOSE

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

VLOOKUP The VLOOKUP function searches for value in the left-most column of lookuparray and returns the value in the same row based on the indexnumber.

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

HLOOKUP The HLOOKUP function searches for value in the top-most row of lookuparray and returns the value in the same column based on the indexnumber.

static mixed
LOOKUP(lookup_value $lookup_value, lookup_vector $lookup_vector, result_vector $result_vector = null)

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

Details

at line 80
static string CELL_ADDRESS(row $row, column $column, relativity $relativity = 1, referenceStyle $referenceStyle = true, sheetText $sheetText = '')

CELL_ADDRESS

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

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

Parameters

row $row Row number to use in the cell reference
column $column Column number to use in the cell reference
relativity $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
referenceStyle $referenceStyle A logical value that specifies the A1 or R1C1 reference style. TRUE or omitted CELLADDRESS returns an A1-style reference FALSE CELLADDRESS returns an R1C1-style reference
sheetText $sheetText Optional Name of worksheet to use

Return Value

string

at line 133
static integer COLUMN(cellAddress $cellAddress = null)

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

cellAddress $cellAddress A reference to a range of cells for which you want the column numbers

Return Value

integer or array of integer

at line 176
static integer COLUMNS(cellAddress $cellAddress = null)

COLUMNS

Returns the number of columns in an array or reference.

Excel Function: =COLUMNS(cellAddress)

Parameters

cellAddress $cellAddress An array or array formula, or a reference to a range of cells for which you want the number of columns

Return Value

integer The number of columns in cellAddress

at line 210
static integer ROW(cellAddress $cellAddress = null)

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

cellAddress $cellAddress A reference to a range of cells for which you want the row numbers

Return Value

integer or array of integer

at line 254
static integer ROWS(cellAddress $cellAddress = null)

ROWS

Returns the number of rows in an array or reference.

Excel Function: =ROWS(cellAddress)

Parameters

cellAddress $cellAddress An array or array formula, or a reference to a range of cells for which you want the number of rows

Return Value

integer The number of rows in cellAddress

HYPERLINK

Excel Function: =HYPERLINK(linkURL,displayName)

Parameters

string $linkURL Value to check, is also the value returned when no error
string $displayName Value to return when testValue is an error condition
PHPExcel_Cell $pCell The cell to set the hyperlink in

Return Value

mixed The value of $displayName (or $linkURL if $displayName was blank)

at line 328
static mixed INDIRECT(cellAddress $cellAddress = null, PHPExcel_Cell $pCell = null)

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 $cellAddress The cell address of the current cell (containing this formula)
PHPExcel_Cell $pCell The current cell (containing this formula)

Return Value

mixed The cells referenced by cellAddress

at line 395
static string OFFSET(cellAddress $cellAddress = null, rows $rows, cols $columns, height $height = null, width $width = null)

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

cellAddress $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.
rows $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).
cols $columns 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).
height $height The height, in number of rows, that you want the returned reference to be. Height must be a positive number.
width $width The width, in number of columns, that you want the returned reference to be. Width must be a positive number.

Return Value

string A reference to a cell or range of cells

at line 483
static mixed CHOOSE()

CHOOSE

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

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

Return Value

mixed The selected value

at line 523
static integer MATCH(lookup_value $lookup_value, lookup_array $lookup_array, match_type $match_type = 1)

MATCH

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

Excel Function: =MATCH(lookupvalue, lookuparray, [match_type])

Parameters

lookup_value $lookup_value The value that you want to match in lookup_array
lookup_array $lookup_array The range of cells being searched
match_type $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 Value

integer The relative position of the found item

at line 620
static mixed INDEX(range_array $arrayValues, row_num $rowNum, column_num $columnNum)

INDEX

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

Excel Function: =INDEX(rangearray, rownum, [column_num])

Parameters

range_array $arrayValues A range of cells or an array constant
row_num $rowNum The row in array from which to return a value. If rownum is omitted, columnnum is required.
column_num $columnNum The column in array from which to return a value. If columnnum is omitted, rownum is required.

Return Value

mixed the value of a specified cell or array of cells

at line 674
static array TRANSPOSE(array $matrixData)

TRANSPOSE

Parameters

array $matrixData A matrix of values

Return Value

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

at line 714
static mixed VLOOKUP(lookup_value $lookup_value, lookup_array $lookup_array, index_number $index_number, not_exact_match $not_exact_match = true)

VLOOKUP The VLOOKUP function searches for value in the left-most column of lookuparray and returns the value in the same row based on the indexnumber.

Parameters

lookup_value $lookup_value The value that you want to match in lookup_array
lookup_array $lookup_array The range of cells being searched
index_number $index_number The column number in table_array from which the matching value must be returned. The first column is 1.
not_exact_match $not_exact_match Determines if you are looking for an exact match based on lookup_value.

Return Value

mixed The value of the found cell

at line 777
static mixed HLOOKUP(lookup_value $lookup_value, lookup_array $lookup_array, index_number $index_number, not_exact_match $not_exact_match = true)

HLOOKUP The HLOOKUP function searches for value in the top-most row of lookuparray and returns the value in the same column based on the indexnumber.

Parameters

lookup_value $lookup_value The value that you want to match in lookup_array
lookup_array $lookup_array The range of cells being searched
index_number $index_number The row number in table_array from which the matching value must be returned. The first row is 1.
not_exact_match $not_exact_match Determines if you are looking for an exact match based on lookup_value.

Return Value

mixed The value of the found cell

at line 840
static mixed LOOKUP(lookup_value $lookup_value, lookup_vector $lookup_vector, result_vector $result_vector = null)

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

Parameters

lookup_value $lookup_value The value that you want to match in lookup_array
lookup_vector $lookup_vector The range of cells being searched
result_vector $result_vector The column from which the matching value must be returned

Return Value

mixed The value of the found cell