Multimag  0.2.992
parser.php
См. документацию.
1 <?php
26 namespace excel\writer;
27 
31 define('SPREADSHEET_EXCEL_WRITER_ADD', "+");
32 
36 define('SPREADSHEET_EXCEL_WRITER_SUB', "-");
37 
41 define('SPREADSHEET_EXCEL_WRITER_MUL', "*");
42 
46 define('SPREADSHEET_EXCEL_WRITER_DIV', "/");
47 
51 define('SPREADSHEET_EXCEL_WRITER_OPEN', "(");
52 
56 define('SPREADSHEET_EXCEL_WRITER_CLOSE', ")");
57 
61 define('SPREADSHEET_EXCEL_WRITER_COMA', ",");
62 
66 define('SPREADSHEET_EXCEL_WRITER_SEMICOLON', ";");
67 
71 define('SPREADSHEET_EXCEL_WRITER_GT', ">");
72 
76 define('SPREADSHEET_EXCEL_WRITER_LT', "<");
77 
81 define('SPREADSHEET_EXCEL_WRITER_LE', "<=");
82 
86 define('SPREADSHEET_EXCEL_WRITER_GE', ">=");
87 
91 define('SPREADSHEET_EXCEL_WRITER_EQ', "=");
92 
96 define('SPREADSHEET_EXCEL_WRITER_NE', "<>");
97 
101 define('SPREADSHEET_EXCEL_WRITER_CONCAT', "&");
102 
103 require_once 'PEAR.php';
104 
113 class Parser extends \PEAR
114 {
120 
126 
132 
138 
144 
150 
156 
162 
168 
175  function __construct($byte_order, $biff_version)
176  {
177  $this->_current_char = 0;
178  $this->_BIFF_version = $biff_version;
179  $this->_current_token = ''; // The token we are working on.
180  $this->_formula = ''; // The formula to parse.
181  $this->_lookahead = ''; // The character ahead of the current char.
182  $this->_parse_tree = ''; // The parse tree to be generated.
183  $this->_initializeHashes(); // Initialize the hashes: ptg's and function's ptg's
184  $this->_byte_order = $byte_order; // Little Endian or Big Endian
185  $this->_ext_sheets = array();
186  $this->_references = array();
187  }
188 
194  function _initializeHashes()
195  {
196  // The Excel ptg indices
197  $this->ptg = array(
198  'ptgExp' => 0x01,
199  'ptgTbl' => 0x02,
200  'ptgAdd' => 0x03,
201  'ptgSub' => 0x04,
202  'ptgMul' => 0x05,
203  'ptgDiv' => 0x06,
204  'ptgPower' => 0x07,
205  'ptgConcat' => 0x08,
206  'ptgLT' => 0x09,
207  'ptgLE' => 0x0A,
208  'ptgEQ' => 0x0B,
209  'ptgGE' => 0x0C,
210  'ptgGT' => 0x0D,
211  'ptgNE' => 0x0E,
212  'ptgIsect' => 0x0F,
213  'ptgUnion' => 0x10,
214  'ptgRange' => 0x11,
215  'ptgUplus' => 0x12,
216  'ptgUminus' => 0x13,
217  'ptgPercent' => 0x14,
218  'ptgParen' => 0x15,
219  'ptgMissArg' => 0x16,
220  'ptgStr' => 0x17,
221  'ptgAttr' => 0x19,
222  'ptgSheet' => 0x1A,
223  'ptgEndSheet' => 0x1B,
224  'ptgErr' => 0x1C,
225  'ptgBool' => 0x1D,
226  'ptgInt' => 0x1E,
227  'ptgNum' => 0x1F,
228  'ptgArray' => 0x20,
229  'ptgFunc' => 0x21,
230  'ptgFuncVar' => 0x22,
231  'ptgName' => 0x23,
232  'ptgRef' => 0x24,
233  'ptgArea' => 0x25,
234  'ptgMemArea' => 0x26,
235  'ptgMemErr' => 0x27,
236  'ptgMemNoMem' => 0x28,
237  'ptgMemFunc' => 0x29,
238  'ptgRefErr' => 0x2A,
239  'ptgAreaErr' => 0x2B,
240  'ptgRefN' => 0x2C,
241  'ptgAreaN' => 0x2D,
242  'ptgMemAreaN' => 0x2E,
243  'ptgMemNoMemN' => 0x2F,
244  'ptgNameX' => 0x39,
245  'ptgRef3d' => 0x3A,
246  'ptgArea3d' => 0x3B,
247  'ptgRefErr3d' => 0x3C,
248  'ptgAreaErr3d' => 0x3D,
249  'ptgArrayV' => 0x40,
250  'ptgFuncV' => 0x41,
251  'ptgFuncVarV' => 0x42,
252  'ptgNameV' => 0x43,
253  'ptgRefV' => 0x44,
254  'ptgAreaV' => 0x45,
255  'ptgMemAreaV' => 0x46,
256  'ptgMemErrV' => 0x47,
257  'ptgMemNoMemV' => 0x48,
258  'ptgMemFuncV' => 0x49,
259  'ptgRefErrV' => 0x4A,
260  'ptgAreaErrV' => 0x4B,
261  'ptgRefNV' => 0x4C,
262  'ptgAreaNV' => 0x4D,
263  'ptgMemAreaNV' => 0x4E,
264  'ptgMemNoMemN' => 0x4F,
265  'ptgFuncCEV' => 0x58,
266  'ptgNameXV' => 0x59,
267  'ptgRef3dV' => 0x5A,
268  'ptgArea3dV' => 0x5B,
269  'ptgRefErr3dV' => 0x5C,
270  'ptgAreaErr3d' => 0x5D,
271  'ptgArrayA' => 0x60,
272  'ptgFuncA' => 0x61,
273  'ptgFuncVarA' => 0x62,
274  'ptgNameA' => 0x63,
275  'ptgRefA' => 0x64,
276  'ptgAreaA' => 0x65,
277  'ptgMemAreaA' => 0x66,
278  'ptgMemErrA' => 0x67,
279  'ptgMemNoMemA' => 0x68,
280  'ptgMemFuncA' => 0x69,
281  'ptgRefErrA' => 0x6A,
282  'ptgAreaErrA' => 0x6B,
283  'ptgRefNA' => 0x6C,
284  'ptgAreaNA' => 0x6D,
285  'ptgMemAreaNA' => 0x6E,
286  'ptgMemNoMemN' => 0x6F,
287  'ptgFuncCEA' => 0x78,
288  'ptgNameXA' => 0x79,
289  'ptgRef3dA' => 0x7A,
290  'ptgArea3dA' => 0x7B,
291  'ptgRefErr3dA' => 0x7C,
292  'ptgAreaErr3d' => 0x7D
293  );
294 
295  // Thanks to Michael Meeks and Gnumeric for the initial arg values.
296  //
297  // The following hash was generated by "function_locale.pl" in the distro.
298  // Refer to function_locale.pl for non-English function names.
299  //
300  // The array elements are as follow:
301  // ptg: The Excel function ptg code.
302  // args: The number of arguments that the function takes:
303  // >=0 is a fixed number of arguments.
304  // -1 is a variable number of arguments.
305  // class: The reference, value or array class of the function args.
306  // vol: The function is volatile.
307  //
308  $this->_functions = array(
309  // function ptg args class vol
310  'COUNT' => array( 0, -1, 0, 0 ),
311  'IF' => array( 1, -1, 1, 0 ),
312  'ISNA' => array( 2, 1, 1, 0 ),
313  'ISERROR' => array( 3, 1, 1, 0 ),
314  'SUM' => array( 4, -1, 0, 0 ),
315  'AVERAGE' => array( 5, -1, 0, 0 ),
316  'MIN' => array( 6, -1, 0, 0 ),
317  'MAX' => array( 7, -1, 0, 0 ),
318  'ROW' => array( 8, -1, 0, 0 ),
319  'COLUMN' => array( 9, -1, 0, 0 ),
320  'NA' => array( 10, 0, 0, 0 ),
321  'NPV' => array( 11, -1, 1, 0 ),
322  'STDEV' => array( 12, -1, 0, 0 ),
323  'DOLLAR' => array( 13, -1, 1, 0 ),
324  'FIXED' => array( 14, -1, 1, 0 ),
325  'SIN' => array( 15, 1, 1, 0 ),
326  'COS' => array( 16, 1, 1, 0 ),
327  'TAN' => array( 17, 1, 1, 0 ),
328  'ATAN' => array( 18, 1, 1, 0 ),
329  'PI' => array( 19, 0, 1, 0 ),
330  'SQRT' => array( 20, 1, 1, 0 ),
331  'EXP' => array( 21, 1, 1, 0 ),
332  'LN' => array( 22, 1, 1, 0 ),
333  'LOG10' => array( 23, 1, 1, 0 ),
334  'ABS' => array( 24, 1, 1, 0 ),
335  'INT' => array( 25, 1, 1, 0 ),
336  'SIGN' => array( 26, 1, 1, 0 ),
337  'ROUND' => array( 27, 2, 1, 0 ),
338  'LOOKUP' => array( 28, -1, 0, 0 ),
339  'INDEX' => array( 29, -1, 0, 1 ),
340  'REPT' => array( 30, 2, 1, 0 ),
341  'MID' => array( 31, 3, 1, 0 ),
342  'LEN' => array( 32, 1, 1, 0 ),
343  'VALUE' => array( 33, 1, 1, 0 ),
344  'TRUE' => array( 34, 0, 1, 0 ),
345  'FALSE' => array( 35, 0, 1, 0 ),
346  'AND' => array( 36, -1, 0, 0 ),
347  'OR' => array( 37, -1, 0, 0 ),
348  'NOT' => array( 38, 1, 1, 0 ),
349  'MOD' => array( 39, 2, 1, 0 ),
350  'DCOUNT' => array( 40, 3, 0, 0 ),
351  'DSUM' => array( 41, 3, 0, 0 ),
352  'DAVERAGE' => array( 42, 3, 0, 0 ),
353  'DMIN' => array( 43, 3, 0, 0 ),
354  'DMAX' => array( 44, 3, 0, 0 ),
355  'DSTDEV' => array( 45, 3, 0, 0 ),
356  'VAR' => array( 46, -1, 0, 0 ),
357  'DVAR' => array( 47, 3, 0, 0 ),
358  'TEXT' => array( 48, 2, 1, 0 ),
359  'LINEST' => array( 49, -1, 0, 0 ),
360  'TREND' => array( 50, -1, 0, 0 ),
361  'LOGEST' => array( 51, -1, 0, 0 ),
362  'GROWTH' => array( 52, -1, 0, 0 ),
363  'PV' => array( 56, -1, 1, 0 ),
364  'FV' => array( 57, -1, 1, 0 ),
365  'NPER' => array( 58, -1, 1, 0 ),
366  'PMT' => array( 59, -1, 1, 0 ),
367  'RATE' => array( 60, -1, 1, 0 ),
368  'MIRR' => array( 61, 3, 0, 0 ),
369  'IRR' => array( 62, -1, 0, 0 ),
370  'RAND' => array( 63, 0, 1, 1 ),
371  'MATCH' => array( 64, -1, 0, 0 ),
372  'DATE' => array( 65, 3, 1, 0 ),
373  'TIME' => array( 66, 3, 1, 0 ),
374  'DAY' => array( 67, 1, 1, 0 ),
375  'MONTH' => array( 68, 1, 1, 0 ),
376  'YEAR' => array( 69, 1, 1, 0 ),
377  'WEEKDAY' => array( 70, -1, 1, 0 ),
378  'HOUR' => array( 71, 1, 1, 0 ),
379  'MINUTE' => array( 72, 1, 1, 0 ),
380  'SECOND' => array( 73, 1, 1, 0 ),
381  'NOW' => array( 74, 0, 1, 1 ),
382  'AREAS' => array( 75, 1, 0, 1 ),
383  'ROWS' => array( 76, 1, 0, 1 ),
384  'COLUMNS' => array( 77, 1, 0, 1 ),
385  'OFFSET' => array( 78, -1, 0, 1 ),
386  'SEARCH' => array( 82, -1, 1, 0 ),
387  'TRANSPOSE' => array( 83, 1, 1, 0 ),
388  'TYPE' => array( 86, 1, 1, 0 ),
389  'ATAN2' => array( 97, 2, 1, 0 ),
390  'ASIN' => array( 98, 1, 1, 0 ),
391  'ACOS' => array( 99, 1, 1, 0 ),
392  'CHOOSE' => array( 100, -1, 1, 0 ),
393  'HLOOKUP' => array( 101, -1, 0, 0 ),
394  'VLOOKUP' => array( 102, -1, 0, 0 ),
395  'ISREF' => array( 105, 1, 0, 0 ),
396  'LOG' => array( 109, -1, 1, 0 ),
397  'CHAR' => array( 111, 1, 1, 0 ),
398  'LOWER' => array( 112, 1, 1, 0 ),
399  'UPPER' => array( 113, 1, 1, 0 ),
400  'PROPER' => array( 114, 1, 1, 0 ),
401  'LEFT' => array( 115, -1, 1, 0 ),
402  'RIGHT' => array( 116, -1, 1, 0 ),
403  'EXACT' => array( 117, 2, 1, 0 ),
404  'TRIM' => array( 118, 1, 1, 0 ),
405  'REPLACE' => array( 119, 4, 1, 0 ),
406  'SUBSTITUTE' => array( 120, -1, 1, 0 ),
407  'CODE' => array( 121, 1, 1, 0 ),
408  'FIND' => array( 124, -1, 1, 0 ),
409  'CELL' => array( 125, -1, 0, 1 ),
410  'ISERR' => array( 126, 1, 1, 0 ),
411  'ISTEXT' => array( 127, 1, 1, 0 ),
412  'ISNUMBER' => array( 128, 1, 1, 0 ),
413  'ISBLANK' => array( 129, 1, 1, 0 ),
414  'T' => array( 130, 1, 0, 0 ),
415  'N' => array( 131, 1, 0, 0 ),
416  'DATEVALUE' => array( 140, 1, 1, 0 ),
417  'TIMEVALUE' => array( 141, 1, 1, 0 ),
418  'SLN' => array( 142, 3, 1, 0 ),
419  'SYD' => array( 143, 4, 1, 0 ),
420  'DDB' => array( 144, -1, 1, 0 ),
421  'INDIRECT' => array( 148, -1, 1, 1 ),
422  'CALL' => array( 150, -1, 1, 0 ),
423  'CLEAN' => array( 162, 1, 1, 0 ),
424  'MDETERM' => array( 163, 1, 2, 0 ),
425  'MINVERSE' => array( 164, 1, 2, 0 ),
426  'MMULT' => array( 165, 2, 2, 0 ),
427  'IPMT' => array( 167, -1, 1, 0 ),
428  'PPMT' => array( 168, -1, 1, 0 ),
429  'COUNTA' => array( 169, -1, 0, 0 ),
430  'PRODUCT' => array( 183, -1, 0, 0 ),
431  'FACT' => array( 184, 1, 1, 0 ),
432  'DPRODUCT' => array( 189, 3, 0, 0 ),
433  'ISNONTEXT' => array( 190, 1, 1, 0 ),
434  'STDEVP' => array( 193, -1, 0, 0 ),
435  'VARP' => array( 194, -1, 0, 0 ),
436  'DSTDEVP' => array( 195, 3, 0, 0 ),
437  'DVARP' => array( 196, 3, 0, 0 ),
438  'TRUNC' => array( 197, -1, 1, 0 ),
439  'ISLOGICAL' => array( 198, 1, 1, 0 ),
440  'DCOUNTA' => array( 199, 3, 0, 0 ),
441  'ROUNDUP' => array( 212, 2, 1, 0 ),
442  'ROUNDDOWN' => array( 213, 2, 1, 0 ),
443  'RANK' => array( 216, -1, 0, 0 ),
444  'ADDRESS' => array( 219, -1, 1, 0 ),
445  'DAYS360' => array( 220, -1, 1, 0 ),
446  'TODAY' => array( 221, 0, 1, 1 ),
447  'VDB' => array( 222, -1, 1, 0 ),
448  'MEDIAN' => array( 227, -1, 0, 0 ),
449  'SUMPRODUCT' => array( 228, -1, 2, 0 ),
450  'SINH' => array( 229, 1, 1, 0 ),
451  'COSH' => array( 230, 1, 1, 0 ),
452  'TANH' => array( 231, 1, 1, 0 ),
453  'ASINH' => array( 232, 1, 1, 0 ),
454  'ACOSH' => array( 233, 1, 1, 0 ),
455  'ATANH' => array( 234, 1, 1, 0 ),
456  'DGET' => array( 235, 3, 0, 0 ),
457  'INFO' => array( 244, 1, 1, 1 ),
458  'DB' => array( 247, -1, 1, 0 ),
459  'FREQUENCY' => array( 252, 2, 0, 0 ),
460  'ERROR.TYPE' => array( 261, 1, 1, 0 ),
461  'REGISTER.ID' => array( 267, -1, 1, 0 ),
462  'AVEDEV' => array( 269, -1, 0, 0 ),
463  'BETADIST' => array( 270, -1, 1, 0 ),
464  'GAMMALN' => array( 271, 1, 1, 0 ),
465  'BETAINV' => array( 272, -1, 1, 0 ),
466  'BINOMDIST' => array( 273, 4, 1, 0 ),
467  'CHIDIST' => array( 274, 2, 1, 0 ),
468  'CHIINV' => array( 275, 2, 1, 0 ),
469  'COMBIN' => array( 276, 2, 1, 0 ),
470  'CONFIDENCE' => array( 277, 3, 1, 0 ),
471  'CRITBINOM' => array( 278, 3, 1, 0 ),
472  'EVEN' => array( 279, 1, 1, 0 ),
473  'EXPONDIST' => array( 280, 3, 1, 0 ),
474  'FDIST' => array( 281, 3, 1, 0 ),
475  'FINV' => array( 282, 3, 1, 0 ),
476  'FISHER' => array( 283, 1, 1, 0 ),
477  'FISHERINV' => array( 284, 1, 1, 0 ),
478  'FLOOR' => array( 285, 2, 1, 0 ),
479  'GAMMADIST' => array( 286, 4, 1, 0 ),
480  'GAMMAINV' => array( 287, 3, 1, 0 ),
481  'CEILING' => array( 288, 2, 1, 0 ),
482  'HYPGEOMDIST' => array( 289, 4, 1, 0 ),
483  'LOGNORMDIST' => array( 290, 3, 1, 0 ),
484  'LOGINV' => array( 291, 3, 1, 0 ),
485  'NEGBINOMDIST' => array( 292, 3, 1, 0 ),
486  'NORMDIST' => array( 293, 4, 1, 0 ),
487  'NORMSDIST' => array( 294, 1, 1, 0 ),
488  'NORMINV' => array( 295, 3, 1, 0 ),
489  'NORMSINV' => array( 296, 1, 1, 0 ),
490  'STANDARDIZE' => array( 297, 3, 1, 0 ),
491  'ODD' => array( 298, 1, 1, 0 ),
492  'PERMUT' => array( 299, 2, 1, 0 ),
493  'POISSON' => array( 300, 3, 1, 0 ),
494  'TDIST' => array( 301, 3, 1, 0 ),
495  'WEIBULL' => array( 302, 4, 1, 0 ),
496  'SUMXMY2' => array( 303, 2, 2, 0 ),
497  'SUMX2MY2' => array( 304, 2, 2, 0 ),
498  'SUMX2PY2' => array( 305, 2, 2, 0 ),
499  'CHITEST' => array( 306, 2, 2, 0 ),
500  'CORREL' => array( 307, 2, 2, 0 ),
501  'COVAR' => array( 308, 2, 2, 0 ),
502  'FORECAST' => array( 309, 3, 2, 0 ),
503  'FTEST' => array( 310, 2, 2, 0 ),
504  'INTERCEPT' => array( 311, 2, 2, 0 ),
505  'PEARSON' => array( 312, 2, 2, 0 ),
506  'RSQ' => array( 313, 2, 2, 0 ),
507  'STEYX' => array( 314, 2, 2, 0 ),
508  'SLOPE' => array( 315, 2, 2, 0 ),
509  'TTEST' => array( 316, 4, 2, 0 ),
510  'PROB' => array( 317, -1, 2, 0 ),
511  'DEVSQ' => array( 318, -1, 0, 0 ),
512  'GEOMEAN' => array( 319, -1, 0, 0 ),
513  'HARMEAN' => array( 320, -1, 0, 0 ),
514  'SUMSQ' => array( 321, -1, 0, 0 ),
515  'KURT' => array( 322, -1, 0, 0 ),
516  'SKEW' => array( 323, -1, 0, 0 ),
517  'ZTEST' => array( 324, -1, 0, 0 ),
518  'LARGE' => array( 325, 2, 0, 0 ),
519  'SMALL' => array( 326, 2, 0, 0 ),
520  'QUARTILE' => array( 327, 2, 0, 0 ),
521  'PERCENTILE' => array( 328, 2, 0, 0 ),
522  'PERCENTRANK' => array( 329, -1, 0, 0 ),
523  'MODE' => array( 330, -1, 2, 0 ),
524  'TRIMMEAN' => array( 331, 2, 0, 0 ),
525  'TINV' => array( 332, 2, 1, 0 ),
526  'CONCATENATE' => array( 336, -1, 1, 0 ),
527  'POWER' => array( 337, 2, 1, 0 ),
528  'RADIANS' => array( 342, 1, 1, 0 ),
529  'DEGREES' => array( 343, 1, 1, 0 ),
530  'SUBTOTAL' => array( 344, -1, 0, 0 ),
531  'SUMIF' => array( 345, -1, 0, 0 ),
532  'COUNTIF' => array( 346, 2, 0, 0 ),
533  'COUNTBLANK' => array( 347, 1, 0, 0 ),
534  'ROMAN' => array( 354, -1, 1, 0 )
535  );
536  }
537 
546  function _convert($token)
547  {
548  if (preg_match("/^\"[^\"]{0,255}\"$/", $token)) {
549  return $this->_convertString($token);
550 
551  } elseif (is_numeric($token)) {
552  return $this->_convertNumber($token);
553 
554  // match references like A1 or $A$1
555  } elseif (preg_match('/^\$?([A-Ia-i]?[A-Za-z])\$?(\d+)$/',$token)) {
556  return $this->_convertRef2d($token);
557 
558  // match external references like Sheet1!A1 or Sheet1:Sheet2!A1
559  } elseif (preg_match("/^\w+(\:\w+)?\![A-Ia-i]?[A-Za-z](\d+)$/u",$token)) {
560  return $this->_convertRef3d($token);
561 
562  // match external references like 'Sheet1'!A1 or 'Sheet1:Sheet2'!A1
563  } elseif (preg_match("/^'[\w -]+(\:[\w -]+)?'\![A-Ia-i]?[A-Za-z](\d+)$/u",$token)) {
564  return $this->_convertRef3d($token);
565 
566  // match ranges like A1:B2
567  } elseif (preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?(\d+)\:(\$)?[A-Ia-i]?[A-Za-z](\$)?(\d+)$/",$token)) {
568  return $this->_convertRange2d($token);
569 
570  // match ranges like A1..B2
571  } elseif (preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?(\d+)\.\.(\$)?[A-Ia-i]?[A-Za-z](\$)?(\d+)$/",$token)) {
572  return $this->_convertRange2d($token);
573 
574  // match external ranges like Sheet1!A1 or Sheet1:Sheet2!A1:B2
575  } elseif (preg_match("/^\w+(\:\w+)?\!([A-Ia-i]?[A-Za-z])?(\d+)\:([A-Ia-i]?[A-Za-z])?(\d+)$/u",$token)) {
576  return $this->_convertRange3d($token);
577 
578  // match external ranges like 'Sheet1'!A1 or 'Sheet1:Sheet2'!A1:B2
579  } elseif (preg_match("/^'[\w -]+(\:[\w -]+)?'\!([A-Ia-i]?[A-Za-z])?(\d+)\:([A-Ia-i]?[A-Za-z])?(\d+)$/u",$token)) {
580  return $this->_convertRange3d($token);
581 
582  // operators (including parentheses)
583  } elseif (isset($this->ptg[$token])) {
584  return pack("C", $this->ptg[$token]);
585 
586  // commented so argument number can be processed correctly. See toReversePolish().
587  /*elseif (preg_match("/[A-Z0-9\xc0-\xdc\.]+/",$token))
588  {
589  return($this->_convertFunction($token,$this->_func_args));
590  }*/
591 
592  // if it's an argument, ignore the token (the argument remains)
593  } elseif ($token == 'arg') {
594  return '';
595  }
596  // TODO: use real error codes
597  return $this->raiseError("Unknown token $token");
598  }
599 
606  function _convertNumber($num)
607  {
608  // Integer in the range 0..2**16-1
609  if ((preg_match("/^\d+$/", $num)) and ($num <= 65535)) {
610  return pack("Cv", $this->ptg['ptgInt'], $num);
611  } else { // A float
612  if ($this->_byte_order) { // if it's Big Endian
613  $num = strrev($num);
614  }
615  return pack("Cd", $this->ptg['ptgNum'], $num);
616  }
617  }
618 
627  function _convertString($string)
628  {
629  // chop away beggining and ending quotes
630  $string = substr($string, 1, strlen($string) - 2);
631  if (strlen($string) > 255) {
632  return $this->raiseError("String is too long");
633  }
634 
635  if ($this->_BIFF_version == 0x0500) {
636  return pack("CC", $this->ptg['ptgStr'], strlen($string)).$string;
637  } elseif ($this->_BIFF_version == 0x0600) {
638  $encoding = 0; // TODO: Unicode support
639  return pack("CCC", $this->ptg['ptgStr'], strlen($string), $encoding).$string;
640  }
641  }
642 
652  function _convertFunction($token, $num_args)
653  {
654  $args = $this->_functions[$token][1];
655  $volatile = $this->_functions[$token][3];
656 
657  // Fixed number of args eg. TIME($i,$j,$k).
658  if ($args >= 0) {
659  return pack("Cv", $this->ptg['ptgFuncV'], $this->_functions[$token][0]);
660  }
661  // Variable number of args eg. SUM($i,$j,$k, ..).
662  if ($args == -1) {
663  return pack("CCv", $this->ptg['ptgFuncVarV'], $num_args, $this->_functions[$token][0]);
664  }
665  }
666 
673  function _convertRange2d($range, $class=0)
674  {
675 
676  // TODO: possible class value 0,1,2 check Formula.pm
677  // Split the range into 2 cell refs
678  if (preg_match("/^([A-Ia-i]?[A-Za-z])(\d+)\:([A-Ia-i]?[A-Za-z])(\d+)$/", $range)) {
679  list($cell1, $cell2) = explode(':', $range);
680  } elseif (preg_match("/^([A-Ia-i]?[A-Za-z])(\d+)\.\.([A-Ia-i]?[A-Za-z])(\d+)$/", $range)) {
681  list($cell1, $cell2) = explode('\.\.', $range);
682 
683  } else {
684  // TODO: use real error codes
685  return $this->raiseError("Unknown range separator", 0, PEAR_ERROR_DIE);
686  }
687 
688  // Convert the cell references
689  $cell_array1 = $this->_cellToPackedRowcol($cell1);
690  if (PEAR::isError($cell_array1)) {
691  return $cell_array1;
692  }
693  list($row1, $col1) = $cell_array1;
694  $cell_array2 = $this->_cellToPackedRowcol($cell2);
695  if (PEAR::isError($cell_array2)) {
696  return $cell_array2;
697  }
698  list($row2, $col2) = $cell_array2;
699 
700  // The ptg value depends on the class of the ptg.
701  if ($class == 0) {
702  $ptgArea = pack("C", $this->ptg['ptgArea']);
703  } elseif ($class == 1) {
704  $ptgArea = pack("C", $this->ptg['ptgAreaV']);
705  } elseif ($class == 2) {
706  $ptgArea = pack("C", $this->ptg['ptgAreaA']);
707  } else {
708  // TODO: use real error codes
709  return $this->raiseError("Unknown class $class", 0, PEAR_ERROR_DIE);
710  }
711  return $ptgArea . $row1 . $row2 . $col1. $col2;
712  }
713 
722  function _convertRange3d($token)
723  {
724  $class = 2; // as far as I know, this is magick.
725 
726  // Split the ref at the ! symbol
727  list($ext_ref, $range) = explode('!', $token);
728 
729  // Convert the external reference part (different for BIFF8)
730  if ($this->_BIFF_version == 0x0500) {
731  $ext_ref = $this->_packExtRef($ext_ref);
732  if (PEAR::isError($ext_ref)) {
733  return $ext_ref;
734  }
735  } elseif ($this->_BIFF_version == 0x0600) {
736  $ext_ref = $this->_getRefIndex($ext_ref);
737  if (PEAR::isError($ext_ref)) {
738  return $ext_ref;
739  }
740  }
741 
742  // Split the range into 2 cell refs
743  list($cell1, $cell2) = explode(':', $range);
744 
745  // Convert the cell references
746  if (preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?(\d+)$/", $cell1)) {
747  $cell_array1 = $this->_cellToPackedRowcol($cell1);
748  if (PEAR::isError($cell_array1)) {
749  return $cell_array1;
750  }
751  list($row1, $col1) = $cell_array1;
752  $cell_array2 = $this->_cellToPackedRowcol($cell2);
753  if (PEAR::isError($cell_array2)) {
754  return $cell_array2;
755  }
756  list($row2, $col2) = $cell_array2;
757  } else { // It's a rows range (like 26:27)
758  $cells_array = $this->_rangeToPackedRange($cell1.':'.$cell2);
759  if (PEAR::isError($cells_array)) {
760  return $cells_array;
761  }
762  list($row1, $col1, $row2, $col2) = $cells_array;
763  }
764 
765  // The ptg value depends on the class of the ptg.
766  if ($class == 0) {
767  $ptgArea = pack("C", $this->ptg['ptgArea3d']);
768  } elseif ($class == 1) {
769  $ptgArea = pack("C", $this->ptg['ptgArea3dV']);
770  } elseif ($class == 2) {
771  $ptgArea = pack("C", $this->ptg['ptgArea3dA']);
772  } else {
773  return $this->raiseError("Unknown class $class", 0, PEAR_ERROR_DIE);
774  }
775 
776  return $ptgArea . $ext_ref . $row1 . $row2 . $col1. $col2;
777  }
778 
786  function _convertRef2d($cell)
787  {
788  $class = 2; // as far as I know, this is magick.
789 
790  // Convert the cell reference
791  $cell_array = $this->_cellToPackedRowcol($cell);
792  if (PEAR::isError($cell_array)) {
793  return $cell_array;
794  }
795  list($row, $col) = $cell_array;
796 
797  // The ptg value depends on the class of the ptg.
798  if ($class == 0) {
799  $ptgRef = pack("C", $this->ptg['ptgRef']);
800  } elseif ($class == 1) {
801  $ptgRef = pack("C", $this->ptg['ptgRefV']);
802  } elseif ($class == 2) {
803  $ptgRef = pack("C", $this->ptg['ptgRefA']);
804  } else {
805  // TODO: use real error codes
806  return $this->raiseError("Unknown class $class");
807  }
808  return $ptgRef.$row.$col;
809  }
810 
819  function _convertRef3d($cell)
820  {
821  $class = 2; // as far as I know, this is magick.
822 
823  // Split the ref at the ! symbol
824  list($ext_ref, $cell) = explode('!', $cell);
825 
826  // Convert the external reference part (different for BIFF8)
827  if ($this->_BIFF_version == 0x0500) {
828  $ext_ref = $this->_packExtRef($ext_ref);
829  if (PEAR::isError($ext_ref)) {
830  return $ext_ref;
831  }
832  } elseif ($this->_BIFF_version == 0x0600) {
833  $ext_ref = $this->_getRefIndex($ext_ref);
834  if (PEAR::isError($ext_ref)) {
835  return $ext_ref;
836  }
837  }
838 
839  // Convert the cell reference part
840  list($row, $col) = $this->_cellToPackedRowcol($cell);
841 
842  // The ptg value depends on the class of the ptg.
843  if ($class == 0) {
844  $ptgRef = pack("C", $this->ptg['ptgRef3d']);
845  } elseif ($class == 1) {
846  $ptgRef = pack("C", $this->ptg['ptgRef3dV']);
847  } elseif ($class == 2) {
848  $ptgRef = pack("C", $this->ptg['ptgRef3dA']);
849  } else {
850  return $this->raiseError("Unknown class $class", 0, PEAR_ERROR_DIE);
851  }
852 
853  return $ptgRef . $ext_ref. $row . $col;
854  }
855 
864  function _packExtRef($ext_ref)
865  {
866  $ext_ref = preg_replace("/^'/", '', $ext_ref); // Remove leading ' if any.
867  $ext_ref = preg_replace("/'$/", '', $ext_ref); // Remove trailing ' if any.
868 
869  // Check if there is a sheet range eg., Sheet1:Sheet2.
870  if (preg_match("/:/", $ext_ref)) {
871  list($sheet_name1, $sheet_name2) = explode(':', $ext_ref);
872 
873  $sheet1 = $this->_getSheetIndex($sheet_name1);
874  if ($sheet1 == -1) {
875  return $this->raiseError("Unknown sheet name $sheet_name1 in formula");
876  }
877  $sheet2 = $this->_getSheetIndex($sheet_name2);
878  if ($sheet2 == -1) {
879  return $this->raiseError("Unknown sheet name $sheet_name2 in formula");
880  }
881 
882  // Reverse max and min sheet numbers if necessary
883  if ($sheet1 > $sheet2) {
884  list($sheet1, $sheet2) = array($sheet2, $sheet1);
885  }
886  } else { // Single sheet name only.
887  $sheet1 = $this->_getSheetIndex($ext_ref);
888  if ($sheet1 == -1) {
889  return $this->raiseError("Unknown sheet name $ext_ref in formula");
890  }
891  $sheet2 = $sheet1;
892  }
893 
894  // References are stored relative to 0xFFFF.
895  $offset = -1 - $sheet1;
896 
897  return pack('vdvv', $offset, 0x00, $sheet1, $sheet2);
898  }
899 
910  function _getRefIndex($ext_ref)
911  {
912  $ext_ref = preg_replace("/^'/", '', $ext_ref); // Remove leading ' if any.
913  $ext_ref = preg_replace("/'$/", '', $ext_ref); // Remove trailing ' if any.
914 
915  // Check if there is a sheet range eg., Sheet1:Sheet2.
916  if (preg_match("/:/", $ext_ref)) {
917  list($sheet_name1, $sheet_name2) = explode(':', $ext_ref);
918 
919  $sheet1 = $this->_getSheetIndex($sheet_name1);
920  if ($sheet1 == -1) {
921  return $this->raiseError("Unknown sheet name $sheet_name1 in formula");
922  }
923  $sheet2 = $this->_getSheetIndex($sheet_name2);
924  if ($sheet2 == -1) {
925  return $this->raiseError("Unknown sheet name $sheet_name2 in formula");
926  }
927 
928  // Reverse max and min sheet numbers if necessary
929  if ($sheet1 > $sheet2) {
930  list($sheet1, $sheet2) = array($sheet2, $sheet1);
931  }
932  } else { // Single sheet name only.
933  $sheet1 = $this->_getSheetIndex($ext_ref);
934  if ($sheet1 == -1) {
935  return $this->raiseError("Unknown sheet name $ext_ref in formula");
936  }
937  $sheet2 = $sheet1;
938  }
939 
940  // assume all references belong to this document
941  $supbook_index = 0x00;
942  $ref = pack('vvv', $supbook_index, $sheet1, $sheet2);
943  $total_references = count($this->_references);
944  $index = -1;
945  for ($i = 0; $i < $total_references; $i++) {
946  if ($ref == $this->_references[$i]) {
947  $index = $i;
948  break;
949  }
950  }
951  // if REF was not found add it to references array
952  if ($index == -1) {
953  $this->_references[$total_references] = $ref;
954  $index = $total_references;
955  }
956 
957  return pack('v', $index);
958  }
959 
968  function _getSheetIndex($sheet_name)
969  {
970  if (!isset($this->_ext_sheets[$sheet_name])) {
971  return -1;
972  } else {
973  return $this->_ext_sheets[$sheet_name];
974  }
975  }
976 
987  function setExtSheet($name, $index)
988  {
989  $this->_ext_sheets[$name] = $index;
990  }
991 
999  function _cellToPackedRowcol($cell)
1000  {
1001  $cell = strtoupper($cell);
1002  list($row, $col, $row_rel, $col_rel) = $this->_cellToRowcol($cell);
1003  if ($col >= 256) {
1004  return $this->raiseError("Column in: $cell greater than 255");
1005  }
1006  // FIXME: change for BIFF8
1007  if ($row >= 16384) {
1008  return $this->raiseError("Row in: $cell greater than 16384 ");
1009  }
1010 
1011  // Set the high bits to indicate if row or col are relative.
1012  if ($this->_BIFF_version == 0x0500) {
1013  $row |= $col_rel << 14;
1014  $row |= $row_rel << 15;
1015  $col = pack('C', $col);
1016  } elseif ($this->_BIFF_version == 0x0600) {
1017  $col |= $col_rel << 14;
1018  $col |= $row_rel << 15;
1019  $col = pack('v', $col);
1020  }
1021  $row = pack('v', $row);
1022 
1023  return array($row, $col);
1024  }
1025 
1034  function _rangeToPackedRange($range)
1035  {
1036  preg_match('/(\$)?(\d+)\:(\$)?(\d+)/', $range, $match);
1037  // return absolute rows if there is a $ in the ref
1038  $row1_rel = empty($match[1]) ? 1 : 0;
1039  $row1 = $match[2];
1040  $row2_rel = empty($match[3]) ? 1 : 0;
1041  $row2 = $match[4];
1042  // Convert 1-index to zero-index
1043  $row1--;
1044  $row2--;
1045  // Trick poor inocent Excel
1046  $col1 = 0;
1047  $col2 = 16383; // FIXME: maximum possible value for Excel 5 (change this!!!)
1048 
1049  // FIXME: this changes for BIFF8
1050  if (($row1 >= 16384) or ($row2 >= 16384)) {
1051  return $this->raiseError("Row in: $range greater than 16384 ");
1052  }
1053 
1054  // Set the high bits to indicate if rows are relative.
1055  if ($this->_BIFF_version == 0x0500) {
1056  $row1 |= $row1_rel << 14; // FIXME: probably a bug
1057  $row2 |= $row2_rel << 15;
1058  $col1 = pack('C', $col1);
1059  $col2 = pack('C', $col2);
1060  } elseif ($this->_BIFF_version == 0x0600) {
1061  $col1 |= $row1_rel << 15;
1062  $col2 |= $row2_rel << 15;
1063  $col1 = pack('v', $col1);
1064  $col2 = pack('v', $col2);
1065  }
1066  $row1 = pack('v', $row1);
1067  $row2 = pack('v', $row2);
1068 
1069  return array($row1, $col1, $row2, $col2);
1070  }
1071 
1081  function _cellToRowcol($cell)
1082  {
1083  preg_match('/(\$)?([A-I]?[A-Z])(\$)?(\d+)/',$cell,$match);
1084  // return absolute column if there is a $ in the ref
1085  $col_rel = empty($match[1]) ? 1 : 0;
1086  $col_ref = $match[2];
1087  $row_rel = empty($match[3]) ? 1 : 0;
1088  $row = $match[4];
1089 
1090  // Convert base26 column string to a number.
1091  $expn = strlen($col_ref) - 1;
1092  $col = 0;
1093  $col_ref_length = strlen($col_ref);
1094  for ($i = 0; $i < $col_ref_length; $i++) {
1095  $col += (ord($col_ref{$i}) - ord('A') + 1) * pow(26, $expn);
1096  $expn--;
1097  }
1098 
1099  // Convert 1-index to zero-index
1100  $row--;
1101  $col--;
1102 
1103  return array($row, $col, $row_rel, $col_rel);
1104  }
1105 
1111  function _advance()
1112  {
1114  $formula_length = strlen($this->_formula);
1115  // eat up white spaces
1116  if ($i < $formula_length) {
1117  while ($this->_formula{$i} == " ") {
1118  $i++;
1119  }
1120 
1121  if ($i < ($formula_length - 1)) {
1122  $this->_lookahead = $this->_formula{$i+1};
1123  }
1124  $token = '';
1125  }
1126 
1127  while ($i < $formula_length) {
1128  $token .= $this->_formula{$i};
1129  if ($i < ($formula_length - 1)) {
1130  $this->_lookahead = $this->_formula{$i+1};
1131  } else {
1132  $this->_lookahead = '';
1133  }
1134 
1135  if ($this->_match($token) != '') {
1136  //if ($i < strlen($this->_formula) - 1) {
1137  // $this->_lookahead = $this->_formula{$i+1};
1138  //}
1139  $this->_current_char = $i + 1;
1140  $this->_current_token = $token;
1141  return 1;
1142  }
1143 
1144  if ($i < ($formula_length - 2)) {
1145  $this->_lookahead = $this->_formula{$i+2};
1146  } else { // if we run out of characters _lookahead becomes empty
1147  $this->_lookahead = '';
1148  }
1149  $i++;
1150  }
1151  //die("Lexical error ".$this->_current_char);
1152  }
1153 
1161  function _match($token)
1162  {
1163  switch($token) {
1165  return $token;
1166  break;
1168  return $token;
1169  break;
1171  return $token;
1172  break;
1174  return $token;
1175  break;
1177  return $token;
1178  break;
1180  return $token;
1181  break;
1183  return $token;
1184  break;
1186  return $token;
1187  break;
1189  if ($this->_lookahead == '=') { // it's a GE token
1190  break;
1191  }
1192  return $token;
1193  break;
1195  // it's a LE or a NE token
1196  if (($this->_lookahead == '=') or ($this->_lookahead == '>')) {
1197  break;
1198  }
1199  return $token;
1200  break;
1202  return $token;
1203  break;
1205  return $token;
1206  break;
1208  return $token;
1209  break;
1211  return $token;
1212  break;
1214  return $token;
1215  break;
1216  default:
1217  // if it's a reference
1218  if (preg_match('/^\$?[A-Ia-i]?[A-Za-z]\$?[0-9]+$/',$token) and
1219  !preg_match("/[0-9]/",$this->_lookahead) and
1220  ($this->_lookahead != ':') and ($this->_lookahead != '.') and
1221  ($this->_lookahead != '!'))
1222  {
1223  return $token;
1224  }
1225  // If it's an external reference (Sheet1!A1 or Sheet1:Sheet2!A1)
1226  elseif (preg_match("/^\w+(\:\w+)?\![A-Ia-i]?[A-Za-z][0-9]+$/u",$token) and
1227  !preg_match("/[0-9]/",$this->_lookahead) and
1228  ($this->_lookahead != ':') and ($this->_lookahead != '.'))
1229  {
1230  return $token;
1231  }
1232  // If it's an external reference ('Sheet1'!A1 or 'Sheet1:Sheet2'!A1)
1233  elseif (preg_match("/^'[\w -]+(\:[\w -]+)?'\![A-Ia-i]?[A-Za-z][0-9]+$/u",$token) and
1234  !preg_match("/[0-9]/",$this->_lookahead) and
1235  ($this->_lookahead != ':') and ($this->_lookahead != '.'))
1236  {
1237  return $token;
1238  }
1239  // if it's a range (A1:A2)
1240  elseif (preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+:(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+$/",$token) and
1241  !preg_match("/[0-9]/",$this->_lookahead))
1242  {
1243  return $token;
1244  }
1245  // if it's a range (A1..A2)
1246  elseif (preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+\.\.(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+$/",$token) and
1247  !preg_match("/[0-9]/",$this->_lookahead))
1248  {
1249  return $token;
1250  }
1251  // If it's an external range like Sheet1!A1 or Sheet1:Sheet2!A1:B2
1252  elseif (preg_match("/^\w+(\:\w+)?\!([A-Ia-i]?[A-Za-z])?[0-9]+:([A-Ia-i]?[A-Za-z])?[0-9]+$/u",$token) and
1253  !preg_match("/[0-9]/",$this->_lookahead))
1254  {
1255  return $token;
1256  }
1257  // If it's an external range like 'Sheet1'!A1 or 'Sheet1:Sheet2'!A1:B2
1258  elseif (preg_match("/^'[\w -]+(\:[\w -]+)?'\!([A-Ia-i]?[A-Za-z])?[0-9]+:([A-Ia-i]?[A-Za-z])?[0-9]+$/u",$token) and
1259  !preg_match("/[0-9]/",$this->_lookahead))
1260  {
1261  return $token;
1262  }
1263  // If it's a number (check that it's not a sheet name or range)
1264  elseif (is_numeric($token) and
1265  (!is_numeric($token.$this->_lookahead) or ($this->_lookahead == '')) and
1266  ($this->_lookahead != '!') and ($this->_lookahead != ':'))
1267  {
1268  return $token;
1269  }
1270  // If it's a string (of maximum 255 characters)
1271  elseif (preg_match("/^\"[^\"]{0,255}\"$/",$token))
1272  {
1273  return $token;
1274  }
1275  // if it's a function call
1276  elseif (preg_match("/^[A-Z0-9\xc0-\xdc\.]+$/i",$token) and ($this->_lookahead == "("))
1277  {
1278  return $token;
1279  }
1280  return '';
1281  }
1282  }
1283 
1292  function parse($formula)
1293  {
1294  $this->_current_char = 0;
1295  $this->_formula = $formula;
1296  $this->_lookahead = $formula{1};
1297  $this->_advance();
1298  $this->_parse_tree = $this->_condition();
1299  if (PEAR::isError($this->_parse_tree)) {
1300  return $this->_parse_tree;
1301  }
1302  return true;
1303  }
1304 
1312  function _condition()
1313  {
1314  $result = $this->_expression();
1315  if (PEAR::isError($result)) {
1316  return $result;
1317  }
1318  if ($this->_current_token == SPREADSHEET_EXCEL_WRITER_LT) {
1319  $this->_advance();
1320  $result2 = $this->_expression();
1321  if (PEAR::isError($result2)) {
1322  return $result2;
1323  }
1324  $result = $this->_createTree('ptgLT', $result, $result2);
1325  } elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_GT) {
1326  $this->_advance();
1327  $result2 = $this->_expression();
1328  if (PEAR::isError($result2)) {
1329  return $result2;
1330  }
1331  $result = $this->_createTree('ptgGT', $result, $result2);
1332  } elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_LE) {
1333  $this->_advance();
1334  $result2 = $this->_expression();
1335  if (PEAR::isError($result2)) {
1336  return $result2;
1337  }
1338  $result = $this->_createTree('ptgLE', $result, $result2);
1339  } elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_GE) {
1340  $this->_advance();
1341  $result2 = $this->_expression();
1342  if (PEAR::isError($result2)) {
1343  return $result2;
1344  }
1345  $result = $this->_createTree('ptgGE', $result, $result2);
1346  } elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_EQ) {
1347  $this->_advance();
1348  $result2 = $this->_expression();
1349  if (PEAR::isError($result2)) {
1350  return $result2;
1351  }
1352  $result = $this->_createTree('ptgEQ', $result, $result2);
1353  } elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_NE) {
1354  $this->_advance();
1355  $result2 = $this->_expression();
1356  if (PEAR::isError($result2)) {
1357  return $result2;
1358  }
1359  $result = $this->_createTree('ptgNE', $result, $result2);
1360  } elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_CONCAT) {
1361  $this->_advance();
1362  $result2 = $this->_expression();
1363  if (PEAR::isError($result2)) {
1364  return $result2;
1365  }
1366  $result = $this->_createTree('ptgConcat', $result, $result2);
1367  }
1368  return $result;
1369  }
1370 
1380  function _expression()
1381  {
1382  // If it's a string return a string node
1383  if (preg_match("/^\"[^\"]{0,255}\"$/", $this->_current_token)) {
1384  $result = $this->_createTree($this->_current_token, '', '');
1385  $this->_advance();
1386  return $result;
1387  } elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_SUB) {
1388  // catch "-" Term
1389  $this->_advance();
1390  $result2 = $this->_expression();
1391  $result = $this->_createTree('ptgUminus', $result2, '');
1392  return $result;
1393  }
1394  $result = $this->_term();
1395  if (PEAR::isError($result)) {
1396  return $result;
1397  }
1398  while (($this->_current_token == SPREADSHEET_EXCEL_WRITER_ADD) or
1399  ($this->_current_token == SPREADSHEET_EXCEL_WRITER_SUB)) {
1400 
1401  if ($this->_current_token == SPREADSHEET_EXCEL_WRITER_ADD) {
1402  $this->_advance();
1403  $result2 = $this->_term();
1404  if (PEAR::isError($result2)) {
1405  return $result2;
1406  }
1407  $result = $this->_createTree('ptgAdd', $result, $result2);
1408  } else {
1409  $this->_advance();
1410  $result2 = $this->_term();
1411  if (PEAR::isError($result2)) {
1412  return $result2;
1413  }
1414  $result = $this->_createTree('ptgSub', $result, $result2);
1415  }
1416  }
1417  return $result;
1418  }
1419 
1429  {
1430  $result = $this->_createTree('ptgParen', $this->_expression(), '');
1431  return $result;
1432  }
1433 
1441  function _term()
1442  {
1443  $result = $this->_fact();
1444  if (PEAR::isError($result)) {
1445  return $result;
1446  }
1447  while (($this->_current_token == SPREADSHEET_EXCEL_WRITER_MUL) or
1448  ($this->_current_token == SPREADSHEET_EXCEL_WRITER_DIV)) {
1449 
1450  if ($this->_current_token == SPREADSHEET_EXCEL_WRITER_MUL) {
1451  $this->_advance();
1452  $result2 = $this->_fact();
1453  if (PEAR::isError($result2)) {
1454  return $result2;
1455  }
1456  $result = $this->_createTree('ptgMul', $result, $result2);
1457  } else {
1458  $this->_advance();
1459  $result2 = $this->_fact();
1460  if (PEAR::isError($result2)) {
1461  return $result2;
1462  }
1463  $result = $this->_createTree('ptgDiv', $result, $result2);
1464  }
1465  }
1466  return $result;
1467  }
1468 
1480  function _fact()
1481  {
1482  if ($this->_current_token == SPREADSHEET_EXCEL_WRITER_OPEN) {
1483  $this->_advance(); // eat the "("
1484  $result = $this->_parenthesizedExpression();
1485  if ($this->_current_token != SPREADSHEET_EXCEL_WRITER_CLOSE) {
1486  return $this->raiseError("')' token expected.");
1487  }
1488  $this->_advance(); // eat the ")"
1489  return $result;
1490  }
1491  // if it's a reference
1492  if (preg_match('/^\$?[A-Ia-i]?[A-Za-z]\$?[0-9]+$/',$this->_current_token))
1493  {
1494  $result = $this->_createTree($this->_current_token, '', '');
1495  $this->_advance();
1496  return $result;
1497  }
1498  // If it's an external reference (Sheet1!A1 or Sheet1:Sheet2!A1)
1499  elseif (preg_match("/^\w+(\:\w+)?\![A-Ia-i]?[A-Za-z][0-9]+$/u",$this->_current_token))
1500  {
1501  $result = $this->_createTree($this->_current_token, '', '');
1502  $this->_advance();
1503  return $result;
1504  }
1505  // If it's an external reference ('Sheet1'!A1 or 'Sheet1:Sheet2'!A1)
1506  elseif (preg_match("/^'[\w -]+(\:[\w -]+)?'\![A-Ia-i]?[A-Za-z][0-9]+$/u",$this->_current_token))
1507  {
1508  $result = $this->_createTree($this->_current_token, '', '');
1509  $this->_advance();
1510  return $result;
1511  }
1512  // if it's a range
1513  elseif (preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+:(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+$/",$this->_current_token) or
1514  preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+\.\.(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+$/",$this->_current_token))
1515  {
1517  $this->_advance();
1518  return $result;
1519  }
1520  // If it's an external range (Sheet1!A1 or Sheet1!A1:B2)
1521  elseif (preg_match("/^\w+(\:\w+)?\!([A-Ia-i]?[A-Za-z])?[0-9]+:([A-Ia-i]?[A-Za-z])?[0-9]+$/u",$this->_current_token))
1522  {
1524  $this->_advance();
1525  return $result;
1526  }
1527  // If it's an external range ('Sheet1'!A1 or 'Sheet1'!A1:B2)
1528  elseif (preg_match("/^'[\w -]+(\:[\w -]+)?'\!([A-Ia-i]?[A-Za-z])?[0-9]+:([A-Ia-i]?[A-Za-z])?[0-9]+$/u",$this->_current_token))
1529  {
1531  $this->_advance();
1532  return $result;
1533  }
1534  elseif (is_numeric($this->_current_token))
1535  {
1536  $result = $this->_createTree($this->_current_token, '', '');
1537  $this->_advance();
1538  return $result;
1539  }
1540  // if it's a function call
1541  elseif (preg_match("/^[A-Z0-9\xc0-\xdc\.]+$/i",$this->_current_token))
1542  {
1543  $result = $this->_func();
1544  return $result;
1545  }
1546  return $this->raiseError("Syntax error: ".$this->_current_token.
1547  ", lookahead: ".$this->_lookahead.
1548  ", current char: ".$this->_current_char);
1549  }
1550 
1558  function _func()
1559  {
1560  $num_args = 0; // number of arguments received
1561  $function = strtoupper($this->_current_token);
1562  $result = ''; // initialize result
1563  $this->_advance();
1564  $this->_advance(); // eat the "("
1565  while ($this->_current_token != ')') {
1566 
1567  if ($num_args > 0) {
1568  if ($this->_current_token == SPREADSHEET_EXCEL_WRITER_COMA or
1569  $this->_current_token == SPREADSHEET_EXCEL_WRITER_SEMICOLON)
1570  {
1571  $this->_advance(); // eat the "," or ";"
1572  } else {
1573  return $this->raiseError("Syntax error: comma expected in ".
1574  "function $function, arg #{$num_args}");
1575  }
1576  $result2 = $this->_condition();
1577  if (PEAR::isError($result2)) {
1578  return $result2;
1579  }
1580  $result = $this->_createTree('arg', $result, $result2);
1581  } else { // first argument
1582  $result2 = $this->_condition();
1583  if (PEAR::isError($result2)) {
1584  return $result2;
1585  }
1586  $result = $this->_createTree('arg', '', $result2);
1587  }
1588  $num_args++;
1589  }
1590  if (!isset($this->_functions[$function])) {
1591  return $this->raiseError("Function $function() doesn't exist");
1592  }
1593  $args = $this->_functions[$function][1];
1594  // If fixed number of args eg. TIME($i,$j,$k). Check that the number of args is valid.
1595  if (($args >= 0) and ($args != $num_args)) {
1596  return $this->raiseError("Incorrect number of arguments in function $function() ");
1597  }
1598 
1599  $result = $this->_createTree($function, $result, $num_args);
1600  $this->_advance(); // eat the ")"
1601  return $result;
1602  }
1603 
1614  function _createTree($value, $left, $right)
1615  {
1616  return array('value' => $value, 'left' => $left, 'right' => $right);
1617  }
1618 
1646  function toReversePolish($tree = array())
1647  {
1648  $polish = ""; // the string we are going to return
1649  if (empty($tree)) { // If it's the first call use _parse_tree
1650  $tree = $this->_parse_tree;
1651  }
1652  if (is_array($tree['left'])) {
1653  $converted_tree = $this->toReversePolish($tree['left']);
1654  if (PEAR::isError($converted_tree)) {
1655  return $converted_tree;
1656  }
1657  $polish .= $converted_tree;
1658  } elseif ($tree['left'] != '') { // It's a final node
1659  $converted_tree = $this->_convert($tree['left']);
1660  if (PEAR::isError($converted_tree)) {
1661  return $converted_tree;
1662  }
1663  $polish .= $converted_tree;
1664  }
1665  if (is_array($tree['right'])) {
1666  $converted_tree = $this->toReversePolish($tree['right']);
1667  if (PEAR::isError($converted_tree)) {
1668  return $converted_tree;
1669  }
1670  $polish .= $converted_tree;
1671  } elseif ($tree['right'] != '') { // It's a final node
1672  $converted_tree = $this->_convert($tree['right']);
1673  if (PEAR::isError($converted_tree)) {
1674  return $converted_tree;
1675  }
1676  $polish .= $converted_tree;
1677  }
1678  // if it's a function convert it here (so we can set it's arguments)
1679  if (preg_match("/^[A-Z0-9\xc0-\xdc\.]+$/",$tree['value']) and
1680  !preg_match('/^([A-Ia-i]?[A-Za-z])(\d+)$/',$tree['value']) and
1681  !preg_match("/^[A-Ia-i]?[A-Za-z](\d+)\.\.[A-Ia-i]?[A-Za-z](\d+)$/",$tree['value']) and
1682  !is_numeric($tree['value']) and
1683  !isset($this->ptg[$tree['value']]))
1684  {
1685  // left subtree for a function is always an array.
1686  if ($tree['left'] != '') {
1687  $left_tree = $this->toReversePolish($tree['left']);
1688  } else {
1689  $left_tree = '';
1690  }
1691  if (PEAR::isError($left_tree)) {
1692  return $left_tree;
1693  }
1694  // add it's left subtree and return.
1695  return $left_tree.$this->_convertFunction($tree['value'], $tree['right']);
1696  } else {
1697  $converted_tree = $this->_convert($tree['value']);
1698  if (PEAR::isError($converted_tree)) {
1699  return $converted_tree;
1700  }
1701  }
1702  $polish .= $converted_tree;
1703  return $polish;
1704  }
1705 }
1706 
_getRefIndex($ext_ref)
Definition: parser.php:910
$i
Definition: images.php:25
_packExtRef($ext_ref)
Definition: parser.php:864
$result
Definition: api.php:36
const SPREADSHEET_EXCEL_WRITER_OPEN
Definition: parser.php:51
const SPREADSHEET_EXCEL_WRITER_MUL
Definition: parser.php:41
const SPREADSHEET_EXCEL_WRITER_CLOSE
Definition: parser.php:56
const SPREADSHEET_EXCEL_WRITER_LE
Definition: parser.php:81
parse($formula)
Definition: parser.php:1292
const SPREADSHEET_EXCEL_WRITER_GT
Definition: parser.php:71
_createTree($value, $left, $right)
Definition: parser.php:1614
_convertString($string)
Definition: parser.php:627
_convert($token)
Definition: parser.php:546
const SPREADSHEET_EXCEL_WRITER_NE
Definition: parser.php:96
setExtSheet($name, $index)
Definition: parser.php:987
const SPREADSHEET_EXCEL_WRITER_GE
Definition: parser.php:86
_cellToPackedRowcol($cell)
Definition: parser.php:999
_convertRange2d($range, $class=0)
Definition: parser.php:673
_convertRef2d($cell)
Definition: parser.php:786
const SPREADSHEET_EXCEL_WRITER_SUB
Definition: parser.php:36
_convertNumber($num)
Definition: parser.php:606
_cellToRowcol($cell)
Definition: parser.php:1081
const SPREADSHEET_EXCEL_WRITER_LT
Definition: parser.php:76
const SPREADSHEET_EXCEL_WRITER_SEMICOLON
Definition: parser.php:66
__construct($byte_order, $biff_version)
Definition: parser.php:175
_getSheetIndex($sheet_name)
Definition: parser.php:968
const SPREADSHEET_EXCEL_WRITER_EQ
Definition: parser.php:91
const SPREADSHEET_EXCEL_WRITER_COMA
Definition: parser.php:61
const SPREADSHEET_EXCEL_WRITER_CONCAT
Definition: parser.php:101
const SPREADSHEET_EXCEL_WRITER_DIV
Definition: parser.php:46
_convertRange3d($token)
Definition: parser.php:722
_convertRef3d($cell)
Definition: parser.php:819
_convertFunction($token, $num_args)
Definition: parser.php:652
toReversePolish($tree=array())
Definition: parser.php:1646
_rangeToPackedRange($range)
Definition: parser.php:1034
const SPREADSHEET_EXCEL_WRITER_ADD
Definition: parser.php:31