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