visualizer / vendor / phpoffice / phpspreadsheet / src / PhpSpreadsheet / Reader / Xls.php

Xls.php in Visualizer – Tables & Charts Manager with Built-in AI Generator 3.11.0, at vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Reader/Xls.php

7,950 lines 279.2 KB

Raw Download Zip

1	<?php
2
3	namespace PhpOffice\PhpSpreadsheet\Reader;
4
5	use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
6	use PhpOffice\PhpSpreadsheet\Cell\DataType;
7	use PhpOffice\PhpSpreadsheet\Cell\DataValidation;
8	use PhpOffice\PhpSpreadsheet\Exception as PhpSpreadsheetException;
9	use PhpOffice\PhpSpreadsheet\NamedRange;
10	use PhpOffice\PhpSpreadsheet\RichText\RichText;
11	use PhpOffice\PhpSpreadsheet\Shared\CodePage;
12	use PhpOffice\PhpSpreadsheet\Shared\Date;
13	use PhpOffice\PhpSpreadsheet\Shared\Escher;
14	use PhpOffice\PhpSpreadsheet\Shared\Escher\DggContainer\BstoreContainer\BSE;
15	use PhpOffice\PhpSpreadsheet\Shared\File;
16	use PhpOffice\PhpSpreadsheet\Shared\OLE;
17	use PhpOffice\PhpSpreadsheet\Shared\OLERead;
18	use PhpOffice\PhpSpreadsheet\Shared\StringHelper;
19	use PhpOffice\PhpSpreadsheet\Spreadsheet;
20	use PhpOffice\PhpSpreadsheet\Style\Alignment;
21	use PhpOffice\PhpSpreadsheet\Style\Borders;
22	use PhpOffice\PhpSpreadsheet\Style\Font;
23	use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
24	use PhpOffice\PhpSpreadsheet\Style\Protection;
25	use PhpOffice\PhpSpreadsheet\Style\Style;
26	use PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawing;
27	use PhpOffice\PhpSpreadsheet\Worksheet\PageSetup;
28	use PhpOffice\PhpSpreadsheet\Worksheet\SheetView;
29	use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
30
31	// Original file header of ParseXL (used as the base for this class):
32	// --------------------------------------------------------------------------------
33	// Adapted from Excel_Spreadsheet_Reader developed by users bizon153,
34	// trex005, and mmp11 (SourceForge.net)
35	// https://sourceforge.net/projects/phpexcelreader/
36	// Primary changes made by canyoncasa (dvc) for ParseXL 1.00 ...
37	// Modelled moreso after Perl Excel Parse/Write modules
38	// Added Parse_Excel_Spreadsheet object
39	// Reads a whole worksheet or tab as row,column array or as
40	// associated hash of indexed rows and named column fields
41	// Added variables for worksheet (tab) indexes and names
42	// Added an object call for loading individual woorksheets
43	// Changed default indexing defaults to 0 based arrays
44	// Fixed date/time and percent formats
45	// Includes patches found at SourceForge...
46	// unicode patch by nobody
47	// unpack("d") machine depedency patch by matchy
48	// boundsheet utf16 patch by bjaenichen
49	// Renamed functions for shorter names
50	// General code cleanup and rigor, including <80 column width
51	// Included a testcase Excel file and PHP example calls
52	// Code works for PHP 5.x
53
54	// Primary changes made by canyoncasa (dvc) for ParseXL 1.10 ...
55	// http://sourceforge.net/tracker/index.php?func=detail&aid=1466964&group_id=99160&atid=623334
56	// Decoding of formula conditions, results, and tokens.
57	// Support for user-defined named cells added as an array "namedcells"
58	// Patch code for user-defined named cells supports single cells only.
59	// NOTE: this patch only works for BIFF8 as BIFF5-7 use a different
60	// external sheet reference structure
61	class Xls extends BaseReader
62	{
63	// ParseXL definitions
64	const XLS_BIFF8 = 0x0600;
65	const XLS_BIFF7 = 0x0500;
66	const XLS_WORKBOOKGLOBALS = 0x0005;
67	const XLS_WORKSHEET = 0x0010;
68
69	// record identifiers
70	const XLS_TYPE_FORMULA = 0x0006;
71	const XLS_TYPE_EOF = 0x000a;
72	const XLS_TYPE_PROTECT = 0x0012;
73	const XLS_TYPE_OBJECTPROTECT = 0x0063;
74	const XLS_TYPE_SCENPROTECT = 0x00dd;
75	const XLS_TYPE_PASSWORD = 0x0013;
76	const XLS_TYPE_HEADER = 0x0014;
77	const XLS_TYPE_FOOTER = 0x0015;
78	const XLS_TYPE_EXTERNSHEET = 0x0017;
79	const XLS_TYPE_DEFINEDNAME = 0x0018;
80	const XLS_TYPE_VERTICALPAGEBREAKS = 0x001a;
81	const XLS_TYPE_HORIZONTALPAGEBREAKS = 0x001b;
82	const XLS_TYPE_NOTE = 0x001c;
83	const XLS_TYPE_SELECTION = 0x001d;
84	const XLS_TYPE_DATEMODE = 0x0022;
85	const XLS_TYPE_EXTERNNAME = 0x0023;
86	const XLS_TYPE_LEFTMARGIN = 0x0026;
87	const XLS_TYPE_RIGHTMARGIN = 0x0027;
88	const XLS_TYPE_TOPMARGIN = 0x0028;
89	const XLS_TYPE_BOTTOMMARGIN = 0x0029;
90	const XLS_TYPE_PRINTGRIDLINES = 0x002b;
91	const XLS_TYPE_FILEPASS = 0x002f;
92	const XLS_TYPE_FONT = 0x0031;
93	const XLS_TYPE_CONTINUE = 0x003c;
94	const XLS_TYPE_PANE = 0x0041;
95	const XLS_TYPE_CODEPAGE = 0x0042;
96	const XLS_TYPE_DEFCOLWIDTH = 0x0055;
97	const XLS_TYPE_OBJ = 0x005d;
98	const XLS_TYPE_COLINFO = 0x007d;
99	const XLS_TYPE_IMDATA = 0x007f;
100	const XLS_TYPE_SHEETPR = 0x0081;
101	const XLS_TYPE_HCENTER = 0x0083;
102	const XLS_TYPE_VCENTER = 0x0084;
103	const XLS_TYPE_SHEET = 0x0085;
104	const XLS_TYPE_PALETTE = 0x0092;
105	const XLS_TYPE_SCL = 0x00a0;
106	const XLS_TYPE_PAGESETUP = 0x00a1;
107	const XLS_TYPE_MULRK = 0x00bd;
108	const XLS_TYPE_MULBLANK = 0x00be;
109	const XLS_TYPE_DBCELL = 0x00d7;
110	const XLS_TYPE_XF = 0x00e0;
111	const XLS_TYPE_MERGEDCELLS = 0x00e5;
112	const XLS_TYPE_MSODRAWINGGROUP = 0x00eb;
113	const XLS_TYPE_MSODRAWING = 0x00ec;
114	const XLS_TYPE_SST = 0x00fc;
115	const XLS_TYPE_LABELSST = 0x00fd;
116	const XLS_TYPE_EXTSST = 0x00ff;
117	const XLS_TYPE_EXTERNALBOOK = 0x01ae;
118	const XLS_TYPE_DATAVALIDATIONS = 0x01b2;
119	const XLS_TYPE_TXO = 0x01b6;
120	const XLS_TYPE_HYPERLINK = 0x01b8;
121	const XLS_TYPE_DATAVALIDATION = 0x01be;
122	const XLS_TYPE_DIMENSION = 0x0200;
123	const XLS_TYPE_BLANK = 0x0201;
124	const XLS_TYPE_NUMBER = 0x0203;
125	const XLS_TYPE_LABEL = 0x0204;
126	const XLS_TYPE_BOOLERR = 0x0205;
127	const XLS_TYPE_STRING = 0x0207;
128	const XLS_TYPE_ROW = 0x0208;
129	const XLS_TYPE_INDEX = 0x020b;
130	const XLS_TYPE_ARRAY = 0x0221;
131	const XLS_TYPE_DEFAULTROWHEIGHT = 0x0225;
132	const XLS_TYPE_WINDOW2 = 0x023e;
133	const XLS_TYPE_RK = 0x027e;
134	const XLS_TYPE_STYLE = 0x0293;
135	const XLS_TYPE_FORMAT = 0x041e;
136	const XLS_TYPE_SHAREDFMLA = 0x04bc;
137	const XLS_TYPE_BOF = 0x0809;
138	const XLS_TYPE_SHEETPROTECTION = 0x0867;
139	const XLS_TYPE_RANGEPROTECTION = 0x0868;
140	const XLS_TYPE_SHEETLAYOUT = 0x0862;
141	const XLS_TYPE_XFEXT = 0x087d;
142	const XLS_TYPE_PAGELAYOUTVIEW = 0x088b;
143	const XLS_TYPE_UNKNOWN = 0xffff;
144
145	// Encryption type
146	const MS_BIFF_CRYPTO_NONE = 0;
147	const MS_BIFF_CRYPTO_XOR = 1;
148	const MS_BIFF_CRYPTO_RC4 = 2;
149
150	// Size of stream blocks when using RC4 encryption
151	const REKEY_BLOCK = 0x400;
152
153	/**
154	* Summary Information stream data.
155	*
156	* @var string
157	*/
158	private $summaryInformation;
159
160	/**
161	* Extended Summary Information stream data.
162	*
163	* @var string
164	*/
165	private $documentSummaryInformation;
166
167	/**
168	* Workbook stream data. (Includes workbook globals substream as well as sheet substreams).
169	*
170	* @var string
171	*/
172	private $data;
173
174	/**
175	* Size in bytes of $this->data.
176	*
177	* @var int
178	*/
179	private $dataSize;
180
181	/**
182	* Current position in stream.
183	*
184	* @var int
185	*/
186	private $pos;
187
188	/**
189	* Workbook to be returned by the reader.
190	*
191	* @var Spreadsheet
192	*/
193	private $spreadsheet;
194
195	/**
196	* Worksheet that is currently being built by the reader.
197	*
198	* @var Worksheet
199	*/
200	private $phpSheet;
201
202	/**
203	* BIFF version.
204	*
205	* @var int
206	*/
207	private $version;
208
209	/**
210	* Codepage set in the Excel file being read. Only important for BIFF5 (Excel 5.0 - Excel 95)
211	* For BIFF8 (Excel 97 - Excel 2003) this will always have the value 'UTF-16LE'.
212	*
213	* @var string
214	*/
215	private $codepage;
216
217	/**
218	* Shared formats.
219	*
220	* @var array
221	*/
222	private $formats;
223
224	/**
225	* Shared fonts.
226	*
227	* @var array
228	*/
229	private $objFonts;
230
231	/**
232	* Color palette.
233	*
234	* @var array
235	*/
236	private $palette;
237
238	/**
239	* Worksheets.
240	*
241	* @var array
242	*/
243	private $sheets;
244
245	/**
246	* External books.
247	*
248	* @var array
249	*/
250	private $externalBooks;
251
252	/**
253	* REF structures. Only applies to BIFF8.
254	*
255	* @var array
256	*/
257	private $ref;
258
259	/**
260	* External names.
261	*
262	* @var array
263	*/
264	private $externalNames;
265
266	/**
267	* Defined names.
268	*
269	* @var array
270	*/
271	private $definedname;
272
273	/**
274	* Shared strings. Only applies to BIFF8.
275	*
276	* @var array
277	*/
278	private $sst;
279
280	/**
281	* Panes are frozen? (in sheet currently being read). See WINDOW2 record.
282	*
283	* @var bool
284	*/
285	private $frozen;
286
287	/**
288	* Fit printout to number of pages? (in sheet currently being read). See SHEETPR record.
289	*
290	* @var bool
291	*/
292	private $isFitToPages;
293
294	/**
295	* Objects. One OBJ record contributes with one entry.
296	*
297	* @var array
298	*/
299	private $objs;
300
301	/**
302	* Text Objects. One TXO record corresponds with one entry.
303	*
304	* @var array
305	*/
306	private $textObjects;
307
308	/**
309	* Cell Annotations (BIFF8).
310	*
311	* @var array
312	*/
313	private $cellNotes;
314
315	/**
316	* The combined MSODRAWINGGROUP data.
317	*
318	* @var string
319	*/
320	private $drawingGroupData;
321
322	/**
323	* The combined MSODRAWING data (per sheet).
324	*
325	* @var string
326	*/
327	private $drawingData;
328
329	/**
330	* Keep track of XF index.
331	*
332	* @var int
333	*/
334	private $xfIndex;
335
336	/**
337	* Mapping of XF index (that is a cell XF) to final index in cellXf collection.
338	*
339	* @var array
340	*/
341	private $mapCellXfIndex;
342
343	/**
344	* Mapping of XF index (that is a style XF) to final index in cellStyleXf collection.
345	*
346	* @var array
347	*/
348	private $mapCellStyleXfIndex;
349
350	/**
351	* The shared formulas in a sheet. One SHAREDFMLA record contributes with one value.
352	*
353	* @var array
354	*/
355	private $sharedFormulas;
356
357	/**
358	* The shared formula parts in a sheet. One FORMULA record contributes with one value if it
359	* refers to a shared formula.
360	*
361	* @var array
362	*/
363	private $sharedFormulaParts;
364
365	/**
366	* The type of encryption in use.
367	*
368	* @var int
369	*/
370	private $encryption = 0;
371
372	/**
373	* The position in the stream after which contents are encrypted.
374	*
375	* @var int
376	*/
377	private $encryptionStartPos = false;
378
379	/**
380	* The current RC4 decryption object.
381	*
382	* @var Xls\RC4
383	*/
384	private $rc4Key;
385
386	/**
387	* The position in the stream that the RC4 decryption object was left at.
388	*
389	* @var int
390	*/
391	private $rc4Pos = 0;
392
393	/**
394	* The current MD5 context state.
395	*
396	* @var string
397	*/
398	private $md5Ctxt;
399
400	/**
401	* @var int
402	*/
403	private $textObjRef;
404
405	/**
406	* @var string
407	*/
408	private $baseCell;
409
410	/**
411	* Create a new Xls Reader instance.
412	*/
413	public function __construct()
414	{
415	parent::__construct();
416	}
417
418	/**
419	* Can the current IReader read the file?
420	*
421	* @param string $pFilename
422	*
423	* @return bool
424	*/
425	public function canRead($pFilename)
426	{
427	File::assertFile($pFilename);
428
429	try {
430	// Use ParseXL for the hard work.
431	$ole = new OLERead();
432
433	// get excel data
434	$ole->read($pFilename);
435
436	return true;
437	} catch (PhpSpreadsheetException $e) {
438	return false;
439	}
440	}
441
442	/**
443	* Reads names of the worksheets from a file, without parsing the whole file to a PhpSpreadsheet object.
444	*
445	* @param string $pFilename
446	*
447	* @throws Exception
448	*
449	* @return array
450	*/
451	public function listWorksheetNames($pFilename)
452	{
453	File::assertFile($pFilename);
454
455	$worksheetNames = [];
456
457	// Read the OLE file
458	$this->loadOLE($pFilename);
459
460	// total byte size of Excel data (workbook global substream + sheet substreams)
461	$this->dataSize = strlen($this->data);
462
463	$this->pos = 0;
464	$this->sheets = [];
465
466	// Parse Workbook Global Substream
467	while ($this->pos < $this->dataSize) {
468	$code = self::getUInt2d($this->data, $this->pos);
469
470	switch ($code) {
471	case self::XLS_TYPE_BOF:
472	$this->readBof();
473
474	break;
475	case self::XLS_TYPE_SHEET:
476	$this->readSheet();
477
478	break;
479	case self::XLS_TYPE_EOF:
480	$this->readDefault();
481
482	break 2;
483	default:
484	$this->readDefault();
485
486	break;
487	}
488	}
489
490	foreach ($this->sheets as $sheet) {
491	if ($sheet['sheetType'] != 0x00) {
492	// 0x00: Worksheet, 0x02: Chart, 0x06: Visual Basic module
493	continue;
494	}
495
496	$worksheetNames[] = $sheet['name'];
497	}
498
499	return $worksheetNames;
500	}
501
502	/**
503	* Return worksheet info (Name, Last Column Letter, Last Column Index, Total Rows, Total Columns).
504	*
505	* @param string $pFilename
506	*
507	* @throws Exception
508	*
509	* @return array
510	*/
511	public function listWorksheetInfo($pFilename)
512	{
513	File::assertFile($pFilename);
514
515	$worksheetInfo = [];
516
517	// Read the OLE file
518	$this->loadOLE($pFilename);
519
520	// total byte size of Excel data (workbook global substream + sheet substreams)
521	$this->dataSize = strlen($this->data);
522
523	// initialize
524	$this->pos = 0;
525	$this->sheets = [];
526
527	// Parse Workbook Global Substream
528	while ($this->pos < $this->dataSize) {
529	$code = self::getUInt2d($this->data, $this->pos);
530
531	switch ($code) {
532	case self::XLS_TYPE_BOF:
533	$this->readBof();
534
535	break;
536	case self::XLS_TYPE_SHEET:
537	$this->readSheet();
538
539	break;
540	case self::XLS_TYPE_EOF:
541	$this->readDefault();
542
543	break 2;
544	default:
545	$this->readDefault();
546
547	break;
548	}
549	}
550
551	// Parse the individual sheets
552	foreach ($this->sheets as $sheet) {
553	if ($sheet['sheetType'] != 0x00) {
554	// 0x00: Worksheet
555	// 0x02: Chart
556	// 0x06: Visual Basic module
557	continue;
558	}
559
560	$tmpInfo = [];
561	$tmpInfo['worksheetName'] = $sheet['name'];
562	$tmpInfo['lastColumnLetter'] = 'A';
563	$tmpInfo['lastColumnIndex'] = 0;
564	$tmpInfo['totalRows'] = 0;
565	$tmpInfo['totalColumns'] = 0;
566
567	$this->pos = $sheet['offset'];
568
569	while ($this->pos <= $this->dataSize - 4) {
570	$code = self::getUInt2d($this->data, $this->pos);
571
572	switch ($code) {
573	case self::XLS_TYPE_RK:
574	case self::XLS_TYPE_LABELSST:
575	case self::XLS_TYPE_NUMBER:
576	case self::XLS_TYPE_FORMULA:
577	case self::XLS_TYPE_BOOLERR:
578	case self::XLS_TYPE_LABEL:
579	$length = self::getUInt2d($this->data, $this->pos + 2);
580	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
581
582	// move stream pointer to next record
583	$this->pos += 4 + $length;
584
585	$rowIndex = self::getUInt2d($recordData, 0) + 1;
586	$columnIndex = self::getUInt2d($recordData, 2);
587
588	$tmpInfo['totalRows'] = max($tmpInfo['totalRows'], $rowIndex);
589	$tmpInfo['lastColumnIndex'] = max($tmpInfo['lastColumnIndex'], $columnIndex);
590
591	break;
592	case self::XLS_TYPE_BOF:
593	$this->readBof();
594
595	break;
596	case self::XLS_TYPE_EOF:
597	$this->readDefault();
598
599	break 2;
600	default:
601	$this->readDefault();
602
603	break;
604	}
605	}
606
607	$tmpInfo['lastColumnLetter'] = Coordinate::stringFromColumnIndex($tmpInfo['lastColumnIndex'] + 1);
608	$tmpInfo['totalColumns'] = $tmpInfo['lastColumnIndex'] + 1;
609
610	$worksheetInfo[] = $tmpInfo;
611	}
612
613	return $worksheetInfo;
614	}
615
616	/**
617	* Loads PhpSpreadsheet from file.
618	*
619	* @param string $pFilename
620	*
621	* @throws Exception
622	*
623	* @return Spreadsheet
624	*/
625	public function load($pFilename)
626	{
627	// Read the OLE file
628	$this->loadOLE($pFilename);
629
630	// Initialisations
631	$this->spreadsheet = new Spreadsheet();
632	$this->spreadsheet->removeSheetByIndex(0); // remove 1st sheet
633	if (!$this->readDataOnly) {
634	$this->spreadsheet->removeCellStyleXfByIndex(0); // remove the default style
635	$this->spreadsheet->removeCellXfByIndex(0); // remove the default style
636	}
637
638	// Read the summary information stream (containing meta data)
639	$this->readSummaryInformation();
640
641	// Read the Additional document summary information stream (containing application-specific meta data)
642	$this->readDocumentSummaryInformation();
643
644	// total byte size of Excel data (workbook global substream + sheet substreams)
645	$this->dataSize = strlen($this->data);
646
647	// initialize
648	$this->pos = 0;
649	$this->codepage = 'CP1252';
650	$this->formats = [];
651	$this->objFonts = [];
652	$this->palette = [];
653	$this->sheets = [];
654	$this->externalBooks = [];
655	$this->ref = [];
656	$this->definedname = [];
657	$this->sst = [];
658	$this->drawingGroupData = '';
659	$this->xfIndex = '';
660	$this->mapCellXfIndex = [];
661	$this->mapCellStyleXfIndex = [];
662
663	// Parse Workbook Global Substream
664	while ($this->pos < $this->dataSize) {
665	$code = self::getUInt2d($this->data, $this->pos);
666
667	switch ($code) {
668	case self::XLS_TYPE_BOF:
669	$this->readBof();
670
671	break;
672	case self::XLS_TYPE_FILEPASS:
673	$this->readFilepass();
674
675	break;
676	case self::XLS_TYPE_CODEPAGE:
677	$this->readCodepage();
678
679	break;
680	case self::XLS_TYPE_DATEMODE:
681	$this->readDateMode();
682
683	break;
684	case self::XLS_TYPE_FONT:
685	$this->readFont();
686
687	break;
688	case self::XLS_TYPE_FORMAT:
689	$this->readFormat();
690
691	break;
692	case self::XLS_TYPE_XF:
693	$this->readXf();
694
695	break;
696	case self::XLS_TYPE_XFEXT:
697	$this->readXfExt();
698
699	break;
700	case self::XLS_TYPE_STYLE:
701	$this->readStyle();
702
703	break;
704	case self::XLS_TYPE_PALETTE:
705	$this->readPalette();
706
707	break;
708	case self::XLS_TYPE_SHEET:
709	$this->readSheet();
710
711	break;
712	case self::XLS_TYPE_EXTERNALBOOK:
713	$this->readExternalBook();
714
715	break;
716	case self::XLS_TYPE_EXTERNNAME:
717	$this->readExternName();
718
719	break;
720	case self::XLS_TYPE_EXTERNSHEET:
721	$this->readExternSheet();
722
723	break;
724	case self::XLS_TYPE_DEFINEDNAME:
725	$this->readDefinedName();
726
727	break;
728	case self::XLS_TYPE_MSODRAWINGGROUP:
729	$this->readMsoDrawingGroup();
730
731	break;
732	case self::XLS_TYPE_SST:
733	$this->readSst();
734
735	break;
736	case self::XLS_TYPE_EOF:
737	$this->readDefault();
738
739	break 2;
740	default:
741	$this->readDefault();
742
743	break;
744	}
745	}
746
747	// Resolve indexed colors for font, fill, and border colors
748	// Cannot be resolved already in XF record, because PALETTE record comes afterwards
749	if (!$this->readDataOnly) {
750	foreach ($this->objFonts as $objFont) {
751	if (isset($objFont->colorIndex)) {
752	$color = Xls\Color::map($objFont->colorIndex, $this->palette, $this->version);
753	$objFont->getColor()->setRGB($color['rgb']);
754	}
755	}
756
757	foreach ($this->spreadsheet->getCellXfCollection() as $objStyle) {
758	// fill start and end color
759	$fill = $objStyle->getFill();
760
761	if (isset($fill->startcolorIndex)) {
762	$startColor = Xls\Color::map($fill->startcolorIndex, $this->palette, $this->version);
763	$fill->getStartColor()->setRGB($startColor['rgb']);
764	}
765	if (isset($fill->endcolorIndex)) {
766	$endColor = Xls\Color::map($fill->endcolorIndex, $this->palette, $this->version);
767	$fill->getEndColor()->setRGB($endColor['rgb']);
768	}
769
770	// border colors
771	$top = $objStyle->getBorders()->getTop();
772	$right = $objStyle->getBorders()->getRight();
773	$bottom = $objStyle->getBorders()->getBottom();
774	$left = $objStyle->getBorders()->getLeft();
775	$diagonal = $objStyle->getBorders()->getDiagonal();
776
777	if (isset($top->colorIndex)) {
778	$borderTopColor = Xls\Color::map($top->colorIndex, $this->palette, $this->version);
779	$top->getColor()->setRGB($borderTopColor['rgb']);
780	}
781	if (isset($right->colorIndex)) {
782	$borderRightColor = Xls\Color::map($right->colorIndex, $this->palette, $this->version);
783	$right->getColor()->setRGB($borderRightColor['rgb']);
784	}
785	if (isset($bottom->colorIndex)) {
786	$borderBottomColor = Xls\Color::map($bottom->colorIndex, $this->palette, $this->version);
787	$bottom->getColor()->setRGB($borderBottomColor['rgb']);
788	}
789	if (isset($left->colorIndex)) {
790	$borderLeftColor = Xls\Color::map($left->colorIndex, $this->palette, $this->version);
791	$left->getColor()->setRGB($borderLeftColor['rgb']);
792	}
793	if (isset($diagonal->colorIndex)) {
794	$borderDiagonalColor = Xls\Color::map($diagonal->colorIndex, $this->palette, $this->version);
795	$diagonal->getColor()->setRGB($borderDiagonalColor['rgb']);
796	}
797	}
798	}
799
800	// treat MSODRAWINGGROUP records, workbook-level Escher
801	if (!$this->readDataOnly && $this->drawingGroupData) {
802	$escherWorkbook = new Escher();
803	$reader = new Xls\Escher($escherWorkbook);
804	$escherWorkbook = $reader->load($this->drawingGroupData);
805	}
806
807	// Parse the individual sheets
808	foreach ($this->sheets as $sheet) {
809	if ($sheet['sheetType'] != 0x00) {
810	// 0x00: Worksheet, 0x02: Chart, 0x06: Visual Basic module
811	continue;
812	}
813
814	// check if sheet should be skipped
815	if (isset($this->loadSheetsOnly) && !in_array($sheet['name'], $this->loadSheetsOnly)) {
816	continue;
817	}
818
819	// add sheet to PhpSpreadsheet object
820	$this->phpSheet = $this->spreadsheet->createSheet();
821	// Use false for $updateFormulaCellReferences to prevent adjustment of worksheet references in formula
822	// cells... during the load, all formulae should be correct, and we're simply bringing the worksheet
823	// name in line with the formula, not the reverse
824	$this->phpSheet->setTitle($sheet['name'], false, false);
825	$this->phpSheet->setSheetState($sheet['sheetState']);
826
827	$this->pos = $sheet['offset'];
828
829	// Initialize isFitToPages. May change after reading SHEETPR record.
830	$this->isFitToPages = false;
831
832	// Initialize drawingData
833	$this->drawingData = '';
834
835	// Initialize objs
836	$this->objs = [];
837
838	// Initialize shared formula parts
839	$this->sharedFormulaParts = [];
840
841	// Initialize shared formulas
842	$this->sharedFormulas = [];
843
844	// Initialize text objs
845	$this->textObjects = [];
846
847	// Initialize cell annotations
848	$this->cellNotes = [];
849	$this->textObjRef = -1;
850
851	while ($this->pos <= $this->dataSize - 4) {
852	$code = self::getUInt2d($this->data, $this->pos);
853
854	switch ($code) {
855	case self::XLS_TYPE_BOF:
856	$this->readBof();
857
858	break;
859	case self::XLS_TYPE_PRINTGRIDLINES:
860	$this->readPrintGridlines();
861
862	break;
863	case self::XLS_TYPE_DEFAULTROWHEIGHT:
864	$this->readDefaultRowHeight();
865
866	break;
867	case self::XLS_TYPE_SHEETPR:
868	$this->readSheetPr();
869
870	break;
871	case self::XLS_TYPE_HORIZONTALPAGEBREAKS:
872	$this->readHorizontalPageBreaks();
873
874	break;
875	case self::XLS_TYPE_VERTICALPAGEBREAKS:
876	$this->readVerticalPageBreaks();
877
878	break;
879	case self::XLS_TYPE_HEADER:
880	$this->readHeader();
881
882	break;
883	case self::XLS_TYPE_FOOTER:
884	$this->readFooter();
885
886	break;
887	case self::XLS_TYPE_HCENTER:
888	$this->readHcenter();
889
890	break;
891	case self::XLS_TYPE_VCENTER:
892	$this->readVcenter();
893
894	break;
895	case self::XLS_TYPE_LEFTMARGIN:
896	$this->readLeftMargin();
897
898	break;
899	case self::XLS_TYPE_RIGHTMARGIN:
900	$this->readRightMargin();
901
902	break;
903	case self::XLS_TYPE_TOPMARGIN:
904	$this->readTopMargin();
905
906	break;
907	case self::XLS_TYPE_BOTTOMMARGIN:
908	$this->readBottomMargin();
909
910	break;
911	case self::XLS_TYPE_PAGESETUP:
912	$this->readPageSetup();
913
914	break;
915	case self::XLS_TYPE_PROTECT:
916	$this->readProtect();
917
918	break;
919	case self::XLS_TYPE_SCENPROTECT:
920	$this->readScenProtect();
921
922	break;
923	case self::XLS_TYPE_OBJECTPROTECT:
924	$this->readObjectProtect();
925
926	break;
927	case self::XLS_TYPE_PASSWORD:
928	$this->readPassword();
929
930	break;
931	case self::XLS_TYPE_DEFCOLWIDTH:
932	$this->readDefColWidth();
933
934	break;
935	case self::XLS_TYPE_COLINFO:
936	$this->readColInfo();
937
938	break;
939	case self::XLS_TYPE_DIMENSION:
940	$this->readDefault();
941
942	break;
943	case self::XLS_TYPE_ROW:
944	$this->readRow();
945
946	break;
947	case self::XLS_TYPE_DBCELL:
948	$this->readDefault();
949
950	break;
951	case self::XLS_TYPE_RK:
952	$this->readRk();
953
954	break;
955	case self::XLS_TYPE_LABELSST:
956	$this->readLabelSst();
957
958	break;
959	case self::XLS_TYPE_MULRK:
960	$this->readMulRk();
961
962	break;
963	case self::XLS_TYPE_NUMBER:
964	$this->readNumber();
965
966	break;
967	case self::XLS_TYPE_FORMULA:
968	$this->readFormula();
969
970	break;
971	case self::XLS_TYPE_SHAREDFMLA:
972	$this->readSharedFmla();
973
974	break;
975	case self::XLS_TYPE_BOOLERR:
976	$this->readBoolErr();
977
978	break;
979	case self::XLS_TYPE_MULBLANK:
980	$this->readMulBlank();
981
982	break;
983	case self::XLS_TYPE_LABEL:
984	$this->readLabel();
985
986	break;
987	case self::XLS_TYPE_BLANK:
988	$this->readBlank();
989
990	break;
991	case self::XLS_TYPE_MSODRAWING:
992	$this->readMsoDrawing();
993
994	break;
995	case self::XLS_TYPE_OBJ:
996	$this->readObj();
997
998	break;
999	case self::XLS_TYPE_WINDOW2:
1000	$this->readWindow2();
1001
1002	break;
1003	case self::XLS_TYPE_PAGELAYOUTVIEW:
1004	$this->readPageLayoutView();
1005
1006	break;
1007	case self::XLS_TYPE_SCL:
1008	$this->readScl();
1009
1010	break;
1011	case self::XLS_TYPE_PANE:
1012	$this->readPane();
1013
1014	break;
1015	case self::XLS_TYPE_SELECTION:
1016	$this->readSelection();
1017
1018	break;
1019	case self::XLS_TYPE_MERGEDCELLS:
1020	$this->readMergedCells();
1021
1022	break;
1023	case self::XLS_TYPE_HYPERLINK:
1024	$this->readHyperLink();
1025
1026	break;
1027	case self::XLS_TYPE_DATAVALIDATIONS:
1028	$this->readDataValidations();
1029
1030	break;
1031	case self::XLS_TYPE_DATAVALIDATION:
1032	$this->readDataValidation();
1033
1034	break;
1035	case self::XLS_TYPE_SHEETLAYOUT:
1036	$this->readSheetLayout();
1037
1038	break;
1039	case self::XLS_TYPE_SHEETPROTECTION:
1040	$this->readSheetProtection();
1041
1042	break;
1043	case self::XLS_TYPE_RANGEPROTECTION:
1044	$this->readRangeProtection();
1045
1046	break;
1047	case self::XLS_TYPE_NOTE:
1048	$this->readNote();
1049
1050	break;
1051	case self::XLS_TYPE_TXO:
1052	$this->readTextObject();
1053
1054	break;
1055	case self::XLS_TYPE_CONTINUE:
1056	$this->readContinue();
1057
1058	break;
1059	case self::XLS_TYPE_EOF:
1060	$this->readDefault();
1061
1062	break 2;
1063	default:
1064	$this->readDefault();
1065
1066	break;
1067	}
1068	}
1069
1070	// treat MSODRAWING records, sheet-level Escher
1071	if (!$this->readDataOnly && $this->drawingData) {
1072	$escherWorksheet = new Escher();
1073	$reader = new Xls\Escher($escherWorksheet);
1074	$escherWorksheet = $reader->load($this->drawingData);
1075
1076	// get all spContainers in one long array, so they can be mapped to OBJ records
1077	$allSpContainers = $escherWorksheet->getDgContainer()->getSpgrContainer()->getAllSpContainers();
1078	}
1079
1080	// treat OBJ records
1081	foreach ($this->objs as $n => $obj) {
1082	// the first shape container never has a corresponding OBJ record, hence $n + 1
1083	if (isset($allSpContainers[$n + 1]) && is_object($allSpContainers[$n + 1])) {
1084	$spContainer = $allSpContainers[$n + 1];
1085
1086	// we skip all spContainers that are a part of a group shape since we cannot yet handle those
1087	if ($spContainer->getNestingLevel() > 1) {
1088	continue;
1089	}
1090
1091	// calculate the width and height of the shape
1092	list($startColumn, $startRow) = Coordinate::coordinateFromString($spContainer->getStartCoordinates());
1093	list($endColumn, $endRow) = Coordinate::coordinateFromString($spContainer->getEndCoordinates());
1094
1095	$startOffsetX = $spContainer->getStartOffsetX();
1096	$startOffsetY = $spContainer->getStartOffsetY();
1097	$endOffsetX = $spContainer->getEndOffsetX();
1098	$endOffsetY = $spContainer->getEndOffsetY();
1099
1100	$width = \PhpOffice\PhpSpreadsheet\Shared\Xls::getDistanceX($this->phpSheet, $startColumn, $startOffsetX, $endColumn, $endOffsetX);
1101	$height = \PhpOffice\PhpSpreadsheet\Shared\Xls::getDistanceY($this->phpSheet, $startRow, $startOffsetY, $endRow, $endOffsetY);
1102
1103	// calculate offsetX and offsetY of the shape
1104	$offsetX = $startOffsetX * \PhpOffice\PhpSpreadsheet\Shared\Xls::sizeCol($this->phpSheet, $startColumn) / 1024;
1105	$offsetY = $startOffsetY * \PhpOffice\PhpSpreadsheet\Shared\Xls::sizeRow($this->phpSheet, $startRow) / 256;
1106
1107	switch ($obj['otObjType']) {
1108	case 0x19:
1109	// Note
1110	if (isset($this->cellNotes[$obj['idObjID']])) {
1111	$cellNote = $this->cellNotes[$obj['idObjID']];
1112
1113	if (isset($this->textObjects[$obj['idObjID']])) {
1114	$textObject = $this->textObjects[$obj['idObjID']];
1115	$this->cellNotes[$obj['idObjID']]['objTextData'] = $textObject;
1116	}
1117	}
1118
1119	break;
1120	case 0x08:
1121	// picture
1122	// get index to BSE entry (1-based)
1123	$BSEindex = $spContainer->getOPT(0x0104);
1124
1125	// If there is no BSE Index, we will fail here and other fields are not read.
1126	// Fix by checking here.
1127	// TODO: Why is there no BSE Index? Is this a new Office Version? Password protected field?
1128	// More likely : a uncompatible picture
1129	if (!$BSEindex) {
1130	continue 2;
1131	}
1132
1133	$BSECollection = $escherWorkbook->getDggContainer()->getBstoreContainer()->getBSECollection();
1134	$BSE = $BSECollection[$BSEindex - 1];
1135	$blipType = $BSE->getBlipType();
1136
1137	// need check because some blip types are not supported by Escher reader such as EMF
1138	if ($blip = $BSE->getBlip()) {
1139	$ih = imagecreatefromstring($blip->getData());
1140	$drawing = new MemoryDrawing();
1141	$drawing->setImageResource($ih);
1142
1143	// width, height, offsetX, offsetY
1144	$drawing->setResizeProportional(false);
1145	$drawing->setWidth($width);
1146	$drawing->setHeight($height);
1147	$drawing->setOffsetX($offsetX);
1148	$drawing->setOffsetY($offsetY);
1149
1150	switch ($blipType) {
1151	case BSE::BLIPTYPE_JPEG:
1152	$drawing->setRenderingFunction(MemoryDrawing::RENDERING_JPEG);
1153	$drawing->setMimeType(MemoryDrawing::MIMETYPE_JPEG);
1154
1155	break;
1156	case BSE::BLIPTYPE_PNG:
1157	$drawing->setRenderingFunction(MemoryDrawing::RENDERING_PNG);
1158	$drawing->setMimeType(MemoryDrawing::MIMETYPE_PNG);
1159
1160	break;
1161	}
1162
1163	$drawing->setWorksheet($this->phpSheet);
1164	$drawing->setCoordinates($spContainer->getStartCoordinates());
1165	}
1166
1167	break;
1168	default:
1169	// other object type
1170	break;
1171	}
1172	}
1173	}
1174
1175	// treat SHAREDFMLA records
1176	if ($this->version == self::XLS_BIFF8) {
1177	foreach ($this->sharedFormulaParts as $cell => $baseCell) {
1178	list($column, $row) = Coordinate::coordinateFromString($cell);
1179	if (($this->getReadFilter() !== null) && $this->getReadFilter()->readCell($column, $row, $this->phpSheet->getTitle())) {
1180	$formula = $this->getFormulaFromStructure($this->sharedFormulas[$baseCell], $cell);
1181	$this->phpSheet->getCell($cell)->setValueExplicit('=' . $formula, DataType::TYPE_FORMULA);
1182	}
1183	}
1184	}
1185
1186	if (!empty($this->cellNotes)) {
1187	foreach ($this->cellNotes as $note => $noteDetails) {
1188	if (!isset($noteDetails['objTextData'])) {
1189	if (isset($this->textObjects[$note])) {
1190	$textObject = $this->textObjects[$note];
1191	$noteDetails['objTextData'] = $textObject;
1192	} else {
1193	$noteDetails['objTextData']['text'] = '';
1194	}
1195	}
1196	$cellAddress = str_replace('$', '', $noteDetails['cellRef']);
1197	$this->phpSheet->getComment($cellAddress)->setAuthor($noteDetails['author'])->setText($this->parseRichText($noteDetails['objTextData']['text']));
1198	}
1199	}
1200	}
1201
1202	// add the named ranges (defined names)
1203	foreach ($this->definedname as $definedName) {
1204	if ($definedName['isBuiltInName']) {
1205	switch ($definedName['name']) {
1206	case pack('C', 0x06):
1207	// print area
1208	// in general, formula looks like this: Foo!$C$7:$J$66,Bar!$A$1:$IV$2
1209	$ranges = explode(',', $definedName['formula']); // FIXME: what if sheetname contains comma?
1210
1211	$extractedRanges = [];
1212	foreach ($ranges as $range) {
1213	// $range should look like one of these
1214	// Foo!$C$7:$J$66
1215	// Bar!$A$1:$IV$2
1216	$explodes = Worksheet::extractSheetTitle($range, true);
1217	$sheetName = trim($explodes[0], "'");
1218	if (count($explodes) == 2) {
1219	if (strpos($explodes[1], ':') === false) {
1220	$explodes[1] = $explodes[1] . ':' . $explodes[1];
1221	}
1222	$extractedRanges[] = str_replace('$', '', $explodes[1]); // C7:J66
1223	}
1224	}
1225	if ($docSheet = $this->spreadsheet->getSheetByName($sheetName)) {
1226	$docSheet->getPageSetup()->setPrintArea(implode(',', $extractedRanges)); // C7:J66,A1:IV2
1227	}
1228
1229	break;
1230	case pack('C', 0x07):
1231	// print titles (repeating rows)
1232	// Assuming BIFF8, there are 3 cases
1233	// 1. repeating rows
1234	// formula looks like this: Sheet!$A$1:$IV$2
1235	// rows 1-2 repeat
1236	// 2. repeating columns
1237	// formula looks like this: Sheet!$A$1:$B$65536
1238	// columns A-B repeat
1239	// 3. both repeating rows and repeating columns
1240	// formula looks like this: Sheet!$A$1:$B$65536,Sheet!$A$1:$IV$2
1241	$ranges = explode(',', $definedName['formula']); // FIXME: what if sheetname contains comma?
1242	foreach ($ranges as $range) {
1243	// $range should look like this one of these
1244	// Sheet!$A$1:$B$65536
1245	// Sheet!$A$1:$IV$2
1246	if (strpos($range, '!') !== false) {
1247	$explodes = Worksheet::extractSheetTitle($range, true);
1248	if ($docSheet = $this->spreadsheet->getSheetByName($explodes[0])) {
1249	$extractedRange = $explodes[1];
1250	$extractedRange = str_replace('$', '', $extractedRange);
1251
1252	$coordinateStrings = explode(':', $extractedRange);
1253	if (count($coordinateStrings) == 2) {
1254	list($firstColumn, $firstRow) = Coordinate::coordinateFromString($coordinateStrings[0]);
1255	list($lastColumn, $lastRow) = Coordinate::coordinateFromString($coordinateStrings[1]);
1256
1257	if ($firstColumn == 'A' and $lastColumn == 'IV') {
1258	// then we have repeating rows
1259	$docSheet->getPageSetup()->setRowsToRepeatAtTop([$firstRow, $lastRow]);
1260	} elseif ($firstRow == 1 and $lastRow == 65536) {
1261	// then we have repeating columns
1262	$docSheet->getPageSetup()->setColumnsToRepeatAtLeft([$firstColumn, $lastColumn]);
1263	}
1264	}
1265	}
1266	}
1267	}
1268
1269	break;
1270	}
1271	} else {
1272	// Extract range
1273	if (strpos($definedName['formula'], '!') !== false) {
1274	$explodes = Worksheet::extractSheetTitle($definedName['formula'], true);
1275	if (($docSheet = $this->spreadsheet->getSheetByName($explodes[0])) \|\|
1276	($docSheet = $this->spreadsheet->getSheetByName(trim($explodes[0], "'")))) {
1277	$extractedRange = $explodes[1];
1278	$extractedRange = str_replace('$', '', $extractedRange);
1279
1280	$localOnly = ($definedName['scope'] == 0) ? false : true;
1281
1282	$scope = ($definedName['scope'] == 0) ? null : $this->spreadsheet->getSheetByName($this->sheets[$definedName['scope'] - 1]['name']);
1283
1284	$this->spreadsheet->addNamedRange(new NamedRange((string) $definedName['name'], $docSheet, $extractedRange, $localOnly, $scope));
1285	}
1286	}
1287	// Named Value
1288	// TODO Provide support for named values
1289	}
1290	}
1291	$this->data = null;
1292
1293	return $this->spreadsheet;
1294	}
1295
1296	/**
1297	* Read record data from stream, decrypting as required.
1298	*
1299	* @param string $data Data stream to read from
1300	* @param int $pos Position to start reading from
1301	* @param int $len Record data length
1302	*
1303	* @return string Record data
1304	*/
1305	private function readRecordData($data, $pos, $len)
1306	{
1307	$data = substr($data, $pos, $len);
1308
1309	// File not encrypted, or record before encryption start point
1310	if ($this->encryption == self::MS_BIFF_CRYPTO_NONE \|\| $pos < $this->encryptionStartPos) {
1311	return $data;
1312	}
1313
1314	$recordData = '';
1315	if ($this->encryption == self::MS_BIFF_CRYPTO_RC4) {
1316	$oldBlock = floor($this->rc4Pos / self::REKEY_BLOCK);
1317	$block = floor($pos / self::REKEY_BLOCK);
1318	$endBlock = floor(($pos + $len) / self::REKEY_BLOCK);
1319
1320	// Spin an RC4 decryptor to the right spot. If we have a decryptor sitting
1321	// at a point earlier in the current block, re-use it as we can save some time.
1322	if ($block != $oldBlock \|\| $pos < $this->rc4Pos \|\| !$this->rc4Key) {
1323	$this->rc4Key = $this->makeKey($block, $this->md5Ctxt);
1324	$step = $pos % self::REKEY_BLOCK;
1325	} else {
1326	$step = $pos - $this->rc4Pos;
1327	}
1328	$this->rc4Key->RC4(str_repeat("\0", $step));
1329
1330	// Decrypt record data (re-keying at the end of every block)
1331	while ($block != $endBlock) {
1332	$step = self::REKEY_BLOCK - ($pos % self::REKEY_BLOCK);
1333	$recordData .= $this->rc4Key->RC4(substr($data, 0, $step));
1334	$data = substr($data, $step);
1335	$pos += $step;
1336	$len -= $step;
1337	++$block;
1338	$this->rc4Key = $this->makeKey($block, $this->md5Ctxt);
1339	}
1340	$recordData .= $this->rc4Key->RC4(substr($data, 0, $len));
1341
1342	// Keep track of the position of this decryptor.
1343	// We'll try and re-use it later if we can to speed things up
1344	$this->rc4Pos = $pos + $len;
1345	} elseif ($this->encryption == self::MS_BIFF_CRYPTO_XOR) {
1346	throw new Exception('XOr encryption not supported');
1347	}
1348
1349	return $recordData;
1350	}
1351
1352	/**
1353	* Use OLE reader to extract the relevant data streams from the OLE file.
1354	*
1355	* @param string $pFilename
1356	*/
1357	private function loadOLE($pFilename)
1358	{
1359	// OLE reader
1360	$ole = new OLERead();
1361	// get excel data,
1362	$ole->read($pFilename);
1363	// Get workbook data: workbook stream + sheet streams
1364	$this->data = $ole->getStream($ole->wrkbook);
1365	// Get summary information data
1366	$this->summaryInformation = $ole->getStream($ole->summaryInformation);
1367	// Get additional document summary information data
1368	$this->documentSummaryInformation = $ole->getStream($ole->documentSummaryInformation);
1369	}
1370
1371	/**
1372	* Read summary information.
1373	*/
1374	private function readSummaryInformation()
1375	{
1376	if (!isset($this->summaryInformation)) {
1377	return;
1378	}
1379
1380	// offset: 0; size: 2; must be 0xFE 0xFF (UTF-16 LE byte order mark)
1381	// offset: 2; size: 2;
1382	// offset: 4; size: 2; OS version
1383	// offset: 6; size: 2; OS indicator
1384	// offset: 8; size: 16
1385	// offset: 24; size: 4; section count
1386	$secCount = self::getInt4d($this->summaryInformation, 24);
1387
1388	// 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
1389	// offset: 44; size: 4
1390	$secOffset = self::getInt4d($this->summaryInformation, 44);
1391
1392	// section header
1393	// offset: $secOffset; size: 4; section length
1394	$secLength = self::getInt4d($this->summaryInformation, $secOffset);
1395
1396	// offset: $secOffset+4; size: 4; property count
1397	$countProperties = self::getInt4d($this->summaryInformation, $secOffset + 4);
1398
1399	// initialize code page (used to resolve string values)
1400	$codePage = 'CP1252';
1401
1402	// offset: ($secOffset+8); size: var
1403	// loop through property decarations and properties
1404	for ($i = 0; $i < $countProperties; ++$i) {
1405	// offset: ($secOffset+8) + (8 * $i); size: 4; property ID
1406	$id = self::getInt4d($this->summaryInformation, ($secOffset + 8) + (8 * $i));
1407
1408	// Use value of property id as appropriate
1409	// offset: ($secOffset+12) + (8 * $i); size: 4; offset from beginning of section (48)
1410	$offset = self::getInt4d($this->summaryInformation, ($secOffset + 12) + (8 * $i));
1411
1412	$type = self::getInt4d($this->summaryInformation, $secOffset + $offset);
1413
1414	// initialize property value
1415	$value = null;
1416
1417	// extract property value based on property type
1418	switch ($type) {
1419	case 0x02: // 2 byte signed integer
1420	$value = self::getUInt2d($this->summaryInformation, $secOffset + 4 + $offset);
1421
1422	break;
1423	case 0x03: // 4 byte signed integer
1424	$value = self::getInt4d($this->summaryInformation, $secOffset + 4 + $offset);
1425
1426	break;
1427	case 0x13: // 4 byte unsigned integer
1428	// not needed yet, fix later if necessary
1429	break;
1430	case 0x1E: // null-terminated string prepended by dword string length
1431	$byteLength = self::getInt4d($this->summaryInformation, $secOffset + 4 + $offset);
1432	$value = substr($this->summaryInformation, $secOffset + 8 + $offset, $byteLength);
1433	$value = StringHelper::convertEncoding($value, 'UTF-8', $codePage);
1434	$value = rtrim($value);
1435
1436	break;
1437	case 0x40: // Filetime (64-bit value representing the number of 100-nanosecond intervals since January 1, 1601)
1438	// PHP-time
1439	$value = OLE::OLE2LocalDate(substr($this->summaryInformation, $secOffset + 4 + $offset, 8));
1440
1441	break;
1442	case 0x47: // Clipboard format
1443	// not needed yet, fix later if necessary
1444	break;
1445	}
1446
1447	switch ($id) {
1448	case 0x01: // Code Page
1449	$codePage = CodePage::numberToName($value);
1450
1451	break;
1452	case 0x02: // Title
1453	$this->spreadsheet->getProperties()->setTitle($value);
1454
1455	break;
1456	case 0x03: // Subject
1457	$this->spreadsheet->getProperties()->setSubject($value);
1458
1459	break;
1460	case 0x04: // Author (Creator)
1461	$this->spreadsheet->getProperties()->setCreator($value);
1462
1463	break;
1464	case 0x05: // Keywords
1465	$this->spreadsheet->getProperties()->setKeywords($value);
1466
1467	break;
1468	case 0x06: // Comments (Description)
1469	$this->spreadsheet->getProperties()->setDescription($value);
1470
1471	break;
1472	case 0x07: // Template
1473	// Not supported by PhpSpreadsheet
1474	break;
1475	case 0x08: // Last Saved By (LastModifiedBy)
1476	$this->spreadsheet->getProperties()->setLastModifiedBy($value);
1477
1478	break;
1479	case 0x09: // Revision
1480	// Not supported by PhpSpreadsheet
1481	break;
1482	case 0x0A: // Total Editing Time
1483	// Not supported by PhpSpreadsheet
1484	break;
1485	case 0x0B: // Last Printed
1486	// Not supported by PhpSpreadsheet
1487	break;
1488	case 0x0C: // Created Date/Time
1489	$this->spreadsheet->getProperties()->setCreated($value);
1490
1491	break;
1492	case 0x0D: // Modified Date/Time
1493	$this->spreadsheet->getProperties()->setModified($value);
1494
1495	break;
1496	case 0x0E: // Number of Pages
1497	// Not supported by PhpSpreadsheet
1498	break;
1499	case 0x0F: // Number of Words
1500	// Not supported by PhpSpreadsheet
1501	break;
1502	case 0x10: // Number of Characters
1503	// Not supported by PhpSpreadsheet
1504	break;
1505	case 0x11: // Thumbnail
1506	// Not supported by PhpSpreadsheet
1507	break;
1508	case 0x12: // Name of creating application
1509	// Not supported by PhpSpreadsheet
1510	break;
1511	case 0x13: // Security
1512	// Not supported by PhpSpreadsheet
1513	break;
1514	}
1515	}
1516	}
1517
1518	/**
1519	* Read additional document summary information.
1520	*/
1521	private function readDocumentSummaryInformation()
1522	{
1523	if (!isset($this->documentSummaryInformation)) {
1524	return;
1525	}
1526
1527	// offset: 0; size: 2; must be 0xFE 0xFF (UTF-16 LE byte order mark)
1528	// offset: 2; size: 2;
1529	// offset: 4; size: 2; OS version
1530	// offset: 6; size: 2; OS indicator
1531	// offset: 8; size: 16
1532	// offset: 24; size: 4; section count
1533	$secCount = self::getInt4d($this->documentSummaryInformation, 24);
1534
1535	// 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
1536	// offset: 44; size: 4; first section offset
1537	$secOffset = self::getInt4d($this->documentSummaryInformation, 44);
1538
1539	// section header
1540	// offset: $secOffset; size: 4; section length
1541	$secLength = self::getInt4d($this->documentSummaryInformation, $secOffset);
1542
1543	// offset: $secOffset+4; size: 4; property count
1544	$countProperties = self::getInt4d($this->documentSummaryInformation, $secOffset + 4);
1545
1546	// initialize code page (used to resolve string values)
1547	$codePage = 'CP1252';
1548
1549	// offset: ($secOffset+8); size: var
1550	// loop through property decarations and properties
1551	for ($i = 0; $i < $countProperties; ++$i) {
1552	// offset: ($secOffset+8) + (8 * $i); size: 4; property ID
1553	$id = self::getInt4d($this->documentSummaryInformation, ($secOffset + 8) + (8 * $i));
1554
1555	// Use value of property id as appropriate
1556	// offset: 60 + 8 * $i; size: 4; offset from beginning of section (48)
1557	$offset = self::getInt4d($this->documentSummaryInformation, ($secOffset + 12) + (8 * $i));
1558
1559	$type = self::getInt4d($this->documentSummaryInformation, $secOffset + $offset);
1560
1561	// initialize property value
1562	$value = null;
1563
1564	// extract property value based on property type
1565	switch ($type) {
1566	case 0x02: // 2 byte signed integer
1567	$value = self::getUInt2d($this->documentSummaryInformation, $secOffset + 4 + $offset);
1568
1569	break;
1570	case 0x03: // 4 byte signed integer
1571	$value = self::getInt4d($this->documentSummaryInformation, $secOffset + 4 + $offset);
1572
1573	break;
1574	case 0x0B: // Boolean
1575	$value = self::getUInt2d($this->documentSummaryInformation, $secOffset + 4 + $offset);
1576	$value = ($value == 0 ? false : true);
1577
1578	break;
1579	case 0x13: // 4 byte unsigned integer
1580	// not needed yet, fix later if necessary
1581	break;
1582	case 0x1E: // null-terminated string prepended by dword string length
1583	$byteLength = self::getInt4d($this->documentSummaryInformation, $secOffset + 4 + $offset);
1584	$value = substr($this->documentSummaryInformation, $secOffset + 8 + $offset, $byteLength);
1585	$value = StringHelper::convertEncoding($value, 'UTF-8', $codePage);
1586	$value = rtrim($value);
1587
1588	break;
1589	case 0x40: // Filetime (64-bit value representing the number of 100-nanosecond intervals since January 1, 1601)
1590	// PHP-Time
1591	$value = OLE::OLE2LocalDate(substr($this->documentSummaryInformation, $secOffset + 4 + $offset, 8));
1592
1593	break;
1594	case 0x47: // Clipboard format
1595	// not needed yet, fix later if necessary
1596	break;
1597	}
1598
1599	switch ($id) {
1600	case 0x01: // Code Page
1601	$codePage = CodePage::numberToName($value);
1602
1603	break;
1604	case 0x02: // Category
1605	$this->spreadsheet->getProperties()->setCategory($value);
1606
1607	break;
1608	case 0x03: // Presentation Target
1609	// Not supported by PhpSpreadsheet
1610	break;
1611	case 0x04: // Bytes
1612	// Not supported by PhpSpreadsheet
1613	break;
1614	case 0x05: // Lines
1615	// Not supported by PhpSpreadsheet
1616	break;
1617	case 0x06: // Paragraphs
1618	// Not supported by PhpSpreadsheet
1619	break;
1620	case 0x07: // Slides
1621	// Not supported by PhpSpreadsheet
1622	break;
1623	case 0x08: // Notes
1624	// Not supported by PhpSpreadsheet
1625	break;
1626	case 0x09: // Hidden Slides
1627	// Not supported by PhpSpreadsheet
1628	break;
1629	case 0x0A: // MM Clips
1630	// Not supported by PhpSpreadsheet
1631	break;
1632	case 0x0B: // Scale Crop
1633	// Not supported by PhpSpreadsheet
1634	break;
1635	case 0x0C: // Heading Pairs
1636	// Not supported by PhpSpreadsheet
1637	break;
1638	case 0x0D: // Titles of Parts
1639	// Not supported by PhpSpreadsheet
1640	break;
1641	case 0x0E: // Manager
1642	$this->spreadsheet->getProperties()->setManager($value);
1643
1644	break;
1645	case 0x0F: // Company
1646	$this->spreadsheet->getProperties()->setCompany($value);
1647
1648	break;
1649	case 0x10: // Links up-to-date
1650	// Not supported by PhpSpreadsheet
1651	break;
1652	}
1653	}
1654	}
1655
1656	/**
1657	* Reads a general type of BIFF record. Does nothing except for moving stream pointer forward to next record.
1658	*/
1659	private function readDefault()
1660	{
1661	$length = self::getUInt2d($this->data, $this->pos + 2);
1662
1663	// move stream pointer to next record
1664	$this->pos += 4 + $length;
1665	}
1666
1667	/**
1668	* The NOTE record specifies a comment associated with a particular cell. In Excel 95 (BIFF7) and earlier versions,
1669	* this record stores a note (cell note). This feature was significantly enhanced in Excel 97.
1670	*/
1671	private function readNote()
1672	{
1673	$length = self::getUInt2d($this->data, $this->pos + 2);
1674	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
1675
1676	// move stream pointer to next record
1677	$this->pos += 4 + $length;
1678
1679	if ($this->readDataOnly) {
1680	return;
1681	}
1682
1683	$cellAddress = $this->readBIFF8CellAddress(substr($recordData, 0, 4));
1684	if ($this->version == self::XLS_BIFF8) {
1685	$noteObjID = self::getUInt2d($recordData, 6);
1686	$noteAuthor = self::readUnicodeStringLong(substr($recordData, 8));
1687	$noteAuthor = $noteAuthor['value'];
1688	$this->cellNotes[$noteObjID] = [
1689	'cellRef' => $cellAddress,
1690	'objectID' => $noteObjID,
1691	'author' => $noteAuthor,
1692	];
1693	} else {
1694	$extension = false;
1695	if ($cellAddress == '$B$65536') {
1696	// If the address row is -1 and the column is 0, (which translates as $B$65536) then this is a continuation
1697	// note from the previous cell annotation. We're not yet handling this, so annotations longer than the
1698	// max 2048 bytes will probably throw a wobbly.
1699	$row = self::getUInt2d($recordData, 0);
1700	$extension = true;
1701	$cellAddress = array_pop(array_keys($this->phpSheet->getComments()));
1702	}
1703
1704	$cellAddress = str_replace('$', '', $cellAddress);
1705	$noteLength = self::getUInt2d($recordData, 4);
1706	$noteText = trim(substr($recordData, 6));
1707
1708	if ($extension) {
1709	// Concatenate this extension with the currently set comment for the cell
1710	$comment = $this->phpSheet->getComment($cellAddress);
1711	$commentText = $comment->getText()->getPlainText();
1712	$comment->setText($this->parseRichText($commentText . $noteText));
1713	} else {
1714	// Set comment for the cell
1715	$this->phpSheet->getComment($cellAddress)->setText($this->parseRichText($noteText));
1716	// ->setAuthor($author)
1717	}
1718	}
1719	}
1720
1721	/**
1722	* The TEXT Object record contains the text associated with a cell annotation.
1723	*/
1724	private function readTextObject()
1725	{
1726	$length = self::getUInt2d($this->data, $this->pos + 2);
1727	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
1728
1729	// move stream pointer to next record
1730	$this->pos += 4 + $length;
1731
1732	if ($this->readDataOnly) {
1733	return;
1734	}
1735
1736	// recordData consists of an array of subrecords looking like this:
1737	// grbit: 2 bytes; Option Flags
1738	// rot: 2 bytes; rotation
1739	// cchText: 2 bytes; length of the text (in the first continue record)
1740	// cbRuns: 2 bytes; length of the formatting (in the second continue record)
1741	// followed by the continuation records containing the actual text and formatting
1742	$grbitOpts = self::getUInt2d($recordData, 0);
1743	$rot = self::getUInt2d($recordData, 2);
1744	$cchText = self::getUInt2d($recordData, 10);
1745	$cbRuns = self::getUInt2d($recordData, 12);
1746	$text = $this->getSplicedRecordData();
1747
1748	$textByte = $text['spliceOffsets'][1] - $text['spliceOffsets'][0] - 1;
1749	$textStr = substr($text['recordData'], $text['spliceOffsets'][0] + 1, $textByte);
1750	// get 1 byte
1751	$is16Bit = ord($text['recordData'][0]);
1752	// it is possible to use a compressed format,
1753	// which omits the high bytes of all characters, if they are all zero
1754	if (($is16Bit & 0x01) === 0) {
1755	$textStr = StringHelper::ConvertEncoding($textStr, 'UTF-8', 'ISO-8859-1');
1756	} else {
1757	$textStr = $this->decodeCodepage($textStr);
1758	}
1759
1760	$this->textObjects[$this->textObjRef] = [
1761	'text' => $textStr,
1762	'format' => substr($text['recordData'], $text['spliceOffsets'][1], $cbRuns),
1763	'alignment' => $grbitOpts,
1764	'rotation' => $rot,
1765	];
1766	}
1767
1768	/**
1769	* Read BOF.
1770	*/
1771	private function readBof()
1772	{
1773	$length = self::getUInt2d($this->data, $this->pos + 2);
1774	$recordData = substr($this->data, $this->pos + 4, $length);
1775
1776	// move stream pointer to next record
1777	$this->pos += 4 + $length;
1778
1779	// offset: 2; size: 2; type of the following data
1780	$substreamType = self::getUInt2d($recordData, 2);
1781
1782	switch ($substreamType) {
1783	case self::XLS_WORKBOOKGLOBALS:
1784	$version = self::getUInt2d($recordData, 0);
1785	if (($version != self::XLS_BIFF8) && ($version != self::XLS_BIFF7)) {
1786	throw new Exception('Cannot read this Excel file. Version is too old.');
1787	}
1788	$this->version = $version;
1789
1790	break;
1791	case self::XLS_WORKSHEET:
1792	// do not use this version information for anything
1793	// it is unreliable (OpenOffice doc, 5.8), use only version information from the global stream
1794	break;
1795	default:
1796	// substream, e.g. chart
1797	// just skip the entire substream
1798	do {
1799	$code = self::getUInt2d($this->data, $this->pos);
1800	$this->readDefault();
1801	} while ($code != self::XLS_TYPE_EOF && $this->pos < $this->dataSize);
1802
1803	break;
1804	}
1805	}
1806
1807	/**
1808	* FILEPASS.
1809	*
1810	* This record is part of the File Protection Block. It
1811	* contains information about the read/write password of the
1812	* file. All record contents following this record will be
1813	* encrypted.
1814	*
1815	* -- "OpenOffice.org's Documentation of the Microsoft
1816	* Excel File Format"
1817	*
1818	* The decryption functions and objects used from here on in
1819	* are based on the source of Spreadsheet-ParseExcel:
1820	* https://metacpan.org/release/Spreadsheet-ParseExcel
1821	*/
1822	private function readFilepass()
1823	{
1824	$length = self::getUInt2d($this->data, $this->pos + 2);
1825
1826	if ($length != 54) {
1827	throw new Exception('Unexpected file pass record length');
1828	}
1829
1830	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
1831
1832	// move stream pointer to next record
1833	$this->pos += 4 + $length;
1834
1835	if (!$this->verifyPassword('VelvetSweatshop', substr($recordData, 6, 16), substr($recordData, 22, 16), substr($recordData, 38, 16), $this->md5Ctxt)) {
1836	throw new Exception('Decryption password incorrect');
1837	}
1838
1839	$this->encryption = self::MS_BIFF_CRYPTO_RC4;
1840
1841	// Decryption required from the record after next onwards
1842	$this->encryptionStartPos = $this->pos + self::getUInt2d($this->data, $this->pos + 2);
1843	}
1844
1845	/**
1846	* Make an RC4 decryptor for the given block.
1847	*
1848	* @param int $block Block for which to create decrypto
1849	* @param string $valContext MD5 context state
1850	*
1851	* @return Xls\RC4
1852	*/
1853	private function makeKey($block, $valContext)
1854	{
1855	$pwarray = str_repeat("\0", 64);
1856
1857	for ($i = 0; $i < 5; ++$i) {
1858	$pwarray[$i] = $valContext[$i];
1859	}
1860
1861	$pwarray[5] = chr($block & 0xff);
1862	$pwarray[6] = chr(($block >> 8) & 0xff);
1863	$pwarray[7] = chr(($block >> 16) & 0xff);
1864	$pwarray[8] = chr(($block >> 24) & 0xff);
1865
1866	$pwarray[9] = "\x80";
1867	$pwarray[56] = "\x48";
1868
1869	$md5 = new Xls\MD5();
1870	$md5->add($pwarray);
1871
1872	$s = $md5->getContext();
1873
1874	return new Xls\RC4($s);
1875	}
1876
1877	/**
1878	* Verify RC4 file password.
1879	*
1880	* @param string $password Password to check
1881	* @param string $docid Document id
1882	* @param string $salt_data Salt data
1883	* @param string $hashedsalt_data Hashed salt data
1884	* @param string $valContext Set to the MD5 context of the value
1885	*
1886	* @return bool Success
1887	*/
1888	private function verifyPassword($password, $docid, $salt_data, $hashedsalt_data, &$valContext)
1889	{
1890	$pwarray = str_repeat("\0", 64);
1891
1892	$iMax = strlen($password);
1893	for ($i = 0; $i < $iMax; ++$i) {
1894	$o = ord(substr($password, $i, 1));
1895	$pwarray[2 * $i] = chr($o & 0xff);
1896	$pwarray[2 * $i + 1] = chr(($o >> 8) & 0xff);
1897	}
1898	$pwarray[2 * $i] = chr(0x80);
1899	$pwarray[56] = chr(($i << 4) & 0xff);
1900
1901	$md5 = new Xls\MD5();
1902	$md5->add($pwarray);
1903
1904	$mdContext1 = $md5->getContext();
1905
1906	$offset = 0;
1907	$keyoffset = 0;
1908	$tocopy = 5;
1909
1910	$md5->reset();
1911
1912	while ($offset != 16) {
1913	if ((64 - $offset) < 5) {
1914	$tocopy = 64 - $offset;
1915	}
1916	for ($i = 0; $i <= $tocopy; ++$i) {
1917	$pwarray[$offset + $i] = $mdContext1[$keyoffset + $i];
1918	}
1919	$offset += $tocopy;
1920
1921	if ($offset == 64) {
1922	$md5->add($pwarray);
1923	$keyoffset = $tocopy;
1924	$tocopy = 5 - $tocopy;
1925	$offset = 0;
1926
1927	continue;
1928	}
1929
1930	$keyoffset = 0;
1931	$tocopy = 5;
1932	for ($i = 0; $i < 16; ++$i) {
1933	$pwarray[$offset + $i] = $docid[$i];
1934	}
1935	$offset += 16;
1936	}
1937
1938	$pwarray[16] = "\x80";
1939	for ($i = 0; $i < 47; ++$i) {
1940	$pwarray[17 + $i] = "\0";
1941	}
1942	$pwarray[56] = "\x80";
1943	$pwarray[57] = "\x0a";
1944
1945	$md5->add($pwarray);
1946	$valContext = $md5->getContext();
1947
1948	$key = $this->makeKey(0, $valContext);
1949
1950	$salt = $key->RC4($salt_data);
1951	$hashedsalt = $key->RC4($hashedsalt_data);
1952
1953	$salt .= "\x80" . str_repeat("\0", 47);
1954	$salt[56] = "\x80";
1955
1956	$md5->reset();
1957	$md5->add($salt);
1958	$mdContext2 = $md5->getContext();
1959
1960	return $mdContext2 == $hashedsalt;
1961	}
1962
1963	/**
1964	* CODEPAGE.
1965	*
1966	* This record stores the text encoding used to write byte
1967	* strings, stored as MS Windows code page identifier.
1968	*
1969	* -- "OpenOffice.org's Documentation of the Microsoft
1970	* Excel File Format"
1971	*/
1972	private function readCodepage()
1973	{
1974	$length = self::getUInt2d($this->data, $this->pos + 2);
1975	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
1976
1977	// move stream pointer to next record
1978	$this->pos += 4 + $length;
1979
1980	// offset: 0; size: 2; code page identifier
1981	$codepage = self::getUInt2d($recordData, 0);
1982
1983	$this->codepage = CodePage::numberToName($codepage);
1984	}
1985
1986	/**
1987	* DATEMODE.
1988	*
1989	* This record specifies the base date for displaying date
1990	* values. All dates are stored as count of days past this
1991	* base date. In BIFF2-BIFF4 this record is part of the
1992	* Calculation Settings Block. In BIFF5-BIFF8 it is
1993	* stored in the Workbook Globals Substream.
1994	*
1995	* -- "OpenOffice.org's Documentation of the Microsoft
1996	* Excel File Format"
1997	*/
1998	private function readDateMode()
1999	{
2000	$length = self::getUInt2d($this->data, $this->pos + 2);
2001	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
2002
2003	// move stream pointer to next record
2004	$this->pos += 4 + $length;
2005
2006	// offset: 0; size: 2; 0 = base 1900, 1 = base 1904
2007	Date::setExcelCalendar(Date::CALENDAR_WINDOWS_1900);
2008	if (ord($recordData[0]) == 1) {
2009	Date::setExcelCalendar(Date::CALENDAR_MAC_1904);
2010	}
2011	}
2012
2013	/**
2014	* Read a FONT record.
2015	*/
2016	private function readFont()
2017	{
2018	$length = self::getUInt2d($this->data, $this->pos + 2);
2019	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
2020
2021	// move stream pointer to next record
2022	$this->pos += 4 + $length;
2023
2024	if (!$this->readDataOnly) {
2025	$objFont = new Font();
2026
2027	// offset: 0; size: 2; height of the font (in twips = 1/20 of a point)
2028	$size = self::getUInt2d($recordData, 0);
2029	$objFont->setSize($size / 20);
2030
2031	// offset: 2; size: 2; option flags
2032	// bit: 0; mask 0x0001; bold (redundant in BIFF5-BIFF8)
2033	// bit: 1; mask 0x0002; italic
2034	$isItalic = (0x0002 & self::getUInt2d($recordData, 2)) >> 1;
2035	if ($isItalic) {
2036	$objFont->setItalic(true);
2037	}
2038
2039	// bit: 2; mask 0x0004; underlined (redundant in BIFF5-BIFF8)
2040	// bit: 3; mask 0x0008; strikethrough
2041	$isStrike = (0x0008 & self::getUInt2d($recordData, 2)) >> 3;
2042	if ($isStrike) {
2043	$objFont->setStrikethrough(true);
2044	}
2045
2046	// offset: 4; size: 2; colour index
2047	$colorIndex = self::getUInt2d($recordData, 4);
2048	$objFont->colorIndex = $colorIndex;
2049
2050	// offset: 6; size: 2; font weight
2051	$weight = self::getUInt2d($recordData, 6);
2052	switch ($weight) {
2053	case 0x02BC:
2054	$objFont->setBold(true);
2055
2056	break;
2057	}
2058
2059	// offset: 8; size: 2; escapement type
2060	$escapement = self::getUInt2d($recordData, 8);
2061	switch ($escapement) {
2062	case 0x0001:
2063	$objFont->setSuperscript(true);
2064
2065	break;
2066	case 0x0002:
2067	$objFont->setSubscript(true);
2068
2069	break;
2070	}
2071
2072	// offset: 10; size: 1; underline type
2073	$underlineType = ord($recordData[10]);
2074	switch ($underlineType) {
2075	case 0x00:
2076	break; // no underline
2077	case 0x01:
2078	$objFont->setUnderline(Font::UNDERLINE_SINGLE);
2079
2080	break;
2081	case 0x02:
2082	$objFont->setUnderline(Font::UNDERLINE_DOUBLE);
2083
2084	break;
2085	case 0x21:
2086	$objFont->setUnderline(Font::UNDERLINE_SINGLEACCOUNTING);
2087
2088	break;
2089	case 0x22:
2090	$objFont->setUnderline(Font::UNDERLINE_DOUBLEACCOUNTING);
2091
2092	break;
2093	}
2094
2095	// offset: 11; size: 1; font family
2096	// offset: 12; size: 1; character set
2097	// offset: 13; size: 1; not used
2098	// offset: 14; size: var; font name
2099	if ($this->version == self::XLS_BIFF8) {
2100	$string = self::readUnicodeStringShort(substr($recordData, 14));
2101	} else {
2102	$string = $this->readByteStringShort(substr($recordData, 14));
2103	}
2104	$objFont->setName($string['value']);
2105
2106	$this->objFonts[] = $objFont;
2107	}
2108	}
2109
2110	/**
2111	* FORMAT.
2112	*
2113	* This record contains information about a number format.
2114	* All FORMAT records occur together in a sequential list.
2115	*
2116	* In BIFF2-BIFF4 other records referencing a FORMAT record
2117	* contain a zero-based index into this list. From BIFF5 on
2118	* the FORMAT record contains the index itself that will be
2119	* used by other records.
2120	*
2121	* -- "OpenOffice.org's Documentation of the Microsoft
2122	* Excel File Format"
2123	*/
2124	private function readFormat()
2125	{
2126	$length = self::getUInt2d($this->data, $this->pos + 2);
2127	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
2128
2129	// move stream pointer to next record
2130	$this->pos += 4 + $length;
2131
2132	if (!$this->readDataOnly) {
2133	$indexCode = self::getUInt2d($recordData, 0);
2134
2135	if ($this->version == self::XLS_BIFF8) {
2136	$string = self::readUnicodeStringLong(substr($recordData, 2));
2137	} else {
2138	// BIFF7
2139	$string = $this->readByteStringShort(substr($recordData, 2));
2140	}
2141
2142	$formatString = $string['value'];
2143	$this->formats[$indexCode] = $formatString;
2144	}
2145	}
2146
2147	/**
2148	* XF - Extended Format.
2149	*
2150	* This record contains formatting information for cells, rows, columns or styles.
2151	* According to https://support.microsoft.com/en-us/help/147732 there are always at least 15 cell style XF
2152	* and 1 cell XF.
2153	* Inspection of Excel files generated by MS Office Excel shows that XF records 0-14 are cell style XF
2154	* and XF record 15 is a cell XF
2155	* We only read the first cell style XF and skip the remaining cell style XF records
2156	* We read all cell XF records.
2157	*
2158	* -- "OpenOffice.org's Documentation of the Microsoft
2159	* Excel File Format"
2160	*/
2161	private function readXf()
2162	{
2163	$length = self::getUInt2d($this->data, $this->pos + 2);
2164	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
2165
2166	// move stream pointer to next record
2167	$this->pos += 4 + $length;
2168
2169	$objStyle = new Style();
2170
2171	if (!$this->readDataOnly) {
2172	// offset: 0; size: 2; Index to FONT record
2173	if (self::getUInt2d($recordData, 0) < 4) {
2174	$fontIndex = self::getUInt2d($recordData, 0);
2175	} else {
2176	// this has to do with that index 4 is omitted in all BIFF versions for some strange reason
2177	// check the OpenOffice documentation of the FONT record
2178	$fontIndex = self::getUInt2d($recordData, 0) - 1;
2179	}
2180	$objStyle->setFont($this->objFonts[$fontIndex]);
2181
2182	// offset: 2; size: 2; Index to FORMAT record
2183	$numberFormatIndex = self::getUInt2d($recordData, 2);
2184	if (isset($this->formats[$numberFormatIndex])) {
2185	// then we have user-defined format code
2186	$numberFormat = ['formatCode' => $this->formats[$numberFormatIndex]];
2187	} elseif (($code = NumberFormat::builtInFormatCode($numberFormatIndex)) !== '') {
2188	// then we have built-in format code
2189	$numberFormat = ['formatCode' => $code];
2190	} else {
2191	// we set the general format code
2192	$numberFormat = ['formatCode' => 'General'];
2193	}
2194	$objStyle->getNumberFormat()->setFormatCode($numberFormat['formatCode']);
2195
2196	// offset: 4; size: 2; XF type, cell protection, and parent style XF
2197	// bit 2-0; mask 0x0007; XF_TYPE_PROT
2198	$xfTypeProt = self::getUInt2d($recordData, 4);
2199	// bit 0; mask 0x01; 1 = cell is locked
2200	$isLocked = (0x01 & $xfTypeProt) >> 0;
2201	$objStyle->getProtection()->setLocked($isLocked ? Protection::PROTECTION_INHERIT : Protection::PROTECTION_UNPROTECTED);
2202
2203	// bit 1; mask 0x02; 1 = Formula is hidden
2204	$isHidden = (0x02 & $xfTypeProt) >> 1;
2205	$objStyle->getProtection()->setHidden($isHidden ? Protection::PROTECTION_PROTECTED : Protection::PROTECTION_UNPROTECTED);
2206
2207	// bit 2; mask 0x04; 0 = Cell XF, 1 = Cell Style XF
2208	$isCellStyleXf = (0x04 & $xfTypeProt) >> 2;
2209
2210	// offset: 6; size: 1; Alignment and text break
2211	// bit 2-0, mask 0x07; horizontal alignment
2212	$horAlign = (0x07 & ord($recordData[6])) >> 0;
2213	switch ($horAlign) {
2214	case 0:
2215	$objStyle->getAlignment()->setHorizontal(Alignment::HORIZONTAL_GENERAL);
2216
2217	break;
2218	case 1:
2219	$objStyle->getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT);
2220
2221	break;
2222	case 2:
2223	$objStyle->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
2224
2225	break;
2226	case 3:
2227	$objStyle->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
2228
2229	break;
2230	case 4:
2231	$objStyle->getAlignment()->setHorizontal(Alignment::HORIZONTAL_FILL);
2232
2233	break;
2234	case 5:
2235	$objStyle->getAlignment()->setHorizontal(Alignment::HORIZONTAL_JUSTIFY);
2236
2237	break;
2238	case 6:
2239	$objStyle->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER_CONTINUOUS);
2240
2241	break;
2242	}
2243	// bit 3, mask 0x08; wrap text
2244	$wrapText = (0x08 & ord($recordData[6])) >> 3;
2245	switch ($wrapText) {
2246	case 0:
2247	$objStyle->getAlignment()->setWrapText(false);
2248
2249	break;
2250	case 1:
2251	$objStyle->getAlignment()->setWrapText(true);
2252
2253	break;
2254	}
2255	// bit 6-4, mask 0x70; vertical alignment
2256	$vertAlign = (0x70 & ord($recordData[6])) >> 4;
2257	switch ($vertAlign) {
2258	case 0:
2259	$objStyle->getAlignment()->setVertical(Alignment::VERTICAL_TOP);
2260
2261	break;
2262	case 1:
2263	$objStyle->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
2264
2265	break;
2266	case 2:
2267	$objStyle->getAlignment()->setVertical(Alignment::VERTICAL_BOTTOM);
2268
2269	break;
2270	case 3:
2271	$objStyle->getAlignment()->setVertical(Alignment::VERTICAL_JUSTIFY);
2272
2273	break;
2274	}
2275
2276	if ($this->version == self::XLS_BIFF8) {
2277	// offset: 7; size: 1; XF_ROTATION: Text rotation angle
2278	$angle = ord($recordData[7]);
2279	$rotation = 0;
2280	if ($angle <= 90) {
2281	$rotation = $angle;
2282	} elseif ($angle <= 180) {
2283	$rotation = 90 - $angle;
2284	} elseif ($angle == 255) {
2285	$rotation = -165;
2286	}
2287	$objStyle->getAlignment()->setTextRotation($rotation);
2288
2289	// offset: 8; size: 1; Indentation, shrink to cell size, and text direction
2290	// bit: 3-0; mask: 0x0F; indent level
2291	$indent = (0x0F & ord($recordData[8])) >> 0;
2292	$objStyle->getAlignment()->setIndent($indent);
2293
2294	// bit: 4; mask: 0x10; 1 = shrink content to fit into cell
2295	$shrinkToFit = (0x10 & ord($recordData[8])) >> 4;
2296	switch ($shrinkToFit) {
2297	case 0:
2298	$objStyle->getAlignment()->setShrinkToFit(false);
2299
2300	break;
2301	case 1:
2302	$objStyle->getAlignment()->setShrinkToFit(true);
2303
2304	break;
2305	}
2306
2307	// offset: 9; size: 1; Flags used for attribute groups
2308
2309	// offset: 10; size: 4; Cell border lines and background area
2310	// bit: 3-0; mask: 0x0000000F; left style
2311	if ($bordersLeftStyle = Xls\Style\Border::lookup((0x0000000F & self::getInt4d($recordData, 10)) >> 0)) {
2312	$objStyle->getBorders()->getLeft()->setBorderStyle($bordersLeftStyle);
2313	}
2314	// bit: 7-4; mask: 0x000000F0; right style
2315	if ($bordersRightStyle = Xls\Style\Border::lookup((0x000000F0 & self::getInt4d($recordData, 10)) >> 4)) {
2316	$objStyle->getBorders()->getRight()->setBorderStyle($bordersRightStyle);
2317	}
2318	// bit: 11-8; mask: 0x00000F00; top style
2319	if ($bordersTopStyle = Xls\Style\Border::lookup((0x00000F00 & self::getInt4d($recordData, 10)) >> 8)) {
2320	$objStyle->getBorders()->getTop()->setBorderStyle($bordersTopStyle);
2321	}
2322	// bit: 15-12; mask: 0x0000F000; bottom style
2323	if ($bordersBottomStyle = Xls\Style\Border::lookup((0x0000F000 & self::getInt4d($recordData, 10)) >> 12)) {
2324	$objStyle->getBorders()->getBottom()->setBorderStyle($bordersBottomStyle);
2325	}
2326	// bit: 22-16; mask: 0x007F0000; left color
2327	$objStyle->getBorders()->getLeft()->colorIndex = (0x007F0000 & self::getInt4d($recordData, 10)) >> 16;
2328
2329	// bit: 29-23; mask: 0x3F800000; right color
2330	$objStyle->getBorders()->getRight()->colorIndex = (0x3F800000 & self::getInt4d($recordData, 10)) >> 23;
2331
2332	// bit: 30; mask: 0x40000000; 1 = diagonal line from top left to right bottom
2333	$diagonalDown = (0x40000000 & self::getInt4d($recordData, 10)) >> 30 ? true : false;
2334
2335	// bit: 31; mask: 0x80000000; 1 = diagonal line from bottom left to top right
2336	$diagonalUp = (0x80000000 & self::getInt4d($recordData, 10)) >> 31 ? true : false;
2337
2338	if ($diagonalUp == false && $diagonalDown == false) {
2339	$objStyle->getBorders()->setDiagonalDirection(Borders::DIAGONAL_NONE);
2340	} elseif ($diagonalUp == true && $diagonalDown == false) {
2341	$objStyle->getBorders()->setDiagonalDirection(Borders::DIAGONAL_UP);
2342	} elseif ($diagonalUp == false && $diagonalDown == true) {
2343	$objStyle->getBorders()->setDiagonalDirection(Borders::DIAGONAL_DOWN);
2344	} elseif ($diagonalUp == true && $diagonalDown == true) {
2345	$objStyle->getBorders()->setDiagonalDirection(Borders::DIAGONAL_BOTH);
2346	}
2347
2348	// offset: 14; size: 4;
2349	// bit: 6-0; mask: 0x0000007F; top color
2350	$objStyle->getBorders()->getTop()->colorIndex = (0x0000007F & self::getInt4d($recordData, 14)) >> 0;
2351
2352	// bit: 13-7; mask: 0x00003F80; bottom color
2353	$objStyle->getBorders()->getBottom()->colorIndex = (0x00003F80 & self::getInt4d($recordData, 14)) >> 7;
2354
2355	// bit: 20-14; mask: 0x001FC000; diagonal color
2356	$objStyle->getBorders()->getDiagonal()->colorIndex = (0x001FC000 & self::getInt4d($recordData, 14)) >> 14;
2357
2358	// bit: 24-21; mask: 0x01E00000; diagonal style
2359	if ($bordersDiagonalStyle = Xls\Style\Border::lookup((0x01E00000 & self::getInt4d($recordData, 14)) >> 21)) {
2360	$objStyle->getBorders()->getDiagonal()->setBorderStyle($bordersDiagonalStyle);
2361	}
2362
2363	// bit: 31-26; mask: 0xFC000000 fill pattern
2364	if ($fillType = Xls\Style\FillPattern::lookup((0xFC000000 & self::getInt4d($recordData, 14)) >> 26)) {
2365	$objStyle->getFill()->setFillType($fillType);
2366	}
2367	// offset: 18; size: 2; pattern and background colour
2368	// bit: 6-0; mask: 0x007F; color index for pattern color
2369	$objStyle->getFill()->startcolorIndex = (0x007F & self::getUInt2d($recordData, 18)) >> 0;
2370
2371	// bit: 13-7; mask: 0x3F80; color index for pattern background
2372	$objStyle->getFill()->endcolorIndex = (0x3F80 & self::getUInt2d($recordData, 18)) >> 7;
2373	} else {
2374	// BIFF5
2375
2376	// offset: 7; size: 1; Text orientation and flags
2377	$orientationAndFlags = ord($recordData[7]);
2378
2379	// bit: 1-0; mask: 0x03; XF_ORIENTATION: Text orientation
2380	$xfOrientation = (0x03 & $orientationAndFlags) >> 0;
2381	switch ($xfOrientation) {
2382	case 0:
2383	$objStyle->getAlignment()->setTextRotation(0);
2384
2385	break;
2386	case 1:
2387	$objStyle->getAlignment()->setTextRotation(-165);
2388
2389	break;
2390	case 2:
2391	$objStyle->getAlignment()->setTextRotation(90);
2392
2393	break;
2394	case 3:
2395	$objStyle->getAlignment()->setTextRotation(-90);
2396
2397	break;
2398	}
2399
2400	// offset: 8; size: 4; cell border lines and background area
2401	$borderAndBackground = self::getInt4d($recordData, 8);
2402
2403	// bit: 6-0; mask: 0x0000007F; color index for pattern color
2404	$objStyle->getFill()->startcolorIndex = (0x0000007F & $borderAndBackground) >> 0;
2405
2406	// bit: 13-7; mask: 0x00003F80; color index for pattern background
2407	$objStyle->getFill()->endcolorIndex = (0x00003F80 & $borderAndBackground) >> 7;
2408
2409	// bit: 21-16; mask: 0x003F0000; fill pattern
2410	$objStyle->getFill()->setFillType(Xls\Style\FillPattern::lookup((0x003F0000 & $borderAndBackground) >> 16));
2411
2412	// bit: 24-22; mask: 0x01C00000; bottom line style
2413	$objStyle->getBorders()->getBottom()->setBorderStyle(Xls\Style\Border::lookup((0x01C00000 & $borderAndBackground) >> 22));
2414
2415	// bit: 31-25; mask: 0xFE000000; bottom line color
2416	$objStyle->getBorders()->getBottom()->colorIndex = (0xFE000000 & $borderAndBackground) >> 25;
2417
2418	// offset: 12; size: 4; cell border lines
2419	$borderLines = self::getInt4d($recordData, 12);
2420
2421	// bit: 2-0; mask: 0x00000007; top line style
2422	$objStyle->getBorders()->getTop()->setBorderStyle(Xls\Style\Border::lookup((0x00000007 & $borderLines) >> 0));
2423
2424	// bit: 5-3; mask: 0x00000038; left line style
2425	$objStyle->getBorders()->getLeft()->setBorderStyle(Xls\Style\Border::lookup((0x00000038 & $borderLines) >> 3));
2426
2427	// bit: 8-6; mask: 0x000001C0; right line style
2428	$objStyle->getBorders()->getRight()->setBorderStyle(Xls\Style\Border::lookup((0x000001C0 & $borderLines) >> 6));
2429
2430	// bit: 15-9; mask: 0x0000FE00; top line color index
2431	$objStyle->getBorders()->getTop()->colorIndex = (0x0000FE00 & $borderLines) >> 9;
2432
2433	// bit: 22-16; mask: 0x007F0000; left line color index
2434	$objStyle->getBorders()->getLeft()->colorIndex = (0x007F0000 & $borderLines) >> 16;
2435
2436	// bit: 29-23; mask: 0x3F800000; right line color index
2437	$objStyle->getBorders()->getRight()->colorIndex = (0x3F800000 & $borderLines) >> 23;
2438	}
2439
2440	// add cellStyleXf or cellXf and update mapping
2441	if ($isCellStyleXf) {
2442	// we only read one style XF record which is always the first
2443	if ($this->xfIndex == 0) {
2444	$this->spreadsheet->addCellStyleXf($objStyle);
2445	$this->mapCellStyleXfIndex[$this->xfIndex] = 0;
2446	}
2447	} else {
2448	// we read all cell XF records
2449	$this->spreadsheet->addCellXf($objStyle);
2450	$this->mapCellXfIndex[$this->xfIndex] = count($this->spreadsheet->getCellXfCollection()) - 1;
2451	}
2452
2453	// update XF index for when we read next record
2454	++$this->xfIndex;
2455	}
2456	}
2457
2458	private function readXfExt()
2459	{
2460	$length = self::getUInt2d($this->data, $this->pos + 2);
2461	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
2462
2463	// move stream pointer to next record
2464	$this->pos += 4 + $length;
2465
2466	if (!$this->readDataOnly) {
2467	// offset: 0; size: 2; 0x087D = repeated header
2468
2469	// offset: 2; size: 2
2470
2471	// offset: 4; size: 8; not used
2472
2473	// offset: 12; size: 2; record version
2474
2475	// offset: 14; size: 2; index to XF record which this record modifies
2476	$ixfe = self::getUInt2d($recordData, 14);
2477
2478	// offset: 16; size: 2; not used
2479
2480	// offset: 18; size: 2; number of extension properties that follow
2481	$cexts = self::getUInt2d($recordData, 18);
2482
2483	// start reading the actual extension data
2484	$offset = 20;
2485	while ($offset < $length) {
2486	// extension type
2487	$extType = self::getUInt2d($recordData, $offset);
2488
2489	// extension length
2490	$cb = self::getUInt2d($recordData, $offset + 2);
2491
2492	// extension data
2493	$extData = substr($recordData, $offset + 4, $cb);
2494
2495	switch ($extType) {
2496	case 4: // fill start color
2497	$xclfType = self::getUInt2d($extData, 0); // color type
2498	$xclrValue = substr($extData, 4, 4); // color value (value based on color type)
2499
2500	if ($xclfType == 2) {
2501	$rgb = sprintf('%02X%02X%02X', ord($xclrValue[0]), ord($xclrValue[1]), ord($xclrValue[2]));
2502
2503	// modify the relevant style property
2504	if (isset($this->mapCellXfIndex[$ixfe])) {
2505	$fill = $this->spreadsheet->getCellXfByIndex($this->mapCellXfIndex[$ixfe])->getFill();
2506	$fill->getStartColor()->setRGB($rgb);
2507	unset($fill->startcolorIndex); // normal color index does not apply, discard
2508	}
2509	}
2510
2511	break;
2512	case 5: // fill end color
2513	$xclfType = self::getUInt2d($extData, 0); // color type
2514	$xclrValue = substr($extData, 4, 4); // color value (value based on color type)
2515
2516	if ($xclfType == 2) {
2517	$rgb = sprintf('%02X%02X%02X', ord($xclrValue[0]), ord($xclrValue[1]), ord($xclrValue[2]));
2518
2519	// modify the relevant style property
2520	if (isset($this->mapCellXfIndex[$ixfe])) {
2521	$fill = $this->spreadsheet->getCellXfByIndex($this->mapCellXfIndex[$ixfe])->getFill();
2522	$fill->getEndColor()->setRGB($rgb);
2523	unset($fill->endcolorIndex); // normal color index does not apply, discard
2524	}
2525	}
2526
2527	break;
2528	case 7: // border color top
2529	$xclfType = self::getUInt2d($extData, 0); // color type
2530	$xclrValue = substr($extData, 4, 4); // color value (value based on color type)
2531
2532	if ($xclfType == 2) {
2533	$rgb = sprintf('%02X%02X%02X', ord($xclrValue[0]), ord($xclrValue[1]), ord($xclrValue[2]));
2534
2535	// modify the relevant style property
2536	if (isset($this->mapCellXfIndex[$ixfe])) {
2537	$top = $this->spreadsheet->getCellXfByIndex($this->mapCellXfIndex[$ixfe])->getBorders()->getTop();
2538	$top->getColor()->setRGB($rgb);
2539	unset($top->colorIndex); // normal color index does not apply, discard
2540	}
2541	}
2542
2543	break;
2544	case 8: // border color bottom
2545	$xclfType = self::getUInt2d($extData, 0); // color type
2546	$xclrValue = substr($extData, 4, 4); // color value (value based on color type)
2547
2548	if ($xclfType == 2) {
2549	$rgb = sprintf('%02X%02X%02X', ord($xclrValue[0]), ord($xclrValue[1]), ord($xclrValue[2]));
2550
2551	// modify the relevant style property
2552	if (isset($this->mapCellXfIndex[$ixfe])) {
2553	$bottom = $this->spreadsheet->getCellXfByIndex($this->mapCellXfIndex[$ixfe])->getBorders()->getBottom();
2554	$bottom->getColor()->setRGB($rgb);
2555	unset($bottom->colorIndex); // normal color index does not apply, discard
2556	}
2557	}
2558
2559	break;
2560	case 9: // border color left
2561	$xclfType = self::getUInt2d($extData, 0); // color type
2562	$xclrValue = substr($extData, 4, 4); // color value (value based on color type)
2563
2564	if ($xclfType == 2) {
2565	$rgb = sprintf('%02X%02X%02X', ord($xclrValue[0]), ord($xclrValue[1]), ord($xclrValue[2]));
2566
2567	// modify the relevant style property
2568	if (isset($this->mapCellXfIndex[$ixfe])) {
2569	$left = $this->spreadsheet->getCellXfByIndex($this->mapCellXfIndex[$ixfe])->getBorders()->getLeft();
2570	$left->getColor()->setRGB($rgb);
2571	unset($left->colorIndex); // normal color index does not apply, discard
2572	}
2573	}
2574
2575	break;
2576	case 10: // border color right
2577	$xclfType = self::getUInt2d($extData, 0); // color type
2578	$xclrValue = substr($extData, 4, 4); // color value (value based on color type)
2579
2580	if ($xclfType == 2) {
2581	$rgb = sprintf('%02X%02X%02X', ord($xclrValue[0]), ord($xclrValue[1]), ord($xclrValue[2]));
2582
2583	// modify the relevant style property
2584	if (isset($this->mapCellXfIndex[$ixfe])) {
2585	$right = $this->spreadsheet->getCellXfByIndex($this->mapCellXfIndex[$ixfe])->getBorders()->getRight();
2586	$right->getColor()->setRGB($rgb);
2587	unset($right->colorIndex); // normal color index does not apply, discard
2588	}
2589	}
2590
2591	break;
2592	case 11: // border color diagonal
2593	$xclfType = self::getUInt2d($extData, 0); // color type
2594	$xclrValue = substr($extData, 4, 4); // color value (value based on color type)
2595
2596	if ($xclfType == 2) {
2597	$rgb = sprintf('%02X%02X%02X', ord($xclrValue[0]), ord($xclrValue[1]), ord($xclrValue[2]));
2598
2599	// modify the relevant style property
2600	if (isset($this->mapCellXfIndex[$ixfe])) {
2601	$diagonal = $this->spreadsheet->getCellXfByIndex($this->mapCellXfIndex[$ixfe])->getBorders()->getDiagonal();
2602	$diagonal->getColor()->setRGB($rgb);
2603	unset($diagonal->colorIndex); // normal color index does not apply, discard
2604	}
2605	}
2606
2607	break;
2608	case 13: // font color
2609	$xclfType = self::getUInt2d($extData, 0); // color type
2610	$xclrValue = substr($extData, 4, 4); // color value (value based on color type)
2611
2612	if ($xclfType == 2) {
2613	$rgb = sprintf('%02X%02X%02X', ord($xclrValue[0]), ord($xclrValue[1]), ord($xclrValue[2]));
2614
2615	// modify the relevant style property
2616	if (isset($this->mapCellXfIndex[$ixfe])) {
2617	$font = $this->spreadsheet->getCellXfByIndex($this->mapCellXfIndex[$ixfe])->getFont();
2618	$font->getColor()->setRGB($rgb);
2619	unset($font->colorIndex); // normal color index does not apply, discard
2620	}
2621	}
2622
2623	break;
2624	}
2625
2626	$offset += $cb;
2627	}
2628	}
2629	}
2630
2631	/**
2632	* Read STYLE record.
2633	*/
2634	private function readStyle()
2635	{
2636	$length = self::getUInt2d($this->data, $this->pos + 2);
2637	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
2638
2639	// move stream pointer to next record
2640	$this->pos += 4 + $length;
2641
2642	if (!$this->readDataOnly) {
2643	// offset: 0; size: 2; index to XF record and flag for built-in style
2644	$ixfe = self::getUInt2d($recordData, 0);
2645
2646	// bit: 11-0; mask 0x0FFF; index to XF record
2647	$xfIndex = (0x0FFF & $ixfe) >> 0;
2648
2649	// bit: 15; mask 0x8000; 0 = user-defined style, 1 = built-in style
2650	$isBuiltIn = (bool) ((0x8000 & $ixfe) >> 15);
2651
2652	if ($isBuiltIn) {
2653	// offset: 2; size: 1; identifier for built-in style
2654	$builtInId = ord($recordData[2]);
2655
2656	switch ($builtInId) {
2657	case 0x00:
2658	// currently, we are not using this for anything
2659	break;
2660	default:
2661	break;
2662	}
2663	}
2664	// user-defined; not supported by PhpSpreadsheet
2665	}
2666	}
2667
2668	/**
2669	* Read PALETTE record.
2670	*/
2671	private function readPalette()
2672	{
2673	$length = self::getUInt2d($this->data, $this->pos + 2);
2674	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
2675
2676	// move stream pointer to next record
2677	$this->pos += 4 + $length;
2678
2679	if (!$this->readDataOnly) {
2680	// offset: 0; size: 2; number of following colors
2681	$nm = self::getUInt2d($recordData, 0);
2682
2683	// list of RGB colors
2684	for ($i = 0; $i < $nm; ++$i) {
2685	$rgb = substr($recordData, 2 + 4 * $i, 4);
2686	$this->palette[] = self::readRGB($rgb);
2687	}
2688	}
2689	}
2690
2691	/**
2692	* SHEET.
2693	*
2694	* This record is located in the Workbook Globals
2695	* Substream and represents a sheet inside the workbook.
2696	* One SHEET record is written for each sheet. It stores the
2697	* sheet name and a stream offset to the BOF record of the
2698	* respective Sheet Substream within the Workbook Stream.
2699	*
2700	* -- "OpenOffice.org's Documentation of the Microsoft
2701	* Excel File Format"
2702	*/
2703	private function readSheet()
2704	{
2705	$length = self::getUInt2d($this->data, $this->pos + 2);
2706	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
2707
2708	// offset: 0; size: 4; absolute stream position of the BOF record of the sheet
2709	// NOTE: not encrypted
2710	$rec_offset = self::getInt4d($this->data, $this->pos + 4);
2711
2712	// move stream pointer to next record
2713	$this->pos += 4 + $length;
2714
2715	// offset: 4; size: 1; sheet state
2716	switch (ord($recordData[4])) {
2717	case 0x00:
2718	$sheetState = Worksheet::SHEETSTATE_VISIBLE;
2719
2720	break;
2721	case 0x01:
2722	$sheetState = Worksheet::SHEETSTATE_HIDDEN;
2723
2724	break;
2725	case 0x02:
2726	$sheetState = Worksheet::SHEETSTATE_VERYHIDDEN;
2727
2728	break;
2729	default:
2730	$sheetState = Worksheet::SHEETSTATE_VISIBLE;
2731
2732	break;
2733	}
2734
2735	// offset: 5; size: 1; sheet type
2736	$sheetType = ord($recordData[5]);
2737
2738	// offset: 6; size: var; sheet name
2739	if ($this->version == self::XLS_BIFF8) {
2740	$string = self::readUnicodeStringShort(substr($recordData, 6));
2741	$rec_name = $string['value'];
2742	} elseif ($this->version == self::XLS_BIFF7) {
2743	$string = $this->readByteStringShort(substr($recordData, 6));
2744	$rec_name = $string['value'];
2745	}
2746
2747	$this->sheets[] = [
2748	'name' => $rec_name,
2749	'offset' => $rec_offset,
2750	'sheetState' => $sheetState,
2751	'sheetType' => $sheetType,
2752	];
2753	}
2754
2755	/**
2756	* Read EXTERNALBOOK record.
2757	*/
2758	private function readExternalBook()
2759	{
2760	$length = self::getUInt2d($this->data, $this->pos + 2);
2761	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
2762
2763	// move stream pointer to next record
2764	$this->pos += 4 + $length;
2765
2766	// offset within record data
2767	$offset = 0;
2768
2769	// there are 4 types of records
2770	if (strlen($recordData) > 4) {
2771	// external reference
2772	// offset: 0; size: 2; number of sheet names ($nm)
2773	$nm = self::getUInt2d($recordData, 0);
2774	$offset += 2;
2775
2776	// offset: 2; size: var; encoded URL without sheet name (Unicode string, 16-bit length)
2777	$encodedUrlString = self::readUnicodeStringLong(substr($recordData, 2));
2778	$offset += $encodedUrlString['size'];
2779
2780	// offset: var; size: var; list of $nm sheet names (Unicode strings, 16-bit length)
2781	$externalSheetNames = [];
2782	for ($i = 0; $i < $nm; ++$i) {
2783	$externalSheetNameString = self::readUnicodeStringLong(substr($recordData, $offset));
2784	$externalSheetNames[] = $externalSheetNameString['value'];
2785	$offset += $externalSheetNameString['size'];
2786	}
2787
2788	// store the record data
2789	$this->externalBooks[] = [
2790	'type' => 'external',
2791	'encodedUrl' => $encodedUrlString['value'],
2792	'externalSheetNames' => $externalSheetNames,
2793	];
2794	} elseif (substr($recordData, 2, 2) == pack('CC', 0x01, 0x04)) {
2795	// internal reference
2796	// offset: 0; size: 2; number of sheet in this document
2797	// offset: 2; size: 2; 0x01 0x04
2798	$this->externalBooks[] = [
2799	'type' => 'internal',
2800	];
2801	} elseif (substr($recordData, 0, 4) == pack('vCC', 0x0001, 0x01, 0x3A)) {
2802	// add-in function
2803	// offset: 0; size: 2; 0x0001
2804	$this->externalBooks[] = [
2805	'type' => 'addInFunction',
2806	];
2807	} elseif (substr($recordData, 0, 2) == pack('v', 0x0000)) {
2808	// DDE links, OLE links
2809	// offset: 0; size: 2; 0x0000
2810	// offset: 2; size: var; encoded source document name
2811	$this->externalBooks[] = [
2812	'type' => 'DDEorOLE',
2813	];
2814	}
2815	}
2816
2817	/**
2818	* Read EXTERNNAME record.
2819	*/
2820	private function readExternName()
2821	{
2822	$length = self::getUInt2d($this->data, $this->pos + 2);
2823	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
2824
2825	// move stream pointer to next record
2826	$this->pos += 4 + $length;
2827
2828	// external sheet references provided for named cells
2829	if ($this->version == self::XLS_BIFF8) {
2830	// offset: 0; size: 2; options
2831	$options = self::getUInt2d($recordData, 0);
2832
2833	// offset: 2; size: 2;
2834
2835	// offset: 4; size: 2; not used
2836
2837	// offset: 6; size: var
2838	$nameString = self::readUnicodeStringShort(substr($recordData, 6));
2839
2840	// offset: var; size: var; formula data
2841	$offset = 6 + $nameString['size'];
2842	$formula = $this->getFormulaFromStructure(substr($recordData, $offset));
2843
2844	$this->externalNames[] = [
2845	'name' => $nameString['value'],
2846	'formula' => $formula,
2847	];
2848	}
2849	}
2850
2851	/**
2852	* Read EXTERNSHEET record.
2853	*/
2854	private function readExternSheet()
2855	{
2856	$length = self::getUInt2d($this->data, $this->pos + 2);
2857	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
2858
2859	// move stream pointer to next record
2860	$this->pos += 4 + $length;
2861
2862	// external sheet references provided for named cells
2863	if ($this->version == self::XLS_BIFF8) {
2864	// offset: 0; size: 2; number of following ref structures
2865	$nm = self::getUInt2d($recordData, 0);
2866	for ($i = 0; $i < $nm; ++$i) {
2867	$this->ref[] = [
2868	// offset: 2 + 6 * $i; index to EXTERNALBOOK record
2869	'externalBookIndex' => self::getUInt2d($recordData, 2 + 6 * $i),
2870	// offset: 4 + 6 * $i; index to first sheet in EXTERNALBOOK record
2871	'firstSheetIndex' => self::getUInt2d($recordData, 4 + 6 * $i),
2872	// offset: 6 + 6 * $i; index to last sheet in EXTERNALBOOK record
2873	'lastSheetIndex' => self::getUInt2d($recordData, 6 + 6 * $i),
2874	];
2875	}
2876	}
2877	}
2878
2879	/**
2880	* DEFINEDNAME.
2881	*
2882	* This record is part of a Link Table. It contains the name
2883	* and the token array of an internal defined name. Token
2884	* arrays of defined names contain tokens with aberrant
2885	* token classes.
2886	*
2887	* -- "OpenOffice.org's Documentation of the Microsoft
2888	* Excel File Format"
2889	*/
2890	private function readDefinedName()
2891	{
2892	$length = self::getUInt2d($this->data, $this->pos + 2);
2893	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
2894
2895	// move stream pointer to next record
2896	$this->pos += 4 + $length;
2897
2898	if ($this->version == self::XLS_BIFF8) {
2899	// retrieves named cells
2900
2901	// offset: 0; size: 2; option flags
2902	$opts = self::getUInt2d($recordData, 0);
2903
2904	// bit: 5; mask: 0x0020; 0 = user-defined name, 1 = built-in-name
2905	$isBuiltInName = (0x0020 & $opts) >> 5;
2906
2907	// offset: 2; size: 1; keyboard shortcut
2908
2909	// offset: 3; size: 1; length of the name (character count)
2910	$nlen = ord($recordData[3]);
2911
2912	// offset: 4; size: 2; size of the formula data (it can happen that this is zero)
2913	// note: there can also be additional data, this is not included in $flen
2914	$flen = self::getUInt2d($recordData, 4);
2915
2916	// offset: 8; size: 2; 0=Global name, otherwise index to sheet (1-based)
2917	$scope = self::getUInt2d($recordData, 8);
2918
2919	// offset: 14; size: var; Name (Unicode string without length field)
2920	$string = self::readUnicodeString(substr($recordData, 14), $nlen);
2921
2922	// offset: var; size: $flen; formula data
2923	$offset = 14 + $string['size'];
2924	$formulaStructure = pack('v', $flen) . substr($recordData, $offset);
2925
2926	try {
2927	$formula = $this->getFormulaFromStructure($formulaStructure);
2928	} catch (PhpSpreadsheetException $e) {
2929	$formula = '';
2930	}
2931
2932	$this->definedname[] = [
2933	'isBuiltInName' => $isBuiltInName,
2934	'name' => $string['value'],
2935	'formula' => $formula,
2936	'scope' => $scope,
2937	];
2938	}
2939	}
2940
2941	/**
2942	* Read MSODRAWINGGROUP record.
2943	*/
2944	private function readMsoDrawingGroup()
2945	{
2946	$length = self::getUInt2d($this->data, $this->pos + 2);
2947
2948	// get spliced record data
2949	$splicedRecordData = $this->getSplicedRecordData();
2950	$recordData = $splicedRecordData['recordData'];
2951
2952	$this->drawingGroupData .= $recordData;
2953	}
2954
2955	/**
2956	* SST - Shared String Table.
2957	*
2958	* This record contains a list of all strings used anywhere
2959	* in the workbook. Each string occurs only once. The
2960	* workbook uses indexes into the list to reference the
2961	* strings.
2962	*
2963	* -- "OpenOffice.org's Documentation of the Microsoft
2964	* Excel File Format"
2965	*/
2966	private function readSst()
2967	{
2968	// offset within (spliced) record data
2969	$pos = 0;
2970
2971	// get spliced record data
2972	$splicedRecordData = $this->getSplicedRecordData();
2973
2974	$recordData = $splicedRecordData['recordData'];
2975	$spliceOffsets = $splicedRecordData['spliceOffsets'];
2976
2977	// offset: 0; size: 4; total number of strings in the workbook
2978	$pos += 4;
2979
2980	// offset: 4; size: 4; number of following strings ($nm)
2981	$nm = self::getInt4d($recordData, 4);
2982	$pos += 4;
2983
2984	// loop through the Unicode strings (16-bit length)
2985	for ($i = 0; $i < $nm; ++$i) {
2986	// number of characters in the Unicode string
2987	$numChars = self::getUInt2d($recordData, $pos);
2988	$pos += 2;
2989
2990	// option flags
2991	$optionFlags = ord($recordData[$pos]);
2992	++$pos;
2993
2994	// bit: 0; mask: 0x01; 0 = compressed; 1 = uncompressed
2995	$isCompressed = (($optionFlags & 0x01) == 0);
2996
2997	// bit: 2; mask: 0x02; 0 = ordinary; 1 = Asian phonetic
2998	$hasAsian = (($optionFlags & 0x04) != 0);
2999
3000	// bit: 3; mask: 0x03; 0 = ordinary; 1 = Rich-Text
3001	$hasRichText = (($optionFlags & 0x08) != 0);
3002
3003	if ($hasRichText) {
3004	// number of Rich-Text formatting runs
3005	$formattingRuns = self::getUInt2d($recordData, $pos);
3006	$pos += 2;
3007	}
3008
3009	if ($hasAsian) {
3010	// size of Asian phonetic setting
3011	$extendedRunLength = self::getInt4d($recordData, $pos);
3012	$pos += 4;
3013	}
3014
3015	// expected byte length of character array if not split
3016	$len = ($isCompressed) ? $numChars : $numChars * 2;
3017
3018	// look up limit position
3019	foreach ($spliceOffsets as $spliceOffset) {
3020	// it can happen that the string is empty, therefore we need
3021	// <= and not just <
3022	if ($pos <= $spliceOffset) {
3023	$limitpos = $spliceOffset;
3024
3025	break;
3026	}
3027	}
3028
3029	if ($pos + $len <= $limitpos) {
3030	// character array is not split between records
3031
3032	$retstr = substr($recordData, $pos, $len);
3033	$pos += $len;
3034	} else {
3035	// character array is split between records
3036
3037	// first part of character array
3038	$retstr = substr($recordData, $pos, $limitpos - $pos);
3039
3040	$bytesRead = $limitpos - $pos;
3041
3042	// remaining characters in Unicode string
3043	$charsLeft = $numChars - (($isCompressed) ? $bytesRead : ($bytesRead / 2));
3044
3045	$pos = $limitpos;
3046
3047	// keep reading the characters
3048	while ($charsLeft > 0) {
3049	// look up next limit position, in case the string span more than one continue record
3050	foreach ($spliceOffsets as $spliceOffset) {
3051	if ($pos < $spliceOffset) {
3052	$limitpos = $spliceOffset;
3053
3054	break;
3055	}
3056	}
3057
3058	// repeated option flags
3059	// OpenOffice.org documentation 5.21
3060	$option = ord($recordData[$pos]);
3061	++$pos;
3062
3063	if ($isCompressed && ($option == 0)) {
3064	// 1st fragment compressed
3065	// this fragment compressed
3066	$len = min($charsLeft, $limitpos - $pos);
3067	$retstr .= substr($recordData, $pos, $len);
3068	$charsLeft -= $len;
3069	$isCompressed = true;
3070	} elseif (!$isCompressed && ($option != 0)) {
3071	// 1st fragment uncompressed
3072	// this fragment uncompressed
3073	$len = min($charsLeft * 2, $limitpos - $pos);
3074	$retstr .= substr($recordData, $pos, $len);
3075	$charsLeft -= $len / 2;
3076	$isCompressed = false;
3077	} elseif (!$isCompressed && ($option == 0)) {
3078	// 1st fragment uncompressed
3079	// this fragment compressed
3080	$len = min($charsLeft, $limitpos - $pos);
3081	for ($j = 0; $j < $len; ++$j) {
3082	$retstr .= $recordData[$pos + $j]
3083	. chr(0);
3084	}
3085	$charsLeft -= $len;
3086	$isCompressed = false;
3087	} else {
3088	// 1st fragment compressed
3089	// this fragment uncompressed
3090	$newstr = '';
3091	$jMax = strlen($retstr);
3092	for ($j = 0; $j < $jMax; ++$j) {
3093	$newstr .= $retstr[$j] . chr(0);
3094	}
3095	$retstr = $newstr;
3096	$len = min($charsLeft * 2, $limitpos - $pos);
3097	$retstr .= substr($recordData, $pos, $len);
3098	$charsLeft -= $len / 2;
3099	$isCompressed = false;
3100	}
3101
3102	$pos += $len;
3103	}
3104	}
3105
3106	// convert to UTF-8
3107	$retstr = self::encodeUTF16($retstr, $isCompressed);
3108
3109	// read additional Rich-Text information, if any
3110	$fmtRuns = [];
3111	if ($hasRichText) {
3112	// list of formatting runs
3113	for ($j = 0; $j < $formattingRuns; ++$j) {
3114	// first formatted character; zero-based
3115	$charPos = self::getUInt2d($recordData, $pos + $j * 4);
3116
3117	// index to font record
3118	$fontIndex = self::getUInt2d($recordData, $pos + 2 + $j * 4);
3119
3120	$fmtRuns[] = [
3121	'charPos' => $charPos,
3122	'fontIndex' => $fontIndex,
3123	];
3124	}
3125	$pos += 4 * $formattingRuns;
3126	}
3127
3128	// read additional Asian phonetics information, if any
3129	if ($hasAsian) {
3130	// For Asian phonetic settings, we skip the extended string data
3131	$pos += $extendedRunLength;
3132	}
3133
3134	// store the shared sting
3135	$this->sst[] = [
3136	'value' => $retstr,
3137	'fmtRuns' => $fmtRuns,
3138	];
3139	}
3140
3141	// getSplicedRecordData() takes care of moving current position in data stream
3142	}
3143
3144	/**
3145	* Read PRINTGRIDLINES record.
3146	*/
3147	private function readPrintGridlines()
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	if ($this->version == self::XLS_BIFF8 && !$this->readDataOnly) {
3156	// offset: 0; size: 2; 0 = do not print sheet grid lines; 1 = print sheet gridlines
3157	$printGridlines = (bool) self::getUInt2d($recordData, 0);
3158	$this->phpSheet->setPrintGridlines($printGridlines);
3159	}
3160	}
3161
3162	/**
3163	* Read DEFAULTROWHEIGHT record.
3164	*/
3165	private function readDefaultRowHeight()
3166	{
3167	$length = self::getUInt2d($this->data, $this->pos + 2);
3168	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
3169
3170	// move stream pointer to next record
3171	$this->pos += 4 + $length;
3172
3173	// offset: 0; size: 2; option flags
3174	// offset: 2; size: 2; default height for unused rows, (twips 1/20 point)
3175	$height = self::getUInt2d($recordData, 2);
3176	$this->phpSheet->getDefaultRowDimension()->setRowHeight($height / 20);
3177	}
3178
3179	/**
3180	* Read SHEETPR record.
3181	*/
3182	private function readSheetPr()
3183	{
3184	$length = self::getUInt2d($this->data, $this->pos + 2);
3185	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
3186
3187	// move stream pointer to next record
3188	$this->pos += 4 + $length;
3189
3190	// offset: 0; size: 2
3191
3192	// bit: 6; mask: 0x0040; 0 = outline buttons above outline group
3193	$isSummaryBelow = (0x0040 & self::getUInt2d($recordData, 0)) >> 6;
3194	$this->phpSheet->setShowSummaryBelow($isSummaryBelow);
3195
3196	// bit: 7; mask: 0x0080; 0 = outline buttons left of outline group
3197	$isSummaryRight = (0x0080 & self::getUInt2d($recordData, 0)) >> 7;
3198	$this->phpSheet->setShowSummaryRight($isSummaryRight);
3199
3200	// bit: 8; mask: 0x100; 0 = scale printout in percent, 1 = fit printout to number of pages
3201	// this corresponds to radio button setting in page setup dialog in Excel
3202	$this->isFitToPages = (bool) ((0x0100 & self::getUInt2d($recordData, 0)) >> 8);
3203	}
3204
3205	/**
3206	* Read HORIZONTALPAGEBREAKS record.
3207	*/
3208	private function readHorizontalPageBreaks()
3209	{
3210	$length = self::getUInt2d($this->data, $this->pos + 2);
3211	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
3212
3213	// move stream pointer to next record
3214	$this->pos += 4 + $length;
3215
3216	if ($this->version == self::XLS_BIFF8 && !$this->readDataOnly) {
3217	// offset: 0; size: 2; number of the following row index structures
3218	$nm = self::getUInt2d($recordData, 0);
3219
3220	// offset: 2; size: 6 * $nm; list of $nm row index structures
3221	for ($i = 0; $i < $nm; ++$i) {
3222	$r = self::getUInt2d($recordData, 2 + 6 * $i);
3223	$cf = self::getUInt2d($recordData, 2 + 6 * $i + 2);
3224	$cl = self::getUInt2d($recordData, 2 + 6 * $i + 4);
3225
3226	// not sure why two column indexes are necessary?
3227	$this->phpSheet->setBreakByColumnAndRow($cf + 1, $r, Worksheet::BREAK_ROW);
3228	}
3229	}
3230	}
3231
3232	/**
3233	* Read VERTICALPAGEBREAKS record.
3234	*/
3235	private function readVerticalPageBreaks()
3236	{
3237	$length = self::getUInt2d($this->data, $this->pos + 2);
3238	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
3239
3240	// move stream pointer to next record
3241	$this->pos += 4 + $length;
3242
3243	if ($this->version == self::XLS_BIFF8 && !$this->readDataOnly) {
3244	// offset: 0; size: 2; number of the following column index structures
3245	$nm = self::getUInt2d($recordData, 0);
3246
3247	// offset: 2; size: 6 * $nm; list of $nm row index structures
3248	for ($i = 0; $i < $nm; ++$i) {
3249	$c = self::getUInt2d($recordData, 2 + 6 * $i);
3250	$rf = self::getUInt2d($recordData, 2 + 6 * $i + 2);
3251	$rl = self::getUInt2d($recordData, 2 + 6 * $i + 4);
3252
3253	// not sure why two row indexes are necessary?
3254	$this->phpSheet->setBreakByColumnAndRow($c + 1, $rf, Worksheet::BREAK_COLUMN);
3255	}
3256	}
3257	}
3258
3259	/**
3260	* Read HEADER record.
3261	*/
3262	private function readHeader()
3263	{
3264	$length = self::getUInt2d($this->data, $this->pos + 2);
3265	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
3266
3267	// move stream pointer to next record
3268	$this->pos += 4 + $length;
3269
3270	if (!$this->readDataOnly) {
3271	// offset: 0; size: var
3272	// realized that $recordData can be empty even when record exists
3273	if ($recordData) {
3274	if ($this->version == self::XLS_BIFF8) {
3275	$string = self::readUnicodeStringLong($recordData);
3276	} else {
3277	$string = $this->readByteStringShort($recordData);
3278	}
3279
3280	$this->phpSheet->getHeaderFooter()->setOddHeader($string['value']);
3281	$this->phpSheet->getHeaderFooter()->setEvenHeader($string['value']);
3282	}
3283	}
3284	}
3285
3286	/**
3287	* Read FOOTER record.
3288	*/
3289	private function readFooter()
3290	{
3291	$length = self::getUInt2d($this->data, $this->pos + 2);
3292	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
3293
3294	// move stream pointer to next record
3295	$this->pos += 4 + $length;
3296
3297	if (!$this->readDataOnly) {
3298	// offset: 0; size: var
3299	// realized that $recordData can be empty even when record exists
3300	if ($recordData) {
3301	if ($this->version == self::XLS_BIFF8) {
3302	$string = self::readUnicodeStringLong($recordData);
3303	} else {
3304	$string = $this->readByteStringShort($recordData);
3305	}
3306	$this->phpSheet->getHeaderFooter()->setOddFooter($string['value']);
3307	$this->phpSheet->getHeaderFooter()->setEvenFooter($string['value']);
3308	}
3309	}
3310	}
3311
3312	/**
3313	* Read HCENTER record.
3314	*/
3315	private function readHcenter()
3316	{
3317	$length = self::getUInt2d($this->data, $this->pos + 2);
3318	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
3319
3320	// move stream pointer to next record
3321	$this->pos += 4 + $length;
3322
3323	if (!$this->readDataOnly) {
3324	// offset: 0; size: 2; 0 = print sheet left aligned, 1 = print sheet centered horizontally
3325	$isHorizontalCentered = (bool) self::getUInt2d($recordData, 0);
3326
3327	$this->phpSheet->getPageSetup()->setHorizontalCentered($isHorizontalCentered);
3328	}
3329	}
3330
3331	/**
3332	* Read VCENTER record.
3333	*/
3334	private function readVcenter()
3335	{
3336	$length = self::getUInt2d($this->data, $this->pos + 2);
3337	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
3338
3339	// move stream pointer to next record
3340	$this->pos += 4 + $length;
3341
3342	if (!$this->readDataOnly) {
3343	// offset: 0; size: 2; 0 = print sheet aligned at top page border, 1 = print sheet vertically centered
3344	$isVerticalCentered = (bool) self::getUInt2d($recordData, 0);
3345
3346	$this->phpSheet->getPageSetup()->setVerticalCentered($isVerticalCentered);
3347	}
3348	}
3349
3350	/**
3351	* Read LEFTMARGIN record.
3352	*/
3353	private function readLeftMargin()
3354	{
3355	$length = self::getUInt2d($this->data, $this->pos + 2);
3356	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
3357
3358	// move stream pointer to next record
3359	$this->pos += 4 + $length;
3360
3361	if (!$this->readDataOnly) {
3362	// offset: 0; size: 8
3363	$this->phpSheet->getPageMargins()->setLeft(self::extractNumber($recordData));
3364	}
3365	}
3366
3367	/**
3368	* Read RIGHTMARGIN record.
3369	*/
3370	private function readRightMargin()
3371	{
3372	$length = self::getUInt2d($this->data, $this->pos + 2);
3373	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
3374
3375	// move stream pointer to next record
3376	$this->pos += 4 + $length;
3377
3378	if (!$this->readDataOnly) {
3379	// offset: 0; size: 8
3380	$this->phpSheet->getPageMargins()->setRight(self::extractNumber($recordData));
3381	}
3382	}
3383
3384	/**
3385	* Read TOPMARGIN record.
3386	*/
3387	private function readTopMargin()
3388	{
3389	$length = self::getUInt2d($this->data, $this->pos + 2);
3390	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
3391
3392	// move stream pointer to next record
3393	$this->pos += 4 + $length;
3394
3395	if (!$this->readDataOnly) {
3396	// offset: 0; size: 8
3397	$this->phpSheet->getPageMargins()->setTop(self::extractNumber($recordData));
3398	}
3399	}
3400
3401	/**
3402	* Read BOTTOMMARGIN record.
3403	*/
3404	private function readBottomMargin()
3405	{
3406	$length = self::getUInt2d($this->data, $this->pos + 2);
3407	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
3408
3409	// move stream pointer to next record
3410	$this->pos += 4 + $length;
3411
3412	if (!$this->readDataOnly) {
3413	// offset: 0; size: 8
3414	$this->phpSheet->getPageMargins()->setBottom(self::extractNumber($recordData));
3415	}
3416	}
3417
3418	/**
3419	* Read PAGESETUP record.
3420	*/
3421	private function readPageSetup()
3422	{
3423	$length = self::getUInt2d($this->data, $this->pos + 2);
3424	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
3425
3426	// move stream pointer to next record
3427	$this->pos += 4 + $length;
3428
3429	if (!$this->readDataOnly) {
3430	// offset: 0; size: 2; paper size
3431	$paperSize = self::getUInt2d($recordData, 0);
3432
3433	// offset: 2; size: 2; scaling factor
3434	$scale = self::getUInt2d($recordData, 2);
3435
3436	// offset: 6; size: 2; fit worksheet width to this number of pages, 0 = use as many as needed
3437	$fitToWidth = self::getUInt2d($recordData, 6);
3438
3439	// offset: 8; size: 2; fit worksheet height to this number of pages, 0 = use as many as needed
3440	$fitToHeight = self::getUInt2d($recordData, 8);
3441
3442	// offset: 10; size: 2; option flags
3443
3444	// bit: 1; mask: 0x0002; 0=landscape, 1=portrait
3445	$isPortrait = (0x0002 & self::getUInt2d($recordData, 10)) >> 1;
3446
3447	// bit: 2; mask: 0x0004; 1= paper size, scaling factor, paper orient. not init
3448	// when this bit is set, do not use flags for those properties
3449	$isNotInit = (0x0004 & self::getUInt2d($recordData, 10)) >> 2;
3450
3451	if (!$isNotInit) {
3452	$this->phpSheet->getPageSetup()->setPaperSize($paperSize);
3453	switch ($isPortrait) {
3454	case 0:
3455	$this->phpSheet->getPageSetup()->setOrientation(PageSetup::ORIENTATION_LANDSCAPE);
3456
3457	break;
3458	case 1:
3459	$this->phpSheet->getPageSetup()->setOrientation(PageSetup::ORIENTATION_PORTRAIT);
3460
3461	break;
3462	}
3463
3464	$this->phpSheet->getPageSetup()->setScale($scale, false);
3465	$this->phpSheet->getPageSetup()->setFitToPage((bool) $this->isFitToPages);
3466	$this->phpSheet->getPageSetup()->setFitToWidth($fitToWidth, false);
3467	$this->phpSheet->getPageSetup()->setFitToHeight($fitToHeight, false);
3468	}
3469
3470	// offset: 16; size: 8; header margin (IEEE 754 floating-point value)
3471	$marginHeader = self::extractNumber(substr($recordData, 16, 8));
3472	$this->phpSheet->getPageMargins()->setHeader($marginHeader);
3473
3474	// offset: 24; size: 8; footer margin (IEEE 754 floating-point value)
3475	$marginFooter = self::extractNumber(substr($recordData, 24, 8));
3476	$this->phpSheet->getPageMargins()->setFooter($marginFooter);
3477	}
3478	}
3479
3480	/**
3481	* PROTECT - Sheet protection (BIFF2 through BIFF8)
3482	* if this record is omitted, then it also means no sheet protection.
3483	*/
3484	private function readProtect()
3485	{
3486	$length = self::getUInt2d($this->data, $this->pos + 2);
3487	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
3488
3489	// move stream pointer to next record
3490	$this->pos += 4 + $length;
3491
3492	if ($this->readDataOnly) {
3493	return;
3494	}
3495
3496	// offset: 0; size: 2;
3497
3498	// bit 0, mask 0x01; 1 = sheet is protected
3499	$bool = (0x01 & self::getUInt2d($recordData, 0)) >> 0;
3500	$this->phpSheet->getProtection()->setSheet((bool) $bool);
3501	}
3502
3503	/**
3504	* SCENPROTECT.
3505	*/
3506	private function readScenProtect()
3507	{
3508	$length = self::getUInt2d($this->data, $this->pos + 2);
3509	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
3510
3511	// move stream pointer to next record
3512	$this->pos += 4 + $length;
3513
3514	if ($this->readDataOnly) {
3515	return;
3516	}
3517
3518	// offset: 0; size: 2;
3519
3520	// bit: 0, mask 0x01; 1 = scenarios are protected
3521	$bool = (0x01 & self::getUInt2d($recordData, 0)) >> 0;
3522
3523	$this->phpSheet->getProtection()->setScenarios((bool) $bool);
3524	}
3525
3526	/**
3527	* OBJECTPROTECT.
3528	*/
3529	private function readObjectProtect()
3530	{
3531	$length = self::getUInt2d($this->data, $this->pos + 2);
3532	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
3533
3534	// move stream pointer to next record
3535	$this->pos += 4 + $length;
3536
3537	if ($this->readDataOnly) {
3538	return;
3539	}
3540
3541	// offset: 0; size: 2;
3542
3543	// bit: 0, mask 0x01; 1 = objects are protected
3544	$bool = (0x01 & self::getUInt2d($recordData, 0)) >> 0;
3545
3546	$this->phpSheet->getProtection()->setObjects((bool) $bool);
3547	}
3548
3549	/**
3550	* PASSWORD - Sheet protection (hashed) password (BIFF2 through BIFF8).
3551	*/
3552	private function readPassword()
3553	{
3554	$length = self::getUInt2d($this->data, $this->pos + 2);
3555	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
3556
3557	// move stream pointer to next record
3558	$this->pos += 4 + $length;
3559
3560	if (!$this->readDataOnly) {
3561	// offset: 0; size: 2; 16-bit hash value of password
3562	$password = strtoupper(dechex(self::getUInt2d($recordData, 0))); // the hashed password
3563	$this->phpSheet->getProtection()->setPassword($password, true);
3564	}
3565	}
3566
3567	/**
3568	* Read DEFCOLWIDTH record.
3569	*/
3570	private function readDefColWidth()
3571	{
3572	$length = self::getUInt2d($this->data, $this->pos + 2);
3573	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
3574
3575	// move stream pointer to next record
3576	$this->pos += 4 + $length;
3577
3578	// offset: 0; size: 2; default column width
3579	$width = self::getUInt2d($recordData, 0);
3580	if ($width != 8) {
3581	$this->phpSheet->getDefaultColumnDimension()->setWidth($width);
3582	}
3583	}
3584
3585	/**
3586	* Read COLINFO record.
3587	*/
3588	private function readColInfo()
3589	{
3590	$length = self::getUInt2d($this->data, $this->pos + 2);
3591	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
3592
3593	// move stream pointer to next record
3594	$this->pos += 4 + $length;
3595
3596	if (!$this->readDataOnly) {
3597	// offset: 0; size: 2; index to first column in range
3598	$firstColumnIndex = self::getUInt2d($recordData, 0);
3599
3600	// offset: 2; size: 2; index to last column in range
3601	$lastColumnIndex = self::getUInt2d($recordData, 2);
3602
3603	// offset: 4; size: 2; width of the column in 1/256 of the width of the zero character
3604	$width = self::getUInt2d($recordData, 4);
3605
3606	// offset: 6; size: 2; index to XF record for default column formatting
3607	$xfIndex = self::getUInt2d($recordData, 6);
3608
3609	// offset: 8; size: 2; option flags
3610	// bit: 0; mask: 0x0001; 1= columns are hidden
3611	$isHidden = (0x0001 & self::getUInt2d($recordData, 8)) >> 0;
3612
3613	// bit: 10-8; mask: 0x0700; outline level of the columns (0 = no outline)
3614	$level = (0x0700 & self::getUInt2d($recordData, 8)) >> 8;
3615
3616	// bit: 12; mask: 0x1000; 1 = collapsed
3617	$isCollapsed = (0x1000 & self::getUInt2d($recordData, 8)) >> 12;
3618
3619	// offset: 10; size: 2; not used
3620
3621	for ($i = $firstColumnIndex + 1; $i <= $lastColumnIndex + 1; ++$i) {
3622	if ($lastColumnIndex == 255 \|\| $lastColumnIndex == 256) {
3623	$this->phpSheet->getDefaultColumnDimension()->setWidth($width / 256);
3624
3625	break;
3626	}
3627	$this->phpSheet->getColumnDimensionByColumn($i)->setWidth($width / 256);
3628	$this->phpSheet->getColumnDimensionByColumn($i)->setVisible(!$isHidden);
3629	$this->phpSheet->getColumnDimensionByColumn($i)->setOutlineLevel($level);
3630	$this->phpSheet->getColumnDimensionByColumn($i)->setCollapsed($isCollapsed);
3631	$this->phpSheet->getColumnDimensionByColumn($i)->setXfIndex($this->mapCellXfIndex[$xfIndex]);
3632	}
3633	}
3634	}
3635
3636	/**
3637	* ROW.
3638	*
3639	* This record contains the properties of a single row in a
3640	* sheet. Rows and cells in a sheet are divided into blocks
3641	* of 32 rows.
3642	*
3643	* -- "OpenOffice.org's Documentation of the Microsoft
3644	* Excel File Format"
3645	*/
3646	private function readRow()
3647	{
3648	$length = self::getUInt2d($this->data, $this->pos + 2);
3649	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
3650
3651	// move stream pointer to next record
3652	$this->pos += 4 + $length;
3653
3654	if (!$this->readDataOnly) {
3655	// offset: 0; size: 2; index of this row
3656	$r = self::getUInt2d($recordData, 0);
3657
3658	// offset: 2; size: 2; index to column of the first cell which is described by a cell record
3659
3660	// offset: 4; size: 2; index to column of the last cell which is described by a cell record, increased by 1
3661
3662	// offset: 6; size: 2;
3663
3664	// bit: 14-0; mask: 0x7FFF; height of the row, in twips = 1/20 of a point
3665	$height = (0x7FFF & self::getUInt2d($recordData, 6)) >> 0;
3666
3667	// bit: 15: mask: 0x8000; 0 = row has custom height; 1= row has default height
3668	$useDefaultHeight = (0x8000 & self::getUInt2d($recordData, 6)) >> 15;
3669
3670	if (!$useDefaultHeight) {
3671	$this->phpSheet->getRowDimension($r + 1)->setRowHeight($height / 20);
3672	}
3673
3674	// offset: 8; size: 2; not used
3675
3676	// offset: 10; size: 2; not used in BIFF5-BIFF8
3677
3678	// offset: 12; size: 4; option flags and default row formatting
3679
3680	// bit: 2-0: mask: 0x00000007; outline level of the row
3681	$level = (0x00000007 & self::getInt4d($recordData, 12)) >> 0;
3682	$this->phpSheet->getRowDimension($r + 1)->setOutlineLevel($level);
3683
3684	// bit: 4; mask: 0x00000010; 1 = outline group start or ends here... and is collapsed
3685	$isCollapsed = (0x00000010 & self::getInt4d($recordData, 12)) >> 4;
3686	$this->phpSheet->getRowDimension($r + 1)->setCollapsed($isCollapsed);
3687
3688	// bit: 5; mask: 0x00000020; 1 = row is hidden
3689	$isHidden = (0x00000020 & self::getInt4d($recordData, 12)) >> 5;
3690	$this->phpSheet->getRowDimension($r + 1)->setVisible(!$isHidden);
3691
3692	// bit: 7; mask: 0x00000080; 1 = row has explicit format
3693	$hasExplicitFormat = (0x00000080 & self::getInt4d($recordData, 12)) >> 7;
3694
3695	// bit: 27-16; mask: 0x0FFF0000; only applies when hasExplicitFormat = 1; index to XF record
3696	$xfIndex = (0x0FFF0000 & self::getInt4d($recordData, 12)) >> 16;
3697
3698	if ($hasExplicitFormat && isset($this->mapCellXfIndex[$xfIndex])) {
3699	$this->phpSheet->getRowDimension($r + 1)->setXfIndex($this->mapCellXfIndex[$xfIndex]);
3700	}
3701	}
3702	}
3703
3704	/**
3705	* Read RK record
3706	* This record represents a cell that contains an RK value
3707	* (encoded integer or floating-point value). If a
3708	* floating-point value cannot be encoded to an RK value,
3709	* a NUMBER record will be written. This record replaces the
3710	* record INTEGER written in BIFF2.
3711	*
3712	* -- "OpenOffice.org's Documentation of the Microsoft
3713	* Excel File Format"
3714	*/
3715	private function readRk()
3716	{
3717	$length = self::getUInt2d($this->data, $this->pos + 2);
3718	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
3719
3720	// move stream pointer to next record
3721	$this->pos += 4 + $length;
3722
3723	// offset: 0; size: 2; index to row
3724	$row = self::getUInt2d($recordData, 0);
3725
3726	// offset: 2; size: 2; index to column
3727	$column = self::getUInt2d($recordData, 2);
3728	$columnString = Coordinate::stringFromColumnIndex($column + 1);
3729
3730	// Read cell?
3731	if (($this->getReadFilter() !== null) && $this->getReadFilter()->readCell($columnString, $row + 1, $this->phpSheet->getTitle())) {
3732	// offset: 4; size: 2; index to XF record
3733	$xfIndex = self::getUInt2d($recordData, 4);
3734
3735	// offset: 6; size: 4; RK value
3736	$rknum = self::getInt4d($recordData, 6);
3737	$numValue = self::getIEEE754($rknum);
3738
3739	$cell = $this->phpSheet->getCell($columnString . ($row + 1));
3740	if (!$this->readDataOnly) {
3741	// add style information
3742	$cell->setXfIndex($this->mapCellXfIndex[$xfIndex]);
3743	}
3744
3745	// add cell
3746	$cell->setValueExplicit($numValue, DataType::TYPE_NUMERIC);
3747	}
3748	}
3749
3750	/**
3751	* Read LABELSST record
3752	* This record represents a cell that contains a string. It
3753	* replaces the LABEL record and RSTRING record used in
3754	* BIFF2-BIFF5.
3755	*
3756	* -- "OpenOffice.org's Documentation of the Microsoft
3757	* Excel File Format"
3758	*/
3759	private function readLabelSst()
3760	{
3761	$length = self::getUInt2d($this->data, $this->pos + 2);
3762	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
3763
3764	// move stream pointer to next record
3765	$this->pos += 4 + $length;
3766
3767	// offset: 0; size: 2; index to row
3768	$row = self::getUInt2d($recordData, 0);
3769
3770	// offset: 2; size: 2; index to column
3771	$column = self::getUInt2d($recordData, 2);
3772	$columnString = Coordinate::stringFromColumnIndex($column + 1);
3773
3774	$emptyCell = true;
3775	// Read cell?
3776	if (($this->getReadFilter() !== null) && $this->getReadFilter()->readCell($columnString, $row + 1, $this->phpSheet->getTitle())) {
3777	// offset: 4; size: 2; index to XF record
3778	$xfIndex = self::getUInt2d($recordData, 4);
3779
3780	// offset: 6; size: 4; index to SST record
3781	$index = self::getInt4d($recordData, 6);
3782
3783	// add cell
3784	if (($fmtRuns = $this->sst[$index]['fmtRuns']) && !$this->readDataOnly) {
3785	// then we should treat as rich text
3786	$richText = new RichText();
3787	$charPos = 0;
3788	$sstCount = count($this->sst[$index]['fmtRuns']);
3789	for ($i = 0; $i <= $sstCount; ++$i) {
3790	if (isset($fmtRuns[$i])) {
3791	$text = StringHelper::substring($this->sst[$index]['value'], $charPos, $fmtRuns[$i]['charPos'] - $charPos);
3792	$charPos = $fmtRuns[$i]['charPos'];
3793	} else {
3794	$text = StringHelper::substring($this->sst[$index]['value'], $charPos, StringHelper::countCharacters($this->sst[$index]['value']));
3795	}
3796
3797	if (StringHelper::countCharacters($text) > 0) {
3798	if ($i == 0) { // first text run, no style
3799	$richText->createText($text);
3800	} else {
3801	$textRun = $richText->createTextRun($text);
3802	if (isset($fmtRuns[$i - 1])) {
3803	if ($fmtRuns[$i - 1]['fontIndex'] < 4) {
3804	$fontIndex = $fmtRuns[$i - 1]['fontIndex'];
3805	} else {
3806	// this has to do with that index 4 is omitted in all BIFF versions for some strange reason
3807	// check the OpenOffice documentation of the FONT record
3808	$fontIndex = $fmtRuns[$i - 1]['fontIndex'] - 1;
3809	}
3810	$textRun->setFont(clone $this->objFonts[$fontIndex]);
3811	}
3812	}
3813	}
3814	}
3815	if ($this->readEmptyCells \|\| trim($richText->getPlainText()) !== '') {
3816	$cell = $this->phpSheet->getCell($columnString . ($row + 1));
3817	$cell->setValueExplicit($richText, DataType::TYPE_STRING);
3818	$emptyCell = false;
3819	}
3820	} else {
3821	if ($this->readEmptyCells \|\| trim($this->sst[$index]['value']) !== '') {
3822	$cell = $this->phpSheet->getCell($columnString . ($row + 1));
3823	$cell->setValueExplicit($this->sst[$index]['value'], DataType::TYPE_STRING);
3824	$emptyCell = false;
3825	}
3826	}
3827
3828	if (!$this->readDataOnly && !$emptyCell) {
3829	// add style information
3830	$cell->setXfIndex($this->mapCellXfIndex[$xfIndex]);
3831	}
3832	}
3833	}
3834
3835	/**
3836	* Read MULRK record
3837	* This record represents a cell range containing RK value
3838	* cells. All cells are located in the same row.
3839	*
3840	* -- "OpenOffice.org's Documentation of the Microsoft
3841	* Excel File Format"
3842	*/
3843	private function readMulRk()
3844	{
3845	$length = self::getUInt2d($this->data, $this->pos + 2);
3846	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
3847
3848	// move stream pointer to next record
3849	$this->pos += 4 + $length;
3850
3851	// offset: 0; size: 2; index to row
3852	$row = self::getUInt2d($recordData, 0);
3853
3854	// offset: 2; size: 2; index to first column
3855	$colFirst = self::getUInt2d($recordData, 2);
3856
3857	// offset: var; size: 2; index to last column
3858	$colLast = self::getUInt2d($recordData, $length - 2);
3859	$columns = $colLast - $colFirst + 1;
3860
3861	// offset within record data
3862	$offset = 4;
3863
3864	for ($i = 1; $i <= $columns; ++$i) {
3865	$columnString = Coordinate::stringFromColumnIndex($colFirst + $i);
3866
3867	// Read cell?
3868	if (($this->getReadFilter() !== null) && $this->getReadFilter()->readCell($columnString, $row + 1, $this->phpSheet->getTitle())) {
3869	// offset: var; size: 2; index to XF record
3870	$xfIndex = self::getUInt2d($recordData, $offset);
3871
3872	// offset: var; size: 4; RK value
3873	$numValue = self::getIEEE754(self::getInt4d($recordData, $offset + 2));
3874	$cell = $this->phpSheet->getCell($columnString . ($row + 1));
3875	if (!$this->readDataOnly) {
3876	// add style
3877	$cell->setXfIndex($this->mapCellXfIndex[$xfIndex]);
3878	}
3879
3880	// add cell value
3881	$cell->setValueExplicit($numValue, DataType::TYPE_NUMERIC);
3882	}
3883
3884	$offset += 6;
3885	}
3886	}
3887
3888	/**
3889	* Read NUMBER record
3890	* This record represents a cell that contains a
3891	* floating-point value.
3892	*
3893	* -- "OpenOffice.org's Documentation of the Microsoft
3894	* Excel File Format"
3895	*/
3896	private function readNumber()
3897	{
3898	$length = self::getUInt2d($this->data, $this->pos + 2);
3899	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
3900
3901	// move stream pointer to next record
3902	$this->pos += 4 + $length;
3903
3904	// offset: 0; size: 2; index to row
3905	$row = self::getUInt2d($recordData, 0);
3906
3907	// offset: 2; size 2; index to column
3908	$column = self::getUInt2d($recordData, 2);
3909	$columnString = Coordinate::stringFromColumnIndex($column + 1);
3910
3911	// Read cell?
3912	if (($this->getReadFilter() !== null) && $this->getReadFilter()->readCell($columnString, $row + 1, $this->phpSheet->getTitle())) {
3913	// offset 4; size: 2; index to XF record
3914	$xfIndex = self::getUInt2d($recordData, 4);
3915
3916	$numValue = self::extractNumber(substr($recordData, 6, 8));
3917
3918	$cell = $this->phpSheet->getCell($columnString . ($row + 1));
3919	if (!$this->readDataOnly) {
3920	// add cell style
3921	$cell->setXfIndex($this->mapCellXfIndex[$xfIndex]);
3922	}
3923
3924	// add cell value
3925	$cell->setValueExplicit($numValue, DataType::TYPE_NUMERIC);
3926	}
3927	}
3928
3929	/**
3930	* Read FORMULA record + perhaps a following STRING record if formula result is a string
3931	* This record contains the token array and the result of a
3932	* formula cell.
3933	*
3934	* -- "OpenOffice.org's Documentation of the Microsoft
3935	* Excel File Format"
3936	*/
3937	private function readFormula()
3938	{
3939	$length = self::getUInt2d($this->data, $this->pos + 2);
3940	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
3941
3942	// move stream pointer to next record
3943	$this->pos += 4 + $length;
3944
3945	// offset: 0; size: 2; row index
3946	$row = self::getUInt2d($recordData, 0);
3947
3948	// offset: 2; size: 2; col index
3949	$column = self::getUInt2d($recordData, 2);
3950	$columnString = Coordinate::stringFromColumnIndex($column + 1);
3951
3952	// offset: 20: size: variable; formula structure
3953	$formulaStructure = substr($recordData, 20);
3954
3955	// offset: 14: size: 2; option flags, recalculate always, recalculate on open etc.
3956	$options = self::getUInt2d($recordData, 14);
3957
3958	// bit: 0; mask: 0x0001; 1 = recalculate always
3959	// bit: 1; mask: 0x0002; 1 = calculate on open
3960	// bit: 2; mask: 0x0008; 1 = part of a shared formula
3961	$isPartOfSharedFormula = (bool) (0x0008 & $options);
3962
3963	// WARNING:
3964	// We can apparently not rely on $isPartOfSharedFormula. Even when $isPartOfSharedFormula = true
3965	// the formula data may be ordinary formula data, therefore we need to check
3966	// explicitly for the tExp token (0x01)
3967	$isPartOfSharedFormula = $isPartOfSharedFormula && ord($formulaStructure[2]) == 0x01;
3968
3969	if ($isPartOfSharedFormula) {
3970	// part of shared formula which means there will be a formula with a tExp token and nothing else
3971	// get the base cell, grab tExp token
3972	$baseRow = self::getUInt2d($formulaStructure, 3);
3973	$baseCol = self::getUInt2d($formulaStructure, 5);
3974	$this->baseCell = Coordinate::stringFromColumnIndex($baseCol + 1) . ($baseRow + 1);
3975	}
3976
3977	// Read cell?
3978	if (($this->getReadFilter() !== null) && $this->getReadFilter()->readCell($columnString, $row + 1, $this->phpSheet->getTitle())) {
3979	if ($isPartOfSharedFormula) {
3980	// formula is added to this cell after the sheet has been read
3981	$this->sharedFormulaParts[$columnString . ($row + 1)] = $this->baseCell;
3982	}
3983
3984	// offset: 16: size: 4; not used
3985
3986	// offset: 4; size: 2; XF index
3987	$xfIndex = self::getUInt2d($recordData, 4);
3988
3989	// offset: 6; size: 8; result of the formula
3990	if ((ord($recordData[6]) == 0) && (ord($recordData[12]) == 255) && (ord($recordData[13]) == 255)) {
3991	// String formula. Result follows in appended STRING record
3992	$dataType = DataType::TYPE_STRING;
3993
3994	// read possible SHAREDFMLA record
3995	$code = self::getUInt2d($this->data, $this->pos);
3996	if ($code == self::XLS_TYPE_SHAREDFMLA) {
3997	$this->readSharedFmla();
3998	}
3999
4000	// read STRING record
4001	$value = $this->readString();
4002	} elseif ((ord($recordData[6]) == 1)
4003	&& (ord($recordData[12]) == 255)
4004	&& (ord($recordData[13]) == 255)) {
4005	// Boolean formula. Result is in +2; 0=false, 1=true
4006	$dataType = DataType::TYPE_BOOL;
4007	$value = (bool) ord($recordData[8]);
4008	} elseif ((ord($recordData[6]) == 2)
4009	&& (ord($recordData[12]) == 255)
4010	&& (ord($recordData[13]) == 255)) {
4011	// Error formula. Error code is in +2
4012	$dataType = DataType::TYPE_ERROR;
4013	$value = Xls\ErrorCode::lookup(ord($recordData[8]));
4014	} elseif ((ord($recordData[6]) == 3)
4015	&& (ord($recordData[12]) == 255)
4016	&& (ord($recordData[13]) == 255)) {
4017	// Formula result is a null string
4018	$dataType = DataType::TYPE_NULL;
4019	$value = '';
4020	} else {
4021	// forumla result is a number, first 14 bytes like _NUMBER record
4022	$dataType = DataType::TYPE_NUMERIC;
4023	$value = self::extractNumber(substr($recordData, 6, 8));
4024	}
4025
4026	$cell = $this->phpSheet->getCell($columnString . ($row + 1));
4027	if (!$this->readDataOnly) {
4028	// add cell style
4029	$cell->setXfIndex($this->mapCellXfIndex[$xfIndex]);
4030	}
4031
4032	// store the formula
4033	if (!$isPartOfSharedFormula) {
4034	// not part of shared formula
4035	// add cell value. If we can read formula, populate with formula, otherwise just used cached value
4036	try {
4037	if ($this->version != self::XLS_BIFF8) {
4038	throw new Exception('Not BIFF8. Can only read BIFF8 formulas');
4039	}
4040	$formula = $this->getFormulaFromStructure($formulaStructure); // get formula in human language
4041	$cell->setValueExplicit('=' . $formula, DataType::TYPE_FORMULA);
4042	} catch (PhpSpreadsheetException $e) {
4043	$cell->setValueExplicit($value, $dataType);
4044	}
4045	} else {
4046	if ($this->version == self::XLS_BIFF8) {
4047	// do nothing at this point, formula id added later in the code
4048	} else {
4049	$cell->setValueExplicit($value, $dataType);
4050	}
4051	}
4052
4053	// store the cached calculated value
4054	$cell->setCalculatedValue($value);
4055	}
4056	}
4057
4058	/**
4059	* Read a SHAREDFMLA record. This function just stores the binary shared formula in the reader,
4060	* which usually contains relative references.
4061	* These will be used to construct the formula in each shared formula part after the sheet is read.
4062	*/
4063	private function readSharedFmla()
4064	{
4065	$length = self::getUInt2d($this->data, $this->pos + 2);
4066	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
4067
4068	// move stream pointer to next record
4069	$this->pos += 4 + $length;
4070
4071	// offset: 0, size: 6; cell range address of the area used by the shared formula, not used for anything
4072	$cellRange = substr($recordData, 0, 6);
4073	$cellRange = $this->readBIFF5CellRangeAddressFixed($cellRange); // note: even BIFF8 uses BIFF5 syntax
4074
4075	// offset: 6, size: 1; not used
4076
4077	// offset: 7, size: 1; number of existing FORMULA records for this shared formula
4078	$no = ord($recordData[7]);
4079
4080	// offset: 8, size: var; Binary token array of the shared formula
4081	$formula = substr($recordData, 8);
4082
4083	// at this point we only store the shared formula for later use
4084	$this->sharedFormulas[$this->baseCell] = $formula;
4085	}
4086
4087	/**
4088	* Read a STRING record from current stream position and advance the stream pointer to next record
4089	* This record is used for storing result from FORMULA record when it is a string, and
4090	* it occurs directly after the FORMULA record.
4091	*
4092	* @return string The string contents as UTF-8
4093	*/
4094	private function readString()
4095	{
4096	$length = self::getUInt2d($this->data, $this->pos + 2);
4097	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
4098
4099	// move stream pointer to next record
4100	$this->pos += 4 + $length;
4101
4102	if ($this->version == self::XLS_BIFF8) {
4103	$string = self::readUnicodeStringLong($recordData);
4104	$value = $string['value'];
4105	} else {
4106	$string = $this->readByteStringLong($recordData);
4107	$value = $string['value'];
4108	}
4109
4110	return $value;
4111	}
4112
4113	/**
4114	* Read BOOLERR record
4115	* This record represents a Boolean value or error value
4116	* cell.
4117	*
4118	* -- "OpenOffice.org's Documentation of the Microsoft
4119	* Excel File Format"
4120	*/
4121	private function readBoolErr()
4122	{
4123	$length = self::getUInt2d($this->data, $this->pos + 2);
4124	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
4125
4126	// move stream pointer to next record
4127	$this->pos += 4 + $length;
4128
4129	// offset: 0; size: 2; row index
4130	$row = self::getUInt2d($recordData, 0);
4131
4132	// offset: 2; size: 2; column index
4133	$column = self::getUInt2d($recordData, 2);
4134	$columnString = Coordinate::stringFromColumnIndex($column + 1);
4135
4136	// Read cell?
4137	if (($this->getReadFilter() !== null) && $this->getReadFilter()->readCell($columnString, $row + 1, $this->phpSheet->getTitle())) {
4138	// offset: 4; size: 2; index to XF record
4139	$xfIndex = self::getUInt2d($recordData, 4);
4140
4141	// offset: 6; size: 1; the boolean value or error value
4142	$boolErr = ord($recordData[6]);
4143
4144	// offset: 7; size: 1; 0=boolean; 1=error
4145	$isError = ord($recordData[7]);
4146
4147	$cell = $this->phpSheet->getCell($columnString . ($row + 1));
4148	switch ($isError) {
4149	case 0: // boolean
4150	$value = (bool) $boolErr;
4151
4152	// add cell value
4153	$cell->setValueExplicit($value, DataType::TYPE_BOOL);
4154
4155	break;
4156	case 1: // error type
4157	$value = Xls\ErrorCode::lookup($boolErr);
4158
4159	// add cell value
4160	$cell->setValueExplicit($value, DataType::TYPE_ERROR);
4161
4162	break;
4163	}
4164
4165	if (!$this->readDataOnly) {
4166	// add cell style
4167	$cell->setXfIndex($this->mapCellXfIndex[$xfIndex]);
4168	}
4169	}
4170	}
4171
4172	/**
4173	* Read MULBLANK record
4174	* This record represents a cell range of empty cells. All
4175	* cells are located in the same row.
4176	*
4177	* -- "OpenOffice.org's Documentation of the Microsoft
4178	* Excel File Format"
4179	*/
4180	private function readMulBlank()
4181	{
4182	$length = self::getUInt2d($this->data, $this->pos + 2);
4183	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
4184
4185	// move stream pointer to next record
4186	$this->pos += 4 + $length;
4187
4188	// offset: 0; size: 2; index to row
4189	$row = self::getUInt2d($recordData, 0);
4190
4191	// offset: 2; size: 2; index to first column
4192	$fc = self::getUInt2d($recordData, 2);
4193
4194	// offset: 4; size: 2 x nc; list of indexes to XF records
4195	// add style information
4196	if (!$this->readDataOnly && $this->readEmptyCells) {
4197	for ($i = 0; $i < $length / 2 - 3; ++$i) {
4198	$columnString = Coordinate::stringFromColumnIndex($fc + $i + 1);
4199
4200	// Read cell?
4201	if (($this->getReadFilter() !== null) && $this->getReadFilter()->readCell($columnString, $row + 1, $this->phpSheet->getTitle())) {
4202	$xfIndex = self::getUInt2d($recordData, 4 + 2 * $i);
4203	$this->phpSheet->getCell($columnString . ($row + 1))->setXfIndex($this->mapCellXfIndex[$xfIndex]);
4204	}
4205	}
4206	}
4207
4208	// offset: 6; size 2; index to last column (not needed)
4209	}
4210
4211	/**
4212	* Read LABEL record
4213	* This record represents a cell that contains a string. In
4214	* BIFF8 it is usually replaced by the LABELSST record.
4215	* Excel still uses this record, if it copies unformatted
4216	* text cells to the clipboard.
4217	*
4218	* -- "OpenOffice.org's Documentation of the Microsoft
4219	* Excel File Format"
4220	*/
4221	private function readLabel()
4222	{
4223	$length = self::getUInt2d($this->data, $this->pos + 2);
4224	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
4225
4226	// move stream pointer to next record
4227	$this->pos += 4 + $length;
4228
4229	// offset: 0; size: 2; index to row
4230	$row = self::getUInt2d($recordData, 0);
4231
4232	// offset: 2; size: 2; index to column
4233	$column = self::getUInt2d($recordData, 2);
4234	$columnString = Coordinate::stringFromColumnIndex($column + 1);
4235
4236	// Read cell?
4237	if (($this->getReadFilter() !== null) && $this->getReadFilter()->readCell($columnString, $row + 1, $this->phpSheet->getTitle())) {
4238	// offset: 4; size: 2; XF index
4239	$xfIndex = self::getUInt2d($recordData, 4);
4240
4241	// add cell value
4242	// todo: what if string is very long? continue record
4243	if ($this->version == self::XLS_BIFF8) {
4244	$string = self::readUnicodeStringLong(substr($recordData, 6));
4245	$value = $string['value'];
4246	} else {
4247	$string = $this->readByteStringLong(substr($recordData, 6));
4248	$value = $string['value'];
4249	}
4250	if ($this->readEmptyCells \|\| trim($value) !== '') {
4251	$cell = $this->phpSheet->getCell($columnString . ($row + 1));
4252	$cell->setValueExplicit($value, DataType::TYPE_STRING);
4253
4254	if (!$this->readDataOnly) {
4255	// add cell style
4256	$cell->setXfIndex($this->mapCellXfIndex[$xfIndex]);
4257	}
4258	}
4259	}
4260	}
4261
4262	/**
4263	* Read BLANK record.
4264	*/
4265	private function readBlank()
4266	{
4267	$length = self::getUInt2d($this->data, $this->pos + 2);
4268	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
4269
4270	// move stream pointer to next record
4271	$this->pos += 4 + $length;
4272
4273	// offset: 0; size: 2; row index
4274	$row = self::getUInt2d($recordData, 0);
4275
4276	// offset: 2; size: 2; col index
4277	$col = self::getUInt2d($recordData, 2);
4278	$columnString = Coordinate::stringFromColumnIndex($col + 1);
4279
4280	// Read cell?
4281	if (($this->getReadFilter() !== null) && $this->getReadFilter()->readCell($columnString, $row + 1, $this->phpSheet->getTitle())) {
4282	// offset: 4; size: 2; XF index
4283	$xfIndex = self::getUInt2d($recordData, 4);
4284
4285	// add style information
4286	if (!$this->readDataOnly && $this->readEmptyCells) {
4287	$this->phpSheet->getCell($columnString . ($row + 1))->setXfIndex($this->mapCellXfIndex[$xfIndex]);
4288	}
4289	}
4290	}
4291
4292	/**
4293	* Read MSODRAWING record.
4294	*/
4295	private function readMsoDrawing()
4296	{
4297	$length = self::getUInt2d($this->data, $this->pos + 2);
4298
4299	// get spliced record data
4300	$splicedRecordData = $this->getSplicedRecordData();
4301	$recordData = $splicedRecordData['recordData'];
4302
4303	$this->drawingData .= $recordData;
4304	}
4305
4306	/**
4307	* Read OBJ record.
4308	*/
4309	private function readObj()
4310	{
4311	$length = self::getUInt2d($this->data, $this->pos + 2);
4312	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
4313
4314	// move stream pointer to next record
4315	$this->pos += 4 + $length;
4316
4317	if ($this->readDataOnly \|\| $this->version != self::XLS_BIFF8) {
4318	return;
4319	}
4320
4321	// recordData consists of an array of subrecords looking like this:
4322	// ft: 2 bytes; ftCmo type (0x15)
4323	// cb: 2 bytes; size in bytes of ftCmo data
4324	// ot: 2 bytes; Object Type
4325	// id: 2 bytes; Object id number
4326	// grbit: 2 bytes; Option Flags
4327	// data: var; subrecord data
4328
4329	// for now, we are just interested in the second subrecord containing the object type
4330	$ftCmoType = self::getUInt2d($recordData, 0);
4331	$cbCmoSize = self::getUInt2d($recordData, 2);
4332	$otObjType = self::getUInt2d($recordData, 4);
4333	$idObjID = self::getUInt2d($recordData, 6);
4334	$grbitOpts = self::getUInt2d($recordData, 6);
4335
4336	$this->objs[] = [
4337	'ftCmoType' => $ftCmoType,
4338	'cbCmoSize' => $cbCmoSize,
4339	'otObjType' => $otObjType,
4340	'idObjID' => $idObjID,
4341	'grbitOpts' => $grbitOpts,
4342	];
4343	$this->textObjRef = $idObjID;
4344	}
4345
4346	/**
4347	* Read WINDOW2 record.
4348	*/
4349	private function readWindow2()
4350	{
4351	$length = self::getUInt2d($this->data, $this->pos + 2);
4352	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
4353
4354	// move stream pointer to next record
4355	$this->pos += 4 + $length;
4356
4357	// offset: 0; size: 2; option flags
4358	$options = self::getUInt2d($recordData, 0);
4359
4360	// offset: 2; size: 2; index to first visible row
4361	$firstVisibleRow = self::getUInt2d($recordData, 2);
4362
4363	// offset: 4; size: 2; index to first visible colum
4364	$firstVisibleColumn = self::getUInt2d($recordData, 4);
4365	if ($this->version === self::XLS_BIFF8) {
4366	// offset: 8; size: 2; not used
4367	// offset: 10; size: 2; cached magnification factor in page break preview (in percent); 0 = Default (60%)
4368	// offset: 12; size: 2; cached magnification factor in normal view (in percent); 0 = Default (100%)
4369	// offset: 14; size: 4; not used
4370	$zoomscaleInPageBreakPreview = self::getUInt2d($recordData, 10);
4371	if ($zoomscaleInPageBreakPreview === 0) {
4372	$zoomscaleInPageBreakPreview = 60;
4373	}
4374	$zoomscaleInNormalView = self::getUInt2d($recordData, 12);
4375	if ($zoomscaleInNormalView === 0) {
4376	$zoomscaleInNormalView = 100;
4377	}
4378	}
4379
4380	// bit: 1; mask: 0x0002; 0 = do not show gridlines, 1 = show gridlines
4381	$showGridlines = (bool) ((0x0002 & $options) >> 1);
4382	$this->phpSheet->setShowGridlines($showGridlines);
4383
4384	// bit: 2; mask: 0x0004; 0 = do not show headers, 1 = show headers
4385	$showRowColHeaders = (bool) ((0x0004 & $options) >> 2);
4386	$this->phpSheet->setShowRowColHeaders($showRowColHeaders);
4387
4388	// bit: 3; mask: 0x0008; 0 = panes are not frozen, 1 = panes are frozen
4389	$this->frozen = (bool) ((0x0008 & $options) >> 3);
4390
4391	// bit: 6; mask: 0x0040; 0 = columns from left to right, 1 = columns from right to left
4392	$this->phpSheet->setRightToLeft((bool) ((0x0040 & $options) >> 6));
4393
4394	// bit: 10; mask: 0x0400; 0 = sheet not active, 1 = sheet active
4395	$isActive = (bool) ((0x0400 & $options) >> 10);
4396	if ($isActive) {
4397	$this->spreadsheet->setActiveSheetIndex($this->spreadsheet->getIndex($this->phpSheet));
4398	}
4399
4400	// bit: 11; mask: 0x0800; 0 = normal view, 1 = page break view
4401	$isPageBreakPreview = (bool) ((0x0800 & $options) >> 11);
4402
4403	//FIXME: set $firstVisibleRow and $firstVisibleColumn
4404
4405	if ($this->phpSheet->getSheetView()->getView() !== SheetView::SHEETVIEW_PAGE_LAYOUT) {
4406	//NOTE: this setting is inferior to page layout view(Excel2007-)
4407	$view = $isPageBreakPreview ? SheetView::SHEETVIEW_PAGE_BREAK_PREVIEW : SheetView::SHEETVIEW_NORMAL;
4408	$this->phpSheet->getSheetView()->setView($view);
4409	if ($this->version === self::XLS_BIFF8) {
4410	$zoomScale = $isPageBreakPreview ? $zoomscaleInPageBreakPreview : $zoomscaleInNormalView;
4411	$this->phpSheet->getSheetView()->setZoomScale($zoomScale);
4412	$this->phpSheet->getSheetView()->setZoomScaleNormal($zoomscaleInNormalView);
4413	}
4414	}
4415	}
4416
4417	/**
4418	* Read PLV Record(Created by Excel2007 or upper).
4419	*/
4420	private function readPageLayoutView()
4421	{
4422	$length = self::getUInt2d($this->data, $this->pos + 2);
4423	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
4424
4425	// move stream pointer to next record
4426	$this->pos += 4 + $length;
4427
4428	// offset: 0; size: 2; rt
4429	//->ignore
4430	$rt = self::getUInt2d($recordData, 0);
4431	// offset: 2; size: 2; grbitfr
4432	//->ignore
4433	$grbitFrt = self::getUInt2d($recordData, 2);
4434	// offset: 4; size: 8; reserved
4435	//->ignore
4436
4437	// offset: 12; size 2; zoom scale
4438	$wScalePLV = self::getUInt2d($recordData, 12);
4439	// offset: 14; size 2; grbit
4440	$grbit = self::getUInt2d($recordData, 14);
4441
4442	// decomprise grbit
4443	$fPageLayoutView = $grbit & 0x01;
4444	$fRulerVisible = ($grbit >> 1) & 0x01; //no support
4445	$fWhitespaceHidden = ($grbit >> 3) & 0x01; //no support
4446
4447	if ($fPageLayoutView === 1) {
4448	$this->phpSheet->getSheetView()->setView(SheetView::SHEETVIEW_PAGE_LAYOUT);
4449	$this->phpSheet->getSheetView()->setZoomScale($wScalePLV); //set by Excel2007 only if SHEETVIEW_PAGE_LAYOUT
4450	}
4451	//otherwise, we cannot know whether SHEETVIEW_PAGE_LAYOUT or SHEETVIEW_PAGE_BREAK_PREVIEW.
4452	}
4453
4454	/**
4455	* Read SCL record.
4456	*/
4457	private function readScl()
4458	{
4459	$length = self::getUInt2d($this->data, $this->pos + 2);
4460	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
4461
4462	// move stream pointer to next record
4463	$this->pos += 4 + $length;
4464
4465	// offset: 0; size: 2; numerator of the view magnification
4466	$numerator = self::getUInt2d($recordData, 0);
4467
4468	// offset: 2; size: 2; numerator of the view magnification
4469	$denumerator = self::getUInt2d($recordData, 2);
4470
4471	// set the zoom scale (in percent)
4472	$this->phpSheet->getSheetView()->setZoomScale($numerator * 100 / $denumerator);
4473	}
4474
4475	/**
4476	* Read PANE record.
4477	*/
4478	private function readPane()
4479	{
4480	$length = self::getUInt2d($this->data, $this->pos + 2);
4481	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
4482
4483	// move stream pointer to next record
4484	$this->pos += 4 + $length;
4485
4486	if (!$this->readDataOnly) {
4487	// offset: 0; size: 2; position of vertical split
4488	$px = self::getUInt2d($recordData, 0);
4489
4490	// offset: 2; size: 2; position of horizontal split
4491	$py = self::getUInt2d($recordData, 2);
4492
4493	// offset: 4; size: 2; top most visible row in the bottom pane
4494	$rwTop = self::getUInt2d($recordData, 4);
4495
4496	// offset: 6; size: 2; first visible left column in the right pane
4497	$colLeft = self::getUInt2d($recordData, 6);
4498
4499	if ($this->frozen) {
4500	// frozen panes
4501	$cell = Coordinate::stringFromColumnIndex($px + 1) . ($py + 1);
4502	$topLeftCell = Coordinate::stringFromColumnIndex($colLeft + 1) . ($rwTop + 1);
4503	$this->phpSheet->freezePane($cell, $topLeftCell);
4504	}
4505	// unfrozen panes; split windows; not supported by PhpSpreadsheet core
4506	}
4507	}
4508
4509	/**
4510	* Read SELECTION record. There is one such record for each pane in the sheet.
4511	*/
4512	private function readSelection()
4513	{
4514	$length = self::getUInt2d($this->data, $this->pos + 2);
4515	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
4516
4517	// move stream pointer to next record
4518	$this->pos += 4 + $length;
4519
4520	if (!$this->readDataOnly) {
4521	// offset: 0; size: 1; pane identifier
4522	$paneId = ord($recordData[0]);
4523
4524	// offset: 1; size: 2; index to row of the active cell
4525	$r = self::getUInt2d($recordData, 1);
4526
4527	// offset: 3; size: 2; index to column of the active cell
4528	$c = self::getUInt2d($recordData, 3);
4529
4530	// offset: 5; size: 2; index into the following cell range list to the
4531	// entry that contains the active cell
4532	$index = self::getUInt2d($recordData, 5);
4533
4534	// offset: 7; size: var; cell range address list containing all selected cell ranges
4535	$data = substr($recordData, 7);
4536	$cellRangeAddressList = $this->readBIFF5CellRangeAddressList($data); // note: also BIFF8 uses BIFF5 syntax
4537
4538	$selectedCells = $cellRangeAddressList['cellRangeAddresses'][0];
4539
4540	// first row '1' + last row '16384' indicates that full column is selected (apparently also in BIFF8!)
4541	if (preg_match('/^([A-Z]+1\:[A-Z]+)16384$/', $selectedCells)) {
4542	$selectedCells = preg_replace('/^([A-Z]+1\:[A-Z]+)16384$/', '${1}1048576', $selectedCells);
4543	}
4544
4545	// first row '1' + last row '65536' indicates that full column is selected
4546	if (preg_match('/^([A-Z]+1\:[A-Z]+)65536$/', $selectedCells)) {
4547	$selectedCells = preg_replace('/^([A-Z]+1\:[A-Z]+)65536$/', '${1}1048576', $selectedCells);
4548	}
4549
4550	// first column 'A' + last column 'IV' indicates that full row is selected
4551	if (preg_match('/^(A\d+\:)IV(\d+)$/', $selectedCells)) {
4552	$selectedCells = preg_replace('/^(A\d+\:)IV(\d+)$/', '${1}XFD${2}', $selectedCells);
4553	}
4554
4555	$this->phpSheet->setSelectedCells($selectedCells);
4556	}
4557	}
4558
4559	private function includeCellRangeFiltered($cellRangeAddress)
4560	{
4561	$includeCellRange = true;
4562	if ($this->getReadFilter() !== null) {
4563	$includeCellRange = false;
4564	$rangeBoundaries = Coordinate::getRangeBoundaries($cellRangeAddress);
4565	++$rangeBoundaries[1][0];
4566	for ($row = $rangeBoundaries[0][1]; $row <= $rangeBoundaries[1][1]; ++$row) {
4567	for ($column = $rangeBoundaries[0][0]; $column != $rangeBoundaries[1][0]; ++$column) {
4568	if ($this->getReadFilter()->readCell($column, $row, $this->phpSheet->getTitle())) {
4569	$includeCellRange = true;
4570
4571	break 2;
4572	}
4573	}
4574	}
4575	}
4576
4577	return $includeCellRange;
4578	}
4579
4580	/**
4581	* MERGEDCELLS.
4582	*
4583	* This record contains the addresses of merged cell ranges
4584	* in the current sheet.
4585	*
4586	* -- "OpenOffice.org's Documentation of the Microsoft
4587	* Excel File Format"
4588	*/
4589	private function readMergedCells()
4590	{
4591	$length = self::getUInt2d($this->data, $this->pos + 2);
4592	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
4593
4594	// move stream pointer to next record
4595	$this->pos += 4 + $length;
4596
4597	if ($this->version == self::XLS_BIFF8 && !$this->readDataOnly) {
4598	$cellRangeAddressList = $this->readBIFF8CellRangeAddressList($recordData);
4599	foreach ($cellRangeAddressList['cellRangeAddresses'] as $cellRangeAddress) {
4600	if ((strpos($cellRangeAddress, ':') !== false) &&
4601	($this->includeCellRangeFiltered($cellRangeAddress))) {
4602	$this->phpSheet->mergeCells($cellRangeAddress);
4603	}
4604	}
4605	}
4606	}
4607
4608	/**
4609	* Read HYPERLINK record.
4610	*/
4611	private function readHyperLink()
4612	{
4613	$length = self::getUInt2d($this->data, $this->pos + 2);
4614	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
4615
4616	// move stream pointer forward to next record
4617	$this->pos += 4 + $length;
4618
4619	if (!$this->readDataOnly) {
4620	// offset: 0; size: 8; cell range address of all cells containing this hyperlink
4621	try {
4622	$cellRange = $this->readBIFF8CellRangeAddressFixed($recordData);
4623	} catch (PhpSpreadsheetException $e) {
4624	return;
4625	}
4626
4627	// offset: 8, size: 16; GUID of StdLink
4628
4629	// offset: 24, size: 4; unknown value
4630
4631	// offset: 28, size: 4; option flags
4632	// bit: 0; mask: 0x00000001; 0 = no link or extant, 1 = file link or URL
4633	$isFileLinkOrUrl = (0x00000001 & self::getUInt2d($recordData, 28)) >> 0;
4634
4635	// bit: 1; mask: 0x00000002; 0 = relative path, 1 = absolute path or URL
4636	$isAbsPathOrUrl = (0x00000001 & self::getUInt2d($recordData, 28)) >> 1;
4637
4638	// bit: 2 (and 4); mask: 0x00000014; 0 = no description
4639	$hasDesc = (0x00000014 & self::getUInt2d($recordData, 28)) >> 2;
4640
4641	// bit: 3; mask: 0x00000008; 0 = no text, 1 = has text
4642	$hasText = (0x00000008 & self::getUInt2d($recordData, 28)) >> 3;
4643
4644	// bit: 7; mask: 0x00000080; 0 = no target frame, 1 = has target frame
4645	$hasFrame = (0x00000080 & self::getUInt2d($recordData, 28)) >> 7;
4646
4647	// bit: 8; mask: 0x00000100; 0 = file link or URL, 1 = UNC path (inc. server name)
4648	$isUNC = (0x00000100 & self::getUInt2d($recordData, 28)) >> 8;
4649
4650	// offset within record data
4651	$offset = 32;
4652
4653	if ($hasDesc) {
4654	// offset: 32; size: var; character count of description text
4655	$dl = self::getInt4d($recordData, 32);
4656	// offset: 36; size: var; character array of description text, no Unicode string header, always 16-bit characters, zero terminated
4657	$desc = self::encodeUTF16(substr($recordData, 36, 2 * ($dl - 1)), false);
4658	$offset += 4 + 2 * $dl;
4659	}
4660	if ($hasFrame) {
4661	$fl = self::getInt4d($recordData, $offset);
4662	$offset += 4 + 2 * $fl;
4663	}
4664
4665	// detect type of hyperlink (there are 4 types)
4666	$hyperlinkType = null;
4667
4668	if ($isUNC) {
4669	$hyperlinkType = 'UNC';
4670	} elseif (!$isFileLinkOrUrl) {
4671	$hyperlinkType = 'workbook';
4672	} elseif (ord($recordData[$offset]) == 0x03) {
4673	$hyperlinkType = 'local';
4674	} elseif (ord($recordData[$offset]) == 0xE0) {
4675	$hyperlinkType = 'URL';
4676	}
4677
4678	switch ($hyperlinkType) {
4679	case 'URL':
4680	// section 5.58.2: Hyperlink containing a URL
4681	// e.g. http://example.org/index.php
4682
4683	// offset: var; size: 16; GUID of URL Moniker
4684	$offset += 16;
4685	// offset: var; size: 4; size (in bytes) of character array of the URL including trailing zero word
4686	$us = self::getInt4d($recordData, $offset);
4687	$offset += 4;
4688	// offset: var; size: $us; character array of the URL, no Unicode string header, always 16-bit characters, zero-terminated
4689	$url = self::encodeUTF16(substr($recordData, $offset, $us - 2), false);
4690	$nullOffset = strpos($url, chr(0x00));
4691	if ($nullOffset) {
4692	$url = substr($url, 0, $nullOffset);
4693	}
4694	$url .= $hasText ? '#' : '';
4695	$offset += $us;
4696
4697	break;
4698	case 'local':
4699	// section 5.58.3: Hyperlink to local file
4700	// examples:
4701	// mydoc.txt
4702	// ../../somedoc.xls#Sheet!A1
4703
4704	// offset: var; size: 16; GUI of File Moniker
4705	$offset += 16;
4706
4707	// offset: var; size: 2; directory up-level count.
4708	$upLevelCount = self::getUInt2d($recordData, $offset);
4709	$offset += 2;
4710
4711	// offset: var; size: 4; character count of the shortened file path and name, including trailing zero word
4712	$sl = self::getInt4d($recordData, $offset);
4713	$offset += 4;
4714
4715	// offset: var; size: sl; character array of the shortened file path and name in 8.3-DOS-format (compressed Unicode string)
4716	$shortenedFilePath = substr($recordData, $offset, $sl);
4717	$shortenedFilePath = self::encodeUTF16($shortenedFilePath, true);
4718	$shortenedFilePath = substr($shortenedFilePath, 0, -1); // remove trailing zero
4719
4720	$offset += $sl;
4721
4722	// offset: var; size: 24; unknown sequence
4723	$offset += 24;
4724
4725	// extended file path
4726	// offset: var; size: 4; size of the following file link field including string lenth mark
4727	$sz = self::getInt4d($recordData, $offset);
4728	$offset += 4;
4729
4730	// only present if $sz > 0
4731	if ($sz > 0) {
4732	// offset: var; size: 4; size of the character array of the extended file path and name
4733	$xl = self::getInt4d($recordData, $offset);
4734	$offset += 4;
4735
4736	// offset: var; size 2; unknown
4737	$offset += 2;
4738
4739	// offset: var; size $xl; character array of the extended file path and name.
4740	$extendedFilePath = substr($recordData, $offset, $xl);
4741	$extendedFilePath = self::encodeUTF16($extendedFilePath, false);
4742	$offset += $xl;
4743	}
4744
4745	// construct the path
4746	$url = str_repeat('..\\', $upLevelCount);
4747	$url .= ($sz > 0) ? $extendedFilePath : $shortenedFilePath; // use extended path if available
4748	$url .= $hasText ? '#' : '';
4749
4750	break;
4751	case 'UNC':
4752	// section 5.58.4: Hyperlink to a File with UNC (Universal Naming Convention) Path
4753	// todo: implement
4754	return;
4755	case 'workbook':
4756	// section 5.58.5: Hyperlink to the Current Workbook
4757	// e.g. Sheet2!B1:C2, stored in text mark field
4758	$url = 'sheet://';
4759
4760	break;
4761	default:
4762	return;
4763	}
4764
4765	if ($hasText) {
4766	// offset: var; size: 4; character count of text mark including trailing zero word
4767	$tl = self::getInt4d($recordData, $offset);
4768	$offset += 4;
4769	// offset: var; size: var; character array of the text mark without the # sign, no Unicode header, always 16-bit characters, zero-terminated
4770	$text = self::encodeUTF16(substr($recordData, $offset, 2 * ($tl - 1)), false);
4771	$url .= $text;
4772	}
4773
4774	// apply the hyperlink to all the relevant cells
4775	foreach (Coordinate::extractAllCellReferencesInRange($cellRange) as $coordinate) {
4776	$this->phpSheet->getCell($coordinate)->getHyperLink()->setUrl($url);
4777	}
4778	}
4779	}
4780
4781	/**
4782	* Read DATAVALIDATIONS record.
4783	*/
4784	private function readDataValidations()
4785	{
4786	$length = self::getUInt2d($this->data, $this->pos + 2);
4787	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
4788
4789	// move stream pointer forward to next record
4790	$this->pos += 4 + $length;
4791	}
4792
4793	/**
4794	* Read DATAVALIDATION record.
4795	*/
4796	private function readDataValidation()
4797	{
4798	$length = self::getUInt2d($this->data, $this->pos + 2);
4799	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
4800
4801	// move stream pointer forward to next record
4802	$this->pos += 4 + $length;
4803
4804	if ($this->readDataOnly) {
4805	return;
4806	}
4807
4808	// offset: 0; size: 4; Options
4809	$options = self::getInt4d($recordData, 0);
4810
4811	// bit: 0-3; mask: 0x0000000F; type
4812	$type = (0x0000000F & $options) >> 0;
4813	switch ($type) {
4814	case 0x00:
4815	$type = DataValidation::TYPE_NONE;
4816
4817	break;
4818	case 0x01:
4819	$type = DataValidation::TYPE_WHOLE;
4820
4821	break;
4822	case 0x02:
4823	$type = DataValidation::TYPE_DECIMAL;
4824
4825	break;
4826	case 0x03:
4827	$type = DataValidation::TYPE_LIST;
4828
4829	break;
4830	case 0x04:
4831	$type = DataValidation::TYPE_DATE;
4832
4833	break;
4834	case 0x05:
4835	$type = DataValidation::TYPE_TIME;
4836
4837	break;
4838	case 0x06:
4839	$type = DataValidation::TYPE_TEXTLENGTH;
4840
4841	break;
4842	case 0x07:
4843	$type = DataValidation::TYPE_CUSTOM;
4844
4845	break;
4846	}
4847
4848	// bit: 4-6; mask: 0x00000070; error type
4849	$errorStyle = (0x00000070 & $options) >> 4;
4850	switch ($errorStyle) {
4851	case 0x00:
4852	$errorStyle = DataValidation::STYLE_STOP;
4853
4854	break;
4855	case 0x01:
4856	$errorStyle = DataValidation::STYLE_WARNING;
4857
4858	break;
4859	case 0x02:
4860	$errorStyle = DataValidation::STYLE_INFORMATION;
4861
4862	break;
4863	}
4864
4865	// bit: 7; mask: 0x00000080; 1= formula is explicit (only applies to list)
4866	// I have only seen cases where this is 1
4867	$explicitFormula = (0x00000080 & $options) >> 7;
4868
4869	// bit: 8; mask: 0x00000100; 1= empty cells allowed
4870	$allowBlank = (0x00000100 & $options) >> 8;
4871
4872	// bit: 9; mask: 0x00000200; 1= suppress drop down arrow in list type validity
4873	$suppressDropDown = (0x00000200 & $options) >> 9;
4874
4875	// bit: 18; mask: 0x00040000; 1= show prompt box if cell selected
4876	$showInputMessage = (0x00040000 & $options) >> 18;
4877
4878	// bit: 19; mask: 0x00080000; 1= show error box if invalid values entered
4879	$showErrorMessage = (0x00080000 & $options) >> 19;
4880
4881	// bit: 20-23; mask: 0x00F00000; condition operator
4882	$operator = (0x00F00000 & $options) >> 20;
4883	switch ($operator) {
4884	case 0x00:
4885	$operator = DataValidation::OPERATOR_BETWEEN;
4886
4887	break;
4888	case 0x01:
4889	$operator = DataValidation::OPERATOR_NOTBETWEEN;
4890
4891	break;
4892	case 0x02:
4893	$operator = DataValidation::OPERATOR_EQUAL;
4894
4895	break;
4896	case 0x03:
4897	$operator = DataValidation::OPERATOR_NOTEQUAL;
4898
4899	break;
4900	case 0x04:
4901	$operator = DataValidation::OPERATOR_GREATERTHAN;
4902
4903	break;
4904	case 0x05:
4905	$operator = DataValidation::OPERATOR_LESSTHAN;
4906
4907	break;
4908	case 0x06:
4909	$operator = DataValidation::OPERATOR_GREATERTHANOREQUAL;
4910
4911	break;
4912	case 0x07:
4913	$operator = DataValidation::OPERATOR_LESSTHANOREQUAL;
4914
4915	break;
4916	}
4917
4918	// offset: 4; size: var; title of the prompt box
4919	$offset = 4;
4920	$string = self::readUnicodeStringLong(substr($recordData, $offset));
4921	$promptTitle = $string['value'] !== chr(0) ? $string['value'] : '';
4922	$offset += $string['size'];
4923
4924	// offset: var; size: var; title of the error box
4925	$string = self::readUnicodeStringLong(substr($recordData, $offset));
4926	$errorTitle = $string['value'] !== chr(0) ? $string['value'] : '';
4927	$offset += $string['size'];
4928
4929	// offset: var; size: var; text of the prompt box
4930	$string = self::readUnicodeStringLong(substr($recordData, $offset));
4931	$prompt = $string['value'] !== chr(0) ? $string['value'] : '';
4932	$offset += $string['size'];
4933
4934	// offset: var; size: var; text of the error box
4935	$string = self::readUnicodeStringLong(substr($recordData, $offset));
4936	$error = $string['value'] !== chr(0) ? $string['value'] : '';
4937	$offset += $string['size'];
4938
4939	// offset: var; size: 2; size of the formula data for the first condition
4940	$sz1 = self::getUInt2d($recordData, $offset);
4941	$offset += 2;
4942
4943	// offset: var; size: 2; not used
4944	$offset += 2;
4945
4946	// offset: var; size: $sz1; formula data for first condition (without size field)
4947	$formula1 = substr($recordData, $offset, $sz1);
4948	$formula1 = pack('v', $sz1) . $formula1; // prepend the length
4949	try {
4950	$formula1 = $this->getFormulaFromStructure($formula1);
4951
4952	// in list type validity, null characters are used as item separators
4953	if ($type == DataValidation::TYPE_LIST) {
4954	$formula1 = str_replace(chr(0), ',', $formula1);
4955	}
4956	} catch (PhpSpreadsheetException $e) {
4957	return;
4958	}
4959	$offset += $sz1;
4960
4961	// offset: var; size: 2; size of the formula data for the first condition
4962	$sz2 = self::getUInt2d($recordData, $offset);
4963	$offset += 2;
4964
4965	// offset: var; size: 2; not used
4966	$offset += 2;
4967
4968	// offset: var; size: $sz2; formula data for second condition (without size field)
4969	$formula2 = substr($recordData, $offset, $sz2);
4970	$formula2 = pack('v', $sz2) . $formula2; // prepend the length
4971	try {
4972	$formula2 = $this->getFormulaFromStructure($formula2);
4973	} catch (PhpSpreadsheetException $e) {
4974	return;
4975	}
4976	$offset += $sz2;
4977
4978	// offset: var; size: var; cell range address list with
4979	$cellRangeAddressList = $this->readBIFF8CellRangeAddressList(substr($recordData, $offset));
4980	$cellRangeAddresses = $cellRangeAddressList['cellRangeAddresses'];
4981
4982	foreach ($cellRangeAddresses as $cellRange) {
4983	$stRange = $this->phpSheet->shrinkRangeToFit($cellRange);
4984	foreach (Coordinate::extractAllCellReferencesInRange($stRange) as $coordinate) {
4985	$objValidation = $this->phpSheet->getCell($coordinate)->getDataValidation();
4986	$objValidation->setType($type);
4987	$objValidation->setErrorStyle($errorStyle);
4988	$objValidation->setAllowBlank((bool) $allowBlank);
4989	$objValidation->setShowInputMessage((bool) $showInputMessage);
4990	$objValidation->setShowErrorMessage((bool) $showErrorMessage);
4991	$objValidation->setShowDropDown(!$suppressDropDown);
4992	$objValidation->setOperator($operator);
4993	$objValidation->setErrorTitle($errorTitle);
4994	$objValidation->setError($error);
4995	$objValidation->setPromptTitle($promptTitle);
4996	$objValidation->setPrompt($prompt);
4997	$objValidation->setFormula1($formula1);
4998	$objValidation->setFormula2($formula2);
4999	}
5000	}
5001	}
5002
5003	/**
5004	* Read SHEETLAYOUT record. Stores sheet tab color information.
5005	*/
5006	private function readSheetLayout()
5007	{
5008	$length = self::getUInt2d($this->data, $this->pos + 2);
5009	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
5010
5011	// move stream pointer to next record
5012	$this->pos += 4 + $length;
5013
5014	// local pointer in record data
5015	$offset = 0;
5016
5017	if (!$this->readDataOnly) {
5018	// offset: 0; size: 2; repeated record identifier 0x0862
5019
5020	// offset: 2; size: 10; not used
5021
5022	// offset: 12; size: 4; size of record data
5023	// Excel 2003 uses size of 0x14 (documented), Excel 2007 uses size of 0x28 (not documented?)
5024	$sz = self::getInt4d($recordData, 12);
5025
5026	switch ($sz) {
5027	case 0x14:
5028	// offset: 16; size: 2; color index for sheet tab
5029	$colorIndex = self::getUInt2d($recordData, 16);
5030	$color = Xls\Color::map($colorIndex, $this->palette, $this->version);
5031	$this->phpSheet->getTabColor()->setRGB($color['rgb']);
5032
5033	break;
5034	case 0x28:
5035	// TODO: Investigate structure for .xls SHEETLAYOUT record as saved by MS Office Excel 2007
5036	return;
5037
5038	break;
5039	}
5040	}
5041	}
5042
5043	/**
5044	* Read SHEETPROTECTION record (FEATHEADR).
5045	*/
5046	private function readSheetProtection()
5047	{
5048	$length = self::getUInt2d($this->data, $this->pos + 2);
5049	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
5050
5051	// move stream pointer to next record
5052	$this->pos += 4 + $length;
5053
5054	if ($this->readDataOnly) {
5055	return;
5056	}
5057
5058	// offset: 0; size: 2; repeated record header
5059
5060	// offset: 2; size: 2; FRT cell reference flag (=0 currently)
5061
5062	// offset: 4; size: 8; Currently not used and set to 0
5063
5064	// offset: 12; size: 2; Shared feature type index (2=Enhanced Protetion, 4=SmartTag)
5065	$isf = self::getUInt2d($recordData, 12);
5066	if ($isf != 2) {
5067	return;
5068	}
5069
5070	// offset: 14; size: 1; =1 since this is a feat header
5071
5072	// offset: 15; size: 4; size of rgbHdrSData
5073
5074	// rgbHdrSData, assume "Enhanced Protection"
5075	// offset: 19; size: 2; option flags
5076	$options = self::getUInt2d($recordData, 19);
5077
5078	// bit: 0; mask 0x0001; 1 = user may edit objects, 0 = users must not edit objects
5079	$bool = (0x0001 & $options) >> 0;
5080	$this->phpSheet->getProtection()->setObjects(!$bool);
5081
5082	// bit: 1; mask 0x0002; edit scenarios
5083	$bool = (0x0002 & $options) >> 1;
5084	$this->phpSheet->getProtection()->setScenarios(!$bool);
5085
5086	// bit: 2; mask 0x0004; format cells
5087	$bool = (0x0004 & $options) >> 2;
5088	$this->phpSheet->getProtection()->setFormatCells(!$bool);
5089
5090	// bit: 3; mask 0x0008; format columns
5091	$bool = (0x0008 & $options) >> 3;
5092	$this->phpSheet->getProtection()->setFormatColumns(!$bool);
5093
5094	// bit: 4; mask 0x0010; format rows
5095	$bool = (0x0010 & $options) >> 4;
5096	$this->phpSheet->getProtection()->setFormatRows(!$bool);
5097
5098	// bit: 5; mask 0x0020; insert columns
5099	$bool = (0x0020 & $options) >> 5;
5100	$this->phpSheet->getProtection()->setInsertColumns(!$bool);
5101
5102	// bit: 6; mask 0x0040; insert rows
5103	$bool = (0x0040 & $options) >> 6;
5104	$this->phpSheet->getProtection()->setInsertRows(!$bool);
5105
5106	// bit: 7; mask 0x0080; insert hyperlinks
5107	$bool = (0x0080 & $options) >> 7;
5108	$this->phpSheet->getProtection()->setInsertHyperlinks(!$bool);
5109
5110	// bit: 8; mask 0x0100; delete columns
5111	$bool = (0x0100 & $options) >> 8;
5112	$this->phpSheet->getProtection()->setDeleteColumns(!$bool);
5113
5114	// bit: 9; mask 0x0200; delete rows
5115	$bool = (0x0200 & $options) >> 9;
5116	$this->phpSheet->getProtection()->setDeleteRows(!$bool);
5117
5118	// bit: 10; mask 0x0400; select locked cells
5119	$bool = (0x0400 & $options) >> 10;
5120	$this->phpSheet->getProtection()->setSelectLockedCells(!$bool);
5121
5122	// bit: 11; mask 0x0800; sort cell range
5123	$bool = (0x0800 & $options) >> 11;
5124	$this->phpSheet->getProtection()->setSort(!$bool);
5125
5126	// bit: 12; mask 0x1000; auto filter
5127	$bool = (0x1000 & $options) >> 12;
5128	$this->phpSheet->getProtection()->setAutoFilter(!$bool);
5129
5130	// bit: 13; mask 0x2000; pivot tables
5131	$bool = (0x2000 & $options) >> 13;
5132	$this->phpSheet->getProtection()->setPivotTables(!$bool);
5133
5134	// bit: 14; mask 0x4000; select unlocked cells
5135	$bool = (0x4000 & $options) >> 14;
5136	$this->phpSheet->getProtection()->setSelectUnlockedCells(!$bool);
5137
5138	// offset: 21; size: 2; not used
5139	}
5140
5141	/**
5142	* Read RANGEPROTECTION record
5143	* Reading of this record is based on Microsoft Office Excel 97-2000 Binary File Format Specification,
5144	* where it is referred to as FEAT record.
5145	*/
5146	private function readRangeProtection()
5147	{
5148	$length = self::getUInt2d($this->data, $this->pos + 2);
5149	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
5150
5151	// move stream pointer to next record
5152	$this->pos += 4 + $length;
5153
5154	// local pointer in record data
5155	$offset = 0;
5156
5157	if (!$this->readDataOnly) {
5158	$offset += 12;
5159
5160	// offset: 12; size: 2; shared feature type, 2 = enhanced protection, 4 = smart tag
5161	$isf = self::getUInt2d($recordData, 12);
5162	if ($isf != 2) {
5163	// we only read FEAT records of type 2
5164	return;
5165	}
5166	$offset += 2;
5167
5168	$offset += 5;
5169
5170	// offset: 19; size: 2; count of ref ranges this feature is on
5171	$cref = self::getUInt2d($recordData, 19);
5172	$offset += 2;
5173
5174	$offset += 6;
5175
5176	// offset: 27; size: 8 * $cref; list of cell ranges (like in hyperlink record)
5177	$cellRanges = [];
5178	for ($i = 0; $i < $cref; ++$i) {
5179	try {
5180	$cellRange = $this->readBIFF8CellRangeAddressFixed(substr($recordData, 27 + 8 * $i, 8));
5181	} catch (PhpSpreadsheetException $e) {
5182	return;
5183	}
5184	$cellRanges[] = $cellRange;
5185	$offset += 8;
5186	}
5187
5188	// offset: var; size: var; variable length of feature specific data
5189	$rgbFeat = substr($recordData, $offset);
5190	$offset += 4;
5191
5192	// offset: var; size: 4; the encrypted password (only 16-bit although field is 32-bit)
5193	$wPassword = self::getInt4d($recordData, $offset);
5194	$offset += 4;
5195
5196	// Apply range protection to sheet
5197	if ($cellRanges) {
5198	$this->phpSheet->protectCells(implode(' ', $cellRanges), strtoupper(dechex($wPassword)), true);
5199	}
5200	}
5201	}
5202
5203	/**
5204	* Read a free CONTINUE record. Free CONTINUE record may be a camouflaged MSODRAWING record
5205	* When MSODRAWING data on a sheet exceeds 8224 bytes, CONTINUE records are used instead. Undocumented.
5206	* In this case, we must treat the CONTINUE record as a MSODRAWING record.
5207	*/
5208	private function readContinue()
5209	{
5210	$length = self::getUInt2d($this->data, $this->pos + 2);
5211	$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
5212
5213	// check if we are reading drawing data
5214	// this is in case a free CONTINUE record occurs in other circumstances we are unaware of
5215	if ($this->drawingData == '') {
5216	// move stream pointer to next record
5217	$this->pos += 4 + $length;
5218
5219	return;
5220	}
5221
5222	// check if record data is at least 4 bytes long, otherwise there is no chance this is MSODRAWING data
5223	if ($length < 4) {
5224	// move stream pointer to next record
5225	$this->pos += 4 + $length;
5226
5227	return;
5228	}
5229
5230	// dirty check to see if CONTINUE record could be a camouflaged MSODRAWING record
5231	// look inside CONTINUE record to see if it looks like a part of an Escher stream
5232	// we know that Escher stream may be split at least at
5233	// 0xF003 MsofbtSpgrContainer
5234	// 0xF004 MsofbtSpContainer
5235	// 0xF00D MsofbtClientTextbox
5236	$validSplitPoints = [0xF003, 0xF004, 0xF00D]; // add identifiers if we find more
5237
5238	$splitPoint = self::getUInt2d($recordData, 2);
5239	if (in_array($splitPoint, $validSplitPoints)) {
5240	// get spliced record data (and move pointer to next record)
5241	$splicedRecordData = $this->getSplicedRecordData();
5242	$this->drawingData .= $splicedRecordData['recordData'];
5243
5244	return;
5245	}
5246
5247	// move stream pointer to next record
5248	$this->pos += 4 + $length;
5249	}
5250
5251	/**
5252	* Reads a record from current position in data stream and continues reading data as long as CONTINUE
5253	* records are found. Splices the record data pieces and returns the combined string as if record data
5254	* is in one piece.
5255	* Moves to next current position in data stream to start of next record different from a CONtINUE record.
5256	*
5257	* @return array
5258	*/
5259	private function getSplicedRecordData()
5260	{
5261	$data = '';
5262	$spliceOffsets = [];
5263
5264	$i = 0;
5265	$spliceOffsets[0] = 0;
5266
5267	do {
5268	++$i;
5269
5270	// offset: 0; size: 2; identifier
5271	$identifier = self::getUInt2d($this->data, $this->pos);
5272	// offset: 2; size: 2; length
5273	$length = self::getUInt2d($this->data, $this->pos + 2);
5274	$data .= $this->readRecordData($this->data, $this->pos + 4, $length);
5275
5276	$spliceOffsets[$i] = $spliceOffsets[$i - 1] + $length;
5277
5278	$this->pos += 4 + $length;
5279	$nextIdentifier = self::getUInt2d($this->data, $this->pos);
5280	} while ($nextIdentifier == self::XLS_TYPE_CONTINUE);
5281
5282	$splicedData = [
5283	'recordData' => $data,
5284	'spliceOffsets' => $spliceOffsets,
5285	];
5286
5287	return $splicedData;
5288	}
5289
5290	/**
5291	* Convert formula structure into human readable Excel formula like 'A3+A5*5'.
5292	*
5293	* @param string $formulaStructure The complete binary data for the formula
5294	* @param string $baseCell Base cell, only needed when formula contains tRefN tokens, e.g. with shared formulas
5295	*
5296	* @return string Human readable formula
5297	*/
5298	private function getFormulaFromStructure($formulaStructure, $baseCell = 'A1')
5299	{
5300	// offset: 0; size: 2; size of the following formula data
5301	$sz = self::getUInt2d($formulaStructure, 0);
5302
5303	// offset: 2; size: sz
5304	$formulaData = substr($formulaStructure, 2, $sz);
5305
5306	// offset: 2 + sz; size: variable (optional)
5307	if (strlen($formulaStructure) > 2 + $sz) {
5308	$additionalData = substr($formulaStructure, 2 + $sz);
5309	} else {
5310	$additionalData = '';
5311	}
5312
5313	return $this->getFormulaFromData($formulaData, $additionalData, $baseCell);
5314	}
5315
5316	/**
5317	* Take formula data and additional data for formula and return human readable formula.
5318	*
5319	* @param string $formulaData The binary data for the formula itself
5320	* @param string $additionalData Additional binary data going with the formula
5321	* @param string $baseCell Base cell, only needed when formula contains tRefN tokens, e.g. with shared formulas
5322	*
5323	* @return string Human readable formula
5324	*/
5325	private function getFormulaFromData($formulaData, $additionalData = '', $baseCell = 'A1')
5326	{
5327	// start parsing the formula data
5328	$tokens = [];
5329
5330	while (strlen($formulaData) > 0 and $token = $this->getNextToken($formulaData, $baseCell)) {
5331	$tokens[] = $token;
5332	$formulaData = substr($formulaData, $token['size']);
5333	}
5334
5335	$formulaString = $this->createFormulaFromTokens($tokens, $additionalData);
5336
5337	return $formulaString;
5338	}
5339
5340	/**
5341	* Take array of tokens together with additional data for formula and return human readable formula.
5342	*
5343	* @param array $tokens
5344	* @param string $additionalData Additional binary data going with the formula
5345	*
5346	* @return string Human readable formula
5347	*/
5348	private function createFormulaFromTokens($tokens, $additionalData)
5349	{
5350	// empty formula?
5351	if (empty($tokens)) {
5352	return '';
5353	}
5354
5355	$formulaStrings = [];
5356	foreach ($tokens as $token) {
5357	// initialize spaces
5358	$space0 = isset($space0) ? $space0 : ''; // spaces before next token, not tParen
5359	$space1 = isset($space1) ? $space1 : ''; // carriage returns before next token, not tParen
5360	$space2 = isset($space2) ? $space2 : ''; // spaces before opening parenthesis
5361	$space3 = isset($space3) ? $space3 : ''; // carriage returns before opening parenthesis
5362	$space4 = isset($space4) ? $space4 : ''; // spaces before closing parenthesis
5363	$space5 = isset($space5) ? $space5 : ''; // carriage returns before closing parenthesis
5364
5365	switch ($token['name']) {
5366	case 'tAdd': // addition
5367	case 'tConcat': // addition
5368	case 'tDiv': // division
5369	case 'tEQ': // equality
5370	case 'tGE': // greater than or equal
5371	case 'tGT': // greater than
5372	case 'tIsect': // intersection
5373	case 'tLE': // less than or equal
5374	case 'tList': // less than or equal
5375	case 'tLT': // less than
5376	case 'tMul': // multiplication
5377	case 'tNE': // multiplication
5378	case 'tPower': // power
5379	case 'tRange': // range
5380	case 'tSub': // subtraction
5381	$op2 = array_pop($formulaStrings);
5382	$op1 = array_pop($formulaStrings);
5383	$formulaStrings[] = "$op1$space1$space0{$token['data']}$op2";
5384	unset($space0, $space1);
5385
5386	break;
5387	case 'tUplus': // unary plus
5388	case 'tUminus': // unary minus
5389	$op = array_pop($formulaStrings);
5390	$formulaStrings[] = "$space1$space0{$token['data']}$op";
5391	unset($space0, $space1);
5392
5393	break;
5394	case 'tPercent': // percent sign
5395	$op = array_pop($formulaStrings);
5396	$formulaStrings[] = "$op$space1$space0{$token['data']}";
5397	unset($space0, $space1);
5398
5399	break;
5400	case 'tAttrVolatile': // indicates volatile function
5401	case 'tAttrIf':
5402	case 'tAttrSkip':
5403	case 'tAttrChoose':
5404	// token is only important for Excel formula evaluator
5405	// do nothing
5406	break;
5407	case 'tAttrSpace': // space / carriage return
5408	// space will be used when next token arrives, do not alter formulaString stack
5409	switch ($token['data']['spacetype']) {
5410	case 'type0':
5411	$space0 = str_repeat(' ', $token['data']['spacecount']);
5412
5413	break;
5414	case 'type1':
5415	$space1 = str_repeat("\n", $token['data']['spacecount']);
5416
5417	break;
5418	case 'type2':
5419	$space2 = str_repeat(' ', $token['data']['spacecount']);
5420
5421	break;
5422	case 'type3':
5423	$space3 = str_repeat("\n", $token['data']['spacecount']);
5424
5425	break;
5426	case 'type4':
5427	$space4 = str_repeat(' ', $token['data']['spacecount']);
5428
5429	break;
5430	case 'type5':
5431	$space5 = str_repeat("\n", $token['data']['spacecount']);
5432
5433	break;
5434	}
5435
5436	break;
5437	case 'tAttrSum': // SUM function with one parameter
5438	$op = array_pop($formulaStrings);
5439	$formulaStrings[] = "{$space1}{$space0}SUM($op)";
5440	unset($space0, $space1);
5441
5442	break;
5443	case 'tFunc': // function with fixed number of arguments
5444	case 'tFuncV': // function with variable number of arguments
5445	if ($token['data']['function'] != '') {
5446	// normal function
5447	$ops = []; // array of operators
5448	for ($i = 0; $i < $token['data']['args']; ++$i) {
5449	$ops[] = array_pop($formulaStrings);
5450	}
5451	$ops = array_reverse($ops);
5452	$formulaStrings[] = "$space1$space0{$token['data']['function']}(" . implode(',', $ops) . ')';
5453	unset($space0, $space1);
5454	} else {
5455	// add-in function
5456	$ops = []; // array of operators
5457	for ($i = 0; $i < $token['data']['args'] - 1; ++$i) {
5458	$ops[] = array_pop($formulaStrings);
5459	}
5460	$ops = array_reverse($ops);
5461	$function = array_pop($formulaStrings);
5462	$formulaStrings[] = "$space1$space0$function(" . implode(',', $ops) . ')';
5463	unset($space0, $space1);
5464	}
5465
5466	break;
5467	case 'tParen': // parenthesis
5468	$expression = array_pop($formulaStrings);
5469	$formulaStrings[] = "$space3$space2($expression$space5$space4)";
5470	unset($space2, $space3, $space4, $space5);
5471
5472	break;
5473	case 'tArray': // array constant
5474	$constantArray = self::readBIFF8ConstantArray($additionalData);
5475	$formulaStrings[] = $space1 . $space0 . $constantArray['value'];
5476	$additionalData = substr($additionalData, $constantArray['size']); // bite of chunk of additional data
5477	unset($space0, $space1);
5478
5479	break;
5480	case 'tMemArea':
5481	// bite off chunk of additional data
5482	$cellRangeAddressList = $this->readBIFF8CellRangeAddressList($additionalData);
5483	$additionalData = substr($additionalData, $cellRangeAddressList['size']);
5484	$formulaStrings[] = "$space1$space0{$token['data']}";
5485	unset($space0, $space1);
5486
5487	break;
5488	case 'tArea': // cell range address
5489	case 'tBool': // boolean
5490	case 'tErr': // error code
5491	case 'tInt': // integer
5492	case 'tMemErr':
5493	case 'tMemFunc':
5494	case 'tMissArg':
5495	case 'tName':
5496	case 'tNameX':
5497	case 'tNum': // number
5498	case 'tRef': // single cell reference
5499	case 'tRef3d': // 3d cell reference
5500	case 'tArea3d': // 3d cell range reference
5501	case 'tRefN':
5502	case 'tAreaN':
5503	case 'tStr': // string
5504	$formulaStrings[] = "$space1$space0{$token['data']}";
5505	unset($space0, $space1);
5506
5507	break;
5508	}
5509	}
5510	$formulaString = $formulaStrings[0];
5511
5512	return $formulaString;
5513	}
5514
5515	/**
5516	* Fetch next token from binary formula data.
5517	*
5518	* @param string $formulaData Formula data
5519	* @param string $baseCell Base cell, only needed when formula contains tRefN tokens, e.g. with shared formulas
5520	*
5521	* @throws Exception
5522	*
5523	* @return array
5524	*/
5525	private function getNextToken($formulaData, $baseCell = 'A1')
5526	{
5527	// offset: 0; size: 1; token id
5528	$id = ord($formulaData[0]); // token id
5529	$name = false; // initialize token name
5530
5531	switch ($id) {
5532	case 0x03:
5533	$name = 'tAdd';
5534	$size = 1;
5535	$data = '+';
5536
5537	break;
5538	case 0x04:
5539	$name = 'tSub';
5540	$size = 1;
5541	$data = '-';
5542
5543	break;
5544	case 0x05:
5545	$name = 'tMul';
5546	$size = 1;
5547	$data = '*';
5548
5549	break;
5550	case 0x06:
5551	$name = 'tDiv';
5552	$size = 1;
5553	$data = '/';
5554
5555	break;
5556	case 0x07:
5557	$name = 'tPower';
5558	$size = 1;
5559	$data = '^';
5560
5561	break;
5562	case 0x08:
5563	$name = 'tConcat';
5564	$size = 1;
5565	$data = '&';
5566
5567	break;
5568	case 0x09:
5569	$name = 'tLT';
5570	$size = 1;
5571	$data = '<';
5572
5573	break;
5574	case 0x0A:
5575	$name = 'tLE';
5576	$size = 1;
5577	$data = '<=';
5578
5579	break;
5580	case 0x0B:
5581	$name = 'tEQ';
5582	$size = 1;
5583	$data = '=';
5584
5585	break;
5586	case 0x0C:
5587	$name = 'tGE';
5588	$size = 1;
5589	$data = '>=';
5590
5591	break;
5592	case 0x0D:
5593	$name = 'tGT';
5594	$size = 1;
5595	$data = '>';
5596
5597	break;
5598	case 0x0E:
5599	$name = 'tNE';
5600	$size = 1;
5601	$data = '<>';
5602
5603	break;
5604	case 0x0F:
5605	$name = 'tIsect';
5606	$size = 1;
5607	$data = ' ';
5608
5609	break;
5610	case 0x10:
5611	$name = 'tList';
5612	$size = 1;
5613	$data = ',';
5614
5615	break;
5616	case 0x11:
5617	$name = 'tRange';
5618	$size = 1;
5619	$data = ':';
5620
5621	break;
5622	case 0x12:
5623	$name = 'tUplus';
5624	$size = 1;
5625	$data = '+';
5626
5627	break;
5628	case 0x13:
5629	$name = 'tUminus';
5630	$size = 1;
5631	$data = '-';
5632
5633	break;
5634	case 0x14:
5635	$name = 'tPercent';
5636	$size = 1;
5637	$data = '%';
5638
5639	break;
5640	case 0x15: // parenthesis
5641	$name = 'tParen';
5642	$size = 1;
5643	$data = null;
5644
5645	break;
5646	case 0x16: // missing argument
5647	$name = 'tMissArg';
5648	$size = 1;
5649	$data = '';
5650
5651	break;
5652	case 0x17: // string
5653	$name = 'tStr';
5654	// offset: 1; size: var; Unicode string, 8-bit string length
5655	$string = self::readUnicodeStringShort(substr($formulaData, 1));
5656	$size = 1 + $string['size'];
5657	$data = self::UTF8toExcelDoubleQuoted($string['value']);
5658
5659	break;
5660	case 0x19: // Special attribute
5661	// offset: 1; size: 1; attribute type flags:
5662	switch (ord($formulaData[1])) {
5663	case 0x01:
5664	$name = 'tAttrVolatile';
5665	$size = 4;
5666	$data = null;
5667
5668	break;
5669	case 0x02:
5670	$name = 'tAttrIf';
5671	$size = 4;
5672	$data = null;
5673
5674	break;
5675	case 0x04:
5676	$name = 'tAttrChoose';
5677	// offset: 2; size: 2; number of choices in the CHOOSE function ($nc, number of parameters decreased by 1)
5678	$nc = self::getUInt2d($formulaData, 2);
5679	// offset: 4; size: 2 * $nc
5680	// offset: 4 + 2 * $nc; size: 2
5681	$size = 2 * $nc + 6;
5682	$data = null;
5683
5684	break;
5685	case 0x08:
5686	$name = 'tAttrSkip';
5687	$size = 4;
5688	$data = null;
5689
5690	break;
5691	case 0x10:
5692	$name = 'tAttrSum';
5693	$size = 4;
5694	$data = null;
5695
5696	break;
5697	case 0x40:
5698	case 0x41:
5699	$name = 'tAttrSpace';
5700	$size = 4;
5701	// offset: 2; size: 2; space type and position
5702	switch (ord($formulaData[2])) {
5703	case 0x00:
5704	$spacetype = 'type0';
5705
5706	break;
5707	case 0x01:
5708	$spacetype = 'type1';
5709
5710	break;
5711	case 0x02:
5712	$spacetype = 'type2';
5713
5714	break;
5715	case 0x03:
5716	$spacetype = 'type3';
5717
5718	break;
5719	case 0x04:
5720	$spacetype = 'type4';
5721
5722	break;
5723	case 0x05:
5724	$spacetype = 'type5';
5725
5726	break;
5727	default:
5728	throw new Exception('Unrecognized space type in tAttrSpace token');
5729
5730	break;
5731	}
5732	// offset: 3; size: 1; number of inserted spaces/carriage returns
5733	$spacecount = ord($formulaData[3]);
5734
5735	$data = ['spacetype' => $spacetype, 'spacecount' => $spacecount];
5736
5737	break;
5738	default:
5739	throw new Exception('Unrecognized attribute flag in tAttr token');
5740
5741	break;
5742	}
5743
5744	break;
5745	case 0x1C: // error code
5746	// offset: 1; size: 1; error code
5747	$name = 'tErr';
5748	$size = 2;
5749	$data = Xls\ErrorCode::lookup(ord($formulaData[1]));
5750
5751	break;
5752	case 0x1D: // boolean
5753	// offset: 1; size: 1; 0 = false, 1 = true;
5754	$name = 'tBool';
5755	$size = 2;
5756	$data = ord($formulaData[1]) ? 'TRUE' : 'FALSE';
5757
5758	break;
5759	case 0x1E: // integer
5760	// offset: 1; size: 2; unsigned 16-bit integer
5761	$name = 'tInt';
5762	$size = 3;
5763	$data = self::getUInt2d($formulaData, 1);
5764
5765	break;
5766	case 0x1F: // number
5767	// offset: 1; size: 8;
5768	$name = 'tNum';
5769	$size = 9;
5770	$data = self::extractNumber(substr($formulaData, 1));
5771	$data = str_replace(',', '.', (string) $data); // in case non-English locale
5772	break;
5773	case 0x20: // array constant
5774	case 0x40:
5775	case 0x60:
5776	// offset: 1; size: 7; not used
5777	$name = 'tArray';
5778	$size = 8;
5779	$data = null;
5780
5781	break;
5782	case 0x21: // function with fixed number of arguments
5783	case 0x41:
5784	case 0x61:
5785	$name = 'tFunc';
5786	$size = 3;
5787	// offset: 1; size: 2; index to built-in sheet function
5788	switch (self::getUInt2d($formulaData, 1)) {
5789	case 2:
5790	$function = 'ISNA';
5791	$args = 1;
5792
5793	break;
5794	case 3:
5795	$function = 'ISERROR';
5796	$args = 1;
5797
5798	break;
5799	case 10:
5800	$function = 'NA';
5801	$args = 0;
5802
5803	break;
5804	case 15:
5805	$function = 'SIN';
5806	$args = 1;
5807
5808	break;
5809	case 16:
5810	$function = 'COS';
5811	$args = 1;
5812
5813	break;
5814	case 17:
5815	$function = 'TAN';
5816	$args = 1;
5817
5818	break;
5819	case 18:
5820	$function = 'ATAN';
5821	$args = 1;
5822
5823	break;
5824	case 19:
5825	$function = 'PI';
5826	$args = 0;
5827
5828	break;
5829	case 20:
5830	$function = 'SQRT';
5831	$args = 1;
5832
5833	break;
5834	case 21:
5835	$function = 'EXP';
5836	$args = 1;
5837
5838	break;
5839	case 22:
5840	$function = 'LN';
5841	$args = 1;
5842
5843	break;
5844	case 23:
5845	$function = 'LOG10';
5846	$args = 1;
5847
5848	break;
5849	case 24:
5850	$function = 'ABS';
5851	$args = 1;
5852
5853	break;
5854	case 25:
5855	$function = 'INT';
5856	$args = 1;
5857
5858	break;
5859	case 26:
5860	$function = 'SIGN';
5861	$args = 1;
5862
5863	break;
5864	case 27:
5865	$function = 'ROUND';
5866	$args = 2;
5867
5868	break;
5869	case 30:
5870	$function = 'REPT';
5871	$args = 2;
5872
5873	break;
5874	case 31:
5875	$function = 'MID';
5876	$args = 3;
5877
5878	break;
5879	case 32:
5880	$function = 'LEN';
5881	$args = 1;
5882
5883	break;
5884	case 33:
5885	$function = 'VALUE';
5886	$args = 1;
5887
5888	break;
5889	case 34:
5890	$function = 'TRUE';
5891	$args = 0;
5892
5893	break;
5894	case 35:
5895	$function = 'FALSE';
5896	$args = 0;
5897
5898	break;
5899	case 38:
5900	$function = 'NOT';
5901	$args = 1;
5902
5903	break;
5904	case 39:
5905	$function = 'MOD';
5906	$args = 2;
5907
5908	break;
5909	case 40:
5910	$function = 'DCOUNT';
5911	$args = 3;
5912
5913	break;
5914	case 41:
5915	$function = 'DSUM';
5916	$args = 3;
5917
5918	break;
5919	case 42:
5920	$function = 'DAVERAGE';
5921	$args = 3;
5922
5923	break;
5924	case 43:
5925	$function = 'DMIN';
5926	$args = 3;
5927
5928	break;
5929	case 44:
5930	$function = 'DMAX';
5931	$args = 3;
5932
5933	break;
5934	case 45:
5935	$function = 'DSTDEV';
5936	$args = 3;
5937
5938	break;
5939	case 48:
5940	$function = 'TEXT';
5941	$args = 2;
5942
5943	break;
5944	case 61:
5945	$function = 'MIRR';
5946	$args = 3;
5947
5948	break;
5949	case 63:
5950	$function = 'RAND';
5951	$args = 0;
5952
5953	break;
5954	case 65:
5955	$function = 'DATE';
5956	$args = 3;
5957
5958	break;
5959	case 66:
5960	$function = 'TIME';
5961	$args = 3;
5962
5963	break;
5964	case 67:
5965	$function = 'DAY';
5966	$args = 1;
5967
5968	break;
5969	case 68:
5970	$function = 'MONTH';
5971	$args = 1;
5972
5973	break;
5974	case 69:
5975	$function = 'YEAR';
5976	$args = 1;
5977
5978	break;
5979	case 71:
5980	$function = 'HOUR';
5981	$args = 1;
5982
5983	break;
5984	case 72:
5985	$function = 'MINUTE';
5986	$args = 1;
5987
5988	break;
5989	case 73:
5990	$function = 'SECOND';
5991	$args = 1;
5992
5993	break;
5994	case 74:
5995	$function = 'NOW';
5996	$args = 0;
5997
5998	break;
5999	case 75:
6000	$function = 'AREAS';
6001	$args = 1;
6002
6003	break;
6004	case 76:
6005	$function = 'ROWS';
6006	$args = 1;
6007
6008	break;
6009	case 77:
6010	$function = 'COLUMNS';
6011	$args = 1;
6012
6013	break;
6014	case 83:
6015	$function = 'TRANSPOSE';
6016	$args = 1;
6017
6018	break;
6019	case 86:
6020	$function = 'TYPE';
6021	$args = 1;
6022
6023	break;
6024	case 97:
6025	$function = 'ATAN2';
6026	$args = 2;
6027
6028	break;
6029	case 98:
6030	$function = 'ASIN';
6031	$args = 1;
6032
6033	break;
6034	case 99:
6035	$function = 'ACOS';
6036	$args = 1;
6037
6038	break;
6039	case 105:
6040	$function = 'ISREF';
6041	$args = 1;
6042
6043	break;
6044	case 111:
6045	$function = 'CHAR';
6046	$args = 1;
6047
6048	break;
6049	case 112:
6050	$function = 'LOWER';
6051	$args = 1;
6052
6053	break;
6054	case 113:
6055	$function = 'UPPER';
6056	$args = 1;
6057
6058	break;
6059	case 114:
6060	$function = 'PROPER';
6061	$args = 1;
6062
6063	break;
6064	case 117:
6065	$function = 'EXACT';
6066	$args = 2;
6067
6068	break;
6069	case 118:
6070	$function = 'TRIM';
6071	$args = 1;
6072
6073	break;
6074	case 119:
6075	$function = 'REPLACE';
6076	$args = 4;
6077
6078	break;
6079	case 121:
6080	$function = 'CODE';
6081	$args = 1;
6082
6083	break;
6084	case 126:
6085	$function = 'ISERR';
6086	$args = 1;
6087
6088	break;
6089	case 127:
6090	$function = 'ISTEXT';
6091	$args = 1;
6092
6093	break;
6094	case 128:
6095	$function = 'ISNUMBER';
6096	$args = 1;
6097
6098	break;
6099	case 129:
6100	$function = 'ISBLANK';
6101	$args = 1;
6102
6103	break;
6104	case 130:
6105	$function = 'T';
6106	$args = 1;
6107
6108	break;
6109	case 131:
6110	$function = 'N';
6111	$args = 1;
6112
6113	break;
6114	case 140:
6115	$function = 'DATEVALUE';
6116	$args = 1;
6117
6118	break;
6119	case 141:
6120	$function = 'TIMEVALUE';
6121	$args = 1;
6122
6123	break;
6124	case 142:
6125	$function = 'SLN';
6126	$args = 3;
6127
6128	break;
6129	case 143:
6130	$function = 'SYD';
6131	$args = 4;
6132
6133	break;
6134	case 162:
6135	$function = 'CLEAN';
6136	$args = 1;
6137
6138	break;
6139	case 163:
6140	$function = 'MDETERM';
6141	$args = 1;
6142
6143	break;
6144	case 164:
6145	$function = 'MINVERSE';
6146	$args = 1;
6147
6148	break;
6149	case 165:
6150	$function = 'MMULT';
6151	$args = 2;
6152
6153	break;
6154	case 184:
6155	$function = 'FACT';
6156	$args = 1;
6157
6158	break;
6159	case 189:
6160	$function = 'DPRODUCT';
6161	$args = 3;
6162
6163	break;
6164	case 190:
6165	$function = 'ISNONTEXT';
6166	$args = 1;
6167
6168	break;
6169	case 195:
6170	$function = 'DSTDEVP';
6171	$args = 3;
6172
6173	break;
6174	case 196:
6175	$function = 'DVARP';
6176	$args = 3;
6177
6178	break;
6179	case 198:
6180	$function = 'ISLOGICAL';
6181	$args = 1;
6182
6183	break;
6184	case 199:
6185	$function = 'DCOUNTA';
6186	$args = 3;
6187
6188	break;
6189	case 207:
6190	$function = 'REPLACEB';
6191	$args = 4;
6192
6193	break;
6194	case 210:
6195	$function = 'MIDB';
6196	$args = 3;
6197
6198	break;
6199	case 211:
6200	$function = 'LENB';
6201	$args = 1;
6202
6203	break;
6204	case 212:
6205	$function = 'ROUNDUP';
6206	$args = 2;
6207
6208	break;
6209	case 213:
6210	$function = 'ROUNDDOWN';
6211	$args = 2;
6212
6213	break;
6214	case 214:
6215	$function = 'ASC';
6216	$args = 1;
6217
6218	break;
6219	case 215:
6220	$function = 'DBCS';
6221	$args = 1;
6222
6223	break;
6224	case 221:
6225	$function = 'TODAY';
6226	$args = 0;
6227
6228	break;
6229	case 229:
6230	$function = 'SINH';
6231	$args = 1;
6232
6233	break;
6234	case 230:
6235	$function = 'COSH';
6236	$args = 1;
6237
6238	break;
6239	case 231:
6240	$function = 'TANH';
6241	$args = 1;
6242
6243	break;
6244	case 232:
6245	$function = 'ASINH';
6246	$args = 1;
6247
6248	break;
6249	case 233:
6250	$function = 'ACOSH';
6251	$args = 1;
6252
6253	break;
6254	case 234:
6255	$function = 'ATANH';
6256	$args = 1;
6257
6258	break;
6259	case 235:
6260	$function = 'DGET';
6261	$args = 3;
6262
6263	break;
6264	case 244:
6265	$function = 'INFO';
6266	$args = 1;
6267
6268	break;
6269	case 252:
6270	$function = 'FREQUENCY';
6271	$args = 2;
6272
6273	break;
6274	case 261:
6275	$function = 'ERROR.TYPE';
6276	$args = 1;
6277
6278	break;
6279	case 271:
6280	$function = 'GAMMALN';
6281	$args = 1;
6282
6283	break;
6284	case 273:
6285	$function = 'BINOMDIST';
6286	$args = 4;
6287
6288	break;
6289	case 274:
6290	$function = 'CHIDIST';
6291	$args = 2;
6292
6293	break;
6294	case 275:
6295	$function = 'CHIINV';
6296	$args = 2;
6297
6298	break;
6299	case 276:
6300	$function = 'COMBIN';
6301	$args = 2;
6302
6303	break;
6304	case 277:
6305	$function = 'CONFIDENCE';
6306	$args = 3;
6307
6308	break;
6309	case 278:
6310	$function = 'CRITBINOM';
6311	$args = 3;
6312
6313	break;
6314	case 279:
6315	$function = 'EVEN';
6316	$args = 1;
6317
6318	break;
6319	case 280:
6320	$function = 'EXPONDIST';
6321	$args = 3;
6322
6323	break;
6324	case 281:
6325	$function = 'FDIST';
6326	$args = 3;
6327
6328	break;
6329	case 282:
6330	$function = 'FINV';
6331	$args = 3;
6332
6333	break;
6334	case 283:
6335	$function = 'FISHER';
6336	$args = 1;
6337
6338	break;
6339	case 284:
6340	$function = 'FISHERINV';
6341	$args = 1;
6342
6343	break;
6344	case 285:
6345	$function = 'FLOOR';
6346	$args = 2;
6347
6348	break;
6349	case 286:
6350	$function = 'GAMMADIST';
6351	$args = 4;
6352
6353	break;
6354	case 287:
6355	$function = 'GAMMAINV';
6356	$args = 3;
6357
6358	break;
6359	case 288:
6360	$function = 'CEILING';
6361	$args = 2;
6362
6363	break;
6364	case 289:
6365	$function = 'HYPGEOMDIST';
6366	$args = 4;
6367
6368	break;
6369	case 290:
6370	$function = 'LOGNORMDIST';
6371	$args = 3;
6372
6373	break;
6374	case 291:
6375	$function = 'LOGINV';
6376	$args = 3;
6377
6378	break;
6379	case 292:
6380	$function = 'NEGBINOMDIST';
6381	$args = 3;
6382
6383	break;
6384	case 293:
6385	$function = 'NORMDIST';
6386	$args = 4;
6387
6388	break;
6389	case 294:
6390	$function = 'NORMSDIST';
6391	$args = 1;
6392
6393	break;
6394	case 295:
6395	$function = 'NORMINV';
6396	$args = 3;
6397
6398	break;
6399	case 296:
6400	$function = 'NORMSINV';
6401	$args = 1;
6402
6403	break;
6404	case 297:
6405	$function = 'STANDARDIZE';
6406	$args = 3;
6407
6408	break;
6409	case 298:
6410	$function = 'ODD';
6411	$args = 1;
6412
6413	break;
6414	case 299:
6415	$function = 'PERMUT';
6416	$args = 2;
6417
6418	break;
6419	case 300:
6420	$function = 'POISSON';
6421	$args = 3;
6422
6423	break;
6424	case 301:
6425	$function = 'TDIST';
6426	$args = 3;
6427
6428	break;
6429	case 302:
6430	$function = 'WEIBULL';
6431	$args = 4;
6432
6433	break;
6434	case 303:
6435	$function = 'SUMXMY2';
6436	$args = 2;
6437
6438	break;
6439	case 304:
6440	$function = 'SUMX2MY2';
6441	$args = 2;
6442
6443	break;
6444	case 305:
6445	$function = 'SUMX2PY2';
6446	$args = 2;
6447
6448	break;
6449	case 306:
6450	$function = 'CHITEST';
6451	$args = 2;
6452
6453	break;
6454	case 307:
6455	$function = 'CORREL';
6456	$args = 2;
6457
6458	break;
6459	case 308:
6460	$function = 'COVAR';
6461	$args = 2;
6462
6463	break;
6464	case 309:
6465	$function = 'FORECAST';
6466	$args = 3;
6467
6468	break;
6469	case 310:
6470	$function = 'FTEST';
6471	$args = 2;
6472
6473	break;
6474	case 311:
6475	$function = 'INTERCEPT';
6476	$args = 2;
6477
6478	break;
6479	case 312:
6480	$function = 'PEARSON';
6481	$args = 2;
6482
6483	break;
6484	case 313:
6485	$function = 'RSQ';
6486	$args = 2;
6487
6488	break;
6489	case 314:
6490	$function = 'STEYX';
6491	$args = 2;
6492
6493	break;
6494	case 315:
6495	$function = 'SLOPE';
6496	$args = 2;
6497
6498	break;
6499	case 316:
6500	$function = 'TTEST';
6501	$args = 4;
6502
6503	break;
6504	case 325:
6505	$function = 'LARGE';
6506	$args = 2;
6507
6508	break;
6509	case 326:
6510	$function = 'SMALL';
6511	$args = 2;
6512
6513	break;
6514	case 327:
6515	$function = 'QUARTILE';
6516	$args = 2;
6517
6518	break;
6519	case 328:
6520	$function = 'PERCENTILE';
6521	$args = 2;
6522
6523	break;
6524	case 331:
6525	$function = 'TRIMMEAN';
6526	$args = 2;
6527
6528	break;
6529	case 332:
6530	$function = 'TINV';
6531	$args = 2;
6532
6533	break;
6534	case 337:
6535	$function = 'POWER';
6536	$args = 2;
6537
6538	break;
6539	case 342:
6540	$function = 'RADIANS';
6541	$args = 1;
6542
6543	break;
6544	case 343:
6545	$function = 'DEGREES';
6546	$args = 1;
6547
6548	break;
6549	case 346:
6550	$function = 'COUNTIF';
6551	$args = 2;
6552
6553	break;
6554	case 347:
6555	$function = 'COUNTBLANK';
6556	$args = 1;
6557
6558	break;
6559	case 350:
6560	$function = 'ISPMT';
6561	$args = 4;
6562
6563	break;
6564	case 351:
6565	$function = 'DATEDIF';
6566	$args = 3;
6567
6568	break;
6569	case 352:
6570	$function = 'DATESTRING';
6571	$args = 1;
6572
6573	break;
6574	case 353:
6575	$function = 'NUMBERSTRING';
6576	$args = 2;
6577
6578	break;
6579	case 360:
6580	$function = 'PHONETIC';
6581	$args = 1;
6582
6583	break;
6584	case 368:
6585	$function = 'BAHTTEXT';
6586	$args = 1;
6587
6588	break;
6589	default:
6590	throw new Exception('Unrecognized function in formula');
6591
6592	break;
6593	}
6594	$data = ['function' => $function, 'args' => $args];
6595
6596	break;
6597	case 0x22: // function with variable number of arguments
6598	case 0x42:
6599	case 0x62:
6600	$name = 'tFuncV';
6601	$size = 4;
6602	// offset: 1; size: 1; number of arguments
6603	$args = ord($formulaData[1]);
6604	// offset: 2: size: 2; index to built-in sheet function
6605	$index = self::getUInt2d($formulaData, 2);
6606	switch ($index) {
6607	case 0:
6608	$function = 'COUNT';
6609
6610	break;
6611	case 1:
6612	$function = 'IF';
6613
6614	break;
6615	case 4:
6616	$function = 'SUM';
6617
6618	break;
6619	case 5:
6620	$function = 'AVERAGE';
6621
6622	break;
6623	case 6:
6624	$function = 'MIN';
6625
6626	break;
6627	case 7:
6628	$function = 'MAX';
6629
6630	break;
6631	case 8:
6632	$function = 'ROW';
6633
6634	break;
6635	case 9:
6636	$function = 'COLUMN';
6637
6638	break;
6639	case 11:
6640	$function = 'NPV';
6641
6642	break;
6643	case 12:
6644	$function = 'STDEV';
6645
6646	break;
6647	case 13:
6648	$function = 'DOLLAR';
6649
6650	break;
6651	case 14:
6652	$function = 'FIXED';
6653
6654	break;
6655	case 28:
6656	$function = 'LOOKUP';
6657
6658	break;
6659	case 29:
6660	$function = 'INDEX';
6661
6662	break;
6663	case 36:
6664	$function = 'AND';
6665
6666	break;
6667	case 37:
6668	$function = 'OR';
6669
6670	break;
6671	case 46:
6672	$function = 'VAR';
6673
6674	break;
6675	case 49:
6676	$function = 'LINEST';
6677
6678	break;
6679	case 50:
6680	$function = 'TREND';
6681
6682	break;
6683	case 51:
6684	$function = 'LOGEST';
6685
6686	break;
6687	case 52:
6688	$function = 'GROWTH';
6689
6690	break;
6691	case 56:
6692	$function = 'PV';
6693
6694	break;
6695	case 57:
6696	$function = 'FV';
6697
6698	break;
6699	case 58:
6700	$function = 'NPER';
6701
6702	break;
6703	case 59:
6704	$function = 'PMT';
6705
6706	break;
6707	case 60:
6708	$function = 'RATE';
6709
6710	break;
6711	case 62:
6712	$function = 'IRR';
6713
6714	break;
6715	case 64:
6716	$function = 'MATCH';
6717
6718	break;
6719	case 70:
6720	$function = 'WEEKDAY';
6721
6722	break;
6723	case 78:
6724	$function = 'OFFSET';
6725
6726	break;
6727	case 82:
6728	$function = 'SEARCH';
6729
6730	break;
6731	case 100:
6732	$function = 'CHOOSE';
6733
6734	break;
6735	case 101:
6736	$function = 'HLOOKUP';
6737
6738	break;
6739	case 102:
6740	$function = 'VLOOKUP';
6741
6742	break;
6743	case 109:
6744	$function = 'LOG';
6745
6746	break;
6747	case 115:
6748	$function = 'LEFT';
6749
6750	break;
6751	case 116:
6752	$function = 'RIGHT';
6753
6754	break;
6755	case 120:
6756	$function = 'SUBSTITUTE';
6757
6758	break;
6759	case 124:
6760	$function = 'FIND';
6761
6762	break;
6763	case 125:
6764	$function = 'CELL';
6765
6766	break;
6767	case 144:
6768	$function = 'DDB';
6769
6770	break;
6771	case 148:
6772	$function = 'INDIRECT';
6773
6774	break;
6775	case 167:
6776	$function = 'IPMT';
6777
6778	break;
6779	case 168:
6780	$function = 'PPMT';
6781
6782	break;
6783	case 169:
6784	$function = 'COUNTA';
6785
6786	break;
6787	case 183:
6788	$function = 'PRODUCT';
6789
6790	break;
6791	case 193:
6792	$function = 'STDEVP';
6793
6794	break;
6795	case 194:
6796	$function = 'VARP';
6797
6798	break;
6799	case 197:
6800	$function = 'TRUNC';
6801
6802	break;
6803	case 204:
6804	$function = 'USDOLLAR';
6805
6806	break;
6807	case 205:
6808	$function = 'FINDB';
6809
6810	break;
6811	case 206:
6812	$function = 'SEARCHB';
6813
6814	break;
6815	case 208:
6816	$function = 'LEFTB';
6817
6818	break;
6819	case 209:
6820	$function = 'RIGHTB';
6821
6822	break;
6823	case 216:
6824	$function = 'RANK';
6825
6826	break;
6827	case 219:
6828	$function = 'ADDRESS';
6829
6830	break;
6831	case 220:
6832	$function = 'DAYS360';
6833
6834	break;
6835	case 222:
6836	$function = 'VDB';
6837
6838	break;
6839	case 227:
6840	$function = 'MEDIAN';
6841
6842	break;
6843	case 228:
6844	$function = 'SUMPRODUCT';
6845
6846	break;
6847	case 247:
6848	$function = 'DB';
6849
6850	break;
6851	case 255:
6852	$function = '';
6853
6854	break;
6855	case 269:
6856	$function = 'AVEDEV';
6857
6858	break;
6859	case 270:
6860	$function = 'BETADIST';
6861
6862	break;
6863	case 272:
6864	$function = 'BETAINV';
6865
6866	break;
6867	case 317:
6868	$function = 'PROB';
6869
6870	break;
6871	case 318:
6872	$function = 'DEVSQ';
6873
6874	break;
6875	case 319:
6876	$function = 'GEOMEAN';
6877
6878	break;
6879	case 320:
6880	$function = 'HARMEAN';
6881
6882	break;
6883	case 321:
6884	$function = 'SUMSQ';
6885
6886	break;
6887	case 322:
6888	$function = 'KURT';
6889
6890	break;
6891	case 323:
6892	$function = 'SKEW';
6893
6894	break;
6895	case 324:
6896	$function = 'ZTEST';
6897
6898	break;
6899	case 329:
6900	$function = 'PERCENTRANK';
6901
6902	break;
6903	case 330:
6904	$function = 'MODE';
6905
6906	break;
6907	case 336:
6908	$function = 'CONCATENATE';
6909
6910	break;
6911	case 344:
6912	$function = 'SUBTOTAL';
6913
6914	break;
6915	case 345:
6916	$function = 'SUMIF';
6917
6918	break;
6919	case 354:
6920	$function = 'ROMAN';
6921
6922	break;
6923	case 358:
6924	$function = 'GETPIVOTDATA';
6925
6926	break;
6927	case 359:
6928	$function = 'HYPERLINK';
6929
6930	break;
6931	case 361:
6932	$function = 'AVERAGEA';
6933
6934	break;
6935	case 362:
6936	$function = 'MAXA';
6937
6938	break;
6939	case 363:
6940	$function = 'MINA';
6941
6942	break;
6943	case 364:
6944	$function = 'STDEVPA';
6945
6946	break;
6947	case 365:
6948	$function = 'VARPA';
6949
6950	break;
6951	case 366:
6952	$function = 'STDEVA';
6953
6954	break;
6955	case 367:
6956	$function = 'VARA';
6957
6958	break;
6959	default:
6960	throw new Exception('Unrecognized function in formula');
6961
6962	break;
6963	}
6964	$data = ['function' => $function, 'args' => $args];
6965
6966	break;
6967	case 0x23: // index to defined name
6968	case 0x43:
6969	case 0x63:
6970	$name = 'tName';
6971	$size = 5;
6972	// offset: 1; size: 2; one-based index to definedname record
6973	$definedNameIndex = self::getUInt2d($formulaData, 1) - 1;
6974	// offset: 2; size: 2; not used
6975	$data = $this->definedname[$definedNameIndex]['name'];
6976
6977	break;
6978	case 0x24: // single cell reference e.g. A5
6979	case 0x44:
6980	case 0x64:
6981	$name = 'tRef';
6982	$size = 5;
6983	$data = $this->readBIFF8CellAddress(substr($formulaData, 1, 4));
6984
6985	break;
6986	case 0x25: // cell range reference to cells in the same sheet (2d)
6987	case 0x45:
6988	case 0x65:
6989	$name = 'tArea';
6990	$size = 9;
6991	$data = $this->readBIFF8CellRangeAddress(substr($formulaData, 1, 8));
6992
6993	break;
6994	case 0x26: // Constant reference sub-expression
6995	case 0x46:
6996	case 0x66:
6997	$name = 'tMemArea';
6998	// offset: 1; size: 4; not used
6999	// offset: 5; size: 2; size of the following subexpression
7000	$subSize = self::getUInt2d($formulaData, 5);
7001	$size = 7 + $subSize;
7002	$data = $this->getFormulaFromData(substr($formulaData, 7, $subSize));
7003
7004	break;
7005	case 0x27: // Deleted constant reference sub-expression
7006	case 0x47:
7007	case 0x67:
7008	$name = 'tMemErr';
7009	// offset: 1; size: 4; not used
7010	// offset: 5; size: 2; size of the following subexpression
7011	$subSize = self::getUInt2d($formulaData, 5);
7012	$size = 7 + $subSize;
7013	$data = $this->getFormulaFromData(substr($formulaData, 7, $subSize));
7014
7015	break;
7016	case 0x29: // Variable reference sub-expression
7017	case 0x49:
7018	case 0x69:
7019	$name = 'tMemFunc';
7020	// offset: 1; size: 2; size of the following sub-expression
7021	$subSize = self::getUInt2d($formulaData, 1);
7022	$size = 3 + $subSize;
7023	$data = $this->getFormulaFromData(substr($formulaData, 3, $subSize));
7024
7025	break;
7026	case 0x2C: // Relative 2d cell reference reference, used in shared formulas and some other places
7027	case 0x4C:
7028	case 0x6C:
7029	$name = 'tRefN';
7030	$size = 5;
7031	$data = $this->readBIFF8CellAddressB(substr($formulaData, 1, 4), $baseCell);
7032
7033	break;
7034	case 0x2D: // Relative 2d range reference
7035	case 0x4D:
7036	case 0x6D:
7037	$name = 'tAreaN';
7038	$size = 9;
7039	$data = $this->readBIFF8CellRangeAddressB(substr($formulaData, 1, 8), $baseCell);
7040
7041	break;
7042	case 0x39: // External name
7043	case 0x59:
7044	case 0x79:
7045	$name = 'tNameX';
7046	$size = 7;
7047	// offset: 1; size: 2; index to REF entry in EXTERNSHEET record
7048	// offset: 3; size: 2; one-based index to DEFINEDNAME or EXTERNNAME record
7049	$index = self::getUInt2d($formulaData, 3);
7050	// assume index is to EXTERNNAME record
7051	$data = $this->externalNames[$index - 1]['name'];
7052	// offset: 5; size: 2; not used
7053	break;
7054	case 0x3A: // 3d reference to cell
7055	case 0x5A:
7056	case 0x7A:
7057	$name = 'tRef3d';
7058	$size = 7;
7059
7060	try {
7061	// offset: 1; size: 2; index to REF entry
7062	$sheetRange = $this->readSheetRangeByRefIndex(self::getUInt2d($formulaData, 1));
7063	// offset: 3; size: 4; cell address
7064	$cellAddress = $this->readBIFF8CellAddress(substr($formulaData, 3, 4));
7065
7066	$data = "$sheetRange!$cellAddress";
7067	} catch (PhpSpreadsheetException $e) {
7068	// deleted sheet reference
7069	$data = '#REF!';
7070	}
7071
7072	break;
7073	case 0x3B: // 3d reference to cell range
7074	case 0x5B:
7075	case 0x7B:
7076	$name = 'tArea3d';
7077	$size = 11;
7078
7079	try {
7080	// offset: 1; size: 2; index to REF entry
7081	$sheetRange = $this->readSheetRangeByRefIndex(self::getUInt2d($formulaData, 1));
7082	// offset: 3; size: 8; cell address
7083	$cellRangeAddress = $this->readBIFF8CellRangeAddress(substr($formulaData, 3, 8));
7084
7085	$data = "$sheetRange!$cellRangeAddress";
7086	} catch (PhpSpreadsheetException $e) {
7087	// deleted sheet reference
7088	$data = '#REF!';
7089	}
7090
7091	break;
7092	// Unknown cases // don't know how to deal with
7093	default:
7094	throw new Exception('Unrecognized token ' . sprintf('%02X', $id) . ' in formula');
7095
7096	break;
7097	}
7098
7099	return [
7100	'id' => $id,
7101	'name' => $name,
7102	'size' => $size,
7103	'data' => $data,
7104	];
7105	}
7106
7107	/**
7108	* Reads a cell address in BIFF8 e.g. 'A2' or '$A$2'
7109	* section 3.3.4.
7110	*
7111	* @param string $cellAddressStructure
7112	*
7113	* @return string
7114	*/
7115	private function readBIFF8CellAddress($cellAddressStructure)
7116	{
7117	// offset: 0; size: 2; index to row (0... 65535) (or offset (-32768... 32767))
7118	$row = self::getUInt2d($cellAddressStructure, 0) + 1;
7119
7120	// offset: 2; size: 2; index to column or column offset + relative flags
7121	// bit: 7-0; mask 0x00FF; column index
7122	$column = Coordinate::stringFromColumnIndex((0x00FF & self::getUInt2d($cellAddressStructure, 2)) + 1);
7123
7124	// bit: 14; mask 0x4000; (1 = relative column index, 0 = absolute column index)
7125	if (!(0x4000 & self::getUInt2d($cellAddressStructure, 2))) {
7126	$column = '$' . $column;
7127	}
7128	// bit: 15; mask 0x8000; (1 = relative row index, 0 = absolute row index)
7129	if (!(0x8000 & self::getUInt2d($cellAddressStructure, 2))) {
7130	$row = '$' . $row;
7131	}
7132
7133	return $column . $row;
7134	}
7135
7136	/**
7137	* Reads a cell address in BIFF8 for shared formulas. Uses positive and negative values for row and column
7138	* to indicate offsets from a base cell
7139	* section 3.3.4.
7140	*
7141	* @param string $cellAddressStructure
7142	* @param string $baseCell Base cell, only needed when formula contains tRefN tokens, e.g. with shared formulas
7143	*
7144	* @return string
7145	*/
7146	private function readBIFF8CellAddressB($cellAddressStructure, $baseCell = 'A1')
7147	{
7148	list($baseCol, $baseRow) = Coordinate::coordinateFromString($baseCell);
7149	$baseCol = Coordinate::columnIndexFromString($baseCol) - 1;
7150
7151	// offset: 0; size: 2; index to row (0... 65535) (or offset (-32768... 32767))
7152	$rowIndex = self::getUInt2d($cellAddressStructure, 0);
7153	$row = self::getUInt2d($cellAddressStructure, 0) + 1;
7154
7155	// bit: 14; mask 0x4000; (1 = relative column index, 0 = absolute column index)
7156	if (!(0x4000 & self::getUInt2d($cellAddressStructure, 2))) {
7157	// offset: 2; size: 2; index to column or column offset + relative flags
7158	// bit: 7-0; mask 0x00FF; column index
7159	$colIndex = 0x00FF & self::getUInt2d($cellAddressStructure, 2);
7160
7161	$column = Coordinate::stringFromColumnIndex($colIndex + 1);
7162	$column = '$' . $column;
7163	} else {
7164	// offset: 2; size: 2; index to column or column offset + relative flags
7165	// bit: 7-0; mask 0x00FF; column index
7166	$relativeColIndex = 0x00FF & self::getInt2d($cellAddressStructure, 2);
7167	$colIndex = $baseCol + $relativeColIndex;
7168	$colIndex = ($colIndex < 256) ? $colIndex : $colIndex - 256;
7169	$colIndex = ($colIndex >= 0) ? $colIndex : $colIndex + 256;
7170	$column = Coordinate::stringFromColumnIndex($colIndex + 1);
7171	}
7172
7173	// bit: 15; mask 0x8000; (1 = relative row index, 0 = absolute row index)
7174	if (!(0x8000 & self::getUInt2d($cellAddressStructure, 2))) {
7175	$row = '$' . $row;
7176	} else {
7177	$rowIndex = ($rowIndex <= 32767) ? $rowIndex : $rowIndex - 65536;
7178	$row = $baseRow + $rowIndex;
7179	}
7180
7181	return $column . $row;
7182	}
7183
7184	/**
7185	* Reads a cell range address in BIFF5 e.g. 'A2:B6' or 'A1'
7186	* always fixed range
7187	* section 2.5.14.
7188	*
7189	* @param string $subData
7190	*
7191	* @throws Exception
7192	*
7193	* @return string
7194	*/
7195	private function readBIFF5CellRangeAddressFixed($subData)
7196	{
7197	// offset: 0; size: 2; index to first row
7198	$fr = self::getUInt2d($subData, 0) + 1;
7199
7200	// offset: 2; size: 2; index to last row
7201	$lr = self::getUInt2d($subData, 2) + 1;
7202
7203	// offset: 4; size: 1; index to first column
7204	$fc = ord($subData[4]);
7205
7206	// offset: 5; size: 1; index to last column
7207	$lc = ord($subData[5]);
7208
7209	// check values
7210	if ($fr > $lr \|\| $fc > $lc) {
7211	throw new Exception('Not a cell range address');
7212	}
7213
7214	// column index to letter
7215	$fc = Coordinate::stringFromColumnIndex($fc + 1);
7216	$lc = Coordinate::stringFromColumnIndex($lc + 1);
7217
7218	if ($fr == $lr and $fc == $lc) {
7219	return "$fc$fr";
7220	}
7221
7222	return "$fc$fr:$lc$lr";
7223	}
7224
7225	/**
7226	* Reads a cell range address in BIFF8 e.g. 'A2:B6' or 'A1'
7227	* always fixed range
7228	* section 2.5.14.
7229	*
7230	* @param string $subData
7231	*
7232	* @throws Exception
7233	*
7234	* @return string
7235	*/
7236	private function readBIFF8CellRangeAddressFixed($subData)
7237	{
7238	// offset: 0; size: 2; index to first row
7239	$fr = self::getUInt2d($subData, 0) + 1;
7240
7241	// offset: 2; size: 2; index to last row
7242	$lr = self::getUInt2d($subData, 2) + 1;
7243
7244	// offset: 4; size: 2; index to first column
7245	$fc = self::getUInt2d($subData, 4);
7246
7247	// offset: 6; size: 2; index to last column
7248	$lc = self::getUInt2d($subData, 6);
7249
7250	// check values
7251	if ($fr > $lr \|\| $fc > $lc) {
7252	throw new Exception('Not a cell range address');
7253	}
7254
7255	// column index to letter
7256	$fc = Coordinate::stringFromColumnIndex($fc + 1);
7257	$lc = Coordinate::stringFromColumnIndex($lc + 1);
7258
7259	if ($fr == $lr and $fc == $lc) {
7260	return "$fc$fr";
7261	}
7262
7263	return "$fc$fr:$lc$lr";
7264	}
7265
7266	/**
7267	* Reads a cell range address in BIFF8 e.g. 'A2:B6' or '$A$2:$B$6'
7268	* there are flags indicating whether column/row index is relative
7269	* section 3.3.4.
7270	*
7271	* @param string $subData
7272	*
7273	* @return string
7274	*/
7275	private function readBIFF8CellRangeAddress($subData)
7276	{
7277	// todo: if cell range is just a single cell, should this funciton
7278	// not just return e.g. 'A1' and not 'A1:A1' ?
7279
7280	// offset: 0; size: 2; index to first row (0... 65535) (or offset (-32768... 32767))
7281	$fr = self::getUInt2d($subData, 0) + 1;
7282
7283	// offset: 2; size: 2; index to last row (0... 65535) (or offset (-32768... 32767))
7284	$lr = self::getUInt2d($subData, 2) + 1;
7285
7286	// offset: 4; size: 2; index to first column or column offset + relative flags
7287
7288	// bit: 7-0; mask 0x00FF; column index
7289	$fc = Coordinate::stringFromColumnIndex((0x00FF & self::getUInt2d($subData, 4)) + 1);
7290
7291	// bit: 14; mask 0x4000; (1 = relative column index, 0 = absolute column index)
7292	if (!(0x4000 & self::getUInt2d($subData, 4))) {
7293	$fc = '$' . $fc;
7294	}
7295
7296	// bit: 15; mask 0x8000; (1 = relative row index, 0 = absolute row index)
7297	if (!(0x8000 & self::getUInt2d($subData, 4))) {
7298	$fr = '$' . $fr;
7299	}
7300
7301	// offset: 6; size: 2; index to last column or column offset + relative flags
7302
7303	// bit: 7-0; mask 0x00FF; column index
7304	$lc = Coordinate::stringFromColumnIndex((0x00FF & self::getUInt2d($subData, 6)) + 1);
7305
7306	// bit: 14; mask 0x4000; (1 = relative column index, 0 = absolute column index)
7307	if (!(0x4000 & self::getUInt2d($subData, 6))) {
7308	$lc = '$' . $lc;
7309	}
7310
7311	// bit: 15; mask 0x8000; (1 = relative row index, 0 = absolute row index)
7312	if (!(0x8000 & self::getUInt2d($subData, 6))) {
7313	$lr = '$' . $lr;
7314	}
7315
7316	return "$fc$fr:$lc$lr";
7317	}
7318
7319	/**
7320	* Reads a cell range address in BIFF8 for shared formulas. Uses positive and negative values for row and column
7321	* to indicate offsets from a base cell
7322	* section 3.3.4.
7323	*
7324	* @param string $subData
7325	* @param string $baseCell Base cell
7326	*
7327	* @return string Cell range address
7328	*/
7329	private function readBIFF8CellRangeAddressB($subData, $baseCell = 'A1')
7330	{
7331	list($baseCol, $baseRow) = Coordinate::coordinateFromString($baseCell);
7332	$baseCol = Coordinate::columnIndexFromString($baseCol) - 1;
7333
7334	// TODO: if cell range is just a single cell, should this funciton
7335	// not just return e.g. 'A1' and not 'A1:A1' ?
7336
7337	// offset: 0; size: 2; first row
7338	$frIndex = self::getUInt2d($subData, 0); // adjust below
7339
7340	// offset: 2; size: 2; relative index to first row (0... 65535) should be treated as offset (-32768... 32767)
7341	$lrIndex = self::getUInt2d($subData, 2); // adjust below
7342
7343	// bit: 14; mask 0x4000; (1 = relative column index, 0 = absolute column index)
7344	if (!(0x4000 & self::getUInt2d($subData, 4))) {
7345	// absolute column index
7346	// offset: 4; size: 2; first column with relative/absolute flags
7347	// bit: 7-0; mask 0x00FF; column index
7348	$fcIndex = 0x00FF & self::getUInt2d($subData, 4);
7349	$fc = Coordinate::stringFromColumnIndex($fcIndex + 1);
7350	$fc = '$' . $fc;
7351	} else {
7352	// column offset
7353	// offset: 4; size: 2; first column with relative/absolute flags
7354	// bit: 7-0; mask 0x00FF; column index
7355	$relativeFcIndex = 0x00FF & self::getInt2d($subData, 4);
7356	$fcIndex = $baseCol + $relativeFcIndex;
7357	$fcIndex = ($fcIndex < 256) ? $fcIndex : $fcIndex - 256;
7358	$fcIndex = ($fcIndex >= 0) ? $fcIndex : $fcIndex + 256;
7359	$fc = Coordinate::stringFromColumnIndex($fcIndex + 1);
7360	}
7361
7362	// bit: 15; mask 0x8000; (1 = relative row index, 0 = absolute row index)
7363	if (!(0x8000 & self::getUInt2d($subData, 4))) {
7364	// absolute row index
7365	$fr = $frIndex + 1;
7366	$fr = '$' . $fr;
7367	} else {
7368	// row offset
7369	$frIndex = ($frIndex <= 32767) ? $frIndex : $frIndex - 65536;
7370	$fr = $baseRow + $frIndex;
7371	}
7372
7373	// bit: 14; mask 0x4000; (1 = relative column index, 0 = absolute column index)
7374	if (!(0x4000 & self::getUInt2d($subData, 6))) {
7375	// absolute column index
7376	// offset: 6; size: 2; last column with relative/absolute flags
7377	// bit: 7-0; mask 0x00FF; column index
7378	$lcIndex = 0x00FF & self::getUInt2d($subData, 6);
7379	$lc = Coordinate::stringFromColumnIndex($lcIndex + 1);
7380	$lc = '$' . $lc;
7381	} else {
7382	// column offset
7383	// offset: 4; size: 2; first column with relative/absolute flags
7384	// bit: 7-0; mask 0x00FF; column index
7385	$relativeLcIndex = 0x00FF & self::getInt2d($subData, 4);
7386	$lcIndex = $baseCol + $relativeLcIndex;
7387	$lcIndex = ($lcIndex < 256) ? $lcIndex : $lcIndex - 256;
7388	$lcIndex = ($lcIndex >= 0) ? $lcIndex : $lcIndex + 256;
7389	$lc = Coordinate::stringFromColumnIndex($lcIndex + 1);
7390	}
7391
7392	// bit: 15; mask 0x8000; (1 = relative row index, 0 = absolute row index)
7393	if (!(0x8000 & self::getUInt2d($subData, 6))) {
7394	// absolute row index
7395	$lr = $lrIndex + 1;
7396	$lr = '$' . $lr;
7397	} else {
7398	// row offset
7399	$lrIndex = ($lrIndex <= 32767) ? $lrIndex : $lrIndex - 65536;
7400	$lr = $baseRow + $lrIndex;
7401	}
7402
7403	return "$fc$fr:$lc$lr";
7404	}
7405
7406	/**
7407	* Read BIFF8 cell range address list
7408	* section 2.5.15.
7409	*
7410	* @param string $subData
7411	*
7412	* @return array
7413	*/
7414	private function readBIFF8CellRangeAddressList($subData)
7415	{
7416	$cellRangeAddresses = [];
7417
7418	// offset: 0; size: 2; number of the following cell range addresses
7419	$nm = self::getUInt2d($subData, 0);
7420
7421	$offset = 2;
7422	// offset: 2; size: 8 * $nm; list of $nm (fixed) cell range addresses
7423	for ($i = 0; $i < $nm; ++$i) {
7424	$cellRangeAddresses[] = $this->readBIFF8CellRangeAddressFixed(substr($subData, $offset, 8));
7425	$offset += 8;
7426	}
7427
7428	return [
7429	'size' => 2 + 8 * $nm,
7430	'cellRangeAddresses' => $cellRangeAddresses,
7431	];
7432	}
7433
7434	/**
7435	* Read BIFF5 cell range address list
7436	* section 2.5.15.
7437	*
7438	* @param string $subData
7439	*
7440	* @return array
7441	*/
7442	private function readBIFF5CellRangeAddressList($subData)
7443	{
7444	$cellRangeAddresses = [];
7445
7446	// offset: 0; size: 2; number of the following cell range addresses
7447	$nm = self::getUInt2d($subData, 0);
7448
7449	$offset = 2;
7450	// offset: 2; size: 6 * $nm; list of $nm (fixed) cell range addresses
7451	for ($i = 0; $i < $nm; ++$i) {
7452	$cellRangeAddresses[] = $this->readBIFF5CellRangeAddressFixed(substr($subData, $offset, 6));
7453	$offset += 6;
7454	}
7455
7456	return [
7457	'size' => 2 + 6 * $nm,
7458	'cellRangeAddresses' => $cellRangeAddresses,
7459	];
7460	}
7461
7462	/**
7463	* Get a sheet range like Sheet1:Sheet3 from REF index
7464	* Note: If there is only one sheet in the range, one gets e.g Sheet1
7465	* It can also happen that the REF structure uses the -1 (FFFF) code to indicate deleted sheets,
7466	* in which case an Exception is thrown.
7467	*
7468	* @param int $index
7469	*
7470	* @throws Exception
7471	*
7472	* @return false\|string
7473	*/
7474	private function readSheetRangeByRefIndex($index)
7475	{
7476	if (isset($this->ref[$index])) {
7477	$type = $this->externalBooks[$this->ref[$index]['externalBookIndex']]['type'];
7478
7479	switch ($type) {
7480	case 'internal':
7481	// check if we have a deleted 3d reference
7482	if ($this->ref[$index]['firstSheetIndex'] == 0xFFFF or $this->ref[$index]['lastSheetIndex'] == 0xFFFF) {
7483	throw new Exception('Deleted sheet reference');
7484	}
7485
7486	// we have normal sheet range (collapsed or uncollapsed)
7487	$firstSheetName = $this->sheets[$this->ref[$index]['firstSheetIndex']]['name'];
7488	$lastSheetName = $this->sheets[$this->ref[$index]['lastSheetIndex']]['name'];
7489
7490	if ($firstSheetName == $lastSheetName) {
7491	// collapsed sheet range
7492	$sheetRange = $firstSheetName;
7493	} else {
7494	$sheetRange = "$firstSheetName:$lastSheetName";
7495	}
7496
7497	// escape the single-quotes
7498	$sheetRange = str_replace("'", "''", $sheetRange);
7499
7500	// if there are special characters, we need to enclose the range in single-quotes
7501	// todo: check if we have identified the whole set of special characters
7502	// it seems that the following characters are not accepted for sheet names
7503	// and we may assume that they are not present: []*/:\?
7504	if (preg_match("/[ !\"@#£$%&{()}<>=+'\|^,;-]/u", $sheetRange)) {
7505	$sheetRange = "'$sheetRange'";
7506	}
7507
7508	return $sheetRange;
7509
7510	break;
7511	default:
7512	// TODO: external sheet support
7513	throw new Exception('Xls reader only supports internal sheets in formulas');
7514
7515	break;
7516	}
7517	}
7518
7519	return false;
7520	}
7521
7522	/**
7523	* read BIFF8 constant value array from array data
7524	* returns e.g. ['value' => '{1,2;3,4}', 'size' => 40]
7525	* section 2.5.8.
7526	*
7527	* @param string $arrayData
7528	*
7529	* @return array
7530	*/
7531	private static function readBIFF8ConstantArray($arrayData)
7532	{
7533	// offset: 0; size: 1; number of columns decreased by 1
7534	$nc = ord($arrayData[0]);
7535
7536	// offset: 1; size: 2; number of rows decreased by 1
7537	$nr = self::getUInt2d($arrayData, 1);
7538	$size = 3; // initialize
7539	$arrayData = substr($arrayData, 3);
7540
7541	// offset: 3; size: var; list of ($nc + 1) * ($nr + 1) constant values
7542	$matrixChunks = [];
7543	for ($r = 1; $r <= $nr + 1; ++$r) {
7544	$items = [];
7545	for ($c = 1; $c <= $nc + 1; ++$c) {
7546	$constant = self::readBIFF8Constant($arrayData);
7547	$items[] = $constant['value'];
7548	$arrayData = substr($arrayData, $constant['size']);
7549	$size += $constant['size'];
7550	}
7551	$matrixChunks[] = implode(',', $items); // looks like e.g. '1,"hello"'
7552	}
7553	$matrix = '{' . implode(';', $matrixChunks) . '}';
7554
7555	return [
7556	'value' => $matrix,
7557	'size' => $size,
7558	];
7559	}
7560
7561	/**
7562	* read BIFF8 constant value which may be 'Empty Value', 'Number', 'String Value', 'Boolean Value', 'Error Value'
7563	* section 2.5.7
7564	* returns e.g. ['value' => '5', 'size' => 9].
7565	*
7566	* @param string $valueData
7567	*
7568	* @return array
7569	*/
7570	private static function readBIFF8Constant($valueData)
7571	{
7572	// offset: 0; size: 1; identifier for type of constant
7573	$identifier = ord($valueData[0]);
7574
7575	switch ($identifier) {
7576	case 0x00: // empty constant (what is this?)
7577	$value = '';
7578	$size = 9;
7579
7580	break;
7581	case 0x01: // number
7582	// offset: 1; size: 8; IEEE 754 floating-point value
7583	$value = self::extractNumber(substr($valueData, 1, 8));
7584	$size = 9;
7585
7586	break;
7587	case 0x02: // string value
7588	// offset: 1; size: var; Unicode string, 16-bit string length
7589	$string = self::readUnicodeStringLong(substr($valueData, 1));
7590	$value = '"' . $string['value'] . '"';
7591	$size = 1 + $string['size'];
7592
7593	break;
7594	case 0x04: // boolean
7595	// offset: 1; size: 1; 0 = FALSE, 1 = TRUE
7596	if (ord($valueData[1])) {
7597	$value = 'TRUE';
7598	} else {
7599	$value = 'FALSE';
7600	}
7601	$size = 9;
7602
7603	break;
7604	case 0x10: // error code
7605	// offset: 1; size: 1; error code
7606	$value = Xls\ErrorCode::lookup(ord($valueData[1]));
7607	$size = 9;
7608
7609	break;
7610	}
7611
7612	return [
7613	'value' => $value,
7614	'size' => $size,
7615	];
7616	}
7617
7618	/**
7619	* Extract RGB color
7620	* OpenOffice.org's Documentation of the Microsoft Excel File Format, section 2.5.4.
7621	*
7622	* @param string $rgb Encoded RGB value (4 bytes)
7623	*
7624	* @return array
7625	*/
7626	private static function readRGB($rgb)
7627	{
7628	// offset: 0; size 1; Red component
7629	$r = ord($rgb[0]);
7630
7631	// offset: 1; size: 1; Green component
7632	$g = ord($rgb[1]);
7633
7634	// offset: 2; size: 1; Blue component
7635	$b = ord($rgb[2]);
7636
7637	// HEX notation, e.g. 'FF00FC'
7638	$rgb = sprintf('%02X%02X%02X', $r, $g, $b);
7639
7640	return ['rgb' => $rgb];
7641	}
7642
7643	/**
7644	* Read byte string (8-bit string length)
7645	* OpenOffice documentation: 2.5.2.
7646	*
7647	* @param string $subData
7648	*
7649	* @return array
7650	*/
7651	private function readByteStringShort($subData)
7652	{
7653	// offset: 0; size: 1; length of the string (character count)
7654	$ln = ord($subData[0]);
7655
7656	// offset: 1: size: var; character array (8-bit characters)
7657	$value = $this->decodeCodepage(substr($subData, 1, $ln));
7658
7659	return [
7660	'value' => $value,
7661	'size' => 1 + $ln, // size in bytes of data structure
7662	];
7663	}
7664
7665	/**
7666	* Read byte string (16-bit string length)
7667	* OpenOffice documentation: 2.5.2.
7668	*
7669	* @param string $subData
7670	*
7671	* @return array
7672	*/
7673	private function readByteStringLong($subData)
7674	{
7675	// offset: 0; size: 2; length of the string (character count)
7676	$ln = self::getUInt2d($subData, 0);
7677
7678	// offset: 2: size: var; character array (8-bit characters)
7679	$value = $this->decodeCodepage(substr($subData, 2));
7680
7681	//return $string;
7682	return [
7683	'value' => $value,
7684	'size' => 2 + $ln, // size in bytes of data structure
7685	];
7686	}
7687
7688	/**
7689	* Extracts an Excel Unicode short string (8-bit string length)
7690	* OpenOffice documentation: 2.5.3
7691	* function will automatically find out where the Unicode string ends.
7692	*
7693	* @param string $subData
7694	*
7695	* @return array
7696	*/
7697	private static function readUnicodeStringShort($subData)
7698	{
7699	$value = '';
7700
7701	// offset: 0: size: 1; length of the string (character count)
7702	$characterCount = ord($subData[0]);
7703
7704	$string = self::readUnicodeString(substr($subData, 1), $characterCount);
7705
7706	// add 1 for the string length
7707	$string['size'] += 1;
7708
7709	return $string;
7710	}
7711
7712	/**
7713	* Extracts an Excel Unicode long string (16-bit string length)
7714	* OpenOffice documentation: 2.5.3
7715	* this function is under construction, needs to support rich text, and Asian phonetic settings.
7716	*
7717	* @param string $subData
7718	*
7719	* @return array
7720	*/
7721	private static function readUnicodeStringLong($subData)
7722	{
7723	$value = '';
7724
7725	// offset: 0: size: 2; length of the string (character count)
7726	$characterCount = self::getUInt2d($subData, 0);
7727
7728	$string = self::readUnicodeString(substr($subData, 2), $characterCount);
7729
7730	// add 2 for the string length
7731	$string['size'] += 2;
7732
7733	return $string;
7734	}
7735
7736	/**
7737	* Read Unicode string with no string length field, but with known character count
7738	* this function is under construction, needs to support rich text, and Asian phonetic settings
7739	* OpenOffice.org's Documentation of the Microsoft Excel File Format, section 2.5.3.
7740	*
7741	* @param string $subData
7742	* @param int $characterCount
7743	*
7744	* @return array
7745	*/
7746	private static function readUnicodeString($subData, $characterCount)
7747	{
7748	$value = '';
7749
7750	// offset: 0: size: 1; option flags
7751	// bit: 0; mask: 0x01; character compression (0 = compressed 8-bit, 1 = uncompressed 16-bit)
7752	$isCompressed = !((0x01 & ord($subData[0])) >> 0);
7753
7754	// bit: 2; mask: 0x04; Asian phonetic settings
7755	$hasAsian = (0x04) & ord($subData[0]) >> 2;
7756
7757	// bit: 3; mask: 0x08; Rich-Text settings
7758	$hasRichText = (0x08) & ord($subData[0]) >> 3;
7759
7760	// offset: 1: size: var; character array
7761	// this offset assumes richtext and Asian phonetic settings are off which is generally wrong
7762	// needs to be fixed
7763	$value = self::encodeUTF16(substr($subData, 1, $isCompressed ? $characterCount : 2 * $characterCount), $isCompressed);
7764
7765	return [
7766	'value' => $value,
7767	'size' => $isCompressed ? 1 + $characterCount : 1 + 2 * $characterCount, // the size in bytes including the option flags
7768	];
7769	}
7770
7771	/**
7772	* Convert UTF-8 string to string surounded by double quotes. Used for explicit string tokens in formulas.
7773	* Example: hello"world --> "hello""world".
7774	*
7775	* @param string $value UTF-8 encoded string
7776	*
7777	* @return string
7778	*/
7779	private static function UTF8toExcelDoubleQuoted($value)
7780	{
7781	return '"' . str_replace('"', '""', $value) . '"';
7782	}
7783
7784	/**
7785	* Reads first 8 bytes of a string and return IEEE 754 float.
7786	*
7787	* @param string $data Binary string that is at least 8 bytes long
7788	*
7789	* @return float
7790	*/
7791	private static function extractNumber($data)
7792	{
7793	$rknumhigh = self::getInt4d($data, 4);
7794	$rknumlow = self::getInt4d($data, 0);
7795	$sign = ($rknumhigh & 0x80000000) >> 31;
7796	$exp = (($rknumhigh & 0x7ff00000) >> 20) - 1023;
7797	$mantissa = (0x100000 \| ($rknumhigh & 0x000fffff));
7798	$mantissalow1 = ($rknumlow & 0x80000000) >> 31;
7799	$mantissalow2 = ($rknumlow & 0x7fffffff);
7800	$value = $mantissa / pow(2, (20 - $exp));
7801
7802	if ($mantissalow1 != 0) {
7803	$value += 1 / pow(2, (21 - $exp));
7804	}
7805
7806	$value += $mantissalow2 / pow(2, (52 - $exp));
7807	if ($sign) {
7808	$value *= -1;
7809	}
7810
7811	return $value;
7812	}
7813
7814	/**
7815	* @param int $rknum
7816	*
7817	* @return float
7818	*/
7819	private static function getIEEE754($rknum)
7820	{
7821	if (($rknum & 0x02) != 0) {
7822	$value = $rknum >> 2;
7823	} else {
7824	// changes by mmp, info on IEEE754 encoding from
7825	// research.microsoft.com/~hollasch/cgindex/coding/ieeefloat.html
7826	// The RK format calls for using only the most significant 30 bits
7827	// of the 64 bit floating point value. The other 34 bits are assumed
7828	// to be 0 so we use the upper 30 bits of $rknum as follows...
7829	$sign = ($rknum & 0x80000000) >> 31;
7830	$exp = ($rknum & 0x7ff00000) >> 20;
7831	$mantissa = (0x100000 \| ($rknum & 0x000ffffc));
7832	$value = $mantissa / pow(2, (20 - ($exp - 1023)));
7833	if ($sign) {
7834	$value = -1 * $value;
7835	}
7836	//end of changes by mmp
7837	}
7838	if (($rknum & 0x01) != 0) {
7839	$value /= 100;
7840	}
7841
7842	return $value;
7843	}
7844
7845	/**
7846	* Get UTF-8 string from (compressed or uncompressed) UTF-16 string.
7847	*
7848	* @param string $string
7849	* @param bool $compressed
7850	*
7851	* @return string
7852	*/
7853	private static function encodeUTF16($string, $compressed = false)
7854	{
7855	if ($compressed) {
7856	$string = self::uncompressByteString($string);
7857	}
7858
7859	return StringHelper::convertEncoding($string, 'UTF-8', 'UTF-16LE');
7860	}
7861
7862	/**
7863	* Convert UTF-16 string in compressed notation to uncompressed form. Only used for BIFF8.
7864	*
7865	* @param string $string
7866	*
7867	* @return string
7868	*/
7869	private static function uncompressByteString($string)
7870	{
7871	$uncompressedString = '';
7872	$strLen = strlen($string);
7873	for ($i = 0; $i < $strLen; ++$i) {
7874	$uncompressedString .= $string[$i] . "\0";
7875	}
7876
7877	return $uncompressedString;
7878	}
7879
7880	/**
7881	* Convert string to UTF-8. Only used for BIFF5.
7882	*
7883	* @param string $string
7884	*
7885	* @return string
7886	*/
7887	private function decodeCodepage($string)
7888	{
7889	return StringHelper::convertEncoding($string, 'UTF-8', $this->codepage);
7890	}
7891
7892	/**
7893	* Read 16-bit unsigned integer.
7894	*
7895	* @param string $data
7896	* @param int $pos
7897	*
7898	* @return int
7899	*/
7900	public static function getUInt2d($data, $pos)
7901	{
7902	return ord($data[$pos]) \| (ord($data[$pos + 1]) << 8);
7903	}
7904
7905	/**
7906	* Read 16-bit signed integer.
7907	*
7908	* @param string $data
7909	* @param int $pos
7910	*
7911	* @return int
7912	*/
7913	public static function getInt2d($data, $pos)
7914	{
7915	return unpack('s', $data[$pos] . $data[$pos + 1])[1];
7916	}
7917
7918	/**
7919	* Read 32-bit signed integer.
7920	*
7921	* @param string $data
7922	* @param int $pos
7923	*
7924	* @return int
7925	*/
7926	public static function getInt4d($data, $pos)
7927	{
7928	// FIX: represent numbers correctly on 64-bit system
7929	// http://sourceforge.net/tracker/index.php?func=detail&aid=1487372&group_id=99160&atid=623334
7930	// Changed by Andreas Rehm 2006 to ensure correct result of the <<24 block on 32 and 64bit systems
7931	$_or_24 = ord($data[$pos + 3]);
7932	if ($_or_24 >= 128) {
7933	// negative number
7934	$_ord_24 = -abs((256 - $_or_24) << 24);
7935	} else {
7936	$_ord_24 = ($_or_24 & 127) << 24;
7937	}
7938
7939	return ord($data[$pos]) \| (ord($data[$pos + 1]) << 8) \| (ord($data[$pos + 2]) << 16) \| $_ord_24;
7940	}
7941
7942	private function parseRichText($is)
7943	{
7944	$value = new RichText();
7945	$value->createText($is);
7946
7947	return $value;
7948	}
7949	}
7950