File Source for Excel5.php

PHPExcel_Reader_Excel5

# Source for file Excel5.php

Documentation is available at Excel5.php

1. <?php
2. /**
3. * PHPExcel
4. *
5. * Copyright (c) 2006 - 2011 PHPExcel
6. *
7. * This library is free software; you can redistribute it and/or
8. * modify it under the terms of the GNU Lesser General Public
9. * License as published by the Free Software Foundation; either
10. * version 2.1 of the License, or (at your option) any later version.
11. *
12. * This library is distributed in the hope that it will be useful,
13. * but WITHOUT ANY WARRANTY; without even the implied warranty of
14. * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
15. * Lesser General Public License for more details.
16. *
17. * You should have received a copy of the GNU Lesser General Public
18. * License along with this library; if not, write to the Free Software
19. * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
20. *
21. * @category PHPExcel
22. * @package PHPExcel_Reader_Excel5
23. * @copyright Copyright (c) 2006 - 2011 PHPExcel (http://www.codeplex.com/PHPExcel)
24. * @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL
25. * @version 1.7.6, 2011-02-27
26. */
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
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
60. /** PHPExcel root directory */
61. if (! defined('PHPEXCEL_ROOT')) {
62. /**
63. * @ignore
64. */
65. define('PHPEXCEL_ROOT', dirname(__FILE__) . '/../../');
66. require(PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php');
67. }
69. /**
70. * PHPExcel_Reader_Excel5
71. *
72. * This class uses {@link http://sourceforge.net/projects/phpexcelreader/parseXL}
73. *
74. * @category PHPExcel
75. * @package PHPExcel_Reader_Excel5
76. * @copyright Copyright (c) 2006 - 2011 PHPExcel (http://www.codeplex.com/PHPExcel)
77. */
78. class PHPExcel_Reader_Excel5 implements PHPExcel_Reader_IReader
79. {
80. // ParseXL definitions
81. const XLS_BIFF8 = 0x0600;
82. const XLS_BIFF7 = 0x0500;
83. const XLS_WorkbookGlobals = 0x0005;
84. const XLS_Worksheet = 0x0010;
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;
97. const XLS_Type_VERTICALPAGEBREAKS = 0x001a;
98. const XLS_Type_HORIZONTALPAGEBREAKS = 0x001b;
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;
107. const XLS_Type_PRINTGRIDLINES = 0x002b;
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;
129. const XLS_Type_MSODRAWINGGROUP = 0x00eb;
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;
135. const XLS_Type_DATAVALIDATIONS = 0x01b2;
136. const XLS_Type_TXO = 0x01b6;
137. const XLS_Type_HYPERLINK = 0x01b8;
138. const XLS_Type_DATAVALIDATION = 0x01be;
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;
148. const XLS_Type_DEFAULTROWHEIGHT = 0x0225;
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;
155. const XLS_Type_SHEETPROTECTION = 0x0867;
156. const XLS_Type_RANGEPROTECTION = 0x0868;
157. const XLS_Type_SHEETLAYOUT = 0x0862;
158. const XLS_Type_XFEXT = 0x087d;
159. const XLS_Type_UNKNOWN = 0xffff;
161. /**
162. * Read data only?
163. * Identifies whether the Reader should only read data values for cells, and ignore any formatting information;
164. * or whether it should read both data and formatting
165. *
166. * @var boolean
167. */
168. private $_readDataOnly = false;
170. /**
171. * Restrict which sheets should be loaded?
172. * This property holds an array of worksheet names to be loaded. If null, then all worksheets will be loaded.
173. *
174. * @var array of string
175. */
176. private $_loadSheetsOnly = null;
178. /**
179. * PHPExcel_Reader_IReadFilter instance
180. *
181. * @var PHPExcel_Reader_IReadFilter
182. */
183. private $_readFilter = null;
185. /**
186. * Summary Information stream data.
187. *
188. * @var string
189. */
190. private $_summaryInformation;
192. /**
193. * Extended Summary Information stream data.
194. *
195. * @var string
196. */
197. private $_documentSummaryInformation;
199. /**
200. * User-Defined Properties stream data.
201. *
202. * @var string
203. */
204. private $_userDefinedProperties;
206. /**
207. * Workbook stream data. (Includes workbook globals substream as well as sheet substreams)
208. *
209. * @var string
210. */
211. private $_data;
213. /**
214. * Size in bytes of $this->_data
215. *
216. * @var int
217. */
218. private $_dataSize;
220. /**
221. * Current position in stream
222. *
223. * @var integer
224. */
225. private $_pos;
227. /**
228. * Workbook to be returned by the reader.
229. *
230. * @var PHPExcel
231. */
232. private $_phpExcel;
234. /**
235. * Worksheet that is currently being built by the reader.
236. *
237. * @var PHPExcel_Worksheet
238. */
239. private $_phpSheet;
241. /**
242. * BIFF version
243. *
244. * @var int
245. */
246. private $_version;
248. /**
249. * Codepage set in the Excel file being read. Only important for BIFF5 (Excel 5.0 - Excel 95)
250. * For BIFF8 (Excel 97 - Excel 2003) this will always have the value 'UTF-16LE'
251. *
252. * @var string
253. */
254. private $_codepage;
256. /**
257. * Shared formats
258. *
259. * @var array
260. */
261. private $_formats;
263. /**
264. * Shared fonts
265. *
266. * @var array
267. */
268. private $_objFonts;
270. /**
271. * Color palette
272. *
273. * @var array
274. */
275. private $_palette;
277. /**
278. * Worksheets
279. *
280. * @var array
281. */
282. private $_sheets;
284. /**
285. * External books
286. *
287. * @var array
288. */
289. private $_externalBooks;
291. /**
292. * REF structures. Only applies to BIFF8.
293. *
294. * @var array
295. */
296. private $_ref;
298. /**
299. * External names
300. *
301. * @var array
302. */
303. private $_externalNames;
305. /**
306. * Defined names
307. *
308. * @var array
309. */
310. private $_definedname;
312. /**
313. * Shared strings. Only applies to BIFF8.
314. *
315. * @var array
316. */
317. private $_sst;
319. /**
320. * Panes are frozen? (in sheet currently being read). See WINDOW2 record.
321. *
322. * @var boolean
323. */
324. private $_frozen;
326. /**
327. * Fit printout to number of pages? (in sheet currently being read). See SHEETPR record.
328. *
329. * @var boolean
330. */
331. private $_isFitToPages;
333. /**
334. * Objects. One OBJ record contributes with one entry.
335. *
336. * @var array
337. */
338. private $_objs;
340. /**
341. * Text Objects. One TXO record corresponds with one entry.
342. *
343. * @var array
344. */
345. private $_textObjects;
347. /**
348. * Cell Annotations (BIFF8)
349. *
350. * @var array
351. */
352. private $_cellNotes;
354. /**
355. * The combined MSODRAWINGGROUP data
356. *
357. * @var string
358. */
359. private $_drawingGroupData;
361. /**
362. * The combined MSODRAWING data (per sheet)
363. *
364. * @var string
365. */
366. private $_drawingData;
368. /**
369. * Keep track of XF index
370. *
371. * @var int
372. */
373. private $_xfIndex;
375. /**
376. * Mapping of XF index (that is a cell XF) to final index in cellXf collection
377. *
378. * @var array
379. */
380. private $_mapCellXfIndex;
382. /**
383. * Mapping of XF index (that is a style XF) to final index in cellStyleXf collection
384. *
385. * @var array
386. */
387. private $_mapCellStyleXfIndex;
389. /**
390. * The shared formulas in a sheet. One SHAREDFMLA record contributes with one value.
391. *
392. * @var array
393. */
394. private $_sharedFormulas;
396. /**
397. * The shared formula parts in a sheet. One FORMULA record contributes with one value if it
398. * refers to a shared formula.
399. *
400. * @var array
401. */
402. private $_sharedFormulaParts;
405. /**
406. * Read data only?
407. * If this is true, then the Reader will only read data values for cells, it will not read any formatting information.
408. * If false (the default) it will read data and formatting.
409. *
410. * @return boolean
411. */
412. public function getReadDataOnly()
413. {
414. return $this->_readDataOnly;
415. }
417. /**
418. * Set read data only
419. * Set to true, to advise the Reader only to read data values for cells, and to ignore any formatting information.
420. * Set to false (the default) to advise the Reader to read both data and formatting for cells.
421. *
422. * @param boolean $pValue
423. *
424. * @return PHPExcel_Reader_Excel5
425. */
426. public function setReadDataOnly($pValue = false)
427. {
428. $this->_readDataOnly = $pValue;
429. return $this;
430. }
432. /**
433. * Get which sheets to load
434. * Returns either an array of worksheet names (the list of worksheets that should be loaded), or a null
435. * indicating that all worksheets in the workbook should be loaded.
436. *
437. * @return mixed
438. */
439. public function getLoadSheetsOnly()
440. {
441. return $this->_loadSheetsOnly;
442. }
444. /**
445. * Set which sheets to load
446. *
447. * @param mixed $value
448. * This should be either an array of worksheet names to be loaded, or a string containing a single worksheet name.
449. * If NULL, then it tells the Reader to read all worksheets in the workbook
450. *
451. * @return PHPExcel_Reader_Excel5
452. */
453. public function setLoadSheetsOnly($value = null)
454. {
455. $this->_loadSheetsOnly = is_array($value) ?
456. $value : array($value);
457. return $this;
458. }
460. /**
461. * Set all sheets to load
462. * Tells the Reader to load all worksheets from the workbook.
463. *
464. * @return PHPExcel_Reader_Excel5
465. */
466. public function setLoadAllSheets()
467. {
468. $this->_loadSheetsOnly = null;
469. return $this;
470. }
472. /**
473. * Read filter
474. *
475. * @return PHPExcel_Reader_IReadFilter
476. */
477. public function getReadFilter() {
478. return $this->_readFilter;
479. }
481. /**
482. * Set read filter
483. *
484. * @param PHPExcel_Reader_IReadFilter $pValue
485. * @return PHPExcel_Reader_Excel5
486. */
487. public function setReadFilter(PHPExcel_Reader_IReadFilter $pValue) {
488. $this->_readFilter = $pValue;
489. return $this;
490. }
492. /**
493. * Create a new PHPExcel_Reader_Excel5 instance
494. */
495. public function __construct() {
496. $this->_readFilter = new PHPExcel_Reader_DefaultReadFilter();
497. }
499. /**
500. * Can the current PHPExcel_Reader_IReader read the file?
501. *
502. * @param string $pFileName
503. * @return boolean
504. */
505. public function canRead($pFilename)
506. {
507. // Check if file exists
508. if (! file_exists($pFilename)) {
509. throw new Exception("Could not open " . $pFilename . " for reading! File does not exist.");
510. }
512. try {
513. // Use ParseXL for the hard work.
514. $ole = new PHPExcel_Shared_OLERead();
516. // get excel data
517. $res = $ole->read($pFilename);
518. return true;
520. } catch (Exception $e) {
521. return false;
522. }
523. }
525. /**
526. * Reads names of the worksheets from a file, without parsing the whole file to a PHPExcel object
527. *
528. * @param string $pFilename
529. * @throws Exception
530. */
531. public function listWorksheetNames($pFilename)
532. {
533. // Check if file exists
534. if (! file_exists($pFilename)) {
535. throw new Exception("Could not open " . $pFilename . " for reading! File does not exist.");
536. }
538. $worksheetNames = array();
540. // Read the OLE file
541. $this->_loadOLE($pFilename);
543. // total byte size of Excel data (workbook global substream + sheet substreams)
544. $this->_dataSize = strlen($this->_data);
546. $this->_pos = 0;
547. $this->_sheets = array();
549. // Parse Workbook Global Substream
550. while ($this->_pos < $this->_dataSize) {
551. $code = self::_GetInt2d($this->_data, $this->_pos);
553. switch ($code) {
554. case self:: XLS_Type_BOF: $this->_readBof(); break;
555. case self:: XLS_Type_SHEET: $this->_readSheet(); break;
556. case self:: XLS_Type_EOF: $this->_readDefault(); break 2;
557. default: $this->_readDefault(); break;
558. }
559. }
561. foreach ($this->_sheets as $sheet) {
562. if ($sheet['sheetType'] != 0x00) {
563. // 0x00: Worksheet, 0x02: Chart, 0x06: Visual Basic module
564. continue;
565. }
567. $worksheetNames[] = $sheet['name'];
568. }
570. return $worksheetNames;
571. }
574. /**
575. * Loads PHPExcel from file
576. *
577. * @param string $pFilename
578. * @return PHPExcel
579. * @throws Exception
580. */
581. public function load($pFilename)
582. {
583. // Read the OLE file
584. $this->_loadOLE($pFilename);
586. // Initialisations
587. $this->_phpExcel = new PHPExcel;
588. $this->_phpExcel->removeSheetByIndex(0); // remove 1st sheet
589. if (!$this->_readDataOnly) {
590. $this->_phpExcel->removeCellStyleXfByIndex(0); // remove the default style
591. $this->_phpExcel->removeCellXfByIndex(0); // remove the default style
592. }
594. // Read the summary information stream (containing meta data)
595. $this->_readSummaryInformation();
597. // Read the Additional document summary information stream (containing application-specific meta data)
598. $this->_readDocumentSummaryInformation();
600. // total byte size of Excel data (workbook global substream + sheet substreams)
601. $this->_dataSize = strlen($this->_data);
603. // initialize
604. $this->_pos = 0;
605. $this->_codepage = 'CP1252';
606. $this->_formats = array();
607. $this->_objFonts = array();
608. $this->_palette = array();
609. $this->_sheets = array();
610. $this->_externalBooks = array();
611. $this->_ref = array();
612. $this->_definedname = array();
613. $this->_sst = array();
614. $this->_drawingGroupData = '';
615. $this->_xfIndex = '';
616. $this->_mapCellXfIndex = array();
617. $this->_mapCellStyleXfIndex = array();
619. // Parse Workbook Global Substream
620. while ($this->_pos < $this->_dataSize) {
621. $code = self::_GetInt2d($this->_data, $this->_pos);
623. switch ($code) {
624. case self:: XLS_Type_BOF: $this->_readBof(); break;
625. case self:: XLS_Type_FILEPASS: $this->_readFilepass(); break;
626. case self:: XLS_Type_CODEPAGE: $this->_readCodepage(); break;
627. case self:: XLS_Type_DATEMODE: $this->_readDateMode(); break;
628. case self:: XLS_Type_FONT: $this->_readFont(); break;
629. case self:: XLS_Type_FORMAT: $this->_readFormat(); break;
630. case self:: XLS_Type_XF: $this->_readXf(); break;
631. case self:: XLS_Type_XFEXT: $this->_readXfExt(); break;
632. case self:: XLS_Type_STYLE: $this->_readStyle(); break;
633. case self:: XLS_Type_PALETTE: $this->_readPalette(); break;
634. case self:: XLS_Type_SHEET: $this->_readSheet(); break;
635. case self:: XLS_Type_EXTERNALBOOK: $this->_readExternalBook(); break;
636. case self:: XLS_Type_EXTERNNAME: $this->_readExternName(); break;
637. case self:: XLS_Type_EXTERNSHEET: $this->_readExternSheet(); break;
638. case self:: XLS_Type_DEFINEDNAME: $this->_readDefinedName(); break;
639. case self:: XLS_Type_MSODRAWINGGROUP: $this->_readMsoDrawingGroup(); break;
640. case self:: XLS_Type_SST: $this->_readSst(); break;
641. case self:: XLS_Type_EOF: $this->_readDefault(); break 2;
642. default: $this->_readDefault(); break;
643. }
644. }
646. // Resolve indexed colors for font, fill, and border colors
647. // Cannot be resolved already in XF record, because PALETTE record comes afterwards
648. if (!$this->_readDataOnly) {
649. foreach ($this->_objFonts as $objFont) {
650. if (isset($objFont->colorIndex)) {
651. $color = self::_readColor($objFont->colorIndex,$this->_palette,$this->_version);
652. $objFont->getColor()->setRGB($color['rgb']);
653. }
654. }
656. foreach ($this->_phpExcel->getCellXfCollection() as $objStyle) {
657. // fill start and end color
658. $fill = $objStyle->getFill();
660. if (isset($fill->startcolorIndex)) {
661. $startColor = self::_readColor($fill->startcolorIndex,$this->_palette,$this->_version);
662. $fill->getStartColor()->setRGB($startColor['rgb']);
663. }
665. if (isset($fill->endcolorIndex)) {
666. $endColor = self::_readColor($fill->endcolorIndex,$this->_palette,$this->_version);
667. $fill->getEndColor()->setRGB($endColor['rgb']);
668. }
670. // border colors
671. $top = $objStyle->getBorders()->getTop();
672. $right = $objStyle->getBorders()->getRight();
673. $bottom = $objStyle->getBorders()->getBottom();
674. $left = $objStyle->getBorders()->getLeft();
675. $diagonal = $objStyle->getBorders()->getDiagonal();
677. if (isset($top->colorIndex)) {
678. $borderTopColor = self::_readColor($top->colorIndex,$this->_palette,$this->_version);
679. $top->getColor()->setRGB($borderTopColor['rgb']);
680. }
682. if (isset($right->colorIndex)) {
683. $borderRightColor = self::_readColor($right->colorIndex,$this->_palette,$this->_version);
684. $right->getColor()->setRGB($borderRightColor['rgb']);
685. }
687. if (isset($bottom->colorIndex)) {
688. $borderBottomColor = self::_readColor($bottom->colorIndex,$this->_palette,$this->_version);
689. $bottom->getColor()->setRGB($borderBottomColor['rgb']);
690. }
692. if (isset($left->colorIndex)) {
693. $borderLeftColor = self::_readColor($left->colorIndex,$this->_palette,$this->_version);
694. $left->getColor()->setRGB($borderLeftColor['rgb']);
695. }
697. if (isset($diagonal->colorIndex)) {
698. $borderDiagonalColor = self::_readColor($diagonal->colorIndex,$this->_palette,$this->_version);
699. $diagonal->getColor()->setRGB($borderDiagonalColor['rgb']);
700. }
701. }
702. }
704. // treat MSODRAWINGGROUP records, workbook-level Escher
705. if (!$this->_readDataOnly && $this->_drawingGroupData) {
706. $escherWorkbook = new PHPExcel_Shared_Escher();
707. $reader = new PHPExcel_Reader_Excel5_Escher($escherWorkbook);
708. $escherWorkbook = $reader->load($this->_drawingGroupData);
710. // debug Escher stream
711. //$debug = new Debug_Escher(new PHPExcel_Shared_Escher());
712. //$debug->load($this->_drawingGroupData);
713. }
715. // Parse the individual sheets
716. foreach ($this->_sheets as $sheet) {
718. if ($sheet['sheetType'] != 0x00) {
719. // 0x00: Worksheet, 0x02: Chart, 0x06: Visual Basic module
720. continue;
721. }
723. // check if sheet should be skipped
724. if (isset($this->_loadSheetsOnly) && ! in_array($sheet['name'], $this->_loadSheetsOnly)) {
725. continue;
726. }
728. // add sheet to PHPExcel object
729. $this->_phpSheet = $this->_phpExcel->createSheet();
730. $this->_phpSheet->setTitle($sheet['name']);
731. $this->_phpSheet->setSheetState($sheet['sheetState']);
733. $this->_pos = $sheet['offset'];
735. // Initialize isFitToPages. May change after reading SHEETPR record.
736. $this->_isFitToPages = false;
738. // Initialize drawingData
739. $this->_drawingData = '';
741. // Initialize objs
742. $this->_objs = array();
744. // Initialize shared formula parts
745. $this->_sharedFormulaParts = array();
747. // Initialize shared formulas
748. $this->_sharedFormulas = array();
750. // Initialize text objs
751. $this->_textObjects = array();
753. // Initialize cell annotations
754. $this->_cellNotes = array();
755. $this->textObjRef = -1;
757. while ($this->_pos <= $this->_dataSize - 4) {
758. $code = self::_GetInt2d($this->_data, $this->_pos);
760. switch ($code) {
761. case self:: XLS_Type_BOF: $this->_readBof(); break;
762. case self:: XLS_Type_PRINTGRIDLINES: $this->_readPrintGridlines(); break;
763. case self:: XLS_Type_DEFAULTROWHEIGHT: $this->_readDefaultRowHeight(); break;
764. case self:: XLS_Type_SHEETPR: $this->_readSheetPr(); break;
765. case self:: XLS_Type_HORIZONTALPAGEBREAKS: $this->_readHorizontalPageBreaks(); break;
766. case self:: XLS_Type_VERTICALPAGEBREAKS: $this->_readVerticalPageBreaks(); break;
767. case self:: XLS_Type_HEADER: $this->_readHeader(); break;
768. case self:: XLS_Type_FOOTER: $this->_readFooter(); break;
769. case self:: XLS_Type_HCENTER: $this->_readHcenter(); break;
770. case self:: XLS_Type_VCENTER: $this->_readVcenter(); break;
771. case self:: XLS_Type_LEFTMARGIN: $this->_readLeftMargin(); break;
772. case self:: XLS_Type_RIGHTMARGIN: $this->_readRightMargin(); break;
773. case self:: XLS_Type_TOPMARGIN: $this->_readTopMargin(); break;
774. case self:: XLS_Type_BOTTOMMARGIN: $this->_readBottomMargin(); break;
775. case self:: XLS_Type_PAGESETUP: $this->_readPageSetup(); break;
776. case self:: XLS_Type_PROTECT: $this->_readProtect(); break;
777. case self:: XLS_Type_SCENPROTECT: $this->_readScenProtect(); break;
778. case self:: XLS_Type_OBJECTPROTECT: $this->_readObjectProtect(); break;
779. case self:: XLS_Type_PASSWORD: $this->_readPassword(); break;
780. case self:: XLS_Type_DEFCOLWIDTH: $this->_readDefColWidth(); break;
781. case self:: XLS_Type_COLINFO: $this->_readColInfo(); break;
782. case self:: XLS_Type_DIMENSION: $this->_readDefault(); break;
783. case self:: XLS_Type_ROW: $this->_readRow(); break;
784. case self:: XLS_Type_DBCELL: $this->_readDefault(); break;
785. case self:: XLS_Type_RK: $this->_readRk(); break;
786. case self:: XLS_Type_LABELSST: $this->_readLabelSst(); break;
787. case self:: XLS_Type_MULRK: $this->_readMulRk(); break;
788. case self:: XLS_Type_NUMBER: $this->_readNumber(); break;
789. case self:: XLS_Type_FORMULA: $this->_readFormula(); break;
790. case self:: XLS_Type_SHAREDFMLA: $this->_readSharedFmla(); break;
791. case self:: XLS_Type_BOOLERR: $this->_readBoolErr(); break;
792. case self:: XLS_Type_MULBLANK: $this->_readMulBlank(); break;
793. case self:: XLS_Type_LABEL: $this->_readLabel(); break;
794. case self:: XLS_Type_BLANK: $this->_readBlank(); break;
795. case self:: XLS_Type_MSODRAWING: $this->_readMsoDrawing(); break;
796. case self:: XLS_Type_OBJ: $this->_readObj(); break;
797. case self:: XLS_Type_WINDOW2: $this->_readWindow2(); break;
798. case self:: XLS_Type_SCL: $this->_readScl(); break;
799. case self:: XLS_Type_PANE: $this->_readPane(); break;
800. case self:: XLS_Type_SELECTION: $this->_readSelection(); break;
801. case self:: XLS_Type_MERGEDCELLS: $this->_readMergedCells(); break;
802. case self:: XLS_Type_HYPERLINK: $this->_readHyperLink(); break;
803. case self:: XLS_Type_DATAVALIDATIONS: $this->_readDataValidations(); break;
804. case self:: XLS_Type_DATAVALIDATION: $this->_readDataValidation(); break;
805. case self:: XLS_Type_SHEETLAYOUT: $this->_readSheetLayout(); break;
806. case self:: XLS_Type_SHEETPROTECTION: $this->_readSheetProtection(); break;
807. case self:: XLS_Type_RANGEPROTECTION: $this->_readRangeProtection(); break;
808. case self:: XLS_Type_NOTE: $this->_readNote(); break;
809. //case self::XLS_Type_IMDATA: $this->_readImData(); break;
810. case self:: XLS_Type_TXO: $this->_readTextObject(); break;
811. case self:: XLS_Type_CONTINUE: $this->_readContinue(); break;
812. case self:: XLS_Type_EOF: $this->_readDefault(); break 2;
813. default: $this->_readDefault(); break;
814. }
816. }
818. // treat MSODRAWING records, sheet-level Escher
819. if (!$this->_readDataOnly && $this->_drawingData) {
820. $escherWorksheet = new PHPExcel_Shared_Escher();
821. $reader = new PHPExcel_Reader_Excel5_Escher($escherWorksheet);
822. $escherWorksheet = $reader->load($this->_drawingData);
824. // debug Escher stream
825. //$debug = new Debug_Escher(new PHPExcel_Shared_Escher());
826. //$debug->load($this->_drawingData);
828. // get all spContainers in one long array, so they can be mapped to OBJ records
829. $allSpContainers = $escherWorksheet->getDgContainer()->getSpgrContainer()->getAllSpContainers();
830. }
832. // treat OBJ records
833. foreach ($this->_objs as $n => $obj) {
834. // echo '<hr /><b>Object</b> reference is ',$n,'<br />';
835. // var_dump($obj);
836. // echo '<br />';
838. // the first shape container never has a corresponding OBJ record, hence $n + 1
839. $spContainer = $allSpContainers[$n + 1];
841. // we skip all spContainers that are a part of a group shape since we cannot yet handle those
842. if ($spContainer->getNestingLevel() > 1) {
843. continue;
844. }
846. // calculate the width and height of the shape
847. list($startColumn, $startRow) = PHPExcel_Cell:: coordinateFromString($spContainer->getStartCoordinates());
848. list($endColumn, $endRow) = PHPExcel_Cell:: coordinateFromString($spContainer->getEndCoordinates());
850. $startOffsetX = $spContainer->getStartOffsetX();
851. $startOffsetY = $spContainer->getStartOffsetY();
852. $endOffsetX = $spContainer->getEndOffsetX();
853. $endOffsetY = $spContainer->getEndOffsetY();
855. $width = PHPExcel_Shared_Excel5:: getDistanceX($this->_phpSheet, $startColumn, $startOffsetX, $endColumn, $endOffsetX);
856. $height = PHPExcel_Shared_Excel5:: getDistanceY($this->_phpSheet, $startRow, $startOffsetY, $endRow, $endOffsetY);
858. // calculate offsetX and offsetY of the shape
859. $offsetX = $startOffsetX * PHPExcel_Shared_Excel5:: sizeCol($this->_phpSheet, $startColumn) / 1024;
860. $offsetY = $startOffsetY * PHPExcel_Shared_Excel5:: sizeRow($this->_phpSheet, $startRow) / 256;
862. switch ($obj['otObjType']) {
864. case 0x19:
865. // Note
866. // echo 'Cell Annotation Object<br />';
867. // echo 'Object ID is ',$obj['idObjID'],'<br />';
868. //
869. if (isset($this->_cellNotes[$obj['idObjID']])) {
870. $cellNote = $this->_cellNotes[$obj['idObjID']];
872. // echo '_cellNotes[',$obj['idObjID'],']: ';
873. // var_dump($cellNote);
874. // echo '<br />';
875. //
876. if (isset($this->_textObjects[$obj['idObjID']])) {
877. $textObject = $this->_textObjects[$obj['idObjID']];
878. // echo '_textObject: ';
879. // var_dump($textObject);
880. // echo '<br />';
881. //
882. $this->_cellNotes[$obj['idObjID']]['objTextData'] = $textObject;
883. $text = $textObject['text'];
884. }
885. // echo $text,'<br />';
886. }
887. break;
889. case 0x08:
890. // echo 'Picture Object<br />';
891. // picture
893. // get index to BSE entry (1-based)
894. $BSEindex = $spContainer->getOPT(0x0104);
895. $BSECollection = $escherWorkbook->getDggContainer()->getBstoreContainer()->getBSECollection();
896. $BSE = $BSECollection[$BSEindex - 1];
897. $blipType = $BSE->getBlipType();
899. // need check because some blip types are not supported by Escher reader such as EMF
900. if ($blip = $BSE->getBlip()) {
901. $ih = imagecreatefromstring($blip->getData());
902. $drawing = new PHPExcel_Worksheet_MemoryDrawing();
903. $drawing->setImageResource($ih);
905. // width, height, offsetX, offsetY
906. $drawing->setResizeProportional(false);
907. $drawing->setWidth($width);
908. $drawing->setHeight($height);
909. $drawing->setOffsetX($offsetX);
910. $drawing->setOffsetY($offsetY);
912. switch ($blipType) {
913. case PHPExcel_Shared_Escher_DggContainer_BstoreContainer_BSE:: BLIPTYPE_JPEG:
914. $drawing->setRenderingFunction( PHPExcel_Worksheet_MemoryDrawing:: RENDERING_JPEG);
915. $drawing->setMimeType( PHPExcel_Worksheet_MemoryDrawing:: MIMETYPE_JPEG);
916. break;
918. case PHPExcel_Shared_Escher_DggContainer_BstoreContainer_BSE:: BLIPTYPE_PNG:
919. $drawing->setRenderingFunction( PHPExcel_Worksheet_MemoryDrawing:: RENDERING_PNG);
920. $drawing->setMimeType( PHPExcel_Worksheet_MemoryDrawing:: MIMETYPE_PNG);
921. break;
922. }
924. $drawing->setWorksheet($this->_phpSheet);
925. $drawing->setCoordinates($spContainer->getStartCoordinates());
926. }
928. break;
930. default:
931. // other object type
932. break;
934. }
935. }
937. // treat SHAREDFMLA records
938. if ($this->_version == self:: XLS_BIFF8) {
939. foreach ($this->_sharedFormulaParts as $cell => $baseCell) {
940. list($column, $row) = PHPExcel_Cell:: coordinateFromString($cell);
941. if ( ! is_null($this-> getReadFilter()) && $this-> getReadFilter()->readCell($column, $row, $this->_phpSheet->getTitle()) ) {
942. $formula = $this->_getFormulaFromStructure($this->_sharedFormulas[$baseCell], $cell);
943. $this->_phpSheet->getCell($cell)->setValueExplicit('=' . $formula, PHPExcel_Cell_DataType:: TYPE_FORMULA);
944. }
945. }
946. }
948. if ( count($this->_cellNotes) > 0) {
949. foreach($this->_cellNotes as $note => $noteDetails) {
950. // echo '<b>Cell annotation ',$note,'</b><br />';
951. // var_dump($noteDetails);
952. // echo '<br />';
953. $cellAddress = str_replace('$','',$noteDetails['cellRef']);
954. $this->_phpSheet->getComment( $cellAddress )
955. ->setAuthor( $noteDetails['author'] )
956. ->setText($this->_parseRichText($noteDetails['objTextData']['text']) );
957. }
958. }
959. }
961. // add the named ranges (defined names)
962. foreach ($this->_definedname as $definedName) {
963. if ($definedName['isBuiltInName']) {
964. switch ($definedName['name']) {
966. case pack('C', 0x06):
967. // print area
968. // in general, formula looks like this: Foo!$C$7:$J$66,Bar!$A$1:$IV$2
970. $ranges = explode(',', $definedName['formula']); // FIXME: what if sheetname contains comma?
972. $extractedRanges = array();
973. foreach ($ranges as $range) {
974. // $range should look like one of these
975. // Foo!$C$7:$J$66
976. // Bar!$A$1:$IV$2
978. $explodes = explode('!', $range); // FIXME: what if sheetname contains exclamation mark?
979. $sheetName = $explodes[0];
981. if ( count($explodes) == 2) {
982. $extractedRanges[] = str_replace('$', '', $explodes[1]); // C7:J66
983. }
984. }
985. if ($docSheet = $this->_phpExcel->getSheetByName($sheetName)) {
986. $docSheet->getPageSetup()->setPrintArea( implode(',', $extractedRanges)); // C7:J66,A1:IV2
987. }
988. break;
990. case pack('C', 0x07):
991. // print titles (repeating rows)
992. // Assuming BIFF8, there are 3 cases
993. // 1. repeating rows
994. // formula looks like this: Sheet!$A$1:$IV$2
995. // rows 1-2 repeat
996. // 2. repeating columns
997. // formula looks like this: Sheet!$A$1:$B$65536
998. // columns A-B repeat
999. // 3. both repeating rows and repeating columns
1000. // formula looks like this: Sheet!$A$1:$B$65536,Sheet!$A$1:$IV$2
1002. $ranges = explode(',', $definedName['formula']); // FIXME: what if sheetname contains comma?
1004. foreach ($ranges as $range) {
1005. // $range should look like this one of these
1006. // Sheet!$A$1:$B$65536
1007. // Sheet!$A$1:$IV$2
1009. $explodes = explode('!', $range);
1011. if ( count($explodes) == 2) {
1012. if ($docSheet = $this->_phpExcel->getSheetByName($explodes[0])) {
1014. $extractedRange = $explodes[1];
1015. $extractedRange = str_replace('$', '', $extractedRange);
1017. $coordinateStrings = explode(':', $extractedRange);
1018. if ( count($coordinateStrings) == 2) {
1019. list($firstColumn, $firstRow) = PHPExcel_Cell:: coordinateFromString($coordinateStrings[0]);
1020. list($lastColumn, $lastRow) = PHPExcel_Cell:: coordinateFromString($coordinateStrings[1]);
1022. if ($firstColumn == 'A' and $lastColumn == 'IV') {
1023. // then we have repeating rows
1024. $docSheet->getPageSetup()->setRowsToRepeatAtTop(array($firstRow, $lastRow));
1025. } elseif ($firstRow == 1 and $lastRow == 65536) {
1026. // then we have repeating columns
1027. $docSheet->getPageSetup()->setColumnsToRepeatAtLeft(array($firstColumn, $lastColumn));
1028. }
1029. }
1030. }
1031. }
1032. }
1033. break;
1035. }
1036. } else {
1037. // Extract range
1038. $explodes = explode('!', $definedName['formula']);
1040. if ( count($explodes) == 2) {
1041. if ($docSheet = $this->_phpExcel->getSheetByName($explodes[0])) {
1042. $extractedRange = $explodes[1];
1043. $extractedRange = str_replace('$', '', $extractedRange);
1045. $localOnly = ($definedName['scope'] == 0) ? false : true;
1046. $scope = ($definedName['scope'] == 0) ?
1047. null : $this->_phpExcel->getSheetByName($this->_sheets[$definedName['scope'] - 1]['name']);
1049. $this->_phpExcel->addNamedRange( new PHPExcel_NamedRange((string)$definedName['name'], $docSheet, $extractedRange, $localOnly, $scope) );
1050. }
1051. }
1052. }
1053. }
1055. return $this->_phpExcel;
1056. }
1058. /**
1059. * Use OLE reader to extract the relevant data streams from the OLE file
1060. *
1061. * @param string $pFilename
1062. */
1063. private function _loadOLE($pFilename)
1064. {
1065. // OLE reader
1066. $ole = new PHPExcel_Shared_OLERead();
1068. // get excel data,
1069. $res = $ole->read($pFilename);
1070. // Get workbook data: workbook stream + sheet streams
1071. $this->_data = $ole->getStream($ole->wrkbook);
1073. // Get summary information data
1074. $this->_summaryInformation = $ole->getStream($ole->summaryInformation);
1076. // Get additional document summary information data
1077. $this->_documentSummaryInformation = $ole->getStream($ole->documentSummaryInformation);
1079. // Get user-defined property data
1080. // $this->_userDefinedProperties = $ole->getUserDefinedProperties();
1081. }
1083. /**
1084. * Read summary information
1085. */
1086. private function _readSummaryInformation()
1087. {
1088. if (!isset($this->_summaryInformation)) {
1089. return;
1090. }
1092. // offset: 0; size: 2; must be 0xFE 0xFF (UTF-16 LE byte order mark)
1093. // offset: 2; size: 2;
1094. // offset: 4; size: 2; OS version
1095. // offset: 6; size: 2; OS indicator
1096. // offset: 8; size: 16
1097. // offset: 24; size: 4; section count
1098. $secCount = self::_GetInt4d($this->_summaryInformation, 24);
1100. // 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
1101. // offset: 44; size: 4
1102. $secOffset = self::_GetInt4d($this->_summaryInformation, 44);
1104. // section header
1105. // offset: $secOffset; size: 4; section length
1106. $secLength = self::_GetInt4d($this->_summaryInformation, $secOffset);
1108. // offset: $secOffset+4; size: 4; property count
1109. $countProperties = self::_GetInt4d($this->_summaryInformation, $secOffset+4);
1111. // initialize code page (used to resolve string values)
1112. $codePage = 'CP1252';
1114. // offset: ($secOffset+8); size: var
1115. // loop through property decarations and properties
1116. for ($i = 0; $i < $countProperties; ++$i) {
1118. // offset: ($secOffset+8) + (8 * $i); size: 4; property ID
1119. $id = self::_GetInt4d($this->_summaryInformation, ($secOffset+8) + (8 * $i));
1121. // Use value of property id as appropriate
1122. // offset: ($secOffset+12) + (8 * $i); size: 4; offset from beginning of section (48)
1123. $offset = self::_GetInt4d($this->_summaryInformation, ($secOffset+12) + (8 * $i));
1125. $type = self::_GetInt4d($this->_summaryInformation, $secOffset + $offset);
1127. // initialize property value
1128. $value = null;
1130. // extract property value based on property type
1131. switch ($type) {
1132. case 0x02: // 2 byte signed integer
1133. $value = self::_GetInt2d($this->_summaryInformation, $secOffset + 4 + $offset);
1134. break;
1136. case 0x03: // 4 byte signed integer
1137. $value = self::_GetInt4d($this->_summaryInformation, $secOffset + 4 + $offset);
1138. break;
1140. case 0x13: // 4 byte unsigned integer
1141. // not needed yet, fix later if necessary
1142. break;
1144. case 0x1E: // null-terminated string prepended by dword string length
1145. $byteLength = self::_GetInt4d($this->_summaryInformation, $secOffset + 4 + $offset);
1146. $value = substr($this->_summaryInformation, $secOffset + 8 + $offset, $byteLength);
1147. $value = PHPExcel_Shared_String:: ConvertEncoding($value, 'UTF-8', $codePage);
1148. $value = rtrim($value);
1149. break;
1151. case 0x40: // Filetime (64-bit value representing the number of 100-nanosecond intervals since January 1, 1601)
1152. // PHP-time
1153. $value = PHPExcel_Shared_OLE:: OLE2LocalDate( substr($this->_summaryInformation, $secOffset + 4 + $offset, 8));
1154. break;
1156. case 0x47: // Clipboard format
1157. // not needed yet, fix later if necessary
1158. break;
1159. }
1161. switch ($id) {
1162. case 0x01: // Code Page
1163. $codePage = PHPExcel_Shared_CodePage:: NumberToName($value);
1164. break;
1166. case 0x02: // Title
1167. $this->_phpExcel->getProperties()->setTitle($value);
1168. break;
1170. case 0x03: // Subject
1171. $this->_phpExcel->getProperties()->setSubject($value);
1172. break;
1174. case 0x04: // Author (Creator)
1175. $this->_phpExcel->getProperties()->setCreator($value);
1176. break;
1178. case 0x05: // Keywords
1179. $this->_phpExcel->getProperties()->setKeywords($value);
1180. break;
1182. case 0x06: // Comments (Description)
1183. $this->_phpExcel->getProperties()->setDescription($value);
1184. break;
1186. case 0x07: // Template
1187. // Not supported by PHPExcel
1188. break;
1190. case 0x08: // Last Saved By (LastModifiedBy)
1191. $this->_phpExcel->getProperties()->setLastModifiedBy($value);
1192. break;
1194. case 0x09: // Revision
1195. // Not supported by PHPExcel
1196. break;
1198. case 0x0A: // Total Editing Time
1199. // Not supported by PHPExcel
1200. break;
1202. case 0x0B: // Last Printed
1203. // Not supported by PHPExcel
1204. break;
1206. case 0x0C: // Created Date/Time
1207. $this->_phpExcel->getProperties()->setCreated($value);
1208. break;
1210. case 0x0D: // Modified Date/Time
1211. $this->_phpExcel->getProperties()->setModified($value);
1212. break;
1214. case 0x0E: // Number of Pages
1215. // Not supported by PHPExcel
1216. break;
1218. case 0x0F: // Number of Words
1219. // Not supported by PHPExcel
1220. break;
1222. case 0x10: // Number of Characters
1223. // Not supported by PHPExcel
1224. break;
1226. case 0x11: // Thumbnail
1227. // Not supported by PHPExcel
1228. break;
1230. case 0x12: // Name of creating application
1231. // Not supported by PHPExcel
1232. break;
1234. case 0x13: // Security
1235. // Not supported by PHPExcel
1236. break;
1238. }
1239. }
1240. }
1242. /**
1243. * Read additional document summary information
1244. */
1245. private function _readDocumentSummaryInformation()
1246. {
1247. if (!isset($this->_documentSummaryInformation)) {
1248. return;
1249. }
1251. // offset: 0; size: 2; must be 0xFE 0xFF (UTF-16 LE byte order mark)
1252. // offset: 2; size: 2;
1253. // offset: 4; size: 2; OS version
1254. // offset: 6; size: 2; OS indicator
1255. // offset: 8; size: 16
1256. // offset: 24; size: 4; section count
1257. $secCount = self::_GetInt4d($this->_documentSummaryInformation, 24);
1258. // echo '$secCount = ',$secCount,'<br />';
1260. // 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
1261. // offset: 44; size: 4; first section offset
1262. $secOffset = self::_GetInt4d($this->_documentSummaryInformation, 44);
1263. // echo '$secOffset = ',$secOffset,'<br />';
1265. // section header
1266. // offset: $secOffset; size: 4; section length
1267. $secLength = self::_GetInt4d($this->_documentSummaryInformation, $secOffset);
1268. // echo '$secLength = ',$secLength,'<br />';
1270. // offset: $secOffset+4; size: 4; property count
1271. $countProperties = self::_GetInt4d($this->_documentSummaryInformation, $secOffset+4);
1272. // echo '$countProperties = ',$countProperties,'<br />';
1274. // initialize code page (used to resolve string values)
1275. $codePage = 'CP1252';
1277. // offset: ($secOffset+8); size: var
1278. // loop through property decarations and properties
1279. for ($i = 0; $i < $countProperties; ++$i) {
1280. // echo 'Property ',$i,'<br />';
1281. // offset: ($secOffset+8) + (8 * $i); size: 4; property ID
1282. $id = self::_GetInt4d($this->_documentSummaryInformation, ($secOffset+8) + (8 * $i));
1283. // echo 'ID is ',$id,'<br />';
1285. // Use value of property id as appropriate
1286. // offset: 60 + 8 * $i; size: 4; offset from beginning of section (48)
1287. $offset = self::_GetInt4d($this->_documentSummaryInformation, ($secOffset+12) + (8 * $i));
1289. $type = self::_GetInt4d($this->_documentSummaryInformation, $secOffset + $offset);
1290. // echo 'Type is ',$type,', ';
1292. // initialize property value
1293. $value = null;
1295. // extract property value based on property type
1296. switch ($type) {
1297. case 0x02: // 2 byte signed integer
1298. $value = self::_GetInt2d($this->_documentSummaryInformation, $secOffset + 4 + $offset);
1299. break;
1301. case 0x03: // 4 byte signed integer
1302. $value = self::_GetInt4d($this->_documentSummaryInformation, $secOffset + 4 + $offset);
1303. break;
1305. case 0x13: // 4 byte unsigned integer
1306. // not needed yet, fix later if necessary
1307. break;
1309. case 0x1E: // null-terminated string prepended by dword string length
1310. $byteLength = self::_GetInt4d($this->_documentSummaryInformation, $secOffset + 4 + $offset);
1311. $value = substr($this->_documentSummaryInformation, $secOffset + 8 + $offset, $byteLength);
1312. $value = PHPExcel_Shared_String:: ConvertEncoding($value, 'UTF-8', $codePage);
1313. $value = rtrim($value);
1314. break;
1316. case 0x40: // Filetime (64-bit value representing the number of 100-nanosecond intervals since January 1, 1601)
1317. // PHP-Time
1318. $value = PHPExcel_Shared_OLE:: OLE2LocalDate( substr($this->_documentSummaryInformation, $secOffset + 4 + $offset, 8));
1319. break;
1321. case 0x47: // Clipboard format
1322. // not needed yet, fix later if necessary
1323. break;
1324. }
1326. switch ($id) {
1327. case 0x01: // Code Page
1328. $codePage = PHPExcel_Shared_CodePage:: NumberToName($value);
1329. break;
1331. case 0x02: // Category
1332. $this->_phpExcel->getProperties()->setCategory($value);
1333. break;
1335. case 0x03: // Presentation Target
1336. // Not supported by PHPExcel
1337. break;
1339. case 0x04: // Bytes
1340. // Not supported by PHPExcel
1341. break;
1343. case 0x05: // Lines
1344. // Not supported by PHPExcel
1345. break;
1347. case 0x06: // Paragraphs
1348. // Not supported by PHPExcel
1349. break;
1351. case 0x07: // Slides
1352. // Not supported by PHPExcel
1353. break;
1355. case 0x08: // Notes
1356. // Not supported by PHPExcel
1357. break;
1359. case 0x09: // Hidden Slides
1360. // Not supported by PHPExcel
1361. break;
1363. case 0x0A: // MM Clips
1364. // Not supported by PHPExcel
1365. break;
1367. case 0x0B: // Scale Crop
1368. // Not supported by PHPExcel
1369. break;
1371. case 0x0C: // Heading Pairs
1372. // Not supported by PHPExcel
1373. break;
1375. case 0x0D: // Titles of Parts
1376. // Not supported by PHPExcel
1377. break;
1379. case 0x0E: // Manager
1380. $this->_phpExcel->getProperties()->setManager($value);
1381. break;
1383. case 0x0F: // Company
1384. $this->_phpExcel->getProperties()->setCompany($value);
1385. break;
1387. case 0x10: // Links up-to-date
1388. // Not supported by PHPExcel
1389. break;
1391. }
1392. }
1393. }
1395. /**
1396. * Reads a general type of BIFF record. Does nothing except for moving stream pointer forward to next record.
1397. */
1398. private function _readDefault()
1399. {
1400. $length = self::_GetInt2d($this->_data, $this->_pos + 2);
1401. // $recordData = substr($this->_data, $this->_pos + 4, $length);
1403. // move stream pointer to next record
1404. $this->_pos += 4 + $length;
1405. }
1408. /**
1409. * The NOTE record specifies a comment associated with a particular cell. In Excel 95 (BIFF7) and earlier versions,
1410. * this record stores a note (cell note). This feature was significantly enhanced in Excel 97.
1411. */
1412. private function _readNote()
1413. {
1414. // echo '<b>Read Cell Annotation</b><br />';
1415. $length = self::_GetInt2d($this->_data, $this->_pos + 2);
1416. $recordData = substr($this->_data, $this->_pos + 4, $length);
1418. // move stream pointer to next record
1419. $this->_pos += 4 + $length;
1421. if ($this->_readDataOnly) {
1422. return;
1423. }
1425. $cellAddress = $this->_readBIFF8CellAddress( substr($recordData, 0, 4));
1426. if ($this->_version == self:: XLS_BIFF8) {
1427. $noteObjID = self::_GetInt2d($recordData, 6);
1428. $noteAuthor = self::_readUnicodeStringLong( substr($recordData, 8));
1429. $noteAuthor = $noteAuthor['value'];
1430. // echo 'Note Address=',$cellAddress,'<br />';
1431. // echo 'Note Object ID=',$noteObjID,'<br />';
1432. // echo 'Note Author=',$noteAuthor,'<hr />';
1433. //
1434. $this->_cellNotes[$noteObjID] = array('cellRef' => $cellAddress,
1435. 'objectID' => $noteObjID,
1436. 'author' => $noteAuthor
1437. );
1438. } else {
1439. $extension = false;
1440. if ($cellAddress == '$B$65536') {
1441. // If the address row is -1 and the column is 0, (which translates as $B$65536) then this is a continuation
1442. // note from the previous cell annotation. We're not yet handling this, so annotations longer than the
1443. // max 2048 bytes will probably throw a wobbly.
1444. $row = self::_GetInt2d($recordData, 0);
1445. $extension = true;
1446. $cellAddress = array_pop( array_keys($this->_phpSheet->getComments()));
1447. }
1448. // echo 'Note Address=',$cellAddress,'<br />';
1450. $cellAddress = str_replace('$','',$cellAddress);
1451. $noteLength = self::_GetInt2d($recordData, 4);
1452. $noteText = trim( substr($recordData, 6));
1453. // echo 'Note Length=',$noteLength,'<br />';
1454. // echo 'Note Text=',$noteText,'<br />';
1456. if ($extension) {
1457. // Concatenate this extension with the currently set comment for the cell
1458. $comment = $this->_phpSheet->getComment( $cellAddress );
1459. $commentText = $comment->getText()->getPlainText();
1460. $comment->setText($this->_parseRichText($commentText.$noteText) );
1461. } else {
1462. // Set comment for the cell
1463. $this->_phpSheet->getComment( $cellAddress )
1464. // ->setAuthor( $author )
1465. ->setText($this->_parseRichText($noteText) );
1466. }
1467. }
1469. }
1471. /**
1472. * The TEXT Object record contains the text associated with a cell annotation.
1473. */
1474. private function _readTextObject()
1475. {
1476. $length = self::_GetInt2d($this->_data, $this->_pos + 2);
1477. $recordData = substr($this->_data, $this->_pos + 4, $length);
1479. // move stream pointer to next record
1480. $this->_pos += 4 + $length;
1482. if ($this->_readDataOnly) {
1483. return;
1484. }
1486. // recordData consists of an array of subrecords looking like this:
1487. // grbit: 2 bytes; Option Flags
1488. // rot: 2 bytes; rotation
1489. // cchText: 2 bytes; length of the text (in the first continue record)
1490. // cbRuns: 2 bytes; length of the formatting (in the second continue record)
1491. // followed by the continuation records containing the actual text and formatting
1492. $grbitOpts = self::_GetInt2d($recordData, 0);
1493. $rot = self::_GetInt2d($recordData, 2);
1494. $cchText = self::_GetInt2d($recordData, 10);
1495. $cbRuns = self::_GetInt2d($recordData, 12);
1496. $text = $this->_getSplicedRecordData();
1498. $this->_textObjects[$this->textObjRef] = array(
1499. 'text' => substr($text["recordData"],$text["spliceOffsets"][0]+1,$cchText),
1500. 'format' => substr($text["recordData"],$text["spliceOffsets"][1],$cbRuns),
1501. 'alignment' => $grbitOpts,
1502. 'rotation' => $rot
1503. );
1505. // echo '<b>_readTextObject()</b><br />';
1506. // var_dump($this->_textObjects[$this->textObjRef]);
1507. // echo '<br />';
1508. }
1510. /**
1511. * Read BOF
1512. */
1513. private function _readBof()
1514. {
1515. $length = self::_GetInt2d($this->_data, $this->_pos + 2);
1516. $recordData = substr($this->_data, $this->_pos + 4, $length);
1518. // move stream pointer to next record
1519. $this->_pos += 4 + $length;
1521. // offset: 2; size: 2; type of the following data
1522. $substreamType = self::_GetInt2d($recordData, 2);
1524. switch ($substreamType) {
1525. case self:: XLS_WorkbookGlobals:
1526. $version = self::_GetInt2d($recordData, 0);
1527. if (($version != self:: XLS_BIFF8) && ($version != self:: XLS_BIFF7)) {
1528. throw new Exception('Cannot read this Excel file. Version is too old.');
1529. }
1530. $this->_version = $version;
1531. break;
1533. case self:: XLS_Worksheet:
1534. // do not use this version information for anything
1535. // it is unreliable (OpenOffice doc, 5.8), use only version information from the global stream
1536. break;
1538. default:
1539. // substream, e.g. chart
1540. // just skip the entire substream
1541. do {
1542. $code = self::_GetInt2d($this->_data, $this->_pos);
1543. $this->_readDefault();
1544. } while ($code != self:: XLS_Type_EOF && $this->_pos < $this->_dataSize);
1545. break;
1546. }
1547. }
1549. /**
1550. * FILEPASS
1551. *
1552. * This record is part of the File Protection Block. It
1553. * contains information about the read/write password of the
1554. * file. All record contents following this record will be
1555. * encrypted.
1556. *
1557. * -- "OpenOffice.org's Documentation of the Microsoft
1558. * Excel File Format"
1559. */
1560. private function _readFilepass()
1561. {
1562. $length = self::_GetInt2d($this->_data, $this->_pos + 2);
1563. // $recordData = substr($this->_data, $this->_pos + 4, $length);
1565. // move stream pointer to next record
1566. $this->_pos += 4 + $length;
1568. throw new Exception('Cannot read encrypted file');
1569. }
1571. /**
1572. * CODEPAGE
1573. *
1574. * This record stores the text encoding used to write byte
1575. * strings, stored as MS Windows code page identifier.
1576. *
1577. * -- "OpenOffice.org's Documentation of the Microsoft
1578. * Excel File Format"
1579. */
1580. private function _readCodepage()
1581. {
1582. $length = self::_GetInt2d($this->_data, $this->_pos + 2);
1583. $recordData = substr($this->_data, $this->_pos + 4, $length);
1585. // move stream pointer to next record
1586. $this->_pos += 4 + $length;
1588. // offset: 0; size: 2; code page identifier
1589. $codepage = self::_GetInt2d($recordData, 0);
1591. $this->_codepage = PHPExcel_Shared_CodePage:: NumberToName($codepage);
1592. }
1594. /**
1595. * DATEMODE
1596. *
1597. * This record specifies the base date for displaying date
1598. * values. All dates are stored as count of days past this
1599. * base date. In BIFF2-BIFF4 this record is part of the
1600. * Calculation Settings Block. In BIFF5-BIFF8 it is
1601. * stored in the Workbook Globals Substream.
1602. *
1603. * -- "OpenOffice.org's Documentation of the Microsoft
1604. * Excel File Format"
1605. */
1606. private function _readDateMode()
1607. {
1608. $length = self::_GetInt2d($this->_data, $this->_pos + 2);
1609. $recordData = substr($this->_data, $this->_pos + 4, $length);
1611. // move stream pointer to next record
1612. $this->_pos += 4 + $length;
1614. // offset: 0; size: 2; 0 = base 1900, 1 = base 1904
1615. PHPExcel_Shared_Date:: setExcelCalendar( PHPExcel_Shared_Date:: CALENDAR_WINDOWS_1900);
1616. if ( ord($recordData{0}) == 1) {
1617. PHPExcel_Shared_Date:: setExcelCalendar( PHPExcel_Shared_Date:: CALENDAR_MAC_1904);
1618. }
1619. }
1621. /**
1622. * Read a FONT record
1623. */
1624. private function _readFont()
1625. {
1626. $length = self::_GetInt2d($this->_data, $this->_pos + 2);
1627. $recordData = substr($this->_data, $this->_pos + 4, $length);
1629. // move stream pointer to next record
1630. $this->_pos += 4 + $length;
1632. if (!$this->_readDataOnly) {
1633. $objFont = new PHPExcel_Style_Font();
1635. // offset: 0; size: 2; height of the font (in twips = 1/20 of a point)
1636. $size = self::_GetInt2d($recordData, 0);
1637. $objFont->setSize($size / 20);
1639. // offset: 2; size: 2; option flags
1640. // bit: 0; mask 0x0001; bold (redundant in BIFF5-BIFF8)
1641. // bit: 1; mask 0x0002; italic
1642. $isItalic = (0x0002 & self::_GetInt2d($recordData, 2)) >> 1;
1643. if ($isItalic) $objFont->setItalic(true);
1645. // bit: 2; mask 0x0004; underlined (redundant in BIFF5-BIFF8)
1646. // bit: 3; mask 0x0008; strike
1647. $isStrike = (0x0008 & self::_GetInt2d($recordData, 2)) >> 3;
1648. if ($isStrike) $objFont->setStrikethrough(true);
1650. // offset: 4; size: 2; colour index
1651. $colorIndex = self::_GetInt2d($recordData, 4);
1652. $objFont->colorIndex = $colorIndex;
1654. // offset: 6; size: 2; font weight
1655. $weight = self::_GetInt2d($recordData, 6);
1656. switch ($weight) {
1657. case 0x02BC:
1658. $objFont->setBold(true);
1659. break;
1660. }
1662. // offset: 8; size: 2; escapement type
1663. $escapement = self::_GetInt2d($recordData, 8);
1664. switch ($escapement) {
1665. case 0x0001:
1666. $objFont->setSuperScript(true);
1667. break;
1668. case 0x0002:
1669. $objFont->setSubScript(true);
1670. break;
1671. }
1673. // offset: 10; size: 1; underline type
1674. $underlineType = ord($recordData{10});
1675. switch ($underlineType) {
1676. case 0x00:
1677. break; // no underline
1678. case 0x01:
1679. $objFont->setUnderline( PHPExcel_Style_Font:: UNDERLINE_SINGLE);
1680. break;
1681. case 0x02:
1682. $objFont->setUnderline( PHPExcel_Style_Font:: UNDERLINE_DOUBLE);
1683. break;
1684. case 0x21:
1685. $objFont->setUnderline( PHPExcel_Style_Font:: UNDERLINE_SINGLEACCOUNTING);
1686. break;
1687. case 0x22:
1688. $objFont->setUnderline( PHPExcel_Style_Font:: UNDERLINE_DOUBLEACCOUNTING);
1689. break;
1690. }
1692. // offset: 11; size: 1; font family
1693. // offset: 12; size: 1; character set
1694. // offset: 13; size: 1; not used
1695. // offset: 14; size: var; font name
1696. if ($this->_version == self:: XLS_BIFF8) {
1697. $string = self::_readUnicodeStringShort( substr($recordData, 14));
1698. } else {
1699. $string = $this->_readByteStringShort( substr($recordData, 14));
1700. }
1701. $objFont->setName($string['value']);
1703. $this->_objFonts[] = $objFont;
1704. }
1705. }
1707. /**
1708. * FORMAT
1709. *
1710. * This record contains information about a number format.
1711. * All FORMAT records occur together in a sequential list.
1712. *
1713. * In BIFF2-BIFF4 other records referencing a FORMAT record
1714. * contain a zero-based index into this list. From BIFF5 on
1715. * the FORMAT record contains the index itself that will be
1716. * used by other records.
1717. *
1718. * -- "OpenOffice.org's Documentation of the Microsoft
1719. * Excel File Format"
1720. */
1721. private function _readFormat()
1722. {
1723. $length = self::_GetInt2d($this->_data, $this->_pos + 2);
1724. $recordData = substr($this->_data, $this->_pos + 4, $length);
1726. // move stream pointer to next record
1727. $this->_pos += 4 + $length;
1729. if (!$this->_readDataOnly) {
1730. $indexCode = self::_GetInt2d($recordData, 0);
1732. if ($this->_version == self:: XLS_BIFF8) {
1733. $string = self::_readUnicodeStringLong( substr($recordData, 2));
1734. } else {
1735. // BIFF7
1736. $string = $this->_readByteStringShort( substr($recordData, 2));
1737. }
1739. $formatString = $string['value'];
1740. $this->_formats[$indexCode] = $formatString;
1741. }
1742. }
1744. /**
1745. * XF - Extended Format
1746. *
1747. * This record contains formatting information for cells, rows, columns or styles.
1748. * According to http://support.microsoft.com/kb/147732 there are always at least 15 cell style XF
1749. * and 1 cell XF.
1750. * Inspection of Excel files generated by MS Office Excel shows that XF records 0-14 are cell style XF
1751. * and XF record 15 is a cell XF
1752. * We only read the first cell style XF and skip the remaining cell style XF records
1753. * We read all cell XF records.
1754. *
1755. * -- "OpenOffice.org's Documentation of the Microsoft
1756. * Excel File Format"
1757. */
1758. private function _readXf()
1759. {
1760. $length = self::_GetInt2d($this->_data, $this->_pos + 2);
1761. $recordData = substr($this->_data, $this->_pos + 4, $length);
1763. // move stream pointer to next record
1764. $this->_pos += 4 + $length;
1766. $objStyle = new PHPExcel_Style();
1768. if (!$this->_readDataOnly) {
1769. // offset: 0; size: 2; Index to FONT record
1770. if (self::_GetInt2d($recordData, 0) < 4) {
1771. $fontIndex = self::_GetInt2d($recordData, 0);
1772. } else {
1773. // this has to do with that index 4 is omitted in all BIFF versions for some strange reason
1774. // check the OpenOffice documentation of the FONT record
1775. $fontIndex = self::_GetInt2d($recordData, 0) - 1;
1776. }
1777. $objStyle->setFont($this->_objFonts[$fontIndex]);
1779. // offset: 2; size: 2; Index to FORMAT record
1780. $numberFormatIndex = self::_GetInt2d($recordData, 2);
1781. if (isset($this->_formats[$numberFormatIndex])) {
1782. // then we have user-defined format code
1783. $numberformat = array('code' => $this->_formats[$numberFormatIndex]);
1784. } elseif (($code = PHPExcel_Style_NumberFormat:: builtInFormatCode($numberFormatIndex)) !== '') {
1785. // then we have built-in format code
1786. $numberformat = array('code' => $code);
1787. } else {
1788. // we set the general format code
1789. $numberformat = array('code' => 'General');
1790. }
1791. $objStyle->getNumberFormat()->setFormatCode($numberformat['code']);
1793. // offset: 4; size: 2; XF type, cell protection, and parent style XF
1794. // bit 2-0; mask 0x0007; XF_TYPE_PROT
1795. $xfTypeProt = self::_GetInt2d($recordData, 4);
1796. // bit 0; mask 0x01; 1 = cell is locked
1797. $isLocked = (0x01 & $xfTypeProt) >> 0;
1798. $objStyle->getProtection()->setLocked($isLocked ?
1799. PHPExcel_Style_Protection:: PROTECTION_INHERIT : PHPExcel_Style_Protection:: PROTECTION_UNPROTECTED);
1801. // bit 1; mask 0x02; 1 = Formula is hidden
1802. $isHidden = (0x02 & $xfTypeProt) >> 1;
1803. $objStyle->getProtection()->setHidden($isHidden ?
1804. PHPExcel_Style_Protection:: PROTECTION_PROTECTED : PHPExcel_Style_Protection:: PROTECTION_UNPROTECTED);
1806. // bit 2; mask 0x04; 0 = Cell XF, 1 = Cell Style XF
1807. $isCellStyleXf = (0x04 & $xfTypeProt) >> 2;
1809. // offset: 6; size: 1; Alignment and text break
1810. // bit 2-0, mask 0x07; horizontal alignment
1811. $horAlign = (0x07 & ord($recordData{6})) >> 0;
1812. switch ($horAlign) {
1813. case 0:
1814. $objStyle->getAlignment()->setHorizontal( PHPExcel_Style_Alignment:: HORIZONTAL_GENERAL);
1815. break;
1816. case 1:
1817. $objStyle->getAlignment()->setHorizontal( PHPExcel_Style_Alignment:: HORIZONTAL_LEFT);
1818. break;
1819. case 2:
1820. $objStyle->getAlignment()->setHorizontal( PHPExcel_Style_Alignment:: HORIZONTAL_CENTER);
1821. break;
1822. case 3:
1823. $objStyle->getAlignment()->setHorizontal( PHPExcel_Style_Alignment:: HORIZONTAL_RIGHT);
1824. break;
1825. case 5:
1826. $objStyle->getAlignment()->setHorizontal( PHPExcel_Style_Alignment:: HORIZONTAL_JUSTIFY);
1827. break;
1828. case 6:
1829. $objStyle->getAlignment()->setHorizontal( PHPExcel_Style_Alignment:: HORIZONTAL_CENTER_CONTINUOUS);
1830. break;
1831. }
1832. // bit 3, mask 0x08; wrap text
1833. $wrapText = (0x08 & ord($recordData{6})) >> 3;
1834. switch ($wrapText) {
1835. case 0:
1836. $objStyle->getAlignment()->setWrapText(false);
1837. break;
1838. case 1:
1839. $objStyle->getAlignment()->setWrapText(true);
1840. break;
1841. }
1842. // bit 6-4, mask 0x70; vertical alignment
1843. $vertAlign = (0x70 & ord($recordData{6})) >> 4;
1844. switch ($vertAlign) {
1845. case 0:
1846. $objStyle->getAlignment()->setVertical( PHPExcel_Style_Alignment:: VERTICAL_TOP);
1847. break;
1848. case 1:
1849. $objStyle->getAlignment()->setVertical( PHPExcel_Style_Alignment:: VERTICAL_CENTER);
1850. break;
1851. case 2:
1852. $objStyle->getAlignment()->setVertical( PHPExcel_Style_Alignment:: VERTICAL_BOTTOM);
1853. break;
1854. case 3:
1855. $objStyle->getAlignment()->setVertical( PHPExcel_Style_Alignment:: VERTICAL_JUSTIFY);
1856. break;
1857. }
1859. if ($this->_version == self:: XLS_BIFF8) {
1860. // offset: 7; size: 1; XF_ROTATION: Text rotation angle
1861. $angle = ord($recordData{7});
1862. $rotation = 0;
1863. if ($angle <= 90) {
1864. $rotation = $angle;
1865. } else if ($angle <= 180) {
1866. $rotation = 90 - $angle;
1867. } else if ($angle == 255) {
1868. $rotation = -165;
1869. }
1870. $objStyle->getAlignment()->setTextRotation($rotation);
1872. // offset: 8; size: 1; Indentation, shrink to cell size, and text direction
1873. // bit: 3-0; mask: 0x0F; indent level
1874. $indent = (0x0F & ord($recordData{8})) >> 0;
1875. $objStyle->getAlignment()->setIndent($indent);
1877. // bit: 4; mask: 0x10; 1 = shrink content to fit into cell
1878. $shrinkToFit = (0x10 & ord($recordData{8})) >> 4;
1879. switch ($shrinkToFit) {
1880. case 0:
1881. $objStyle->getAlignment()->setShrinkToFit(false);
1882. break;
1883. case 1:
1884. $objStyle->getAlignment()->setShrinkToFit(true);
1885. break;
1886. }
1888. // offset: 9; size: 1; Flags used for attribute groups
1890. // offset: 10; size: 4; Cell border lines and background area
1891. // bit: 3-0; mask: 0x0000000F; left style
1892. if ($bordersLeftStyle = self::_mapBorderStyle((0x0000000F & self::_GetInt4d($recordData, 10)) >> 0)) {
1893. $objStyle->getBorders()->getLeft()->setBorderStyle($bordersLeftStyle);
1894. }
1895. // bit: 7-4; mask: 0x000000F0; right style
1896. if ($bordersRightStyle = self::_mapBorderStyle((0x000000F0 & self::_GetInt4d($recordData, 10)) >> 4)) {
1897. $objStyle->getBorders()->getRight()->setBorderStyle($bordersRightStyle);
1898. }
1899. // bit: 11-8; mask: 0x00000F00; top style
1900. if ($bordersTopStyle = self::_mapBorderStyle((0x00000F00 & self::_GetInt4d($recordData, 10)) >> 8)) {
1901. $objStyle->getBorders()->getTop()->setBorderStyle($bordersTopStyle);
1902. }
1903. // bit: 15-12; mask: 0x0000F000; bottom style
1904. if ($bordersBottomStyle = self::_mapBorderStyle((0x0000F000 & self::_GetInt4d($recordData, 10)) >> 12)) {
1905. $objStyle->getBorders()->getBottom()->setBorderStyle($bordersBottomStyle);
1906. }
1907. // bit: 22-16; mask: 0x007F0000; left color
1908. $objStyle->getBorders()->getLeft()->colorIndex = (0x007F0000 & self::_GetInt4d($recordData, 10)) >> 16;
1910. // bit: 29-23; mask: 0x3F800000; right color
1911. $objStyle->getBorders()->getRight()->colorIndex = (0x3F800000 & self::_GetInt4d($recordData, 10)) >> 23;
1913. // bit: 30; mask: 0x40000000; 1 = diagonal line from top left to right bottom
1914. $diagonalDown = (0x40000000 & self::_GetInt4d($recordData, 10)) >> 30 ?
1915. true : false;
1917. // bit: 31; mask: 0x80000000; 1 = diagonal line from bottom left to top right
1918. $diagonalUp = (0x80000000 & self::_GetInt4d($recordData, 10)) >> 31 ?
1919. true : false;
1921. if ($diagonalUp == false && $diagonalDown == false) {
1922. $objStyle->getBorders()->setDiagonalDirection( PHPExcel_Style_Borders:: DIAGONAL_NONE);
1923. } elseif ($diagonalUp == true && $diagonalDown == false) {
1924. $objStyle->getBorders()->setDiagonalDirection( PHPExcel_Style_Borders:: DIAGONAL_UP);
1925. } elseif ($diagonalUp == false && $diagonalDown == true) {
1926. $objStyle->getBorders()->setDiagonalDirection( PHPExcel_Style_Borders:: DIAGONAL_DOWN);
1927. } elseif ($diagonalUp == true && $diagonalDown == true) {
1928. $objStyle->getBorders()->setDiagonalDirection( PHPExcel_Style_Borders:: DIAGONAL_BOTH);
1929. }
1931. // offset: 14; size: 4;
1932. // bit: 6-0; mask: 0x0000007F; top color
1933. $objStyle->getBorders()->getTop()->colorIndex = (0x0000007F & self::_GetInt4d($recordData, 14)) >> 0;
1935. // bit: 13-7; mask: 0x00003F80; bottom color
1936. $objStyle->getBorders()->getBottom()->colorIndex = (0x00003F80 & self::_GetInt4d($recordData, 14)) >> 7;
1938. // bit: 20-14; mask: 0x001FC000; diagonal color
1939. $objStyle->getBorders()->getDiagonal()->colorIndex = (0x001FC000 & self::_GetInt4d($recordData, 14)) >> 14;
1941. // bit: 24-21; mask: 0x01E00000; diagonal style
1942. if ($bordersDiagonalStyle = self::_mapBorderStyle((0x01E00000 & self::_GetInt4d($recordData, 14)) >> 21)) {
1943. $objStyle->getBorders()->getDiagonal()->setBorderStyle($bordersDiagonalStyle);
1944. }
1946. // bit: 31-26; mask: 0xFC000000 fill pattern
1947. if ($fillType = self::_mapFillPattern((0xFC000000 & self::_GetInt4d($recordData, 14)) >> 26)) {
1948. $objStyle->getFill()->setFillType($fillType);
1949. }
1950. // offset: 18; size: 2; pattern and background colour
1951. // bit: 6-0; mask: 0x007F; color index for pattern color
1952. $objStyle->getFill()->startcolorIndex = (0x007F & self::_GetInt2d($recordData, 18)) >> 0;
1954. // bit: 13-7; mask: 0x3F80; color index for pattern background
1955. $objStyle->getFill()->endcolorIndex = (0x3F80 & self::_GetInt2d($recordData, 18)) >> 7;
1956. } else {
1957. // BIFF5
1959. // offset: 7; size: 1; Text orientation and flags
1960. $orientationAndFlags = ord($recordData{7});
1962. // bit: 1-0; mask: 0x03; XF_ORIENTATION: Text orientation
1963. $xfOrientation = (0x03 & $orientationAndFlags) >> 0;
1964. switch ($xfOrientation) {
1965. case 0:
1966. $objStyle->getAlignment()->setTextRotation(0);
1967. break;
1968. case 1:
1969. $objStyle->getAlignment()->setTextRotation(-165);
1970. break;
1971. case 2:
1972. $objStyle->getAlignment()->setTextRotation(90);
1973. break;
1974. case 3:
1975. $objStyle->getAlignment()->setTextRotation(-90);
1976. break;
1977. }
1979. // offset: 8; size: 4; cell border lines and background area
1980. $borderAndBackground = self::_GetInt4d($recordData, 8);
1982. // bit: 6-0; mask: 0x0000007F; color index for pattern color
1983. $objStyle->getFill()->startcolorIndex = (0x0000007F & $borderAndBackground) >> 0;
1985. // bit: 13-7; mask: 0x00003F80; color index for pattern background
1986. $objStyle->getFill()->endcolorIndex = (0x00003F80 & $borderAndBackground) >> 7;
1988. // bit: 21-16; mask: 0x003F0000; fill pattern
1989. $objStyle->getFill()->setFillType(self::_mapFillPattern((0x003F0000 & $borderAndBackground) >> 16));
1991. // bit: 24-22; mask: 0x01C00000; bottom line style
1992. $objStyle->getBorders()->getBottom()->setBorderStyle(self::_mapBorderStyle((0x01C00000 & $borderAndBackground) >> 22));
1994. // bit: 31-25; mask: 0xFE000000; bottom line color
1995. $objStyle->getBorders()->getBottom()->colorIndex = (0xFE000000 & $borderAndBackground) >> 25;
1997. // offset: 12; size: 4; cell border lines
1998. $borderLines = self::_GetInt4d($recordData, 12);
2000. // bit: 2-0; mask: 0x00000007; top line style
2001. $objStyle->getBorders()->getTop()->setBorderStyle(self::_mapBorderStyle((0x00000007 & $borderLines) >> 0));
2003. // bit: 5-3; mask: 0x00000038; left line style
2004. $objStyle->getBorders()->getLeft()->setBorderStyle(self::_mapBorderStyle((0x00000038 & $borderLines) >> 3));
2006. // bit: 8-6; mask: 0x000001C0; right line style
2007. $objStyle->getBorders()->getRight()->setBorderStyle(self::_mapBorderStyle((0x000001C0 & $borderLines) >> 6));
2009. // bit: 15-9; mask: 0x0000FE00; top line color index
2010. $objStyle->getBorders()->getTop()->colorIndex = (0x0000FE00 & $borderLines) >> 9;
2012. // bit: 22-16; mask: 0x007F0000; left line color index
2013. $objStyle->getBorders()->getLeft()->colorIndex = (0x007F0000 & $borderLines) >> 16;
2015. // bit: 29-23; mask: 0x3F800000; right line color index
2016. $objStyle->getBorders()->getRight()->colorIndex = (0x3F800000 & $borderLines) >> 23;
2017. }
2019. // add cellStyleXf or cellXf and update mapping
2020. if ($isCellStyleXf) {
2021. // we only read one style XF record which is always the first
2022. if ($this->_xfIndex == 0) {
2023. $this->_phpExcel->addCellStyleXf($objStyle);
2024. $this->_mapCellStyleXfIndex[$this->_xfIndex] = 0;
2025. }
2026. } else {
2027. // we read all cell XF records
2028. $this->_phpExcel->addCellXf($objStyle);
2029. $this->_mapCellXfIndex[$this->_xfIndex] = count($this->_phpExcel->getCellXfCollection()) - 1;
2030. }
2032. // update XF index for when we read next record
2033. ++$this->_xfIndex;
2034. }
2035. }
2037. /**
2038. *
2039. */
2040. private function _readXfExt()
2041. {
2042. $length = self::_GetInt2d($this->_data, $this->_pos + 2);
2043. $recordData = substr($this->_data, $this->_pos + 4, $length);
2045. // move stream pointer to next record
2046. $this->_pos += 4 + $length;
2048. if (!$this->_readDataOnly) {
2049. // offset: 0; size: 2; 0x087D = repeated header
2051. // offset: 2; size: 2
2053. // offset: 4; size: 8; not used
2055. // offset: 12; size: 2; record version
2057. // offset: 14; size: 2; index to XF record which this record modifies
2058. $ixfe = self::_GetInt2d($recordData, 14);
2060. // offset: 16; size: 2; not used
2062. // offset: 18; size: 2; number of extension properties that follow
2063. $cexts = self::_GetInt2d($recordData, 18);
2065. // start reading the actual extension data
2066. $offset = 20;
2067. while ($offset < $length) {
2068. // extension type
2069. $extType = self::_GetInt2d($recordData, $offset);
2071. // extension length
2072. $cb = self::_GetInt2d($recordData, $offset + 2);
2074. // extension data
2075. $extData = substr($recordData, $offset + 4, $cb);
2077. switch ($extType) {
2078. case 4: // fill start color
2079. $xclfType = self::_GetInt2d($extData, 0); // color type
2080. $xclrValue = substr($extData, 4, 4); // color value (value based on color type)
2082. if ($xclfType == 2) {
2083. $rgb = sprintf('%02X%02X%02X', ord($xclrValue{0}), ord($xclrValue{1}), ord($xclrValue{2}));
2085. // modify the relevant style property
2086. if ( isset($this->_mapCellXfIndex[$ixfe]) ) {
2087. $fill = $this->_phpExcel->getCellXfByIndex($this->_mapCellXfIndex[$ixfe])->getFill();
2088. $fill->getStartColor()->setRGB($rgb);
2089. unset($fill->startcolorIndex); // normal color index does not apply, discard
2090. }
2091. }
2092. break;
2094. case 5: // fill end color
2095. $xclfType = self::_GetInt2d($extData, 0); // color type
2096. $xclrValue = substr($extData, 4, 4); // color value (value based on color type)
2098. if ($xclfType == 2) {
2099. $rgb = sprintf('%02X%02X%02X', ord($xclrValue{0}), ord($xclrValue{1}), ord($xclrValue{2}));
2101. // modify the relevant style property
2102. if ( isset($this->_mapCellXfIndex[$ixfe]) ) {
2103. $fill = $this->_phpExcel->getCellXfByIndex($this->_mapCellXfIndex[$ixfe])->getFill();
2104. $fill->getEndColor()->setRGB($rgb);
2105. unset($fill->endcolorIndex); // normal color index does not apply, discard
2106. }
2107. }
2108. break;
2110. case 7: // border color top
2111. $xclfType = self::_GetInt2d($extData, 0); // color type
2112. $xclrValue = substr($extData, 4, 4); // color value (value based on color type)
2114. if ($xclfType == 2) {
2115. $rgb = sprintf('%02X%02X%02X', ord($xclrValue{0}), ord($xclrValue{1}), ord($xclrValue{2}));
2117. // modify the relevant style property
2118. if ( isset($this->_mapCellXfIndex[$ixfe]) ) {
2119. $top = $this->_phpExcel->getCellXfByIndex($this->_mapCellXfIndex[$ixfe])->getBorders()->getTop();
2120. $top->getColor()->setRGB($rgb);
2121. unset($top->colorIndex); // normal color index does not apply, discard
2122. }
2123. }
2124. break;
2126. case 8: // border color bottom
2127. $xclfType = self::_GetInt2d($extData, 0); // color type
2128. $xclrValue = substr($extData, 4, 4); // color value (value based on color type)
2130. if ($xclfType == 2) {
2131. $rgb = sprintf('%02X%02X%02X', ord($xclrValue{0}), ord($xclrValue{1}), ord($xclrValue{2}));
2133. // modify the relevant style property
2134. if ( isset($this->_mapCellXfIndex[$ixfe]) ) {
2135. $bottom = $this->_phpExcel->getCellXfByIndex($this->_mapCellXfIndex[$ixfe])->getBorders()->getBottom();
2136. $bottom->getColor()->setRGB($rgb);
2137. unset($bottom->colorIndex); // normal color index does not apply, discard
2138. }
2139. }
2140. break;
2142. case 9: // border color left
2143. $xclfType = self::_GetInt2d($extData, 0); // color type
2144. $xclrValue = substr($extData, 4, 4); // color value (value based on color type)
2146. if ($xclfType == 2) {
2147. $rgb = sprintf('%02X%02X%02X', ord($xclrValue{0}), ord($xclrValue{1}), ord($xclrValue{2}));
2149. // modify the relevant style property
2150. if ( isset($this->_mapCellXfIndex[$ixfe]) ) {
2151. $left = $this->_phpExcel->getCellXfByIndex($this->_mapCellXfIndex[$ixfe])->getBorders()->getLeft();
2152. $left->getColor()->setRGB($rgb);
2153. unset($left->colorIndex); // normal color index does not apply, discard
2154. }
2155. }
2156. break;
2158. case 10: // border color right
2159. $xclfType = self::_GetInt2d($extData, 0); // color type
2160. $xclrValue = substr($extData, 4, 4); // color value (value based on color type)
2162. if ($xclfType == 2) {
2163. $rgb = sprintf('%02X%02X%02X', ord($xclrValue{0}), ord($xclrValue{1}), ord($xclrValue{2}));
2165. // modify the relevant style property
2166. if ( isset($this->_mapCellXfIndex[$ixfe]) ) {
2167. $right = $this->_phpExcel->getCellXfByIndex($this->_mapCellXfIndex[$ixfe])->getBorders()->getRight();
2168. $right->getColor()->setRGB($rgb);
2169. unset($right->colorIndex); // normal color index does not apply, discard
2170. }
2171. }
2172. break;
2174. case 11: // border color diagonal
2175. $xclfType = self::_GetInt2d($extData, 0); // color type
2176. $xclrValue = substr($extData, 4, 4); // color value (value based on color type)
2178. if ($xclfType == 2) {
2179. $rgb = sprintf('%02X%02X%02X', ord($xclrValue{0}), ord($xclrValue{1}), ord($xclrValue{2}));
2181. // modify the relevant style property
2182. if ( isset($this->_mapCellXfIndex[$ixfe]) ) {
2183. $diagonal = $this->_phpExcel->getCellXfByIndex($this->_mapCellXfIndex[$ixfe])->getBorders()->getDiagonal();
2184. $diagonal->getColor()->setRGB($rgb);
2185. unset($diagonal->colorIndex); // normal color index does not apply, discard
2186. }
2187. }
2188. break;
2190. case 13: // font color
2191. $xclfType = self::_GetInt2d($extData, 0); // color type
2192. $xclrValue = substr($extData, 4, 4); // color value (value based on color type)
2194. if ($xclfType == 2) {
2195. $rgb = sprintf('%02X%02X%02X', ord($xclrValue{0}), ord($xclrValue{1}), ord($xclrValue{2}));
2197. // modify the relevant style property
2198. if ( isset($this->_mapCellXfIndex[$ixfe]) ) {
2199. $font = $this->_phpExcel->getCellXfByIndex($this->_mapCellXfIndex[$ixfe])->getFont();
2200. $font->getColor()->setRGB($rgb);
2201. unset($font->colorIndex); // normal color index does not apply, discard
2202. }
2203. }
2204. break;
2205. }
2207. $offset += $cb;
2208. }
2209. }
2211. }
2213. /**
2214. * Read STYLE record
2215. */
2216. private function _readStyle()
2217. {
2218. $length = self::_GetInt2d($this->_data, $this->_pos + 2);
2219. $recordData = substr($this->_data, $this->_pos + 4, $length);
2221. // move stream pointer to next record
2222. $this->_pos += 4 + $length;
2224. if (!$this->_readDataOnly) {
2225. // offset: 0; size: 2; index to XF record and flag for built-in style
2226. $ixfe = self::_GetInt2d($recordData, 0);
2228. // bit: 11-0; mask 0x0FFF; index to XF record
2229. $xfIndex = (0x0FFF & $ixfe) >> 0;
2231. // bit: 15; mask 0x8000; 0 = user-defined style, 1 = built-in style
2232. $isBuiltIn = (bool) ((0x8000 & $ixfe) >> 15);
2234. if ($isBuiltIn) {
2235. // offset: 2; size: 1; identifier for built-in style
2236. $builtInId = ord($recordData{2});
2238. switch ($builtInId) {
2239. case 0x00:
2240. // currently, we are not using this for anything
2241. break;
2243. default:
2244. break;
2245. }
2247. } else {
2248. // user-defined; not supported by PHPExcel
2249. }
2250. }
2251. }
2253. /**
2254. * Read PALETTE record
2255. */
2256. private function _readPalette()
2257. {
2258. $length = self::_GetInt2d($this->_data, $this->_pos + 2);
2259. $recordData = substr($this->_data, $this->_pos + 4, $length);
2261. // move stream pointer to next record
2262. $this->_pos += 4 + $length;
2264. if (!$this->_readDataOnly) {
2265. // offset: 0; size: 2; number of following colors
2266. $nm = self::_GetInt2d($recordData, 0);
2268. // list of RGB colors
2269. for ($i = 0; $i < $nm; ++$i) {
2270. $rgb = substr($recordData, 2 + 4 * $i, 4);
2271. $this->_palette[] = self::_readRGB($rgb);
2272. }
2273. }
2274. }
2276. /**
2277. * SHEET
2278. *
2279. * This record is located in the Workbook Globals
2280. * Substream and represents a sheet inside the workbook.
2281. * One SHEET record is written for each sheet. It stores the
2282. * sheet name and a stream offset to the BOF record of the
2283. * respective Sheet Substream within the Workbook Stream.
2284. *
2285. * -- "OpenOffice.org's Documentation of the Microsoft
2286. * Excel File Format"
2287. */
2288. private function _readSheet()
2289. {
2290. $length = self::_GetInt2d($this->_data, $this->_pos + 2);
2291. $recordData = substr($this->_data, $this->_pos + 4, $length);
2293. // move stream pointer to next record
2294. $this->_pos += 4 + $length;
2296. // offset: 0; size: 4; absolute stream position of the BOF record of the sheet
2297. $rec_offset = self::_GetInt4d($recordData, 0);
2299. // offset: 4; size: 1; sheet state
2300. switch ( ord($recordData{4})) {
2301. case 0x00: $sheetState = PHPExcel_Worksheet:: SHEETSTATE_VISIBLE; break;
2302. case 0x01: $sheetState = PHPExcel_Worksheet:: SHEETSTATE_HIDDEN; break;
2303. case 0x02: $sheetState = PHPExcel_Worksheet:: SHEETSTATE_VERYHIDDEN; break;
2304. default: $sheetState = PHPExcel_Worksheet:: SHEETSTATE_VISIBLE; break;
2305. }
2307. // offset: 5; size: 1; sheet type
2308. $sheetType = ord($recordData{5});
2310. // offset: 6; size: var; sheet name
2311. if ($this->_version == self:: XLS_BIFF8) {
2312. $string = self::_readUnicodeStringShort( substr($recordData, 6));
2313. $rec_name = $string['value'];
2314. } elseif ($this->_version == self:: XLS_BIFF7) {
2315. $string = $this->_readByteStringShort( substr($recordData, 6));
2316. $rec_name = $string['value'];
2317. }
2319. $this->_sheets[] = array(
2320. 'name' => $rec_name,
2321. 'offset' => $rec_offset,
2322. 'sheetState' => $sheetState,
2323. 'sheetType' => $sheetType,
2324. );
2325. }
2327. /**
2328. * Read EXTERNALBOOK record
2329. */
2330. private function _readExternalBook()
2331. {
2332. $length = self::_GetInt2d($this->_data, $this->_pos + 2);
2333. $recordData = substr($this->_data, $this->_pos + 4, $length);
2335. // move stream pointer to next record
2336. $this->_pos += 4 + $length;
2338. // offset within record data
2339. $offset = 0;
2341. // there are 4 types of records
2342. if ( strlen($recordData) > 4) {
2343. // external reference
2344. // offset: 0; size: 2; number of sheet names ($nm)
2345. $nm = self::_GetInt2d($recordData, 0);
2346. $offset += 2;
2348. // offset: 2; size: var; encoded URL without sheet name (Unicode string, 16-bit length)
2349. $encodedUrlString = self::_readUnicodeStringLong( substr($recordData, 2));
2350. $offset += $encodedUrlString['size'];
2352. // offset: var; size: var; list of $nm sheet names (Unicode strings, 16-bit length)
2353. $externalSheetNames = array();
2354. for ($i = 0; $i < $nm; ++$i) {
2355. $externalSheetNameString = self::_readUnicodeStringLong( substr($recordData, $offset));
2356. $externalSheetNames[] = $externalSheetNameString['value'];
2357. $offset += $externalSheetNameString['size'];
2358. }
2360. // store the record data
2361. $this->_externalBooks[] = array(
2362. 'type' => 'external',
2363. 'encodedUrl' => $encodedUrlString['value'],
2364. 'externalSheetNames' => $externalSheetNames,
2365. );
2367. } elseif ( substr($recordData, 2, 2) == pack('CC', 0x01, 0x04)) {
2368. // internal reference
2369. // offset: 0; size: 2; number of sheet in this document
2370. // offset: 2; size: 2; 0x01 0x04
2371. $this->_externalBooks[] = array(
2372. 'type' => 'internal',
2373. );
2374. } elseif ( substr($recordData, 0, 4) == pack('vCC', 0x0001, 0x01, 0x3A)) {
2375. // add-in function
2376. // offset: 0; size: 2; 0x0001
2377. $this->_externalBooks[] = array(
2378. 'type' => 'addInFunction',
2379. );
2380. } elseif ( substr($recordData, 0, 2) == pack('v', 0x0000)) {
2381. // DDE links, OLE links
2382. // offset: 0; size: 2; 0x0000
2383. // offset: 2; size: var; encoded source document name
2384. $this->_externalBooks[] = array(
2385. 'type' => 'DDEorOLE',
2386. );
2387. }
2388. }
2390. /**
2391. * Read EXTERNNAME record.
2392. */
2393. private function _readExternName()
2394. {
2395. $length = self::_GetInt2d($this->_data, $this->_pos + 2);
2396. $recordData = substr($this->_data, $this->_pos + 4, $length);
2398. // move stream pointer to next record
2399. $this->_pos += 4 + $length;
2401. // external sheet references provided for named cells
2402. if ($this->_version == self:: XLS_BIFF8) {
2403. // offset: 0; size: 2; options
2404. $options = self::_GetInt2d($recordData, 0);
2406. // offset: 2; size: 2;
2408. // offset: 4; size: 2; not used
2410. // offset: 6; size: var
2411. $nameString = self::_readUnicodeStringShort( substr($recordData, 6));
2413. // offset: var; size: var; formula data
2414. $offset = 6 + $nameString['size'];
2415. $formula = $this->_getFormulaFromStructure( substr($recordData, $offset));
2417. $this->_externalNames[] = array(
2418. 'name' => $nameString['value'],
2419. 'formula' => $formula,
2420. );
2421. }
2422. }
2424. /**
2425. * Read EXTERNSHEET record
2426. */
2427. private function _readExternSheet()
2428. {
2429. $length = self::_GetInt2d($this->_data, $this->_pos + 2);
2430. $recordData = substr($this->_data, $this->_pos + 4, $length);
2432. // move stream pointer to next record
2433. $this->_pos += 4 + $length;
2435. // external sheet references provided for named cells
2436. if ($this->_version == self:: XLS_BIFF8) {
2437. // offset: 0; size: 2; number of following ref structures
2438. $nm = self::_GetInt2d($recordData, 0);
2439. for ($i = 0; $i < $nm; ++$i) {
2440. $this->_ref[] = array(
2441. // offset: 2 + 6 * $i; index to EXTERNALBOOK record
2442. 'externalBookIndex' => self::_GetInt2d($recordData, 2 + 6 * $i),
2443. // offset: 4 + 6 * $i; index to first sheet in EXTERNALBOOK record
2444. 'firstSheetIndex' => self::_GetInt2d($recordData, 4 + 6 * $i),
2445. // offset: 6 + 6 * $i; index to last sheet in EXTERNALBOOK record
2446. 'lastSheetIndex' => self::_GetInt2d($recordData, 6 + 6 * $i),
2447. );
2448. }
2449. }
2450. }
2452. /**
2453. * DEFINEDNAME
2454. *
2455. * This record is part of a Link Table. It contains the name
2456. * and the token array of an internal defined name. Token
2457. * arrays of defined names contain tokens with aberrant
2458. * token classes.
2459. *
2460. * -- "OpenOffice.org's Documentation of the Microsoft
2461. * Excel File Format"
2462. */
2463. private function _readDefinedName()
2464. {
2465. $length = self::_GetInt2d($this->_data, $this->_pos + 2);
2466. $recordData = substr($this->_data, $this->_pos + 4, $length);
2468. // move stream pointer to next record
2469. $this->_pos += 4 + $length;
2471. if ($this->_version == self:: XLS_BIFF8) {
2472. // retrieves named cells
2474. // offset: 0; size: 2; option flags
2475. $opts = self::_GetInt2d($recordData, 0);
2477. // bit: 5; mask: 0x0020; 0 = user-defined name, 1 = built-in-name
2478. $isBuiltInName = (0x0020 & $opts) >> 5;
2480. // offset: 2; size: 1; keyboard shortcut
2482. // offset: 3; size: 1; length of the name (character count)
2483. $nlen = ord($recordData{3});
2485. // offset: 4; size: 2; size of the formula data (it can happen that this is zero)
2486. // note: there can also be additional data, this is not included in $flen
2487. $flen = self::_GetInt2d($recordData, 4);
2489. // offset: 8; size: 2; 0=Global name, otherwise index to sheet (1-based)
2490. $scope = self::_GetInt2d($recordData, 8);
2492. // offset: 14; size: var; Name (Unicode string without length field)
2493. $string = self::_readUnicodeString( substr($recordData, 14), $nlen);
2495. // offset: var; size: $flen; formula data
2496. $offset = 14 + $string['size'];
2497. $formulaStructure = pack('v', $flen) . substr($recordData, $offset);
2499. try {
2500. $formula = $this->_getFormulaFromStructure($formulaStructure);
2501. } catch (Exception $e) {
2502. $formula = '';
2503. }
2505. $this->_definedname[] = array(
2506. 'isBuiltInName' => $isBuiltInName,
2507. 'name' => $string['value'],
2508. 'formula' => $formula,
2509. 'scope' => $scope,
2510. );
2511. }
2512. }
2514. /**
2515. * Read MSODRAWINGGROUP record
2516. */
2517. private function _readMsoDrawingGroup()
2518. {
2519. $length = self::_GetInt2d($this->_data, $this->_pos + 2);
2521. // get spliced record data
2522. $splicedRecordData = $this->_getSplicedRecordData();
2523. $recordData = $splicedRecordData['recordData'];
2525. $this->_drawingGroupData .= $recordData;
2526. }
2528. /**
2529. * SST - Shared String Table
2530. *
2531. * This record contains a list of all strings used anywhere
2532. * in the workbook. Each string occurs only once. The
2533. * workbook uses indexes into the list to reference the
2534. * strings.
2535. *
2536. * -- "OpenOffice.org's Documentation of the Microsoft
2537. * Excel File Format"
2538. **/
2540. private function _readSst()
2541. {
2542. // offset within (spliced) record data
2543. $pos = 0;
2545. // get spliced record data
2546. $splicedRecordData = $this->_getSplicedRecordData();
2548. $recordData = $splicedRecordData['recordData'];
2549. $spliceOffsets = $splicedRecordData['spliceOffsets'];
2551. // offset: 0; size: 4; total number of strings in the workbook
2552. $pos += 4;
2554. // offset: 4; size: 4; number of following strings ($nm)
2555. $nm = self::_GetInt4d($recordData, 4);
2556. $pos += 4;
2558. // loop through the Unicode strings (16-bit length)
2559. for ($i = 0; $i < $nm; ++$i) {
2561. // number of characters in the Unicode string
2562. $numChars = self::_GetInt2d($recordData, $pos);
2563. $pos += 2;
2565. // option flags
2566. $optionFlags = ord($recordData{$pos});
2567. ++$pos;
2569. // bit: 0; mask: 0x01; 0 = compressed; 1 = uncompressed
2570. $isCompressed = (($optionFlags & 0x01) == 0) ;
2572. // bit: 2; mask: 0x02; 0 = ordinary; 1 = Asian phonetic
2573. $hasAsian = (($optionFlags & 0x04) != 0);
2575. // bit: 3; mask: 0x03; 0 = ordinary; 1 = Rich-Text
2576. $hasRichText = (($optionFlags & 0x08) != 0);
2578. if ($hasRichText) {
2579. // number of Rich-Text formatting runs
2580. $formattingRuns = self::_GetInt2d($recordData, $pos);
2581. $pos += 2;
2582. }
2584. if ($hasAsian) {
2585. // size of Asian phonetic setting
2586. $extendedRunLength = self::_GetInt4d($recordData, $pos);
2587. $pos += 4;
2588. }
2590. // expected byte length of character array if not split
2591. $len = ($isCompressed) ? $numChars : $numChars * 2;
2593. // look up limit position
2594. foreach ($spliceOffsets as $spliceOffset) {
2595. // it can happen that the string is empty, therefore we need
2596. // <= and not just <
2597. if ($pos <= $spliceOffset) {
2598. $limitpos = $spliceOffset;
2599. break;
2600. }
2601. }
2603. if ($pos + $len <= $limitpos) {
2604. // character array is not split between records
2606. $retstr = substr($recordData, $pos, $len);
2607. $pos += $len;
2609. } else {
2610. // character array is split between records
2612. // first part of character array
2613. $retstr = substr($recordData, $pos, $limitpos - $pos);
2615. $bytesRead = $limitpos - $pos;
2617. // remaining characters in Unicode string
2618. $charsLeft = $numChars - (($isCompressed) ? $bytesRead : ($bytesRead / 2));
2620. $pos = $limitpos;
2622. // keep reading the characters
2623. while ($charsLeft > 0) {
2625. // look up next limit position, in case the string span more than one continue record
2626. foreach ($spliceOffsets as $spliceOffset) {
2627. if ($pos < $spliceOffset) {
2628. $limitpos = $spliceOffset;
2629. break;
2630. }
2631. }
2633. // repeated option flags
2634. // OpenOffice.org documentation 5.21
2635. $option = ord($recordData{$pos});
2636. ++$pos;
2638. if ($isCompressed && ($option == 0)) {
2639. // 1st fragment compressed
2640. // this fragment compressed
2641. $len = min($charsLeft, $limitpos - $pos);
2642. $retstr .= substr($recordData, $pos, $len);
2643. $charsLeft -= $len;
2644. $isCompressed = true;
2646. } elseif (!$isCompressed && ($option != 0)) {
2647. // 1st fragment uncompressed
2648. // this fragment uncompressed
2649. $len = min($charsLeft * 2, $limitpos - $pos);
2650. $retstr .= substr($recordData, $pos, $len);
2651. $charsLeft -= $len / 2;
2652. $isCompressed = false;
2654. } elseif (!$isCompressed && ($option == 0)) {
2655. // 1st fragment uncompressed
2656. // this fragment compressed
2657. $len = min($charsLeft, $limitpos - $pos);
2658. for ($j = 0; $j < $len; ++$j) {
2659. $retstr .= $recordData{$pos + $j} . chr(0);
2660. }
2661. $charsLeft -= $len;
2662. $isCompressed = false;
2664. } else {
2665. // 1st fragment compressed
2666. // this fragment uncompressed
2667. $newstr = '';
2668. for ($j = 0; $j < strlen($retstr); ++$j) {
2669. $newstr .= $retstr[$j] . chr(0);
2670. }
2671. $retstr = $newstr;
2672. $len = min($charsLeft * 2, $limitpos - $pos);
2673. $retstr .= substr($recordData, $pos, $len);
2674. $charsLeft -= $len / 2;
2675. $isCompressed = false;
2676. }
2678. $pos += $len;
2679. }
2680. }
2682. // convert to UTF-8
2683. $retstr = self::_encodeUTF16($retstr, $isCompressed);
2685. // read additional Rich-Text information, if any
2686. $fmtRuns = array();
2687. if ($hasRichText) {
2688. // list of formatting runs
2689. for ($j = 0; $j < $formattingRuns; ++$j) {
2690. // first formatted character; zero-based
2691. $charPos = self::_GetInt2d($recordData, $pos + $j * 4);
2693. // index to font record
2694. $fontIndex = self::_GetInt2d($recordData, $pos + 2 + $j * 4);
2696. $fmtRuns[] = array(
2697. 'charPos' => $charPos,
2698. 'fontIndex' => $fontIndex,
2699. );
2700. }
2701. $pos += 4 * $formattingRuns;
2702. }
2704. // read additional Asian phonetics information, if any
2705. if ($hasAsian) {
2706. // For Asian phonetic settings, we skip the extended string data
2707. $pos += $extendedRunLength;
2708. }
2710. // store the shared sting
2711. $this->_sst[] = array(
2712. 'value' => $retstr,
2713. 'fmtRuns' => $fmtRuns,
2714. );
2715. }
2717. // _getSplicedRecordData() takes care of moving current position in data stream
2718. }
2720. /**
2721. * Read PRINTGRIDLINES record
2722. */
2723. private function _readPrintGridlines()
2724. {
2725. $length = self::_GetInt2d($this->_data, $this->_pos + 2);
2726. $recordData = substr($this->_data, $this->_pos + 4, $length);
2728. // move stream pointer to next record
2729. $this->_pos += 4 + $length;
2731. if ($this->_version == self:: XLS_BIFF8 && !$this->_readDataOnly) {
2732. // offset: 0; size: 2; 0 = do not print sheet grid lines; 1 = print sheet gridlines
2733. $printGridlines = (bool) self::_GetInt2d($recordData, 0);
2734. $this->_phpSheet->setPrintGridlines($printGridlines);
2735. }
2736. }
2738. /**
2739. * Read DEFAULTROWHEIGHT record
2740. */
2741. private function _readDefaultRowHeight()
2742. {
2743. $length = self::_GetInt2d($this->_data, $this->_pos + 2);
2744. $recordData = substr($this->_data, $this->_pos + 4, $length);
2746. // move stream pointer to next record
2747. $this->_pos += 4 + $length;
2749. // offset: 0; size: 2; option flags
2750. // offset: 2; size: 2; default height for unused rows, (twips 1/20 point)
2751. $height = self::_GetInt2d($recordData, 2);
2752. $this->_phpSheet->getDefaultRowDimension()->setRowHeight($height / 20);
2753. }
2755. /**
2756. * Read SHEETPR record
2757. */
2758. private function _readSheetPr()
2759. {
2760. $length = self::_GetInt2d($this->_data, $this->_pos + 2);
2761. $recordData = substr($this->_data, $this->_pos + 4, $length);
2763. // move stream pointer to next record
2764. $this->_pos += 4 + $length;
2766. // offset: 0; size: 2
2768. // bit: 6; mask: 0x0040; 0 = outline buttons above outline group
2769. $isSummaryBelow = (0x0040 & self::_GetInt2d($recordData, 0)) >> 6;
2770. $this->_phpSheet->setShowSummaryBelow($isSummaryBelow);
2772. // bit: 7; mask: 0x0080; 0 = outline buttons left of outline group
2773. $isSummaryRight = (0x0080 & self::_GetInt2d($recordData, 0)) >> 7;
2774. $this->_phpSheet->setShowSummaryRight($isSummaryRight);
2776. // bit: 8; mask: 0x100; 0 = scale printout in percent, 1 = fit printout to number of pages
2777. // this corresponds to radio button setting in page setup dialog in Excel
2778. $this->_isFitToPages = (bool) ((0x0100 & self::_GetInt2d($recordData, 0)) >> 8);
2779. }
2781. /**
2782. * Read HORIZONTALPAGEBREAKS record
2783. */
2784. private function _readHorizontalPageBreaks()
2785. {
2786. $length = self::_GetInt2d($this->_data, $this->_pos + 2);
2787. $recordData = substr($this->_data, $this->_pos + 4, $length);
2789. // move stream pointer to next record
2790. $this->_pos += 4 + $length;
2792. if ($this->_version == self:: XLS_BIFF8 && !$this->_readDataOnly) {
2794. // offset: 0; size: 2; number of the following row index structures
2795. $nm = self::_GetInt2d($recordData, 0);
2797. // offset: 2; size: 6 * $nm; list of $nm row index structures
2798. for ($i = 0; $i < $nm; ++$i) {
2799. $r = self::_GetInt2d($recordData, 2 + 6 * $i);
2800. $cf = self::_GetInt2d($recordData, 2 + 6 * $i + 2);
2801. $cl = self::_GetInt2d($recordData, 2 + 6 * $i + 4);
2803. // not sure why two column indexes are necessary?
2804. $this->_phpSheet->setBreakByColumnAndRow($cf, $r, PHPExcel_Worksheet:: BREAK_ROW);
2805. }
2806. }
2807. }
2809. /**
2810. * Read VERTICALPAGEBREAKS record
2811. */
2812. private function _readVerticalPageBreaks()
2813. {
2814. $length = self::_GetInt2d($this->_data, $this->_pos + 2);
2815. $recordData = substr($this->_data, $this->_pos + 4, $length);
2817. // move stream pointer to next record
2818. $this->_pos += 4 + $length;
2820. if ($this->_version == self:: XLS_BIFF8 && !$this->_readDataOnly) {
2821. // offset: 0; size: 2; number of the following column index structures
2822. $nm = self::_GetInt2d($recordData, 0);
2824. // offset: 2; size: 6 * $nm; list of $nm row index structures
2825. for ($i = 0; $i < $nm; ++$i) {
2826. $c = self::_GetInt2d($recordData, 2 + 6 * $i);
2827. $rf = self::_GetInt2d($recordData, 2 + 6 * $i + 2);
2828. $rl = self::_GetInt2d($recordData, 2 + 6 * $i + 4);
2830. // not sure why two row indexes are necessary?
2831. $this->_phpSheet->setBreakByColumnAndRow($c, $rf, PHPExcel_Worksheet:: BREAK_COLUMN);
2832. }
2833. }
2834. }
2836. /**
2837. * Read HEADER record
2838. */
2839. private function _readHeader()
2840. {
2841. $length = self::_GetInt2d($this->_data, $this->_pos + 2);
2842. $recordData = substr($this->_data, $this->_pos + 4, $length);
2844. // move stream pointer to next record
2845. $this->_pos += 4 + $length;
2847. if (!$this->_readDataOnly) {
2848. // offset: 0; size: var
2849. // realized that $recordData can be empty even when record exists
2850. if ($recordData) {
2851. if ($this->_version == self:: XLS_BIFF8) {
2852. $string = self::_readUnicodeStringLong($recordData);
2853. } else {
2854. $string = $this->_readByteStringShort($recordData);
2855. }
2857. $this->_phpSheet->getHeaderFooter()->setOddHeader($string['value']);
2858. $this->_phpSheet->getHeaderFooter()->setEvenHeader($string['value']);
2859. }
2860. }
2861. }
2863. /**
2864. * Read FOOTER record
2865. */
2866. private function _readFooter()
2867. {
2868. $length = self::_GetInt2d($this->_data, $this->_pos + 2);
2869. $recordData = substr($this->_data, $this->_pos + 4, $length);
2871. // move stream pointer to next record
2872. $this->_pos += 4 + $length;
2874. if (!$this->_readDataOnly) {
2875. // offset: 0; size: var
2876. // realized that $recordData can be empty even when record exists
2877. if ($recordData) {
2878. if ($this->_version == self:: XLS_BIFF8) {
2879. $string = self::_readUnicodeStringLong($recordData);
2880. } else {
2881. $string = $this->_readByteStringShort($recordData);
2882. }
2883. $this->_phpSheet->getHeaderFooter()->setOddFooter($string['value']);
2884. $this->_phpSheet->getHeaderFooter()->setEvenFooter($string['value']);
2885. }
2886. }
2887. }
2889. /**
2890. * Read HCENTER record
2891. */
2892. private function _readHcenter()
2893. {
2894. $length = self::_GetInt2d($this->_data, $this->_pos + 2);
2895. $recordData = substr($this->_data, $this->_pos + 4, $length);
2897. // move stream pointer to next record
2898. $this->_pos += 4 + $length;
2900. if (!$this->_readDataOnly) {
2901. // offset: 0; size: 2; 0 = print sheet left aligned, 1 = print sheet centered horizontally
2902. $isHorizontalCentered = (bool) self::_GetInt2d($recordData, 0);
2904. $this->_phpSheet->getPageSetup()->setHorizontalCentered($isHorizontalCentered);
2905. }
2906. }
2908. /**
2909. * Read VCENTER record
2910. */
2911. private function _readVcenter()
2912. {
2913. $length = self::_GetInt2d($this->_data, $this->_pos + 2);
2914. $recordData = substr($this->_data, $this->_pos + 4, $length);
2916. // move stream pointer to next record
2917. $this->_pos += 4 + $length;
2919. if (!$this->_readDataOnly) {
2920. // offset: 0; size: 2; 0 = print sheet aligned at top page border, 1 = print sheet vertically centered
2921. $isVerticalCentered = (bool) self::_GetInt2d($recordData, 0);
2923. $this->_phpSheet->getPageSetup()->setVerticalCentered($isVerticalCentered);
2924. }
2925. }
2927. /**
2928. * Read LEFTMARGIN record
2929. */
2930. private function _readLeftMargin()
2931. {
2932. $length = self::_GetInt2d($this->_data, $this->_pos + 2);
2933. $recordData = substr($this->_data, $this->_pos + 4, $length);
2935. // move stream pointer to next record
2936. $this->_pos += 4 + $length;
2938. if (!$this->_readDataOnly) {
2939. // offset: 0; size: 8
2940. $this->_phpSheet->getPageMargins()->setLeft(self::_extractNumber($recordData));
2941. }
2942. }
2944. /**
2945. * Read RIGHTMARGIN record
2946. */
2947. private function _readRightMargin()
2948. {
2949. $length = self::_GetInt2d($this->_data, $this->_pos + 2);
2950. $recordData = substr($this->_data, $this->_pos + 4, $length);
2952. // move stream pointer to next record
2953. $this->_pos += 4 + $length;
2955. if (!$this->_readDataOnly) {
2956. // offset: 0; size: 8
2957. $this->_phpSheet->getPageMargins()->setRight(self::_extractNumber($recordData));
2958. }
2959. }
2961. /**
2962. * Read TOPMARGIN record
2963. */
2964. private function _readTopMargin()
2965. {
2966. $length = self::_GetInt2d($this->_data, $this->_pos + 2);
2967. $recordData = substr($this->_data, $this->_pos + 4, $length);
2969. // move stream pointer to next record
2970. $this->_pos += 4 + $length;
2972. if (!$this->_readDataOnly) {
2973. // offset: 0; size: 8
2974. $this->_phpSheet->getPageMargins()->setTop(self::_extractNumber($recordData));
2975. }
2976. }
2978. /**
2979. * Read BOTTOMMARGIN record
2980. */
2981. private function _readBottomMargin()
2982. {
2983. $length = self::_GetInt2d($this->_data, $this->_pos + 2);
2984. $recordData = substr($this->_data, $this->_pos + 4, $length);
2986. // move stream pointer to next record
2987. $this->_pos += 4 + $length;
2989. if (!$this->_readDataOnly) {
2990. // offset: 0; size: 8
2991. $this->_phpSheet->getPageMargins()->setBottom(self::_extractNumber($recordData));
2992. }
2993. }
2995. /**
2996. * Read PAGESETUP record
2997. */
2998. private function _readPageSetup()
2999. {
3000. $length = self::_GetInt2d($this->_data, $this->_pos + 2);
3001. $recordData = substr($this->_data, $this->_pos + 4, $length);
3003. // move stream pointer to next record
3004. $this->_pos += 4 + $length;
3006. if (!$this->_readDataOnly) {
3007. // offset: 0; size: 2; paper size
3008. $paperSize = self::_GetInt2d($recordData, 0);
3010. // offset: 2; size: 2; scaling factor
3011. $scale = self::_GetInt2d($recordData, 2);
3013. // offset: 6; size: 2; fit worksheet width to this number of pages, 0 = use as many as needed
3014. $fitToWidth = self::_GetInt2d($recordData, 6);
3016. // offset: 8; size: 2; fit worksheet height to this number of pages, 0 = use as many as needed
3017. $fitToHeight = self::_GetInt2d($recordData, 8);
3019. // offset: 10; size: 2; option flags
3021. // bit: 1; mask: 0x0002; 0=landscape, 1=portrait
3022. $isPortrait = (0x0002 & self::_GetInt2d($recordData, 10)) >> 1;
3024. // bit: 2; mask: 0x0004; 1= paper size, scaling factor, paper orient. not init
3025. // when this bit is set, do not use flags for those properties
3026. $isNotInit = (0x0004 & self::_GetInt2d($recordData, 10)) >> 2;
3028. if (!$isNotInit) {
3029. $this->_phpSheet->getPageSetup()->setPaperSize($paperSize);
3030. switch ($isPortrait) {
3031. case 0: $this->_phpSheet->getPageSetup()->setOrientation( PHPExcel_Worksheet_PageSetup:: ORIENTATION_LANDSCAPE); break;
3032. case 1: $this->_phpSheet->getPageSetup()->setOrientation( PHPExcel_Worksheet_PageSetup:: ORIENTATION_PORTRAIT); break;
3033. }
3035. $this->_phpSheet->getPageSetup()->setScale($scale, false);
3036. $this->_phpSheet->getPageSetup()->setFitToPage((bool) $this->_isFitToPages);
3037. $this->_phpSheet->getPageSetup()->setFitToWidth($fitToWidth, false);
3038. $this->_phpSheet->getPageSetup()->setFitToHeight($fitToHeight, false);
3039. }
3041. // offset: 16; size: 8; header margin (IEEE 754 floating-point value)
3042. $marginHeader = self::_extractNumber( substr($recordData, 16, 8));
3043. $this->_phpSheet->getPageMargins()->setHeader($marginHeader);
3045. // offset: 24; size: 8; footer margin (IEEE 754 floating-point value)
3046. $marginFooter = self::_extractNumber( substr($recordData, 24, 8));
3047. $this->_phpSheet->getPageMargins()->setFooter($marginFooter);
3048. }
3049. }
3051. /**
3052. * PROTECT - Sheet protection (BIFF2 through BIFF8)
3053. * if this record is omitted, then it also means no sheet protection
3054. */
3055. private function _readProtect()
3056. {
3057. $length = self::_GetInt2d($this->_data, $this->_pos + 2);
3058. $recordData = substr($this->_data, $this->_pos + 4, $length);
3060. // move stream pointer to next record
3061. $this->_pos += 4 + $length;
3063. if ($this->_readDataOnly) {
3064. return;
3065. }
3067. // offset: 0; size: 2;
3069. // bit 0, mask 0x01; 1 = sheet is protected
3070. $bool = (0x01 & self::_GetInt2d($recordData, 0)) >> 0;
3071. $this->_phpSheet->getProtection()->setSheet((bool)$bool);
3072. }
3074. /**
3075. * SCENPROTECT
3076. */
3077. private function _readScenProtect()
3078. {
3079. $length = self::_GetInt2d($this->_data, $this->_pos + 2);
3080. $recordData = substr($this->_data, $this->_pos + 4, $length);
3082. // move stream pointer to next record
3083. $this->_pos += 4 + $length;
3085. if ($this->_readDataOnly) {
3086. return;
3087. }
3089. // offset: 0; size: 2;
3091. // bit: 0, mask 0x01; 1 = scenarios are protected
3092. $bool = (0x01 & self::_GetInt2d($recordData, 0)) >> 0;
3094. $this->_phpSheet->getProtection()->setScenarios((bool)$bool);
3095. }
3097. /**
3098. * OBJECTPROTECT
3099. */
3100. private function _readObjectProtect()
3101. {
3102. $length = self::_GetInt2d($this->_data, $this->_pos + 2);
3103. $recordData = substr($this->_data, $this->_pos + 4, $length);
3105. // move stream pointer to next record
3106. $this->_pos += 4 + $length;
3108. if ($this->_readDataOnly) {
3109. return;
3110. }
3112. // offset: 0; size: 2;
3114. // bit: 0, mask 0x01; 1 = objects are protected
3115. $bool = (0x01 & self::_GetInt2d($recordData, 0)) >> 0;
3117. $this->_phpSheet->getProtection()->setObjects((bool)$bool);
3118. }
3120. /**
3121. * PASSWORD - Sheet protection (hashed) password (BIFF2 through BIFF8)
3122. */
3123. private function _readPassword()
3124. {
3125. $length = self::_GetInt2d($this->_data, $this->_pos + 2);
3126. $recordData = substr($this->_data, $this->_pos + 4, $length);
3128. // move stream pointer to next record
3129. $this->_pos += 4 + $length;
3131. if (!$this->_readDataOnly) {
3132. // offset: 0; size: 2; 16-bit hash value of password
3133. $password = strtoupper( dechex(self::_GetInt2d($recordData, 0))); // the hashed password
3134. $this->_phpSheet->getProtection()->setPassword($password, true);
3135. }
3136. }
3138. /**
3139. * Read DEFCOLWIDTH record
3140. */
3141. private function _readDefColWidth()
3142. {
3143. $length = self::_GetInt2d($this->_data, $this->_pos + 2);
3144. $recordData = substr($this->_data, $this->_pos + 4, $length);
3146. // move stream pointer to next record
3147. $this->_pos += 4 + $length;
3149. // offset: 0; size: 2; default column width
3150. $width = self::_GetInt2d($recordData, 0);
3151. if ($width != 8) {
3152. $this->_phpSheet->getDefaultColumnDimension()->setWidth($width);
3153. }
3154. }
3156. /**
3157. * Read COLINFO record
3158. */
3159. private function _readColInfo()
3160. {
3161. $length = self::_GetInt2d($this->_data, $this->_pos + 2);
3162. $recordData = substr($this->_data, $this->_pos + 4, $length);
3164. // move stream pointer to next record
3165. $this->_pos += 4 + $length;
3167. if (!$this->_readDataOnly) {
3168. // offset: 0; size: 2; index to first column in range
3169. $fc = self::_GetInt2d($recordData, 0); // first column index
3171. // offset: 2; size: 2; index to last column in range
3172. $lc = self::_GetInt2d($recordData, 2); // first column index
3174. // offset: 4; size: 2; width of the column in 1/256 of the width of the zero character
3175. $width = self::_GetInt2d($recordData, 4);
3177. // offset: 6; size: 2; index to XF record for default column formatting
3178. $xfIndex = self::_GetInt2d($recordData, 6);
3180. // offset: 8; size: 2; option flags
3182. // bit: 0; mask: 0x0001; 1= columns are hidden
3183. $isHidden = (0x0001 & self::_GetInt2d($recordData, 8)) >> 0;
3185. // bit: 10-8; mask: 0x0700; outline level of the columns (0 = no outline)
3186. $level = (0x0700 & self::_GetInt2d($recordData, 8)) >> 8;
3188. // bit: 12; mask: 0x1000; 1 = collapsed
3189. $isCollapsed = (0x1000 & self::_GetInt2d($recordData, 8)) >> 12;
3191. // offset: 10; size: 2; not used
3193. for ($i = $fc; $i <= $lc; ++$i) {
3194. if ($lc == 255 || $lc == 256) {
3195. $this->_phpSheet->getDefaultColumnDimension()->setWidth($width / 256);
3196. break;
3197. }
3198. $this->_phpSheet->getColumnDimensionByColumn($i)->setWidth($width / 256);
3199. $this->_phpSheet->getColumnDimensionByColumn($i)->setVisible(!$isHidden);
3200. $this->_phpSheet->getColumnDimensionByColumn($i)->setOutlineLevel($level);
3201. $this->_phpSheet->getColumnDimensionByColumn($i)->setCollapsed($isCollapsed);
3202. $this->_phpSheet->getColumnDimensionByColumn($i)->setXfIndex($this->_mapCellXfIndex[$xfIndex]);
3203. }
3204. }
3205. }
3207. /**
3208. * ROW
3209. *
3210. * This record contains the properties of a single row in a
3211. * sheet. Rows and cells in a sheet are divided into blocks
3212. * of 32 rows.
3213. *
3214. * -- "OpenOffice.org's Documentation of the Microsoft
3215. * Excel File Format"
3216. */
3217. private function _readRow()
3218. {
3219. $length = self::_GetInt2d($this->_data, $this->_pos + 2);
3220. $recordData = substr($this->_data, $this->_pos + 4, $length);
3222. // move stream pointer to next record
3223. $this->_pos += 4 + $length;
3225. if (!$this->_readDataOnly) {
3226. // offset: 0; size: 2; index of this row
3227. $r = self::_GetInt2d($recordData, 0);
3229. // offset: 2; size: 2; index to column of the first cell which is described by a cell record
3231. // offset: 4; size: 2; index to column of the last cell which is described by a cell record, increased by 1
3233. // offset: 6; size: 2;
3235. // bit: 14-0; mask: 0x7FFF; height of the row, in twips = 1/20 of a point
3236. $height = (0x7FFF & self::_GetInt2d($recordData, 6)) >> 0;
3238. // bit: 15: mask: 0x8000; 0 = row has custom height; 1= row has default height
3239. $useDefaultHeight = (0x8000 & self::_GetInt2d($recordData, 6)) >> 15;
3241. if (!$useDefaultHeight) {
3242. $this->_phpSheet->getRowDimension($r + 1)->setRowHeight($height / 20);
3243. }
3245. // offset: 8; size: 2; not used
3247. // offset: 10; size: 2; not used in BIFF5-BIFF8
3249. // offset: 12; size: 4; option flags and default row formatting
3251. // bit: 2-0: mask: 0x00000007; outline level of the row
3252. $level = (0x00000007 & self::_GetInt4d($recordData, 12)) >> 0;
3253. $this->_phpSheet->getRowDimension($r + 1)->setOutlineLevel($level);
3255. // bit: 4; mask: 0x00000010; 1 = outline group start or ends here... and is collapsed
3256. $isCollapsed = (0x00000010 & self::_GetInt4d($recordData, 12)) >> 4;
3257. $this->_phpSheet->getRowDimension($r + 1)->setCollapsed($isCollapsed);
3259. // bit: 5; mask: 0x00000020; 1 = row is hidden
3260. $isHidden = (0x00000020 & self::_GetInt4d($recordData, 12)) >> 5;
3261. $this->_phpSheet->getRowDimension($r + 1)->setVisible(!$isHidden);
3263. // bit: 7; mask: 0x00000080; 1 = row has explicit format
3264. $hasExplicitFormat = (0x00000080 & self::_GetInt4d($recordData, 12)) >> 7;
3266. // bit: 27-16; mask: 0x0FFF0000; only applies when hasExplicitFormat = 1; index to XF record
3267. $xfIndex = (0x0FFF0000 & self::_GetInt4d($recordData, 12)) >> 16;
3269. if ($hasExplicitFormat) {
3270. $this->_phpSheet->getRowDimension($r + 1)->setXfIndex($this->_mapCellXfIndex[$xfIndex]);
3271. }
3272. }
3273. }
3275. /**
3276. * Read RK record
3277. * This record represents a cell that contains an RK value
3278. * (encoded integer or floating-point value). If a
3279. * floating-point value cannot be encoded to an RK value,
3280. * a NUMBER record will be written. This record replaces the
3281. * record INTEGER written in BIFF2.
3282. *
3283. * -- "OpenOffice.org's Documentation of the Microsoft
3284. * Excel File Format"
3285. */
3286. private function _readRk()
3287. {
3288. $length = self::_GetInt2d($this->_data, $this->_pos + 2);
3289. $recordData = substr($this->_data, $this->_pos + 4, $length);
3291. // move stream pointer to next record
3292. $this->_pos += 4 + $length;
3294. // offset: 0; size: 2; index to row
3295. $row = self::_GetInt2d($recordData, 0);
3297. // offset: 2; size: 2; index to column
3298. $column = self::_GetInt2d($recordData, 2);
3299. $columnString = PHPExcel_Cell:: stringFromColumnIndex($column);
3301. // Read cell?
3302. if ( ! is_null($this-> getReadFilter()) && $this-> getReadFilter()->readCell($columnString, $row + 1, $this->_phpSheet->getTitle()) ) {
3303. // offset: 4; size: 2; index to XF record
3304. $xfIndex = self::_GetInt2d($recordData, 4);
3306. // offset: 6; size: 4; RK value
3307. $rknum = self::_GetInt4d($recordData, 6);
3308. $numValue = self::_GetIEEE754($rknum);
3310. $cell = $this->_phpSheet->getCell($columnString . ($row + 1));
3311. if (!$this->_readDataOnly) {
3312. // add style information
3313. $cell->setXfIndex($this->_mapCellXfIndex[$xfIndex]);
3314. }
3316. // add cell
3317. $cell->setValueExplicit($numValue, PHPExcel_Cell_DataType:: TYPE_NUMERIC);
3318. }
3319. }
3321. /**
3322. * Read LABELSST record
3323. * This record represents a cell that contains a string. It
3324. * replaces the LABEL record and RSTRING record used in
3325. * BIFF2-BIFF5.
3326. *
3327. * -- "OpenOffice.org's Documentation of the Microsoft
3328. * Excel File Format"
3329. */
3330. private function _readLabelSst()
3331. {
3332. $length = self::_GetInt2d($this->_data, $this->_pos + 2);
3333. $recordData = substr($this->_data, $this->_pos + 4, $length);
3335. // move stream pointer to next record
3336. $this->_pos += 4 + $length;
3338. // offset: 0; size: 2; index to row
3339. $row = self::_GetInt2d($recordData, 0);
3341. // offset: 2; size: 2; index to column
3342. $column = self::_GetInt2d($recordData, 2);
3343. $columnString = PHPExcel_Cell:: stringFromColumnIndex($column);
3345. // Read cell?
3346. if ( ! is_null($this-> getReadFilter()) && $this-> getReadFilter()->readCell($columnString, $row + 1, $this->_phpSheet->getTitle()) ) {
3347. // offset: 4; size: 2; index to XF record
3348. $xfIndex = self::_GetInt2d($recordData, 4);
3350. // offset: 6; size: 4; index to SST record
3351. $index = self::_GetInt4d($recordData, 6);
3353. // add cell
3354. if (($fmtRuns = $this->_sst[$index]['fmtRuns']) && !$this->_readDataOnly) {
3355. // then we should treat as rich text
3356. $richText = new PHPExcel_RichText();
3357. $charPos = 0;
3358. $sstCount = count($this->_sst[$index]['fmtRuns']);
3359. for ($i = 0; $i <= $sstCount; ++$i) {
3360. if (isset($fmtRuns[$i])) {
3361. $text = PHPExcel_Shared_String:: Substring($this->_sst[$index]['value'], $charPos, $fmtRuns[$i]['charPos'] - $charPos);
3362. $charPos = $fmtRuns[$i]['charPos'];
3363. } else {
3364. $text = PHPExcel_Shared_String:: Substring($this->_sst[$index]['value'], $charPos, PHPExcel_Shared_String:: CountCharacters($this->_sst[$index]['value']));
3365. }
3367. if ( PHPExcel_Shared_String:: CountCharacters($text) > 0) {
3368. if ($i == 0) { // first text run, no style
3369. $richText->createText($text);
3370. } else {
3371. $textRun = $richText->createTextRun($text);
3372. if (isset($fmtRuns[$i - 1])) {
3373. if ($fmtRuns[$i - 1]['fontIndex'] < 4) {
3374. $fontIndex = $fmtRuns[$i - 1]['fontIndex'];
3375. } else {
3376. // this has to do with that index 4 is omitted in all BIFF versions for some strange reason
3377. // check the OpenOffice documentation of the FONT record
3378. $fontIndex = $fmtRuns[$i - 1]['fontIndex'] - 1;
3379. }
3380. $textRun->setFont(clone $this->_objFonts[$fontIndex]);
3381. }
3382. }
3383. }
3384. }
3385. $cell = $this->_phpSheet->getCell($columnString . ($row + 1));
3386. $cell->setValueExplicit($richText, PHPExcel_Cell_DataType:: TYPE_STRING);
3387. } else {
3388. $cell = $this->_phpSheet->getCell($columnString . ($row + 1));
3389. $cell->setValueExplicit($this->_sst[$index]['value'], PHPExcel_Cell_DataType:: TYPE_STRING);
3390. }
3392. if (!$this->_readDataOnly) {
3393. // add style information
3394. $cell->setXfIndex($this->_mapCellXfIndex[$xfIndex]);
3395. }
3396. }
3397. }
3399. /**
3400. * Read MULRK record
3401. * This record represents a cell range containing RK value
3402. * cells. All cells are located in the same row.
3403. *
3404. * -- "OpenOffice.org's Documentation of the Microsoft
3405. * Excel File Format"
3406. */
3407. private function _readMulRk()
3408. {
3409. $length = self::_GetInt2d($this->_data, $this->_pos + 2);
3410. $recordData = substr($this->_data, $this->_pos + 4, $length);
3412. // move stream pointer to next record
3413. $this->_pos += 4 + $length;
3415. // offset: 0; size: 2; index to row
3416. $row = self::_GetInt2d($recordData, 0);
3418. // offset: 2; size: 2; index to first column
3419. $colFirst = self::_GetInt2d($recordData, 2);
3421. // offset: var; size: 2; index to last column
3422. $colLast = self::_GetInt2d($recordData, $length - 2);
3423. $columns = $colLast - $colFirst + 1;
3425. // offset within record data
3426. $offset = 4;
3428. for ($i = 0; $i < $columns; ++$i) {
3429. $columnString = PHPExcel_Cell:: stringFromColumnIndex($colFirst + $i);
3431. // Read cell?
3432. if ( ! is_null($this-> getReadFilter()) && $this-> getReadFilter()->readCell($columnString, $row + 1, $this->_phpSheet->getTitle()) ) {
3434. // offset: var; size: 2; index to XF record
3435. $xfIndex = self::_GetInt2d($recordData, $offset);
3437. // offset: var; size: 4; RK value
3438. $numValue = self::_GetIEEE754(self::_GetInt4d($recordData, $offset + 2));
3439. $cell = $this->_phpSheet->getCell($columnString . ($row + 1));
3440. if (!$this->_readDataOnly) {
3441. // add style
3442. $cell->setXfIndex($this->_mapCellXfIndex[$xfIndex]);
3443. }
3445. // add cell value
3446. $cell->setValueExplicit($numValue, PHPExcel_Cell_DataType:: TYPE_NUMERIC);
3447. }
3449. $offset += 6;
3450. }
3451. }
3453. /**
3454. * Read NUMBER record
3455. * This record represents a cell that contains a
3456. * floating-point value.
3457. *
3458. * -- "OpenOffice.org's Documentation of the Microsoft
3459. * Excel File Format"
3460. */
3461. private function _readNumber()
3462. {
3463. $length = self::_GetInt2d($this->_data, $this->_pos + 2);
3464. $recordData = substr($this->_data, $this->_pos + 4, $length);
3466. // move stream pointer to next record
3467. $this->_pos += 4 + $length;
3469. // offset: 0; size: 2; index to row
3470. $row = self::_GetInt2d($recordData, 0);
3472. // offset: 2; size 2; index to column
3473. $column = self::_GetInt2d($recordData, 2);
3474. $columnString = PHPExcel_Cell:: stringFromColumnIndex($column);
3476. // Read cell?
3477. if ( ! is_null($this-> getReadFilter()) && $this-> getReadFilter()->readCell($columnString, $row + 1, $this->_phpSheet->getTitle()) ) {
3478. // offset 4; size: 2; index to XF record
3479. $xfIndex = self::_GetInt2d($recordData, 4);
3481. $numValue = self::_extractNumber( substr($recordData, 6, 8));
3483. $cell = $this->_phpSheet->getCell($columnString . ($row + 1));
3484. if (!$this->_readDataOnly) {
3485. // add cell style
3486. $cell->setXfIndex($this->_mapCellXfIndex[$xfIndex]);
3487. }
3489. // add cell value
3490. $cell->setValueExplicit($numValue, PHPExcel_Cell_DataType:: TYPE_NUMERIC);
3491. }
3492. }
3494. /**
3495. * Read FORMULA record + perhaps a following STRING record if formula result is a string
3496. * This record contains the token array and the result of a
3497. * formula cell.
3498. *
3499. * -- "OpenOffice.org's Documentation of the Microsoft
3500. * Excel File Format"
3501. */
3502. private function _readFormula()
3503. {
3504. $length = self::_GetInt2d($this->_data, $this->_pos + 2);
3505. $recordData = substr($this->_data, $this->_pos + 4, $length);
3507. // move stream pointer to next record
3508. $this->_pos += 4 + $length;
3510. // offset: 0; size: 2; row index
3511. $row = self::_GetInt2d($recordData, 0);
3513. // offset: 2; size: 2; col index
3514. $column = self::_GetInt2d($recordData, 2);
3515. $columnString = PHPExcel_Cell:: stringFromColumnIndex($column);
3517. // offset: 20: size: variable; formula structure
3518. $formulaStructure = substr($recordData, 20);
3520. // offset: 14: size: 2; option flags, recalculate always, recalculate on open etc.
3521. $options = self::_GetInt2d($recordData, 14);
3523. // bit: 0; mask: 0x0001; 1 = recalculate always
3524. // bit: 1; mask: 0x0002; 1 = calculate on open
3525. // bit: 2; mask: 0x0008; 1 = part of a shared formula
3526. $isPartOfSharedFormula = (bool) (0x0008 & $options);
3528. // WARNING:
3529. // We can apparently not rely on $isPartOfSharedFormula. Even when $isPartOfSharedFormula = true
3530. // the formula data may be ordinary formula data, therefore we need to check
3531. // explicitly for the tExp token (0x01)
3532. $isPartOfSharedFormula = $isPartOfSharedFormula && ord($formulaStructure{2}) == 0x01;
3534. if ($isPartOfSharedFormula) {
3535. // part of shared formula which means there will be a formula with a tExp token and nothing else
3536. // get the base cell, grab tExp token
3537. $baseRow = self::_GetInt2d($formulaStructure, 3);
3538. $baseCol = self::_GetInt2d($formulaStructure, 5);
3539. $this->_baseCell = PHPExcel_Cell:: stringFromColumnIndex($baseCol). ($baseRow + 1);
3540. }
3542. // Read cell?
3543. if ( ! is_null($this-> getReadFilter()) && $this-> getReadFilter()->readCell($columnString, $row + 1, $this->_phpSheet->getTitle()) ) {
3545. if ($isPartOfSharedFormula) {
3546. // formula is added to this cell after the sheet has been read
3547. $this->_sharedFormulaParts[$columnString . ($row + 1)] = $this->_baseCell;
3548. }
3550. // offset: 16: size: 4; not used
3552. // offset: 4; size: 2; XF index
3553. $xfIndex = self::_GetInt2d($recordData, 4);
3555. // offset: 6; size: 8; result of the formula
3556. if ( ( ord($recordData{6}) == 0)
3557. && ( ord($recordData{12}) == 255)
3558. && ( ord($recordData{13}) == 255) ) {
3560. // String formula. Result follows in appended STRING record
3561. $dataType = PHPExcel_Cell_DataType:: TYPE_STRING;
3563. // read possible SHAREDFMLA record
3564. $code = self::_GetInt2d($this->_data, $this->_pos);
3565. if ($code == self:: XLS_Type_SHAREDFMLA) {
3566. $this->_readSharedFmla();
3567. }
3569. // read STRING record
3570. $value = $this->_readString();
3572. } elseif (( ord($recordData{6}) == 1)
3573. && ( ord($recordData{12}) == 255)
3574. && ( ord($recordData{13}) == 255)) {
3576. // Boolean formula. Result is in +2; 0=false, 1=true
3577. $dataType = PHPExcel_Cell_DataType:: TYPE_BOOL;
3578. $value = (bool) ord($recordData{8});
3580. } elseif (( ord($recordData{6}) == 2)
3581. && ( ord($recordData{12}) == 255)
3582. && ( ord($recordData{13}) == 255)) {
3584. // Error formula. Error code is in +2
3585. $dataType = PHPExcel_Cell_DataType:: TYPE_ERROR;
3586. $value = self::_mapErrorCode( ord($recordData{8}));
3588. } elseif (( ord($recordData{6}) == 3)
3589. && ( ord($recordData{12}) == 255)
3590. && ( ord($recordData{13}) == 255)) {
3592. // Formula result is a null string
3593. $dataType = PHPExcel_Cell_DataType:: TYPE_NULL;
3594. $value = '';
3596. } else {
3598. // forumla result is a number, first 14 bytes like _NUMBER record
3599. $dataType = PHPExcel_Cell_DataType:: TYPE_NUMERIC;
3600. $value = self::_extractNumber( substr($recordData, 6, 8));
3602. }
3604. $cell = $this->_phpSheet->getCell($columnString . ($row + 1));
3605. if (!$this->_readDataOnly) {
3606. // add cell style
3607. $cell->setXfIndex($this->_mapCellXfIndex[$xfIndex]);
3608. }
3610. // store the formula
3611. if (!$isPartOfSharedFormula) {
3612. // not part of shared formula
3613. // add cell value. If we can read formula, populate with formula, otherwise just used cached value
3614. try {
3615. if ($this->_version != self:: XLS_BIFF8) {
3616. throw new Exception('Not BIFF8. Can only read BIFF8 formulas');
3617. }
3618. $formula = $this->_getFormulaFromStructure($formulaStructure); // get formula in human language
3619. $cell->setValueExplicit('=' . $formula, PHPExcel_Cell_DataType:: TYPE_FORMULA);
3621. } catch (Exception $e) {
3622. $cell->setValueExplicit($value, $dataType);
3623. }
3624. } else {
3625. if ($this->_version == self:: XLS_BIFF8) {
3626. // do nothing at this point, formula id added later in the code
3627. } else {
3628. $cell->setValueExplicit($value, $dataType);
3629. }
3630. }
3632. // store the cached calculated value
3633. $cell->setCalculatedValue($value);
3634. }
3635. }
3637. /**
3638. * Read a SHAREDFMLA record. This function just stores the binary shared formula in the reader,
3639. * which usually contains relative references.
3640. * These will be used to construct the formula in each shared formula part after the sheet is read.
3641. */
3642. private function _readSharedFmla()
3643. {
3644. $length = self::_GetInt2d($this->_data, $this->_pos + 2);
3645. $recordData = substr($this->_data, $this->_pos + 4, $length);
3647. // move stream pointer to next record
3648. $this->_pos += 4 + $length;
3650. // offset: 0, size: 6; cell range address of the area used by the shared formula, not used for anything
3651. $cellRange = substr($recordData, 0, 6);
3652. $cellRange = $this->_readBIFF5CellRangeAddressFixed($cellRange); // note: even BIFF8 uses BIFF5 syntax
3654. // offset: 6, size: 1; not used
3656. // offset: 7, size: 1; number of existing FORMULA records for this shared formula
3657. $no = ord($recordData{7});
3659. // offset: 8, size: var; Binary token array of the shared formula
3660. $formula = substr($recordData, 8);
3662. // at this point we only store the shared formula for later use
3663. $this->_sharedFormulas[$this->_baseCell] = $formula;
3665. }
3667. /**
3668. * Read a STRING record from current stream position and advance the stream pointer to next record
3669. * This record is used for storing result from FORMULA record when it is a string, and
3670. * it occurs directly after the FORMULA record
3671. *
3672. * @return string The string contents as UTF-8
3673. */
3674. private function _readString()
3675. {
3676. $length = self::_GetInt2d($this->_data, $this->_pos + 2);
3677. $recordData = substr($this->_data, $this->_pos + 4, $length);
3679. // move stream pointer to next record
3680. $this->_pos += 4 + $length;
3682. if ($this->_version == self:: XLS_BIFF8) {
3683. $string = self::_readUnicodeStringLong($recordData);
3684. $value = $string['value'];
3685. } else {
3686. $string = $this->_readByteStringLong($recordData);
3687. $value = $string['value'];
3688. }
3690. return $value;
3691. }
3693. /**
3694. * Read BOOLERR record
3695. * This record represents a Boolean value or error value
3696. * cell.
3697. *
3698. * -- "OpenOffice.org's Documentation of the Microsoft
3699. * Excel File Format"
3700. */
3701. private function _readBoolErr()
3702. {
3703. $length = self::_GetInt2d($this->_data, $this->_pos + 2);
3704. $recordData = substr($this->_data, $this->_pos + 4, $length);
3706. // move stream pointer to next record
3707. $this->_pos += 4 + $length;
3709. // offset: 0; size: 2; row index
3710. $row = self::_GetInt2d($recordData, 0);
3712. // offset: 2; size: 2; column index
3713. $column = self::_GetInt2d($recordData, 2);
3714. $columnString = PHPExcel_Cell:: stringFromColumnIndex($column);
3716. // Read cell?
3717. if ( ! is_null($this-> getReadFilter()) && $this-> getReadFilter()->readCell($columnString, $row + 1, $this->_phpSheet->getTitle()) ) {
3718. // offset: 4; size: 2; index to XF record
3719. $xfIndex = self::_GetInt2d($recordData, 4);
3721. // offset: 6; size: 1; the boolean value or error value
3722. $boolErr = ord($recordData{6});
3724. // offset: 7; size: 1; 0=boolean; 1=error
3725. $isError = ord($recordData{7});
3727. $cell = $this->_phpSheet->getCell($columnString . ($row + 1));
3728. switch ($isError) {
3729. case 0: // boolean
3730. $value = (bool) $boolErr;
3732. // add cell value
3733. $cell->setValueExplicit($value, PHPExcel_Cell_DataType:: TYPE_BOOL);
3734. break;
3736. case 1: // error type
3737. $value = self::_mapErrorCode($boolErr);
3739. // add cell value
3740. $cell->setValueExplicit($value, PHPExcel_Cell_DataType:: TYPE_ERROR);
3741. break;
3742. }
3744. if (!$this->_readDataOnly) {
3745. // add cell style
3746. $cell->setXfIndex($this->_mapCellXfIndex[$xfIndex]);
3747. }
3748. }
3749. }
3751. /**
3752. * Read MULBLANK record
3753. * This record represents a cell range of empty cells. All
3754. * cells are located in the same row
3755. *
3756. * -- "OpenOffice.org's Documentation of the Microsoft
3757. * Excel File Format"
3758. */
3759. private function _readMulBlank()
3760. {
3761. $length = self::_GetInt2d($this->_data, $this->_pos + 2);
3762. $recordData = substr($this->_data, $this->_pos + 4, $length);
3764. // move stream pointer to next record
3765. $this->_pos += 4 + $length;
3767. // offset: 0; size: 2; index to row
3768. $row = self::_GetInt2d($recordData, 0);
3770. // offset: 2; size: 2; index to first column
3771. $fc = self::_GetInt2d($recordData, 2);
3773. // offset: 4; size: 2 x nc; list of indexes to XF records
3774. // add style information
3775. if (!$this->_readDataOnly) {
3776. for ($i = 0; $i < $length / 2 - 3; ++$i) {
3777. $columnString = PHPExcel_Cell:: stringFromColumnIndex($fc + $i);
3779. // Read cell?
3780. if ( ! is_null($this-> getReadFilter()) && $this-> getReadFilter()->readCell($columnString, $row + 1, $this->_phpSheet->getTitle()) ) {
3781. $xfIndex = self::_GetInt2d($recordData, 4 + 2 * $i);
3782. $this->_phpSheet->getCell($columnString . ($row + 1))->setXfIndex($this->_mapCellXfIndex[$xfIndex]);
3783. }
3784. }
3785. }
3787. // offset: 6; size 2; index to last column (not needed)
3788. }
3790. /**
3791. * Read LABEL record
3792. * This record represents a cell that contains a string. In
3793. * BIFF8 it is usually replaced by the LABELSST record.
3794. * Excel still uses this record, if it copies unformatted
3795. * text cells to the clipboard.
3796. *
3797. * -- "OpenOffice.org's Documentation of the Microsoft
3798. * Excel File Format"
3799. */
3800. private function _readLabel()
3801. {
3802. $length = self::_GetInt2d($this->_data, $this->_pos + 2);
3803. $recordData = substr($this->_data, $this->_pos + 4, $length);
3805. // move stream pointer to next record
3806. $this->_pos += 4 + $length;
3808. // offset: 0; size: 2; index to row
3809. $row = self::_GetInt2d($recordData, 0);
3811. // offset: 2; size: 2; index to column
3812. $column = self::_GetInt2d($recordData, 2);
3813. $columnString = PHPExcel_Cell:: stringFromColumnIndex($column);
3815. // Read cell?
3816. if ( ! is_null($this-> getReadFilter()) && $this-> getReadFilter()->readCell($columnString, $row + 1, $this->_phpSheet->getTitle()) ) {
3817. // offset: 4; size: 2; XF index
3818. $xfIndex = self::_GetInt2d($recordData, 4);
3820. // add cell value
3821. // todo: what if string is very long? continue record
3822. if ($this->_version == self:: XLS_BIFF8) {
3823. $string = self::_readUnicodeStringLong( substr($recordData, 6));
3824. $value = $string['value'];
3825. } else {
3826. $string = $this->_readByteStringLong( substr($recordData, 6));
3827. $value = $string['value'];
3828. }
3829. $cell = $this->_phpSheet->getCell($columnString . ($row + 1));
3830. $cell->setValueExplicit($value, PHPExcel_Cell_DataType:: TYPE_STRING);
3832. if (!$this->_readDataOnly) {
3833. // add cell style
3834. $cell->setXfIndex($this->_mapCellXfIndex[$xfIndex]);
3835. }
3836. }
3837. }
3839. /**
3840. * Read BLANK record
3841. */
3842. private function _readBlank()
3843. {
3844. $length = self::_GetInt2d($this->_data, $this->_pos + 2);
3845. $recordData = substr($this->_data, $this->_pos + 4, $length);
3847. // move stream pointer to next record
3848. $this->_pos += 4 + $length;
3850. // offset: 0; size: 2; row index
3851. $row = self::_GetInt2d($recordData, 0);
3853. // offset: 2; size: 2; col index
3854. $col = self::_GetInt2d($recordData, 2);
3855. $columnString = PHPExcel_Cell:: stringFromColumnIndex($col);
3857. // Read cell?
3858. if ( ! is_null($this-> getReadFilter()) && $this-> getReadFilter()->readCell($columnString, $row + 1, $this->_phpSheet->getTitle()) ) {
3859. // offset: 4; size: 2; XF index
3860. $xfIndex = self::_GetInt2d($recordData, 4);
3862. // add style information
3863. if (!$this->_readDataOnly) {
3864. $this->_phpSheet->getCell($columnString . ($row + 1))->setXfIndex($this->_mapCellXfIndex[$xfIndex]);
3865. }
3866. }
3868. }
3870. /**
3871. * Read MSODRAWING record
3872. */
3873. private function _readMsoDrawing()
3874. {
3875. $length = self::_GetInt2d($this->_data, $this->_pos + 2);
3877. // get spliced record data
3878. $splicedRecordData = $this->_getSplicedRecordData();
3879. $recordData = $splicedRecordData['recordData'];
3881. $this->_drawingData .= $recordData;
3882. }
3884. /**
3885. * Read OBJ record
3886. */
3887. private function _readObj()
3888. {
3889. $length = self::_GetInt2d($this->_data, $this->_pos + 2);
3890. $recordData = substr($this->_data, $this->_pos + 4, $length);
3892. // move stream pointer to next record
3893. $this->_pos += 4 + $length;
3895. if ($this->_readDataOnly || $this->_version != self:: XLS_BIFF8) {
3896. return;
3897. }
3899. // recordData consists of an array of subrecords looking like this:
3900. // ft: 2 bytes; ftCmo type (0x15)
3901. // cb: 2 bytes; size in bytes of ftCmo data
3902. // ot: 2 bytes; Object Type
3903. // id: 2 bytes; Object id number
3904. // grbit: 2 bytes; Option Flags
3905. // data: var; subrecord data
3907. // for now, we are just interested in the second subrecord containing the object type
3908. $ftCmoType = self::_GetInt2d($recordData, 0);
3909. $cbCmoSize = self::_GetInt2d($recordData, 2);
3910. $otObjType = self::_GetInt2d($recordData, 4);
3911. $idObjID = self::_GetInt2d($recordData, 6);
3912. $grbitOpts = self::_GetInt2d($recordData, 6);
3914. $this->_objs[] = array(
3915. 'ftCmoType' => $ftCmoType,
3916. 'cbCmoSize' => $cbCmoSize,
3917. 'otObjType' => $otObjType,
3918. 'idObjID' => $idObjID,
3919. 'grbitOpts' => $grbitOpts
3920. );
3921. $this->textObjRef = $idObjID;
3923. // echo '<b>_readObj()</b><br />';
3924. // var_dump(end($this->_objs));
3925. // echo '<br />';
3926. }
3928. /**
3929. * Read WINDOW2 record
3930. */
3931. private function _readWindow2()
3932. {
3933. $length = self::_GetInt2d($this->_data, $this->_pos + 2);
3934. $recordData = substr($this->_data, $this->_pos + 4, $length);
3936. // move stream pointer to next record
3937. $this->_pos += 4 + $length;
3939. // offset: 0; size: 2; option flags
3940. $options = self::_GetInt2d($recordData, 0);
3942. // bit: 1; mask: 0x0002; 0 = do not show gridlines, 1 = show gridlines
3943. $showGridlines = (bool) ((0x0002 & $options) >> 1);
3944. $this->_phpSheet->setShowGridlines($showGridlines);
3946. // bit: 2; mask: 0x0004; 0 = do not show headers, 1 = show headers
3947. $showRowColHeaders = (bool) ((0x0004 & $options) >> 2);
3948. $this->_phpSheet->setShowRowColHeaders($showRowColHeaders);
3950. // bit: 3; mask: 0x0008; 0 = panes are not frozen, 1 = panes are frozen
3951. $this->_frozen = (bool) ((0x0008 & $options) >> 3);
3953. // bit: 6; mask: 0x0040; 0 = columns from left to right, 1 = columns from right to left
3954. $this->_phpSheet->setRightToLeft((bool)((0x0040 & $options) >> 6));
3956. // bit: 10; mask: 0x0400; 0 = sheet not active, 1 = sheet active
3957. $isActive = (bool) ((0x0400 & $options) >> 10);
3958. if ($isActive) {
3959. $this->_phpExcel->setActiveSheetIndex($this->_phpExcel->getIndex($this->_phpSheet));
3960. }
3961. }
3963. /**
3964. * Read SCL record
3965. */
3966. private function _readScl()
3967. {
3968. $length = self::_GetInt2d($this->_data, $this->_pos + 2);
3969. $recordData = substr($this->_data, $this->_pos + 4, $length);
3971. // move stream pointer to next record
3972. $this->_pos += 4 + $length;
3974. // offset: 0; size: 2; numerator of the view magnification
3975. $numerator = self::_GetInt2d($recordData, 0);
3977. // offset: 2; size: 2; numerator of the view magnification
3978. $denumerator = self::_GetInt2d($recordData, 2);
3980. // set the zoom scale (in percent)
3981. $this->_phpSheet->getSheetView()->setZoomScale($numerator * 100 / $denumerator);
3982. }
3984. /**
3985. * Read PANE record
3986. */
3987. private function _readPane()
3988. {
3989. $length = self::_GetInt2d($this->_data, $this->_pos + 2);
3990. $recordData = substr($this->_data, $this->_pos + 4, $length);
3992. // move stream pointer to next record
3993. $this->_pos += 4 + $length;
3995. if (!$this->_readDataOnly) {
3996. // offset: 0; size: 2; position of vertical split
3997. $px = self::_GetInt2d($recordData, 0);
3999. // offset: 2; size: 2; position of horizontal split
4000. $py = self::_GetInt2d($recordData, 2);
4002. if ($this->_frozen) {
4003. // frozen panes
4004. $this->_phpSheet->freezePane( PHPExcel_Cell:: stringFromColumnIndex($px) . ($py + 1));
4005. } else {
4006. // unfrozen panes; split windows; not supported by PHPExcel core
4007. }
4008. }
4009. }
4011. /**
4012. * Read SELECTION record. There is one such record for each pane in the sheet.
4013. */
4014. private function _readSelection()
4015. {
4016. $length = self::_GetInt2d($this->_data, $this->_pos + 2);
4017. $recordData = substr($this->_data, $this->_pos + 4, $length);
4019. // move stream pointer to next record
4020. $this->_pos += 4 + $length;
4022. if (!$this->_readDataOnly) {
4023. // offset: 0; size: 1; pane identifier
4024. $paneId = ord($recordData{0});
4026. // offset: 1; size: 2; index to row of the active cell
4027. $r = self::_GetInt2d($recordData, 1);
4029. // offset: 3; size: 2; index to column of the active cell
4030. $c = self::_GetInt2d($recordData, 3);
4032. // offset: 5; size: 2; index into the following cell range list to the
4033. // entry that contains the active cell
4034. $index = self::_GetInt2d($recordData, 5);
4036. // offset: 7; size: var; cell range address list containing all selected cell ranges
4037. $data = substr($recordData, 7);
4038. $cellRangeAddressList = $this->_readBIFF5CellRangeAddressList($data); // note: also BIFF8 uses BIFF5 syntax
4040. $selectedCells = $cellRangeAddressList['cellRangeAddresses'][0];
4042. // first row '1' + last row '16384' indicates that full column is selected (apparently also in BIFF8!)
4043. if ( preg_match('/^([A-Z]+1\:[A-Z]+)16384$/', $selectedCells)) {
4044. $selectedCells = preg_replace('/^([A-Z]+1\:[A-Z]+)16384$/', '${1}1048576', $selectedCells);
4045. }
4047. // first row '1' + last row '65536' indicates that full column is selected
4048. if ( preg_match('/^([A-Z]+1\:[A-Z]+)65536$/', $selectedCells)) {
4049. $selectedCells = preg_replace('/^([A-Z]+1\:[A-Z]+)65536$/', '${1}1048576', $selectedCells);
4050. }
4052. // first column 'A' + last column 'IV' indicates that full row is selected
4053. if ( preg_match('/^(A[0-9]+\:)IV([0-9]+)$/', $selectedCells)) {
4054. $selectedCells = preg_replace('/^(A[0-9]+\:)IV([0-9]+)$/', '${1}XFD${2}', $selectedCells);
4055. }
4057. $this->_phpSheet->setSelectedCells($selectedCells);
4058. }
4059. }
4061. private function _includeCellRangeFiltered($cellRangeAddress)
4062. {
4063. $includeCellRange = true;
4064. if (! is_null($this-> getReadFilter())) {
4065. $includeCellRange = false;
4066. $rangeBoundaries = PHPExcel_Cell:: getRangeBoundaries($cellRangeAddress);
4067. $rangeBoundaries[1][0]++;
4068. for ($row = $rangeBoundaries[0][1]; $row <= $rangeBoundaries[1][1]; $row++) {
4069. for ($column = $rangeBoundaries[0][0]; $column != $rangeBoundaries[1][0]; $column++) {
4070. if ($this-> getReadFilter()->readCell($column, $row, $this->_phpSheet->getTitle())) {
4071. $includeCellRange = true;
4072. break 2;
4073. }
4074. }
4075. }
4076. }
4077. return $includeCellRange;
4078. }
4080. /**
4081. * MERGEDCELLS
4082. *
4083. * This record contains the addresses of merged cell ranges
4084. * in the current sheet.
4085. *
4086. * -- "OpenOffice.org's Documentation of the Microsoft
4087. * Excel File Format"
4088. */
4089. private function _readMergedCells()
4090. {
4091. $length = self::_GetInt2d($this->_data, $this->_pos + 2);
4092. $recordData = substr($this->_data, $this->_pos + 4, $length);
4094. // move stream pointer to next record
4095. $this->_pos += 4 + $length;
4097. if ($this->_version == self:: XLS_BIFF8 && !$this->_readDataOnly) {
4098. $cellRangeAddressList = $this->_readBIFF8CellRangeAddressList($recordData);
4099. foreach ($cellRangeAddressList['cellRangeAddresses'] as $cellRangeAddress) {
4100. if ($this->_includeCellRangeFiltered($cellRangeAddress)) {
4101. $this->_phpSheet->mergeCells($cellRangeAddress);
4102. }
4103. }
4104. }
4105. }
4107. /**
4108. * Read HYPERLINK record
4109. */
4110. private function _readHyperLink()
4111. {
4112. $length = self::_GetInt2d($this->_data, $this->_pos + 2);
4113. $recordData = substr($this->_data, $this->_pos + 4, $length);
4115. // move stream pointer forward to next record
4116. $this->_pos += 4 + $length;
4118. if (!$this->_readDataOnly) {
4119. // offset: 0; size: 8; cell range address of all cells containing this hyperlink
4120. try {
4121. $cellRange = $this->_readBIFF8CellRangeAddressFixed($recordData, 0, 8);
4122. } catch (Exception $e) {
4123. return;
4124. }
4126. // offset: 8, size: 16; GUID of StdLink
4128. // offset: 24, size: 4; unknown value
4130. // offset: 28, size: 4; option flags
4132. // bit: 0; mask: 0x00000001; 0 = no link or extant, 1 = file link or URL
4133. $isFileLinkOrUrl = (0x00000001 & self::_GetInt2d($recordData, 28)) >> 0;
4135. // bit: 1; mask: 0x00000002; 0 = relative path, 1 = absolute path or URL
4136. $isAbsPathOrUrl = (0x00000001 & self::_GetInt2d($recordData, 28)) >> 1;
4138. // bit: 2 (and 4); mask: 0x00000014; 0 = no description
4139. $hasDesc = (0x00000014 & self::_GetInt2d($recordData, 28)) >> 2;
4141. // bit: 3; mask: 0x00000008; 0 = no text, 1 = has text
4142. $hasText = (0x00000008 & self::_GetInt2d($recordData, 28)) >> 3;
4144. // bit: 7; mask: 0x00000080; 0 = no target frame, 1 = has target frame
4145. $hasFrame = (0x00000080 & self::_GetInt2d($recordData, 28)) >> 7;
4147. // bit: 8; mask: 0x00000100; 0 = file link or URL, 1 = UNC path (inc. server name)
4148. $isUNC = (0x00000100 & self::_GetInt2d($recordData, 28)) >> 8;
4150. // offset within record data
4151. $offset = 32;
4153. if ($hasDesc) {
4154. // offset: 32; size: var; character count of description text
4155. $dl = self::_GetInt4d($recordData, 32);
4156. // offset: 36; size: var; character array of description text, no Unicode string header, always 16-bit characters, zero terminated
4157. $desc = self::_encodeUTF16( substr($recordData, 36, 2 * ($dl - 1)), false);
4158. $offset += 4 + 2 * $dl;
4159. }
4160. if ($hasFrame) {
4161. $fl = self::_GetInt4d($recordData, $offset);
4162. $offset += 4 + 2 * $fl;
4163. }
4165. // detect type of hyperlink (there are 4 types)
4166. $hyperlinkType = null;
4168. if ($isUNC) {
4169. $hyperlinkType = 'UNC';
4170. } else if (!$isFileLinkOrUrl) {
4171. $hyperlinkType = 'workbook';
4172. } else if ( ord($recordData{$offset}) == 0x03) {
4173. $hyperlinkType = 'local';
4174. } else if ( ord($recordData{$offset}) == 0xE0) {
4175. $hyperlinkType = 'URL';
4176. }
4178. switch ($hyperlinkType) {
4179. case 'URL':
4180. // section 5.58.2: Hyperlink containing a URL
4181. // e.g. http://example.org/index.php
4183. // offset: var; size: 16; GUID of URL Moniker
4184. $offset += 16;
4185. // offset: var; size: 4; size (in bytes) of character array of the URL including trailing zero word
4186. $us = self::_GetInt4d($recordData, $offset);
4187. $offset += 4;
4188. // offset: var; size: $us; character array of the URL, no Unicode string header, always 16-bit characters, zero-terminated
4189. $url = self::_encodeUTF16( substr($recordData, $offset, $us - 2), false);
4190. $url .= $hasText ? '#' : '';
4191. $offset += $us;
4192. break;
4194. case 'local':
4195. // section 5.58.3: Hyperlink to local file
4196. // examples:
4197. // mydoc.txt
4198. // ../../somedoc.xls#Sheet!A1
4200. // offset: var; size: 16; GUI of File Moniker
4201. $offset += 16;
4203. // offset: var; size: 2; directory up-level count.
4204. $upLevelCount = self::_GetInt2d($recordData, $offset);
4205. $offset += 2;
4207. // offset: var; size: 4; character count of the shortened file path and name, including trailing zero word
4208. $sl = self::_GetInt4d($recordData, $offset);
4209. $offset += 4;
4211. // offset: var; size: sl; character array of the shortened file path and name in 8.3-DOS-format (compressed Unicode string)
4212. $shortenedFilePath = substr($recordData, $offset, $sl);
4213. $shortenedFilePath = self::_encodeUTF16($shortenedFilePath, true);
4214. $shortenedFilePath = substr($shortenedFilePath, 0, -1); // remove trailing zero
4216. $offset += $sl;
4218. // offset: var; size: 24; unknown sequence
4219. $offset += 24;
4221. // extended file path
4222. // offset: var; size: 4; size of the following file link field including string lenth mark
4223. $sz = self::_GetInt4d($recordData, $offset);
4224. $offset += 4;
4226. // only present if $sz > 0
4227. if ($sz > 0) {
4228. // offset: var; size: 4; size of the character array of the extended file path and name
4229. $xl = self::_GetInt4d($recordData, $offset);
4230. $offset += 4;
4232. // offset: var; size 2; unknown
4233. $offset += 2;
4235. // offset: var; size $xl; character array of the extended file path and name.
4236. $extendedFilePath = substr($recordData, $offset, $xl);
4237. $extendedFilePath = self::_encodeUTF16($extendedFilePath, false);
4238. $offset += $xl;
4239. }
4241. // construct the path
4242. $url = str_repeat('..\\', $upLevelCount);
4243. $url .= ($sz > 0) ?
4244. $extendedFilePath : $shortenedFilePath; // use extended path if available
4245. $url .= $hasText ? '#' : '';
4247. break;
4250. case 'UNC':
4251. // section 5.58.4: Hyperlink to a File with UNC (Universal Naming Convention) Path
4252. // todo: implement
4253. return;
4255. case 'workbook':
4256. // section 5.58.5: Hyperlink to the Current Workbook
4257. // e.g. Sheet2!B1:C2, stored in text mark field
4258. $url = 'sheet://';
4259. break;
4261. default:
4262. return;
4264. }
4266. if ($hasText) {
4267. // offset: var; size: 4; character count of text mark including trailing zero word
4268. $tl = self::_GetInt4d($recordData, $offset);
4269. $offset += 4;
4270. // offset: var; size: var; character array of the text mark without the # sign, no Unicode header, always 16-bit characters, zero-terminated
4271. $text = self::_encodeUTF16( substr($recordData, $offset, 2 * ($tl - 1)), false);
4272. $url .= $text;
4273. }
4275. // apply the hyperlink to all the relevant cells
4276. foreach ( PHPExcel_Cell:: extractAllCellReferencesInRange($cellRange) as $coordinate) {
4277. $this->_phpSheet->getCell($coordinate)->getHyperLink()->setUrl($url);
4278. }
4279. }
4280. }
4282. /**
4283. * Read DATAVALIDATIONS record
4284. */
4285. private function _readDataValidations()
4286. {
4287. $length = self::_GetInt2d($this->_data, $this->_pos + 2);
4288. $recordData = substr($this->_data, $this->_pos + 4, $length);
4290. // move stream pointer forward to next record
4291. $this->_pos += 4 + $length;
4292. }
4294. /**
4295. * Read DATAVALIDATION record
4296. */
4297. private function _readDataValidation()
4298. {
4299. $length = self::_GetInt2d($this->_data, $this->_pos + 2);
4300. $recordData = substr($this->_data, $this->_pos + 4, $length);
4302. // move stream pointer forward to next record
4303. $this->_pos += 4 + $length;
4305. if ($this->_readDataOnly) {
4306. return;
4307. }
4309. // offset: 0; size: 4; Options
4310. $options = self::_GetInt4d($recordData, 0);
4312. // bit: 0-3; mask: 0x0000000F; type
4313. $type = (0x0000000F & $options) >> 0;
4314. switch ($type) {
4315. case 0x00: $type = PHPExcel_Cell_DataValidation:: TYPE_NONE; break;
4316. case 0x01: $type = PHPExcel_Cell_DataValidation:: TYPE_WHOLE; break;
4317. case 0x02: $type = PHPExcel_Cell_DataValidation:: TYPE_DECIMAL; break;
4318. case 0x03: $type = PHPExcel_Cell_DataValidation:: TYPE_LIST; break;
4319. case 0x04: $type = PHPExcel_Cell_DataValidation:: TYPE_DATE; break;
4320. case 0x05: $type = PHPExcel_Cell_DataValidation:: TYPE_TIME; break;
4321. case 0x06: $type = PHPExcel_Cell_DataValidation:: TYPE_TEXTLENGTH; break;
4322. case 0x07: $type = PHPExcel_Cell_DataValidation:: TYPE_CUSTOM; break;
4323. }
4325. // bit: 4-6; mask: 0x00000070; error type
4326. $errorStyle = (0x00000070 & $options) >> 4;
4327. switch ($errorStyle) {
4328. case 0x00: $errorStyle = PHPExcel_Cell_DataValidation:: STYLE_STOP; break;
4329. case 0x01: $errorStyle = PHPExcel_Cell_DataValidation:: STYLE_WARNING; break;
4330. case 0x02: $errorStyle = PHPExcel_Cell_DataValidation:: STYLE_INFORMATION; break;
4331. }
4333. // bit: 7; mask: 0x00000080; 1= formula is explicit (only applies to list)
4334. // I have only seen cases where this is 1
4335. $explicitFormula = (0x00000080 & $options) >> 7;
4337. // bit: 8; mask: 0x00000100; 1= empty cells allowed
4338. $allowBlank = (0x00000100 & $options) >> 8;
4340. // bit: 9; mask: 0x00000200; 1= suppress drop down arrow in list type validity
4341. $suppressDropDown = (0x00000200 & $options) >> 9;
4343. // bit: 18; mask: 0x00040000; 1= show prompt box if cell selected
4344. $showInputMessage = (0x00040000 & $options) >> 18;
4346. // bit: 19; mask: 0x00080000; 1= show error box if invalid values entered
4347. $showErrorMessage = (0x00080000 & $options) >> 19;
4349. // bit: 20-23; mask: 0x00F00000; condition operator
4350. $operator = (0x00F00000 & $options) >> 20;
4351. switch ($operator) {
4352. case 0x00: $operator = PHPExcel_Cell_DataValidation:: OPERATOR_BETWEEN ; break;
4353. case 0x01: $operator = PHPExcel_Cell_DataValidation:: OPERATOR_NOTBETWEEN ; break;
4354. case 0x02: $operator = PHPExcel_Cell_DataValidation:: OPERATOR_EQUAL ; break;
4355. case 0x03: $operator = PHPExcel_Cell_DataValidation:: OPERATOR_NOTEQUAL ; break;
4356. case 0x04: $operator = PHPExcel_Cell_DataValidation:: OPERATOR_GREATERTHAN ; break;
4357. case 0x05: $operator = PHPExcel_Cell_DataValidation:: OPERATOR_LESSTHAN ; break;
4358. case 0x06: $operator = PHPExcel_Cell_DataValidation:: OPERATOR_GREATERTHANOREQUAL; break;
4359. case 0x07: $operator = PHPExcel_Cell_DataValidation:: OPERATOR_LESSTHANOREQUAL ; break;
4360. }
4362. // offset: 4; size: var; title of the prompt box
4363. $offset = 4;
4364. $string = self::_readUnicodeStringLong( substr($recordData, $offset));
4365. $promptTitle = $string['value'] !== chr(0) ?
4366. $string['value'] : '';
4367. $offset += $string['size'];
4369. // offset: var; size: var; title of the error box
4370. $string = self::_readUnicodeStringLong( substr($recordData, $offset));
4371. $errorTitle = $string['value'] !== chr(0) ?
4372. $string['value'] : '';
4373. $offset += $string['size'];
4375. // offset: var; size: var; text of the prompt box
4376. $string = self::_readUnicodeStringLong( substr($recordData, $offset));
4377. $prompt = $string['value'] !== chr(0) ?
4378. $string['value'] : '';
4379. $offset += $string['size'];
4381. // offset: var; size: var; text of the error box
4382. $string = self::_readUnicodeStringLong( substr($recordData, $offset));
4383. $error = $string['value'] !== chr(0) ?
4384. $string['value'] : '';
4385. $offset += $string['size'];
4387. // offset: var; size: 2; size of the formula data for the first condition
4388. $sz1 = self::_GetInt2d($recordData, $offset);
4389. $offset += 2;
4391. // offset: var; size: 2; not used
4392. $offset += 2;
4394. // offset: var; size: $sz1; formula data for first condition (without size field)
4395. $formula1 = substr($recordData, $offset, $sz1);
4396. $formula1 = pack('v', $sz1) . $formula1; // prepend the length
4397. try {
4398. $formula1 = $this->_getFormulaFromStructure($formula1);
4400. // in list type validity, null characters are used as item separators
4401. if ($type == PHPExcel_Cell_DataValidation:: TYPE_LIST) {
4402. $formula1 = str_replace( chr(0), ',', $formula1);
4403. }
4404. } catch (Exception $e) {
4405. return;
4406. }
4407. $offset += $sz1;
4409. // offset: var; size: 2; size of the formula data for the first condition
4410. $sz2 = self::_GetInt2d($recordData, $offset);
4411. $offset += 2;
4413. // offset: var; size: 2; not used
4414. $offset += 2;
4416. // offset: var; size: $sz2; formula data for second condition (without size field)
4417. $formula2 = substr($recordData, $offset, $sz2);
4418. $formula2 = pack('v', $sz2) . $formula2; // prepend the length
4419. try {
4420. $formula2 = $this->_getFormulaFromStructure($formula2);
4421. } catch (Exception $e) {
4422. return;
4423. }
4424. $offset += $sz2;
4426. // offset: var; size: var; cell range address list with
4427. $cellRangeAddressList = $this->_readBIFF8CellRangeAddressList( substr($recordData, $offset));
4428. $cellRangeAddresses = $cellRangeAddressList['cellRangeAddresses'];
4430. foreach ($cellRangeAddresses as $cellRange) {
4431. $stRange = $this->_phpSheet->shrinkRangeToFit($cellRange);
4432. $stRange = PHPExcel_Cell:: extractAllCellReferencesInRange($stRange);
4433. foreach ($stRange as $coordinate) {
4434. $objValidation = $this->_phpSheet->getCell($coordinate)->getDataValidation();
4435. $objValidation->setType($type);
4436. $objValidation->setErrorStyle($errorStyle);
4437. $objValidation->setAllowBlank((bool)$allowBlank);
4438. $objValidation->setShowInputMessage((bool)$showInputMessage);
4439. $objValidation->setShowErrorMessage((bool)$showErrorMessage);
4440. $objValidation->setShowDropDown(!$suppressDropDown);
4441. $objValidation->setOperator($operator);
4442. $objValidation->setErrorTitle($errorTitle);
4443. $objValidation->setError($error);
4444. $objValidation->setPromptTitle($promptTitle);
4445. $objValidation->setPrompt($prompt);
4446. $objValidation->setFormula1($formula1);
4447. $objValidation->setFormula2($formula2);
4448. }
4449. }
4451. }
4453. /**
4454. * Read SHEETLAYOUT record. Stores sheet tab color information.
4455. */
4456. private function _readSheetLayout()
4457. {
4458. $length = self::_GetInt2d($this->_data, $this->_pos + 2);
4459. $recordData = substr($this->_data, $this->_pos + 4, $length);
4461. // move stream pointer to next record
4462. $this->_pos += 4 + $length;
4464. // local pointer in record data
4465. $offset = 0;
4467. if (!$this->_readDataOnly) {
4468. // offset: 0; size: 2; repeated record identifier 0x0862
4470. // offset: 2; size: 10; not used
4472. // offset: 12; size: 4; size of record data
4473. // Excel 2003 uses size of 0x14 (documented), Excel 2007 uses size of 0x28 (not documented?)
4474. $sz = self::_GetInt4d($recordData, 12);
4476. switch ($sz) {
4477. case 0x14:
4478. // offset: 16; size: 2; color index for sheet tab
4479. $colorIndex = self::_GetInt2d($recordData, 16);
4480. $color = self::_readColor($colorIndex,$this->_palette,$this->_version);
4481. $this->_phpSheet->getTabColor()->setRGB($color['rgb']);
4482. break;
4484. case 0x28:
4485. // TODO: Investigate structure for .xls SHEETLAYOUT record as saved by MS Office Excel 2007
4486. return;
4487. break;
4488. }
4489. }
4490. }
4492. /**
4493. * Read SHEETPROTECTION record (FEATHEADR)
4494. */
4495. private function _readSheetProtection()
4496. {
4497. $length = self::_GetInt2d($this->_data, $this->_pos + 2);
4498. $recordData = substr($this->_data, $this->_pos + 4, $length);
4500. // move stream pointer to next record
4501. $this->_pos += 4 + $length;
4503. if ($this->_readDataOnly) {
4504. return;
4505. }
4507. // offset: 0; size: 2; repeated record header
4509. // offset: 2; size: 2; FRT cell reference flag (=0 currently)
4511. // offset: 4; size: 8; Currently not used and set to 0
4513. // offset: 12; size: 2; Shared feature type index (2=Enhanced Protetion, 4=SmartTag)
4514. $isf = self::_GetInt2d($recordData, 12);
4515. if ($isf != 2) {
4516. return;
4517. }
4519. // offset: 14; size: 1; =1 since this is a feat header
4521. // offset: 15; size: 4; size of rgbHdrSData
4523. // rgbHdrSData, assume "Enhanced Protection"
4524. // offset: 19; size: 2; option flags
4525. $options = self::_GetInt2d($recordData, 19);
4527. // bit: 0; mask 0x0001; 1 = user may edit objects, 0 = users must not edit objects
4528. $bool = (0x0001 & $options) >> 0;
4529. $this->_phpSheet->getProtection()->setObjects(!$bool);
4531. // bit: 1; mask 0x0002; edit scenarios
4532. $bool = (0x0002 & $options) >> 1;
4533. $this->_phpSheet->getProtection()->setScenarios(!$bool);
4535. // bit: 2; mask 0x0004; format cells
4536. $bool = (0x0004 & $options) >> 2;
4537. $this->_phpSheet->getProtection()->setFormatCells(!$bool);
4539. // bit: 3; mask 0x0008; format columns
4540. $bool = (0x0008 & $options) >> 3;
4541. $this->_phpSheet->getProtection()->setFormatColumns(!$bool);
4543. // bit: 4; mask 0x0010; format rows
4544. $bool = (0x0010 & $options) >> 4;
4545. $this->_phpSheet->getProtection()->setFormatRows(!$bool);
4547. // bit: 5; mask 0x0020; insert columns
4548. $bool = (0x0020 & $options) >> 5;
4549. $this->_phpSheet->getProtection()->setInsertColumns(!$bool);
4551. // bit: 6; mask 0x0040; insert rows
4552. $bool = (0x0040 & $options) >> 6;
4553. $this->_phpSheet->getProtection()->setInsertRows(!$bool);
4555. // bit: 7; mask 0x0080; insert hyperlinks
4556. $bool = (0x0080 & $options) >> 7;
4557. $this->_phpSheet->getProtection()->setInsertHyperlinks(!$bool);
4559. // bit: 8; mask 0x0100; delete columns
4560. $bool = (0x0100 & $options) >> 8;
4561. $this->_phpSheet->getProtection()->setDeleteColumns(!$bool);
4563. // bit: 9; mask 0x0200; delete rows
4564. $bool = (0x0200 & $options) >> 9;
4565. $this->_phpSheet->getProtection()->setDeleteRows(!$bool);
4567. // bit: 10; mask 0x0400; select locked cells
4568. $bool = (0x0400 & $options) >> 10;
4569. $this->_phpSheet->getProtection()->setSelectLockedCells(!$bool);
4571. // bit: 11; mask 0x0800; sort cell range
4572. $bool = (0x0800 & $options) >> 11;
4573. $this->_phpSheet->getProtection()->setSort(!$bool);
4575. // bit: 12; mask 0x1000; auto filter
4576. $bool = (0x1000 & $options) >> 12;
4577. $this->_phpSheet->getProtection()->setAutoFilter(!$bool);
4579. // bit: 13; mask 0x2000; pivot tables
4580. $bool = (0x2000 & $options) >> 13;
4581. $this->_phpSheet->getProtection()->setPivotTables(!$bool);
4583. // bit: 14; mask 0x4000; select unlocked cells
4584. $bool = (0x4000 & $options) >> 14;
4585. $this->_phpSheet->getProtection()->setSelectUnlockedCells(!$bool);
4587. // offset: 21; size: 2; not used
4588. }
4590. /**
4591. * Read RANGEPROTECTION record
4592. * Reading of this record is based on Microsoft Office Excel 97-2000 Binary File Format Specification,
4593. * where it is referred to as FEAT record
4594. */
4595. private function _readRangeProtection()
4596. {
4597. $length = self::_GetInt2d($this->_data, $this->_pos + 2);
4598. $recordData = substr($this->_data, $this->_pos + 4, $length);
4600. // move stream pointer to next record
4601. $this->_pos += 4 + $length;
4603. // local pointer in record data
4604. $offset = 0;
4606. if (!$this->_readDataOnly) {
4607. $offset += 12;
4609. // offset: 12; size: 2; shared feature type, 2 = enhanced protection, 4 = smart tag
4610. $isf = self::_GetInt2d($recordData, 12);
4611. if ($isf != 2) {
4612. // we only read FEAT records of type 2
4613. return;
4614. }
4615. $offset += 2;
4617. $offset += 5;
4619. // offset: 19; size: 2; count of ref ranges this feature is on
4620. $cref = self::_GetInt2d($recordData, 19);
4621. $offset += 2;
4623. $offset += 6;
4625. // offset: 27; size: 8 * $cref; list of cell ranges (like in hyperlink record)
4626. $cellRanges = array();
4627. for ($i = 0; $i < $cref; ++$i) {
4628. try {
4629. $cellRange = $this->_readBIFF8CellRangeAddressFixed( substr($recordData, 27 + 8 * $i, 8));
4630. } catch (Exception $e) {
4631. return;
4632. }
4633. $cellRanges[] = $cellRange;
4634. $offset += 8;
4635. }
4637. // offset: var; size: var; variable length of feature specific data
4638. $rgbFeat = substr($recordData, $offset);
4639. $offset += 4;
4641. // offset: var; size: 4; the encrypted password (only 16-bit although field is 32-bit)
4642. $wPassword = self::_GetInt4d($recordData, $offset);
4643. $offset += 4;
4645. // Apply range protection to sheet
4646. if ($cellRanges) {
4647. $this->_phpSheet->protectCells( implode(' ', $cellRanges), strtoupper( dechex($wPassword)), true);
4648. }
4649. }
4650. }
4652. /**
4653. * Read IMDATA record
4654. */
4655. private function _readImData()
4656. {
4657. $length = self::_GetInt2d($this->_data, $this->_pos + 2);
4659. // get spliced record data
4660. $splicedRecordData = $this->_getSplicedRecordData();
4661. $recordData = $splicedRecordData['recordData'];
4663. // UNDER CONSTRUCTION
4665. // offset: 0; size: 2; image format
4666. $cf = self::_GetInt2d($recordData, 0);
4668. // offset: 2; size: 2; environment from which the file was written
4669. $env = self::_GetInt2d($recordData, 2);
4671. // offset: 4; size: 4; length of the image data
4672. $lcb = self::_GetInt4d($recordData, 4);
4674. // offset: 8; size: var; image data
4675. $iData = substr($recordData, 8);
4677. switch ($cf) {
4678. case 0x09: // Windows bitmap format
4679. // BITMAPCOREINFO
4680. // 1. BITMAPCOREHEADER
4681. // offset: 0; size: 4; bcSize, Specifies the number of bytes required by the structure
4682. $bcSize = self::_GetInt4d($iData, 0);
4683. // var_dump($bcSize);
4685. // offset: 4; size: 2; bcWidth, specifies the width of the bitmap, in pixels
4686. $bcWidth = self::_GetInt2d($iData, 4);
4687. // var_dump($bcWidth);
4689. // offset: 6; size: 2; bcHeight, specifies the height of the bitmap, in pixels.
4690. $bcHeight = self::_GetInt2d($iData, 6);
4691. // var_dump($bcHeight);
4692. $ih = imagecreatetruecolor($bcWidth, $bcHeight);
4694. // offset: 8; size: 2; bcPlanes, specifies the number of planes for the target device. This value must be 1
4696. // offset: 10; size: 2; bcBitCount specifies the number of bits-per-pixel. This value must be 1, 4, 8, or 24
4697. $bcBitCount = self::_GetInt2d($iData, 10);
4698. // var_dump($bcBitCount);
4700. $rgbString = substr($iData, 12);
4701. $rgbTriples = array();
4702. while ( strlen($rgbString) > 0) {
4703. $rgbTriples[] = unpack('Cb/Cg/Cr', $rgbString);
4704. $rgbString = substr($rgbString, 3);
4705. }
4706. $x = 0;
4707. $y = 0;
4708. foreach ($rgbTriples as $i => $rgbTriple) {
4709. $color = imagecolorallocate($ih, $rgbTriple['r'], $rgbTriple['g'], $rgbTriple['b']);
4710. imagesetpixel($ih, $x, $bcHeight - 1 - $y, $color);
4711. $x = ($x + 1) % $bcWidth;
4712. $y = $y + floor(($x + 1) / $bcWidth);
4713. }
4714. //imagepng($ih, 'image.png');
4716. $drawing = new PHPExcel_Worksheet_Drawing();
4717. $drawing->setPath($filename);
4718. $drawing->setWorksheet($this->_phpSheet);
4720. break;
4722. case 0x02: // Windows metafile or Macintosh PICT format
4723. case 0x0e: // native format
4724. default;
4725. break;
4727. }
4729. // _getSplicedRecordData() takes care of moving current position in data stream
4730. }
4732. /**
4733. * Read a free CONTINUE record. Free CONTINUE record may be a camouflaged MSODRAWING record
4734. * When MSODRAWING data on a sheet exceeds 8224 bytes, CONTINUE records are used instead. Undocumented.
4735. * In this case, we must treat the CONTINUE record as a MSODRAWING record
4736. */
4737. private function _readContinue()
4738. {
4739. $length = self::_GetInt2d($this->_data, $this->_pos + 2);
4740. $recordData = substr($this->_data, $this->_pos + 4, $length);
4742. // check if we are reading drawing data
4743. // this is in case a free CONTINUE record occurs in other circumstances we are unaware of
4744. if ($this->_drawingData == '') {
4745. // move stream pointer to next record
4746. $this->_pos += 4 + $length;
4748. return;
4749. }
4751. // check if record data is at least 4 bytes long, otherwise there is no chance this is MSODRAWING data
4752. if ($length < 4) {
4753. // move stream pointer to next record
4754. $this->_pos += 4 + $length;
4756. return;
4757. }
4759. // dirty check to see if CONTINUE record could be a camouflaged MSODRAWING record
4760. // look inside CONTINUE record to see if it looks like a part of an Escher stream
4761. // we know that Escher stream may be split at least at
4762. // 0xF003 MsofbtSpgrContainer
4763. // 0xF004 MsofbtSpContainer
4764. // 0xF00D MsofbtClientTextbox
4765. $validSplitPoints = array(0xF003, 0xF004, 0xF00D); // add identifiers if we find more
4767. $splitPoint = self::_GetInt2d($recordData, 2);
4768. if ( in_array($splitPoint, $validSplitPoints)) {
4769. // get spliced record data (and move pointer to next record)
4770. $splicedRecordData = $this->_getSplicedRecordData();
4771. $this->_drawingData .= $splicedRecordData['recordData'];
4773. return;
4774. }
4776. // move stream pointer to next record
4777. $this->_pos += 4 + $length;
4779. }
4782. /**
4783. * Reads a record from current position in data stream and continues reading data as long as CONTINUE
4784. * records are found. Splices the record data pieces and returns the combined string as if record data
4785. * is in one piece.
4786. * Moves to next current position in data stream to start of next record different from a CONtINUE record
4787. *
4788. * @return array
4789. */
4790. private function _getSplicedRecordData()
4791. {
4792. $data = '';
4793. $spliceOffsets = array();
4795. $i = 0;
4796. $spliceOffsets[0] = 0;
4798. do {
4799. ++$i;
4801. // offset: 0; size: 2; identifier
4802. $identifier = self::_GetInt2d($this->_data, $this->_pos);
4803. // offset: 2; size: 2; length
4804. $length = self::_GetInt2d($this->_data, $this->_pos + 2);
4805. $data .= substr($this->_data, $this->_pos + 4, $length);
4807. $spliceOffsets[$i] = $spliceOffsets[$i - 1] + $length;
4809. $this->_pos += 4 + $length;
4810. $nextIdentifier = self::_GetInt2d($this->_data, $this->_pos);
4811. }
4812. while ($nextIdentifier == self:: XLS_Type_CONTINUE);
4814. $splicedData = array(
4815. 'recordData' => $data,
4816. 'spliceOffsets' => $spliceOffsets,
4817. );
4819. return $splicedData;
4821. }
4823. /**
4824. * Convert formula structure into human readable Excel formula like 'A3+A5*5'
4825. *
4826. * @param string $formulaStructure The complete binary data for the formula
4827. * @param string $baseCell Base cell, only needed when formula contains tRefN tokens, e.g. with shared formulas
4828. * @return string Human readable formula
4829. */
4830. private function _getFormulaFromStructure($formulaStructure, $baseCell = 'A1')
4831. {
4832. // offset: 0; size: 2; size of the following formula data
4833. $sz = self::_GetInt2d($formulaStructure, 0);
4835. // offset: 2; size: sz
4836. $formulaData = substr($formulaStructure, 2, $sz);
4838. // for debug: dump the formula data
4839. //echo '<xmp>';
4840. //echo 'size: ' . $sz . "\n";
4841. //echo 'the entire formula data: ';
4842. //Debug::dump($formulaData);
4843. //echo "\n----\n";
4845. // offset: 2 + sz; size: variable (optional)
4846. if ( strlen($formulaStructure) > 2 + $sz) {
4847. $additionalData = substr($formulaStructure, 2 + $sz);
4849. // for debug: dump the additional data
4850. //echo 'the entire additional data: ';
4851. //Debug::dump($additionalData);
4852. //echo "\n----\n";
4854. } else {
4855. $additionalData = '';
4856. }
4858. return $this->_getFormulaFromData($formulaData, $additionalData, $baseCell);
4859. }
4861. /**
4862. * Take formula data and additional data for formula and return human readable formula
4863. *
4864. * @param string $formulaData The binary data for the formula itself
4865. * @param string $additionalData Additional binary data going with the formula
4866. * @param string $baseCell Base cell, only needed when formula contains tRefN tokens, e.g. with shared formulas
4867. * @return string Human readable formula
4868. */
4869. private function _getFormulaFromData($formulaData, $additionalData = '', $baseCell = 'A1')
4870. {
4871. // start parsing the formula data
4872. $tokens = array();
4874. while ( strlen($formulaData) > 0 and $token = $this->_getNextToken($formulaData, $baseCell)) {
4875. $tokens[] = $token;
4876. $formulaData = substr($formulaData, $token['size']);
4878. // for debug: dump the token
4879. //var_dump($token);
4880. }
4882. $formulaString = $this->_createFormulaFromTokens($tokens, $additionalData);
4884. return $formulaString;
4885. }
4887. /**
4888. * Take array of tokens together with additional data for formula and return human readable formula
4889. *
4890. * @param array $tokens
4891. * @param array $additionalData Additional binary data going with the formula
4892. * @param string $baseCell Base cell, only needed when formula contains tRefN tokens, e.g. with shared formulas
4893. * @return string Human readable formula
4894. */
4895. private function _createFormulaFromTokens($tokens, $additionalData)
4896. {
4897. // empty formula?
4898. if ( count($tokens) == 0) {
4899. return '';
4900. }
4902. $formulaStrings = array();
4903. foreach ($tokens as $token) {
4904. // initialize spaces
4905. $space0 = isset($space0) ? $space0 : ''; // spaces before next token, not tParen
4906. $space1 = isset($space1) ? $space1 : ''; // carriage returns before next token, not tParen
4907. $space2 = isset($space2) ? $space2 : ''; // spaces before opening parenthesis
4908. $space3 = isset($space3) ? $space3 : ''; // carriage returns before opening parenthesis
4909. $space4 = isset($space4) ? $space4 : ''; // spaces before closing parenthesis
4910. $space5 = isset($space5) ? $space5 : ''; // carriage returns before closing parenthesis
4912. switch ($token['name']) {
4913. case 'tAdd': // addition
4914. case 'tConcat': // addition
4915. case 'tDiv': // division
4916. case 'tEQ': // equality
4917. case 'tGE': // greater than or equal
4918. case 'tGT': // greater than
4919. case 'tIsect': // intersection
4920. case 'tLE': // less than or equal
4921. case 'tList': // less than or equal
4922. case 'tLT': // less than
4923. case 'tMul': // multiplication
4924. case 'tNE': // multiplication
4925. case 'tPower': // power
4926. case 'tRange': // range
4927. case 'tSub': // subtraction
4928. $op2 = array_pop($formulaStrings);
4929. $op1 = array_pop($formulaStrings);
4930. $formulaStrings[] = "$op1$space1$space0{$token['data']}$op2";
4931. unset($space0, $space1);
4932. break;
4933. case 'tUplus': // unary plus
4934. case 'tUminus': // unary minus
4935. $op = array_pop($formulaStrings);
4936. $formulaStrings[] = "$space1$space0{$token['data']}$op";
4937. unset($space0, $space1);
4938. break;
4939. case 'tPercent': // percent sign
4940. $op = array_pop($formulaStrings);
4941. $formulaStrings[] = "$op$space1$space0{$token['data']}";
4942. unset($space0, $space1);
4943. break;
4944. case 'tAttrVolatile': // indicates volatile function
4945. case 'tAttrIf':
4946. case 'tAttrSkip':
4947. case 'tAttrChoose':
4948. // token is only important for Excel formula evaluator
4949. // do nothing
4950. break;
4951. case 'tAttrSpace': // space / carriage return
4952. // space will be used when next token arrives, do not alter formulaString stack
4953. switch ($token['data']['spacetype']) {
4954. case 'type0':
4955. $space0 = str_repeat(' ', $token['data']['spacecount']);
4956. break;
4957. case 'type1':
4958. $space1 = str_repeat("\n", $token['data']['spacecount']);
4959. break;
4960. case 'type2':
4961. $space2 = str_repeat(' ', $token['data']['spacecount']);
4962. break;
4963. case 'type3':
4964. $space3 = str_repeat("\n", $token['data']['spacecount']);
4965. break;
4966. case 'type4':
4967. $space4 = str_repeat(' ', $token['data']['spacecount']);
4968. break;
4969. case 'type5':
4970. $space5 = str_repeat("\n", $token['data']['spacecount']);
4971. break;
4972. }
4973. break;
4974. case 'tAttrSum': // SUM function with one parameter
4975. $op = array_pop($formulaStrings);
4976. $formulaStrings[] = "{$space1}{$space0}SUM($op)";
4977. unset($space0, $space1);
4978. break;
4979. case 'tFunc': // function with fixed number of arguments
4980. case 'tFuncV': // function with variable number of arguments
4981. if ($token['data']['function'] != '') {
4982. // normal function
4983. $ops = array(); // array of operators
4984. for ($i = 0; $i < $token['data']['args']; ++$i) {
4985. $ops[] = array_pop($formulaStrings);
4986. }
4987. $ops = array_reverse($ops);
4988. $formulaStrings[] = "$space1$space0{$token['data']['function']}(" . implode(',', $ops) . ")";
4989. unset($space0, $space1);
4990. } else {
4991. // add-in function
4992. $ops = array(); // array of operators
4993. for ($i = 0; $i < $token['data']['args'] - 1; ++$i) {
4994. $ops[] = array_pop($formulaStrings);
4995. }
4996. $ops = array_reverse($ops);
4997. $function = array_pop($formulaStrings);
4998. $formulaStrings[] = "$space1$space0$function(" . implode(',', $ops) . ")";
4999. unset($space0, $space1);
5000. }
5001. break;
5002. case 'tParen': // parenthesis
5003. $expression = array_pop($formulaStrings);
5004. $formulaStrings[] = "$space3$space2($expression$space5$space4)";
5005. unset($space2, $space3, $space4, $space5);
5006. break;
5007. case 'tArray': // array constant
5008. $constantArray = self::_readBIFF8ConstantArray($additionalData);
5009. $formulaStrings[] = $space1 . $space0 . $constantArray['value'];
5010. $additionalData = substr($additionalData, $constantArray['size']); // bite of chunk of additional data
5011. unset($space0, $space1);
5012. break;
5013. case 'tMemArea':
5014. // bite off chunk of additional data
5015. $cellRangeAddressList = $this->_readBIFF8CellRangeAddressList($additionalData);
5016. $additionalData = substr($additionalData, $cellRangeAddressList['size']);
5017. $formulaStrings[] = "$space1$space0{$token['data']}";
5018. unset($space0, $space1);
5019. break;
5020. case 'tArea': // cell range address
5021. case 'tBool': // boolean
5022. case 'tErr': // error code
5023. case 'tInt': // integer
5024. case 'tMemErr':
5025. case 'tMemFunc':
5026. case 'tMissArg':
5027. case 'tName':
5028. case 'tNameX':
5029. case 'tNum': // number
5030. case 'tRef': // single cell reference
5031. case 'tRef3d': // 3d cell reference
5032. case 'tArea3d': // 3d cell range reference
5033. case 'tRefN':
5034. case 'tAreaN':
5035. case 'tStr': // string
5036. $formulaStrings[] = "$space1$space0{$token['data']}";
5037. unset($space0, $space1);
5038. break;
5039. }
5040. }
5041. $formulaString = $formulaStrings[0];
5043. // for debug: dump the human readable formula
5044. //echo '----' . "\n";
5045. //echo 'Formula: ' . $formulaString;
5047. return $formulaString;
5048. }
5050. /**
5051. * Fetch next token from binary formula data
5052. *
5053. * @param string Formula data
5054. * @param string $baseCell Base cell, only needed when formula contains tRefN tokens, e.g. with shared formulas
5055. * @return array
5056. * @throws Exception
5057. */
5058. private function _getNextToken($formulaData, $baseCell = 'A1')
5059. {
5060. // offset: 0; size: 1; token id
5061. $id = ord($formulaData[0]); // token id
5062. $name = false; // initialize token name
5064. switch ($id) {
5065. case 0x03: $name = 'tAdd'; $size = 1; $data = '+'; break;
5066. case 0x04: $name = 'tSub'; $size = 1; $data = '-'; break;
5067. case 0x05: $name = 'tMul'; $size = 1; $data = '*'; break;
5068. case 0x06: $name = 'tDiv'; $size = 1; $data = '/'; break;
5069. case 0x07: $name = 'tPower'; $size = 1; $data = '^'; break;
5070. case 0x08: $name = 'tConcat'; $size = 1; $data = '&'; break;
5071. case 0x09: $name = 'tLT'; $size = 1; $data = '<'; break;
5072. case 0x0A: $name = 'tLE'; $size = 1; $data = '<='; break;
5073. case 0x0B: $name = 'tEQ'; $size = 1; $data = '='; break;
5074. case 0x0C: $name = 'tGE'; $size = 1; $data = '>='; break;
5075. case 0x0D: $name = 'tGT'; $size = 1; $data = '>'; break;
5076. case 0x0E: $name = 'tNE'; $size = 1; $data = '<>'; break;
5077. case 0x0F: $name = 'tIsect'; $size = 1; $data = ' '; break;
5078. case 0x10: $name = 'tList'; $size = 1; $data = ','; break;
5079. case 0x11: $name = 'tRange'; $size = 1; $data = ':'; break;
5080. case 0x12: $name = 'tUplus'; $size = 1; $data = '+'; break;
5081. case 0x13: $name = 'tUminus'; $size = 1; $data = '-'; break;
5082. case 0x14: $name = 'tPercent'; $size = 1; $data = '%'; break;
5083. case 0x15: // parenthesis
5084. $name = 'tParen';
5085. $size = 1;
5086. $data = null;
5087. break;
5088. case 0x16: // missing argument
5089. $name = 'tMissArg';
5090. $size = 1;
5091. $data = '';
5092. break;
5093. case 0x17: // string
5094. $name = 'tStr';
5095. // offset: 1; size: var; Unicode string, 8-bit string length
5096. $string = self::_readUnicodeStringShort( substr($formulaData, 1));
5097. $size = 1 + $string['size'];
5098. $data = self::_UTF8toExcelDoubleQuoted($string['value']);
5099. break;
5100. case 0x19: // Special attribute
5101. // offset: 1; size: 1; attribute type flags:
5102. switch ( ord($formulaData[1])) {
5103. case 0x01:
5104. $name = 'tAttrVolatile';
5105. $size = 4;
5106. $data = null;
5107. break;
5108. case 0x02:
5109. $name = 'tAttrIf';
5110. $size = 4;
5111. $data = null;
5112. break;
5113. case 0x04:
5114. $name = 'tAttrChoose';
5115. // offset: 2; size: 2; number of choices in the CHOOSE function ($nc, number of parameters decreased by 1)
5116. $nc = self::_GetInt2d($formulaData, 2);
5117. // offset: 4; size: 2 * $nc
5118. // offset: 4 + 2 * $nc; size: 2
5119. $size = 2 * $nc + 6;
5120. $data = null;
5121. break;
5122. case 0x08:
5123. $name = 'tAttrSkip';
5124. $size = 4;
5125. $data = null;
5126. break;
5127. case 0x10:
5128. $name = 'tAttrSum';
5129. $size = 4;
5130. $data = null;
5131. break;
5132. case 0x40:
5133. case 0x41:
5134. $name = 'tAttrSpace';
5135. $size = 4;
5136. // offset: 2; size: 2; space type and position
5137. switch ( ord($formulaData[2])) {
5138. case 0x00:
5139. $spacetype = 'type0';
5140. break;
5141. case 0x01:
5142. $spacetype = 'type1';
5143. break;
5144. case 0x02:
5145. $spacetype = 'type2';
5146. break;
5147. case 0x03:
5148. $spacetype = 'type3';
5149. break;
5150. case 0x04:
5151. $spacetype = 'type4';
5152. break;
5153. case 0x05:
5154. $spacetype = 'type5';
5155. break;
5156. default:
5157. throw new Exception('Unrecognized space type in tAttrSpace token');
5158. break;
5159. }
5160. // offset: 3; size: 1; number of inserted spaces/carriage returns
5161. $spacecount = ord($formulaData[3]);
5163. $data = array('spacetype' => $spacetype, 'spacecount' => $spacecount);
5164. break;
5165. default:
5166. throw new Exception('Unrecognized attribute flag in tAttr token');
5167. break;
5168. }
5169. break;
5170. case 0x1C: // error code
5171. // offset: 1; size: 1; error code
5172. $name = 'tErr';
5173. $size = 2;
5174. $data = self::_mapErrorCode( ord($formulaData[1]));
5175. break;
5176. case 0x1D: // boolean
5177. // offset: 1; size: 1; 0 = false, 1 = true;
5178. $name = 'tBool';
5179. $size = 2;
5180. $data = ord($formulaData[1]) ? 'TRUE' : 'FALSE';
5181. break;
5182. case 0x1E: // integer
5183. // offset: 1; size: 2; unsigned 16-bit integer
5184. $name = 'tInt';
5185. $size = 3;
5186. $data = self::_GetInt2d($formulaData, 1);
5187. break;
5188. case 0x1F: // number
5189. // offset: 1; size: 8;
5190. $name = 'tNum';
5191. $size = 9;
5192. $data = self::_extractNumber( substr($formulaData, 1));
5193. $data = str_replace(',', '.', (string)$data); // in case non-English locale
5194. break;
5195. case 0x20: // array constant
5196. case 0x40:
5197. case 0x60:
5198. // offset: 1; size: 7; not used
5199. $name = 'tArray';
5200. $size = 8;
5201. $data = null;
5202. break;
5203. case 0x21: // function with fixed number of arguments
5204. case 0x41:
5205. case 0x61:
5206. $name = 'tFunc';
5207. $size = 3;
5208. // offset: 1; size: 2; index to built-in sheet function
5209. switch (self::_GetInt2d($formulaData, 1)) {
5210. case 2: $function = 'ISNA'; $args = 1; break;
5211. case 3: $function = 'ISERROR'; $args = 1; break;
5212. case 10: $function = 'NA'; $args = 0; break;
5213. case 15: $function = 'SIN'; $args = 1; break;
5214. case 16: $function = 'COS'; $args = 1; break;
5215. case 17: $function = 'TAN'; $args = 1; break;
5216. case 18: $function = 'ATAN'; $args = 1; break;
5217. case 19: $function = 'PI'; $args = 0; break;
5218. case 20: $function = 'SQRT'; $args = 1; break;
5219. case 21: $function = 'EXP'; $args = 1; break;
5220. case 22: $function = 'LN'; $args = 1; break;
5221. case 23: $function = 'LOG10'; $args = 1; break;
5222. case 24: $function = 'ABS'; $args = 1; break;
5223. case 25: $function = 'INT'; $args = 1; break;
5224. case 26: $function = 'SIGN'; $args = 1; break;
5225. case 27: $function = 'ROUND'; $args = 2; break;
5226. case 30: $function = 'REPT'; $args = 2; break;
5227. case 31: $function = 'MID'; $args = 3; break;
5228. case 32: $function = 'LEN'; $args = 1; break;
5229. case 33: $function = 'VALUE'; $args = 1; break;
5230. case 34: $function = 'TRUE'; $args = 0; break;
5231. case 35: $function = 'FALSE'; $args = 0; break;
5232. case 38: $function = 'NOT'; $args = 1; break;
5233. case 39: $function = 'MOD'; $args = 2; break;
5234. case 40: $function = 'DCOUNT'; $args = 3; break;
5235. case 41: $function = 'DSUM'; $args = 3; break;
5236. case 42: $function = 'DAVERAGE'; $args = 3; break;
5237. case 43: $function = 'DMIN'; $args = 3; break;
5238. case 44: $function = 'DMAX'; $args = 3; break;
5239. case 45: $function = 'DSTDEV'; $args = 3; break;
5240. case 48: $function = 'TEXT'; $args = 2; break;
5241. case 61: $function = 'MIRR'; $args = 3; break;
5242. case 63: $function = 'RAND'; $args = 0; break;
5243. case 65: $function = 'DATE'; $args = 3; break;
5244. case 66: $function = 'TIME'; $args = 3; break;
5245. case 67: $function = 'DAY'; $args = 1; break;
5246. case 68: $function = 'MONTH'; $args = 1; break;
5247. case 69: $function = 'YEAR'; $args = 1; break;
5248. case 71: $function = 'HOUR'; $args = 1; break;
5249. case 72: $function = 'MINUTE'; $args = 1; break;
5250. case 73: $function = 'SECOND'; $args = 1; break;
5251. case 74: $function = 'NOW'; $args = 0; break;
5252. case 75: $function = 'AREAS'; $args = 1; break;
5253. case 76: $function = 'ROWS'; $args = 1; break;
5254. case 77: $function = 'COLUMNS'; $args = 1; break;
5255. case 83: $function = 'TRANSPOSE'; $args = 1; break;
5256. case 86: $function = 'TYPE'; $args = 1; break;
5257. case 97: $function = 'ATAN2'; $args = 2; break;
5258. case 98: $function = 'ASIN'; $args = 1; break;
5259. case 99: $function = 'ACOS'; $args = 1; break;
5260. case 105: $function = 'ISREF'; $args = 1; break;
5261. case 111: $function = 'CHAR'; $args = 1; break;
5262. case 112: $function = 'LOWER'; $args = 1; break;
5263. case 113: $function = 'UPPER'; $args = 1; break;
5264. case 114: $function = 'PROPER'; $args = 1; break;
5265. case 117: $function = 'EXACT'; $args = 2; break;
5266. case 118: $function = 'TRIM'; $args = 1; break;
5267. case 119: $function = 'REPLACE'; $args = 4; break;
5268. case 121: $function = 'CODE'; $args = 1; break;
5269. case 126: $function = 'ISERR'; $args = 1; break;
5270. case 127: $function = 'ISTEXT'; $args = 1; break;
5271. case 128: $function = 'ISNUMBER'; $args = 1; break;
5272. case 129: $function = 'ISBLANK'; $args = 1; break;
5273. case 130: $function = 'T'; $args = 1; break;
5274. case 131: $function = 'N'; $args = 1; break;
5275. case 140: $function = 'DATEVALUE'; $args = 1; break;
5276. case 141: $function = 'TIMEVALUE'; $args = 1; break;
5277. case 142: $function = 'SLN'; $args = 3; break;
5278. case 143: $function = 'SYD'; $args = 4; break;
5279. case 162: $function = 'CLEAN'; $args = 1; break;
5280. case 163: $function = 'MDETERM'; $args = 1; break;
5281. case 164: $function = 'MINVERSE'; $args = 1; break;
5282. case 165: $function = 'MMULT'; $args = 2; break;
5283. case 184: $function = 'FACT'; $args = 1; break;
5284. case 189: $function = 'DPRODUCT'; $args = 3; break;
5285. case 190: $function = 'ISNONTEXT'; $args = 1; break;
5286. case 195: $function = 'DSTDEVP'; $args = 3; break;
5287. case 196: $function = 'DVARP'; $args = 3; break;
5288. case 198: $function = 'ISLOGICAL'; $args = 1; break;
5289. case 199: $function = 'DCOUNTA'; $args = 3; break;
5290. case 207: $function = 'REPLACEB'; $args = 4; break;
5291. case 210: $function = 'MIDB'; $args = 3; break;
5292. case 211: $function = 'LENB'; $args = 1; break;
5293. case 212: $function = 'ROUNDUP'; $args = 2; break;
5294. case 213: $function = 'ROUNDDOWN'; $args = 2; break;
5295. case 214: $function = 'ASC'; $args = 1; break;
5296. case 215: $function = 'DBCS'; $args = 1; break;
5297. case 221: $function = 'TODAY'; $args = 0; break;
5298. case 229: $function = 'SINH'; $args = 1; break;
5299. case 230: $function = 'COSH'; $args = 1; break;
5300. case 231: $function = 'TANH'; $args = 1; break;
5301. case 232: $function = 'ASINH'; $args = 1; break;
5302. case 233: $function = 'ACOSH'; $args = 1; break;
5303. case 234: $function = 'ATANH'; $args = 1; break;
5304. case 235: $function = 'DGET'; $args = 3; break;
5305. case 244: $function = 'INFO'; $args = 1; break;
5306. case 252: $function = 'FREQUENCY'; $args = 2; break;
5307. case 261: $function = 'ERROR.TYPE'; $args = 1; break;
5308. case 271: $function = 'GAMMALN'; $args = 1; break;
5309. case 273: $function = 'BINOMDIST'; $args = 4; break;
5310. case 274: $function = 'CHIDIST'; $args = 2; break;
5311. case 275: $function = 'CHIINV'; $args = 2; break;
5312. case 276: $function = 'COMBIN'; $args = 2; break;
5313. case 277: $function = 'CONFIDENCE'; $args = 3; break;
5314. case 278: $function = 'CRITBINOM'; $args = 3; break;
5315. case 279: $function = 'EVEN'; $args = 1; break;
5316. case 280: $function = 'EXPONDIST'; $args = 3; break;
5317. case 281: $function = 'FDIST'; $args = 3; break;
5318. case 282: $function = 'FINV'; $args = 3; break;
5319. case 283: $function = 'FISHER'; $args = 1; break;
5320. case 284: $function = 'FISHERINV'; $args = 1; break;
5321. case 285: $function = 'FLOOR'; $args = 2; break;
5322. case 286: $function = 'GAMMADIST'; $args = 4; break;
5323. case 287: $function = 'GAMMAINV'; $args = 3; break;
5324. case 288: $function = 'CEILING'; $args = 2; break;
5325. case 289: $function = 'HYPGEOMDIST'; $args = 4; break;
5326. case 290: $function = 'LOGNORMDIST'; $args = 3; break;
5327. case 291: $function = 'LOGINV'; $args = 3; break;
5328. case 292: $function = 'NEGBINOMDIST'; $args = 3; break;
5329. case 293: $function = 'NORMDIST'; $args = 4; break;
5330. case 294: $function = 'NORMSDIST'; $args = 1; break;
5331. case 295: $function = 'NORMINV'; $args = 3; break;
5332. case 296: $function = 'NORMSINV'; $args = 1; break;
5333. case 297: $function = 'STANDARDIZE'; $args = 3; break;
5334. case 298: $function = 'ODD'; $args = 1; break;
5335. case 299: $function = 'PERMUT'; $args = 2; break;
5336. case 300: $function = 'POISSON'; $args = 3; break;
5337. case 301: $function = 'TDIST'; $args = 3; break;
5338. case 302: $function = 'WEIBULL'; $args = 4; break;
5339. case 303: $function = 'SUMXMY2'; $args = 2; break;
5340. case 304: $function = 'SUMX2MY2'; $args = 2; break;
5341. case 305: $function = 'SUMX2PY2'; $args = 2; break;
5342. case 306: $function = 'CHITEST'; $args = 2; break;
5343. case 307: $function = 'CORREL'; $args = 2; break;
5344. case 308: $function = 'COVAR'; $args = 2; break;
5345. case 309: $function = 'FORECAST'; $args = 3; break;
5346. case 310: $function = 'FTEST'; $args = 2; break;
5347. case 311: $function = 'INTERCEPT'; $args = 2; break;
5348. case 312: $function = 'PEARSON'; $args = 2; break;
5349. case 313: $function = 'RSQ'; $args = 2; break;
5350. case 314: $function = 'STEYX'; $args = 2; break;
5351. case 315: $function = 'SLOPE'; $args = 2; break;
5352. case 316: $function = 'TTEST'; $args = 4; break;
5353. case 325: $function = 'LARGE'; $args = 2; break;
5354. case 326: $function = 'SMALL'; $args = 2; break;
5355. case 327: $function = 'QUARTILE'; $args = 2; break;
5356. case 328: $function = 'PERCENTILE'; $args = 2; break;
5357. case 331: $function = 'TRIMMEAN'; $args = 2; break;
5358. case 332: $function = 'TINV'; $args = 2; break;
5359. case 337: $function = 'POWER'; $args = 2; break;
5360. case 342: $function = 'RADIANS'; $args = 1; break;
5361. case 343: $function = 'DEGREES'; $args = 1; break;
5362. case 346: $function = 'COUNTIF'; $args = 2; break;
5363. case 347: $function = 'COUNTBLANK'; $args = 1; break;
5364. case 350: $function = 'ISPMT'; $args = 4; break;
5365. case 351: $function = 'DATEDIF'; $args = 3; break;
5366. case 352: $function = 'DATESTRING'; $args = 1; break;
5367. case 353: $function = 'NUMBERSTRING'; $args = 2; break;
5368. case 360: $function = 'PHONETIC'; $args = 1; break;
5369. case 368: $function = 'BAHTTEXT'; $args = 1; break;
5370. default:
5371. throw new Exception('Unrecognized function in formula');
5372. break;
5373. }
5374. $data = array('function' => $function, 'args' => $args);
5375. break;
5376. case 0x22: // function with variable number of arguments
5377. case 0x42:
5378. case 0x62:
5379. $name = 'tFuncV';
5380. $size = 4;
5381. // offset: 1; size: 1; number of arguments
5382. $args = ord($formulaData[1]);
5383. // offset: 2: size: 2; index to built-in sheet function
5384. $index = self::_GetInt2d($formulaData, 2);
5385. switch ($index) {
5386. case 0: $function = 'COUNT'; break;
5387. case 1: $function = 'IF'; break;
5388. case 4: $function = 'SUM'; break;
5389. case 5: $function = 'AVERAGE'; break;
5390. case 6: $function = 'MIN'; break;
5391. case 7: $function = 'MAX'; break;
5392. case 8: $function = 'ROW'; break;
5393. case 9: $function = 'COLUMN'; break;
5394. case 11: $function = 'NPV'; break;
5395. case 12: $function = 'STDEV'; break;
5396. case 13: $function = 'DOLLAR'; break;
5397. case 14: $function = 'FIXED'; break;
5398. case 28: $function = 'LOOKUP'; break;
5399. case 29: $function = 'INDEX'; break;
5400. case 36: $function = 'AND'; break;
5401. case 37: $function = 'OR'; break;
5402. case 46: $function = 'VAR'; break;
5403. case 49: $function = 'LINEST'; break;
5404. case 50: $function = 'TREND'; break;
5405. case 51: $function = 'LOGEST'; break;
5406. case 52: $function = 'GROWTH'; break;
5407. case 56: $function = 'PV'; break;
5408. case 57: $function = 'FV'; break;
5409. case 58: $function = 'NPER'; break;
5410. case 59: $function = 'PMT'; break;
5411. case 60: $function = 'RATE'; break;
5412. case 62: $function = 'IRR'; break;
5413. case 64: $function = 'MATCH'; break;
5414. case 70: $function = 'WEEKDAY'; break;
5415. case 78: $function = 'OFFSET'; break;
5416. case 82: $function = 'SEARCH'; break;
5417. case 100: $function = 'CHOOSE'; break;
5418. case 101: $function = 'HLOOKUP'; break;
5419. case 102: $function = 'VLOOKUP'; break;
5420. case 109: $function = 'LOG'; break;
5421. case 115: $function = 'LEFT'; break;
5422. case 116: $function = 'RIGHT'; break;
5423. case 120: $function = 'SUBSTITUTE'; break;
5424. case 124: $function = 'FIND'; break;
5425. case 125: $function = 'CELL'; break;
5426. case 144: $function = 'DDB'; break;
5427. case 148: $function = 'INDIRECT'; break;
5428. case 167: $function = 'IPMT'; break;
5429. case 168: $function = 'PPMT'; break;
5430. case 169: $function = 'COUNTA'; break;
5431. case 183: $function = 'PRODUCT'; break;
5432. case 193: $function = 'STDEVP'; break;
5433. case 194: $function = 'VARP'; break;
5434. case 197: $function = 'TRUNC'; break;
5435. case 204: $function = 'USDOLLAR'; break;
5436. case 205: $function = 'FINDB'; break;
5437. case 206: $function = 'SEARCHB'; break;
5438. case 208: $function = 'LEFTB'; break;
5439. case 209: $function = 'RIGHTB'; break;
5440. case 216: $function = 'RANK'; break;
5441. case 219: $function = 'ADDRESS'; break;
5442. case 220: $function = 'DAYS360'; break;
5443. case 222: $function = 'VDB'; break;
5444. case 227: $function = 'MEDIAN'; break;
5445. case 228: $function = 'SUMPRODUCT'; break;
5446. case 247: $function = 'DB'; break;
5447. case 255: $function = ''; break;
5448. case 269: $function = 'AVEDEV'; break;
5449. case 270: $function = 'BETADIST'; break;
5450. case 272: $function = 'BETAINV'; break;
5451. case 317: $function = 'PROB'; break;
5452. case 318: $function = 'DEVSQ'; break;
5453. case 319: $function = 'GEOMEAN'; break;
5454. case 320: $function = 'HARMEAN'; break;
5455. case 321: $function = 'SUMSQ'; break;
5456. case 322: $function = 'KURT'; break;
5457. case 323: $function = 'SKEW'; break;
5458. case 324: $function = 'ZTEST'; break;
5459. case 329: $function = 'PERCENTRANK'; break;
5460. case 330: $function = 'MODE'; break;
5461. case 336: $function = 'CONCATENATE'; break;
5462. case 344: $function = 'SUBTOTAL'; break;
5463. case 345: $function = 'SUMIF'; break;
5464. case 354: $function = 'ROMAN'; break;
5465. case 358: $function = 'GETPIVOTDATA'; break;
5466. case 359: $function = 'HYPERLINK'; break;
5467. case 361: $function = 'AVERAGEA'; break;
5468. case 362: $function = 'MAXA'; break;
5469. case 363: $function = 'MINA'; break;
5470. case 364: $function = 'STDEVPA'; break;
5471. case 365: $function = 'VARPA'; break;
5472. case 366: $function = 'STDEVA'; break;
5473. case 367: $function = 'VARA'; break;
5474. default:
5475. throw new Exception('Unrecognized function in formula');
5476. break;
5477. }
5478. $data = array('function' => $function, 'args' => $args);
5479. break;
5480. case 0x23: // index to defined name
5481. case 0x43:
5482. case 0x63:
5483. $name = 'tName';
5484. $size = 5;
5485. // offset: 1; size: 2; one-based index to definedname record
5486. $definedNameIndex = self::_GetInt2d($formulaData, 1) - 1;
5487. // offset: 2; size: 2; not used
5488. $data = $this->_definedname[$definedNameIndex]['name'];
5489. break;
5490. case 0x24: // single cell reference e.g. A5
5491. case 0x44:
5492. case 0x64:
5493. $name = 'tRef';
5494. $size = 5;
5495. $data = $this->_readBIFF8CellAddress( substr($formulaData, 1, 4));
5496. break;
5497. case 0x25: // cell range reference to cells in the same sheet (2d)
5498. case 0x45:
5499. case 0x65:
5500. $name = 'tArea';
5501. $size = 9;
5502. $data = $this->_readBIFF8CellRangeAddress( substr($formulaData, 1, 8));
5503. break;
5504. case 0x26: // Constant reference sub-expression
5505. case 0x46:
5506. case 0x66:
5507. $name = 'tMemArea';
5508. // offset: 1; size: 4; not used
5509. // offset: 5; size: 2; size of the following subexpression
5510. $subSize = self::_GetInt2d($formulaData, 5);
5511. $size = 7 + $subSize;
5512. $data = $this->_getFormulaFromData( substr($formulaData, 7, $subSize));
5513. break;
5514. case 0x27: // Deleted constant reference sub-expression
5515. case 0x47:
5516. case 0x67:
5517. $name = 'tMemErr';
5518. // offset: 1; size: 4; not used
5519. // offset: 5; size: 2; size of the following subexpression
5520. $subSize = self::_GetInt2d($formulaData, 5);
5521. $size = 7 + $subSize;
5522. $data = $this->_getFormulaFromData( substr($formulaData, 7, $subSize));
5523. break;
5524. case 0x29: // Variable reference sub-expression
5525. case 0x49:
5526. case 0x69:
5527. $name = 'tMemFunc';
5528. // offset: 1; size: 2; size of the following sub-expression
5529. $subSize = self::_GetInt2d($formulaData, 1);
5530. $size = 3 + $subSize;
5531. $data = $this->_getFormulaFromData( substr($formulaData, 3, $subSize));
5532. break;
5534. case 0x2C: // Relative 2d cell reference reference, used in shared formulas and some other places
5535. case 0x4C:
5536. case 0x6C:
5537. $name = 'tRefN';
5538. $size = 5;
5539. $data = $this->_readBIFF8CellAddressB( substr($formulaData, 1, 4), $baseCell);
5540. break;
5542. case 0x2D: // Relative 2d range reference
5543. case 0x4D:
5544. case 0x6D:
5545. $name = 'tAreaN';
5546. $size = 9;
5547. $data = $this->_readBIFF8CellRangeAddressB( substr($formulaData, 1, 8), $baseCell);
5548. break;
5550. case 0x39: // External name
5551. case 0x59:
5552. case 0x79:
5553. $name = 'tNameX';
5554. $size = 7;
5555. // offset: 1; size: 2; index to REF entry in EXTERNSHEET record
5556. // offset: 3; size: 2; one-based index to DEFINEDNAME or EXTERNNAME record
5557. $index = self::_GetInt2d($formulaData, 3);
5558. // assume index is to EXTERNNAME record
5559. $data = $this->_externalNames[$index - 1]['name'];
5560. // offset: 5; size: 2; not used
5561. break;
5563. case 0x3A: // 3d reference to cell
5564. case 0x5A:
5565. case 0x7A:
5566. $name = 'tRef3d';
5567. $size = 7;
5569. try {
5570. // offset: 1; size: 2; index to REF entry
5571. $sheetRange = $this->_readSheetRangeByRefIndex(self::_GetInt2d($formulaData, 1));
5572. // offset: 3; size: 4; cell address
5573. $cellAddress = $this->_readBIFF8CellAddress( substr($formulaData, 3, 4));
5575. $data = "$sheetRange!$cellAddress";
5576. } catch (Exception $e) {
5577. // deleted sheet reference
5578. $data = '#REF!';
5579. }
5581. break;
5582. case 0x3B: // 3d reference to cell range
5583. case 0x5B:
5584. case 0x7B:
5585. $name = 'tArea3d';
5586. $size = 11;
5588. try {
5589. // offset: 1; size: 2; index to REF entry
5590. $sheetRange = $this->_readSheetRangeByRefIndex(self::_GetInt2d($formulaData, 1));
5591. // offset: 3; size: 8; cell address
5592. $cellRangeAddress = $this->_readBIFF8CellRangeAddress( substr($formulaData, 3, 8));
5594. $data = "$sheetRange!$cellRangeAddress";
5595. } catch (Exception $e) {
5596. // deleted sheet reference
5597. $data = '#REF!';
5598. }
5600. break;
5601. // Unknown cases // don't know how to deal with
5602. default:
5603. throw new Exception('Unrecognized token ' . sprintf('%02X', $id) . ' in formula');
5604. break;
5605. }
5607. return array(
5608. 'id' => $id,
5609. 'name' => $name,
5610. 'size' => $size,
5611. 'data' => $data,
5612. );
5613. }
5615. /**
5616. * Reads a cell address in BIFF8 e.g. 'A2' or '$A$2'
5617. * section 3.3.4
5618. *
5619. * @param string $cellAddressStructure
5620. * @return string
5621. */
5622. private function _readBIFF8CellAddress($cellAddressStructure)
5623. {
5624. // offset: 0; size: 2; index to row (0... 65535) (or offset (-32768... 32767))
5625. $row = self::_GetInt2d($cellAddressStructure, 0) + 1;
5627. // offset: 2; size: 2; index to column or column offset + relative flags
5629. // bit: 7-0; mask 0x00FF; column index
5630. $column = PHPExcel_Cell:: stringFromColumnIndex(0x00FF & self::_GetInt2d($cellAddressStructure, 2));
5632. // bit: 14; mask 0x4000; (1 = relative column index, 0 = absolute column index)
5633. if (!(0x4000 & self::_GetInt2d($cellAddressStructure, 2))) {
5634. $column = '$' . $column;
5635. }
5636. // bit: 15; mask 0x8000; (1 = relative row index, 0 = absolute row index)
5637. if (!(0x8000 & self::_GetInt2d($cellAddressStructure, 2))) {
5638. $row = '$' . $row;
5639. }
5641. return $column . $row;
5642. }
5644. /**
5645. * Reads a cell address in BIFF8 for shared formulas. Uses positive and negative values for row and column
5646. * to indicate offsets from a base cell
5647. * section 3.3.4
5648. *
5649. * @param string $cellAddressStructure
5650. * @param string $baseCell Base cell, only needed when formula contains tRefN tokens, e.g. with shared formulas
5651. * @return string
5652. */
5653. private function _readBIFF8CellAddressB($cellAddressStructure, $baseCell = 'A1')
5654. {
5655. list($baseCol, $baseRow) = PHPExcel_Cell:: coordinateFromString($baseCell);
5656. $baseCol = PHPExcel_Cell:: columnIndexFromString($baseCol) - 1;
5658. // offset: 0; size: 2; index to row (0... 65535) (or offset (-32768... 32767))
5659. $rowIndex = self::_GetInt2d($cellAddressStructure, 0);
5660. $row = self::_GetInt2d($cellAddressStructure, 0) + 1;
5662. // offset: 2; size: 2; index to column or column offset + relative flags
5664. // bit: 7-0; mask 0x00FF; column index
5665. $colIndex = 0x00FF & self::_GetInt2d($cellAddressStructure, 2);
5667. // bit: 14; mask 0x4000; (1 = relative column index, 0 = absolute column index)
5668. if (!(0x4000 & self::_GetInt2d($cellAddressStructure, 2))) {
5669. $column = PHPExcel_Cell:: stringFromColumnIndex($colIndex);
5670. $column = '$' . $column;
5671. } else {
5672. $colIndex = ($colIndex <= 127) ? $colIndex : $colIndex - 256;
5673. $column = PHPExcel_Cell:: stringFromColumnIndex($baseCol + $colIndex);
5674. }
5676. // bit: 15; mask 0x8000; (1 = relative row index, 0 = absolute row index)
5677. if (!(0x8000 & self::_GetInt2d($cellAddressStructure, 2))) {
5678. $row = '$' . $row;
5679. } else {
5680. $rowIndex = ($rowIndex <= 32767) ? $rowIndex : $rowIndex - 65536;
5681. $row = $baseRow + $rowIndex;
5682. }
5684. return $column . $row;
5685. }
5687. /**
5688. * Reads a cell range address in BIFF5 e.g. 'A2:B6' or 'A1'
5689. * always fixed range
5690. * section 2.5.14
5691. *
5692. * @param string $subData
5693. * @return string
5694. * @throws Exception
5695. */
5696. private function _readBIFF5CellRangeAddressFixed($subData)
5697. {
5698. // offset: 0; size: 2; index to first row
5699. $fr = self::_GetInt2d($subData, 0) + 1;
5701. // offset: 2; size: 2; index to last row
5702. $lr = self::_GetInt2d($subData, 2) + 1;
5704. // offset: 4; size: 1; index to first column
5705. $fc = ord($subData{4});
5707. // offset: 5; size: 1; index to last column
5708. $lc = ord($subData{5});
5710. // check values
5711. if ($fr > $lr || $fc > $lc) {
5712. throw new Exception('Not a cell range address');
5713. }
5715. // column index to letter
5716. $fc = PHPExcel_Cell:: stringFromColumnIndex($fc);
5717. $lc = PHPExcel_Cell:: stringFromColumnIndex($lc);
5719. if ($fr == $lr and $fc == $lc) {
5720. return "$fc$fr";
5721. }
5722. return "$fc$fr:$lc$lr";
5723. }
5725. /**
5726. * Reads a cell range address in BIFF8 e.g. 'A2:B6' or 'A1'
5727. * always fixed range
5728. * section 2.5.14
5729. *
5730. * @param string $subData
5731. * @return string
5732. * @throws Exception
5733. */
5734. private function _readBIFF8CellRangeAddressFixed($subData)
5735. {
5736. // offset: 0; size: 2; index to first row
5737. $fr = self::_GetInt2d($subData, 0) + 1;
5739. // offset: 2; size: 2; index to last row
5740. $lr = self::_GetInt2d($subData, 2) + 1;
5742. // offset: 4; size: 2; index to first column
5743. $fc = self::_GetInt2d($subData, 4);
5745. // offset: 6; size: 2; index to last column
5746. $lc = self::_GetInt2d($subData, 6);
5748. // check values
5749. if ($fr > $lr || $fc > $lc) {
5750. throw new Exception('Not a cell range address');
5751. }
5753. // column index to letter
5754. $fc = PHPExcel_Cell:: stringFromColumnIndex($fc);
5755. $lc = PHPExcel_Cell:: stringFromColumnIndex($lc);
5757. if ($fr == $lr and $fc == $lc) {
5758. return "$fc$fr";
5759. }
5760. return "$fc$fr:$lc$lr";
5761. }
5763. /**
5764. * Reads a cell range address in BIFF8 e.g. 'A2:B6' or '$A$2:$B$6'
5765. * there are flags indicating whether column/row index is relative
5766. * section 3.3.4
5767. *
5768. * @param string $subData
5769. * @return string
5770. */
5771. private function _readBIFF8CellRangeAddress($subData)
5772. {
5773. // todo: if cell range is just a single cell, should this funciton
5774. // not just return e.g. 'A1' and not 'A1:A1' ?
5776. // offset: 0; size: 2; index to first row (0... 65535) (or offset (-32768... 32767))
5777. $fr = self::_GetInt2d($subData, 0) + 1;
5779. // offset: 2; size: 2; index to last row (0... 65535) (or offset (-32768... 32767))
5780. $lr = self::_GetInt2d($subData, 2) + 1;
5782. // offset: 4; size: 2; index to first column or column offset + relative flags
5784. // bit: 7-0; mask 0x00FF; column index
5785. $fc = PHPExcel_Cell:: stringFromColumnIndex(0x00FF & self::_GetInt2d($subData, 4));
5787. // bit: 14; mask 0x4000; (1 = relative column index, 0 = absolute column index)
5788. if (!(0x4000 & self::_GetInt2d($subData, 4))) {
5789. $fc = '$' . $fc;
5790. }
5792. // bit: 15; mask 0x8000; (1 = relative row index, 0 = absolute row index)
5793. if (!(0x8000 & self::_GetInt2d($subData, 4))) {
5794. $fr = '$' . $fr;
5795. }
5797. // offset: 6; size: 2; index to last column or column offset + relative flags
5799. // bit: 7-0; mask 0x00FF; column index
5800. $lc = PHPExcel_Cell:: stringFromColumnIndex(0x00FF & self::_GetInt2d($subData, 6));
5802. // bit: 14; mask 0x4000; (1 = relative column index, 0 = absolute column index)
5803. if (!(0x4000 & self::_GetInt2d($subData, 6))) {
5804. $lc = '$' . $lc;
5805. }
5807. // bit: 15; mask 0x8000; (1 = relative row index, 0 = absolute row index)
5808. if (!(0x8000 & self::_GetInt2d($subData, 6))) {
5809. $lr = '$' . $lr;
5810. }
5812. return "$fc$fr:$lc$lr";
5813. }
5815. /**
5816. * Reads a cell range address in BIFF8 for shared formulas. Uses positive and negative values for row and column
5817. * to indicate offsets from a base cell
5818. * section 3.3.4
5819. *
5820. * @param string $subData
5821. * @param string $baseCell Base cell
5822. * @return string Cell range address
5823. */
5824. private function _readBIFF8CellRangeAddressB($subData, $baseCell = 'A1')
5825. {
5826. list($baseCol, $baseRow) = PHPExcel_Cell:: coordinateFromString($baseCell);
5827. $baseCol = PHPExcel_Cell:: columnIndexFromString($baseCol) - 1;
5829. // TODO: if cell range is just a single cell, should this funciton
5830. // not just return e.g. 'A1' and not 'A1:A1' ?
5832. // offset: 0; size: 2; first row
5833. $frIndex = self::_GetInt2d($subData, 0); // adjust below
5835. // offset: 2; size: 2; relative index to first row (0... 65535) should be treated as offset (-32768... 32767)
5836. $lrIndex = self::_GetInt2d($subData, 2); // adjust below
5838. // offset: 4; size: 2; first column with relative/absolute flags
5840. // bit: 7-0; mask 0x00FF; column index
5841. $fcIndex = 0x00FF & self::_GetInt2d($subData, 4);
5843. // bit: 14; mask 0x4000; (1 = relative column index, 0 = absolute column index)
5844. if (!(0x4000 & self::_GetInt2d($subData, 4))) {
5845. // absolute column index
5846. $fc = PHPExcel_Cell:: stringFromColumnIndex($fcIndex);
5847. $fc = '$' . $fc;
5848. } else {
5849. // column offset
5850. $fcIndex = ($fcIndex <= 127) ? $fcIndex : $fcIndex - 256;
5851. $fc = PHPExcel_Cell:: stringFromColumnIndex($baseCol + $fcIndex);
5852. }
5854. // bit: 15; mask 0x8000; (1 = relative row index, 0 = absolute row index)
5855. if (!(0x8000 & self::_GetInt2d($subData, 4))) {
5856. // absolute row index
5857. $fr = $frIndex + 1;
5858. $fr = '$' . $fr;
5859. } else {
5860. // row offset
5861. $frIndex = ($frIndex <= 32767) ? $frIndex : $frIndex - 65536;
5862. $fr = $baseRow + $frIndex;
5863. }
5865. // offset: 6; size: 2; last column with relative/absolute flags
5867. // bit: 7-0; mask 0x00FF; column index
5868. $lcIndex = 0x00FF & self::_GetInt2d($subData, 6);
5869. $lcIndex = ($lcIndex <= 127) ? $lcIndex : $lcIndex - 256;
5870. $lc = PHPExcel_Cell:: stringFromColumnIndex($baseCol + $lcIndex);
5872. // bit: 14; mask 0x4000; (1 = relative column index, 0 = absolute column index)
5873. if (!(0x4000 & self::_GetInt2d($subData, 6))) {
5874. // absolute column index
5875. $lc = PHPExcel_Cell:: stringFromColumnIndex($lcIndex);
5876. $lc = '$' . $lc;
5877. } else {
5878. // column offset
5879. $lcIndex = ($lcIndex <= 127) ? $lcIndex : $lcIndex - 256;
5880. $lc = PHPExcel_Cell:: stringFromColumnIndex($baseCol + $lcIndex);
5881. }
5883. // bit: 15; mask 0x8000; (1 = relative row index, 0 = absolute row index)
5884. if (!(0x8000 & self::_GetInt2d($subData, 6))) {
5885. // absolute row index
5886. $lr = $lrIndex + 1;
5887. $lr = '$' . $lr;
5888. } else {
5889. // row offset
5890. $lrIndex = ($lrIndex <= 32767) ? $lrIndex : $lrIndex - 65536;
5891. $lr = $baseRow + $lrIndex;
5892. }
5894. return "$fc$fr:$lc$lr";
5895. }
5897. /**
5898. * Read BIFF8 cell range address list
5899. * section 2.5.15
5900. *
5901. * @param string $subData
5902. * @return array
5903. */
5904. private function _readBIFF8CellRangeAddressList($subData)
5905. {
5906. $cellRangeAddresses = array();
5908. // offset: 0; size: 2; number of the following cell range addresses
5909. $nm = self::_GetInt2d($subData, 0);
5911. $offset = 2;
5912. // offset: 2; size: 8 * $nm; list of $nm (fixed) cell range addresses
5913. for ($i = 0; $i < $nm; ++$i) {
5914. $cellRangeAddresses[] = $this->_readBIFF8CellRangeAddressFixed( substr($subData, $offset, 8));
5915. $offset += 8;
5916. }
5918. return array(
5919. 'size' => 2 + 8 * $nm,
5920. 'cellRangeAddresses' => $cellRangeAddresses,
5921. );
5922. }
5924. /**
5925. * Read BIFF5 cell range address list
5926. * section 2.5.15
5927. *
5928. * @param string $subData
5929. * @return array
5930. */
5931. private function _readBIFF5CellRangeAddressList($subData)
5932. {
5933. $cellRangeAddresses = array();
5935. // offset: 0; size: 2; number of the following cell range addresses
5936. $nm = self::_GetInt2d($subData, 0);
5938. $offset = 2;
5939. // offset: 2; size: 6 * $nm; list of $nm (fixed) cell range addresses
5940. for ($i = 0; $i < $nm; ++$i) {
5941. $cellRangeAddresses[] = $this->_readBIFF5CellRangeAddressFixed( substr($subData, $offset, 6));
5942. $offset += 6;
5943. }
5945. return array(
5946. 'size' => 2 + 6 * $nm,
5947. 'cellRangeAddresses' => $cellRangeAddresses,
5948. );
5949. }
5951. /**
5952. * Get a sheet range like Sheet1:Sheet3 from REF index
5953. * Note: If there is only one sheet in the range, one gets e.g Sheet1
5954. * It can also happen that the REF structure uses the -1 (FFFF) code to indicate deleted sheets,
5955. * in which case an exception is thrown
5956. *
5957. * @param int $index
5958. * @return string|false
5959. * @throws Exception
5960. */
5961. private function _readSheetRangeByRefIndex($index)
5962. {
5963. if (isset($this->_ref[$index])) {
5965. $type = $this->_externalBooks[$this->_ref[$index]['externalBookIndex']]['type'];
5967. switch ($type) {
5968. case 'internal':
5969. // check if we have a deleted 3d reference
5970. if ($this->_ref[$index]['firstSheetIndex'] == 0xFFFF or $this->_ref[$index]['lastSheetIndex'] == 0xFFFF) {
5971. throw new Exception('Deleted sheet reference');
5972. }
5974. // we have normal sheet range (collapsed or uncollapsed)
5975. $firstSheetName = $this->_sheets[$this->_ref[$index]['firstSheetIndex']]['name'];
5976. $lastSheetName = $this->_sheets[$this->_ref[$index]['lastSheetIndex']]['name'];
5978. if ($firstSheetName == $lastSheetName) {
5979. // collapsed sheet range
5980. $sheetRange = $firstSheetName;
5981. } else {
5982. $sheetRange = "$firstSheetName:$lastSheetName";
5983. }
5985. // escape the single-quotes
5986. $sheetRange = str_replace("'", "''", $sheetRange);
5988. // if there are special characters, we need to enclose the range in single-quotes
5989. // todo: check if we have identified the whole set of special characters
5990. // it seems that the following characters are not accepted for sheet names
5991. // and we may assume that they are not present: []*/:\?
5992. if ( preg_match("/[ !\"@#£$%&{()}<>=+'|^,;-]/", $sheetRange)) {
5993. $sheetRange = "'$sheetRange'";
5994. }
5996. return $sheetRange;
5997. break;
5999. default:
6000. // TODO: external sheet support
6001. throw new Exception('Excel5 reader only supports internal sheets in fomulas');
6002. break;
6003. }
6004. }
6005. return false;
6006. }
6008. /**
6009. * read BIFF8 constant value array from array data
6010. * returns e.g. array('value' => '{1,2;3,4}', 'size' => 40}
6011. * section 2.5.8
6012. *
6013. * @param string $arrayData
6014. * @return array
6015. */
6016. private static function _readBIFF8ConstantArray($arrayData)
6017. {
6018. // offset: 0; size: 1; number of columns decreased by 1
6019. $nc = ord($arrayData[0]);
6021. // offset: 1; size: 2; number of rows decreased by 1
6022. $nr = self::_GetInt2d($arrayData, 1);
6023. $size = 3; // initialize
6024. $arrayData = substr($arrayData, 3);
6026. // offset: 3; size: var; list of ($nc + 1) * ($nr + 1) constant values
6027. $matrixChunks = array();
6028. for ($r = 1; $r <= $nr + 1; ++$r) {
6029. $items = array();
6030. for ($c = 1; $c <= $nc + 1; ++$c) {
6031. $constant = self::_readBIFF8Constant($arrayData);
6032. $items[] = $constant['value'];
6033. $arrayData = substr($arrayData, $constant['size']);
6034. $size += $constant['size'];
6035. }
6036. $matrixChunks[] = implode(',', $items); // looks like e.g. '1,"hello"'
6037. }
6038. $matrix = '{' . implode(';', $matrixChunks) . '}';
6040. return array(
6041. 'value' => $matrix,
6042. 'size' => $size,
6043. );
6044. }
6046. /**
6047. * read BIFF8 constant value which may be 'Empty Value', 'Number', 'String Value', 'Boolean Value', 'Error Value'
6048. * section 2.5.7
6049. * returns e.g. array('value' => '5', 'size' => 9)
6050. *
6051. * @param string $valueData
6052. * @return array
6053. */
6054. private static function _readBIFF8Constant($valueData)
6055. {
6056. // offset: 0; size: 1; identifier for type of constant
6057. $identifier = ord($valueData[0]);
6059. switch ($identifier) {
6060. case 0x00: // empty constant (what is this?)
6061. $value = '';
6062. $size = 9;
6063. break;
6064. case 0x01: // number
6065. // offset: 1; size: 8; IEEE 754 floating-point value
6066. $value = self::_extractNumber( substr($valueData, 1, 8));
6067. $size = 9;
6068. break;
6069. case 0x02: // string value
6070. // offset: 1; size: var; Unicode string, 16-bit string length
6071. $string = self::_readUnicodeStringLong( substr($valueData, 1));
6072. $value = '"' . $string['value'] . '"';
6073. $size = 1 + $string['size'];
6074. break;
6075. case 0x04: // boolean
6076. // offset: 1; size: 1; 0 = FALSE, 1 = TRUE
6077. if ( ord($valueData[1])) {
6078. $value = 'TRUE';
6079. } else {
6080. $value = 'FALSE';
6081. }
6082. $size = 9;
6083. break;
6084. case 0x10: // error code
6085. // offset: 1; size: 1; error code
6086. $value = self::_mapErrorCode( ord($valueData[1]));
6087. $size = 9;
6088. break;
6089. }
6090. return array(
6091. 'value' => $value,
6092. 'size' => $size,
6093. );
6094. }
6096. /**
6097. * Extract RGB color
6098. * OpenOffice.org's Documentation of the Microsoft Excel File Format, section 2.5.4
6099. *
6100. * @param string $rgb Encoded RGB value (4 bytes)
6101. * @return array
6102. */
6103. private static function _readRGB($rgb)
6104. {
6105. // offset: 0; size 1; Red component
6106. $r = ord($rgb{0});
6108. // offset: 1; size: 1; Green component
6109. $g = ord($rgb{1});
6111. // offset: 2; size: 1; Blue component
6112. $b = ord($rgb{2});
6114. // HEX notation, e.g. 'FF00FC'
6115. $rgb = sprintf('%02X%02X%02X', $r, $g, $b);
6117. return array('rgb' => $rgb);
6118. }
6120. /**
6121. * Read byte string (8-bit string length)
6122. * OpenOffice documentation: 2.5.2
6123. *
6124. * @param string $subData
6125. * @return array
6126. */
6127. private function _readByteStringShort($subData)
6128. {
6129. // offset: 0; size: 1; length of the string (character count)
6130. $ln = ord($subData[0]);
6132. // offset: 1: size: var; character array (8-bit characters)
6133. $value = $this->_decodeCodepage( substr($subData, 1, $ln));
6135. return array(
6136. 'value' => $value,
6137. 'size' => 1 + $ln, // size in bytes of data structure
6138. );
6139. }
6141. /**
6142. * Read byte string (16-bit string length)
6143. * OpenOffice documentation: 2.5.2
6144. *
6145. * @param string $subData
6146. * @return array
6147. */
6148. private function _readByteStringLong($subData)
6149. {
6150. // offset: 0; size: 2; length of the string (character count)
6151. $ln = self::_GetInt2d($subData, 0);
6153. // offset: 2: size: var; character array (8-bit characters)
6154. $value = $this->_decodeCodepage( substr($subData, 2));
6156. //return $string;
6157. return array(
6158. 'value' => $value,
6159. 'size' => 2 + $ln, // size in bytes of data structure
6160. );
6161. }
6163. /**
6164. * Extracts an Excel Unicode short string (8-bit string length)
6165. * OpenOffice documentation: 2.5.3
6166. * function will automatically find out where the Unicode string ends.
6167. *
6168. * @param string $subData
6169. * @return array
6170. */
6171. private static function _readUnicodeStringShort($subData)
6172. {
6173. $value = '';
6175. // offset: 0: size: 1; length of the string (character count)
6176. $characterCount = ord($subData[0]);
6178. $string = self::_readUnicodeString( substr($subData, 1), $characterCount);
6180. // add 1 for the string length
6181. $string['size'] += 1;
6183. return $string;
6184. }
6186. /**
6187. * Extracts an Excel Unicode long string (16-bit string length)
6188. * OpenOffice documentation: 2.5.3
6189. * this function is under construction, needs to support rich text, and Asian phonetic settings
6190. *
6191. * @param string $subData
6192. * @return array
6193. */
6194. private static function _readUnicodeStringLong($subData)
6195. {
6196. $value = '';
6198. // offset: 0: size: 2; length of the string (character count)
6199. $characterCount = self::_GetInt2d($subData, 0);
6201. $string = self::_readUnicodeString( substr($subData, 2), $characterCount);
6203. // add 2 for the string length
6204. $string['size'] += 2;
6206. return $string;
6207. }
6209. /**
6210. * Read Unicode string with no string length field, but with known character count
6211. * this function is under construction, needs to support rich text, and Asian phonetic settings
6212. * OpenOffice.org's Documentation of the Microsoft Excel File Format, section 2.5.3
6213. *
6214. * @param string $subData
6215. * @param int $characterCount
6216. * @return array
6217. */
6218. private static function _readUnicodeString($subData, $characterCount)
6219. {
6220. $value = '';
6222. // offset: 0: size: 1; option flags
6224. // bit: 0; mask: 0x01; character compression (0 = compressed 8-bit, 1 = uncompressed 16-bit)
6225. $isCompressed = !((0x01 & ord($subData[0])) >> 0);
6227. // bit: 2; mask: 0x04; Asian phonetic settings
6228. $hasAsian = (0x04) & ord($subData[0]) >> 2;
6230. // bit: 3; mask: 0x08; Rich-Text settings
6231. $hasRichText = (0x08) & ord($subData[0]) >> 3;
6233. // offset: 1: size: var; character array
6234. // this offset assumes richtext and Asian phonetic settings are off which is generally wrong
6235. // needs to be fixed
6236. $value = self::_encodeUTF16( substr($subData, 1, $isCompressed ? $characterCount : 2 * $characterCount), $isCompressed);
6238. return array(
6239. 'value' => $value,
6240. 'size' => $isCompressed ? 1 + $characterCount : 1 + 2 * $characterCount, // the size in bytes including the option flags
6241. );
6242. }
6244. /**
6245. * Convert UTF-8 string to string surounded by double quotes. Used for explicit string tokens in formulas.
6246. * Example: hello"world --> "hello""world"
6247. *
6248. * @param string $value UTF-8 encoded string
6249. * @return string
6250. */
6251. private static function _UTF8toExcelDoubleQuoted($value)
6252. {
6253. return '"' . str_replace('"', '""', $value) . '"';
6254. }
6256. /**
6257. * Reads first 8 bytes of a string and return IEEE 754 float
6258. *
6259. * @param string $data Binary string that is at least 8 bytes long
6260. * @return float
6261. */
6262. private static function _extractNumber($data)
6263. {
6264. $rknumhigh = self::_GetInt4d($data, 4);
6265. $rknumlow = self::_GetInt4d($data, 0);
6266. $sign = ($rknumhigh & 0x80000000) >> 31;
6267. $exp = (($rknumhigh & 0x7ff00000) >> 20) - 1023;
6268. $mantissa = (0x100000 | ($rknumhigh & 0x000fffff));
6269. $mantissalow1 = ($rknumlow & 0x80000000) >> 31;
6270. $mantissalow2 = ($rknumlow & 0x7fffffff);
6271. $value = $mantissa / pow( 2 , (20 - $exp));
6273. if ($mantissalow1 != 0) {
6274. $value += 1 / pow (2 , (21 - $exp));
6275. }
6277. $value += $mantissalow2 / pow (2 , (52 - $exp));
6278. if ($sign) {
6279. $value = -1 * $value;
6280. }
6282. return $value;
6283. }
6285. private static function _GetIEEE754($rknum)
6286. {
6287. if (($rknum & 0x02) != 0) {
6288. $value = $rknum >> 2;
6289. }
6290. else {
6291. // changes by mmp, info on IEEE754 encoding from
6292. // research.microsoft.com/~hollasch/cgindex/coding/ieeefloat.html
6293. // The RK format calls for using only the most significant 30 bits
6294. // of the 64 bit floating point value. The other 34 bits are assumed
6295. // to be 0 so we use the upper 30 bits of $rknum as follows...
6296. $sign = ($rknum & 0x80000000) >> 31;
6297. $exp = ($rknum & 0x7ff00000) >> 20;
6298. $mantissa = (0x100000 | ($rknum & 0x000ffffc));
6299. $value = $mantissa / pow( 2 , (20- ($exp - 1023)));
6300. if ($sign) {
6301. $value = -1 * $value;
6302. }
6303. //end of changes by mmp
6304. }
6305. if (($rknum & 0x01) != 0) {
6306. $value /= 100;
6307. }
6308. return $value;
6309. }
6311. /**
6312. * Get UTF-8 string from (compressed or uncompressed) UTF-16 string
6313. *
6314. * @param string $string
6315. * @param bool $compressed
6316. * @return string
6317. */
6318. private static function _encodeUTF16($string, $compressed = '')
6319. {
6320. if ($compressed) {
6321. $string = self::_uncompressByteString($string);
6322. }
6324. return PHPExcel_Shared_String:: ConvertEncoding($string, 'UTF-8', 'UTF-16LE');
6325. }
6327. /**
6328. * Convert UTF-16 string in compressed notation to uncompressed form. Only used for BIFF8.
6329. *
6330. * @param string $string
6331. * @return string
6332. */
6333. private static function _uncompressByteString($string)
6334. {
6335. $uncompressedString = '';
6336. $strLen = strlen($string);
6337. for ($i = 0; $i < $strLen; ++$i) {
6338. $uncompressedString .= $string[$i] . "\0";
6339. }
6341. return $uncompressedString;
6342. }
6344. /**
6345. * Convert string to UTF-8. Only used for BIFF5.
6346. *
6347. * @param string $string
6348. * @return string
6349. */
6350. private function _decodeCodepage($string)
6351. {
6352. return PHPExcel_Shared_String:: ConvertEncoding($string, 'UTF-8', $this->_codepage);
6353. }
6355. /**
6356. * Read 16-bit unsigned integer
6357. *
6358. * @param string $data
6359. * @param int $pos
6360. * @return int
6361. */
6362. public static function _GetInt2d($data, $pos)
6363. {
6364. return ord($data[$pos]) | ( ord($data[$pos + 1]) << 8);
6365. }
6367. /**
6368. * Read 32-bit signed integer
6369. *
6370. * @param string $data
6371. * @param int $pos
6372. * @return int
6373. */
6374. public static function _GetInt4d($data, $pos)
6375. {
6376. // FIX: represent numbers correctly on 64-bit system
6377. // http://sourceforge.net/tracker/index.php?func=detail&aid=1487372&group_id=99160&atid=623334
6378. // Hacked by Andreas Rehm 2006 to ensure correct result of the <<24 block on 32 and 64bit systems
6379. $_or_24 = ord($data[$pos + 3]);
6380. if ($_or_24 >= 128) {
6381. // negative number
6382. $_ord_24 = -abs((256 - $_or_24) << 24);
6383. } else {
6384. $_ord_24 = ($_or_24 & 127) << 24;
6385. }
6386. return ord($data[$pos]) | ( ord($data[$pos + 1]) << 8) | ( ord($data[$pos + 2]) << 16) | $_ord_24;
6387. }
6389. /**
6390. * Read color
6391. *
6392. * @param int $color Indexed color
6393. * @param array $palette Color palette
6394. * @return array RGB color value, example: array('rgb' => 'FF0000')
6395. */
6396. private static function _readColor($color,$palette,$version)
6397. {
6398. if ($color <= 0x07 || $color >= 0x40) {
6399. // special built-in color
6400. return self::_mapBuiltInColor($color);
6401. } elseif (isset($palette) && isset($palette[$color - 8])) {
6402. // palette color, color index 0x08 maps to pallete index 0
6403. return $palette[$color - 8];
6404. } else {
6405. // default color table
6406. if ($version == self:: XLS_BIFF8) {
6407. return self::_mapColor($color);
6408. } else {
6409. // BIFF5
6410. return self::_mapColorBIFF5($color);
6411. }
6412. }
6414. return $color;
6415. }
6418. /**
6419. * Map border style
6420. * OpenOffice documentation: 2.5.11
6421. *
6422. * @param int $index
6423. * @return string
6424. */
6425. private static function _mapBorderStyle($index)
6426. {
6427. switch ($index) {
6428. case 0x00: return PHPExcel_Style_Border:: BORDER_NONE;
6429. case 0x01: return PHPExcel_Style_Border:: BORDER_THIN;
6430. case 0x02: return PHPExcel_Style_Border:: BORDER_MEDIUM;
6431. case 0x03: return PHPExcel_Style_Border:: BORDER_DASHED;
6432. case 0x04: return PHPExcel_Style_Border:: BORDER_DOTTED;
6433. case 0x05: return PHPExcel_Style_Border:: BORDER_THICK;
6434. case 0x06: return PHPExcel_Style_Border:: BORDER_DOUBLE;
6435. case 0x07: return PHPExcel_Style_Border:: BORDER_HAIR;
6436. case 0x08: return PHPExcel_Style_Border:: BORDER_MEDIUMDASHED;
6437. case 0x09: return PHPExcel_Style_Border:: BORDER_DASHDOT;
6438. case 0x0A: return PHPExcel_Style_Border:: BORDER_MEDIUMDASHDOT;
6439. case 0x0B: return PHPExcel_Style_Border:: BORDER_DASHDOTDOT;
6440. case 0x0C: return PHPExcel_Style_Border:: BORDER_MEDIUMDASHDOTDOT;
6441. case 0x0D: return PHPExcel_Style_Border:: BORDER_SLANTDASHDOT;
6442. default: return PHPExcel_Style_Border:: BORDER_NONE;
6443. }
6444. }
6446. /**
6447. * Get fill pattern from index
6448. * OpenOffice documentation: 2.5.12
6449. *
6450. * @param int $index
6451. * @return string
6452. */
6453. private static function _mapFillPattern($index)
6454. {
6455. switch ($index) {
6456. case 0x00: return PHPExcel_Style_Fill:: FILL_NONE;
6457. case 0x01: return PHPExcel_Style_Fill:: FILL_SOLID;
6458. case 0x02: return PHPExcel_Style_Fill:: FILL_PATTERN_MEDIUMGRAY;
6459. case 0x03: return PHPExcel_Style_Fill:: FILL_PATTERN_DARKGRAY;
6460. case 0x04: return PHPExcel_Style_Fill:: FILL_PATTERN_LIGHTGRAY;
6461. case 0x05: return PHPExcel_Style_Fill:: FILL_PATTERN_DARKHORIZONTAL;
6462. case 0x06: return PHPExcel_Style_Fill:: FILL_PATTERN_DARKVERTICAL;
6463. case 0x07: return PHPExcel_Style_Fill:: FILL_PATTERN_DARKDOWN;
6464. case 0x08: return PHPExcel_Style_Fill:: FILL_PATTERN_DARKUP;
6465. case 0x09: return PHPExcel_Style_Fill:: FILL_PATTERN_DARKGRID;
6466. case 0x0A: return PHPExcel_Style_Fill:: FILL_PATTERN_DARKTRELLIS;
6467. case 0x0B: return PHPExcel_Style_Fill:: FILL_PATTERN_LIGHTHORIZONTAL;
6468. case 0x0C: return PHPExcel_Style_Fill:: FILL_PATTERN_LIGHTVERTICAL;
6469. case 0x0D: return PHPExcel_Style_Fill:: FILL_PATTERN_LIGHTDOWN;
6470. case 0x0E: return PHPExcel_Style_Fill:: FILL_PATTERN_LIGHTUP;
6471. case 0x0F: return PHPExcel_Style_Fill:: FILL_PATTERN_LIGHTGRID;
6472. case 0x10: return PHPExcel_Style_Fill:: FILL_PATTERN_LIGHTTRELLIS;
6473. case 0x11: return PHPExcel_Style_Fill:: FILL_PATTERN_GRAY125;
6474. case 0x12: return PHPExcel_Style_Fill:: FILL_PATTERN_GRAY0625;
6475. default: return PHPExcel_Style_Fill:: FILL_NONE;
6476. }
6477. }
6479. /**
6480. * Map error code, e.g. '#N/A'
6481. *
6482. * @param int $subData
6483. * @return string
6484. */
6485. private static function _mapErrorCode($subData)
6486. {
6487. switch ($subData) {
6488. case 0x00: return '#NULL!'; break;
6489. case 0x07: return '#DIV/0!'; break;
6490. case 0x0F: return '#VALUE!'; break;
6491. case 0x17: return '#REF!'; break;
6492. case 0x1D: return '#NAME?'; break;
6493. case 0x24: return '#NUM!'; break;
6494. case 0x2A: return '#N/A'; break;
6495. default: return false;
6496. }
6497. }
6499. /**
6500. * Map built-in color to RGB value
6501. *
6502. * @param int $color Indexed color
6503. * @return array
6504. */
6505. private static function _mapBuiltInColor($color)
6506. {
6507. switch ($color) {
6508. case 0x00: return array('rgb' => '000000');
6509. case 0x01: return array('rgb' => 'FFFFFF');
6510. case 0x02: return array('rgb' => 'FF0000');
6511. case 0x03: return array('rgb' => '00FF00');
6512. case 0x04: return array('rgb' => '0000FF');
6513. case 0x05: return array('rgb' => 'FFFF00');
6514. case 0x06: return array('rgb' => 'FF00FF');
6515. case 0x07: return array('rgb' => '00FFFF');
6516. case 0x40: return array('rgb' => '000000'); // system window text color
6517. case 0x41: return array('rgb' => 'FFFFFF'); // system window background color
6518. default: return array('rgb' => '000000');
6519. }
6520. }
6522. /**
6523. * Map color array from BIFF5 built-in color index
6524. *
6525. * @param int $subData
6526. * @return array
6527. */
6528. private static function _mapColorBIFF5($subData)
6529. {
6530. switch ($subData) {
6531. case 0x08: return array('rgb' => '000000');
6532. case 0x09: return array('rgb' => 'FFFFFF');
6533. case 0x0A: return array('rgb' => 'FF0000');
6534. case 0x0B: return array('rgb' => '00FF00');
6535. case 0x0C: return array('rgb' => '0000FF');
6536. case 0x0D: return array('rgb' => 'FFFF00');
6537. case 0x0E: return array('rgb' => 'FF00FF');
6538. case 0x0F: return array('rgb' => '00FFFF');
6539. case 0x10: return array('rgb' => '800000');
6540. case 0x11: return array('rgb' => '008000');
6541. case 0x12: return array('rgb' => '000080');
6542. case 0x13: return array('rgb' => '808000');
6543. case 0x14: return array('rgb' => '800080');
6544. case 0x15: return array('rgb' => '008080');
6545. case 0x16: return array('rgb' => 'C0C0C0');
6546. case 0x17: return array('rgb' => '808080');
6547. case 0x18: return array('rgb' => '8080FF');
6548. case 0x19: return array('rgb' => '802060');
6549. case 0x1A: return array('rgb' => 'FFFFC0');
6550. case 0x1B: return array('rgb' => 'A0E0F0');
6551. case 0x1C: return array('rgb' => '600080');
6552. case 0x1D: return array('rgb' => 'FF8080');
6553. case 0x1E: return array('rgb' => '0080C0');
6554. case 0x1F: return array('rgb' => 'C0C0FF');
6555. case 0x20: return array('rgb' => '000080');
6556. case 0x21: return array('rgb' => 'FF00FF');
6557. case 0x22: return array('rgb' => 'FFFF00');
6558. case 0x23: return array('rgb' => '00FFFF');
6559. case 0x24: return array('rgb' => '800080');
6560. case 0x25: return array('rgb' => '800000');
6561. case 0x26: return array('rgb' => '008080');
6562. case 0x27: return array('rgb' => '0000FF');
6563. case 0x28: return array('rgb' => '00CFFF');
6564. case 0x29: return array('rgb' => '69FFFF');
6565. case 0x2A: return array('rgb' => 'E0FFE0');
6566. case 0x2B: return array('rgb' => 'FFFF80');
6567. case 0x2C: return array('rgb' => 'A6CAF0');
6568. case 0x2D: return array('rgb' => 'DD9CB3');
6569. case 0x2E: return array('rgb' => 'B38FEE');
6570. case 0x2F: return array('rgb' => 'E3E3E3');
6571. case 0x30: return array('rgb' => '2A6FF9');
6572. case 0x31: return array('rgb' => '3FB8CD');
6573. case 0x32: return array('rgb' => '488436');
6574. case 0x33: return array('rgb' => '958C41');
6575. case 0x34: return array('rgb' => '8E5E42');
6576. case 0x35: return array('rgb' => 'A0627A');
6577. case 0x36: return array('rgb' => '624FAC');
6578. case 0x37: return array('rgb' => '969696');
6579. case 0x38: return array('rgb' => '1D2FBE');
6580. case 0x39: return array('rgb' => '286676');
6581. case 0x3A: return array('rgb' => '004500');
6582. case 0x3B: return array('rgb' => '453E01');
6583. case 0x3C: return array('rgb' => '6A2813');
6584. case 0x3D: return array('rgb' => '85396A');
6585. case 0x3E: return array('rgb' => '4A3285');
6586. case 0x3F: return array('rgb' => '424242');
6587. default: return array('rgb' => '000000');
6588. }
6589. }
6591. /**
6592. * Map color array from BIFF8 built-in color index
6593. *
6594. * @param int $subData
6595. * @return array
6596. */
6597. private static function _mapColor($subData)
6598. {
6599. switch ($subData) {
6600. case 0x08: return array('rgb' => '000000');
6601. case 0x09: return array('rgb' => 'FFFFFF');
6602. case 0x0A: return array('rgb' => 'FF0000');
6603. case 0x0B: return array('rgb' => '00FF00');
6604. case 0x0C: return array('rgb' => '0000FF');
6605. case 0x0D: return array('rgb' => 'FFFF00');
6606. case 0x0E: return array('rgb' => 'FF00FF');
6607. case 0x0F: return array('rgb' => '00FFFF');
6608. case 0x10: return array('rgb' => '800000');
6609. case 0x11: return array('rgb' => '008000');
6610. case 0x12: return array('rgb' => '000080');
6611. case 0x13: return array('rgb' => '808000');
6612. case 0x14: return array('rgb' => '800080');
6613. case 0x15: return array('rgb' => '008080');
6614. case 0x16: return array('rgb' => 'C0C0C0');
6615. case 0x17: return array('rgb' => '808080');
6616. case 0x18: return array('rgb' => '9999FF');
6617. case 0x19: return array('rgb' => '993366');
6618. case 0x1A: return array('rgb' => 'FFFFCC');
6619. case 0x1B: return array('rgb' => 'CCFFFF');
6620. case 0x1C: return array('rgb' => '660066');
6621. case 0x1D: return array('rgb' => 'FF8080');
6622. case 0x1E: return array('rgb' => '0066CC');
6623. case 0x1F: return array('rgb' => 'CCCCFF');
6624. case 0x20: return array('rgb' => '000080');
6625. case 0x21: return array('rgb' => 'FF00FF');
6626. case 0x22: return array('rgb' => 'FFFF00');
6627. case 0x23: return array('rgb' => '00FFFF');
6628. case 0x24: return array('rgb' => '800080');
6629. case 0x25: return array('rgb' => '800000');
6630. case 0x26: return array('rgb' => '008080');
6631. case 0x27: return array('rgb' => '0000FF');
6632. case 0x28: return array('rgb' => '00CCFF');
6633. case 0x29: return array('rgb' => 'CCFFFF');
6634. case 0x2A: return array('rgb' => 'CCFFCC');
6635. case 0x2B: return array('rgb' => 'FFFF99');
6636. case 0x2C: return array('rgb' => '99CCFF');
6637. case 0x2D: return array('rgb' => 'FF99CC');
6638. case 0x2E: return array('rgb' => 'CC99FF');
6639. case 0x2F: return array('rgb' => 'FFCC99');
6640. case 0x30: return array('rgb' => '3366FF');
6641. case 0x31: return array('rgb' => '33CCCC');
6642. case 0x32: return array('rgb' => '99CC00');
6643. case 0x33: return array('rgb' => 'FFCC00');
6644. case 0x34: return array('rgb' => 'FF9900');
6645. case 0x35: return array('rgb' => 'FF6600');
6646. case 0x36: return array('rgb' => '666699');
6647. case 0x37: return array('rgb' => '969696');
6648. case 0x38: return array('rgb' => '003366');
6649. case 0x39: return array('rgb' => '339966');
6650. case 0x3A: return array('rgb' => '003300');
6651. case 0x3B: return array('rgb' => '333300');
6652. case 0x3C: return array('rgb' => '993300');
6653. case 0x3D: return array('rgb' => '993366');
6654. case 0x3E: return array('rgb' => '333399');
6655. case 0x3F: return array('rgb' => '333333');
6656. default: return array('rgb' => '000000');
6657. }
6658. }
6660. private function _parseRichText($is = '') {
6661. $value = new PHPExcel_RichText();
6663. $value->createText($is);
6665. return $value;
6666. }
6668. }

Documentation generated on Sun, 27 Feb 2011 16:31:11 -0800 by phpDocumentor 1.4.3