lib/excel/Parser.php

   1 <?php
   2 /**
   3 *  Class for parsing Excel formulas
   4 *
   5 *  License Information:
   6 *
   7 *    Spreadsheet::WriteExcel:  A library for generating Excel Spreadsheets
   8 *    Copyright (C) 2002 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 ADD token identifier for character "+"
  27 */
  28 define('ADD',"+");
  29
  30 /**
  31 * @const SUB token identifier for character "-"
  32 */
  33 define('SUB',"-");
  34
  35 /**
  36 * @const EQUAL token identifier for character "="
  37 */
  38 define('EQUAL',"=");
  39
  40 /**
  41 * @const MUL token identifier for character "*"
  42 */
  43 define('MUL',"*");
  44
  45 /**
  46 * @const DIV token identifier for character "/"
  47 */
  48 define('DIV',"/");
  49
  50 /**
  51 * @const OPEN token identifier for character "("
  52 */
  53 define('OPEN',"(");
  54
  55 /**
  56 * @const CLOSE token identifier for character ")"
  57 */
  58 define('CLOSE',")");
  59
  60 /**
  61 * @const COMA token identifier for character ","
  62 */
  63 define('COMA',",");
  64
  65 /**
  66 * Class for parsing Excel formulas
  67 *
  68 * @author Xavier Noguer <xnoguer@rezebra.com>
  69 * @package Spreadsheet_WriteExcel
  70 */
  71 class Parser
  72   {
  73 /**
  74 * The class constructor
  75 *
  76 * @param integer $byte_order The byte order (Little endian or Big endian) of the architecture
  77                              (optional). 1 => big endian, 0 (default) => little endian.
  78 */
  79   function Parser($byte_order = 0)
  80     {
  81     $this->_current_char  = 0;        // The index of the character we are currently looking at.
  82     $this->_current_token = '';       // The token we are working on.
  83     $this->_formula       = "";       // The formula to parse.
  84     $this->_lookahead     = '';       // The character ahead of the current char.
  85     $this->_parse_tree    = '';       // The parse tree to be generated.
  86     $this->_initialize_hashes();      // Initialize the hashes: ptg's and function's ptg's
  87     $this->_byte_order = $byte_order; // Little Endian or Big Endian
  88     $this->_func_args  = 0;           // Number of arguments for the current function
  89     $this->_volatile   = 0;
  90     }
  91
  92 /**
  93 * Initialize the ptg and function hashes.
  94 */
  95   function _initialize_hashes()
  96     {
  97     // The Excel ptg indices
  98     $this->ptg = array(
  99         'ptgExp'       => 0x01,
 100         'ptgTbl'       => 0x02,
 101         'ptgAdd'       => 0x03,
 102         'ptgSub'       => 0x04,
 103         'ptgMul'       => 0x05,
 104         'ptgDiv'       => 0x06,
 105         'ptgPower'     => 0x07,
 106         'ptgConcat'    => 0x08,
 107         'ptgLT'        => 0x09,
 108         'ptgLE'        => 0x0A,
 109         'ptgEQ'        => 0x0B,
 110         'ptgGE'        => 0x0C,
 111         'ptgGT'        => 0x0D,
 112         'ptgNE'        => 0x0E,
 113         'ptgIsect'     => 0x0F,
 114         'ptgUnion'     => 0x10,
 115         'ptgRange'     => 0x11,
 116         'ptgUplus'     => 0x12,
 117         'ptgUminus'    => 0x13,
 118         'ptgPercent'   => 0x14,
 119         'ptgParen'     => 0x15,
 120         'ptgMissArg'   => 0x16,
 121         'ptgStr'       => 0x17,
 122         'ptgAttr'      => 0x19,
 123         'ptgSheet'     => 0x1A,
 124         'ptgEndSheet'  => 0x1B,
 125         'ptgErr'       => 0x1C,
 126         'ptgBool'      => 0x1D,
 127         'ptgInt'       => 0x1E,
 128         'ptgNum'       => 0x1F,
 129         'ptgArray'     => 0x20,
 130         'ptgFunc'      => 0x21,
 131         'ptgFuncVar'   => 0x22,
 132         'ptgName'      => 0x23,
 133         'ptgRef'       => 0x24,
 134         'ptgArea'      => 0x25,
 135         'ptgMemArea'   => 0x26,
 136         'ptgMemErr'    => 0x27,
 137         'ptgMemNoMem'  => 0x28,
 138         'ptgMemFunc'   => 0x29,
 139         'ptgRefErr'    => 0x2A,
 140         'ptgAreaErr'   => 0x2B,
 141         'ptgRefN'      => 0x2C,
 142         'ptgAreaN'     => 0x2D,
 143         'ptgMemAreaN'  => 0x2E,
 144         'ptgMemNoMemN' => 0x2F,
 145         'ptgNameX'     => 0x39,
 146         'ptgRef3d'     => 0x3A,
 147         'ptgArea3d'    => 0x3B,
 148         'ptgRefErr3d'  => 0x3C,
 149         'ptgAreaErr3d' => 0x3D,
 150         'ptgArrayV'    => 0x40,
 151         'ptgFuncV'     => 0x41,
 152         'ptgFuncVarV'  => 0x42,
 153         'ptgNameV'     => 0x43,
 154         'ptgRefV'      => 0x44,
 155         'ptgAreaV'     => 0x45,
 156         'ptgMemAreaV'  => 0x46,
 157         'ptgMemErrV'   => 0x47,
 158         'ptgMemNoMemV' => 0x48,
 159         'ptgMemFuncV'  => 0x49,
 160         'ptgRefErrV'   => 0x4A,
 161         'ptgAreaErrV'  => 0x4B,
 162         'ptgRefNV'     => 0x4C,
 163         'ptgAreaNV'    => 0x4D,
 164         'ptgMemAreaNV' => 0x4E,
 165         'ptgMemNoMemN' => 0x4F,
 166         'ptgFuncCEV'   => 0x58,
 167         'ptgNameXV'    => 0x59,
 168         'ptgRef3dV'    => 0x5A,
 169         'ptgArea3dV'   => 0x5B,
 170         'ptgRefErr3dV' => 0x5C,
 171         'ptgAreaErr3d' => 0x5D,
 172         'ptgArrayA'    => 0x60,
 173         'ptgFuncA'     => 0x61,
 174         'ptgFuncVarA'  => 0x62,
 175         'ptgNameA'     => 0x63,
 176         'ptgRefA'      => 0x64,
 177         'ptgAreaA'     => 0x65,
 178         'ptgMemAreaA'  => 0x66,
 179         'ptgMemErrA'   => 0x67,
 180         'ptgMemNoMemA' => 0x68,
 181         'ptgMemFuncA'  => 0x69,
 182         'ptgRefErrA'   => 0x6A,
 183         'ptgAreaErrA'  => 0x6B,
 184         'ptgRefNA'     => 0x6C,
 185         'ptgAreaNA'    => 0x6D,
 186         'ptgMemAreaNA' => 0x6E,
 187         'ptgMemNoMemN' => 0x6F,
 188         'ptgFuncCEA'   => 0x78,
 189         'ptgNameXA'    => 0x79,
 190         'ptgRef3dA'    => 0x7A,
 191         'ptgArea3dA'   => 0x7B,
 192         'ptgRefErr3dA' => 0x7C,
 193         'ptgAreaErr3d' => 0x7D
 194         );
 195
 196     // Thanks to Michael Meeks and Gnumeric for the initial arg values.
 197     //
 198     // The following hash was generated by "function_locale.pl" in the distro.
 199     // Refer to function_locale.pl for non-English function names.
 200     //
 201     // The array elements are as follow:
 202     // ptg:   The Excel function ptg code.
 203     // args:  The number of arguments that the function takes:
 204     //           >=0 is a fixed number of arguments.
 205     //           -1  is a variable  number of arguments.
 206     // class: The reference, value or array class of the function args.
 207     // vol:   The function is volatile.
 208     //
 209     $this->_functions = array(
 210           // function                  ptg  args  class  vol
 211           'COUNT'           => array(   0,   -1,    0,    0 ),
 212           'IF'              => array(   1,   -1,    1,    0 ),
 213           'ISNA'            => array(   2,    1,    1,    0 ),
 214           'ISERROR'         => array(   3,    1,    1,    0 ),
 215           'SUM'             => array(   4,   -1,    0,    0 ),
 216           'AVERAGE'         => array(   5,   -1,    0,    0 ),
 217           'MIN'             => array(   6,   -1,    0,    0 ),
 218           'MAX'             => array(   7,   -1,    0,    0 ),
 219           'ROW'             => array(   8,   -1,    0,    0 ),
 220           'COLUMN'          => array(   9,   -1,    0,    0 ),
 221           'NA'              => array(  10,    0,    0,    0 ),
 222           'NPV'             => array(  11,   -1,    1,    0 ),
 223           'STDEV'           => array(  12,   -1,    0,    0 ),
 224           'DOLLAR'          => array(  13,   -1,    1,    0 ),
 225           'FIXED'           => array(  14,   -1,    1,    0 ),
 226           'SIN'             => array(  15,    1,    1,    0 ),
 227           'COS'             => array(  16,    1,    1,    0 ),
 228           'TAN'             => array(  17,    1,    1,    0 ),
 229           'ATAN'            => array(  18,    1,    1,    0 ),
 230           'PI'              => array(  19,    0,    1,    0 ),
 231           'SQRT'            => array(  20,    1,    1,    0 ),
 232           'EXP'             => array(  21,    1,    1,    0 ),
 233           'LN'              => array(  22,    1,    1,    0 ),
 234           'LOG10'           => array(  23,    1,    1,    0 ),
 235           'ABS'             => array(  24,    1,    1,    0 ),
 236           'INT'             => array(  25,    1,    1,    0 ),
 237           'SIGN'            => array(  26,    1,    1,    0 ),
 238           'ROUND'           => array(  27,    2,    1,    0 ),
 239           'LOOKUP'          => array(  28,   -1,    0,    0 ),
 240           'INDEX'           => array(  29,   -1,    0,    1 ),
 241           'REPT'            => array(  30,    2,    1,    0 ),
 242           'MID'             => array(  31,    3,    1,    0 ),
 243           'LEN'             => array(  32,    1,    1,    0 ),
 244           'VALUE'           => array(  33,    1,    1,    0 ),
 245           'TRUE'            => array(  34,    0,    1,    0 ),
 246           'FALSE'           => array(  35,    0,    1,    0 ),
 247           'AND'             => array(  36,   -1,    0,    0 ),
 248           'OR'              => array(  37,   -1,    0,    0 ),
 249           'NOT'             => array(  38,    1,    1,    0 ),
 250           'MOD'             => array(  39,    2,    1,    0 ),
 251           'DCOUNT'          => array(  40,    3,    0,    0 ),
 252           'DSUM'            => array(  41,    3,    0,    0 ),
 253           'DAVERAGE'        => array(  42,    3,    0,    0 ),
 254           'DMIN'            => array(  43,    3,    0,    0 ),
 255           'DMAX'            => array(  44,    3,    0,    0 ),
 256           'DSTDEV'          => array(  45,    3,    0,    0 ),
 257           'VAR'             => array(  46,   -1,    0,    0 ),
 258           'DVAR'            => array(  47,    3,    0,    0 ),
 259           'TEXT'            => array(  48,    2,    1,    0 ),
 260           'LINEST'          => array(  49,   -1,    0,    0 ),
 261           'TREND'           => array(  50,   -1,    0,    0 ),
 262           'LOGEST'          => array(  51,   -1,    0,    0 ),
 263           'GROWTH'          => array(  52,   -1,    0,    0 ),
 264           'PV'              => array(  56,   -1,    1,    0 ),
 265           'FV'              => array(  57,   -1,    1,    0 ),
 266           'NPER'            => array(  58,   -1,    1,    0 ),
 267           'PMT'             => array(  59,   -1,    1,    0 ),
 268           'RATE'            => array(  60,   -1,    1,    0 ),
 269           'MIRR'            => array(  61,    3,    0,    0 ),
 270           'IRR'             => array(  62,   -1,    0,    0 ),
 271           'RAND'            => array(  63,    0,    1,    1 ),
 272           'MATCH'           => array(  64,   -1,    0,    0 ),
 273           'DATE'            => array(  65,    3,    1,    0 ),
 274           'TIME'            => array(  66,    3,    1,    0 ),
 275           'DAY'             => array(  67,    1,    1,    0 ),
 276           'MONTH'           => array(  68,    1,    1,    0 ),
 277           'YEAR'            => array(  69,    1,    1,    0 ),
 278           'WEEKDAY'         => array(  70,   -1,    1,    0 ),
 279           'HOUR'            => array(  71,    1,    1,    0 ),
 280           'MINUTE'          => array(  72,    1,    1,    0 ),
 281           'SECOND'          => array(  73,    1,    1,    0 ),
 282           'NOW'             => array(  74,    0,    1,    1 ),
 283           'AREAS'           => array(  75,    1,    0,    1 ),
 284           'ROWS'            => array(  76,    1,    0,    1 ),
 285           'COLUMNS'         => array(  77,    1,    0,    1 ),
 286           'OFFSET'          => array(  78,   -1,    0,    1 ),
 287           'SEARCH'          => array(  82,   -1,    1,    0 ),
 288           'TRANSPOSE'       => array(  83,    1,    1,    0 ),
 289           'TYPE'            => array(  86,    1,    1,    0 ),
 290           'ATAN2'           => array(  97,    2,    1,    0 ),
 291           'ASIN'            => array(  98,    1,    1,    0 ),
 292           'ACOS'            => array(  99,    1,    1,    0 ),
 293           'CHOOSE'          => array( 100,   -1,    1,    0 ),
 294           'HLOOKUP'         => array( 101,   -1,    0,    0 ),
 295           'VLOOKUP'         => array( 102,   -1,    0,    0 ),
 296           'ISREF'           => array( 105,    1,    0,    0 ),
 297           'LOG'             => array( 109,   -1,    1,    0 ),
 298           'CHAR'            => array( 111,    1,    1,    0 ),
 299           'LOWER'           => array( 112,    1,    1,    0 ),
 300           'UPPER'           => array( 113,    1,    1,    0 ),
 301           'PROPER'          => array( 114,    1,    1,    0 ),
 302           'LEFT'            => array( 115,   -1,    1,    0 ),
 303           'RIGHT'           => array( 116,   -1,    1,    0 ),
 304           'EXACT'           => array( 117,    2,    1,    0 ),
 305           'TRIM'            => array( 118,    1,    1,    0 ),
 306           'REPLACE'         => array( 119,    4,    1,    0 ),
 307           'SUBSTITUTE'      => array( 120,   -1,    1,    0 ),
 308           'CODE'            => array( 121,    1,    1,    0 ),
 309           'FIND'            => array( 124,   -1,    1,    0 ),
 310           'CELL'            => array( 125,   -1,    0,    1 ),
 311           'ISERR'           => array( 126,    1,    1,    0 ),
 312           'ISTEXT'          => array( 127,    1,    1,    0 ),
 313           'ISNUMBER'        => array( 128,    1,    1,    0 ),
 314           'ISBLANK'         => array( 129,    1,    1,    0 ),
 315           'T'               => array( 130,    1,    0,    0 ),
 316           'N'               => array( 131,    1,    0,    0 ),
 317           'DATEVALUE'       => array( 140,    1,    1,    0 ),
 318           'TIMEVALUE'       => array( 141,    1,    1,    0 ),
 319           'SLN'             => array( 142,    3,    1,    0 ),
 320           'SYD'             => array( 143,    4,    1,    0 ),
 321           'DDB'             => array( 144,   -1,    1,    0 ),
 322           'INDIRECT'        => array( 148,   -1,    1,    1 ),
 323           'CALL'            => array( 150,   -1,    1,    0 ),
 324           'CLEAN'           => array( 162,    1,    1,    0 ),
 325           'MDETERM'         => array( 163,    1,    2,    0 ),
 326           'MINVERSE'        => array( 164,    1,    2,    0 ),
 327           'MMULT'           => array( 165,    2,    2,    0 ),
 328           'IPMT'            => array( 167,   -1,    1,    0 ),
 329           'PPMT'            => array( 168,   -1,    1,    0 ),
 330           'COUNTA'          => array( 169,   -1,    0,    0 ),
 331           'PRODUCT'         => array( 183,   -1,    0,    0 ),
 332           'FACT'            => array( 184,    1,    1,    0 ),
 333           'DPRODUCT'        => array( 189,    3,    0,    0 ),
 334           'ISNONTEXT'       => array( 190,    1,    1,    0 ),
 335           'STDEVP'          => array( 193,   -1,    0,    0 ),
 336           'VARP'            => array( 194,   -1,    0,    0 ),
 337           'DSTDEVP'         => array( 195,    3,    0,    0 ),
 338           'DVARP'           => array( 196,    3,    0,    0 ),
 339           'TRUNC'           => array( 197,   -1,    1,    0 ),
 340           'ISLOGICAL'       => array( 198,    1,    1,    0 ),
 341           'DCOUNTA'         => array( 199,    3,    0,    0 ),
 342           'ROUNDUP'         => array( 212,    2,    1,    0 ),
 343           'ROUNDDOWN'       => array( 213,    2,    1,    0 ),
 344           'RANK'            => array( 216,   -1,    0,    0 ),
 345           'ADDRESS'         => array( 219,   -1,    1,    0 ),
 346           'DAYS360'         => array( 220,   -1,    1,    0 ),
 347           'TODAY'           => array( 221,    0,    1,    1 ),
 348           'VDB'             => array( 222,   -1,    1,    0 ),
 349           'MEDIAN'          => array( 227,   -1,    0,    0 ),
 350           'SUMPRODUCT'      => array( 228,   -1,    2,    0 ),
 351           'SINH'            => array( 229,    1,    1,    0 ),
 352           'COSH'            => array( 230,    1,    1,    0 ),
 353           'TANH'            => array( 231,    1,    1,    0 ),
 354           'ASINH'           => array( 232,    1,    1,    0 ),
 355           'ACOSH'           => array( 233,    1,    1,    0 ),
 356           'ATANH'           => array( 234,    1,    1,    0 ),
 357           'DGET'            => array( 235,    3,    0,    0 ),
 358           'INFO'            => array( 244,    1,    1,    1 ),
 359           'DB'              => array( 247,   -1,    1,    0 ),
 360           'FREQUENCY'       => array( 252,    2,    0,    0 ),
 361           'ERROR.TYPE'      => array( 261,    1,    1,    0 ),
 362           'REGISTER.ID'     => array( 267,   -1,    1,    0 ),
 363           'AVEDEV'          => array( 269,   -1,    0,    0 ),
 364           'BETADIST'        => array( 270,   -1,    1,    0 ),
 365           'GAMMALN'         => array( 271,    1,    1,    0 ),
 366           'BETAINV'         => array( 272,   -1,    1,    0 ),
 367           'BINOMDIST'       => array( 273,    4,    1,    0 ),
 368           'CHIDIST'         => array( 274,    2,    1,    0 ),
 369           'CHIINV'          => array( 275,    2,    1,    0 ),
 370           'COMBIN'          => array( 276,    2,    1,    0 ),
 371           'CONFIDENCE'      => array( 277,    3,    1,    0 ),
 372           'CRITBINOM'       => array( 278,    3,    1,    0 ),
 373           'EVEN'            => array( 279,    1,    1,    0 ),
 374           'EXPONDIST'       => array( 280,    3,    1,    0 ),
 375           'FDIST'           => array( 281,    3,    1,    0 ),
 376           'FINV'            => array( 282,    3,    1,    0 ),
 377           'FISHER'          => array( 283,    1,    1,    0 ),
 378           'FISHERINV'       => array( 284,    1,    1,    0 ),
 379           'FLOOR'           => array( 285,    2,    1,    0 ),
 380           'GAMMADIST'       => array( 286,    4,    1,    0 ),
 381           'GAMMAINV'        => array( 287,    3,    1,    0 ),
 382           'CEILING'         => array( 288,    2,    1,    0 ),
 383           'HYPGEOMDIST'     => array( 289,    4,    1,    0 ),
 384           'LOGNORMDIST'     => array( 290,    3,    1,    0 ),
 385           'LOGINV'          => array( 291,    3,    1,    0 ),
 386           'NEGBINOMDIST'    => array( 292,    3,    1,    0 ),
 387           'NORMDIST'        => array( 293,    4,    1,    0 ),
 388           'NORMSDIST'       => array( 294,    1,    1,    0 ),
 389           'NORMINV'         => array( 295,    3,    1,    0 ),
 390           'NORMSINV'        => array( 296,    1,    1,    0 ),
 391           'STANDARDIZE'     => array( 297,    3,    1,    0 ),
 392           'ODD'             => array( 298,    1,    1,    0 ),
 393           'PERMUT'          => array( 299,    2,    1,    0 ),
 394           'POISSON'         => array( 300,    3,    1,    0 ),
 395           'TDIST'           => array( 301,    3,    1,    0 ),
 396           'WEIBULL'         => array( 302,    4,    1,    0 ),
 397           'SUMXMY2'         => array( 303,    2,    2,    0 ),
 398           'SUMX2MY2'        => array( 304,    2,    2,    0 ),
 399           'SUMX2PY2'        => array( 305,    2,    2,    0 ),
 400           'CHITEST'         => array( 306,    2,    2,    0 ),
 401           'CORREL'          => array( 307,    2,    2,    0 ),
 402           'COVAR'           => array( 308,    2,    2,    0 ),
 403           'FORECAST'        => array( 309,    3,    2,    0 ),
 404           'FTEST'           => array( 310,    2,    2,    0 ),
 405           'INTERCEPT'       => array( 311,    2,    2,    0 ),
 406           'PEARSON'         => array( 312,    2,    2,    0 ),
 407           'RSQ'             => array( 313,    2,    2,    0 ),
 408           'STEYX'           => array( 314,    2,    2,    0 ),
 409           'SLOPE'           => array( 315,    2,    2,    0 ),
 410           'TTEST'           => array( 316,    4,    2,    0 ),
 411           'PROB'            => array( 317,   -1,    2,    0 ),
 412           'DEVSQ'           => array( 318,   -1,    0,    0 ),
 413           'GEOMEAN'         => array( 319,   -1,    0,    0 ),
 414           'HARMEAN'         => array( 320,   -1,    0,    0 ),
 415           'SUMSQ'           => array( 321,   -1,    0,    0 ),
 416           'KURT'            => array( 322,   -1,    0,    0 ),
 417           'SKEW'            => array( 323,   -1,    0,    0 ),
 418           'ZTEST'           => array( 324,   -1,    0,    0 ),
 419           'LARGE'           => array( 325,    2,    0,    0 ),
 420           'SMALL'           => array( 326,    2,    0,    0 ),
 421           'QUARTILE'        => array( 327,    2,    0,    0 ),
 422           'PERCENTILE'      => array( 328,    2,    0,    0 ),
 423           'PERCENTRANK'     => array( 329,   -1,    0,    0 ),
 424           'MODE'            => array( 330,   -1,    2,    0 ),
 425           'TRIMMEAN'        => array( 331,    2,    0,    0 ),
 426           'TINV'            => array( 332,    2,    1,    0 ),
 427           'CONCATENATE'     => array( 336,   -1,    1,    0 ),
 428           'POWER'           => array( 337,    2,    1,    0 ),
 429           'RADIANS'         => array( 342,    1,    1,    0 ),
 430           'DEGREES'         => array( 343,    1,    1,    0 ),
 431           'SUBTOTAL'        => array( 344,   -1,    0,    0 ),
 432           'SUMIF'           => array( 345,   -1,    0,    0 ),
 433           'COUNTIF'         => array( 346,    2,    0,    0 ),
 434           'COUNTBLANK'      => array( 347,    1,    0,    0 ),
 435           'ROMAN'           => array( 354,   -1,    1,    0 )
 436           );
 437     }
 438
 439 /**
 440 * Convert a token to the proper ptg value.
 441 *
 442 * @param mixed $token The token to convert.
 443 */
 444   function _convert($token)
 445     {
 446     if(is_numeric($token))
 447         {
 448         return($this->_convert_number($token));
 449         }
 450     // match references like A1
 451     elseif(preg_match("/^([A-I]?[A-Z])(\d+)$/",$token))
 452         {
 453         return($this->_convert_ref2d($token));
 454         }
 455     // match ranges like A1:B2
 456     elseif(preg_match("/^([A-I]?[A-Z])(\d+)\:([A-I]?[A-Z])(\d+)$/",$token))
 457         {
 458         return($this->_convert_range2d($token));
 459         }
 460     // match ranges like A1..B2
 461     elseif(preg_match("/^([A-I]?[A-Z])(\d+)\.\.([A-I]?[A-Z])(\d+)$/",$token))
 462         {
 463         return($this->_convert_range2d($token));
 464         }
 465     elseif(isset($this->ptg[$token])) // operators (including parentheses)
 466         {
 467         return(pack("C", $this->ptg[$token]));
 468         }
 469     elseif(preg_match("/[A-Z0-9�-�\.]+/",$token))
 470         {
 471         return($this->_convert_function($token,$this->_func_args));
 472         }
 473     // if it's an argument, ignore the token (the argument remains)
 474     elseif($token == 'arg')
 475         {
 476         $this->_func_args++;
 477         return('');
 478         }
 479     die("Unknown token $token");
 480     }
 481
 482 /**
 483 * Convert a number token to ptgInt or ptgNum
 484 *
 485 * @param mixed $num an integer or double for conersion to its ptg value
 486 */
 487   function _convert_number($num)
 488     {
 489     // Integer in the range 0..2**16-1
 490     if ((preg_match("/^\d+$/",$num)) and ($num <= 65535)) {
 491         return pack("Cv", $this->ptg['ptgInt'], $num);
 492         }
 493     else // A float
 494         {
 495         if($this->_byte_order) // if it's Big Endian
 496             {
 497             $num = strrev($num);
 498             }
 499         return pack("Cd", $this->ptg['ptgNum'], $num);
 500         }
 501     }
 502
 503 /**
 504 * Convert a function to a ptgFunc or ptgFuncVarV depending on the number of
 505 * args that it takes.
 506 *
 507 * @param string  $token    The name of the function for convertion to ptg value.
 508 * @param integer $num_args The number of arguments the function recieves.
 509 */
 510   function _convert_function($token, $num_args)
 511     {
 512     $this->_func_args = 0; // re initialize the number of arguments
 513     $args     = $this->_functions[$token][1];
 514     $volatile = $this->_functions[$token][3];
 515
 516     if($volatile) {
 517         $this->_volatile = 1;
 518         }
 519     // Fixed number of args eg. TIME($i,$j,$k).
 520     if ($args >= 0)
 521         {
 522         return(pack("Cv", $this->ptg['ptgFuncV'], $this->_functions[$token][0]));
 523         }
 524     // Variable number of args eg. SUM($i,$j,$k, ..).
 525     if ($args == -1) {
 526         return(pack("CCv", $this->ptg['ptgFuncVarV'], $num_args, $this->_functions[$token][0]));
 527         }
 528     }
 529
 530 /**
 531 * Convert an Excel range such as A1:D4 to a ptgRefV.
 532 *
 533 * @param string $range An Excel range in the A1:A2 or A1..A2 format.
 534 */
 535   function _convert_range2d($range)
 536     {
 537     $class = 2; // as far as I know, this is magick.
 538
 539     // Split the range into 2 cell refs
 540     if(preg_match("/^([A-I]?[A-Z])(\d+)\:([A-I]?[A-Z])(\d+)$/",$range)) {
 541         list($cell1, $cell2) = explode(':', $range);
 542         }
 543     elseif(preg_match("/^([A-I]?[A-Z])(\d+)\.\.([A-I]?[A-Z])(\d+)$/",$range)) {
 544         list($cell1, $cell2) = explode('..', $range);
 545         }
 546     else {
 547         die("Unknown range separator");
 548         }
 549
 550     // Convert the cell references
 551     list($row1, $col1) = $this->_cell_to_packed_rowcol($cell1);
 552     list($row2, $col2) = $this->_cell_to_packed_rowcol($cell2);
 553
 554     // The ptg value depends on the class of the ptg.
 555     if ($class == 0) {
 556         $ptgArea = pack("C", $this->ptg['ptgArea']);
 557         }
 558     elseif ($class == 1) {
 559         $ptgArea = pack("C", $this->ptg['ptgAreaV']);
 560         }
 561     elseif ($class == 2) {
 562         $ptgArea = pack("C", $this->ptg['ptgAreaA']);
 563         }
 564     else{
 565         die("Unknown class ");
 566         }
 567
 568     return($ptgArea . $row1 . $row2 . $col1. $col2);
 569     }
 570
 571 /**
 572 * Convert an Excel reference such as A1, $B2, C$3 or $D$4 to a ptgRefV.
 573 *
 574 * @param string $cell An Excel cell reference
 575 */
 576   function _convert_ref2d($cell)
 577     {
 578     $class = 2; // as far as I know, this is magick.
 579
 580     // Convert the cell reference
 581     list($row, $col) = $this->_cell_to_packed_rowcol($cell);
 582
 583     // The ptg value depends on the class of the ptg.
 584     if ($class == 0) {
 585         $ptgRef = pack("C", $this->ptg['ptgRef']);
 586         }
 587     elseif ($class == 1) {
 588         $ptgRef = pack("C", $this->ptg['ptgRefV']);
 589         }
 590     elseif ($class == 2) {
 591         $ptgRef = pack("C", $this->ptg['ptgRefA']);
 592         }
 593     else{
 594         die("Unknown class ");
 595         }
 596     return $ptgRef.$row.$col;
 597     }
 598
 599 /**
 600 * pack() row and column into the required 3 byte format.
 601 *
 602 * @param string $cell The Excel cell reference to be packed
 603 */
 604   function _cell_to_packed_rowcol($cell)
 605     {
 606     list($row, $col, $row_rel, $col_rel) = $this->_cell_to_rowcol($cell);
 607     if ($col >= 256) {
 608         die("Column in: $cell greater than 255 ");
 609         }
 610     if ($row >= 16384) {
 611         die("Row in: $cell greater than 16384 ");
 612         }
 613
 614     // Set the high bits to indicate if row or col are relative.
 615     $row    |= $col_rel << 14;
 616     $row    |= $row_rel << 15;
 617
 618     $row     = pack('v', $row);
 619     $col     = pack('C', $col);
 620
 621     return (array($row, $col));
 622     }
 623
 624 /**
 625 * Convert an Excel cell reference such as A1 or $B2 or C$3 or $D$4 to a zero
 626 * indexed row and column number. Also returns two boolean values to indicate
 627 * whether the row or column are relative references.
 628 *
 629 * @param string $cell The Excel cell reference in A1 format.
 630 */
 631   function _cell_to_rowcol($cell)
 632     {
 633     preg_match('/(\$)?([A-I]?[A-Z])(\$)?(\d+)/',$cell,$match);
 634     // return absolute column if there is a $ in the ref
 635     $col_rel = empty($match[1]) ? 1 : 0;
 636     $col_ref = $match[2];
 637     $row_rel = empty($match[3]) ? 1 : 0;
 638     $row     = $match[4];
 639
 640     // Convert base26 column string to a number.
 641     $expn   = strlen($col_ref) - 1;
 642     $col    = 0;
 643     for($i=0; $i < strlen($col_ref); $i++)
 644     {
 645         $col += (ord($col_ref{$i}) - ord('A') + 1) * pow(26, $expn);
 646         $expn--;
 647     }
 648
 649     // Convert 1-index to zero-index
 650     $row--;
 651     $col--;
 652
 653     return(array($row, $col, $row_rel, $col_rel));
 654     }
 655
 656 /**
 657 * Advance to the next valid token.
 658 */
 659   function _advance()
 660     {
 661     $i = $this->_current_char;
 662     // eat up white spaces
 663     if($i < strlen($this->_formula))
 664         {
 665         while($this->_formula{$i} == " ")
 666             {
 667             $i++;
 668             }
 669         if($i < strlen($this->_formula) - 1)
 670             {
 671             $this->_lookahead = $this->_formula{$i+1};
 672             }
 673         $token = "";
 674         }
 675     while($i < strlen($this->_formula))
 676         {
 677         $token .= $this->_formula{$i};
 678         if($this->_match($token) != '')
 679             {
 680             if($i < strlen($this->_formula) - 1)
 681                 {
 682                 $this->_lookahead = $this->_formula{$i+1};
 683                 }
 684             $this->_current_char = $i + 1;
 685             $this->_current_token = $token;
 686             return(1);
 687             }
 688         $this->_lookahead = $this->_formula{$i+2};
 689         $i++;
 690         }
 691     //die("Lexical error ".$this->_current_char);
 692     }
 693
 694 /**
 695 * Checks if it's a valid token.
 696 *
 697 * @param mixed $token The token to check.
 698 */
 699   function _match($token)
 700     {
 701     switch($token)
 702         {
 703         case ADD:
 704             return($token);
 705             break;
 706         case SUB:
 707             return($token);
 708             break;
 709         case MUL:
 710             return($token);
 711             break;
 712         case DIV:
 713             return($token);
 714             break;
 715         case OPEN:
 716             return($token);
 717             break;
 718         case CLOSE:
 719             return($token);
 720             break;
 721         case COMA:
 722             return($token);
 723             break;
 724         default:
 725             // if it's a reference
 726             if(preg_match("/^[A-I]?[A-Z][0-9]+$/i",$token) and
 727                !preg_match("/[0-9]/",$this->_lookahead) and
 728                ($this->_lookahead != ':') and ($this->_lookahead != '.'))
 729                 {
 730                 return($token);
 731                 }
 732             // if it's a range (A1:A2)
 733             elseif(preg_match("/^[A-I]?[A-Z][0-9]+:[A-I]?[A-Z][0-9]+$/i",$token) and
 734                    !preg_match("/[0-9]/",$this->_lookahead))
 735                 {
 736                 return($token);
 737                 }
 738             // if it's a range (A1..A2)
 739             elseif(preg_match("/^[A-I]?[A-Z][0-9]+\.\.[A-I]?[A-Z][0-9]+$/i",$token) and
 740                    !preg_match("/[0-9]/",$this->_lookahead))
 741                 {
 742                 return($token);
 743                 }
 744             elseif(is_numeric($token) and !is_numeric($token.$this->_lookahead))
 745                 {
 746                 return($token);
 747                 }
 748             // if it's a function call
 749             elseif(preg_match("/^[A-Z0-9�-�\.]+$/i",$token) and ($this->_lookahead == "("))
 750
 751                 {
 752                 return($token);
 753                 }
 754             return '';
 755         }
 756     }
 757
 758 /**
 759 * The parsing method. It parses a formula.
 760 *
 761 * @access public
 762 * @param string $formula The formula to parse, without the initial equal sign (=).
 763 */
 764   function parse($formula)
 765     {
 766     $this->_current_char = 0;
 767     $this->_formula      = $formula;
 768     $this->_lookahead    = $formula{1};
 769     $this->_advance();
 770     $this->_parse_tree   = $this->_expression();
 771     }
 772
 773 /**
 774 * It parses a expression. It assumes the following rule:
 775 * Expr -> Term [("+" | "-") Term]
 776 *
 777 * @return mixed The parsed ptg'd tree
 778 */
 779   function _expression()
 780     {
 781     $result = $this->_term();
 782     while ($this->_current_token == ADD or $this->_current_token == SUB)
 783         {
 784         if ($this->_current_token == ADD)
 785             {
 786             $this->_advance();
 787             $result = $this->_create_tree('ptgAdd', $result, $this->_term());
 788             }
 789         else
 790             {
 791             $this->_advance();
 792             $result = $this->_create_tree('ptgSub', $result, $this->_term());
 793             }
 794         }
 795     return $result;
 796     }
 797
 798 /**
 799 * This function just introduces a ptgParen element in the tree, so that Excel
 800 * doesn't get confused when working with a parenthesized formula afterwards.
 801 *
 802 * @see _fact
 803 * @return mixed The parsed ptg'd tree
 804 */
 805   function _parenthesized_expression()
 806     {
 807     $result = $this->_create_tree('ptgParen', $this->_expression(), '');
 808     return($result);
 809     }
 810
 811 /**
 812 * It parses a term. It assumes the following rule:
 813 * Term -> Fact [("*" | "/") Fact]
 814 *
 815 * @return mixed The parsed ptg'd tree
 816 */
 817   function _term()
 818     {
 819     $result = $this->_fact();
 820     while ($this->_current_token == MUL || $this->_current_token == DIV)
 821         {
 822         if ($this->_current_token == MUL)
 823             {
 824             $this->_advance();
 825             $result = $this->_create_tree('ptgMul', $result, $this->_fact());
 826             }
 827         else
 828             {
 829             $this->_advance();
 830             $result = $this->_create_tree('ptgDiv', $result, $this->_fact());
 831             }
 832         }
 833     return($result);
 834     }
 835
 836 /**
 837 * It parses a factor. It assumes the following rule:
 838 * Fact -> ( Expr )
 839 *       | CellRef
 840 *       | CellRange
 841 *       | Number
 842 *       | Function
 843 *
 844 * @return mixed The parsed ptg'd tree
 845 */
 846   function _fact()
 847     {
 848     if ($this->_current_token == OPEN)
 849         {
 850         $this->_advance();         // eat the "("
 851         $result = $this->_parenthesized_expression();//$this->_expression();
 852
 853         if ($this->_current_token != CLOSE) {
 854             die("')' token expected.");
 855             }
 856         $this->_advance();         // eat the ")"
 857         return($result);
 858         }
 859     // if it's a reference
 860     if (preg_match("/^[A-I]?[A-Z][0-9]+$/i",$this->_current_token))
 861         {
 862         $result = $this->_create_tree($this->_current_token, '', '');
 863         $this->_advance();
 864         return($result);
 865         }
 866     // if it's a range
 867     elseif (preg_match("/^[A-I]?[A-Z][0-9]+:[A-I]?[A-Z][0-9]+$/i",$this->_current_token) or
 868             preg_match("/^[A-I]?[A-Z][0-9]+\.\.[A-I]?[A-Z][0-9]+$/i",$this->_current_token))
 869         {
 870         $result = $this->_current_token;
 871         $this->_advance();
 872         return($result);
 873         }
 874     elseif (is_numeric($this->_current_token))
 875         {
 876         $result = $this->_create_tree($this->_current_token, '', '');
 877         $this->_advance();
 878         return($result);
 879         }
 880     // if it's a function call
 881     elseif (preg_match("/^[A-Z0-9�-�\.]+$/i",$this->_current_token))
 882         {
 883         $result = $this->_func();
 884         return($result);
 885         }
 886     die("Sintactic error: ".$this->_current_token.", lookahead: ".
 887         $this->_lookahead.", current char: ".$this->_current_char);
 888     }
 889
 890 /**
 891 * It parses a function call. It assumes the following rule:
 892 * Func -> ( Expr [,Expr]* )
 893 *
 894 */
 895   function _func()
 896     {
 897     $num_args = 0; // number of arguments received
 898     $function = $this->_current_token;
 899     $this->_advance();
 900     $this->_advance();         // eat the "("
 901     while($this->_current_token != ')')
 902         {
 903         if($num_args > 0)
 904             {
 905             if($this->_current_token == COMA) {
 906                 $this->_advance();  // eat the ","
 907                 }
 908             else {
 909                 die("Sintactic error: coma expected $num_args");
 910                 }
 911             $result = $this->_create_tree('arg', $result, $this->_expression());
 912             }
 913         else {
 914             $result = $this->_create_tree('arg', '', $this->_expression());
 915             }
 916         $num_args++;
 917         }
 918     $args = $this->_functions[$function][1];
 919     // If fixed number of args eg. TIME($i,$j,$k). Check that the number of args is valid.
 920     if (($args >= 0) and ($args != $num_args))
 921         {
 922         die("Incorrect number of arguments in function $function() ");
 923         }
 924
 925     $result = $this->_create_tree($function, $result, '');
 926     $this->_advance();         // eat the ")"
 927     return($result);
 928     }
 929
 930 /**
 931 * Creates a tree. In fact an array which may have one or two arrays (sub-trees)
 932 * as elements.
 933 *
 934 * @param mixed $value The value of this node.
 935 * @param mixed $left  The left array (sub-tree) or a final node.
 936 * @param mixed $right The right array (sub-tree) or a final node.
 937 */
 938   function _create_tree($value, $left, $right)
 939     {
 940     return array('value' => $value, 'left' => $left, 'right' => $right);
 941     }
 942
 943 /**
 944 * Builds a string containing the tree in reverse polish notation (What you
 945 * would use in a HP calculator stack).
 946 * The following tree:
 947 *
 948 *    +
 949 *   / \
 950 *  2   3
 951 *
 952 * produces: "23+"
 953 *
 954 * The following tree:
 955 *
 956 *    +
 957 *   / \
 958 *  3   *
 959 *     / \
 960 *    6   A1
 961 *
 962 * produces: "36A1*+"
 963 *
 964 * In fact all operands, functions, references, etc... are written as ptg's
 965 *
 966 * @access public
 967 * @param array $tree The optional tree to convert.
 968 */
 969   function to_reverse_polish($tree = array())
 970     {
 971     $polish = ""; // the string we are going to return
 972     if (empty($tree)) // If it's the first call use _parse_tree
 973         {
 974         $tree = $this->_parse_tree;
 975         }
 976     if (is_array($tree['left']))
 977         {
 978         $polish .= $this->to_reverse_polish($tree['left']);
 979         }
 980     elseif($tree['left'] != '') // It's a final node
 981         {
 982         $polish .= $this->_convert($tree['left']); //$tree['left'];
 983         }
 984     if (is_array($tree['right']))
 985         {
 986         $polish .= $this->to_reverse_polish($tree['right']);
 987         }
 988     elseif($tree['right'] != '') // It's a final node
 989         {
 990         $polish .= $this->_convert($tree['right']);
 991         }
 992     $polish .= $this->_convert($tree['value']);
 993     return $polish;
 994     }
 995   }
 996 ?>