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