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