File Source for LookupRef.php

PHPExcel_Calculation

# Source for file LookupRef.php

Documentation is available at LookupRef.php

1. <?php
2. /**
3. * PHPExcel
4. *
5. * Copyright (c) 2006 - 2011 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 - 2011 PHPExcel (http://www.codeplex.com/PHPExcel)
24. * @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL
25. * @version 1.7.6, 2011-02-27
26. */
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. }
39. /**
40. * PHPExcel_Calculation_LookupRef
41. *
42. * @category PHPExcel
43. * @package PHPExcel_Calculation
44. * @copyright Copyright (c) 2006 - 2011 PHPExcel (http://www.codeplex.com/PHPExcel)
45. */
46. class PHPExcel_Calculation_LookupRef {
49. /**
50. * CELL_ADDRESS
51. *
52. * Creates a cell address as text, given specified row and column numbers.
53. *
54. * @param row Row number to use in the cell reference
55. * @param column Column number to use in the cell reference
56. * @param relativity Flag indicating the type of reference to return
57. * 1 or omitted Absolute
58. * 2 Absolute row; relative column
59. * 3 Relative row; absolute column
60. * 4 Relative
61. * @param referenceStyle A logical value that specifies the A1 or R1C1 reference style.
62. * TRUE or omitted CELL_ADDRESS returns an A1-style reference
63. * FALSE CELL_ADDRESS returns an R1C1-style reference
64. * @param sheetText Optional Name of worksheet to use
65. * @return string
66. */
67. public static function CELL_ADDRESS($row, $column, $relativity=1, $referenceStyle=True, $sheetText='') {
68. $row = PHPExcel_Calculation_Functions:: flattenSingleValue($row);
69. $column = PHPExcel_Calculation_Functions:: flattenSingleValue($column);
70. $relativity = PHPExcel_Calculation_Functions:: flattenSingleValue($relativity);
71. $sheetText = PHPExcel_Calculation_Functions:: flattenSingleValue($sheetText);
73. if (($row < 1) || ($column < 1)) {
74. return PHPExcel_Calculation_Functions:: VALUE();
75. }
77. if ($sheetText > '') {
78. if ( strpos($sheetText,' ') !== False) { $sheetText = "'".$sheetText."'"; }
79. $sheetText .='!';
80. }
81. if ((! is_bool($referenceStyle)) || $referenceStyle) {
82. $rowRelative = $columnRelative = '$';
83. $column = PHPExcel_Cell:: stringFromColumnIndex($column-1);
84. if (($relativity == 2) || ($relativity == 4)) { $columnRelative = ''; }
85. if (($relativity == 3) || ($relativity == 4)) { $rowRelative = ''; }
86. return $sheetText.$columnRelative.$column.$rowRelative.$row;
87. } else {
88. if (($relativity == 2) || ($relativity == 4)) { $column = '['.$column.']'; }
89. if (($relativity == 3) || ($relativity == 4)) { $row = '['.$row.']'; }
90. return $sheetText.'R'.$row.'C'.$column;
91. }
92. } // function CELL_ADDRESS()
95. /**
96. * COLUMN
97. *
98. * Returns the column number of the given cell reference
99. * If the cell reference is a range of cells, COLUMN returns the column numbers of each column in the reference as a horizontal array.
100. * If cell reference is omitted, and the function is being called through the calculation engine, then it is assumed to be the
101. * reference of the cell in which the COLUMN function appears; otherwise this function returns 0.
102. *
103. * @param cellAddress A reference to a range of cells for which you want the column numbers
104. * @return integer or array of integer
105. */
106. public static function COLUMN($cellAddress=Null) {
107. if ( is_null($cellAddress) || trim($cellAddress) === '') { return 0; }
109. if ( is_array($cellAddress)) {
110. foreach($cellAddress as $columnKey => $value) {
111. $columnKey = preg_replace('/[^a-z]/i','',$columnKey);
112. return (integer) PHPExcel_Cell:: columnIndexFromString($columnKey);
113. }
114. } else {
115. if ( strpos($cellAddress,'!') !== false) {
116. list($sheet,$cellAddress) = explode('!',$cellAddress);
117. }
118. if ( strpos($cellAddress,':') !== false) {
119. list($startAddress,$endAddress) = explode(':',$cellAddress);
120. $startAddress = preg_replace('/[^a-z]/i','',$startAddress);
121. $endAddress = preg_replace('/[^a-z]/i','',$endAddress);
122. $returnValue = array();
123. do {
124. $returnValue[] = (integer) PHPExcel_Cell:: columnIndexFromString($startAddress);
125. } while ($startAddress++ != $endAddress);
126. return $returnValue;
127. } else {
128. $cellAddress = preg_replace('/[^a-z]/i','',$cellAddress);
129. return (integer) PHPExcel_Cell:: columnIndexFromString($cellAddress);
130. }
131. }
132. } // function COLUMN()
135. /**
136. * COLUMNS
137. *
138. * Returns the number of columns in an array or reference.
139. *
140. * @param cellAddress An array or array formula, or a reference to a range of cells for which you want the number of columns
141. * @return integer
142. */
143. public static function COLUMNS($cellAddress=Null) {
144. if ( is_null($cellAddress) || $cellAddress === '') {
145. return 1;
146. } elseif (! is_array($cellAddress)) {
147. return PHPExcel_Calculation_Functions:: VALUE();
148. }
150. $x = array_keys($cellAddress);
151. $x = array_shift($x);
152. $isMatrix = ( is_numeric($x));
153. list($columns,$rows) = PHPExcel_Calculation::_getMatrixDimensions($cellAddress);
155. if ($isMatrix) {
156. return $rows;
157. } else {
158. return $columns;
159. }
160. } // function COLUMNS()
163. /**
164. * ROW
165. *
166. * Returns the row number of the given cell reference
167. * If the cell reference is a range of cells, ROW returns the row numbers of each row in the reference as a vertical array.
168. * If cell reference is omitted, and the function is being called through the calculation engine, then it is assumed to be the
169. * reference of the cell in which the ROW function appears; otherwise this function returns 0.
170. *
171. * @param cellAddress A reference to a range of cells for which you want the row numbers
172. * @return integer or array of integer
173. */
174. public static function ROW($cellAddress=Null) {
175. if ( is_null($cellAddress) || trim($cellAddress) === '') { return 0; }
177. if ( is_array($cellAddress)) {
178. foreach($cellAddress as $columnKey => $rowValue) {
179. foreach($rowValue as $rowKey => $cellValue) {
180. return (integer) preg_replace('/[^0-9]/i','',$rowKey);
181. }
182. }
183. } else {
184. if ( strpos($cellAddress,'!') !== false) {
185. list($sheet,$cellAddress) = explode('!',$cellAddress);
186. }
187. if ( strpos($cellAddress,':') !== false) {
188. list($startAddress,$endAddress) = explode(':',$cellAddress);
189. $startAddress = preg_replace('/[^0-9]/','',$startAddress);
190. $endAddress = preg_replace('/[^0-9]/','',$endAddress);
191. $returnValue = array();
192. do {
193. $returnValue[][] = (integer) $startAddress;
194. } while ($startAddress++ != $endAddress);
195. return $returnValue;
196. } else {
197. list($cellAddress) = explode(':',$cellAddress);
198. return (integer) preg_replace('/[^0-9]/','',$cellAddress);
199. }
200. }
201. } // function ROW()
204. /**
205. * ROWS
206. *
207. * Returns the number of rows in an array or reference.
208. *
209. * @param cellAddress An array or array formula, or a reference to a range of cells for which you want the number of rows
210. * @return integer
211. */
212. public static function ROWS($cellAddress=Null) {
213. if ( is_null($cellAddress) || $cellAddress === '') {
214. return 1;
215. } elseif (! is_array($cellAddress)) {
216. return PHPExcel_Calculation_Functions:: VALUE();
217. }
219. $i = array_keys($cellAddress);
220. $isMatrix = ( is_numeric( array_shift($i)));
221. list($columns,$rows) = PHPExcel_Calculation::_getMatrixDimensions($cellAddress);
223. if ($isMatrix) {
224. return $columns;
225. } else {
226. return $rows;
227. }
228. } // function ROWS()
231. /**
232. * HYPERLINK
233. *
234. * Excel Function:
235. * =HYPERLINK(linkURL,displayName)
236. *
237. * @access public
238. * @category Logical Functions
239. * @param string $linkURL Value to check, is also the value returned when no error
240. * @param string $displayName Value to return when testValue is an error condition
241. * @return mixed The value of errorpart or testValue determined by error condition
242. */
243. public static function HYPERLINK($linkURL = '', $displayName = null, PHPExcel_Cell $pCell = null) {
244. $args = func_get_args();
245. $pCell = array_pop($args);
247. $linkURL = ( is_null($linkURL)) ? '' : PHPExcel_Calculation_Functions:: flattenSingleValue($linkURL);
248. $displayName = ( is_null($displayName)) ? '' : PHPExcel_Calculation_Functions:: flattenSingleValue($displayName);
250. if ((! is_object($pCell)) || ( trim($linkURL) == '')) {
251. return PHPExcel_Calculation_Functions:: REF();
252. }
254. if (( is_object($displayName)) || trim($displayName) == '') {
255. $displayName = $linkURL;
256. }
258. $pCell->getHyperlink()->setUrl($linkURL);
260. return $displayName;
261. } // function HYPERLINK()
264. /**
265. * INDIRECT
266. *
267. * Returns the number of rows in an array or reference.
268. *
269. * @param cellAddress An array or array formula, or a reference to a range of cells for which you want the number of rows
270. * @return integer
271. */
272. public static function INDIRECT($cellAddress=Null, PHPExcel_Cell $pCell = null) {
273. $cellAddress = PHPExcel_Calculation_Functions:: flattenSingleValue($cellAddress);
274. if ( is_null($cellAddress) || $cellAddress === '') {
275. return PHPExcel_Calculation_Functions:: REF();
276. }
278. $cellAddress1 = $cellAddress;
279. $cellAddress2 = NULL;
280. if ( strpos($cellAddress,':') !== false) {
281. list($cellAddress1,$cellAddress2) = explode(':',$cellAddress);
282. }
284. if ((! preg_match('/^'.PHPExcel_Calculation:: CALCULATION_REGEXP_CELLREF.'$/i', $cellAddress1, $matches)) ||
285. ((! is_null($cellAddress2)) && (! preg_match('/^'.PHPExcel_Calculation:: CALCULATION_REGEXP_CELLREF.'$/i', $cellAddress2, $matches)))) {
286. return PHPExcel_Calculation_Functions:: REF();
287. }
289. if ( strpos($cellAddress,'!') !== false) {
290. list($sheetName,$cellAddress) = explode('!',$cellAddress);
291. $pSheet = $pCell->getParent()->getParent()->getSheetByName($sheetName);
292. } else {
293. $pSheet = $pCell->getParent();
294. }
296. return PHPExcel_Calculation:: getInstance()->extractCellRange($cellAddress, $pSheet, False);
297. } // function INDIRECT()
300. /**
301. * OFFSET
302. *
303. * Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells.
304. * The reference that is returned can be a single cell or a range of cells. You can specify the number of rows and
305. * the number of columns to be returned.
306. *
307. * @param cellAddress The reference from which you want to base the offset. Reference must refer to a cell or
308. * range of adjacent cells; otherwise, OFFSET returns the #VALUE! error value.
309. * @param rows The number of rows, up or down, that you want the upper-left cell to refer to.
310. * Using 5 as the rows argument specifies that the upper-left cell in the reference is
311. * five rows below reference. Rows can be positive (which means below the starting reference)
312. * or negative (which means above the starting reference).
313. * @param cols The number of columns, to the left or right, that you want the upper-left cell of the result
314. * to refer to. Using 5 as the cols argument specifies that the upper-left cell in the
315. * reference is five columns to the right of reference. Cols can be positive (which means
316. * to the right of the starting reference) or negative (which means to the left of the
317. * starting reference).
318. * @param height The height, in number of rows, that you want the returned reference to be. Height must be a positive number.
319. * @param width The width, in number of columns, that you want the returned reference to be. Width must be a positive number.
320. * @return string A reference to a cell or range of cells
321. */
322. public static function OFFSET($cellAddress=Null,$rows=0,$columns=0,$height=null,$width=null) {
323. $rows = PHPExcel_Calculation_Functions:: flattenSingleValue($rows);
324. $columns = PHPExcel_Calculation_Functions:: flattenSingleValue($columns);
325. $height = PHPExcel_Calculation_Functions:: flattenSingleValue($height);
326. $width = PHPExcel_Calculation_Functions:: flattenSingleValue($width);
327. if ($cellAddress == Null) {
328. return 0;
329. }
331. $args = func_get_args();
332. $pCell = array_pop($args);
333. if (! is_object($pCell)) {
334. return PHPExcel_Calculation_Functions:: REF();
335. }
337. $sheetName = null;
338. if ( strpos($cellAddress,"!")) {
339. list($sheetName,$cellAddress) = explode("!",$cellAddress);
340. }
341. if ( strpos($cellAddress,":")) {
342. list($startCell,$endCell) = explode(":",$cellAddress);
343. } else {
344. $startCell = $endCell = $cellAddress;
345. }
346. list($startCellColumn,$startCellRow) = PHPExcel_Cell:: coordinateFromString($startCell);
347. list($endCellColumn,$endCellRow) = PHPExcel_Cell:: coordinateFromString($endCell);
349. $startCellRow += $rows;
350. $startCellColumn = PHPExcel_Cell:: columnIndexFromString($startCellColumn) - 1;
351. $startCellColumn += $columns;
353. if (($startCellRow <= 0) || ($startCellColumn < 0)) {
354. return PHPExcel_Calculation_Functions:: REF();
355. }
356. $endCellColumn = PHPExcel_Cell:: columnIndexFromString($endCellColumn) - 1;
357. if (($width != null) && (! is_object($width))) {
358. $endCellColumn = $startCellColumn + $width - 1;
359. } else {
360. $endCellColumn += $columns;
361. }
362. $startCellColumn = PHPExcel_Cell:: stringFromColumnIndex($startCellColumn);
364. if (($height != null) && (! is_object($height))) {
365. $endCellRow = $startCellRow + $height - 1;
366. } else {
367. $endCellRow += $rows;
368. }
370. if (($endCellRow <= 0) || ($endCellColumn < 0)) {
371. return PHPExcel_Calculation_Functions:: REF();
372. }
373. $endCellColumn = PHPExcel_Cell:: stringFromColumnIndex($endCellColumn);
375. $cellAddress = $startCellColumn.$startCellRow;
376. if (($startCellColumn != $endCellColumn) || ($startCellRow != $endCellRow)) {
377. $cellAddress .= ':'.$endCellColumn.$endCellRow;
378. }
380. if ($sheetName !== null) {
381. $pSheet = $pCell->getParent()->getParent()->getSheetByName($sheetName);
382. } else {
383. $pSheet = $pCell->getParent();
384. }
386. return PHPExcel_Calculation:: getInstance()->extractCellRange($cellAddress, $pSheet, False);
387. } // function OFFSET()
390. public static function CHOOSE() {
391. $chooseArgs = func_get_args();
392. $chosenEntry = PHPExcel_Calculation_Functions:: flattenArray( array_shift($chooseArgs));
393. $entryCount = count($chooseArgs) - 1;
395. if( is_array($chosenEntry)) {
396. $chosenEntry = array_shift($chosenEntry);
397. }
398. if (( is_numeric($chosenEntry)) && (! is_bool($chosenEntry))) {
399. --$chosenEntry;
400. } else {
401. return PHPExcel_Calculation_Functions:: VALUE();
402. }
403. $chosenEntry = floor($chosenEntry);
404. if (($chosenEntry <= 0) || ($chosenEntry > $entryCount)) {
405. return PHPExcel_Calculation_Functions:: VALUE();
406. }
408. if ( is_array($chooseArgs[$chosenEntry])) {
409. return PHPExcel_Calculation_Functions:: flattenArray($chooseArgs[$chosenEntry]);
410. } else {
411. return $chooseArgs[$chosenEntry];
412. }
413. } // function CHOOSE()
416. /**
417. * MATCH
418. *
419. * The MATCH function searches for a specified item in a range of cells
420. *
421. * @param lookup_value The value that you want to match in lookup_array
422. * @param lookup_array The range of cells being searched
423. * @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.
424. * @return integer The relative position of the found item
425. */
426. public static function MATCH($lookup_value, $lookup_array, $match_type=1) {
427. $lookup_array = PHPExcel_Calculation_Functions:: flattenArray($lookup_array);
428. $lookup_value = PHPExcel_Calculation_Functions:: flattenSingleValue($lookup_value);
429. $match_type = ( is_null($match_type)) ? 1 : (int) PHPExcel_Calculation_Functions:: flattenSingleValue($match_type);
430. // MATCH is not case sensitive
431. $lookup_value = strtolower($lookup_value);
433. // lookup_value type has to be number, text, or logical values
434. if ((! is_numeric($lookup_value)) && (! is_string($lookup_value)) && (! is_bool($lookup_value))) {
435. return PHPExcel_Calculation_Functions:: NA();
436. }
438. // match_type is 0, 1 or -1
439. if (($match_type !== 0) && ($match_type !== -1) && ($match_type !== 1)) {
440. return PHPExcel_Calculation_Functions:: NA();
441. }
443. // lookup_array should not be empty
444. $lookupArraySize = count($lookup_array);
445. if ($lookupArraySize <= 0) {
446. return PHPExcel_Calculation_Functions:: NA();
447. }
449. // lookup_array should contain only number, text, or logical values, or empty (null) cells
450. foreach($lookup_array as $i => $lookupArrayValue) {
451. // check the type of the value
452. if ((! is_numeric($lookupArrayValue)) && (! is_string($lookupArrayValue)) &&
453. (! is_bool($lookupArrayValue)) && (! is_null($lookupArrayValue))) {
454. return PHPExcel_Calculation_Functions:: NA();
455. }
456. // convert strings to lowercase for case-insensitive testing
457. if ( is_string($lookupArrayValue)) {
458. $lookup_array[$i] = strtolower($lookupArrayValue);
459. }
460. if (( is_null($lookupArrayValue)) && (($match_type == 1) || ($match_type == -1))) {
461. $lookup_array = array_slice($lookup_array,0,$i-1);
462. }
463. }
465. // if match_type is 1 or -1, the list has to be ordered
466. if ($match_type == 1) {
467. asort($lookup_array);
468. $keySet = array_keys($lookup_array);
469. } elseif($match_type == -1) {
470. arsort($lookup_array);
471. $keySet = array_keys($lookup_array);
472. }
474. // **
475. // find the match
476. // **
477. // loop on the cells
478. // var_dump($lookup_array);
479. // echo '<br />';
480. foreach($lookup_array as $i => $lookupArrayValue) {
481. if (($match_type == 0) && ($lookupArrayValue == $lookup_value)) {
482. // exact match
483. return ++$i;
484. } elseif (($match_type == -1) && ($lookupArrayValue <= $lookup_value)) {
485. // echo '$i = '.$i.' => ';
486. // var_dump($lookupArrayValue);
487. // echo '<br />';
488. // echo 'Keyset = ';
489. // var_dump($keySet);
490. // echo '<br />';
491. $i = array_search($i,$keySet);
492. // echo '$i='.$i.'<br />';
493. // if match_type is -1 <=> find the smallest value that is greater than or equal to lookup_value
494. if ($i < 1){
495. // 1st cell was allready smaller than the lookup_value
496. break;
497. } else {
498. // the previous cell was the match
499. return $keySet[$i-1]+1;
500. }
501. } elseif (($match_type == 1) && ($lookupArrayValue >= $lookup_value)) {
502. // echo '$i = '.$i.' => ';
503. // var_dump($lookupArrayValue);
504. // echo '<br />';
505. // echo 'Keyset = ';
506. // var_dump($keySet);
507. // echo '<br />';
508. $i = array_search($i,$keySet);
509. // echo '$i='.$i.'<br />';
510. // if match_type is 1 <=> find the largest value that is less than or equal to lookup_value
511. if ($i < 1){
512. // 1st cell was allready bigger than the lookup_value
513. break;
514. } else {
515. // the previous cell was the match
516. return $keySet[$i-1]+1;
517. }
518. }
519. }
521. // unsuccessful in finding a match, return #N/A error value
522. return PHPExcel_Calculation_Functions:: NA();
523. } // function MATCH()
526. /**
527. * INDEX
528. *
529. * Uses an index to choose a value from a reference or array
530. * implemented: Return the value of a specified cell or array of cells Array form
531. * not implemented: Return a reference to specified cells Reference form
532. *
533. * @param range_array a range of cells or an array constant
534. * @param row_num selects the row in array from which to return a value. If row_num is omitted, column_num is required.
535. * @param column_num selects the column in array from which to return a value. If column_num is omitted, row_num is required.
536. */
537. public static function INDEX($arrayValues,$rowNum = 0,$columnNum = 0) {
539. if (($rowNum < 0) || ($columnNum < 0)) {
540. return PHPExcel_Calculation_Functions:: VALUE();
541. }
543. if (! is_array($arrayValues)) {
544. return PHPExcel_Calculation_Functions:: REF();
545. }
547. $rowKeys = array_keys($arrayValues);
548. $columnKeys = @ array_keys($arrayValues[$rowKeys[0]]);
550. if ($columnNum > count($columnKeys)) {
551. return PHPExcel_Calculation_Functions:: VALUE();
552. } elseif ($columnNum == 0) {
553. if ($rowNum == 0) {
554. return $arrayValues;
555. }
556. $rowNum = $rowKeys[--$rowNum];
557. $returnArray = array();
558. foreach($arrayValues as $arrayColumn) {
559. if ( is_array($arrayColumn)) {
560. if (isset($arrayColumn[$rowNum])) {
561. $returnArray[] = $arrayColumn[$rowNum];
562. } else {
563. return $arrayValues[$rowNum];
564. }
565. } else {
566. return $arrayValues[$rowNum];
567. }
568. }
569. return $returnArray;
570. }
571. $columnNum = $columnKeys[--$columnNum];
572. if ($rowNum > count($rowKeys)) {
573. return PHPExcel_Calculation_Functions:: VALUE();
574. } elseif ($rowNum == 0) {
575. return $arrayValues[$columnNum];
576. }
577. $rowNum = $rowKeys[--$rowNum];
579. return $arrayValues[$rowNum][$columnNum];
580. } // function INDEX()
583. /**
584. * TRANSPOSE
585. *
586. * @param array $matrixData A matrix of values
587. * @return array
588. *
589. * Unlike the Excel TRANSPOSE function, which will only work on a single row or column, this function will transpose a full matrix.
590. */
591. public static function TRANSPOSE($matrixData) {
592. $returnMatrix = array();
593. if (! is_array($matrixData)) { $matrixData = array(array($matrixData)); }
595. $column = 0;
596. foreach($matrixData as $matrixRow) {
597. $row = 0;
598. foreach($matrixRow as $matrixCell) {
599. $returnMatrix[$row][$column] = $matrixCell;
600. ++$row;
601. }
602. ++$column;
603. }
604. return $returnMatrix;
605. } // function TRANSPOSE()
608. private static function _vlookupSort($a,$b) {
609. $f = array_keys($a);
610. $firstColumn = array_shift($f);
611. if ( strtolower($a[$firstColumn]) == strtolower($b[$firstColumn])) {
612. return 0;
613. }
614. return ( strtolower($a[$firstColumn]) < strtolower($b[$firstColumn])) ? -1 : 1;
615. } // function _vlookupSort()
618. /**
619. * VLOOKUP
620. * 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.
621. * @param lookup_value The value that you want to match in lookup_array
622. * @param lookup_array The range of cells being searched
623. * @param index_number The column number in table_array from which the matching value must be returned. The first column is 1.
624. * @param not_exact_match Determines if you are looking for an exact match based on lookup_value.
625. * @return mixed The value of the found cell
626. */
627. public static function VLOOKUP($lookup_value, $lookup_array, $index_number, $not_exact_match=true) {
628. $lookup_value = PHPExcel_Calculation_Functions:: flattenSingleValue($lookup_value);
629. $index_number = PHPExcel_Calculation_Functions:: flattenSingleValue($index_number);
630. $not_exact_match = PHPExcel_Calculation_Functions:: flattenSingleValue($not_exact_match);
632. // index_number must be greater than or equal to 1
633. if ($index_number < 1) {
634. return PHPExcel_Calculation_Functions:: VALUE();
635. }
637. // index_number must be less than or equal to the number of columns in lookup_array
638. if ((! is_array($lookup_array)) || ( count($lookup_array) < 1)) {
639. return PHPExcel_Calculation_Functions:: REF();
640. } else {
641. $f = array_keys($lookup_array);
642. $firstRow = array_pop($f);
643. if ((! is_array($lookup_array[$firstRow])) || ($index_number > count($lookup_array[$firstRow]))) {
644. return PHPExcel_Calculation_Functions:: REF();
645. } else {
646. $columnKeys = array_keys($lookup_array[$firstRow]);
647. $returnColumn = $columnKeys[--$index_number];
648. $firstColumn = array_shift($columnKeys);
649. }
650. }
652. if (!$not_exact_match) {
653. uasort($lookup_array,array('self','_vlookupSort'));
654. }
656. $rowNumber = $rowValue = False;
657. foreach($lookup_array as $rowKey => $rowData) {
658. if ( strtolower($rowData[$firstColumn]) > strtolower($lookup_value)) {
659. break;
660. }
661. $rowNumber = $rowKey;
662. $rowValue = $rowData[$firstColumn];
663. }
665. if ($rowNumber !== false) {
666. if ((!$not_exact_match) && ($rowValue != $lookup_value)) {
667. // if an exact match is required, we have what we need to return an appropriate response
668. return PHPExcel_Calculation_Functions:: NA();
669. } else {
670. // otherwise return the appropriate value
671. return $lookup_array[$rowNumber][$returnColumn];
672. }
673. }
675. return PHPExcel_Calculation_Functions:: NA();
676. } // function VLOOKUP()
679. /**
680. * LOOKUP
681. * The LOOKUP function searches for value either from a one-row or one-column range or from an array.
682. * @param lookup_value The value that you want to match in lookup_array
683. * @param lookup_vector The range of cells being searched
684. * @param result_vector The column from which the matching value must be returned
685. * @return mixed The value of the found cell
686. */
687. public static function LOOKUP($lookup_value, $lookup_vector, $result_vector=null) {
688. $lookup_value = PHPExcel_Calculation_Functions:: flattenSingleValue($lookup_value);
690. if (! is_array($lookup_vector)) {
691. return PHPExcel_Calculation_Functions:: NA();
692. }
693. $lookupRows = count($lookup_vector);
694. $l = array_keys($lookup_vector);
695. $l = array_shift($l);
696. $lookupColumns = count($lookup_vector[$l]);
697. if ((($lookupRows == 1) && ($lookupColumns > 1)) || (($lookupRows == 2) && ($lookupColumns != 2))) {
698. $lookup_vector = self:: TRANSPOSE($lookup_vector);
699. $lookupRows = count($lookup_vector);
700. $l = array_keys($lookup_vector);
701. $lookupColumns = count($lookup_vector[ array_shift($l)]);
702. }
704. if ( is_null($result_vector)) {
705. $result_vector = $lookup_vector;
706. }
707. $resultRows = count($result_vector);
708. $l = array_keys($result_vector);
709. $l = array_shift($l);
710. $resultColumns = count($result_vector[$l]);
711. if ((($resultRows == 1) && ($resultColumns > 1)) || (($resultRows == 2) && ($resultColumns != 2))) {
712. $result_vector = self:: TRANSPOSE($result_vector);
713. $resultRows = count($result_vector);
714. $r = array_keys($result_vector);
715. $resultColumns = count($result_vector[ array_shift($r)]);
716. }
718. if ($lookupRows == 2) {
719. $result_vector = array_pop($lookup_vector);
720. $lookup_vector = array_shift($lookup_vector);
721. }
722. if ($lookupColumns != 2) {
723. foreach($lookup_vector as &$value) {
724. if ( is_array($value)) {
725. $k = array_keys($value);
726. $key1 = $key2 = array_shift($k);
727. $key2++;
728. $dataValue1 = $value[$key1];
729. } else {
730. $key1 = 0;
731. $key2 = 1;
732. $dataValue1 = $value;
733. }
734. $dataValue2 = array_shift($result_vector);
735. if ( is_array($dataValue2)) {
736. $dataValue2 = array_shift($dataValue2);
737. }
738. $value = array($key1 => $dataValue1, $key2 => $dataValue2);
739. }
740. unset($value);
741. }
743. return self:: VLOOKUP($lookup_value,$lookup_vector,2);
744. } // function LOOKUP()
746. } // class PHPExcel_Calculation_LookupRef

Documentation generated on Sun, 27 Feb 2011 16:32:32 -0800 by phpDocumentor 1.4.3