ILIAS  release_5-3 Revision v5.3.23-19-g915713cf615
Excel5.php
Go to the documentation of this file.
1<?php
28// Original file header of ParseXL (used as the base for this class):
29// --------------------------------------------------------------------------------
30// Adapted from Excel_Spreadsheet_Reader developed by users bizon153,
31// trex005, and mmp11 (SourceForge.net)
32// http://sourceforge.net/projects/phpexcelreader/
33// Primary changes made by canyoncasa (dvc) for ParseXL 1.00 ...
34// Modelled moreso after Perl Excel Parse/Write modules
35// Added Parse_Excel_Spreadsheet object
36// Reads a whole worksheet or tab as row,column array or as
37// associated hash of indexed rows and named column fields
38// Added variables for worksheet (tab) indexes and names
39// Added an object call for loading individual woorksheets
40// Changed default indexing defaults to 0 based arrays
41// Fixed date/time and percent formats
42// Includes patches found at SourceForge...
43// unicode patch by nobody
44// unpack("d") machine depedency patch by matchy
45// boundsheet utf16 patch by bjaenichen
46// Renamed functions for shorter names
47// General code cleanup and rigor, including <80 column width
48// Included a testcase Excel file and PHP example calls
49// Code works for PHP 5.x
50
51// Primary changes made by canyoncasa (dvc) for ParseXL 1.10 ...
52// http://sourceforge.net/tracker/index.php?func=detail&aid=1466964&group_id=99160&atid=623334
53// Decoding of formula conditions, results, and tokens.
54// Support for user-defined named cells added as an array "namedcells"
55// Patch code for user-defined named cells supports single cells only.
56// NOTE: this patch only works for BIFF8 as BIFF5-7 use a different
57// external sheet reference structure
58
59
61if (!defined('PHPEXCEL_ROOT')) {
65 define('PHPEXCEL_ROOT', dirname(__FILE__) . '/../../');
66 require(PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php');
67}
68
79{
80 // ParseXL definitions
81 const XLS_BIFF8 = 0x0600;
82 const XLS_BIFF7 = 0x0500;
83 const XLS_WorkbookGlobals = 0x0005;
84 const XLS_Worksheet = 0x0010;
85
86 // record identifiers
87 const XLS_Type_FORMULA = 0x0006;
88 const XLS_Type_EOF = 0x000a;
89 const XLS_Type_PROTECT = 0x0012;
90 const XLS_Type_OBJECTPROTECT = 0x0063;
91 const XLS_Type_SCENPROTECT = 0x00dd;
92 const XLS_Type_PASSWORD = 0x0013;
93 const XLS_Type_HEADER = 0x0014;
94 const XLS_Type_FOOTER = 0x0015;
95 const XLS_Type_EXTERNSHEET = 0x0017;
96 const XLS_Type_DEFINEDNAME = 0x0018;
99 const XLS_Type_NOTE = 0x001c;
100 const XLS_Type_SELECTION = 0x001d;
101 const XLS_Type_DATEMODE = 0x0022;
102 const XLS_Type_EXTERNNAME = 0x0023;
103 const XLS_Type_LEFTMARGIN = 0x0026;
104 const XLS_Type_RIGHTMARGIN = 0x0027;
105 const XLS_Type_TOPMARGIN = 0x0028;
106 const XLS_Type_BOTTOMMARGIN = 0x0029;
108 const XLS_Type_FILEPASS = 0x002f;
109 const XLS_Type_FONT = 0x0031;
110 const XLS_Type_CONTINUE = 0x003c;
111 const XLS_Type_PANE = 0x0041;
112 const XLS_Type_CODEPAGE = 0x0042;
113 const XLS_Type_DEFCOLWIDTH = 0x0055;
114 const XLS_Type_OBJ = 0x005d;
115 const XLS_Type_COLINFO = 0x007d;
116 const XLS_Type_IMDATA = 0x007f;
117 const XLS_Type_SHEETPR = 0x0081;
118 const XLS_Type_HCENTER = 0x0083;
119 const XLS_Type_VCENTER = 0x0084;
120 const XLS_Type_SHEET = 0x0085;
121 const XLS_Type_PALETTE = 0x0092;
122 const XLS_Type_SCL = 0x00a0;
123 const XLS_Type_PAGESETUP = 0x00a1;
124 const XLS_Type_MULRK = 0x00bd;
125 const XLS_Type_MULBLANK = 0x00be;
126 const XLS_Type_DBCELL = 0x00d7;
127 const XLS_Type_XF = 0x00e0;
128 const XLS_Type_MERGEDCELLS = 0x00e5;
130 const XLS_Type_MSODRAWING = 0x00ec;
131 const XLS_Type_SST = 0x00fc;
132 const XLS_Type_LABELSST = 0x00fd;
133 const XLS_Type_EXTSST = 0x00ff;
134 const XLS_Type_EXTERNALBOOK = 0x01ae;
136 const XLS_Type_TXO = 0x01b6;
137 const XLS_Type_HYPERLINK = 0x01b8;
139 const XLS_Type_DIMENSION = 0x0200;
140 const XLS_Type_BLANK = 0x0201;
141 const XLS_Type_NUMBER = 0x0203;
142 const XLS_Type_LABEL = 0x0204;
143 const XLS_Type_BOOLERR = 0x0205;
144 const XLS_Type_STRING = 0x0207;
145 const XLS_Type_ROW = 0x0208;
146 const XLS_Type_INDEX = 0x020b;
147 const XLS_Type_ARRAY = 0x0221;
149 const XLS_Type_WINDOW2 = 0x023e;
150 const XLS_Type_RK = 0x027e;
151 const XLS_Type_STYLE = 0x0293;
152 const XLS_Type_FORMAT = 0x041e;
153 const XLS_Type_SHAREDFMLA = 0x04bc;
154 const XLS_Type_BOF = 0x0809;
157 const XLS_Type_SHEETLAYOUT = 0x0862;
158 const XLS_Type_XFEXT = 0x087d;
160 const XLS_Type_UNKNOWN = 0xffff;
161
162 // Encryption type
166
167 // Size of stream blocks when using RC4 encryption
168 const REKEY_BLOCK = 0x400;
169
176
183
190
196 private $_data;
197
203 private $_dataSize;
204
210 private $_pos;
211
217 private $_phpExcel;
218
224 private $_phpSheet;
225
231 private $_version;
232
239 private $_codepage;
240
246 private $_formats;
247
253 private $_objFonts;
254
260 private $_palette;
261
267 private $_sheets;
268
275
281 private $_ref;
282
289
296
302 private $_sst;
303
309 private $_frozen;
310
317
323 private $_objs;
324
331
337 private $_cellNotes;
338
345
352
358 private $_xfIndex;
359
366
373
380
388
394 private $_encryption = 0;
395
401 private $_encryptionStartPos = false;
402
408 private $_rc4Key = null;
409
415 private $_rc4Pos = 0;
416
422 private $_md5Ctxt = null;
423
427 public function __construct() {
428 $this->_readFilter = new PHPExcel_Reader_DefaultReadFilter();
429 }
430
431
439 public function canRead($pFilename)
440 {
441 // Check if file exists
442 if (!file_exists($pFilename)) {
443 throw new PHPExcel_Reader_Exception("Could not open " . $pFilename . " for reading! File does not exist.");
444 }
445
446 try {
447 // Use ParseXL for the hard work.
448 $ole = new PHPExcel_Shared_OLERead();
449
450 // get excel data
451 $res = $ole->read($pFilename);
452 return true;
453 } catch (PHPExcel_Exception $e) {
454 return false;
455 }
456 }
457
458
465 public function listWorksheetNames($pFilename)
466 {
467 // Check if file exists
468 if (!file_exists($pFilename)) {
469 throw new PHPExcel_Reader_Exception("Could not open " . $pFilename . " for reading! File does not exist.");
470 }
471
472 $worksheetNames = array();
473
474 // Read the OLE file
475 $this->_loadOLE($pFilename);
476
477 // total byte size of Excel data (workbook global substream + sheet substreams)
478 $this->_dataSize = strlen($this->_data);
479
480 $this->_pos = 0;
481 $this->_sheets = array();
482
483 // Parse Workbook Global Substream
484 while ($this->_pos < $this->_dataSize) {
485 $code = self::_GetInt2d($this->_data, $this->_pos);
486
487 switch ($code) {
488 case self::XLS_Type_BOF: $this->_readBof(); break;
489 case self::XLS_Type_SHEET: $this->_readSheet(); break;
490 case self::XLS_Type_EOF: $this->_readDefault(); break 2;
491 default: $this->_readDefault(); break;
492 }
493 }
494
495 foreach ($this->_sheets as $sheet) {
496 if ($sheet['sheetType'] != 0x00) {
497 // 0x00: Worksheet, 0x02: Chart, 0x06: Visual Basic module
498 continue;
499 }
500
501 $worksheetNames[] = $sheet['name'];
502 }
503
504 return $worksheetNames;
505 }
506
507
514 public function listWorksheetInfo($pFilename)
515 {
516 // Check if file exists
517 if (!file_exists($pFilename)) {
518 throw new PHPExcel_Reader_Exception("Could not open " . $pFilename . " for reading! File does not exist.");
519 }
520
521 $worksheetInfo = array();
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 = array();
532
533 // Parse Workbook Global Substream
534 while ($this->_pos < $this->_dataSize) {
535 $code = self::_GetInt2d($this->_data, $this->_pos);
536
537 switch ($code) {
538 case self::XLS_Type_BOF: $this->_readBof(); break;
539 case self::XLS_Type_SHEET: $this->_readSheet(); break;
540 case self::XLS_Type_EOF: $this->_readDefault(); break 2;
541 default: $this->_readDefault(); break;
542 }
543 }
544
545 // Parse the individual sheets
546 foreach ($this->_sheets as $sheet) {
547
548 if ($sheet['sheetType'] != 0x00) {
549 // 0x00: Worksheet
550 // 0x02: Chart
551 // 0x06: Visual Basic module
552 continue;
553 }
554
555 $tmpInfo = array();
556 $tmpInfo['worksheetName'] = $sheet['name'];
557 $tmpInfo['lastColumnLetter'] = 'A';
558 $tmpInfo['lastColumnIndex'] = 0;
559 $tmpInfo['totalRows'] = 0;
560 $tmpInfo['totalColumns'] = 0;
561
562 $this->_pos = $sheet['offset'];
563
564 while ($this->_pos <= $this->_dataSize - 4) {
565 $code = self::_GetInt2d($this->_data, $this->_pos);
566
567 switch ($code) {
574 $length = self::_GetInt2d($this->_data, $this->_pos + 2);
575 $recordData = $this->_readRecordData($this->_data, $this->_pos + 4, $length);
576
577 // move stream pointer to next record
578 $this->_pos += 4 + $length;
579
580 $rowIndex = self::_GetInt2d($recordData, 0) + 1;
581 $columnIndex = self::_GetInt2d($recordData, 2);
582
583 $tmpInfo['totalRows'] = max($tmpInfo['totalRows'], $rowIndex);
584 $tmpInfo['lastColumnIndex'] = max($tmpInfo['lastColumnIndex'], $columnIndex);
585 break;
586 case self::XLS_Type_BOF: $this->_readBof(); break;
587 case self::XLS_Type_EOF: $this->_readDefault(); break 2;
588 default: $this->_readDefault(); break;
589 }
590 }
591
592 $tmpInfo['lastColumnLetter'] = PHPExcel_Cell::stringFromColumnIndex($tmpInfo['lastColumnIndex']);
593 $tmpInfo['totalColumns'] = $tmpInfo['lastColumnIndex'] + 1;
594
595 $worksheetInfo[] = $tmpInfo;
596 }
597
598 return $worksheetInfo;
599 }
600
601
609 public function load($pFilename)
610 {
611 // Read the OLE file
612 $this->_loadOLE($pFilename);
613
614 // Initialisations
615 $this->_phpExcel = new PHPExcel;
616 $this->_phpExcel->removeSheetByIndex(0); // remove 1st sheet
617 if (!$this->_readDataOnly) {
618 $this->_phpExcel->removeCellStyleXfByIndex(0); // remove the default style
619 $this->_phpExcel->removeCellXfByIndex(0); // remove the default style
620 }
621
622 // Read the summary information stream (containing meta data)
624
625 // Read the Additional document summary information stream (containing application-specific meta data)
627
628 // total byte size of Excel data (workbook global substream + sheet substreams)
629 $this->_dataSize = strlen($this->_data);
630
631 // initialize
632 $this->_pos = 0;
633 $this->_codepage = 'CP1252';
634 $this->_formats = array();
635 $this->_objFonts = array();
636 $this->_palette = array();
637 $this->_sheets = array();
638 $this->_externalBooks = array();
639 $this->_ref = array();
640 $this->_definedname = array();
641 $this->_sst = array();
642 $this->_drawingGroupData = '';
643 $this->_xfIndex = '';
644 $this->_mapCellXfIndex = array();
645 $this->_mapCellStyleXfIndex = array();
646
647 // Parse Workbook Global Substream
648 while ($this->_pos < $this->_dataSize) {
649 $code = self::_GetInt2d($this->_data, $this->_pos);
650
651 switch ($code) {
652 case self::XLS_Type_BOF: $this->_readBof(); break;
653 case self::XLS_Type_FILEPASS: $this->_readFilepass(); break;
654 case self::XLS_Type_CODEPAGE: $this->_readCodepage(); break;
655 case self::XLS_Type_DATEMODE: $this->_readDateMode(); break;
656 case self::XLS_Type_FONT: $this->_readFont(); break;
657 case self::XLS_Type_FORMAT: $this->_readFormat(); break;
658 case self::XLS_Type_XF: $this->_readXf(); break;
659 case self::XLS_Type_XFEXT: $this->_readXfExt(); break;
660 case self::XLS_Type_STYLE: $this->_readStyle(); break;
661 case self::XLS_Type_PALETTE: $this->_readPalette(); break;
662 case self::XLS_Type_SHEET: $this->_readSheet(); break;
664 case self::XLS_Type_EXTERNNAME: $this->_readExternName(); break;
665 case self::XLS_Type_EXTERNSHEET: $this->_readExternSheet(); break;
666 case self::XLS_Type_DEFINEDNAME: $this->_readDefinedName(); break;
668 case self::XLS_Type_SST: $this->_readSst(); break;
669 case self::XLS_Type_EOF: $this->_readDefault(); break 2;
670 default: $this->_readDefault(); break;
671 }
672 }
673
674 // Resolve indexed colors for font, fill, and border colors
675 // Cannot be resolved already in XF record, because PALETTE record comes afterwards
676 if (!$this->_readDataOnly) {
677 foreach ($this->_objFonts as $objFont) {
678 if (isset($objFont->colorIndex)) {
679 $color = self::_readColor($objFont->colorIndex,$this->_palette,$this->_version);
680 $objFont->getColor()->setRGB($color['rgb']);
681 }
682 }
683
684 foreach ($this->_phpExcel->getCellXfCollection() as $objStyle) {
685 // fill start and end color
686 $fill = $objStyle->getFill();
687
688 if (isset($fill->startcolorIndex)) {
689 $startColor = self::_readColor($fill->startcolorIndex,$this->_palette,$this->_version);
690 $fill->getStartColor()->setRGB($startColor['rgb']);
691 }
692
693 if (isset($fill->endcolorIndex)) {
694 $endColor = self::_readColor($fill->endcolorIndex,$this->_palette,$this->_version);
695 $fill->getEndColor()->setRGB($endColor['rgb']);
696 }
697
698 // border colors
699 $top = $objStyle->getBorders()->getTop();
700 $right = $objStyle->getBorders()->getRight();
701 $bottom = $objStyle->getBorders()->getBottom();
702 $left = $objStyle->getBorders()->getLeft();
703 $diagonal = $objStyle->getBorders()->getDiagonal();
704
705 if (isset($top->colorIndex)) {
706 $borderTopColor = self::_readColor($top->colorIndex,$this->_palette,$this->_version);
707 $top->getColor()->setRGB($borderTopColor['rgb']);
708 }
709
710 if (isset($right->colorIndex)) {
711 $borderRightColor = self::_readColor($right->colorIndex,$this->_palette,$this->_version);
712 $right->getColor()->setRGB($borderRightColor['rgb']);
713 }
714
715 if (isset($bottom->colorIndex)) {
716 $borderBottomColor = self::_readColor($bottom->colorIndex,$this->_palette,$this->_version);
717 $bottom->getColor()->setRGB($borderBottomColor['rgb']);
718 }
719
720 if (isset($left->colorIndex)) {
721 $borderLeftColor = self::_readColor($left->colorIndex,$this->_palette,$this->_version);
722 $left->getColor()->setRGB($borderLeftColor['rgb']);
723 }
724
725 if (isset($diagonal->colorIndex)) {
726 $borderDiagonalColor = self::_readColor($diagonal->colorIndex,$this->_palette,$this->_version);
727 $diagonal->getColor()->setRGB($borderDiagonalColor['rgb']);
728 }
729 }
730 }
731
732 // treat MSODRAWINGGROUP records, workbook-level Escher
733 if (!$this->_readDataOnly && $this->_drawingGroupData) {
734 $escherWorkbook = new PHPExcel_Shared_Escher();
735 $reader = new PHPExcel_Reader_Excel5_Escher($escherWorkbook);
736 $escherWorkbook = $reader->load($this->_drawingGroupData);
737
738 // debug Escher stream
739 //$debug = new Debug_Escher(new PHPExcel_Shared_Escher());
740 //$debug->load($this->_drawingGroupData);
741 }
742
743 // Parse the individual sheets
744 foreach ($this->_sheets as $sheet) {
745
746 if ($sheet['sheetType'] != 0x00) {
747 // 0x00: Worksheet, 0x02: Chart, 0x06: Visual Basic module
748 continue;
749 }
750
751 // check if sheet should be skipped
752 if (isset($this->_loadSheetsOnly) && !in_array($sheet['name'], $this->_loadSheetsOnly)) {
753 continue;
754 }
755
756 // add sheet to PHPExcel object
757 $this->_phpSheet = $this->_phpExcel->createSheet();
758 // Use false for $updateFormulaCellReferences to prevent adjustment of worksheet references in formula
759 // cells... during the load, all formulae should be correct, and we're simply bringing the worksheet
760 // name in line with the formula, not the reverse
761 $this->_phpSheet->setTitle($sheet['name'],false);
762 $this->_phpSheet->setSheetState($sheet['sheetState']);
763
764 $this->_pos = $sheet['offset'];
765
766 // Initialize isFitToPages. May change after reading SHEETPR record.
767 $this->_isFitToPages = false;
768
769 // Initialize drawingData
770 $this->_drawingData = '';
771
772 // Initialize objs
773 $this->_objs = array();
774
775 // Initialize shared formula parts
776 $this->_sharedFormulaParts = array();
777
778 // Initialize shared formulas
779 $this->_sharedFormulas = array();
780
781 // Initialize text objs
782 $this->_textObjects = array();
783
784 // Initialize cell annotations
785 $this->_cellNotes = array();
786 $this->textObjRef = -1;
787
788 while ($this->_pos <= $this->_dataSize - 4) {
789 $code = self::_GetInt2d($this->_data, $this->_pos);
790
791 switch ($code) {
792 case self::XLS_Type_BOF: $this->_readBof(); break;
795 case self::XLS_Type_SHEETPR: $this->_readSheetPr(); break;
798 case self::XLS_Type_HEADER: $this->_readHeader(); break;
799 case self::XLS_Type_FOOTER: $this->_readFooter(); break;
800 case self::XLS_Type_HCENTER: $this->_readHcenter(); break;
801 case self::XLS_Type_VCENTER: $this->_readVcenter(); break;
802 case self::XLS_Type_LEFTMARGIN: $this->_readLeftMargin(); break;
803 case self::XLS_Type_RIGHTMARGIN: $this->_readRightMargin(); break;
804 case self::XLS_Type_TOPMARGIN: $this->_readTopMargin(); break;
806 case self::XLS_Type_PAGESETUP: $this->_readPageSetup(); break;
807 case self::XLS_Type_PROTECT: $this->_readProtect(); break;
808 case self::XLS_Type_SCENPROTECT: $this->_readScenProtect(); break;
810 case self::XLS_Type_PASSWORD: $this->_readPassword(); break;
811 case self::XLS_Type_DEFCOLWIDTH: $this->_readDefColWidth(); break;
812 case self::XLS_Type_COLINFO: $this->_readColInfo(); break;
813 case self::XLS_Type_DIMENSION: $this->_readDefault(); break;
814 case self::XLS_Type_ROW: $this->_readRow(); break;
815 case self::XLS_Type_DBCELL: $this->_readDefault(); break;
816 case self::XLS_Type_RK: $this->_readRk(); break;
817 case self::XLS_Type_LABELSST: $this->_readLabelSst(); break;
818 case self::XLS_Type_MULRK: $this->_readMulRk(); break;
819 case self::XLS_Type_NUMBER: $this->_readNumber(); break;
820 case self::XLS_Type_FORMULA: $this->_readFormula(); break;
821 case self::XLS_Type_SHAREDFMLA: $this->_readSharedFmla(); break;
822 case self::XLS_Type_BOOLERR: $this->_readBoolErr(); break;
823 case self::XLS_Type_MULBLANK: $this->_readMulBlank(); break;
824 case self::XLS_Type_LABEL: $this->_readLabel(); break;
825 case self::XLS_Type_BLANK: $this->_readBlank(); break;
826 case self::XLS_Type_MSODRAWING: $this->_readMsoDrawing(); break;
827 case self::XLS_Type_OBJ: $this->_readObj(); break;
828 case self::XLS_Type_WINDOW2: $this->_readWindow2(); break;
830 case self::XLS_Type_SCL: $this->_readScl(); break;
831 case self::XLS_Type_PANE: $this->_readPane(); break;
832 case self::XLS_Type_SELECTION: $this->_readSelection(); break;
833 case self::XLS_Type_MERGEDCELLS: $this->_readMergedCells(); break;
834 case self::XLS_Type_HYPERLINK: $this->_readHyperLink(); break;
837 case self::XLS_Type_SHEETLAYOUT: $this->_readSheetLayout(); break;
840 case self::XLS_Type_NOTE: $this->_readNote(); break;
841 //case self::XLS_Type_IMDATA: $this->_readImData(); break;
842 case self::XLS_Type_TXO: $this->_readTextObject(); break;
843 case self::XLS_Type_CONTINUE: $this->_readContinue(); break;
844 case self::XLS_Type_EOF: $this->_readDefault(); break 2;
845 default: $this->_readDefault(); break;
846 }
847
848 }
849
850 // treat MSODRAWING records, sheet-level Escher
851 if (!$this->_readDataOnly && $this->_drawingData) {
852 $escherWorksheet = new PHPExcel_Shared_Escher();
853 $reader = new PHPExcel_Reader_Excel5_Escher($escherWorksheet);
854 $escherWorksheet = $reader->load($this->_drawingData);
855
856 // debug Escher stream
857 //$debug = new Debug_Escher(new PHPExcel_Shared_Escher());
858 //$debug->load($this->_drawingData);
859
860 // get all spContainers in one long array, so they can be mapped to OBJ records
861 $allSpContainers = $escherWorksheet->getDgContainer()->getSpgrContainer()->getAllSpContainers();
862 }
863
864 // treat OBJ records
865 foreach ($this->_objs as $n => $obj) {
866// echo '<hr /><b>Object</b> reference is ',$n,'<br />';
867// var_dump($obj);
868// echo '<br />';
869
870 // the first shape container never has a corresponding OBJ record, hence $n + 1
871 if (isset($allSpContainers[$n + 1]) && is_object($allSpContainers[$n + 1])) {
872 $spContainer = $allSpContainers[$n + 1];
873
874 // we skip all spContainers that are a part of a group shape since we cannot yet handle those
875 if ($spContainer->getNestingLevel() > 1) {
876 continue;
877 }
878
879 // calculate the width and height of the shape
880 list($startColumn, $startRow) = PHPExcel_Cell::coordinateFromString($spContainer->getStartCoordinates());
881 list($endColumn, $endRow) = PHPExcel_Cell::coordinateFromString($spContainer->getEndCoordinates());
882
883 $startOffsetX = $spContainer->getStartOffsetX();
884 $startOffsetY = $spContainer->getStartOffsetY();
885 $endOffsetX = $spContainer->getEndOffsetX();
886 $endOffsetY = $spContainer->getEndOffsetY();
887
888 $width = PHPExcel_Shared_Excel5::getDistanceX($this->_phpSheet, $startColumn, $startOffsetX, $endColumn, $endOffsetX);
889 $height = PHPExcel_Shared_Excel5::getDistanceY($this->_phpSheet, $startRow, $startOffsetY, $endRow, $endOffsetY);
890
891 // calculate offsetX and offsetY of the shape
892 $offsetX = $startOffsetX * PHPExcel_Shared_Excel5::sizeCol($this->_phpSheet, $startColumn) / 1024;
893 $offsetY = $startOffsetY * PHPExcel_Shared_Excel5::sizeRow($this->_phpSheet, $startRow) / 256;
894
895 switch ($obj['otObjType']) {
896 case 0x19:
897 // Note
898// echo 'Cell Annotation Object<br />';
899// echo 'Object ID is ',$obj['idObjID'],'<br />';
900//
901 if (isset($this->_cellNotes[$obj['idObjID']])) {
902 $cellNote = $this->_cellNotes[$obj['idObjID']];
903
904 if (isset($this->_textObjects[$obj['idObjID']])) {
905 $textObject = $this->_textObjects[$obj['idObjID']];
906 $this->_cellNotes[$obj['idObjID']]['objTextData'] = $textObject;
907 }
908 }
909 break;
910
911 case 0x08:
912// echo 'Picture Object<br />';
913 // picture
914
915 // get index to BSE entry (1-based)
916 $BSEindex = $spContainer->getOPT(0x0104);
917 $BSECollection = $escherWorkbook->getDggContainer()->getBstoreContainer()->getBSECollection();
918 $BSE = $BSECollection[$BSEindex - 1];
919 $blipType = $BSE->getBlipType();
920
921 // need check because some blip types are not supported by Escher reader such as EMF
922 if ($blip = $BSE->getBlip()) {
923 $ih = imagecreatefromstring($blip->getData());
924 $drawing = new PHPExcel_Worksheet_MemoryDrawing();
925 $drawing->setImageResource($ih);
926
927 // width, height, offsetX, offsetY
928 $drawing->setResizeProportional(false);
929 $drawing->setWidth($width);
930 $drawing->setHeight($height);
931 $drawing->setOffsetX($offsetX);
932 $drawing->setOffsetY($offsetY);
933
934 switch ($blipType) {
936 $drawing->setRenderingFunction(PHPExcel_Worksheet_MemoryDrawing::RENDERING_JPEG);
938 break;
939
941 $drawing->setRenderingFunction(PHPExcel_Worksheet_MemoryDrawing::RENDERING_PNG);
943 break;
944 }
945
946 $drawing->setWorksheet($this->_phpSheet);
947 $drawing->setCoordinates($spContainer->getStartCoordinates());
948 }
949
950 break;
951
952 default:
953 // other object type
954 break;
955
956 }
957 }
958 }
959
960 // treat SHAREDFMLA records
961 if ($this->_version == self::XLS_BIFF8) {
962 foreach ($this->_sharedFormulaParts as $cell => $baseCell) {
964 if (($this->getReadFilter() !== NULL) && $this->getReadFilter()->readCell($column, $row, $this->_phpSheet->getTitle()) ) {
965 $formula = $this->_getFormulaFromStructure($this->_sharedFormulas[$baseCell], $cell);
966 $this->_phpSheet->getCell($cell)->setValueExplicit('=' . $formula, PHPExcel_Cell_DataType::TYPE_FORMULA);
967 }
968 }
969 }
970
971 if (!empty($this->_cellNotes)) {
972 foreach($this->_cellNotes as $note => $noteDetails) {
973 if (!isset($noteDetails['objTextData'])) {
974 if (isset($this->_textObjects[$note])) {
975 $textObject = $this->_textObjects[$note];
976 $noteDetails['objTextData'] = $textObject;
977 } else {
978 $noteDetails['objTextData']['text'] = '';
979 }
980 }
981// echo '<b>Cell annotation ',$note,'</b><br />';
982// var_dump($noteDetails);
983// echo '<br />';
984 $cellAddress = str_replace('$','',$noteDetails['cellRef']);
985 $this->_phpSheet->getComment( $cellAddress )
986 ->setAuthor( $noteDetails['author'] )
987 ->setText($this->_parseRichText($noteDetails['objTextData']['text']) );
988 }
989 }
990 }
991
992 // add the named ranges (defined names)
993 foreach ($this->_definedname as $definedName) {
994 if ($definedName['isBuiltInName']) {
995 switch ($definedName['name']) {
996
997 case pack('C', 0x06):
998 // print area
999 // in general, formula looks like this: Foo!$C$7:$J$66,Bar!$A$1:$IV$2
1000 $ranges = explode(',', $definedName['formula']); // FIXME: what if sheetname contains comma?
1001
1002 $extractedRanges = array();
1003 foreach ($ranges as $range) {
1004 // $range should look like one of these
1005 // Foo!$C$7:$J$66
1006 // Bar!$A$1:$IV$2
1007
1008 $explodes = explode('!', $range); // FIXME: what if sheetname contains exclamation mark?
1009 $sheetName = trim($explodes[0], "'");
1010
1011 if (count($explodes) == 2) {
1012 if (strpos($explodes[1], ':') === FALSE) {
1013 $explodes[1] = $explodes[1] . ':' . $explodes[1];
1014 }
1015 $extractedRanges[] = str_replace('$', '', $explodes[1]); // C7:J66
1016 }
1017 }
1018 if ($docSheet = $this->_phpExcel->getSheetByName($sheetName)) {
1019 $docSheet->getPageSetup()->setPrintArea(implode(',', $extractedRanges)); // C7:J66,A1:IV2
1020 }
1021 break;
1022
1023 case pack('C', 0x07):
1024 // print titles (repeating rows)
1025 // Assuming BIFF8, there are 3 cases
1026 // 1. repeating rows
1027 // formula looks like this: Sheet!$A$1:$IV$2
1028 // rows 1-2 repeat
1029 // 2. repeating columns
1030 // formula looks like this: Sheet!$A$1:$B$65536
1031 // columns A-B repeat
1032 // 3. both repeating rows and repeating columns
1033 // formula looks like this: Sheet!$A$1:$B$65536,Sheet!$A$1:$IV$2
1034
1035 $ranges = explode(',', $definedName['formula']); // FIXME: what if sheetname contains comma?
1036
1037 foreach ($ranges as $range) {
1038 // $range should look like this one of these
1039 // Sheet!$A$1:$B$65536
1040 // Sheet!$A$1:$IV$2
1041
1042 $explodes = explode('!', $range);
1043
1044 if (count($explodes) == 2) {
1045 if ($docSheet = $this->_phpExcel->getSheetByName($explodes[0])) {
1046
1047 $extractedRange = $explodes[1];
1048 $extractedRange = str_replace('$', '', $extractedRange);
1049
1050 $coordinateStrings = explode(':', $extractedRange);
1051 if (count($coordinateStrings) == 2) {
1052 list($firstColumn, $firstRow) = PHPExcel_Cell::coordinateFromString($coordinateStrings[0]);
1053 list($lastColumn, $lastRow) = PHPExcel_Cell::coordinateFromString($coordinateStrings[1]);
1054
1055 if ($firstColumn == 'A' and $lastColumn == 'IV') {
1056 // then we have repeating rows
1057 $docSheet->getPageSetup()->setRowsToRepeatAtTop(array($firstRow, $lastRow));
1058 } elseif ($firstRow == 1 and $lastRow == 65536) {
1059 // then we have repeating columns
1060 $docSheet->getPageSetup()->setColumnsToRepeatAtLeft(array($firstColumn, $lastColumn));
1061 }
1062 }
1063 }
1064 }
1065 }
1066 break;
1067
1068 }
1069 } else {
1070 // Extract range
1071 $explodes = explode('!', $definedName['formula']);
1072
1073 if (count($explodes) == 2) {
1074 if (($docSheet = $this->_phpExcel->getSheetByName($explodes[0])) ||
1075 ($docSheet = $this->_phpExcel->getSheetByName(trim($explodes[0],"'")))) {
1076 $extractedRange = $explodes[1];
1077 $extractedRange = str_replace('$', '', $extractedRange);
1078
1079 $localOnly = ($definedName['scope'] == 0) ? false : true;
1080
1081 $scope = ($definedName['scope'] == 0) ?
1082 null : $this->_phpExcel->getSheetByName($this->_sheets[$definedName['scope'] - 1]['name']);
1083
1084 $this->_phpExcel->addNamedRange( new PHPExcel_NamedRange((string)$definedName['name'], $docSheet, $extractedRange, $localOnly, $scope) );
1085 }
1086 } else {
1087 // Named Value
1088 // TODO Provide support for named values
1089 }
1090 }
1091 }
1092 $this->_data = null;
1093
1094 return $this->_phpExcel;
1095 }
1096
1106 private function _readRecordData($data, $pos, $len)
1107 {
1108 $data = substr($data, $pos, $len);
1109
1110 // File not encrypted, or record before encryption start point
1111 if ($this->_encryption == self::MS_BIFF_CRYPTO_NONE || $pos < $this->_encryptionStartPos) {
1112 return $data;
1113 }
1114
1115 $recordData = '';
1116 if ($this->_encryption == self::MS_BIFF_CRYPTO_RC4) {
1117
1118 $oldBlock = floor($this->_rc4Pos / self::REKEY_BLOCK);
1119 $block = floor($pos / self::REKEY_BLOCK);
1120 $endBlock = floor(($pos + $len) / self::REKEY_BLOCK);
1121
1122 // Spin an RC4 decryptor to the right spot. If we have a decryptor sitting
1123 // at a point earlier in the current block, re-use it as we can save some time.
1124 if ($block != $oldBlock || $pos < $this->_rc4Pos || !$this->_rc4Key) {
1125 $this->_rc4Key = $this->_makeKey($block, $this->_md5Ctxt);
1126 $step = $pos % self::REKEY_BLOCK;
1127 } else {
1128 $step = $pos - $this->_rc4Pos;
1129 }
1130 $this->_rc4Key->RC4(str_repeat("\0", $step));
1131
1132 // Decrypt record data (re-keying at the end of every block)
1133 while ($block != $endBlock) {
1134 $step = self::REKEY_BLOCK - ($pos % self::REKEY_BLOCK);
1135 $recordData .= $this->_rc4Key->RC4(substr($data, 0, $step));
1136 $data = substr($data, $step);
1137 $pos += $step;
1138 $len -= $step;
1139 $block++;
1140 $this->_rc4Key = $this->_makeKey($block, $this->_md5Ctxt);
1141 }
1142 $recordData .= $this->_rc4Key->RC4(substr($data, 0, $len));
1143
1144 // Keep track of the position of this decryptor.
1145 // We'll try and re-use it later if we can to speed things up
1146 $this->_rc4Pos = $pos + $len;
1147
1148 } elseif ($this->_encryption == self::MS_BIFF_CRYPTO_XOR) {
1149 throw new PHPExcel_Reader_Exception('XOr encryption not supported');
1150 }
1151 return $recordData;
1152 }
1153
1159 private function _loadOLE($pFilename)
1160 {
1161 // OLE reader
1162 $ole = new PHPExcel_Shared_OLERead();
1163
1164 // get excel data,
1165 $res = $ole->read($pFilename);
1166 // Get workbook data: workbook stream + sheet streams
1167 $this->_data = $ole->getStream($ole->wrkbook);
1168
1169 // Get summary information data
1170 $this->_summaryInformation = $ole->getStream($ole->summaryInformation);
1171
1172 // Get additional document summary information data
1173 $this->_documentSummaryInformation = $ole->getStream($ole->documentSummaryInformation);
1174
1175 // Get user-defined property data
1176// $this->_userDefinedProperties = $ole->getUserDefinedProperties();
1177 }
1178
1179
1183 private function _readSummaryInformation()
1184 {
1185 if (!isset($this->_summaryInformation)) {
1186 return;
1187 }
1188
1189 // offset: 0; size: 2; must be 0xFE 0xFF (UTF-16 LE byte order mark)
1190 // offset: 2; size: 2;
1191 // offset: 4; size: 2; OS version
1192 // offset: 6; size: 2; OS indicator
1193 // offset: 8; size: 16
1194 // offset: 24; size: 4; section count
1195 $secCount = self::_GetInt4d($this->_summaryInformation, 24);
1196
1197 // 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
1198 // offset: 44; size: 4
1199 $secOffset = self::_GetInt4d($this->_summaryInformation, 44);
1200
1201 // section header
1202 // offset: $secOffset; size: 4; section length
1203 $secLength = self::_GetInt4d($this->_summaryInformation, $secOffset);
1204
1205 // offset: $secOffset+4; size: 4; property count
1206 $countProperties = self::_GetInt4d($this->_summaryInformation, $secOffset+4);
1207
1208 // initialize code page (used to resolve string values)
1209 $codePage = 'CP1252';
1210
1211 // offset: ($secOffset+8); size: var
1212 // loop through property decarations and properties
1213 for ($i = 0; $i < $countProperties; ++$i) {
1214
1215 // offset: ($secOffset+8) + (8 * $i); size: 4; property ID
1216 $id = self::_GetInt4d($this->_summaryInformation, ($secOffset+8) + (8 * $i));
1217
1218 // Use value of property id as appropriate
1219 // offset: ($secOffset+12) + (8 * $i); size: 4; offset from beginning of section (48)
1220 $offset = self::_GetInt4d($this->_summaryInformation, ($secOffset+12) + (8 * $i));
1221
1222 $type = self::_GetInt4d($this->_summaryInformation, $secOffset + $offset);
1223
1224 // initialize property value
1225 $value = null;
1226
1227 // extract property value based on property type
1228 switch ($type) {
1229 case 0x02: // 2 byte signed integer
1230 $value = self::_GetInt2d($this->_summaryInformation, $secOffset + 4 + $offset);
1231 break;
1232
1233 case 0x03: // 4 byte signed integer
1234 $value = self::_GetInt4d($this->_summaryInformation, $secOffset + 4 + $offset);
1235 break;
1236
1237 case 0x13: // 4 byte unsigned integer
1238 // not needed yet, fix later if necessary
1239 break;
1240
1241 case 0x1E: // null-terminated string prepended by dword string length
1242 $byteLength = self::_GetInt4d($this->_summaryInformation, $secOffset + 4 + $offset);
1243 $value = substr($this->_summaryInformation, $secOffset + 8 + $offset, $byteLength);
1244 $value = PHPExcel_Shared_String::ConvertEncoding($value, 'UTF-8', $codePage);
1245 $value = rtrim($value);
1246 break;
1247
1248 case 0x40: // Filetime (64-bit value representing the number of 100-nanosecond intervals since January 1, 1601)
1249 // PHP-time
1250 $value = PHPExcel_Shared_OLE::OLE2LocalDate(substr($this->_summaryInformation, $secOffset + 4 + $offset, 8));
1251 break;
1252
1253 case 0x47: // Clipboard format
1254 // not needed yet, fix later if necessary
1255 break;
1256 }
1257
1258 switch ($id) {
1259 case 0x01: // Code Page
1260 $codePage = PHPExcel_Shared_CodePage::NumberToName($value);
1261 break;
1262
1263 case 0x02: // Title
1264 $this->_phpExcel->getProperties()->setTitle($value);
1265 break;
1266
1267 case 0x03: // Subject
1268 $this->_phpExcel->getProperties()->setSubject($value);
1269 break;
1270
1271 case 0x04: // Author (Creator)
1272 $this->_phpExcel->getProperties()->setCreator($value);
1273 break;
1274
1275 case 0x05: // Keywords
1276 $this->_phpExcel->getProperties()->setKeywords($value);
1277 break;
1278
1279 case 0x06: // Comments (Description)
1280 $this->_phpExcel->getProperties()->setDescription($value);
1281 break;
1282
1283 case 0x07: // Template
1284 // Not supported by PHPExcel
1285 break;
1286
1287 case 0x08: // Last Saved By (LastModifiedBy)
1288 $this->_phpExcel->getProperties()->setLastModifiedBy($value);
1289 break;
1290
1291 case 0x09: // Revision
1292 // Not supported by PHPExcel
1293 break;
1294
1295 case 0x0A: // Total Editing Time
1296 // Not supported by PHPExcel
1297 break;
1298
1299 case 0x0B: // Last Printed
1300 // Not supported by PHPExcel
1301 break;
1302
1303 case 0x0C: // Created Date/Time
1304 $this->_phpExcel->getProperties()->setCreated($value);
1305 break;
1306
1307 case 0x0D: // Modified Date/Time
1308 $this->_phpExcel->getProperties()->setModified($value);
1309 break;
1310
1311 case 0x0E: // Number of Pages
1312 // Not supported by PHPExcel
1313 break;
1314
1315 case 0x0F: // Number of Words
1316 // Not supported by PHPExcel
1317 break;
1318
1319 case 0x10: // Number of Characters
1320 // Not supported by PHPExcel
1321 break;
1322
1323 case 0x11: // Thumbnail
1324 // Not supported by PHPExcel
1325 break;
1326
1327 case 0x12: // Name of creating application
1328 // Not supported by PHPExcel
1329 break;
1330
1331 case 0x13: // Security
1332 // Not supported by PHPExcel
1333 break;
1334
1335 }
1336 }
1337 }
1338
1339
1344 {
1345 if (!isset($this->_documentSummaryInformation)) {
1346 return;
1347 }
1348
1349 // offset: 0; size: 2; must be 0xFE 0xFF (UTF-16 LE byte order mark)
1350 // offset: 2; size: 2;
1351 // offset: 4; size: 2; OS version
1352 // offset: 6; size: 2; OS indicator
1353 // offset: 8; size: 16
1354 // offset: 24; size: 4; section count
1355 $secCount = self::_GetInt4d($this->_documentSummaryInformation, 24);
1356// echo '$secCount = ',$secCount,'<br />';
1357
1358 // 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
1359 // offset: 44; size: 4; first section offset
1360 $secOffset = self::_GetInt4d($this->_documentSummaryInformation, 44);
1361// echo '$secOffset = ',$secOffset,'<br />';
1362
1363 // section header
1364 // offset: $secOffset; size: 4; section length
1365 $secLength = self::_GetInt4d($this->_documentSummaryInformation, $secOffset);
1366// echo '$secLength = ',$secLength,'<br />';
1367
1368 // offset: $secOffset+4; size: 4; property count
1369 $countProperties = self::_GetInt4d($this->_documentSummaryInformation, $secOffset+4);
1370// echo '$countProperties = ',$countProperties,'<br />';
1371
1372 // initialize code page (used to resolve string values)
1373 $codePage = 'CP1252';
1374
1375 // offset: ($secOffset+8); size: var
1376 // loop through property decarations and properties
1377 for ($i = 0; $i < $countProperties; ++$i) {
1378// echo 'Property ',$i,'<br />';
1379 // offset: ($secOffset+8) + (8 * $i); size: 4; property ID
1380 $id = self::_GetInt4d($this->_documentSummaryInformation, ($secOffset+8) + (8 * $i));
1381// echo 'ID is ',$id,'<br />';
1382
1383 // Use value of property id as appropriate
1384 // offset: 60 + 8 * $i; size: 4; offset from beginning of section (48)
1385 $offset = self::_GetInt4d($this->_documentSummaryInformation, ($secOffset+12) + (8 * $i));
1386
1387 $type = self::_GetInt4d($this->_documentSummaryInformation, $secOffset + $offset);
1388// echo 'Type is ',$type,', ';
1389
1390 // initialize property value
1391 $value = null;
1392
1393 // extract property value based on property type
1394 switch ($type) {
1395 case 0x02: // 2 byte signed integer
1396 $value = self::_GetInt2d($this->_documentSummaryInformation, $secOffset + 4 + $offset);
1397 break;
1398
1399 case 0x03: // 4 byte signed integer
1400 $value = self::_GetInt4d($this->_documentSummaryInformation, $secOffset + 4 + $offset);
1401 break;
1402
1403 case 0x0B: // Boolean
1404 $value = self::_GetInt2d($this->_documentSummaryInformation, $secOffset + 4 + $offset);
1405 $value = ($value == 0 ? false : true);
1406 break;
1407
1408 case 0x13: // 4 byte unsigned integer
1409 // not needed yet, fix later if necessary
1410 break;
1411
1412 case 0x1E: // null-terminated string prepended by dword string length
1413 $byteLength = self::_GetInt4d($this->_documentSummaryInformation, $secOffset + 4 + $offset);
1414 $value = substr($this->_documentSummaryInformation, $secOffset + 8 + $offset, $byteLength);
1415 $value = PHPExcel_Shared_String::ConvertEncoding($value, 'UTF-8', $codePage);
1416 $value = rtrim($value);
1417 break;
1418
1419 case 0x40: // Filetime (64-bit value representing the number of 100-nanosecond intervals since January 1, 1601)
1420 // PHP-Time
1421 $value = PHPExcel_Shared_OLE::OLE2LocalDate(substr($this->_documentSummaryInformation, $secOffset + 4 + $offset, 8));
1422 break;
1423
1424 case 0x47: // Clipboard format
1425 // not needed yet, fix later if necessary
1426 break;
1427 }
1428
1429 switch ($id) {
1430 case 0x01: // Code Page
1431 $codePage = PHPExcel_Shared_CodePage::NumberToName($value);
1432 break;
1433
1434 case 0x02: // Category
1435 $this->_phpExcel->getProperties()->setCategory($value);
1436 break;
1437
1438 case 0x03: // Presentation Target
1439 // Not supported by PHPExcel
1440 break;
1441
1442 case 0x04: // Bytes
1443 // Not supported by PHPExcel
1444 break;
1445
1446 case 0x05: // Lines
1447 // Not supported by PHPExcel
1448 break;
1449
1450 case 0x06: // Paragraphs
1451 // Not supported by PHPExcel
1452 break;
1453
1454 case 0x07: // Slides
1455 // Not supported by PHPExcel
1456 break;
1457
1458 case 0x08: // Notes
1459 // Not supported by PHPExcel
1460 break;
1461
1462 case 0x09: // Hidden Slides
1463 // Not supported by PHPExcel
1464 break;
1465
1466 case 0x0A: // MM Clips
1467 // Not supported by PHPExcel
1468 break;
1469
1470 case 0x0B: // Scale Crop
1471 // Not supported by PHPExcel
1472 break;
1473
1474 case 0x0C: // Heading Pairs
1475 // Not supported by PHPExcel
1476 break;
1477
1478 case 0x0D: // Titles of Parts
1479 // Not supported by PHPExcel
1480 break;
1481
1482 case 0x0E: // Manager
1483 $this->_phpExcel->getProperties()->setManager($value);
1484 break;
1485
1486 case 0x0F: // Company
1487 $this->_phpExcel->getProperties()->setCompany($value);
1488 break;
1489
1490 case 0x10: // Links up-to-date
1491 // Not supported by PHPExcel
1492 break;
1493
1494 }
1495 }
1496 }
1497
1498
1502 private function _readDefault()
1503 {
1504 $length = self::_GetInt2d($this->_data, $this->_pos + 2);
1505// $recordData = $this->_readRecordData($this->_data, $this->_pos + 4, $length);
1506
1507 // move stream pointer to next record
1508 $this->_pos += 4 + $length;
1509 }
1510
1511
1516 private function _readNote()
1517 {
1518// echo '<b>Read Cell Annotation</b><br />';
1519 $length = self::_GetInt2d($this->_data, $this->_pos + 2);
1520 $recordData = $this->_readRecordData($this->_data, $this->_pos + 4, $length);
1521
1522 // move stream pointer to next record
1523 $this->_pos += 4 + $length;
1524
1525 if ($this->_readDataOnly) {
1526 return;
1527 }
1528
1529 $cellAddress = $this->_readBIFF8CellAddress(substr($recordData, 0, 4));
1530 if ($this->_version == self::XLS_BIFF8) {
1531 $noteObjID = self::_GetInt2d($recordData, 6);
1532 $noteAuthor = self::_readUnicodeStringLong(substr($recordData, 8));
1533 $noteAuthor = $noteAuthor['value'];
1534// echo 'Note Address=',$cellAddress,'<br />';
1535// echo 'Note Object ID=',$noteObjID,'<br />';
1536// echo 'Note Author=',$noteAuthor,'<hr />';
1537//
1538 $this->_cellNotes[$noteObjID] = array('cellRef' => $cellAddress,
1539 'objectID' => $noteObjID,
1540 'author' => $noteAuthor
1541 );
1542 } else {
1543 $extension = false;
1544 if ($cellAddress == '$B$65536') {
1545 // If the address row is -1 and the column is 0, (which translates as $B$65536) then this is a continuation
1546 // note from the previous cell annotation. We're not yet handling this, so annotations longer than the
1547 // max 2048 bytes will probably throw a wobbly.
1548 $row = self::_GetInt2d($recordData, 0);
1549 $extension = true;
1550 $cellAddress = array_pop(array_keys($this->_phpSheet->getComments()));
1551 }
1552// echo 'Note Address=',$cellAddress,'<br />';
1553
1554 $cellAddress = str_replace('$','',$cellAddress);
1555 $noteLength = self::_GetInt2d($recordData, 4);
1556 $noteText = trim(substr($recordData, 6));
1557// echo 'Note Length=',$noteLength,'<br />';
1558// echo 'Note Text=',$noteText,'<br />';
1559
1560 if ($extension) {
1561 // Concatenate this extension with the currently set comment for the cell
1562 $comment = $this->_phpSheet->getComment( $cellAddress );
1563 $commentText = $comment->getText()->getPlainText();
1564 $comment->setText($this->_parseRichText($commentText.$noteText) );
1565 } else {
1566 // Set comment for the cell
1567 $this->_phpSheet->getComment( $cellAddress )
1568// ->setAuthor( $author )
1569 ->setText($this->_parseRichText($noteText) );
1570 }
1571 }
1572
1573 }
1574
1575
1579 private function _readTextObject()
1580 {
1581 $length = self::_GetInt2d($this->_data, $this->_pos + 2);
1582 $recordData = $this->_readRecordData($this->_data, $this->_pos + 4, $length);
1583
1584 // move stream pointer to next record
1585 $this->_pos += 4 + $length;
1586
1587 if ($this->_readDataOnly) {
1588 return;
1589 }
1590
1591 // recordData consists of an array of subrecords looking like this:
1592 // grbit: 2 bytes; Option Flags
1593 // rot: 2 bytes; rotation
1594 // cchText: 2 bytes; length of the text (in the first continue record)
1595 // cbRuns: 2 bytes; length of the formatting (in the second continue record)
1596 // followed by the continuation records containing the actual text and formatting
1597 $grbitOpts = self::_GetInt2d($recordData, 0);
1598 $rot = self::_GetInt2d($recordData, 2);
1599 $cchText = self::_GetInt2d($recordData, 10);
1600 $cbRuns = self::_GetInt2d($recordData, 12);
1601 $text = $this->_getSplicedRecordData();
1602
1603 $this->_textObjects[$this->textObjRef] = array(
1604 'text' => substr($text["recordData"],$text["spliceOffsets"][0]+1,$cchText),
1605 'format' => substr($text["recordData"],$text["spliceOffsets"][1],$cbRuns),
1606 'alignment' => $grbitOpts,
1607 'rotation' => $rot
1608 );
1609
1610// echo '<b>_readTextObject()</b><br />';
1611// var_dump($this->_textObjects[$this->textObjRef]);
1612// echo '<br />';
1613 }
1614
1615
1619 private function _readBof()
1620 {
1621 $length = self::_GetInt2d($this->_data, $this->_pos + 2);
1622 $recordData = substr($this->_data, $this->_pos + 4, $length);
1623
1624 // move stream pointer to next record
1625 $this->_pos += 4 + $length;
1626
1627 // offset: 2; size: 2; type of the following data
1628 $substreamType = self::_GetInt2d($recordData, 2);
1629
1630 switch ($substreamType) {
1632 $version = self::_GetInt2d($recordData, 0);
1633 if (($version != self::XLS_BIFF8) && ($version != self::XLS_BIFF7)) {
1634 throw new PHPExcel_Reader_Exception('Cannot read this Excel file. Version is too old.');
1635 }
1636 $this->_version = $version;
1637 break;
1638
1640 // do not use this version information for anything
1641 // it is unreliable (OpenOffice doc, 5.8), use only version information from the global stream
1642 break;
1643
1644 default:
1645 // substream, e.g. chart
1646 // just skip the entire substream
1647 do {
1648 $code = self::_GetInt2d($this->_data, $this->_pos);
1649 $this->_readDefault();
1650 } while ($code != self::XLS_Type_EOF && $this->_pos < $this->_dataSize);
1651 break;
1652 }
1653 }
1654
1655
1671 private function _readFilepass()
1672 {
1673 $length = self::_GetInt2d($this->_data, $this->_pos + 2);
1674
1675 if ($length != 54) {
1676 throw new PHPExcel_Reader_Exception('Unexpected file pass record length');
1677 }
1678
1679 $recordData = $this->_readRecordData($this->_data, $this->_pos + 4, $length);
1680
1681 // move stream pointer to next record
1682 $this->_pos += 4 + $length;
1683
1684 if (!$this->_verifyPassword(
1685 'VelvetSweatshop',
1686 substr($recordData, 6, 16),
1687 substr($recordData, 22, 16),
1688 substr($recordData, 38, 16),
1689 $this->_md5Ctxt
1690 )) {
1691 throw new PHPExcel_Reader_Exception('Decryption password incorrect');
1692 }
1693
1694 $this->_encryption = self::MS_BIFF_CRYPTO_RC4;
1695
1696 // Decryption required from the record after next onwards
1697 $this->_encryptionStartPos = $this->_pos + self::_GetInt2d($this->_data, $this->_pos + 2);
1698 }
1699
1708 private function _makeKey($block, $valContext)
1709 {
1710 $pwarray = str_repeat("\0", 64);
1711
1712 for ($i = 0; $i < 5; $i++) {
1713 $pwarray[$i] = $valContext[$i];
1714 }
1715
1716 $pwarray[5] = chr($block & 0xff);
1717 $pwarray[6] = chr(($block >> 8) & 0xff);
1718 $pwarray[7] = chr(($block >> 16) & 0xff);
1719 $pwarray[8] = chr(($block >> 24) & 0xff);
1720
1721 $pwarray[9] = "\x80";
1722 $pwarray[56] = "\x48";
1723
1724 $md5 = new PHPExcel_Reader_Excel5_MD5();
1725 $md5->add($pwarray);
1726
1727 $s = $md5->getContext();
1728 return new PHPExcel_Reader_Excel5_RC4($s);
1729 }
1730
1742 private function _verifyPassword($password, $docid, $salt_data, $hashedsalt_data, &$valContext)
1743 {
1744 $pwarray = str_repeat("\0", 64);
1745
1746 for ($i = 0; $i < strlen($password); $i++) {
1747 $o = ord(substr($password, $i, 1));
1748 $pwarray[2 * $i] = chr($o & 0xff);
1749 $pwarray[2 * $i + 1] = chr(($o >> 8) & 0xff);
1750 }
1751 $pwarray[2 * $i] = chr(0x80);
1752 $pwarray[56] = chr(($i << 4) & 0xff);
1753
1754 $md5 = new PHPExcel_Reader_Excel5_MD5();
1755 $md5->add($pwarray);
1756
1757 $mdContext1 = $md5->getContext();
1758
1759 $offset = 0;
1760 $keyoffset = 0;
1761 $tocopy = 5;
1762
1763 $md5->reset();
1764
1765 while ($offset != 16) {
1766 if ((64 - $offset) < 5) {
1767 $tocopy = 64 - $offset;
1768 }
1769
1770 for ($i = 0; $i <= $tocopy; $i++) {
1771 $pwarray[$offset + $i] = $mdContext1[$keyoffset + $i];
1772 }
1773
1774 $offset += $tocopy;
1775
1776 if ($offset == 64) {
1777 $md5->add($pwarray);
1778 $keyoffset = $tocopy;
1779 $tocopy = 5 - $tocopy;
1780 $offset = 0;
1781 continue;
1782 }
1783
1784 $keyoffset = 0;
1785 $tocopy = 5;
1786 for ($i = 0; $i < 16; $i++) {
1787 $pwarray[$offset + $i] = $docid[$i];
1788 }
1789 $offset += 16;
1790 }
1791
1792 $pwarray[16] = "\x80";
1793 for ($i = 0; $i < 47; $i++) {
1794 $pwarray[17 + $i] = "\0";
1795 }
1796 $pwarray[56] = "\x80";
1797 $pwarray[57] = "\x0a";
1798
1799 $md5->add($pwarray);
1800 $valContext = $md5->getContext();
1801
1802 $key = $this->_makeKey(0, $valContext);
1803
1804 $salt = $key->RC4($salt_data);
1805 $hashedsalt = $key->RC4($hashedsalt_data);
1806
1807 $salt .= "\x80" . str_repeat("\0", 47);
1808 $salt[56] = "\x80";
1809
1810 $md5->reset();
1811 $md5->add($salt);
1812 $mdContext2 = $md5->getContext();
1813
1814 return $mdContext2 == $hashedsalt;
1815 }
1816
1826 private function _readCodepage()
1827 {
1828 $length = self::_GetInt2d($this->_data, $this->_pos + 2);
1829 $recordData = $this->_readRecordData($this->_data, $this->_pos + 4, $length);
1830
1831 // move stream pointer to next record
1832 $this->_pos += 4 + $length;
1833
1834 // offset: 0; size: 2; code page identifier
1835 $codepage = self::_GetInt2d($recordData, 0);
1836
1837 $this->_codepage = PHPExcel_Shared_CodePage::NumberToName($codepage);
1838 }
1839
1840
1853 private function _readDateMode()
1854 {
1855 $length = self::_GetInt2d($this->_data, $this->_pos + 2);
1856 $recordData = $this->_readRecordData($this->_data, $this->_pos + 4, $length);
1857
1858 // move stream pointer to next record
1859 $this->_pos += 4 + $length;
1860
1861 // offset: 0; size: 2; 0 = base 1900, 1 = base 1904
1863 if (ord($recordData{0}) == 1) {
1865 }
1866 }
1867
1868
1872 private function _readFont()
1873 {
1874 $length = self::_GetInt2d($this->_data, $this->_pos + 2);
1875 $recordData = $this->_readRecordData($this->_data, $this->_pos + 4, $length);
1876
1877 // move stream pointer to next record
1878 $this->_pos += 4 + $length;
1879
1880 if (!$this->_readDataOnly) {
1881 $objFont = new PHPExcel_Style_Font();
1882
1883 // offset: 0; size: 2; height of the font (in twips = 1/20 of a point)
1884 $size = self::_GetInt2d($recordData, 0);
1885 $objFont->setSize($size / 20);
1886
1887 // offset: 2; size: 2; option flags
1888 // bit: 0; mask 0x0001; bold (redundant in BIFF5-BIFF8)
1889 // bit: 1; mask 0x0002; italic
1890 $isItalic = (0x0002 & self::_GetInt2d($recordData, 2)) >> 1;
1891 if ($isItalic) $objFont->setItalic(true);
1892
1893 // bit: 2; mask 0x0004; underlined (redundant in BIFF5-BIFF8)
1894 // bit: 3; mask 0x0008; strike
1895 $isStrike = (0x0008 & self::_GetInt2d($recordData, 2)) >> 3;
1896 if ($isStrike) $objFont->setStrikethrough(true);
1897
1898 // offset: 4; size: 2; colour index
1899 $colorIndex = self::_GetInt2d($recordData, 4);
1900 $objFont->colorIndex = $colorIndex;
1901
1902 // offset: 6; size: 2; font weight
1903 $weight = self::_GetInt2d($recordData, 6);
1904 switch ($weight) {
1905 case 0x02BC:
1906 $objFont->setBold(true);
1907 break;
1908 }
1909
1910 // offset: 8; size: 2; escapement type
1911 $escapement = self::_GetInt2d($recordData, 8);
1912 switch ($escapement) {
1913 case 0x0001:
1914 $objFont->setSuperScript(true);
1915 break;
1916 case 0x0002:
1917 $objFont->setSubScript(true);
1918 break;
1919 }
1920
1921 // offset: 10; size: 1; underline type
1922 $underlineType = ord($recordData{10});
1923 switch ($underlineType) {
1924 case 0x00:
1925 break; // no underline
1926 case 0x01:
1927 $objFont->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
1928 break;
1929 case 0x02:
1930 $objFont->setUnderline(PHPExcel_Style_Font::UNDERLINE_DOUBLE);
1931 break;
1932 case 0x21:
1934 break;
1935 case 0x22:
1937 break;
1938 }
1939
1940 // offset: 11; size: 1; font family
1941 // offset: 12; size: 1; character set
1942 // offset: 13; size: 1; not used
1943 // offset: 14; size: var; font name
1944 if ($this->_version == self::XLS_BIFF8) {
1945 $string = self::_readUnicodeStringShort(substr($recordData, 14));
1946 } else {
1947 $string = $this->_readByteStringShort(substr($recordData, 14));
1948 }
1949 $objFont->setName($string['value']);
1950
1951 $this->_objFonts[] = $objFont;
1952 }
1953 }
1954
1955
1970 private function _readFormat()
1971 {
1972 $length = self::_GetInt2d($this->_data, $this->_pos + 2);
1973 $recordData = $this->_readRecordData($this->_data, $this->_pos + 4, $length);
1974
1975 // move stream pointer to next record
1976 $this->_pos += 4 + $length;
1977
1978 if (!$this->_readDataOnly) {
1979 $indexCode = self::_GetInt2d($recordData, 0);
1980
1981 if ($this->_version == self::XLS_BIFF8) {
1982 $string = self::_readUnicodeStringLong(substr($recordData, 2));
1983 } else {
1984 // BIFF7
1985 $string = $this->_readByteStringShort(substr($recordData, 2));
1986 }
1987
1988 $formatString = $string['value'];
1989 $this->_formats[$indexCode] = $formatString;
1990 }
1991 }
1992
1993
2008 private function _readXf()
2009 {
2010 $length = self::_GetInt2d($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 $objStyle = new PHPExcel_Style();
2017
2018 if (!$this->_readDataOnly) {
2019 // offset: 0; size: 2; Index to FONT record
2020 if (self::_GetInt2d($recordData, 0) < 4) {
2021 $fontIndex = self::_GetInt2d($recordData, 0);
2022 } else {
2023 // this has to do with that index 4 is omitted in all BIFF versions for some strange reason
2024 // check the OpenOffice documentation of the FONT record
2025 $fontIndex = self::_GetInt2d($recordData, 0) - 1;
2026 }
2027 $objStyle->setFont($this->_objFonts[$fontIndex]);
2028
2029 // offset: 2; size: 2; Index to FORMAT record
2030 $numberFormatIndex = self::_GetInt2d($recordData, 2);
2031 if (isset($this->_formats[$numberFormatIndex])) {
2032 // then we have user-defined format code
2033 $numberformat = array('code' => $this->_formats[$numberFormatIndex]);
2034 } elseif (($code = PHPExcel_Style_NumberFormat::builtInFormatCode($numberFormatIndex)) !== '') {
2035 // then we have built-in format code
2036 $numberformat = array('code' => $code);
2037 } else {
2038 // we set the general format code
2039 $numberformat = array('code' => 'General');
2040 }
2041 $objStyle->getNumberFormat()->setFormatCode($numberformat['code']);
2042
2043 // offset: 4; size: 2; XF type, cell protection, and parent style XF
2044 // bit 2-0; mask 0x0007; XF_TYPE_PROT
2045 $xfTypeProt = self::_GetInt2d($recordData, 4);
2046 // bit 0; mask 0x01; 1 = cell is locked
2047 $isLocked = (0x01 & $xfTypeProt) >> 0;
2048 $objStyle->getProtection()->setLocked($isLocked ?
2050
2051 // bit 1; mask 0x02; 1 = Formula is hidden
2052 $isHidden = (0x02 & $xfTypeProt) >> 1;
2053 $objStyle->getProtection()->setHidden($isHidden ?
2055
2056 // bit 2; mask 0x04; 0 = Cell XF, 1 = Cell Style XF
2057 $isCellStyleXf = (0x04 & $xfTypeProt) >> 2;
2058
2059 // offset: 6; size: 1; Alignment and text break
2060 // bit 2-0, mask 0x07; horizontal alignment
2061 $horAlign = (0x07 & ord($recordData{6})) >> 0;
2062 switch ($horAlign) {
2063 case 0:
2064 $objStyle->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_GENERAL);
2065 break;
2066 case 1:
2067 $objStyle->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
2068 break;
2069 case 2:
2070 $objStyle->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
2071 break;
2072 case 3:
2073 $objStyle->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
2074 break;
2075 case 4:
2076 $objStyle->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_FILL);
2077 break;
2078 case 5:
2079 $objStyle->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY);
2080 break;
2081 case 6:
2082 $objStyle->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER_CONTINUOUS);
2083 break;
2084 }
2085 // bit 3, mask 0x08; wrap text
2086 $wrapText = (0x08 & ord($recordData{6})) >> 3;
2087 switch ($wrapText) {
2088 case 0:
2089 $objStyle->getAlignment()->setWrapText(false);
2090 break;
2091 case 1:
2092 $objStyle->getAlignment()->setWrapText(true);
2093 break;
2094 }
2095 // bit 6-4, mask 0x70; vertical alignment
2096 $vertAlign = (0x70 & ord($recordData{6})) >> 4;
2097 switch ($vertAlign) {
2098 case 0:
2099 $objStyle->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
2100 break;
2101 case 1:
2102 $objStyle->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
2103 break;
2104 case 2:
2105 $objStyle->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_BOTTOM);
2106 break;
2107 case 3:
2108 $objStyle->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_JUSTIFY);
2109 break;
2110 }
2111
2112 if ($this->_version == self::XLS_BIFF8) {
2113 // offset: 7; size: 1; XF_ROTATION: Text rotation angle
2114 $angle = ord($recordData{7});
2115 $rotation = 0;
2116 if ($angle <= 90) {
2117 $rotation = $angle;
2118 } else if ($angle <= 180) {
2119 $rotation = 90 - $angle;
2120 } else if ($angle == 255) {
2121 $rotation = -165;
2122 }
2123 $objStyle->getAlignment()->setTextRotation($rotation);
2124
2125 // offset: 8; size: 1; Indentation, shrink to cell size, and text direction
2126 // bit: 3-0; mask: 0x0F; indent level
2127 $indent = (0x0F & ord($recordData{8})) >> 0;
2128 $objStyle->getAlignment()->setIndent($indent);
2129
2130 // bit: 4; mask: 0x10; 1 = shrink content to fit into cell
2131 $shrinkToFit = (0x10 & ord($recordData{8})) >> 4;
2132 switch ($shrinkToFit) {
2133 case 0:
2134 $objStyle->getAlignment()->setShrinkToFit(false);
2135 break;
2136 case 1:
2137 $objStyle->getAlignment()->setShrinkToFit(true);
2138 break;
2139 }
2140
2141 // offset: 9; size: 1; Flags used for attribute groups
2142
2143 // offset: 10; size: 4; Cell border lines and background area
2144 // bit: 3-0; mask: 0x0000000F; left style
2145 if ($bordersLeftStyle = self::_mapBorderStyle((0x0000000F & self::_GetInt4d($recordData, 10)) >> 0)) {
2146 $objStyle->getBorders()->getLeft()->setBorderStyle($bordersLeftStyle);
2147 }
2148 // bit: 7-4; mask: 0x000000F0; right style
2149 if ($bordersRightStyle = self::_mapBorderStyle((0x000000F0 & self::_GetInt4d($recordData, 10)) >> 4)) {
2150 $objStyle->getBorders()->getRight()->setBorderStyle($bordersRightStyle);
2151 }
2152 // bit: 11-8; mask: 0x00000F00; top style
2153 if ($bordersTopStyle = self::_mapBorderStyle((0x00000F00 & self::_GetInt4d($recordData, 10)) >> 8)) {
2154 $objStyle->getBorders()->getTop()->setBorderStyle($bordersTopStyle);
2155 }
2156 // bit: 15-12; mask: 0x0000F000; bottom style
2157 if ($bordersBottomStyle = self::_mapBorderStyle((0x0000F000 & self::_GetInt4d($recordData, 10)) >> 12)) {
2158 $objStyle->getBorders()->getBottom()->setBorderStyle($bordersBottomStyle);
2159 }
2160 // bit: 22-16; mask: 0x007F0000; left color
2161 $objStyle->getBorders()->getLeft()->colorIndex = (0x007F0000 & self::_GetInt4d($recordData, 10)) >> 16;
2162
2163 // bit: 29-23; mask: 0x3F800000; right color
2164 $objStyle->getBorders()->getRight()->colorIndex = (0x3F800000 & self::_GetInt4d($recordData, 10)) >> 23;
2165
2166 // bit: 30; mask: 0x40000000; 1 = diagonal line from top left to right bottom
2167 $diagonalDown = (0x40000000 & self::_GetInt4d($recordData, 10)) >> 30 ?
2168 true : false;
2169
2170 // bit: 31; mask: 0x80000000; 1 = diagonal line from bottom left to top right
2171 $diagonalUp = (0x80000000 & self::_GetInt4d($recordData, 10)) >> 31 ?
2172 true : false;
2173
2174 if ($diagonalUp == false && $diagonalDown == false) {
2175 $objStyle->getBorders()->setDiagonalDirection(PHPExcel_Style_Borders::DIAGONAL_NONE);
2176 } elseif ($diagonalUp == true && $diagonalDown == false) {
2177 $objStyle->getBorders()->setDiagonalDirection(PHPExcel_Style_Borders::DIAGONAL_UP);
2178 } elseif ($diagonalUp == false && $diagonalDown == true) {
2179 $objStyle->getBorders()->setDiagonalDirection(PHPExcel_Style_Borders::DIAGONAL_DOWN);
2180 } elseif ($diagonalUp == true && $diagonalDown == true) {
2181 $objStyle->getBorders()->setDiagonalDirection(PHPExcel_Style_Borders::DIAGONAL_BOTH);
2182 }
2183
2184 // offset: 14; size: 4;
2185 // bit: 6-0; mask: 0x0000007F; top color
2186 $objStyle->getBorders()->getTop()->colorIndex = (0x0000007F & self::_GetInt4d($recordData, 14)) >> 0;
2187
2188 // bit: 13-7; mask: 0x00003F80; bottom color
2189 $objStyle->getBorders()->getBottom()->colorIndex = (0x00003F80 & self::_GetInt4d($recordData, 14)) >> 7;
2190
2191 // bit: 20-14; mask: 0x001FC000; diagonal color
2192 $objStyle->getBorders()->getDiagonal()->colorIndex = (0x001FC000 & self::_GetInt4d($recordData, 14)) >> 14;
2193
2194 // bit: 24-21; mask: 0x01E00000; diagonal style
2195 if ($bordersDiagonalStyle = self::_mapBorderStyle((0x01E00000 & self::_GetInt4d($recordData, 14)) >> 21)) {
2196 $objStyle->getBorders()->getDiagonal()->setBorderStyle($bordersDiagonalStyle);
2197 }
2198
2199 // bit: 31-26; mask: 0xFC000000 fill pattern
2200 if ($fillType = self::_mapFillPattern((0xFC000000 & self::_GetInt4d($recordData, 14)) >> 26)) {
2201 $objStyle->getFill()->setFillType($fillType);
2202 }
2203 // offset: 18; size: 2; pattern and background colour
2204 // bit: 6-0; mask: 0x007F; color index for pattern color
2205 $objStyle->getFill()->startcolorIndex = (0x007F & self::_GetInt2d($recordData, 18)) >> 0;
2206
2207 // bit: 13-7; mask: 0x3F80; color index for pattern background
2208 $objStyle->getFill()->endcolorIndex = (0x3F80 & self::_GetInt2d($recordData, 18)) >> 7;
2209 } else {
2210 // BIFF5
2211
2212 // offset: 7; size: 1; Text orientation and flags
2213 $orientationAndFlags = ord($recordData{7});
2214
2215 // bit: 1-0; mask: 0x03; XF_ORIENTATION: Text orientation
2216 $xfOrientation = (0x03 & $orientationAndFlags) >> 0;
2217 switch ($xfOrientation) {
2218 case 0:
2219 $objStyle->getAlignment()->setTextRotation(0);
2220 break;
2221 case 1:
2222 $objStyle->getAlignment()->setTextRotation(-165);
2223 break;
2224 case 2:
2225 $objStyle->getAlignment()->setTextRotation(90);
2226 break;
2227 case 3:
2228 $objStyle->getAlignment()->setTextRotation(-90);
2229 break;
2230 }
2231
2232 // offset: 8; size: 4; cell border lines and background area
2233 $borderAndBackground = self::_GetInt4d($recordData, 8);
2234
2235 // bit: 6-0; mask: 0x0000007F; color index for pattern color
2236 $objStyle->getFill()->startcolorIndex = (0x0000007F & $borderAndBackground) >> 0;
2237
2238 // bit: 13-7; mask: 0x00003F80; color index for pattern background
2239 $objStyle->getFill()->endcolorIndex = (0x00003F80 & $borderAndBackground) >> 7;
2240
2241 // bit: 21-16; mask: 0x003F0000; fill pattern
2242 $objStyle->getFill()->setFillType(self::_mapFillPattern((0x003F0000 & $borderAndBackground) >> 16));
2243
2244 // bit: 24-22; mask: 0x01C00000; bottom line style
2245 $objStyle->getBorders()->getBottom()->setBorderStyle(self::_mapBorderStyle((0x01C00000 & $borderAndBackground) >> 22));
2246
2247 // bit: 31-25; mask: 0xFE000000; bottom line color
2248 $objStyle->getBorders()->getBottom()->colorIndex = (0xFE000000 & $borderAndBackground) >> 25;
2249
2250 // offset: 12; size: 4; cell border lines
2251 $borderLines = self::_GetInt4d($recordData, 12);
2252
2253 // bit: 2-0; mask: 0x00000007; top line style
2254 $objStyle->getBorders()->getTop()->setBorderStyle(self::_mapBorderStyle((0x00000007 & $borderLines) >> 0));
2255
2256 // bit: 5-3; mask: 0x00000038; left line style
2257 $objStyle->getBorders()->getLeft()->setBorderStyle(self::_mapBorderStyle((0x00000038 & $borderLines) >> 3));
2258
2259 // bit: 8-6; mask: 0x000001C0; right line style
2260 $objStyle->getBorders()->getRight()->setBorderStyle(self::_mapBorderStyle((0x000001C0 & $borderLines) >> 6));
2261
2262 // bit: 15-9; mask: 0x0000FE00; top line color index
2263 $objStyle->getBorders()->getTop()->colorIndex = (0x0000FE00 & $borderLines) >> 9;
2264
2265 // bit: 22-16; mask: 0x007F0000; left line color index
2266 $objStyle->getBorders()->getLeft()->colorIndex = (0x007F0000 & $borderLines) >> 16;
2267
2268 // bit: 29-23; mask: 0x3F800000; right line color index
2269 $objStyle->getBorders()->getRight()->colorIndex = (0x3F800000 & $borderLines) >> 23;
2270 }
2271
2272 // add cellStyleXf or cellXf and update mapping
2273 if ($isCellStyleXf) {
2274 // we only read one style XF record which is always the first
2275 if ($this->_xfIndex == 0) {
2276 $this->_phpExcel->addCellStyleXf($objStyle);
2277 $this->_mapCellStyleXfIndex[$this->_xfIndex] = 0;
2278 }
2279 } else {
2280 // we read all cell XF records
2281 $this->_phpExcel->addCellXf($objStyle);
2282 $this->_mapCellXfIndex[$this->_xfIndex] = count($this->_phpExcel->getCellXfCollection()) - 1;
2283 }
2284
2285 // update XF index for when we read next record
2287 }
2288 }
2289
2290
2294 private function _readXfExt()
2295 {
2296 $length = self::_GetInt2d($this->_data, $this->_pos + 2);
2297 $recordData = $this->_readRecordData($this->_data, $this->_pos + 4, $length);
2298
2299 // move stream pointer to next record
2300 $this->_pos += 4 + $length;
2301
2302 if (!$this->_readDataOnly) {
2303 // offset: 0; size: 2; 0x087D = repeated header
2304
2305 // offset: 2; size: 2
2306
2307 // offset: 4; size: 8; not used
2308
2309 // offset: 12; size: 2; record version
2310
2311 // offset: 14; size: 2; index to XF record which this record modifies
2312 $ixfe = self::_GetInt2d($recordData, 14);
2313
2314 // offset: 16; size: 2; not used
2315
2316 // offset: 18; size: 2; number of extension properties that follow
2317 $cexts = self::_GetInt2d($recordData, 18);
2318
2319 // start reading the actual extension data
2320 $offset = 20;
2321 while ($offset < $length) {
2322 // extension type
2323 $extType = self::_GetInt2d($recordData, $offset);
2324
2325 // extension length
2326 $cb = self::_GetInt2d($recordData, $offset + 2);
2327
2328 // extension data
2329 $extData = substr($recordData, $offset + 4, $cb);
2330
2331 switch ($extType) {
2332 case 4: // fill start color
2333 $xclfType = self::_GetInt2d($extData, 0); // color type
2334 $xclrValue = substr($extData, 4, 4); // color value (value based on color type)
2335
2336 if ($xclfType == 2) {
2337 $rgb = sprintf('%02X%02X%02X', ord($xclrValue{0}), ord($xclrValue{1}), ord($xclrValue{2}));
2338
2339 // modify the relevant style property
2340 if ( isset($this->_mapCellXfIndex[$ixfe]) ) {
2341 $fill = $this->_phpExcel->getCellXfByIndex($this->_mapCellXfIndex[$ixfe])->getFill();
2342 $fill->getStartColor()->setRGB($rgb);
2343 unset($fill->startcolorIndex); // normal color index does not apply, discard
2344 }
2345 }
2346 break;
2347
2348 case 5: // fill end color
2349 $xclfType = self::_GetInt2d($extData, 0); // color type
2350 $xclrValue = substr($extData, 4, 4); // color value (value based on color type)
2351
2352 if ($xclfType == 2) {
2353 $rgb = sprintf('%02X%02X%02X', ord($xclrValue{0}), ord($xclrValue{1}), ord($xclrValue{2}));
2354
2355 // modify the relevant style property
2356 if ( isset($this->_mapCellXfIndex[$ixfe]) ) {
2357 $fill = $this->_phpExcel->getCellXfByIndex($this->_mapCellXfIndex[$ixfe])->getFill();
2358 $fill->getEndColor()->setRGB($rgb);
2359 unset($fill->endcolorIndex); // normal color index does not apply, discard
2360 }
2361 }
2362 break;
2363
2364 case 7: // border color top
2365 $xclfType = self::_GetInt2d($extData, 0); // color type
2366 $xclrValue = substr($extData, 4, 4); // color value (value based on color type)
2367
2368 if ($xclfType == 2) {
2369 $rgb = sprintf('%02X%02X%02X', ord($xclrValue{0}), ord($xclrValue{1}), ord($xclrValue{2}));
2370
2371 // modify the relevant style property
2372 if ( isset($this->_mapCellXfIndex[$ixfe]) ) {
2373 $top = $this->_phpExcel->getCellXfByIndex($this->_mapCellXfIndex[$ixfe])->getBorders()->getTop();
2374 $top->getColor()->setRGB($rgb);
2375 unset($top->colorIndex); // normal color index does not apply, discard
2376 }
2377 }
2378 break;
2379
2380 case 8: // border color bottom
2381 $xclfType = self::_GetInt2d($extData, 0); // color type
2382 $xclrValue = substr($extData, 4, 4); // color value (value based on color type)
2383
2384 if ($xclfType == 2) {
2385 $rgb = sprintf('%02X%02X%02X', ord($xclrValue{0}), ord($xclrValue{1}), ord($xclrValue{2}));
2386
2387 // modify the relevant style property
2388 if ( isset($this->_mapCellXfIndex[$ixfe]) ) {
2389 $bottom = $this->_phpExcel->getCellXfByIndex($this->_mapCellXfIndex[$ixfe])->getBorders()->getBottom();
2390 $bottom->getColor()->setRGB($rgb);
2391 unset($bottom->colorIndex); // normal color index does not apply, discard
2392 }
2393 }
2394 break;
2395
2396 case 9: // border color left
2397 $xclfType = self::_GetInt2d($extData, 0); // color type
2398 $xclrValue = substr($extData, 4, 4); // color value (value based on color type)
2399
2400 if ($xclfType == 2) {
2401 $rgb = sprintf('%02X%02X%02X', ord($xclrValue{0}), ord($xclrValue{1}), ord($xclrValue{2}));
2402
2403 // modify the relevant style property
2404 if ( isset($this->_mapCellXfIndex[$ixfe]) ) {
2405 $left = $this->_phpExcel->getCellXfByIndex($this->_mapCellXfIndex[$ixfe])->getBorders()->getLeft();
2406 $left->getColor()->setRGB($rgb);
2407 unset($left->colorIndex); // normal color index does not apply, discard
2408 }
2409 }
2410 break;
2411
2412 case 10: // border color right
2413 $xclfType = self::_GetInt2d($extData, 0); // color type
2414 $xclrValue = substr($extData, 4, 4); // color value (value based on color type)
2415
2416 if ($xclfType == 2) {
2417 $rgb = sprintf('%02X%02X%02X', ord($xclrValue{0}), ord($xclrValue{1}), ord($xclrValue{2}));
2418
2419 // modify the relevant style property
2420 if ( isset($this->_mapCellXfIndex[$ixfe]) ) {
2421 $right = $this->_phpExcel->getCellXfByIndex($this->_mapCellXfIndex[$ixfe])->getBorders()->getRight();
2422 $right->getColor()->setRGB($rgb);
2423 unset($right->colorIndex); // normal color index does not apply, discard
2424 }
2425 }
2426 break;
2427
2428 case 11: // border color diagonal
2429 $xclfType = self::_GetInt2d($extData, 0); // color type
2430 $xclrValue = substr($extData, 4, 4); // color value (value based on color type)
2431
2432 if ($xclfType == 2) {
2433 $rgb = sprintf('%02X%02X%02X', ord($xclrValue{0}), ord($xclrValue{1}), ord($xclrValue{2}));
2434
2435 // modify the relevant style property
2436 if ( isset($this->_mapCellXfIndex[$ixfe]) ) {
2437 $diagonal = $this->_phpExcel->getCellXfByIndex($this->_mapCellXfIndex[$ixfe])->getBorders()->getDiagonal();
2438 $diagonal->getColor()->setRGB($rgb);
2439 unset($diagonal->colorIndex); // normal color index does not apply, discard
2440 }
2441 }
2442 break;
2443
2444 case 13: // font color
2445 $xclfType = self::_GetInt2d($extData, 0); // color type
2446 $xclrValue = substr($extData, 4, 4); // color value (value based on color type)
2447
2448 if ($xclfType == 2) {
2449 $rgb = sprintf('%02X%02X%02X', ord($xclrValue{0}), ord($xclrValue{1}), ord($xclrValue{2}));
2450
2451 // modify the relevant style property
2452 if ( isset($this->_mapCellXfIndex[$ixfe]) ) {
2453 $font = $this->_phpExcel->getCellXfByIndex($this->_mapCellXfIndex[$ixfe])->getFont();
2454 $font->getColor()->setRGB($rgb);
2455 unset($font->colorIndex); // normal color index does not apply, discard
2456 }
2457 }
2458 break;
2459 }
2460
2461 $offset += $cb;
2462 }
2463 }
2464
2465 }
2466
2467
2471 private function _readStyle()
2472 {
2473 $length = self::_GetInt2d($this->_data, $this->_pos + 2);
2474 $recordData = $this->_readRecordData($this->_data, $this->_pos + 4, $length);
2475
2476 // move stream pointer to next record
2477 $this->_pos += 4 + $length;
2478
2479 if (!$this->_readDataOnly) {
2480 // offset: 0; size: 2; index to XF record and flag for built-in style
2481 $ixfe = self::_GetInt2d($recordData, 0);
2482
2483 // bit: 11-0; mask 0x0FFF; index to XF record
2484 $xfIndex = (0x0FFF & $ixfe) >> 0;
2485
2486 // bit: 15; mask 0x8000; 0 = user-defined style, 1 = built-in style
2487 $isBuiltIn = (bool) ((0x8000 & $ixfe) >> 15);
2488
2489 if ($isBuiltIn) {
2490 // offset: 2; size: 1; identifier for built-in style
2491 $builtInId = ord($recordData{2});
2492
2493 switch ($builtInId) {
2494 case 0x00:
2495 // currently, we are not using this for anything
2496 break;
2497
2498 default:
2499 break;
2500 }
2501
2502 } else {
2503 // user-defined; not supported by PHPExcel
2504 }
2505 }
2506 }
2507
2508
2512 private function _readPalette()
2513 {
2514 $length = self::_GetInt2d($this->_data, $this->_pos + 2);
2515 $recordData = $this->_readRecordData($this->_data, $this->_pos + 4, $length);
2516
2517 // move stream pointer to next record
2518 $this->_pos += 4 + $length;
2519
2520 if (!$this->_readDataOnly) {
2521 // offset: 0; size: 2; number of following colors
2522 $nm = self::_GetInt2d($recordData, 0);
2523
2524 // list of RGB colors
2525 for ($i = 0; $i < $nm; ++$i) {
2526 $rgb = substr($recordData, 2 + 4 * $i, 4);
2527 $this->_palette[] = self::_readRGB($rgb);
2528 }
2529 }
2530 }
2531
2532
2545 private function _readSheet()
2546 {
2547 $length = self::_GetInt2d($this->_data, $this->_pos + 2);
2548 $recordData = $this->_readRecordData($this->_data, $this->_pos + 4, $length);
2549
2550 // offset: 0; size: 4; absolute stream position of the BOF record of the sheet
2551 // NOTE: not encrypted
2552 $rec_offset = self::_GetInt4d($this->_data, $this->_pos + 4);
2553
2554 // move stream pointer to next record
2555 $this->_pos += 4 + $length;
2556
2557 // offset: 4; size: 1; sheet state
2558 switch (ord($recordData{4})) {
2559 case 0x00: $sheetState = PHPExcel_Worksheet::SHEETSTATE_VISIBLE; break;
2560 case 0x01: $sheetState = PHPExcel_Worksheet::SHEETSTATE_HIDDEN; break;
2561 case 0x02: $sheetState = PHPExcel_Worksheet::SHEETSTATE_VERYHIDDEN; break;
2562 default: $sheetState = PHPExcel_Worksheet::SHEETSTATE_VISIBLE; break;
2563 }
2564
2565 // offset: 5; size: 1; sheet type
2566 $sheetType = ord($recordData{5});
2567
2568 // offset: 6; size: var; sheet name
2569 if ($this->_version == self::XLS_BIFF8) {
2570 $string = self::_readUnicodeStringShort(substr($recordData, 6));
2571 $rec_name = $string['value'];
2572 } elseif ($this->_version == self::XLS_BIFF7) {
2573 $string = $this->_readByteStringShort(substr($recordData, 6));
2574 $rec_name = $string['value'];
2575 }
2576
2577 $this->_sheets[] = array(
2578 'name' => $rec_name,
2579 'offset' => $rec_offset,
2580 'sheetState' => $sheetState,
2581 'sheetType' => $sheetType,
2582 );
2583 }
2584
2585
2589 private function _readExternalBook()
2590 {
2591 $length = self::_GetInt2d($this->_data, $this->_pos + 2);
2592 $recordData = $this->_readRecordData($this->_data, $this->_pos + 4, $length);
2593
2594 // move stream pointer to next record
2595 $this->_pos += 4 + $length;
2596
2597 // offset within record data
2598 $offset = 0;
2599
2600 // there are 4 types of records
2601 if (strlen($recordData) > 4) {
2602 // external reference
2603 // offset: 0; size: 2; number of sheet names ($nm)
2604 $nm = self::_GetInt2d($recordData, 0);
2605 $offset += 2;
2606
2607 // offset: 2; size: var; encoded URL without sheet name (Unicode string, 16-bit length)
2608 $encodedUrlString = self::_readUnicodeStringLong(substr($recordData, 2));
2609 $offset += $encodedUrlString['size'];
2610
2611 // offset: var; size: var; list of $nm sheet names (Unicode strings, 16-bit length)
2612 $externalSheetNames = array();
2613 for ($i = 0; $i < $nm; ++$i) {
2614 $externalSheetNameString = self::_readUnicodeStringLong(substr($recordData, $offset));
2615 $externalSheetNames[] = $externalSheetNameString['value'];
2616 $offset += $externalSheetNameString['size'];
2617 }
2618
2619 // store the record data
2620 $this->_externalBooks[] = array(
2621 'type' => 'external',
2622 'encodedUrl' => $encodedUrlString['value'],
2623 'externalSheetNames' => $externalSheetNames,
2624 );
2625
2626 } elseif (substr($recordData, 2, 2) == pack('CC', 0x01, 0x04)) {
2627 // internal reference
2628 // offset: 0; size: 2; number of sheet in this document
2629 // offset: 2; size: 2; 0x01 0x04
2630 $this->_externalBooks[] = array(
2631 'type' => 'internal',
2632 );
2633 } elseif (substr($recordData, 0, 4) == pack('vCC', 0x0001, 0x01, 0x3A)) {
2634 // add-in function
2635 // offset: 0; size: 2; 0x0001
2636 $this->_externalBooks[] = array(
2637 'type' => 'addInFunction',
2638 );
2639 } elseif (substr($recordData, 0, 2) == pack('v', 0x0000)) {
2640 // DDE links, OLE links
2641 // offset: 0; size: 2; 0x0000
2642 // offset: 2; size: var; encoded source document name
2643 $this->_externalBooks[] = array(
2644 'type' => 'DDEorOLE',
2645 );
2646 }
2647 }
2648
2649
2653 private function _readExternName()
2654 {
2655 $length = self::_GetInt2d($this->_data, $this->_pos + 2);
2656 $recordData = $this->_readRecordData($this->_data, $this->_pos + 4, $length);
2657
2658 // move stream pointer to next record
2659 $this->_pos += 4 + $length;
2660
2661 // external sheet references provided for named cells
2662 if ($this->_version == self::XLS_BIFF8) {
2663 // offset: 0; size: 2; options
2664 $options = self::_GetInt2d($recordData, 0);
2665
2666 // offset: 2; size: 2;
2667
2668 // offset: 4; size: 2; not used
2669
2670 // offset: 6; size: var
2671 $nameString = self::_readUnicodeStringShort(substr($recordData, 6));
2672
2673 // offset: var; size: var; formula data
2674 $offset = 6 + $nameString['size'];
2675 $formula = $this->_getFormulaFromStructure(substr($recordData, $offset));
2676
2677 $this->_externalNames[] = array(
2678 'name' => $nameString['value'],
2679 'formula' => $formula,
2680 );
2681 }
2682 }
2683
2684
2688 private function _readExternSheet()
2689 {
2690 $length = self::_GetInt2d($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 // external sheet references provided for named cells
2697 if ($this->_version == self::XLS_BIFF8) {
2698 // offset: 0; size: 2; number of following ref structures
2699 $nm = self::_GetInt2d($recordData, 0);
2700 for ($i = 0; $i < $nm; ++$i) {
2701 $this->_ref[] = array(
2702 // offset: 2 + 6 * $i; index to EXTERNALBOOK record
2703 'externalBookIndex' => self::_GetInt2d($recordData, 2 + 6 * $i),
2704 // offset: 4 + 6 * $i; index to first sheet in EXTERNALBOOK record
2705 'firstSheetIndex' => self::_GetInt2d($recordData, 4 + 6 * $i),
2706 // offset: 6 + 6 * $i; index to last sheet in EXTERNALBOOK record
2707 'lastSheetIndex' => self::_GetInt2d($recordData, 6 + 6 * $i),
2708 );
2709 }
2710 }
2711 }
2712
2713
2725 private function _readDefinedName()
2726 {
2727 $length = self::_GetInt2d($this->_data, $this->_pos + 2);
2728 $recordData = $this->_readRecordData($this->_data, $this->_pos + 4, $length);
2729
2730 // move stream pointer to next record
2731 $this->_pos += 4 + $length;
2732
2733 if ($this->_version == self::XLS_BIFF8) {
2734 // retrieves named cells
2735
2736 // offset: 0; size: 2; option flags
2737 $opts = self::_GetInt2d($recordData, 0);
2738
2739 // bit: 5; mask: 0x0020; 0 = user-defined name, 1 = built-in-name
2740 $isBuiltInName = (0x0020 & $opts) >> 5;
2741
2742 // offset: 2; size: 1; keyboard shortcut
2743
2744 // offset: 3; size: 1; length of the name (character count)
2745 $nlen = ord($recordData{3});
2746
2747 // offset: 4; size: 2; size of the formula data (it can happen that this is zero)
2748 // note: there can also be additional data, this is not included in $flen
2749 $flen = self::_GetInt2d($recordData, 4);
2750
2751 // offset: 8; size: 2; 0=Global name, otherwise index to sheet (1-based)
2752 $scope = self::_GetInt2d($recordData, 8);
2753
2754 // offset: 14; size: var; Name (Unicode string without length field)
2755 $string = self::_readUnicodeString(substr($recordData, 14), $nlen);
2756
2757 // offset: var; size: $flen; formula data
2758 $offset = 14 + $string['size'];
2759 $formulaStructure = pack('v', $flen) . substr($recordData, $offset);
2760
2761 try {
2762 $formula = $this->_getFormulaFromStructure($formulaStructure);
2763 } catch (PHPExcel_Exception $e) {
2764 $formula = '';
2765 }
2766
2767 $this->_definedname[] = array(
2768 'isBuiltInName' => $isBuiltInName,
2769 'name' => $string['value'],
2770 'formula' => $formula,
2771 'scope' => $scope,
2772 );
2773 }
2774 }
2775
2776
2780 private function _readMsoDrawingGroup()
2781 {
2782 $length = self::_GetInt2d($this->_data, $this->_pos + 2);
2783
2784 // get spliced record data
2785 $splicedRecordData = $this->_getSplicedRecordData();
2786 $recordData = $splicedRecordData['recordData'];
2787
2788 $this->_drawingGroupData .= $recordData;
2789 }
2790
2791
2803 private function _readSst()
2804 {
2805 // offset within (spliced) record data
2806 $pos = 0;
2807
2808 // get spliced record data
2809 $splicedRecordData = $this->_getSplicedRecordData();
2810
2811 $recordData = $splicedRecordData['recordData'];
2812 $spliceOffsets = $splicedRecordData['spliceOffsets'];
2813
2814 // offset: 0; size: 4; total number of strings in the workbook
2815 $pos += 4;
2816
2817 // offset: 4; size: 4; number of following strings ($nm)
2818 $nm = self::_GetInt4d($recordData, 4);
2819 $pos += 4;
2820
2821 // loop through the Unicode strings (16-bit length)
2822 for ($i = 0; $i < $nm; ++$i) {
2823
2824 // number of characters in the Unicode string
2825 $numChars = self::_GetInt2d($recordData, $pos);
2826 $pos += 2;
2827
2828 // option flags
2829 $optionFlags = ord($recordData{$pos});
2830 ++$pos;
2831
2832 // bit: 0; mask: 0x01; 0 = compressed; 1 = uncompressed
2833 $isCompressed = (($optionFlags & 0x01) == 0) ;
2834
2835 // bit: 2; mask: 0x02; 0 = ordinary; 1 = Asian phonetic
2836 $hasAsian = (($optionFlags & 0x04) != 0);
2837
2838 // bit: 3; mask: 0x03; 0 = ordinary; 1 = Rich-Text
2839 $hasRichText = (($optionFlags & 0x08) != 0);
2840
2841 if ($hasRichText) {
2842 // number of Rich-Text formatting runs
2843 $formattingRuns = self::_GetInt2d($recordData, $pos);
2844 $pos += 2;
2845 }
2846
2847 if ($hasAsian) {
2848 // size of Asian phonetic setting
2849 $extendedRunLength = self::_GetInt4d($recordData, $pos);
2850 $pos += 4;
2851 }
2852
2853 // expected byte length of character array if not split
2854 $len = ($isCompressed) ? $numChars : $numChars * 2;
2855
2856 // look up limit position
2857 foreach ($spliceOffsets as $spliceOffset) {
2858 // it can happen that the string is empty, therefore we need
2859 // <= and not just <
2860 if ($pos <= $spliceOffset) {
2861 $limitpos = $spliceOffset;
2862 break;
2863 }
2864 }
2865
2866 if ($pos + $len <= $limitpos) {
2867 // character array is not split between records
2868
2869 $retstr = substr($recordData, $pos, $len);
2870 $pos += $len;
2871
2872 } else {
2873 // character array is split between records
2874
2875 // first part of character array
2876 $retstr = substr($recordData, $pos, $limitpos - $pos);
2877
2878 $bytesRead = $limitpos - $pos;
2879
2880 // remaining characters in Unicode string
2881 $charsLeft = $numChars - (($isCompressed) ? $bytesRead : ($bytesRead / 2));
2882
2883 $pos = $limitpos;
2884
2885 // keep reading the characters
2886 while ($charsLeft > 0) {
2887
2888 // look up next limit position, in case the string span more than one continue record
2889 foreach ($spliceOffsets as $spliceOffset) {
2890 if ($pos < $spliceOffset) {
2891 $limitpos = $spliceOffset;
2892 break;
2893 }
2894 }
2895
2896 // repeated option flags
2897 // OpenOffice.org documentation 5.21
2898 $option = ord($recordData{$pos});
2899 ++$pos;
2900
2901 if ($isCompressed && ($option == 0)) {
2902 // 1st fragment compressed
2903 // this fragment compressed
2904 $len = min($charsLeft, $limitpos - $pos);
2905 $retstr .= substr($recordData, $pos, $len);
2906 $charsLeft -= $len;
2907 $isCompressed = true;
2908
2909 } elseif (!$isCompressed && ($option != 0)) {
2910 // 1st fragment uncompressed
2911 // this fragment uncompressed
2912 $len = min($charsLeft * 2, $limitpos - $pos);
2913 $retstr .= substr($recordData, $pos, $len);
2914 $charsLeft -= $len / 2;
2915 $isCompressed = false;
2916
2917 } elseif (!$isCompressed && ($option == 0)) {
2918 // 1st fragment uncompressed
2919 // this fragment compressed
2920 $len = min($charsLeft, $limitpos - $pos);
2921 for ($j = 0; $j < $len; ++$j) {
2922 $retstr .= $recordData{$pos + $j} . chr(0);
2923 }
2924 $charsLeft -= $len;
2925 $isCompressed = false;
2926
2927 } else {
2928 // 1st fragment compressed
2929 // this fragment uncompressed
2930 $newstr = '';
2931 for ($j = 0; $j < strlen($retstr); ++$j) {
2932 $newstr .= $retstr[$j] . chr(0);
2933 }
2934 $retstr = $newstr;
2935 $len = min($charsLeft * 2, $limitpos - $pos);
2936 $retstr .= substr($recordData, $pos, $len);
2937 $charsLeft -= $len / 2;
2938 $isCompressed = false;
2939 }
2940
2941 $pos += $len;
2942 }
2943 }
2944
2945 // convert to UTF-8
2946 $retstr = self::_encodeUTF16($retstr, $isCompressed);
2947
2948 // read additional Rich-Text information, if any
2949 $fmtRuns = array();
2950 if ($hasRichText) {
2951 // list of formatting runs
2952 for ($j = 0; $j < $formattingRuns; ++$j) {
2953 // first formatted character; zero-based
2954 $charPos = self::_GetInt2d($recordData, $pos + $j * 4);
2955
2956 // index to font record
2957 $fontIndex = self::_GetInt2d($recordData, $pos + 2 + $j * 4);
2958
2959 $fmtRuns[] = array(
2960 'charPos' => $charPos,
2961 'fontIndex' => $fontIndex,
2962 );
2963 }
2964 $pos += 4 * $formattingRuns;
2965 }
2966
2967 // read additional Asian phonetics information, if any
2968 if ($hasAsian) {
2969 // For Asian phonetic settings, we skip the extended string data
2970 $pos += $extendedRunLength;
2971 }
2972
2973 // store the shared sting
2974 $this->_sst[] = array(
2975 'value' => $retstr,
2976 'fmtRuns' => $fmtRuns,
2977 );
2978 }
2979
2980 // _getSplicedRecordData() takes care of moving current position in data stream
2981 }
2982
2983
2987 private function _readPrintGridlines()
2988 {
2989 $length = self::_GetInt2d($this->_data, $this->_pos + 2);
2990 $recordData = $this->_readRecordData($this->_data, $this->_pos + 4, $length);
2991
2992 // move stream pointer to next record
2993 $this->_pos += 4 + $length;
2994
2995 if ($this->_version == self::XLS_BIFF8 && !$this->_readDataOnly) {
2996 // offset: 0; size: 2; 0 = do not print sheet grid lines; 1 = print sheet gridlines
2997 $printGridlines = (bool) self::_GetInt2d($recordData, 0);
2998 $this->_phpSheet->setPrintGridlines($printGridlines);
2999 }
3000 }
3001
3002
3006 private function _readDefaultRowHeight()
3007 {
3008 $length = self::_GetInt2d($this->_data, $this->_pos + 2);
3009 $recordData = $this->_readRecordData($this->_data, $this->_pos + 4, $length);
3010
3011 // move stream pointer to next record
3012 $this->_pos += 4 + $length;
3013
3014 // offset: 0; size: 2; option flags
3015 // offset: 2; size: 2; default height for unused rows, (twips 1/20 point)
3016 $height = self::_GetInt2d($recordData, 2);
3017 $this->_phpSheet->getDefaultRowDimension()->setRowHeight($height / 20);
3018 }
3019
3020
3024 private function _readSheetPr()
3025 {
3026 $length = self::_GetInt2d($this->_data, $this->_pos + 2);
3027 $recordData = $this->_readRecordData($this->_data, $this->_pos + 4, $length);
3028
3029 // move stream pointer to next record
3030 $this->_pos += 4 + $length;
3031
3032 // offset: 0; size: 2
3033
3034 // bit: 6; mask: 0x0040; 0 = outline buttons above outline group
3035 $isSummaryBelow = (0x0040 & self::_GetInt2d($recordData, 0)) >> 6;
3036 $this->_phpSheet->setShowSummaryBelow($isSummaryBelow);
3037
3038 // bit: 7; mask: 0x0080; 0 = outline buttons left of outline group
3039 $isSummaryRight = (0x0080 & self::_GetInt2d($recordData, 0)) >> 7;
3040 $this->_phpSheet->setShowSummaryRight($isSummaryRight);
3041
3042 // bit: 8; mask: 0x100; 0 = scale printout in percent, 1 = fit printout to number of pages
3043 // this corresponds to radio button setting in page setup dialog in Excel
3044 $this->_isFitToPages = (bool) ((0x0100 & self::_GetInt2d($recordData, 0)) >> 8);
3045 }
3046
3047
3051 private function _readHorizontalPageBreaks()
3052 {
3053 $length = self::_GetInt2d($this->_data, $this->_pos + 2);
3054 $recordData = $this->_readRecordData($this->_data, $this->_pos + 4, $length);
3055
3056 // move stream pointer to next record
3057 $this->_pos += 4 + $length;
3058
3059 if ($this->_version == self::XLS_BIFF8 && !$this->_readDataOnly) {
3060
3061 // offset: 0; size: 2; number of the following row index structures
3062 $nm = self::_GetInt2d($recordData, 0);
3063
3064 // offset: 2; size: 6 * $nm; list of $nm row index structures
3065 for ($i = 0; $i < $nm; ++$i) {
3066 $r = self::_GetInt2d($recordData, 2 + 6 * $i);
3067 $cf = self::_GetInt2d($recordData, 2 + 6 * $i + 2);
3068 $cl = self::_GetInt2d($recordData, 2 + 6 * $i + 4);
3069
3070 // not sure why two column indexes are necessary?
3071 $this->_phpSheet->setBreakByColumnAndRow($cf, $r, PHPExcel_Worksheet::BREAK_ROW);
3072 }
3073 }
3074 }
3075
3076
3080 private function _readVerticalPageBreaks()
3081 {
3082 $length = self::_GetInt2d($this->_data, $this->_pos + 2);
3083 $recordData = $this->_readRecordData($this->_data, $this->_pos + 4, $length);
3084
3085 // move stream pointer to next record
3086 $this->_pos += 4 + $length;
3087
3088 if ($this->_version == self::XLS_BIFF8 && !$this->_readDataOnly) {
3089 // offset: 0; size: 2; number of the following column index structures
3090 $nm = self::_GetInt2d($recordData, 0);
3091
3092 // offset: 2; size: 6 * $nm; list of $nm row index structures
3093 for ($i = 0; $i < $nm; ++$i) {
3094 $c = self::_GetInt2d($recordData, 2 + 6 * $i);
3095 $rf = self::_GetInt2d($recordData, 2 + 6 * $i + 2);
3096 $rl = self::_GetInt2d($recordData, 2 + 6 * $i + 4);
3097
3098 // not sure why two row indexes are necessary?
3099 $this->_phpSheet->setBreakByColumnAndRow($c, $rf, PHPExcel_Worksheet::BREAK_COLUMN);
3100 }
3101 }
3102 }
3103
3104
3108 private function _readHeader()
3109 {
3110 $length = self::_GetInt2d($this->_data, $this->_pos + 2);
3111 $recordData = $this->_readRecordData($this->_data, $this->_pos + 4, $length);
3112
3113 // move stream pointer to next record
3114 $this->_pos += 4 + $length;
3115
3116 if (!$this->_readDataOnly) {
3117 // offset: 0; size: var
3118 // realized that $recordData can be empty even when record exists
3119 if ($recordData) {
3120 if ($this->_version == self::XLS_BIFF8) {
3121 $string = self::_readUnicodeStringLong($recordData);
3122 } else {
3123 $string = $this->_readByteStringShort($recordData);
3124 }
3125
3126 $this->_phpSheet->getHeaderFooter()->setOddHeader($string['value']);
3127 $this->_phpSheet->getHeaderFooter()->setEvenHeader($string['value']);
3128 }
3129 }
3130 }
3131
3132
3136 private function _readFooter()
3137 {
3138 $length = self::_GetInt2d($this->_data, $this->_pos + 2);
3139 $recordData = $this->_readRecordData($this->_data, $this->_pos + 4, $length);
3140
3141 // move stream pointer to next record
3142 $this->_pos += 4 + $length;
3143
3144 if (!$this->_readDataOnly) {
3145 // offset: 0; size: var
3146 // realized that $recordData can be empty even when record exists
3147 if ($recordData) {
3148 if ($this->_version == self::XLS_BIFF8) {
3149 $string = self::_readUnicodeStringLong($recordData);
3150 } else {
3151 $string = $this->_readByteStringShort($recordData);
3152 }
3153 $this->_phpSheet->getHeaderFooter()->setOddFooter($string['value']);
3154 $this->_phpSheet->getHeaderFooter()->setEvenFooter($string['value']);
3155 }
3156 }
3157 }
3158
3159
3163 private function _readHcenter()
3164 {
3165 $length = self::_GetInt2d($this->_data, $this->_pos + 2);
3166 $recordData = $this->_readRecordData($this->_data, $this->_pos + 4, $length);
3167
3168 // move stream pointer to next record
3169 $this->_pos += 4 + $length;
3170
3171 if (!$this->_readDataOnly) {
3172 // offset: 0; size: 2; 0 = print sheet left aligned, 1 = print sheet centered horizontally
3173 $isHorizontalCentered = (bool) self::_GetInt2d($recordData, 0);
3174
3175 $this->_phpSheet->getPageSetup()->setHorizontalCentered($isHorizontalCentered);
3176 }
3177 }
3178
3179
3183 private function _readVcenter()
3184 {
3185 $length = self::_GetInt2d($this->_data, $this->_pos + 2);
3186 $recordData = $this->_readRecordData($this->_data, $this->_pos + 4, $length);
3187
3188 // move stream pointer to next record
3189 $this->_pos += 4 + $length;
3190
3191 if (!$this->_readDataOnly) {
3192 // offset: 0; size: 2; 0 = print sheet aligned at top page border, 1 = print sheet vertically centered
3193 $isVerticalCentered = (bool) self::_GetInt2d($recordData, 0);
3194
3195 $this->_phpSheet->getPageSetup()->setVerticalCentered($isVerticalCentered);
3196 }
3197 }
3198
3199
3203 private function _readLeftMargin()
3204 {
3205 $length = self::_GetInt2d($this->_data, $this->_pos + 2);
3206 $recordData = $this->_readRecordData($this->_data, $this->_pos + 4, $length);
3207
3208 // move stream pointer to next record
3209 $this->_pos += 4 + $length;
3210
3211 if (!$this->_readDataOnly) {
3212 // offset: 0; size: 8
3213 $this->_phpSheet->getPageMargins()->setLeft(self::_extractNumber($recordData));
3214 }
3215 }
3216
3217
3221 private function _readRightMargin()
3222 {
3223 $length = self::_GetInt2d($this->_data, $this->_pos + 2);
3224 $recordData = $this->_readRecordData($this->_data, $this->_pos + 4, $length);
3225
3226 // move stream pointer to next record
3227 $this->_pos += 4 + $length;
3228
3229 if (!$this->_readDataOnly) {
3230 // offset: 0; size: 8
3231 $this->_phpSheet->getPageMargins()->setRight(self::_extractNumber($recordData));
3232 }
3233 }
3234
3235
3239 private function _readTopMargin()
3240 {
3241 $length = self::_GetInt2d($this->_data, $this->_pos + 2);
3242 $recordData = $this->_readRecordData($this->_data, $this->_pos + 4, $length);
3243
3244 // move stream pointer to next record
3245 $this->_pos += 4 + $length;
3246
3247 if (!$this->_readDataOnly) {
3248 // offset: 0; size: 8
3249 $this->_phpSheet->getPageMargins()->setTop(self::_extractNumber($recordData));
3250 }
3251 }
3252
3253
3257 private function _readBottomMargin()
3258 {
3259 $length = self::_GetInt2d($this->_data, $this->_pos + 2);
3260 $recordData = $this->_readRecordData($this->_data, $this->_pos + 4, $length);
3261
3262 // move stream pointer to next record
3263 $this->_pos += 4 + $length;
3264
3265 if (!$this->_readDataOnly) {
3266 // offset: 0; size: 8
3267 $this->_phpSheet->getPageMargins()->setBottom(self::_extractNumber($recordData));
3268 }
3269 }
3270
3271
3275 private function _readPageSetup()
3276 {
3277 $length = self::_GetInt2d($this->_data, $this->_pos + 2);
3278 $recordData = $this->_readRecordData($this->_data, $this->_pos + 4, $length);
3279
3280 // move stream pointer to next record
3281 $this->_pos += 4 + $length;
3282
3283 if (!$this->_readDataOnly) {
3284 // offset: 0; size: 2; paper size
3285 $paperSize = self::_GetInt2d($recordData, 0);
3286
3287 // offset: 2; size: 2; scaling factor
3288 $scale = self::_GetInt2d($recordData, 2);
3289
3290 // offset: 6; size: 2; fit worksheet width to this number of pages, 0 = use as many as needed
3291 $fitToWidth = self::_GetInt2d($recordData, 6);
3292
3293 // offset: 8; size: 2; fit worksheet height to this number of pages, 0 = use as many as needed
3294 $fitToHeight = self::_GetInt2d($recordData, 8);
3295
3296 // offset: 10; size: 2; option flags
3297
3298 // bit: 1; mask: 0x0002; 0=landscape, 1=portrait
3299 $isPortrait = (0x0002 & self::_GetInt2d($recordData, 10)) >> 1;
3300
3301 // bit: 2; mask: 0x0004; 1= paper size, scaling factor, paper orient. not init
3302 // when this bit is set, do not use flags for those properties
3303 $isNotInit = (0x0004 & self::_GetInt2d($recordData, 10)) >> 2;
3304
3305 if (!$isNotInit) {
3306 $this->_phpSheet->getPageSetup()->setPaperSize($paperSize);
3307 switch ($isPortrait) {
3308 case 0: $this->_phpSheet->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE); break;
3309 case 1: $this->_phpSheet->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_PORTRAIT); break;
3310 }
3311
3312 $this->_phpSheet->getPageSetup()->setScale($scale, false);
3313 $this->_phpSheet->getPageSetup()->setFitToPage((bool) $this->_isFitToPages);
3314 $this->_phpSheet->getPageSetup()->setFitToWidth($fitToWidth, false);
3315 $this->_phpSheet->getPageSetup()->setFitToHeight($fitToHeight, false);
3316 }
3317
3318 // offset: 16; size: 8; header margin (IEEE 754 floating-point value)
3319 $marginHeader = self::_extractNumber(substr($recordData, 16, 8));
3320 $this->_phpSheet->getPageMargins()->setHeader($marginHeader);
3321
3322 // offset: 24; size: 8; footer margin (IEEE 754 floating-point value)
3323 $marginFooter = self::_extractNumber(substr($recordData, 24, 8));
3324 $this->_phpSheet->getPageMargins()->setFooter($marginFooter);
3325 }
3326 }
3327
3328
3333 private function _readProtect()
3334 {
3335 $length = self::_GetInt2d($this->_data, $this->_pos + 2);
3336 $recordData = $this->_readRecordData($this->_data, $this->_pos + 4, $length);
3337
3338 // move stream pointer to next record
3339 $this->_pos += 4 + $length;
3340
3341 if ($this->_readDataOnly) {
3342 return;
3343 }
3344
3345 // offset: 0; size: 2;
3346
3347 // bit 0, mask 0x01; 1 = sheet is protected
3348 $bool = (0x01 & self::_GetInt2d($recordData, 0)) >> 0;
3349 $this->_phpSheet->getProtection()->setSheet((bool)$bool);
3350 }
3351
3352
3356 private function _readScenProtect()
3357 {
3358 $length = self::_GetInt2d($this->_data, $this->_pos + 2);
3359 $recordData = $this->_readRecordData($this->_data, $this->_pos + 4, $length);
3360
3361 // move stream pointer to next record
3362 $this->_pos += 4 + $length;
3363
3364 if ($this->_readDataOnly) {
3365 return;
3366 }
3367
3368 // offset: 0; size: 2;
3369
3370 // bit: 0, mask 0x01; 1 = scenarios are protected
3371 $bool = (0x01 & self::_GetInt2d($recordData, 0)) >> 0;
3372
3373 $this->_phpSheet->getProtection()->setScenarios((bool)$bool);
3374 }
3375
3376
3380 private function _readObjectProtect()
3381 {
3382 $length = self::_GetInt2d($this->_data, $this->_pos + 2);
3383 $recordData = $this->_readRecordData($this->_data, $this->_pos + 4, $length);
3384
3385 // move stream pointer to next record
3386 $this->_pos += 4 + $length;
3387
3388 if ($this->_readDataOnly) {
3389 return;
3390 }
3391
3392 // offset: 0; size: 2;
3393
3394 // bit: 0, mask 0x01; 1 = objects are protected
3395 $bool = (0x01 & self::_GetInt2d($recordData, 0)) >> 0;
3396
3397 $this->_phpSheet->getProtection()->setObjects((bool)$bool);
3398 }
3399
3400
3404 private function _readPassword()
3405 {
3406 $length = self::_GetInt2d($this->_data, $this->_pos + 2);
3407 $recordData = $this->_readRecordData($this->_data, $this->_pos + 4, $length);
3408
3409 // move stream pointer to next record
3410 $this->_pos += 4 + $length;
3411
3412 if (!$this->_readDataOnly) {
3413 // offset: 0; size: 2; 16-bit hash value of password
3414 $password = strtoupper(dechex(self::_GetInt2d($recordData, 0))); // the hashed password
3415 $this->_phpSheet->getProtection()->setPassword($password, true);
3416 }
3417 }
3418
3419
3423 private function _readDefColWidth()
3424 {
3425 $length = self::_GetInt2d($this->_data, $this->_pos + 2);
3426 $recordData = $this->_readRecordData($this->_data, $this->_pos + 4, $length);
3427
3428 // move stream pointer to next record
3429 $this->_pos += 4 + $length;
3430
3431 // offset: 0; size: 2; default column width
3432 $width = self::_GetInt2d($recordData, 0);
3433 if ($width != 8) {
3434 $this->_phpSheet->getDefaultColumnDimension()->setWidth($width);
3435 }
3436 }
3437
3438
3442 private function _readColInfo()
3443 {
3444 $length = self::_GetInt2d($this->_data, $this->_pos + 2);
3445 $recordData = $this->_readRecordData($this->_data, $this->_pos + 4, $length);
3446
3447 // move stream pointer to next record
3448 $this->_pos += 4 + $length;
3449
3450 if (!$this->_readDataOnly) {
3451 // offset: 0; size: 2; index to first column in range
3452 $fc = self::_GetInt2d($recordData, 0); // first column index
3453
3454 // offset: 2; size: 2; index to last column in range
3455 $lc = self::_GetInt2d($recordData, 2); // first column index
3456
3457 // offset: 4; size: 2; width of the column in 1/256 of the width of the zero character
3458 $width = self::_GetInt2d($recordData, 4);
3459
3460 // offset: 6; size: 2; index to XF record for default column formatting
3461 $xfIndex = self::_GetInt2d($recordData, 6);
3462
3463 // offset: 8; size: 2; option flags
3464
3465 // bit: 0; mask: 0x0001; 1= columns are hidden
3466 $isHidden = (0x0001 & self::_GetInt2d($recordData, 8)) >> 0;
3467
3468 // bit: 10-8; mask: 0x0700; outline level of the columns (0 = no outline)
3469 $level = (0x0700 & self::_GetInt2d($recordData, 8)) >> 8;
3470
3471 // bit: 12; mask: 0x1000; 1 = collapsed
3472 $isCollapsed = (0x1000 & self::_GetInt2d($recordData, 8)) >> 12;
3473
3474 // offset: 10; size: 2; not used
3475
3476 for ($i = $fc; $i <= $lc; ++$i) {
3477 if ($lc == 255 || $lc == 256) {
3478 $this->_phpSheet->getDefaultColumnDimension()->setWidth($width / 256);
3479 break;
3480 }
3481 $this->_phpSheet->getColumnDimensionByColumn($i)->setWidth($width / 256);
3482 $this->_phpSheet->getColumnDimensionByColumn($i)->setVisible(!$isHidden);
3483 $this->_phpSheet->getColumnDimensionByColumn($i)->setOutlineLevel($level);
3484 $this->_phpSheet->getColumnDimensionByColumn($i)->setCollapsed($isCollapsed);
3485 $this->_phpSheet->getColumnDimensionByColumn($i)->setXfIndex($this->_mapCellXfIndex[$xfIndex]);
3486 }
3487 }
3488 }
3489
3490
3501 private function _readRow()
3502 {
3503 $length = self::_GetInt2d($this->_data, $this->_pos + 2);
3504 $recordData = $this->_readRecordData($this->_data, $this->_pos + 4, $length);
3505
3506 // move stream pointer to next record
3507 $this->_pos += 4 + $length;
3508
3509 if (!$this->_readDataOnly) {
3510 // offset: 0; size: 2; index of this row
3511 $r = self::_GetInt2d($recordData, 0);
3512
3513 // offset: 2; size: 2; index to column of the first cell which is described by a cell record
3514
3515 // offset: 4; size: 2; index to column of the last cell which is described by a cell record, increased by 1
3516
3517 // offset: 6; size: 2;
3518
3519 // bit: 14-0; mask: 0x7FFF; height of the row, in twips = 1/20 of a point
3520 $height = (0x7FFF & self::_GetInt2d($recordData, 6)) >> 0;
3521
3522 // bit: 15: mask: 0x8000; 0 = row has custom height; 1= row has default height
3523 $useDefaultHeight = (0x8000 & self::_GetInt2d($recordData, 6)) >> 15;
3524
3525 if (!$useDefaultHeight) {
3526 $this->_phpSheet->getRowDimension($r + 1)->setRowHeight($height / 20);
3527 }
3528
3529 // offset: 8; size: 2; not used
3530
3531 // offset: 10; size: 2; not used in BIFF5-BIFF8
3532
3533 // offset: 12; size: 4; option flags and default row formatting
3534
3535 // bit: 2-0: mask: 0x00000007; outline level of the row
3536 $level = (0x00000007 & self::_GetInt4d($recordData, 12)) >> 0;
3537 $this->_phpSheet->getRowDimension($r + 1)->setOutlineLevel($level);
3538
3539 // bit: 4; mask: 0x00000010; 1 = outline group start or ends here... and is collapsed
3540 $isCollapsed = (0x00000010 & self::_GetInt4d($recordData, 12)) >> 4;
3541 $this->_phpSheet->getRowDimension($r + 1)->setCollapsed($isCollapsed);
3542
3543 // bit: 5; mask: 0x00000020; 1 = row is hidden
3544 $isHidden = (0x00000020 & self::_GetInt4d($recordData, 12)) >> 5;
3545 $this->_phpSheet->getRowDimension($r + 1)->setVisible(!$isHidden);
3546
3547 // bit: 7; mask: 0x00000080; 1 = row has explicit format
3548 $hasExplicitFormat = (0x00000080 & self::_GetInt4d($recordData, 12)) >> 7;
3549
3550 // bit: 27-16; mask: 0x0FFF0000; only applies when hasExplicitFormat = 1; index to XF record
3551 $xfIndex = (0x0FFF0000 & self::_GetInt4d($recordData, 12)) >> 16;
3552
3553 if ($hasExplicitFormat) {
3554 $this->_phpSheet->getRowDimension($r + 1)->setXfIndex($this->_mapCellXfIndex[$xfIndex]);
3555 }
3556 }
3557 }
3558
3559
3571 private function _readRk()
3572 {
3573 $length = self::_GetInt2d($this->_data, $this->_pos + 2);
3574 $recordData = $this->_readRecordData($this->_data, $this->_pos + 4, $length);
3575
3576 // move stream pointer to next record
3577 $this->_pos += 4 + $length;
3578
3579 // offset: 0; size: 2; index to row
3580 $row = self::_GetInt2d($recordData, 0);
3581
3582 // offset: 2; size: 2; index to column
3583 $column = self::_GetInt2d($recordData, 2);
3585
3586 // Read cell?
3587 if (($this->getReadFilter() !== NULL) && $this->getReadFilter()->readCell($columnString, $row + 1, $this->_phpSheet->getTitle()) ) {
3588 // offset: 4; size: 2; index to XF record
3589 $xfIndex = self::_GetInt2d($recordData, 4);
3590
3591 // offset: 6; size: 4; RK value
3592 $rknum = self::_GetInt4d($recordData, 6);
3593 $numValue = self::_GetIEEE754($rknum);
3594
3595 $cell = $this->_phpSheet->getCell($columnString . ($row + 1));
3596 if (!$this->_readDataOnly) {
3597 // add style information
3598 $cell->setXfIndex($this->_mapCellXfIndex[$xfIndex]);
3599 }
3600
3601 // add cell
3602 $cell->setValueExplicit($numValue, PHPExcel_Cell_DataType::TYPE_NUMERIC);
3603 }
3604 }
3605
3606
3616 private function _readLabelSst()
3617 {
3618 $length = self::_GetInt2d($this->_data, $this->_pos + 2);
3619 $recordData = $this->_readRecordData($this->_data, $this->_pos + 4, $length);
3620
3621 // move stream pointer to next record
3622 $this->_pos += 4 + $length;
3623
3624 // offset: 0; size: 2; index to row
3625 $row = self::_GetInt2d($recordData, 0);
3626
3627 // offset: 2; size: 2; index to column
3628 $column = self::_GetInt2d($recordData, 2);
3630
3631 // Read cell?
3632 if (($this->getReadFilter() !== NULL) && $this->getReadFilter()->readCell($columnString, $row + 1, $this->_phpSheet->getTitle()) ) {
3633 // offset: 4; size: 2; index to XF record
3634 $xfIndex = self::_GetInt2d($recordData, 4);
3635
3636 // offset: 6; size: 4; index to SST record
3637 $index = self::_GetInt4d($recordData, 6);
3638
3639 // add cell
3640 if (($fmtRuns = $this->_sst[$index]['fmtRuns']) && !$this->_readDataOnly) {
3641 // then we should treat as rich text
3643 $charPos = 0;
3644 $sstCount = count($this->_sst[$index]['fmtRuns']);
3645 for ($i = 0; $i <= $sstCount; ++$i) {
3646 if (isset($fmtRuns[$i])) {
3647 $text = PHPExcel_Shared_String::Substring($this->_sst[$index]['value'], $charPos, $fmtRuns[$i]['charPos'] - $charPos);
3648 $charPos = $fmtRuns[$i]['charPos'];
3649 } else {
3650 $text = PHPExcel_Shared_String::Substring($this->_sst[$index]['value'], $charPos, PHPExcel_Shared_String::CountCharacters($this->_sst[$index]['value']));
3651 }
3652
3654 if ($i == 0) { // first text run, no style
3655 $richText->createText($text);
3656 } else {
3657 $textRun = $richText->createTextRun($text);
3658 if (isset($fmtRuns[$i - 1])) {
3659 if ($fmtRuns[$i - 1]['fontIndex'] < 4) {
3660 $fontIndex = $fmtRuns[$i - 1]['fontIndex'];
3661 } else {
3662 // this has to do with that index 4 is omitted in all BIFF versions for some strange reason
3663 // check the OpenOffice documentation of the FONT record
3664 $fontIndex = $fmtRuns[$i - 1]['fontIndex'] - 1;
3665 }
3666 $textRun->setFont(clone $this->_objFonts[$fontIndex]);
3667 }
3668 }
3669 }
3670 }
3671 $cell = $this->_phpSheet->getCell($columnString . ($row + 1));
3672 $cell->setValueExplicit($richText, PHPExcel_Cell_DataType::TYPE_STRING);
3673 } else {
3674 $cell = $this->_phpSheet->getCell($columnString . ($row + 1));
3675 $cell->setValueExplicit($this->_sst[$index]['value'], PHPExcel_Cell_DataType::TYPE_STRING);
3676 }
3677
3678 if (!$this->_readDataOnly) {
3679 // add style information
3680 $cell->setXfIndex($this->_mapCellXfIndex[$xfIndex]);
3681 }
3682 }
3683 }
3684
3685
3694 private function _readMulRk()
3695 {
3696 $length = self::_GetInt2d($this->_data, $this->_pos + 2);
3697 $recordData = $this->_readRecordData($this->_data, $this->_pos + 4, $length);
3698
3699 // move stream pointer to next record
3700 $this->_pos += 4 + $length;
3701
3702 // offset: 0; size: 2; index to row
3703 $row = self::_GetInt2d($recordData, 0);
3704
3705 // offset: 2; size: 2; index to first column
3706 $colFirst = self::_GetInt2d($recordData, 2);
3707
3708 // offset: var; size: 2; index to last column
3709 $colLast = self::_GetInt2d($recordData, $length - 2);
3710 $columns = $colLast - $colFirst + 1;
3711
3712 // offset within record data
3713 $offset = 4;
3714
3715 for ($i = 0; $i < $columns; ++$i) {
3716 $columnString = PHPExcel_Cell::stringFromColumnIndex($colFirst + $i);
3717
3718 // Read cell?
3719 if (($this->getReadFilter() !== NULL) && $this->getReadFilter()->readCell($columnString, $row + 1, $this->_phpSheet->getTitle()) ) {
3720
3721 // offset: var; size: 2; index to XF record
3722 $xfIndex = self::_GetInt2d($recordData, $offset);
3723
3724 // offset: var; size: 4; RK value
3725 $numValue = self::_GetIEEE754(self::_GetInt4d($recordData, $offset + 2));
3726 $cell = $this->_phpSheet->getCell($columnString . ($row + 1));
3727 if (!$this->_readDataOnly) {
3728 // add style
3729 $cell->setXfIndex($this->_mapCellXfIndex[$xfIndex]);
3730 }
3731
3732 // add cell value
3733 $cell->setValueExplicit($numValue, PHPExcel_Cell_DataType::TYPE_NUMERIC);
3734 }
3735
3736 $offset += 6;
3737 }
3738 }
3739
3740
3749 private function _readNumber()
3750 {
3751 $length = self::_GetInt2d($this->_data, $this->_pos + 2);
3752 $recordData = $this->_readRecordData($this->_data, $this->_pos + 4, $length);
3753
3754 // move stream pointer to next record
3755 $this->_pos += 4 + $length;
3756
3757 // offset: 0; size: 2; index to row
3758 $row = self::_GetInt2d($recordData, 0);
3759
3760 // offset: 2; size 2; index to column
3761 $column = self::_GetInt2d($recordData, 2);
3763
3764 // Read cell?
3765 if (($this->getReadFilter() !== NULL) && $this->getReadFilter()->readCell($columnString, $row + 1, $this->_phpSheet->getTitle()) ) {
3766 // offset 4; size: 2; index to XF record
3767 $xfIndex = self::_GetInt2d($recordData, 4);
3768
3769 $numValue = self::_extractNumber(substr($recordData, 6, 8));
3770
3771 $cell = $this->_phpSheet->getCell($columnString . ($row + 1));
3772 if (!$this->_readDataOnly) {
3773 // add cell style
3774 $cell->setXfIndex($this->_mapCellXfIndex[$xfIndex]);
3775 }
3776
3777 // add cell value
3778 $cell->setValueExplicit($numValue, PHPExcel_Cell_DataType::TYPE_NUMERIC);
3779 }
3780 }
3781
3782
3791 private function _readFormula()
3792 {
3793 $length = self::_GetInt2d($this->_data, $this->_pos + 2);
3794 $recordData = $this->_readRecordData($this->_data, $this->_pos + 4, $length);
3795
3796 // move stream pointer to next record
3797 $this->_pos += 4 + $length;
3798
3799 // offset: 0; size: 2; row index
3800 $row = self::_GetInt2d($recordData, 0);
3801
3802 // offset: 2; size: 2; col index
3803 $column = self::_GetInt2d($recordData, 2);
3805
3806 // offset: 20: size: variable; formula structure
3807 $formulaStructure = substr($recordData, 20);
3808
3809 // offset: 14: size: 2; option flags, recalculate always, recalculate on open etc.
3810 $options = self::_GetInt2d($recordData, 14);
3811
3812 // bit: 0; mask: 0x0001; 1 = recalculate always
3813 // bit: 1; mask: 0x0002; 1 = calculate on open
3814 // bit: 2; mask: 0x0008; 1 = part of a shared formula
3815 $isPartOfSharedFormula = (bool) (0x0008 & $options);
3816
3817 // WARNING:
3818 // We can apparently not rely on $isPartOfSharedFormula. Even when $isPartOfSharedFormula = true
3819 // the formula data may be ordinary formula data, therefore we need to check
3820 // explicitly for the tExp token (0x01)
3821 $isPartOfSharedFormula = $isPartOfSharedFormula && ord($formulaStructure{2}) == 0x01;
3822
3823 if ($isPartOfSharedFormula) {
3824 // part of shared formula which means there will be a formula with a tExp token and nothing else
3825 // get the base cell, grab tExp token
3826 $baseRow = self::_GetInt2d($formulaStructure, 3);
3827 $baseCol = self::_GetInt2d($formulaStructure, 5);
3828 $this->_baseCell = PHPExcel_Cell::stringFromColumnIndex($baseCol). ($baseRow + 1);
3829 }
3830
3831 // Read cell?
3832 if (($this->getReadFilter() !== NULL) && $this->getReadFilter()->readCell($columnString, $row + 1, $this->_phpSheet->getTitle()) ) {
3833
3834 if ($isPartOfSharedFormula) {
3835 // formula is added to this cell after the sheet has been read
3836 $this->_sharedFormulaParts[$columnString . ($row + 1)] = $this->_baseCell;
3837 }
3838
3839 // offset: 16: size: 4; not used
3840
3841 // offset: 4; size: 2; XF index
3842 $xfIndex = self::_GetInt2d($recordData, 4);
3843
3844 // offset: 6; size: 8; result of the formula
3845 if ( (ord($recordData{6}) == 0)
3846 && (ord($recordData{12}) == 255)
3847 && (ord($recordData{13}) == 255) ) {
3848
3849 // String formula. Result follows in appended STRING record
3851
3852 // read possible SHAREDFMLA record
3853 $code = self::_GetInt2d($this->_data, $this->_pos);
3854 if ($code == self::XLS_Type_SHAREDFMLA) {
3855 $this->_readSharedFmla();
3856 }
3857
3858 // read STRING record
3859 $value = $this->_readString();
3860
3861 } elseif ((ord($recordData{6}) == 1)
3862 && (ord($recordData{12}) == 255)
3863 && (ord($recordData{13}) == 255)) {
3864
3865 // Boolean formula. Result is in +2; 0=false, 1=true
3867 $value = (bool) ord($recordData{8});
3868
3869 } elseif ((ord($recordData{6}) == 2)
3870 && (ord($recordData{12}) == 255)
3871 && (ord($recordData{13}) == 255)) {
3872
3873 // Error formula. Error code is in +2
3875 $value = self::_mapErrorCode(ord($recordData{8}));
3876
3877 } elseif ((ord($recordData{6}) == 3)
3878 && (ord($recordData{12}) == 255)
3879 && (ord($recordData{13}) == 255)) {
3880
3881 // Formula result is a null string
3883 $value = '';
3884
3885 } else {
3886
3887 // forumla result is a number, first 14 bytes like _NUMBER record
3889 $value = self::_extractNumber(substr($recordData, 6, 8));
3890
3891 }
3892
3893 $cell = $this->_phpSheet->getCell($columnString . ($row + 1));
3894 if (!$this->_readDataOnly) {
3895 // add cell style
3896 $cell->setXfIndex($this->_mapCellXfIndex[$xfIndex]);
3897 }
3898
3899 // store the formula
3900 if (!$isPartOfSharedFormula) {
3901 // not part of shared formula
3902 // add cell value. If we can read formula, populate with formula, otherwise just used cached value
3903 try {
3904 if ($this->_version != self::XLS_BIFF8) {
3905 throw new PHPExcel_Reader_Exception('Not BIFF8. Can only read BIFF8 formulas');
3906 }
3907 $formula = $this->_getFormulaFromStructure($formulaStructure); // get formula in human language
3908 $cell->setValueExplicit('=' . $formula, PHPExcel_Cell_DataType::TYPE_FORMULA);
3909
3910 } catch (PHPExcel_Exception $e) {
3911 $cell->setValueExplicit($value, $dataType);
3912 }
3913 } else {
3914 if ($this->_version == self::XLS_BIFF8) {
3915 // do nothing at this point, formula id added later in the code
3916 } else {
3917 $cell->setValueExplicit($value, $dataType);
3918 }
3919 }
3920
3921 // store the cached calculated value
3922 $cell->setCalculatedValue($value);
3923 }
3924 }
3925
3926
3932 private function _readSharedFmla()
3933 {
3934 $length = self::_GetInt2d($this->_data, $this->_pos + 2);
3935 $recordData = $this->_readRecordData($this->_data, $this->_pos + 4, $length);
3936
3937 // move stream pointer to next record
3938 $this->_pos += 4 + $length;
3939
3940 // offset: 0, size: 6; cell range address of the area used by the shared formula, not used for anything
3941 $cellRange = substr($recordData, 0, 6);
3942 $cellRange = $this->_readBIFF5CellRangeAddressFixed($cellRange); // note: even BIFF8 uses BIFF5 syntax
3943
3944 // offset: 6, size: 1; not used
3945
3946 // offset: 7, size: 1; number of existing FORMULA records for this shared formula
3947 $no = ord($recordData{7});
3948
3949 // offset: 8, size: var; Binary token array of the shared formula
3950 $formula = substr($recordData, 8);
3951
3952 // at this point we only store the shared formula for later use
3953 $this->_sharedFormulas[$this->_baseCell] = $formula;
3954
3955 }
3956
3957
3965 private function _readString()
3966 {
3967 $length = self::_GetInt2d($this->_data, $this->_pos + 2);
3968 $recordData = $this->_readRecordData($this->_data, $this->_pos + 4, $length);
3969
3970 // move stream pointer to next record
3971 $this->_pos += 4 + $length;
3972
3973 if ($this->_version == self::XLS_BIFF8) {
3974 $string = self::_readUnicodeStringLong($recordData);
3975 $value = $string['value'];
3976 } else {
3977 $string = $this->_readByteStringLong($recordData);
3978 $value = $string['value'];
3979 }
3980
3981 return $value;
3982 }
3983
3984
3993 private function _readBoolErr()
3994 {
3995 $length = self::_GetInt2d($this->_data, $this->_pos + 2);
3996 $recordData = $this->_readRecordData($this->_data, $this->_pos + 4, $length);
3997
3998 // move stream pointer to next record
3999 $this->_pos += 4 + $length;
4000
4001 // offset: 0; size: 2; row index
4002 $row = self::_GetInt2d($recordData, 0);
4003
4004 // offset: 2; size: 2; column index
4005 $column = self::_GetInt2d($recordData, 2);
4007
4008 // Read cell?
4009 if (($this->getReadFilter() !== NULL) && $this->getReadFilter()->readCell($columnString, $row + 1, $this->_phpSheet->getTitle()) ) {
4010 // offset: 4; size: 2; index to XF record
4011 $xfIndex = self::_GetInt2d($recordData, 4);
4012
4013 // offset: 6; size: 1; the boolean value or error value
4014 $boolErr = ord($recordData{6});
4015
4016 // offset: 7; size: 1; 0=boolean; 1=error
4017 $isError = ord($recordData{7});
4018
4019 $cell = $this->_phpSheet->getCell($columnString . ($row + 1));
4020 switch ($isError) {
4021 case 0: // boolean
4022 $value = (bool) $boolErr;
4023
4024 // add cell value
4025 $cell->setValueExplicit($value, PHPExcel_Cell_DataType::TYPE_BOOL);
4026 break;
4027
4028 case 1: // error type
4029 $value = self::_mapErrorCode($boolErr);
4030
4031 // add cell value
4032 $cell->setValueExplicit($value, PHPExcel_Cell_DataType::TYPE_ERROR);
4033 break;
4034 }
4035
4036 if (!$this->_readDataOnly) {
4037 // add cell style
4038 $cell->setXfIndex($this->_mapCellXfIndex[$xfIndex]);
4039 }
4040 }
4041 }
4042
4043
4052 private function _readMulBlank()
4053 {
4054 $length = self::_GetInt2d($this->_data, $this->_pos + 2);
4055 $recordData = $this->_readRecordData($this->_data, $this->_pos + 4, $length);
4056
4057 // move stream pointer to next record
4058 $this->_pos += 4 + $length;
4059
4060 // offset: 0; size: 2; index to row
4061 $row = self::_GetInt2d($recordData, 0);
4062
4063 // offset: 2; size: 2; index to first column
4064 $fc = self::_GetInt2d($recordData, 2);
4065
4066 // offset: 4; size: 2 x nc; list of indexes to XF records
4067 // add style information
4068 if (!$this->_readDataOnly) {
4069 for ($i = 0; $i < $length / 2 - 3; ++$i) {
4070 $columnString = PHPExcel_Cell::stringFromColumnIndex($fc + $i);
4071
4072 // Read cell?
4073 if (($this->getReadFilter() !== NULL) && $this->getReadFilter()->readCell($columnString, $row + 1, $this->_phpSheet->getTitle()) ) {
4074 $xfIndex = self::_GetInt2d($recordData, 4 + 2 * $i);
4075 $this->_phpSheet->getCell($columnString . ($row + 1))->setXfIndex($this->_mapCellXfIndex[$xfIndex]);
4076 }
4077 }
4078 }
4079
4080 // offset: 6; size 2; index to last column (not needed)
4081 }
4082
4083
4094 private function _readLabel()
4095 {
4096 $length = self::_GetInt2d($this->_data, $this->_pos + 2);
4097 $recordData = $this->_readRecordData($this->_data, $this->_pos + 4, $length);
4098
4099 // move stream pointer to next record
4100 $this->_pos += 4 + $length;
4101
4102 // offset: 0; size: 2; index to row
4103 $row = self::_GetInt2d($recordData, 0);
4104
4105 // offset: 2; size: 2; index to column
4106 $column = self::_GetInt2d($recordData, 2);
4108
4109 // Read cell?
4110 if (($this->getReadFilter() !== NULL) && $this->getReadFilter()->readCell($columnString, $row + 1, $this->_phpSheet->getTitle()) ) {
4111 // offset: 4; size: 2; XF index
4112 $xfIndex = self::_GetInt2d($recordData, 4);
4113
4114 // add cell value
4115 // todo: what if string is very long? continue record
4116 if ($this->_version == self::XLS_BIFF8) {
4117 $string = self::_readUnicodeStringLong(substr($recordData, 6));
4118 $value = $string['value'];
4119 } else {
4120 $string = $this->_readByteStringLong(substr($recordData, 6));
4121 $value = $string['value'];
4122 }
4123 $cell = $this->_phpSheet->getCell($columnString . ($row + 1));
4124 $cell->setValueExplicit($value, PHPExcel_Cell_DataType::TYPE_STRING);
4125
4126 if (!$this->_readDataOnly) {
4127 // add cell style
4128 $cell->setXfIndex($this->_mapCellXfIndex[$xfIndex]);
4129 }
4130 }
4131 }
4132
4133
4137 private function _readBlank()
4138 {
4139 $length = self::_GetInt2d($this->_data, $this->_pos + 2);
4140 $recordData = $this->_readRecordData($this->_data, $this->_pos + 4, $length);
4141
4142 // move stream pointer to next record
4143 $this->_pos += 4 + $length;
4144
4145 // offset: 0; size: 2; row index
4146 $row = self::_GetInt2d($recordData, 0);
4147
4148 // offset: 2; size: 2; col index
4149 $col = self::_GetInt2d($recordData, 2);
4150 $columnString = PHPExcel_Cell::stringFromColumnIndex($col);
4151
4152 // Read cell?
4153 if (($this->getReadFilter() !== NULL) && $this->getReadFilter()->readCell($columnString, $row + 1, $this->_phpSheet->getTitle()) ) {
4154 // offset: 4; size: 2; XF index
4155 $xfIndex = self::_GetInt2d($recordData, 4);
4156
4157 // add style information
4158 if (!$this->_readDataOnly) {
4159 $this->_phpSheet->getCell($columnString . ($row + 1))->setXfIndex($this->_mapCellXfIndex[$xfIndex]);
4160 }
4161 }
4162
4163 }
4164
4165
4169 private function _readMsoDrawing()
4170 {
4171 $length = self::_GetInt2d($this->_data, $this->_pos + 2);
4172
4173 // get spliced record data
4174 $splicedRecordData = $this->_getSplicedRecordData();
4175 $recordData = $splicedRecordData['recordData'];
4176
4177 $this->_drawingData .= $recordData;
4178 }
4179
4180
4184 private function _readObj()
4185 {
4186 $length = self::_GetInt2d($this->_data, $this->_pos + 2);
4187 $recordData = $this->_readRecordData($this->_data, $this->_pos + 4, $length);
4188
4189 // move stream pointer to next record
4190 $this->_pos += 4 + $length;
4191
4192 if ($this->_readDataOnly || $this->_version != self::XLS_BIFF8) {
4193 return;
4194 }
4195
4196 // recordData consists of an array of subrecords looking like this:
4197 // ft: 2 bytes; ftCmo type (0x15)
4198 // cb: 2 bytes; size in bytes of ftCmo data
4199 // ot: 2 bytes; Object Type
4200 // id: 2 bytes; Object id number
4201 // grbit: 2 bytes; Option Flags
4202 // data: var; subrecord data
4203
4204 // for now, we are just interested in the second subrecord containing the object type
4205 $ftCmoType = self::_GetInt2d($recordData, 0);
4206 $cbCmoSize = self::_GetInt2d($recordData, 2);
4207 $otObjType = self::_GetInt2d($recordData, 4);
4208 $idObjID = self::_GetInt2d($recordData, 6);
4209 $grbitOpts = self::_GetInt2d($recordData, 6);
4210
4211 $this->_objs[] = array(
4212 'ftCmoType' => $ftCmoType,
4213 'cbCmoSize' => $cbCmoSize,
4214 'otObjType' => $otObjType,
4215 'idObjID' => $idObjID,
4216 'grbitOpts' => $grbitOpts
4217 );
4218 $this->textObjRef = $idObjID;
4219
4220// echo '<b>_readObj()</b><br />';
4221// var_dump(end($this->_objs));
4222// echo '<br />';
4223 }
4224
4225
4229 private function _readWindow2()
4230 {
4231 $length = self::_GetInt2d($this->_data, $this->_pos + 2);
4232 $recordData = $this->_readRecordData($this->_data, $this->_pos + 4, $length);
4233
4234 // move stream pointer to next record
4235 $this->_pos += 4 + $length;
4236
4237 // offset: 0; size: 2; option flags
4238 $options = self::_GetInt2d($recordData, 0);
4239
4240 // offset: 2; size: 2; index to first visible row
4241 $firstVisibleRow = self::_GetInt2d($recordData, 2);
4242
4243 // offset: 4; size: 2; index to first visible colum
4244 $firstVisibleColumn = self::_GetInt2d($recordData, 4);
4245 if ($this->_version === self::XLS_BIFF8) {
4246 // offset: 8; size: 2; not used
4247 // offset: 10; size: 2; cached magnification factor in page break preview (in percent); 0 = Default (60%)
4248 // offset: 12; size: 2; cached magnification factor in normal view (in percent); 0 = Default (100%)
4249 // offset: 14; size: 4; not used
4250 $zoomscaleInPageBreakPreview = self::_GetInt2d($recordData, 10);
4251 if ($zoomscaleInPageBreakPreview === 0) $zoomscaleInPageBreakPreview = 60;
4252 $zoomscaleInNormalView = self::_GetInt2d($recordData, 12);
4253 if ($zoomscaleInNormalView === 0) $zoomscaleInNormalView = 100;
4254 }
4255
4256 // bit: 1; mask: 0x0002; 0 = do not show gridlines, 1 = show gridlines
4257 $showGridlines = (bool) ((0x0002 & $options) >> 1);
4258 $this->_phpSheet->setShowGridlines($showGridlines);
4259
4260 // bit: 2; mask: 0x0004; 0 = do not show headers, 1 = show headers
4261 $showRowColHeaders = (bool) ((0x0004 & $options) >> 2);
4262 $this->_phpSheet->setShowRowColHeaders($showRowColHeaders);
4263
4264 // bit: 3; mask: 0x0008; 0 = panes are not frozen, 1 = panes are frozen
4265 $this->_frozen = (bool) ((0x0008 & $options) >> 3);
4266
4267 // bit: 6; mask: 0x0040; 0 = columns from left to right, 1 = columns from right to left
4268 $this->_phpSheet->setRightToLeft((bool)((0x0040 & $options) >> 6));
4269
4270 // bit: 10; mask: 0x0400; 0 = sheet not active, 1 = sheet active
4271 $isActive = (bool) ((0x0400 & $options) >> 10);
4272 if ($isActive) {
4273 $this->_phpExcel->setActiveSheetIndex($this->_phpExcel->getIndex($this->_phpSheet));
4274 }
4275
4276 // bit: 11; mask: 0x0800; 0 = normal view, 1 = page break view
4277 $isPageBreakPreview = (bool) ((0x0800 & $options) >> 11);
4278
4279 //FIXME: set $firstVisibleRow and $firstVisibleColumn
4280
4281 if ($this->_phpSheet->getSheetView()->getView() !== PHPExcel_Worksheet_SheetView::SHEETVIEW_PAGE_LAYOUT) {
4282 //NOTE: this setting is inferior to page layout view(Excel2007-)
4285 $this->_phpSheet->getSheetView()->setView($view);
4286 if ($this->_version === self::XLS_BIFF8) {
4287 $zoomScale = $isPageBreakPreview? $zoomscaleInPageBreakPreview : $zoomscaleInNormalView;
4288 $this->_phpSheet->getSheetView()->setZoomScale($zoomScale);
4289 $this->_phpSheet->getSheetView()->setZoomScaleNormal($zoomscaleInNormalView);
4290 }
4291 }
4292 }
4293
4297 private function _readPageLayoutView(){
4298 $length = self::_GetInt2d($this->_data, $this->_pos + 2);
4299 $recordData = $this->_readRecordData($this->_data, $this->_pos + 4, $length);
4300
4301 // move stream pointer to next record
4302 $this->_pos += 4 + $length;
4303
4304 //var_dump(unpack("vrt/vgrbitFrt/V2reserved/vwScalePLV/vgrbit", $recordData));
4305
4306 // offset: 0; size: 2; rt
4307 //->ignore
4308 $rt = self::_GetInt2d($recordData, 0);
4309 // offset: 2; size: 2; grbitfr
4310 //->ignore
4311 $grbitFrt = self::_GetInt2d($recordData, 2);
4312 // offset: 4; size: 8; reserved
4313 //->ignore
4314
4315 // offset: 12; size 2; zoom scale
4316 $wScalePLV = self::_GetInt2d($recordData, 12);
4317 // offset: 14; size 2; grbit
4318 $grbit = self::_GetInt2d($recordData, 14);
4319
4320 // decomprise grbit
4321 $fPageLayoutView = $grbit & 0x01;
4322 $fRulerVisible = ($grbit >> 1) & 0x01; //no support
4323 $fWhitespaceHidden = ($grbit >> 3) & 0x01; //no support
4324
4325 if ($fPageLayoutView === 1) {
4326 $this->_phpSheet->getSheetView()->setView(PHPExcel_Worksheet_SheetView::SHEETVIEW_PAGE_LAYOUT);
4327 $this->_phpSheet->getSheetView()->setZoomScale($wScalePLV); //set by Excel2007 only if SHEETVIEW_PAGE_LAYOUT
4328 }
4329 //otherwise, we cannot know whether SHEETVIEW_PAGE_LAYOUT or SHEETVIEW_PAGE_BREAK_PREVIEW.
4330 }
4331
4335 private function _readScl()
4336 {
4337 $length = self::_GetInt2d($this->_data, $this->_pos + 2);
4338 $recordData = $this->_readRecordData($this->_data, $this->_pos + 4, $length);
4339
4340 // move stream pointer to next record
4341 $this->_pos += 4 + $length;
4342
4343 // offset: 0; size: 2; numerator of the view magnification
4344 $numerator = self::_GetInt2d($recordData, 0);
4345
4346 // offset: 2; size: 2; numerator of the view magnification
4347 $denumerator = self::_GetInt2d($recordData, 2);
4348
4349 // set the zoom scale (in percent)
4350 $this->_phpSheet->getSheetView()->setZoomScale($numerator * 100 / $denumerator);
4351 }
4352
4353
4357 private function _readPane()
4358 {
4359 $length = self::_GetInt2d($this->_data, $this->_pos + 2);
4360 $recordData = $this->_readRecordData($this->_data, $this->_pos + 4, $length);
4361
4362 // move stream pointer to next record
4363 $this->_pos += 4 + $length;
4364
4365 if (!$this->_readDataOnly) {
4366 // offset: 0; size: 2; position of vertical split
4367 $px = self::_GetInt2d($recordData, 0);
4368
4369 // offset: 2; size: 2; position of horizontal split
4370 $py = self::_GetInt2d($recordData, 2);
4371
4372 if ($this->_frozen) {
4373 // frozen panes
4374 $this->_phpSheet->freezePane(PHPExcel_Cell::stringFromColumnIndex($px) . ($py + 1));
4375 } else {
4376 // unfrozen panes; split windows; not supported by PHPExcel core
4377 }
4378 }
4379 }
4380
4381
4385 private function _readSelection()
4386 {
4387 $length = self::_GetInt2d($this->_data, $this->_pos + 2);
4388 $recordData = $this->_readRecordData($this->_data, $this->_pos + 4, $length);
4389
4390 // move stream pointer to next record
4391 $this->_pos += 4 + $length;
4392
4393 if (!$this->_readDataOnly) {
4394 // offset: 0; size: 1; pane identifier
4395 $paneId = ord($recordData{0});
4396
4397 // offset: 1; size: 2; index to row of the active cell
4398 $r = self::_GetInt2d($recordData, 1);
4399
4400 // offset: 3; size: 2; index to column of the active cell
4401 $c = self::_GetInt2d($recordData, 3);
4402
4403 // offset: 5; size: 2; index into the following cell range list to the
4404 // entry that contains the active cell
4405 $index = self::_GetInt2d($recordData, 5);
4406
4407 // offset: 7; size: var; cell range address list containing all selected cell ranges
4408 $data = substr($recordData, 7);
4409 $cellRangeAddressList = $this->_readBIFF5CellRangeAddressList($data); // note: also BIFF8 uses BIFF5 syntax
4410
4411 $selectedCells = $cellRangeAddressList['cellRangeAddresses'][0];
4412
4413 // first row '1' + last row '16384' indicates that full column is selected (apparently also in BIFF8!)
4414 if (preg_match('/^([A-Z]+1\:[A-Z]+)16384$/', $selectedCells)) {
4415 $selectedCells = preg_replace('/^([A-Z]+1\:[A-Z]+)16384$/', '${1}1048576', $selectedCells);
4416 }
4417
4418 // first row '1' + last row '65536' indicates that full column is selected
4419 if (preg_match('/^([A-Z]+1\:[A-Z]+)65536$/', $selectedCells)) {
4420 $selectedCells = preg_replace('/^([A-Z]+1\:[A-Z]+)65536$/', '${1}1048576', $selectedCells);
4421 }
4422
4423 // first column 'A' + last column 'IV' indicates that full row is selected
4424 if (preg_match('/^(A[0-9]+\:)IV([0-9]+)$/', $selectedCells)) {
4425 $selectedCells = preg_replace('/^(A[0-9]+\:)IV([0-9]+)$/', '${1}XFD${2}', $selectedCells);
4426 }
4427
4428 $this->_phpSheet->setSelectedCells($selectedCells);
4429 }
4430 }
4431
4432
4433 private function _includeCellRangeFiltered($cellRangeAddress)
4434 {
4435 $includeCellRange = true;
4436 if ($this->getReadFilter() !== NULL) {
4437 $includeCellRange = false;
4438 $rangeBoundaries = PHPExcel_Cell::getRangeBoundaries($cellRangeAddress);
4439 $rangeBoundaries[1][0]++;
4440 for ($row = $rangeBoundaries[0][1]; $row <= $rangeBoundaries[1][1]; $row++) {
4441 for ($column = $rangeBoundaries[0][0]; $column != $rangeBoundaries[1][0]; $column++) {
4442 if ($this->getReadFilter()->readCell($column, $row, $this->_phpSheet->getTitle())) {
4443 $includeCellRange = true;
4444 break 2;
4445 }
4446 }
4447 }
4448 }
4449 return $includeCellRange;
4450 }
4451
4452
4462 private function _readMergedCells()
4463 {
4464 $length = self::_GetInt2d($this->_data, $this->_pos + 2);
4465 $recordData = $this->_readRecordData($this->_data, $this->_pos + 4, $length);
4466
4467 // move stream pointer to next record
4468 $this->_pos += 4 + $length;
4469
4470 if ($this->_version == self::XLS_BIFF8 && !$this->_readDataOnly) {
4471 $cellRangeAddressList = $this->_readBIFF8CellRangeAddressList($recordData);
4472 foreach ($cellRangeAddressList['cellRangeAddresses'] as $cellRangeAddress) {
4473 if ((strpos($cellRangeAddress,':') !== FALSE) &&
4474 ($this->_includeCellRangeFiltered($cellRangeAddress))) {
4475 $this->_phpSheet->mergeCells($cellRangeAddress);
4476 }
4477 }
4478 }
4479 }
4480
4481
4485 private function _readHyperLink()
4486 {
4487 $length = self::_GetInt2d($this->_data, $this->_pos + 2);
4488 $recordData = $this->_readRecordData($this->_data, $this->_pos + 4, $length);
4489
4490 // move stream pointer forward to next record
4491 $this->_pos += 4 + $length;
4492
4493 if (!$this->_readDataOnly) {
4494 // offset: 0; size: 8; cell range address of all cells containing this hyperlink
4495 try {
4496 $cellRange = $this->_readBIFF8CellRangeAddressFixed($recordData, 0, 8);
4497 } catch (PHPExcel_Exception $e) {
4498 return;
4499 }
4500
4501 // offset: 8, size: 16; GUID of StdLink
4502
4503 // offset: 24, size: 4; unknown value
4504
4505 // offset: 28, size: 4; option flags
4506
4507 // bit: 0; mask: 0x00000001; 0 = no link or extant, 1 = file link or URL
4508 $isFileLinkOrUrl = (0x00000001 & self::_GetInt2d($recordData, 28)) >> 0;
4509
4510 // bit: 1; mask: 0x00000002; 0 = relative path, 1 = absolute path or URL
4511 $isAbsPathOrUrl = (0x00000001 & self::_GetInt2d($recordData, 28)) >> 1;
4512
4513 // bit: 2 (and 4); mask: 0x00000014; 0 = no description
4514 $hasDesc = (0x00000014 & self::_GetInt2d($recordData, 28)) >> 2;
4515
4516 // bit: 3; mask: 0x00000008; 0 = no text, 1 = has text
4517 $hasText = (0x00000008 & self::_GetInt2d($recordData, 28)) >> 3;
4518
4519 // bit: 7; mask: 0x00000080; 0 = no target frame, 1 = has target frame
4520 $hasFrame = (0x00000080 & self::_GetInt2d($recordData, 28)) >> 7;
4521
4522 // bit: 8; mask: 0x00000100; 0 = file link or URL, 1 = UNC path (inc. server name)
4523 $isUNC = (0x00000100 & self::_GetInt2d($recordData, 28)) >> 8;
4524
4525 // offset within record data
4526 $offset = 32;
4527
4528 if ($hasDesc) {
4529 // offset: 32; size: var; character count of description text
4530 $dl = self::_GetInt4d($recordData, 32);
4531 // offset: 36; size: var; character array of description text, no Unicode string header, always 16-bit characters, zero terminated
4532 $desc = self::_encodeUTF16(substr($recordData, 36, 2 * ($dl - 1)), false);
4533 $offset += 4 + 2 * $dl;
4534 }
4535 if ($hasFrame) {
4536 $fl = self::_GetInt4d($recordData, $offset);
4537 $offset += 4 + 2 * $fl;
4538 }
4539
4540 // detect type of hyperlink (there are 4 types)
4541 $hyperlinkType = null;
4542
4543 if ($isUNC) {
4544 $hyperlinkType = 'UNC';
4545 } else if (!$isFileLinkOrUrl) {
4546 $hyperlinkType = 'workbook';
4547 } else if (ord($recordData{$offset}) == 0x03) {
4548 $hyperlinkType = 'local';
4549 } else if (ord($recordData{$offset}) == 0xE0) {
4550 $hyperlinkType = 'URL';
4551 }
4552
4553 switch ($hyperlinkType) {
4554 case 'URL':
4555 // section 5.58.2: Hyperlink containing a URL
4556 // e.g. http://example.org/index.php
4557
4558 // offset: var; size: 16; GUID of URL Moniker
4559 $offset += 16;
4560 // offset: var; size: 4; size (in bytes) of character array of the URL including trailing zero word
4561 $us = self::_GetInt4d($recordData, $offset);
4562 $offset += 4;
4563 // offset: var; size: $us; character array of the URL, no Unicode string header, always 16-bit characters, zero-terminated
4564 $url = self::_encodeUTF16(substr($recordData, $offset, $us - 2), false);
4565 $nullOffset = strpos($url, 0x00);
4566 if ($nullOffset)
4567 $url = substr($url,0,$nullOffset);
4568 $url .= $hasText ? '#' : '';
4569 $offset += $us;
4570 break;
4571
4572 case 'local':
4573 // section 5.58.3: Hyperlink to local file
4574 // examples:
4575 // mydoc.txt
4576 // ../../somedoc.xls#Sheet!A1
4577
4578 // offset: var; size: 16; GUI of File Moniker
4579 $offset += 16;
4580
4581 // offset: var; size: 2; directory up-level count.
4582 $upLevelCount = self::_GetInt2d($recordData, $offset);
4583 $offset += 2;
4584
4585 // offset: var; size: 4; character count of the shortened file path and name, including trailing zero word
4586 $sl = self::_GetInt4d($recordData, $offset);
4587 $offset += 4;
4588
4589 // offset: var; size: sl; character array of the shortened file path and name in 8.3-DOS-format (compressed Unicode string)
4590 $shortenedFilePath = substr($recordData, $offset, $sl);
4591 $shortenedFilePath = self::_encodeUTF16($shortenedFilePath, true);
4592 $shortenedFilePath = substr($shortenedFilePath, 0, -1); // remove trailing zero
4593
4594 $offset += $sl;
4595
4596 // offset: var; size: 24; unknown sequence
4597 $offset += 24;
4598
4599 // extended file path
4600 // offset: var; size: 4; size of the following file link field including string lenth mark
4601 $sz = self::_GetInt4d($recordData, $offset);
4602 $offset += 4;
4603
4604 // only present if $sz > 0
4605 if ($sz > 0) {
4606 // offset: var; size: 4; size of the character array of the extended file path and name
4607 $xl = self::_GetInt4d($recordData, $offset);
4608 $offset += 4;
4609
4610 // offset: var; size 2; unknown
4611 $offset += 2;
4612
4613 // offset: var; size $xl; character array of the extended file path and name.
4614 $extendedFilePath = substr($recordData, $offset, $xl);
4615 $extendedFilePath = self::_encodeUTF16($extendedFilePath, false);
4616 $offset += $xl;
4617 }
4618
4619 // construct the path
4620 $url = str_repeat('..\\', $upLevelCount);
4621 $url .= ($sz > 0) ?
4622 $extendedFilePath : $shortenedFilePath; // use extended path if available
4623 $url .= $hasText ? '#' : '';
4624
4625 break;
4626
4627
4628 case 'UNC':
4629 // section 5.58.4: Hyperlink to a File with UNC (Universal Naming Convention) Path
4630 // todo: implement
4631 return;
4632
4633 case 'workbook':
4634 // section 5.58.5: Hyperlink to the Current Workbook
4635 // e.g. Sheet2!B1:C2, stored in text mark field
4636 $url = 'sheet://';
4637 break;
4638
4639 default:
4640 return;
4641
4642 }
4643
4644 if ($hasText) {
4645 // offset: var; size: 4; character count of text mark including trailing zero word
4646 $tl = self::_GetInt4d($recordData, $offset);
4647 $offset += 4;
4648 // offset: var; size: var; character array of the text mark without the # sign, no Unicode header, always 16-bit characters, zero-terminated
4649 $text = self::_encodeUTF16(substr($recordData, $offset, 2 * ($tl - 1)), false);
4650 $url .= $text;
4651 }
4652
4653 // apply the hyperlink to all the relevant cells
4654 foreach (PHPExcel_Cell::extractAllCellReferencesInRange($cellRange) as $coordinate) {
4655 $this->_phpSheet->getCell($coordinate)->getHyperLink()->setUrl($url);
4656 }
4657 }
4658 }
4659
4660
4664 private function _readDataValidations()
4665 {
4666 $length = self::_GetInt2d($this->_data, $this->_pos + 2);
4667 $recordData = $this->_readRecordData($this->_data, $this->_pos + 4, $length);
4668
4669 // move stream pointer forward to next record
4670 $this->_pos += 4 + $length;
4671 }
4672
4673
4677 private function _readDataValidation()
4678 {
4679 $length = self::_GetInt2d($this->_data, $this->_pos + 2);
4680 $recordData = $this->_readRecordData($this->_data, $this->_pos + 4, $length);
4681
4682 // move stream pointer forward to next record
4683 $this->_pos += 4 + $length;
4684
4685 if ($this->_readDataOnly) {
4686 return;
4687 }
4688
4689 // offset: 0; size: 4; Options
4690 $options = self::_GetInt4d($recordData, 0);
4691
4692 // bit: 0-3; mask: 0x0000000F; type
4693 $type = (0x0000000F & $options) >> 0;
4694 switch ($type) {
4703 }
4704
4705 // bit: 4-6; mask: 0x00000070; error type
4706 $errorStyle = (0x00000070 & $options) >> 4;
4707 switch ($errorStyle) {
4708 case 0x00: $errorStyle = PHPExcel_Cell_DataValidation::STYLE_STOP; break;
4709 case 0x01: $errorStyle = PHPExcel_Cell_DataValidation::STYLE_WARNING; break;
4710 case 0x02: $errorStyle = PHPExcel_Cell_DataValidation::STYLE_INFORMATION; break;
4711 }
4712
4713 // bit: 7; mask: 0x00000080; 1= formula is explicit (only applies to list)
4714 // I have only seen cases where this is 1
4715 $explicitFormula = (0x00000080 & $options) >> 7;
4716
4717 // bit: 8; mask: 0x00000100; 1= empty cells allowed
4718 $allowBlank = (0x00000100 & $options) >> 8;
4719
4720 // bit: 9; mask: 0x00000200; 1= suppress drop down arrow in list type validity
4721 $suppressDropDown = (0x00000200 & $options) >> 9;
4722
4723 // bit: 18; mask: 0x00040000; 1= show prompt box if cell selected
4724 $showInputMessage = (0x00040000 & $options) >> 18;
4725
4726 // bit: 19; mask: 0x00080000; 1= show error box if invalid values entered
4727 $showErrorMessage = (0x00080000 & $options) >> 19;
4728
4729 // bit: 20-23; mask: 0x00F00000; condition operator
4730 $operator = (0x00F00000 & $options) >> 20;
4731 switch ($operator) {
4732 case 0x00: $operator = PHPExcel_Cell_DataValidation::OPERATOR_BETWEEN ; break;
4733 case 0x01: $operator = PHPExcel_Cell_DataValidation::OPERATOR_NOTBETWEEN ; break;
4734 case 0x02: $operator = PHPExcel_Cell_DataValidation::OPERATOR_EQUAL ; break;
4735 case 0x03: $operator = PHPExcel_Cell_DataValidation::OPERATOR_NOTEQUAL ; break;
4736 case 0x04: $operator = PHPExcel_Cell_DataValidation::OPERATOR_GREATERTHAN ; break;
4737 case 0x05: $operator = PHPExcel_Cell_DataValidation::OPERATOR_LESSTHAN ; break;
4739 case 0x07: $operator = PHPExcel_Cell_DataValidation::OPERATOR_LESSTHANOREQUAL ; break;
4740 }
4741
4742 // offset: 4; size: var; title of the prompt box
4743 $offset = 4;
4744 $string = self::_readUnicodeStringLong(substr($recordData, $offset));
4745 $promptTitle = $string['value'] !== chr(0) ?
4746 $string['value'] : '';
4747 $offset += $string['size'];
4748
4749 // offset: var; size: var; title of the error box
4750 $string = self::_readUnicodeStringLong(substr($recordData, $offset));
4751 $errorTitle = $string['value'] !== chr(0) ?
4752 $string['value'] : '';
4753 $offset += $string['size'];
4754
4755 // offset: var; size: var; text of the prompt box
4756 $string = self::_readUnicodeStringLong(substr($recordData, $offset));
4757 $prompt = $string['value'] !== chr(0) ?
4758 $string['value'] : '';
4759 $offset += $string['size'];
4760
4761 // offset: var; size: var; text of the error box
4762 $string = self::_readUnicodeStringLong(substr($recordData, $offset));
4763 $error = $string['value'] !== chr(0) ?
4764 $string['value'] : '';
4765 $offset += $string['size'];
4766
4767 // offset: var; size: 2; size of the formula data for the first condition
4768 $sz1 = self::_GetInt2d($recordData, $offset);
4769 $offset += 2;
4770
4771 // offset: var; size: 2; not used
4772 $offset += 2;
4773
4774 // offset: var; size: $sz1; formula data for first condition (without size field)
4775 $formula1 = substr($recordData, $offset, $sz1);
4776 $formula1 = pack('v', $sz1) . $formula1; // prepend the length
4777 try {
4778 $formula1 = $this->_getFormulaFromStructure($formula1);
4779
4780 // in list type validity, null characters are used as item separators
4782 $formula1 = str_replace(chr(0), ',', $formula1);
4783 }
4784 } catch (PHPExcel_Exception $e) {
4785 return;
4786 }
4787 $offset += $sz1;
4788
4789 // offset: var; size: 2; size of the formula data for the first condition
4790 $sz2 = self::_GetInt2d($recordData, $offset);
4791 $offset += 2;
4792
4793 // offset: var; size: 2; not used
4794 $offset += 2;
4795
4796 // offset: var; size: $sz2; formula data for second condition (without size field)
4797 $formula2 = substr($recordData, $offset, $sz2);
4798 $formula2 = pack('v', $sz2) . $formula2; // prepend the length
4799 try {
4800 $formula2 = $this->_getFormulaFromStructure($formula2);
4801 } catch (PHPExcel_Exception $e) {
4802 return;
4803 }
4804 $offset += $sz2;
4805
4806 // offset: var; size: var; cell range address list with
4807 $cellRangeAddressList = $this->_readBIFF8CellRangeAddressList(substr($recordData, $offset));
4808 $cellRangeAddresses = $cellRangeAddressList['cellRangeAddresses'];
4809
4810 foreach ($cellRangeAddresses as $cellRange) {
4811 $stRange = $this->_phpSheet->shrinkRangeToFit($cellRange);
4813 foreach ($stRange as $coordinate) {
4814 $objValidation = $this->_phpSheet->getCell($coordinate)->getDataValidation();
4815 $objValidation->setType($type);
4816 $objValidation->setErrorStyle($errorStyle);
4817 $objValidation->setAllowBlank((bool)$allowBlank);
4818 $objValidation->setShowInputMessage((bool)$showInputMessage);
4819 $objValidation->setShowErrorMessage((bool)$showErrorMessage);
4820 $objValidation->setShowDropDown(!$suppressDropDown);
4821 $objValidation->setOperator($operator);
4822 $objValidation->setErrorTitle($errorTitle);
4823 $objValidation->setError($error);
4824 $objValidation->setPromptTitle($promptTitle);
4825 $objValidation->setPrompt($prompt);
4826 $objValidation->setFormula1($formula1);
4827 $objValidation->setFormula2($formula2);
4828 }
4829 }
4830
4831 }
4832
4833
4837 private function _readSheetLayout()
4838 {
4839 $length = self::_GetInt2d($this->_data, $this->_pos + 2);
4840 $recordData = $this->_readRecordData($this->_data, $this->_pos + 4, $length);
4841
4842 // move stream pointer to next record
4843 $this->_pos += 4 + $length;
4844
4845 // local pointer in record data
4846 $offset = 0;
4847
4848 if (!$this->_readDataOnly) {
4849 // offset: 0; size: 2; repeated record identifier 0x0862
4850
4851 // offset: 2; size: 10; not used
4852
4853 // offset: 12; size: 4; size of record data
4854 // Excel 2003 uses size of 0x14 (documented), Excel 2007 uses size of 0x28 (not documented?)
4855 $sz = self::_GetInt4d($recordData, 12);
4856
4857 switch ($sz) {
4858 case 0x14:
4859 // offset: 16; size: 2; color index for sheet tab
4860 $colorIndex = self::_GetInt2d($recordData, 16);
4861 $color = self::_readColor($colorIndex,$this->_palette,$this->_version);
4862 $this->_phpSheet->getTabColor()->setRGB($color['rgb']);
4863 break;
4864
4865 case 0x28:
4866 // TODO: Investigate structure for .xls SHEETLAYOUT record as saved by MS Office Excel 2007
4867 return;
4868 break;
4869 }
4870 }
4871 }
4872
4873
4877 private function _readSheetProtection()
4878 {
4879 $length = self::_GetInt2d($this->_data, $this->_pos + 2);
4880 $recordData = $this->_readRecordData($this->_data, $this->_pos + 4, $length);
4881
4882 // move stream pointer to next record
4883 $this->_pos += 4 + $length;
4884
4885 if ($this->_readDataOnly) {
4886 return;
4887 }
4888
4889 // offset: 0; size: 2; repeated record header
4890
4891 // offset: 2; size: 2; FRT cell reference flag (=0 currently)
4892
4893 // offset: 4; size: 8; Currently not used and set to 0
4894
4895 // offset: 12; size: 2; Shared feature type index (2=Enhanced Protetion, 4=SmartTag)
4896 $isf = self::_GetInt2d($recordData, 12);
4897 if ($isf != 2) {
4898 return;
4899 }
4900
4901 // offset: 14; size: 1; =1 since this is a feat header
4902
4903 // offset: 15; size: 4; size of rgbHdrSData
4904
4905 // rgbHdrSData, assume "Enhanced Protection"
4906 // offset: 19; size: 2; option flags
4907 $options = self::_GetInt2d($recordData, 19);
4908
4909 // bit: 0; mask 0x0001; 1 = user may edit objects, 0 = users must not edit objects
4910 $bool = (0x0001 & $options) >> 0;
4911 $this->_phpSheet->getProtection()->setObjects(!$bool);
4912
4913 // bit: 1; mask 0x0002; edit scenarios
4914 $bool = (0x0002 & $options) >> 1;
4915 $this->_phpSheet->getProtection()->setScenarios(!$bool);
4916
4917 // bit: 2; mask 0x0004; format cells
4918 $bool = (0x0004 & $options) >> 2;
4919 $this->_phpSheet->getProtection()->setFormatCells(!$bool);
4920
4921 // bit: 3; mask 0x0008; format columns
4922 $bool = (0x0008 & $options) >> 3;
4923 $this->_phpSheet->getProtection()->setFormatColumns(!$bool);
4924
4925 // bit: 4; mask 0x0010; format rows
4926 $bool = (0x0010 & $options) >> 4;
4927 $this->_phpSheet->getProtection()->setFormatRows(!$bool);
4928
4929 // bit: 5; mask 0x0020; insert columns
4930 $bool = (0x0020 & $options) >> 5;
4931 $this->_phpSheet->getProtection()->setInsertColumns(!$bool);
4932
4933 // bit: 6; mask 0x0040; insert rows
4934 $bool = (0x0040 & $options) >> 6;
4935 $this->_phpSheet->getProtection()->setInsertRows(!$bool);
4936
4937 // bit: 7; mask 0x0080; insert hyperlinks
4938 $bool = (0x0080 & $options) >> 7;
4939 $this->_phpSheet->getProtection()->setInsertHyperlinks(!$bool);
4940
4941 // bit: 8; mask 0x0100; delete columns
4942 $bool = (0x0100 & $options) >> 8;
4943 $this->_phpSheet->getProtection()->setDeleteColumns(!$bool);
4944
4945 // bit: 9; mask 0x0200; delete rows
4946 $bool = (0x0200 & $options) >> 9;
4947 $this->_phpSheet->getProtection()->setDeleteRows(!$bool);
4948
4949 // bit: 10; mask 0x0400; select locked cells
4950 $bool = (0x0400 & $options) >> 10;
4951 $this->_phpSheet->getProtection()->setSelectLockedCells(!$bool);
4952
4953 // bit: 11; mask 0x0800; sort cell range
4954 $bool = (0x0800 & $options) >> 11;
4955 $this->_phpSheet->getProtection()->setSort(!$bool);
4956
4957 // bit: 12; mask 0x1000; auto filter
4958 $bool = (0x1000 & $options) >> 12;
4959 $this->_phpSheet->getProtection()->setAutoFilter(!$bool);
4960
4961 // bit: 13; mask 0x2000; pivot tables
4962 $bool = (0x2000 & $options) >> 13;
4963 $this->_phpSheet->getProtection()->setPivotTables(!$bool);
4964
4965 // bit: 14; mask 0x4000; select unlocked cells
4966 $bool = (0x4000 & $options) >> 14;
4967 $this->_phpSheet->getProtection()->setSelectUnlockedCells(!$bool);
4968
4969 // offset: 21; size: 2; not used
4970 }
4971
4972
4978 private function _readRangeProtection()
4979 {
4980 $length = self::_GetInt2d($this->_data, $this->_pos + 2);
4981 $recordData = $this->_readRecordData($this->_data, $this->_pos + 4, $length);
4982
4983 // move stream pointer to next record
4984 $this->_pos += 4 + $length;
4985
4986 // local pointer in record data
4987 $offset = 0;
4988
4989 if (!$this->_readDataOnly) {
4990 $offset += 12;
4991
4992 // offset: 12; size: 2; shared feature type, 2 = enhanced protection, 4 = smart tag
4993 $isf = self::_GetInt2d($recordData, 12);
4994 if ($isf != 2) {
4995 // we only read FEAT records of type 2
4996 return;
4997 }
4998 $offset += 2;
4999
5000 $offset += 5;
5001
5002 // offset: 19; size: 2; count of ref ranges this feature is on
5003 $cref = self::_GetInt2d($recordData, 19);
5004 $offset += 2;
5005
5006 $offset += 6;
5007
5008 // offset: 27; size: 8 * $cref; list of cell ranges (like in hyperlink record)
5009 $cellRanges = array();
5010 for ($i = 0; $i < $cref; ++$i) {
5011 try {
5012 $cellRange = $this->_readBIFF8CellRangeAddressFixed(substr($recordData, 27 + 8 * $i, 8));
5013 } catch (PHPExcel_Exception $e) {
5014 return;
5015 }
5016 $cellRanges[] = $cellRange;
5017 $offset += 8;
5018 }
5019
5020 // offset: var; size: var; variable length of feature specific data
5021 $rgbFeat = substr($recordData, $offset);
5022 $offset += 4;
5023
5024 // offset: var; size: 4; the encrypted password (only 16-bit although field is 32-bit)
5025 $wPassword = self::_GetInt4d($recordData, $offset);
5026 $offset += 4;
5027
5028 // Apply range protection to sheet
5029 if ($cellRanges) {
5030 $this->_phpSheet->protectCells(implode(' ', $cellRanges), strtoupper(dechex($wPassword)), true);
5031 }
5032 }
5033 }
5034
5035
5039 private function _readImData()
5040 {
5041 $length = self::_GetInt2d($this->_data, $this->_pos + 2);
5042
5043 // get spliced record data
5044 $splicedRecordData = $this->_getSplicedRecordData();
5045 $recordData = $splicedRecordData['recordData'];
5046
5047 // UNDER CONSTRUCTION
5048
5049 // offset: 0; size: 2; image format
5050 $cf = self::_GetInt2d($recordData, 0);
5051
5052 // offset: 2; size: 2; environment from which the file was written
5053 $env = self::_GetInt2d($recordData, 2);
5054
5055 // offset: 4; size: 4; length of the image data
5056 $lcb = self::_GetInt4d($recordData, 4);
5057
5058 // offset: 8; size: var; image data
5059 $iData = substr($recordData, 8);
5060
5061 switch ($cf) {
5062 case 0x09: // Windows bitmap format
5063 // BITMAPCOREINFO
5064 // 1. BITMAPCOREHEADER
5065 // offset: 0; size: 4; bcSize, Specifies the number of bytes required by the structure
5066 $bcSize = self::_GetInt4d($iData, 0);
5067// var_dump($bcSize);
5068
5069 // offset: 4; size: 2; bcWidth, specifies the width of the bitmap, in pixels
5070 $bcWidth = self::_GetInt2d($iData, 4);
5071// var_dump($bcWidth);
5072
5073 // offset: 6; size: 2; bcHeight, specifies the height of the bitmap, in pixels.
5074 $bcHeight = self::_GetInt2d($iData, 6);
5075// var_dump($bcHeight);
5076 $ih = imagecreatetruecolor($bcWidth, $bcHeight);
5077
5078 // offset: 8; size: 2; bcPlanes, specifies the number of planes for the target device. This value must be 1
5079
5080 // offset: 10; size: 2; bcBitCount specifies the number of bits-per-pixel. This value must be 1, 4, 8, or 24
5081 $bcBitCount = self::_GetInt2d($iData, 10);
5082// var_dump($bcBitCount);
5083
5084 $rgbString = substr($iData, 12);
5085 $rgbTriples = array();
5086 while (strlen($rgbString) > 0) {
5087 $rgbTriples[] = unpack('Cb/Cg/Cr', $rgbString);
5088 $rgbString = substr($rgbString, 3);
5089 }
5090 $x = 0;
5091 $y = 0;
5092 foreach ($rgbTriples as $i => $rgbTriple) {
5093 $color = imagecolorallocate($ih, $rgbTriple['r'], $rgbTriple['g'], $rgbTriple['b']);
5094 imagesetpixel($ih, $x, $bcHeight - 1 - $y, $color);
5095 $x = ($x + 1) % $bcWidth;
5096 $y = $y + floor(($x + 1) / $bcWidth);
5097 }
5098 //imagepng($ih, 'image.png');
5099
5100 $drawing = new PHPExcel_Worksheet_Drawing();
5101 $drawing->setPath($filename);
5102 $drawing->setWorksheet($this->_phpSheet);
5103
5104 break;
5105
5106 case 0x02: // Windows metafile or Macintosh PICT format
5107 case 0x0e: // native format
5108 default;
5109 break;
5110
5111 }
5112
5113 // _getSplicedRecordData() takes care of moving current position in data stream
5114 }
5115
5116
5122 private function _readContinue()
5123 {
5124 $length = self::_GetInt2d($this->_data, $this->_pos + 2);
5125 $recordData = $this->_readRecordData($this->_data, $this->_pos + 4, $length);
5126
5127 // check if we are reading drawing data
5128 // this is in case a free CONTINUE record occurs in other circumstances we are unaware of
5129 if ($this->_drawingData == '') {
5130 // move stream pointer to next record
5131 $this->_pos += 4 + $length;
5132
5133 return;
5134 }
5135
5136 // check if record data is at least 4 bytes long, otherwise there is no chance this is MSODRAWING data
5137 if ($length < 4) {
5138 // move stream pointer to next record
5139 $this->_pos += 4 + $length;
5140
5141 return;
5142 }
5143
5144 // dirty check to see if CONTINUE record could be a camouflaged MSODRAWING record
5145 // look inside CONTINUE record to see if it looks like a part of an Escher stream
5146 // we know that Escher stream may be split at least at
5147 // 0xF003 MsofbtSpgrContainer
5148 // 0xF004 MsofbtSpContainer
5149 // 0xF00D MsofbtClientTextbox
5150 $validSplitPoints = array(0xF003, 0xF004, 0xF00D); // add identifiers if we find more
5151
5152 $splitPoint = self::_GetInt2d($recordData, 2);
5153 if (in_array($splitPoint, $validSplitPoints)) {
5154 // get spliced record data (and move pointer to next record)
5155 $splicedRecordData = $this->_getSplicedRecordData();
5156 $this->_drawingData .= $splicedRecordData['recordData'];
5157
5158 return;
5159 }
5160
5161 // move stream pointer to next record
5162 $this->_pos += 4 + $length;
5163
5164 }
5165
5166
5175 private function _getSplicedRecordData()
5176 {
5177 $data = '';
5178 $spliceOffsets = array();
5179
5180 $i = 0;
5181 $spliceOffsets[0] = 0;
5182
5183 do {
5184 ++$i;
5185
5186 // offset: 0; size: 2; identifier
5187 $identifier = self::_GetInt2d($this->_data, $this->_pos);
5188 // offset: 2; size: 2; length
5189 $length = self::_GetInt2d($this->_data, $this->_pos + 2);
5190 $data .= $this->_readRecordData($this->_data, $this->_pos + 4, $length);
5191
5192 $spliceOffsets[$i] = $spliceOffsets[$i - 1] + $length;
5193
5194 $this->_pos += 4 + $length;
5195 $nextIdentifier = self::_GetInt2d($this->_data, $this->_pos);
5196 }
5197 while ($nextIdentifier == self::XLS_Type_CONTINUE);
5198
5199 $splicedData = array(
5200 'recordData' => $data,
5201 'spliceOffsets' => $spliceOffsets,
5202 );
5203
5204 return $splicedData;
5205
5206 }
5207
5208
5216 private function _getFormulaFromStructure($formulaStructure, $baseCell = 'A1')
5217 {
5218 // offset: 0; size: 2; size of the following formula data
5219 $sz = self::_GetInt2d($formulaStructure, 0);
5220
5221 // offset: 2; size: sz
5222 $formulaData = substr($formulaStructure, 2, $sz);
5223
5224 // for debug: dump the formula data
5225 //echo '<xmp>';
5226 //echo 'size: ' . $sz . "\n";
5227 //echo 'the entire formula data: ';
5228 //Debug::dump($formulaData);
5229 //echo "\n----\n";
5230
5231 // offset: 2 + sz; size: variable (optional)
5232 if (strlen($formulaStructure) > 2 + $sz) {
5233 $additionalData = substr($formulaStructure, 2 + $sz);
5234
5235 // for debug: dump the additional data
5236 //echo 'the entire additional data: ';
5237 //Debug::dump($additionalData);
5238 //echo "\n----\n";
5239
5240 } else {
5241 $additionalData = '';
5242 }
5243
5244 return $this->_getFormulaFromData($formulaData, $additionalData, $baseCell);
5245 }
5246
5247
5256 private function _getFormulaFromData($formulaData, $additionalData = '', $baseCell = 'A1')
5257 {
5258 // start parsing the formula data
5259 $tokens = array();
5260
5261 while (strlen($formulaData) > 0 and $token = $this->_getNextToken($formulaData, $baseCell)) {
5262 $tokens[] = $token;
5263 $formulaData = substr($formulaData, $token['size']);
5264
5265 // for debug: dump the token
5266 //var_dump($token);
5267 }
5268
5269 $formulaString = $this->_createFormulaFromTokens($tokens, $additionalData);
5270
5271 return $formulaString;
5272 }
5273
5274
5283 private function _createFormulaFromTokens($tokens, $additionalData)
5284 {
5285 // empty formula?
5286 if (empty($tokens)) {
5287 return '';
5288 }
5289
5290 $formulaStrings = array();
5291 foreach ($tokens as $token) {
5292 // initialize spaces
5293 $space0 = isset($space0) ? $space0 : ''; // spaces before next token, not tParen
5294 $space1 = isset($space1) ? $space1 : ''; // carriage returns before next token, not tParen
5295 $space2 = isset($space2) ? $space2 : ''; // spaces before opening parenthesis
5296 $space3 = isset($space3) ? $space3 : ''; // carriage returns before opening parenthesis
5297 $space4 = isset($space4) ? $space4 : ''; // spaces before closing parenthesis
5298 $space5 = isset($space5) ? $space5 : ''; // carriage returns before closing parenthesis
5299
5300 switch ($token['name']) {
5301 case 'tAdd': // addition
5302 case 'tConcat': // addition
5303 case 'tDiv': // division
5304 case 'tEQ': // equality
5305 case 'tGE': // greater than or equal
5306 case 'tGT': // greater than
5307 case 'tIsect': // intersection
5308 case 'tLE': // less than or equal
5309 case 'tList': // less than or equal
5310 case 'tLT': // less than
5311 case 'tMul': // multiplication
5312 case 'tNE': // multiplication
5313 case 'tPower': // power
5314 case 'tRange': // range
5315 case 'tSub': // subtraction
5316 $op2 = array_pop($formulaStrings);
5317 $op1 = array_pop($formulaStrings);
5318 $formulaStrings[] = "$op1$space1$space0{$token['data']}$op2";
5319 unset($space0, $space1);
5320 break;
5321 case 'tUplus': // unary plus
5322 case 'tUminus': // unary minus
5323 $op = array_pop($formulaStrings);
5324 $formulaStrings[] = "$space1$space0{$token['data']}$op";
5325 unset($space0, $space1);
5326 break;
5327 case 'tPercent': // percent sign
5328 $op = array_pop($formulaStrings);
5329 $formulaStrings[] = "$op$space1$space0{$token['data']}";
5330 unset($space0, $space1);
5331 break;
5332 case 'tAttrVolatile': // indicates volatile function
5333 case 'tAttrIf':
5334 case 'tAttrSkip':
5335 case 'tAttrChoose':
5336 // token is only important for Excel formula evaluator
5337 // do nothing
5338 break;
5339 case 'tAttrSpace': // space / carriage return
5340 // space will be used when next token arrives, do not alter formulaString stack
5341 switch ($token['data']['spacetype']) {
5342 case 'type0':
5343 $space0 = str_repeat(' ', $token['data']['spacecount']);
5344 break;
5345 case 'type1':
5346 $space1 = str_repeat("\n", $token['data']['spacecount']);
5347 break;
5348 case 'type2':
5349 $space2 = str_repeat(' ', $token['data']['spacecount']);
5350 break;
5351 case 'type3':
5352 $space3 = str_repeat("\n", $token['data']['spacecount']);
5353 break;
5354 case 'type4':
5355 $space4 = str_repeat(' ', $token['data']['spacecount']);
5356 break;
5357 case 'type5':
5358 $space5 = str_repeat("\n", $token['data']['spacecount']);
5359 break;
5360 }
5361 break;
5362 case 'tAttrSum': // SUM function with one parameter
5363 $op = array_pop($formulaStrings);
5364 $formulaStrings[] = "{$space1}{$space0}SUM($op)";
5365 unset($space0, $space1);
5366 break;
5367 case 'tFunc': // function with fixed number of arguments
5368 case 'tFuncV': // function with variable number of arguments
5369 if ($token['data']['function'] != '') {
5370 // normal function
5371 $ops = array(); // array of operators
5372 for ($i = 0; $i < $token['data']['args']; ++$i) {
5373 $ops[] = array_pop($formulaStrings);
5374 }
5375 $ops = array_reverse($ops);
5376 $formulaStrings[] = "$space1$space0{$token['data']['function']}(" . implode(',', $ops) . ")";
5377 unset($space0, $space1);
5378 } else {
5379 // add-in function
5380 $ops = array(); // array of operators
5381 for ($i = 0; $i < $token['data']['args'] - 1; ++$i) {
5382 $ops[] = array_pop($formulaStrings);
5383 }
5384 $ops = array_reverse($ops);
5385 $function = array_pop($formulaStrings);
5386 $formulaStrings[] = "$space1$space0$function(" . implode(',', $ops) . ")";
5387 unset($space0, $space1);
5388 }
5389 break;
5390 case 'tParen': // parenthesis
5391 $expression = array_pop($formulaStrings);
5392 $formulaStrings[] = "$space3$space2($expression$space5$space4)";
5393 unset($space2, $space3, $space4, $space5);
5394 break;
5395 case 'tArray': // array constant
5396 $constantArray = self::_readBIFF8ConstantArray($additionalData);
5397 $formulaStrings[] = $space1 . $space0 . $constantArray['value'];
5398 $additionalData = substr($additionalData, $constantArray['size']); // bite of chunk of additional data
5399 unset($space0, $space1);
5400 break;
5401 case 'tMemArea':
5402 // bite off chunk of additional data
5403 $cellRangeAddressList = $this->_readBIFF8CellRangeAddressList($additionalData);
5404 $additionalData = substr($additionalData, $cellRangeAddressList['size']);
5405 $formulaStrings[] = "$space1$space0{$token['data']}";
5406 unset($space0, $space1);
5407 break;
5408 case 'tArea': // cell range address
5409 case 'tBool': // boolean
5410 case 'tErr': // error code
5411 case 'tInt': // integer
5412 case 'tMemErr':
5413 case 'tMemFunc':
5414 case 'tMissArg':
5415 case 'tName':
5416 case 'tNameX':
5417 case 'tNum': // number
5418 case 'tRef': // single cell reference
5419 case 'tRef3d': // 3d cell reference
5420 case 'tArea3d': // 3d cell range reference
5421 case 'tRefN':
5422 case 'tAreaN':
5423 case 'tStr': // string
5424 $formulaStrings[] = "$space1$space0{$token['data']}";
5425 unset($space0, $space1);
5426 break;
5427 }
5428 }
5429 $formulaString = $formulaStrings[0];
5430
5431 // for debug: dump the human readable formula
5432 //echo '----' . "\n";
5433 //echo 'Formula: ' . $formulaString;
5434
5435 return $formulaString;
5436 }
5437
5438
5447 private function _getNextToken($formulaData, $baseCell = 'A1')
5448 {
5449 // offset: 0; size: 1; token id
5450 $id = ord($formulaData[0]); // token id
5451 $name = false; // initialize token name
5452
5453 switch ($id) {
5454 case 0x03: $name = 'tAdd'; $size = 1; $data = '+'; break;
5455 case 0x04: $name = 'tSub'; $size = 1; $data = '-'; break;
5456 case 0x05: $name = 'tMul'; $size = 1; $data = '*'; break;
5457 case 0x06: $name = 'tDiv'; $size = 1; $data = '/'; break;
5458 case 0x07: $name = 'tPower'; $size = 1; $data = '^'; break;
5459 case 0x08: $name = 'tConcat'; $size = 1; $data = '&'; break;
5460 case 0x09: $name = 'tLT'; $size = 1; $data = '<'; break;
5461 case 0x0A: $name = 'tLE'; $size = 1; $data = '<='; break;
5462 case 0x0B: $name = 'tEQ'; $size = 1; $data = '='; break;
5463 case 0x0C: $name = 'tGE'; $size = 1; $data = '>='; break;
5464 case 0x0D: $name = 'tGT'; $size = 1; $data = '>'; break;
5465 case 0x0E: $name = 'tNE'; $size = 1; $data = '<>'; break;
5466 case 0x0F: $name = 'tIsect'; $size = 1; $data = ' '; break;
5467 case 0x10: $name = 'tList'; $size = 1; $data = ','; break;
5468 case 0x11: $name = 'tRange'; $size = 1; $data = ':'; break;
5469 case 0x12: $name = 'tUplus'; $size = 1; $data = '+'; break;
5470 case 0x13: $name = 'tUminus'; $size = 1; $data = '-'; break;
5471 case 0x14: $name = 'tPercent'; $size = 1; $data = '%'; break;
5472 case 0x15: // parenthesis
5473 $name = 'tParen';
5474 $size = 1;
5475 $data = null;
5476 break;
5477 case 0x16: // missing argument
5478 $name = 'tMissArg';
5479 $size = 1;
5480 $data = '';
5481 break;
5482 case 0x17: // string
5483 $name = 'tStr';
5484 // offset: 1; size: var; Unicode string, 8-bit string length
5485 $string = self::_readUnicodeStringShort(substr($formulaData, 1));
5486 $size = 1 + $string['size'];
5487 $data = self::_UTF8toExcelDoubleQuoted($string['value']);
5488 break;
5489 case 0x19: // Special attribute
5490 // offset: 1; size: 1; attribute type flags:
5491 switch (ord($formulaData[1])) {
5492 case 0x01:
5493 $name = 'tAttrVolatile';
5494 $size = 4;
5495 $data = null;
5496 break;
5497 case 0x02:
5498 $name = 'tAttrIf';
5499 $size = 4;
5500 $data = null;
5501 break;
5502 case 0x04:
5503 $name = 'tAttrChoose';
5504 // offset: 2; size: 2; number of choices in the CHOOSE function ($nc, number of parameters decreased by 1)
5505 $nc = self::_GetInt2d($formulaData, 2);
5506 // offset: 4; size: 2 * $nc
5507 // offset: 4 + 2 * $nc; size: 2
5508 $size = 2 * $nc + 6;
5509 $data = null;
5510 break;
5511 case 0x08:
5512 $name = 'tAttrSkip';
5513 $size = 4;
5514 $data = null;
5515 break;
5516 case 0x10:
5517 $name = 'tAttrSum';
5518 $size = 4;
5519 $data = null;
5520 break;
5521 case 0x40:
5522 case 0x41:
5523 $name = 'tAttrSpace';
5524 $size = 4;
5525 // offset: 2; size: 2; space type and position
5526 switch (ord($formulaData[2])) {
5527 case 0x00:
5528 $spacetype = 'type0';
5529 break;
5530 case 0x01:
5531 $spacetype = 'type1';
5532 break;
5533 case 0x02:
5534 $spacetype = 'type2';
5535 break;
5536 case 0x03:
5537 $spacetype = 'type3';
5538 break;
5539 case 0x04:
5540 $spacetype = 'type4';
5541 break;
5542 case 0x05:
5543 $spacetype = 'type5';
5544 break;
5545 default:
5546 throw new PHPExcel_Reader_Exception('Unrecognized space type in tAttrSpace token');
5547 break;
5548 }
5549 // offset: 3; size: 1; number of inserted spaces/carriage returns
5550 $spacecount = ord($formulaData[3]);
5551
5552 $data = array('spacetype' => $spacetype, 'spacecount' => $spacecount);
5553 break;
5554 default:
5555 throw new PHPExcel_Reader_Exception('Unrecognized attribute flag in tAttr token');
5556 break;
5557 }
5558 break;
5559 case 0x1C: // error code
5560 // offset: 1; size: 1; error code
5561 $name = 'tErr';
5562 $size = 2;
5563 $data = self::_mapErrorCode(ord($formulaData[1]));
5564 break;
5565 case 0x1D: // boolean
5566 // offset: 1; size: 1; 0 = false, 1 = true;
5567 $name = 'tBool';
5568 $size = 2;
5569 $data = ord($formulaData[1]) ? 'TRUE' : 'FALSE';
5570 break;
5571 case 0x1E: // integer
5572 // offset: 1; size: 2; unsigned 16-bit integer
5573 $name = 'tInt';
5574 $size = 3;
5575 $data = self::_GetInt2d($formulaData, 1);
5576 break;
5577 case 0x1F: // number
5578 // offset: 1; size: 8;
5579 $name = 'tNum';
5580 $size = 9;
5581 $data = self::_extractNumber(substr($formulaData, 1));
5582 $data = str_replace(',', '.', (string)$data); // in case non-English locale
5583 break;
5584 case 0x20: // array constant
5585 case 0x40:
5586 case 0x60:
5587 // offset: 1; size: 7; not used
5588 $name = 'tArray';
5589 $size = 8;
5590 $data = null;
5591 break;
5592 case 0x21: // function with fixed number of arguments
5593 case 0x41:
5594 case 0x61:
5595 $name = 'tFunc';
5596 $size = 3;
5597 // offset: 1; size: 2; index to built-in sheet function
5598 switch (self::_GetInt2d($formulaData, 1)) {
5599 case 2: $function = 'ISNA'; $args = 1; break;
5600 case 3: $function = 'ISERROR'; $args = 1; break;
5601 case 10: $function = 'NA'; $args = 0; break;
5602 case 15: $function = 'SIN'; $args = 1; break;
5603 case 16: $function = 'COS'; $args = 1; break;
5604 case 17: $function = 'TAN'; $args = 1; break;
5605 case 18: $function = 'ATAN'; $args = 1; break;
5606 case 19: $function = 'PI'; $args = 0; break;
5607 case 20: $function = 'SQRT'; $args = 1; break;
5608 case 21: $function = 'EXP'; $args = 1; break;
5609 case 22: $function = 'LN'; $args = 1; break;
5610 case 23: $function = 'LOG10'; $args = 1; break;
5611 case 24: $function = 'ABS'; $args = 1; break;
5612 case 25: $function = 'INT'; $args = 1; break;
5613 case 26: $function = 'SIGN'; $args = 1; break;
5614 case 27: $function = 'ROUND'; $args = 2; break;
5615 case 30: $function = 'REPT'; $args = 2; break;
5616 case 31: $function = 'MID'; $args = 3; break;
5617 case 32: $function = 'LEN'; $args = 1; break;
5618 case 33: $function = 'VALUE'; $args = 1; break;
5619 case 34: $function = 'TRUE'; $args = 0; break;
5620 case 35: $function = 'FALSE'; $args = 0; break;
5621 case 38: $function = 'NOT'; $args = 1; break;
5622 case 39: $function = 'MOD'; $args = 2; break;
5623 case 40: $function = 'DCOUNT'; $args = 3; break;
5624 case 41: $function = 'DSUM'; $args = 3; break;
5625 case 42: $function = 'DAVERAGE'; $args = 3; break;
5626 case 43: $function = 'DMIN'; $args = 3; break;
5627 case 44: $function = 'DMAX'; $args = 3; break;
5628 case 45: $function = 'DSTDEV'; $args = 3; break;
5629 case 48: $function = 'TEXT'; $args = 2; break;
5630 case 61: $function = 'MIRR'; $args = 3; break;
5631 case 63: $function = 'RAND'; $args = 0; break;
5632 case 65: $function = 'DATE'; $args = 3; break;
5633 case 66: $function = 'TIME'; $args = 3; break;
5634 case 67: $function = 'DAY'; $args = 1; break;
5635 case 68: $function = 'MONTH'; $args = 1; break;
5636 case 69: $function = 'YEAR'; $args = 1; break;
5637 case 71: $function = 'HOUR'; $args = 1; break;
5638 case 72: $function = 'MINUTE'; $args = 1; break;
5639 case 73: $function = 'SECOND'; $args = 1; break;
5640 case 74: $function = 'NOW'; $args = 0; break;
5641 case 75: $function = 'AREAS'; $args = 1; break;
5642 case 76: $function = 'ROWS'; $args = 1; break;
5643 case 77: $function = 'COLUMNS'; $args = 1; break;
5644 case 83: $function = 'TRANSPOSE'; $args = 1; break;
5645 case 86: $function = 'TYPE'; $args = 1; break;
5646 case 97: $function = 'ATAN2'; $args = 2; break;
5647 case 98: $function = 'ASIN'; $args = 1; break;
5648 case 99: $function = 'ACOS'; $args = 1; break;
5649 case 105: $function = 'ISREF'; $args = 1; break;
5650 case 111: $function = 'CHAR'; $args = 1; break;
5651 case 112: $function = 'LOWER'; $args = 1; break;
5652 case 113: $function = 'UPPER'; $args = 1; break;
5653 case 114: $function = 'PROPER'; $args = 1; break;
5654 case 117: $function = 'EXACT'; $args = 2; break;
5655 case 118: $function = 'TRIM'; $args = 1; break;
5656 case 119: $function = 'REPLACE'; $args = 4; break;
5657 case 121: $function = 'CODE'; $args = 1; break;
5658 case 126: $function = 'ISERR'; $args = 1; break;
5659 case 127: $function = 'ISTEXT'; $args = 1; break;
5660 case 128: $function = 'ISNUMBER'; $args = 1; break;
5661 case 129: $function = 'ISBLANK'; $args = 1; break;
5662 case 130: $function = 'T'; $args = 1; break;
5663 case 131: $function = 'N'; $args = 1; break;
5664 case 140: $function = 'DATEVALUE'; $args = 1; break;
5665 case 141: $function = 'TIMEVALUE'; $args = 1; break;
5666 case 142: $function = 'SLN'; $args = 3; break;
5667 case 143: $function = 'SYD'; $args = 4; break;
5668 case 162: $function = 'CLEAN'; $args = 1; break;
5669 case 163: $function = 'MDETERM'; $args = 1; break;
5670 case 164: $function = 'MINVERSE'; $args = 1; break;
5671 case 165: $function = 'MMULT'; $args = 2; break;
5672 case 184: $function = 'FACT'; $args = 1; break;
5673 case 189: $function = 'DPRODUCT'; $args = 3; break;
5674 case 190: $function = 'ISNONTEXT'; $args = 1; break;
5675 case 195: $function = 'DSTDEVP'; $args = 3; break;
5676 case 196: $function = 'DVARP'; $args = 3; break;
5677 case 198: $function = 'ISLOGICAL'; $args = 1; break;
5678 case 199: $function = 'DCOUNTA'; $args = 3; break;
5679 case 207: $function = 'REPLACEB'; $args = 4; break;
5680 case 210: $function = 'MIDB'; $args = 3; break;
5681 case 211: $function = 'LENB'; $args = 1; break;
5682 case 212: $function = 'ROUNDUP'; $args = 2; break;
5683 case 213: $function = 'ROUNDDOWN'; $args = 2; break;
5684 case 214: $function = 'ASC'; $args = 1; break;
5685 case 215: $function = 'DBCS'; $args = 1; break;
5686 case 221: $function = 'TODAY'; $args = 0; break;
5687 case 229: $function = 'SINH'; $args = 1; break;
5688 case 230: $function = 'COSH'; $args = 1; break;
5689 case 231: $function = 'TANH'; $args = 1; break;
5690 case 232: $function = 'ASINH'; $args = 1; break;
5691 case 233: $function = 'ACOSH'; $args = 1; break;
5692 case 234: $function = 'ATANH'; $args = 1; break;
5693 case 235: $function = 'DGET'; $args = 3; break;
5694 case 244: $function = 'INFO'; $args = 1; break;
5695 case 252: $function = 'FREQUENCY'; $args = 2; break;
5696 case 261: $function = 'ERROR.TYPE'; $args = 1; break;
5697 case 271: $function = 'GAMMALN'; $args = 1; break;
5698 case 273: $function = 'BINOMDIST'; $args = 4; break;
5699 case 274: $function = 'CHIDIST'; $args = 2; break;
5700 case 275: $function = 'CHIINV'; $args = 2; break;
5701 case 276: $function = 'COMBIN'; $args = 2; break;
5702 case 277: $function = 'CONFIDENCE'; $args = 3; break;
5703 case 278: $function = 'CRITBINOM'; $args = 3; break;
5704 case 279: $function = 'EVEN'; $args = 1; break;
5705 case 280: $function = 'EXPONDIST'; $args = 3; break;
5706 case 281: $function = 'FDIST'; $args = 3; break;
5707 case 282: $function = 'FINV'; $args = 3; break;
5708 case 283: $function = 'FISHER'; $args = 1; break;
5709 case 284: $function = 'FISHERINV'; $args = 1; break;
5710 case 285: $function = 'FLOOR'; $args = 2; break;
5711 case 286: $function = 'GAMMADIST'; $args = 4; break;
5712 case 287: $function = 'GAMMAINV'; $args = 3; break;
5713 case 288: $function = 'CEILING'; $args = 2; break;
5714 case 289: $function = 'HYPGEOMDIST'; $args = 4; break;
5715 case 290: $function = 'LOGNORMDIST'; $args = 3; break;
5716 case 291: $function = 'LOGINV'; $args = 3; break;
5717 case 292: $function = 'NEGBINOMDIST'; $args = 3; break;
5718 case 293: $function = 'NORMDIST'; $args = 4; break;
5719 case 294: $function = 'NORMSDIST'; $args = 1; break;
5720 case 295: $function = 'NORMINV'; $args = 3; break;
5721 case 296: $function = 'NORMSINV'; $args = 1; break;
5722 case 297: $function = 'STANDARDIZE'; $args = 3; break;
5723 case 298: $function = 'ODD'; $args = 1; break;
5724 case 299: $function = 'PERMUT'; $args = 2; break;
5725 case 300: $function = 'POISSON'; $args = 3; break;
5726 case 301: $function = 'TDIST'; $args = 3; break;
5727 case 302: $function = 'WEIBULL'; $args = 4; break;
5728 case 303: $function = 'SUMXMY2'; $args = 2; break;
5729 case 304: $function = 'SUMX2MY2'; $args = 2; break;
5730 case 305: $function = 'SUMX2PY2'; $args = 2; break;
5731 case 306: $function = 'CHITEST'; $args = 2; break;
5732 case 307: $function = 'CORREL'; $args = 2; break;
5733 case 308: $function = 'COVAR'; $args = 2; break;
5734 case 309: $function = 'FORECAST'; $args = 3; break;
5735 case 310: $function = 'FTEST'; $args = 2; break;
5736 case 311: $function = 'INTERCEPT'; $args = 2; break;
5737 case 312: $function = 'PEARSON'; $args = 2; break;
5738 case 313: $function = 'RSQ'; $args = 2; break;
5739 case 314: $function = 'STEYX'; $args = 2; break;
5740 case 315: $function = 'SLOPE'; $args = 2; break;
5741 case 316: $function = 'TTEST'; $args = 4; break;
5742 case 325: $function = 'LARGE'; $args = 2; break;
5743 case 326: $function = 'SMALL'; $args = 2; break;
5744 case 327: $function = 'QUARTILE'; $args = 2; break;
5745 case 328: $function = 'PERCENTILE'; $args = 2; break;
5746 case 331: $function = 'TRIMMEAN'; $args = 2; break;
5747 case 332: $function = 'TINV'; $args = 2; break;
5748 case 337: $function = 'POWER'; $args = 2; break;
5749 case 342: $function = 'RADIANS'; $args = 1; break;
5750 case 343: $function = 'DEGREES'; $args = 1; break;
5751 case 346: $function = 'COUNTIF'; $args = 2; break;
5752 case 347: $function = 'COUNTBLANK'; $args = 1; break;
5753 case 350: $function = 'ISPMT'; $args = 4; break;
5754 case 351: $function = 'DATEDIF'; $args = 3; break;
5755 case 352: $function = 'DATESTRING'; $args = 1; break;
5756 case 353: $function = 'NUMBERSTRING'; $args = 2; break;
5757 case 360: $function = 'PHONETIC'; $args = 1; break;
5758 case 368: $function = 'BAHTTEXT'; $args = 1; break;
5759 default:
5760 throw new PHPExcel_Reader_Exception('Unrecognized function in formula');
5761 break;
5762 }
5763 $data = array('function' => $function, 'args' => $args);
5764 break;
5765 case 0x22: // function with variable number of arguments
5766 case 0x42:
5767 case 0x62:
5768 $name = 'tFuncV';
5769 $size = 4;
5770 // offset: 1; size: 1; number of arguments
5771 $args = ord($formulaData[1]);
5772 // offset: 2: size: 2; index to built-in sheet function
5773 $index = self::_GetInt2d($formulaData, 2);
5774 switch ($index) {
5775 case 0: $function = 'COUNT'; break;
5776 case 1: $function = 'IF'; break;
5777 case 4: $function = 'SUM'; break;
5778 case 5: $function = 'AVERAGE'; break;
5779 case 6: $function = 'MIN'; break;
5780 case 7: $function = 'MAX'; break;
5781 case 8: $function = 'ROW'; break;
5782 case 9: $function = 'COLUMN'; break;
5783 case 11: $function = 'NPV'; break;
5784 case 12: $function = 'STDEV'; break;
5785 case 13: $function = 'DOLLAR'; break;
5786 case 14: $function = 'FIXED'; break;
5787 case 28: $function = 'LOOKUP'; break;
5788 case 29: $function = 'INDEX'; break;
5789 case 36: $function = 'AND'; break;
5790 case 37: $function = 'OR'; break;
5791 case 46: $function = 'VAR'; break;
5792 case 49: $function = 'LINEST'; break;
5793 case 50: $function = 'TREND'; break;
5794 case 51: $function = 'LOGEST'; break;
5795 case 52: $function = 'GROWTH'; break;
5796 case 56: $function = 'PV'; break;
5797 case 57: $function = 'FV'; break;
5798 case 58: $function = 'NPER'; break;
5799 case 59: $function = 'PMT'; break;
5800 case 60: $function = 'RATE'; break;
5801 case 62: $function = 'IRR'; break;
5802 case 64: $function = 'MATCH'; break;
5803 case 70: $function = 'WEEKDAY'; break;
5804 case 78: $function = 'OFFSET'; break;
5805 case 82: $function = 'SEARCH'; break;
5806 case 100: $function = 'CHOOSE'; break;
5807 case 101: $function = 'HLOOKUP'; break;
5808 case 102: $function = 'VLOOKUP'; break;
5809 case 109: $function = 'LOG'; break;
5810 case 115: $function = 'LEFT'; break;
5811 case 116: $function = 'RIGHT'; break;
5812 case 120: $function = 'SUBSTITUTE'; break;
5813 case 124: $function = 'FIND'; break;
5814 case 125: $function = 'CELL'; break;
5815 case 144: $function = 'DDB'; break;
5816 case 148: $function = 'INDIRECT'; break;
5817 case 167: $function = 'IPMT'; break;
5818 case 168: $function = 'PPMT'; break;
5819 case 169: $function = 'COUNTA'; break;
5820 case 183: $function = 'PRODUCT'; break;
5821 case 193: $function = 'STDEVP'; break;
5822 case 194: $function = 'VARP'; break;
5823 case 197: $function = 'TRUNC'; break;
5824 case 204: $function = 'USDOLLAR'; break;
5825 case 205: $function = 'FINDB'; break;
5826 case 206: $function = 'SEARCHB'; break;
5827 case 208: $function = 'LEFTB'; break;
5828 case 209: $function = 'RIGHTB'; break;
5829 case 216: $function = 'RANK'; break;
5830 case 219: $function = 'ADDRESS'; break;
5831 case 220: $function = 'DAYS360'; break;
5832 case 222: $function = 'VDB'; break;
5833 case 227: $function = 'MEDIAN'; break;
5834 case 228: $function = 'SUMPRODUCT'; break;
5835 case 247: $function = 'DB'; break;
5836 case 255: $function = ''; break;
5837 case 269: $function = 'AVEDEV'; break;
5838 case 270: $function = 'BETADIST'; break;
5839 case 272: $function = 'BETAINV'; break;
5840 case 317: $function = 'PROB'; break;
5841 case 318: $function = 'DEVSQ'; break;
5842 case 319: $function = 'GEOMEAN'; break;
5843 case 320: $function = 'HARMEAN'; break;
5844 case 321: $function = 'SUMSQ'; break;
5845 case 322: $function = 'KURT'; break;
5846 case 323: $function = 'SKEW'; break;
5847 case 324: $function = 'ZTEST'; break;
5848 case 329: $function = 'PERCENTRANK'; break;
5849 case 330: $function = 'MODE'; break;
5850 case 336: $function = 'CONCATENATE'; break;
5851 case 344: $function = 'SUBTOTAL'; break;
5852 case 345: $function = 'SUMIF'; break;
5853 case 354: $function = 'ROMAN'; break;
5854 case 358: $function = 'GETPIVOTDATA'; break;
5855 case 359: $function = 'HYPERLINK'; break;
5856 case 361: $function = 'AVERAGEA'; break;
5857 case 362: $function = 'MAXA'; break;
5858 case 363: $function = 'MINA'; break;
5859 case 364: $function = 'STDEVPA'; break;
5860 case 365: $function = 'VARPA'; break;
5861 case 366: $function = 'STDEVA'; break;
5862 case 367: $function = 'VARA'; break;
5863 default:
5864 throw new PHPExcel_Reader_Exception('Unrecognized function in formula');
5865 break;
5866 }
5867 $data = array('function' => $function, 'args' => $args);
5868 break;
5869 case 0x23: // index to defined name
5870 case 0x43:
5871 case 0x63:
5872 $name = 'tName';
5873 $size = 5;
5874 // offset: 1; size: 2; one-based index to definedname record
5875 $definedNameIndex = self::_GetInt2d($formulaData, 1) - 1;
5876 // offset: 2; size: 2; not used
5877 $data = $this->_definedname[$definedNameIndex]['name'];
5878 break;
5879 case 0x24: // single cell reference e.g. A5
5880 case 0x44:
5881 case 0x64:
5882 $name = 'tRef';
5883 $size = 5;
5884 $data = $this->_readBIFF8CellAddress(substr($formulaData, 1, 4));
5885 break;
5886 case 0x25: // cell range reference to cells in the same sheet (2d)
5887 case 0x45:
5888 case 0x65:
5889 $name = 'tArea';
5890 $size = 9;
5891 $data = $this->_readBIFF8CellRangeAddress(substr($formulaData, 1, 8));
5892 break;
5893 case 0x26: // Constant reference sub-expression
5894 case 0x46:
5895 case 0x66:
5896 $name = 'tMemArea';
5897 // offset: 1; size: 4; not used
5898 // offset: 5; size: 2; size of the following subexpression
5899 $subSize = self::_GetInt2d($formulaData, 5);
5900 $size = 7 + $subSize;
5901 $data = $this->_getFormulaFromData(substr($formulaData, 7, $subSize));
5902 break;
5903 case 0x27: // Deleted constant reference sub-expression
5904 case 0x47:
5905 case 0x67:
5906 $name = 'tMemErr';
5907 // offset: 1; size: 4; not used
5908 // offset: 5; size: 2; size of the following subexpression
5909 $subSize = self::_GetInt2d($formulaData, 5);
5910 $size = 7 + $subSize;
5911 $data = $this->_getFormulaFromData(substr($formulaData, 7, $subSize));
5912 break;
5913 case 0x29: // Variable reference sub-expression
5914 case 0x49:
5915 case 0x69:
5916 $name = 'tMemFunc';
5917 // offset: 1; size: 2; size of the following sub-expression
5918 $subSize = self::_GetInt2d($formulaData, 1);
5919 $size = 3 + $subSize;
5920 $data = $this->_getFormulaFromData(substr($formulaData, 3, $subSize));
5921 break;
5922
5923 case 0x2C: // Relative 2d cell reference reference, used in shared formulas and some other places
5924 case 0x4C:
5925 case 0x6C:
5926 $name = 'tRefN';
5927 $size = 5;
5928 $data = $this->_readBIFF8CellAddressB(substr($formulaData, 1, 4), $baseCell);
5929 break;
5930
5931 case 0x2D: // Relative 2d range reference
5932 case 0x4D:
5933 case 0x6D:
5934 $name = 'tAreaN';
5935 $size = 9;
5936 $data = $this->_readBIFF8CellRangeAddressB(substr($formulaData, 1, 8), $baseCell);
5937 break;
5938
5939 case 0x39: // External name
5940 case 0x59:
5941 case 0x79:
5942 $name = 'tNameX';
5943 $size = 7;
5944 // offset: 1; size: 2; index to REF entry in EXTERNSHEET record
5945 // offset: 3; size: 2; one-based index to DEFINEDNAME or EXTERNNAME record
5946 $index = self::_GetInt2d($formulaData, 3);
5947 // assume index is to EXTERNNAME record
5948 $data = $this->_externalNames[$index - 1]['name'];
5949 // offset: 5; size: 2; not used
5950 break;
5951
5952 case 0x3A: // 3d reference to cell
5953 case 0x5A:
5954 case 0x7A:
5955 $name = 'tRef3d';
5956 $size = 7;
5957
5958 try {
5959 // offset: 1; size: 2; index to REF entry
5960 $sheetRange = $this->_readSheetRangeByRefIndex(self::_GetInt2d($formulaData, 1));
5961 // offset: 3; size: 4; cell address
5962 $cellAddress = $this->_readBIFF8CellAddress(substr($formulaData, 3, 4));
5963
5964 $data = "$sheetRange!$cellAddress";
5965 } catch (PHPExcel_Exception $e) {
5966 // deleted sheet reference
5967 $data = '#REF!';
5968 }
5969
5970 break;
5971 case 0x3B: // 3d reference to cell range
5972 case 0x5B:
5973 case 0x7B:
5974 $name = 'tArea3d';
5975 $size = 11;
5976
5977 try {
5978 // offset: 1; size: 2; index to REF entry
5979 $sheetRange = $this->_readSheetRangeByRefIndex(self::_GetInt2d($formulaData, 1));
5980 // offset: 3; size: 8; cell address
5981 $cellRangeAddress = $this->_readBIFF8CellRangeAddress(substr($formulaData, 3, 8));
5982
5983 $data = "$sheetRange!$cellRangeAddress";
5984 } catch (PHPExcel_Exception $e) {
5985 // deleted sheet reference
5986 $data = '#REF!';
5987 }
5988
5989 break;
5990 // Unknown cases // don't know how to deal with
5991 default:
5992 throw new PHPExcel_Reader_Exception('Unrecognized token ' . sprintf('%02X', $id) . ' in formula');
5993 break;
5994 }
5995
5996 return array(
5997 'id' => $id,
5998 'name' => $name,
5999 'size' => $size,
6000 'data' => $data,
6001 );
6002 }
6003
6004
6012 private function _readBIFF8CellAddress($cellAddressStructure)
6013 {
6014 // offset: 0; size: 2; index to row (0... 65535) (or offset (-32768... 32767))
6015 $row = self::_GetInt2d($cellAddressStructure, 0) + 1;
6016
6017 // offset: 2; size: 2; index to column or column offset + relative flags
6018
6019 // bit: 7-0; mask 0x00FF; column index
6020 $column = PHPExcel_Cell::stringFromColumnIndex(0x00FF & self::_GetInt2d($cellAddressStructure, 2));
6021
6022 // bit: 14; mask 0x4000; (1 = relative column index, 0 = absolute column index)
6023 if (!(0x4000 & self::_GetInt2d($cellAddressStructure, 2))) {
6024 $column = '$' . $column;
6025 }
6026 // bit: 15; mask 0x8000; (1 = relative row index, 0 = absolute row index)
6027 if (!(0x8000 & self::_GetInt2d($cellAddressStructure, 2))) {
6028 $row = '$' . $row;
6029 }
6030
6031 return $column . $row;
6032 }
6033
6034
6044 private function _readBIFF8CellAddressB($cellAddressStructure, $baseCell = 'A1')
6045 {
6046 list($baseCol, $baseRow) = PHPExcel_Cell::coordinateFromString($baseCell);
6047 $baseCol = PHPExcel_Cell::columnIndexFromString($baseCol) - 1;
6048
6049 // offset: 0; size: 2; index to row (0... 65535) (or offset (-32768... 32767))
6050 $rowIndex = self::_GetInt2d($cellAddressStructure, 0);
6051 $row = self::_GetInt2d($cellAddressStructure, 0) + 1;
6052
6053 // offset: 2; size: 2; index to column or column offset + relative flags
6054
6055 // bit: 7-0; mask 0x00FF; column index
6056 $colIndex = 0x00FF & self::_GetInt2d($cellAddressStructure, 2);
6057
6058 // bit: 14; mask 0x4000; (1 = relative column index, 0 = absolute column index)
6059 if (!(0x4000 & self::_GetInt2d($cellAddressStructure, 2))) {
6061 $column = '$' . $column;
6062 } else {
6063 $colIndex = ($colIndex <= 127) ? $colIndex : $colIndex - 256;
6064 $column = PHPExcel_Cell::stringFromColumnIndex($baseCol + $colIndex);
6065 }
6066
6067 // bit: 15; mask 0x8000; (1 = relative row index, 0 = absolute row index)
6068 if (!(0x8000 & self::_GetInt2d($cellAddressStructure, 2))) {
6069 $row = '$' . $row;
6070 } else {
6071 $rowIndex = ($rowIndex <= 32767) ? $rowIndex : $rowIndex - 65536;
6072 $row = $baseRow + $rowIndex;
6073 }
6074
6075 return $column . $row;
6076 }
6077
6078
6088 private function _readBIFF5CellRangeAddressFixed($subData)
6089 {
6090 // offset: 0; size: 2; index to first row
6091 $fr = self::_GetInt2d($subData, 0) + 1;
6092
6093 // offset: 2; size: 2; index to last row
6094 $lr = self::_GetInt2d($subData, 2) + 1;
6095
6096 // offset: 4; size: 1; index to first column
6097 $fc = ord($subData{4});
6098
6099 // offset: 5; size: 1; index to last column
6100 $lc = ord($subData{5});
6101
6102 // check values
6103 if ($fr > $lr || $fc > $lc) {
6104 throw new PHPExcel_Reader_Exception('Not a cell range address');
6105 }
6106
6107 // column index to letter
6110
6111 if ($fr == $lr and $fc == $lc) {
6112 return "$fc$fr";
6113 }
6114 return "$fc$fr:$lc$lr";
6115 }
6116
6117
6127 private function _readBIFF8CellRangeAddressFixed($subData)
6128 {
6129 // offset: 0; size: 2; index to first row
6130 $fr = self::_GetInt2d($subData, 0) + 1;
6131
6132 // offset: 2; size: 2; index to last row
6133 $lr = self::_GetInt2d($subData, 2) + 1;
6134
6135 // offset: 4; size: 2; index to first column
6136 $fc = self::_GetInt2d($subData, 4);
6137
6138 // offset: 6; size: 2; index to last column
6139 $lc = self::_GetInt2d($subData, 6);
6140
6141 // check values
6142 if ($fr > $lr || $fc > $lc) {
6143 throw new PHPExcel_Reader_Exception('Not a cell range address');
6144 }
6145
6146 // column index to letter
6149
6150 if ($fr == $lr and $fc == $lc) {
6151 return "$fc$fr";
6152 }
6153 return "$fc$fr:$lc$lr";
6154 }
6155
6156
6165 private function _readBIFF8CellRangeAddress($subData)
6166 {
6167 // todo: if cell range is just a single cell, should this funciton
6168 // not just return e.g. 'A1' and not 'A1:A1' ?
6169
6170 // offset: 0; size: 2; index to first row (0... 65535) (or offset (-32768... 32767))
6171 $fr = self::_GetInt2d($subData, 0) + 1;
6172
6173 // offset: 2; size: 2; index to last row (0... 65535) (or offset (-32768... 32767))
6174 $lr = self::_GetInt2d($subData, 2) + 1;
6175
6176 // offset: 4; size: 2; index to first column or column offset + relative flags
6177
6178 // bit: 7-0; mask 0x00FF; column index
6179 $fc = PHPExcel_Cell::stringFromColumnIndex(0x00FF & self::_GetInt2d($subData, 4));
6180
6181 // bit: 14; mask 0x4000; (1 = relative column index, 0 = absolute column index)
6182 if (!(0x4000 & self::_GetInt2d($subData, 4))) {
6183 $fc = '$' . $fc;
6184 }
6185
6186 // bit: 15; mask 0x8000; (1 = relative row index, 0 = absolute row index)
6187 if (!(0x8000 & self::_GetInt2d($subData, 4))) {
6188 $fr = '$' . $fr;
6189 }
6190
6191 // offset: 6; size: 2; index to last column or column offset + relative flags
6192
6193 // bit: 7-0; mask 0x00FF; column index
6194 $lc = PHPExcel_Cell::stringFromColumnIndex(0x00FF & self::_GetInt2d($subData, 6));
6195
6196 // bit: 14; mask 0x4000; (1 = relative column index, 0 = absolute column index)
6197 if (!(0x4000 & self::_GetInt2d($subData, 6))) {
6198 $lc = '$' . $lc;
6199 }
6200
6201 // bit: 15; mask 0x8000; (1 = relative row index, 0 = absolute row index)
6202 if (!(0x8000 & self::_GetInt2d($subData, 6))) {
6203 $lr = '$' . $lr;
6204 }
6205
6206 return "$fc$fr:$lc$lr";
6207 }
6208
6209
6219 private function _readBIFF8CellRangeAddressB($subData, $baseCell = 'A1')
6220 {
6221 list($baseCol, $baseRow) = PHPExcel_Cell::coordinateFromString($baseCell);
6222 $baseCol = PHPExcel_Cell::columnIndexFromString($baseCol) - 1;
6223
6224 // TODO: if cell range is just a single cell, should this funciton
6225 // not just return e.g. 'A1' and not 'A1:A1' ?
6226
6227 // offset: 0; size: 2; first row
6228 $frIndex = self::_GetInt2d($subData, 0); // adjust below
6229
6230 // offset: 2; size: 2; relative index to first row (0... 65535) should be treated as offset (-32768... 32767)
6231 $lrIndex = self::_GetInt2d($subData, 2); // adjust below
6232
6233 // offset: 4; size: 2; first column with relative/absolute flags
6234
6235 // bit: 7-0; mask 0x00FF; column index
6236 $fcIndex = 0x00FF & self::_GetInt2d($subData, 4);
6237
6238 // bit: 14; mask 0x4000; (1 = relative column index, 0 = absolute column index)
6239 if (!(0x4000 & self::_GetInt2d($subData, 4))) {
6240 // absolute column index
6242 $fc = '$' . $fc;
6243 } else {
6244 // column offset
6245 $fcIndex = ($fcIndex <= 127) ? $fcIndex : $fcIndex - 256;
6246 $fc = PHPExcel_Cell::stringFromColumnIndex($baseCol + $fcIndex);
6247 }
6248
6249 // bit: 15; mask 0x8000; (1 = relative row index, 0 = absolute row index)
6250 if (!(0x8000 & self::_GetInt2d($subData, 4))) {
6251 // absolute row index
6252 $fr = $frIndex + 1;
6253 $fr = '$' . $fr;
6254 } else {
6255 // row offset
6256 $frIndex = ($frIndex <= 32767) ? $frIndex : $frIndex - 65536;
6257 $fr = $baseRow + $frIndex;
6258 }
6259
6260 // offset: 6; size: 2; last column with relative/absolute flags
6261
6262 // bit: 7-0; mask 0x00FF; column index
6263 $lcIndex = 0x00FF & self::_GetInt2d($subData, 6);
6264 $lcIndex = ($lcIndex <= 127) ? $lcIndex : $lcIndex - 256;
6265 $lc = PHPExcel_Cell::stringFromColumnIndex($baseCol + $lcIndex);
6266
6267 // bit: 14; mask 0x4000; (1 = relative column index, 0 = absolute column index)
6268 if (!(0x4000 & self::_GetInt2d($subData, 6))) {
6269 // absolute column index
6271 $lc = '$' . $lc;
6272 } else {
6273 // column offset
6274 $lcIndex = ($lcIndex <= 127) ? $lcIndex : $lcIndex - 256;
6275 $lc = PHPExcel_Cell::stringFromColumnIndex($baseCol + $lcIndex);
6276 }
6277
6278 // bit: 15; mask 0x8000; (1 = relative row index, 0 = absolute row index)
6279 if (!(0x8000 & self::_GetInt2d($subData, 6))) {
6280 // absolute row index
6281 $lr = $lrIndex + 1;
6282 $lr = '$' . $lr;
6283 } else {
6284 // row offset
6285 $lrIndex = ($lrIndex <= 32767) ? $lrIndex : $lrIndex - 65536;
6286 $lr = $baseRow + $lrIndex;
6287 }
6288
6289 return "$fc$fr:$lc$lr";
6290 }
6291
6292
6300 private function _readBIFF8CellRangeAddressList($subData)
6301 {
6302 $cellRangeAddresses = array();
6303
6304 // offset: 0; size: 2; number of the following cell range addresses
6305 $nm = self::_GetInt2d($subData, 0);
6306
6307 $offset = 2;
6308 // offset: 2; size: 8 * $nm; list of $nm (fixed) cell range addresses
6309 for ($i = 0; $i < $nm; ++$i) {
6310 $cellRangeAddresses[] = $this->_readBIFF8CellRangeAddressFixed(substr($subData, $offset, 8));
6311 $offset += 8;
6312 }
6313
6314 return array(
6315 'size' => 2 + 8 * $nm,
6316 'cellRangeAddresses' => $cellRangeAddresses,
6317 );
6318 }
6319
6320
6328 private function _readBIFF5CellRangeAddressList($subData)
6329 {
6330 $cellRangeAddresses = array();
6331
6332 // offset: 0; size: 2; number of the following cell range addresses
6333 $nm = self::_GetInt2d($subData, 0);
6334
6335 $offset = 2;
6336 // offset: 2; size: 6 * $nm; list of $nm (fixed) cell range addresses
6337 for ($i = 0; $i < $nm; ++$i) {
6338 $cellRangeAddresses[] = $this->_readBIFF5CellRangeAddressFixed(substr($subData, $offset, 6));
6339 $offset += 6;
6340 }
6341
6342 return array(
6343 'size' => 2 + 6 * $nm,
6344 'cellRangeAddresses' => $cellRangeAddresses,
6345 );
6346 }
6347
6348
6360 {
6361 if (isset($this->_ref[$index])) {
6362
6363 $type = $this->_externalBooks[$this->_ref[$index]['externalBookIndex']]['type'];
6364
6365 switch ($type) {
6366 case 'internal':
6367 // check if we have a deleted 3d reference
6368 if ($this->_ref[$index]['firstSheetIndex'] == 0xFFFF or $this->_ref[$index]['lastSheetIndex'] == 0xFFFF) {
6369 throw new PHPExcel_Reader_Exception('Deleted sheet reference');
6370 }
6371
6372 // we have normal sheet range (collapsed or uncollapsed)
6373 $firstSheetName = $this->_sheets[$this->_ref[$index]['firstSheetIndex']]['name'];
6374 $lastSheetName = $this->_sheets[$this->_ref[$index]['lastSheetIndex']]['name'];
6375
6376 if ($firstSheetName == $lastSheetName) {
6377 // collapsed sheet range
6378 $sheetRange = $firstSheetName;
6379 } else {
6380 $sheetRange = "$firstSheetName:$lastSheetName";
6381 }
6382
6383 // escape the single-quotes
6384 $sheetRange = str_replace("'", "''", $sheetRange);
6385
6386 // if there are special characters, we need to enclose the range in single-quotes
6387 // todo: check if we have identified the whole set of special characters
6388 // it seems that the following characters are not accepted for sheet names
6389 // and we may assume that they are not present: []*/:\?
6390 if (preg_match("/[ !\"@#£$%&{()}<>=+'|^,;-]/", $sheetRange)) {
6391 $sheetRange = "'$sheetRange'";
6392 }
6393
6394 return $sheetRange;
6395 break;
6396
6397 default:
6398 // TODO: external sheet support
6399 throw new PHPExcel_Reader_Exception('Excel5 reader only supports internal sheets in fomulas');
6400 break;
6401 }
6402 }
6403 return false;
6404 }
6405
6406
6415 private static function _readBIFF8ConstantArray($arrayData)
6416 {
6417 // offset: 0; size: 1; number of columns decreased by 1
6418 $nc = ord($arrayData[0]);
6419
6420 // offset: 1; size: 2; number of rows decreased by 1
6421 $nr = self::_GetInt2d($arrayData, 1);
6422 $size = 3; // initialize
6423 $arrayData = substr($arrayData, 3);
6424
6425 // offset: 3; size: var; list of ($nc + 1) * ($nr + 1) constant values
6426 $matrixChunks = array();
6427 for ($r = 1; $r <= $nr + 1; ++$r) {
6428 $items = array();
6429 for ($c = 1; $c <= $nc + 1; ++$c) {
6430 $constant = self::_readBIFF8Constant($arrayData);
6431 $items[] = $constant['value'];
6432 $arrayData = substr($arrayData, $constant['size']);
6433 $size += $constant['size'];
6434 }
6435 $matrixChunks[] = implode(',', $items); // looks like e.g. '1,"hello"'
6436 }
6437 $matrix = '{' . implode(';', $matrixChunks) . '}';
6438
6439 return array(
6440 'value' => $matrix,
6441 'size' => $size,
6442 );
6443 }
6444
6445
6454 private static function _readBIFF8Constant($valueData)
6455 {
6456 // offset: 0; size: 1; identifier for type of constant
6457 $identifier = ord($valueData[0]);
6458
6459 switch ($identifier) {
6460 case 0x00: // empty constant (what is this?)
6461 $value = '';
6462 $size = 9;
6463 break;
6464 case 0x01: // number
6465 // offset: 1; size: 8; IEEE 754 floating-point value
6466 $value = self::_extractNumber(substr($valueData, 1, 8));
6467 $size = 9;
6468 break;
6469 case 0x02: // string value
6470 // offset: 1; size: var; Unicode string, 16-bit string length
6471 $string = self::_readUnicodeStringLong(substr($valueData, 1));
6472 $value = '"' . $string['value'] . '"';
6473 $size = 1 + $string['size'];
6474 break;
6475 case 0x04: // boolean
6476 // offset: 1; size: 1; 0 = FALSE, 1 = TRUE
6477 if (ord($valueData[1])) {
6478 $value = 'TRUE';
6479 } else {
6480 $value = 'FALSE';
6481 }
6482 $size = 9;
6483 break;
6484 case 0x10: // error code
6485 // offset: 1; size: 1; error code
6486 $value = self::_mapErrorCode(ord($valueData[1]));
6487 $size = 9;
6488 break;
6489 }
6490 return array(
6491 'value' => $value,
6492 'size' => $size,
6493 );
6494 }
6495
6496
6504 private static function _readRGB($rgb)
6505 {
6506 // offset: 0; size 1; Red component
6507 $r = ord($rgb{0});
6508
6509 // offset: 1; size: 1; Green component
6510 $g = ord($rgb{1});
6511
6512 // offset: 2; size: 1; Blue component
6513 $b = ord($rgb{2});
6514
6515 // HEX notation, e.g. 'FF00FC'
6516 $rgb = sprintf('%02X%02X%02X', $r, $g, $b);
6517
6518 return array('rgb' => $rgb);
6519 }
6520
6521
6529 private function _readByteStringShort($subData)
6530 {
6531 // offset: 0; size: 1; length of the string (character count)
6532 $ln = ord($subData[0]);
6533
6534 // offset: 1: size: var; character array (8-bit characters)
6535 $value = $this->_decodeCodepage(substr($subData, 1, $ln));
6536
6537 return array(
6538 'value' => $value,
6539 'size' => 1 + $ln, // size in bytes of data structure
6540 );
6541 }
6542
6543
6551 private function _readByteStringLong($subData)
6552 {
6553 // offset: 0; size: 2; length of the string (character count)
6554 $ln = self::_GetInt2d($subData, 0);
6555
6556 // offset: 2: size: var; character array (8-bit characters)
6557 $value = $this->_decodeCodepage(substr($subData, 2));
6558
6559 //return $string;
6560 return array(
6561 'value' => $value,
6562 'size' => 2 + $ln, // size in bytes of data structure
6563 );
6564 }
6565
6566
6575 private static function _readUnicodeStringShort($subData)
6576 {
6577 $value = '';
6578
6579 // offset: 0: size: 1; length of the string (character count)
6580 $characterCount = ord($subData[0]);
6581
6582 $string = self::_readUnicodeString(substr($subData, 1), $characterCount);
6583
6584 // add 1 for the string length
6585 $string['size'] += 1;
6586
6587 return $string;
6588 }
6589
6590
6599 private static function _readUnicodeStringLong($subData)
6600 {
6601 $value = '';
6602
6603 // offset: 0: size: 2; length of the string (character count)
6604 $characterCount = self::_GetInt2d($subData, 0);
6605
6606 $string = self::_readUnicodeString(substr($subData, 2), $characterCount);
6607
6608 // add 2 for the string length
6609 $string['size'] += 2;
6610
6611 return $string;
6612 }
6613
6614
6624 private static function _readUnicodeString($subData, $characterCount)
6625 {
6626 $value = '';
6627
6628 // offset: 0: size: 1; option flags
6629
6630 // bit: 0; mask: 0x01; character compression (0 = compressed 8-bit, 1 = uncompressed 16-bit)
6631 $isCompressed = !((0x01 & ord($subData[0])) >> 0);
6632
6633 // bit: 2; mask: 0x04; Asian phonetic settings
6634 $hasAsian = (0x04) & ord($subData[0]) >> 2;
6635
6636 // bit: 3; mask: 0x08; Rich-Text settings
6637 $hasRichText = (0x08) & ord($subData[0]) >> 3;
6638
6639 // offset: 1: size: var; character array
6640 // this offset assumes richtext and Asian phonetic settings are off which is generally wrong
6641 // needs to be fixed
6642 $value = self::_encodeUTF16(substr($subData, 1, $isCompressed ? $characterCount : 2 * $characterCount), $isCompressed);
6643
6644 return array(
6645 'value' => $value,
6646 'size' => $isCompressed ? 1 + $characterCount : 1 + 2 * $characterCount, // the size in bytes including the option flags
6647 );
6648 }
6649
6650
6658 private static function _UTF8toExcelDoubleQuoted($value)
6659 {
6660 return '"' . str_replace('"', '""', $value) . '"';
6661 }
6662
6663
6670 private static function _extractNumber($data)
6671 {
6672 $rknumhigh = self::_GetInt4d($data, 4);
6673 $rknumlow = self::_GetInt4d($data, 0);
6674 $sign = ($rknumhigh & 0x80000000) >> 31;
6675 $exp = (($rknumhigh & 0x7ff00000) >> 20) - 1023;
6676 $mantissa = (0x100000 | ($rknumhigh & 0x000fffff));
6677 $mantissalow1 = ($rknumlow & 0x80000000) >> 31;
6678 $mantissalow2 = ($rknumlow & 0x7fffffff);
6679 $value = $mantissa / pow( 2 , (20 - $exp));
6680
6681 if ($mantissalow1 != 0) {
6682 $value += 1 / pow (2 , (21 - $exp));
6683 }
6684
6685 $value += $mantissalow2 / pow (2 , (52 - $exp));
6686 if ($sign) {
6687 $value *= -1;
6688 }
6689
6690 return $value;
6691 }
6692
6693
6694 private static function _GetIEEE754($rknum)
6695 {
6696 if (($rknum & 0x02) != 0) {
6697 $value = $rknum >> 2;
6698 } else {
6699 // changes by mmp, info on IEEE754 encoding from
6700 // research.microsoft.com/~hollasch/cgindex/coding/ieeefloat.html
6701 // The RK format calls for using only the most significant 30 bits
6702 // of the 64 bit floating point value. The other 34 bits are assumed
6703 // to be 0 so we use the upper 30 bits of $rknum as follows...
6704 $sign = ($rknum & 0x80000000) >> 31;
6705 $exp = ($rknum & 0x7ff00000) >> 20;
6706 $mantissa = (0x100000 | ($rknum & 0x000ffffc));
6707 $value = $mantissa / pow( 2 , (20- ($exp - 1023)));
6708 if ($sign) {
6709 $value = -1 * $value;
6710 }
6711 //end of changes by mmp
6712 }
6713 if (($rknum & 0x01) != 0) {
6714 $value /= 100;
6715 }
6716 return $value;
6717 }
6718
6719
6727 private static function _encodeUTF16($string, $compressed = '')
6728 {
6729 if ($compressed) {
6730 $string = self::_uncompressByteString($string);
6731 }
6732
6733 return PHPExcel_Shared_String::ConvertEncoding($string, 'UTF-8', 'UTF-16LE');
6734 }
6735
6736
6743 private static function _uncompressByteString($string)
6744 {
6745 $uncompressedString = '';
6746 $strLen = strlen($string);
6747 for ($i = 0; $i < $strLen; ++$i) {
6748 $uncompressedString .= $string[$i] . "\0";
6749 }
6750
6751 return $uncompressedString;
6752 }
6753
6754
6761 private function _decodeCodepage($string)
6762 {
6763 return PHPExcel_Shared_String::ConvertEncoding($string, 'UTF-8', $this->_codepage);
6764 }
6765
6766
6774 public static function _GetInt2d($data, $pos)
6775 {
6776 return ord($data[$pos]) | (ord($data[$pos+1]) << 8);
6777 }
6778
6779
6787 public static function _GetInt4d($data, $pos)
6788 {
6789 // FIX: represent numbers correctly on 64-bit system
6790 // http://sourceforge.net/tracker/index.php?func=detail&aid=1487372&group_id=99160&atid=623334
6791 // Hacked by Andreas Rehm 2006 to ensure correct result of the <<24 block on 32 and 64bit systems
6792 $_or_24 = ord($data[$pos + 3]);
6793 if ($_or_24 >= 128) {
6794 // negative number
6795 $_ord_24 = -abs((256 - $_or_24) << 24);
6796 } else {
6797 $_ord_24 = ($_or_24 & 127) << 24;
6798 }
6799 return ord($data[$pos]) | (ord($data[$pos+1]) << 8) | (ord($data[$pos+2]) << 16) | $_ord_24;
6800 }
6801
6802
6810 private static function _readColor($color,$palette,$version)
6811 {
6812 if ($color <= 0x07 || $color >= 0x40) {
6813 // special built-in color
6814 return self::_mapBuiltInColor($color);
6815 } elseif (isset($palette) && isset($palette[$color - 8])) {
6816 // palette color, color index 0x08 maps to pallete index 0
6817 return $palette[$color - 8];
6818 } else {
6819 // default color table
6820 if ($version == self::XLS_BIFF8) {
6821 return self::_mapColor($color);
6822 } else {
6823 // BIFF5
6824 return self::_mapColorBIFF5($color);
6825 }
6826 }
6827
6828 return $color;
6829 }
6830
6831
6839 private static function _mapBorderStyle($index)
6840 {
6841 switch ($index) {
6842 case 0x00: return PHPExcel_Style_Border::BORDER_NONE;
6843 case 0x01: return PHPExcel_Style_Border::BORDER_THIN;
6844 case 0x02: return PHPExcel_Style_Border::BORDER_MEDIUM;
6845 case 0x03: return PHPExcel_Style_Border::BORDER_DASHED;
6846 case 0x04: return PHPExcel_Style_Border::BORDER_DOTTED;
6847 case 0x05: return PHPExcel_Style_Border::BORDER_THICK;
6848 case 0x06: return PHPExcel_Style_Border::BORDER_DOUBLE;
6849 case 0x07: return PHPExcel_Style_Border::BORDER_HAIR;
6851 case 0x09: return PHPExcel_Style_Border::BORDER_DASHDOT;
6856 default: return PHPExcel_Style_Border::BORDER_NONE;
6857 }
6858 }
6859
6860
6868 private static function _mapFillPattern($index)
6869 {
6870 switch ($index) {
6871 case 0x00: return PHPExcel_Style_Fill::FILL_NONE;
6872 case 0x01: return PHPExcel_Style_Fill::FILL_SOLID;
6890 default: return PHPExcel_Style_Fill::FILL_NONE;
6891 }
6892 }
6893
6894
6901 private static function _mapErrorCode($subData)
6902 {
6903 switch ($subData) {
6904 case 0x00: return '#NULL!'; break;
6905 case 0x07: return '#DIV/0!'; break;
6906 case 0x0F: return '#VALUE!'; break;
6907 case 0x17: return '#REF!'; break;
6908 case 0x1D: return '#NAME?'; break;
6909 case 0x24: return '#NUM!'; break;
6910 case 0x2A: return '#N/A'; break;
6911 default: return false;
6912 }
6913 }
6914
6915
6922 private static function _mapBuiltInColor($color)
6923 {
6924 switch ($color) {
6925 case 0x00: return array('rgb' => '000000');
6926 case 0x01: return array('rgb' => 'FFFFFF');
6927 case 0x02: return array('rgb' => 'FF0000');
6928 case 0x03: return array('rgb' => '00FF00');
6929 case 0x04: return array('rgb' => '0000FF');
6930 case 0x05: return array('rgb' => 'FFFF00');
6931 case 0x06: return array('rgb' => 'FF00FF');
6932 case 0x07: return array('rgb' => '00FFFF');
6933 case 0x40: return array('rgb' => '000000'); // system window text color
6934 case 0x41: return array('rgb' => 'FFFFFF'); // system window background color
6935 default: return array('rgb' => '000000');
6936 }
6937 }
6938
6939
6946 private static function _mapColorBIFF5($subData)
6947 {
6948 switch ($subData) {
6949 case 0x08: return array('rgb' => '000000');
6950 case 0x09: return array('rgb' => 'FFFFFF');
6951 case 0x0A: return array('rgb' => 'FF0000');
6952 case 0x0B: return array('rgb' => '00FF00');
6953 case 0x0C: return array('rgb' => '0000FF');
6954 case 0x0D: return array('rgb' => 'FFFF00');
6955 case 0x0E: return array('rgb' => 'FF00FF');
6956 case 0x0F: return array('rgb' => '00FFFF');
6957 case 0x10: return array('rgb' => '800000');
6958 case 0x11: return array('rgb' => '008000');
6959 case 0x12: return array('rgb' => '000080');
6960 case 0x13: return array('rgb' => '808000');
6961 case 0x14: return array('rgb' => '800080');
6962 case 0x15: return array('rgb' => '008080');
6963 case 0x16: return array('rgb' => 'C0C0C0');
6964 case 0x17: return array('rgb' => '808080');
6965 case 0x18: return array('rgb' => '8080FF');
6966 case 0x19: return array('rgb' => '802060');
6967 case 0x1A: return array('rgb' => 'FFFFC0');
6968 case 0x1B: return array('rgb' => 'A0E0F0');
6969 case 0x1C: return array('rgb' => '600080');
6970 case 0x1D: return array('rgb' => 'FF8080');
6971 case 0x1E: return array('rgb' => '0080C0');
6972 case 0x1F: return array('rgb' => 'C0C0FF');
6973 case 0x20: return array('rgb' => '000080');
6974 case 0x21: return array('rgb' => 'FF00FF');
6975 case 0x22: return array('rgb' => 'FFFF00');
6976 case 0x23: return array('rgb' => '00FFFF');
6977 case 0x24: return array('rgb' => '800080');
6978 case 0x25: return array('rgb' => '800000');
6979 case 0x26: return array('rgb' => '008080');
6980 case 0x27: return array('rgb' => '0000FF');
6981 case 0x28: return array('rgb' => '00CFFF');
6982 case 0x29: return array('rgb' => '69FFFF');
6983 case 0x2A: return array('rgb' => 'E0FFE0');
6984 case 0x2B: return array('rgb' => 'FFFF80');
6985 case 0x2C: return array('rgb' => 'A6CAF0');
6986 case 0x2D: return array('rgb' => 'DD9CB3');
6987 case 0x2E: return array('rgb' => 'B38FEE');
6988 case 0x2F: return array('rgb' => 'E3E3E3');
6989 case 0x30: return array('rgb' => '2A6FF9');
6990 case 0x31: return array('rgb' => '3FB8CD');
6991 case 0x32: return array('rgb' => '488436');
6992 case 0x33: return array('rgb' => '958C41');
6993 case 0x34: return array('rgb' => '8E5E42');
6994 case 0x35: return array('rgb' => 'A0627A');
6995 case 0x36: return array('rgb' => '624FAC');
6996 case 0x37: return array('rgb' => '969696');
6997 case 0x38: return array('rgb' => '1D2FBE');
6998 case 0x39: return array('rgb' => '286676');
6999 case 0x3A: return array('rgb' => '004500');
7000 case 0x3B: return array('rgb' => '453E01');
7001 case 0x3C: return array('rgb' => '6A2813');
7002 case 0x3D: return array('rgb' => '85396A');
7003 case 0x3E: return array('rgb' => '4A3285');
7004 case 0x3F: return array('rgb' => '424242');
7005 default: return array('rgb' => '000000');
7006 }
7007 }
7008
7009
7016 private static function _mapColor($subData)
7017 {
7018 switch ($subData) {
7019 case 0x08: return array('rgb' => '000000');
7020 case 0x09: return array('rgb' => 'FFFFFF');
7021 case 0x0A: return array('rgb' => 'FF0000');
7022 case 0x0B: return array('rgb' => '00FF00');
7023 case 0x0C: return array('rgb' => '0000FF');
7024 case 0x0D: return array('rgb' => 'FFFF00');
7025 case 0x0E: return array('rgb' => 'FF00FF');
7026 case 0x0F: return array('rgb' => '00FFFF');
7027 case 0x10: return array('rgb' => '800000');
7028 case 0x11: return array('rgb' => '008000');
7029 case 0x12: return array('rgb' => '000080');
7030 case 0x13: return array('rgb' => '808000');
7031 case 0x14: return array('rgb' => '800080');
7032 case 0x15: return array('rgb' => '008080');
7033 case 0x16: return array('rgb' => 'C0C0C0');
7034 case 0x17: return array('rgb' => '808080');
7035 case 0x18: return array('rgb' => '9999FF');
7036 case 0x19: return array('rgb' => '993366');
7037 case 0x1A: return array('rgb' => 'FFFFCC');
7038 case 0x1B: return array('rgb' => 'CCFFFF');
7039 case 0x1C: return array('rgb' => '660066');
7040 case 0x1D: return array('rgb' => 'FF8080');
7041 case 0x1E: return array('rgb' => '0066CC');
7042 case 0x1F: return array('rgb' => 'CCCCFF');
7043 case 0x20: return array('rgb' => '000080');
7044 case 0x21: return array('rgb' => 'FF00FF');
7045 case 0x22: return array('rgb' => 'FFFF00');
7046 case 0x23: return array('rgb' => '00FFFF');
7047 case 0x24: return array('rgb' => '800080');
7048 case 0x25: return array('rgb' => '800000');
7049 case 0x26: return array('rgb' => '008080');
7050 case 0x27: return array('rgb' => '0000FF');
7051 case 0x28: return array('rgb' => '00CCFF');
7052 case 0x29: return array('rgb' => 'CCFFFF');
7053 case 0x2A: return array('rgb' => 'CCFFCC');
7054 case 0x2B: return array('rgb' => 'FFFF99');
7055 case 0x2C: return array('rgb' => '99CCFF');
7056 case 0x2D: return array('rgb' => 'FF99CC');
7057 case 0x2E: return array('rgb' => 'CC99FF');
7058 case 0x2F: return array('rgb' => 'FFCC99');
7059 case 0x30: return array('rgb' => '3366FF');
7060 case 0x31: return array('rgb' => '33CCCC');
7061 case 0x32: return array('rgb' => '99CC00');
7062 case 0x33: return array('rgb' => 'FFCC00');
7063 case 0x34: return array('rgb' => 'FF9900');
7064 case 0x35: return array('rgb' => 'FF6600');
7065 case 0x36: return array('rgb' => '666699');
7066 case 0x37: return array('rgb' => '969696');
7067 case 0x38: return array('rgb' => '003366');
7068 case 0x39: return array('rgb' => '339966');
7069 case 0x3A: return array('rgb' => '003300');
7070 case 0x3B: return array('rgb' => '333300');
7071 case 0x3C: return array('rgb' => '993300');
7072 case 0x3D: return array('rgb' => '993366');
7073 case 0x3E: return array('rgb' => '333399');
7074 case 0x3F: return array('rgb' => '333333');
7075 default: return array('rgb' => '000000');
7076 }
7077 }
7078
7079
7080 private function _parseRichText($is = '') {
7081 $value = new PHPExcel_RichText();
7082
7083 $value->createText($is);
7084
7085 return $value;
7086 }
7087
7088}
sprintf('%.4f', $callTime)
$baseRow
Definition: 30template.php:66
$column
Definition: 39dropdown.php:62
$richText
Definition: 42richText.php:87
$env
$n
Definition: RandomTest.php:85
$size
Definition: RandomTest.php:84
if(! $in) $columns
Definition: Utf8Test.php:45
if(!isset( $_REQUEST[ 'ReturnTo'])) if(!isset($_REQUEST['AuthId'])) $options
Definition: as_login.php:20
$comment
Definition: buildRTE.php:83
$function
Definition: cas.php:28
An exception for terminatinating execution or to throw for unit testing.
static getRangeBoundaries($pRange='A1:A1')
Calculate range boundaries.
Definition: Cell.php:756
static extractAllCellReferencesInRange($pRange='A1')
Extract all cell references in range.
Definition: Cell.php:854
static stringFromColumnIndex($pColumnIndex=0)
String from columnindex.
Definition: Cell.php:825
static coordinateFromString($pCoordinateString='A1')
Coordinate from string.
Definition: Cell.php:580
static columnIndexFromString($pString='A')
Column index from string.
Definition: Cell.php:782
getReadFilter()
Read filter.
Definition: Abstract.php:173
static _readUnicodeStringLong($subData)
Extracts an Excel Unicode long string (16-bit string length) OpenOffice documentation: 2....
Definition: Excel5.php:6599
_readPageLayoutView()
Read PLV Record(Created by Excel2007 or upper)
Definition: Excel5.php:4297
listWorksheetNames($pFilename)
Reads names of the worksheets from a file, without parsing the whole file to a PHPExcel object.
Definition: Excel5.php:465
_readTopMargin()
Read TOPMARGIN record.
Definition: Excel5.php:3239
static _mapBuiltInColor($color)
Map built-in color to RGB value.
Definition: Excel5.php:6922
_readWindow2()
Read WINDOW2 record.
Definition: Excel5.php:4229
static _GetInt2d($data, $pos)
Read 16-bit unsigned integer.
Definition: Excel5.php:6774
_readBIFF5CellRangeAddressFixed($subData)
Reads a cell range address in BIFF5 e.g.
Definition: Excel5.php:6088
_readPane()
Read PANE record.
Definition: Excel5.php:4357
listWorksheetInfo($pFilename)
Return worksheet info (Name, Last Column Letter, Last Column Index, Total Rows, Total Columns)
Definition: Excel5.php:514
static _readColor($color, $palette, $version)
Read color.
Definition: Excel5.php:6810
const XLS_Type_BOTTOMMARGIN
Definition: Excel5.php:106
static _readUnicodeStringShort($subData)
Extracts an Excel Unicode short string (8-bit string length) OpenOffice documentation: 2....
Definition: Excel5.php:6575
const XLS_Type_RIGHTMARGIN
Definition: Excel5.php:104
_readColInfo()
Read COLINFO record.
Definition: Excel5.php:3442
_readObjectProtect()
OBJECTPROTECT.
Definition: Excel5.php:3380
_readSheetPr()
Read SHEETPR record.
Definition: Excel5.php:3024
static _mapErrorCode($subData)
Map error code, e.g.
Definition: Excel5.php:6901
_readNumber()
Read NUMBER record This record represents a cell that contains a floating-point value.
Definition: Excel5.php:3749
static _mapFillPattern($index)
Get fill pattern from index OpenOffice documentation: 2.5.12.
Definition: Excel5.php:6868
const XLS_Type_SCENPROTECT
Definition: Excel5.php:91
_getNextToken($formulaData, $baseCell='A1')
Fetch next token from binary formula data.
Definition: Excel5.php:5447
_readRk()
Read RK record This record represents a cell that contains an RK value (encoded integer or floating-p...
Definition: Excel5.php:3571
_readMulBlank()
Read MULBLANK record This record represents a cell range of empty cells.
Definition: Excel5.php:4052
_readBlank()
Read BLANK record.
Definition: Excel5.php:4137
canRead($pFilename)
Can the current PHPExcel_Reader_IReader read the file?
Definition: Excel5.php:439
_includeCellRangeFiltered($cellRangeAddress)
Definition: Excel5.php:4433
static _uncompressByteString($string)
Convert UTF-16 string in compressed notation to uncompressed form.
Definition: Excel5.php:6743
_readBoolErr()
Read BOOLERR record This record represents a Boolean value or error value cell.
Definition: Excel5.php:3993
_readBIFF8CellRangeAddress($subData)
Reads a cell range address in BIFF8 e.g.
Definition: Excel5.php:6165
static _mapColor($subData)
Map color array from BIFF8 built-in color index.
Definition: Excel5.php:7016
const XLS_Type_HORIZONTALPAGEBREAKS
Definition: Excel5.php:98
const XLS_Type_EXTERNALBOOK
Definition: Excel5.php:134
_readExternalBook()
Read EXTERNALBOOK record.
Definition: Excel5.php:2589
_readLabel()
Read LABEL record This record represents a cell that contains a string.
Definition: Excel5.php:4094
const XLS_Type_VERTICALPAGEBREAKS
Definition: Excel5.php:97
const XLS_Type_PRINTGRIDLINES
Definition: Excel5.php:107
_readSheetProtection()
Read SHEETPROTECTION record (FEATHEADR)
Definition: Excel5.php:4877
const XLS_Type_DEFAULTROWHEIGHT
Definition: Excel5.php:148
_readScenProtect()
SCENPROTECT.
Definition: Excel5.php:3356
_readVerticalPageBreaks()
Read VERTICALPAGEBREAKS record.
Definition: Excel5.php:3080
_readHorizontalPageBreaks()
Read HORIZONTALPAGEBREAKS record.
Definition: Excel5.php:3051
_readFont()
Read a FONT record.
Definition: Excel5.php:1872
_readString()
Read a STRING record from current stream position and advance the stream pointer to next record This ...
Definition: Excel5.php:3965
_readContinue()
Read a free CONTINUE record.
Definition: Excel5.php:5122
_readStyle()
Read STYLE record.
Definition: Excel5.php:2471
_readBottomMargin()
Read BOTTOMMARGIN record.
Definition: Excel5.php:3257
_readPassword()
PASSWORD - Sheet protection (hashed) password (BIFF2 through BIFF8)
Definition: Excel5.php:3404
_readLabelSst()
Read LABELSST record This record represents a cell that contains a string.
Definition: Excel5.php:3616
_readVcenter()
Read VCENTER record.
Definition: Excel5.php:3183
_readBIFF8CellRangeAddressB($subData, $baseCell='A1')
Reads a cell range address in BIFF8 for shared formulas.
Definition: Excel5.php:6219
_readSheetRangeByRefIndex($index)
Get a sheet range like Sheet1:Sheet3 from REF index Note: If there is only one sheet in the range,...
Definition: Excel5.php:6359
_readMsoDrawingGroup()
Read MSODRAWINGGROUP record.
Definition: Excel5.php:2780
_readFilepass()
FILEPASS.
Definition: Excel5.php:1671
_readNote()
The NOTE record specifies a comment associated with a particular cell.
Definition: Excel5.php:1516
const XLS_Type_DEFINEDNAME
Definition: Excel5.php:96
_readDefaultRowHeight()
Read DEFAULTROWHEIGHT record.
Definition: Excel5.php:3006
static _extractNumber($data)
Reads first 8 bytes of a string and return IEEE 754 float.
Definition: Excel5.php:6670
const XLS_Type_MERGEDCELLS
Definition: Excel5.php:128
const XLS_Type_DATAVALIDATIONS
Definition: Excel5.php:135
static _mapColorBIFF5($subData)
Map color array from BIFF5 built-in color index.
Definition: Excel5.php:6946
_readMsoDrawing()
Read MSODRAWING record.
Definition: Excel5.php:4169
_readHcenter()
Read HCENTER record.
Definition: Excel5.php:3163
const XLS_Type_PAGELAYOUTVIEW
Definition: Excel5.php:159
const XLS_WorkbookGlobals
Definition: Excel5.php:83
load($pFilename)
Loads PHPExcel from file.
Definition: Excel5.php:609
_readDataValidations()
Read DATAVALIDATIONS record.
Definition: Excel5.php:4664
_makeKey($block, $valContext)
Definition: Excel5.php:1708
_readObj()
Read OBJ record.
Definition: Excel5.php:4184
_readBIFF8CellAddressB($cellAddressStructure, $baseCell='A1')
Reads a cell address in BIFF8 for shared formulas.
Definition: Excel5.php:6044
_readExternName()
Read EXTERNNAME record.
Definition: Excel5.php:2653
_getFormulaFromStructure($formulaStructure, $baseCell='A1')
Convert formula structure into human readable Excel formula like 'A3+A5*5'.
Definition: Excel5.php:5216
_getSplicedRecordData()
Reads a record from current position in data stream and continues reading data as long as CONTINUE re...
Definition: Excel5.php:5175
_readSelection()
Read SELECTION record.
Definition: Excel5.php:4385
_readMergedCells()
MERGEDCELLS.
Definition: Excel5.php:4462
const XLS_Type_SHEETLAYOUT
Definition: Excel5.php:157
const XLS_Type_SHEETPROTECTION
Definition: Excel5.php:155
_readDataValidation()
Read DATAVALIDATION record.
Definition: Excel5.php:4677
_readTextObject()
The TEXT Object record contains the text associated with a cell annotation.
Definition: Excel5.php:1579
_readBof()
Read BOF.
Definition: Excel5.php:1619
_readBIFF8CellRangeAddressFixed($subData)
Reads a cell range address in BIFF8 e.g.
Definition: Excel5.php:6127
_readSheetLayout()
Read SHEETLAYOUT record.
Definition: Excel5.php:4837
_readScl()
Read SCL record.
Definition: Excel5.php:4335
_readRightMargin()
Read RIGHTMARGIN record.
Definition: Excel5.php:3221
static _GetInt4d($data, $pos)
Read 32-bit signed integer.
Definition: Excel5.php:6787
const XLS_Type_MSODRAWINGGROUP
Definition: Excel5.php:129
static _readRGB($rgb)
Extract RGB color OpenOffice.org's Documentation of the Microsoft Excel File Format,...
Definition: Excel5.php:6504
_readRecordData($data, $pos, $len)
Read record data from stream, decrypting as required.
Definition: Excel5.php:1106
const XLS_Type_DATAVALIDATION
Definition: Excel5.php:138
const XLS_Type_EXTERNSHEET
Definition: Excel5.php:95
_readRangeProtection()
Read RANGEPROTECTION record Reading of this record is based on Microsoft Office Excel 97-2000 Binary ...
Definition: Excel5.php:4978
_readSummaryInformation()
Read summary information.
Definition: Excel5.php:1183
_readSst()
SST - Shared String Table.
Definition: Excel5.php:2803
_decodeCodepage($string)
Convert string to UTF-8.
Definition: Excel5.php:6761
_readDateMode()
DATEMODE.
Definition: Excel5.php:1853
_readMulRk()
Read MULRK record This record represents a cell range containing RK value cells.
Definition: Excel5.php:3694
_readSharedFmla()
Read a SHAREDFMLA record.
Definition: Excel5.php:3932
__construct()
Create a new PHPExcel_Reader_Excel5 instance.
Definition: Excel5.php:427
_readPrintGridlines()
Read PRINTGRIDLINES record.
Definition: Excel5.php:2987
_readDefColWidth()
Read DEFCOLWIDTH record.
Definition: Excel5.php:3423
_readFooter()
Read FOOTER record.
Definition: Excel5.php:3136
_readImData()
Read IMDATA record.
Definition: Excel5.php:5039
_readDefault()
Reads a general type of BIFF record.
Definition: Excel5.php:1502
_verifyPassword($password, $docid, $salt_data, $hashedsalt_data, &$valContext)
Definition: Excel5.php:1742
static _mapBorderStyle($index)
Map border style OpenOffice documentation: 2.5.11.
Definition: Excel5.php:6839
_readBIFF5CellRangeAddressList($subData)
Read BIFF5 cell range address list section 2.5.15.
Definition: Excel5.php:6328
const XLS_Type_OBJECTPROTECT
Definition: Excel5.php:90
_readBIFF8CellAddress($cellAddressStructure)
Reads a cell address in BIFF8 e.g.
Definition: Excel5.php:6012
static _readBIFF8Constant($valueData)
read BIFF8 constant value which may be 'Empty Value', 'Number', 'String Value', 'Boolean Value',...
Definition: Excel5.php:6454
_loadOLE($pFilename)
Use OLE reader to extract the relevant data streams from the OLE file.
Definition: Excel5.php:1159
_readLeftMargin()
Read LEFTMARGIN record.
Definition: Excel5.php:3203
_readHeader()
Read HEADER record.
Definition: Excel5.php:3108
static _readBIFF8ConstantArray($arrayData)
read BIFF8 constant value array from array data returns e.g.
Definition: Excel5.php:6415
const XLS_Type_RANGEPROTECTION
Definition: Excel5.php:156
_readByteStringLong($subData)
Read byte string (16-bit string length) OpenOffice documentation: 2.5.2.
Definition: Excel5.php:6551
_createFormulaFromTokens($tokens, $additionalData)
Take array of tokens together with additional data for formula and return human readable formula.
Definition: Excel5.php:5283
_readPalette()
Read PALETTE record.
Definition: Excel5.php:2512
_readPageSetup()
Read PAGESETUP record.
Definition: Excel5.php:3275
_readByteStringShort($subData)
Read byte string (8-bit string length) OpenOffice documentation: 2.5.2.
Definition: Excel5.php:6529
_readCodepage()
CODEPAGE.
Definition: Excel5.php:1826
_readProtect()
PROTECT - Sheet protection (BIFF2 through BIFF8) if this record is omitted, then it also means no she...
Definition: Excel5.php:3333
_getFormulaFromData($formulaData, $additionalData='', $baseCell='A1')
Take formula data and additional data for formula and return human readable formula.
Definition: Excel5.php:5256
_readXf()
XF - Extended Format.
Definition: Excel5.php:2008
static _readUnicodeString($subData, $characterCount)
Read Unicode string with no string length field, but with known character count this function is unde...
Definition: Excel5.php:6624
_readExternSheet()
Read EXTERNSHEET record.
Definition: Excel5.php:2688
static _GetIEEE754($rknum)
Definition: Excel5.php:6694
_readBIFF8CellRangeAddressList($subData)
Read BIFF8 cell range address list section 2.5.15.
Definition: Excel5.php:6300
_readDocumentSummaryInformation()
Read additional document summary information.
Definition: Excel5.php:1343
_readDefinedName()
DEFINEDNAME.
Definition: Excel5.php:2725
_readFormula()
Read FORMULA record + perhaps a following STRING record if formula result is a string This record con...
Definition: Excel5.php:3791
const XLS_Type_DEFCOLWIDTH
Definition: Excel5.php:113
_readHyperLink()
Read HYPERLINK record.
Definition: Excel5.php:4485
static _encodeUTF16($string, $compressed='')
Get UTF-8 string from (compressed or uncompressed) UTF-16 string.
Definition: Excel5.php:6727
static _UTF8toExcelDoubleQuoted($value)
Convert UTF-8 string to string surounded by double quotes.
Definition: Excel5.php:6658
static NumberToName($codePage=1252)
Convert Microsoft Code Page Identifier to Code Page Name which iconv and mbstring understands.
Definition: CodePage.php:46
static setExcelCalendar($baseDate)
Set the Excel calendar (Windows 1900 or Mac 1904)
Definition: Date.php:91
const CALENDAR_MAC_1904
Definition: Date.php:41
const CALENDAR_WINDOWS_1900
constants
Definition: Date.php:40
static getDistanceX(PHPExcel_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: Excel5.php:144
static getDistanceY(PHPExcel_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: Excel5.php:175
static sizeCol($sheet, $col='A')
Get the width of a column in pixels.
Definition: Excel5.php:46
static sizeRow($sheet, $row=1)
Convert the height of a cell from user's units to pixels.
Definition: Excel5.php:93
static OLE2LocalDate($string)
Returns a timestamp from an OLE container's date.
Definition: OLE.php:509
static ConvertEncoding($value, $to, $from)
Convert string from one encoding to another.
Definition: String.php:493
static CountCharacters($value, $enc='UTF-8')
Get character count.
Definition: String.php:550
static Substring($pValue='', $pStart=0, $pLength=0)
Get a substring of a UTF-8 encoded string.
Definition: String.php:572
const HORIZONTAL_CENTER_CONTINUOUS
Definition: Alignment.php:43
const BORDER_MEDIUMDASHDOTDOT
Definition: Border.php:48
const BORDER_MEDIUMDASHED
Definition: Border.php:49
const BORDER_SLANTDASHDOT
Definition: Border.php:50
const BORDER_MEDIUMDASHDOT
Definition: Border.php:47
const BORDER_DASHDOTDOT
Definition: Border.php:41
const FILL_PATTERN_LIGHTUP
Definition: Fill.php:57
const FILL_SOLID
Definition: Fill.php:40
const FILL_PATTERN_DARKDOWN
Definition: Fill.php:43
const FILL_PATTERN_DARKUP
Definition: Fill.php:48
const FILL_PATTERN_LIGHTVERTICAL
Definition: Fill.php:58
const FILL_PATTERN_LIGHTGRAY
Definition: Fill.php:53
const FILL_PATTERN_LIGHTDOWN
Definition: Fill.php:52
const FILL_PATTERN_DARKGRAY
Definition: Fill.php:44
const FILL_PATTERN_GRAY125
Definition: Fill.php:51
const FILL_PATTERN_LIGHTHORIZONTAL
Definition: Fill.php:55
const FILL_PATTERN_DARKTRELLIS
Definition: Fill.php:47
const FILL_PATTERN_GRAY0625
Definition: Fill.php:50
const FILL_NONE
Definition: Fill.php:39
const FILL_PATTERN_MEDIUMGRAY
Definition: Fill.php:59
const FILL_PATTERN_DARKVERTICAL
Definition: Fill.php:49
const FILL_PATTERN_DARKHORIZONTAL
Definition: Fill.php:46
const FILL_PATTERN_LIGHTGRID
Definition: Fill.php:54
const FILL_PATTERN_LIGHTTRELLIS
Definition: Fill.php:56
const FILL_PATTERN_DARKGRID
Definition: Fill.php:45
const UNDERLINE_DOUBLE
Definition: Font.php:40
const UNDERLINE_SINGLEACCOUNTING
Definition: Font.php:43
const UNDERLINE_DOUBLEACCOUNTING
Definition: Font.php:41
const UNDERLINE_SINGLE
Definition: Font.php:42
static builtInFormatCode($pIndex)
Get built-in format code.
const PROTECTION_INHERIT
Protection styles.
Definition: Protection.php:39
const SHEETSTATE_HIDDEN
Definition: Worksheet.php:45
const SHEETSTATE_VERYHIDDEN
Definition: Worksheet.php:46
const SHEETSTATE_VISIBLE
Definition: Worksheet.php:44
$key
Definition: croninfo.php:18
$lc
Definition: date.php:267
$i
Definition: disco.tpl.php:19
$y
Definition: example_007.php:83
$x
Definition: example_009.php:98
$px
$angle
$py
$r
Definition: example_031.php:79
$code
Definition: example_050.php:99
if(!array_key_exists('StateId', $_REQUEST)) $id
$error
Definition: Error.php:17
if($format !==null) $name
Definition: metadata.php:146
$index
Definition: metadata.php:60
$lr
defined( 'APPLICATION_ENV')||define( 'APPLICATION_ENV'
Definition: bootstrap.php:27
$type
$url
$s
Definition: pwgen.php:45
$password
Definition: pwgen.php:17
foreach($_POST as $key=> $value) $res
$text
Definition: errorreport.php:18