Subversion Repositories eFlore/Applications.cel

Rev

Rev 418 | Rev 996 | Go to most recent revision | Details | Compare with Previous | Last modification | View Log | RSS feed

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