34        define(
'PHPEXCEL_ROOT', dirname(__FILE__) . 
'/../../');
 
   35        require(PHPEXCEL_ROOT . 
'PHPExcel/Autoloader.php');
 
   80                if ($sheetText > 
'') {
 
   81                        if (strpos($sheetText,
' ') !== False) { $sheetText = 
"'".$sheetText.
"'"; }
 
   84                if ((!is_bool($referenceStyle)) || $referenceStyle) {
 
   85                        $rowRelative = $columnRelative = 
'$';
 
   87                        if (($relativity == 2) || ($relativity == 4)) { $columnRelative = 
''; }
 
   88                        if (($relativity == 3) || ($relativity == 4)) { $rowRelative = 
''; }
 
   89                        return $sheetText.$columnRelative.$column.$rowRelative.$row;
 
   91                        if (($relativity == 2) || ($relativity == 4)) { 
$column = 
'['.$column.
']'; }
 
   92                        if (($relativity == 3) || ($relativity == 4)) { 
$row = 
'['.$row.
']'; }
 
  112        public static function COLUMN($cellAddress=Null) {
 
  113                if (is_null($cellAddress) || trim($cellAddress) === 
'') { 
return 0; }
 
  115                if (is_array($cellAddress)) {
 
  116                        foreach($cellAddress as $columnKey => $value) {
 
  117                                $columnKey = preg_replace(
'/[^a-z]/i',
'',$columnKey);
 
  121                        if (strpos($cellAddress,
'!') !== 
false) {
 
  122                                list($sheet,$cellAddress) = explode(
'!',$cellAddress);
 
  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();
 
  131                                } 
while ($startAddress++ != $endAddress);
 
  134                                $cellAddress = preg_replace(
'/[^a-z]/i',
'',$cellAddress);
 
  152        public static function COLUMNS($cellAddress=Null) {
 
  153                if (is_null($cellAddress) || $cellAddress === 
'') {
 
  155                } elseif (!is_array($cellAddress)) {
 
  159                $x = array_keys($cellAddress);
 
  160                $x = array_shift(
$x);
 
  161                $isMatrix = (is_numeric(
$x));
 
  186        public static function ROW($cellAddress=Null) {
 
  187                if (is_null($cellAddress) || trim($cellAddress) === 
'') { 
return 0; }
 
  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);
 
  196                        if (strpos($cellAddress,
'!') !== 
false) {
 
  197                                list($sheet,$cellAddress) = explode(
'!',$cellAddress);
 
  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();
 
  205                                        $returnValue[][] = (integer) $startAddress;
 
  206                                } 
while ($startAddress++ != $endAddress);
 
  209                                list($cellAddress) = explode(
':',$cellAddress);
 
  210                                return (integer) preg_replace(
'/[^0-9]/',
'',$cellAddress);
 
  227        public static function ROWS($cellAddress=Null) {
 
  228                if (is_null($cellAddress) || $cellAddress === 
'') {
 
  230                } elseif (!is_array($cellAddress)) {
 
  234                $i = array_keys($cellAddress);
 
  235                $isMatrix = (is_numeric(array_shift(
$i)));
 
  260                $args = func_get_args();
 
  261                $pCell = array_pop($args);
 
  266                if ((!is_object($pCell)) || (trim($linkURL) == 
'')) {
 
  270                if ((is_object($displayName)) || trim($displayName) == 
'') {
 
  271                        $displayName = $linkURL;
 
  274                $pCell->getHyperlink()->setUrl($linkURL);
 
  300                if (is_null($cellAddress) || $cellAddress === 
'') {
 
  304                $cellAddress1 = $cellAddress;
 
  305                $cellAddress2 = NULL;
 
  306                if (strpos($cellAddress,
':') !== 
false) {
 
  307                        list($cellAddress1,$cellAddress2) = explode(
':',$cellAddress);
 
  316                        if (strpos($cellAddress,
'!') !== FALSE) {
 
  317                                list($sheetName, $cellAddress) = explode(
'!',$cellAddress);
 
  318                                $sheetName = trim($sheetName, 
"'");
 
  319                                $pSheet = $pCell->getWorksheet()->getParent()->getSheetByName($sheetName);
 
  321                                $pSheet = $pCell->getWorksheet();
 
  327                if (strpos($cellAddress,
'!') !== FALSE) {
 
  328                        list($sheetName,$cellAddress) = explode(
'!',$cellAddress);
 
  329                        $sheetName = trim($sheetName, 
"'");
 
  330                        $pSheet = $pCell->getWorksheet()->getParent()->getSheetByName($sheetName);
 
  332                        $pSheet = $pCell->getWorksheet();
 
  369                if ($cellAddress == Null) {
 
  373                $args = func_get_args();
 
  374                $pCell = array_pop($args);
 
  375                if (!is_object($pCell)) {
 
  380                if (strpos($cellAddress,
"!")) {
 
  381                        list($sheetName,$cellAddress) = explode(
"!",$cellAddress);
 
  382                        $sheetName = trim($sheetName, 
"'");
 
  384                if (strpos($cellAddress,
":")) {
 
  385                        list($startCell,$endCell) = explode(
":",$cellAddress);
 
  387                        $startCell = $endCell = $cellAddress;
 
  392                $startCellRow += 
$rows;
 
  396                if (($startCellRow <= 0) || ($startCellColumn < 0)) {
 
  400                if (($width != 
null) && (!is_object($width))) {
 
  401                        $endCellColumn = $startCellColumn + $width - 1;
 
  407                if (($height != 
null) && (!is_object($height))) {
 
  408                        $endCellRow = $startCellRow + $height - 1;
 
  410                        $endCellRow += 
$rows;
 
  413                if (($endCellRow <= 0) || ($endCellColumn < 0)) {
 
  418                $cellAddress = $startCellColumn.$startCellRow;
 
  419                if (($startCellColumn != $endCellColumn) || ($startCellRow != $endCellRow)) {
 
  420                        $cellAddress .= 
':'.$endCellColumn.$endCellRow;
 
  423                if ($sheetName !== NULL) {
 
  424                        $pSheet = $pCell->getWorksheet()->getParent()->getSheetByName($sheetName);
 
  426                        $pSheet = $pCell->getWorksheet();
 
  452                $chooseArgs = func_get_args();
 
  454                $entryCount = count($chooseArgs) - 1;
 
  456                if(is_array($chosenEntry)) {
 
  457                        $chosenEntry = array_shift($chosenEntry);
 
  459                if ((is_numeric($chosenEntry)) && (!is_bool($chosenEntry))) {
 
  464                $chosenEntry = floor($chosenEntry);
 
  465                if (($chosenEntry < 0) || ($chosenEntry > $entryCount)) {
 
  469                if (is_array($chooseArgs[$chosenEntry])) {
 
  472                        return $chooseArgs[$chosenEntry];
 
  490        public static function MATCH($lookup_value, $lookup_array, $match_type=1) {
 
  495                $lookup_value = strtolower($lookup_value);
 
  498                if ((!is_numeric($lookup_value)) && (!is_string($lookup_value)) && (!is_bool($lookup_value))) {
 
  503                if (($match_type !== 0) && ($match_type !== -1) && ($match_type !== 1)) {
 
  508                $lookupArraySize = count($lookup_array);
 
  509                if ($lookupArraySize <= 0) {
 
  514                foreach($lookup_array as 
$i => $lookupArrayValue) {
 
  516                        if ((!is_numeric($lookupArrayValue)) && (!is_string($lookupArrayValue)) &&
 
  517                                (!is_bool($lookupArrayValue)) && (!is_null($lookupArrayValue))) {
 
  521                        if (is_string($lookupArrayValue)) {
 
  522                                $lookup_array[
$i] = strtolower($lookupArrayValue);
 
  524                        if ((is_null($lookupArrayValue)) && (($match_type == 1) || ($match_type == -1))) {
 
  525                                $lookup_array = array_slice($lookup_array,0,
$i-1);
 
  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);
 
  544                foreach($lookup_array as 
$i => $lookupArrayValue) {
 
  545                        if (($match_type == 0) && ($lookupArrayValue == $lookup_value)) {
 
  548                        } elseif (($match_type == -1) && ($lookupArrayValue <= $lookup_value)) {
 
  555                                $i = array_search(
$i,$keySet);
 
  563                                        return $keySet[
$i-1]+1;
 
  565                        } elseif (($match_type == 1) && ($lookupArrayValue >= $lookup_value)) {
 
  572                                $i = array_search(
$i,$keySet);
 
  580                                        return $keySet[
$i-1]+1;
 
  603        public static function INDEX($arrayValues,$rowNum = 0,$columnNum = 0) {
 
  605                if (($rowNum < 0) || ($columnNum < 0)) {
 
  609                if (!is_array($arrayValues)) {
 
  613                $rowKeys = array_keys($arrayValues);
 
  614                $columnKeys = @array_keys($arrayValues[$rowKeys[0]]);
 
  616                if ($columnNum > count($columnKeys)) {
 
  618                } elseif ($columnNum == 0) {
 
  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];
 
  629                                                return $arrayValues[$rowNum];
 
  632                                        return $arrayValues[$rowNum];
 
  637                $columnNum = $columnKeys[--$columnNum];
 
  638                if ($rowNum > count($rowKeys)) {
 
  640                } elseif ($rowNum == 0) {
 
  641                        return $arrayValues[$columnNum];
 
  643                $rowNum = $rowKeys[--$rowNum];
 
  645                return $arrayValues[$rowNum][$columnNum];
 
  658                $returnMatrix = array();
 
  659                if (!is_array($matrixData)) { $matrixData = array(array($matrixData)); }
 
  662                foreach($matrixData as $matrixRow) {
 
  664                        foreach($matrixRow as $matrixCell) {
 
  670                return $returnMatrix;
 
  676                $firstColumn = array_shift($f);
 
  677                if (strtolower($a[$firstColumn]) == strtolower($b[$firstColumn])) {
 
  680                return (strtolower($a[$firstColumn]) < strtolower($b[$firstColumn])) ? -1 : 1;
 
  693        public static function VLOOKUP($lookup_value, $lookup_array, $index_number, $not_exact_match=
true) {
 
  699                if ($index_number < 1) {
 
  704                if ((!is_array($lookup_array)) || (empty($lookup_array))) {
 
  707                        $f = array_keys($lookup_array);
 
  708                        $firstRow = array_pop($f);
 
  709                        if ((!is_array($lookup_array[$firstRow])) || ($index_number > count($lookup_array[$firstRow]))) {
 
  712                                $columnKeys = array_keys($lookup_array[$firstRow]);
 
  713                                $returnColumn = $columnKeys[--$index_number];
 
  714                                $firstColumn = array_shift($columnKeys);
 
  718                if (!$not_exact_match) {
 
  719                        uasort($lookup_array,array(
'self',
'_vlookupSort'));
 
  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)))) {
 
  728                        $rowNumber = $rowKey;
 
  729                        $rowValue = $rowData[$firstColumn];
 
  732                if ($rowNumber !== 
false) {
 
  733                        if ((!$not_exact_match) && ($rowValue != $lookup_value)) {
 
  738                                return $lookup_array[$rowNumber][$returnColumn];
 
  755    public static function HLOOKUP($lookup_value, $lookup_array, $index_number, $not_exact_match=
true) {
 
  761        if ($index_number < 1) {
 
  766        if ((!is_array($lookup_array)) || (empty($lookup_array))) {
 
  769            $f = array_keys($lookup_array);
 
  770            $firstRow = array_pop($f);
 
  771            if ((!is_array($lookup_array[$firstRow])) || ($index_number > count($lookup_array[$firstRow]))) {
 
  774                $columnKeys = array_keys($lookup_array[$firstRow]);
 
  775                                $firstkey = $f[0] - 1;
 
  776                $returnColumn = $firstkey + $index_number;
 
  777                $firstColumn = array_shift($f);
 
  781        if (!$not_exact_match) {
 
  782            $firstRowH = asort($lookup_array[$firstColumn]);
 
  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)))) {
 
  791            $rowNumber = $rowKey;
 
  792            $rowValue = $rowData;
 
  795        if ($rowNumber !== 
false) {
 
  796            if ((!$not_exact_match) && ($rowValue != $lookup_value)) {
 
  801                return $lookup_array[$returnColumn][$rowNumber];
 
  817        public static function LOOKUP($lookup_value, $lookup_vector, $result_vector=
null) {
 
  820                if (!is_array($lookup_vector)) {
 
  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))) {
 
  829                        $lookupRows = count($lookup_vector);
 
  830                        $l = array_keys($lookup_vector);
 
  831                        $lookupColumns = count($lookup_vector[array_shift(
$l)]);
 
  834                if (is_null($result_vector)) {
 
  835                        $result_vector = $lookup_vector;
 
  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))) {
 
  843                        $resultRows = count($result_vector);
 
  844                        $r = array_keys($result_vector);
 
  845                        $resultColumns = count($result_vector[array_shift(
$r)]);
 
  848                if ($lookupRows == 2) {
 
  849                        $result_vector = array_pop($lookup_vector);
 
  850                        $lookup_vector = array_shift($lookup_vector);
 
  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);
 
  858                                        $dataValue1 = $value[$key1];
 
  862                                        $dataValue1 = $value;
 
  864                                $dataValue2 = array_shift($result_vector);
 
  865                                if (is_array($dataValue2)) {
 
  866                                        $dataValue2 = array_shift($dataValue2);
 
  868                                $value = array($key1 => $dataValue1, $key2 => $dataValue2);
 
An exception for terminatinating execution or to throw for unit testing.
static flattenSingleValue($value='')
Convert an array to a single scalar value by extracting the first element.
static flattenArray($array)
Convert a multi-dimensional array to a simple 1-dimensional array.
static MATCH($lookup_value, $lookup_array, $match_type=1)
MATCH.
static INDIRECT($cellAddress=NULL, PHPExcel_Cell $pCell=NULL)
INDIRECT.
static VLOOKUP($lookup_value, $lookup_array, $index_number, $not_exact_match=true)
VLOOKUP The VLOOKUP function searches for value in the left-most column of lookup_array and returns t...
static COLUMN($cellAddress=Null)
COLUMN.
static _vlookupSort($a, $b)
static INDEX($arrayValues, $rowNum=0, $columnNum=0)
INDEX.
static OFFSET($cellAddress=Null, $rows=0, $columns=0, $height=null, $width=null)
OFFSET.
static CELL_ADDRESS($row, $column, $relativity=1, $referenceStyle=True, $sheetText='')
CELL_ADDRESS.
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 ar...
static ROW($cellAddress=Null)
ROW.
static HYPERLINK($linkURL='', $displayName=null, PHPExcel_Cell $pCell=null)
static ROWS($cellAddress=Null)
ROWS.
static TRANSPOSE($matrixData)
TRANSPOSE.
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 v...
static COLUMNS($cellAddress=Null)
COLUMNS.
static getInstance(PHPExcel $workbook=NULL)
Get an instance of this class.
static _getMatrixDimensions(&$matrix)
Read the dimensions of a matrix, and re-index it with straight numeric keys starting from row 0,...
const CALCULATION_REGEXP_CELLREF
const CALCULATION_REGEXP_NAMEDRANGE
static stringFromColumnIndex($pColumnIndex=0)
String from columnindex.
static coordinateFromString($pCoordinateString='A1')
Coordinate from string.
static columnIndexFromString($pString='A')
Column index from string.
defined( 'APPLICATION_ENV')||define( 'APPLICATION_ENV'