tablepress / libraries / vendor / PhpSpreadsheet / Reader / Xls.php

Xls.php in TablePress – Tables in WordPress made easy 3.4, at libraries/vendor/PhpSpreadsheet/Reader/Xls.php

4,943 lines 154.4 KB

Raw Download Zip

1	<?php
2
3	namespace TablePress\PhpOffice\PhpSpreadsheet\Reader;
4
5	use TablePress\Composer\Pcre\Preg;
6	use TablePress\PhpOffice\PhpSpreadsheet\Cell\AddressRange;
7	use TablePress\PhpOffice\PhpSpreadsheet\Cell\Coordinate;
8	use TablePress\PhpOffice\PhpSpreadsheet\Cell\DataType;
9	use TablePress\PhpOffice\PhpSpreadsheet\Cell\DataValidation;
10	use TablePress\PhpOffice\PhpSpreadsheet\Exception as PhpSpreadsheetException;
11	use TablePress\PhpOffice\PhpSpreadsheet\Reader\Xls\Style\CellFont;
12	use TablePress\PhpOffice\PhpSpreadsheet\Reader\Xls\Style\FillPattern;
13	use TablePress\PhpOffice\PhpSpreadsheet\RichText\RichText;
14	use TablePress\PhpOffice\PhpSpreadsheet\Shared\CodePage;
15	use TablePress\PhpOffice\PhpSpreadsheet\Shared\Date;
16	use TablePress\PhpOffice\PhpSpreadsheet\Shared\Escher;
17	use TablePress\PhpOffice\PhpSpreadsheet\Shared\File;
18	use TablePress\PhpOffice\PhpSpreadsheet\Shared\OLE;
19	use TablePress\PhpOffice\PhpSpreadsheet\Shared\OLERead;
20	use TablePress\PhpOffice\PhpSpreadsheet\Shared\StringHelper;
21	use TablePress\PhpOffice\PhpSpreadsheet\Spreadsheet;
22	use TablePress\PhpOffice\PhpSpreadsheet\Style\Alignment;
23	use TablePress\PhpOffice\PhpSpreadsheet\Style\Border;
24	use TablePress\PhpOffice\PhpSpreadsheet\Style\Borders;
25	use TablePress\PhpOffice\PhpSpreadsheet\Style\Conditional;
26	use TablePress\PhpOffice\PhpSpreadsheet\Style\Fill;
27	use TablePress\PhpOffice\PhpSpreadsheet\Style\Font;
28	use TablePress\PhpOffice\PhpSpreadsheet\Style\NumberFormat;
29	use TablePress\PhpOffice\PhpSpreadsheet\Style\Protection;
30	use TablePress\PhpOffice\PhpSpreadsheet\Style\Style;
31	use TablePress\PhpOffice\PhpSpreadsheet\Worksheet\PageSetup;
32	use TablePress\PhpOffice\PhpSpreadsheet\Worksheet\SheetView;
33	use TablePress\PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
34
35	// Original file header of ParseXL (used as the base for this class):
36	// --------------------------------------------------------------------------------
37	// Adapted from Excel_Spreadsheet_Reader developed by users bizon153,
38	// trex005, and mmp11 (SourceForge.net)
39	// https://sourceforge.net/projects/phpexcelreader/
40	// Primary changes made by canyoncasa (dvc) for ParseXL 1.00 ...
41	// Modelled moreso after Perl Excel Parse/Write modules
42	// Added Parse_Excel_Spreadsheet object
43	// Reads a whole worksheet or tab as row,column array or as
44	// associated hash of indexed rows and named column fields
45	// Added variables for worksheet (tab) indexes and names
46	// Added an object call for loading individual woorksheets
47	// Changed default indexing defaults to 0 based arrays
48	// Fixed date/time and percent formats
49	// Includes patches found at SourceForge...
50	// unicode patch by nobody
51	// unpack("d") machine depedency patch by matchy
52	// boundsheet utf16 patch by bjaenichen
53	// Renamed functions for shorter names
54	// General code cleanup and rigor, including <80 column width
55	// Included a testcase Excel file and PHP example calls
56	// Code works for PHP 5.x
57
58	// Primary changes made by canyoncasa (dvc) for ParseXL 1.10 ...
59	// http://sourceforge.net/tracker/index.php?func=detail&aid=1466964&group_id=99160&atid=623334
60	// Decoding of formula conditions, results, and tokens.
61	// Support for user-defined named cells added as an array "namedcells"
62	// Patch code for user-defined named cells supports single cells only.
63	// NOTE: this patch only works for BIFF8 as BIFF5-7 use a different
64	// external sheet reference structure
65	class Xls extends XlsBase
66	{
67	/**
68	* Summary Information stream data.
69	*/
70	protected ?string $summaryInformation = null;
71
72	/**
73	* Extended Summary Information stream data.
74	*/
75	protected ?string $documentSummaryInformation = null;
76
77	/**
78	* Workbook stream data. (Includes workbook globals substream as well as sheet substreams).
79	*/
80	protected string $data;
81
82	/**
83	* Size in bytes of $this->data.
84	*/
85	protected int $dataSize;
86
87	/**
88	* Current position in stream.
89	*/
90	protected int $pos;
91
92	/**
93	* Workbook to be returned by the reader.
94	*/
95	protected Spreadsheet $spreadsheet;
96
97	/**
98	* Worksheet that is currently being built by the reader.
99	*/
100	protected Worksheet $phpSheet;
101
102	/**
103	* Cached sheet title for the current sheet being parsed.
104	* Avoids repeated getTitle() calls in per-cell read filter checks.
105	*/
106	protected string $phpSheetTitle = '';
107
108	/**
109	* BIFF version.
110	*/
111	protected int $version = 0;
112
113	/**
114	* Shared formats.
115	*
116	* @var mixed[]
117	*/
118	protected array $formats;
119
120	/**
121	* Shared fonts.
122	*
123	* @var Font[]
124	*/
125	protected array $objFonts;
126
127	/**
128	* Color palette.
129	*
130	* @var string[][]
131	*/
132	protected array $palette;
133
134	/**
135	* Worksheets.
136	*
137	* @var array<array{name: string, offset: int, sheetState: string, sheetType: int\|string}>
138	*/
139	protected array $sheets;
140
141	/**
142	* External books.
143	*
144	* @var mixed[][]
145	*/
146	protected array $externalBooks;
147
148	/**
149	* REF structures. Only applies to BIFF8.
150	*
151	* @var array<int, array{'externalBookIndex': int, 'firstSheetIndex': int, 'lastSheetIndex': int}>
152	*/
153	protected array $ref;
154
155	/**
156	* External names.
157	*
158	* @var array<array<string, mixed>\|string>
159	*/
160	protected array $externalNames;
161
162	/**
163	* Defined names.
164	*
165	* @var array<int, array{isBuiltInName: int, name: string, formula: string, scope: int}>
166	*/
167	protected array $definedname;
168
169	/**
170	* Shared strings. Only applies to BIFF8.
171	*
172	* @var array<array{value: string, fmtRuns: mixed[]}>
173	*/
174	protected array $sst;
175
176	/**
177	* Panes are frozen? (in sheet currently being read). See WINDOW2 record.
178	*/
179	protected bool $frozen;
180
181	/**
182	* Fit printout to number of pages? (in sheet currently being read). See SHEETPR record.
183	*/
184	protected bool $isFitToPages;
185
186	/**
187	* Objects. One OBJ record contributes with one entry.
188	*
189	* @var mixed[]
190	*/
191	protected array $objs;
192
193	/**
194	* Text Objects. One TXO record corresponds with one entry.
195	*
196	* @var array<array{text: string, format: string, alignment: int, rotation: int}>
197	*/
198	protected array $textObjects;
199
200	/**
201	* Cell Annotations (BIFF8).
202	*
203	* @var mixed[]
204	*/
205	protected array $cellNotes;
206
207	/**
208	* The combined MSODRAWINGGROUP data.
209	*/
210	protected string $drawingGroupData;
211
212	/**
213	* The combined MSODRAWING data (per sheet).
214	*/
215	protected string $drawingData;
216
217	/**
218	* Keep track of XF index.
219	*/
220	protected int $xfIndex;
221
222	/**
223	* Mapping of XF index (that is a cell XF) to final index in cellXf collection.
224	*
225	* @var int[]
226	*/
227	protected array $mapCellXfIndex;
228
229	/**
230	* Mapping of XF index (that is a style XF) to final index in cellStyleXf collection.
231	*
232	* @var int[]
233	*/
234	protected array $mapCellStyleXfIndex;
235
236	/**
237	* The shared formulas in a sheet. One SHAREDFMLA record contributes with one value.
238	*
239	* @var mixed[]
240	*/
241	protected array $sharedFormulas;
242
243	/**
244	* The shared formula parts in a sheet. One FORMULA record contributes with one value if it
245	* refers to a shared formula.
246	*
247	* @var mixed[]
248	*/
249	protected array $sharedFormulaParts;
250
251	/**
252	* The type of encryption in use.
253	*/
254	protected int $encryption = 0;
255
256	/**
257	* The position in the stream after which contents are encrypted.
258	*/
259	protected int $encryptionStartPos = 0;
260
261	protected string $encryptionPassword = 'VelvetSweatshop';
262
263	/**
264	* The current RC4 decryption object.
265	*/
266	protected ?Xls\RC4 $rc4Key = null;
267
268	/**
269	* The position in the stream that the RC4 decryption object was left at.
270	*/
271	protected int $rc4Pos = 0;
272
273	/**
274	* The current MD5 context state.
275	* It is set via call-by-reference to verifyPassword.
276	*/
277	private string $md5Ctxt = '';
278
279	protected int $textObjRef;
280
281	protected string $baseCell;
282
283	protected bool $activeSheetSet = false;
284
285	/**
286	* Reads names of the worksheets from a file, without parsing the whole file to a PhpSpreadsheet object.
287	*
288	* @return string[]
289	*/
290	public function listWorksheetNames(string $filename): array
291	{
292	return (new Xls\ListFunctions())->listWorksheetNames2($filename, $this);
293	}
294
295	/**
296	* Return worksheet info (Name, Last Column Letter, Last Column Index, Total Rows, Total Columns).
297	*
298	* @return array<int, array{worksheetName: string, lastColumnLetter: string, lastColumnIndex: int, totalRows: int, totalColumns: int, sheetState: string}>
299	*/
300	public function listWorksheetInfo(string $filename): array
301	{
302	return (new Xls\ListFunctions())->listWorksheetInfo2($filename, $this);
303	}
304
305	/**
306	* Return worksheet info (Name, Last Column Letter, Last Column Index, Total Rows, Total Columns).
307	*
308	* @return array<int, array{worksheetName: string, dimensionsMinR: int, dimensionsMinC: int, dimensionsMaxR: int, dimensionsMaxC: int, lastColumnLetter: string}>
309	*/
310	public function listWorksheetDimensions(string $filename): array
311	{
312	return (new Xls\ListFunctions())->listWorksheetDimensions2($filename, $this);
313	}
314
315	/**
316	* Loads PhpSpreadsheet from file.
317	*/
318	protected function loadSpreadsheetFromFile(string $filename): Spreadsheet
319	{
320	return (new Xls\LoadSpreadsheet())->loadSpreadsheetFromFile2($filename, $this);
321	}
322
323	/**
324	* Read record data from stream, decrypting as required.
325	*
326	* @param string $data Data stream to read from
327	* @param int $pos Position to start reading from
328	* @param int $len Record data length
329	*
330	* @return string Record data
331	*/
332	protected function readRecordData(string $data, int $pos, int $len): string
333	{
334	$data = (string) substr($data, $pos, $len);
335
336	// File not encrypted, or record before encryption start point
337	if ($this->encryption == self::MS_BIFF_CRYPTO_NONE \|\| $pos < $this->encryptionStartPos) {
338	return $data;
339	}
340
341	$recordData = '';
342	if ($this->encryption == self::MS_BIFF_CRYPTO_RC4) {
343	$oldBlock = floor($this->rc4Pos / self::REKEY_BLOCK);
344	$block = (int) floor($pos / self::REKEY_BLOCK);
345	$endBlock = (int) floor(($pos + $len) / self::REKEY_BLOCK);
346
347	// Spin an RC4 decryptor to the right spot. If we have a decryptor sitting
348	// at a point earlier in the current block, re-use it as we can save some time.
349	if ($block != $oldBlock \|\| $pos < $this->rc4Pos \|\| !$this->rc4Key) {
350	$this->rc4Key = $this->makeKey($block, $this->md5Ctxt);
351	$step = $pos % self::REKEY_BLOCK;
352	} else {
353	$step = $pos - $this->rc4Pos;
354	}
355	$this->rc4Key->RC4(str_repeat("\0", $step));
356
357	// Decrypt record data (re-keying at the end of every block)
358	while ($block != $endBlock) {
359	$step = self::REKEY_BLOCK - ($pos % self::REKEY_BLOCK);
360	$recordData .= $this->rc4Key->RC4((string) substr($data, 0, $step));
361	$data = (string) substr($data, $step);
362	$pos += $step;
363	$len -= $step;
364	++$block;
365	$this->rc4Key = $this->makeKey($block, $this->md5Ctxt);
366	}
367	$recordData .= $this->rc4Key->RC4((string) substr($data, 0, $len));
368
369	// Keep track of the position of this decryptor.
370	// We'll try and re-use it later if we can to speed things up
371	$this->rc4Pos = $pos + $len;
372	} elseif ($this->encryption == self::MS_BIFF_CRYPTO_XOR) {
373	throw new Exception('XOr encryption not supported');
374	}
375
376	return $recordData;
377	}
378
379	/**
380	* Use OLE reader to extract the relevant data streams from the OLE file.
381	*/
382	protected function loadOLE(string $filename): void
383	{
384	// OLE reader
385	$ole = new OLERead();
386	// get excel data,
387	$ole->read($filename);
388	// Get workbook data: workbook stream + sheet streams
389	$this->data = $ole->getStream($ole->wrkbook) ?? '';
390	// Get summary information data
391	$this->summaryInformation = $ole->getStream($ole->summaryInformation);
392	// Get additional document summary information data
393	$this->documentSummaryInformation = $ole->getStream($ole->documentSummaryInformation);
394	}
395
396	/**
397	* Read summary information.
398	*/
399	protected function readSummaryInformation(): void
400	{
401	if (!isset($this->summaryInformation)) {
402	return;
403	}
404
405	// offset: 0; size: 2; must be 0xFE 0xFF (UTF-16 LE byte order mark)
406	// offset: 2; size: 2;
407	// offset: 4; size: 2; OS version
408	// offset: 6; size: 2; OS indicator
409	// offset: 8; size: 16
410	// offset: 24; size: 4; section count
411	//$secCount = self::getInt4d($this->summaryInformation, 24);
412
413	// offset: 28; size: 16; first section's class id: e0 85 9f f2 f9 4f 68 10 ab 91 08 00 2b 27 b3 d9
414	// offset: 44; size: 4
415	$secOffset = self::getInt4d($this->summaryInformation, 44);
416
417	// section header
418	// offset: $secOffset; size: 4; section length
419	//$secLength = self::getInt4d($this->summaryInformation, $secOffset);
420
421	// offset: $secOffset+4; size: 4; property count
422	$countProperties = self::getInt4d($this->summaryInformation, $secOffset + 4);
423
424	// initialize code page (used to resolve string values)
425	$codePage = 'CP1252';
426
427	// offset: ($secOffset+8); size: var
428	// loop through property decarations and properties
429	for ($i = 0; $i < $countProperties; ++$i) {
430	// offset: ($secOffset+8) + (8 * $i); size: 4; property ID
431	$id = self::getInt4d($this->summaryInformation, ($secOffset + 8) + (8 * $i));
432
433	// Use value of property id as appropriate
434	// offset: ($secOffset+12) + (8 * $i); size: 4; offset from beginning of section (48)
435	$offset = self::getInt4d($this->summaryInformation, ($secOffset + 12) + (8 * $i));
436
437	$type = self::getInt4d($this->summaryInformation, $secOffset + $offset);
438
439	// initialize property value
440	$value = null;
441
442	// extract property value based on property type
443	switch ($type) {
444	case 0x02: // 2 byte signed integer
445	$value = self::getUInt2d($this->summaryInformation, $secOffset + 4 + $offset);
446
447	break;
448	case 0x03: // 4 byte signed integer
449	$value = self::getInt4d($this->summaryInformation, $secOffset + 4 + $offset);
450
451	break;
452	case 0x13: // 4 byte unsigned integer
453	// not needed yet, fix later if necessary
454	break;
455	case 0x1E: // null-terminated string prepended by dword string length
456	$byteLength = self::getInt4d($this->summaryInformation, $secOffset + 4 + $offset);
457	$value = (string) substr($this->summaryInformation, $secOffset + 8 + $offset, $byteLength);
458	$value = StringHelper::convertEncoding($value, 'UTF-8', $codePage);
459	$value = rtrim($value);
460
461	break;
462	case 0x40: // Filetime (64-bit value representing the number of 100-nanosecond intervals since January 1, 1601)
463	// PHP-time
464	$value = OLE::OLE2LocalDate((string) substr($this->summaryInformation, $secOffset + 4 + $offset, 8));
465
466	break;
467	case 0x47: // Clipboard format
468	// not needed yet, fix later if necessary
469	break;
470	}
471
472	switch ($id) {
473	case 0x01: // Code Page
474	$codePage = CodePage::numberToName((int) $value);
475
476	break;
477	case 0x02: // Title
478	$this->spreadsheet->getProperties()->setTitle("$value");
479
480	break;
481	case 0x03: // Subject
482	$this->spreadsheet->getProperties()->setSubject("$value");
483
484	break;
485	case 0x04: // Author (Creator)
486	$this->spreadsheet->getProperties()->setCreator("$value");
487
488	break;
489	case 0x05: // Keywords
490	$this->spreadsheet->getProperties()->setKeywords("$value");
491
492	break;
493	case 0x06: // Comments (Description)
494	$this->spreadsheet->getProperties()->setDescription("$value");
495
496	break;
497	case 0x07: // Template
498	// Not supported by PhpSpreadsheet
499	break;
500	case 0x08: // Last Saved By (LastModifiedBy)
501	$this->spreadsheet->getProperties()->setLastModifiedBy("$value");
502
503	break;
504	case 0x09: // Revision
505	// Not supported by PhpSpreadsheet
506	break;
507	case 0x0A: // Total Editing Time
508	// Not supported by PhpSpreadsheet
509	break;
510	case 0x0B: // Last Printed
511	// Not supported by PhpSpreadsheet
512	break;
513	case 0x0C: // Created Date/Time
514	$this->spreadsheet->getProperties()->setCreated($value);
515
516	break;
517	case 0x0D: // Modified Date/Time
518	$this->spreadsheet->getProperties()->setModified($value);
519
520	break;
521	case 0x0E: // Number of Pages
522	// Not supported by PhpSpreadsheet
523	break;
524	case 0x0F: // Number of Words
525	// Not supported by PhpSpreadsheet
526	break;
527	case 0x10: // Number of Characters
528	// Not supported by PhpSpreadsheet
529	break;
530	case 0x11: // Thumbnail
531	// Not supported by PhpSpreadsheet
532	break;
533	case 0x12: // Name of creating application
534	// Not supported by PhpSpreadsheet
535	break;
536	case 0x13: // Security
537	// Not supported by PhpSpreadsheet
538	break;
539	}
540	}
541	}
542
543	/**
544	* Read additional document summary information.
545	*/
546	protected function readDocumentSummaryInformation(): void
547	{
548	if (!isset($this->documentSummaryInformation)) {
549	return;
550	}
551
552	// offset: 0; size: 2; must be 0xFE 0xFF (UTF-16 LE byte order mark)
553	// offset: 2; size: 2;
554	// offset: 4; size: 2; OS version
555	// offset: 6; size: 2; OS indicator
556	// offset: 8; size: 16
557	// offset: 24; size: 4; section count
558	//$secCount = self::getInt4d($this->documentSummaryInformation, 24);
559
560	// offset: 28; size: 16; first section's class id: 02 d5 cd d5 9c 2e 1b 10 93 97 08 00 2b 2c f9 ae
561	// offset: 44; size: 4; first section offset
562	$secOffset = self::getInt4d($this->documentSummaryInformation, 44);
563
564	// section header
565	// offset: $secOffset; size: 4; section length
566	//$secLength = self::getInt4d($this->documentSummaryInformation, $secOffset);
567
568	// offset: $secOffset+4; size: 4; property count
569	$countProperties = self::getInt4d($this->documentSummaryInformation, $secOffset + 4);
570
571	// initialize code page (used to resolve string values)
572	$codePage = 'CP1252';
573
574	// offset: ($secOffset+8); size: var
575	// loop through property decarations and properties
576	for ($i = 0; $i < $countProperties; ++$i) {
577	// offset: ($secOffset+8) + (8 * $i); size: 4; property ID
578	$id = self::getInt4d($this->documentSummaryInformation, ($secOffset + 8) + (8 * $i));
579
580	// Use value of property id as appropriate
581	// offset: 60 + 8 * $i; size: 4; offset from beginning of section (48)
582	$offset = self::getInt4d($this->documentSummaryInformation, ($secOffset + 12) + (8 * $i));
583
584	$type = self::getInt4d($this->documentSummaryInformation, $secOffset + $offset);
585
586	// initialize property value
587	$value = null;
588
589	// extract property value based on property type
590	switch ($type) {
591	case 0x02: // 2 byte signed integer
592	$value = self::getUInt2d($this->documentSummaryInformation, $secOffset + 4 + $offset);
593
594	break;
595	case 0x03: // 4 byte signed integer
596	$value = self::getInt4d($this->documentSummaryInformation, $secOffset + 4 + $offset);
597
598	break;
599	case 0x0B: // Boolean
600	$value = self::getUInt2d($this->documentSummaryInformation, $secOffset + 4 + $offset);
601	$value = ($value == 0 ? false : true);
602
603	break;
604	case 0x13: // 4 byte unsigned integer
605	// not needed yet, fix later if necessary
606	break;
607	case 0x1E: // null-terminated string prepended by dword string length
608	$byteLength = self::getInt4d($this->documentSummaryInformation, $secOffset + 4 + $offset);
609	$value = (string) substr($this->documentSummaryInformation, $secOffset + 8 + $offset, $byteLength);
610	$value = StringHelper::convertEncoding($value, 'UTF-8', $codePage);
611	$value = rtrim($value);
612
613	break;
614	case 0x40: // Filetime (64-bit value representing the number of 100-nanosecond intervals since January 1, 1601)
615	// PHP-Time
616	$value = OLE::OLE2LocalDate((string) substr($this->documentSummaryInformation, $secOffset + 4 + $offset, 8));
617
618	break;
619	case 0x47: // Clipboard format
620	// not needed yet, fix later if necessary
621	break;
622	}
623
624	switch ($id) {
625	case 0x01: // Code Page
626	$codePage = CodePage::numberToName((int) $value);
627
628	break;
629	case 0x02: // Category
630	$this->spreadsheet->getProperties()->setCategory("$value");
631
632	break;
633	case 0x03: // Presentation Target
634	// Not supported by PhpSpreadsheet
635	break;
636	case 0x04: // Bytes
637	// Not supported by PhpSpreadsheet
638	break;
639	case 0x05: // Lines
640	// Not supported by PhpSpreadsheet
641	break;
642	case 0x06: // Paragraphs
643	// Not supported by PhpSpreadsheet
644	break;
645	case 0x07: // Slides
646	// Not supported by PhpSpreadsheet
647	break;
648	case 0x08: // Notes
649	// Not supported by PhpSpreadsheet
650	break;
651	case 0x09: // Hidden Slides
652	// Not supported by PhpSpreadsheet
653	break;
654	case 0x0A: // MM Clips
655	// Not supported by PhpSpreadsheet
656	break;
657	case 0x0B: // Scale Crop
658	// Not supported by PhpSpreadsheet
659	break;
660	case 0x0C: // Heading Pairs
661	// Not supported by PhpSpreadsheet
662	break;
663	case 0x0D: // Titles of Parts
664	// Not supported by PhpSpreadsheet
665	break;
666	case 0x0E: // Manager
667	$this->spreadsheet->getProperties()->setManager("$value");
668
669	break;
670	case 0x0F: // Company
671	$this->spreadsheet->getProperties()->setCompany("$value");
672
673	break;
674	case 0x10: // Links up-to-date
675	// Not supported by PhpSpreadsheet
676	break;
677	}
678	}
679	}
680
681	/**
682	* Reads a general type of BIFF record. Does nothing except for moving stream pointer forward to next record.
683	*/
684	protected function readDefault(): void
685	{
686	$length = self::getUInt2d($this->data, $this->pos + 2);
687
688	// move stream pointer to next record
689	$this->pos += 4 + $length;
690	}
691
692	/**
693	* The NOTE record specifies a comment associated with a particular cell. In Excel 95 (BIFF7) and earlier versions,
694	* this record stores a note (cell note). This feature was significantly enhanced in Excel 97.
695	*/
696	protected function readNote(): void
697	{
698	$length = self::getUInt2d($this->data, $this->pos + 2);
699	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
700
701	// move stream pointer to next record
702	$this->pos += 4 + $length;
703
704	if ($this->readDataOnly) {
705	return;
706	}
707
708	$cellAddress = Xls\Biff8::readBIFF8CellAddress(substr($recordData, 0, 4));
709	if ($this->version == self::XLS_BIFF8) {
710	$noteObjID = self::getUInt2d($recordData, 6);
711	$noteAuthor = self::readUnicodeStringLong((string) substr($recordData, 8));
712	$noteAuthor = $noteAuthor['value'];
713	$this->cellNotes[$noteObjID] = [
714	'cellRef' => $cellAddress,
715	'objectID' => $noteObjID,
716	'author' => $noteAuthor,
717	];
718	} else {
719	$extension = false;
720	if ($cellAddress === '$B$' . AddressRange::MAX_ROW_XLS) {
721	// If the address row is -1 and the column is 0, (which translates as $B$65536) then this is a continuation
722	// note from the previous cell annotation. We're not yet handling this, so annotations longer than the
723	// max 2048 bytes will probably throw a wobbly.
724	//$row = self::getUInt2d($recordData, 0);
725	$extension = true;
726	$arrayKeys = array_keys($this->phpSheet->getComments());
727	$cellAddress = array_pop($arrayKeys);
728	}
729
730	$cellAddress = str_replace('$', '', (string) $cellAddress);
731	//$noteLength = self::getUInt2d($recordData, 4);
732	$noteText = trim((string) substr($recordData, 6));
733
734	if ($extension) {
735	// Concatenate this extension with the currently set comment for the cell
736	$comment = $this->phpSheet->getComment($cellAddress);
737	$commentText = $comment->getText()->getPlainText();
738	$comment->setText($this->parseRichText($commentText . $noteText));
739	} else {
740	// Set comment for the cell
741	$this->phpSheet->getComment($cellAddress)->setText($this->parseRichText($noteText));
742	// ->setAuthor($author)
743	}
744	}
745	}
746
747	/**
748	* The TEXT Object record contains the text associated with a cell annotation.
749	*/
750	protected function readTextObject(): void
751	{
752	$length = self::getUInt2d($this->data, $this->pos + 2);
753	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
754
755	// move stream pointer to next record
756	$this->pos += 4 + $length;
757
758	if ($this->readDataOnly) {
759	return;
760	}
761
762	// recordData consists of an array of subrecords looking like this:
763	// grbit: 2 bytes; Option Flags
764	// rot: 2 bytes; rotation
765	// cchText: 2 bytes; length of the text (in the first continue record)
766	// cbRuns: 2 bytes; length of the formatting (in the second continue record)
767	// followed by the continuation records containing the actual text and formatting
768	$grbitOpts = self::getUInt2d($recordData, 0);
769	$rot = self::getUInt2d($recordData, 2);
770	//$cchText = self::getUInt2d($recordData, 10);
771	$cbRuns = self::getUInt2d($recordData, 12);
772	$text = $this->getSplicedRecordData();
773
774	/** @var int[] */
775	$tempSplice = $text['spliceOffsets'];
776	/** @var int */
777	$temp = $tempSplice[0];
778	/** @var int */
779	$temp1 = $tempSplice[1];
780	$textByte = $temp1 - $temp - 1;
781	/** @var string */
782	$textRecordData = $text['recordData'];
783	$textStr = (string) substr($textRecordData, $temp + 1, $textByte);
784	// get 1 byte
785	$is16Bit = ord($textRecordData[0]);
786	// it is possible to use a compressed format,
787	// which omits the high bytes of all characters, if they are all zero
788	if (($is16Bit & 0x01) === 0) {
789	$textStr = StringHelper::ConvertEncoding($textStr, 'UTF-8', 'ISO-8859-1');
790	} else {
791	$textStr = $this->decodeCodepage($textStr);
792	}
793
794	$this->textObjects[$this->textObjRef] = [
795	'text' => $textStr,
796	'format' => (string) substr($textRecordData, $tempSplice[1], $cbRuns),
797	'alignment' => $grbitOpts,
798	'rotation' => $rot,
799	];
800	}
801
802	/**
803	* Read BOF.
804	*/
805	protected function readBof(): void
806	{
807	$length = self::getUInt2d($this->data, $this->pos + 2);
808	$recordData = (string) substr($this->data, $this->pos + 4, $length);
809
810	// move stream pointer to next record
811	$this->pos += 4 + $length;
812
813	// offset: 2; size: 2; type of the following data
814	$substreamType = self::getUInt2d($recordData, 2);
815
816	switch ($substreamType) {
817	case self::XLS_WORKBOOKGLOBALS:
818	$version = self::getUInt2d($recordData, 0);
819	if (($version != self::XLS_BIFF8) && ($version != self::XLS_BIFF7)) {
820	throw new Exception('Cannot read this Excel file. Version is too old.');
821	}
822	$this->version = $version;
823
824	break;
825	case self::XLS_WORKSHEET:
826	// do not use this version information for anything
827	// it is unreliable (OpenOffice doc, 5.8), use only version information from the global stream
828	break;
829	default:
830	// substream, e.g. chart
831	// just skip the entire substream
832	do {
833	$code = self::getUInt2d($this->data, $this->pos);
834	$this->readDefault();
835	} while ($code != self::XLS_TYPE_EOF && $this->pos < $this->dataSize);
836
837	break;
838	}
839	}
840
841	public function setEncryptionPassword(string $encryptionPassword): self
842	{
843	$this->encryptionPassword = $encryptionPassword;
844
845	return $this;
846	}
847
848	/**
849	* FILEPASS.
850	*
851	* This record is part of the File Protection Block. It
852	* contains information about the read/write password of the
853	* file. All record contents following this record will be
854	* encrypted.
855	*
856	* -- "OpenOffice.org's Documentation of the Microsoft
857	* Excel File Format"
858	*
859	* The decryption functions and objects used from here on in
860	* are based on the source of Spreadsheet-ParseExcel:
861	* https://metacpan.org/release/Spreadsheet-ParseExcel
862	*/
863	protected function readFilepass(): void
864	{
865	$length = self::getUInt2d($this->data, $this->pos + 2);
866
867	if ($length < 54) {
868	throw new Exception('Unexpected file pass record length');
869	}
870
871	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
872
873	// move stream pointer to next record
874	$this->pos += 4 + $length;
875
876	if (substr($recordData, 0, 2) !== "\x01\x00" \|\| substr($recordData, 4, 2) !== "\x01\x00") {
877	throw new Exception('Unsupported encryption algorithm');
878	}
879	if (!$this->verifyPassword($this->encryptionPassword, (string) substr($recordData, 6, 16), (string) substr($recordData, 22, 16), (string) substr($recordData, 38, 16), $this->md5Ctxt)) {
880	throw new Exception('Decryption password incorrect');
881	}
882
883	$this->encryption = self::MS_BIFF_CRYPTO_RC4;
884
885	// Decryption required from the record after next onwards
886	$this->encryptionStartPos = $this->pos + self::getUInt2d($this->data, $this->pos + 2);
887	}
888
889	/**
890	* Make an RC4 decryptor for the given block.
891	*
892	* @param int $block Block for which to create decrypto
893	* @param string $valContext MD5 context state
894	*/
895	private function makeKey(int $block, string $valContext): Xls\RC4
896	{
897	$pwarray = str_repeat("\0", 64);
898
899	for ($i = 0; $i < 5; ++$i) {
900	$pwarray[$i] = $valContext[$i];
901	}
902
903	$pwarray[5] = chr($block & 0xFF);
904	$pwarray[6] = chr(($block >> 8) & 0xFF);
905	$pwarray[7] = chr(($block >> 16) & 0xFF);
906	$pwarray[8] = chr(($block >> 24) & 0xFF);
907
908	$pwarray[9] = "\x80";
909	$pwarray[56] = "\x48";
910
911	$md5 = new Xls\MD5();
912	$md5->add($pwarray);
913
914	$s = $md5->getContext();
915
916	return new Xls\RC4($s);
917	}
918
919	/**
920	* Verify RC4 file password.
921	*
922	* @param string $password Password to check
923	* @param string $docid Document id
924	* @param string $salt_data Salt data
925	* @param string $hashedsalt_data Hashed salt data
926	* @param string $valContext Set to the MD5 context of the value
927	*
928	* @return bool Success
929	*/
930	private function verifyPassword(string $password, string $docid, string $salt_data, string $hashedsalt_data, string &$valContext): bool
931	{
932	$pwarray = str_repeat("\0", 64);
933
934	$iMax = strlen($password);
935	for ($i = 0; $i < $iMax; ++$i) {
936	$o = ord((string) substr($password, $i, 1));
937	$pwarray[2 * $i] = chr($o & 0xFF);
938	$pwarray[2 * $i + 1] = chr(($o >> 8) & 0xFF);
939	}
940	$pwarray[2 * $i] = chr(0x80);
941	$pwarray[56] = chr(($i << 4) & 0xFF);
942
943	$md5 = new Xls\MD5();
944	$md5->add($pwarray);
945
946	$mdContext1 = $md5->getContext();
947
948	$offset = 0;
949	$keyoffset = 0;
950	$tocopy = 5;
951
952	$md5->reset();
953
954	while ($offset != 16) {
955	if ((64 - $offset) < 5) {
956	$tocopy = 64 - $offset;
957	}
958	for ($i = 0; $i <= $tocopy; ++$i) {
959	$pwarray[$offset + $i] = $mdContext1[$keyoffset + $i];
960	}
961	$offset += $tocopy;
962
963	if ($offset == 64) {
964	$md5->add($pwarray);
965	$keyoffset = $tocopy;
966	$tocopy = 5 - $tocopy;
967	$offset = 0;
968
969	continue;
970	}
971
972	$keyoffset = 0;
973	$tocopy = 5;
974	for ($i = 0; $i < 16; ++$i) {
975	$pwarray[$offset + $i] = $docid[$i];
976	}
977	$offset += 16;
978	}
979
980	$pwarray[16] = "\x80";
981	for ($i = 0; $i < 47; ++$i) {
982	$pwarray[17 + $i] = "\0";
983	}
984	$pwarray[56] = "\x80";
985	$pwarray[57] = "\x0a";
986
987	$md5->add($pwarray);
988	$valContext = $md5->getContext();
989
990	$key = $this->makeKey(0, $valContext);
991
992	$salt = $key->RC4($salt_data);
993	$hashedsalt = $key->RC4($hashedsalt_data);
994
995	$salt .= "\x80" . str_repeat("\0", 47);
996	$salt[56] = "\x80";
997
998	$md5->reset();
999	$md5->add($salt);
1000	$mdContext2 = $md5->getContext();
1001
1002	return $mdContext2 == $hashedsalt;
1003	}
1004
1005	/**
1006	* CODEPAGE.
1007	*
1008	* This record stores the text encoding used to write byte
1009	* strings, stored as MS Windows code page identifier.
1010	*
1011	* -- "OpenOffice.org's Documentation of the Microsoft
1012	* Excel File Format"
1013	*/
1014	protected function readCodepage(): void
1015	{
1016	$length = self::getUInt2d($this->data, $this->pos + 2);
1017	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
1018
1019	// move stream pointer to next record
1020	$this->pos += 4 + $length;
1021
1022	// offset: 0; size: 2; code page identifier
1023	$codepage = self::getUInt2d($recordData, 0);
1024
1025	$this->codepage = CodePage::numberToName($codepage);
1026	}
1027
1028	/**
1029	* DATEMODE.
1030	*
1031	* This record specifies the base date for displaying date
1032	* values. All dates are stored as count of days past this
1033	* base date. In BIFF2-BIFF4 this record is part of the
1034	* Calculation Settings Block. In BIFF5-BIFF8 it is
1035	* stored in the Workbook Globals Substream.
1036	*
1037	* -- "OpenOffice.org's Documentation of the Microsoft
1038	* Excel File Format"
1039	*/
1040	protected function readDateMode(): void
1041	{
1042	$length = self::getUInt2d($this->data, $this->pos + 2);
1043	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
1044
1045	// move stream pointer to next record
1046	$this->pos += 4 + $length;
1047
1048	// offset: 0; size: 2; 0 = base 1900, 1 = base 1904
1049	Date::setExcelCalendar(Date::CALENDAR_WINDOWS_1900);
1050	$this->spreadsheet->setExcelCalendar(Date::CALENDAR_WINDOWS_1900);
1051	if (ord($recordData[0]) == 1) {
1052	Date::setExcelCalendar(Date::CALENDAR_MAC_1904);
1053	$this->spreadsheet->setExcelCalendar(Date::CALENDAR_MAC_1904);
1054	}
1055	}
1056
1057	/**
1058	* Read a FONT record.
1059	*/
1060	protected function readFont(): void
1061	{
1062	$length = self::getUInt2d($this->data, $this->pos + 2);
1063	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
1064
1065	// move stream pointer to next record
1066	$this->pos += 4 + $length;
1067
1068	if (!$this->readDataOnly) {
1069	$objFont = new Font();
1070
1071	// offset: 0; size: 2; height of the font (in twips = 1/20 of a point)
1072	$size = self::getUInt2d($recordData, 0);
1073	$objFont->setSize($size / 20);
1074
1075	// offset: 2; size: 2; option flags
1076	// bit: 0; mask 0x0001; bold (redundant in BIFF5-BIFF8)
1077	// bit: 1; mask 0x0002; italic
1078	$isItalic = (0x0002 & self::getUInt2d($recordData, 2)) >> 1;
1079	if ($isItalic) {
1080	$objFont->setItalic(true);
1081	}
1082
1083	// bit: 2; mask 0x0004; underlined (redundant in BIFF5-BIFF8)
1084	// bit: 3; mask 0x0008; strikethrough
1085	$isStrike = (0x0008 & self::getUInt2d($recordData, 2)) >> 3;
1086	if ($isStrike) {
1087	$objFont->setStrikethrough(true);
1088	}
1089
1090	// offset: 4; size: 2; colour index
1091	$colorIndex = self::getUInt2d($recordData, 4);
1092	$objFont->colorIndex = $colorIndex;
1093
1094	// offset: 6; size: 2; font weight
1095	$weight = self::getUInt2d($recordData, 6); // regular=400 bold=700
1096	if ($weight >= 550) {
1097	$objFont->setBold(true);
1098	}
1099
1100	// offset: 8; size: 2; escapement type
1101	$escapement = self::getUInt2d($recordData, 8);
1102	CellFont::escapement($objFont, $escapement);
1103
1104	// offset: 10; size: 1; underline type
1105	$underlineType = ord($recordData[10]);
1106	CellFont::underline($objFont, $underlineType);
1107
1108	// offset: 11; size: 1; font family
1109	// offset: 12; size: 1; character set
1110	// offset: 13; size: 1; not used
1111	// offset: 14; size: var; font name
1112	if ($this->version == self::XLS_BIFF8) {
1113	$string = self::readUnicodeStringShort((string) substr($recordData, 14));
1114	} else {
1115	$string = $this->readByteStringShort((string) substr($recordData, 14));
1116	}
1117	/** @var string[] $string */
1118	$objFont->setName($string['value']);
1119
1120	$this->objFonts[] = $objFont;
1121	}
1122	}
1123
1124	/**
1125	* FORMAT.
1126	*
1127	* This record contains information about a number format.
1128	* All FORMAT records occur together in a sequential list.
1129	*
1130	* In BIFF2-BIFF4 other records referencing a FORMAT record
1131	* contain a zero-based index into this list. From BIFF5 on
1132	* the FORMAT record contains the index itself that will be
1133	* used by other records.
1134	*
1135	* -- "OpenOffice.org's Documentation of the Microsoft
1136	* Excel File Format"
1137	*/
1138	protected function readFormat(): void
1139	{
1140	$length = self::getUInt2d($this->data, $this->pos + 2);
1141	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
1142
1143	// move stream pointer to next record
1144	$this->pos += 4 + $length;
1145
1146	if (!$this->readDataOnly) {
1147	$indexCode = self::getUInt2d($recordData, 0);
1148
1149	if ($this->version == self::XLS_BIFF8) {
1150	$string = self::readUnicodeStringLong((string) substr($recordData, 2));
1151	} else {
1152	// BIFF7
1153	$string = $this->readByteStringShort((string) substr($recordData, 2));
1154	}
1155
1156	$formatString = $string['value'];
1157	// Apache Open Office sets wrong case writing to xls - issue 2239
1158	if ($formatString === 'GENERAL') {
1159	$formatString = NumberFormat::FORMAT_GENERAL;
1160	}
1161	$this->formats[$indexCode] = $formatString;
1162	}
1163	}
1164
1165	/**
1166	* XF - Extended Format.
1167	*
1168	* This record contains formatting information for cells, rows, columns or styles.
1169	* According to https://support.microsoft.com/en-us/help/147732 there are always at least 15 cell style XF
1170	* and 1 cell XF.
1171	* Inspection of Excel files generated by MS Office Excel shows that XF records 0-14 are cell style XF
1172	* and XF record 15 is a cell XF
1173	* We only read the first cell style XF and skip the remaining cell style XF records
1174	* We read all cell XF records.
1175	*
1176	* -- "OpenOffice.org's Documentation of the Microsoft
1177	* Excel File Format"
1178	*/
1179	protected function readXf(): void
1180	{
1181	$length = self::getUInt2d($this->data, $this->pos + 2);
1182	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
1183
1184	// move stream pointer to next record
1185	$this->pos += 4 + $length;
1186
1187	$objStyle = new Style();
1188
1189	if (!$this->readDataOnly) {
1190	// offset: 0; size: 2; Index to FONT record
1191	if (self::getUInt2d($recordData, 0) < 4) {
1192	$fontIndex = self::getUInt2d($recordData, 0);
1193	} else {
1194	// this has to do with that index 4 is omitted in all BIFF versions for some strange reason
1195	// check the OpenOffice documentation of the FONT record
1196	$fontIndex = self::getUInt2d($recordData, 0) - 1;
1197	}
1198	if (isset($this->objFonts[$fontIndex])) {
1199	$objStyle->setFont($this->objFonts[$fontIndex]);
1200	}
1201
1202	// offset: 2; size: 2; Index to FORMAT record
1203	$numberFormatIndex = self::getUInt2d($recordData, 2);
1204	if (isset($this->formats[$numberFormatIndex])) {
1205	// then we have user-defined format code
1206	$numberFormat = ['formatCode' => $this->formats[$numberFormatIndex]];
1207	} elseif (($code = NumberFormat::builtInFormatCode($numberFormatIndex)) !== '') {
1208	// then we have built-in format code
1209	$numberFormat = ['formatCode' => $code];
1210	} else {
1211	// we set the general format code
1212	$numberFormat = ['formatCode' => NumberFormat::FORMAT_GENERAL];
1213	}
1214	/** @var string[] $numberFormat */
1215	$objStyle->getNumberFormat()
1216	->setFormatCode($numberFormat['formatCode']);
1217
1218	// offset: 4; size: 2; XF type, cell protection, and parent style XF
1219	// bit 2-0; mask 0x0007; XF_TYPE_PROT
1220	$xfTypeProt = self::getUInt2d($recordData, 4);
1221	// bit 0; mask 0x01; 1 = cell is locked
1222	$isLocked = (0x01 & $xfTypeProt) >> 0;
1223	$objStyle->getProtection()->setLocked($isLocked ? Protection::PROTECTION_INHERIT : Protection::PROTECTION_UNPROTECTED);
1224
1225	// bit 1; mask 0x02; 1 = Formula is hidden
1226	$isHidden = (0x02 & $xfTypeProt) >> 1;
1227	$objStyle->getProtection()->setHidden($isHidden ? Protection::PROTECTION_PROTECTED : Protection::PROTECTION_UNPROTECTED);
1228
1229	// bit 2; mask 0x04; 0 = Cell XF, 1 = Cell Style XF
1230	$isCellStyleXf = (0x04 & $xfTypeProt) >> 2;
1231
1232	// offset: 6; size: 1; Alignment and text break
1233	// bit 2-0, mask 0x07; horizontal alignment
1234	$horAlign = (0x07 & ord($recordData[6])) >> 0;
1235	Xls\Style\CellAlignment::horizontal($objStyle->getAlignment(), $horAlign);
1236
1237	// bit 3, mask 0x08; wrap text
1238	$wrapText = (0x08 & ord($recordData[6])) >> 3;
1239	Xls\Style\CellAlignment::wrap($objStyle->getAlignment(), $wrapText);
1240
1241	// bit 6-4, mask 0x70; vertical alignment
1242	$vertAlign = (0x70 & ord($recordData[6])) >> 4;
1243	Xls\Style\CellAlignment::vertical($objStyle->getAlignment(), $vertAlign);
1244
1245	if ($this->version == self::XLS_BIFF8) {
1246	// offset: 7; size: 1; XF_ROTATION: Text rotation angle
1247	$angle = ord($recordData[7]);
1248	$rotation = 0;
1249	if ($angle <= 90) {
1250	$rotation = $angle;
1251	} elseif ($angle <= 180) {
1252	$rotation = 90 - $angle;
1253	} elseif ($angle == Alignment::TEXTROTATION_STACK_EXCEL) {
1254	$rotation = Alignment::TEXTROTATION_STACK_PHPSPREADSHEET;
1255	}
1256	$objStyle->getAlignment()->setTextRotation($rotation);
1257
1258	// offset: 8; size: 1; Indentation, shrink to cell size, and text direction
1259	// bit: 3-0; mask: 0x0F; indent level
1260	$indent = (0x0F & ord($recordData[8])) >> 0;
1261	$objStyle->getAlignment()->setIndent($indent);
1262
1263	// bit: 4; mask: 0x10; 1 = shrink content to fit into cell
1264	$shrinkToFit = (0x10 & ord($recordData[8])) >> 4;
1265	switch ($shrinkToFit) {
1266	case 0:
1267	$objStyle->getAlignment()->setShrinkToFit(false);
1268
1269	break;
1270	case 1:
1271	$objStyle->getAlignment()->setShrinkToFit(true);
1272
1273	break;
1274	}
1275	$readOrder = (0xC0 & ord($recordData[8])) >> 6;
1276	$objStyle->getAlignment()->setReadOrder($readOrder);
1277
1278	// offset: 9; size: 1; Flags used for attribute groups
1279
1280	// offset: 10; size: 4; Cell border lines and background area
1281	// bit: 3-0; mask: 0x0000000F; left style
1282	if ($bordersLeftStyle = Xls\Style\Border::lookup((0x0000000F & self::getInt4d($recordData, 10)) >> 0)) {
1283	$objStyle->getBorders()->getLeft()->setBorderStyle($bordersLeftStyle);
1284	}
1285	// bit: 7-4; mask: 0x000000F0; right style
1286	if ($bordersRightStyle = Xls\Style\Border::lookup((0x000000F0 & self::getInt4d($recordData, 10)) >> 4)) {
1287	$objStyle->getBorders()->getRight()->setBorderStyle($bordersRightStyle);
1288	}
1289	// bit: 11-8; mask: 0x00000F00; top style
1290	if ($bordersTopStyle = Xls\Style\Border::lookup((0x00000F00 & self::getInt4d($recordData, 10)) >> 8)) {
1291	$objStyle->getBorders()->getTop()->setBorderStyle($bordersTopStyle);
1292	}
1293	// bit: 15-12; mask: 0x0000F000; bottom style
1294	if ($bordersBottomStyle = Xls\Style\Border::lookup((0x0000F000 & self::getInt4d($recordData, 10)) >> 12)) {
1295	$objStyle->getBorders()->getBottom()->setBorderStyle($bordersBottomStyle);
1296	}
1297	// bit: 22-16; mask: 0x007F0000; left color
1298	$objStyle->getBorders()->getLeft()->colorIndex = (0x007F0000 & self::getInt4d($recordData, 10)) >> 16;
1299
1300	// bit: 29-23; mask: 0x3F800000; right color
1301	$objStyle->getBorders()->getRight()->colorIndex = (0x3F800000 & self::getInt4d($recordData, 10)) >> 23;
1302
1303	// bit: 30; mask: 0x40000000; 1 = diagonal line from top left to right bottom
1304	$diagonalDown = (0x40000000 & self::getInt4d($recordData, 10)) >> 30 ? true : false;
1305
1306	// bit: 31; mask: 0x800000; 1 = diagonal line from bottom left to top right
1307	$diagonalUp = (self::HIGH_ORDER_BIT & self::getInt4d($recordData, 10)) >> 31 ? true : false;
1308
1309	if ($diagonalUp === false) {
1310	if ($diagonalDown === false) {
1311	$objStyle->getBorders()->setDiagonalDirection(Borders::DIAGONAL_NONE);
1312	} else {
1313	$objStyle->getBorders()->setDiagonalDirection(Borders::DIAGONAL_DOWN);
1314	}
1315	} elseif ($diagonalDown === false) {
1316	$objStyle->getBorders()->setDiagonalDirection(Borders::DIAGONAL_UP);
1317	} else {
1318	$objStyle->getBorders()->setDiagonalDirection(Borders::DIAGONAL_BOTH);
1319	}
1320
1321	// offset: 14; size: 4;
1322	// bit: 6-0; mask: 0x0000007F; top color
1323	$objStyle->getBorders()->getTop()->colorIndex = (0x0000007F & self::getInt4d($recordData, 14)) >> 0;
1324
1325	// bit: 13-7; mask: 0x00003F80; bottom color
1326	$objStyle->getBorders()->getBottom()->colorIndex = (0x00003F80 & self::getInt4d($recordData, 14)) >> 7;
1327
1328	// bit: 20-14; mask: 0x001FC000; diagonal color
1329	$objStyle->getBorders()->getDiagonal()->colorIndex = (0x001FC000 & self::getInt4d($recordData, 14)) >> 14;
1330
1331	// bit: 24-21; mask: 0x01E00000; diagonal style
1332	if ($bordersDiagonalStyle = Xls\Style\Border::lookup((0x01E00000 & self::getInt4d($recordData, 14)) >> 21)) {
1333	$objStyle->getBorders()->getDiagonal()->setBorderStyle($bordersDiagonalStyle);
1334	}
1335
1336	// bit: 31-26; mask: 0xFC000000 fill pattern
1337	if ($fillType = FillPattern::lookup((self::FC000000 & self::getInt4d($recordData, 14)) >> 26)) {
1338	$objStyle->getFill()->setFillType($fillType);
1339	}
1340	// offset: 18; size: 2; pattern and background colour
1341	// bit: 6-0; mask: 0x007F; color index for pattern color
1342	$objStyle->getFill()->startcolorIndex = (0x007F & self::getUInt2d($recordData, 18)) >> 0;
1343
1344	// bit: 13-7; mask: 0x3F80; color index for pattern background
1345	$objStyle->getFill()->endcolorIndex = (0x3F80 & self::getUInt2d($recordData, 18)) >> 7;
1346	} else {
1347	// BIFF5
1348
1349	// offset: 7; size: 1; Text orientation and flags
1350	$orientationAndFlags = ord($recordData[7]);
1351
1352	// bit: 1-0; mask: 0x03; XF_ORIENTATION: Text orientation
1353	$xfOrientation = (0x03 & $orientationAndFlags) >> 0;
1354	switch ($xfOrientation) {
1355	case 0:
1356	$objStyle->getAlignment()->setTextRotation(0);
1357
1358	break;
1359	case 1:
1360	$objStyle->getAlignment()->setTextRotation(Alignment::TEXTROTATION_STACK_PHPSPREADSHEET);
1361
1362	break;
1363	case 2:
1364	$objStyle->getAlignment()->setTextRotation(90);
1365
1366	break;
1367	case 3:
1368	$objStyle->getAlignment()->setTextRotation(-90);
1369
1370	break;
1371	}
1372
1373	// offset: 8; size: 4; cell border lines and background area
1374	$borderAndBackground = self::getInt4d($recordData, 8);
1375
1376	// bit: 6-0; mask: 0x0000007F; color index for pattern color
1377	$objStyle->getFill()->startcolorIndex = (0x0000007F & $borderAndBackground) >> 0;
1378
1379	// bit: 13-7; mask: 0x00003F80; color index for pattern background
1380	$objStyle->getFill()->endcolorIndex = (0x00003F80 & $borderAndBackground) >> 7;
1381
1382	// bit: 21-16; mask: 0x003F0000; fill pattern
1383	$objStyle->getFill()->setFillType(FillPattern::lookup((0x003F0000 & $borderAndBackground) >> 16));
1384
1385	// bit: 24-22; mask: 0x01C00000; bottom line style
1386	$objStyle->getBorders()->getBottom()->setBorderStyle(Xls\Style\Border::lookup((0x01C00000 & $borderAndBackground) >> 22));
1387
1388	// bit: 31-25; mask: 0xFE000000; bottom line color
1389	$objStyle->getBorders()->getBottom()->colorIndex = (self::FE000000 & $borderAndBackground) >> 25;
1390
1391	// offset: 12; size: 4; cell border lines
1392	$borderLines = self::getInt4d($recordData, 12);
1393
1394	// bit: 2-0; mask: 0x00000007; top line style
1395	$objStyle->getBorders()->getTop()->setBorderStyle(Xls\Style\Border::lookup((0x00000007 & $borderLines) >> 0));
1396
1397	// bit: 5-3; mask: 0x00000038; left line style
1398	$objStyle->getBorders()->getLeft()->setBorderStyle(Xls\Style\Border::lookup((0x00000038 & $borderLines) >> 3));
1399
1400	// bit: 8-6; mask: 0x000001C0; right line style
1401	$objStyle->getBorders()->getRight()->setBorderStyle(Xls\Style\Border::lookup((0x000001C0 & $borderLines) >> 6));
1402
1403	// bit: 15-9; mask: 0x0000FE00; top line color index
1404	$objStyle->getBorders()->getTop()->colorIndex = (0x0000FE00 & $borderLines) >> 9;
1405
1406	// bit: 22-16; mask: 0x007F0000; left line color index
1407	$objStyle->getBorders()->getLeft()->colorIndex = (0x007F0000 & $borderLines) >> 16;
1408
1409	// bit: 29-23; mask: 0x3F800000; right line color index
1410	$objStyle->getBorders()->getRight()->colorIndex = (0x3F800000 & $borderLines) >> 23;
1411	}
1412
1413	// add cellStyleXf or cellXf and update mapping
1414	if ($isCellStyleXf) {
1415	// we only read one style XF record which is always the first
1416	if ($this->xfIndex == 0) {
1417	$this->spreadsheet->addCellStyleXf($objStyle);
1418	$this->mapCellStyleXfIndex[$this->xfIndex] = 0;
1419	}
1420	} else {
1421	// we read all cell XF records
1422	$this->spreadsheet->addCellXf($objStyle);
1423	$this->mapCellXfIndex[$this->xfIndex] = count($this->spreadsheet->getCellXfCollection()) - 1;
1424	}
1425
1426	// update XF index for when we read next record
1427	++$this->xfIndex;
1428	}
1429	}
1430
1431	protected function readXfExt(): void
1432	{
1433	$length = self::getUInt2d($this->data, $this->pos + 2);
1434	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
1435
1436	// move stream pointer to next record
1437	$this->pos += 4 + $length;
1438
1439	if (!$this->readDataOnly) {
1440	// offset: 0; size: 2; 0x087D = repeated header
1441
1442	// offset: 2; size: 2
1443
1444	// offset: 4; size: 8; not used
1445
1446	// offset: 12; size: 2; record version
1447
1448	// offset: 14; size: 2; index to XF record which this record modifies
1449	$ixfe = self::getUInt2d($recordData, 14);
1450
1451	// offset: 16; size: 2; not used
1452
1453	// offset: 18; size: 2; number of extension properties that follow
1454	//$cexts = self::getUInt2d($recordData, 18);
1455
1456	// start reading the actual extension data
1457	$offset = 20;
1458	while ($offset < $length) {
1459	// extension type
1460	$extType = self::getUInt2d($recordData, $offset);
1461
1462	// extension length
1463	$cb = self::getUInt2d($recordData, $offset + 2);
1464
1465	// extension data
1466	$extData = (string) substr($recordData, $offset + 4, $cb);
1467
1468	switch ($extType) {
1469	case 4: // fill start color
1470	$xclfType = self::getUInt2d($extData, 0); // color type
1471	$xclrValue = (string) substr($extData, 4, 4); // color value (value based on color type)
1472
1473	if ($xclfType == 2) {
1474	$rgb = sprintf('%02X%02X%02X', ord($xclrValue[0]), ord($xclrValue[1]), ord($xclrValue[2]));
1475
1476	// modify the relevant style property
1477	if (isset($this->mapCellXfIndex[$ixfe])) {
1478	$fill = $this->spreadsheet->getCellXfByIndex($this->mapCellXfIndex[$ixfe])->getFill();
1479	$fill->getStartColor()->setRGB($rgb);
1480	$fill->startcolorIndex = null; // normal color index does not apply, discard
1481	}
1482	}
1483
1484	break;
1485	case 5: // fill end color
1486	$xclfType = self::getUInt2d($extData, 0); // color type
1487	$xclrValue = (string) substr($extData, 4, 4); // color value (value based on color type)
1488
1489	if ($xclfType == 2) {
1490	$rgb = sprintf('%02X%02X%02X', ord($xclrValue[0]), ord($xclrValue[1]), ord($xclrValue[2]));
1491
1492	// modify the relevant style property
1493	if (isset($this->mapCellXfIndex[$ixfe])) {
1494	$fill = $this->spreadsheet->getCellXfByIndex($this->mapCellXfIndex[$ixfe])->getFill();
1495	$fill->getEndColor()->setRGB($rgb);
1496	$fill->endcolorIndex = null; // normal color index does not apply, discard
1497	}
1498	}
1499
1500	break;
1501	case 7: // border color top
1502	$xclfType = self::getUInt2d($extData, 0); // color type
1503	$xclrValue = (string) substr($extData, 4, 4); // color value (value based on color type)
1504
1505	if ($xclfType == 2) {
1506	$rgb = sprintf('%02X%02X%02X', ord($xclrValue[0]), ord($xclrValue[1]), ord($xclrValue[2]));
1507
1508	// modify the relevant style property
1509	if (isset($this->mapCellXfIndex[$ixfe])) {
1510	$top = $this->spreadsheet->getCellXfByIndex($this->mapCellXfIndex[$ixfe])->getBorders()->getTop();
1511	$top->getColor()->setRGB($rgb);
1512	$top->colorIndex = null; // normal color index does not apply, discard
1513	}
1514	}
1515
1516	break;
1517	case 8: // border color bottom
1518	$xclfType = self::getUInt2d($extData, 0); // color type
1519	$xclrValue = (string) substr($extData, 4, 4); // color value (value based on color type)
1520
1521	if ($xclfType == 2) {
1522	$rgb = sprintf('%02X%02X%02X', ord($xclrValue[0]), ord($xclrValue[1]), ord($xclrValue[2]));
1523
1524	// modify the relevant style property
1525	if (isset($this->mapCellXfIndex[$ixfe])) {
1526	$bottom = $this->spreadsheet->getCellXfByIndex($this->mapCellXfIndex[$ixfe])->getBorders()->getBottom();
1527	$bottom->getColor()->setRGB($rgb);
1528	$bottom->colorIndex = null; // normal color index does not apply, discard
1529	}
1530	}
1531
1532	break;
1533	case 9: // border color left
1534	$xclfType = self::getUInt2d($extData, 0); // color type
1535	$xclrValue = (string) substr($extData, 4, 4); // color value (value based on color type)
1536
1537	if ($xclfType == 2) {
1538	$rgb = sprintf('%02X%02X%02X', ord($xclrValue[0]), ord($xclrValue[1]), ord($xclrValue[2]));
1539
1540	// modify the relevant style property
1541	if (isset($this->mapCellXfIndex[$ixfe])) {
1542	$left = $this->spreadsheet->getCellXfByIndex($this->mapCellXfIndex[$ixfe])->getBorders()->getLeft();
1543	$left->getColor()->setRGB($rgb);
1544	$left->colorIndex = null; // normal color index does not apply, discard
1545	}
1546	}
1547
1548	break;
1549	case 10: // border color right
1550	$xclfType = self::getUInt2d($extData, 0); // color type
1551	$xclrValue = (string) substr($extData, 4, 4); // color value (value based on color type)
1552
1553	if ($xclfType == 2) {
1554	$rgb = sprintf('%02X%02X%02X', ord($xclrValue[0]), ord($xclrValue[1]), ord($xclrValue[2]));
1555
1556	// modify the relevant style property
1557	if (isset($this->mapCellXfIndex[$ixfe])) {
1558	$right = $this->spreadsheet->getCellXfByIndex($this->mapCellXfIndex[$ixfe])->getBorders()->getRight();
1559	$right->getColor()->setRGB($rgb);
1560	$right->colorIndex = null; // normal color index does not apply, discard
1561	}
1562	}
1563
1564	break;
1565	case 11: // border color diagonal
1566	$xclfType = self::getUInt2d($extData, 0); // color type
1567	$xclrValue = (string) substr($extData, 4, 4); // color value (value based on color type)
1568
1569	if ($xclfType == 2) {
1570	$rgb = sprintf('%02X%02X%02X', ord($xclrValue[0]), ord($xclrValue[1]), ord($xclrValue[2]));
1571
1572	// modify the relevant style property
1573	if (isset($this->mapCellXfIndex[$ixfe])) {
1574	$diagonal = $this->spreadsheet->getCellXfByIndex($this->mapCellXfIndex[$ixfe])->getBorders()->getDiagonal();
1575	$diagonal->getColor()->setRGB($rgb);
1576	$diagonal->colorIndex = null; // normal color index does not apply, discard
1577	}
1578	}
1579
1580	break;
1581	case 13: // font color
1582	$xclfType = self::getUInt2d($extData, 0); // color type
1583	$xclrValue = (string) substr($extData, 4, 4); // color value (value based on color type)
1584
1585	if ($xclfType == 2) {
1586	$rgb = sprintf('%02X%02X%02X', ord($xclrValue[0]), ord($xclrValue[1]), ord($xclrValue[2]));
1587
1588	// modify the relevant style property
1589	if (isset($this->mapCellXfIndex[$ixfe])) {
1590	$font = $this->spreadsheet->getCellXfByIndex($this->mapCellXfIndex[$ixfe])->getFont();
1591	$font->getColor()->setRGB($rgb);
1592	$font->colorIndex = null; // normal color index does not apply, discard
1593	}
1594	}
1595
1596	break;
1597	}
1598
1599	$offset += $cb;
1600	}
1601	}
1602	}
1603
1604	/**
1605	* Read STYLE record.
1606	*/
1607	protected function readStyle(): void
1608	{
1609	$length = self::getUInt2d($this->data, $this->pos + 2);
1610	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
1611
1612	// move stream pointer to next record
1613	$this->pos += 4 + $length;
1614
1615	if (!$this->readDataOnly) {
1616	// offset: 0; size: 2; index to XF record and flag for built-in style
1617	$ixfe = self::getUInt2d($recordData, 0);
1618
1619	// bit: 11-0; mask 0x0FFF; index to XF record
1620	//$xfIndex = (0x0FFF & $ixfe) >> 0;
1621
1622	// bit: 15; mask 0x8000; 0 = user-defined style, 1 = built-in style
1623	$isBuiltIn = (bool) ((0x8000 & $ixfe) >> 15);
1624
1625	if ($isBuiltIn) {
1626	// offset: 2; size: 1; identifier for built-in style
1627	$builtInId = ord($recordData[2]);
1628
1629	switch ($builtInId) {
1630	case 0x00:
1631	// currently, we are not using this for anything
1632	break;
1633	default:
1634	break;
1635	}
1636	}
1637	// user-defined; not supported by PhpSpreadsheet
1638	}
1639	}
1640
1641	/**
1642	* Read PALETTE record.
1643	*/
1644	protected function readPalette(): void
1645	{
1646	$length = self::getUInt2d($this->data, $this->pos + 2);
1647	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
1648
1649	// move stream pointer to next record
1650	$this->pos += 4 + $length;
1651
1652	if (!$this->readDataOnly) {
1653	// offset: 0; size: 2; number of following colors
1654	$nm = self::getUInt2d($recordData, 0);
1655
1656	// list of RGB colors
1657	for ($i = 0; $i < $nm; ++$i) {
1658	$rgb = (string) substr($recordData, 2 + 4 * $i, 4);
1659	$this->palette[] = self::readRGB($rgb);
1660	}
1661	}
1662	}
1663
1664	/**
1665	* SHEET.
1666	*
1667	* This record is located in the Workbook Globals
1668	* Substream and represents a sheet inside the workbook.
1669	* One SHEET record is written for each sheet. It stores the
1670	* sheet name and a stream offset to the BOF record of the
1671	* respective Sheet Substream within the Workbook Stream.
1672	*
1673	* -- "OpenOffice.org's Documentation of the Microsoft
1674	* Excel File Format"
1675	*/
1676	protected function readSheet(): void
1677	{
1678	$length = self::getUInt2d($this->data, $this->pos + 2);
1679	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
1680
1681	// offset: 0; size: 4; absolute stream position of the BOF record of the sheet
1682	// NOTE: not encrypted
1683	$rec_offset = self::getInt4d($this->data, $this->pos + 4);
1684
1685	// move stream pointer to next record
1686	$this->pos += 4 + $length;
1687
1688	switch (ord($recordData[4])) {
1689	case 0x00:
1690	$sheetState = Worksheet::SHEETSTATE_VISIBLE;
1691	break;
1692	case 0x01:
1693	$sheetState = Worksheet::SHEETSTATE_HIDDEN;
1694	break;
1695	case 0x02:
1696	$sheetState = Worksheet::SHEETSTATE_VERYHIDDEN;
1697	break;
1698	default:
1699	$sheetState = Worksheet::SHEETSTATE_VISIBLE;
1700	break;
1701	}
1702
1703	// offset: 5; size: 1; sheet type
1704	$sheetType = ord($recordData[5]);
1705
1706	// offset: 6; size: var; sheet name
1707	$rec_name = null;
1708	if ($this->version == self::XLS_BIFF8) {
1709	$string = self::readUnicodeStringShort((string) substr($recordData, 6));
1710	$rec_name = $string['value'];
1711	} elseif ($this->version == self::XLS_BIFF7) {
1712	$string = $this->readByteStringShort((string) substr($recordData, 6));
1713	$rec_name = $string['value'];
1714	}
1715	/** @var string $rec_name */
1716	$this->sheets[] = [
1717	'name' => $rec_name,
1718	'offset' => $rec_offset,
1719	'sheetState' => $sheetState,
1720	'sheetType' => $sheetType,
1721	];
1722	}
1723
1724	/**
1725	* Read EXTERNALBOOK record.
1726	*/
1727	protected function readExternalBook(): void
1728	{
1729	$length = self::getUInt2d($this->data, $this->pos + 2);
1730	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
1731
1732	// move stream pointer to next record
1733	$this->pos += 4 + $length;
1734
1735	// offset within record data
1736	$offset = 0;
1737
1738	// there are 4 types of records
1739	if (strlen($recordData) > 4) {
1740	// external reference
1741	// offset: 0; size: 2; number of sheet names ($nm)
1742	$nm = self::getUInt2d($recordData, 0);
1743	$offset += 2;
1744
1745	// offset: 2; size: var; encoded URL without sheet name (Unicode string, 16-bit length)
1746	$encodedUrlString = self::readUnicodeStringLong((string) substr($recordData, 2));
1747	$offset += $encodedUrlString['size'];
1748
1749	// offset: var; size: var; list of $nm sheet names (Unicode strings, 16-bit length)
1750	$externalSheetNames = [];
1751	for ($i = 0; $i < $nm; ++$i) {
1752	$externalSheetNameString = self::readUnicodeStringLong((string) substr($recordData, $offset));
1753	$externalSheetNames[] = $externalSheetNameString['value'];
1754	$offset += $externalSheetNameString['size'];
1755	}
1756
1757	// store the record data
1758	$this->externalBooks[] = [
1759	'type' => 'external',
1760	'encodedUrl' => $encodedUrlString['value'],
1761	'externalSheetNames' => $externalSheetNames,
1762	];
1763	} elseif (substr($recordData, 2, 2) == pack('CC', 0x01, 0x04)) {
1764	// internal reference
1765	// offset: 0; size: 2; number of sheet in this document
1766	// offset: 2; size: 2; 0x01 0x04
1767	$this->externalBooks[] = [
1768	'type' => 'internal',
1769	];
1770	} elseif (substr($recordData, 0, 4) == pack('vCC', 0x0001, 0x01, 0x3A)) {
1771	// add-in function
1772	// offset: 0; size: 2; 0x0001
1773	$this->externalBooks[] = [
1774	'type' => 'addInFunction',
1775	];
1776	} elseif (substr($recordData, 0, 2) == pack('v', 0x0000)) {
1777	// DDE links, OLE links
1778	// offset: 0; size: 2; 0x0000
1779	// offset: 2; size: var; encoded source document name
1780	$this->externalBooks[] = [
1781	'type' => 'DDEorOLE',
1782	];
1783	}
1784	}
1785
1786	/**
1787	* Read EXTERNNAME record.
1788	*/
1789	protected function readExternName(): void
1790	{
1791	$length = self::getUInt2d($this->data, $this->pos + 2);
1792	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
1793
1794	// move stream pointer to next record
1795	$this->pos += 4 + $length;
1796
1797	// external sheet references provided for named cells
1798	if ($this->version == self::XLS_BIFF8) {
1799	// offset: 0; size: 2; options
1800	//$options = self::getUInt2d($recordData, 0);
1801
1802	// offset: 2; size: 2;
1803
1804	// offset: 4; size: 2; not used
1805
1806	// offset: 6; size: var
1807	$nameString = self::readUnicodeStringShort((string) substr($recordData, 6));
1808
1809	// offset: var; size: var; formula data
1810	$offset = 6 + $nameString['size'];
1811	$formula = $this->getFormulaFromStructure((string) substr($recordData, $offset));
1812
1813	$this->externalNames[] = [
1814	'name' => $nameString['value'],
1815	'formula' => $formula,
1816	];
1817	}
1818	}
1819
1820	/**
1821	* Read EXTERNSHEET record.
1822	*/
1823	protected function readExternSheet(): void
1824	{
1825	$length = self::getUInt2d($this->data, $this->pos + 2);
1826	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
1827
1828	// move stream pointer to next record
1829	$this->pos += 4 + $length;
1830
1831	// external sheet references provided for named cells
1832	if ($this->version == self::XLS_BIFF8) {
1833	// offset: 0; size: 2; number of following ref structures
1834	$nm = self::getUInt2d($recordData, 0);
1835	for ($i = 0; $i < $nm; ++$i) {
1836	$this->ref[] = [
1837	// offset: 2 + 6 * $i; index to EXTERNALBOOK record
1838	'externalBookIndex' => self::getUInt2d($recordData, 2 + 6 * $i),
1839	// offset: 4 + 6 * $i; index to first sheet in EXTERNALBOOK record
1840	'firstSheetIndex' => self::getUInt2d($recordData, 4 + 6 * $i),
1841	// offset: 6 + 6 * $i; index to last sheet in EXTERNALBOOK record
1842	'lastSheetIndex' => self::getUInt2d($recordData, 6 + 6 * $i),
1843	];
1844	}
1845	}
1846	}
1847
1848	/**
1849	* DEFINEDNAME.
1850	*
1851	* This record is part of a Link Table. It contains the name
1852	* and the token array of an internal defined name. Token
1853	* arrays of defined names contain tokens with aberrant
1854	* token classes.
1855	*
1856	* -- "OpenOffice.org's Documentation of the Microsoft
1857	* Excel File Format"
1858	*/
1859	protected function readDefinedName(): void
1860	{
1861	$length = self::getUInt2d($this->data, $this->pos + 2);
1862	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
1863
1864	// move stream pointer to next record
1865	$this->pos += 4 + $length;
1866
1867	if ($this->version == self::XLS_BIFF8) {
1868	// retrieves named cells
1869
1870	// offset: 0; size: 2; option flags
1871	$opts = self::getUInt2d($recordData, 0);
1872
1873	// bit: 5; mask: 0x0020; 0 = user-defined name, 1 = built-in-name
1874	$isBuiltInName = (0x0020 & $opts) >> 5;
1875
1876	// offset: 2; size: 1; keyboard shortcut
1877
1878	// offset: 3; size: 1; length of the name (character count)
1879	$nlen = ord($recordData[3]);
1880
1881	// offset: 4; size: 2; size of the formula data (it can happen that this is zero)
1882	// note: there can also be additional data, this is not included in $flen
1883	$flen = self::getUInt2d($recordData, 4);
1884
1885	// offset: 8; size: 2; 0=Global name, otherwise index to sheet (1-based)
1886	$scope = self::getUInt2d($recordData, 8);
1887
1888	// offset: 14; size: var; Name (Unicode string without length field)
1889	$string = self::readUnicodeString((string) substr($recordData, 14), $nlen);
1890
1891	// offset: var; size: $flen; formula data
1892	$offset = 14 + $string['size'];
1893	$formulaStructure = pack('v', $flen) . substr($recordData, $offset);
1894
1895	try {
1896	$formula = $this->getFormulaFromStructure($formulaStructure);
1897	} catch (PhpSpreadsheetException $exception) {
1898	$formula = '';
1899	$isBuiltInName = 0;
1900	}
1901
1902	$this->definedname[] = [
1903	'isBuiltInName' => $isBuiltInName,
1904	'name' => $string['value'],
1905	'formula' => $formula,
1906	'scope' => $scope,
1907	];
1908	}
1909	}
1910
1911	/**
1912	* Read MSODRAWINGGROUP record.
1913	*/
1914	protected function readMsoDrawingGroup(): void
1915	{
1916	//$length = self::getUInt2d($this->data, $this->pos + 2);
1917
1918	// get spliced record data
1919	$splicedRecordData = $this->getSplicedRecordData();
1920	/** @var string */
1921	$recordData = $splicedRecordData['recordData'];
1922
1923	$this->drawingGroupData .= $recordData;
1924	}
1925
1926	/**
1927	* SST - Shared String Table.
1928	*
1929	* This record contains a list of all strings used anywhere
1930	* in the workbook. Each string occurs only once. The
1931	* workbook uses indexes into the list to reference the
1932	* strings.
1933	*
1934	* -- "OpenOffice.org's Documentation of the Microsoft
1935	* Excel File Format"
1936	*/
1937	protected function readSst(): void
1938	{
1939	// offset within (spliced) record data
1940	$pos = 0;
1941
1942	// Limit global SST position, further control for bad SST Length in BIFF8 data
1943	$limitposSST = 0;
1944
1945	// get spliced record data
1946	$splicedRecordData = $this->getSplicedRecordData();
1947
1948	$recordData = $splicedRecordData['recordData'];
1949	/** @var mixed[] */
1950	$spliceOffsets = $splicedRecordData['spliceOffsets'];
1951
1952	// offset: 0; size: 4; total number of strings in the workbook
1953	$pos += 4;
1954
1955	// offset: 4; size: 4; number of following strings ($nm)
1956	/** @var string $recordData */
1957	$nm = self::getInt4d($recordData, 4);
1958	$pos += 4;
1959
1960	// look up limit position (last splice offset where pos fits)
1961	foreach ($spliceOffsets as $spliceOffset) {
1962	// it can happen that the string is empty, therefore we need
1963	// <= and not just <
1964	if ($pos <= $spliceOffset) {
1965	$limitposSST = $spliceOffset;
1966	}
1967	}
1968
1969	// loop through the Unicode strings (16-bit length)
1970	for ($i = 0; $i < $nm && $pos < $limitposSST; ++$i) {
1971	// number of characters in the Unicode string
1972	/** @var int $pos */
1973	$numChars = self::getUInt2d($recordData, $pos);
1974	/** @var int $pos */
1975	$pos += 2;
1976
1977	// option flags
1978	/** @var string $recordData */
1979	$optionFlags = ord($recordData[$pos]);
1980	++$pos;
1981
1982	// bit: 0; mask: 0x01; 0 = compressed; 1 = uncompressed
1983	$isCompressed = (($optionFlags & 0x01) == 0);
1984
1985	// bit: 2; mask: 0x02; 0 = ordinary; 1 = Asian phonetic
1986	$hasAsian = (($optionFlags & 0x04) != 0);
1987
1988	// bit: 3; mask: 0x03; 0 = ordinary; 1 = Rich-Text
1989	$hasRichText = (($optionFlags & 0x08) != 0);
1990
1991	$formattingRuns = 0;
1992	if ($hasRichText) {
1993	// number of Rich-Text formatting runs
1994	$formattingRuns = self::getUInt2d($recordData, $pos);
1995	$pos += 2;
1996	}
1997
1998	$extendedRunLength = 0;
1999	if ($hasAsian) {
2000	// size of Asian phonetic setting
2001	$extendedRunLength = self::getInt4d($recordData, $pos);
2002	$pos += 4;
2003	}
2004
2005	// expected byte length of character array if not split
2006	$len = ($isCompressed) ? $numChars : $numChars * 2;
2007
2008	// look up limit position - find the first splice offset at or beyond current pos
2009	$limitpos = null;
2010	foreach ($spliceOffsets as $spliceOffset) {
2011	// it can happen that the string is empty, therefore we need
2012	// <= and not just <
2013	if ($pos <= $spliceOffset) {
2014	$limitpos = $spliceOffset;
2015
2016	break;
2017	}
2018	}
2019
2020	/** @var int $limitpos */
2021	if ($pos + $len <= $limitpos) {
2022	// character array is not split between records
2023
2024	$retstr = (string) substr($recordData, $pos, $len);
2025	$pos += $len;
2026	} else {
2027	// character array is split between records
2028
2029	// first part of character array
2030	$retstr = (string) substr($recordData, $pos, $limitpos - $pos);
2031
2032	$bytesRead = $limitpos - $pos;
2033
2034	// remaining characters in Unicode string
2035	$charsLeft = $numChars - (($isCompressed) ? $bytesRead : ($bytesRead / 2));
2036
2037	$pos = $limitpos;
2038
2039	// keep reading the characters
2040	while ($charsLeft > 0) {
2041	// look up next limit position, in case the string spans more than one continue record
2042	foreach ($spliceOffsets as $spliceOffset) {
2043	if ($pos < $spliceOffset) {
2044	$limitpos = $spliceOffset;
2045
2046	break;
2047	}
2048	}
2049
2050	// repeated option flags
2051	// OpenOffice.org documentation 5.21
2052	/** @var int $pos */
2053	$option = ord($recordData[$pos]);
2054	++$pos;
2055
2056	/** @var int $limitpos */
2057	if ($isCompressed && ($option == 0)) {
2058	// 1st fragment compressed
2059	// this fragment compressed
2060	/** @var int */
2061	$len = min($charsLeft, $limitpos - $pos);
2062	$retstr .= substr($recordData, $pos, $len);
2063	$charsLeft -= $len;
2064	$isCompressed = true;
2065	} elseif (!$isCompressed && ($option != 0)) {
2066	// 1st fragment uncompressed
2067	// this fragment uncompressed
2068	/** @var int */
2069	$len = min($charsLeft * 2, $limitpos - $pos);
2070	$retstr .= substr($recordData, $pos, $len);
2071	$charsLeft -= $len / 2;
2072	$isCompressed = false;
2073	} elseif (!$isCompressed /&& ($option == 0)/) {
2074	// 1st fragment uncompressed
2075	// this fragment compressed
2076	$len = (int) min($charsLeft, $limitpos - $pos);
2077	// Pad each byte with a null byte to expand to UTF-16LE
2078	$retstr .= chunk_split((string) substr($recordData, $pos, $len), 1, "\x00");
2079	$charsLeft -= $len;
2080	$isCompressed = false;
2081	} else {
2082	// 1st fragment compressed
2083	// this fragment uncompressed
2084	// Pad existing compressed string bytes with null bytes
2085	$retstr = chunk_split($retstr, 1, "\x00");
2086	/** @var int */
2087	$len = min($charsLeft * 2, $limitpos - $pos);
2088	$retstr .= substr($recordData, $pos, $len);
2089	$charsLeft -= $len / 2;
2090	$isCompressed = false;
2091	}
2092
2093	$pos += $len;
2094	}
2095	}
2096
2097	// convert to UTF-8
2098	$retstr = self::encodeUTF16($retstr, $isCompressed);
2099
2100	// read additional Rich-Text information, if any
2101	$fmtRuns = [];
2102	if ($hasRichText) {
2103	// list of formatting runs
2104	for ($j = 0; $j < $formattingRuns; ++$j) {
2105	// first formatted character; zero-based
2106	/** @var int $pos */
2107	$charPos = self::getUInt2d($recordData, $pos + $j * 4);
2108
2109	// index to font record
2110	$fontIndex = self::getUInt2d($recordData, $pos + 2 + $j * 4);
2111
2112	$fmtRuns[] = [
2113	'charPos' => $charPos,
2114	'fontIndex' => $fontIndex,
2115	];
2116	}
2117	$pos += 4 * $formattingRuns;
2118	}
2119
2120	// read additional Asian phonetics information, if any
2121	if ($hasAsian) {
2122	// For Asian phonetic settings, we skip the extended string data
2123	$pos += $extendedRunLength;
2124	}
2125
2126	// store the shared sting
2127	$this->sst[] = [
2128	'value' => $retstr,
2129	'fmtRuns' => $fmtRuns,
2130	];
2131	}
2132
2133	// getSplicedRecordData() takes care of moving current position in data stream
2134	}
2135
2136	/**
2137	* Read PRINTGRIDLINES record.
2138	*/
2139	protected function readPrintGridlines(): void
2140	{
2141	$length = self::getUInt2d($this->data, $this->pos + 2);
2142	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
2143
2144	// move stream pointer to next record
2145	$this->pos += 4 + $length;
2146
2147	if ($this->version == self::XLS_BIFF8 && !$this->readDataOnly) {
2148	// offset: 0; size: 2; 0 = do not print sheet grid lines; 1 = print sheet gridlines
2149	$printGridlines = (bool) self::getUInt2d($recordData, 0);
2150	$this->phpSheet->setPrintGridlines($printGridlines);
2151	}
2152	}
2153
2154	/**
2155	* Read DEFAULTROWHEIGHT record.
2156	*/
2157	protected function readDefaultRowHeight(): void
2158	{
2159	$length = self::getUInt2d($this->data, $this->pos + 2);
2160	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
2161
2162	// move stream pointer to next record
2163	$this->pos += 4 + $length;
2164
2165	// offset: 0; size: 2; option flags
2166	// offset: 2; size: 2; default height for unused rows, (twips 1/20 point)
2167	$height = self::getUInt2d($recordData, 2);
2168	$this->phpSheet->getDefaultRowDimension()->setRowHeight($height / 20);
2169	}
2170
2171	/**
2172	* Read SHEETPR record.
2173	*/
2174	protected function readSheetPr(): void
2175	{
2176	$length = self::getUInt2d($this->data, $this->pos + 2);
2177	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
2178
2179	// move stream pointer to next record
2180	$this->pos += 4 + $length;
2181
2182	// offset: 0; size: 2
2183
2184	// bit: 6; mask: 0x0040; 0 = outline buttons above outline group
2185	$isSummaryBelow = (0x0040 & self::getUInt2d($recordData, 0)) >> 6;
2186	$this->phpSheet->setShowSummaryBelow((bool) $isSummaryBelow);
2187
2188	// bit: 7; mask: 0x0080; 0 = outline buttons left of outline group
2189	$isSummaryRight = (0x0080 & self::getUInt2d($recordData, 0)) >> 7;
2190	$this->phpSheet->setShowSummaryRight((bool) $isSummaryRight);
2191
2192	// bit: 8; mask: 0x100; 0 = scale printout in percent, 1 = fit printout to number of pages
2193	// this corresponds to radio button setting in page setup dialog in Excel
2194	$this->isFitToPages = (bool) ((0x0100 & self::getUInt2d($recordData, 0)) >> 8);
2195	}
2196
2197	/**
2198	* Read HORIZONTALPAGEBREAKS record.
2199	*/
2200	protected function readHorizontalPageBreaks(): void
2201	{
2202	$length = self::getUInt2d($this->data, $this->pos + 2);
2203	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
2204
2205	// move stream pointer to next record
2206	$this->pos += 4 + $length;
2207
2208	if ($this->version == self::XLS_BIFF8 && !$this->readDataOnly) {
2209	// offset: 0; size: 2; number of the following row index structures
2210	$nm = self::getUInt2d($recordData, 0);
2211
2212	// offset: 2; size: 6 * $nm; list of $nm row index structures
2213	for ($i = 0; $i < $nm; ++$i) {
2214	$r = self::getUInt2d($recordData, 2 + 6 * $i);
2215	$cf = self::getUInt2d($recordData, 2 + 6 * $i + 2);
2216	//$cl = self::getUInt2d($recordData, 2 + 6 * $i + 4);
2217
2218	// not sure why two column indexes are necessary?
2219	$this->phpSheet->setBreak([$cf + 1, $r], Worksheet::BREAK_ROW);
2220	}
2221	}
2222	}
2223
2224	/**
2225	* Read VERTICALPAGEBREAKS record.
2226	*/
2227	protected function readVerticalPageBreaks(): void
2228	{
2229	$length = self::getUInt2d($this->data, $this->pos + 2);
2230	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
2231
2232	// move stream pointer to next record
2233	$this->pos += 4 + $length;
2234
2235	if ($this->version == self::XLS_BIFF8 && !$this->readDataOnly) {
2236	// offset: 0; size: 2; number of the following column index structures
2237	$nm = self::getUInt2d($recordData, 0);
2238
2239	// offset: 2; size: 6 * $nm; list of $nm row index structures
2240	for ($i = 0; $i < $nm; ++$i) {
2241	$c = self::getUInt2d($recordData, 2 + 6 * $i);
2242	$rf = self::getUInt2d($recordData, 2 + 6 * $i + 2);
2243	//$rl = self::getUInt2d($recordData, 2 + 6 * $i + 4);
2244
2245	// not sure why two row indexes are necessary?
2246	$this->phpSheet->setBreak([$c + 1, ($rf > 0) ? $rf : 1], Worksheet::BREAK_COLUMN);
2247	}
2248	}
2249	}
2250
2251	/**
2252	* Read HEADER record.
2253	*/
2254	protected function readHeader(): void
2255	{
2256	$length = self::getUInt2d($this->data, $this->pos + 2);
2257	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
2258
2259	// move stream pointer to next record
2260	$this->pos += 4 + $length;
2261
2262	if (!$this->readDataOnly) {
2263	// offset: 0; size: var
2264	// realized that $recordData can be empty even when record exists
2265	if ($recordData) {
2266	if ($this->version == self::XLS_BIFF8) {
2267	$string = self::readUnicodeStringLong($recordData);
2268	} else {
2269	$string = $this->readByteStringShort($recordData);
2270	}
2271
2272	/** @var string[] $string */
2273	$this->phpSheet
2274	->getHeaderFooter()
2275	->setOddHeader($string['value']);
2276	$this->phpSheet
2277	->getHeaderFooter()
2278	->setEvenHeader($string['value']);
2279	}
2280	}
2281	}
2282
2283	/**
2284	* Read FOOTER record.
2285	*/
2286	protected function readFooter(): void
2287	{
2288	$length = self::getUInt2d($this->data, $this->pos + 2);
2289	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
2290
2291	// move stream pointer to next record
2292	$this->pos += 4 + $length;
2293
2294	if (!$this->readDataOnly) {
2295	// offset: 0; size: var
2296	// realized that $recordData can be empty even when record exists
2297	if ($recordData) {
2298	if ($this->version == self::XLS_BIFF8) {
2299	$string = self::readUnicodeStringLong($recordData);
2300	} else {
2301	$string = $this->readByteStringShort($recordData);
2302	}
2303	/** @var string */
2304	$temp = $string['value'];
2305	$this->phpSheet
2306	->getHeaderFooter()
2307	->setOddFooter($temp);
2308	$this->phpSheet
2309	->getHeaderFooter()
2310	->setEvenFooter($temp);
2311	}
2312	}
2313	}
2314
2315	/**
2316	* Read HCENTER record.
2317	*/
2318	protected function readHcenter(): void
2319	{
2320	$length = self::getUInt2d($this->data, $this->pos + 2);
2321	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
2322
2323	// move stream pointer to next record
2324	$this->pos += 4 + $length;
2325
2326	if (!$this->readDataOnly) {
2327	// offset: 0; size: 2; 0 = print sheet left aligned, 1 = print sheet centered horizontally
2328	$isHorizontalCentered = (bool) self::getUInt2d($recordData, 0);
2329
2330	$this->phpSheet->getPageSetup()->setHorizontalCentered($isHorizontalCentered);
2331	}
2332	}
2333
2334	/**
2335	* Read VCENTER record.
2336	*/
2337	protected function readVcenter(): void
2338	{
2339	$length = self::getUInt2d($this->data, $this->pos + 2);
2340	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
2341
2342	// move stream pointer to next record
2343	$this->pos += 4 + $length;
2344
2345	if (!$this->readDataOnly) {
2346	// offset: 0; size: 2; 0 = print sheet aligned at top page border, 1 = print sheet vertically centered
2347	$isVerticalCentered = (bool) self::getUInt2d($recordData, 0);
2348
2349	$this->phpSheet->getPageSetup()->setVerticalCentered($isVerticalCentered);
2350	}
2351	}
2352
2353	/**
2354	* Read LEFTMARGIN record.
2355	*/
2356	protected function readLeftMargin(): void
2357	{
2358	$length = self::getUInt2d($this->data, $this->pos + 2);
2359	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
2360
2361	// move stream pointer to next record
2362	$this->pos += 4 + $length;
2363
2364	if (!$this->readDataOnly) {
2365	// offset: 0; size: 8
2366	$this->phpSheet->getPageMargins()->setLeft(self::extractNumber($recordData));
2367	}
2368	}
2369
2370	/**
2371	* Read RIGHTMARGIN record.
2372	*/
2373	protected function readRightMargin(): void
2374	{
2375	$length = self::getUInt2d($this->data, $this->pos + 2);
2376	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
2377
2378	// move stream pointer to next record
2379	$this->pos += 4 + $length;
2380
2381	if (!$this->readDataOnly) {
2382	// offset: 0; size: 8
2383	$this->phpSheet->getPageMargins()->setRight(self::extractNumber($recordData));
2384	}
2385	}
2386
2387	/**
2388	* Read TOPMARGIN record.
2389	*/
2390	protected function readTopMargin(): void
2391	{
2392	$length = self::getUInt2d($this->data, $this->pos + 2);
2393	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
2394
2395	// move stream pointer to next record
2396	$this->pos += 4 + $length;
2397
2398	if (!$this->readDataOnly) {
2399	// offset: 0; size: 8
2400	$this->phpSheet->getPageMargins()->setTop(self::extractNumber($recordData));
2401	}
2402	}
2403
2404	/**
2405	* Read BOTTOMMARGIN record.
2406	*/
2407	protected function readBottomMargin(): void
2408	{
2409	$length = self::getUInt2d($this->data, $this->pos + 2);
2410	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
2411
2412	// move stream pointer to next record
2413	$this->pos += 4 + $length;
2414
2415	if (!$this->readDataOnly) {
2416	// offset: 0; size: 8
2417	$this->phpSheet->getPageMargins()->setBottom(self::extractNumber($recordData));
2418	}
2419	}
2420
2421	/**
2422	* Read PAGESETUP record.
2423	*/
2424	protected function readPageSetup(): void
2425	{
2426	$length = self::getUInt2d($this->data, $this->pos + 2);
2427	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
2428
2429	// move stream pointer to next record
2430	$this->pos += 4 + $length;
2431
2432	if (!$this->readDataOnly) {
2433	// offset: 0; size: 2; paper size
2434	$paperSize = self::getUInt2d($recordData, 0);
2435
2436	// offset: 2; size: 2; scaling factor
2437	$scale = self::getUInt2d($recordData, 2);
2438
2439	// offset: 6; size: 2; fit worksheet width to this number of pages, 0 = use as many as needed
2440	$fitToWidth = self::getUInt2d($recordData, 6);
2441
2442	// offset: 8; size: 2; fit worksheet height to this number of pages, 0 = use as many as needed
2443	$fitToHeight = self::getUInt2d($recordData, 8);
2444
2445	// offset: 10; size: 2; option flags
2446
2447	// bit: 0; mask: 0x0001; 0=down then over, 1=over then down
2448	$isOverThenDown = (0x0001 & self::getUInt2d($recordData, 10));
2449
2450	// bit: 1; mask: 0x0002; 0=landscape, 1=portrait
2451	$isPortrait = (0x0002 & self::getUInt2d($recordData, 10)) >> 1;
2452
2453	// bit: 2; mask: 0x0004; 1= paper size, scaling factor, paper orient. not init
2454	// when this bit is set, do not use flags for those properties
2455	$isNotInit = (0x0004 & self::getUInt2d($recordData, 10)) >> 2;
2456
2457	if (!$isNotInit) {
2458	$this->phpSheet->getPageSetup()->setPaperSize($paperSize);
2459	$this->phpSheet->getPageSetup()->setPageOrder(((bool) $isOverThenDown) ? PageSetup::PAGEORDER_OVER_THEN_DOWN : PageSetup::PAGEORDER_DOWN_THEN_OVER);
2460	$this->phpSheet->getPageSetup()->setOrientation(((bool) $isPortrait) ? PageSetup::ORIENTATION_PORTRAIT : PageSetup::ORIENTATION_LANDSCAPE);
2461
2462	$this->phpSheet->getPageSetup()->setScale($scale, false);
2463	$this->phpSheet->getPageSetup()->setFitToPage((bool) $this->isFitToPages);
2464	$this->phpSheet->getPageSetup()->setFitToWidth($fitToWidth, false);
2465	$this->phpSheet->getPageSetup()->setFitToHeight($fitToHeight, false);
2466	}
2467
2468	// offset: 16; size: 8; header margin (IEEE 754 floating-point value)
2469	$marginHeader = self::extractNumber((string) substr($recordData, 16, 8));
2470	$this->phpSheet->getPageMargins()->setHeader($marginHeader);
2471
2472	// offset: 24; size: 8; footer margin (IEEE 754 floating-point value)
2473	$marginFooter = self::extractNumber((string) substr($recordData, 24, 8));
2474	$this->phpSheet->getPageMargins()->setFooter($marginFooter);
2475	}
2476	}
2477
2478	/**
2479	* PROTECT - Sheet protection (BIFF2 through BIFF8)
2480	* if this record is omitted, then it also means no sheet protection.
2481	*/
2482	protected function readProtect(): void
2483	{
2484	$length = self::getUInt2d($this->data, $this->pos + 2);
2485	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
2486
2487	// move stream pointer to next record
2488	$this->pos += 4 + $length;
2489
2490	if ($this->readDataOnly) {
2491	return;
2492	}
2493
2494	// offset: 0; size: 2;
2495
2496	// bit 0, mask 0x01; 1 = sheet is protected
2497	$bool = (0x01 & self::getUInt2d($recordData, 0)) >> 0;
2498	$this->phpSheet->getProtection()->setSheet((bool) $bool);
2499	}
2500
2501	/**
2502	* SCENPROTECT.
2503	*/
2504	protected function readScenProtect(): void
2505	{
2506	$length = self::getUInt2d($this->data, $this->pos + 2);
2507	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
2508
2509	// move stream pointer to next record
2510	$this->pos += 4 + $length;
2511
2512	if ($this->readDataOnly) {
2513	return;
2514	}
2515
2516	// offset: 0; size: 2;
2517
2518	// bit: 0, mask 0x01; 1 = scenarios are protected
2519	$bool = (0x01 & self::getUInt2d($recordData, 0)) >> 0;
2520
2521	$this->phpSheet->getProtection()->setScenarios((bool) $bool);
2522	}
2523
2524	/**
2525	* OBJECTPROTECT.
2526	*/
2527	protected function readObjectProtect(): void
2528	{
2529	$length = self::getUInt2d($this->data, $this->pos + 2);
2530	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
2531
2532	// move stream pointer to next record
2533	$this->pos += 4 + $length;
2534
2535	if ($this->readDataOnly) {
2536	return;
2537	}
2538
2539	// offset: 0; size: 2;
2540
2541	// bit: 0, mask 0x01; 1 = objects are protected
2542	$bool = (0x01 & self::getUInt2d($recordData, 0)) >> 0;
2543
2544	$this->phpSheet->getProtection()->setObjects((bool) $bool);
2545	}
2546
2547	/**
2548	* PASSWORD - Sheet protection (hashed) password (BIFF2 through BIFF8).
2549	*/
2550	protected function readPassword(): void
2551	{
2552	$length = self::getUInt2d($this->data, $this->pos + 2);
2553	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
2554
2555	// move stream pointer to next record
2556	$this->pos += 4 + $length;
2557
2558	if (!$this->readDataOnly) {
2559	// offset: 0; size: 2; 16-bit hash value of password
2560	$password = strtoupper(dechex(self::getUInt2d($recordData, 0))); // the hashed password
2561	$this->phpSheet->getProtection()->setPassword($password, true);
2562	}
2563	}
2564
2565	/**
2566	* Read DEFCOLWIDTH record.
2567	*/
2568	protected function readDefColWidth(): void
2569	{
2570	$length = self::getUInt2d($this->data, $this->pos + 2);
2571	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
2572
2573	// move stream pointer to next record
2574	$this->pos += 4 + $length;
2575
2576	// offset: 0; size: 2; default column width
2577	$width = self::getUInt2d($recordData, 0);
2578	if ($width != 8) {
2579	$this->phpSheet->getDefaultColumnDimension()->setWidth($width);
2580	}
2581	}
2582
2583	/**
2584	* Read COLINFO record.
2585	*/
2586	protected function readColInfo(): void
2587	{
2588	$length = self::getUInt2d($this->data, $this->pos + 2);
2589	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
2590
2591	// move stream pointer to next record
2592	$this->pos += 4 + $length;
2593
2594	if (!$this->readDataOnly) {
2595	// offset: 0; size: 2; index to first column in range
2596	$firstColumnIndex = self::getUInt2d($recordData, 0);
2597
2598	// offset: 2; size: 2; index to last column in range
2599	$lastColumnIndex = self::getUInt2d($recordData, 2);
2600
2601	// offset: 4; size: 2; width of the column in 1/256 of the width of the zero character
2602	$width = self::getUInt2d($recordData, 4);
2603
2604	// offset: 6; size: 2; index to XF record for default column formatting
2605	$xfIndex = self::getUInt2d($recordData, 6);
2606
2607	// offset: 8; size: 2; option flags
2608	// bit: 0; mask: 0x0001; 1= columns are hidden
2609	$isHidden = (0x0001 & self::getUInt2d($recordData, 8)) >> 0;
2610
2611	// bit: 10-8; mask: 0x0700; outline level of the columns (0 = no outline)
2612	$level = (0x0700 & self::getUInt2d($recordData, 8)) >> 8;
2613
2614	// bit: 12; mask: 0x1000; 1 = collapsed
2615	$isCollapsed = (bool) ((0x1000 & self::getUInt2d($recordData, 8)) >> 12);
2616
2617	// offset: 10; size: 2; not used
2618
2619	for ($i = $firstColumnIndex + 1; $i <= $lastColumnIndex + 1; ++$i) {
2620	if ($lastColumnIndex == AddressRange::MAX_COLUMN_INT_XLS - 1 \|\| $lastColumnIndex == AddressRange::MAX_COLUMN_INT) {
2621	$this->phpSheet->getDefaultColumnDimension()->setWidth($width / 256);
2622
2623	break;
2624	}
2625	$this->phpSheet->getColumnDimensionByColumn($i)->setWidth($width / 256);
2626	$this->phpSheet->getColumnDimensionByColumn($i)->setVisible(!$isHidden);
2627	$this->phpSheet->getColumnDimensionByColumn($i)->setOutlineLevel($level);
2628	$this->phpSheet->getColumnDimensionByColumn($i)->setCollapsed($isCollapsed);
2629	if (isset($this->mapCellXfIndex[$xfIndex])) {
2630	$this->phpSheet->getColumnDimensionByColumn($i)->setXfIndex($this->mapCellXfIndex[$xfIndex]);
2631	}
2632	}
2633	}
2634	}
2635
2636	/**
2637	* ROW.
2638	*
2639	* This record contains the properties of a single row in a
2640	* sheet. Rows and cells in a sheet are divided into blocks
2641	* of 32 rows.
2642	*
2643	* -- "OpenOffice.org's Documentation of the Microsoft
2644	* Excel File Format"
2645	*/
2646	protected function readRow(): void
2647	{
2648	$length = self::getUInt2d($this->data, $this->pos + 2);
2649	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
2650
2651	// move stream pointer to next record
2652	$this->pos += 4 + $length;
2653
2654	if (!$this->readDataOnly) {
2655	// offset: 0; size: 2; index of this row
2656	$r = self::getUInt2d($recordData, 0);
2657
2658	// offset: 2; size: 2; index to column of the first cell which is described by a cell record
2659
2660	// offset: 4; size: 2; index to column of the last cell which is described by a cell record, increased by 1
2661
2662	// offset: 6; size: 2;
2663
2664	// bit: 14-0; mask: 0x7FFF; height of the row, in twips = 1/20 of a point
2665	$height = (0x7FFF & self::getUInt2d($recordData, 6)) >> 0;
2666
2667	// bit: 15: mask: 0x8000; 0 = row has custom height; 1= row has default height
2668	$useDefaultHeight = (0x8000 & self::getUInt2d($recordData, 6)) >> 15;
2669
2670	if (!$useDefaultHeight) {
2671	if (
2672	$this->phpSheet->getDefaultRowDimension()->getRowHeight() > 0
2673	) {
2674	$this->phpSheet->getRowDimension($r + 1)
2675	->setCustomFormat(true, ($height === 255) ? -1 : ($height / 20));
2676	} else {
2677	$this->phpSheet->getRowDimension($r + 1)->setRowHeight($height / 20);
2678	}
2679	}
2680
2681	// offset: 8; size: 2; not used
2682
2683	// offset: 10; size: 2; not used in BIFF5-BIFF8
2684
2685	// offset: 12; size: 4; option flags and default row formatting
2686
2687	// bit: 2-0: mask: 0x00000007; outline level of the row
2688	$level = (0x00000007 & self::getInt4d($recordData, 12)) >> 0;
2689	$this->phpSheet->getRowDimension($r + 1)->setOutlineLevel($level);
2690
2691	// bit: 4; mask: 0x00000010; 1 = outline group start or ends here... and is collapsed
2692	$isCollapsed = (bool) ((0x00000010 & self::getInt4d($recordData, 12)) >> 4);
2693	$this->phpSheet->getRowDimension($r + 1)->setCollapsed($isCollapsed);
2694
2695	// bit: 5; mask: 0x00000020; 1 = row is hidden
2696	$isHidden = (0x00000020 & self::getInt4d($recordData, 12)) >> 5;
2697	$this->phpSheet->getRowDimension($r + 1)->setVisible(!$isHidden);
2698
2699	// bit: 7; mask: 0x00000080; 1 = row has explicit format
2700	$hasExplicitFormat = (0x00000080 & self::getInt4d($recordData, 12)) >> 7;
2701
2702	// bit: 27-16; mask: 0x0FFF0000; only applies when hasExplicitFormat = 1; index to XF record
2703	$xfIndex = (0x0FFF0000 & self::getInt4d($recordData, 12)) >> 16;
2704
2705	if ($hasExplicitFormat && isset($this->mapCellXfIndex[$xfIndex])) {
2706	$this->phpSheet->getRowDimension($r + 1)->setXfIndex($this->mapCellXfIndex[$xfIndex]);
2707	}
2708	}
2709	}
2710
2711	/**
2712	* Read RK record
2713	* This record represents a cell that contains an RK value
2714	* (encoded integer or floating-point value). If a
2715	* floating-point value cannot be encoded to an RK value,
2716	* a NUMBER record will be written. This record replaces the
2717	* record INTEGER written in BIFF2.
2718	*
2719	* -- "OpenOffice.org's Documentation of the Microsoft
2720	* Excel File Format"
2721	*/
2722	protected function readRk(): void
2723	{
2724	$length = self::getUInt2d($this->data, $this->pos + 2);
2725	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
2726
2727	// move stream pointer to next record
2728	$this->pos += 4 + $length;
2729
2730	// offset: 0; size: 2; index to row
2731	$row = self::getUInt2d($recordData, 0);
2732
2733	// offset: 2; size: 2; index to column
2734	$column = self::getUInt2d($recordData, 2);
2735	$columnString = Coordinate::stringFromColumnIndex($column + 1);
2736	$cellCoordinate = $columnString . ($row + 1);
2737
2738	// Read cell?
2739	if ($this->readFilter->readCell($columnString, $row + 1, $this->phpSheetTitle)) {
2740	// offset: 4; size: 2; index to XF record
2741	$xfIndex = self::getUInt2d($recordData, 4);
2742
2743	// offset: 6; size: 4; RK value
2744	$rknum = self::getInt4d($recordData, 6);
2745	$numValue = self::getIEEE754($rknum);
2746
2747	$cell = $this->phpSheet->getCell($cellCoordinate);
2748	if (!$this->readDataOnly && isset($this->mapCellXfIndex[$xfIndex])) {
2749	// add style information
2750	$cell->setXfIndexNoUpdate($this->mapCellXfIndex[$xfIndex]);
2751	}
2752
2753	// add cell
2754	$cell->setValueExplicit($numValue, DataType::TYPE_NUMERIC);
2755	}
2756	}
2757
2758	/**
2759	* Read LABELSST record
2760	* This record represents a cell that contains a string. It
2761	* replaces the LABEL record and RSTRING record used in
2762	* BIFF2-BIFF5.
2763	*
2764	* -- "OpenOffice.org's Documentation of the Microsoft
2765	* Excel File Format"
2766	*/
2767	protected function readLabelSst(): void
2768	{
2769	$length = self::getUInt2d($this->data, $this->pos + 2);
2770	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
2771
2772	// move stream pointer to next record
2773	$this->pos += 4 + $length;
2774
2775	// offset: 0; size: 2; index to row
2776	$row = self::getUInt2d($recordData, 0);
2777
2778	// offset: 2; size: 2; index to column
2779	$column = self::getUInt2d($recordData, 2);
2780	$columnString = Coordinate::stringFromColumnIndex($column + 1);
2781	$cellCoordinate = $columnString . ($row + 1);
2782
2783	// Read cell?
2784	if ($this->readFilter->readCell($columnString, $row + 1, $this->phpSheetTitle)) {
2785	// offset: 4; size: 2; index to XF record
2786	$xfIndex = self::getUInt2d($recordData, 4);
2787
2788	// offset: 6; size: 4; index to SST record
2789	$index = self::getInt4d($recordData, 6);
2790
2791	// cache SST entry locally to avoid repeated array lookups
2792	$sstValue = $this->sst[$index]['value'];
2793	$fmtRuns = $this->sst[$index]['fmtRuns'];
2794
2795	// add cell
2796	if ($fmtRuns && !$this->readDataOnly) {
2797	// then we should treat as rich text
2798	$richText = new RichText();
2799	$charPos = 0;
2800	$sstCount = count($fmtRuns);
2801	$sstValueLength = StringHelper::countCharacters($sstValue);
2802	$lastFontIndex = count($this->objFonts) - 1;
2803	for ($i = 0; $i <= $sstCount; ++$i) {
2804	/** @var mixed[][] $fmtRuns */
2805	if (isset($fmtRuns[$i])) {
2806	/** @var int[] */
2807	$temp = $fmtRuns[$i];
2808	$temp = $temp['charPos'];
2809	/** @var int $charPos */
2810	$text = StringHelper::substring($sstValue, $charPos, $temp - $charPos);
2811	$charPos = $temp;
2812	} else {
2813	$text = StringHelper::substring($sstValue, $charPos, $sstValueLength);
2814	}
2815
2816	if (StringHelper::countCharacters($text) > 0) {
2817	if ($i == 0) { // first text run, no style
2818	$richText->createText($text);
2819	} else {
2820	$textRun = $richText->createTextRun($text);
2821	/** @var int[][] $fmtRuns */
2822	if (isset($fmtRuns[$i - 1])) {
2823	if ($fmtRuns[$i - 1]['fontIndex'] < 4) {
2824	$fontIndex = $fmtRuns[$i - 1]['fontIndex'];
2825	} else {
2826	// this has to do with that index 4 is omitted in all BIFF versions for some stra nge reason
2827	// check the OpenOffice documentation of the FONT record
2828	/** @var int */
2829	$temp = $fmtRuns[$i - 1]['fontIndex'];
2830	$fontIndex = $temp - 1;
2831	}
2832	if ($fontIndex > $lastFontIndex) {
2833	$fontIndex = $lastFontIndex;
2834	}
2835	$textRun->setFont(clone $this->objFonts[$fontIndex]);
2836	}
2837	}
2838	}
2839	}
2840	if ($this->readEmptyCells \|\| trim($richText->getPlainText()) !== '') {
2841	$cell = $this->phpSheet->getCell($cellCoordinate);
2842	if (isset($this->mapCellXfIndex[$xfIndex])) {
2843	$cell->setXfIndexNoUpdate($this->mapCellXfIndex[$xfIndex]);
2844	}
2845	$cell->setValueExplicit($richText, DataType::TYPE_STRING);
2846	}
2847	} else {
2848	if ($this->readEmptyCells \|\| trim($sstValue) !== '') {
2849	$cell = $this->phpSheet->getCell($cellCoordinate);
2850	if (!$this->readDataOnly && isset($this->mapCellXfIndex[$xfIndex])) {
2851	$cell->setXfIndexNoUpdate($this->mapCellXfIndex[$xfIndex]);
2852	}
2853	$cell->setValueExplicit($sstValue, DataType::TYPE_STRING);
2854	}
2855	}
2856	}
2857	}
2858
2859	/**
2860	* Read MULRK record
2861	* This record represents a cell range containing RK value
2862	* cells. All cells are located in the same row.
2863	*
2864	* -- "OpenOffice.org's Documentation of the Microsoft
2865	* Excel File Format"
2866	*/
2867	protected function readMulRk(): void
2868	{
2869	$length = self::getUInt2d($this->data, $this->pos + 2);
2870	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
2871
2872	// move stream pointer to next record
2873	$this->pos += 4 + $length;
2874
2875	// offset: 0; size: 2; index to row
2876	$row = self::getUInt2d($recordData, 0);
2877
2878	// offset: 2; size: 2; index to first column
2879	$colFirst = self::getUInt2d($recordData, 2);
2880
2881	// offset: var; size: 2; index to last column
2882	$colLast = self::getUInt2d($recordData, $length - 2);
2883	$columns = $colLast - $colFirst + 1;
2884
2885	// offset within record data
2886	$offset = 4;
2887
2888	$rowIndex = $row + 1;
2889	for ($i = 1; $i <= $columns; ++$i) {
2890	$columnString = Coordinate::stringFromColumnIndex($colFirst + $i);
2891
2892	// Read cell?
2893	if ($this->readFilter->readCell($columnString, $rowIndex, $this->phpSheetTitle)) {
2894	// offset: var; size: 2; index to XF record
2895	$xfIndex = self::getUInt2d($recordData, $offset);
2896
2897	// offset: var; size: 4; RK value
2898	$numValue = self::getIEEE754(self::getInt4d($recordData, $offset + 2));
2899	$cell = $this->phpSheet->getCell($columnString . $rowIndex);
2900	if (!$this->readDataOnly && isset($this->mapCellXfIndex[$xfIndex])) {
2901	// add style
2902	$cell->setXfIndexNoUpdate($this->mapCellXfIndex[$xfIndex]);
2903	}
2904
2905	// add cell value
2906	$cell->setValueExplicit($numValue, DataType::TYPE_NUMERIC);
2907	}
2908
2909	$offset += 6;
2910	}
2911	}
2912
2913	/**
2914	* Read NUMBER record
2915	* This record represents a cell that contains a
2916	* floating-point value.
2917	*
2918	* -- "OpenOffice.org's Documentation of the Microsoft
2919	* Excel File Format"
2920	*/
2921	protected function readNumber(): void
2922	{
2923	$length = self::getUInt2d($this->data, $this->pos + 2);
2924	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
2925
2926	// move stream pointer to next record
2927	$this->pos += 4 + $length;
2928
2929	// offset: 0; size: 2; index to row
2930	$row = self::getUInt2d($recordData, 0);
2931
2932	// offset: 2; size 2; index to column
2933	$column = self::getUInt2d($recordData, 2);
2934	$columnString = Coordinate::stringFromColumnIndex($column + 1);
2935	$cellCoordinate = $columnString . ($row + 1);
2936
2937	// Read cell?
2938	if ($this->readFilter->readCell($columnString, $row + 1, $this->phpSheetTitle)) {
2939	// offset 4; size: 2; index to XF record
2940	$xfIndex = self::getUInt2d($recordData, 4);
2941
2942	$numValue = self::extractNumber((string) substr($recordData, 6, 8));
2943
2944	$cell = $this->phpSheet->getCell($cellCoordinate);
2945	if (!$this->readDataOnly && isset($this->mapCellXfIndex[$xfIndex])) {
2946	// add cell style
2947	$cell->setXfIndexNoUpdate($this->mapCellXfIndex[$xfIndex]);
2948	}
2949
2950	// add cell value
2951	$cell->setValueExplicit($numValue, DataType::TYPE_NUMERIC);
2952	}
2953	}
2954
2955	/**
2956	* Read FORMULA record + perhaps a following STRING record if formula result is a string
2957	* This record contains the token array and the result of a
2958	* formula cell.
2959	*
2960	* -- "OpenOffice.org's Documentation of the Microsoft
2961	* Excel File Format"
2962	*/
2963	protected function readFormula(): void
2964	{
2965	$length = self::getUInt2d($this->data, $this->pos + 2);
2966	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
2967
2968	// move stream pointer to next record
2969	$this->pos += 4 + $length;
2970
2971	// offset: 0; size: 2; row index
2972	$row = self::getUInt2d($recordData, 0);
2973
2974	// offset: 2; size: 2; col index
2975	$column = self::getUInt2d($recordData, 2);
2976	$columnString = Coordinate::stringFromColumnIndex($column + 1);
2977	$cellCoordinate = $columnString . ($row + 1);
2978
2979	// offset: 20: size: variable; formula structure
2980	$formulaStructure = (string) substr($recordData, 20);
2981
2982	// offset: 14: size: 2; option flags, recalculate always, recalculate on open etc.
2983	$options = self::getUInt2d($recordData, 14);
2984
2985	// bit: 0; mask: 0x0001; 1 = recalculate always
2986	// bit: 1; mask: 0x0002; 1 = calculate on open
2987	// bit: 2; mask: 0x0008; 1 = part of a shared formula
2988	$isPartOfSharedFormula = (bool) (0x0008 & $options);
2989
2990	// WARNING:
2991	// We can apparently not rely on $isPartOfSharedFormula. Even when $isPartOfSharedFormula = true
2992	// the formula data may be ordinary formula data, therefore we need to check
2993	// explicitly for the tExp token (0x01)
2994	$isPartOfSharedFormula = $isPartOfSharedFormula && ord($formulaStructure[2]) == 0x01;
2995
2996	if ($isPartOfSharedFormula) {
2997	// part of shared formula which means there will be a formula with a tExp token and nothing else
2998	// get the base cell, grab tExp token
2999	$baseRow = self::getUInt2d($formulaStructure, 3);
3000	$baseCol = self::getUInt2d($formulaStructure, 5);
3001	$this->baseCell = Coordinate::stringFromColumnIndex($baseCol + 1) . ($baseRow + 1);
3002	}
3003
3004	// Read cell?
3005	if ($this->readFilter->readCell($columnString, $row + 1, $this->phpSheetTitle)) {
3006	if ($isPartOfSharedFormula) {
3007	// formula is added to this cell after the sheet has been read
3008	$this->sharedFormulaParts[$cellCoordinate] = $this->baseCell;
3009	}
3010
3011	// offset: 16: size: 4; not used
3012
3013	// offset: 4; size: 2; XF index
3014	$xfIndex = self::getUInt2d($recordData, 4);
3015
3016	// offset: 6; size: 8; result of the formula
3017	$resultType = ord($recordData[6]);
3018	$isSpecialResult = (ord($recordData[12]) == 255) && (ord($recordData[13]) == 255);
3019	if (($resultType == 0) && $isSpecialResult) {
3020	// String formula. Result follows in appended STRING record
3021	$dataType = DataType::TYPE_STRING;
3022
3023	// read possible SHAREDFMLA record
3024	$code = self::getUInt2d($this->data, $this->pos);
3025	if ($code == self::XLS_TYPE_SHAREDFMLA) {
3026	$this->readSharedFmla();
3027	}
3028
3029	// read STRING record
3030	$value = $this->readString();
3031	} elseif (($resultType == 1) && $isSpecialResult) {
3032	// Boolean formula. Result is in +2; 0=false, 1=true
3033	$dataType = DataType::TYPE_BOOL;
3034	$value = (bool) ord($recordData[8]);
3035	} elseif (($resultType == 2) && $isSpecialResult) {
3036	// Error formula. Error code is in +2
3037	$dataType = DataType::TYPE_ERROR;
3038	$value = Xls\ErrorCode::lookup(ord($recordData[8]));
3039	} elseif (($resultType == 3) && $isSpecialResult) {
3040	// Formula result is a null string
3041	$dataType = DataType::TYPE_NULL;
3042	$value = '';
3043	} else {
3044	// forumla result is a number, first 14 bytes like _NUMBER record
3045	$dataType = DataType::TYPE_NUMERIC;
3046	$value = self::extractNumber((string) substr($recordData, 6, 8));
3047	}
3048
3049	$cell = $this->phpSheet->getCell($cellCoordinate);
3050	if (!$this->readDataOnly && isset($this->mapCellXfIndex[$xfIndex])) {
3051	// add cell style; skipping the collection update is safe here
3052	// because every path below ends in setCalculatedValue(), which
3053	// performs the update
3054	$cell->setXfIndexNoUpdate($this->mapCellXfIndex[$xfIndex]);
3055	}
3056
3057	// store the formula
3058	if (!$isPartOfSharedFormula) {
3059	// not part of shared formula
3060	// add cell value. If we can read formula, populate with formula, otherwise just used cached value
3061	try {
3062	if ($this->version != self::XLS_BIFF8) {
3063	throw new Exception('Not BIFF8. Can only read BIFF8 formulas');
3064	}
3065	$formula = $this->getFormulaFromStructure($formulaStructure); // get formula in human language
3066	$cell->setValueExplicit('=' . $formula, DataType::TYPE_FORMULA);
3067	} catch (PhpSpreadsheetException $exception) {
3068	$cell->setValueExplicit($value, $dataType);
3069	}
3070	} else {
3071	if ($this->version == self::XLS_BIFF8) {
3072	// do nothing at this point, formula id added later in the code
3073	} else {
3074	$cell->setValueExplicit($value, $dataType);
3075	}
3076	}
3077
3078	// store the cached calculated value
3079	$cell->setCalculatedValue($value, $dataType === DataType::TYPE_NUMERIC);
3080	}
3081	}
3082
3083	/**
3084	* Read a SHAREDFMLA record. This function just stores the binary shared formula in the reader,
3085	* which usually contains relative references.
3086	* These will be used to construct the formula in each shared formula part after the sheet is read.
3087	*/
3088	protected function readSharedFmla(): void
3089	{
3090	$length = self::getUInt2d($this->data, $this->pos + 2);
3091	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
3092
3093	// move stream pointer to next record
3094	$this->pos += 4 + $length;
3095
3096	// offset: 0, size: 6; cell range address of the area used by the shared formula, not used for anything
3097	//$cellRange = substr($recordData, 0, 6);
3098	//$cellRange = Xls\Biff5::readBIFF5CellRangeAddressFixed($cellRange); // note: even BIFF8 uses BIFF5 syntax
3099
3100	// offset: 6, size: 1; not used
3101
3102	// offset: 7, size: 1; number of existing FORMULA records for this shared formula
3103	//$no = ord($recordData[7]);
3104
3105	// offset: 8, size: var; Binary token array of the shared formula
3106	$formula = (string) substr($recordData, 8);
3107
3108	// at this point we only store the shared formula for later use
3109	$this->sharedFormulas[$this->baseCell] = $formula;
3110	}
3111
3112	/**
3113	* Read a STRING record from current stream position and advance the stream pointer to next record
3114	* This record is used for storing result from FORMULA record when it is a string, and
3115	* it occurs directly after the FORMULA record.
3116	*
3117	* @return string The string contents as UTF-8
3118	*/
3119	protected function readString(): string
3120	{
3121	$length = self::getUInt2d($this->data, $this->pos + 2);
3122	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
3123
3124	// move stream pointer to next record
3125	$this->pos += 4 + $length;
3126
3127	if ($this->version == self::XLS_BIFF8) {
3128	$string = self::readUnicodeStringLong($recordData);
3129	$value = $string['value'];
3130	} else {
3131	$string = $this->readByteStringLong($recordData);
3132	$value = $string['value'];
3133	}
3134	/** @var string $value */
3135
3136	return $value;
3137	}
3138
3139	/**
3140	* Read BOOLERR record
3141	* This record represents a Boolean value or error value
3142	* cell.
3143	*
3144	* -- "OpenOffice.org's Documentation of the Microsoft
3145	* Excel File Format"
3146	*/
3147	protected function readBoolErr(): void
3148	{
3149	$length = self::getUInt2d($this->data, $this->pos + 2);
3150	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
3151
3152	// move stream pointer to next record
3153	$this->pos += 4 + $length;
3154
3155	// offset: 0; size: 2; row index
3156	$row = self::getUInt2d($recordData, 0);
3157
3158	// offset: 2; size: 2; column index
3159	$column = self::getUInt2d($recordData, 2);
3160	$columnString = Coordinate::stringFromColumnIndex($column + 1);
3161	$cellCoordinate = $columnString . ($row + 1);
3162
3163	// Read cell?
3164	if ($this->readFilter->readCell($columnString, $row + 1, $this->phpSheetTitle)) {
3165	// offset: 4; size: 2; index to XF record
3166	$xfIndex = self::getUInt2d($recordData, 4);
3167
3168	// offset: 6; size: 1; the boolean value or error value
3169	$boolErr = ord($recordData[6]);
3170
3171	// offset: 7; size: 1; 0=boolean; 1=error
3172	$isError = ord($recordData[7]);
3173
3174	$cell = $this->phpSheet->getCell($cellCoordinate);
3175	if (!$this->readDataOnly && isset($this->mapCellXfIndex[$xfIndex])) {
3176	// add cell style; a value write does not follow on every path,
3177	// so use the updating setter to guarantee persistence
3178	$cell->setXfIndex($this->mapCellXfIndex[$xfIndex]);
3179	}
3180	switch ($isError) {
3181	case 0: // boolean
3182	$value = (bool) $boolErr;
3183
3184	// add cell value
3185	$cell->setValueExplicit($value, DataType::TYPE_BOOL);
3186
3187	break;
3188	case 1: // error type
3189	$value = Xls\ErrorCode::lookup($boolErr);
3190
3191	// add cell value
3192	$cell->setValueExplicit($value, DataType::TYPE_ERROR);
3193
3194	break;
3195	}
3196	}
3197	}
3198
3199	/**
3200	* Read MULBLANK record
3201	* This record represents a cell range of empty cells. All
3202	* cells are located in the same row.
3203	*
3204	* -- "OpenOffice.org's Documentation of the Microsoft
3205	* Excel File Format"
3206	*/
3207	protected function readMulBlank(): void
3208	{
3209	$length = self::getUInt2d($this->data, $this->pos + 2);
3210	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
3211
3212	// move stream pointer to next record
3213	$this->pos += 4 + $length;
3214
3215	// offset: 0; size: 2; index to row
3216	$row = self::getUInt2d($recordData, 0);
3217
3218	// offset: 2; size: 2; index to first column
3219	$fc = self::getUInt2d($recordData, 2);
3220
3221	// offset: 4; size: 2 x nc; list of indexes to XF records
3222	// add style information
3223	if (!$this->readDataOnly && $this->readEmptyCells) {
3224	$rowIndex = $row + 1;
3225	for ($i = 0; $i < $length / 2 - 3; ++$i) {
3226	$columnString = Coordinate::stringFromColumnIndex($fc + $i + 1);
3227
3228	// Read cell?
3229	if ($this->readFilter->readCell($columnString, $rowIndex, $this->phpSheetTitle)) {
3230	$xfIndex = self::getUInt2d($recordData, 4 + 2 * $i);
3231	if (isset($this->mapCellXfIndex[$xfIndex])) {
3232	// blank cells never receive a value write, so use the
3233	// updating setter to guarantee persistence
3234	$this->phpSheet->getCell($columnString . $rowIndex)->setXfIndex($this->mapCellXfIndex[$xfIndex]);
3235	}
3236	}
3237	}
3238	}
3239
3240	// offset: 6; size 2; index to last column (not needed)
3241	}
3242
3243	/**
3244	* Read LABEL record
3245	* This record represents a cell that contains a string. In
3246	* BIFF8 it is usually replaced by the LABELSST record.
3247	* Excel still uses this record, if it copies unformatted
3248	* text cells to the clipboard.
3249	*
3250	* -- "OpenOffice.org's Documentation of the Microsoft
3251	* Excel File Format"
3252	*/
3253	protected function readLabel(): void
3254	{
3255	$length = self::getUInt2d($this->data, $this->pos + 2);
3256	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
3257
3258	// move stream pointer to next record
3259	$this->pos += 4 + $length;
3260
3261	// offset: 0; size: 2; index to row
3262	$row = self::getUInt2d($recordData, 0);
3263
3264	// offset: 2; size: 2; index to column
3265	$column = self::getUInt2d($recordData, 2);
3266	$columnString = Coordinate::stringFromColumnIndex($column + 1);
3267	$cellCoordinate = $columnString . ($row + 1);
3268
3269	// Read cell?
3270	if ($this->readFilter->readCell($columnString, $row + 1, $this->phpSheetTitle)) {
3271	// offset: 4; size: 2; XF index
3272	$xfIndex = self::getUInt2d($recordData, 4);
3273
3274	// add cell value
3275	// todo: what if string is very long? continue record
3276	if ($this->version == self::XLS_BIFF8) {
3277	$string = self::readUnicodeStringLong((string) substr($recordData, 6));
3278	$value = $string['value'];
3279	} else {
3280	$string = $this->readByteStringLong((string) substr($recordData, 6));
3281	$value = $string['value'];
3282	}
3283	/** @var string $value */
3284	if ($this->readEmptyCells \|\| trim($value) !== '') {
3285	$cell = $this->phpSheet->getCell($cellCoordinate);
3286	if (!$this->readDataOnly && isset($this->mapCellXfIndex[$xfIndex])) {
3287	// add cell style
3288	$cell->setXfIndexNoUpdate($this->mapCellXfIndex[$xfIndex]);
3289	}
3290	$cell->setValueExplicit($value, DataType::TYPE_STRING);
3291	}
3292	}
3293	}
3294
3295	/**
3296	* Read BLANK record.
3297	*/
3298	protected function readBlank(): void
3299	{
3300	$length = self::getUInt2d($this->data, $this->pos + 2);
3301	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
3302
3303	// move stream pointer to next record
3304	$this->pos += 4 + $length;
3305
3306	// offset: 0; size: 2; row index
3307	$row = self::getUInt2d($recordData, 0);
3308
3309	// offset: 2; size: 2; col index
3310	$col = self::getUInt2d($recordData, 2);
3311	$columnString = Coordinate::stringFromColumnIndex($col + 1);
3312
3313	$rowIndex = $row + 1;
3314
3315	// Read cell?
3316	if ($this->readFilter->readCell($columnString, $rowIndex, $this->phpSheetTitle)) {
3317	// offset: 4; size: 2; XF index
3318	$xfIndex = self::getUInt2d($recordData, 4);
3319
3320	// add style information; blank cells never receive a value write,
3321	// so use the updating setter to guarantee persistence
3322	if (!$this->readDataOnly && $this->readEmptyCells && isset($this->mapCellXfIndex[$xfIndex])) {
3323	$this->phpSheet->getCell($columnString . $rowIndex)->setXfIndex($this->mapCellXfIndex[$xfIndex]);
3324	}
3325	}
3326	}
3327
3328	/**
3329	* Read MSODRAWING record.
3330	*/
3331	protected function readMsoDrawing(): void
3332	{
3333	//$length = self::getUInt2d($this->data, $this->pos + 2);
3334
3335	// get spliced record data
3336	$splicedRecordData = $this->getSplicedRecordData();
3337	$recordData = $splicedRecordData['recordData'];
3338
3339	$this->drawingData .= StringHelper::convertToString($recordData);
3340	}
3341
3342	/**
3343	* Read OBJ record.
3344	*/
3345	protected function readObj(): void
3346	{
3347	$length = self::getUInt2d($this->data, $this->pos + 2);
3348	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
3349
3350	// move stream pointer to next record
3351	$this->pos += 4 + $length;
3352
3353	if ($this->readDataOnly \|\| $this->version != self::XLS_BIFF8) {
3354	return;
3355	}
3356
3357	// recordData consists of an array of subrecords looking like this:
3358	// ft: 2 bytes; ftCmo type (0x15)
3359	// cb: 2 bytes; size in bytes of ftCmo data
3360	// ot: 2 bytes; Object Type
3361	// id: 2 bytes; Object id number
3362	// grbit: 2 bytes; Option Flags
3363	// data: var; subrecord data
3364
3365	// for now, we are just interested in the second subrecord containing the object type
3366	$ftCmoType = self::getUInt2d($recordData, 0);
3367	$cbCmoSize = self::getUInt2d($recordData, 2);
3368	$otObjType = self::getUInt2d($recordData, 4);
3369	$idObjID = self::getUInt2d($recordData, 6);
3370	$grbitOpts = self::getUInt2d($recordData, 6);
3371
3372	$this->objs[] = [
3373	'ftCmoType' => $ftCmoType,
3374	'cbCmoSize' => $cbCmoSize,
3375	'otObjType' => $otObjType,
3376	'idObjID' => $idObjID,
3377	'grbitOpts' => $grbitOpts,
3378	];
3379	$this->textObjRef = $idObjID;
3380	}
3381
3382	/**
3383	* Read WINDOW2 record.
3384	*/
3385	protected function readWindow2(): void
3386	{
3387	$length = self::getUInt2d($this->data, $this->pos + 2);
3388	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
3389
3390	// move stream pointer to next record
3391	$this->pos += 4 + $length;
3392
3393	// offset: 0; size: 2; option flags
3394	$options = self::getUInt2d($recordData, 0);
3395
3396	// offset: 2; size: 2; index to first visible row
3397	//$firstVisibleRow = self::getUInt2d($recordData, 2);
3398
3399	// offset: 4; size: 2; index to first visible colum
3400	//$firstVisibleColumn = self::getUInt2d($recordData, 4);
3401	$zoomscaleInPageBreakPreview = 0;
3402	$zoomscaleInNormalView = 0;
3403	if ($this->version === self::XLS_BIFF8) {
3404	// offset: 8; size: 2; not used
3405	// offset: 10; size: 2; cached magnification factor in page break preview (in percent); 0 = Default (60%)
3406	// offset: 12; size: 2; cached magnification factor in normal view (in percent); 0 = Default (100%)
3407	// offset: 14; size: 4; not used
3408	if (!isset($recordData[10])) {
3409	$zoomscaleInPageBreakPreview = 0;
3410	} else {
3411	$zoomscaleInPageBreakPreview = self::getUInt2d($recordData, 10);
3412	}
3413
3414	if ($zoomscaleInPageBreakPreview === 0) {
3415	$zoomscaleInPageBreakPreview = 60;
3416	}
3417
3418	if (!isset($recordData[12])) {
3419	$zoomscaleInNormalView = 0;
3420	} else {
3421	$zoomscaleInNormalView = self::getUInt2d($recordData, 12);
3422	}
3423
3424	if ($zoomscaleInNormalView === 0) {
3425	$zoomscaleInNormalView = 100;
3426	}
3427	}
3428
3429	// bit: 1; mask: 0x0002; 0 = do not show gridlines, 1 = show gridlines
3430	$showGridlines = (bool) ((0x0002 & $options) >> 1);
3431	$this->phpSheet->setShowGridlines($showGridlines);
3432
3433	// bit: 2; mask: 0x0004; 0 = do not show headers, 1 = show headers
3434	$showRowColHeaders = (bool) ((0x0004 & $options) >> 2);
3435	$this->phpSheet->setShowRowColHeaders($showRowColHeaders);
3436
3437	// bit: 3; mask: 0x0008; 0 = panes are not frozen, 1 = panes are frozen
3438	$this->frozen = (bool) ((0x0008 & $options) >> 3);
3439
3440	// bit: 6; mask: 0x0040; 0 = columns from left to right, 1 = columns from right to left
3441	$this->phpSheet->setRightToLeft((bool) ((0x0040 & $options) >> 6));
3442
3443	// bit: 10; mask: 0x0400; 0 = sheet not active, 1 = sheet active
3444	$isActive = (bool) ((0x0400 & $options) >> 10);
3445	if ($isActive) {
3446	$this->spreadsheet->setActiveSheetIndex($this->spreadsheet->getIndex($this->phpSheet));
3447	$this->activeSheetSet = true;
3448	}
3449
3450	// bit: 11; mask: 0x0800; 0 = normal view, 1 = page break view
3451	$isPageBreakPreview = (bool) ((0x0800 & $options) >> 11);
3452
3453	//FIXME: set $firstVisibleRow and $firstVisibleColumn
3454
3455	if ($this->phpSheet->getSheetView()->getView() !== SheetView::SHEETVIEW_PAGE_LAYOUT) {
3456	//NOTE: this setting is inferior to page layout view(Excel2007-)
3457	$view = $isPageBreakPreview ? SheetView::SHEETVIEW_PAGE_BREAK_PREVIEW : SheetView::SHEETVIEW_NORMAL;
3458	$this->phpSheet->getSheetView()->setView($view);
3459	if ($this->version === self::XLS_BIFF8) {
3460	$zoomScale = $isPageBreakPreview ? $zoomscaleInPageBreakPreview : $zoomscaleInNormalView;
3461	$this->phpSheet->getSheetView()->setZoomScale($zoomScale);
3462	$this->phpSheet->getSheetView()->setZoomScaleNormal($zoomscaleInNormalView);
3463	}
3464	}
3465	}
3466
3467	/**
3468	* Read PLV Record(Created by Excel2007 or upper).
3469	*/
3470	protected function readPageLayoutView(): void
3471	{
3472	$length = self::getUInt2d($this->data, $this->pos + 2);
3473	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
3474
3475	// move stream pointer to next record
3476	$this->pos += 4 + $length;
3477
3478	// offset: 0; size: 2; rt
3479	//->ignore
3480	//$rt = self::getUInt2d($recordData, 0);
3481	// offset: 2; size: 2; grbitfr
3482	//->ignore
3483	//$grbitFrt = self::getUInt2d($recordData, 2);
3484	// offset: 4; size: 8; reserved
3485	//->ignore
3486
3487	// offset: 12; size 2; zoom scale
3488	$wScalePLV = self::getUInt2d($recordData, 12);
3489	// offset: 14; size 2; grbit
3490	$grbit = self::getUInt2d($recordData, 14);
3491
3492	// decomprise grbit
3493	$fPageLayoutView = $grbit & 0x01;
3494	//$fRulerVisible = ($grbit >> 1) & 0x01; //no support
3495	//$fWhitespaceHidden = ($grbit >> 3) & 0x01; //no support
3496
3497	if ($fPageLayoutView === 1) {
3498	$this->phpSheet->getSheetView()->setView(SheetView::SHEETVIEW_PAGE_LAYOUT);
3499	$this->phpSheet->getSheetView()->setZoomScale($wScalePLV); //set by Excel2007 only if SHEETVIEW_PAGE_LAYOUT
3500	}
3501	//otherwise, we cannot know whether SHEETVIEW_PAGE_LAYOUT or SHEETVIEW_PAGE_BREAK_PREVIEW.
3502	}
3503
3504	/**
3505	* Read SCL record.
3506	*/
3507	protected function readScl(): void
3508	{
3509	$length = self::getUInt2d($this->data, $this->pos + 2);
3510	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
3511
3512	// move stream pointer to next record
3513	$this->pos += 4 + $length;
3514
3515	// offset: 0; size: 2; numerator of the view magnification
3516	$numerator = self::getUInt2d($recordData, 0);
3517
3518	// offset: 2; size: 2; numerator of the view magnification
3519	$denumerator = self::getUInt2d($recordData, 2);
3520
3521	// set the zoom scale (in percent)
3522	$this->phpSheet->getSheetView()->setZoomScale($numerator * 100 / $denumerator);
3523	}
3524
3525	/**
3526	* Read PANE record.
3527	*/
3528	protected function readPane(): void
3529	{
3530	$length = self::getUInt2d($this->data, $this->pos + 2);
3531	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
3532
3533	// move stream pointer to next record
3534	$this->pos += 4 + $length;
3535
3536	if (!$this->readDataOnly) {
3537	// offset: 0; size: 2; position of vertical split
3538	$px = self::getUInt2d($recordData, 0);
3539
3540	// offset: 2; size: 2; position of horizontal split
3541	$py = self::getUInt2d($recordData, 2);
3542
3543	// offset: 4; size: 2; top most visible row in the bottom pane
3544	$rwTop = self::getUInt2d($recordData, 4);
3545
3546	// offset: 6; size: 2; first visible left column in the right pane
3547	$colLeft = self::getUInt2d($recordData, 6);
3548
3549	if ($this->frozen) {
3550	// frozen panes
3551	$cell = Coordinate::stringFromColumnIndex($px + 1) . ($py + 1);
3552	$topLeftCell = Coordinate::stringFromColumnIndex($colLeft + 1) . ($rwTop + 1);
3553	$this->phpSheet->freezePane($cell, $topLeftCell);
3554	}
3555	// unfrozen panes; split windows; not supported by PhpSpreadsheet core
3556	}
3557	}
3558
3559	private const REGEX_WHOLE_COLUMN = '/^([A-Z]+1\:[A-Z]+)'
3560	. '(' . AddressRange::MAX_ROW_XLS_OLD . '\|' . AddressRange::MAX_ROW_XLS . ')'
3561	. '$/';
3562	private const REGEX_WHOLE_COLUMN_REPLACE = '${1}' . AddressRange::MAX_ROW;
3563	private const REGEX_WHOLE_ROW = '/^(A\d+\:)'
3564	. AddressRange::MAX_COLUMN_XLS
3565	. '(\d+)$/';
3566	private const REGEX_WHOLE_ROW_REPLACE = '${1}'
3567	. AddressRange::MAX_COLUMN
3568	. '${2}';
3569
3570	/**
3571	* Read SELECTION record. There is one such record for each pane in the sheet.
3572	*/
3573	protected function readSelection(): string
3574	{
3575	$length = self::getUInt2d($this->data, $this->pos + 2);
3576	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
3577	$selectedCells = '';
3578
3579	// move stream pointer to next record
3580	$this->pos += 4 + $length;
3581
3582	if (!$this->readDataOnly) {
3583	// offset: 0; size: 1; pane identifier
3584	//$paneId = ord($recordData[0]);
3585
3586	// offset: 1; size: 2; index to row of the active cell
3587	//$r = self::getUInt2d($recordData, 1);
3588
3589	// offset: 3; size: 2; index to column of the active cell
3590	//$c = self::getUInt2d($recordData, 3);
3591
3592	// offset: 5; size: 2; index into the following cell range list to the
3593	// entry that contains the active cell
3594	//$index = self::getUInt2d($recordData, 5);
3595
3596	// offset: 7; size: var; cell range address list containing all selected cell ranges
3597	$data = (string) substr($recordData, 7);
3598	$cellRangeAddressList = Xls\Biff5::readBIFF5CellRangeAddressList($data); // note: also BIFF8 uses BIFF5 syntax
3599
3600	$selectedCells = $cellRangeAddressList['cellRangeAddresses'][0];
3601
3602	// first row '1' + last row '16384' or '65536' indicates that full column is selected (apparently also in BIFF8!)
3603	if (Preg::isMatch(self::REGEX_WHOLE_COLUMN, $selectedCells)) {
3604	$selectedCells = Preg::replace(self::REGEX_WHOLE_COLUMN, self::REGEX_WHOLE_COLUMN_REPLACE, $selectedCells);
3605	}
3606
3607	// first column 'A' + last column 'IV' indicates that full row is selected
3608	if (Preg::isMatch(self::REGEX_WHOLE_ROW, $selectedCells)) {
3609	$selectedCells = Preg::replace(self::REGEX_WHOLE_ROW, self::REGEX_WHOLE_ROW_REPLACE, $selectedCells);
3610	}
3611
3612	$this->phpSheet->setSelectedCells($selectedCells);
3613	}
3614
3615	return $selectedCells;
3616	}
3617
3618	private function includeCellRangeFiltered(string $cellRangeAddress): bool
3619	{
3620	$includeCellRange = false;
3621	$rangeBoundaries = Coordinate::getRangeBoundaries($cellRangeAddress);
3622	StringHelper::stringIncrement($rangeBoundaries[1][0]);
3623	for ($row = $rangeBoundaries[0][1]; $row <= $rangeBoundaries[1][1]; ++$row) {
3624	for ($column = $rangeBoundaries[0][0]; $column != $rangeBoundaries[1][0]; StringHelper::stringIncrement($column)) {
3625	if ($this->readFilter->readCell($column, $row, $this->phpSheetTitle)) {
3626	$includeCellRange = true;
3627
3628	break 2;
3629	}
3630	}
3631	}
3632
3633	return $includeCellRange;
3634	}
3635
3636	/**
3637	* MERGEDCELLS.
3638	*
3639	* This record contains the addresses of merged cell ranges
3640	* in the current sheet.
3641	*
3642	* -- "OpenOffice.org's Documentation of the Microsoft
3643	* Excel File Format"
3644	*/
3645	protected function readMergedCells(): void
3646	{
3647	$length = self::getUInt2d($this->data, $this->pos + 2);
3648	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
3649
3650	// move stream pointer to next record
3651	$this->pos += 4 + $length;
3652
3653	if ($this->version == self::XLS_BIFF8 && !$this->readDataOnly) {
3654	$cellRangeAddressList = Xls\Biff8::readBIFF8CellRangeAddressList($recordData);
3655	foreach ($cellRangeAddressList['cellRangeAddresses'] as $cellRangeAddress) {
3656	/** @var string $cellRangeAddress */
3657	if (
3658	(str_contains($cellRangeAddress, ':'))
3659	&& ($this->includeCellRangeFiltered($cellRangeAddress))
3660	) {
3661	$this->phpSheet->mergeCells($cellRangeAddress, Worksheet::MERGE_CELL_CONTENT_HIDE);
3662	}
3663	}
3664	}
3665	}
3666
3667	/**
3668	* Read HYPERLINK record.
3669	*/
3670	protected function readHyperLink(): void
3671	{
3672	$length = self::getUInt2d($this->data, $this->pos + 2);
3673	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
3674
3675	// move stream pointer forward to next record
3676	$this->pos += 4 + $length;
3677
3678	if (!$this->readDataOnly) {
3679	// offset: 0; size: 8; cell range address of all cells containing this hyperlink
3680	try {
3681	$cellRange = Xls\Biff8::readBIFF8CellRangeAddressFixed($recordData);
3682	} catch (PhpSpreadsheetException $exception) {
3683	return;
3684	}
3685
3686	// offset: 8, size: 16; GUID of StdLink
3687
3688	// offset: 24, size: 4; unknown value
3689
3690	// offset: 28, size: 4; option flags
3691	// bit: 0; mask: 0x00000001; 0 = no link or extant, 1 = file link or URL
3692	$isFileLinkOrUrl = (0x00000001 & self::getUInt2d($recordData, 28)) >> 0;
3693
3694	// bit: 1; mask: 0x00000002; 0 = relative path, 1 = absolute path or URL
3695	//$isAbsPathOrUrl = (0x00000001 & self::getUInt2d($recordData, 28)) >> 1;
3696
3697	// bit: 2 (and 4); mask: 0x00000014; 0 = no description
3698	$hasDesc = (0x00000014 & self::getUInt2d($recordData, 28)) >> 2;
3699
3700	// bit: 3; mask: 0x00000008; 0 = no text, 1 = has text
3701	$hasText = (0x00000008 & self::getUInt2d($recordData, 28)) >> 3;
3702
3703	// bit: 7; mask: 0x00000080; 0 = no target frame, 1 = has target frame
3704	$hasFrame = (0x00000080 & self::getUInt2d($recordData, 28)) >> 7;
3705
3706	// bit: 8; mask: 0x00000100; 0 = file link or URL, 1 = UNC path (inc. server name)
3707	$isUNC = (0x00000100 & self::getUInt2d($recordData, 28)) >> 8;
3708
3709	// offset within record data
3710	$offset = 32;
3711
3712	if ($hasDesc) {
3713	// offset: 32; size: var; character count of description text
3714	$dl = self::getInt4d($recordData, 32);
3715	// offset: 36; size: var; character array of description text, no Unicode string header, always 16-bit characters, zero terminated
3716	//$desc = self::encodeUTF16(substr($recordData, 36, 2 * ($dl - 1)), false);
3717	$offset += 4 + 2 * $dl;
3718	}
3719	if ($hasFrame) {
3720	$fl = self::getInt4d($recordData, $offset);
3721	$offset += 4 + 2 * $fl;
3722	}
3723
3724	// detect type of hyperlink (there are 4 types)
3725	$hyperlinkType = null;
3726
3727	if ($isUNC) {
3728	$hyperlinkType = 'UNC';
3729	} elseif (!$isFileLinkOrUrl) {
3730	$hyperlinkType = 'workbook';
3731	} elseif (ord($recordData[$offset]) == 0x03) {
3732	$hyperlinkType = 'local';
3733	} elseif (ord($recordData[$offset]) == 0xE0) {
3734	$hyperlinkType = 'URL';
3735	}
3736
3737	switch ($hyperlinkType) {
3738	case 'URL':
3739	// section 5.58.2: Hyperlink containing a URL
3740	// e.g. http://example.org/index.php
3741
3742	// offset: var; size: 16; GUID of URL Moniker
3743	$offset += 16;
3744	// offset: var; size: 4; size (in bytes) of character array of the URL including trailing zero word
3745	$us = self::getInt4d($recordData, $offset);
3746	$offset += 4;
3747	// offset: var; size: $us; character array of the URL, no Unicode string header, always 16-bit characters, zero-terminated
3748	$url = self::encodeUTF16((string) substr($recordData, $offset, $us - 2), false);
3749	$nullOffset = strpos($url, chr(0x00));
3750	if ($nullOffset) {
3751	$url = (string) substr($url, 0, $nullOffset);
3752	}
3753	$url .= $hasText ? '#' : '';
3754	$offset += $us;
3755
3756	break;
3757	case 'local':
3758	// section 5.58.3: Hyperlink to local file
3759	// examples:
3760	// mydoc.txt
3761	// ../../somedoc.xls#Sheet!A1
3762
3763	// offset: var; size: 16; GUI of File Moniker
3764	$offset += 16;
3765
3766	// offset: var; size: 2; directory up-level count.
3767	$upLevelCount = self::getUInt2d($recordData, $offset);
3768	$offset += 2;
3769
3770	// offset: var; size: 4; character count of the shortened file path and name, including trailing zero word
3771	$sl = self::getInt4d($recordData, $offset);
3772	$offset += 4;
3773
3774	// offset: var; size: sl; character array of the shortened file path and name in 8.3-DOS-format (compressed Unicode string)
3775	$shortenedFilePath = (string) substr($recordData, $offset, $sl);
3776	$shortenedFilePath = self::encodeUTF16($shortenedFilePath, true);
3777	$shortenedFilePath = (string) substr($shortenedFilePath, 0, -1); // remove trailing zero
3778
3779	$offset += $sl;
3780
3781	// offset: var; size: 24; unknown sequence
3782	$offset += 24;
3783
3784	// extended file path
3785	// offset: var; size: 4; size of the following file link field including string lenth mark
3786	$sz = self::getInt4d($recordData, $offset);
3787	$offset += 4;
3788
3789	$extendedFilePath = '';
3790	// only present if $sz > 0
3791	if ($sz > 0) {
3792	// offset: var; size: 4; size of the character array of the extended file path and name
3793	$xl = self::getInt4d($recordData, $offset);
3794	$offset += 4;
3795
3796	// offset: var; size 2; unknown
3797	$offset += 2;
3798
3799	// offset: var; size $xl; character array of the extended file path and name.
3800	$extendedFilePath = (string) substr($recordData, $offset, $xl);
3801	$extendedFilePath = self::encodeUTF16($extendedFilePath, false);
3802	$offset += $xl;
3803	}
3804
3805	// construct the path
3806	$url = str_repeat('..\\', $upLevelCount);
3807	$url .= ($sz > 0) ? $extendedFilePath : $shortenedFilePath; // use extended path if available
3808	$url .= $hasText ? '#' : '';
3809
3810	break;
3811	case 'UNC':
3812	// section 5.58.4: Hyperlink to a File with UNC (Universal Naming Convention) Path
3813	// todo: implement
3814	return;
3815	case 'workbook':
3816	// section 5.58.5: Hyperlink to the Current Workbook
3817	// e.g. Sheet2!B1:C2, stored in text mark field
3818	$url = 'sheet://';
3819
3820	break;
3821	default:
3822	return;
3823	}
3824
3825	if ($hasText) {
3826	// offset: var; size: 4; character count of text mark including trailing zero word
3827	$tl = self::getInt4d($recordData, $offset);
3828	$offset += 4;
3829	// offset: var; size: var; character array of the text mark without the # sign, no Unicode header, always 16-bit characters, zero-terminated
3830	$text = self::encodeUTF16((string) substr($recordData, $offset, 2 * ($tl - 1)), false);
3831	$url .= $text;
3832	}
3833
3834	// apply the hyperlink to all the relevant cells
3835	foreach (Coordinate::extractAllCellReferencesInRange($cellRange) as $coordinate) {
3836	$this->phpSheet->getCell($coordinate)->getHyperLink()->setUrl($url);
3837	}
3838	}
3839	}
3840
3841	/**
3842	* Read DATAVALIDATIONS record.
3843	*/
3844	protected function readDataValidations(): void
3845	{
3846	$length = self::getUInt2d($this->data, $this->pos + 2);
3847	//$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
3848
3849	// move stream pointer forward to next record
3850	$this->pos += 4 + $length;
3851	}
3852
3853	/**
3854	* Read DATAVALIDATION record.
3855	*/
3856	protected function readDataValidation(): void
3857	{
3858	(new Xls\DataValidationHelper())->readDataValidation2($this);
3859	}
3860
3861	/**
3862	* Read SHEETLAYOUT record. Stores sheet tab color information.
3863	*/
3864	protected function readSheetLayout(): void
3865	{
3866	$length = self::getUInt2d($this->data, $this->pos + 2);
3867	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
3868
3869	// move stream pointer to next record
3870	$this->pos += 4 + $length;
3871
3872	if (!$this->readDataOnly) {
3873	// offset: 0; size: 2; repeated record identifier 0x0862
3874
3875	// offset: 2; size: 10; not used
3876
3877	// offset: 12; size: 4; size of record data
3878	// Excel 2003 uses size of 0x14 (documented), Excel 2007 uses size of 0x28 (not documented?)
3879	$sz = self::getInt4d($recordData, 12);
3880
3881	switch ($sz) {
3882	case 0x14:
3883	// offset: 16; size: 2; color index for sheet tab
3884	$colorIndex = self::getUInt2d($recordData, 16);
3885	/** @var string[] */
3886	$color = Xls\Color::map($colorIndex, $this->palette, $this->version);
3887	$this->phpSheet->getTabColor()->setRGB($color['rgb']);
3888
3889	break;
3890	case 0x28:
3891	// TODO: Investigate structure for .xls SHEETLAYOUT record as saved by MS Office Excel 2007
3892	return;
3893	}
3894	}
3895	}
3896
3897	/**
3898	* Read SHEETPROTECTION record (FEATHEADR).
3899	*/
3900	protected function readSheetProtection(): void
3901	{
3902	$length = self::getUInt2d($this->data, $this->pos + 2);
3903	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
3904
3905	// move stream pointer to next record
3906	$this->pos += 4 + $length;
3907
3908	if ($this->readDataOnly) {
3909	return;
3910	}
3911
3912	// offset: 0; size: 2; repeated record header
3913
3914	// offset: 2; size: 2; FRT cell reference flag (=0 currently)
3915
3916	// offset: 4; size: 8; Currently not used and set to 0
3917
3918	// offset: 12; size: 2; Shared feature type index (2=Enhanced Protetion, 4=SmartTag)
3919	$isf = self::getUInt2d($recordData, 12);
3920	if ($isf != 2) {
3921	return;
3922	}
3923
3924	// offset: 14; size: 1; =1 since this is a feat header
3925
3926	// offset: 15; size: 4; size of rgbHdrSData
3927
3928	// rgbHdrSData, assume "Enhanced Protection"
3929	// offset: 19; size: 2; option flags
3930	$options = self::getUInt2d($recordData, 19);
3931
3932	// bit: 0; mask 0x0001; 1 = user may edit objects, 0 = users must not edit objects
3933	// Note - do not negate $bool
3934	$bool = (0x0001 & $options) >> 0;
3935	$this->phpSheet->getProtection()->setObjects((bool) $bool);
3936
3937	// bit: 1; mask 0x0002; edit scenarios
3938	// Note - do not negate $bool
3939	$bool = (0x0002 & $options) >> 1;
3940	$this->phpSheet->getProtection()->setScenarios((bool) $bool);
3941
3942	// bit: 2; mask 0x0004; format cells
3943	$bool = (0x0004 & $options) >> 2;
3944	$this->phpSheet->getProtection()->setFormatCells(!$bool);
3945
3946	// bit: 3; mask 0x0008; format columns
3947	$bool = (0x0008 & $options) >> 3;
3948	$this->phpSheet->getProtection()->setFormatColumns(!$bool);
3949
3950	// bit: 4; mask 0x0010; format rows
3951	$bool = (0x0010 & $options) >> 4;
3952	$this->phpSheet->getProtection()->setFormatRows(!$bool);
3953
3954	// bit: 5; mask 0x0020; insert columns
3955	$bool = (0x0020 & $options) >> 5;
3956	$this->phpSheet->getProtection()->setInsertColumns(!$bool);
3957
3958	// bit: 6; mask 0x0040; insert rows
3959	$bool = (0x0040 & $options) >> 6;
3960	$this->phpSheet->getProtection()->setInsertRows(!$bool);
3961
3962	// bit: 7; mask 0x0080; insert hyperlinks
3963	$bool = (0x0080 & $options) >> 7;
3964	$this->phpSheet->getProtection()->setInsertHyperlinks(!$bool);
3965
3966	// bit: 8; mask 0x0100; delete columns
3967	$bool = (0x0100 & $options) >> 8;
3968	$this->phpSheet->getProtection()->setDeleteColumns(!$bool);
3969
3970	// bit: 9; mask 0x0200; delete rows
3971	$bool = (0x0200 & $options) >> 9;
3972	$this->phpSheet->getProtection()->setDeleteRows(!$bool);
3973
3974	// bit: 10; mask 0x0400; select locked cells
3975	// Note that this is opposite of most of above.
3976	$bool = (0x0400 & $options) >> 10;
3977	$this->phpSheet->getProtection()->setSelectLockedCells((bool) $bool);
3978
3979	// bit: 11; mask 0x0800; sort cell range
3980	$bool = (0x0800 & $options) >> 11;
3981	$this->phpSheet->getProtection()->setSort(!$bool);
3982
3983	// bit: 12; mask 0x1000; auto filter
3984	$bool = (0x1000 & $options) >> 12;
3985	$this->phpSheet->getProtection()->setAutoFilter(!$bool);
3986
3987	// bit: 13; mask 0x2000; pivot tables
3988	$bool = (0x2000 & $options) >> 13;
3989	$this->phpSheet->getProtection()->setPivotTables(!$bool);
3990
3991	// bit: 14; mask 0x4000; select unlocked cells
3992	// Note that this is opposite of most of above.
3993	$bool = (0x4000 & $options) >> 14;
3994	$this->phpSheet->getProtection()->setSelectUnlockedCells((bool) $bool);
3995
3996	// offset: 21; size: 2; not used
3997	}
3998
3999	/**
4000	* Read RANGEPROTECTION record
4001	* Reading of this record is based on Microsoft Office Excel 97-2000 Binary File Format Specification,
4002	* where it is referred to as FEAT record.
4003	*/
4004	protected function readRangeProtection(): void
4005	{
4006	$length = self::getUInt2d($this->data, $this->pos + 2);
4007	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
4008
4009	// move stream pointer to next record
4010	$this->pos += 4 + $length;
4011
4012	// local pointer in record data
4013	$offset = 0;
4014
4015	if (!$this->readDataOnly) {
4016	$offset += 12;
4017
4018	// offset: 12; size: 2; shared feature type, 2 = enhanced protection, 4 = smart tag
4019	$isf = self::getUInt2d($recordData, 12);
4020	if ($isf != 2) {
4021	// we only read FEAT records of type 2
4022	return;
4023	}
4024	$offset += 2;
4025
4026	$offset += 5;
4027
4028	// offset: 19; size: 2; count of ref ranges this feature is on
4029	$cref = self::getUInt2d($recordData, 19);
4030	$offset += 2;
4031
4032	$offset += 6;
4033
4034	// offset: 27; size: 8 * $cref; list of cell ranges (like in hyperlink record)
4035	$cellRanges = [];
4036	for ($i = 0; $i < $cref; ++$i) {
4037	try {
4038	$cellRange = Xls\Biff8::readBIFF8CellRangeAddressFixed((string) substr($recordData, 27 + 8 * $i, 8));
4039	} catch (PhpSpreadsheetException $exception) {
4040	return;
4041	}
4042	$cellRanges[] = $cellRange;
4043	$offset += 8;
4044	}
4045
4046	// offset: var; size: var; variable length of feature specific data
4047	//$rgbFeat = substr($recordData, $offset);
4048	$offset += 4;
4049
4050	// offset: var; size: 4; the encrypted password (only 16-bit although field is 32-bit)
4051	$wPassword = self::getInt4d($recordData, $offset);
4052	$offset += 4;
4053
4054	// Apply range protection to sheet
4055	if ($cellRanges) {
4056	$this->phpSheet->protectCells(implode(' ', $cellRanges), ($wPassword === 0) ? '' : strtoupper(dechex($wPassword)), true);
4057	}
4058	}
4059	}
4060
4061	/**
4062	* Read a free CONTINUE record. Free CONTINUE record may be a camouflaged MSODRAWING record
4063	* When MSODRAWING data on a sheet exceeds 8224 bytes, CONTINUE records are used instead. Undocumented.
4064	* In this case, we must treat the CONTINUE record as a MSODRAWING record.
4065	*/
4066	protected function readContinue(): void
4067	{
4068	$length = self::getUInt2d($this->data, $this->pos + 2);
4069	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
4070
4071	// check if we are reading drawing data
4072	// this is in case a free CONTINUE record occurs in other circumstances we are unaware of
4073	if ($this->drawingData == '') {
4074	// move stream pointer to next record
4075	$this->pos += 4 + $length;
4076
4077	return;
4078	}
4079
4080	// check if record data is at least 4 bytes long, otherwise there is no chance this is MSODRAWING data
4081	if ($length < 4) {
4082	// move stream pointer to next record
4083	$this->pos += 4 + $length;
4084
4085	return;
4086	}
4087
4088	// dirty check to see if CONTINUE record could be a camouflaged MSODRAWING record
4089	// look inside CONTINUE record to see if it looks like a part of an Escher stream
4090	// we know that Escher stream may be split at least at
4091	// 0xF003 MsofbtSpgrContainer
4092	// 0xF004 MsofbtSpContainer
4093	// 0xF00D MsofbtClientTextbox
4094	$validSplitPoints = [0xF003, 0xF004, 0xF00D]; // add identifiers if we find more
4095
4096	$splitPoint = self::getUInt2d($recordData, 2);
4097	if (in_array($splitPoint, $validSplitPoints)) {
4098	// get spliced record data (and move pointer to next record)
4099	$splicedRecordData = $this->getSplicedRecordData();
4100	$this->drawingData .= StringHelper::convertToString($splicedRecordData['recordData']);
4101
4102	return;
4103	}
4104
4105	// move stream pointer to next record
4106	$this->pos += 4 + $length;
4107	}
4108
4109	/**
4110	* Reads a record from current position in data stream and continues reading data as long as CONTINUE
4111	* records are found. Splices the record data pieces and returns the combined string as if record data
4112	* is in one piece.
4113	* Moves to next current position in data stream to start of next record different from a CONtINUE record.
4114	*
4115	* @return mixed[]
4116	*/
4117	private function getSplicedRecordData(): array
4118	{
4119	$data = '';
4120	$spliceOffsets = [];
4121
4122	$i = 0;
4123	$spliceOffsets[0] = 0;
4124
4125	do {
4126	++$i;
4127
4128	// offset: 0; size: 2; identifier
4129	//$identifier = self::getUInt2d($this->data, $this->pos);
4130	// offset: 2; size: 2; length
4131	$length = self::getUInt2d($this->data, $this->pos + 2);
4132	$data .= $this->readRecordData($this->data, $this->pos + 4, $length);
4133
4134	$spliceOffsets[$i] = $spliceOffsets[$i - 1] + $length;
4135
4136	$this->pos += 4 + $length;
4137	$nextIdentifier = self::getUInt2d($this->data, $this->pos);
4138	} while ($nextIdentifier == self::XLS_TYPE_CONTINUE);
4139
4140	return [
4141	'recordData' => $data,
4142	'spliceOffsets' => $spliceOffsets,
4143	];
4144	}
4145
4146	/**
4147	* Convert formula structure into human readable Excel formula like 'A3+A5*5'.
4148	*
4149	* @param string $formulaStructure The complete binary data for the formula
4150	* @param string $baseCell Base cell, only needed when formula contains tRefN tokens, e.g. with shared formulas
4151	*
4152	* @return string Human readable formula
4153	*/
4154	protected function getFormulaFromStructure(string $formulaStructure, string $baseCell = 'A1'): string
4155	{
4156	// offset: 0; size: 2; size of the following formula data
4157	$sz = self::getUInt2d($formulaStructure, 0);
4158
4159	// offset: 2; size: sz
4160	$formulaData = (string) substr($formulaStructure, 2, $sz);
4161
4162	// offset: 2 + sz; size: variable (optional)
4163	if (strlen($formulaStructure) > 2 + $sz) {
4164	$additionalData = (string) substr($formulaStructure, 2 + $sz);
4165	} else {
4166	$additionalData = '';
4167	}
4168
4169	return $this->getFormulaFromData($formulaData, $additionalData, $baseCell);
4170	}
4171
4172	/**
4173	* Take formula data and additional data for formula and return human readable formula.
4174	*
4175	* @param string $formulaData The binary data for the formula itself
4176	* @param string $additionalData Additional binary data going with the formula
4177	* @param string $baseCell Base cell, only needed when formula contains tRefN tokens, e.g. with shared formulas
4178	*
4179	* @return string Human readable formula
4180	*/
4181	private function getFormulaFromData(string $formulaData, string $additionalData = '', string $baseCell = 'A1'): string
4182	{
4183	// start parsing the formula data
4184	$tokens = [];
4185
4186	while ($formulaData !== '' && $token = $this->getNextToken($formulaData, $baseCell)) {
4187	$tokens[] = $token;
4188	/** @var int[] $token */
4189	$formulaData = (string) substr($formulaData, $token['size']);
4190	}
4191
4192	$formulaString = $this->createFormulaFromTokens($tokens, $additionalData);
4193
4194	return $formulaString;
4195	}
4196
4197	/**
4198	* Take array of tokens together with additional data for formula and return human readable formula.
4199	*
4200	* @param mixed[][] $tokens
4201	* @param string $additionalData Additional binary data going with the formula
4202	*
4203	* @return string Human readable formula
4204	*/
4205	private function createFormulaFromTokens(array $tokens, string $additionalData): string
4206	{
4207	// empty formula?
4208	if (empty($tokens)) {
4209	return '';
4210	}
4211
4212	$formulaStrings = [];
4213	foreach ($tokens as $token) {
4214	// initialize spaces
4215	$space0 = $space0 ?? ''; // spaces before next token, not tParen
4216	$space1 = $space1 ?? ''; // carriage returns before next token, not tParen
4217	$space2 = $space2 ?? ''; // spaces before opening parenthesis
4218	$space3 = $space3 ?? ''; // carriage returns before opening parenthesis
4219	$space4 = $space4 ?? ''; // spaces before closing parenthesis
4220	$space5 = $space5 ?? ''; // carriage returns before closing parenthesis
4221	/** @var string */
4222	$tokenData = $token['data'] ?? '';
4223	switch ($token['name']) {
4224	case 'tAdd': // addition
4225	case 'tConcat': // addition
4226	case 'tDiv': // division
4227	case 'tEQ': // equality
4228	case 'tGE': // greater than or equal
4229	case 'tGT': // greater than
4230	case 'tIsect': // intersection
4231	case 'tLE': // less than or equal
4232	case 'tList': // less than or equal
4233	case 'tLT': // less than
4234	case 'tMul': // multiplication
4235	case 'tNE': // multiplication
4236	case 'tPower': // power
4237	case 'tRange': // range
4238	case 'tSub': // subtraction
4239	$op2 = array_pop($formulaStrings);
4240	$op1 = array_pop($formulaStrings);
4241	$formulaStrings[] = "$op1$space1$space0{$tokenData}$op2";
4242	unset($space0, $space1);
4243
4244	break;
4245	case 'tUplus': // unary plus
4246	case 'tUminus': // unary minus
4247	$op = array_pop($formulaStrings);
4248	$formulaStrings[] = "$space1$space0{$tokenData}$op";
4249	unset($space0, $space1);
4250
4251	break;
4252	case 'tPercent': // percent sign
4253	$op = array_pop($formulaStrings);
4254	$formulaStrings[] = "$op$space1$space0{$tokenData}";
4255	unset($space0, $space1);
4256
4257	break;
4258	case 'tAttrVolatile': // indicates volatile function
4259	case 'tAttrIf':
4260	case 'tAttrSkip':
4261	case 'tAttrChoose':
4262	// token is only important for Excel formula evaluator
4263	// do nothing
4264	break;
4265	case 'tAttrSpace': // space / carriage return
4266	// space will be used when next token arrives, do not alter formulaString stack
4267	/** @var string[][] $token */
4268	switch ($token['data']['spacetype']) {
4269	case 'type0':
4270	$space0 = str_repeat(' ', (int) $token['data']['spacecount']);
4271
4272	break;
4273	case 'type1':
4274	$space1 = str_repeat("\n", (int) $token['data']['spacecount']);
4275
4276	break;
4277	case 'type2':
4278	$space2 = str_repeat(' ', (int) $token['data']['spacecount']);
4279
4280	break;
4281	case 'type3':
4282	$space3 = str_repeat("\n", (int) $token['data']['spacecount']);
4283
4284	break;
4285	case 'type4':
4286	$space4 = str_repeat(' ', (int) $token['data']['spacecount']);
4287
4288	break;
4289	case 'type5':
4290	$space5 = str_repeat("\n", (int) $token['data']['spacecount']);
4291
4292	break;
4293	}
4294
4295	break;
4296	case 'tAttrSum': // SUM function with one parameter
4297	$op = array_pop($formulaStrings);
4298	$formulaStrings[] = "{$space1}{$space0}SUM($op)";
4299	unset($space0, $space1);
4300
4301	break;
4302	case 'tFunc': // function with fixed number of arguments
4303	case 'tFuncV': // function with variable number of arguments
4304	/** @var string[] */
4305	$temp1 = $token['data'];
4306	$temp2 = $temp1['function'];
4307	if ($temp2 != '') {
4308	// normal function
4309	$ops = []; // array of operators
4310	$temp3 = (int) $temp1['args'];
4311	for ($i = 0; $i < $temp3; ++$i) {
4312	$ops[] = array_pop($formulaStrings);
4313	}
4314	$ops = array_reverse($ops);
4315	$formulaStrings[] = "$space1$space0{$temp2}(" . implode(',', $ops) . ')';
4316	unset($space0, $space1);
4317	} else {
4318	// add-in function
4319	$ops = []; // array of operators
4320	/** @var int[] */
4321	$temp = $token['data'];
4322	for ($i = 0; $i < $temp['args'] - 1; ++$i) {
4323	$ops[] = array_pop($formulaStrings);
4324	}
4325	$ops = array_reverse($ops);
4326	$function = array_pop($formulaStrings);
4327	$formulaStrings[] = "$space1$space0$function(" . implode(',', $ops) . ')';
4328	unset($space0, $space1);
4329	}
4330
4331	break;
4332	case 'tParen': // parenthesis
4333	$expression = array_pop($formulaStrings);
4334	$formulaStrings[] = "$space3$space2($expression$space5$space4)";
4335	unset($space2, $space3, $space4, $space5);
4336
4337	break;
4338	case 'tArray': // array constant
4339	$constantArray = Xls\Biff8::readBIFF8ConstantArray($additionalData);
4340	$formulaStrings[] = $space1 . $space0 . $constantArray['value'];
4341	$additionalData = (string) substr($additionalData, $constantArray['size']); // bite of chunk of additional data
4342	unset($space0, $space1);
4343
4344	break;
4345	case 'tMemArea':
4346	// bite off chunk of additional data
4347	$cellRangeAddressList = Xls\Biff8::readBIFF8CellRangeAddressList($additionalData);
4348	$additionalData = (string) substr($additionalData, $cellRangeAddressList['size']);
4349	$formulaStrings[] = "$space1$space0{$tokenData}";
4350	unset($space0, $space1);
4351
4352	break;
4353	case 'tArea': // cell range address
4354	case 'tBool': // boolean
4355	case 'tErr': // error code
4356	case 'tInt': // integer
4357	case 'tMemErr':
4358	case 'tMemFunc':
4359	case 'tMissArg':
4360	case 'tName':
4361	case 'tNameX':
4362	case 'tNum': // number
4363	case 'tRef': // single cell reference
4364	case 'tRef3d': // 3d cell reference
4365	case 'tArea3d': // 3d cell range reference
4366	case 'tRefN':
4367	case 'tAreaN':
4368	case 'tStr': // string
4369	$formulaStrings[] = "$space1$space0{$tokenData}";
4370	unset($space0, $space1);
4371
4372	break;
4373	}
4374	}
4375	$formulaString = $formulaStrings[0];
4376
4377	return $formulaString;
4378	}
4379
4380	/**
4381	* Fetch next token from binary formula data.
4382	*
4383	* @param string $formulaData Formula data
4384	* @param string $baseCell Base cell, only needed when formula contains tRefN tokens, e.g. with shared formulas
4385	*
4386	* @return mixed[]
4387	*/
4388	private function getNextToken(string $formulaData, string $baseCell = 'A1'): array
4389	{
4390	// offset: 0; size: 1; token id
4391	$id = ord($formulaData[0]); // token id
4392	$name = false; // initialize token name
4393
4394	switch ($id) {
4395	case 0x03:
4396	$name = 'tAdd';
4397	$size = 1;
4398	$data = '+';
4399
4400	break;
4401	case 0x04:
4402	$name = 'tSub';
4403	$size = 1;
4404	$data = '-';
4405
4406	break;
4407	case 0x05:
4408	$name = 'tMul';
4409	$size = 1;
4410	$data = '*';
4411
4412	break;
4413	case 0x06:
4414	$name = 'tDiv';
4415	$size = 1;
4416	$data = '/';
4417
4418	break;
4419	case 0x07:
4420	$name = 'tPower';
4421	$size = 1;
4422	$data = '^';
4423
4424	break;
4425	case 0x08:
4426	$name = 'tConcat';
4427	$size = 1;
4428	$data = '&';
4429
4430	break;
4431	case 0x09:
4432	$name = 'tLT';
4433	$size = 1;
4434	$data = '<';
4435
4436	break;
4437	case 0x0A:
4438	$name = 'tLE';
4439	$size = 1;
4440	$data = '<=';
4441
4442	break;
4443	case 0x0B:
4444	$name = 'tEQ';
4445	$size = 1;
4446	$data = '=';
4447
4448	break;
4449	case 0x0C:
4450	$name = 'tGE';
4451	$size = 1;
4452	$data = '>=';
4453
4454	break;
4455	case 0x0D:
4456	$name = 'tGT';
4457	$size = 1;
4458	$data = '>';
4459
4460	break;
4461	case 0x0E:
4462	$name = 'tNE';
4463	$size = 1;
4464	$data = '<>';
4465
4466	break;
4467	case 0x0F:
4468	$name = 'tIsect';
4469	$size = 1;
4470	$data = ' ';
4471
4472	break;
4473	case 0x10:
4474	$name = 'tList';
4475	$size = 1;
4476	$data = ',';
4477
4478	break;
4479	case 0x11:
4480	$name = 'tRange';
4481	$size = 1;
4482	$data = ':';
4483
4484	break;
4485	case 0x12:
4486	$name = 'tUplus';
4487	$size = 1;
4488	$data = '+';
4489
4490	break;
4491	case 0x13:
4492	$name = 'tUminus';
4493	$size = 1;
4494	$data = '-';
4495
4496	break;
4497	case 0x14:
4498	$name = 'tPercent';
4499	$size = 1;
4500	$data = '%';
4501
4502	break;
4503	case 0x15: // parenthesis
4504	$name = 'tParen';
4505	$size = 1;
4506	$data = null;
4507
4508	break;
4509	case 0x16: // missing argument
4510	$name = 'tMissArg';
4511	$size = 1;
4512	$data = '';
4513
4514	break;
4515	case 0x17: // string
4516	$name = 'tStr';
4517	// offset: 1; size: var; Unicode string, 8-bit string length
4518	$string = self::readUnicodeStringShort((string) substr($formulaData, 1));
4519	$size = 1 + $string['size'];
4520	$data = self::UTF8toExcelDoubleQuoted($string['value']);
4521
4522	break;
4523	case 0x19: // Special attribute
4524	// offset: 1; size: 1; attribute type flags:
4525	switch (ord($formulaData[1])) {
4526	case 0x01:
4527	$name = 'tAttrVolatile';
4528	$size = 4;
4529	$data = null;
4530
4531	break;
4532	case 0x02:
4533	$name = 'tAttrIf';
4534	$size = 4;
4535	$data = null;
4536
4537	break;
4538	case 0x04:
4539	$name = 'tAttrChoose';
4540	// offset: 2; size: 2; number of choices in the CHOOSE function ($nc, number of parameters decreased by 1)
4541	$nc = self::getUInt2d($formulaData, 2);
4542	// offset: 4; size: 2 * $nc
4543	// offset: 4 + 2 * $nc; size: 2
4544	$size = 2 * $nc + 6;
4545	$data = null;
4546
4547	break;
4548	case 0x08:
4549	$name = 'tAttrSkip';
4550	$size = 4;
4551	$data = null;
4552
4553	break;
4554	case 0x10:
4555	$name = 'tAttrSum';
4556	$size = 4;
4557	$data = null;
4558
4559	break;
4560	case 0x40:
4561	case 0x41:
4562	$name = 'tAttrSpace';
4563	$size = 4;
4564	switch (ord($formulaData[2])) {
4565	case 0x00:
4566	$spacetype = 'type0';
4567	break;
4568	case 0x01:
4569	$spacetype = 'type1';
4570	break;
4571	case 0x02:
4572	$spacetype = 'type2';
4573	break;
4574	case 0x03:
4575	$spacetype = 'type3';
4576	break;
4577	case 0x04:
4578	$spacetype = 'type4';
4579	break;
4580	case 0x05:
4581	$spacetype = 'type5';
4582	break;
4583	default:
4584	throw new Exception('Unrecognized space type in tAttrSpace token');
4585	}
4586	// offset: 3; size: 1; number of inserted spaces/carriage returns
4587	$spacecount = ord($formulaData[3]);
4588
4589	$data = ['spacetype' => $spacetype, 'spacecount' => $spacecount];
4590
4591	break;
4592	default:
4593	throw new Exception('Unrecognized attribute flag in tAttr token');
4594	}
4595
4596	break;
4597	case 0x1C: // error code
4598	// offset: 1; size: 1; error code
4599	$name = 'tErr';
4600	$size = 2;
4601	$data = Xls\ErrorCode::lookup(ord($formulaData[1]));
4602
4603	break;
4604	case 0x1D: // boolean
4605	// offset: 1; size: 1; 0 = false, 1 = true;
4606	$name = 'tBool';
4607	$size = 2;
4608	$data = ord($formulaData[1]) ? 'TRUE' : 'FALSE';
4609
4610	break;
4611	case 0x1E: // integer
4612	// offset: 1; size: 2; unsigned 16-bit integer
4613	$name = 'tInt';
4614	$size = 3;
4615	$data = self::getUInt2d($formulaData, 1);
4616
4617	break;
4618	case 0x1F: // number
4619	// offset: 1; size: 8;
4620	$name = 'tNum';
4621	$size = 9;
4622	$data = self::extractNumber((string) substr($formulaData, 1));
4623	$data = str_replace(',', '.', (string) $data); // in case non-English locale
4624
4625	break;
4626	case 0x20: // array constant
4627	case 0x40:
4628	case 0x60:
4629	// offset: 1; size: 7; not used
4630	$name = 'tArray';
4631	$size = 8;
4632	$data = null;
4633
4634	break;
4635	case 0x21: // function with fixed number of arguments
4636	case 0x41:
4637	case 0x61:
4638	$name = 'tFunc';
4639	$size = 3;
4640	// offset: 1; size: 2; index to built-in sheet function
4641	$mapping = Xls\Mappings::TFUNC_MAPPINGS[self::getUInt2d($formulaData, 1)] ?? null;
4642	if ($mapping === null) {
4643	throw new Exception('Unrecognized function in formula');
4644	}
4645	$data = ['function' => $mapping[0], 'args' => $mapping[1]];
4646
4647	break;
4648	case 0x22: // function with variable number of arguments
4649	case 0x42:
4650	case 0x62:
4651	$name = 'tFuncV';
4652	$size = 4;
4653	// offset: 1; size: 1; number of arguments
4654	$args = ord($formulaData[1]);
4655	// offset: 2: size: 2; index to built-in sheet function
4656	$index = self::getUInt2d($formulaData, 2);
4657	$function = Xls\Mappings::TFUNCV_MAPPINGS[$index] ?? null;
4658	if ($function === null) {
4659	throw new Exception('Unrecognized function in formula');
4660	}
4661	$data = ['function' => $function, 'args' => $args];
4662
4663	break;
4664	case 0x23: // index to defined name
4665	case 0x43:
4666	case 0x63:
4667	$name = 'tName';
4668	$size = 5;
4669	// offset: 1; size: 2; one-based index to definedname record
4670	$definedNameIndex = self::getUInt2d($formulaData, 1) - 1;
4671	// offset: 2; size: 2; not used
4672	/** @var string[] */
4673	$data = $this->definedname[$definedNameIndex]['name'] ?? '';
4674
4675	break;
4676	case 0x24: // single cell reference e.g. A5
4677	case 0x44:
4678	case 0x64:
4679	$name = 'tRef';
4680	$size = 5;
4681	$data = Xls\Biff8::readBIFF8CellAddress((string) substr($formulaData, 1, 4));
4682
4683	break;
4684	case 0x25: // cell range reference to cells in the same sheet (2d)
4685	case 0x45:
4686	case 0x65:
4687	$name = 'tArea';
4688	$size = 9;
4689	$data = Xls\Biff8::readBIFF8CellRangeAddress((string) substr($formulaData, 1, 8));
4690
4691	break;
4692	case 0x26: // Constant reference sub-expression
4693	case 0x46:
4694	case 0x66:
4695	$name = 'tMemArea';
4696	// offset: 1; size: 4; not used
4697	// offset: 5; size: 2; size of the following subexpression
4698	$subSize = self::getUInt2d($formulaData, 5);
4699	$size = 7 + $subSize;
4700	$data = $this->getFormulaFromData((string) substr($formulaData, 7, $subSize));
4701
4702	break;
4703	case 0x27: // Deleted constant reference sub-expression
4704	case 0x47:
4705	case 0x67:
4706	$name = 'tMemErr';
4707	// offset: 1; size: 4; not used
4708	// offset: 5; size: 2; size of the following subexpression
4709	$subSize = self::getUInt2d($formulaData, 5);
4710	$size = 7 + $subSize;
4711	$data = $this->getFormulaFromData((string) substr($formulaData, 7, $subSize));
4712
4713	break;
4714	case 0x29: // Variable reference sub-expression
4715	case 0x49:
4716	case 0x69:
4717	$name = 'tMemFunc';
4718	// offset: 1; size: 2; size of the following sub-expression
4719	$subSize = self::getUInt2d($formulaData, 1);
4720	$size = 3 + $subSize;
4721	$data = $this->getFormulaFromData((string) substr($formulaData, 3, $subSize));
4722
4723	break;
4724	case 0x2C: // Relative 2d cell reference reference, used in shared formulas and some other places
4725	case 0x4C:
4726	case 0x6C:
4727	$name = 'tRefN';
4728	$size = 5;
4729	$data = Xls\Biff8::readBIFF8CellAddressB((string) substr($formulaData, 1, 4), $baseCell);
4730
4731	break;
4732	case 0x2D: // Relative 2d range reference
4733	case 0x4D:
4734	case 0x6D:
4735	$name = 'tAreaN';
4736	$size = 9;
4737	$data = Xls\Biff8::readBIFF8CellRangeAddressB((string) substr($formulaData, 1, 8), $baseCell);
4738
4739	break;
4740	case 0x39: // External name
4741	case 0x59:
4742	case 0x79:
4743	$name = 'tNameX';
4744	$size = 7;
4745	// offset: 1; size: 2; index to REF entry in EXTERNSHEET record
4746	// offset: 3; size: 2; one-based index to DEFINEDNAME or EXTERNNAME record
4747	$index = self::getUInt2d($formulaData, 3);
4748	// assume index is to EXTERNNAME record
4749	$data = $this->externalNames[$index - 1]['name'] ?? '';
4750
4751	// offset: 5; size: 2; not used
4752	break;
4753	case 0x3A: // 3d reference to cell
4754	case 0x5A:
4755	case 0x7A:
4756	$name = 'tRef3d';
4757	$size = 7;
4758
4759	try {
4760	// offset: 1; size: 2; index to REF entry
4761	$sheetRange = $this->readSheetRangeByRefIndex(self::getUInt2d($formulaData, 1));
4762	// offset: 3; size: 4; cell address
4763	$cellAddress = Xls\Biff8::readBIFF8CellAddress((string) substr($formulaData, 3, 4));
4764
4765	$data = "$sheetRange!$cellAddress";
4766	} catch (PhpSpreadsheetException $exception) {
4767	// deleted sheet reference
4768	$data = '#REF!';
4769	}
4770
4771	break;
4772	case 0x3B: // 3d reference to cell range
4773	case 0x5B:
4774	case 0x7B:
4775	$name = 'tArea3d';
4776	$size = 11;
4777
4778	try {
4779	// offset: 1; size: 2; index to REF entry
4780	$sheetRange = $this->readSheetRangeByRefIndex(self::getUInt2d($formulaData, 1));
4781	// offset: 3; size: 8; cell address
4782	$cellRangeAddress = Xls\Biff8::readBIFF8CellRangeAddress((string) substr($formulaData, 3, 8));
4783
4784	$data = "$sheetRange!$cellRangeAddress";
4785	} catch (PhpSpreadsheetException $exception) {
4786	// deleted sheet reference
4787	$data = '#REF!';
4788	}
4789
4790	break;
4791	// Unknown cases // don't know how to deal with
4792	default:
4793	throw new Exception('Unrecognized token ' . sprintf('%02X', $id) . ' in formula');
4794	}
4795
4796	return [
4797	'id' => $id,
4798	'name' => $name,
4799	'size' => $size,
4800	'data' => $data,
4801	];
4802	}
4803
4804	/**
4805	* Get a sheet range like Sheet1:Sheet3 from REF index
4806	* Note: If there is only one sheet in the range, one gets e.g Sheet1
4807	* It can also happen that the REF structure uses the -1 (FFFF) code to indicate deleted sheets,
4808	* in which case an Exception is thrown.
4809	* @return string\|false
4810	*/
4811	protected function readSheetRangeByRefIndex(int $index)
4812	{
4813	if (isset($this->ref[$index])) {
4814	$type = $this->externalBooks[$this->ref[$index]['externalBookIndex']]['type'];
4815
4816	switch ($type) {
4817	case 'internal':
4818	// check if we have a deleted 3d reference
4819	if ($this->ref[$index]['firstSheetIndex'] == 0xFFFF \|\| $this->ref[$index]['lastSheetIndex'] == 0xFFFF) {
4820	throw new Exception('Deleted sheet reference');
4821	}
4822
4823	// we have normal sheet range (collapsed or uncollapsed)
4824	$firstSheetName = $this->sheets[$this->ref[$index]['firstSheetIndex']]['name'];
4825	$lastSheetName = $this->sheets[$this->ref[$index]['lastSheetIndex']]['name'];
4826
4827	if ($firstSheetName == $lastSheetName) {
4828	// collapsed sheet range
4829	$sheetRange = $firstSheetName;
4830	} else {
4831	$sheetRange = "$firstSheetName:$lastSheetName";
4832	}
4833
4834	// escape the single-quotes
4835	$sheetRange = str_replace("'", "''", $sheetRange);
4836
4837	// if there are special characters, we need to enclose the range in single-quotes
4838	// todo: check if we have identified the whole set of special characters
4839	// it seems that the following characters are not accepted for sheet names
4840	// and we may assume that they are not present: []*/:\?
4841	// 'u' qualifier makes it risky to use Preg::isMatch here
4842	if (preg_match("/[ !\"@#£$%&{()}<>=+'\|^,;-]/u", $sheetRange)) {
4843	$sheetRange = "'$sheetRange'";
4844	}
4845
4846	return $sheetRange;
4847	default:
4848	// TODO: external sheet support
4849	throw new Exception('Xls reader only supports internal sheets in formulas');
4850	}
4851	}
4852
4853	return false;
4854	}
4855
4856	/**
4857	* Read byte string (8-bit string length)
4858	* OpenOffice documentation: 2.5.2.
4859	*
4860	* @return array{value: mixed, size: int}
4861	*/
4862	protected function readByteStringShort(string $subData): array
4863	{
4864	// offset: 0; size: 1; length of the string (character count)
4865	$ln = ord($subData[0]);
4866
4867	// offset: 1: size: var; character array (8-bit characters)
4868	$value = $this->decodeCodepage((string) substr($subData, 1, $ln));
4869
4870	return [
4871	'value' => $value,
4872	'size' => 1 + $ln, // size in bytes of data structure
4873	];
4874	}
4875
4876	/**
4877	* Read byte string (16-bit string length)
4878	* OpenOffice documentation: 2.5.2.
4879	*
4880	* @return array{value: mixed, size: int}
4881	*/
4882	protected function readByteStringLong(string $subData): array
4883	{
4884	// offset: 0; size: 2; length of the string (character count)
4885	$ln = self::getUInt2d($subData, 0);
4886
4887	// offset: 2: size: var; character array (8-bit characters)
4888	$value = $this->decodeCodepage((string) substr($subData, 2));
4889
4890	//return $string;
4891	return [
4892	'value' => $value,
4893	'size' => 2 + $ln, // size in bytes of data structure
4894	];
4895	}
4896
4897	protected function parseRichText(string $is): RichText
4898	{
4899	$value = new RichText();
4900	$value->createText($is);
4901
4902	return $value;
4903	}
4904
4905	/**
4906	* Phpstan 1.4.4 complains that this property is never read.
4907	* So, we might be able to get rid of it altogether.
4908	* For now, however, this function makes it readable,
4909	* which satisfies Phpstan.
4910	*
4911	* @return mixed[]
4912	*
4913	* @codeCoverageIgnore
4914	*/
4915	public function getMapCellStyleXfIndex(): array
4916	{
4917	return $this->mapCellStyleXfIndex;
4918	}
4919
4920	/**
4921	* Parse conditional formatting blocks.
4922	*
4923	* @see https://www.openoffice.org/sc/excelfileformat.pdf Search for CFHEADER followed by CFRULE
4924	*
4925	* @return mixed[]
4926	*/
4927	protected function readCFHeader(): array
4928	{
4929	return (new Xls\ConditionalFormatting())->readCFHeader2($this);
4930	}
4931
4932	/** @param string[] $cellRangeAddresses */
4933	protected function readCFRule(array $cellRangeAddresses): void
4934	{
4935	(new Xls\ConditionalFormatting())->readCFRule2($cellRangeAddresses, $this);
4936	}
4937
4938	public function getVersion(): int
4939	{
4940	return $this->version;
4941	}
4942	}
4943