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