|
Moodle
2.2.1
http://www.collinsharper.com
|
|
Moodle
2.2.1
http://www.collinsharper.com
|
00001 <?php 00028 define('ADD',"+"); 00029 00033 define('SUB',"-"); 00034 00038 define('EQUAL',"="); 00039 00043 define('MUL',"*"); 00044 00048 define('DIV',"/"); 00049 00053 define('OPEN',"("); 00054 00058 define('CLOSE',")"); 00059 00063 define('COMA',","); 00064 00071 class Parser 00072 { 00079 function Parser($byte_order = 0) 00080 { 00081 $this->_current_char = 0; // The index of the character we are currently looking at. 00082 $this->_current_token = ''; // The token we are working on. 00083 $this->_formula = ""; // The formula to parse. 00084 $this->_lookahead = ''; // The character ahead of the current char. 00085 $this->_parse_tree = ''; // The parse tree to be generated. 00086 $this->_initialize_hashes(); // Initialize the hashes: ptg's and function's ptg's 00087 $this->_byte_order = $byte_order; // Little Endian or Big Endian 00088 $this->_func_args = 0; // Number of arguments for the current function 00089 $this->_volatile = 0; 00090 } 00091 00095 function _initialize_hashes() 00096 { 00097 // The Excel ptg indices 00098 $this->ptg = array( 00099 'ptgExp' => 0x01, 00100 'ptgTbl' => 0x02, 00101 'ptgAdd' => 0x03, 00102 'ptgSub' => 0x04, 00103 'ptgMul' => 0x05, 00104 'ptgDiv' => 0x06, 00105 'ptgPower' => 0x07, 00106 'ptgConcat' => 0x08, 00107 'ptgLT' => 0x09, 00108 'ptgLE' => 0x0A, 00109 'ptgEQ' => 0x0B, 00110 'ptgGE' => 0x0C, 00111 'ptgGT' => 0x0D, 00112 'ptgNE' => 0x0E, 00113 'ptgIsect' => 0x0F, 00114 'ptgUnion' => 0x10, 00115 'ptgRange' => 0x11, 00116 'ptgUplus' => 0x12, 00117 'ptgUminus' => 0x13, 00118 'ptgPercent' => 0x14, 00119 'ptgParen' => 0x15, 00120 'ptgMissArg' => 0x16, 00121 'ptgStr' => 0x17, 00122 'ptgAttr' => 0x19, 00123 'ptgSheet' => 0x1A, 00124 'ptgEndSheet' => 0x1B, 00125 'ptgErr' => 0x1C, 00126 'ptgBool' => 0x1D, 00127 'ptgInt' => 0x1E, 00128 'ptgNum' => 0x1F, 00129 'ptgArray' => 0x20, 00130 'ptgFunc' => 0x21, 00131 'ptgFuncVar' => 0x22, 00132 'ptgName' => 0x23, 00133 'ptgRef' => 0x24, 00134 'ptgArea' => 0x25, 00135 'ptgMemArea' => 0x26, 00136 'ptgMemErr' => 0x27, 00137 'ptgMemNoMem' => 0x28, 00138 'ptgMemFunc' => 0x29, 00139 'ptgRefErr' => 0x2A, 00140 'ptgAreaErr' => 0x2B, 00141 'ptgRefN' => 0x2C, 00142 'ptgAreaN' => 0x2D, 00143 'ptgMemAreaN' => 0x2E, 00144 'ptgMemNoMemN' => 0x2F, 00145 'ptgNameX' => 0x39, 00146 'ptgRef3d' => 0x3A, 00147 'ptgArea3d' => 0x3B, 00148 'ptgRefErr3d' => 0x3C, 00149 'ptgAreaErr3d' => 0x3D, 00150 'ptgArrayV' => 0x40, 00151 'ptgFuncV' => 0x41, 00152 'ptgFuncVarV' => 0x42, 00153 'ptgNameV' => 0x43, 00154 'ptgRefV' => 0x44, 00155 'ptgAreaV' => 0x45, 00156 'ptgMemAreaV' => 0x46, 00157 'ptgMemErrV' => 0x47, 00158 'ptgMemNoMemV' => 0x48, 00159 'ptgMemFuncV' => 0x49, 00160 'ptgRefErrV' => 0x4A, 00161 'ptgAreaErrV' => 0x4B, 00162 'ptgRefNV' => 0x4C, 00163 'ptgAreaNV' => 0x4D, 00164 'ptgMemAreaNV' => 0x4E, 00165 'ptgMemNoMemN' => 0x4F, 00166 'ptgFuncCEV' => 0x58, 00167 'ptgNameXV' => 0x59, 00168 'ptgRef3dV' => 0x5A, 00169 'ptgArea3dV' => 0x5B, 00170 'ptgRefErr3dV' => 0x5C, 00171 'ptgAreaErr3d' => 0x5D, 00172 'ptgArrayA' => 0x60, 00173 'ptgFuncA' => 0x61, 00174 'ptgFuncVarA' => 0x62, 00175 'ptgNameA' => 0x63, 00176 'ptgRefA' => 0x64, 00177 'ptgAreaA' => 0x65, 00178 'ptgMemAreaA' => 0x66, 00179 'ptgMemErrA' => 0x67, 00180 'ptgMemNoMemA' => 0x68, 00181 'ptgMemFuncA' => 0x69, 00182 'ptgRefErrA' => 0x6A, 00183 'ptgAreaErrA' => 0x6B, 00184 'ptgRefNA' => 0x6C, 00185 'ptgAreaNA' => 0x6D, 00186 'ptgMemAreaNA' => 0x6E, 00187 'ptgMemNoMemN' => 0x6F, 00188 'ptgFuncCEA' => 0x78, 00189 'ptgNameXA' => 0x79, 00190 'ptgRef3dA' => 0x7A, 00191 'ptgArea3dA' => 0x7B, 00192 'ptgRefErr3dA' => 0x7C, 00193 'ptgAreaErr3d' => 0x7D 00194 ); 00195 00196 // Thanks to Michael Meeks and Gnumeric for the initial arg values. 00197 // 00198 // The following hash was generated by "function_locale.pl" in the distro. 00199 // Refer to function_locale.pl for non-English function names. 00200 // 00201 // The array elements are as follow: 00202 // ptg: The Excel function ptg code. 00203 // args: The number of arguments that the function takes: 00204 // >=0 is a fixed number of arguments. 00205 // -1 is a variable number of arguments. 00206 // class: The reference, value or array class of the function args. 00207 // vol: The function is volatile. 00208 // 00209 $this->_functions = array( 00210 // function ptg args class vol 00211 'COUNT' => array( 0, -1, 0, 0 ), 00212 'IF' => array( 1, -1, 1, 0 ), 00213 'ISNA' => array( 2, 1, 1, 0 ), 00214 'ISERROR' => array( 3, 1, 1, 0 ), 00215 'SUM' => array( 4, -1, 0, 0 ), 00216 'AVERAGE' => array( 5, -1, 0, 0 ), 00217 'MIN' => array( 6, -1, 0, 0 ), 00218 'MAX' => array( 7, -1, 0, 0 ), 00219 'ROW' => array( 8, -1, 0, 0 ), 00220 'COLUMN' => array( 9, -1, 0, 0 ), 00221 'NA' => array( 10, 0, 0, 0 ), 00222 'NPV' => array( 11, -1, 1, 0 ), 00223 'STDEV' => array( 12, -1, 0, 0 ), 00224 'DOLLAR' => array( 13, -1, 1, 0 ), 00225 'FIXED' => array( 14, -1, 1, 0 ), 00226 'SIN' => array( 15, 1, 1, 0 ), 00227 'COS' => array( 16, 1, 1, 0 ), 00228 'TAN' => array( 17, 1, 1, 0 ), 00229 'ATAN' => array( 18, 1, 1, 0 ), 00230 'PI' => array( 19, 0, 1, 0 ), 00231 'SQRT' => array( 20, 1, 1, 0 ), 00232 'EXP' => array( 21, 1, 1, 0 ), 00233 'LN' => array( 22, 1, 1, 0 ), 00234 'LOG10' => array( 23, 1, 1, 0 ), 00235 'ABS' => array( 24, 1, 1, 0 ), 00236 'INT' => array( 25, 1, 1, 0 ), 00237 'SIGN' => array( 26, 1, 1, 0 ), 00238 'ROUND' => array( 27, 2, 1, 0 ), 00239 'LOOKUP' => array( 28, -1, 0, 0 ), 00240 'INDEX' => array( 29, -1, 0, 1 ), 00241 'REPT' => array( 30, 2, 1, 0 ), 00242 'MID' => array( 31, 3, 1, 0 ), 00243 'LEN' => array( 32, 1, 1, 0 ), 00244 'VALUE' => array( 33, 1, 1, 0 ), 00245 'TRUE' => array( 34, 0, 1, 0 ), 00246 'FALSE' => array( 35, 0, 1, 0 ), 00247 'AND' => array( 36, -1, 0, 0 ), 00248 'OR' => array( 37, -1, 0, 0 ), 00249 'NOT' => array( 38, 1, 1, 0 ), 00250 'MOD' => array( 39, 2, 1, 0 ), 00251 'DCOUNT' => array( 40, 3, 0, 0 ), 00252 'DSUM' => array( 41, 3, 0, 0 ), 00253 'DAVERAGE' => array( 42, 3, 0, 0 ), 00254 'DMIN' => array( 43, 3, 0, 0 ), 00255 'DMAX' => array( 44, 3, 0, 0 ), 00256 'DSTDEV' => array( 45, 3, 0, 0 ), 00257 'VAR' => array( 46, -1, 0, 0 ), 00258 'DVAR' => array( 47, 3, 0, 0 ), 00259 'TEXT' => array( 48, 2, 1, 0 ), 00260 'LINEST' => array( 49, -1, 0, 0 ), 00261 'TREND' => array( 50, -1, 0, 0 ), 00262 'LOGEST' => array( 51, -1, 0, 0 ), 00263 'GROWTH' => array( 52, -1, 0, 0 ), 00264 'PV' => array( 56, -1, 1, 0 ), 00265 'FV' => array( 57, -1, 1, 0 ), 00266 'NPER' => array( 58, -1, 1, 0 ), 00267 'PMT' => array( 59, -1, 1, 0 ), 00268 'RATE' => array( 60, -1, 1, 0 ), 00269 'MIRR' => array( 61, 3, 0, 0 ), 00270 'IRR' => array( 62, -1, 0, 0 ), 00271 'RAND' => array( 63, 0, 1, 1 ), 00272 'MATCH' => array( 64, -1, 0, 0 ), 00273 'DATE' => array( 65, 3, 1, 0 ), 00274 'TIME' => array( 66, 3, 1, 0 ), 00275 'DAY' => array( 67, 1, 1, 0 ), 00276 'MONTH' => array( 68, 1, 1, 0 ), 00277 'YEAR' => array( 69, 1, 1, 0 ), 00278 'WEEKDAY' => array( 70, -1, 1, 0 ), 00279 'HOUR' => array( 71, 1, 1, 0 ), 00280 'MINUTE' => array( 72, 1, 1, 0 ), 00281 'SECOND' => array( 73, 1, 1, 0 ), 00282 'NOW' => array( 74, 0, 1, 1 ), 00283 'AREAS' => array( 75, 1, 0, 1 ), 00284 'ROWS' => array( 76, 1, 0, 1 ), 00285 'COLUMNS' => array( 77, 1, 0, 1 ), 00286 'OFFSET' => array( 78, -1, 0, 1 ), 00287 'SEARCH' => array( 82, -1, 1, 0 ), 00288 'TRANSPOSE' => array( 83, 1, 1, 0 ), 00289 'TYPE' => array( 86, 1, 1, 0 ), 00290 'ATAN2' => array( 97, 2, 1, 0 ), 00291 'ASIN' => array( 98, 1, 1, 0 ), 00292 'ACOS' => array( 99, 1, 1, 0 ), 00293 'CHOOSE' => array( 100, -1, 1, 0 ), 00294 'HLOOKUP' => array( 101, -1, 0, 0 ), 00295 'VLOOKUP' => array( 102, -1, 0, 0 ), 00296 'ISREF' => array( 105, 1, 0, 0 ), 00297 'LOG' => array( 109, -1, 1, 0 ), 00298 'CHAR' => array( 111, 1, 1, 0 ), 00299 'LOWER' => array( 112, 1, 1, 0 ), 00300 'UPPER' => array( 113, 1, 1, 0 ), 00301 'PROPER' => array( 114, 1, 1, 0 ), 00302 'LEFT' => array( 115, -1, 1, 0 ), 00303 'RIGHT' => array( 116, -1, 1, 0 ), 00304 'EXACT' => array( 117, 2, 1, 0 ), 00305 'TRIM' => array( 118, 1, 1, 0 ), 00306 'REPLACE' => array( 119, 4, 1, 0 ), 00307 'SUBSTITUTE' => array( 120, -1, 1, 0 ), 00308 'CODE' => array( 121, 1, 1, 0 ), 00309 'FIND' => array( 124, -1, 1, 0 ), 00310 'CELL' => array( 125, -1, 0, 1 ), 00311 'ISERR' => array( 126, 1, 1, 0 ), 00312 'ISTEXT' => array( 127, 1, 1, 0 ), 00313 'ISNUMBER' => array( 128, 1, 1, 0 ), 00314 'ISBLANK' => array( 129, 1, 1, 0 ), 00315 'T' => array( 130, 1, 0, 0 ), 00316 'N' => array( 131, 1, 0, 0 ), 00317 'DATEVALUE' => array( 140, 1, 1, 0 ), 00318 'TIMEVALUE' => array( 141, 1, 1, 0 ), 00319 'SLN' => array( 142, 3, 1, 0 ), 00320 'SYD' => array( 143, 4, 1, 0 ), 00321 'DDB' => array( 144, -1, 1, 0 ), 00322 'INDIRECT' => array( 148, -1, 1, 1 ), 00323 'CALL' => array( 150, -1, 1, 0 ), 00324 'CLEAN' => array( 162, 1, 1, 0 ), 00325 'MDETERM' => array( 163, 1, 2, 0 ), 00326 'MINVERSE' => array( 164, 1, 2, 0 ), 00327 'MMULT' => array( 165, 2, 2, 0 ), 00328 'IPMT' => array( 167, -1, 1, 0 ), 00329 'PPMT' => array( 168, -1, 1, 0 ), 00330 'COUNTA' => array( 169, -1, 0, 0 ), 00331 'PRODUCT' => array( 183, -1, 0, 0 ), 00332 'FACT' => array( 184, 1, 1, 0 ), 00333 'DPRODUCT' => array( 189, 3, 0, 0 ), 00334 'ISNONTEXT' => array( 190, 1, 1, 0 ), 00335 'STDEVP' => array( 193, -1, 0, 0 ), 00336 'VARP' => array( 194, -1, 0, 0 ), 00337 'DSTDEVP' => array( 195, 3, 0, 0 ), 00338 'DVARP' => array( 196, 3, 0, 0 ), 00339 'TRUNC' => array( 197, -1, 1, 0 ), 00340 'ISLOGICAL' => array( 198, 1, 1, 0 ), 00341 'DCOUNTA' => array( 199, 3, 0, 0 ), 00342 'ROUNDUP' => array( 212, 2, 1, 0 ), 00343 'ROUNDDOWN' => array( 213, 2, 1, 0 ), 00344 'RANK' => array( 216, -1, 0, 0 ), 00345 'ADDRESS' => array( 219, -1, 1, 0 ), 00346 'DAYS360' => array( 220, -1, 1, 0 ), 00347 'TODAY' => array( 221, 0, 1, 1 ), 00348 'VDB' => array( 222, -1, 1, 0 ), 00349 'MEDIAN' => array( 227, -1, 0, 0 ), 00350 'SUMPRODUCT' => array( 228, -1, 2, 0 ), 00351 'SINH' => array( 229, 1, 1, 0 ), 00352 'COSH' => array( 230, 1, 1, 0 ), 00353 'TANH' => array( 231, 1, 1, 0 ), 00354 'ASINH' => array( 232, 1, 1, 0 ), 00355 'ACOSH' => array( 233, 1, 1, 0 ), 00356 'ATANH' => array( 234, 1, 1, 0 ), 00357 'DGET' => array( 235, 3, 0, 0 ), 00358 'INFO' => array( 244, 1, 1, 1 ), 00359 'DB' => array( 247, -1, 1, 0 ), 00360 'FREQUENCY' => array( 252, 2, 0, 0 ), 00361 'ERROR.TYPE' => array( 261, 1, 1, 0 ), 00362 'REGISTER.ID' => array( 267, -1, 1, 0 ), 00363 'AVEDEV' => array( 269, -1, 0, 0 ), 00364 'BETADIST' => array( 270, -1, 1, 0 ), 00365 'GAMMALN' => array( 271, 1, 1, 0 ), 00366 'BETAINV' => array( 272, -1, 1, 0 ), 00367 'BINOMDIST' => array( 273, 4, 1, 0 ), 00368 'CHIDIST' => array( 274, 2, 1, 0 ), 00369 'CHIINV' => array( 275, 2, 1, 0 ), 00370 'COMBIN' => array( 276, 2, 1, 0 ), 00371 'CONFIDENCE' => array( 277, 3, 1, 0 ), 00372 'CRITBINOM' => array( 278, 3, 1, 0 ), 00373 'EVEN' => array( 279, 1, 1, 0 ), 00374 'EXPONDIST' => array( 280, 3, 1, 0 ), 00375 'FDIST' => array( 281, 3, 1, 0 ), 00376 'FINV' => array( 282, 3, 1, 0 ), 00377 'FISHER' => array( 283, 1, 1, 0 ), 00378 'FISHERINV' => array( 284, 1, 1, 0 ), 00379 'FLOOR' => array( 285, 2, 1, 0 ), 00380 'GAMMADIST' => array( 286, 4, 1, 0 ), 00381 'GAMMAINV' => array( 287, 3, 1, 0 ), 00382 'CEILING' => array( 288, 2, 1, 0 ), 00383 'HYPGEOMDIST' => array( 289, 4, 1, 0 ), 00384 'LOGNORMDIST' => array( 290, 3, 1, 0 ), 00385 'LOGINV' => array( 291, 3, 1, 0 ), 00386 'NEGBINOMDIST' => array( 292, 3, 1, 0 ), 00387 'NORMDIST' => array( 293, 4, 1, 0 ), 00388 'NORMSDIST' => array( 294, 1, 1, 0 ), 00389 'NORMINV' => array( 295, 3, 1, 0 ), 00390 'NORMSINV' => array( 296, 1, 1, 0 ), 00391 'STANDARDIZE' => array( 297, 3, 1, 0 ), 00392 'ODD' => array( 298, 1, 1, 0 ), 00393 'PERMUT' => array( 299, 2, 1, 0 ), 00394 'POISSON' => array( 300, 3, 1, 0 ), 00395 'TDIST' => array( 301, 3, 1, 0 ), 00396 'WEIBULL' => array( 302, 4, 1, 0 ), 00397 'SUMXMY2' => array( 303, 2, 2, 0 ), 00398 'SUMX2MY2' => array( 304, 2, 2, 0 ), 00399 'SUMX2PY2' => array( 305, 2, 2, 0 ), 00400 'CHITEST' => array( 306, 2, 2, 0 ), 00401 'CORREL' => array( 307, 2, 2, 0 ), 00402 'COVAR' => array( 308, 2, 2, 0 ), 00403 'FORECAST' => array( 309, 3, 2, 0 ), 00404 'FTEST' => array( 310, 2, 2, 0 ), 00405 'INTERCEPT' => array( 311, 2, 2, 0 ), 00406 'PEARSON' => array( 312, 2, 2, 0 ), 00407 'RSQ' => array( 313, 2, 2, 0 ), 00408 'STEYX' => array( 314, 2, 2, 0 ), 00409 'SLOPE' => array( 315, 2, 2, 0 ), 00410 'TTEST' => array( 316, 4, 2, 0 ), 00411 'PROB' => array( 317, -1, 2, 0 ), 00412 'DEVSQ' => array( 318, -1, 0, 0 ), 00413 'GEOMEAN' => array( 319, -1, 0, 0 ), 00414 'HARMEAN' => array( 320, -1, 0, 0 ), 00415 'SUMSQ' => array( 321, -1, 0, 0 ), 00416 'KURT' => array( 322, -1, 0, 0 ), 00417 'SKEW' => array( 323, -1, 0, 0 ), 00418 'ZTEST' => array( 324, -1, 0, 0 ), 00419 'LARGE' => array( 325, 2, 0, 0 ), 00420 'SMALL' => array( 326, 2, 0, 0 ), 00421 'QUARTILE' => array( 327, 2, 0, 0 ), 00422 'PERCENTILE' => array( 328, 2, 0, 0 ), 00423 'PERCENTRANK' => array( 329, -1, 0, 0 ), 00424 'MODE' => array( 330, -1, 2, 0 ), 00425 'TRIMMEAN' => array( 331, 2, 0, 0 ), 00426 'TINV' => array( 332, 2, 1, 0 ), 00427 'CONCATENATE' => array( 336, -1, 1, 0 ), 00428 'POWER' => array( 337, 2, 1, 0 ), 00429 'RADIANS' => array( 342, 1, 1, 0 ), 00430 'DEGREES' => array( 343, 1, 1, 0 ), 00431 'SUBTOTAL' => array( 344, -1, 0, 0 ), 00432 'SUMIF' => array( 345, -1, 0, 0 ), 00433 'COUNTIF' => array( 346, 2, 0, 0 ), 00434 'COUNTBLANK' => array( 347, 1, 0, 0 ), 00435 'ROMAN' => array( 354, -1, 1, 0 ) 00436 ); 00437 } 00438 00444 function _convert($token) 00445 { 00446 if(is_numeric($token)) 00447 { 00448 return($this->_convert_number($token)); 00449 } 00450 // match references like A1 00451 elseif(preg_match("/^([A-I]?[A-Z])(\d+)$/",$token)) 00452 { 00453 return($this->_convert_ref2d($token)); 00454 } 00455 // match ranges like A1:B2 00456 elseif(preg_match("/^([A-I]?[A-Z])(\d+)\:([A-I]?[A-Z])(\d+)$/",$token)) 00457 { 00458 return($this->_convert_range2d($token)); 00459 } 00460 // match ranges like A1..B2 00461 elseif(preg_match("/^([A-I]?[A-Z])(\d+)\.\.([A-I]?[A-Z])(\d+)$/",$token)) 00462 { 00463 return($this->_convert_range2d($token)); 00464 } 00465 elseif(isset($this->ptg[$token])) // operators (including parentheses) 00466 { 00467 return(pack("C", $this->ptg[$token])); 00468 } 00469 elseif(preg_match("/[A-Z0-9�-�\.]+/",$token)) 00470 { 00471 return($this->_convert_function($token,$this->_func_args)); 00472 } 00473 // if it's an argument, ignore the token (the argument remains) 00474 elseif($token == 'arg') 00475 { 00476 $this->_func_args++; 00477 return(''); 00478 } 00479 die("Unknown token $token"); 00480 } 00481 00487 function _convert_number($num) 00488 { 00489 // Integer in the range 0..2**16-1 00490 if ((preg_match("/^\d+$/",$num)) and ($num <= 65535)) { 00491 return pack("Cv", $this->ptg['ptgInt'], $num); 00492 } 00493 else // A float 00494 { 00495 if($this->_byte_order) // if it's Big Endian 00496 { 00497 $num = strrev($num); 00498 } 00499 return pack("Cd", $this->ptg['ptgNum'], $num); 00500 } 00501 } 00502 00510 function _convert_function($token, $num_args) 00511 { 00512 $this->_func_args = 0; // re initialize the number of arguments 00513 $args = $this->_functions[$token][1]; 00514 $volatile = $this->_functions[$token][3]; 00515 00516 if($volatile) { 00517 $this->_volatile = 1; 00518 } 00519 // Fixed number of args eg. TIME($i,$j,$k). 00520 if ($args >= 0) 00521 { 00522 return(pack("Cv", $this->ptg['ptgFuncV'], $this->_functions[$token][0])); 00523 } 00524 // Variable number of args eg. SUM($i,$j,$k, ..). 00525 if ($args == -1) { 00526 return(pack("CCv", $this->ptg['ptgFuncVarV'], $num_args, $this->_functions[$token][0])); 00527 } 00528 } 00529 00535 function _convert_range2d($range) 00536 { 00537 $class = 2; // as far as I know, this is magick. 00538 00539 // Split the range into 2 cell refs 00540 if(preg_match("/^([A-I]?[A-Z])(\d+)\:([A-I]?[A-Z])(\d+)$/",$range)) { 00541 list($cell1, $cell2) = explode(':', $range); 00542 } 00543 elseif(preg_match("/^([A-I]?[A-Z])(\d+)\.\.([A-I]?[A-Z])(\d+)$/",$range)) { 00544 list($cell1, $cell2) = explode('..', $range); 00545 } 00546 else { 00547 die("Unknown range separator"); 00548 } 00549 00550 // Convert the cell references 00551 list($row1, $col1) = $this->_cell_to_packed_rowcol($cell1); 00552 list($row2, $col2) = $this->_cell_to_packed_rowcol($cell2); 00553 00554 // The ptg value depends on the class of the ptg. 00555 if ($class == 0) { 00556 $ptgArea = pack("C", $this->ptg['ptgArea']); 00557 } 00558 elseif ($class == 1) { 00559 $ptgArea = pack("C", $this->ptg['ptgAreaV']); 00560 } 00561 elseif ($class == 2) { 00562 $ptgArea = pack("C", $this->ptg['ptgAreaA']); 00563 } 00564 else{ 00565 die("Unknown class "); 00566 } 00567 00568 return($ptgArea . $row1 . $row2 . $col1. $col2); 00569 } 00570 00576 function _convert_ref2d($cell) 00577 { 00578 $class = 2; // as far as I know, this is magick. 00579 00580 // Convert the cell reference 00581 list($row, $col) = $this->_cell_to_packed_rowcol($cell); 00582 00583 // The ptg value depends on the class of the ptg. 00584 if ($class == 0) { 00585 $ptgRef = pack("C", $this->ptg['ptgRef']); 00586 } 00587 elseif ($class == 1) { 00588 $ptgRef = pack("C", $this->ptg['ptgRefV']); 00589 } 00590 elseif ($class == 2) { 00591 $ptgRef = pack("C", $this->ptg['ptgRefA']); 00592 } 00593 else{ 00594 die("Unknown class "); 00595 } 00596 return $ptgRef.$row.$col; 00597 } 00598 00604 function _cell_to_packed_rowcol($cell) 00605 { 00606 list($row, $col, $row_rel, $col_rel) = $this->_cell_to_rowcol($cell); 00607 if ($col >= 256) { 00608 die("Column in: $cell greater than 255 "); 00609 } 00610 if ($row >= 16384) { 00611 die("Row in: $cell greater than 16384 "); 00612 } 00613 00614 // Set the high bits to indicate if row or col are relative. 00615 $row |= $col_rel << 14; 00616 $row |= $row_rel << 15; 00617 00618 $row = pack('v', $row); 00619 $col = pack('C', $col); 00620 00621 return (array($row, $col)); 00622 } 00623 00631 function _cell_to_rowcol($cell) 00632 { 00633 preg_match('/(\$)?([A-I]?[A-Z])(\$)?(\d+)/',$cell,$match); 00634 // return absolute column if there is a $ in the ref 00635 $col_rel = empty($match[1]) ? 1 : 0; 00636 $col_ref = $match[2]; 00637 $row_rel = empty($match[3]) ? 1 : 0; 00638 $row = $match[4]; 00639 00640 // Convert base26 column string to a number. 00641 $expn = strlen($col_ref) - 1; 00642 $col = 0; 00643 for($i=0; $i < strlen($col_ref); $i++) 00644 { 00645 $col += (ord($col_ref{$i}) - ord('A') + 1) * pow(26, $expn); 00646 $expn--; 00647 } 00648 00649 // Convert 1-index to zero-index 00650 $row--; 00651 $col--; 00652 00653 return(array($row, $col, $row_rel, $col_rel)); 00654 } 00655 00659 function _advance() 00660 { 00661 $i = $this->_current_char; 00662 // eat up white spaces 00663 if($i < strlen($this->_formula)) 00664 { 00665 while($this->_formula{$i} == " ") 00666 { 00667 $i++; 00668 } 00669 if($i < strlen($this->_formula) - 1) 00670 { 00671 $this->_lookahead = $this->_formula{$i+1}; 00672 } 00673 $token = ""; 00674 } 00675 while($i < strlen($this->_formula)) 00676 { 00677 $token .= $this->_formula{$i}; 00678 if($this->_match($token) != '') 00679 { 00680 if($i < strlen($this->_formula) - 1) 00681 { 00682 $this->_lookahead = $this->_formula{$i+1}; 00683 } 00684 $this->_current_char = $i + 1; 00685 $this->_current_token = $token; 00686 return(1); 00687 } 00688 $this->_lookahead = $this->_formula{$i+2}; 00689 $i++; 00690 } 00691 //die("Lexical error ".$this->_current_char); 00692 } 00693 00699 function _match($token) 00700 { 00701 switch($token) 00702 { 00703 case ADD: 00704 return($token); 00705 break; 00706 case SUB: 00707 return($token); 00708 break; 00709 case MUL: 00710 return($token); 00711 break; 00712 case DIV: 00713 return($token); 00714 break; 00715 case OPEN: 00716 return($token); 00717 break; 00718 case CLOSE: 00719 return($token); 00720 break; 00721 case COMA: 00722 return($token); 00723 break; 00724 default: 00725 // if it's a reference 00726 if(preg_match("/^[A-I]?[A-Z][0-9]+$/i",$token) and 00727 !preg_match("/[0-9]/",$this->_lookahead) and 00728 ($this->_lookahead != ':') and ($this->_lookahead != '.')) 00729 { 00730 return($token); 00731 } 00732 // if it's a range (A1:A2) 00733 elseif(preg_match("/^[A-I]?[A-Z][0-9]+:[A-I]?[A-Z][0-9]+$/i",$token) and 00734 !preg_match("/[0-9]/",$this->_lookahead)) 00735 { 00736 return($token); 00737 } 00738 // if it's a range (A1..A2) 00739 elseif(preg_match("/^[A-I]?[A-Z][0-9]+\.\.[A-I]?[A-Z][0-9]+$/i",$token) and 00740 !preg_match("/[0-9]/",$this->_lookahead)) 00741 { 00742 return($token); 00743 } 00744 elseif(is_numeric($token) and !is_numeric($token.$this->_lookahead)) 00745 { 00746 return($token); 00747 } 00748 // if it's a function call 00749 elseif(preg_match("/^[A-Z0-9�-�\.]+$/i",$token) and ($this->_lookahead == "(")) 00750 00751 { 00752 return($token); 00753 } 00754 return ''; 00755 } 00756 } 00757 00764 function parse($formula) 00765 { 00766 $this->_current_char = 0; 00767 $this->_formula = $formula; 00768 $this->_lookahead = $formula{1}; 00769 $this->_advance(); 00770 $this->_parse_tree = $this->_expression(); 00771 } 00772 00779 function _expression() 00780 { 00781 $result = $this->_term(); 00782 while ($this->_current_token == ADD or $this->_current_token == SUB) 00783 { 00784 if ($this->_current_token == ADD) 00785 { 00786 $this->_advance(); 00787 $result = $this->_create_tree('ptgAdd', $result, $this->_term()); 00788 } 00789 else 00790 { 00791 $this->_advance(); 00792 $result = $this->_create_tree('ptgSub', $result, $this->_term()); 00793 } 00794 } 00795 return $result; 00796 } 00797 00805 function _parenthesized_expression() 00806 { 00807 $result = $this->_create_tree('ptgParen', $this->_expression(), ''); 00808 return($result); 00809 } 00810 00817 function _term() 00818 { 00819 $result = $this->_fact(); 00820 while ($this->_current_token == MUL || $this->_current_token == DIV) 00821 { 00822 if ($this->_current_token == MUL) 00823 { 00824 $this->_advance(); 00825 $result = $this->_create_tree('ptgMul', $result, $this->_fact()); 00826 } 00827 else 00828 { 00829 $this->_advance(); 00830 $result = $this->_create_tree('ptgDiv', $result, $this->_fact()); 00831 } 00832 } 00833 return($result); 00834 } 00835 00846 function _fact() 00847 { 00848 if ($this->_current_token == OPEN) 00849 { 00850 $this->_advance(); // eat the "(" 00851 $result = $this->_parenthesized_expression();//$this->_expression(); 00852 00853 if ($this->_current_token != CLOSE) { 00854 die("')' token expected."); 00855 } 00856 $this->_advance(); // eat the ")" 00857 return($result); 00858 } 00859 // if it's a reference 00860 if (preg_match("/^[A-I]?[A-Z][0-9]+$/i",$this->_current_token)) 00861 { 00862 $result = $this->_create_tree($this->_current_token, '', ''); 00863 $this->_advance(); 00864 return($result); 00865 } 00866 // if it's a range 00867 elseif (preg_match("/^[A-I]?[A-Z][0-9]+:[A-I]?[A-Z][0-9]+$/i",$this->_current_token) or 00868 preg_match("/^[A-I]?[A-Z][0-9]+\.\.[A-I]?[A-Z][0-9]+$/i",$this->_current_token)) 00869 { 00870 $result = $this->_current_token; 00871 $this->_advance(); 00872 return($result); 00873 } 00874 elseif (is_numeric($this->_current_token)) 00875 { 00876 $result = $this->_create_tree($this->_current_token, '', ''); 00877 $this->_advance(); 00878 return($result); 00879 } 00880 // if it's a function call 00881 elseif (preg_match("/^[A-Z0-9�-�\.]+$/i",$this->_current_token)) 00882 { 00883 $result = $this->_func(); 00884 return($result); 00885 } 00886 die("Sintactic error: ".$this->_current_token.", lookahead: ". 00887 $this->_lookahead.", current char: ".$this->_current_char); 00888 } 00889 00895 function _func() 00896 { 00897 $num_args = 0; // number of arguments received 00898 $function = $this->_current_token; 00899 $this->_advance(); 00900 $this->_advance(); // eat the "(" 00901 while($this->_current_token != ')') 00902 { 00903 if($num_args > 0) 00904 { 00905 if($this->_current_token == COMA) { 00906 $this->_advance(); // eat the "," 00907 } 00908 else { 00909 die("Sintactic error: coma expected $num_args"); 00910 } 00911 $result = $this->_create_tree('arg', $result, $this->_expression()); 00912 } 00913 else { 00914 $result = $this->_create_tree('arg', '', $this->_expression()); 00915 } 00916 $num_args++; 00917 } 00918 $args = $this->_functions[$function][1]; 00919 // If fixed number of args eg. TIME($i,$j,$k). Check that the number of args is valid. 00920 if (($args >= 0) and ($args != $num_args)) 00921 { 00922 die("Incorrect number of arguments in function $function() "); 00923 } 00924 00925 $result = $this->_create_tree($function, $result, ''); 00926 $this->_advance(); // eat the ")" 00927 return($result); 00928 } 00929 00938 function _create_tree($value, $left, $right) 00939 { 00940 return array('value' => $value, 'left' => $left, 'right' => $right); 00941 } 00942 00969 function to_reverse_polish($tree = array()) 00970 { 00971 $polish = ""; // the string we are going to return 00972 if (empty($tree)) // If it's the first call use _parse_tree 00973 { 00974 $tree = $this->_parse_tree; 00975 } 00976 if (is_array($tree['left'])) 00977 { 00978 $polish .= $this->to_reverse_polish($tree['left']); 00979 } 00980 elseif($tree['left'] != '') // It's a final node 00981 { 00982 $polish .= $this->_convert($tree['left']); //$tree['left']; 00983 } 00984 if (is_array($tree['right'])) 00985 { 00986 $polish .= $this->to_reverse_polish($tree['right']); 00987 } 00988 elseif($tree['right'] != '') // It's a final node 00989 { 00990 $polish .= $this->_convert($tree['right']); 00991 } 00992 $polish .= $this->_convert($tree['value']); 00993 return $polish; 00994 } 00995 } 00996 ?>
1.7.6.1 
