| [ Index ] |
PHP Cross Reference of phpwcms V1.4.3 _r380 (23.11.09) |
[Summary view] [Print] [Text view]
1 <?php 2 /** 3 * Class for parsing Excel formulas 4 * 5 * License Information: 6 * 7 * Spreadsheet_Excel_Writer: A library for generating Excel Spreadsheets 8 * Copyright (c) 2002-2003 Xavier Noguer xnoguer@rezebra.com 9 * 10 * This library is free software; you can redistribute it and/or 11 * modify it under the terms of the GNU Lesser General Public 12 * License as published by the Free Software Foundation; either 13 * version 2.1 of the License, or (at your option) any later version. 14 * 15 * This library is distributed in the hope that it will be useful, 16 * but WITHOUT ANY WARRANTY; without even the implied warranty of 17 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU 18 * Lesser General Public License for more details. 19 * 20 * You should have received a copy of the GNU Lesser General Public 21 * License along with this library; if not, write to the Free Software 22 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA 23 */ 24 25 /** 26 * @const SPREADSHEET_EXCEL_WRITER_ADD token identifier for character "+" 27 */ 28 define('SPREADSHEET_EXCEL_WRITER_ADD', "+"); 29 30 /** 31 * @const SPREADSHEET_EXCEL_WRITER_SUB token identifier for character "-" 32 */ 33 define('SPREADSHEET_EXCEL_WRITER_SUB', "-"); 34 35 /** 36 * @const SPREADSHEET_EXCEL_WRITER_MUL token identifier for character "*" 37 */ 38 define('SPREADSHEET_EXCEL_WRITER_MUL', "*"); 39 40 /** 41 * @const SPREADSHEET_EXCEL_WRITER_DIV token identifier for character "/" 42 */ 43 define('SPREADSHEET_EXCEL_WRITER_DIV', "/"); 44 45 /** 46 * @const SPREADSHEET_EXCEL_WRITER_OPEN token identifier for character "(" 47 */ 48 define('SPREADSHEET_EXCEL_WRITER_OPEN', "("); 49 50 /** 51 * @const SPREADSHEET_EXCEL_WRITER_CLOSE token identifier for character ")" 52 */ 53 define('SPREADSHEET_EXCEL_WRITER_CLOSE', ")"); 54 55 /** 56 * @const SPREADSHEET_EXCEL_WRITER_COMA token identifier for character "," 57 */ 58 define('SPREADSHEET_EXCEL_WRITER_COMA', ","); 59 60 /** 61 * @const SPREADSHEET_EXCEL_WRITER_SEMICOLON token identifier for character ";" 62 */ 63 define('SPREADSHEET_EXCEL_WRITER_SEMICOLON', ";"); 64 65 /** 66 * @const SPREADSHEET_EXCEL_WRITER_GT token identifier for character ">" 67 */ 68 define('SPREADSHEET_EXCEL_WRITER_GT', ">"); 69 70 /** 71 * @const SPREADSHEET_EXCEL_WRITER_LT token identifier for character "<" 72 */ 73 define('SPREADSHEET_EXCEL_WRITER_LT', "<"); 74 75 /** 76 * @const SPREADSHEET_EXCEL_WRITER_LE token identifier for character "<=" 77 */ 78 define('SPREADSHEET_EXCEL_WRITER_LE', "<="); 79 80 /** 81 * @const SPREADSHEET_EXCEL_WRITER_GE token identifier for character ">=" 82 */ 83 define('SPREADSHEET_EXCEL_WRITER_GE', ">="); 84 85 /** 86 * @const SPREADSHEET_EXCEL_WRITER_EQ token identifier for character "=" 87 */ 88 define('SPREADSHEET_EXCEL_WRITER_EQ', "="); 89 90 /** 91 * @const SPREADSHEET_EXCEL_WRITER_NE token identifier for character "<>" 92 */ 93 define('SPREADSHEET_EXCEL_WRITER_NE', "<>"); 94 95 96 require_once 'PEAR.php'; 97 98 /** 99 * Class for parsing Excel formulas 100 * 101 * @author Xavier Noguer <xnoguer@rezebra.com> 102 * @category FileFormats 103 * @package Spreadsheet_Excel_Writer 104 */ 105 106 class Spreadsheet_Excel_Writer_Parser extends PEAR 107 { 108 /** 109 * The index of the character we are currently looking at 110 * @var integer 111 */ 112 var $_current_char; 113 114 /** 115 * The token we are working on. 116 * @var string 117 */ 118 var $_current_token; 119 120 /** 121 * The formula to parse 122 * @var string 123 */ 124 var $_formula; 125 126 /** 127 * The character ahead of the current char 128 * @var string 129 */ 130 var $_lookahead; 131 132 /** 133 * The parse tree to be generated 134 * @var string 135 */ 136 var $_parse_tree; 137 138 /** 139 * The byte order. 1 => big endian, 0 => little endian. 140 * @var integer 141 */ 142 var $_byte_order; 143 144 /** 145 * Array of external sheets 146 * @var array 147 */ 148 var $_ext_sheets; 149 150 /** 151 * Array of sheet references in the form of REF structures 152 * @var array 153 */ 154 var $_references; 155 156 /** 157 * The BIFF version for the workbook 158 * @var integer 159 */ 160 var $_BIFF_version; 161 162 /** 163 * The class constructor 164 * 165 * @param integer $byte_order The byte order (Little endian or Big endian) of the architecture 166 (optional). 1 => big endian, 0 (default) little endian. 167 */ 168 function Spreadsheet_Excel_Writer_Parser($byte_order, $biff_version) 169 { 170 $this->_current_char = 0; 171 $this->_BIFF_version = $biff_version; 172 $this->_current_token = ''; // The token we are working on. 173 $this->_formula = ''; // The formula to parse. 174 $this->_lookahead = ''; // The character ahead of the current char. 175 $this->_parse_tree = ''; // The parse tree to be generated. 176 $this->_initializeHashes(); // Initialize the hashes: ptg's and function's ptg's 177 $this->_byte_order = $byte_order; // Little Endian or Big Endian 178 $this->_ext_sheets = array(); 179 $this->_references = array(); 180 } 181 182 /** 183 * Initialize the ptg and function hashes. 184 * 185 * @access private 186 */ 187 function _initializeHashes() 188 { 189 // The Excel ptg indices 190 $this->ptg = array( 191 'ptgExp' => 0x01, 192 'ptgTbl' => 0x02, 193 'ptgAdd' => 0x03, 194 'ptgSub' => 0x04, 195 'ptgMul' => 0x05, 196 'ptgDiv' => 0x06, 197 'ptgPower' => 0x07, 198 'ptgConcat' => 0x08, 199 'ptgLT' => 0x09, 200 'ptgLE' => 0x0A, 201 'ptgEQ' => 0x0B, 202 'ptgGE' => 0x0C, 203 'ptgGT' => 0x0D, 204 'ptgNE' => 0x0E, 205 'ptgIsect' => 0x0F, 206 'ptgUnion' => 0x10, 207 'ptgRange' => 0x11, 208 'ptgUplus' => 0x12, 209 'ptgUminus' => 0x13, 210 'ptgPercent' => 0x14, 211 'ptgParen' => 0x15, 212 'ptgMissArg' => 0x16, 213 'ptgStr' => 0x17, 214 'ptgAttr' => 0x19, 215 'ptgSheet' => 0x1A, 216 'ptgEndSheet' => 0x1B, 217 'ptgErr' => 0x1C, 218 'ptgBool' => 0x1D, 219 'ptgInt' => 0x1E, 220 'ptgNum' => 0x1F, 221 'ptgArray' => 0x20, 222 'ptgFunc' => 0x21, 223 'ptgFuncVar' => 0x22, 224 'ptgName' => 0x23, 225 'ptgRef' => 0x24, 226 'ptgArea' => 0x25, 227 'ptgMemArea' => 0x26, 228 'ptgMemErr' => 0x27, 229 'ptgMemNoMem' => 0x28, 230 'ptgMemFunc' => 0x29, 231 'ptgRefErr' => 0x2A, 232 'ptgAreaErr' => 0x2B, 233 'ptgRefN' => 0x2C, 234 'ptgAreaN' => 0x2D, 235 'ptgMemAreaN' => 0x2E, 236 'ptgMemNoMemN' => 0x2F, 237 'ptgNameX' => 0x39, 238 'ptgRef3d' => 0x3A, 239 'ptgArea3d' => 0x3B, 240 'ptgRefErr3d' => 0x3C, 241 'ptgAreaErr3d' => 0x3D, 242 'ptgArrayV' => 0x40, 243 'ptgFuncV' => 0x41, 244 'ptgFuncVarV' => 0x42, 245 'ptgNameV' => 0x43, 246 'ptgRefV' => 0x44, 247 'ptgAreaV' => 0x45, 248 'ptgMemAreaV' => 0x46, 249 'ptgMemErrV' => 0x47, 250 'ptgMemNoMemV' => 0x48, 251 'ptgMemFuncV' => 0x49, 252 'ptgRefErrV' => 0x4A, 253 'ptgAreaErrV' => 0x4B, 254 'ptgRefNV' => 0x4C, 255 'ptgAreaNV' => 0x4D, 256 'ptgMemAreaNV' => 0x4E, 257 'ptgMemNoMemN' => 0x4F, 258 'ptgFuncCEV' => 0x58, 259 'ptgNameXV' => 0x59, 260 'ptgRef3dV' => 0x5A, 261 'ptgArea3dV' => 0x5B, 262 'ptgRefErr3dV' => 0x5C, 263 'ptgAreaErr3d' => 0x5D, 264 'ptgArrayA' => 0x60, 265 'ptgFuncA' => 0x61, 266 'ptgFuncVarA' => 0x62, 267 'ptgNameA' => 0x63, 268 'ptgRefA' => 0x64, 269 'ptgAreaA' => 0x65, 270 'ptgMemAreaA' => 0x66, 271 'ptgMemErrA' => 0x67, 272 'ptgMemNoMemA' => 0x68, 273 'ptgMemFuncA' => 0x69, 274 'ptgRefErrA' => 0x6A, 275 'ptgAreaErrA' => 0x6B, 276 'ptgRefNA' => 0x6C, 277 'ptgAreaNA' => 0x6D, 278 'ptgMemAreaNA' => 0x6E, 279 'ptgMemNoMemN' => 0x6F, 280 'ptgFuncCEA' => 0x78, 281 'ptgNameXA' => 0x79, 282 'ptgRef3dA' => 0x7A, 283 'ptgArea3dA' => 0x7B, 284 'ptgRefErr3dA' => 0x7C, 285 'ptgAreaErr3d' => 0x7D 286 ); 287 288 // Thanks to Michael Meeks and Gnumeric for the initial arg values. 289 // 290 // The following hash was generated by "function_locale.pl" in the distro. 291 // Refer to function_locale.pl for non-English function names. 292 // 293 // The array elements are as follow: 294 // ptg: The Excel function ptg code. 295 // args: The number of arguments that the function takes: 296 // >=0 is a fixed number of arguments. 297 // -1 is a variable number of arguments. 298 // class: The reference, value or array class of the function args. 299 // vol: The function is volatile. 300 // 301 $this->_functions = array( 302 // function ptg args class vol 303 'COUNT' => array( 0, -1, 0, 0 ), 304 'IF' => array( 1, -1, 1, 0 ), 305 'ISNA' => array( 2, 1, 1, 0 ), 306 'ISERROR' => array( 3, 1, 1, 0 ), 307 'SUM' => array( 4, -1, 0, 0 ), 308 'AVERAGE' => array( 5, -1, 0, 0 ), 309 'MIN' => array( 6, -1, 0, 0 ), 310 'MAX' => array( 7, -1, 0, 0 ), 311 'ROW' => array( 8, -1, 0, 0 ), 312 'COLUMN' => array( 9, -1, 0, 0 ), 313 'NA' => array( 10, 0, 0, 0 ), 314 'NPV' => array( 11, -1, 1, 0 ), 315 'STDEV' => array( 12, -1, 0, 0 ), 316 'DOLLAR' => array( 13, -1, 1, 0 ), 317 'FIXED' => array( 14, -1, 1, 0 ), 318 'SIN' => array( 15, 1, 1, 0 ), 319 'COS' => array( 16, 1, 1, 0 ), 320 'TAN' => array( 17, 1, 1, 0 ), 321 'ATAN' => array( 18, 1, 1, 0 ), 322 'PI' => array( 19, 0, 1, 0 ), 323 'SQRT' => array( 20, 1, 1, 0 ), 324 'EXP' => array( 21, 1, 1, 0 ), 325 'LN' => array( 22, 1, 1, 0 ), 326 'LOG10' => array( 23, 1, 1, 0 ), 327 'ABS' => array( 24, 1, 1, 0 ), 328 'INT' => array( 25, 1, 1, 0 ), 329 'SIGN' => array( 26, 1, 1, 0 ), 330 'ROUND' => array( 27, 2, 1, 0 ), 331 'LOOKUP' => array( 28, -1, 0, 0 ), 332 'INDEX' => array( 29, -1, 0, 1 ), 333 'REPT' => array( 30, 2, 1, 0 ), 334 'MID' => array( 31, 3, 1, 0 ), 335 'LEN' => array( 32, 1, 1, 0 ), 336 'VALUE' => array( 33, 1, 1, 0 ), 337 'TRUE' => array( 34, 0, 1, 0 ), 338 'FALSE' => array( 35, 0, 1, 0 ), 339 'AND' => array( 36, -1, 0, 0 ), 340 'OR' => array( 37, -1, 0, 0 ), 341 'NOT' => array( 38, 1, 1, 0 ), 342 'MOD' => array( 39, 2, 1, 0 ), 343 'DCOUNT' => array( 40, 3, 0, 0 ), 344 'DSUM' => array( 41, 3, 0, 0 ), 345 'DAVERAGE' => array( 42, 3, 0, 0 ), 346 'DMIN' => array( 43, 3, 0, 0 ), 347 'DMAX' => array( 44, 3, 0, 0 ), 348 'DSTDEV' => array( 45, 3, 0, 0 ), 349 'VAR' => array( 46, -1, 0, 0 ), 350 'DVAR' => array( 47, 3, 0, 0 ), 351 'TEXT' => array( 48, 2, 1, 0 ), 352 'LINEST' => array( 49, -1, 0, 0 ), 353 'TREND' => array( 50, -1, 0, 0 ), 354 'LOGEST' => array( 51, -1, 0, 0 ), 355 'GROWTH' => array( 52, -1, 0, 0 ), 356 'PV' => array( 56, -1, 1, 0 ), 357 'FV' => array( 57, -1, 1, 0 ), 358 'NPER' => array( 58, -1, 1, 0 ), 359 'PMT' => array( 59, -1, 1, 0 ), 360 'RATE' => array( 60, -1, 1, 0 ), 361 'MIRR' => array( 61, 3, 0, 0 ), 362 'IRR' => array( 62, -1, 0, 0 ), 363 'RAND' => array( 63, 0, 1, 1 ), 364 'MATCH' => array( 64, -1, 0, 0 ), 365 'DATE' => array( 65, 3, 1, 0 ), 366 'TIME' => array( 66, 3, 1, 0 ), 367 'DAY' => array( 67, 1, 1, 0 ), 368 'MONTH' => array( 68, 1, 1, 0 ), 369 'YEAR' => array( 69, 1, 1, 0 ), 370 'WEEKDAY' => array( 70, -1, 1, 0 ), 371 'HOUR' => array( 71, 1, 1, 0 ), 372 'MINUTE' => array( 72, 1, 1, 0 ), 373 'SECOND' => array( 73, 1, 1, 0 ), 374 'NOW' => array( 74, 0, 1, 1 ), 375 'AREAS' => array( 75, 1, 0, 1 ), 376 'ROWS' => array( 76, 1, 0, 1 ), 377 'COLUMNS' => array( 77, 1, 0, 1 ), 378 'OFFSET' => array( 78, -1, 0, 1 ), 379 'SEARCH' => array( 82, -1, 1, 0 ), 380 'TRANSPOSE' => array( 83, 1, 1, 0 ), 381 'TYPE' => array( 86, 1, 1, 0 ), 382 'ATAN2' => array( 97, 2, 1, 0 ), 383 'ASIN' => array( 98, 1, 1, 0 ), 384 'ACOS' => array( 99, 1, 1, 0 ), 385 'CHOOSE' => array( 100, -1, 1, 0 ), 386 'HLOOKUP' => array( 101, -1, 0, 0 ), 387 'VLOOKUP' => array( 102, -1, 0, 0 ), 388 'ISREF' => array( 105, 1, 0, 0 ), 389 'LOG' => array( 109, -1, 1, 0 ), 390 'CHAR' => array( 111, 1, 1, 0 ), 391 'LOWER' => array( 112, 1, 1, 0 ), 392 'UPPER' => array( 113, 1, 1, 0 ), 393 'PROPER' => array( 114, 1, 1, 0 ), 394 'LEFT' => array( 115, -1, 1, 0 ), 395 'RIGHT' => array( 116, -1, 1, 0 ), 396 'EXACT' => array( 117, 2, 1, 0 ), 397 'TRIM' => array( 118, 1, 1, 0 ), 398 'REPLACE' => array( 119, 4, 1, 0 ), 399 'SUBSTITUTE' => array( 120, -1, 1, 0 ), 400 'CODE' => array( 121, 1, 1, 0 ), 401 'FIND' => array( 124, -1, 1, 0 ), 402 'CELL' => array( 125, -1, 0, 1 ), 403 'ISERR' => array( 126, 1, 1, 0 ), 404 'ISTEXT' => array( 127, 1, 1, 0 ), 405 'ISNUMBER' => array( 128, 1, 1, 0 ), 406 'ISBLANK' => array( 129, 1, 1, 0 ), 407 'T' => array( 130, 1, 0, 0 ), 408 'N' => array( 131, 1, 0, 0 ), 409 'DATEVALUE' => array( 140, 1, 1, 0 ), 410 'TIMEVALUE' => array( 141, 1, 1, 0 ), 411 'SLN' => array( 142, 3, 1, 0 ), 412 'SYD' => array( 143, 4, 1, 0 ), 413 'DDB' => array( 144, -1, 1, 0 ), 414 'INDIRECT' => array( 148, -1, 1, 1 ), 415 'CALL' => array( 150, -1, 1, 0 ), 416 'CLEAN' => array( 162, 1, 1, 0 ), 417 'MDETERM' => array( 163, 1, 2, 0 ), 418 'MINVERSE' => array( 164, 1, 2, 0 ), 419 'MMULT' => array( 165, 2, 2, 0 ), 420 'IPMT' => array( 167, -1, 1, 0 ), 421 'PPMT' => array( 168, -1, 1, 0 ), 422 'COUNTA' => array( 169, -1, 0, 0 ), 423 'PRODUCT' => array( 183, -1, 0, 0 ), 424 'FACT' => array( 184, 1, 1, 0 ), 425 'DPRODUCT' => array( 189, 3, 0, 0 ), 426 'ISNONTEXT' => array( 190, 1, 1, 0 ), 427 'STDEVP' => array( 193, -1, 0, 0 ), 428 'VARP' => array( 194, -1, 0, 0 ), 429 'DSTDEVP' => array( 195, 3, 0, 0 ), 430 'DVARP' => array( 196, 3, 0, 0 ), 431 'TRUNC' => array( 197, -1, 1, 0 ), 432 'ISLOGICAL' => array( 198, 1, 1, 0 ), 433 'DCOUNTA' => array( 199, 3, 0, 0 ), 434 'ROUNDUP' => array( 212, 2, 1, 0 ), 435 'ROUNDDOWN' => array( 213, 2, 1, 0 ), 436 'RANK' => array( 216, -1, 0, 0 ), 437 'ADDRESS' => array( 219, -1, 1, 0 ), 438 'DAYS360' => array( 220, -1, 1, 0 ), 439 'TODAY' => array( 221, 0, 1, 1 ), 440 'VDB' => array( 222, -1, 1, 0 ), 441 'MEDIAN' => array( 227, -1, 0, 0 ), 442 'SUMPRODUCT' => array( 228, -1, 2, 0 ), 443 'SINH' => array( 229, 1, 1, 0 ), 444 'COSH' => array( 230, 1, 1, 0 ), 445 'TANH' => array( 231, 1, 1, 0 ), 446 'ASINH' => array( 232, 1, 1, 0 ), 447 'ACOSH' => array( 233, 1, 1, 0 ), 448 'ATANH' => array( 234, 1, 1, 0 ), 449 'DGET' => array( 235, 3, 0, 0 ), 450 'INFO' => array( 244, 1, 1, 1 ), 451 'DB' => array( 247, -1, 1, 0 ), 452 'FREQUENCY' => array( 252, 2, 0, 0 ), 453 'ERROR.TYPE' => array( 261, 1, 1, 0 ), 454 'REGISTER.ID' => array( 267, -1, 1, 0 ), 455 'AVEDEV' => array( 269, -1, 0, 0 ), 456 'BETADIST' => array( 270, -1, 1, 0 ), 457 'GAMMALN' => array( 271, 1, 1, 0 ), 458 'BETAINV' => array( 272, -1, 1, 0 ), 459 'BINOMDIST' => array( 273, 4, 1, 0 ), 460 'CHIDIST' => array( 274, 2, 1, 0 ), 461 'CHIINV' => array( 275, 2, 1, 0 ), 462 'COMBIN' => array( 276, 2, 1, 0 ), 463 'CONFIDENCE' => array( 277, 3, 1, 0 ), 464 'CRITBINOM' => array( 278, 3, 1, 0 ), 465 'EVEN' => array( 279, 1, 1, 0 ), 466 'EXPONDIST' => array( 280, 3, 1, 0 ), 467 'FDIST' => array( 281, 3, 1, 0 ), 468 'FINV' => array( 282, 3, 1, 0 ), 469 'FISHER' => array( 283, 1, 1, 0 ), 470 'FISHERINV' => array( 284, 1, 1, 0 ), 471 'FLOOR' => array( 285, 2, 1, 0 ), 472 'GAMMADIST' => array( 286, 4, 1, 0 ), 473 'GAMMAINV' => array( 287, 3, 1, 0 ), 474 'CEILING' => array( 288, 2, 1, 0 ), 475 'HYPGEOMDIST' => array( 289, 4, 1, 0 ), 476 'LOGNORMDIST' => array( 290, 3, 1, 0 ), 477 'LOGINV' => array( 291, 3, 1, 0 ), 478 'NEGBINOMDIST' => array( 292, 3, 1, 0 ), 479 'NORMDIST' => array( 293, 4, 1, 0 ), 480 'NORMSDIST' => array( 294, 1, 1, 0 ), 481 'NORMINV' => array( 295, 3, 1, 0 ), 482 'NORMSINV' => array( 296, 1, 1, 0 ), 483 'STANDARDIZE' => array( 297, 3, 1, 0 ), 484 'ODD' => array( 298, 1, 1, 0 ), 485 'PERMUT' => array( 299, 2, 1, 0 ), 486 'POISSON' => array( 300, 3, 1, 0 ), 487 'TDIST' => array( 301, 3, 1, 0 ), 488 'WEIBULL' => array( 302, 4, 1, 0 ), 489 'SUMXMY2' => array( 303, 2, 2, 0 ), 490 'SUMX2MY2' => array( 304, 2, 2, 0 ), 491 'SUMX2PY2' => array( 305, 2, 2, 0 ), 492 'CHITEST' => array( 306, 2, 2, 0 ), 493 'CORREL' => array( 307, 2, 2, 0 ), 494 'COVAR' => array( 308, 2, 2, 0 ), 495 'FORECAST' => array( 309, 3, 2, 0 ), 496 'FTEST' => array( 310, 2, 2, 0 ), 497 'INTERCEPT' => array( 311, 2, 2, 0 ), 498 'PEARSON' => array( 312, 2, 2, 0 ), 499 'RSQ' => array( 313, 2, 2, 0 ), 500 'STEYX' => array( 314, 2, 2, 0 ), 501 'SLOPE' => array( 315, 2, 2, 0 ), 502 'TTEST' => array( 316, 4, 2, 0 ), 503 'PROB' => array( 317, -1, 2, 0 ), 504 'DEVSQ' => array( 318, -1, 0, 0 ), 505 'GEOMEAN' => array( 319, -1, 0, 0 ), 506 'HARMEAN' => array( 320, -1, 0, 0 ), 507 'SUMSQ' => array( 321, -1, 0, 0 ), 508 'KURT' => array( 322, -1, 0, 0 ), 509 'SKEW' => array( 323, -1, 0, 0 ), 510 'ZTEST' => array( 324, -1, 0, 0 ), 511 'LARGE' => array( 325, 2, 0, 0 ), 512 'SMALL' => array( 326, 2, 0, 0 ), 513 'QUARTILE' => array( 327, 2, 0, 0 ), 514 'PERCENTILE' => array( 328, 2, 0, 0 ), 515 'PERCENTRANK' => array( 329, -1, 0, 0 ), 516 'MODE' => array( 330, -1, 2, 0 ), 517 'TRIMMEAN' => array( 331, 2, 0, 0 ), 518 'TINV' => array( 332, 2, 1, 0 ), 519 'CONCATENATE' => array( 336, -1, 1, 0 ), 520 'POWER' => array( 337, 2, 1, 0 ), 521 'RADIANS' => array( 342, 1, 1, 0 ), 522 'DEGREES' => array( 343, 1, 1, 0 ), 523 'SUBTOTAL' => array( 344, -1, 0, 0 ), 524 'SUMIF' => array( 345, -1, 0, 0 ), 525 'COUNTIF' => array( 346, 2, 0, 0 ), 526 'COUNTBLANK' => array( 347, 1, 0, 0 ), 527 'ROMAN' => array( 354, -1, 1, 0 ) 528 ); 529 } 530 531 /** 532 * Convert a token to the proper ptg value. 533 * 534 * @access private 535 * @param mixed $token The token to convert. 536 * @return mixed the converted token on success. PEAR_Error if the token 537 * is not recognized 538 */ 539 function _convert($token) 540 { 541 if (preg_match("/^\"[^\"]{0,255}\"$/", $token)) { 542 return $this->_convertString($token); 543 544 } elseif (is_numeric($token)) { 545 return $this->_convertNumber($token); 546 547 // match references like A1 or $A$1 548 } elseif (preg_match('/^\$?([A-Ia-i]?[A-Za-z])\$?(\d+)$/',$token)) { 549 return $this->_convertRef2d($token); 550 551 // match external references like Sheet1!A1 or Sheet1:Sheet2!A1 552 } elseif (preg_match("/^\w+(\:\w+)?\![A-Ia-i]?[A-Za-z](\d+)$/u",$token)) { 553 return $this->_convertRef3d($token); 554 555 // match external references like 'Sheet1'!A1 or 'Sheet1:Sheet2'!A1 556 } elseif (preg_match("/^'[\w -]+(\:[\w -]+)?'\![A-Ia-i]?[A-Za-z](\d+)$/u",$token)) { 557 return $this->_convertRef3d($token); 558 559 // match ranges like A1:B2 560 } elseif (preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?(\d+)\:(\$)?[A-Ia-i]?[A-Za-z](\$)?(\d+)$/",$token)) { 561 return $this->_convertRange2d($token); 562 563 // match ranges like A1..B2 564 } elseif (preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?(\d+)\.\.(\$)?[A-Ia-i]?[A-Za-z](\$)?(\d+)$/",$token)) { 565 return $this->_convertRange2d($token); 566 567 // match external ranges like Sheet1!A1 or Sheet1:Sheet2!A1:B2 568 } elseif (preg_match("/^\w+(\:\w+)?\!([A-Ia-i]?[A-Za-z])?(\d+)\:([A-Ia-i]?[A-Za-z])?(\d+)$/u",$token)) { 569 return $this->_convertRange3d($token); 570 571 // match external ranges like 'Sheet1'!A1 or 'Sheet1:Sheet2'!A1:B2 572 } elseif (preg_match("/^'[\w -]+(\:[\w -]+)?'\!([A-Ia-i]?[A-Za-z])?(\d+)\:([A-Ia-i]?[A-Za-z])?(\d+)$/u",$token)) { 573 return $this->_convertRange3d($token); 574 575 // operators (including parentheses) 576 } elseif (isset($this->ptg[$token])) { 577 return pack("C", $this->ptg[$token]); 578 579 // commented so argument number can be processed correctly. See toReversePolish(). 580 /*elseif (preg_match("/[A-Z0-9\xc0-\xdc\.]+/",$token)) 581 { 582 return($this->_convertFunction($token,$this->_func_args)); 583 }*/ 584 585 // if it's an argument, ignore the token (the argument remains) 586 } elseif ($token == 'arg') { 587 return ''; 588 } 589 // TODO: use real error codes 590 return $this->raiseError("Unknown token $token"); 591 } 592 593 /** 594 * Convert a number token to ptgInt or ptgNum 595 * 596 * @access private 597 * @param mixed $num an integer or double for conversion to its ptg value 598 */ 599 function _convertNumber($num) 600 { 601 // Integer in the range 0..2**16-1 602 if ((preg_match("/^\d+$/", $num)) and ($num <= 65535)) { 603 return pack("Cv", $this->ptg['ptgInt'], $num); 604 } else { // A float 605 if ($this->_byte_order) { // if it's Big Endian 606 $num = strrev($num); 607 } 608 return pack("Cd", $this->ptg['ptgNum'], $num); 609 } 610 } 611 612 /** 613 * Convert a string token to ptgStr 614 * 615 * @access private 616 * @param string $string A string for conversion to its ptg value. 617 * @return mixed the converted token on success. PEAR_Error if the string 618 * is longer than 255 characters. 619 */ 620 function _convertString($string) 621 { 622 // chop away beggining and ending quotes 623 $string = substr($string, 1, strlen($string) - 2); 624 if (strlen($string) > 255) { 625 return $this->raiseError("String is too long"); 626 } 627 628 if ($this->_BIFF_version == 0x0500) { 629 return pack("CC", $this->ptg['ptgStr'], strlen($string)).$string; 630 } elseif ($this->_BIFF_version == 0x0600) { 631 $encoding = 0; // TODO: Unicode support 632 return pack("CCC", $this->ptg['ptgStr'], strlen($string), $encoding).$string; 633 } 634 } 635 636 /** 637 * Convert a function to a ptgFunc or ptgFuncVarV depending on the number of 638 * args that it takes. 639 * 640 * @access private 641 * @param string $token The name of the function for convertion to ptg value. 642 * @param integer $num_args The number of arguments the function receives. 643 * @return string The packed ptg for the function 644 */ 645 function _convertFunction($token, $num_args) 646 { 647 $args = $this->_functions[$token][1]; 648 $volatile = $this->_functions[$token][3]; 649 650 // Fixed number of args eg. TIME($i,$j,$k). 651 if ($args >= 0) { 652 return pack("Cv", $this->ptg['ptgFuncV'], $this->_functions[$token][0]); 653 } 654 // Variable number of args eg. SUM($i,$j,$k, ..). 655 if ($args == -1) { 656 return pack("CCv", $this->ptg['ptgFuncVarV'], $num_args, $this->_functions[$token][0]); 657 } 658 } 659 660 /** 661 * Convert an Excel range such as A1:D4 to a ptgRefV. 662 * 663 * @access private 664 * @param string $range An Excel range in the A1:A2 or A1..A2 format. 665 */ 666 function _convertRange2d($range) 667 { 668 $class = 2; // as far as I know, this is magick. 669 670 // Split the range into 2 cell refs 671 if (preg_match("/^([A-Ia-i]?[A-Za-z])(\d+)\:([A-Ia-i]?[A-Za-z])(\d+)$/", $range)) { 672 list($cell1, $cell2) = split(':', $range); 673 } elseif (preg_match("/^([A-Ia-i]?[A-Za-z])(\d+)\.\.([A-Ia-i]?[A-Za-z])(\d+)$/", $range)) { 674 list($cell1, $cell2) = split('\.\.', $range); 675 676 } else { 677 // TODO: use real error codes 678 return $this->raiseError("Unknown range separator", 0, PEAR_ERROR_DIE); 679 } 680 681 // Convert the cell references 682 $cell_array1 = $this->_cellToPackedRowcol($cell1); 683 if (PEAR::isError($cell_array1)) { 684 return $cell_array1; 685 } 686 list($row1, $col1) = $cell_array1; 687 $cell_array2 = $this->_cellToPackedRowcol($cell2); 688 if (PEAR::isError($cell_array2)) { 689 return $cell_array2; 690 } 691 list($row2, $col2) = $cell_array2; 692 693 // The ptg value depends on the class of the ptg. 694 if ($class == 0) { 695 $ptgArea = pack("C", $this->ptg['ptgArea']); 696 } elseif ($class == 1) { 697 $ptgArea = pack("C", $this->ptg['ptgAreaV']); 698 } elseif ($class == 2) { 699 $ptgArea = pack("C", $this->ptg['ptgAreaA']); 700 } else { 701 // TODO: use real error codes 702 return $this->raiseError("Unknown class $class", 0, PEAR_ERROR_DIE); 703 } 704 return $ptgArea . $row1 . $row2 . $col1. $col2; 705 } 706 707 /** 708 * Convert an Excel 3d range such as "Sheet1!A1:D4" or "Sheet1:Sheet2!A1:D4" to 709 * a ptgArea3d. 710 * 711 * @access private 712 * @param string $token An Excel range in the Sheet1!A1:A2 format. 713 * @return mixed The packed ptgArea3d token on success, PEAR_Error on failure. 714 */ 715 function _convertRange3d($token) 716 { 717 $class = 2; // as far as I know, this is magick. 718 719 // Split the ref at the ! symbol 720 list($ext_ref, $range) = split('!', $token); 721 722 // Convert the external reference part (different for BIFF8) 723 if ($this->_BIFF_version == 0x0500) { 724 $ext_ref = $this->_packExtRef($ext_ref); 725 if (PEAR::isError($ext_ref)) { 726 return $ext_ref; 727 } 728 } elseif ($this->_BIFF_version == 0x0600) { 729 $ext_ref = $this->_getRefIndex($ext_ref); 730 if (PEAR::isError($ext_ref)) { 731 return $ext_ref; 732 } 733 } 734 735 // Split the range into 2 cell refs 736 list($cell1, $cell2) = split(':', $range); 737 738 // Convert the cell references 739 if (preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?(\d+)$/", $cell1)) { 740 $cell_array1 = $this->_cellToPackedRowcol($cell1); 741 if (PEAR::isError($cell_array1)) { 742 return $cell_array1; 743 } 744 list($row1, $col1) = $cell_array1; 745 $cell_array2 = $this->_cellToPackedRowcol($cell2); 746 if (PEAR::isError($cell_array2)) { 747 return $cell_array2; 748 } 749 list($row2, $col2) = $cell_array2; 750 } else { // It's a rows range (like 26:27) 751 $cells_array = $this->_rangeToPackedRange($cell1.':'.$cell2); 752 if (PEAR::isError($cells_array)) { 753 return $cells_array; 754 } 755 list($row1, $col1, $row2, $col2) = $cells_array; 756 } 757 758 // The ptg value depends on the class of the ptg. 759 if ($class == 0) { 760 $ptgArea = pack("C", $this->ptg['ptgArea3d']); 761 } elseif ($class == 1) { 762 $ptgArea = pack("C", $this->ptg['ptgArea3dV']); 763 } elseif ($class == 2) { 764 $ptgArea = pack("C", $this->ptg['ptgArea3dA']); 765 } else { 766 return $this->raiseError("Unknown class $class", 0, PEAR_ERROR_DIE); 767 } 768 769 return $ptgArea . $ext_ref . $row1 . $row2 . $col1. $col2; 770 } 771 772 /** 773 * Convert an Excel reference such as A1, $B2, C$3 or $D$4 to a ptgRefV. 774 * 775 * @access private 776 * @param string $cell An Excel cell reference 777 * @return string The cell in packed() format with the corresponding ptg 778 */ 779 function _convertRef2d($cell) 780 { 781 $class = 2; // as far as I know, this is magick. 782 783 // Convert the cell reference 784 $cell_array = $this->_cellToPackedRowcol($cell); 785 if (PEAR::isError($cell_array)) { 786 return $cell_array; 787 } 788 list($row, $col) = $cell_array; 789 790 // The ptg value depends on the class of the ptg. 791 if ($class == 0) { 792 $ptgRef = pack("C", $this->ptg['ptgRef']); 793 } elseif ($class == 1) { 794 $ptgRef = pack("C", $this->ptg['ptgRefV']); 795 } elseif ($class == 2) { 796 $ptgRef = pack("C", $this->ptg['ptgRefA']); 797 } else { 798 // TODO: use real error codes 799 return $this->raiseError("Unknown class $class"); 800 } 801 return $ptgRef.$row.$col; 802 } 803 804 /** 805 * Convert an Excel 3d reference such as "Sheet1!A1" or "Sheet1:Sheet2!A1" to a 806 * ptgRef3d. 807 * 808 * @access private 809 * @param string $cell An Excel cell reference 810 * @return mixed The packed ptgRef3d token on success, PEAR_Error on failure. 811 */ 812 function _convertRef3d($cell) 813 { 814 $class = 2; // as far as I know, this is magick. 815 816 // Split the ref at the ! symbol 817 list($ext_ref, $cell) = split('!', $cell); 818 819 // Convert the external reference part (different for BIFF8) 820 if ($this->_BIFF_version == 0x0500) { 821 $ext_ref = $this->_packExtRef($ext_ref); 822 if (PEAR::isError($ext_ref)) { 823 return $ext_ref; 824 } 825 } elseif ($this->_BIFF_version == 0x0600) { 826 $ext_ref = $this->_getRefIndex($ext_ref); 827 if (PEAR::isError($ext_ref)) { 828 return $ext_ref; 829 } 830 } 831 832 // Convert the cell reference part 833 list($row, $col) = $this->_cellToPackedRowcol($cell); 834 835 // The ptg value depends on the class of the ptg. 836 if ($class == 0) { 837 $ptgRef = pack("C", $this->ptg['ptgRef3d']); 838 } elseif ($class == 1) { 839 $ptgRef = pack("C", $this->ptg['ptgRef3dV']); 840 } elseif ($class == 2) { 841 $ptgRef = pack("C", $this->ptg['ptgRef3dA']); 842 } else { 843 return $this->raiseError("Unknown class $class", 0, PEAR_ERROR_DIE); 844 } 845 846 return $ptgRef . $ext_ref. $row . $col; 847 } 848 849 /** 850 * Convert the sheet name part of an external reference, for example "Sheet1" or 851 * "Sheet1:Sheet2", to a packed structure. 852 * 853 * @access private 854 * @param string $ext_ref The name of the external reference 855 * @return string The reference index in packed() format 856 */ 857 function _packExtRef($ext_ref) 858 { 859 $ext_ref = preg_replace("/^'/", '', $ext_ref); // Remove leading ' if any. 860 $ext_ref = preg_replace("/'$/", '', $ext_ref); // Remove trailing ' if any. 861 862 // Check if there is a sheet range eg., Sheet1:Sheet2. 863 if (preg_match("/:/", $ext_ref)) { 864 list($sheet_name1, $sheet_name2) = split(':', $ext_ref); 865 866 $sheet1 = $this->_getSheetIndex($sheet_name1); 867 if ($sheet1 == -1) { 868 return $this->raiseError("Unknown sheet name $sheet_name1 in formula"); 869 } 870 $sheet2 = $this->_getSheetIndex($sheet_name2); 871 if ($sheet2 == -1) { 872 return $this->raiseError("Unknown sheet name $sheet_name2 in formula"); 873 } 874 875 // Reverse max and min sheet numbers if necessary 876 if ($sheet1 > $sheet2) { 877 list($sheet1, $sheet2) = array($sheet2, $sheet1); 878 } 879 } else { // Single sheet name only. 880 $sheet1 = $this->_getSheetIndex($ext_ref); 881 if ($sheet1 == -1) { 882 return $this->raiseError("Unknown sheet name $ext_ref in formula"); 883 } 884 $sheet2 = $sheet1; 885 } 886 887 // References are stored relative to 0xFFFF. 888 $offset = -1 - $sheet1; 889 890 return pack('vdvv', $offset, 0x00, $sheet1, $sheet2); 891 } 892 893 /** 894 * Look up the REF index that corresponds to an external sheet name 895 * (or range). If it doesn't exist yet add it to the workbook's references 896 * array. It assumes all sheet names given must exist. 897 * 898 * @access private 899 * @param string $ext_ref The name of the external reference 900 * @return mixed The reference index in packed() format on success, 901 * PEAR_Error on failure 902 */ 903 function _getRefIndex($ext_ref) 904 { 905 $ext_ref = preg_replace("/^'/", '', $ext_ref); // Remove leading ' if any. 906 $ext_ref = preg_replace("/'$/", '', $ext_ref); // Remove trailing ' if any. 907 908 // Check if there is a sheet range eg., Sheet1:Sheet2. 909 if (preg_match("/:/", $ext_ref)) { 910 list($sheet_name1, $sheet_name2) = split(':', $ext_ref); 911 912 $sheet1 = $this->_getSheetIndex($sheet_name1); 913 if ($sheet1 == -1) { 914 return $this->raiseError("Unknown sheet name $sheet_name1 in formula"); 915 } 916 $sheet2 = $this->_getSheetIndex($sheet_name2); 917 if ($sheet2 == -1) { 918 return $this->raiseError("Unknown sheet name $sheet_name2 in formula"); 919 } 920 921 // Reverse max and min sheet numbers if necessary 922 if ($sheet1 > $sheet2) { 923 list($sheet1, $sheet2) = array($sheet2, $sheet1); 924 } 925 } else { // Single sheet name only. 926 $sheet1 = $this->_getSheetIndex($ext_ref); 927 if ($sheet1 == -1) { 928 return $this->raiseError("Unknown sheet name $ext_ref in formula"); 929 } 930 $sheet2 = $sheet1; 931 } 932 933 // assume all references belong to this document 934 $supbook_index = 0x00; 935 $ref = pack('vvv', $supbook_index, $sheet1, $sheet2); 936 $total_references = count($this->_references); 937 $index = -1; 938 for ($i = 0; $i < $total_references; $i++) { 939 if ($ref == $this->_references[$i]) { 940 $index = $i; 941 break; 942 } 943 } 944 // if REF was not found add it to references array 945 if ($index == -1) { 946 $this->_references[$total_references] = $ref; 947 $index = $total_references; 948 } 949 950 return pack('v', $index); 951 } 952 953 /** 954 * Look up the index that corresponds to an external sheet name. The hash of 955 * sheet names is updated by the addworksheet() method of the 956 * Spreadsheet_Excel_Writer_Workbook class. 957 * 958 * @access private 959 * @return integer The sheet index, -1 if the sheet was not found 960 */ 961 function _getSheetIndex($sheet_name) 962 { 963 if (!isset($this->_ext_sheets[$sheet_name])) { 964 return -1; 965 } else { 966 return $this->_ext_sheets[$sheet_name]; 967 } 968 } 969 970 /** 971 * This method is used to update the array of sheet names. It is 972 * called by the addWorksheet() method of the 973 * Spreadsheet_Excel_Writer_Workbook class. 974 * 975 * @access public 976 * @see Spreadsheet_Excel_Writer_Workbook::addWorksheet() 977 * @param string $name The name of the worksheet being added 978 * @param integer $index The index of the worksheet being added 979 */ 980 function setExtSheet($name, $index) 981 { 982 $this->_ext_sheets[$name] = $index; 983 } 984 985 /** 986 * pack() row and column into the required 3 or 4 byte format. 987 * 988 * @access private 989 * @param string $cell The Excel cell reference to be packed 990 * @return array Array containing the row and column in packed() format 991 */ 992 function _cellToPackedRowcol($cell) 993 { 994 $cell = strtoupper($cell); 995 list($row, $col, $row_rel, $col_rel) = $this->_cellToRowcol($cell); 996 if ($col >= 256) { 997 return $this->raiseError("Column in: $cell greater than 255"); 998 } 999 // FIXME: change for BIFF8 1000 if ($row >= 16384) { 1001 return $this->raiseError("Row in: $cell greater than 16384 "); 1002 } 1003 1004 // Set the high bits to indicate if row or col are relative. 1005 if ($this->_BIFF_version == 0x0500) { 1006 $row |= $col_rel << 14; 1007 $row |= $row_rel << 15; 1008 $col = pack('C', $col); 1009 } elseif ($this->_BIFF_version == 0x0600) { 1010 $col |= $col_rel << 14; 1011 $col |= $row_rel << 15; 1012 $col = pack('v', $col); 1013 } 1014 $row = pack('v', $row); 1015 1016 return array($row, $col); 1017 } 1018 1019 /** 1020 * pack() row range into the required 3 or 4 byte format. 1021 * Just using maximum col/rows, which is probably not the correct solution 1022 * 1023 * @access private 1024 * @param string $range The Excel range to be packed 1025 * @return array Array containing (row1,col1,row2,col2) in packed() format 1026 */ 1027 function _rangeToPackedRange($range) 1028 { 1029 preg_match('/(\$)?(\d+)\:(\$)?(\d+)/', $range, $match); 1030 // return absolute rows if there is a $ in the ref 1031 $row1_rel = empty($match[1]) ? 1 : 0; 1032 $row1 = $match[2]; 1033 $row2_rel = empty($match[3]) ? 1 : 0; 1034 $row2 = $match[4]; 1035 // Convert 1-index to zero-index 1036 $row1--; 1037 $row2--; 1038 // Trick poor inocent Excel 1039 $col1 = 0; 1040 $col2 = 16383; // FIXME: maximum possible value for Excel 5 (change this!!!) 1041 1042 // FIXME: this changes for BIFF8 1043 if (($row1 >= 16384) or ($row2 >= 16384)) { 1044 return $this->raiseError("Row in: $range greater than 16384 "); 1045 } 1046 1047 // Set the high bits to indicate if rows are relative. 1048 if ($this->_BIFF_version == 0x0500) { 1049 $row1 |= $row1_rel << 14; // FIXME: probably a bug 1050 $row2 |= $row2_rel << 15; 1051 $col1 = pack('C', $col1); 1052 $col2 = pack('C', $col2); 1053 } elseif ($this->_BIFF_version == 0x0600) { 1054 $col1 |= $row1_rel << 15; 1055 $col2 |= $row2_rel << 15; 1056 $col1 = pack('v', $col1); 1057 $col2 = pack('v', $col2); 1058 } 1059 $row1 = pack('v', $row1); 1060 $row2 = pack('v', $row2); 1061 1062 return array($row1, $col1, $row2, $col2); 1063 } 1064 1065 /** 1066 * Convert an Excel cell reference such as A1 or $B2 or C$3 or $D$4 to a zero 1067 * indexed row and column number. Also returns two (0,1) values to indicate 1068 * whether the row or column are relative references. 1069 * 1070 * @access private 1071 * @param string $cell The Excel cell reference in A1 format. 1072 * @return array 1073 */ 1074 function _cellToRowcol($cell) 1075 { 1076 preg_match('/(\$)?([A-I]?[A-Z])(\$)?(\d+)/',$cell,$match); 1077 // return absolute column if there is a $ in the ref 1078 $col_rel = empty($match[1]) ? 1 : 0; 1079 $col_ref = $match[2]; 1080 $row_rel = empty($match[3]) ? 1 : 0; 1081 $row = $match[4]; 1082 1083 // Convert base26 column string to a number. 1084 $expn = strlen($col_ref) - 1; 1085 $col = 0; 1086 $col_ref_length = strlen($col_ref); 1087 for ($i = 0; $i < $col_ref_length; $i++) { 1088 $col += (ord($col_ref{$i}) - ord('A') + 1) * pow(26, $expn); 1089 $expn--; 1090 } 1091 1092 // Convert 1-index to zero-index 1093 $row--; 1094 $col--; 1095 1096 return array($row, $col, $row_rel, $col_rel); 1097 } 1098 1099 /** 1100 * Advance to the next valid token. 1101 * 1102 * @access private 1103 */ 1104 function _advance() 1105 { 1106 $i = $this->_current_char; 1107 $formula_length = strlen($this->_formula); 1108 // eat up white spaces 1109 if ($i < $formula_length) { 1110 while ($this->_formula{$i} == " ") { 1111 $i++; 1112 } 1113 1114 if ($i < ($formula_length - 1)) { 1115 $this->_lookahead = $this->_formula{$i+1}; 1116 } 1117 $token = ''; 1118 } 1119 1120 while ($i < $formula_length) { 1121 $token .= $this->_formula{$i}; 1122 if ($i < ($formula_length - 1)) { 1123 $this->_lookahead = $this->_formula{$i+1}; 1124 } else { 1125 $this->_lookahead = ''; 1126 } 1127 1128 if ($this->_match($token) != '') { 1129 //if ($i < strlen($this->_formula) - 1) { 1130 // $this->_lookahead = $this->_formula{$i+1}; 1131 //} 1132 $this->_current_char = $i + 1; 1133 $this->_current_token = $token; 1134 return 1; 1135 } 1136 1137 if ($i < ($formula_length - 2)) { 1138 $this->_lookahead = $this->_formula{$i+2}; 1139 } else { // if we run out of characters _lookahead becomes empty 1140 $this->_lookahead = ''; 1141 } 1142 $i++; 1143 } 1144 //die("Lexical error ".$this->_current_char); 1145 } 1146 1147 /** 1148 * Checks if it's a valid token. 1149 * 1150 * @access private 1151 * @param mixed $token The token to check. 1152 * @return mixed The checked token or false on failure 1153 */ 1154 function _match($token) 1155 { 1156 switch($token) { 1157 case SPREADSHEET_EXCEL_WRITER_ADD: 1158 return $token; 1159 break; 1160 case SPREADSHEET_EXCEL_WRITER_SUB: 1161 return $token; 1162 break; 1163 case SPREADSHEET_EXCEL_WRITER_MUL: 1164 return $token; 1165 break; 1166 case SPREADSHEET_EXCEL_WRITER_DIV: 1167 return $token; 1168 break; 1169 case SPREADSHEET_EXCEL_WRITER_OPEN: 1170 return $token; 1171 break; 1172 case SPREADSHEET_EXCEL_WRITER_CLOSE: 1173 return $token; 1174 break; 1175 case SPREADSHEET_EXCEL_WRITER_COMA: 1176 return $token; 1177 break; 1178 case SPREADSHEET_EXCEL_WRITER_SEMICOLON: 1179 return $token; 1180 break; 1181 case SPREADSHEET_EXCEL_WRITER_GT: 1182 if ($this->_lookahead == '=') { // it's a GE token 1183 break; 1184 } 1185 return $token; 1186 break; 1187 case SPREADSHEET_EXCEL_WRITER_LT: 1188 // it's a LE or a NE token 1189 if (($this->_lookahead == '=') or ($this->_lookahead == '>')) { 1190 break; 1191 } 1192 return $token; 1193 break; 1194 case SPREADSHEET_EXCEL_WRITER_GE: 1195 return $token; 1196 break; 1197 case SPREADSHEET_EXCEL_WRITER_LE: 1198 return $token; 1199 break; 1200 case SPREADSHEET_EXCEL_WRITER_EQ: 1201 return $token; 1202 break; 1203 case SPREADSHEET_EXCEL_WRITER_NE: 1204 return $token; 1205 break; 1206 default: 1207 // if it's a reference 1208 if (preg_match('/^\$?[A-Ia-i]?[A-Za-z]\$?[0-9]+$/',$token) and 1209 !ereg("[0-9]",$this->_lookahead) and 1210 ($this->_lookahead != ':') and ($this->_lookahead != '.') and 1211 ($this->_lookahead != '!')) 1212 { 1213 return $token; 1214 } 1215 // If it's an external reference (Sheet1!A1 or Sheet1:Sheet2!A1) 1216 elseif (preg_match("/^\w+(\:\w+)?\![A-Ia-i]?[A-Za-z][0-9]+$/u",$token) and 1217 !ereg("[0-9]",$this->_lookahead) and 1218 ($this->_lookahead != ':') and ($this->_lookahead != '.')) 1219 { 1220 return $token; 1221 } 1222 // If it's an external reference ('Sheet1'!A1 or 'Sheet1:Sheet2'!A1) 1223 elseif (preg_match("/^'[\w -]+(\:[\w -]+)?'\![A-Ia-i]?[A-Za-z][0-9]+$/u",$token) and 1224 !ereg("[0-9]",$this->_lookahead) and 1225 ($this->_lookahead != ':') and ($this->_lookahead != '.')) 1226 { 1227 return $token; 1228 } 1229 // if it's a range (A1:A2) 1230 elseif (preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+:(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+$/",$token) and 1231 !ereg("[0-9]",$this->_lookahead)) 1232 { 1233 return $token; 1234 } 1235 // if it's a range (A1..A2) 1236 elseif (preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+\.\.(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+$/",$token) and 1237 !ereg("[0-9]",$this->_lookahead)) 1238 { 1239 return $token; 1240 } 1241 // If it's an external range like Sheet1!A1 or Sheet1:Sheet2!A1:B2 1242 elseif (preg_match("/^\w+(\:\w+)?\!([A-Ia-i]?[A-Za-z])?[0-9]+:([A-Ia-i]?[A-Za-z])?[0-9]+$/u",$token) and 1243 !ereg("[0-9]",$this->_lookahead)) 1244 { 1245 return $token; 1246 } 1247 // If it's an external range like 'Sheet1'!A1 or 'Sheet1:Sheet2'!A1:B2 1248 elseif (preg_match("/^'[\w -]+(\:[\w -]+)?'\!([A-Ia-i]?[A-Za-z])?[0-9]+:([A-Ia-i]?[A-Za-z])?[0-9]+$/u",$token) and 1249 !ereg("[0-9]",$this->_lookahead)) 1250 { 1251 return $token; 1252 } 1253 // If it's a number (check that it's not a sheet name or range) 1254 elseif (is_numeric($token) and 1255 (!is_numeric($token.$this->_lookahead) or ($this->_lookahead == '')) and 1256 ($this->_lookahead != '!') and ($this->_lookahead != ':')) 1257 { 1258 return $token; 1259 } 1260 // If it's a string (of maximum 255 characters) 1261 elseif (ereg("^\"[^\"]{0,255}\"$",$token)) 1262 { 1263 return $token; 1264 } 1265 // if it's a function call 1266 elseif (eregi("^[A-Z0-9\xc0-\xdc\.]+$",$token) and ($this->_lookahead == "(")) 1267 { 1268 return $token; 1269 } 1270 return ''; 1271 } 1272 } 1273 1274 /** 1275 * The parsing method. It parses a formula. 1276 * 1277 * @access public 1278 * @param string $formula The formula to parse, without the initial equal 1279 * sign (=). 1280 * @return mixed true on success, PEAR_Error on failure 1281 */ 1282 function parse($formula) 1283 { 1284 $this->_current_char = 0; 1285 $this->_formula = $formula; 1286 $this->_lookahead = $formula{1}; 1287 $this->_advance(); 1288 $this->_parse_tree = $this->_condition(); 1289 if (PEAR::isError($this->_parse_tree)) { 1290 return $this->_parse_tree; 1291 } 1292 return true; 1293 } 1294 1295 /** 1296 * It parses a condition. It assumes the following rule: 1297 * Cond -> Expr [(">" | "<") Expr] 1298 * 1299 * @access private 1300 * @return mixed The parsed ptg'd tree on success, PEAR_Error on failure 1301 */ 1302 function _condition() 1303 { 1304 $result = $this->_expression(); 1305 if (PEAR::isError($result)) { 1306 return $result; 1307 } 1308 if ($this->_current_token == SPREADSHEET_EXCEL_WRITER_LT) { 1309 $this->_advance(); 1310 $result2 = $this->_expression(); 1311 if (PEAR::isError($result2)) { 1312 return $result2; 1313 } 1314 $result = $this->_createTree('ptgLT', $result, $result2); 1315 } elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_GT) { 1316 $this->_advance(); 1317 $result2 = $this->_expression(); 1318 if (PEAR::isError($result2)) { 1319 return $result2; 1320 } 1321 $result = $this->_createTree('ptgGT', $result, $result2); 1322 } elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_LE) { 1323 $this->_advance(); 1324 $result2 = $this->_expression(); 1325 if (PEAR::isError($result2)) { 1326 return $result2; 1327 } 1328 $result = $this->_createTree('ptgLE', $result, $result2); 1329 } elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_GE) { 1330 $this->_advance(); 1331 $result2 = $this->_expression(); 1332 if (PEAR::isError($result2)) { 1333 return $result2; 1334 } 1335 $result = $this->_createTree('ptgGE', $result, $result2); 1336 } elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_EQ) { 1337 $this->_advance(); 1338 $result2 = $this->_expression(); 1339 if (PEAR::isError($result2)) { 1340 return $result2; 1341 } 1342 $result = $this->_createTree('ptgEQ', $result, $result2); 1343 } elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_NE) { 1344 $this->_advance(); 1345 $result2 = $this->_expression(); 1346 if (PEAR::isError($result2)) { 1347 return $result2; 1348 } 1349 $result = $this->_createTree('ptgNE', $result, $result2); 1350 } 1351 return $result; 1352 } 1353 1354 /** 1355 * It parses a expression. It assumes the following rule: 1356 * Expr -> Term [("+" | "-") Term] 1357 * -> "string" 1358 * -> "-" Term 1359 * 1360 * @access private 1361 * @return mixed The parsed ptg'd tree on success, PEAR_Error on failure 1362 */ 1363 function _expression() 1364 { 1365 // If it's a string return a string node 1366 if (ereg("^\"[^\"]{0,255}\"$", $this->_current_token)) { 1367 $result = $this->_createTree($this->_current_token, '', ''); 1368 $this->_advance(); 1369 return $result; 1370 } elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_SUB) { 1371 // catch "-" Term 1372 $this->_advance(); 1373 $result2 = $this->_expression(); 1374 $result = $this->_createTree('ptgUminus', $result2, ''); 1375 return $result; 1376 } 1377 $result = $this->_term(); 1378 if (PEAR::isError($result)) { 1379 return $result; 1380 } 1381 while (($this->_current_token == SPREADSHEET_EXCEL_WRITER_ADD) or 1382 ($this->_current_token == SPREADSHEET_EXCEL_WRITER_SUB)) { 1383 /**/ 1384 if ($this->_current_token == SPREADSHEET_EXCEL_WRITER_ADD) { 1385 $this->_advance(); 1386 $result2 = $this->_term(); 1387 if (PEAR::isError($result2)) { 1388 return $result2; 1389 } 1390 $result = $this->_createTree('ptgAdd', $result, $result2); 1391 } else { 1392 $this->_advance(); 1393 $result2 = $this->_term(); 1394 if (PEAR::isError($result2)) { 1395 return $result2; 1396 } 1397 $result = $this->_createTree('ptgSub', $result, $result2); 1398 } 1399 } 1400 return $result; 1401 } 1402 1403 /** 1404 * This function just introduces a ptgParen element in the tree, so that Excel 1405 * doesn't get confused when working with a parenthesized formula afterwards. 1406 * 1407 * @access private 1408 * @see _fact() 1409 * @return array The parsed ptg'd tree 1410 */ 1411 function _parenthesizedExpression() 1412 { 1413 $result = $this->_createTree('ptgParen', $this->_expression(), ''); 1414 return $result; 1415 } 1416 1417 /** 1418 * It parses a term. It assumes the following rule: 1419 * Term -> Fact [("*" | "/") Fact] 1420 * 1421 * @access private 1422 * @return mixed The parsed ptg'd tree on success, PEAR_Error on failure 1423 */ 1424 function _term() 1425 { 1426 $result = $this->_fact(); 1427 if (PEAR::isError($result)) { 1428 return $result; 1429 } 1430 while (($this->_current_token == SPREADSHEET_EXCEL_WRITER_MUL) or 1431 ($this->_current_token == SPREADSHEET_EXCEL_WRITER_DIV)) { 1432 /**/ 1433 if ($this->_current_token == SPREADSHEET_EXCEL_WRITER_MUL) { 1434 $this->_advance(); 1435 $result2 = $this->_fact(); 1436 if (PEAR::isError($result2)) { 1437 return $result2; 1438 } 1439 $result = $this->_createTree('ptgMul', $result, $result2); 1440 } else { 1441 $this->_advance(); 1442 $result2 = $this->_fact(); 1443 if (PEAR::isError($result2)) { 1444 return $result2; 1445 } 1446 $result = $this->_createTree('ptgDiv', $result, $result2); 1447 } 1448 } 1449 return $result; 1450 } 1451 1452 /** 1453 * It parses a factor. It assumes the following rule: 1454 * Fact -> ( Expr ) 1455 * | CellRef 1456 * | CellRange 1457 * | Number 1458 * | Function 1459 * 1460 * @access private 1461 * @return mixed The parsed ptg'd tree on success, PEAR_Error on failure 1462 */ 1463 function _fact() 1464 { 1465 if ($this->_current_token == SPREADSHEET_EXCEL_WRITER_OPEN) { 1466 $this->_advance(); // eat the "(" 1467 $result = $this->_parenthesizedExpression(); 1468 if ($this->_current_token != SPREADSHEET_EXCEL_WRITER_CLOSE) { 1469 return $this->raiseError("')' token expected."); 1470 } 1471 $this->_advance(); // eat the ")" 1472 return $result; 1473 } 1474 // if it's a reference 1475 if (preg_match('/^\$?[A-Ia-i]?[A-Za-z]\$?[0-9]+$/',$this->_current_token)) 1476 { 1477 $result = $this->_createTree($this->_current_token, '', ''); 1478 $this->_advance(); 1479 return $result; 1480 } 1481 // If it's an external reference (Sheet1!A1 or Sheet1:Sheet2!A1) 1482 elseif (preg_match("/^\w+(\:\w+)?\![A-Ia-i]?[A-Za-z][0-9]+$/u",$this->_current_token)) 1483 { 1484 $result = $this->_createTree($this->_current_token, '', ''); 1485 $this->_advance(); 1486 return $result; 1487 } 1488 // If it's an external reference ('Sheet1'!A1 or 'Sheet1:Sheet2'!A1) 1489 elseif (preg_match("/^'[\w -]+(\:[\w -]+)?'\![A-Ia-i]?[A-Za-z][0-9]+$/u",$this->_current_token)) 1490 { 1491 $result = $this->_createTree($this->_current_token, '', ''); 1492 $this->_advance(); 1493 return $result; 1494 } 1495 // if it's a range 1496 elseif (preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+:(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+$/",$this->_current_token) or 1497 preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+\.\.(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+$/",$this->_current_token)) 1498 { 1499 $result = $this->_current_token; 1500 $this->_advance(); 1501 return $result; 1502 } 1503 // If it's an external range (Sheet1!A1 or Sheet1!A1:B2) 1504 elseif (preg_match("/^\w+(\:\w+)?\!([A-Ia-i]?[A-Za-z])?[0-9]+:([A-Ia-i]?[A-Za-z])?[0-9]+$/u",$this->_current_token)) 1505 { 1506 $result = $this->_current_token; 1507 $this->_advance(); 1508 return $result; 1509 } 1510 // If it's an external range ('Sheet1'!A1 or 'Sheet1'!A1:B2) 1511 elseif (preg_match("/^'[\w -]+(\:[\w -]+)?'\!([A-Ia-i]?[A-Za-z])?[0-9]+:([A-Ia-i]?[A-Za-z])?[0-9]+$/u",$this->_current_token)) 1512 { 1513 $result = $this->_current_token; 1514 $this->_advance(); 1515 return $result; 1516 } 1517 elseif (is_numeric($this->_current_token)) 1518 { 1519 $result = $this->_createTree($this->_current_token, '', ''); 1520 $this->_advance(); 1521 return $result; 1522 } 1523 // if it's a function call 1524 elseif (eregi("^[A-Z0-9\xc0-\xdc\.]+$",$this->_current_token)) 1525 { 1526 $result = $this->_func(); 1527 return $result; 1528 } 1529 return $this->raiseError("Syntax error: ".$this->_current_token. 1530 ", lookahead: ".$this->_lookahead. 1531 ", current char: ".$this->_current_char); 1532 } 1533 1534 /** 1535 * It parses a function call. It assumes the following rule: 1536 * Func -> ( Expr [,Expr]* ) 1537 * 1538 * @access private 1539 * @return mixed The parsed ptg'd tree on success, PEAR_Error on failure 1540 */ 1541 function _func() 1542 { 1543 $num_args = 0; // number of arguments received 1544 $function = strtoupper($this->_current_token); 1545 $result = ''; // initialize result 1546 $this->_advance(); 1547 $this->_advance(); // eat the "(" 1548 while ($this->_current_token != ')') { 1549 /**/ 1550 if ($num_args > 0) { 1551 if ($this->_current_token == SPREADSHEET_EXCEL_WRITER_COMA or 1552 $this->_current_token == SPREADSHEET_EXCEL_WRITER_SEMICOLON) 1553 { 1554 $this->_advance(); // eat the "," or ";" 1555 } else { 1556 return $this->raiseError("Syntax error: comma expected in ". 1557 "function $function, arg #{$num_args}"); 1558 } 1559 $result2 = $this->_condition(); 1560 if (PEAR::isError($result2)) { 1561 return $result2; 1562 } 1563 $result = $this->_createTree('arg', $result, $result2); 1564 } else { // first argument 1565 $result2 = $this->_condition(); 1566 if (PEAR::isError($result2)) { 1567 return $result2; 1568 } 1569 $result = $this->_createTree('arg', '', $result2); 1570 } 1571 $num_args++; 1572 } 1573 if (!isset($this->_functions[$function])) { 1574 return $this->raiseError("Function $function() doesn't exist"); 1575 } 1576 $args = $this->_functions[$function][1]; 1577 // If fixed number of args eg. TIME($i,$j,$k). Check that the number of args is valid. 1578 if (($args >= 0) and ($args != $num_args)) { 1579 return $this->raiseError("Incorrect number of arguments in function $function() "); 1580 } 1581 1582 $result = $this->_createTree($function, $result, $num_args); 1583 $this->_advance(); // eat the ")" 1584 return $result; 1585 } 1586 1587 /** 1588 * Creates a tree. In fact an array which may have one or two arrays (sub-trees) 1589 * as elements. 1590 * 1591 * @access private 1592 * @param mixed $value The value of this node. 1593 * @param mixed $left The left array (sub-tree) or a final node. 1594 * @param mixed $right The right array (sub-tree) or a final node. 1595 * @return array A tree 1596 */ 1597 function _createTree($value, $left, $right) 1598 { 1599 return array('value' => $value, 'left' => $left, 'right' => $right); 1600 } 1601 1602 /** 1603 * Builds a string containing the tree in reverse polish notation (What you 1604 * would use in a HP calculator stack). 1605 * The following tree: 1606 * 1607 * + 1608 * / \ 1609 * 2 3 1610 * 1611 * produces: "23+" 1612 * 1613 * The following tree: 1614 * 1615 * + 1616 * / \ 1617 * 3 * 1618 * / \ 1619 * 6 A1 1620 * 1621 * produces: "36A1*+" 1622 * 1623 * In fact all operands, functions, references, etc... are written as ptg's 1624 * 1625 * @access public 1626 * @param array $tree The optional tree to convert. 1627 * @return string The tree in reverse polish notation 1628 */ 1629 function toReversePolish($tree = array()) 1630 { 1631 $polish = ""; // the string we are going to return 1632 if (empty($tree)) { // If it's the first call use _parse_tree 1633 $tree = $this->_parse_tree; 1634 } 1635 if (is_array($tree['left'])) { 1636 $converted_tree = $this->toReversePolish($tree['left']); 1637 if (PEAR::isError($converted_tree)) { 1638 return $converted_tree; 1639 } 1640 $polish .= $converted_tree; 1641 } elseif ($tree['left'] != '') { // It's a final node 1642 $converted_tree = $this->_convert($tree['left']); 1643 if (PEAR::isError($converted_tree)) { 1644 return $converted_tree; 1645 } 1646 $polish .= $converted_tree; 1647 } 1648 if (is_array($tree['right'])) { 1649 $converted_tree = $this->toReversePolish($tree['right']); 1650 if (PEAR::isError($converted_tree)) { 1651 return $converted_tree; 1652 } 1653 $polish .= $converted_tree; 1654 } elseif ($tree['right'] != '') { // It's a final node 1655 $converted_tree = $this->_convert($tree['right']); 1656 if (PEAR::isError($converted_tree)) { 1657 return $converted_tree; 1658 } 1659 $polish .= $converted_tree; 1660 } 1661 // if it's a function convert it here (so we can set it's arguments) 1662 if (preg_match("/^[A-Z0-9\xc0-\xdc\.]+$/",$tree['value']) and 1663 !preg_match('/^([A-Ia-i]?[A-Za-z])(\d+)$/',$tree['value']) and 1664 !preg_match("/^[A-Ia-i]?[A-Za-z](\d+)\.\.[A-Ia-i]?[A-Za-z](\d+)$/",$tree['value']) and 1665 !is_numeric($tree['value']) and 1666 !isset($this->ptg[$tree['value']])) 1667 { 1668 // left subtree for a function is always an array. 1669 if ($tree['left'] != '') { 1670 $left_tree = $this->toReversePolish($tree['left']); 1671 } else { 1672 $left_tree = ''; 1673 } 1674 if (PEAR::isError($left_tree)) { 1675 return $left_tree; 1676 } 1677 // add it's left subtree and return. 1678 return $left_tree.$this->_convertFunction($tree['value'], $tree['right']); 1679 } else { 1680 $converted_tree = $this->_convert($tree['value']); 1681 if (PEAR::isError($converted_tree)) { 1682 return $converted_tree; 1683 } 1684 } 1685 $polish .= $converted_tree; 1686 return $polish; 1687 } 1688 } 1689 ?>
title
Description
Body
title
Description
Body
title
Description
Body
title
Body
| Generated: Wed Dec 30 05:55:15 2009 | Cross-referenced by PHPXref 0.7 |