wpdatatables / lib / phpoffice / phpspreadsheet / src / PhpSpreadsheet / Writer / Xls / Parser.php

Parser.php in wpDataTables – WordPress Data Table, Dynamic Tables & Table Charts Plugin 6.5.1.7, at lib/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Writer/Xls/Parser.php

1,665 lines 55.8 KB

Raw Download Zip

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