Subversion Repositories eFlore/Applications.cel

Rev

Rev 1604 | 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
*/
1604 raphael 28
define('SPREADSHEET_EXCEL_WRITER_ADD', "+");
418 aurelien 29
 
30
/**
31
* @const SPREADSHEET_EXCEL_WRITER_SUB token identifier for character "-"
32
*/
1604 raphael 33
define('SPREADSHEET_EXCEL_WRITER_SUB', "-");
418 aurelien 34
 
35
/**
36
* @const SPREADSHEET_EXCEL_WRITER_MUL token identifier for character "*"
37
*/
1604 raphael 38
define('SPREADSHEET_EXCEL_WRITER_MUL', "*");
418 aurelien 39
 
40
/**
41
* @const SPREADSHEET_EXCEL_WRITER_DIV token identifier for character "/"
42
*/
1604 raphael 43
define('SPREADSHEET_EXCEL_WRITER_DIV', "/");
418 aurelien 44
 
45
/**
46
* @const SPREADSHEET_EXCEL_WRITER_OPEN token identifier for character "("
47
*/
1604 raphael 48
define('SPREADSHEET_EXCEL_WRITER_OPEN', "(");
418 aurelien 49
 
50
/**
51
* @const SPREADSHEET_EXCEL_WRITER_CLOSE token identifier for character ")"
52
*/
1604 raphael 53
define('SPREADSHEET_EXCEL_WRITER_CLOSE', ")");
418 aurelien 54
 
55
/**
56
* @const SPREADSHEET_EXCEL_WRITER_COMA token identifier for character ","
57
*/
1604 raphael 58
define('SPREADSHEET_EXCEL_WRITER_COMA', ",");
418 aurelien 59
 
60
/**
1604 raphael 61
* @const SPREADSHEET_EXCEL_WRITER_SEMICOLON token identifier for character ";"
62
*/
63
define('SPREADSHEET_EXCEL_WRITER_SEMICOLON', ";");
64
 
65
/**
418 aurelien 66
* @const SPREADSHEET_EXCEL_WRITER_GT token identifier for character ">"
67
*/
1604 raphael 68
define('SPREADSHEET_EXCEL_WRITER_GT', ">");
418 aurelien 69
 
70
/**
71
* @const SPREADSHEET_EXCEL_WRITER_LT token identifier for character "<"
72
*/
1604 raphael 73
define('SPREADSHEET_EXCEL_WRITER_LT', "<");
418 aurelien 74
 
75
/**
76
* @const SPREADSHEET_EXCEL_WRITER_LE token identifier for character "<="
77
*/
1604 raphael 78
define('SPREADSHEET_EXCEL_WRITER_LE', "<=");
418 aurelien 79
 
80
/**
81
* @const SPREADSHEET_EXCEL_WRITER_GE token identifier for character ">="
82
*/
1604 raphael 83
define('SPREADSHEET_EXCEL_WRITER_GE', ">=");
418 aurelien 84
 
85
/**
86
* @const SPREADSHEET_EXCEL_WRITER_EQ token identifier for character "="
87
*/
1604 raphael 88
define('SPREADSHEET_EXCEL_WRITER_EQ', "=");
418 aurelien 89
 
90
/**
91
* @const SPREADSHEET_EXCEL_WRITER_NE token identifier for character "<>"
92
*/
1604 raphael 93
define('SPREADSHEET_EXCEL_WRITER_NE', "<>");
418 aurelien 94
 
1604 raphael 95
/**
96
* * @const SPREADSHEET_EXCEL_WRITER_CONCAT token identifier for character "&"
97
*/
98
define('SPREADSHEET_EXCEL_WRITER_CONCAT', "&");
418 aurelien 99
 
1604 raphael 100
require_once 'PEAR.php';
418 aurelien 101
 
102
/**
103
* Class for parsing Excel formulas
104
*
105
* @author   Xavier Noguer <xnoguer@rezebra.com>
106
* @category FileFormats
107
* @package  Spreadsheet_Excel_Writer
108
*/
109
 
110
class Spreadsheet_Excel_Writer_Parser extends PEAR
111
{
112
    /**
113
    * The index of the character we are currently looking at
114
    * @var integer
115
    */
116
    var $_current_char;
117
 
118
    /**
119
    * The token we are working on.
120
    * @var string
121
    */
122
    var $_current_token;
123
 
124
    /**
125
    * The formula to parse
126
    * @var string
127
    */
128
    var $_formula;
129
 
130
    /**
131
    * The character ahead of the current char
132
    * @var string
133
    */
134
    var $_lookahead;
135
 
136
    /**
137
    * The parse tree to be generated
138
    * @var string
139
    */
140
    var $_parse_tree;
141
 
142
    /**
143
    * The byte order. 1 => big endian, 0 => little endian.
144
    * @var integer
145
    */
146
    var $_byte_order;
147
 
148
    /**
1604 raphael 149
    * Array of external sheets
150
    * @var array
418 aurelien 151
    */
1604 raphael 152
    var $_ext_sheets;
418 aurelien 153
 
154
    /**
1604 raphael 155
    * Array of sheet references in the form of REF structures
418 aurelien 156
    * @var array
157
    */
1604 raphael 158
    var $_references;
418 aurelien 159
 
160
    /**
1604 raphael 161
    * The BIFF version for the workbook
162
    * @var integer
163
    */
164
    var $_BIFF_version;
165
 
166
    /**
418 aurelien 167
    * The class constructor
168
    *
169
    * @param integer $byte_order The byte order (Little endian or Big endian) of the architecture
1604 raphael 170
                                 (optional). 1 => big endian, 0 (default) little endian.
418 aurelien 171
    */
1604 raphael 172
    function Spreadsheet_Excel_Writer_Parser($byte_order, $biff_version)
418 aurelien 173
    {
174
        $this->_current_char  = 0;
1604 raphael 175
        $this->_BIFF_version  = $biff_version;
418 aurelien 176
        $this->_current_token = '';       // The token we are working on.
1604 raphael 177
        $this->_formula       = '';       // The formula to parse.
418 aurelien 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();
1604 raphael 183
        $this->_references = array();
418 aurelien 184
    }
1604 raphael 185
 
418 aurelien 186
    /**
1604 raphael 187
    * Initialize the ptg and function hashes.
418 aurelien 188
    *
189
    * @access private
190
    */
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
            );
1604 raphael 291
 
418 aurelien 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
    }
1604 raphael 534
 
418 aurelien 535
    /**
536
    * Convert a token to the proper ptg value.
537
    *
538
    * @access private
539
    * @param mixed $token The token to convert.
1604 raphael 540
    * @return mixed the converted token on success. PEAR_Error if the token
541
    *               is not recognized
418 aurelien 542
    */
543
    function _convert($token)
544
    {
1604 raphael 545
        if (preg_match("/^\"[^\"]{0,255}\"$/", $token)) {
418 aurelien 546
            return $this->_convertString($token);
1604 raphael 547
 
548
        } elseif (is_numeric($token)) {
418 aurelien 549
            return $this->_convertNumber($token);
1604 raphael 550
 
418 aurelien 551
        // match references like A1 or $A$1
1604 raphael 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)) {
418 aurelien 557
            return $this->_convertRef3d($token);
1604 raphael 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
 
418 aurelien 563
        // match ranges like A1:B2
1604 raphael 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
 
418 aurelien 567
        // match ranges like A1..B2
1604 raphael 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)) {
418 aurelien 573
            return $this->_convertRange3d($token);
1604 raphael 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))
418 aurelien 585
        {
586
            return($this->_convertFunction($token,$this->_func_args));
1604 raphael 587
        }*/
588
 
418 aurelien 589
        // if it's an argument, ignore the token (the argument remains)
1604 raphael 590
        } elseif ($token == 'arg') {
591
            return '';
418 aurelien 592
        }
593
        // TODO: use real error codes
1604 raphael 594
        return $this->raiseError("Unknown token $token");
418 aurelien 595
    }
1604 raphael 596
 
418 aurelien 597
    /**
598
    * Convert a number token to ptgInt or ptgNum
599
    *
600
    * @access private
601
    * @param mixed $num an integer or double for conversion to its ptg value
602
    */
603
    function _convertNumber($num)
604
    {
605
        // Integer in the range 0..2**16-1
1604 raphael 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
418 aurelien 610
                $num = strrev($num);
611
            }
1604 raphael 612
            return pack("Cd", $this->ptg['ptgNum'], $num);
418 aurelien 613
        }
614
    }
1604 raphael 615
 
418 aurelien 616
    /**
617
    * Convert a string token to ptgStr
618
    *
619
    * @access private
1604 raphael 620
    * @param string $string A string for conversion to its ptg value.
621
    * @return mixed the converted token on success. PEAR_Error if the string
622
    *               is longer than 255 characters.
418 aurelien 623
    */
624
    function _convertString($string)
625
    {
626
        // chop away beggining and ending quotes
627
        $string = substr($string, 1, strlen($string) - 2);
1604 raphael 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
        }
418 aurelien 638
    }
1604 raphael 639
 
418 aurelien 640
    /**
641
    * Convert a function to a ptgFunc or ptgFuncVarV depending on the number of
642
    * args that it takes.
643
    *
644
    * @access private
645
    * @param string  $token    The name of the function for convertion to ptg value.
1604 raphael 646
    * @param integer $num_args The number of arguments the function receives.
647
    * @return string The packed ptg for the function
418 aurelien 648
    */
649
    function _convertFunction($token, $num_args)
650
    {
651
        $args     = $this->_functions[$token][1];
652
        $volatile = $this->_functions[$token][3];
1604 raphael 653
 
418 aurelien 654
        // Fixed number of args eg. TIME($i,$j,$k).
655
        if ($args >= 0) {
1604 raphael 656
            return pack("Cv", $this->ptg['ptgFuncV'], $this->_functions[$token][0]);
418 aurelien 657
        }
658
        // Variable number of args eg. SUM($i,$j,$k, ..).
659
        if ($args == -1) {
1604 raphael 660
            return pack("CCv", $this->ptg['ptgFuncVarV'], $num_args, $this->_functions[$token][0]);
418 aurelien 661
        }
662
    }
1604 raphael 663
 
418 aurelien 664
    /**
665
    * Convert an Excel range such as A1:D4 to a ptgRefV.
666
    *
667
    * @access private
668
    * @param string $range An Excel range in the A1:A2 or A1..A2 format.
669
    */
1604 raphael 670
    function _convertRange2d($range, $class=0)
418 aurelien 671
    {
1604 raphael 672
 
673
        // TODO: possible class value 0,1,2 check Formula.pm
418 aurelien 674
        // Split the range into 2 cell refs
1604 raphael 675
        if (preg_match("/^([A-Ia-i]?[A-Za-z])(\d+)\:([A-Ia-i]?[A-Za-z])(\d+)$/", $range)) {
996 aurelien 676
            list($cell1, $cell2) = explode(':', $range);
1604 raphael 677
        } elseif (preg_match("/^([A-Ia-i]?[A-Za-z])(\d+)\.\.([A-Ia-i]?[A-Za-z])(\d+)$/", $range)) {
678
            list($cell1, $cell2) = explode('..', $range);
679
 
680
        } else {
418 aurelien 681
            // TODO: use real error codes
1604 raphael 682
            return $this->raiseError("Unknown range separator", 0, PEAR_ERROR_DIE);
418 aurelien 683
        }
1604 raphael 684
 
418 aurelien 685
        // Convert the cell references
686
        $cell_array1 = $this->_cellToPackedRowcol($cell1);
1604 raphael 687
        if (PEAR::isError($cell_array1)) {
688
            return $cell_array1;
689
        }
690
        list($row1, $col1) = $cell_array1;
418 aurelien 691
        $cell_array2 = $this->_cellToPackedRowcol($cell2);
1604 raphael 692
        if (PEAR::isError($cell_array2)) {
693
            return $cell_array2;
694
        }
695
        list($row2, $col2) = $cell_array2;
696
 
418 aurelien 697
        // The ptg value depends on the class of the ptg.
698
        if ($class == 0) {
699
            $ptgArea = pack("C", $this->ptg['ptgArea']);
1604 raphael 700
        } elseif ($class == 1) {
418 aurelien 701
            $ptgArea = pack("C", $this->ptg['ptgAreaV']);
1604 raphael 702
        } elseif ($class == 2) {
418 aurelien 703
            $ptgArea = pack("C", $this->ptg['ptgAreaA']);
1604 raphael 704
        } else {
418 aurelien 705
            // TODO: use real error codes
1604 raphael 706
            return $this->raiseError("Unknown class $class", 0, PEAR_ERROR_DIE);
418 aurelien 707
        }
1604 raphael 708
        return $ptgArea . $row1 . $row2 . $col1. $col2;
418 aurelien 709
    }
1604 raphael 710
 
418 aurelien 711
    /**
712
    * Convert an Excel 3d range such as "Sheet1!A1:D4" or "Sheet1:Sheet2!A1:D4" to
1604 raphael 713
    * a ptgArea3d.
418 aurelien 714
    *
715
    * @access private
716
    * @param string $token An Excel range in the Sheet1!A1:A2 format.
1604 raphael 717
    * @return mixed The packed ptgArea3d token on success, PEAR_Error on failure.
418 aurelien 718
    */
719
    function _convertRange3d($token)
720
    {
721
        $class = 2; // as far as I know, this is magick.
1604 raphael 722
 
418 aurelien 723
        // Split the ref at the ! symbol
996 aurelien 724
        list($ext_ref, $range) = explode('!', $token);
1604 raphael 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
             }
418 aurelien 737
        }
1604 raphael 738
 
418 aurelien 739
        // Split the range into 2 cell refs
996 aurelien 740
        list($cell1, $cell2) = explode(':', $range);
1604 raphael 741
 
418 aurelien 742
        // Convert the cell references
1604 raphael 743
        if (preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?(\d+)$/", $cell1)) {
418 aurelien 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;
1604 raphael 754
        } else { // It's a rows range (like 26:27)
418 aurelien 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
        }
1604 raphael 761
 
418 aurelien 762
        // The ptg value depends on the class of the ptg.
763
        if ($class == 0) {
764
            $ptgArea = pack("C", $this->ptg['ptgArea3d']);
1604 raphael 765
        } elseif ($class == 1) {
418 aurelien 766
            $ptgArea = pack("C", $this->ptg['ptgArea3dV']);
1604 raphael 767
        } elseif ($class == 2) {
418 aurelien 768
            $ptgArea = pack("C", $this->ptg['ptgArea3dA']);
1604 raphael 769
        } else {
770
            return $this->raiseError("Unknown class $class", 0, PEAR_ERROR_DIE);
418 aurelien 771
        }
1604 raphael 772
 
418 aurelien 773
        return $ptgArea . $ext_ref . $row1 . $row2 . $col1. $col2;
774
    }
775
 
776
    /**
777
    * Convert an Excel reference such as A1, $B2, C$3 or $D$4 to a ptgRefV.
778
    *
779
    * @access private
780
    * @param string $cell An Excel cell reference
781
    * @return string The cell in packed() format with the corresponding ptg
782
    */
783
    function _convertRef2d($cell)
784
    {
785
        $class = 2; // as far as I know, this is magick.
1604 raphael 786
 
418 aurelien 787
        // Convert the cell reference
788
        $cell_array = $this->_cellToPackedRowcol($cell);
1604 raphael 789
        if (PEAR::isError($cell_array)) {
790
            return $cell_array;
791
        }
418 aurelien 792
        list($row, $col) = $cell_array;
1604 raphael 793
 
418 aurelien 794
        // The ptg value depends on the class of the ptg.
795
        if ($class == 0) {
796
            $ptgRef = pack("C", $this->ptg['ptgRef']);
1604 raphael 797
        } elseif ($class == 1) {
418 aurelien 798
            $ptgRef = pack("C", $this->ptg['ptgRefV']);
1604 raphael 799
        } elseif ($class == 2) {
418 aurelien 800
            $ptgRef = pack("C", $this->ptg['ptgRefA']);
1604 raphael 801
        } else {
418 aurelien 802
            // TODO: use real error codes
1604 raphael 803
            return $this->raiseError("Unknown class $class");
418 aurelien 804
        }
1604 raphael 805
        return $ptgRef.$row.$col;
418 aurelien 806
    }
1604 raphael 807
 
418 aurelien 808
    /**
809
    * Convert an Excel 3d reference such as "Sheet1!A1" or "Sheet1:Sheet2!A1" to a
1604 raphael 810
    * ptgRef3d.
418 aurelien 811
    *
812
    * @access private
813
    * @param string $cell An Excel cell reference
1604 raphael 814
    * @return mixed The packed ptgRef3d token on success, PEAR_Error on failure.
418 aurelien 815
    */
816
    function _convertRef3d($cell)
817
    {
818
        $class = 2; // as far as I know, this is magick.
1604 raphael 819
 
418 aurelien 820
        // Split the ref at the ! symbol
996 aurelien 821
        list($ext_ref, $cell) = explode('!', $cell);
1604 raphael 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
            }
418 aurelien 834
        }
1604 raphael 835
 
418 aurelien 836
        // Convert the cell reference part
837
        list($row, $col) = $this->_cellToPackedRowcol($cell);
1604 raphael 838
 
418 aurelien 839
        // The ptg value depends on the class of the ptg.
840
        if ($class == 0) {
841
            $ptgRef = pack("C", $this->ptg['ptgRef3d']);
1604 raphael 842
        } elseif ($class == 1) {
418 aurelien 843
            $ptgRef = pack("C", $this->ptg['ptgRef3dV']);
1604 raphael 844
        } elseif ($class == 2) {
418 aurelien 845
            $ptgRef = pack("C", $this->ptg['ptgRef3dA']);
1604 raphael 846
        } else {
847
            return $this->raiseError("Unknown class $class", 0, PEAR_ERROR_DIE);
418 aurelien 848
        }
849
 
850
        return $ptgRef . $ext_ref. $row . $col;
851
    }
852
 
853
    /**
854
    * Convert the sheet name part of an external reference, for example "Sheet1" or
855
    * "Sheet1:Sheet2", to a packed structure.
856
    *
857
    * @access private
858
    * @param string $ext_ref The name of the external reference
859
    * @return string The reference index in packed() format
860
    */
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.
1604 raphael 865
 
418 aurelien 866
        // Check if there is a sheet range eg., Sheet1:Sheet2.
1604 raphael 867
        if (preg_match("/:/", $ext_ref)) {
996 aurelien 868
            list($sheet_name1, $sheet_name2) = explode(':', $ext_ref);
1604 raphael 869
 
418 aurelien 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
            }
1604 raphael 878
 
418 aurelien 879
            // Reverse max and min sheet numbers if necessary
880
            if ($sheet1 > $sheet2) {
881
                list($sheet1, $sheet2) = array($sheet2, $sheet1);
882
            }
1604 raphael 883
        } else { // Single sheet name only.
418 aurelien 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
        }
1604 raphael 890
 
418 aurelien 891
        // References are stored relative to 0xFFFF.
892
        $offset = -1 - $sheet1;
893
 
894
        return pack('vdvv', $offset, 0x00, $sheet1, $sheet2);
895
    }
896
 
897
    /**
1604 raphael 898
    * Look up the REF index that corresponds to an external sheet name
899
    * (or range). If it doesn't exist yet add it to the workbook's references
900
    * array. It assumes all sheet names given must exist.
901
    *
902
    * @access private
903
    * @param string $ext_ref The name of the external reference
904
    * @return mixed The reference index in packed() format on success,
905
    *               PEAR_Error on failure
906
    */
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) = explode(':', $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
 
957
    /**
418 aurelien 958
    * Look up the index that corresponds to an external sheet name. The hash of
1604 raphael 959
    * sheet names is updated by the addworksheet() method of the
418 aurelien 960
    * Spreadsheet_Excel_Writer_Workbook class.
961
    *
962
    * @access private
1604 raphael 963
    * @return integer The sheet index, -1 if the sheet was not found
418 aurelien 964
    */
965
    function _getSheetIndex($sheet_name)
966
    {
967
        if (!isset($this->_ext_sheets[$sheet_name])) {
968
            return -1;
1604 raphael 969
        } else {
418 aurelien 970
            return $this->_ext_sheets[$sheet_name];
971
        }
972
    }
973
 
974
    /**
975
    * This method is used to update the array of sheet names. It is
1604 raphael 976
    * called by the addWorksheet() method of the
977
    * Spreadsheet_Excel_Writer_Workbook class.
418 aurelien 978
    *
1604 raphael 979
    * @access public
980
    * @see Spreadsheet_Excel_Writer_Workbook::addWorksheet()
418 aurelien 981
    * @param string  $name  The name of the worksheet being added
982
    * @param integer $index The index of the worksheet being added
983
    */
984
    function setExtSheet($name, $index)
985
    {
986
        $this->_ext_sheets[$name] = $index;
987
    }
988
 
989
    /**
1604 raphael 990
    * pack() row and column into the required 3 or 4 byte format.
418 aurelien 991
    *
992
    * @access private
993
    * @param string $cell The Excel cell reference to be packed
994
    * @return array Array containing the row and column in packed() format
995
    */
996
    function _cellToPackedRowcol($cell)
997
    {
1604 raphael 998
        $cell = strtoupper($cell);
418 aurelien 999
        list($row, $col, $row_rel, $col_rel) = $this->_cellToRowcol($cell);
1000
        if ($col >= 256) {
1604 raphael 1001
            return $this->raiseError("Column in: $cell greater than 255");
418 aurelien 1002
        }
1604 raphael 1003
        // FIXME: change for BIFF8
418 aurelien 1004
        if ($row >= 16384) {
1604 raphael 1005
            return $this->raiseError("Row in: $cell greater than 16384 ");
418 aurelien 1006
        }
1604 raphael 1007
 
418 aurelien 1008
        // Set the high bits to indicate if row or col are relative.
1604 raphael 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
        }
418 aurelien 1018
        $row     = pack('v', $row);
1604 raphael 1019
 
1020
        return array($row, $col);
418 aurelien 1021
    }
1604 raphael 1022
 
418 aurelien 1023
    /**
1604 raphael 1024
    * pack() row range into the required 3 or 4 byte format.
1025
    * Just using maximum col/rows, which is probably not the correct solution
418 aurelien 1026
    *
1027
    * @access private
1028
    * @param string $range The Excel range to be packed
1029
    * @return array Array containing (row1,col1,row2,col2) in packed() format
1030
    */
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;
1604 raphael 1044
        $col2 = 16383; // FIXME: maximum possible value for Excel 5 (change this!!!)
418 aurelien 1045
 
1604 raphael 1046
        // FIXME: this changes for BIFF8
418 aurelien 1047
        if (($row1 >= 16384) or ($row2 >= 16384)) {
1604 raphael 1048
            return $this->raiseError("Row in: $range greater than 16384 ");
418 aurelien 1049
        }
1604 raphael 1050
 
418 aurelien 1051
        // Set the high bits to indicate if rows are relative.
1604 raphael 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
        }
418 aurelien 1063
        $row1     = pack('v', $row1);
1064
        $row2     = pack('v', $row2);
1604 raphael 1065
 
418 aurelien 1066
        return array($row1, $col1, $row2, $col2);
1067
    }
1068
 
1069
    /**
1070
    * Convert an Excel cell reference such as A1 or $B2 or C$3 or $D$4 to a zero
1071
    * indexed row and column number. Also returns two (0,1) values to indicate
1072
    * whether the row or column are relative references.
1073
    *
1074
    * @access private
1075
    * @param string $cell The Excel cell reference in A1 format.
1076
    * @return array
1077
    */
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];
1604 raphael 1086
 
418 aurelien 1087
        // Convert base26 column string to a number.
1088
        $expn   = strlen($col_ref) - 1;
1089
        $col    = 0;
1604 raphael 1090
        $col_ref_length = strlen($col_ref);
1091
        for ($i = 0; $i < $col_ref_length; $i++) {
418 aurelien 1092
            $col += (ord($col_ref{$i}) - ord('A') + 1) * pow(26, $expn);
1093
            $expn--;
1094
        }
1604 raphael 1095
 
418 aurelien 1096
        // Convert 1-index to zero-index
1097
        $row--;
1098
        $col--;
1604 raphael 1099
 
1100
        return array($row, $col, $row_rel, $col_rel);
418 aurelien 1101
    }
1604 raphael 1102
 
418 aurelien 1103
    /**
1104
    * Advance to the next valid token.
1105
    *
1106
    * @access private
1107
    */
1108
    function _advance()
1109
    {
1110
        $i = $this->_current_char;
1604 raphael 1111
        $formula_length = strlen($this->_formula);
418 aurelien 1112
        // eat up white spaces
1604 raphael 1113
        if ($i < $formula_length) {
1114
            while ($this->_formula{$i} == " ") {
418 aurelien 1115
                $i++;
1116
            }
1604 raphael 1117
 
1118
            if ($i < ($formula_length - 1)) {
418 aurelien 1119
                $this->_lookahead = $this->_formula{$i+1};
1120
            }
1604 raphael 1121
            $token = '';
418 aurelien 1122
        }
1604 raphael 1123
 
1124
        while ($i < $formula_length) {
418 aurelien 1125
            $token .= $this->_formula{$i};
1604 raphael 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
                //}
418 aurelien 1136
                $this->_current_char = $i + 1;
1137
                $this->_current_token = $token;
1604 raphael 1138
                return 1;
418 aurelien 1139
            }
1604 raphael 1140
 
1141
            if ($i < ($formula_length - 2)) {
418 aurelien 1142
                $this->_lookahead = $this->_formula{$i+2};
1604 raphael 1143
            } else { // if we run out of characters _lookahead becomes empty
418 aurelien 1144
                $this->_lookahead = '';
1145
            }
1146
            $i++;
1147
        }
1148
        //die("Lexical error ".$this->_current_char);
1149
    }
1604 raphael 1150
 
418 aurelien 1151
    /**
1152
    * Checks if it's a valid token.
1153
    *
1154
    * @access private
1155
    * @param mixed $token The token to check.
1156
    * @return mixed       The checked token or false on failure
1157
    */
1158
    function _match($token)
1159
    {
1604 raphael 1160
        switch($token) {
418 aurelien 1161
            case SPREADSHEET_EXCEL_WRITER_ADD:
1604 raphael 1162
                return $token;
418 aurelien 1163
                break;
1164
            case SPREADSHEET_EXCEL_WRITER_SUB:
1604 raphael 1165
                return $token;
418 aurelien 1166
                break;
1167
            case SPREADSHEET_EXCEL_WRITER_MUL:
1604 raphael 1168
                return $token;
418 aurelien 1169
                break;
1170
            case SPREADSHEET_EXCEL_WRITER_DIV:
1604 raphael 1171
                return $token;
418 aurelien 1172
                break;
1173
            case SPREADSHEET_EXCEL_WRITER_OPEN:
1604 raphael 1174
                return $token;
418 aurelien 1175
                break;
1176
            case SPREADSHEET_EXCEL_WRITER_CLOSE:
1604 raphael 1177
                return $token;
418 aurelien 1178
                break;
1179
            case SPREADSHEET_EXCEL_WRITER_COMA:
1604 raphael 1180
                return $token;
418 aurelien 1181
                break;
1604 raphael 1182
            case SPREADSHEET_EXCEL_WRITER_SEMICOLON:
1183
                return $token;
1184
                break;
418 aurelien 1185
            case SPREADSHEET_EXCEL_WRITER_GT:
1186
                if ($this->_lookahead == '=') { // it's a GE token
1187
                    break;
1188
                }
1604 raphael 1189
                return $token;
418 aurelien 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
                }
1604 raphael 1196
                return $token;
418 aurelien 1197
                break;
1198
            case SPREADSHEET_EXCEL_WRITER_GE:
1604 raphael 1199
                return $token;
418 aurelien 1200
                break;
1201
            case SPREADSHEET_EXCEL_WRITER_LE:
1604 raphael 1202
                return $token;
418 aurelien 1203
                break;
1204
            case SPREADSHEET_EXCEL_WRITER_EQ:
1604 raphael 1205
                return $token;
418 aurelien 1206
                break;
1207
            case SPREADSHEET_EXCEL_WRITER_NE:
1604 raphael 1208
                return $token;
418 aurelien 1209
                break;
1604 raphael 1210
            case SPREADSHEET_EXCEL_WRITER_CONCAT:
1211
                return $token;
1212
                break;
418 aurelien 1213
            default:
1214
                // if it's a reference
1604 raphael 1215
                if (preg_match('/^\$?[A-Ia-i]?[A-Za-z]\$?[0-9]+$/',$token) and
1216
                   !preg_match("/[0-9]/",$this->_lookahead) and
418 aurelien 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)
1604 raphael 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
418 aurelien 1225
                       ($this->_lookahead != ':') and ($this->_lookahead != '.'))
1226
                {
1227
                    return $token;
1228
                }
1604 raphael 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
                }
418 aurelien 1236
                // if it's a range (A1:A2)
1604 raphael 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))
418 aurelien 1239
                {
1240
                    return $token;
1241
                }
1242
                // if it's a range (A1..A2)
1604 raphael 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))
418 aurelien 1245
                {
1246
                    return $token;
1247
                }
1604 raphael 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))
418 aurelien 1251
                {
1252
                    return $token;
1253
                }
1604 raphael 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
                }
418 aurelien 1260
                // If it's a number (check that it's not a sheet name or range)
1604 raphael 1261
                elseif (is_numeric($token) and
1262
                        (!is_numeric($token.$this->_lookahead) or ($this->_lookahead == '')) and
1263
                        ($this->_lookahead != '!') and ($this->_lookahead != ':'))
418 aurelien 1264
                {
1265
                    return $token;
1266
                }
1267
                // If it's a string (of maximum 255 characters)
1604 raphael 1268
                elseif (preg_match("/^\"[^\"]{0,255}\"$/",$token))
418 aurelien 1269
                {
1604 raphael 1270
                    return $token;
418 aurelien 1271
                }
1272
                // if it's a function call
1604 raphael 1273
                elseif (preg_match("/^[A-Z0-9\xc0-\xdc\.]+$/i",$token) and ($this->_lookahead == "("))
418 aurelien 1274
                {
1604 raphael 1275
                    return $token;
418 aurelien 1276
                }
1277
                return '';
1278
        }
1279
    }
1604 raphael 1280
 
418 aurelien 1281
    /**
1282
    * The parsing method. It parses a formula.
1283
    *
1284
    * @access public
1604 raphael 1285
    * @param string $formula The formula to parse, without the initial equal
1286
    *                        sign (=).
1287
    * @return mixed true on success, PEAR_Error on failure
418 aurelien 1288
    */
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();
1604 raphael 1296
        if (PEAR::isError($this->_parse_tree)) {
418 aurelien 1297
            return $this->_parse_tree;
1298
        }
1604 raphael 1299
        return true;
418 aurelien 1300
    }
1604 raphael 1301
 
418 aurelien 1302
    /**
1303
    * It parses a condition. It assumes the following rule:
1304
    * Cond -> Expr [(">" | "<") Expr]
1305
    *
1306
    * @access private
1604 raphael 1307
    * @return mixed The parsed ptg'd tree on success, PEAR_Error on failure
418 aurelien 1308
    */
1309
    function _condition()
1310
    {
1311
        $result = $this->_expression();
1604 raphael 1312
        if (PEAR::isError($result)) {
418 aurelien 1313
            return $result;
1314
        }
1604 raphael 1315
        if ($this->_current_token == SPREADSHEET_EXCEL_WRITER_LT) {
418 aurelien 1316
            $this->_advance();
1317
            $result2 = $this->_expression();
1604 raphael 1318
            if (PEAR::isError($result2)) {
418 aurelien 1319
                return $result2;
1320
            }
1321
            $result = $this->_createTree('ptgLT', $result, $result2);
1604 raphael 1322
        } elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_GT) {
418 aurelien 1323
            $this->_advance();
1324
            $result2 = $this->_expression();
1604 raphael 1325
            if (PEAR::isError($result2)) {
418 aurelien 1326
                return $result2;
1327
            }
1328
            $result = $this->_createTree('ptgGT', $result, $result2);
1604 raphael 1329
        } elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_LE) {
418 aurelien 1330
            $this->_advance();
1331
            $result2 = $this->_expression();
1604 raphael 1332
            if (PEAR::isError($result2)) {
418 aurelien 1333
                return $result2;
1334
            }
1335
            $result = $this->_createTree('ptgLE', $result, $result2);
1604 raphael 1336
        } elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_GE) {
418 aurelien 1337
            $this->_advance();
1338
            $result2 = $this->_expression();
1604 raphael 1339
            if (PEAR::isError($result2)) {
418 aurelien 1340
                return $result2;
1341
            }
1342
            $result = $this->_createTree('ptgGE', $result, $result2);
1604 raphael 1343
        } elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_EQ) {
418 aurelien 1344
            $this->_advance();
1345
            $result2 = $this->_expression();
1604 raphael 1346
            if (PEAR::isError($result2)) {
418 aurelien 1347
                return $result2;
1348
            }
1349
            $result = $this->_createTree('ptgEQ', $result, $result2);
1604 raphael 1350
        } elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_NE) {
418 aurelien 1351
            $this->_advance();
1352
            $result2 = $this->_expression();
1604 raphael 1353
            if (PEAR::isError($result2)) {
418 aurelien 1354
                return $result2;
1355
            }
1356
            $result = $this->_createTree('ptgNE', $result, $result2);
1604 raphael 1357
        } elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_CONCAT) {
1358
            $this->_advance();
1359
            $result2 = $this->_expression();
1360
            if (PEAR::isError($result2)) {
1361
                return $result2;
418 aurelien 1362
        }
1604 raphael 1363
            $result = $this->_createTree('ptgConcat', $result, $result2);
1364
        }
418 aurelien 1365
        return $result;
1366
    }
1604 raphael 1367
 
418 aurelien 1368
    /**
1369
    * It parses a expression. It assumes the following rule:
1370
    * Expr -> Term [("+" | "-") Term]
1604 raphael 1371
    *      -> "string"
1372
    *      -> "-" Term
418 aurelien 1373
    *
1374
    * @access private
1604 raphael 1375
    * @return mixed The parsed ptg'd tree on success, PEAR_Error on failure
418 aurelien 1376
    */
1377
    function _expression()
1378
    {
1379
        // If it's a string return a string node
1604 raphael 1380
        if (preg_match("/^\"[^\"]{0,255}\"$/", $this->_current_token)) {
418 aurelien 1381
            $result = $this->_createTree($this->_current_token, '', '');
1382
            $this->_advance();
1604 raphael 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;
418 aurelien 1390
        }
1391
        $result = $this->_term();
1604 raphael 1392
        if (PEAR::isError($result)) {
1393
            return $result;
418 aurelien 1394
        }
1604 raphael 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) {
418 aurelien 1399
                $this->_advance();
1400
                $result2 = $this->_term();
1604 raphael 1401
                if (PEAR::isError($result2)) {
1402
                    return $result2;
418 aurelien 1403
                }
1404
                $result = $this->_createTree('ptgAdd', $result, $result2);
1604 raphael 1405
            } else {
418 aurelien 1406
                $this->_advance();
1407
                $result2 = $this->_term();
1604 raphael 1408
                if (PEAR::isError($result2)) {
1409
                    return $result2;
418 aurelien 1410
                }
1411
                $result = $this->_createTree('ptgSub', $result, $result2);
1412
            }
1413
        }
1604 raphael 1414
        return $result;
418 aurelien 1415
    }
1604 raphael 1416
 
418 aurelien 1417
    /**
1418
    * This function just introduces a ptgParen element in the tree, so that Excel
1419
    * doesn't get confused when working with a parenthesized formula afterwards.
1420
    *
1421
    * @access private
1422
    * @see _fact()
1604 raphael 1423
    * @return array The parsed ptg'd tree
418 aurelien 1424
    */
1425
    function _parenthesizedExpression()
1426
    {
1427
        $result = $this->_createTree('ptgParen', $this->_expression(), '');
1604 raphael 1428
        return $result;
418 aurelien 1429
    }
1604 raphael 1430
 
418 aurelien 1431
    /**
1432
    * It parses a term. It assumes the following rule:
1433
    * Term -> Fact [("*" | "/") Fact]
1434
    *
1435
    * @access private
1604 raphael 1436
    * @return mixed The parsed ptg'd tree on success, PEAR_Error on failure
418 aurelien 1437
    */
1438
    function _term()
1439
    {
1440
        $result = $this->_fact();
1604 raphael 1441
        if (PEAR::isError($result)) {
1442
            return $result;
418 aurelien 1443
        }
1604 raphael 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) {
418 aurelien 1448
                $this->_advance();
1449
                $result2 = $this->_fact();
1604 raphael 1450
                if (PEAR::isError($result2)) {
1451
                    return $result2;
418 aurelien 1452
                }
1453
                $result = $this->_createTree('ptgMul', $result, $result2);
1604 raphael 1454
            } else {
418 aurelien 1455
                $this->_advance();
1456
                $result2 = $this->_fact();
1604 raphael 1457
                if (PEAR::isError($result2)) {
1458
                    return $result2;
418 aurelien 1459
                }
1460
                $result = $this->_createTree('ptgDiv', $result, $result2);
1461
            }
1462
        }
1604 raphael 1463
        return $result;
418 aurelien 1464
    }
1604 raphael 1465
 
418 aurelien 1466
    /**
1467
    * It parses a factor. It assumes the following rule:
1468
    * Fact -> ( Expr )
1469
    *       | CellRef
1470
    *       | CellRange
1471
    *       | Number
1472
    *       | Function
1473
    *
1474
    * @access private
1604 raphael 1475
    * @return mixed The parsed ptg'd tree on success, PEAR_Error on failure
418 aurelien 1476
    */
1477
    function _fact()
1478
    {
1604 raphael 1479
        if ($this->_current_token == SPREADSHEET_EXCEL_WRITER_OPEN) {
418 aurelien 1480
            $this->_advance();         // eat the "("
1481
            $result = $this->_parenthesizedExpression();
1482
            if ($this->_current_token != SPREADSHEET_EXCEL_WRITER_CLOSE) {
1604 raphael 1483
                return $this->raiseError("')' token expected.");
418 aurelien 1484
            }
1485
            $this->_advance();         // eat the ")"
1486
            return $result;
1487
        }
1488
        // if it's a reference
1604 raphael 1489
        if (preg_match('/^\$?[A-Ia-i]?[A-Za-z]\$?[0-9]+$/',$this->_current_token))
418 aurelien 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)
1604 raphael 1496
        elseif (preg_match("/^\w+(\:\w+)?\![A-Ia-i]?[A-Za-z][0-9]+$/u",$this->_current_token))
418 aurelien 1497
        {
1498
            $result = $this->_createTree($this->_current_token, '', '');
1499
            $this->_advance();
1500
            return $result;
1501
        }
1604 raphael 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
        }
418 aurelien 1509
        // if it's a range
1604 raphael 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))
418 aurelien 1512
        {
1513
            $result = $this->_current_token;
1514
            $this->_advance();
1515
            return $result;
1516
        }
1604 raphael 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))
418 aurelien 1519
        {
1520
            $result = $this->_current_token;
1521
            $this->_advance();
1604 raphael 1522
            return $result;
418 aurelien 1523
        }
1604 raphael 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
        }
418 aurelien 1531
        elseif (is_numeric($this->_current_token))
1532
        {
1533
            $result = $this->_createTree($this->_current_token, '', '');
1534
            $this->_advance();
1604 raphael 1535
            return $result;
418 aurelien 1536
        }
1537
        // if it's a function call
1604 raphael 1538
        elseif (preg_match("/^[A-Z0-9\xc0-\xdc\.]+$/i",$this->_current_token))
418 aurelien 1539
        {
1540
            $result = $this->_func();
1604 raphael 1541
            return $result;
418 aurelien 1542
        }
1604 raphael 1543
        return $this->raiseError("Syntax error: ".$this->_current_token.
1544
                                 ", lookahead: ".$this->_lookahead.
1545
                                 ", current char: ".$this->_current_char);
418 aurelien 1546
    }
1604 raphael 1547
 
418 aurelien 1548
    /**
1549
    * It parses a function call. It assumes the following rule:
1550
    * Func -> ( Expr [,Expr]* )
1551
    *
1552
    * @access private
1604 raphael 1553
    * @return mixed The parsed ptg'd tree on success, PEAR_Error on failure
418 aurelien 1554
    */
1555
    function _func()
1556
    {
1557
        $num_args = 0; // number of arguments received
1604 raphael 1558
        $function = strtoupper($this->_current_token);
1559
        $result   = ''; // initialize result
418 aurelien 1560
        $this->_advance();
1561
        $this->_advance();         // eat the "("
1604 raphael 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}");
418 aurelien 1572
                }
1573
                $result2 = $this->_condition();
1604 raphael 1574
                if (PEAR::isError($result2)) {
1575
                    return $result2;
418 aurelien 1576
                }
1577
                $result = $this->_createTree('arg', $result, $result2);
1604 raphael 1578
            } else { // first argument
418 aurelien 1579
                $result2 = $this->_condition();
1604 raphael 1580
                if (PEAR::isError($result2)) {
1581
                    return $result2;
418 aurelien 1582
                }
1583
                $result = $this->_createTree('arg', '', $result2);
1584
            }
1585
            $num_args++;
1586
        }
1604 raphael 1587
        if (!isset($this->_functions[$function])) {
1588
            return $this->raiseError("Function $function() doesn't exist");
1589
        }
418 aurelien 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.
1604 raphael 1592
        if (($args >= 0) and ($args != $num_args)) {
1593
            return $this->raiseError("Incorrect number of arguments in function $function() ");
418 aurelien 1594
        }
1604 raphael 1595
 
1596
        $result = $this->_createTree($function, $result, $num_args);
418 aurelien 1597
        $this->_advance();         // eat the ")"
1604 raphael 1598
        return $result;
418 aurelien 1599
    }
1604 raphael 1600
 
418 aurelien 1601
    /**
1602
    * Creates a tree. In fact an array which may have one or two arrays (sub-trees)
1603
    * as elements.
1604
    *
1605
    * @access private
1606
    * @param mixed $value The value of this node.
1607
    * @param mixed $left  The left array (sub-tree) or a final node.
1608
    * @param mixed $right The right array (sub-tree) or a final node.
1604 raphael 1609
    * @return array A tree
418 aurelien 1610
    */
1611
    function _createTree($value, $left, $right)
1612
    {
1604 raphael 1613
        return array('value' => $value, 'left' => $left, 'right' => $right);
418 aurelien 1614
    }
1604 raphael 1615
 
418 aurelien 1616
    /**
1604 raphael 1617
    * Builds a string containing the tree in reverse polish notation (What you
418 aurelien 1618
    * would use in a HP calculator stack).
1619
    * The following tree:
1604 raphael 1620
    *
418 aurelien 1621
    *    +
1622
    *   / \
1623
    *  2   3
1624
    *
1625
    * produces: "23+"
1626
    *
1627
    * The following tree:
1628
    *
1629
    *    +
1630
    *   / \
1631
    *  3   *
1632
    *     / \
1633
    *    6   A1
1634
    *
1635
    * produces: "36A1*+"
1636
    *
1637
    * In fact all operands, functions, references, etc... are written as ptg's
1638
    *
1639
    * @access public
1640
    * @param array $tree The optional tree to convert.
1641
    * @return string The tree in reverse polish notation
1642
    */
1643
    function toReversePolish($tree = array())
1644
    {
1645
        $polish = ""; // the string we are going to return
1604 raphael 1646
        if (empty($tree)) { // If it's the first call use _parse_tree
418 aurelien 1647
            $tree = $this->_parse_tree;
1648
        }
1604 raphael 1649
        if (is_array($tree['left'])) {
418 aurelien 1650
            $converted_tree = $this->toReversePolish($tree['left']);
1604 raphael 1651
            if (PEAR::isError($converted_tree)) {
1652
                return $converted_tree;
1653
            }
418 aurelien 1654
            $polish .= $converted_tree;
1604 raphael 1655
        } elseif ($tree['left'] != '') { // It's a final node
418 aurelien 1656
            $converted_tree = $this->_convert($tree['left']);
1604 raphael 1657
            if (PEAR::isError($converted_tree)) {
1658
                return $converted_tree;
1659
            }
418 aurelien 1660
            $polish .= $converted_tree;
1661
        }
1604 raphael 1662
        if (is_array($tree['right'])) {
418 aurelien 1663
            $converted_tree = $this->toReversePolish($tree['right']);
1604 raphael 1664
            if (PEAR::isError($converted_tree)) {
1665
                return $converted_tree;
1666
            }
418 aurelien 1667
            $polish .= $converted_tree;
1604 raphael 1668
        } elseif ($tree['right'] != '') { // It's a final node
418 aurelien 1669
            $converted_tree = $this->_convert($tree['right']);
1604 raphael 1670
            if (PEAR::isError($converted_tree)) {
1671
                return $converted_tree;
1672
            }
418 aurelien 1673
            $polish .= $converted_tree;
1674
        }
1604 raphael 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 = '';
418 aurelien 1687
            }
1604 raphael 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
        }
418 aurelien 1699
        $polish .= $converted_tree;
1604 raphael 1700
        return $polish;
418 aurelien 1701
    }
1702
}
1703
?>