1: <?php
  2: /**
  3:  * PHPExcel
  4:  *
  5:  * Copyright (c) 2006 - 2014 PHPExcel
  6:  *
  7:  * This library is free software; you can redistribute it and/or
  8:  * modify it under the terms of the GNU Lesser General Public
  9:  * License as published by the Free Software Foundation; either
 10:  * version 2.1 of the License, or (at your option) any later version.
 11:  *
 12:  * This library is distributed in the hope that it will be useful,
 13:  * but WITHOUT ANY WARRANTY; without even the implied warranty of
 14:  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
 15:  * Lesser General Public License for more details.
 16:  *
 17:  * You should have received a copy of the GNU Lesser General Public
 18:  * License along with this library; if not, write to the Free Software
 19:  * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
 20:  *
 21:  * @category    PHPExcel
 22:  * @package     PHPExcel_Calculation
 23:  * @copyright   Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel)
 24:  * @license     http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt   LGPL
 25:  * @version     1.8.0, 2014-03-02
 26:  */
 27: 
 28: 
 29: /** PHPExcel root directory */
 30: if (!defined('PHPEXCEL_ROOT')) {
 31:     /**
 32:      * @ignore
 33:      */
 34:     define('PHPEXCEL_ROOT', dirname(__FILE__) . '/../../');
 35:     require(PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php');
 36: }
 37: 
 38: 
 39: /**
 40:  * PHPExcel_Calculation_LookupRef
 41:  *
 42:  * @category    PHPExcel
 43:  * @package     PHPExcel_Calculation
 44:  * @copyright   Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel)
 45:  */
 46: class PHPExcel_Calculation_LookupRef {
 47: 
 48: 
 49:     /**
 50:      * CELL_ADDRESS
 51:      *
 52:      * Creates a cell address as text, given specified row and column numbers.
 53:      *
 54:      * Excel Function:
 55:      *      =ADDRESS(row, column, [relativity], [referenceStyle], [sheetText])
 56:      *
 57:      * @param   row             Row number to use in the cell reference
 58:      * @param   column          Column number to use in the cell reference
 59:      * @param   relativity      Flag indicating the type of reference to return
 60:      *                              1 or omitted    Absolute
 61:      *                              2               Absolute row; relative column
 62:      *                              3               Relative row; absolute column
 63:      *                              4               Relative
 64:      * @param   referenceStyle  A logical value that specifies the A1 or R1C1 reference style.
 65:      *                              TRUE or omitted     CELL_ADDRESS returns an A1-style reference
 66:      *                              FALSE               CELL_ADDRESS returns an R1C1-style reference
 67:      * @param   sheetText       Optional Name of worksheet to use
 68:      * @return  string
 69:      */
 70:     public static function CELL_ADDRESS($row, $column, $relativity=1, $referenceStyle=True, $sheetText='') {
 71:         $row        = PHPExcel_Calculation_Functions::flattenSingleValue($row);
 72:         $column     = PHPExcel_Calculation_Functions::flattenSingleValue($column);
 73:         $relativity = PHPExcel_Calculation_Functions::flattenSingleValue($relativity);
 74:         $sheetText  = PHPExcel_Calculation_Functions::flattenSingleValue($sheetText);
 75: 
 76:         if (($row < 1) || ($column < 1)) {
 77:             return PHPExcel_Calculation_Functions::VALUE();
 78:         }
 79: 
 80:         if ($sheetText > '') {
 81:             if (strpos($sheetText,' ') !== False) { $sheetText = "'".$sheetText."'"; }
 82:             $sheetText .='!';
 83:         }
 84:         if ((!is_bool($referenceStyle)) || $referenceStyle) {
 85:             $rowRelative = $columnRelative = '$';
 86:             $column = PHPExcel_Cell::stringFromColumnIndex($column-1);
 87:             if (($relativity == 2) || ($relativity == 4)) { $columnRelative = ''; }
 88:             if (($relativity == 3) || ($relativity == 4)) { $rowRelative = ''; }
 89:             return $sheetText.$columnRelative.$column.$rowRelative.$row;
 90:         } else {
 91:             if (($relativity == 2) || ($relativity == 4)) { $column = '['.$column.']'; }
 92:             if (($relativity == 3) || ($relativity == 4)) { $row = '['.$row.']'; }
 93:             return $sheetText.'R'.$row.'C'.$column;
 94:         }
 95:     }   //  function CELL_ADDRESS()
 96: 
 97: 
 98:     /**
 99:      * COLUMN
100:      *
101:      * Returns the column number of the given cell reference
102:      * If the cell reference is a range of cells, COLUMN returns the column numbers of each column in the reference as a horizontal array.
103:      * If cell reference is omitted, and the function is being called through the calculation engine, then it is assumed to be the
104:      *      reference of the cell in which the COLUMN function appears; otherwise this function returns 0.
105:      *
106:      * Excel Function:
107:      *      =COLUMN([cellAddress])
108:      *
109:      * @param   cellAddress     A reference to a range of cells for which you want the column numbers
110:      * @return  integer or array of integer
111:      */
112:     public static function COLUMN($cellAddress=Null) {
113:         if (is_null($cellAddress) || trim($cellAddress) === '') { return 0; }
114: 
115:         if (is_array($cellAddress)) {
116:             foreach($cellAddress as $columnKey => $value) {
117:                 $columnKey = preg_replace('/[^a-z]/i','',$columnKey);
118:                 return (integer) PHPExcel_Cell::columnIndexFromString($columnKey);
119:             }
120:         } else {
121:             if (strpos($cellAddress,'!') !== false) {
122:                 list($sheet,$cellAddress) = explode('!',$cellAddress);
123:             }
124:             if (strpos($cellAddress,':') !== false) {
125:                 list($startAddress,$endAddress) = explode(':',$cellAddress);
126:                 $startAddress = preg_replace('/[^a-z]/i','',$startAddress);
127:                 $endAddress = preg_replace('/[^a-z]/i','',$endAddress);
128:                 $returnValue = array();
129:                 do {
130:                     $returnValue[] = (integer) PHPExcel_Cell::columnIndexFromString($startAddress);
131:                 } while ($startAddress++ != $endAddress);
132:                 return $returnValue;
133:             } else {
134:                 $cellAddress = preg_replace('/[^a-z]/i','',$cellAddress);
135:                 return (integer) PHPExcel_Cell::columnIndexFromString($cellAddress);
136:             }
137:         }
138:     }   //  function COLUMN()
139: 
140: 
141:     /**
142:      * COLUMNS
143:      *
144:      * Returns the number of columns in an array or reference.
145:      *
146:      * Excel Function:
147:      *      =COLUMNS(cellAddress)
148:      *
149:      * @param   cellAddress     An array or array formula, or a reference to a range of cells for which you want the number of columns
150:      * @return  integer         The number of columns in cellAddress
151:      */
152:     public static function COLUMNS($cellAddress=Null) {
153:         if (is_null($cellAddress) || $cellAddress === '') {
154:             return 1;
155:         } elseif (!is_array($cellAddress)) {
156:             return PHPExcel_Calculation_Functions::VALUE();
157:         }
158: 
159:         $x = array_keys($cellAddress);
160:         $x = array_shift($x);
161:         $isMatrix = (is_numeric($x));
162:         list($columns,$rows) = PHPExcel_Calculation::_getMatrixDimensions($cellAddress);
163: 
164:         if ($isMatrix) {
165:             return $rows;
166:         } else {
167:             return $columns;
168:         }
169:     }   //  function COLUMNS()
170: 
171: 
172:     /**
173:      * ROW
174:      *
175:      * Returns the row number of the given cell reference
176:      * If the cell reference is a range of cells, ROW returns the row numbers of each row in the reference as a vertical array.
177:      * If cell reference is omitted, and the function is being called through the calculation engine, then it is assumed to be the
178:      *      reference of the cell in which the ROW function appears; otherwise this function returns 0.
179:      *
180:      * Excel Function:
181:      *      =ROW([cellAddress])
182:      *
183:      * @param   cellAddress     A reference to a range of cells for which you want the row numbers
184:      * @return  integer or array of integer
185:      */
186:     public static function ROW($cellAddress=Null) {
187:         if (is_null($cellAddress) || trim($cellAddress) === '') { return 0; }
188: 
189:         if (is_array($cellAddress)) {
190:             foreach($cellAddress as $columnKey => $rowValue) {
191:                 foreach($rowValue as $rowKey => $cellValue) {
192:                     return (integer) preg_replace('/[^0-9]/i','',$rowKey);
193:                 }
194:             }
195:         } else {
196:             if (strpos($cellAddress,'!') !== false) {
197:                 list($sheet,$cellAddress) = explode('!',$cellAddress);
198:             }
199:             if (strpos($cellAddress,':') !== false) {
200:                 list($startAddress,$endAddress) = explode(':',$cellAddress);
201:                 $startAddress = preg_replace('/[^0-9]/','',$startAddress);
202:                 $endAddress = preg_replace('/[^0-9]/','',$endAddress);
203:                 $returnValue = array();
204:                 do {
205:                     $returnValue[][] = (integer) $startAddress;
206:                 } while ($startAddress++ != $endAddress);
207:                 return $returnValue;
208:             } else {
209:                 list($cellAddress) = explode(':',$cellAddress);
210:                 return (integer) preg_replace('/[^0-9]/','',$cellAddress);
211:             }
212:         }
213:     }   //  function ROW()
214: 
215: 
216:     /**
217:      * ROWS
218:      *
219:      * Returns the number of rows in an array or reference.
220:      *
221:      * Excel Function:
222:      *      =ROWS(cellAddress)
223:      *
224:      * @param   cellAddress     An array or array formula, or a reference to a range of cells for which you want the number of rows
225:      * @return  integer         The number of rows in cellAddress
226:      */
227:     public static function ROWS($cellAddress=Null) {
228:         if (is_null($cellAddress) || $cellAddress === '') {
229:             return 1;
230:         } elseif (!is_array($cellAddress)) {
231:             return PHPExcel_Calculation_Functions::VALUE();
232:         }
233: 
234:         $i = array_keys($cellAddress);
235:         $isMatrix = (is_numeric(array_shift($i)));
236:         list($columns,$rows) = PHPExcel_Calculation::_getMatrixDimensions($cellAddress);
237: 
238:         if ($isMatrix) {
239:             return $columns;
240:         } else {
241:             return $rows;
242:         }
243:     }   //  function ROWS()
244: 
245: 
246:     /**
247:      * HYPERLINK
248:      *
249:      * Excel Function:
250:      *      =HYPERLINK(linkURL,displayName)
251:      *
252:      * @access  public
253:      * @category Logical Functions
254:      * @param   string          $linkURL        Value to check, is also the value returned when no error
255:      * @param   string          $displayName    Value to return when testValue is an error condition
256:      * @param   PHPExcel_Cell   $pCell          The cell to set the hyperlink in
257:      * @return  mixed   The value of $displayName (or $linkURL if $displayName was blank)
258:      */
259:     public static function HYPERLINK($linkURL = '', $displayName = null, PHPExcel_Cell $pCell = null) {
260:         $args = func_get_args();
261:         $pCell = array_pop($args);
262: 
263:         $linkURL        = (is_null($linkURL))       ? '' :  PHPExcel_Calculation_Functions::flattenSingleValue($linkURL);
264:         $displayName    = (is_null($displayName))   ? '' :  PHPExcel_Calculation_Functions::flattenSingleValue($displayName);
265: 
266:         if ((!is_object($pCell)) || (trim($linkURL) == '')) {
267:             return PHPExcel_Calculation_Functions::REF();
268:         }
269: 
270:         if ((is_object($displayName)) || trim($displayName) == '') {
271:             $displayName = $linkURL;
272:         }
273: 
274:         $pCell->getHyperlink()->setUrl($linkURL);
275: 
276:         return $displayName;
277:     }   //  function HYPERLINK()
278: 
279: 
280:     /**
281:      * INDIRECT
282:      *
283:      * Returns the reference specified by a text string.
284:      * References are immediately evaluated to display their contents.
285:      *
286:      * Excel Function:
287:      *      =INDIRECT(cellAddress)
288:      *
289:      * NOTE - INDIRECT() does not yet support the optional a1 parameter introduced in Excel 2010
290:      *
291:      * @param   cellAddress     $cellAddress    The cell address of the current cell (containing this formula)
292:      * @param   PHPExcel_Cell   $pCell          The current cell (containing this formula)
293:      * @return  mixed           The cells referenced by cellAddress
294:      *
295:      * @todo    Support for the optional a1 parameter introduced in Excel 2010
296:      *
297:      */
298:     public static function INDIRECT($cellAddress = NULL, PHPExcel_Cell $pCell = NULL) {
299:         $cellAddress    = PHPExcel_Calculation_Functions::flattenSingleValue($cellAddress);
300:         if (is_null($cellAddress) || $cellAddress === '') {
301:             return PHPExcel_Calculation_Functions::REF();
302:         }
303: 
304:         $cellAddress1 = $cellAddress;
305:         $cellAddress2 = NULL;
306:         if (strpos($cellAddress,':') !== false) {
307:             list($cellAddress1,$cellAddress2) = explode(':',$cellAddress);
308:         }
309: 
310:         if ((!preg_match('/^'.PHPExcel_Calculation::CALCULATION_REGEXP_CELLREF.'$/i', $cellAddress1, $matches)) ||
311:             ((!is_null($cellAddress2)) && (!preg_match('/^'.PHPExcel_Calculation::CALCULATION_REGEXP_CELLREF.'$/i', $cellAddress2, $matches)))) {
312:             if (!preg_match('/^'.PHPExcel_Calculation::CALCULATION_REGEXP_NAMEDRANGE.'$/i', $cellAddress1, $matches)) {
313:                 return PHPExcel_Calculation_Functions::REF();
314:             }
315: 
316:             if (strpos($cellAddress,'!') !== FALSE) {
317:                 list($sheetName, $cellAddress) = explode('!',$cellAddress);
318:                 $sheetName = trim($sheetName, "'");
319:                 $pSheet = $pCell->getWorksheet()->getParent()->getSheetByName($sheetName);
320:             } else {
321:                 $pSheet = $pCell->getWorksheet();
322:             }
323: 
324:             return PHPExcel_Calculation::getInstance()->extractNamedRange($cellAddress, $pSheet, FALSE);
325:         }
326: 
327:         if (strpos($cellAddress,'!') !== FALSE) {
328:             list($sheetName,$cellAddress) = explode('!',$cellAddress);
329:             $sheetName = trim($sheetName, "'");
330:             $pSheet = $pCell->getWorksheet()->getParent()->getSheetByName($sheetName);
331:         } else {
332:             $pSheet = $pCell->getWorksheet();
333:         }
334: 
335:         return PHPExcel_Calculation::getInstance()->extractCellRange($cellAddress, $pSheet, FALSE);
336:     }   //  function INDIRECT()
337: 
338: 
339:     /**
340:      * OFFSET
341:      *
342:      * Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells.
343:      * The reference that is returned can be a single cell or a range of cells. You can specify the number of rows and
344:      * the number of columns to be returned.
345:      *
346:      * Excel Function:
347:      *      =OFFSET(cellAddress, rows, cols, [height], [width])
348:      *
349:      * @param   cellAddress     The reference from which you want to base the offset. Reference must refer to a cell or
350:      *                              range of adjacent cells; otherwise, OFFSET returns the #VALUE! error value.
351:      * @param   rows            The number of rows, up or down, that you want the upper-left cell to refer to.
352:      *                              Using 5 as the rows argument specifies that the upper-left cell in the reference is
353:      *                              five rows below reference. Rows can be positive (which means below the starting reference)
354:      *                              or negative (which means above the starting reference).
355:      * @param   cols            The number of columns, to the left or right, that you want the upper-left cell of the result
356:      *                              to refer to. Using 5 as the cols argument specifies that the upper-left cell in the
357:      *                              reference is five columns to the right of reference. Cols can be positive (which means
358:      *                              to the right of the starting reference) or negative (which means to the left of the
359:      *                              starting reference).
360:      * @param   height          The height, in number of rows, that you want the returned reference to be. Height must be a positive number.
361:      * @param   width           The width, in number of columns, that you want the returned reference to be. Width must be a positive number.
362:      * @return  string          A reference to a cell or range of cells
363:      */
364:     public static function OFFSET($cellAddress=Null,$rows=0,$columns=0,$height=null,$width=null) {
365:         $rows       = PHPExcel_Calculation_Functions::flattenSingleValue($rows);
366:         $columns    = PHPExcel_Calculation_Functions::flattenSingleValue($columns);
367:         $height     = PHPExcel_Calculation_Functions::flattenSingleValue($height);
368:         $width      = PHPExcel_Calculation_Functions::flattenSingleValue($width);
369:         if ($cellAddress == Null) {
370:             return 0;
371:         }
372: 
373:         $args = func_get_args();
374:         $pCell = array_pop($args);
375:         if (!is_object($pCell)) {
376:             return PHPExcel_Calculation_Functions::REF();
377:         }
378: 
379:         $sheetName = NULL;
380:         if (strpos($cellAddress,"!")) {
381:             list($sheetName,$cellAddress) = explode("!",$cellAddress);
382:             $sheetName = trim($sheetName, "'");
383:         }
384:         if (strpos($cellAddress,":")) {
385:             list($startCell,$endCell) = explode(":",$cellAddress);
386:         } else {
387:             $startCell = $endCell = $cellAddress;
388:         }
389:         list($startCellColumn,$startCellRow) = PHPExcel_Cell::coordinateFromString($startCell);
390:         list($endCellColumn,$endCellRow) = PHPExcel_Cell::coordinateFromString($endCell);
391: 
392:         $startCellRow += $rows;
393:         $startCellColumn = PHPExcel_Cell::columnIndexFromString($startCellColumn) - 1;
394:         $startCellColumn += $columns;
395: 
396:         if (($startCellRow <= 0) || ($startCellColumn < 0)) {
397:             return PHPExcel_Calculation_Functions::REF();
398:         }
399:         $endCellColumn = PHPExcel_Cell::columnIndexFromString($endCellColumn) - 1;
400:         if (($width != null) && (!is_object($width))) {
401:             $endCellColumn = $startCellColumn + $width - 1;
402:         } else {
403:             $endCellColumn += $columns;
404:         }
405:         $startCellColumn = PHPExcel_Cell::stringFromColumnIndex($startCellColumn);
406: 
407:         if (($height != null) && (!is_object($height))) {
408:             $endCellRow = $startCellRow + $height - 1;
409:         } else {
410:             $endCellRow += $rows;
411:         }
412: 
413:         if (($endCellRow <= 0) || ($endCellColumn < 0)) {
414:             return PHPExcel_Calculation_Functions::REF();
415:         }
416:         $endCellColumn = PHPExcel_Cell::stringFromColumnIndex($endCellColumn);
417: 
418:         $cellAddress = $startCellColumn.$startCellRow;
419:         if (($startCellColumn != $endCellColumn) || ($startCellRow != $endCellRow)) {
420:             $cellAddress .= ':'.$endCellColumn.$endCellRow;
421:         }
422: 
423:         if ($sheetName !== NULL) {
424:             $pSheet = $pCell->getWorksheet()->getParent()->getSheetByName($sheetName);
425:         } else {
426:             $pSheet = $pCell->getWorksheet();
427:         }
428: 
429:         return PHPExcel_Calculation::getInstance()->extractCellRange($cellAddress, $pSheet, False);
430:     }   //  function OFFSET()
431: 
432: 
433:     /**
434:      * CHOOSE
435:      *
436:      * Uses lookup_value to return a value from the list of value arguments.
437:      * Use CHOOSE to select one of up to 254 values based on the lookup_value.
438:      *
439:      * Excel Function:
440:      *      =CHOOSE(index_num, value1, [value2], ...)
441:      *
442:      * @param   index_num       Specifies which value argument is selected.
443:      *                          Index_num must be a number between 1 and 254, or a formula or reference to a cell containing a number
444:      *                              between 1 and 254.
445:      * @param   value1...       Value1 is required, subsequent values are optional.
446:      *                          Between 1 to 254 value arguments from which CHOOSE selects a value or an action to perform based on
447:      *                              index_num. The arguments can be numbers, cell references, defined names, formulas, functions, or
448:      *                              text.
449:      * @return  mixed           The selected value
450:      */
451:     public static function CHOOSE() {
452:         $chooseArgs = func_get_args();
453:         $chosenEntry = PHPExcel_Calculation_Functions::flattenArray(array_shift($chooseArgs));
454:         $entryCount = count($chooseArgs) - 1;
455: 
456:         if(is_array($chosenEntry)) {
457:             $chosenEntry = array_shift($chosenEntry);
458:         }
459:         if ((is_numeric($chosenEntry)) && (!is_bool($chosenEntry))) {
460:             --$chosenEntry;
461:         } else {
462:             return PHPExcel_Calculation_Functions::VALUE();
463:         }
464:         $chosenEntry = floor($chosenEntry);
465:         if (($chosenEntry < 0) || ($chosenEntry > $entryCount)) {
466:             return PHPExcel_Calculation_Functions::VALUE();
467:         }
468: 
469:         if (is_array($chooseArgs[$chosenEntry])) {
470:             return PHPExcel_Calculation_Functions::flattenArray($chooseArgs[$chosenEntry]);
471:         } else {
472:             return $chooseArgs[$chosenEntry];
473:         }
474:     }   //  function CHOOSE()
475: 
476: 
477:     /**
478:      * MATCH
479:      *
480:      * The MATCH function searches for a specified item in a range of cells
481:      *
482:      * Excel Function:
483:      *      =MATCH(lookup_value, lookup_array, [match_type])
484:      *
485:      * @param   lookup_value    The value that you want to match in lookup_array
486:      * @param   lookup_array    The range of cells being searched
487:      * @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.
488:      * @return  integer         The relative position of the found item
489:      */
490:     public static function MATCH($lookup_value, $lookup_array, $match_type=1) {
491:         $lookup_array = PHPExcel_Calculation_Functions::flattenArray($lookup_array);
492:         $lookup_value = PHPExcel_Calculation_Functions::flattenSingleValue($lookup_value);
493:         $match_type = (is_null($match_type)) ? 1 : (int) PHPExcel_Calculation_Functions::flattenSingleValue($match_type);
494:         //  MATCH is not case sensitive
495:         $lookup_value = strtolower($lookup_value);
496: 
497:         //  lookup_value type has to be number, text, or logical values
498:         if ((!is_numeric($lookup_value)) && (!is_string($lookup_value)) && (!is_bool($lookup_value))) {
499:             return PHPExcel_Calculation_Functions::NA();
500:         }
501: 
502:         //  match_type is 0, 1 or -1
503:         if (($match_type !== 0) && ($match_type !== -1) && ($match_type !== 1)) {
504:             return PHPExcel_Calculation_Functions::NA();
505:         }
506: 
507:         //  lookup_array should not be empty
508:         $lookupArraySize = count($lookup_array);
509:         if ($lookupArraySize <= 0) {
510:             return PHPExcel_Calculation_Functions::NA();
511:         }
512: 
513:         //  lookup_array should contain only number, text, or logical values, or empty (null) cells
514:         foreach($lookup_array as $i => $lookupArrayValue) {
515:             //  check the type of the value
516:             if ((!is_numeric($lookupArrayValue)) && (!is_string($lookupArrayValue)) &&
517:                 (!is_bool($lookupArrayValue)) && (!is_null($lookupArrayValue))) {
518:                 return PHPExcel_Calculation_Functions::NA();
519:             }
520:             //  convert strings to lowercase for case-insensitive testing
521:             if (is_string($lookupArrayValue)) {
522:                 $lookup_array[$i] = strtolower($lookupArrayValue);
523:             }
524:             if ((is_null($lookupArrayValue)) && (($match_type == 1) || ($match_type == -1))) {
525:                 $lookup_array = array_slice($lookup_array,0,$i-1);
526:             }
527:         }
528: 
529:         // if match_type is 1 or -1, the list has to be ordered
530:         if ($match_type == 1) {
531:             asort($lookup_array);
532:             $keySet = array_keys($lookup_array);
533:         } elseif($match_type == -1) {
534:             arsort($lookup_array);
535:             $keySet = array_keys($lookup_array);
536:         }
537: 
538:         // **
539:         // find the match
540:         // **
541:         // loop on the cells
542: //      var_dump($lookup_array);
543: //      echo '<br />';
544:         foreach($lookup_array as $i => $lookupArrayValue) {
545:             if (($match_type == 0) && ($lookupArrayValue == $lookup_value)) {
546:                 //  exact match
547:                 return ++$i;
548:             } elseif (($match_type == -1) && ($lookupArrayValue <= $lookup_value)) {
549: //              echo '$i = '.$i.' => ';
550: //              var_dump($lookupArrayValue);
551: //              echo '<br />';
552: //              echo 'Keyset = ';
553: //              var_dump($keySet);
554: //              echo '<br />';
555:                 $i = array_search($i,$keySet);
556: //              echo '$i='.$i.'<br />';
557:                 // if match_type is -1 <=> find the smallest value that is greater than or equal to lookup_value
558:                 if ($i < 1){
559:                     // 1st cell was allready smaller than the lookup_value
560:                     break;
561:                 } else {
562:                     // the previous cell was the match
563:                     return $keySet[$i-1]+1;
564:                 }
565:             } elseif (($match_type == 1) && ($lookupArrayValue >= $lookup_value)) {
566: //              echo '$i = '.$i.' => ';
567: //              var_dump($lookupArrayValue);
568: //              echo '<br />';
569: //              echo 'Keyset = ';
570: //              var_dump($keySet);
571: //              echo '<br />';
572:                 $i = array_search($i,$keySet);
573: //              echo '$i='.$i.'<br />';
574:                 // if match_type is 1 <=> find the largest value that is less than or equal to lookup_value
575:                 if ($i < 1){
576:                     // 1st cell was allready bigger than the lookup_value
577:                     break;
578:                 } else {
579:                     // the previous cell was the match
580:                     return $keySet[$i-1]+1;
581:                 }
582:             }
583:         }
584: 
585:         //  unsuccessful in finding a match, return #N/A error value
586:         return PHPExcel_Calculation_Functions::NA();
587:     }   //  function MATCH()
588: 
589: 
590:     /**
591:      * INDEX
592:      *
593:      * Uses an index to choose a value from a reference or array
594:      *
595:      * Excel Function:
596:      *      =INDEX(range_array, row_num, [column_num])
597:      *
598:      * @param   range_array     A range of cells or an array constant
599:      * @param   row_num         The row in array from which to return a value. If row_num is omitted, column_num is required.
600:      * @param   column_num      The column in array from which to return a value. If column_num is omitted, row_num is required.
601:      * @return  mixed           the value of a specified cell or array of cells
602:      */
603:     public static function INDEX($arrayValues,$rowNum = 0,$columnNum = 0) {
604: 
605:         if (($rowNum < 0) || ($columnNum < 0)) {
606:             return PHPExcel_Calculation_Functions::VALUE();
607:         }
608: 
609:         if (!is_array($arrayValues)) {
610:             return PHPExcel_Calculation_Functions::REF();
611:         }
612: 
613:         $rowKeys = array_keys($arrayValues);
614:         $columnKeys = @array_keys($arrayValues[$rowKeys[0]]);
615: 
616:         if ($columnNum > count($columnKeys)) {
617:             return PHPExcel_Calculation_Functions::VALUE();
618:         } elseif ($columnNum == 0) {
619:             if ($rowNum == 0) {
620:                 return $arrayValues;
621:             }
622:             $rowNum = $rowKeys[--$rowNum];
623:             $returnArray = array();
624:             foreach($arrayValues as $arrayColumn) {
625:                 if (is_array($arrayColumn)) {
626:                     if (isset($arrayColumn[$rowNum])) {
627:                         $returnArray[] = $arrayColumn[$rowNum];
628:                     } else {
629:                         return $arrayValues[$rowNum];
630:                     }
631:                 } else {
632:                     return $arrayValues[$rowNum];
633:                 }
634:             }
635:             return $returnArray;
636:         }
637:         $columnNum = $columnKeys[--$columnNum];
638:         if ($rowNum > count($rowKeys)) {
639:             return PHPExcel_Calculation_Functions::VALUE();
640:         } elseif ($rowNum == 0) {
641:             return $arrayValues[$columnNum];
642:         }
643:         $rowNum = $rowKeys[--$rowNum];
644: 
645:         return $arrayValues[$rowNum][$columnNum];
646:     }   //  function INDEX()
647: 
648: 
649:     /**
650:      * TRANSPOSE
651:      *
652:      * @param   array   $matrixData A matrix of values
653:      * @return  array
654:      *
655:      * Unlike the Excel TRANSPOSE function, which will only work on a single row or column, this function will transpose a full matrix.
656:      */
657:     public static function TRANSPOSE($matrixData) {
658:         $returnMatrix = array();
659:         if (!is_array($matrixData)) { $matrixData = array(array($matrixData)); }
660: 
661:         $column = 0;
662:         foreach($matrixData as $matrixRow) {
663:             $row = 0;
664:             foreach($matrixRow as $matrixCell) {
665:                 $returnMatrix[$row][$column] = $matrixCell;
666:                 ++$row;
667:             }
668:             ++$column;
669:         }
670:         return $returnMatrix;
671:     }   //  function TRANSPOSE()
672: 
673: 
674:     private static function _vlookupSort($a,$b) {
675:         $f = array_keys($a);
676:         $firstColumn = array_shift($f);
677:         if (strtolower($a[$firstColumn]) == strtolower($b[$firstColumn])) {
678:             return 0;
679:         }
680:         return (strtolower($a[$firstColumn]) < strtolower($b[$firstColumn])) ? -1 : 1;
681:     }   //  function _vlookupSort()
682: 
683: 
684:     /**
685:     * VLOOKUP
686:     * 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.
687:     * @param    lookup_value    The value that you want to match in lookup_array
688:     * @param    lookup_array    The range of cells being searched
689:     * @param    index_number    The column number in table_array from which the matching value must be returned. The first column is 1.
690:     * @param    not_exact_match Determines if you are looking for an exact match based on lookup_value.
691:     * @return   mixed           The value of the found cell
692:     */
693:     public static function VLOOKUP($lookup_value, $lookup_array, $index_number, $not_exact_match=true) {
694:         $lookup_value   = PHPExcel_Calculation_Functions::flattenSingleValue($lookup_value);
695:         $index_number   = PHPExcel_Calculation_Functions::flattenSingleValue($index_number);
696:         $not_exact_match    = PHPExcel_Calculation_Functions::flattenSingleValue($not_exact_match);
697: 
698:         // index_number must be greater than or equal to 1
699:         if ($index_number < 1) {
700:             return PHPExcel_Calculation_Functions::VALUE();
701:         }
702: 
703:         // index_number must be less than or equal to the number of columns in lookup_array
704:         if ((!is_array($lookup_array)) || (empty($lookup_array))) {
705:             return PHPExcel_Calculation_Functions::REF();
706:         } else {
707:             $f = array_keys($lookup_array);
708:             $firstRow = array_pop($f);
709:             if ((!is_array($lookup_array[$firstRow])) || ($index_number > count($lookup_array[$firstRow]))) {
710:                 return PHPExcel_Calculation_Functions::REF();
711:             } else {
712:                 $columnKeys = array_keys($lookup_array[$firstRow]);
713:                 $returnColumn = $columnKeys[--$index_number];
714:                 $firstColumn = array_shift($columnKeys);
715:             }
716:         }
717: 
718:         if (!$not_exact_match) {
719:             uasort($lookup_array,array('self','_vlookupSort'));
720:         }
721: 
722:         $rowNumber = $rowValue = False;
723:         foreach($lookup_array as $rowKey => $rowData) {
724:             if ((is_numeric($lookup_value) && is_numeric($rowData[$firstColumn]) && ($rowData[$firstColumn] > $lookup_value)) ||
725:                 (!is_numeric($lookup_value) && !is_numeric($rowData[$firstColumn]) && (strtolower($rowData[$firstColumn]) > strtolower($lookup_value)))) {
726:                 break;
727:             }
728:             $rowNumber = $rowKey;
729:             $rowValue = $rowData[$firstColumn];
730:         }
731: 
732:         if ($rowNumber !== false) {
733:             if ((!$not_exact_match) && ($rowValue != $lookup_value)) {
734:                 //  if an exact match is required, we have what we need to return an appropriate response
735:                 return PHPExcel_Calculation_Functions::NA();
736:             } else {
737:                 //  otherwise return the appropriate value
738:                 $result = $lookup_array[$rowNumber][$returnColumn];
739:                 if ((is_numeric($lookup_value) && is_numeric($result)) ||
740:                     (!is_numeric($lookup_value) && !is_numeric($result))) {
741:                     return $result;
742:                 }
743:             }
744:         }
745: 
746:         return PHPExcel_Calculation_Functions::NA();
747:     }   //  function VLOOKUP()
748: 
749: 
750: /**
751:     * HLOOKUP
752:     * 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.
753:     * @param    lookup_value    The value that you want to match in lookup_array
754:     * @param    lookup_array    The range of cells being searched
755:     * @param    index_number    The row number in table_array from which the matching value must be returned. The first row is 1.
756:     * @param    not_exact_match Determines if you are looking for an exact match based on lookup_value.
757:     * @return   mixed           The value of the found cell
758:     */
759:     public static function HLOOKUP($lookup_value, $lookup_array, $index_number, $not_exact_match=true) {
760:         $lookup_value   = PHPExcel_Calculation_Functions::flattenSingleValue($lookup_value);
761:         $index_number   = PHPExcel_Calculation_Functions::flattenSingleValue($index_number);
762:         $not_exact_match    = PHPExcel_Calculation_Functions::flattenSingleValue($not_exact_match);
763: 
764:         // index_number must be greater than or equal to 1
765:         if ($index_number < 1) {
766:             return PHPExcel_Calculation_Functions::VALUE();
767:         }
768: 
769:         // index_number must be less than or equal to the number of columns in lookup_array
770:         if ((!is_array($lookup_array)) || (empty($lookup_array))) {
771:             return PHPExcel_Calculation_Functions::REF();
772:         } else {
773:             $f = array_keys($lookup_array);
774:             $firstRow = array_pop($f);
775:             if ((!is_array($lookup_array[$firstRow])) || ($index_number > count($lookup_array[$firstRow]))) {
776:                 return PHPExcel_Calculation_Functions::REF();
777:             } else {
778:                 $columnKeys = array_keys($lookup_array[$firstRow]);
779:                                 $firstkey = $f[0] - 1;
780:                 $returnColumn = $firstkey + $index_number;
781:                 $firstColumn = array_shift($f);
782:             }
783:         }
784: 
785:         if (!$not_exact_match) {
786:             $firstRowH = asort($lookup_array[$firstColumn]);
787:         }
788: 
789:         $rowNumber = $rowValue = False;
790:         foreach($lookup_array[$firstColumn] as $rowKey => $rowData) {
791:             if ((is_numeric($lookup_value) && is_numeric($rowData) && ($rowData > $lookup_value)) ||
792:                 (!is_numeric($lookup_value) && !is_numeric($rowData) && (strtolower($rowData) > strtolower($lookup_value)))) {
793:                 break;
794:             }
795:             $rowNumber = $rowKey;
796:             $rowValue = $rowData;
797:         }
798: 
799:         if ($rowNumber !== false) {
800:             if ((!$not_exact_match) && ($rowValue != $lookup_value)) {
801:                 //  if an exact match is required, we have what we need to return an appropriate response
802:                 return PHPExcel_Calculation_Functions::NA();
803:             } else {
804:                 //  otherwise return the appropriate value
805:                 $result = $lookup_array[$returnColumn][$rowNumber];
806:                 return $result;
807:             }
808:         }
809: 
810:         return PHPExcel_Calculation_Functions::NA();
811:     }   //  function HLOOKUP()
812: 
813: 
814:     /**
815:      * LOOKUP
816:      * The LOOKUP function searches for value either from a one-row or one-column range or from an array.
817:      * @param   lookup_value    The value that you want to match in lookup_array
818:      * @param   lookup_vector   The range of cells being searched
819:      * @param   result_vector   The column from which the matching value must be returned
820:      * @return  mixed           The value of the found cell
821:      */
822:     public static function LOOKUP($lookup_value, $lookup_vector, $result_vector=null) {
823:         $lookup_value   = PHPExcel_Calculation_Functions::flattenSingleValue($lookup_value);
824: 
825:         if (!is_array($lookup_vector)) {
826:             return PHPExcel_Calculation_Functions::NA();
827:         }
828:         $lookupRows = count($lookup_vector);
829:         $l = array_keys($lookup_vector);
830:         $l = array_shift($l);
831:         $lookupColumns = count($lookup_vector[$l]);
832:         if ((($lookupRows == 1) && ($lookupColumns > 1)) || (($lookupRows == 2) && ($lookupColumns != 2))) {
833:             $lookup_vector = self::TRANSPOSE($lookup_vector);
834:             $lookupRows = count($lookup_vector);
835:             $l = array_keys($lookup_vector);
836:             $lookupColumns = count($lookup_vector[array_shift($l)]);
837:         }
838: 
839:         if (is_null($result_vector)) {
840:             $result_vector = $lookup_vector;
841:         }
842:         $resultRows = count($result_vector);
843:         $l = array_keys($result_vector);
844:         $l = array_shift($l);
845:         $resultColumns = count($result_vector[$l]);
846:         if ((($resultRows == 1) && ($resultColumns > 1)) || (($resultRows == 2) && ($resultColumns != 2))) {
847:             $result_vector = self::TRANSPOSE($result_vector);
848:             $resultRows = count($result_vector);
849:             $r = array_keys($result_vector);
850:             $resultColumns = count($result_vector[array_shift($r)]);
851:         }
852: 
853:         if ($lookupRows == 2) {
854:             $result_vector = array_pop($lookup_vector);
855:             $lookup_vector = array_shift($lookup_vector);
856:         }
857:         if ($lookupColumns != 2) {
858:             foreach($lookup_vector as &$value) {
859:                 if (is_array($value)) {
860:                     $k = array_keys($value);
861:                     $key1 = $key2 = array_shift($k);
862:                     $key2++;
863:                     $dataValue1 = $value[$key1];
864:                 } else {
865:                     $key1 = 0;
866:                     $key2 = 1;
867:                     $dataValue1 = $value;
868:                 }
869:                 $dataValue2 = array_shift($result_vector);
870:                 if (is_array($dataValue2)) {
871:                     $dataValue2 = array_shift($dataValue2);
872:                 }
873:                 $value = array($key1 => $dataValue1, $key2 => $dataValue2);
874:             }
875:             unset($value);
876:         }
877: 
878:         return self::VLOOKUP($lookup_value,$lookup_vector,2);
879:     }   //  function LOOKUP()
880: 
881: }   //  class PHPExcel_Calculation_LookupRef
882: