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