ILIAS Release_3_7_x_branch .rev 46817: classes/Spreadsheet/Excel/Writer/Parser.php Source File

Go to the documentation of this file.
00001 <?php
00028 define('SPREADSHEET_EXCEL_WRITER_ADD',"+");
00029 
00033 define('SPREADSHEET_EXCEL_WRITER_SUB',"-");
00034 
00038 define('SPREADSHEET_EXCEL_WRITER_MUL',"*");
00039 
00043 define('SPREADSHEET_EXCEL_WRITER_DIV',"/");
00044 
00048 define('SPREADSHEET_EXCEL_WRITER_OPEN',"(");
00049 
00053 define('SPREADSHEET_EXCEL_WRITER_CLOSE',")");
00054 
00058 define('SPREADSHEET_EXCEL_WRITER_COMA',",");
00059 
00063 define('SPREADSHEET_EXCEL_WRITER_SEMICOLON',";");
00064 
00068 define('SPREADSHEET_EXCEL_WRITER_GT',">");
00069 
00073 define('SPREADSHEET_EXCEL_WRITER_LT',"<");
00074 
00078 define('SPREADSHEET_EXCEL_WRITER_LE',"<=");
00079 
00083 define('SPREADSHEET_EXCEL_WRITER_GE',">=");
00084 
00088 define('SPREADSHEET_EXCEL_WRITER_EQ',"=");
00089 
00093 define('SPREADSHEET_EXCEL_WRITER_NE',"<>");
00094 
00095 
00096 require_once('PEAR.php');
00097 
00106 class Spreadsheet_Excel_Writer_Parser extends PEAR
00107 {
00112     var $_current_char;
00113 
00118     var $_current_token;
00119 
00124     var $_formula;
00125 
00130     var $_lookahead;
00131 
00136     var $_parse_tree;
00137 
00142     var $_byte_order;
00143 
00148     var $_ext_sheets;
00149 
00154     var $_references;
00155 
00160     var $_BIFF_version;
00161 
00168     function Spreadsheet_Excel_Writer_Parser($byte_order, $biff_version)
00169     {
00170         $this->_current_char  = 0;
00171         $this->_BIFF_version  = $biff_version;
00172         $this->_current_token = '';       // The token we are working on.
00173         $this->_formula       = "";       // The formula to parse.
00174         $this->_lookahead     = '';       // The character ahead of the current char.
00175         $this->_parse_tree    = '';       // The parse tree to be generated.
00176         $this->_initializeHashes();      // Initialize the hashes: ptg's and function's ptg's
00177         $this->_byte_order = $byte_order; // Little Endian or Big Endian
00178         $this->_ext_sheets = array();
00179         $this->_references = array();
00180     }
00181     
00187     function _initializeHashes()
00188     {
00189         // The Excel ptg indices
00190         $this->ptg = array(
00191             'ptgExp'       => 0x01,
00192             'ptgTbl'       => 0x02,
00193             'ptgAdd'       => 0x03,
00194             'ptgSub'       => 0x04,
00195             'ptgMul'       => 0x05,
00196             'ptgDiv'       => 0x06,
00197             'ptgPower'     => 0x07,
00198             'ptgConcat'    => 0x08,
00199             'ptgLT'        => 0x09,
00200             'ptgLE'        => 0x0A,
00201             'ptgEQ'        => 0x0B,
00202             'ptgGE'        => 0x0C,
00203             'ptgGT'        => 0x0D,
00204             'ptgNE'        => 0x0E,
00205             'ptgIsect'     => 0x0F,
00206             'ptgUnion'     => 0x10,
00207             'ptgRange'     => 0x11,
00208             'ptgUplus'     => 0x12,
00209             'ptgUminus'    => 0x13,
00210             'ptgPercent'   => 0x14,
00211             'ptgParen'     => 0x15,
00212             'ptgMissArg'   => 0x16,
00213             'ptgStr'       => 0x17,
00214             'ptgAttr'      => 0x19,
00215             'ptgSheet'     => 0x1A,
00216             'ptgEndSheet'  => 0x1B,
00217             'ptgErr'       => 0x1C,
00218             'ptgBool'      => 0x1D,
00219             'ptgInt'       => 0x1E,
00220             'ptgNum'       => 0x1F,
00221             'ptgArray'     => 0x20,
00222             'ptgFunc'      => 0x21,
00223             'ptgFuncVar'   => 0x22,
00224             'ptgName'      => 0x23,
00225             'ptgRef'       => 0x24,
00226             'ptgArea'      => 0x25,
00227             'ptgMemArea'   => 0x26,
00228             'ptgMemErr'    => 0x27,
00229             'ptgMemNoMem'  => 0x28,
00230             'ptgMemFunc'   => 0x29,
00231             'ptgRefErr'    => 0x2A,
00232             'ptgAreaErr'   => 0x2B,
00233             'ptgRefN'      => 0x2C,
00234             'ptgAreaN'     => 0x2D,
00235             'ptgMemAreaN'  => 0x2E,
00236             'ptgMemNoMemN' => 0x2F,
00237             'ptgNameX'     => 0x39,
00238             'ptgRef3d'     => 0x3A,
00239             'ptgArea3d'    => 0x3B,
00240             'ptgRefErr3d'  => 0x3C,
00241             'ptgAreaErr3d' => 0x3D,
00242             'ptgArrayV'    => 0x40,
00243             'ptgFuncV'     => 0x41,
00244             'ptgFuncVarV'  => 0x42,
00245             'ptgNameV'     => 0x43,
00246             'ptgRefV'      => 0x44,
00247             'ptgAreaV'     => 0x45,
00248             'ptgMemAreaV'  => 0x46,
00249             'ptgMemErrV'   => 0x47,
00250             'ptgMemNoMemV' => 0x48,
00251             'ptgMemFuncV'  => 0x49,
00252             'ptgRefErrV'   => 0x4A,
00253             'ptgAreaErrV'  => 0x4B,
00254             'ptgRefNV'     => 0x4C,
00255             'ptgAreaNV'    => 0x4D,
00256             'ptgMemAreaNV' => 0x4E,
00257             'ptgMemNoMemN' => 0x4F,
00258             'ptgFuncCEV'   => 0x58,
00259             'ptgNameXV'    => 0x59,
00260             'ptgRef3dV'    => 0x5A,
00261             'ptgArea3dV'   => 0x5B,
00262             'ptgRefErr3dV' => 0x5C,
00263             'ptgAreaErr3d' => 0x5D,
00264             'ptgArrayA'    => 0x60,
00265             'ptgFuncA'     => 0x61,
00266             'ptgFuncVarA'  => 0x62,
00267             'ptgNameA'     => 0x63,
00268             'ptgRefA'      => 0x64,
00269             'ptgAreaA'     => 0x65,
00270             'ptgMemAreaA'  => 0x66,
00271             'ptgMemErrA'   => 0x67,
00272             'ptgMemNoMemA' => 0x68,
00273             'ptgMemFuncA'  => 0x69,
00274             'ptgRefErrA'   => 0x6A,
00275             'ptgAreaErrA'  => 0x6B,
00276             'ptgRefNA'     => 0x6C,
00277             'ptgAreaNA'    => 0x6D,
00278             'ptgMemAreaNA' => 0x6E,
00279             'ptgMemNoMemN' => 0x6F,
00280             'ptgFuncCEA'   => 0x78,
00281             'ptgNameXA'    => 0x79,
00282             'ptgRef3dA'    => 0x7A,
00283             'ptgArea3dA'   => 0x7B,
00284             'ptgRefErr3dA' => 0x7C,
00285             'ptgAreaErr3d' => 0x7D
00286             );
00287     
00288         // Thanks to Michael Meeks and Gnumeric for the initial arg values.
00289         //
00290         // The following hash was generated by "function_locale.pl" in the distro.
00291         // Refer to function_locale.pl for non-English function names.
00292         //
00293         // The array elements are as follow:
00294         // ptg:   The Excel function ptg code.
00295         // args:  The number of arguments that the function takes:
00296         //           >=0 is a fixed number of arguments.
00297         //           -1  is a variable  number of arguments.
00298         // class: The reference, value or array class of the function args.
00299         // vol:   The function is volatile.
00300         //
00301         $this->_functions = array(
00302               // function                  ptg  args  class  vol
00303               'COUNT'           => array(   0,   -1,    0,    0 ),
00304               'IF'              => array(   1,   -1,    1,    0 ),
00305               'ISNA'            => array(   2,    1,    1,    0 ),
00306               'ISERROR'         => array(   3,    1,    1,    0 ),
00307               'SUM'             => array(   4,   -1,    0,    0 ),
00308               'AVERAGE'         => array(   5,   -1,    0,    0 ),
00309               'MIN'             => array(   6,   -1,    0,    0 ),
00310               'MAX'             => array(   7,   -1,    0,    0 ),
00311               'ROW'             => array(   8,   -1,    0,    0 ),
00312               'COLUMN'          => array(   9,   -1,    0,    0 ),
00313               'NA'              => array(  10,    0,    0,    0 ),
00314               'NPV'             => array(  11,   -1,    1,    0 ),
00315               'STDEV'           => array(  12,   -1,    0,    0 ),
00316               'DOLLAR'          => array(  13,   -1,    1,    0 ),
00317               'FIXED'           => array(  14,   -1,    1,    0 ),
00318               'SIN'             => array(  15,    1,    1,    0 ),
00319               'COS'             => array(  16,    1,    1,    0 ),
00320               'TAN'             => array(  17,    1,    1,    0 ),
00321               'ATAN'            => array(  18,    1,    1,    0 ),
00322               'PI'              => array(  19,    0,    1,    0 ),
00323               'SQRT'            => array(  20,    1,    1,    0 ),
00324               'EXP'             => array(  21,    1,    1,    0 ),
00325               'LN'              => array(  22,    1,    1,    0 ),
00326               'LOG10'           => array(  23,    1,    1,    0 ),
00327               'ABS'             => array(  24,    1,    1,    0 ),
00328               'INT'             => array(  25,    1,    1,    0 ),
00329               'SIGN'            => array(  26,    1,    1,    0 ),
00330               'ROUND'           => array(  27,    2,    1,    0 ),
00331               'LOOKUP'          => array(  28,   -1,    0,    0 ),
00332               'INDEX'           => array(  29,   -1,    0,    1 ),
00333               'REPT'            => array(  30,    2,    1,    0 ),
00334               'MID'             => array(  31,    3,    1,    0 ),
00335               'LEN'             => array(  32,    1,    1,    0 ),
00336               'VALUE'           => array(  33,    1,    1,    0 ),
00337               'TRUE'            => array(  34,    0,    1,    0 ),
00338               'FALSE'           => array(  35,    0,    1,    0 ),
00339               'AND'             => array(  36,   -1,    0,    0 ),
00340               'OR'              => array(  37,   -1,    0,    0 ),
00341               'NOT'             => array(  38,    1,    1,    0 ),
00342               'MOD'             => array(  39,    2,    1,    0 ),
00343               'DCOUNT'          => array(  40,    3,    0,    0 ),
00344               'DSUM'            => array(  41,    3,    0,    0 ),
00345               'DAVERAGE'        => array(  42,    3,    0,    0 ),
00346               'DMIN'            => array(  43,    3,    0,    0 ),
00347               'DMAX'            => array(  44,    3,    0,    0 ),
00348               'DSTDEV'          => array(  45,    3,    0,    0 ),
00349               'VAR'             => array(  46,   -1,    0,    0 ),
00350               'DVAR'            => array(  47,    3,    0,    0 ),
00351               'TEXT'            => array(  48,    2,    1,    0 ),
00352               'LINEST'          => array(  49,   -1,    0,    0 ),
00353               'TREND'           => array(  50,   -1,    0,    0 ),
00354               'LOGEST'          => array(  51,   -1,    0,    0 ),
00355               'GROWTH'          => array(  52,   -1,    0,    0 ),
00356               'PV'              => array(  56,   -1,    1,    0 ),
00357               'FV'              => array(  57,   -1,    1,    0 ),
00358               'NPER'            => array(  58,   -1,    1,    0 ),
00359               'PMT'             => array(  59,   -1,    1,    0 ),
00360               'RATE'            => array(  60,   -1,    1,    0 ),
00361               'MIRR'            => array(  61,    3,    0,    0 ),
00362               'IRR'             => array(  62,   -1,    0,    0 ),
00363               'RAND'            => array(  63,    0,    1,    1 ),
00364               'MATCH'           => array(  64,   -1,    0,    0 ),
00365               'DATE'            => array(  65,    3,    1,    0 ),
00366               'TIME'            => array(  66,    3,    1,    0 ),
00367               'DAY'             => array(  67,    1,    1,    0 ),
00368               'MONTH'           => array(  68,    1,    1,    0 ),
00369               'YEAR'            => array(  69,    1,    1,    0 ),
00370               'WEEKDAY'         => array(  70,   -1,    1,    0 ),
00371               'HOUR'            => array(  71,    1,    1,    0 ),
00372               'MINUTE'          => array(  72,    1,    1,    0 ),
00373               'SECOND'          => array(  73,    1,    1,    0 ),
00374               'NOW'             => array(  74,    0,    1,    1 ),
00375               'AREAS'           => array(  75,    1,    0,    1 ),
00376               'ROWS'            => array(  76,    1,    0,    1 ),
00377               'COLUMNS'         => array(  77,    1,    0,    1 ),
00378               'OFFSET'          => array(  78,   -1,    0,    1 ),
00379               'SEARCH'          => array(  82,   -1,    1,    0 ),
00380               'TRANSPOSE'       => array(  83,    1,    1,    0 ),
00381               'TYPE'            => array(  86,    1,    1,    0 ),
00382               'ATAN2'           => array(  97,    2,    1,    0 ),
00383               'ASIN'            => array(  98,    1,    1,    0 ),
00384               'ACOS'            => array(  99,    1,    1,    0 ),
00385               'CHOOSE'          => array( 100,   -1,    1,    0 ),
00386               'HLOOKUP'         => array( 101,   -1,    0,    0 ),
00387               'VLOOKUP'         => array( 102,   -1,    0,    0 ),
00388               'ISREF'           => array( 105,    1,    0,    0 ),
00389               'LOG'             => array( 109,   -1,    1,    0 ),
00390               'CHAR'            => array( 111,    1,    1,    0 ),
00391               'LOWER'           => array( 112,    1,    1,    0 ),
00392               'UPPER'           => array( 113,    1,    1,    0 ),
00393               'PROPER'          => array( 114,    1,    1,    0 ),
00394               'LEFT'            => array( 115,   -1,    1,    0 ),
00395               'RIGHT'           => array( 116,   -1,    1,    0 ),
00396               'EXACT'           => array( 117,    2,    1,    0 ),
00397               'TRIM'            => array( 118,    1,    1,    0 ),
00398               'REPLACE'         => array( 119,    4,    1,    0 ),
00399               'SUBSTITUTE'      => array( 120,   -1,    1,    0 ),
00400               'CODE'            => array( 121,    1,    1,    0 ),
00401               'FIND'            => array( 124,   -1,    1,    0 ),
00402               'CELL'            => array( 125,   -1,    0,    1 ),
00403               'ISERR'           => array( 126,    1,    1,    0 ),
00404               'ISTEXT'          => array( 127,    1,    1,    0 ),
00405               'ISNUMBER'        => array( 128,    1,    1,    0 ),
00406               'ISBLANK'         => array( 129,    1,    1,    0 ),
00407               'T'               => array( 130,    1,    0,    0 ),
00408               'N'               => array( 131,    1,    0,    0 ),
00409               'DATEVALUE'       => array( 140,    1,    1,    0 ),
00410               'TIMEVALUE'       => array( 141,    1,    1,    0 ),
00411               'SLN'             => array( 142,    3,    1,    0 ),
00412               'SYD'             => array( 143,    4,    1,    0 ),
00413               'DDB'             => array( 144,   -1,    1,    0 ),
00414               'INDIRECT'        => array( 148,   -1,    1,    1 ),
00415               'CALL'            => array( 150,   -1,    1,    0 ),
00416               'CLEAN'           => array( 162,    1,    1,    0 ),
00417               'MDETERM'         => array( 163,    1,    2,    0 ),
00418               'MINVERSE'        => array( 164,    1,    2,    0 ),
00419               'MMULT'           => array( 165,    2,    2,    0 ),
00420               'IPMT'            => array( 167,   -1,    1,    0 ),
00421               'PPMT'            => array( 168,   -1,    1,    0 ),
00422               'COUNTA'          => array( 169,   -1,    0,    0 ),
00423               'PRODUCT'         => array( 183,   -1,    0,    0 ),
00424               'FACT'            => array( 184,    1,    1,    0 ),
00425               'DPRODUCT'        => array( 189,    3,    0,    0 ),
00426               'ISNONTEXT'       => array( 190,    1,    1,    0 ),
00427               'STDEVP'          => array( 193,   -1,    0,    0 ),
00428               'VARP'            => array( 194,   -1,    0,    0 ),
00429               'DSTDEVP'         => array( 195,    3,    0,    0 ),
00430               'DVARP'           => array( 196,    3,    0,    0 ),
00431               'TRUNC'           => array( 197,   -1,    1,    0 ),
00432               'ISLOGICAL'       => array( 198,    1,    1,    0 ),
00433               'DCOUNTA'         => array( 199,    3,    0,    0 ),
00434               'ROUNDUP'         => array( 212,    2,    1,    0 ),
00435               'ROUNDDOWN'       => array( 213,    2,    1,    0 ),
00436               'RANK'            => array( 216,   -1,    0,    0 ),
00437               'ADDRESS'         => array( 219,   -1,    1,    0 ),
00438               'DAYS360'         => array( 220,   -1,    1,    0 ),
00439               'TODAY'           => array( 221,    0,    1,    1 ),
00440               'VDB'             => array( 222,   -1,    1,    0 ),
00441               'MEDIAN'          => array( 227,   -1,    0,    0 ),
00442               'SUMPRODUCT'      => array( 228,   -1,    2,    0 ),
00443               'SINH'            => array( 229,    1,    1,    0 ),
00444               'COSH'            => array( 230,    1,    1,    0 ),
00445               'TANH'            => array( 231,    1,    1,    0 ),
00446               'ASINH'           => array( 232,    1,    1,    0 ),
00447               'ACOSH'           => array( 233,    1,    1,    0 ),
00448               'ATANH'           => array( 234,    1,    1,    0 ),
00449               'DGET'            => array( 235,    3,    0,    0 ),
00450               'INFO'            => array( 244,    1,    1,    1 ),
00451               'DB'              => array( 247,   -1,    1,    0 ),
00452               'FREQUENCY'       => array( 252,    2,    0,    0 ),
00453               'ERROR.TYPE'      => array( 261,    1,    1,    0 ),
00454               'REGISTER.ID'     => array( 267,   -1,    1,    0 ),
00455               'AVEDEV'          => array( 269,   -1,    0,    0 ),
00456               'BETADIST'        => array( 270,   -1,    1,    0 ),
00457               'GAMMALN'         => array( 271,    1,    1,    0 ),
00458               'BETAINV'         => array( 272,   -1,    1,    0 ),
00459               'BINOMDIST'       => array( 273,    4,    1,    0 ),
00460               'CHIDIST'         => array( 274,    2,    1,    0 ),
00461               'CHIINV'          => array( 275,    2,    1,    0 ),
00462               'COMBIN'          => array( 276,    2,    1,    0 ),
00463               'CONFIDENCE'      => array( 277,    3,    1,    0 ),
00464               'CRITBINOM'       => array( 278,    3,    1,    0 ),
00465               'EVEN'            => array( 279,    1,    1,    0 ),
00466               'EXPONDIST'       => array( 280,    3,    1,    0 ),
00467               'FDIST'           => array( 281,    3,    1,    0 ),
00468               'FINV'            => array( 282,    3,    1,    0 ),
00469               'FISHER'          => array( 283,    1,    1,    0 ),
00470               'FISHERINV'       => array( 284,    1,    1,    0 ),
00471               'FLOOR'           => array( 285,    2,    1,    0 ),
00472               'GAMMADIST'       => array( 286,    4,    1,    0 ),
00473               'GAMMAINV'        => array( 287,    3,    1,    0 ),
00474               'CEILING'         => array( 288,    2,    1,    0 ),
00475               'HYPGEOMDIST'     => array( 289,    4,    1,    0 ),
00476               'LOGNORMDIST'     => array( 290,    3,    1,    0 ),
00477               'LOGINV'          => array( 291,    3,    1,    0 ),
00478               'NEGBINOMDIST'    => array( 292,    3,    1,    0 ),
00479               'NORMDIST'        => array( 293,    4,    1,    0 ),
00480               'NORMSDIST'       => array( 294,    1,    1,    0 ),
00481               'NORMINV'         => array( 295,    3,    1,    0 ),
00482               'NORMSINV'        => array( 296,    1,    1,    0 ),
00483               'STANDARDIZE'     => array( 297,    3,    1,    0 ),
00484               'ODD'             => array( 298,    1,    1,    0 ),
00485               'PERMUT'          => array( 299,    2,    1,    0 ),
00486               'POISSON'         => array( 300,    3,    1,    0 ),
00487               'TDIST'           => array( 301,    3,    1,    0 ),
00488               'WEIBULL'         => array( 302,    4,    1,    0 ),
00489               'SUMXMY2'         => array( 303,    2,    2,    0 ),
00490               'SUMX2MY2'        => array( 304,    2,    2,    0 ),
00491               'SUMX2PY2'        => array( 305,    2,    2,    0 ),
00492               'CHITEST'         => array( 306,    2,    2,    0 ),
00493               'CORREL'          => array( 307,    2,    2,    0 ),
00494               'COVAR'           => array( 308,    2,    2,    0 ),
00495               'FORECAST'        => array( 309,    3,    2,    0 ),
00496               'FTEST'           => array( 310,    2,    2,    0 ),
00497               'INTERCEPT'       => array( 311,    2,    2,    0 ),
00498               'PEARSON'         => array( 312,    2,    2,    0 ),
00499               'RSQ'             => array( 313,    2,    2,    0 ),
00500               'STEYX'           => array( 314,    2,    2,    0 ),
00501               'SLOPE'           => array( 315,    2,    2,    0 ),
00502               'TTEST'           => array( 316,    4,    2,    0 ),
00503               'PROB'            => array( 317,   -1,    2,    0 ),
00504               'DEVSQ'           => array( 318,   -1,    0,    0 ),
00505               'GEOMEAN'         => array( 319,   -1,    0,    0 ),
00506               'HARMEAN'         => array( 320,   -1,    0,    0 ),
00507               'SUMSQ'           => array( 321,   -1,    0,    0 ),
00508               'KURT'            => array( 322,   -1,    0,    0 ),
00509               'SKEW'            => array( 323,   -1,    0,    0 ),
00510               'ZTEST'           => array( 324,   -1,    0,    0 ),
00511               'LARGE'           => array( 325,    2,    0,    0 ),
00512               'SMALL'           => array( 326,    2,    0,    0 ),
00513               'QUARTILE'        => array( 327,    2,    0,    0 ),
00514               'PERCENTILE'      => array( 328,    2,    0,    0 ),
00515               'PERCENTRANK'     => array( 329,   -1,    0,    0 ),
00516               'MODE'            => array( 330,   -1,    2,    0 ),
00517               'TRIMMEAN'        => array( 331,    2,    0,    0 ),
00518               'TINV'            => array( 332,    2,    1,    0 ),
00519               'CONCATENATE'     => array( 336,   -1,    1,    0 ),
00520               'POWER'           => array( 337,    2,    1,    0 ),
00521               'RADIANS'         => array( 342,    1,    1,    0 ),
00522               'DEGREES'         => array( 343,    1,    1,    0 ),
00523               'SUBTOTAL'        => array( 344,   -1,    0,    0 ),
00524               'SUMIF'           => array( 345,   -1,    0,    0 ),
00525               'COUNTIF'         => array( 346,    2,    0,    0 ),
00526               'COUNTBLANK'      => array( 347,    1,    0,    0 ),
00527               'ROMAN'           => array( 354,   -1,    1,    0 )
00528               );
00529     }
00530     
00539     function _convert($token)
00540     {
00541         if (preg_match("/^\"[^\"]{0,255}\"$/", $token))
00542         {
00543             return $this->_convertString($token);
00544         }
00545         elseif (is_numeric($token))
00546         {
00547             return $this->_convertNumber($token);
00548         }
00549         // match references like A1 or $A$1
00550         elseif (preg_match('/^\$?([A-Ia-i]?[A-Za-z])\$?(\d+)$/',$token))
00551         {
00552             return $this->_convertRef2d($token);
00553         }
00554         // match external references like Sheet1!A1 or Sheet1:Sheet2!A1
00555         elseif (preg_match("/^\w+(\:\w+)?\![A-Ia-i]?[A-Za-z](\d+)$/",$token))
00556         {
00557             return $this->_convertRef3d($token);
00558         }
00559         // match external references like Sheet1!A1 or Sheet1:Sheet2!A1
00560         elseif (preg_match("/^'\w+(\:\w+)?'\![A-Ia-i]?[A-Za-z](\d+)$/",$token))
00561         {
00562             return $this->_convertRef3d($token);
00563         }
00564         // match ranges like A1:B2
00565         elseif (preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?(\d+)\:(\$)?[A-Ia-i]?[A-Za-z](\$)?(\d+)$/",$token))
00566         {
00567             return $this->_convertRange2d($token);
00568         }
00569         // match ranges like A1..B2
00570         elseif (preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?(\d+)\.\.(\$)?[A-Ia-i]?[A-Za-z](\$)?(\d+)$/",$token))
00571         {
00572             return $this->_convertRange2d($token);
00573         }
00574         // match external ranges like Sheet1!A1 or Sheet1:Sheet2!A1:B2
00575         elseif (preg_match("/^\w+(\:\w+)?\!([A-Ia-i]?[A-Za-z])?(\d+)\:([A-Ia-i]?[A-Za-z])?(\d+)$/",$token))
00576         {
00577             return $this->_convertRange3d($token);
00578         }
00579         // match external ranges like 'Sheet1'!A1 or 'Sheet1:Sheet2'!A1:B2
00580         elseif (preg_match("/^'\w+(\:\w+)?'\!([A-Ia-i]?[A-Za-z])?(\d+)\:([A-Ia-i]?[A-Za-z])?(\d+)$/",$token))
00581         {
00582             return $this->_convertRange3d($token);
00583         }
00584         elseif (isset($this->ptg[$token])) // operators (including parentheses)
00585         {
00586             return pack("C", $this->ptg[$token]);
00587         }
00588         // commented so argument number can be processed correctly. See toReversePolish().
00589         /*elseif (preg_match("/[A-Z0-9\xc0-\xdc\.]+/",$token))
00590         {
00591             return($this->_convertFunction($token,$this->_func_args));
00592         }*/
00593         // if it's an argument, ignore the token (the argument remains)
00594         elseif ($token == 'arg')
00595         {
00596             return '';
00597         }
00598         // TODO: use real error codes
00599         return $this->raiseError("Unknown token $token");
00600     }
00601     
00608     function _convertNumber($num)
00609     {
00610         // Integer in the range 0..2**16-1
00611         if ((preg_match("/^\d+$/",$num)) and ($num <= 65535)) {
00612             return pack("Cv", $this->ptg['ptgInt'], $num);
00613         }
00614         else // A float
00615         {
00616             if ($this->_byte_order) { // if it's Big Endian
00617                 $num = strrev($num);
00618             }
00619             return pack("Cd", $this->ptg['ptgNum'], $num);
00620         }
00621     }
00622     
00631     function _convertString($string)
00632     {
00633         // chop away beggining and ending quotes
00634         $string = substr($string, 1, strlen($string) - 2);
00635         if (strlen($string) > 255) {
00636             return $this->raiseError("String is too long");
00637         }
00638         if ($this->_BIFF_version == 0x0500) {
00639             return pack("CC", $this->ptg['ptgStr'], strlen($string)).$string;
00640         }
00641         elseif ($this->_BIFF_version == 0x0600) {
00642             $encoding = 0;   // TODO: Unicode support
00643             return pack("CCC", $this->ptg['ptgStr'], strlen($string), $encoding).$string;
00644         }
00645     }
00646  
00656     function _convertFunction($token, $num_args)
00657     {
00658         $args     = $this->_functions[$token][1];
00659         $volatile = $this->_functions[$token][3];
00660     
00661         // Fixed number of args eg. TIME($i,$j,$k).
00662         if ($args >= 0) {
00663             return pack("Cv", $this->ptg['ptgFuncV'], $this->_functions[$token][0]);
00664         }
00665         // Variable number of args eg. SUM($i,$j,$k, ..).
00666         if ($args == -1) {
00667             return pack("CCv", $this->ptg['ptgFuncVarV'], $num_args, $this->_functions[$token][0]);
00668         }
00669     }
00670     
00677     function _convertRange2d($range)
00678     {
00679         $class = 2; // as far as I know, this is magick.
00680     
00681         // Split the range into 2 cell refs
00682         if (preg_match("/^([A-Ia-i]?[A-Za-z])(\d+)\:([A-Ia-i]?[A-Za-z])(\d+)$/",$range)) {
00683             list($cell1, $cell2) = split(':', $range);
00684         }
00685         elseif (preg_match("/^([A-Ia-i]?[A-Za-z])(\d+)\.\.([A-Ia-i]?[A-Za-z])(\d+)$/",$range)) {
00686             list($cell1, $cell2) = split('\.\.', $range);
00687         
00688         }
00689         else {
00690             // TODO: use real error codes
00691             return $this->raiseError("Unknown range separator", 0, PEAR_ERROR_DIE);
00692         }
00693     
00694         // Convert the cell references
00695         $cell_array1 = $this->_cellToPackedRowcol($cell1);
00696         if (PEAR::isError($cell_array1)) {
00697             return $cell_array1;
00698         }
00699         list($row1, $col1) = $cell_array1;
00700         $cell_array2 = $this->_cellToPackedRowcol($cell2);
00701         if (PEAR::isError($cell_array2)) {
00702             return $cell_array2;
00703         }
00704         list($row2, $col2) = $cell_array2;
00705     
00706         // The ptg value depends on the class of the ptg.
00707         if ($class == 0) {
00708             $ptgArea = pack("C", $this->ptg['ptgArea']);
00709         }
00710         elseif ($class == 1) {
00711             $ptgArea = pack("C", $this->ptg['ptgAreaV']);
00712         }
00713         elseif ($class == 2) {
00714             $ptgArea = pack("C", $this->ptg['ptgAreaA']);
00715         }
00716         else {
00717             // TODO: use real error codes
00718             return $this->raiseError("Unknown class $class", 0, PEAR_ERROR_DIE);
00719         }
00720         return $ptgArea . $row1 . $row2 . $col1. $col2;
00721     }
00722  
00731     function _convertRange3d($token)
00732     {
00733         $class = 2; // as far as I know, this is magick.
00734  
00735         // Split the ref at the ! symbol
00736         list($ext_ref, $range) = split('!', $token);
00737  
00738         // Convert the external reference part (different for BIFF8)
00739         if ($this->_BIFF_version == 0x0500) {
00740             $ext_ref = $this->_packExtRef($ext_ref);
00741             if (PEAR::isError($ext_ref)) {
00742                 return $ext_ref;
00743             }
00744         }
00745         elseif ($this->_BIFF_version == 0x0600) {
00746              $ext_ref = $this->_getRefIndex($ext_ref);
00747              if (PEAR::isError($ext_ref)) {
00748                  return $ext_ref;
00749              }
00750         }
00751 
00752         // Split the range into 2 cell refs
00753         list($cell1, $cell2) = split(':', $range);
00754  
00755         // Convert the cell references
00756         if (preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?(\d+)$/", $cell1))
00757         {
00758             $cell_array1 = $this->_cellToPackedRowcol($cell1);
00759             if (PEAR::isError($cell_array1)) {
00760                 return $cell_array1;
00761             }
00762             list($row1, $col1) = $cell_array1;
00763             $cell_array2 = $this->_cellToPackedRowcol($cell2);
00764             if (PEAR::isError($cell_array2)) {
00765                 return $cell_array2;
00766             }
00767             list($row2, $col2) = $cell_array2;
00768         }
00769         else { // It's a rows range (like 26:27)
00770              $cells_array = $this->_rangeToPackedRange($cell1.':'.$cell2);
00771              if (PEAR::isError($cells_array)) {
00772                  return $cells_array;
00773              }
00774              list($row1, $col1, $row2, $col2) = $cells_array;
00775         }
00776  
00777         // The ptg value depends on the class of the ptg.
00778         if ($class == 0) {
00779             $ptgArea = pack("C", $this->ptg['ptgArea3d']);
00780         }
00781         elseif ($class == 1) {
00782             $ptgArea = pack("C", $this->ptg['ptgArea3dV']);
00783         }
00784         elseif ($class == 2) {
00785             $ptgArea = pack("C", $this->ptg['ptgArea3dA']);
00786         }
00787         else {
00788             return $this->raiseError("Unknown class $class", 0, PEAR_ERROR_DIE);
00789         }
00790  
00791         return $ptgArea . $ext_ref . $row1 . $row2 . $col1. $col2;
00792     }
00793 
00801     function _convertRef2d($cell)
00802     {
00803         $class = 2; // as far as I know, this is magick.
00804     
00805         // Convert the cell reference
00806         $cell_array = $this->_cellToPackedRowcol($cell);
00807         if (PEAR::isError($cell_array)) {
00808             return $cell_array;
00809         }
00810         list($row, $col) = $cell_array;
00811     
00812         // The ptg value depends on the class of the ptg.
00813         if ($class == 0) {
00814             $ptgRef = pack("C", $this->ptg['ptgRef']);
00815         }
00816         elseif ($class == 1) {
00817             $ptgRef = pack("C", $this->ptg['ptgRefV']);
00818         }
00819         elseif ($class == 2) {
00820             $ptgRef = pack("C", $this->ptg['ptgRefA']);
00821         }
00822         else {
00823             // TODO: use real error codes
00824             return $this->raiseError("Unknown class $class");
00825         }
00826         return $ptgRef.$row.$col;
00827     }
00828     
00837     function _convertRef3d($cell)
00838     {
00839         $class = 2; // as far as I know, this is magick.
00840  
00841         // Split the ref at the ! symbol
00842         list($ext_ref, $cell) = split('!', $cell);
00843  
00844         // Convert the external reference part (different for BIFF8)
00845         if ($this->_BIFF_version == 0x0500) {
00846             $ext_ref = $this->_packExtRef($ext_ref);
00847             if (PEAR::isError($ext_ref)) {
00848                 return $ext_ref;
00849             }
00850         }
00851         elseif ($this->_BIFF_version == 0x0600) {
00852             $ext_ref = $this->_getRefIndex($ext_ref);
00853             if (PEAR::isError($ext_ref)) {
00854                 return $ext_ref;
00855             }
00856         }
00857 
00858         // Convert the cell reference part
00859         list($row, $col) = $this->_cellToPackedRowcol($cell);
00860  
00861         // The ptg value depends on the class of the ptg.
00862         if ($class == 0) {
00863             $ptgRef = pack("C", $this->ptg['ptgRef3d']);
00864         }
00865         elseif ($class == 1) {
00866             $ptgRef = pack("C", $this->ptg['ptgRef3dV']);
00867         }
00868         elseif ($class == 2) {
00869             $ptgRef = pack("C", $this->ptg['ptgRef3dA']);
00870         }
00871         else {
00872             return $this->raiseError("Unknown class $class", 0, PEAR_ERROR_DIE);
00873         }
00874 
00875         return $ptgRef . $ext_ref. $row . $col;
00876     }
00877 
00886     function _packExtRef($ext_ref)
00887     {
00888         $ext_ref = preg_replace("/^'/", '', $ext_ref); // Remove leading  ' if any.
00889         $ext_ref = preg_replace("/'$/", '', $ext_ref); // Remove trailing ' if any.
00890  
00891         // Check if there is a sheet range eg., Sheet1:Sheet2.
00892         if (preg_match("/:/", $ext_ref))
00893         {
00894             list($sheet_name1, $sheet_name2) = split(':', $ext_ref);
00895  
00896             $sheet1 = $this->_getSheetIndex($sheet_name1);
00897             if ($sheet1 == -1) {
00898                 return $this->raiseError("Unknown sheet name $sheet_name1 in formula");
00899             }
00900             $sheet2 = $this->_getSheetIndex($sheet_name2);
00901             if ($sheet2 == -1) {
00902                 return $this->raiseError("Unknown sheet name $sheet_name2 in formula");
00903             }
00904  
00905             // Reverse max and min sheet numbers if necessary
00906             if ($sheet1 > $sheet2) {
00907                 list($sheet1, $sheet2) = array($sheet2, $sheet1);
00908             }
00909         }
00910         else // Single sheet name only.
00911         {
00912             $sheet1 = $this->_getSheetIndex($ext_ref);
00913             if ($sheet1 == -1) {
00914                 return $this->raiseError("Unknown sheet name $ext_ref in formula");
00915             }
00916             $sheet2 = $sheet1;
00917         }
00918  
00919         // References are stored relative to 0xFFFF.
00920         $offset = -1 - $sheet1;
00921 
00922         return pack('vdvv', $offset, 0x00, $sheet1, $sheet2);
00923     }
00924 
00935     function _getRefIndex($ext_ref)
00936     {
00937         $ext_ref = preg_replace("/^'/", '', $ext_ref); // Remove leading  ' if any.
00938         $ext_ref = preg_replace("/'$/", '', $ext_ref); // Remove trailing ' if any.
00939  
00940         // Check if there is a sheet range eg., Sheet1:Sheet2.
00941         if (preg_match("/:/", $ext_ref))
00942         {
00943             list($sheet_name1, $sheet_name2) = split(':', $ext_ref);
00944  
00945             $sheet1 = $this->_getSheetIndex($sheet_name1);
00946             if ($sheet1 == -1) {
00947                 return $this->raiseError("Unknown sheet name $sheet_name1 in formula");
00948             }
00949             $sheet2 = $this->_getSheetIndex($sheet_name2);
00950             if ($sheet2 == -1) {
00951                 return $this->raiseError("Unknown sheet name $sheet_name2 in formula");
00952             }
00953  
00954             // Reverse max and min sheet numbers if necessary
00955             if ($sheet1 > $sheet2) {
00956                 list($sheet1, $sheet2) = array($sheet2, $sheet1);
00957             }
00958         }
00959         else // Single sheet name only.
00960         {
00961             $sheet1 = $this->_getSheetIndex($ext_ref);
00962             if ($sheet1 == -1) {
00963                 return $this->raiseError("Unknown sheet name $ext_ref in formula");
00964             }
00965             $sheet2 = $sheet1;
00966         }
00967  
00968         // assume all references belong to this document
00969         $supbook_index = 0x00;
00970         $ref = pack('vvv', $supbook_index, $sheet1, $sheet2);
00971         $total_references = count($this->_references);
00972         $index = -1;
00973         for ($i = 0; $i < $total_references; $i++)
00974         {
00975             if ($ref == $this->_references[$i]) {
00976                 $index = $i;
00977                 break;
00978             }
00979         }
00980         // if REF was not found add it to references array
00981         if ($index == -1)
00982         {
00983             $this->_references[$total_references] = $ref;
00984             $index = $total_references;
00985         }
00986 
00987         return pack('v', $index);
00988     }
00989 
00998     function _getSheetIndex($sheet_name)
00999     {
01000         if (!isset($this->_ext_sheets[$sheet_name])) {
01001             return -1;
01002         }
01003         else {
01004             return $this->_ext_sheets[$sheet_name];
01005         }
01006     }
01007 
01018     function setExtSheet($name, $index)
01019     {
01020         $this->_ext_sheets[$name] = $index;
01021     }
01022 
01030     function _cellToPackedRowcol($cell)
01031     {
01032         $cell = strtoupper($cell);
01033         list($row, $col, $row_rel, $col_rel) = $this->_cellToRowcol($cell);
01034         if ($col >= 256) {
01035             return $this->raiseError("Column in: $cell greater than 255");
01036         }
01037         // FIXME: change for BIFF8
01038         if ($row >= 16384) {
01039             return $this->raiseError("Row in: $cell greater than 16384 ");
01040         }
01041     
01042         // Set the high bits to indicate if row or col are relative.
01043         if ($this->_BIFF_version == 0x0500) {
01044             $row    |= $col_rel << 14;
01045             $row    |= $row_rel << 15;
01046             $col     = pack('C', $col);
01047         }
01048         elseif ($this->_BIFF_version == 0x0600) {
01049             $col    |= $col_rel << 14;
01050             $col    |= $row_rel << 15;
01051             $col     = pack('v', $col);
01052         }
01053         $row     = pack('v', $row);
01054     
01055         return array($row, $col);
01056     }
01057     
01066     function _rangeToPackedRange($range)
01067     {
01068         preg_match('/(\$)?(\d+)\:(\$)?(\d+)/', $range, $match);
01069         // return absolute rows if there is a $ in the ref
01070         $row1_rel = empty($match[1]) ? 1 : 0;
01071         $row1     = $match[2];
01072         $row2_rel = empty($match[3]) ? 1 : 0;
01073         $row2     = $match[4];
01074         // Convert 1-index to zero-index
01075         $row1--;
01076         $row2--;
01077         // Trick poor inocent Excel
01078         $col1 = 0;
01079         $col2 = 16383; // FIXME: maximum possible value for Excel 5 (change this!!!)
01080 
01081         // FIXME: this changes for BIFF8
01082         if (($row1 >= 16384) or ($row2 >= 16384)) {
01083             return $this->raiseError("Row in: $range greater than 16384 ");
01084         }
01085     
01086         // Set the high bits to indicate if rows are relative.
01087         if ($this->_BIFF_version == 0x0500) {
01088             $row1    |= $row1_rel << 14; // FIXME: probably a bug
01089             $row2    |= $row2_rel << 15;
01090             $col1     = pack('C', $col1);
01091             $col2     = pack('C', $col2);
01092         }
01093         elseif ($this->_BIFF_version == 0x0600) {
01094             $col1    |= $row1_rel << 15;
01095             $col2    |= $row2_rel << 15;
01096             $col1     = pack('v', $col1);
01097             $col2     = pack('v', $col2);
01098         }
01099         $row1     = pack('v', $row1);
01100         $row2     = pack('v', $row2);
01101     
01102         return array($row1, $col1, $row2, $col2);
01103     }
01104 
01114     function _cellToRowcol($cell)
01115     {
01116         preg_match('/(\$)?([A-I]?[A-Z])(\$)?(\d+)/',$cell,$match);
01117         // return absolute column if there is a $ in the ref
01118         $col_rel = empty($match[1]) ? 1 : 0;
01119         $col_ref = $match[2];
01120         $row_rel = empty($match[3]) ? 1 : 0;
01121         $row     = $match[4];
01122         
01123         // Convert base26 column string to a number.
01124         $expn   = strlen($col_ref) - 1;
01125         $col    = 0;
01126         for ($i=0; $i < strlen($col_ref); $i++)
01127         {
01128             $col += (ord($col_ref{$i}) - ord('A') + 1) * pow(26, $expn);
01129             $expn--;
01130         }
01131     
01132         // Convert 1-index to zero-index
01133         $row--;
01134         $col--;
01135     
01136         return array($row, $col, $row_rel, $col_rel);
01137     }
01138     
01144     function _advance()
01145     {
01146         $i = $this->_current_char;
01147         // eat up white spaces
01148         if ($i < strlen($this->_formula))
01149         {
01150             while ($this->_formula{$i} == " ") {
01151                 $i++;
01152             }
01153             if ($i < strlen($this->_formula) - 1) {
01154                 $this->_lookahead = $this->_formula{$i+1};
01155             }
01156             $token = "";
01157         }
01158         while ($i < strlen($this->_formula))
01159         {
01160             $token .= $this->_formula{$i};
01161             if ($i < strlen($this->_formula) - 1) {
01162                 $this->_lookahead = $this->_formula{$i+1};
01163             }
01164             else {
01165                 $this->_lookahead = '';
01166             }
01167             if ($this->_match($token) != '')
01168             {
01169                 //if ($i < strlen($this->_formula) - 1) {
01170                 //    $this->_lookahead = $this->_formula{$i+1};
01171                 //}
01172                 $this->_current_char = $i + 1;
01173                 $this->_current_token = $token;
01174                 return 1;
01175             }
01176             if ($i < strlen($this->_formula) - 2) {
01177                 $this->_lookahead = $this->_formula{$i+2};
01178             }
01179             // if we run out of characters _lookahead becomes empty
01180             else {
01181                 $this->_lookahead = '';
01182             }
01183             $i++;
01184         }
01185         //die("Lexical error ".$this->_current_char);
01186     }
01187     
01195     function _match($token)
01196     {
01197         switch($token)
01198         {
01199             case SPREADSHEET_EXCEL_WRITER_ADD:
01200                 return $token;
01201                 break;
01202             case SPREADSHEET_EXCEL_WRITER_SUB:
01203                 return $token;
01204                 break;
01205             case SPREADSHEET_EXCEL_WRITER_MUL:
01206                 return $token;
01207                 break;
01208             case SPREADSHEET_EXCEL_WRITER_DIV:
01209                 return $token;
01210                 break;
01211             case SPREADSHEET_EXCEL_WRITER_OPEN:
01212                 return $token;
01213                 break;
01214             case SPREADSHEET_EXCEL_WRITER_CLOSE:
01215                 return $token;
01216                 break;
01217             case SPREADSHEET_EXCEL_WRITER_COMA:
01218                 return $token;
01219                 break;
01220             case SPREADSHEET_EXCEL_WRITER_SEMICOLON:
01221                 return $token;
01222                 break;
01223             case SPREADSHEET_EXCEL_WRITER_GT:
01224                 if ($this->_lookahead == '=') { // it's a GE token
01225                     break;
01226                 }
01227                 return $token;
01228                 break;
01229             case SPREADSHEET_EXCEL_WRITER_LT:
01230                 // it's a LE or a NE token
01231                 if (($this->_lookahead == '=') or ($this->_lookahead == '>')) {
01232                     break;
01233                 }
01234                 return $token;
01235                 break;
01236             case SPREADSHEET_EXCEL_WRITER_GE:
01237                 return $token;
01238                 break;
01239             case SPREADSHEET_EXCEL_WRITER_LE:
01240                 return $token;
01241                 break;
01242             case SPREADSHEET_EXCEL_WRITER_EQ:
01243                 return $token;
01244                 break;
01245             case SPREADSHEET_EXCEL_WRITER_NE:
01246                 return $token;
01247                 break;
01248             default:
01249                 // if it's a reference
01250                 if (preg_match('/^\$?[A-Ia-i]?[A-Za-z]\$?[0-9]+$/',$token) and
01251                    !ereg("[0-9]",$this->_lookahead) and 
01252                    ($this->_lookahead != ':') and ($this->_lookahead != '.') and
01253                    ($this->_lookahead != '!'))
01254                 {
01255                     return $token;
01256                 }
01257                 // If it's an external reference (Sheet1!A1 or Sheet1:Sheet2!A1)
01258                 elseif (preg_match("/^\w+(\:\w+)?\![A-Ia-i]?[A-Za-z][0-9]+$/",$token) and
01259                        !ereg("[0-9]",$this->_lookahead) and
01260                        ($this->_lookahead != ':') and ($this->_lookahead != '.'))
01261                 {
01262                     return $token;
01263                 }
01264                 // If it's an external reference (Sheet1!A1 or Sheet1:Sheet2!A1)
01265                 elseif (preg_match("/^'\w+(\:\w+)?'\![A-Ia-i]?[A-Za-z][0-9]+$/",$token) and
01266                        !ereg("[0-9]",$this->_lookahead) and
01267                        ($this->_lookahead != ':') and ($this->_lookahead != '.'))
01268                 {
01269                     return $token;
01270                 }
01271                 // if it's a range (A1:A2)
01272                 elseif (preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+:(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+$/",$token) and 
01273                        !ereg("[0-9]",$this->_lookahead))
01274                 {
01275                     return $token;
01276                 }
01277                 // if it's a range (A1..A2)
01278                 elseif (preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+\.\.(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+$/",$token) and 
01279                        !ereg("[0-9]",$this->_lookahead))
01280                 {
01281                     return $token;
01282                 }
01283                 // If it's an external range like Sheet1!A1 or Sheet1:Sheet2!A1:B2
01284                 elseif (preg_match("/^\w+(\:\w+)?\!([A-Ia-i]?[A-Za-z])?[0-9]+:([A-Ia-i]?[A-Za-z])?[0-9]+$/",$token) and
01285                        !ereg("[0-9]",$this->_lookahead))
01286                 {
01287                     return $token;
01288                 }
01289                 // If it's an external range like 'Sheet1'!A1 or 'Sheet1:Sheet2'!A1:B2
01290                 elseif (preg_match("/^'\w+(\:\w+)?'\!([A-Ia-i]?[A-Za-z])?[0-9]+:([A-Ia-i]?[A-Za-z])?[0-9]+$/",$token) and
01291                        !ereg("[0-9]",$this->_lookahead))
01292                 {
01293                     return $token;
01294                 }
01295                 // If it's a number (check that it's not a sheet name or range)
01296                 elseif (is_numeric($token) and 
01297                         (!is_numeric($token.$this->_lookahead) or ($this->_lookahead == '')) and
01298                         ($this->_lookahead != '!') and ($this->_lookahead != ':'))
01299                 {
01300                     return $token;
01301                 }
01302                 // If it's a string (of maximum 255 characters)
01303                 elseif (ereg("^\"[^\"]{0,255}\"$",$token))
01304                 {
01305                     return $token;
01306                 }
01307                 // if it's a function call
01308                 elseif (eregi("^[A-Z0-9\xc0-\xdc\.]+$",$token) and ($this->_lookahead == "("))
01309                 {
01310                     return $token;
01311                 }
01312                 return '';
01313         }
01314     }
01315     
01324     function parse($formula)
01325     {
01326         $this->_current_char = 0;
01327         $this->_formula      = $formula;
01328         $this->_lookahead    = $formula{1};
01329         $this->_advance();
01330         $this->_parse_tree   = $this->_condition();
01331         if (PEAR::isError($this->_parse_tree)) {
01332             return $this->_parse_tree;
01333         }
01334         return true;
01335     }
01336     
01344     function _condition()
01345     {
01346         $result = $this->_expression();
01347         if (PEAR::isError($result)) {
01348             return $result;
01349         }
01350         if ($this->_current_token == SPREADSHEET_EXCEL_WRITER_LT)
01351         {
01352             $this->_advance();
01353             $result2 = $this->_expression();
01354             if (PEAR::isError($result2)) {
01355                 return $result2;
01356             }
01357             $result = $this->_createTree('ptgLT', $result, $result2);
01358         }
01359         elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_GT) 
01360         {
01361             $this->_advance();
01362             $result2 = $this->_expression();
01363             if (PEAR::isError($result2)) {
01364                 return $result2;
01365             }
01366             $result = $this->_createTree('ptgGT', $result, $result2);
01367         }
01368         elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_LE) 
01369         {
01370             $this->_advance();
01371             $result2 = $this->_expression();
01372             if (PEAR::isError($result2)) {
01373                 return $result2;
01374             }
01375             $result = $this->_createTree('ptgLE', $result, $result2);
01376         }
01377         elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_GE) 
01378         {
01379             $this->_advance();
01380             $result2 = $this->_expression();
01381             if (PEAR::isError($result2)) {
01382                 return $result2;
01383             }
01384             $result = $this->_createTree('ptgGE', $result, $result2);
01385         }
01386         elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_EQ) 
01387         {
01388             $this->_advance();
01389             $result2 = $this->_expression();
01390             if (PEAR::isError($result2)) {
01391                 return $result2;
01392             }
01393             $result = $this->_createTree('ptgEQ', $result, $result2);
01394         }
01395         elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_NE) 
01396         {
01397             $this->_advance();
01398             $result2 = $this->_expression();
01399             if (PEAR::isError($result2)) {
01400                 return $result2;
01401             }
01402             $result = $this->_createTree('ptgNE', $result, $result2);
01403         }
01404         return $result;
01405     }
01406 
01416     function _expression()
01417     {
01418         // If it's a string return a string node
01419         if (ereg("^\"[^\"]{0,255}\"$", $this->_current_token)) {
01420             $result = $this->_createTree($this->_current_token, '', '');
01421             $this->_advance();
01422             return $result;
01423         }
01424         // catch "-" Term
01425         elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_SUB) {
01426             $this->_advance();
01427             $result2 = $this->_expression();
01428             $result = $this->_createTree('ptgUminus', $result2, '');
01429             return $result;
01430         }
01431         $result = $this->_term();
01432         if (PEAR::isError($result)) {
01433             return $result;
01434         }
01435         while (($this->_current_token == SPREADSHEET_EXCEL_WRITER_ADD) or 
01436                ($this->_current_token == SPREADSHEET_EXCEL_WRITER_SUB))
01437         {
01438             if ($this->_current_token == SPREADSHEET_EXCEL_WRITER_ADD)
01439             {
01440                 $this->_advance();
01441                 $result2 = $this->_term();
01442                 if (PEAR::isError($result2)) {
01443                     return $result2;
01444                 }
01445                 $result = $this->_createTree('ptgAdd', $result, $result2);
01446             }
01447             else 
01448             {
01449                 $this->_advance();
01450                 $result2 = $this->_term();
01451                 if (PEAR::isError($result2)) {
01452                     return $result2;
01453                 }
01454                 $result = $this->_createTree('ptgSub', $result, $result2);
01455             }
01456         }
01457         return $result;
01458     }
01459     
01468     function _parenthesizedExpression()
01469     {
01470         $result = $this->_createTree('ptgParen', $this->_expression(), '');
01471         return $result;
01472     }
01473     
01481     function _term()
01482     {
01483         $result = $this->_fact();
01484         if (PEAR::isError($result)) {
01485             return $result;
01486         }
01487         while (($this->_current_token == SPREADSHEET_EXCEL_WRITER_MUL) or 
01488                ($this->_current_token == SPREADSHEET_EXCEL_WRITER_DIV))
01489         {
01490             if ($this->_current_token == SPREADSHEET_EXCEL_WRITER_MUL)
01491             {
01492                 $this->_advance();
01493                 $result2 = $this->_fact();
01494                 if (PEAR::isError($result2)) {
01495                     return $result2;
01496                 }
01497                 $result = $this->_createTree('ptgMul', $result, $result2);
01498             }
01499             else 
01500             {
01501                 $this->_advance();
01502                 $result2 = $this->_fact();
01503                 if (PEAR::isError($result2)) {
01504                     return $result2;
01505                 }
01506                 $result = $this->_createTree('ptgDiv', $result, $result2);
01507             }
01508         }
01509         return $result;
01510     }
01511     
01523     function _fact()
01524     {
01525         if ($this->_current_token == SPREADSHEET_EXCEL_WRITER_OPEN)
01526         {
01527             $this->_advance();         // eat the "("
01528             $result = $this->_parenthesizedExpression();
01529             if ($this->_current_token != SPREADSHEET_EXCEL_WRITER_CLOSE) {
01530                 return $this->raiseError("')' token expected.");
01531             }
01532             $this->_advance();         // eat the ")"
01533             return $result;
01534         }
01535         // if it's a reference
01536         if (preg_match('/^\$?[A-Ia-i]?[A-Za-z]\$?[0-9]+$/',$this->_current_token))
01537         {
01538             $result = $this->_createTree($this->_current_token, '', '');
01539             $this->_advance();
01540             return $result;
01541         }
01542         // If it's an external reference (Sheet1!A1 or Sheet1:Sheet2!A1)
01543         elseif (preg_match("/^\w+(\:\w+)?\![A-Ia-i]?[A-Za-z][0-9]+$/",$this->_current_token))
01544         {
01545             $result = $this->_createTree($this->_current_token, '', '');
01546             $this->_advance();
01547             return $result;
01548         }
01549         // If it's an external reference (Sheet1!A1 or Sheet1:Sheet2!A1)
01550         elseif (preg_match("/^'\w+(\:\w+)?'\![A-Ia-i]?[A-Za-z][0-9]+$/",$this->_current_token))
01551         {
01552             $result = $this->_createTree($this->_current_token, '', '');
01553             $this->_advance();
01554             return $result;
01555         }
01556         // if it's a range
01557         elseif (preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+:(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+$/",$this->_current_token) or 
01558                 preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+\.\.(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+$/",$this->_current_token))
01559         {
01560             $result = $this->_current_token;
01561             $this->_advance();
01562             return $result;
01563         }
01564         // If it's an external range (Sheet1!A1 or Sheet1!A1:B2)
01565         elseif (preg_match("/^\w+(\:\w+)?\!([A-Ia-i]?[A-Za-z])?[0-9]+:([A-Ia-i]?[A-Za-z])?[0-9]+$/",$this->_current_token))
01566         {
01567             $result = $this->_current_token;
01568             $this->_advance();
01569             return $result;
01570         }
01571         // If it's an external range ('Sheet1'!A1 or 'Sheet1'!A1:B2)
01572         elseif (preg_match("/^'\w+(\:\w+)?'\!([A-Ia-i]?[A-Za-z])?[0-9]+:([A-Ia-i]?[A-Za-z])?[0-9]+$/",$this->_current_token))
01573         {
01574             $result = $this->_current_token;
01575             $this->_advance();
01576             return $result;
01577         }
01578         elseif (is_numeric($this->_current_token))
01579         {
01580             $result = $this->_createTree($this->_current_token, '', '');
01581             $this->_advance();
01582             return $result;
01583         }
01584         // if it's a function call
01585         elseif (eregi("^[A-Z0-9\xc0-\xdc\.]+$",$this->_current_token))
01586         {
01587             $result = $this->_func();
01588             return $result;
01589         }
01590         return $this->raiseError("Syntax error: ".$this->_current_token.
01591                                  ", lookahead: ".$this->_lookahead.
01592                                  ", current char: ".$this->_current_char);
01593     }
01594     
01602     function _func()
01603     {
01604         $num_args = 0; // number of arguments received
01605         $function = $this->_current_token;
01606         $this->_advance();
01607         $this->_advance();         // eat the "("
01608         while ($this->_current_token != ')')
01609         {
01610             if ($num_args > 0)
01611             {
01612                 if ($this->_current_token == SPREADSHEET_EXCEL_WRITER_COMA ||
01613                     $this->_current_token == SPREADSHEET_EXCEL_WRITER_SEMICOLON)
01614                 {
01615                     $this->_advance();  // eat the "," or ";"
01616                 }
01617                 else {
01618                     return $this->raiseError("Syntax error: comma expected in ".
01619                                       "function $function, arg #{$num_args}");
01620                 }
01621                 $result2 = $this->_condition();
01622                 if (PEAR::isError($result2)) {
01623                     return $result2;
01624                 }
01625                 $result = $this->_createTree('arg', $result, $result2);
01626             }
01627             else // first argument
01628             {
01629                 $result2 = $this->_condition();
01630                 if (PEAR::isError($result2)) {
01631                     return $result2;
01632                 }
01633                 $result = $this->_createTree('arg', '', $result2);
01634             }
01635             $num_args++;
01636         }
01637         $args = $this->_functions[$function][1];
01638         // If fixed number of args eg. TIME($i,$j,$k). Check that the number of args is valid.
01639         if (($args >= 0) and ($args != $num_args)) {
01640             return $this->raiseError("Incorrect number of arguments in function $function() ");
01641         }
01642     
01643         $result = $this->_createTree($function, $result, $num_args);
01644         $this->_advance();         // eat the ")"
01645         return $result;
01646     }
01647     
01658     function _createTree($value, $left, $right)
01659     {
01660         return array('value' => $value, 'left' => $left, 'right' => $right);
01661     }
01662     
01690     function toReversePolish($tree = array())
01691     {
01692         $polish = ""; // the string we are going to return
01693         if (empty($tree)) // If it's the first call use _parse_tree
01694         {
01695             $tree = $this->_parse_tree;
01696         }
01697         if (is_array($tree['left']))
01698         {
01699             $converted_tree = $this->toReversePolish($tree['left']);
01700             if (PEAR::isError($converted_tree)) {
01701                 return $converted_tree;
01702             }
01703             $polish .= $converted_tree;
01704         }
01705         elseif ($tree['left'] != '') // It's a final node
01706         {
01707             $converted_tree = $this->_convert($tree['left']);
01708             if (PEAR::isError($converted_tree)) {
01709                 return $converted_tree;
01710             }
01711             $polish .= $converted_tree;
01712         }
01713         if (is_array($tree['right']))
01714         {
01715             $converted_tree = $this->toReversePolish($tree['right']);
01716             if (PEAR::isError($converted_tree)) {
01717                 return $converted_tree;
01718             }
01719             $polish .= $converted_tree;
01720         }
01721         elseif ($tree['right'] != '') // It's a final node
01722         {
01723             $converted_tree = $this->_convert($tree['right']);
01724             if (PEAR::isError($converted_tree)) {
01725                 return $converted_tree;
01726             }
01727             $polish .= $converted_tree;
01728         }
01729         // if it's a function convert it here (so we can set it's arguments)
01730         if (preg_match("/^[A-Z0-9\xc0-\xdc\.]+$/",$tree['value']) and
01731             !preg_match('/^([A-Ia-i]?[A-Za-z])(\d+)$/',$tree['value']) and
01732             !preg_match("/^[A-Ia-i]?[A-Za-z](\d+)\.\.[A-Ia-i]?[A-Za-z](\d+)$/",$tree['value']) and
01733             !is_numeric($tree['value']) and
01734             !isset($this->ptg[$tree['value']]))
01735         {
01736             // left subtree for a function is always an array.
01737             if ($tree['left'] != '') {
01738                 $left_tree = $this->toReversePolish($tree['left']);
01739             }
01740             else {
01741                 $left_tree = '';
01742             }
01743             if (PEAR::isError($left_tree)) {
01744                 return $left_tree;
01745             }
01746             // add it's left subtree and return.
01747             return $left_tree.$this->_convertFunction($tree['value'], $tree['right']);
01748         }
01749         else
01750         {
01751             $converted_tree = $this->_convert($tree['value']);
01752             if (PEAR::isError($converted_tree)) {
01753                 return $converted_tree;
01754             }
01755         }
01756         $polish .= $converted_tree;
01757         return $polish;
01758     }
01759 }
01760 ?>