"Fossies" - the Fresh Open Source Software Archive 
Member "php_writeexcel-0.3.0/class.writeexcel_formula.inc.php" (1 Nov 2005, 56012 Bytes) of package /linux/www/old/php_writeexcel-0.3.0.tar.gz:
As a special service "Fossies" has tried to format the requested source page into HTML format using (guessed) PHP source code syntax highlighting (style:
standard) with prefixed line numbers and
code folding option.
Alternatively you can here
view or
download the uninterpreted source code file.
1 <?php
2
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 */
21
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
25
26 Imported and adapted by Andreas Brodowski 2003 (andreas.brodowski@oscar-gmbh.com).
27
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.
30
31 Changes: 03/08/27 Added SPREADSHEET_EXCEL_WRITER_SCOLON for arg seperation in excel functions
32 */
33
34 /*
35 * This is the Spreadsheet::WriteExcel Perl package ported to PHP
36 * Spreadsheet::WriteExcel was written by John McNamara, jmcnamara@cpan.org
37 */
38
39 define('SPREADSHEET_EXCEL_WRITER_ADD',"+");
40 // @const SPREADSHEET_EXCEL_WRITER_ADD token identifier for character "+"
41 define('SPREADSHEET_EXCEL_WRITER_SUB',"-");
42 // @const SPREADSHEET_EXCEL_WRITER_SUB token identifier for character "-"
43 define('SPREADSHEET_EXCEL_WRITER_MUL',"*");
44 // @const SPREADSHEET_EXCEL_WRITER_MUL token identifier for character "*"
45 define('SPREADSHEET_EXCEL_WRITER_DIV',"/");
46 // @const SPREADSHEET_EXCEL_WRITER_DIV token identifier for character "/"
47 define('SPREADSHEET_EXCEL_WRITER_OPEN',"(");
48 // @const SPREADSHEET_EXCEL_WRITER_OPEN token identifier for character "("
49 define('SPREADSHEET_EXCEL_WRITER_CLOSE',")");
50 // @const SPREADSHEET_EXCEL_WRITER_CLOSE token identifier for character ")"
51 define('SPREADSHEET_EXCEL_WRITER_COMA',",");
52 // @const SPREADSHEET_EXCEL_WRITER_COMA token identifier for character ","
53 define('SPREADSHEET_EXCEL_WRITER_SCOLON',";");
54 // @const SPREADSHEET_EXCEL_WRITER_SCOLON token identifier for character ";"
55 define('SPREADSHEET_EXCEL_WRITER_GT',">");
56 // @const SPREADSHEET_EXCEL_WRITER_GT token identifier for character ">"
57 define('SPREADSHEET_EXCEL_WRITER_LT',"<");
58 // @const SPREADSHEET_EXCEL_WRITER_LT token identifier for character "<"
59 define('SPREADSHEET_EXCEL_WRITER_LE',"<=");
60 // @const SPREADSHEET_EXCEL_WRITER_LE token identifier for character "<="
61 define('SPREADSHEET_EXCEL_WRITER_GE',">=");
62 // @const SPREADSHEET_EXCEL_WRITER_GE token identifier for character ">="
63 define('SPREADSHEET_EXCEL_WRITER_EQ',"=");
64 // @const SPREADSHEET_EXCEL_WRITER_EQ token identifier for character "="
65 define('SPREADSHEET_EXCEL_WRITER_NE',"<>");
66 // @const SPREADSHEET_EXCEL_WRITER_NE token identifier for character "<>"
67
68
69 class writeexcel_formula {
70
71 ###############################################################################
72 #
73 # Class data.
74 #
75 var $parser;
76 var $ptg;
77 var $_functions;
78 var $_current_char;
79 var $_current_token;
80 var $_lookahead;
81 var $_debug;
82 var $_byte_order;
83 var $_volatile;
84 var $_workbook;
85 var $_ext_sheets;
86 var $_formula;
87
88 ###############################################################################
89 #
90 # new()
91 #
92 # Constructor
93 #
94 function writeexcel_formula($byte_order) {
95
96 $this->parser = false;
97 $this->ptg = array();
98 $this->_functions = array();
99 $this->_debug = 0;
100 $this->_byte_order = $byte_order;
101 $this->_volatile = 0;
102 $this->_workbook = "";
103 $this->_ext_sheets = array();
104 $this->_current_token = '';
105 $this->_lookahead = '';
106 $this->_current_char = 0;
107 $this->_formula = '';
108 }
109
110 ###############################################################################
111 #
112 # _init_parser()
113 #
114 # There is a small overhead involved in generating the parser. Therefore, the
115 # initialisation is delayed until a formula is required. TODO: use a pre-
116 # compiled header.
117 #
118 function _init_parser() {
119
120 $this->_initializeHashes();
121
122
123 if ($this->_debug) {
124 print "Init_parser.\n\n";
125 }
126 }
127
128 ###############################################################################
129 #
130 # parse_formula()
131 #
132 # This is the only public method. It takes a textual description of a formula
133 # and returns a RPN encoded byte string.
134 #
135 function parse_formula() {
136
137 $_=func_get_args();
138
139 # Initialise the parser if this is the first call
140 if ($this->parser===false) {
141 $this->_init_parser();
142 }
143
144 $formula = array_shift($_);
145 //$str;
146 //$tokens;
147
148 if ($this->_debug) {
149 print "$formula\n";
150 }
151
152 # Build the parse tree for the formula
153
154 $this->_formula = $formula;
155 $this->_current_char = 0;
156 $this->_lookahead = $this->_formula{1};
157 $this->_advance($formula);
158 $parsetree = $this->_condition();
159
160 $str = $this->toReversePolish($parsetree);
161
162 return $str;
163 }
164
165 function isError($data) {
166 return (bool)(is_object($data) &&
167 (get_class($data) == 'pear_error' ||
168 is_subclass_of($data, 'pear_error')));
169 }
170
171 /**
172 * Class for parsing Excel formulas
173 *
174 * @author Xavier Noguer <xnoguer@rezebra.com>
175 * @category FileFormats
176 * @package Spreadsheet_Excel_Writer
177 */
178
179
180 /**
181 * Initialize the ptg and function hashes.
182 *
183 * @access private
184 */
185 function _initializeHashes()
186 {
187 // The Excel ptg indices
188 $this->ptg = array(
189 'ptgExp' => 0x01,
190 'ptgTbl' => 0x02,
191 'ptgAdd' => 0x03,
192 'ptgSub' => 0x04,
193 'ptgMul' => 0x05,
194 'ptgDiv' => 0x06,
195 'ptgPower' => 0x07, 'ptgConcat' => 0x08,
196 'ptgLT' => 0x09,
197 'ptgLE' => 0x0A,
198 'ptgEQ' => 0x0B,
199 'ptgGE' => 0x0C,
200 'ptgGT' => 0x0D,
201 'ptgNE' => 0x0E,
202 'ptgIsect' => 0x0F,
203 'ptgUnion' => 0x10,
204 'ptgRange' => 0x11,
205 'ptgUplus' => 0x12,
206 'ptgUminus' => 0x13,
207 'ptgPercent' => 0x14,
208 'ptgParen' => 0x15,
209 'ptgMissArg' => 0x16,
210 'ptgStr' => 0x17,
211 'ptgAttr' => 0x19,
212 'ptgSheet' => 0x1A,
213 'ptgEndSheet' => 0x1B,
214 'ptgErr' => 0x1C,
215 'ptgBool' => 0x1D,
216 'ptgInt' => 0x1E,
217 'ptgNum' => 0x1F,
218 'ptgArray' => 0x20,
219 'ptgFunc' => 0x21,
220 'ptgFuncVar' => 0x22,
221 'ptgName' => 0x23,
222 'ptgRef' => 0x24,
223 'ptgArea' => 0x25,
224 'ptgMemArea' => 0x26,
225 'ptgMemErr' => 0x27,
226 'ptgMemNoMem' => 0x28,
227 'ptgMemFunc' => 0x29,
228 'ptgRefErr' => 0x2A,
229 'ptgAreaErr' => 0x2B,
230 'ptgRefN' => 0x2C,
231 'ptgAreaN' => 0x2D,
232 'ptgMemAreaN' => 0x2E,
233 'ptgMemNoMemN' => 0x2F,
234 'ptgNameX' => 0x39,
235 'ptgRef3d' => 0x3A,
236
237 'ptgArea3d' => 0x3B,
238 'ptgRefErr3d' => 0x3C,
239 'ptgAreaErr3d' => 0x3D,
240 'ptgArrayV' => 0x40,
241 'ptgFuncV' => 0x41,
242 'ptgFuncVarV' => 0x42,
243 'ptgNameV' => 0x43,
244 'ptgRefV' => 0x44,
245 'ptgAreaV' => 0x45,
246 'ptgMemAreaV' => 0x46,
247 'ptgMemErrV' => 0x47,
248 'ptgMemNoMemV' => 0x48,
249 'ptgMemFuncV' => 0x49,
250 'ptgRefErrV' => 0x4A,
251 'ptgAreaErrV' => 0x4B,
252 'ptgRefNV' => 0x4C,
253 'ptgAreaNV' => 0x4D,
254 'ptgMemAreaNV' => 0x4E,
255 'ptgMemNoMemN' => 0x4F,
256 'ptgFuncCEV' => 0x58,
257 'ptgNameXV' => 0x59,
258 'ptgRef3dV' => 0x5A,
259 'ptgArea3dV' => 0x5B, 'ptgRefErr3dV' => 0x5C,
260 'ptgAreaErr3d' => 0x5D,
261 'ptgArrayA' => 0x60,
262 'ptgFuncA' => 0x61,
263 'ptgFuncVarA' => 0x62,
264 'ptgNameA' => 0x63, 'ptgRefA' => 0x64,
265 'ptgAreaA' => 0x65,
266 'ptgMemAreaA' => 0x66,
267 'ptgMemErrA' => 0x67,
268 'ptgMemNoMemA' => 0x68,
269 'ptgMemFuncA' => 0x69,
270 'ptgRefErrA' => 0x6A,
271 'ptgAreaErrA' => 0x6B,
272 'ptgRefNA' => 0x6C,
273 'ptgAreaNA' => 0x6D,
274 'ptgMemAreaNA' => 0x6E,
275 'ptgMemNoMemN' => 0x6F,
276 'ptgFuncCEA' => 0x78,
277 'ptgNameXA' => 0x79,
278 'ptgRef3dA' => 0x7A,
279 'ptgArea3dA' => 0x7B,
280 'ptgRefErr3dA' => 0x7C,
281 'ptgAreaErr3d' => 0x7D
282 );
283
284 // Thanks to Michael Meeks and Gnumeric for the initial arg values.
285 //
286 // The following hash was generated by "function_locale.pl" in the distro.
287 // Refer to function_locale.pl for non-English function names.
288 //
289 // The array elements are as follow:
290 // ptg: The Excel function ptg code.
291 // args: The number of arguments that the function takes:
292 // >=0 is a fixed number of arguments.
293 // -1 is a variable number of arguments.
294 // class: The reference, value or array class of the function args.
295 // vol: The function is volatile.
296 //
297 $this->_functions = array(
298 // function ptg args class vol
299 'COUNT' => array( 0, -1, 0, 0 ),
300 'IF' => array( 1, -1, 1, 0 ),
301 'ISNA' => array( 2, 1, 1, 0 ),
302 'ISERROR' => array( 3, 1, 1, 0 ),
303 'SUM' => array( 4, -1, 0, 0 ),
304 'AVERAGE' => array( 5, -1, 0, 0 ),
305 'MIN' => array( 6, -1, 0, 0 ),
306 'MAX' => array( 7, -1, 0, 0 ),
307 'ROW' => array( 8, -1, 0, 0 ),
308 'COLUMN' => array( 9, -1, 0, 0 ),
309 'NA' => array( 10, 0, 0, 0 ),
310 'NPV' => array( 11, -1, 1, 0 ),
311 'STDEV' => array( 12, -1, 0, 0 ),
312 'DOLLAR' => array( 13, -1, 1, 0 ),
313 'FIXED' => array( 14, -1, 1, 0 ),
314 'SIN' => array( 15, 1, 1, 0 ),
315 'COS' => array( 16, 1, 1, 0 ),
316 'TAN' => array( 17, 1, 1, 0 ),
317 'ATAN' => array( 18, 1, 1, 0 ),
318 'PI' => array( 19, 0, 1, 0 ),
319 'SQRT' => array( 20, 1, 1, 0 ),
320 'EXP' => array( 21, 1, 1, 0 ),
321 'LN' => array( 22, 1, 1, 0 ),
322 'LOG10' => array( 23, 1, 1, 0 ),
323 'ABS' => array( 24, 1, 1, 0 ),
324 'INT' => array( 25, 1, 1, 0 ),
325 'SIGN' => array( 26, 1, 1, 0 ),
326 'ROUND' => array( 27, 2, 1, 0 ),
327 'LOOKUP' => array( 28, -1, 0, 0 ),
328 'INDEX' => array( 29, -1, 0, 1 ),
329 'REPT' => array( 30, 2, 1, 0 ),
330 'MID' => array( 31, 3, 1, 0 ),
331 'LEN' => array( 32, 1, 1, 0 ),
332 'VALUE' => array( 33, 1, 1, 0 ),
333 'TRUE' => array( 34, 0, 1, 0 ),
334 'FALSE' => array( 35, 0, 1, 0 ),
335 'AND' => array( 36, -1, 0, 0 ),
336 'OR' => array( 37, -1, 0, 0 ),
337 'NOT' => array( 38, 1, 1, 0 ),
338 'MOD' => array( 39, 2, 1, 0 ),
339 'DCOUNT' => array( 40, 3, 0, 0 ),
340 'DSUM' => array( 41, 3, 0, 0 ),
341 'DAVERAGE' => array( 42, 3, 0, 0 ),
342 'DMIN' => array( 43, 3, 0, 0 ),
343 'DMAX' => array( 44, 3, 0, 0 ),
344 'DSTDEV' => array( 45, 3, 0, 0 ),
345 'VAR' => array( 46, -1, 0, 0 ),
346 'DVAR' => array( 47, 3, 0, 0 ),
347 'TEXT' => array( 48, 2, 1, 0 ),
348 'LINEST' => array( 49, -1, 0, 0 ),
349 'TREND' => array( 50, -1, 0, 0 ),
350 'LOGEST' => array( 51, -1, 0, 0 ),
351 'GROWTH' => array( 52, -1, 0, 0 ),
352 'PV' => array( 56, -1, 1, 0 ),
353 'FV' => array( 57, -1, 1, 0 ),
354 'NPER' => array( 58, -1, 1, 0 ),
355 'PMT' => array( 59, -1, 1, 0 ),
356 'RATE' => array( 60, -1, 1, 0 ),
357 'MIRR' => array( 61, 3, 0, 0 ),
358 'IRR' => array( 62, -1, 0, 0 ),
359 'RAND' => array( 63, 0, 1, 1 ),
360 'MATCH' => array( 64, -1, 0, 0 ),
361 'DATE' => array( 65, 3, 1, 0 ),
362 'TIME' => array( 66, 3, 1, 0 ),
363 'DAY' => array( 67, 1, 1, 0 ),
364 'MONTH' => array( 68, 1, 1, 0 ),
365 'YEAR' => array( 69, 1, 1, 0 ),
366 'WEEKDAY' => array( 70, -1, 1, 0 ),
367 'HOUR' => array( 71, 1, 1, 0 ),
368 'MINUTE' => array( 72, 1, 1, 0 ),
369 'SECOND' => array( 73, 1, 1, 0 ),
370 'NOW' => array( 74, 0, 1, 1 ),
371 'AREAS' => array( 75, 1, 0, 1 ),
372 'ROWS' => array( 76, 1, 0, 1 ),
373 'COLUMNS' => array( 77, 1, 0, 1 ),
374 'OFFSET' => array( 78, -1, 0, 1 ),
375 'SEARCH' => array( 82, -1, 1, 0 ),
376 'TRANSPOSE' => array( 83, 1, 1, 0 ),
377 'TYPE' => array( 86, 1, 1, 0 ),
378 'ATAN2' => array( 97, 2, 1, 0 ),
379 'ASIN' => array( 98, 1, 1, 0 ),
380 'ACOS' => array( 99, 1, 1, 0 ),
381 'CHOOSE' => array( 100, -1, 1, 0 ),
382 'HLOOKUP' => array( 101, -1, 0, 0 ),
383 'VLOOKUP' => array( 102, -1, 0, 0 ),
384 'ISREF' => array( 105, 1, 0, 0 ),
385 'LOG' => array( 109, -1, 1, 0 ),
386 'CHAR' => array( 111, 1, 1, 0 ),
387 'LOWER' => array( 112, 1, 1, 0 ),
388 'UPPER' => array( 113, 1, 1, 0 ),
389 'PROPER' => array( 114, 1, 1, 0 ),
390 'LEFT' => array( 115, -1, 1, 0 ),
391 'RIGHT' => array( 116, -1, 1, 0 ),
392 'EXACT' => array( 117, 2, 1, 0 ),
393 'TRIM' => array( 118, 1, 1, 0 ),
394 'REPLACE' => array( 119, 4, 1, 0 ),
395 'SUBSTITUTE' => array( 120, -1, 1, 0 ),
396 'CODE' => array( 121, 1, 1, 0 ),
397 'FIND' => array( 124, -1, 1, 0 ),
398 'CELL' => array( 125, -1, 0, 1 ),
399 'ISERR' => array( 126, 1, 1, 0 ),
400 'ISTEXT' => array( 127, 1, 1, 0 ),
401 'ISNUMBER' => array( 128, 1, 1, 0 ),
402 'ISBLANK' => array( 129, 1, 1, 0 ),
403 'T' => array( 130, 1, 0, 0 ),
404 'N' => array( 131, 1, 0, 0 ),
405 'DATEVALUE' => array( 140, 1, 1, 0 ),
406 'TIMEVALUE' => array( 141, 1, 1, 0 ),
407 'SLN' => array( 142, 3, 1, 0 ),
408 'SYD' => array( 143, 4, 1, 0 ),
409 'DDB' => array( 144, -1, 1, 0 ),
410 'INDIRECT' => array( 148, -1, 1, 1 ),
411 'CALL' => array( 150, -1, 1, 0 ),
412 'CLEAN' => array( 162, 1, 1, 0 ),
413 'MDETERM' => array( 163, 1, 2, 0 ),
414 'MINVERSE' => array( 164, 1, 2, 0 ),
415 'MMULT' => array( 165, 2, 2, 0 ),
416 'IPMT' => array( 167, -1, 1, 0 ),
417 'PPMT' => array( 168, -1, 1, 0 ),
418 'COUNTA' => array( 169, -1, 0, 0 ),
419 'PRODUCT' => array( 183, -1, 0, 0 ),
420 'FACT' => array( 184, 1, 1, 0 ),
421 'DPRODUCT' => array( 189, 3, 0, 0 ),
422 'ISNONTEXT' => array( 190, 1, 1, 0 ),
423 'STDEVP' => array( 193, -1, 0, 0 ),
424 'VARP' => array( 194, -1, 0, 0 ),
425 'DSTDEVP' => array( 195, 3, 0, 0 ),
426 'DVARP' => array( 196, 3, 0, 0 ),
427 'TRUNC' => array( 197, -1, 1, 0 ),
428 'ISLOGICAL' => array( 198, 1, 1, 0 ),
429 'DCOUNTA' => array( 199, 3, 0, 0 ),
430 'ROUNDUP' => array( 212, 2, 1, 0 ),
431 'ROUNDDOWN' => array( 213, 2, 1, 0 ),
432 'RANK' => array( 216, -1, 0, 0 ),
433 'ADDRESS' => array( 219, -1, 1, 0 ),
434 'DAYS360' => array( 220, -1, 1, 0 ),
435 'TODAY' => array( 221, 0, 1, 1 ),
436 'VDB' => array( 222, -1, 1, 0 ),
437 'MEDIAN' => array( 227, -1, 0, 0 ),
438 'SUMPRODUCT' => array( 228, -1, 2, 0 ),
439 'SINH' => array( 229, 1, 1, 0 ),
440 'COSH' => array( 230, 1, 1, 0 ),
441 'TANH' => array( 231, 1, 1, 0 ),
442 'ASINH' => array( 232, 1, 1, 0 ),
443 'ACOSH' => array( 233, 1, 1, 0 ),
444 'ATANH' => array( 234, 1, 1, 0 ),
445 'DGET' => array( 235, 3, 0, 0 ),
446 'INFO' => array( 244, 1, 1, 1 ),
447 'DB' => array( 247, -1, 1, 0 ),
448 'FREQUENCY' => array( 252, 2, 0, 0 ),
449 'ERROR.TYPE' => array( 261, 1, 1, 0 ),
450 'REGISTER.ID' => array( 267, -1, 1, 0 ),
451 'AVEDEV' => array( 269, -1, 0, 0 ),
452 'BETADIST' => array( 270, -1, 1, 0 ),
453 'GAMMALN' => array( 271, 1, 1, 0 ),
454 'BETAINV' => array( 272, -1, 1, 0 ),
455 'BINOMDIST' => array( 273, 4, 1, 0 ),
456 'CHIDIST' => array( 274, 2, 1, 0 ),
457 'CHIINV' => array( 275, 2, 1, 0 ),
458 'COMBIN' => array( 276, 2, 1, 0 ),
459 'CONFIDENCE' => array( 277, 3, 1, 0 ),
460 'CRITBINOM' => array( 278, 3, 1, 0 ),
461 'EVEN' => array( 279, 1, 1, 0 ),
462 'EXPONDIST' => array( 280, 3, 1, 0 ),
463 'FDIST' => array( 281, 3, 1, 0 ),
464 'FINV' => array( 282, 3, 1, 0 ),
465 'FISHER' => array( 283, 1, 1, 0 ),
466 'FISHERINV' => array( 284, 1, 1, 0 ),
467 'FLOOR' => array( 285, 2, 1, 0 ),
468 'GAMMADIST' => array( 286, 4, 1, 0 ),
469 'GAMMAINV' => array( 287, 3, 1, 0 ),
470 'CEILING' => array( 288, 2, 1, 0 ),
471 'HYPGEOMDIST' => array( 289, 4, 1, 0 ),
472 'LOGNORMDIST' => array( 290, 3, 1, 0 ),
473 'LOGINV' => array( 291, 3, 1, 0 ),
474 'NEGBINOMDIST' => array( 292, 3, 1, 0 ),
475 'NORMDIST' => array( 293, 4, 1, 0 ),
476 'NORMSDIST' => array( 294, 1, 1, 0 ),
477 'NORMINV' => array( 295, 3, 1, 0 ),
478 'NORMSINV' => array( 296, 1, 1, 0 ),
479 'STANDARDIZE' => array( 297, 3, 1, 0 ),
480 'ODD' => array( 298, 1, 1, 0 ),
481 'PERMUT' => array( 299, 2, 1, 0 ),
482 'POISSON' => array( 300, 3, 1, 0 ),
483 'TDIST' => array( 301, 3, 1, 0 ),
484 'WEIBULL' => array( 302, 4, 1, 0 ),
485 'SUMXMY2' => array( 303, 2, 2, 0 ),
486 'SUMX2MY2' => array( 304, 2, 2, 0 ),
487 'SUMX2PY2' => array( 305, 2, 2, 0 ),
488 'CHITEST' => array( 306, 2, 2, 0 ),
489 'CORREL' => array( 307, 2, 2, 0 ),
490 'COVAR' => array( 308, 2, 2, 0 ),
491 'FORECAST' => array( 309, 3, 2, 0 ),
492 'FTEST' => array( 310, 2, 2, 0 ),
493 'INTERCEPT' => array( 311, 2, 2, 0 ),
494 'PEARSON' => array( 312, 2, 2, 0 ),
495 'RSQ' => array( 313, 2, 2, 0 ),
496 'STEYX' => array( 314, 2, 2, 0 ),
497 'SLOPE' => array( 315, 2, 2, 0 ),
498 'TTEST' => array( 316, 4, 2, 0 ),
499 'PROB' => array( 317, -1, 2, 0 ),
500 'DEVSQ' => array( 318, -1, 0, 0 ),
501 'GEOMEAN' => array( 319, -1, 0, 0 ),
502 'HARMEAN' => array( 320, -1, 0, 0 ),
503 'SUMSQ' => array( 321, -1, 0, 0 ),
504 'KURT' => array( 322, -1, 0, 0 ),
505 'SKEW' => array( 323, -1, 0, 0 ),
506 'ZTEST' => array( 324, -1, 0, 0 ),
507 'LARGE' => array( 325, 2, 0, 0 ),
508 'SMALL' => array( 326, 2, 0, 0 ),
509 'QUARTILE' => array( 327, 2, 0, 0 ),
510 'PERCENTILE' => array( 328, 2, 0, 0 ),
511 'PERCENTRANK' => array( 329, -1, 0, 0 ),
512 'MODE' => array( 330, -1, 2, 0 ),
513 'TRIMMEAN' => array( 331, 2, 0, 0 ),
514 'TINV' => array( 332, 2, 1, 0 ),
515 'CONCATENATE' => array( 336, -1, 1, 0 ),
516 'POWER' => array( 337, 2, 1, 0 ),
517 'RADIANS' => array( 342, 1, 1, 0 ),
518 'DEGREES' => array( 343, 1, 1, 0 ),
519 'SUBTOTAL' => array( 344, -1, 0, 0 ),
520 'SUMIF' => array( 345, -1, 0, 0 ),
521 'COUNTIF' => array( 346, 2, 0, 0 ),
522 'COUNTBLANK' => array( 347, 1, 0, 0 ),
523 'ROMAN' => array( 354, -1, 1, 0 )
524 );
525 }
526
527 /**
528 * Convert a token to the proper ptg value.
529 *
530 * @access private
531 * @param mixed $token The token to convert.
532 * @return mixed the converted token on success. PEAR_Error if the token
533 * is not recognized
534 */
535 function _convert($token)
536 {
537 if (preg_match('/^"[^"]{0,255}"$/', $token))
538 {
539 return $this->_convertString($token);
540 }
541 elseif (is_numeric($token))
542 {
543 return $this->_convertNumber($token);
544 }
545 // match references like A1 or $A$1
546
547 elseif (preg_match('/^\$?([A-Ia-i]?[A-Za-z])\$?(\d+)$/',$token))
548 {
549 return $this->_convertRef2d($token);
550 }
551 // match external references like Sheet1:Sheet2!A1
552 elseif (preg_match("/^[A-Za-z0-9_]+(\:[A-Za-z0-9_]+)?\![A-Ia-i]?[A-Za-z](\d+)$/",$token))
553 {
554
555 return $this->_convertRef3d($token);
556 }
557 // match ranges like A1:B2
558 elseif (preg_match('/^\$?[A-Ia-i]?[A-Za-z]\$?\d+\:\$?[A-Ia-i]?[A-Za-z]\$?\d+$/',$token))
559 {
560 return $this->_convertRange2d($token);
561 }
562 // match ranges like A1..B2
563 elseif (preg_match('/^\$?[A-Ia-i]?[A-Za-z]\$?\d+\.\.\$?[A-Ia-i]?[A-Za-z]\$?\d+$/',$token))
564 {
565 return $this->_convertRange2d($token);
566 }
567 // match external ranges like Sheet1:Sheet2!A1:B2
568 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))
569 {
570 return $this->_convertRange3d($token);
571 }
572 // match external ranges like 'Sheet1:Sheet2'!A1:B2
573 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))
574 {
575 return $this->_convertRange3d($token);
576 }
577 elseif (isset($this->ptg[$token])) // operators (including parentheses)
578 {
579 return pack("C", $this->ptg[$token]);
580 }
581 // commented so argument number can be processed correctly. See toReversePolish().
582 /*elseif (preg_match("/[A-Z0-9\xc0-\xdc\.]+/",$token))
583 {
584 return($this->_convertFunction($token,$this->_func_args));
585 }*/
586 // if it's an argument, ignore the token (the argument remains)
587 elseif ($token == 'arg')
588 {
589 return '';
590 }
591 // TODO: use real error codes
592 trigger_error("Unknown token $token", E_USER_ERROR);
593 }
594
595 /**
596 * Convert a number token to ptgInt or ptgNum
597 *
598 * @access private
599 * @param mixed $num an integer or double for conversion to its ptg value
600 */
601 function _convertNumber($num)
602 {
603
604 // Integer in the range 0..2**16-1
605
606 if ((preg_match("/^\d+$/",$num)) and ($num <= 65535)) {
607 return(pack("Cv", $this->ptg['ptgInt'], $num));
608 }
609 else { // A float
610 if ($this->_byte_order) { // if it's Big Endian
611 $num = strrev($num);
612 }
613 return pack("Cd", $this->ptg['ptgNum'], $num);
614 }
615 }
616
617 /**
618 * Convert a string token to ptgStr
619 *
620 * @access private
621 * @param string $string A string for conversion to its ptg value
622 */
623 function _convertString($string)
624 {
625 // chop away beggining and ending quotes
626 $string = substr($string, 1, strlen($string) - 2);
627 return pack("CC", $this->ptg['ptgStr'], strlen($string)).$string;
628 }
629
630 /**
631 * Convert a function to a ptgFunc or ptgFuncVarV depending on the number of
632 * args that it takes.
633 *
634 * @access private
635 * @param string $token The name of the function for convertion to ptg value.
636 * @param integer $num_args The number of arguments the function receives.
637 * @return string The packed ptg for the function
638 */
639 function _convertFunction($token, $num_args)
640 {
641 $args = $this->_functions[$token][1];
642 $volatile = $this->_functions[$token][3];
643
644 // Fixed number of args eg. TIME($i,$j,$k).
645 if ($args >= 0) {
646 return pack("Cv", $this->ptg['ptgFuncV'], $this->_functions[$token][0]);
647 }
648 // Variable number of args eg. SUM($i,$j,$k, ..).
649 if ($args == -1) {
650 return pack("CCv", $this->ptg['ptgFuncVarV'], $num_args, $this->_functions[$token][0]);
651 }
652 }
653
654 /**
655 * Convert an Excel range such as A1:D4 to a ptgRefV.
656 *
657 * @access private
658 * @param string $range An Excel range in the A1:A2 or A1..A2 format.
659 */
660 function _convertRange2d($range)
661 {
662 $class = 2; // as far as I know, this is magick.
663
664 // Split the range into 2 cell refs
665 if (preg_match('/^\$?([A-Ia-i]?[A-Za-z])\$?(\d+)\:\$?([A-Ia-i]?[A-Za-z])\$?(\d+)$/',$range)) {
666 list($cell1, $cell2) = split(':', $range);
667 }
668 elseif (preg_match('/^\$?([A-Ia-i]?[A-Za-z])\$?(\d+)\.\.\$?([A-Ia-i]?[A-Za-z])\$?(\d+)$/',$range)) {
669 list($cell1, $cell2) = split('\.\.', $range);
670 }
671 else {
672 // TODO: use real error codes
673 trigger_error("Unknown range separator", E_USER_ERROR);
674 }
675
676 // Convert the cell references
677 $cell_array1 = $this->_cellToPackedRowcol($cell1);
678 if ($this->isError($cell_array1)) {
679 return $cell_array1;
680 }
681 list($row1, $col1) = $cell_array1;
682 $cell_array2 = $this->_cellToPackedRowcol($cell2);
683 if ($this->isError($cell_array2)) {
684 return $cell_array2;
685 }
686 list($row2, $col2) = $cell_array2;
687
688 // The ptg value depends on the class of the ptg.
689 if ($class == 0) {
690 $ptgArea = pack("C", $this->ptg['ptgArea']);
691 }
692 elseif ($class == 1) {
693 $ptgArea = pack("C", $this->ptg['ptgAreaV']);
694 }
695 elseif ($class == 2) {
696 $ptgArea = pack("C", $this->ptg['ptgAreaA']);
697 }
698 else {
699 // TODO: use real error codes
700 trigger_error("Unknown class $class", E_USER_ERROR);
701 }
702 return $ptgArea . $row1 . $row2 . $col1. $col2;
703 }
704
705 /**
706 * Convert an Excel 3d range such as "Sheet1!A1:D4" or "Sheet1:Sheet2!A1:D4" to
707 * a ptgArea3dV.
708 *
709 * @access private
710 * @param string $token An Excel range in the Sheet1!A1:A2 format.
711 */
712 function _convertRange3d($token)
713 {
714 $class = 2; // as far as I know, this is magick.
715
716 // Split the ref at the ! symbol
717 list($ext_ref, $range) = split('!', $token);
718
719 // Convert the external reference part
720 $ext_ref = $this->_packExtRef($ext_ref);
721 if ($this->isError($ext_ref)) {
722 return $ext_ref;
723 }
724
725 // Split the range into 2 cell refs
726 list($cell1, $cell2) = split(':', $range);
727
728 // Convert the cell references
729 if (preg_match('/^(\$)?[A-Ia-i]?[A-Za-z](\$)?(\d+)$/', $cell1))
730 {
731 $cell_array1 = $this->_cellToPackedRowcol($cell1);
732 if ($this->isError($cell_array1)) {
733 return $cell_array1;
734 }
735 list($row1, $col1) = $cell_array1;
736 $cell_array2 = $this->_cellToPackedRowcol($cell2);
737 if ($this->isError($cell_array2)) {
738 return $cell_array2;
739 }
740 list($row2, $col2) = $cell_array2;
741 }
742 else { // It's a columns range (like 26:27)
743 $cells_array = $this->_rangeToPackedRange($cell1.':'.$cell2);
744 if ($this->isError($cells_array)) {
745 return $cells_array;
746 }
747 list($row1, $col1, $row2, $col2) = $cells_array;
748 }
749
750 // The ptg value depends on the class of the ptg.
751 if ($class == 0) {
752 $ptgArea = pack("C", $this->ptg['ptgArea3d']);
753 }
754 elseif ($class == 1) {
755 $ptgArea = pack("C", $this->ptg['ptgArea3dV']);
756 }
757 elseif ($class == 2) {
758 $ptgArea = pack("C", $this->ptg['ptgArea3dA']);
759 }
760 else {
761 trigger_error("Unknown class $class", E_USER_ERROR);
762 }
763
764 return $ptgArea . $ext_ref . $row1 . $row2 . $col1. $col2;
765 }
766
767 /**
768 * Convert an Excel reference such as A1, $B2, C$3 or $D$4 to a ptgRefV.
769 *
770 * @access private
771 * @param string $cell An Excel cell reference
772 * @return string The cell in packed() format with the corresponding ptg
773 */
774 function _convertRef2d($cell)
775 {
776 $class = 2; // as far as I know, this is magick.
777
778 // Convert the cell reference
779 $cell_array = $this->_cellToPackedRowcol($cell);
780 if ($this->isError($cell_array)) {
781 return $cell_array;
782 }
783 list($row, $col) = $cell_array;
784
785 // The ptg value depends on the class of the ptg.
786 if ($class == 0) {
787 $ptgRef = pack("C", $this->ptg['ptgRef']);
788 }
789 elseif ($class == 1) {
790 $ptgRef = pack("C", $this->ptg['ptgRefV']);
791 }
792 elseif ($class == 2) {
793 $ptgRef = pack("C", $this->ptg['ptgRefA']);
794 }
795 else {
796 // TODO: use real error codes
797 trigger_error("Unknown class $class",E_USER_ERROR);
798 }
799 return $ptgRef.$row.$col;
800 }
801
802 /**
803 * Convert an Excel 3d reference such as "Sheet1!A1" or "Sheet1:Sheet2!A1" to a
804 * ptgRef3dV.
805 *
806 * @access private
807 * @param string $cell An Excel cell reference
808 * @return string The cell in packed() format with the corresponding ptg
809 */
810 function _convertRef3d($cell)
811 {
812 $class = 2; // as far as I know, this is magick.
813
814 // Split the ref at the ! symbol
815 list($ext_ref, $cell) = split('!', $cell);
816
817 // Convert the external reference part
818 $ext_ref = $this->_packExtRef($ext_ref);
819 if ($this->isError($ext_ref)) {
820 return $ext_ref;
821 }
822
823 // Convert the cell reference part
824 list($row, $col) = $this->_cellToPackedRowcol($cell);
825
826 // The ptg value depends on the class of the ptg.
827 if ($class == 0) {
828 $ptgRef = pack("C", $this->ptg['ptgRef3d']);
829 } elseif ($class == 1) {
830 $ptgRef = pack("C", $this->ptg['ptgRef3dV']);
831 } elseif ($class == 2) {
832 $ptgRef = pack("C", $this->ptg['ptgRef3dA']);
833 }
834 else {
835 trigger_error("Unknown class $class", E_USER_ERROR);
836 }
837
838 return $ptgRef . $ext_ref. $row . $col;
839 }
840
841 /**
842 * Convert the sheet name part of an external reference, for example "Sheet1" or
843 * "Sheet1:Sheet2", to a packed structure.
844 *
845 * @access private
846 * @param string $ext_ref The name of the external reference
847 * @return string The reference index in packed() format
848 */
849 function _packExtRef($ext_ref) {
850 $ext_ref = preg_replace("/^'/", '', $ext_ref); // Remove leading ' if any.
851 $ext_ref = preg_replace("/'$/", '', $ext_ref); // Remove trailing ' if any.
852
853 // Check if there is a sheet range eg., Sheet1:Sheet2.
854 if (preg_match("/:/", $ext_ref))
855 {
856 list($sheet_name1, $sheet_name2) = split(':', $ext_ref);
857
858 $sheet1 = $this->_getSheetIndex($sheet_name1);
859 if ($sheet1 == -1) {
860 trigger_error("Unknown sheet name $sheet_name1 in formula",E_USER_ERROR);
861 }
862 $sheet2 = $this->_getSheetIndex($sheet_name2);
863 if ($sheet2 == -1) {
864 trigger_error("Unknown sheet name $sheet_name2 in formula",E_USER_ERROR);
865 }
866
867 // Reverse max and min sheet numbers if necessary
868 if ($sheet1 > $sheet2) {
869 list($sheet1, $sheet2) = array($sheet2, $sheet1);
870 }
871 }
872 else { // Single sheet name only.
873 $sheet1 = $this->_getSheetIndex($ext_ref);
874 if ($sheet1 == -1) {
875 trigger_error("Unknown sheet name $ext_ref in formula",E_USER_ERROR);
876 }
877 $sheet2 = $sheet1;
878 }
879
880 // References are stored relative to 0xFFFF.
881 $offset = -1 - $sheet1;
882
883 return pack('vdvv', $offset, 0x00, $sheet1, $sheet2);
884 }
885
886 /**
887 * Look up the index that corresponds to an external sheet name. The hash of
888 * sheet names is updated by the addworksheet() method of the
889 * Spreadsheet_Excel_Writer_Workbook class.
890 *
891 * @access private
892 * @return integer
893 */
894 function _getSheetIndex($sheet_name)
895 {
896 if (!isset($this->_ext_sheets[$sheet_name])) {
897 return -1;
898 }
899 else {
900 return $this->_ext_sheets[$sheet_name];
901 }
902 }
903
904 /**
905 * This method is used to update the array of sheet names. It is
906 * called by the addWorksheet() method of the Spreadsheet_Excel_Writer_Workbook class.
907 *
908 * @access private
909 * @param string $name The name of the worksheet being added
910 * @param integer $index The index of the worksheet being added
911 */
912 function set_ext_sheet($name, $index)
913 {
914 $this->_ext_sheets[$name] = $index;
915 }
916
917 /**
918 * pack() row and column into the required 3 byte format.
919 *
920 * @access private
921 * @param string $cell The Excel cell reference to be packed
922 * @return array Array containing the row and column in packed() format
923 */
924 function _cellToPackedRowcol($cell)
925 {
926 $cell = strtoupper($cell);
927 list($row, $col, $row_rel, $col_rel) = $this->_cellToRowcol($cell);
928 if ($col >= 256) {
929 trigger_error("Column in: $cell greater than 255", E_USER_ERROR);
930 }
931 if ($row >= 16384) {
932 trigger_error("Row in: $cell greater than 16384 ", E_USER_ERROR);
933 }
934
935 // Set the high bits to indicate if row or col are relative.
936 $row |= $col_rel << 14;
937 $row |= $row_rel << 15;
938
939 $row = pack('v', $row);
940 $col = pack('C', $col);
941
942 return array($row, $col);
943 }
944
945 /**
946 * pack() row range into the required 3 byte format.
947 * Just using maximun col/rows, which is probably not the correct solution
948 *
949 * @access private
950 * @param string $range The Excel range to be packed
951 * @return array Array containing (row1,col1,row2,col2) in packed() format
952 */
953 function _rangeToPackedRange($range)
954 {
955 preg_match('/(\$)?(\d+)\:(\$)?(\d+)/', $range, $match);
956 // return absolute rows if there is a $ in the ref
957 $row1_rel = empty($match[1]) ? 1 : 0;
958 $row1 = $match[2];
959 $row2_rel = empty($match[3]) ? 1 : 0;
960 $row2 = $match[4];
961 // Convert 1-index to zero-index
962 $row1--;
963 $row2--;
964 // Trick poor inocent Excel
965 $col1 = 0;
966 $col2 = 16383; // maximum possible value for Excel 5 (change this!!!)
967
968 //list($row, $col, $row_rel, $col_rel) = $this->_cellToRowcol($cell);
969 if (($row1 >= 16384) or ($row2 >= 16384)) {
970 trigger_error("Row in: $range greater than 16384 ",E_USER_ERROR);
971 }
972
973 // Set the high bits to indicate if rows are relative.
974 $row1 |= $row1_rel << 14;
975 $row2 |= $row2_rel << 15;
976
977 $row1 = pack('v', $row1);
978 $row2 = pack('v', $row2);
979 $col1 = pack('C', $col1);
980 $col2 = pack('C', $col2);
981
982 return array($row1, $col1, $row2, $col2);
983 }
984
985 /**
986 * Convert an Excel cell reference such as A1 or $B2 or C$3 or $D$4 to a zero
987 * indexed row and column number. Also returns two (0,1) values to indicate
988 * whether the row or column are relative references.
989 *
990 * @access private
991 * @param string $cell The Excel cell reference in A1 format.
992 * @return array
993 */
994 function _cellToRowcol($cell)
995 {
996 preg_match('/(\$)?([A-I]?[A-Z])(\$)?(\d+)/',$cell,$match);
997 // return absolute column if there is a $ in the ref
998 $col_rel = empty($match[1]) ? 1 : 0;
999 $col_ref = $match[2];
1000 $row_rel = empty($match[3]) ? 1 : 0;
1001 $row = $match[4];
1002
1003 // Convert base26 column string to a number.
1004 $expn = strlen($col_ref) - 1;
1005 $col = 0;
1006 for ($i=0; $i < strlen($col_ref); $i++)
1007 {
1008 $col += (ord($col_ref{$i}) - ord('A') + 1) * pow(26, $expn);
1009 $expn--;
1010 }
1011
1012 // Convert 1-index to zero-index
1013 $row--;
1014 $col--;
1015
1016 return array($row, $col, $row_rel, $col_rel);
1017 }
1018
1019 /**
1020 * Advance to the next valid token.
1021 *
1022 * @access private
1023 */
1024 function _advance()
1025 {
1026 $i = $this->_current_char;
1027 // eat up white spaces
1028 if ($i < strlen($this->_formula))
1029 {
1030 while ($this->_formula{$i} == " ") {
1031 $i++;
1032 }
1033 if ($i < strlen($this->_formula) - 1) {
1034 $this->_lookahead = $this->_formula{$i+1};
1035 }
1036 $token = "";
1037 }
1038 while ($i < strlen($this->_formula))
1039 {
1040 $token .= $this->_formula{$i};
1041 if ($i < strlen($this->_formula) - 1) {
1042 $this->_lookahead = $this->_formula{$i+1};
1043 }
1044 else {
1045 $this->_lookahead = '';
1046 }
1047 if ($this->_match($token) != '')
1048 {
1049 //if ($i < strlen($this->_formula) - 1) {
1050 // $this->_lookahead = $this->_formula{$i+1};
1051 //}
1052 $this->_current_char = $i + 1;
1053 $this->_current_token = $token;
1054 return 1;
1055 }
1056 if ($i < strlen($this->_formula) - 2) {
1057 $this->_lookahead = $this->_formula{$i+2};
1058 }
1059 else {
1060 // if we run out of characters _lookahead becomes empty
1061 $this->_lookahead = '';
1062 }
1063 $i++;
1064 }
1065 //die("Lexical error ".$this->_current_char);
1066 }
1067
1068 /**
1069 * Checks if it's a valid token.
1070 *
1071 * @access private
1072 * @param mixed $token The token to check.
1073 * @return mixed The checked token or false on failure
1074 */
1075 function _match($token)
1076 {
1077 switch($token)
1078 {
1079 case SPREADSHEET_EXCEL_WRITER_ADD:
1080 return($token);
1081 break;
1082 case SPREADSHEET_EXCEL_WRITER_SUB:
1083 return($token);
1084 break;
1085 case SPREADSHEET_EXCEL_WRITER_MUL:
1086 return($token);
1087 break;
1088 case SPREADSHEET_EXCEL_WRITER_DIV:
1089 return($token);
1090 break;
1091 case SPREADSHEET_EXCEL_WRITER_OPEN:
1092 return($token);
1093 break;
1094 case SPREADSHEET_EXCEL_WRITER_CLOSE:
1095 return($token);
1096 break;
1097 case SPREADSHEET_EXCEL_WRITER_SCOLON:
1098 return($token);
1099 break;
1100 case SPREADSHEET_EXCEL_WRITER_COMA:
1101 return($token);
1102 break;
1103 case SPREADSHEET_EXCEL_WRITER_GT:
1104 if ($this->_lookahead == '=') { // it's a GE token
1105 break;
1106 }
1107 return($token);
1108 break;
1109 case SPREADSHEET_EXCEL_WRITER_LT:
1110 // it's a LE or a NE token
1111 if (($this->_lookahead == '=') or ($this->_lookahead == '>')) {
1112 break;
1113 }
1114 return($token);
1115 break;
1116 case SPREADSHEET_EXCEL_WRITER_GE:
1117 return($token);
1118 break;
1119 case SPREADSHEET_EXCEL_WRITER_LE:
1120 return($token);
1121 break;
1122 case SPREADSHEET_EXCEL_WRITER_EQ:
1123 return($token);
1124 break;
1125 case SPREADSHEET_EXCEL_WRITER_NE:
1126 return($token);
1127 break;
1128 default:
1129 // if it's a reference
1130 if (preg_match('/^\$?[A-Ia-i]?[A-Za-z]\$?[0-9]+$/',$token) and
1131 !ereg("[0-9]",$this->_lookahead) and
1132 ($this->_lookahead != ':') and ($this->_lookahead != '.') and
1133 ($this->_lookahead != '!'))
1134 {
1135 return $token;
1136 }
1137 // If it's an external reference (Sheet1!A1 or Sheet1:Sheet2!A1)
1138 elseif (preg_match("/^[A-Za-z0-9_]+(\:[A-Za-z0-9_]+)?\![A-Ia-i]?[A-Za-z][0-9]+$/",$token) and
1139 !ereg("[0-9]",$this->_lookahead) and
1140 ($this->_lookahead != ':') and ($this->_lookahead != '.'))
1141 {
1142 return $token;
1143 }
1144 // if it's a range (A1:A2)
1145 elseif (preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+:(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+$/",$token) and
1146 !ereg("[0-9]",$this->_lookahead))
1147 {
1148 return $token;
1149 }
1150 // if it's a range (A1..A2)
1151 elseif (preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+\.\.(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+$/",$token) and
1152 !ereg("[0-9]",$this->_lookahead))
1153 {
1154 return $token;
1155 }
1156 // If it's an external range like Sheet1:Sheet2!A1:B2
1157 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
1158 !ereg("[0-9]",$this->_lookahead))
1159 {
1160 return $token;
1161 }
1162 // If it's an external range like 'Sheet1:Sheet2'!A1:B2
1163 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
1164 !ereg("[0-9]",$this->_lookahead))
1165 {
1166 return $token;
1167 }
1168 // If it's a number (check that it's not a sheet name or range)
1169 elseif (is_numeric($token) and
1170 (!is_numeric($token.$this->_lookahead) or ($this->_lookahead == '')) and
1171 ($this->_lookahead != '!') and ($this->_lookahead != ':'))
1172 {
1173 return $token;
1174 }
1175 // If it's a string (of maximum 255 characters)
1176 elseif (ereg("^\"[^\"]{0,255}\"$",$token))
1177 {
1178 return $token;
1179 }
1180 // if it's a function call
1181 elseif (eregi("^[A-Z0-9\xc0-\xdc\.]+$",$token) and ($this->_lookahead == "(")) {
1182 return $token;
1183 }
1184 return '';
1185 }
1186 }
1187
1188 /**
1189 * The parsing method. It parses a formula.
1190 *
1191 * @access public
1192 * @param string $formula The formula to parse, without the initial equal sign (=).
1193 */
1194 function parse($formula)
1195 {
1196 $this->_current_char = 0;
1197 $this->_formula = $formula;
1198 $this->_lookahead = $formula{1};
1199 $this->_advance();
1200 $this->_parse_tree = $this->_condition();
1201 if ($this->isError($this->_parse_tree)) {
1202 return $this->_parse_tree;
1203 }
1204 }
1205
1206 /**
1207 * It parses a condition. It assumes the following rule:
1208 * Cond -> Expr [(">" | "<") Expr]
1209 *
1210 * @access private
1211 * @return mixed The parsed ptg'd tree
1212 */
1213 function _condition()
1214 {
1215 $result = $this->_expression();
1216 if ($this->isError($result)) {
1217 return $result;
1218 }
1219 if ($this->_current_token == SPREADSHEET_EXCEL_WRITER_LT)
1220 {
1221 $this->_advance();
1222 $result2 = $this->_expression();
1223 if ($this->isError($result2)) {
1224 return $result2;
1225 }
1226 $result = $this->_createTree('ptgLT', $result, $result2);
1227 }
1228 elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_GT)
1229 {
1230 $this->_advance();
1231 $result2 = $this->_expression();
1232 if ($this->isError($result2)) {
1233 return $result2;
1234 }
1235 $result = $this->_createTree('ptgGT', $result, $result2);
1236 }
1237 elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_LE)
1238 {
1239 $this->_advance();
1240 $result2 = $this->_expression();
1241 if ($this->isError($result2)) {
1242 return $result2;
1243 }
1244 $result = $this->_createTree('ptgLE', $result, $result2);
1245 }
1246 elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_GE)
1247 {
1248 $this->_advance();
1249 $result2 = $this->_expression();
1250 if ($this->isError($result2)) {
1251 return $result2;
1252 }
1253 $result = $this->_createTree('ptgGE', $result, $result2);
1254 }
1255 elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_EQ)
1256 {
1257 $this->_advance();
1258 $result2 = $this->_expression();
1259 if ($this->isError($result2)) {
1260 return $result2;
1261 }
1262 $result = $this->_createTree('ptgEQ', $result, $result2);
1263 }
1264 elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_NE)
1265 {
1266 $this->_advance();
1267 $result2 = $this->_expression();
1268 if ($this->isError($result2)) {
1269 return $result2;
1270 }
1271 $result = $this->_createTree('ptgNE', $result, $result2);
1272 }
1273 return $result;
1274 }
1275
1276 /**
1277 * It parses a expression. It assumes the following rule:
1278 * Expr -> Term [("+" | "-") Term]
1279 *
1280 * @access private
1281 * @return mixed The parsed ptg'd tree
1282 */
1283 function _expression()
1284 {
1285 // If it's a string return a string node
1286 if (ereg("^\"[^\"]{0,255}\"$", $this->_current_token))
1287 {
1288 $result = $this->_createTree($this->_current_token, '', '');
1289 $this->_advance();
1290 return $result;
1291 }
1292 $result = $this->_term();
1293 if ($this->isError($result)) {
1294 return $result;
1295 }
1296 while (($this->_current_token == SPREADSHEET_EXCEL_WRITER_ADD) or
1297 ($this->_current_token == SPREADSHEET_EXCEL_WRITER_SUB))
1298 {
1299 if ($this->_current_token == SPREADSHEET_EXCEL_WRITER_ADD)
1300
1301 {
1302 $this->_advance();
1303 $result2 = $this->_term();
1304 if ($this->isError($result2)) {
1305 return $result2;
1306 }
1307 $result = $this->_createTree('ptgAdd', $result, $result2);
1308 }
1309 else
1310 {
1311 $this->_advance();
1312 $result2 = $this->_term();
1313 if ($this->isError($result2)) {
1314 return $result2;
1315 }
1316 $result = $this->_createTree('ptgSub', $result, $result2);
1317 }
1318 }
1319 return $result;
1320 }
1321
1322 /**
1323 * This function just introduces a ptgParen element in the tree, so that Excel
1324 * doesn't get confused when working with a parenthesized formula afterwards.
1325 *
1326 * @access private
1327 * @see _fact()
1328 * @return mixed The parsed ptg'd tree
1329 */
1330 function _parenthesizedExpression()
1331 {
1332 $result = $this->_createTree('ptgParen', $this->_expression(), '');
1333 return $result;
1334 }
1335
1336 /**
1337 * It parses a term. It assumes the following rule:
1338 * Term -> Fact [("*" | "/") Fact]
1339 *
1340 * @access private
1341 * @return mixed The parsed ptg'd tree
1342 */
1343 function _term()
1344 {
1345 $result = $this->_fact();
1346 if ($this->isError($result)) {
1347 return $result;
1348 }
1349 while (($this->_current_token == SPREADSHEET_EXCEL_WRITER_MUL) or
1350 ($this->_current_token == SPREADSHEET_EXCEL_WRITER_DIV)) {
1351 if ($this->_current_token == SPREADSHEET_EXCEL_WRITER_MUL)
1352
1353 {
1354 $this->_advance();
1355 $result2 = $this->_fact();
1356 if ($this->isError($result2)) {
1357 return $result2;
1358 }
1359 $result = $this->_createTree('ptgMul', $result, $result2);
1360 }
1361 else
1362 {
1363 $this->_advance();
1364 $result2 = $this->_fact();
1365 if ($this->isError($result2)) {
1366 return $result2;
1367 }
1368 $result = $this->_createTree('ptgDiv', $result, $result2);
1369 }
1370 }
1371 return $result;
1372 }
1373
1374 /**
1375 * It parses a factor. It assumes the following rule:
1376 * Fact -> ( Expr )
1377 * | CellRef
1378 * | CellRange
1379 * | Number
1380 * | Function
1381 *
1382 * @access private
1383 * @return mixed The parsed ptg'd tree
1384 */
1385 function _fact()
1386 {
1387 if ($this->_current_token == SPREADSHEET_EXCEL_WRITER_OPEN)
1388 {
1389 $this->_advance(); // eat the "("
1390 $result = $this->_parenthesizedExpression();
1391 if ($this->_current_token != SPREADSHEET_EXCEL_WRITER_CLOSE) {
1392 trigger_error("')' token expected.",E_USER_ERROR);
1393 }
1394 $this->_advance(); // eat the ")"
1395 return $result;
1396 }
1397 if (preg_match('/^\$?[A-Ia-i]?[A-Za-z]\$?[0-9]+$/',$this->_current_token))
1398 {
1399 // if it's a reference
1400 $result = $this->_createTree($this->_current_token, '', '');
1401 $this->_advance();
1402 return $result;
1403 }
1404 elseif (preg_match("/^[A-Za-z0-9_]+(\:[A-Za-z0-9_]+)?\![A-Ia-i]?[A-Za-z][0-9]+$/",$this->_current_token))
1405 {
1406 // If it's an external reference (Sheet1!A1 or Sheet1:Sheet2!A1)
1407 $result = $this->_createTree($this->_current_token, '', '');
1408 $this->_advance();
1409 return $result;
1410 }
1411 elseif (preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+:(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+$/",$this->_current_token) or
1412 preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+\.\.(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+$/",$this->_current_token))
1413 {
1414 // if it's a range
1415 $result = $this->_current_token;
1416 $this->_advance();
1417 return $result;
1418 }
1419 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))
1420 {
1421 // If it's an external range (Sheet1!A1:B2)
1422 $result = $this->_current_token;
1423 $this->_advance();
1424 return $result;
1425 }
1426 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))
1427 {
1428 // If it's an external range ('Sheet1'!A1:B2)
1429 $result = $this->_current_token;
1430 $this->_advance();
1431 return $result;
1432 }
1433 elseif (is_numeric($this->_current_token))
1434 {
1435 $result = $this->_createTree($this->_current_token, '', '');
1436 $this->_advance();
1437 return $result;
1438 }
1439 elseif (eregi("^[A-Z0-9\xc0-\xdc\.]+$",$this->_current_token))
1440 {
1441 // if it's a function call
1442 $result = $this->_func();
1443 return $result;
1444 }
1445 trigger_error("Sintactic error: ".$this->_current_token.", lookahead: ".
1446 $this->_lookahead.", current char: ".$this->_current_char, E_USER_ERROR);
1447 }
1448
1449 /**
1450 * It parses a function call. It assumes the following rule:
1451 * Func -> ( Expr [,Expr]* )
1452 *
1453 * @access private
1454 */
1455 function _func()
1456 {
1457 $num_args = 0; // number of arguments received
1458 $function = $this->_current_token;
1459 $this->_advance();
1460 $this->_advance(); // eat the "("
1461 while ($this->_current_token != ')')
1462 {
1463 if ($num_args > 0)
1464 {
1465 if ($this->_current_token == SPREADSHEET_EXCEL_WRITER_COMA ||
1466 $this->_current_token == SPREADSHEET_EXCEL_WRITER_SCOLON) {
1467 $this->_advance(); // eat the ","
1468 }
1469 else {
1470 trigger_error("Sintactic error: coma expected in ".
1471 "function $function, {$num_args}º arg", E_USER_ERROR);
1472 }
1473 $result2 = $this->_condition();
1474 if ($this->isError($result2)) {
1475 return $result2;
1476 }
1477 $result = $this->_createTree('arg', $result, $result2);
1478 }
1479 else { // first argument
1480 $result2 = $this->_condition();
1481 if ($this->isError($result2)) {
1482 return $result2;
1483 }
1484 $result = $this->_createTree('arg', '', $result2);
1485 }
1486 $num_args++;
1487 }
1488 $args = $this->_functions[$function][1];
1489 // If fixed number of args eg. TIME($i,$j,$k). Check that the number of args is valid.
1490 if (($args >= 0) and ($args != $num_args)) {
1491 trigger_error("Incorrect number of arguments in function $function() ",E_USER_ERROR);
1492 }
1493
1494 $result = $this->_createTree($function, $result, $num_args);
1495 $this->_advance(); // eat the ")"
1496 return $result;
1497 }
1498
1499 /**
1500 * Creates a tree. In fact an array which may have one or two arrays (sub-trees)
1501 * as elements.
1502 *
1503 * @access private
1504 * @param mixed $value The value of this node.
1505 * @param mixed $left The left array (sub-tree) or a final node.
1506 * @param mixed $right The right array (sub-tree) or a final node.
1507 */
1508 function _createTree($value, $left, $right)
1509 {
1510 return(array('value' => $value, 'left' => $left, 'right' => $right));
1511 }
1512
1513 /**
1514 * Builds a string containing the tree in reverse polish notation (What you
1515 * would use in a HP calculator stack).
1516 * The following tree:
1517 *
1518 * +
1519 * / \
1520 * 2 3
1521 *
1522 * produces: "23+"
1523 *
1524 * The following tree:
1525 *
1526 * +
1527 * / \
1528 * 3 *
1529 * / \
1530 * 6 A1
1531 *
1532 * produces: "36A1*+"
1533 *
1534 * In fact all operands, functions, references, etc... are written as ptg's
1535 *
1536 * @access public
1537 * @param array $tree The optional tree to convert.
1538 * @return string The tree in reverse polish notation
1539 */
1540 function toReversePolish($tree = array())
1541 {
1542 $polish = ""; // the string we are going to return
1543 if (empty($tree)) { // If it's the first call use _parse_tree
1544 $tree = $this->_parse_tree;
1545 }
1546 if (is_array($tree['left']))
1547 {
1548 $converted_tree = $this->toReversePolish($tree['left']);
1549 if ($this->isError($converted_tree)) {
1550 return $converted_tree;
1551 }
1552 $polish .= $converted_tree;
1553 }
1554 elseif ($tree['left'] != '') { // It's a final node
1555 $converted_tree = $this->_convert($tree['left']);
1556 if ($this->isError($converted_tree)) {
1557 return $converted_tree;
1558 }
1559 $polish .= $converted_tree;
1560 }
1561 if (is_array($tree['right']))
1562 {
1563 $converted_tree = $this->toReversePolish($tree['right']);
1564 if ($this->isError($converted_tree)) {
1565 return $converted_tree;
1566 }
1567 $polish .= $converted_tree;
1568 }
1569 elseif ($tree['right'] != '') { // It's a final node
1570 $converted_tree = $this->_convert($tree['right']);
1571 if ($this->isError($converted_tree)) {
1572 return $converted_tree;
1573 }
1574 $polish .= $converted_tree;
1575 }
1576 // if it's a function convert it here (so we can set it's arguments)
1577 if (preg_match("/^[A-Z0-9\xc0-\xdc\.]+$/",$tree['value']) and
1578 !preg_match('/^([A-Ia-i]?[A-Za-z])(\d+)$/',$tree['value']) and
1579 !preg_match("/^[A-Ia-i]?[A-Za-z](\d+)\.\.[A-Ia-i]?[A-Za-z](\d+)$/",$tree['value']) and
1580 !is_numeric($tree['value']) and
1581 !isset($this->ptg[$tree['value']]))
1582 {
1583 // left subtree for a function is always an array.
1584 if ($tree['left'] != '') {
1585 $left_tree = $this->toReversePolish($tree['left']);
1586 }
1587 else {
1588 $left_tree = '';
1589 }
1590 if ($this->isError($left_tree)) {
1591 return $left_tree;
1592 }
1593 // add it's left subtree and return.
1594 return $left_tree.$this->_convertFunction($tree['value'], $tree['right']);
1595 }
1596 else
1597 {
1598 $converted_tree = $this->_convert($tree['value']);
1599 if ($this->isError($converted_tree)) {
1600 return $converted_tree;
1601 }
1602 }
1603 $polish .= $converted_tree;
1604 return $polish;
1605 }
1606
1607 }
1608
1609
1610 ?>