1 <?php
3 /*
4 * Copyleft 2002 Johann Hanne
5 *
6 * This is free software; you can redistribute it and/or
7 * modify it under the terms of the GNU Lesser General Public
8 * License as published by the Free Software Foundation; either
9 * version 2.1 of the License, or (at your option) any later version.
10 *
11 * This software is distributed in the hope that it will be useful,
12 * but WITHOUT ANY WARRANTY; without even the implied warranty of
13 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
14 * Lesser General Public License for more details.
15 *
16 * You should have received a copy of the GNU Lesser General Public
17 * License along with this software; if not, write to the
18 * Free Software Foundation, Inc., 59 Temple Place,
19 * Suite 330, Boston, MA 02111-1307 USA
20 */
22 /* This file contains source from the PEAR::Spreadsheet class Parser.php file version 0.4 .
23 The raiseError was replaced by triggerError function.
24 The PEAR::isError was imported to keep compatibility to PEAR::Spreadsheet class
26 Imported and adapted by Andreas Brodowski 2003 (andreas.brodowski@oscar-gmbh.com).
28 There should be no license rights in question because the Parser.php from PEAR class is
29 published under GNU License the same way like this class.
31 Changes: 03/08/27 Added SPREADSHEET_EXCEL_WRITER_SCOLON for arg seperation in excel functions
32 */
34 /*
35 * This is the Spreadsheet::WriteExcel Perl package ported to PHP
36 * Spreadsheet::WriteExcel was written by John McNamara, jmcnamara@cpan.org
37 */
39 define('SPREADSHEET_EXCEL_WRITER_ADD',"+");\r // @const SPREADSHEET_EXCEL_WRITER_ADD token identifier for character "+"
40 define('SPREADSHEET_EXCEL_WRITER_SUB',"-");\r // @const SPREADSHEET_EXCEL_WRITER_SUB token identifier for character "-"
41 define('SPREADSHEET_EXCEL_WRITER_MUL',"*");\r // @const SPREADSHEET_EXCEL_WRITER_MUL token identifier for character "*"
42 define('SPREADSHEET_EXCEL_WRITER_DIV',"/");\r // @const SPREADSHEET_EXCEL_WRITER_DIV token identifier for character "/"
43 define('SPREADSHEET_EXCEL_WRITER_OPEN',"(");\r // @const SPREADSHEET_EXCEL_WRITER_OPEN token identifier for character "("
44 define('SPREADSHEET_EXCEL_WRITER_CLOSE',")"); \r // @const SPREADSHEET_EXCEL_WRITER_CLOSE token identifier for character ")"
45 define('SPREADSHEET_EXCEL_WRITER_COMA',",");\r // @const SPREADSHEET_EXCEL_WRITER_COMA token identifier for character ","
46 define('SPREADSHEET_EXCEL_WRITER_SCOLON',";"); \r// @const SPREADSHEET_EXCEL_WRITER_SCOLON token identifier for character ";"
47 define('SPREADSHEET_EXCEL_WRITER_GT',">");\r // @const SPREADSHEET_EXCEL_WRITER_GT token identifier for character ">"
48 define('SPREADSHEET_EXCEL_WRITER_LT',"<");\r // @const SPREADSHEET_EXCEL_WRITER_LT token identifier for character "<"
49 define('SPREADSHEET_EXCEL_WRITER_LE',"<=");\r // @const SPREADSHEET_EXCEL_WRITER_LE token identifier for character "<="
50 define('SPREADSHEET_EXCEL_WRITER_GE',">=");\r // @const SPREADSHEET_EXCEL_WRITER_GE token identifier for character ">="
51 define('SPREADSHEET_EXCEL_WRITER_EQ',"=");\r // @const SPREADSHEET_EXCEL_WRITER_EQ token identifier for character "="
52 define('SPREADSHEET_EXCEL_WRITER_NE',"<>");\r // @const SPREADSHEET_EXCEL_WRITER_NE token identifier for character "<>"
55 class writeexcel_formula {
57 ###############################################################################
58 #
59 # Class data.
60 #
61 var $parser;
62 var $ptg;
63 var $_functions;
64 var $_current_char;
65 var $_current_token;
66 var $_lookahead;
67 var $_debug;
68 var $_byte_order;
69 var $_volatile;
70 var $_workbook;
71 var $_ext_sheets;
72 var $_formula;
74 ###############################################################################
75 #
76 # new()
77 #
78 # Constructor
79 #
80 function writeexcel_formula($byte_order) {
82 $this->parser = false;
83 $this->ptg = array();
84 $this->_functions = array();
85 $this->_debug = 0;
86 $this->_byte_order = $byte_order;
87 $this->_volatile = 0;
88 $this->_workbook = "";
89 $this->_ext_sheets = array();
90 $this->_current_token = '';
91 $this->_lookahead = '';
92 $this->_current_char = 0;
93 $this->_formula = '';
94 }
96 ###############################################################################
97 #
98 # _init_parser()
99 #
100 # There is a small overhead involved in generating the parser. Therefore, the
101 # initialisation is delayed until a formula is required. TODO: use a pre-
102 # compiled header.
103 #
104 function _init_parser() {
106 $this->_initializeHashes();
109 if ($this->_debug) {
110 print "Init_parser.\n\n";
111 }
112 }
114 ###############################################################################
115 #
116 # parse_formula()
117 #
118 # This is the only public method. It takes a textual description of a formula
119 # and returns a RPN encoded byte string.
120 #
121 function parse_formula() {
123 $_=func_get_args();
125 # Initialise the parser if this is the first call
126 if ($this->parser===false) {
127 $this->_init_parser();
128 }
130 $formula = array_shift($_);
131 //$str;
132 //$tokens;
134 if ($this->_debug) {
135 print "$formula\n";
136 }
138 # Build the parse tree for the formula
140 $this->_formula = $formula;
141 $this->_current_char = 0;
142 $this->_lookahead = $this->_formula{1};
143 $this->_advance($formula);
144 $parsetree = $this->_condition();
146 $str = $this->toReversePolish($parsetree);
148 return $str;
149 }
151 function set_ext_sheet($key, $value) {
153 $this->_ext_sheets->$key = $value;
154 }
156 function isError($data) {
157 return (bool)(is_object($data) &&
158 (get_class($data) == 'pear_error' ||
159 is_subclass_of($data, 'pear_error')));
160 }
162 /**
163 * Class for parsing Excel formulas
164 *
165 * @author Xavier Noguer <xnoguer@rezebra.com>
166 * @category FileFormats
167 * @package Spreadsheet_Excel_Writer
168 */
171 /**
172 * Initialize the ptg and function hashes.
173 *
174 * @access private
175 */
176 function _initializeHashes()\r {
177 // The Excel ptg indices
178 $this->ptg = array(
191 \r 'ptgArea3d' => 0x3B,\r 'ptgRefErr3d' => 0x3C,\r 'ptgAreaErr3d' => 0x3D,\r 'ptgArrayV' => 0x40,
195 'ptgAreaNV' => 0x4D,\r 'ptgMemAreaNV' => 0x4E,\r 'ptgMemNoMemN' => 0x4F,\r 'ptgFuncCEV' => 0x58,
199 'ptgMemErrA' => 0x67,\r 'ptgMemNoMemA' => 0x68,\r 'ptgMemFuncA' => 0x69,\r 'ptgRefErrA' => 0x6A,
203 );
205 // Thanks to Michael Meeks and Gnumeric for the initial arg values.
206 //
207 // The following hash was generated by "function_locale.pl" in the distro.
208 // Refer to function_locale.pl for non-English function names.
209 //
210 // The array elements are as follow:
211 // ptg: The Excel function ptg code.
212 // args: The number of arguments that the function takes:
213 // >=0 is a fixed number of arguments.
214 // -1 is a variable number of arguments.
215 // class: The reference, value or array class of the function args.
216 // vol: The function is volatile.
217 //
218 $this->_functions = array(
219 // function ptg args class vol
220 'COUNT' => array( 0, -1, 0, 0 ),
221 'IF' => array( 1, -1, 1, 0 ),
222 'ISNA' => array( 2, 1, 1, 0 ),
223 'ISERROR' => array( 3, 1, 1, 0 ),
224 'SUM' => array( 4, -1, 0, 0 ),
225 'AVERAGE' => array( 5, -1, 0, 0 ),
226 'MIN' => array( 6, -1, 0, 0 ),
227 'MAX' => array( 7, -1, 0, 0 ),
228 'ROW' => array( 8, -1, 0, 0 ),
229 'COLUMN' => array( 9, -1, 0, 0 ),
230 'NA' => array( 10, 0, 0, 0 ),
231 'NPV' => array( 11, -1, 1, 0 ),
232 'STDEV' => array( 12, -1, 0, 0 ),
233 'DOLLAR' => array( 13, -1, 1, 0 ),
234 'FIXED' => array( 14, -1, 1, 0 ),
235 'SIN' => array( 15, 1, 1, 0 ),
236 'COS' => array( 16, 1, 1, 0 ),
237 'TAN' => array( 17, 1, 1, 0 ),
238 'ATAN' => array( 18, 1, 1, 0 ),
239 'PI' => array( 19, 0, 1, 0 ),
240 'SQRT' => array( 20, 1, 1, 0 ),
241 'EXP' => array( 21, 1, 1, 0 ),
242 'LN' => array( 22, 1, 1, 0 ),
243 'LOG10' => array( 23, 1, 1, 0 ),
244 'ABS' => array( 24, 1, 1, 0 ),
245 'INT' => array( 25, 1, 1, 0 ),
246 'SIGN' => array( 26, 1, 1, 0 ),
247 'ROUND' => array( 27, 2, 1, 0 ),
248 'LOOKUP' => array( 28, -1, 0, 0 ),
249 'INDEX' => array( 29, -1, 0, 1 ),
250 'REPT' => array( 30, 2, 1, 0 ),
251 'MID' => array( 31, 3, 1, 0 ),
252 'LEN' => array( 32, 1, 1, 0 ),
253 'VALUE' => array( 33, 1, 1, 0 ),
254 'TRUE' => array( 34, 0, 1, 0 ),
255 'FALSE' => array( 35, 0, 1, 0 ),
256 'AND' => array( 36, -1, 0, 0 ),
257 'OR' => array( 37, -1, 0, 0 ),
258 'NOT' => array( 38, 1, 1, 0 ),
259 'MOD' => array( 39, 2, 1, 0 ),
260 'DCOUNT' => array( 40, 3, 0, 0 ),
261 'DSUM' => array( 41, 3, 0, 0 ),
262 'DAVERAGE' => array( 42, 3, 0, 0 ),
263 'DMIN' => array( 43, 3, 0, 0 ),
264 'DMAX' => array( 44, 3, 0, 0 ),
265 'DSTDEV' => array( 45, 3, 0, 0 ),
266 'VAR' => array( 46, -1, 0, 0 ),
267 'DVAR' => array( 47, 3, 0, 0 ),
268 'TEXT' => array( 48, 2, 1, 0 ),
269 'LINEST' => array( 49, -1, 0, 0 ),
270 'TREND' => array( 50, -1, 0, 0 ),
271 'LOGEST' => array( 51, -1, 0, 0 ),
272 'GROWTH' => array( 52, -1, 0, 0 ),
273 'PV' => array( 56, -1, 1, 0 ),
274 'FV' => array( 57, -1, 1, 0 ),
275 'NPER' => array( 58, -1, 1, 0 ),
276 'PMT' => array( 59, -1, 1, 0 ),
277 'RATE' => array( 60, -1, 1, 0 ),
278 'MIRR' => array( 61, 3, 0, 0 ),
279 'IRR' => array( 62, -1, 0, 0 ),
280 'RAND' => array( 63, 0, 1, 1 ),
281 'MATCH' => array( 64, -1, 0, 0 ),
282 'DATE' => array( 65, 3, 1, 0 ),
283 'TIME' => array( 66, 3, 1, 0 ),
284 'DAY' => array( 67, 1, 1, 0 ),
285 'MONTH' => array( 68, 1, 1, 0 ),
286 'YEAR' => array( 69, 1, 1, 0 ),
287 'WEEKDAY' => array( 70, -1, 1, 0 ),
288 'HOUR' => array( 71, 1, 1, 0 ),
289 'MINUTE' => array( 72, 1, 1, 0 ),
290 'SECOND' => array( 73, 1, 1, 0 ),
291 'NOW' => array( 74, 0, 1, 1 ),
292 'AREAS' => array( 75, 1, 0, 1 ),
293 'ROWS' => array( 76, 1, 0, 1 ),
294 'COLUMNS' => array( 77, 1, 0, 1 ),
295 'OFFSET' => array( 78, -1, 0, 1 ),
296 'SEARCH' => array( 82, -1, 1, 0 ),
297 'TRANSPOSE' => array( 83, 1, 1, 0 ),
298 'TYPE' => array( 86, 1, 1, 0 ),
299 'ATAN2' => array( 97, 2, 1, 0 ),
300 'ASIN' => array( 98, 1, 1, 0 ),
301 'ACOS' => array( 99, 1, 1, 0 ),
302 'CHOOSE' => array( 100, -1, 1, 0 ),
303 'HLOOKUP' => array( 101, -1, 0, 0 ),
304 'VLOOKUP' => array( 102, -1, 0, 0 ),
305 'ISREF' => array( 105, 1, 0, 0 ),
306 'LOG' => array( 109, -1, 1, 0 ),
307 'CHAR' => array( 111, 1, 1, 0 ),
308 'LOWER' => array( 112, 1, 1, 0 ),
309 'UPPER' => array( 113, 1, 1, 0 ),
310 'PROPER' => array( 114, 1, 1, 0 ),
311 'LEFT' => array( 115, -1, 1, 0 ),
312 'RIGHT' => array( 116, -1, 1, 0 ),
313 'EXACT' => array( 117, 2, 1, 0 ),
314 'TRIM' => array( 118, 1, 1, 0 ),
315 'REPLACE' => array( 119, 4, 1, 0 ),
316 'SUBSTITUTE' => array( 120, -1, 1, 0 ),
317 'CODE' => array( 121, 1, 1, 0 ),
318 'FIND' => array( 124, -1, 1, 0 ),
319 'CELL' => array( 125, -1, 0, 1 ),
320 'ISERR' => array( 126, 1, 1, 0 ),
321 'ISTEXT' => array( 127, 1, 1, 0 ),
322 'ISNUMBER' => array( 128, 1, 1, 0 ),
323 'ISBLANK' => array( 129, 1, 1, 0 ),
324 'T' => array( 130, 1, 0, 0 ),
325 'N' => array( 131, 1, 0, 0 ),
326 'DATEVALUE' => array( 140, 1, 1, 0 ),
327 'TIMEVALUE' => array( 141, 1, 1, 0 ),
328 'SLN' => array( 142, 3, 1, 0 ),
329 'SYD' => array( 143, 4, 1, 0 ),
330 'DDB' => array( 144, -1, 1, 0 ),
331 'INDIRECT' => array( 148, -1, 1, 1 ),
332 'CALL' => array( 150, -1, 1, 0 ),
333 'CLEAN' => array( 162, 1, 1, 0 ),
334 'MDETERM' => array( 163, 1, 2, 0 ),
335 'MINVERSE' => array( 164, 1, 2, 0 ),
336 'MMULT' => array( 165, 2, 2, 0 ),
337 'IPMT' => array( 167, -1, 1, 0 ),
338 'PPMT' => array( 168, -1, 1, 0 ),
339 'COUNTA' => array( 169, -1, 0, 0 ),
340 'PRODUCT' => array( 183, -1, 0, 0 ),
341 'FACT' => array( 184, 1, 1, 0 ),
342 'DPRODUCT' => array( 189, 3, 0, 0 ),
343 'ISNONTEXT' => array( 190, 1, 1, 0 ),
344 'STDEVP' => array( 193, -1, 0, 0 ),
345 'VARP' => array( 194, -1, 0, 0 ),
346 'DSTDEVP' => array( 195, 3, 0, 0 ),
347 'DVARP' => array( 196, 3, 0, 0 ),
348 'TRUNC' => array( 197, -1, 1, 0 ),
349 'ISLOGICAL' => array( 198, 1, 1, 0 ),
350 'DCOUNTA' => array( 199, 3, 0, 0 ),
351 'ROUNDUP' => array( 212, 2, 1, 0 ),
352 'ROUNDDOWN' => array( 213, 2, 1, 0 ),
353 'RANK' => array( 216, -1, 0, 0 ),
354 'ADDRESS' => array( 219, -1, 1, 0 ),
355 'DAYS360' => array( 220, -1, 1, 0 ),
356 'TODAY' => array( 221, 0, 1, 1 ),
357 'VDB' => array( 222, -1, 1, 0 ),
358 'MEDIAN' => array( 227, -1, 0, 0 ),
359 'SUMPRODUCT' => array( 228, -1, 2, 0 ),
360 'SINH' => array( 229, 1, 1, 0 ),
361 'COSH' => array( 230, 1, 1, 0 ),
362 'TANH' => array( 231, 1, 1, 0 ),
363 'ASINH' => array( 232, 1, 1, 0 ),
364 'ACOSH' => array( 233, 1, 1, 0 ),
365 'ATANH' => array( 234, 1, 1, 0 ),
366 'DGET' => array( 235, 3, 0, 0 ),
367 'INFO' => array( 244, 1, 1, 1 ),
368 'DB' => array( 247, -1, 1, 0 ),
369 'FREQUENCY' => array( 252, 2, 0, 0 ),
370 'ERROR.TYPE' => array( 261, 1, 1, 0 ),
371 'REGISTER.ID' => array( 267, -1, 1, 0 ),
372 'AVEDEV' => array( 269, -1, 0, 0 ),
373 'BETADIST' => array( 270, -1, 1, 0 ),
374 'GAMMALN' => array( 271, 1, 1, 0 ),
375 'BETAINV' => array( 272, -1, 1, 0 ),
376 'BINOMDIST' => array( 273, 4, 1, 0 ),
377 'CHIDIST' => array( 274, 2, 1, 0 ),
378 'CHIINV' => array( 275, 2, 1, 0 ),
379 'COMBIN' => array( 276, 2, 1, 0 ),
380 'CONFIDENCE' => array( 277, 3, 1, 0 ),
381 'CRITBINOM' => array( 278, 3, 1, 0 ),
382 'EVEN' => array( 279, 1, 1, 0 ),
383 'EXPONDIST' => array( 280, 3, 1, 0 ),
384 'FDIST' => array( 281, 3, 1, 0 ),
385 'FINV' => array( 282, 3, 1, 0 ),
386 'FISHER' => array( 283, 1, 1, 0 ),
387 'FISHERINV' => array( 284, 1, 1, 0 ),
388 'FLOOR' => array( 285, 2, 1, 0 ),
389 'GAMMADIST' => array( 286, 4, 1, 0 ),
390 'GAMMAINV' => array( 287, 3, 1, 0 ),
391 'CEILING' => array( 288, 2, 1, 0 ),
392 'HYPGEOMDIST' => array( 289, 4, 1, 0 ),
393 'LOGNORMDIST' => array( 290, 3, 1, 0 ),
394 'LOGINV' => array( 291, 3, 1, 0 ),
395 'NEGBINOMDIST' => array( 292, 3, 1, 0 ),
396 'NORMDIST' => array( 293, 4, 1, 0 ),
397 'NORMSDIST' => array( 294, 1, 1, 0 ),
398 'NORMINV' => array( 295, 3, 1, 0 ),
399 'NORMSINV' => array( 296, 1, 1, 0 ),
400 'STANDARDIZE' => array( 297, 3, 1, 0 ),
401 'ODD' => array( 298, 1, 1, 0 ),
402 'PERMUT' => array( 299, 2, 1, 0 ),
403 'POISSON' => array( 300, 3, 1, 0 ),
404 'TDIST' => array( 301, 3, 1, 0 ),
405 'WEIBULL' => array( 302, 4, 1, 0 ),
406 'SUMXMY2' => array( 303, 2, 2, 0 ),
407 'SUMX2MY2' => array( 304, 2, 2, 0 ),
408 'SUMX2PY2' => array( 305, 2, 2, 0 ),
409 'CHITEST' => array( 306, 2, 2, 0 ),
410 'CORREL' => array( 307, 2, 2, 0 ),
411 'COVAR' => array( 308, 2, 2, 0 ),
412 'FORECAST' => array( 309, 3, 2, 0 ),
413 'FTEST' => array( 310, 2, 2, 0 ),
414 'INTERCEPT' => array( 311, 2, 2, 0 ),
415 'PEARSON' => array( 312, 2, 2, 0 ),
416 'RSQ' => array( 313, 2, 2, 0 ),
417 'STEYX' => array( 314, 2, 2, 0 ),
418 'SLOPE' => array( 315, 2, 2, 0 ),
419 'TTEST' => array( 316, 4, 2, 0 ),
420 'PROB' => array( 317, -1, 2, 0 ),
421 'DEVSQ' => array( 318, -1, 0, 0 ),
422 'GEOMEAN' => array( 319, -1, 0, 0 ),
423 'HARMEAN' => array( 320, -1, 0, 0 ),
424 'SUMSQ' => array( 321, -1, 0, 0 ),
425 'KURT' => array( 322, -1, 0, 0 ),
426 'SKEW' => array( 323, -1, 0, 0 ),
427 'ZTEST' => array( 324, -1, 0, 0 ),
428 'LARGE' => array( 325, 2, 0, 0 ),
429 'SMALL' => array( 326, 2, 0, 0 ),
430 'QUARTILE' => array( 327, 2, 0, 0 ),
431 'PERCENTILE' => array( 328, 2, 0, 0 ),
432 'PERCENTRANK' => array( 329, -1, 0, 0 ),
433 'MODE' => array( 330, -1, 2, 0 ),
434 'TRIMMEAN' => array( 331, 2, 0, 0 ),
435 'TINV' => array( 332, 2, 1, 0 ),
436 'CONCATENATE' => array( 336, -1, 1, 0 ),
437 'POWER' => array( 337, 2, 1, 0 ),
438 'RADIANS' => array( 342, 1, 1, 0 ),
439 'DEGREES' => array( 343, 1, 1, 0 ),
440 'SUBTOTAL' => array( 344, -1, 0, 0 ),
441 'SUMIF' => array( 345, -1, 0, 0 ),
442 'COUNTIF' => array( 346, 2, 0, 0 ),
443 'COUNTBLANK' => array( 347, 1, 0, 0 ),
444 'ROMAN' => array( 354, -1, 1, 0 )
445 );
446 }
448 /**
449 * Convert a token to the proper ptg value.
450 *
451 * @access private
452 * @param mixed $token The token to convert.
453 * @return mixed the converted token on success. PEAR_Error if the token
454 * is not recognized
455 */
456 function _convert($token)\r {
457 if (preg_match("/^\"[^\"]{0,255}\"$/", $token))\r {
458 return $this->_convertString($token);
460 return $this->_convertNumber($token);
461 }
462 // match references like A1 or $A$1
464 return $this->_convertRef2d($token);
465 }
466 // match external references like Sheet1:Sheet2!A1
468 return $this->_convertRef3d($token);
469 }
470 // match ranges like A1:B2
471 elseif (preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?(\d+)\:(\$)?[A-Ia-i]?[A-Za-z](\$)?(\d+)$/",$token))\r {
472 return $this->_convertRange2d($token);
473 }
474 // match ranges like A1..B2
475 elseif (preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?(\d+)\.\.(\$)?[A-Ia-i]?[A-Za-z](\$)?(\d+)$/",$token))\r {
476 return $this->_convertRange2d($token);
477 }
478 // match external ranges like Sheet1:Sheet2!A1:B2
479 elseif (preg_match("/^[A-Za-z0-9_]+(\:[A-Za-z0-9_]+)?\!([A-Ia-i]?[A-Za-z])?(\d+)\:([A-Ia-i]?[A-Za-z])?(\d+)$/",$token))\r {
480 return $this->_convertRange3d($token);
481 }
482 // match external ranges like 'Sheet1:Sheet2'!A1:B2
483 elseif (preg_match("/^'[A-Za-z0-9_ ]+(\:[A-Za-z0-9_ ]+)?'\!([A-Ia-i]?[A-Za-z])?(\d+)\:([A-Ia-i]?[A-Za-z])?(\d+)$/",$token))\r {
484 return $this->_convertRange3d($token);
485 }
486 elseif (isset($this->ptg[$token])) // operators (including parentheses)\r {
487 return pack("C", $this->ptg[$token]);
488 }
489 // commented so argument number can be processed correctly. See toReversePolish().
490 /*elseif (preg_match("/[A-Z0-9\xc0-\xdc\.]+/",$token))
491 {
492 return($this->_convertFunction($token,$this->_func_args));
493 }*/
494 // if it's an argument, ignore the token (the argument remains)
495 elseif ($token == 'arg')\r {
496 return '';
497 }
498 // TODO: use real error codes
499 trigger_error("Unknown token $token", E_USER_ERROR);
500 }
502 /**
503 * Convert a number token to ptgInt or ptgNum
504 *
505 * @access private
506 * @param mixed $num an integer or double for conversion to its ptg value
507 */
508 function _convertNumber($num)\r {
510 // Integer in the range 0..2**16-1
512 if ((preg_match("/^\d+$/",$num)) and ($num <= 65535)) {
513 return(pack("Cv", $this->ptg['ptgInt'], $num));
514 }\r else { // A float
515 if ($this->_byte_order) { // if it's Big Endian
516 $num = strrev($num);
517 }
518 return pack("Cd", $this->ptg['ptgNum'], $num);
519 }
520 }
522 /**
523 * Convert a string token to ptgStr
524 *
525 * @access private
526 * @param string $string A string for conversion to its ptg value
527 */
528 function _convertString($string)\r {
529 // chop away beggining and ending quotes
530 $string = substr($string, 1, strlen($string) - 2);
531 return pack("CC", $this->ptg['ptgStr'], strlen($string)).$string;
532 }
534 /**
535 * Convert a function to a ptgFunc or ptgFuncVarV depending on the number of
536 * args that it takes.
537 *
538 * @access private
539 * @param string $token The name of the function for convertion to ptg value.
540 * @param integer $num_args The number of arguments the function receives.
541 * @return string The packed ptg for the function
542 */
543 function _convertFunction($token, $num_args)\r {
544 $args = $this->_functions[$token][1];
545 $volatile = $this->_functions[$token][3];
547 // Fixed number of args eg. TIME($i,$j,$k).
548 if ($args >= 0) {
549 return pack("Cv", $this->ptg['ptgFuncV'], $this->_functions[$token][0]);
550 }
551 // Variable number of args eg. SUM($i,$j,$k, ..).
552 if ($args == -1) {
553 return pack("CCv", $this->ptg['ptgFuncVarV'], $num_args, $this->_functions[$token][0]);
554 }
555 }
557 /**
558 * Convert an Excel range such as A1:D4 to a ptgRefV.
559 *
560 * @access private
561 * @param string $range An Excel range in the A1:A2 or A1..A2 format.
562 */
563 function _convertRange2d($range)\r {
564 $class = 2; // as far as I know, this is magick.
566 // Split the range into 2 cell refs
567 if (preg_match("/^([A-Ia-i]?[A-Za-z])(\d+)\:([A-Ia-i]?[A-Za-z])(\d+)$/",$range)) {
568 list($cell1, $cell2) = explode(':', $range);
569 }\r elseif (preg_match("/^([A-Ia-i]?[A-Za-z])(\d+)\.\.([A-Ia-i]?[A-Za-z])(\d+)$/",$range)) {
570 list($cell1, $cell2) = explode('..', $range);
571 }\r else {
572 // TODO: use real error codes
573 trigger_error("Unknown range separator", E_USER_ERROR);
574 }
576 // Convert the cell references
577 $cell_array1 = $this->_cellToPackedRowcol($cell1);
578 if ($this->isError($cell_array1)) {
579 return $cell_array1;
580 }
581 list($row1, $col1) = $cell_array1;
582 $cell_array2 = $this->_cellToPackedRowcol($cell2);
583 if ($this->isError($cell_array2)) {
584 return $cell_array2;
585 }
586 list($row2, $col2) = $cell_array2;
588 // The ptg value depends on the class of the ptg.
589 if ($class == 0) {
590 $ptgArea = pack("C", $this->ptg['ptgArea']);
591 }\r elseif ($class == 1) {
592 $ptgArea = pack("C", $this->ptg['ptgAreaV']);
593 }\r elseif ($class == 2) {
594 $ptgArea = pack("C", $this->ptg['ptgAreaA']);
595 }\r else {
596 // TODO: use real error codes
597 trigger_error("Unknown class $class", E_USER_ERROR);
598 }
599 return $ptgArea . $row1 . $row2 . $col1. $col2;
600 }
602 /**
603 * Convert an Excel 3d range such as "Sheet1!A1:D4" or "Sheet1:Sheet2!A1:D4" to
604 * a ptgArea3dV.
605 *
606 * @access private
607 * @param string $token An Excel range in the Sheet1!A1:A2 format.
608 */
609 function _convertRange3d($token)\r {
610 $class = 2; // as far as I know, this is magick.
612 // Split the ref at the ! symbol
613 list($ext_ref, $range) = explode('!', $token);
615 // Convert the external reference part
616 $ext_ref = $this->_packExtRef($ext_ref);
617 if ($this->isError($ext_ref)) {
618 return $ext_ref;
619 }
621 // Split the range into 2 cell refs
622 list($cell1, $cell2) = explode(':', $range);
624 // Convert the cell references
625 if (preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?(\d+)$/", $cell1))\r {
626 $cell_array1 = $this->_cellToPackedRowcol($cell1);
627 if ($this->isError($cell_array1)) {
628 return $cell_array1;
629 }
630 list($row1, $col1) = $cell_array1;
631 $cell_array2 = $this->_cellToPackedRowcol($cell2);
632 if ($this->isError($cell_array2)) {
633 return $cell_array2;
634 }
635 list($row2, $col2) = $cell_array2;
636 }\r else { // It's a columns range (like 26:27)
637 $cells_array = $this->_rangeToPackedRange($cell1.':'.$cell2);
638 if ($this->isError($cells_array)) {
639 return $cells_array;
640 }
641 list($row1, $col1, $row2, $col2) = $cells_array;
642 }
644 // The ptg value depends on the class of the ptg.
645 if ($class == 0) {
646 $ptgArea = pack("C", $this->ptg['ptgArea3d']);
647 }\r elseif ($class == 1) {
648 $ptgArea = pack("C", $this->ptg['ptgArea3dV']);
649 }\r elseif ($class == 2) {
650 $ptgArea = pack("C", $this->ptg['ptgArea3dA']);
651 }\r else {
652 trigger_error("Unknown class $class", E_USER_ERROR);
653 }
655 return $ptgArea . $ext_ref . $row1 . $row2 . $col1. $col2;
656 }
658 /**
659 * Convert an Excel reference such as A1, $B2, C$3 or $D$4 to a ptgRefV.
660 *
661 * @access private
662 * @param string $cell An Excel cell reference
663 * @return string The cell in packed() format with the corresponding ptg
664 */
665 function _convertRef2d($cell)\r {
666 $class = 2; // as far as I know, this is magick.
668 // Convert the cell reference
669 $cell_array = $this->_cellToPackedRowcol($cell);
670 if ($this->isError($cell_array)) {
671 return $cell_array;
672 }
673 list($row, $col) = $cell_array;
675 // The ptg value depends on the class of the ptg.
676 if ($class == 0) {
677 $ptgRef = pack("C", $this->ptg['ptgRef']);
678 }\r elseif ($class == 1) {
679 $ptgRef = pack("C", $this->ptg['ptgRefV']);
680 }\r elseif ($class == 2) {
681 $ptgRef = pack("C", $this->ptg['ptgRefA']);
682 }\r else {
683 // TODO: use real error codes
684 trigger_error("Unknown class $class",E_USER_ERROR);
685 }
686 return $ptgRef.$row.$col;
687 }
689 /**
690 * Convert an Excel 3d reference such as "Sheet1!A1" or "Sheet1:Sheet2!A1" to a
691 * ptgRef3dV.
692 *
693 * @access private
694 * @param string $cell An Excel cell reference
695 * @return string The cell in packed() format with the corresponding ptg
696 */
697 function _convertRef3d($cell)\r {
698 $class = 2; // as far as I know, this is magick.
700 // Split the ref at the ! symbol
701 list($ext_ref, $cell) = explode('!', $cell);
703 // Convert the external reference part
704 $ext_ref = $this->_packExtRef($ext_ref);
705 if ($this->isError($ext_ref)) {
706 return $ext_ref;
707 }
709 // Convert the cell reference part
710 list($row, $col) = $this->_cellToPackedRowcol($cell);
712 // The ptg value depends on the class of the ptg.
713 if ($class == 0) {
714 $ptgRef = pack("C", $this->ptg['ptgRef3d']);
715 } elseif ($class == 1) {
716 $ptgRef = pack("C", $this->ptg['ptgRef3dV']);
717 } elseif ($class == 2) {
718 $ptgRef = pack("C", $this->ptg['ptgRef3dA']);
719 }\r else {
720 trigger_error("Unknown class $class", E_USER_ERROR);
721 }
723 return $ptgRef . $ext_ref. $row . $col;
724 }
726 /**
727 * Convert the sheet name part of an external reference, for example "Sheet1" or
728 * "Sheet1:Sheet2", to a packed structure.
729 *
730 * @access private
731 * @param string $ext_ref The name of the external reference
732 * @return string The reference index in packed() format
733 */
734 function _packExtRef($ext_ref) {
735 $ext_ref = preg_replace("/^'/", '', $ext_ref); // Remove leading ' if any.
736 $ext_ref = preg_replace("/'$/", '', $ext_ref); // Remove trailing ' if any.
738 // Check if there is a sheet range eg., Sheet1:Sheet2.
739 if (preg_match("/:/", $ext_ref))\r {
740 list($sheet_name1, $sheet_name2) = explode(':', $ext_ref);
742 $sheet1 = $this->_getSheetIndex($sheet_name1);
743 if ($sheet1 == -1) {
744 trigger_error("Unknown sheet name $sheet_name1 in formula",E_USER_ERROR);
745 }
746 $sheet2 = $this->_getSheetIndex($sheet_name2);
747 if ($sheet2 == -1) {
748 trigger_error("Unknown sheet name $sheet_name2 in formula",E_USER_ERROR);
749 }
751 // Reverse max and min sheet numbers if necessary
752 if ($sheet1 > $sheet2) {
753 list($sheet1, $sheet2) = array($sheet2, $sheet1);
754 }
755 }\r else { // Single sheet name only.
756 $sheet1 = $this->_getSheetIndex($ext_ref);
757 if ($sheet1 == -1) {
758 trigger_error("Unknown sheet name $ext_ref in formula",E_USER_ERROR);
759 }
760 $sheet2 = $sheet1;
761 }
763 // References are stored relative to 0xFFFF.
764 $offset = -1 - $sheet1;
766 return pack('vdvv', $offset, 0x00, $sheet1, $sheet2);
767 }
769 /**
770 * Look up the index that corresponds to an external sheet name. The hash of
771 * sheet names is updated by the addworksheet() method of the
772 * Spreadsheet_Excel_Writer_Workbook class.
773 *
774 * @access private
775 * @return integer
776 */
777 function _getSheetIndex($sheet_name)\r {
778 if (!isset($this->_ext_sheets[$sheet_name])) {
779 return -1;
780 }\r else {
781 return $this->_ext_sheets[$sheet_name];
782 }
783 }
785 /**
786 * This method is used to update the array of sheet names. It is
787 * called by the addWorksheet() method of the Spreadsheet_Excel_Writer_Workbook class.
788 *
789 * @access private
790 * @param string $name The name of the worksheet being added
791 * @param integer $index The index of the worksheet being added
792 */
793 function setExtSheet($name, $index)\r {
794 $this->_ext_sheets[$name] = $index;
795 }
797 /**
798 * pack() row and column into the required 3 byte format.
799 *
800 * @access private
801 * @param string $cell The Excel cell reference to be packed
802 * @return array Array containing the row and column in packed() format
803 */
804 function _cellToPackedRowcol($cell)\r {
805 $cell = strtoupper($cell);
806 list($row, $col, $row_rel, $col_rel) = $this->_cellToRowcol($cell);
807 if ($col >= 256) {
808 trigger_error("Column in: $cell greater than 255", E_USER_ERROR);
809 }
810 if ($row >= 16384) {
811 trigger_error("Row in: $cell greater than 16384 ", E_USER_ERROR);
812 }
814 // Set the high bits to indicate if row or col are relative.
815 $row |= $col_rel << 14;
816 $row |= $row_rel << 15;
818 $row = pack('v', $row);
819 $col = pack('C', $col);
821 return array($row, $col);
822 }
824 /**
825 * pack() row range into the required 3 byte format.
826 * Just using maximun col/rows, which is probably not the correct solution
827 *
828 * @access private
829 * @param string $range The Excel range to be packed
830 * @return array Array containing (row1,col1,row2,col2) in packed() format
831 */
832 function _rangeToPackedRange($range)\r {
833 preg_match('/(\$)?(\d+)\:(\$)?(\d+)/', $range, $match);
834 // return absolute rows if there is a $ in the ref
835 $row1_rel = empty($match[1]) ? 1 : 0;
836 $row1 = $match[2];
837 $row2_rel = empty($match[3]) ? 1 : 0;
838 $row2 = $match[4];
839 // Convert 1-index to zero-index
840 $row1--;
841 $row2--;
842 // Trick poor inocent Excel
843 $col1 = 0;
844 $col2 = 16383; // maximum possible value for Excel 5 (change this!!!)
846 //list($row, $col, $row_rel, $col_rel) = $this->_cellToRowcol($cell);
847 if (($row1 >= 16384) or ($row2 >= 16384)) {
848 trigger_error("Row in: $range greater than 16384 ",E_USER_ERROR);
849 }
851 // Set the high bits to indicate if rows are relative.
852 $row1 |= $row1_rel << 14;
853 $row2 |= $row2_rel << 15;
855 $row1 = pack('v', $row1);
856 $row2 = pack('v', $row2);
857 $col1 = pack('C', $col1);
858 $col2 = pack('C', $col2);
860 return array($row1, $col1, $row2, $col2);
861 }
863 /**
864 * Convert an Excel cell reference such as A1 or $B2 or C$3 or $D$4 to a zero
865 * indexed row and column number. Also returns two (0,1) values to indicate
866 * whether the row or column are relative references.
867 *
868 * @access private
869 * @param string $cell The Excel cell reference in A1 format.
870 * @return array
871 */
872 function _cellToRowcol($cell)\r {
873 preg_match('/(\$)?([A-I]?[A-Z])(\$)?(\d+)/',$cell,$match);
874 // return absolute column if there is a $ in the ref
875 $col_rel = empty($match[1]) ? 1 : 0;
876 $col_ref = $match[2];
877 $row_rel = empty($match[3]) ? 1 : 0;
878 $row = $match[4];
880 // Convert base26 column string to a number.
881 $expn = strlen($col_ref) - 1;
882 $col = 0;
883 for ($i=0; $i < strlen($col_ref); $i++)\r {
884 $col += (ord($col_ref{$i}) - ord('A') + 1) * pow(26, $expn);
885 $expn--;
886 }
888 // Convert 1-index to zero-index
889 $row--;
890 $col--;
892 return array($row, $col, $row_rel, $col_rel);
893 }
895 /**
896 * Advance to the next valid token.
897 *
898 * @access private
899 */
900 function _advance()\r {
901 $i = $this->_current_char;
902 // eat up white spaces
903 if ($i < strlen($this->_formula))\r {
904 while ($this->_formula{$i} == " ") {
905 $i++;
906 }
907 if ($i < strlen($this->_formula) - 1) {
908 $this->_lookahead = $this->_formula{$i+1};
909 }
910 $token = "";
911 }
912 while ($i < strlen($this->_formula))\r {
913 $token .= $this->_formula{$i};
914 if ($i < strlen($this->_formula) - 1) {
915 $this->_lookahead = $this->_formula{$i+1};
916 }\r else {
917 $this->_lookahead = '';
918 }
919 if ($this->_match($token) != '')\r {
920 //if ($i < strlen($this->_formula) - 1) {
921 // $this->_lookahead = $this->_formula{$i+1};
922 //}
923 $this->_current_char = $i + 1;
924 $this->_current_token = $token;
925 return 1;
926 }
927 if ($i < strlen($this->_formula) - 2) {
928 $this->_lookahead = $this->_formula{$i+2};
929 }\r else {
930 // if we run out of characters _lookahead becomes empty
931 $this->_lookahead = '';
932 }
933 $i++;
934 }
935 //die("Lexical error ".$this->_current_char);
936 }
938 /**
939 * Checks if it's a valid token.
940 *
941 * @access private
942 * @param mixed $token The token to check.
943 * @return mixed The checked token or false on failure
944 */
945 function _match($token)\r {
946 switch($token)\r {
947 case SPREADSHEET_EXCEL_WRITER_ADD:
948 return($token);
949 break;
950 case SPREADSHEET_EXCEL_WRITER_SUB:
951 return($token);
952 break;
953 case SPREADSHEET_EXCEL_WRITER_MUL:
954 return($token);
955 break;
956 case SPREADSHEET_EXCEL_WRITER_DIV:
957 return($token);
958 break;
959 case SPREADSHEET_EXCEL_WRITER_OPEN:
960 return($token);
961 break;
962 case SPREADSHEET_EXCEL_WRITER_CLOSE:
963 return($token);
964 break;
965 case SPREADSHEET_EXCEL_WRITER_SCOLON:
966 return($token);
967 break;
968 case SPREADSHEET_EXCEL_WRITER_COMA:
969 return($token);
970 break;
971 case SPREADSHEET_EXCEL_WRITER_GT:
972 if ($this->_lookahead == '=') { // it's a GE token
973 break;
974 }
975 return($token);
976 break;
977 case SPREADSHEET_EXCEL_WRITER_LT:
978 // it's a LE or a NE token
979 if (($this->_lookahead == '=') or ($this->_lookahead == '>')) {
980 break;
981 }
982 return($token);
983 break;
984 case SPREADSHEET_EXCEL_WRITER_GE:
985 return($token);
986 break;
987 case SPREADSHEET_EXCEL_WRITER_LE:
988 return($token);
989 break;
990 case SPREADSHEET_EXCEL_WRITER_EQ:
991 return($token);
992 break;
993 case SPREADSHEET_EXCEL_WRITER_NE:
994 return($token);
995 break;
996 default:
997 // if it's a reference
998 if (preg_match('/^\$?[A-Ia-i]?[A-Za-z]\$?[0-9]+$/',$token) and
999 !preg_match("/[0-9]/",$this->_lookahead) and
1000 ($this->_lookahead != ':') and ($this->_lookahead != '.') and
1001 ($this->_lookahead != '!')) {
1002 return $token;
1003 }
1004 // If it's an external reference (Sheet1!A1 or Sheet1:Sheet2!A1)
1005 elseif (preg_match("/^[A-Za-z0-9_]+(\:[A-Za-z0-9_]+)?\![A-Ia-i]?[A-Za-z][0-9]+$/",$token) and
1006 !preg_match("/[0-9]/",$this->_lookahead) and
1007 ($this->_lookahead != ':') and ($this->_lookahead != '.')) {
1008 return $token;
1009 }
1010 // if it's a range (A1:A2)
1011 elseif (preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+:(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+$/",$token) and
1012 !preg_match("/[0-9]/",$this->_lookahead)) {
1013 return $token;
1014 }
1015 // if it's a range (A1..A2)
1016 elseif (preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+\.\.(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+$/",$token) and
1017 !preg_match("/[0-9]/",$this->_lookahead)) {
1018 return $token;
1019 }
1020 // If it's an external range like Sheet1:Sheet2!A1:B2
1021 elseif (preg_match("/^[A-Za-z0-9_]+(\:[A-Za-z0-9_]+)?\!([A-Ia-i]?[A-Za-z])?[0-9]+:([A-Ia-i]?[A-Za-z])?[0-9]+$/",$token) and
1022 !preg_match("/[0-9]/",$this->_lookahead)) {
1023 return $token;
1024 }
1025 // If it's an external range like 'Sheet1:Sheet2'!A1:B2
1026 elseif (preg_match("/^'[A-Za-z0-9_ ]+(\:[A-Za-z0-9_ ]+)?'\!([A-Ia-i]?[A-Za-z])?[0-9]+:([A-Ia-i]?[A-Za-z])?[0-9]+$/",$token) and
1027 !preg_match("/[0-9]/",$this->_lookahead)) {
1028 return $token;
1029 }
1030 // If it's a number (check that it's not a sheet name or range)
1031 elseif (is_numeric($token) and
1032 (!is_numeric($token.$this->_lookahead) or ($this->_lookahead == '')) and
1033 ($this->_lookahead != '!') and ($this->_lookahead != ':')) {
1034 return $token;
1035 }
1036 // If it's a string (of maximum 255 characters)
1037 elseif (preg_match("/^\"[^\"]{0,255}\"$/",$token)) {
1038 return $token;
1039 }
1040 // if it's a function call
1041 elseif (preg_match("/^[A-Z0-9\xc0-\xdc\.]+$/i",$token) and ($this->_lookahead == "(")) {
1042 return $token;
1043 }
1044 return '';
1045 }
1046 }
1048 /**
1049 * The parsing method. It parses a formula.
1050 *
1051 * @access public
1052 * @param string $formula The formula to parse, without the initial equal sign (=).
1053 */
1054 function parse($formula) {
1055 $this->_current_char = 0;
1056 $this->_formula = $formula;
1057 $this->_lookahead = $formula{1};
1058 $this->_advance();
1059 $this->_parse_tree = $this->_condition();
1060 if ($this->isError($this->_parse_tree)) {
1061 return $this->_parse_tree;
1062 }
1063 }
1065 /**
1066 * It parses a condition. It assumes the following rule:
1067 * Cond -> Expr [(">" | "<") Expr]
1068 *
1069 * @access private
1070 * @return mixed The parsed ptg'd tree
1071 */
1072 function _condition()\r {
1073 $result = $this->_expression();
1074 if ($this->isError($result)) {
1075 return $result;
1076 }
1077 if ($this->_current_token == SPREADSHEET_EXCEL_WRITER_LT)\r {
1078 $this->_advance();
1079 $result2 = $this->_expression();
1080 if ($this->isError($result2)) {
1081 return $result2;
1082 }
1083 $result = $this->_createTree('ptgLT', $result, $result2);
1085 $this->_advance();
1086 $result2 = $this->_expression();
1087 if ($this->isError($result2)) {
1088 return $result2;
1089 }
1090 $result = $this->_createTree('ptgGT', $result, $result2);
1092 $this->_advance();
1093 $result2 = $this->_expression();
1094 if ($this->isError($result2)) {
1095 return $result2;
1096 }
1097 $result = $this->_createTree('ptgLE', $result, $result2);
1099 $this->_advance();
1100 $result2 = $this->_expression();
1101 if ($this->isError($result2)) {
1102 return $result2;
1103 }
1104 $result = $this->_createTree('ptgGE', $result, $result2);
1106 $this->_advance();
1107 $result2 = $this->_expression();
1108 if ($this->isError($result2)) {
1109 return $result2;
1110 }
1111 $result = $this->_createTree('ptgEQ', $result, $result2);
1113 $this->_advance();
1114 $result2 = $this->_expression();
1115 if ($this->isError($result2)) {
1116 return $result2;
1117 }
1118 $result = $this->_createTree('ptgNE', $result, $result2);
1119 }
1120 return $result;
1121 }
1123 /**
1124 * It parses a expression. It assumes the following rule:
1125 * Expr -> Term [("+" | "-") Term]
1126 *
1127 * @access private
1128 * @return mixed The parsed ptg'd tree
1129 */
1130 function _expression()\r {
1131 // If it's a string return a string node
1132 if (preg_match("/^\"[^\"]{0,255}\"$/", $this->_current_token))\r {
1133 $result = $this->_createTree($this->_current_token, '', '');
1134 $this->_advance();
1135 return $result;
1136 }
1137 $result = $this->_term();
1138 if ($this->isError($result)) {
1139 return $result;
1140 }
1141 while (($this->_current_token == SPREADSHEET_EXCEL_WRITER_ADD) or
1142 ($this->_current_token == SPREADSHEET_EXCEL_WRITER_SUB))\r {
1144 $this->_advance();
1145 $result2 = $this->_term();
1146 if ($this->isError($result2)) {
1147 return $result2;
1148 }
1149 $result = $this->_createTree('ptgAdd', $result, $result2);
1151 $this->_advance();
1152 $result2 = $this->_term();
1153 if ($this->isError($result2)) {
1154 return $result2;
1155 }
1156 $result = $this->_createTree('ptgSub', $result, $result2);
1157 }
1158 }
1159 return $result;
1160 }
1162 /**
1163 * This function just introduces a ptgParen element in the tree, so that Excel
1164 * doesn't get confused when working with a parenthesized formula afterwards.
1165 *
1166 * @access private
1167 * @see _fact()
1168 * @return mixed The parsed ptg'd tree
1169 */
1170 function _parenthesizedExpression()\r {
1171 $result = $this->_createTree('ptgParen', $this->_expression(), '');
1172 return $result;
1173 }
1175 /**
1176 * It parses a term. It assumes the following rule:
1177 * Term -> Fact [("*" | "/") Fact]
1178 *
1179 * @access private
1180 * @return mixed The parsed ptg'd tree
1181 */
1182 function _term()\r {
1183 $result = $this->_fact();
1184 if ($this->isError($result)) {
1185 return $result;
1186 }
1187 while (($this->_current_token == SPREADSHEET_EXCEL_WRITER_MUL) or
1188 ($this->_current_token == SPREADSHEET_EXCEL_WRITER_DIV)) {
1190 $this->_advance();
1191 $result2 = $this->_fact();
1192 if ($this->isError($result2)) {
1193 return $result2;
1194 }
1195 $result = $this->_createTree('ptgMul', $result, $result2);
1197 $this->_advance();
1198 $result2 = $this->_fact();
1199 if ($this->isError($result2)) {
1200 return $result2;
1201 }
1202 $result = $this->_createTree('ptgDiv', $result, $result2);
1203 }
1204 }
1205 return $result;
1206 }
1208 /**
1209 * It parses a factor. It assumes the following rule:
1210 * Fact -> ( Expr )
1211 * | CellRef
1212 * | CellRange
1213 * | Number
1214 * | Function
1215 *
1216 * @access private
1217 * @return mixed The parsed ptg'd tree
1218 */
1219 function _fact()\r {
1220 if ($this->_current_token == SPREADSHEET_EXCEL_WRITER_OPEN)\r {
1221 $this->_advance(); // eat the "("
1222 $result = $this->_parenthesizedExpression();
1223 if ($this->_current_token != SPREADSHEET_EXCEL_WRITER_CLOSE) {
1224 trigger_error("')' token expected.",E_USER_ERROR);
1225 }
1226 $this->_advance(); // eat the ")"
1227 return $result;
1229 // if it's a reference
1230 $result = $this->_createTree($this->_current_token, '', '');
1231 $this->_advance();
1232 return $result;
1233 }\r elseif (preg_match("/^[A-Za-z0-9_]+(\:[A-Za-z0-9_]+)?\![A-Ia-i]?[A-Za-z][0-9]+$/",$this->_current_token))\r {
1234 // If it's an external reference (Sheet1!A1 or Sheet1:Sheet2!A1)
1235 $result = $this->_createTree($this->_current_token, '', '');
1236 $this->_advance();
1237 return $result;
1238 }\r elseif (preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+:(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+$/",$this->_current_token) or
1239 preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+\.\.(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+$/",$this->_current_token))\r {
1240 // if it's a range
1241 $result = $this->_current_token;
1242 $this->_advance();
1243 return $result;
1244 }\r elseif (preg_match("/^[A-Za-z0-9_]+(\:[A-Za-z0-9_]+)?\!([A-Ia-i]?[A-Za-z])?[0-9]+:([A-Ia-i]?[A-Za-z])?[0-9]+$/",$this->_current_token))\r {
1245 // If it's an external range (Sheet1!A1:B2)
1246 $result = $this->_current_token;
1247 $this->_advance();
1248 return $result;
1249 }\r elseif (preg_match("/^'[A-Za-z0-9_ ]+(\:[A-Za-z0-9_ ]+)?'\!([A-Ia-i]?[A-Za-z])?[0-9]+:([A-Ia-i]?[A-Za-z])?[0-9]+$/",$this->_current_token))\r {
1250 // If it's an external range ('Sheet1'!A1:B2)
1251 $result = $this->_current_token;
1252 $this->_advance();
1253 return $result;
1255 $result = $this->_createTree($this->_current_token, '', '');
1256 $this->_advance();
1257 return $result;
1259 // if it's a function call
1260 $result = $this->_func();
1261 return $result;
1262 }
1263 trigger_error("Sintactic error: ".$this->_current_token.", lookahead: ".
1264 $this->_lookahead.", current char: ".$this->_current_char, E_USER_ERROR);
1265 }
1267 /**
1268 * It parses a function call. It assumes the following rule:
1269 * Func -> ( Expr [,Expr]* )
1270 *
1271 * @access private
1272 */
1273 function _func()\r {
1274 $num_args = 0; // number of arguments received
1275 $function = $this->_current_token;
1276 $this->_advance();
1277 $this->_advance(); // eat the "("
1278 while ($this->_current_token != ')')\r {
1279 if ($num_args > 0)\r {
1280 if ($this->_current_token == SPREADSHEET_EXCEL_WRITER_COMA ||
1281 $this->_current_token == SPREADSHEET_EXCEL_WRITER_SCOLON) {
1282 $this->_advance(); // eat the ","
1283 }\r else {
1284 trigger_error("Sintactic error: coma expected in ".
1285 "function $function, {$num_args}º arg", E_USER_ERROR);
1286 }
1287 $result2 = $this->_condition();
1288 if ($this->isError($result2)) {
1289 return $result2;
1290 }
1291 $result = $this->_createTree('arg', $result, $result2);
1292 }\r else { // first argument
1293 $result2 = $this->_condition();
1294 if ($this->isError($result2)) {
1295 return $result2;
1296 }
1297 $result = $this->_createTree('arg', '', $result2);
1298 }
1299 $num_args++;
1300 }
1301 $args = $this->_functions[$function][1];
1302 // If fixed number of args eg. TIME($i,$j,$k). Check that the number of args is valid.
1303 if (($args >= 0) and ($args != $num_args)) {
1304 trigger_error("Incorrect number of arguments in function $function() ",E_USER_ERROR);
1305 }
1307 $result = $this->_createTree($function, $result, $num_args);
1308 $this->_advance(); // eat the ")"
1309 return $result;
1310 }
1312 /**
1313 * Creates a tree. In fact an array which may have one or two arrays (sub-trees)
1314 * as elements.
1315 *
1316 * @access private
1317 * @param mixed $value The value of this node.
1318 * @param mixed $left The left array (sub-tree) or a final node.
1319 * @param mixed $right The right array (sub-tree) or a final node.
1320 */
1321 function _createTree($value, $left, $right)\r {
1322 return(array('value' => $value, 'left' => $left, 'right' => $right));
1323 }
1325 /**
1326 * Builds a string containing the tree in reverse polish notation (What you
1327 * would use in a HP calculator stack).
1328 * The following tree:
1329 *
1330 * +
1331 * / \
1332 * 2 3
1333 *
1334 * produces: "23+"
1335 *
1336 * The following tree:
1337 *
1338 * +
1339 * / \
1340 * 3 *
1341 * / \
1342 * 6 A1
1343 *
1344 * produces: "36A1*+"
1345 *
1346 * In fact all operands, functions, references, etc... are written as ptg's
1347 *
1348 * @access public
1349 * @param array $tree The optional tree to convert.
1350 * @return string The tree in reverse polish notation
1351 */
1352 function toReversePolish($tree = array())\r {
1353 $polish = ""; // the string we are going to return
1354 if (empty($tree)) { // If it's the first call use _parse_tree
1355 $tree = $this->_parse_tree;
1356 }
1357 if (is_array($tree['left']))\r {
1358 $converted_tree = $this->toReversePolish($tree['left']);
1359 if ($this->isError($converted_tree)) {
1360 return $converted_tree;
1361 }
1362 $polish .= $converted_tree;
1363 }\r elseif ($tree['left'] != '') { // It's a final node
1364 $converted_tree = $this->_convert($tree['left']);
1365 if ($this->isError($converted_tree)) {
1366 return $converted_tree;
1367 }
1368 $polish .= $converted_tree;
1369 }
1370 if (is_array($tree['right']))\r {
1371 $converted_tree = $this->toReversePolish($tree['right']);
1372 if ($this->isError($converted_tree)) {
1373 return $converted_tree;
1374 }
1375 $polish .= $converted_tree;
1376 }\r elseif ($tree['right'] != '') { // It's a final node
1377 $converted_tree = $this->_convert($tree['right']);
1378 if ($this->isError($converted_tree)) {
1379 return $converted_tree;
1380 }
1381 $polish .= $converted_tree;
1382 }
1383 // if it's a function convert it here (so we can set it's arguments)
1384 if (preg_match("/^[A-Z0-9\xc0-\xdc\.]+$/",$tree['value']) and
1385 !preg_match('/^([A-Ia-i]?[A-Za-z])(\d+)$/',$tree['value']) and
1386 !preg_match("/^[A-Ia-i]?[A-Za-z](\d+)\.\.[A-Ia-i]?[A-Za-z](\d+)$/",$tree['value']) and
1387 !is_numeric($tree['value']) and
1388 !isset($this->ptg[$tree['value']]))\r {
1389 // left subtree for a function is always an array.
1390 if ($tree['left'] != '') {
1391 $left_tree = $this->toReversePolish($tree['left']);
1392 }\r else {
1393 $left_tree = '';
1394 }
1395 if ($this->isError($left_tree)) {
1396 return $left_tree;
1397 }
1398 // add it's left subtree and return.
1399 return $left_tree.$this->_convertFunction($tree['value'], $tree['right']);
1401 $converted_tree = $this->_convert($tree['value']);
1402 if ($this->isError($converted_tree)) {
1403 return $converted_tree;
1404 }
1405 }
1406 $polish .= $converted_tree;
1407 return $polish;
1408 }
1410 }
1413 ?>