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 
?>