PageRenderTime 62ms CodeModel.GetById 17ms RepoModel.GetById 1ms app.codeStats 1ms

/add-ons/PHPExcel/PHPExcel/Reader/Excel5.php

https://github.com/jcplat/console-seolan
PHP | 5416 lines | 3082 code | 806 blank | 1528 comment | 378 complexity | b5e81007ba75bc114280ff950053af5d MD5 | raw file
Possible License(s): LGPL-2.0, LGPL-2.1, GPL-3.0, Apache-2.0, BSD-3-Clause

Large files files are truncated, but you can click here to view the full file

  1. <?php
  2. /**
  3. * PHPExcel
  4. *
  5. * Copyright (c) 2006 - 2009 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 - 2009 PHPExcel (http://www.codeplex.com/PHPExcel)
  24. * @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL
  25. * @version 1.7.1, 2009-11-02
  26. */
  27. // Original file header of ParseXL (used as the base for this class):
  28. // --------------------------------------------------------------------------------
  29. // Adapted from Excel_Spreadsheet_Reader developed by users bizon153,
  30. // trex005, and mmp11 (SourceForge.net)
  31. // http://sourceforge.net/projects/phpexcelreader/
  32. // Primary changes made by canyoncasa (dvc) for ParseXL 1.00 ...
  33. // Modelled moreso after Perl Excel Parse/Write modules
  34. // Added Parse_Excel_Spreadsheet object
  35. // Reads a whole worksheet or tab as row,column array or as
  36. // associated hash of indexed rows and named column fields
  37. // Added variables for worksheet (tab) indexes and names
  38. // Added an object call for loading individual woorksheets
  39. // Changed default indexing defaults to 0 based arrays
  40. // Fixed date/time and percent formats
  41. // Includes patches found at SourceForge...
  42. // unicode patch by nobody
  43. // unpack("d") machine depedency patch by matchy
  44. // boundsheet utf16 patch by bjaenichen
  45. // Renamed functions for shorter names
  46. // General code cleanup and rigor, including <80 column width
  47. // Included a testcase Excel file and PHP example calls
  48. // Code works for PHP 5.x
  49. // Primary changes made by canyoncasa (dvc) for ParseXL 1.10 ...
  50. // http://sourceforge.net/tracker/index.php?func=detail&aid=1466964&group_id=99160&atid=623334
  51. // Decoding of formula conditions, results, and tokens.
  52. // Support for user-defined named cells added as an array "namedcells"
  53. // Patch code for user-defined named cells supports single cells only.
  54. // NOTE: this patch only works for BIFF8 as BIFF5-7 use a different
  55. // external sheet reference structure
  56. /** PHPExcel root directory */
  57. if (!defined('PHPEXCEL_ROOT')) {
  58. /**
  59. * @ignore
  60. */
  61. define('PHPEXCEL_ROOT', dirname(__FILE__) . '/../../');
  62. }
  63. /** PHPExcel */
  64. require_once PHPEXCEL_ROOT . 'PHPExcel.php';
  65. /** PHPExcel_Reader_IReader */
  66. require_once PHPEXCEL_ROOT . 'PHPExcel/Reader/IReader.php';
  67. /** PHPExcel_Reader_Excel5_Escher */
  68. require_once PHPEXCEL_ROOT . 'PHPExcel/Reader/Excel5/Escher.php';
  69. /** PHPExcel_Shared_Date */
  70. require_once PHPEXCEL_ROOT . 'PHPExcel/Shared/Date.php';
  71. /** PHPExcel_Shared_Excel5 */
  72. require_once PHPEXCEL_ROOT . 'PHPExcel/Shared/Excel5.php';
  73. /** PHPExcel_Shared_Escher */
  74. require_once PHPEXCEL_ROOT . 'PHPExcel/Shared/Escher.php';
  75. /** PHPExcel_Shared_Escher_DggContainer_BstoreContainer_BSE */
  76. require_once PHPEXCEL_ROOT . 'PHPExcel/Shared/Escher/DggContainer/BstoreContainer/BSE.php';
  77. /** PHPExcel_Shared_OLERead */
  78. require_once PHPEXCEL_ROOT . 'PHPExcel/Shared/OLERead.php';
  79. /** PHPExcel_Shared_String */
  80. require_once PHPEXCEL_ROOT . 'PHPExcel/Shared/String.php';
  81. /** PHPExcel_Cell */
  82. require_once PHPEXCEL_ROOT . 'PHPExcel/Cell.php';
  83. /** PHPExcel_NamedRange */
  84. require_once PHPEXCEL_ROOT . 'PHPExcel/NamedRange.php';
  85. /** PHPExcel_Reader_IReadFilter */
  86. require_once PHPEXCEL_ROOT . 'PHPExcel/Reader/IReadFilter.php';
  87. /** PHPExcel_Reader_DefaultReadFilter */
  88. require_once PHPEXCEL_ROOT . 'PHPExcel/Reader/DefaultReadFilter.php';
  89. /** PHPExcel_Worksheet_MemoryDrawing */
  90. require_once PHPEXCEL_ROOT . 'PHPExcel/Worksheet/MemoryDrawing.php';
  91. /**
  92. * PHPExcel_Reader_Excel5
  93. *
  94. * This class uses {@link http://sourceforge.net/projects/phpexcelreader/parseXL}
  95. *
  96. * @category PHPExcel
  97. * @package PHPExcel_Reader_Excel5
  98. * @copyright Copyright (c) 2006 - 2009 PHPExcel (http://www.codeplex.com/PHPExcel)
  99. */
  100. class PHPExcel_Reader_Excel5 implements PHPExcel_Reader_IReader
  101. {
  102. // ParseXL definitions
  103. const XLS_BIFF8 = 0x0600;
  104. const XLS_BIFF7 = 0x0500;
  105. const XLS_WorkbookGlobals = 0x0005;
  106. const XLS_Worksheet = 0x0010;
  107. // record identifiers
  108. const XLS_Type_FORMULA = 0x0006;
  109. const XLS_Type_EOF = 0x000a;
  110. const XLS_Type_PROTECT = 0x0012;
  111. const XLS_Type_PASSWORD = 0x0013;
  112. const XLS_Type_HEADER = 0x0014;
  113. const XLS_Type_FOOTER = 0x0015;
  114. const XLS_Type_EXTERNSHEET = 0x0017;
  115. const XLS_Type_DEFINEDNAME = 0x0018;
  116. const XLS_Type_VERTICALPAGEBREAKS = 0x001a;
  117. const XLS_Type_HORIZONTALPAGEBREAKS = 0x001b;
  118. const XLS_Type_NOTE = 0x001c;
  119. const XLS_Type_DATEMODE = 0x0022;
  120. const XLS_Type_LEFTMARGIN = 0x0026;
  121. const XLS_Type_RIGHTMARGIN = 0x0027;
  122. const XLS_Type_TOPMARGIN = 0x0028;
  123. const XLS_Type_BOTTOMMARGIN = 0x0029;
  124. const XLS_Type_PRINTGRIDLINES = 0x002b;
  125. const XLS_Type_FILEPASS = 0x002f;
  126. const XLS_Type_FONT = 0x0031;
  127. const XLS_Type_CONTINUE = 0x003c;
  128. const XLS_Type_PANE = 0x0041;
  129. const XLS_Type_CODEPAGE = 0x0042;
  130. const XLS_Type_DEFCOLWIDTH = 0x0055;
  131. const XLS_Type_OBJ = 0x005d;
  132. const XLS_Type_COLINFO = 0x007d;
  133. const XLS_Type_IMDATA = 0x007f;
  134. const XLS_Type_SHEETPR = 0x0081;
  135. const XLS_Type_HCENTER = 0x0083;
  136. const XLS_Type_VCENTER = 0x0084;
  137. const XLS_Type_SHEET = 0x0085;
  138. const XLS_Type_PALETTE = 0x0092;
  139. const XLS_Type_SCL = 0x00a0;
  140. const XLS_Type_PAGESETUP = 0x00a1;
  141. const XLS_Type_MULRK = 0x00bd;
  142. const XLS_Type_MULBLANK = 0x00be;
  143. const XLS_Type_DBCELL = 0x00d7;
  144. const XLS_Type_XF = 0x00e0;
  145. const XLS_Type_MERGEDCELLS = 0x00e5;
  146. const XLS_Type_MSODRAWINGGROUP = 0x00eb;
  147. const XLS_Type_MSODRAWING = 0x00ec;
  148. const XLS_Type_SST = 0x00fc;
  149. const XLS_Type_LABELSST = 0x00fd;
  150. const XLS_Type_EXTSST = 0x00ff;
  151. const XLS_Type_EXTERNALBOOK = 0x01ae;
  152. const XLS_Type_TXO = 0x01b6;
  153. const XLS_Type_HYPERLINK = 0x01b8;
  154. const XLS_Type_DIMENSION = 0x0200;
  155. const XLS_Type_BLANK = 0x0201;
  156. const XLS_Type_NUMBER = 0x0203;
  157. const XLS_Type_LABEL = 0x0204;
  158. const XLS_Type_BOOLERR = 0x0205;
  159. const XLS_Type_STRING = 0x0207;
  160. const XLS_Type_ROW = 0x0208;
  161. const XLS_Type_INDEX = 0x020b;
  162. const XLS_Type_ARRAY = 0x0221;
  163. const XLS_Type_DEFAULTROWHEIGHT = 0x0225;
  164. const XLS_Type_WINDOW2 = 0x023e;
  165. const XLS_Type_RK = 0x027e;
  166. const XLS_Type_STYLE = 0x0293;
  167. const XLS_Type_FORMAT = 0x041e;
  168. const XLS_Type_SHAREDFMLA = 0x04bc;
  169. const XLS_Type_BOF = 0x0809;
  170. const XLS_Type_RANGEPROTECTION = 0x0868;
  171. const XLS_Type_SHEETLAYOUT = 0x0862;
  172. const XLS_Type_UNKNOWN = 0xffff;
  173. /**
  174. * Read data only?
  175. *
  176. * @var boolean
  177. */
  178. private $_readDataOnly = false;
  179. /**
  180. * Restict which sheets should be loaded?
  181. *
  182. * @var array
  183. */
  184. private $_loadSheetsOnly = null;
  185. /**
  186. * PHPExcel_Reader_IReadFilter instance
  187. *
  188. * @var PHPExcel_Reader_IReadFilter
  189. */
  190. private $_readFilter = null;
  191. /**
  192. * OLE reader
  193. *
  194. * @var PHPExcel_Shared_OLERead
  195. */
  196. private $_ole;
  197. /**
  198. * Stream data that is read. Includes workbook globals substream as well as sheet substreams
  199. *
  200. * @var string
  201. */
  202. private $_data;
  203. /**
  204. * Size in bytes of $this->_data
  205. *
  206. * @var int
  207. */
  208. private $_dataSize;
  209. /**
  210. * Current position in stream
  211. *
  212. * @var integer
  213. */
  214. private $_pos;
  215. /**
  216. * Workbook to be returned by the reader.
  217. *
  218. * @var PHPExcel
  219. */
  220. private $_phpExcel;
  221. /**
  222. * Worksheet that is currently being built by the reader.
  223. *
  224. * @var PHPExcel_Worksheet
  225. */
  226. private $_phpSheet;
  227. /**
  228. * BIFF version
  229. *
  230. * @var int
  231. */
  232. private $_version;
  233. /**
  234. * Codepage set in the Excel file being read. Only important for BIFF5 (Excel 5.0 - Excel 95)
  235. * For BIFF8 (Excel 97 - Excel 2003) this will always have the value 'UTF-16LE'
  236. *
  237. * @var string
  238. */
  239. private $_codepage;
  240. /**
  241. * Shared formats
  242. *
  243. * @var array
  244. */
  245. private $_formats;
  246. /**
  247. * Shared fonts
  248. *
  249. * @var array
  250. */
  251. private $_objFonts;
  252. /**
  253. * Color palette
  254. *
  255. * @var array
  256. */
  257. private $_palette;
  258. /**
  259. * Worksheets
  260. *
  261. * @var array
  262. */
  263. private $_sheets;
  264. /**
  265. * External books
  266. *
  267. * @var array
  268. */
  269. private $_externalBooks;
  270. /**
  271. * REF structures. Only applies to BIFF8.
  272. *
  273. * @var array
  274. */
  275. private $_ref;
  276. /**
  277. * Defined names
  278. *
  279. * @var array
  280. */
  281. private $_definedname;
  282. /**
  283. * Shared strings. Only applies to BIFF8.
  284. *
  285. * @var array
  286. */
  287. private $_sst;
  288. /**
  289. * Panes are frozen? (in sheet currently being read). See WINDOW2 record.
  290. *
  291. * @var boolean
  292. */
  293. private $_frozen;
  294. /**
  295. * Fit printout to number of pages? (in sheet currently being read). See SHEETPR record.
  296. *
  297. * @var boolean
  298. */
  299. private $_isFitToPages;
  300. /**
  301. * Objects. One OBJ record contributes with one entry.
  302. *
  303. * @var array
  304. */
  305. private $_objs;
  306. /**
  307. * The combined MSODRAWINGGROUP data
  308. *
  309. * @var string
  310. */
  311. private $_drawingGroupData;
  312. /**
  313. * The combined MSODRAWING data (per sheet)
  314. *
  315. * @var string
  316. */
  317. private $_drawingData;
  318. /**
  319. * Keep track of XF index
  320. *
  321. * @var int
  322. */
  323. private $_xfIndex;
  324. /**
  325. * Mapping of XF index (that is a cell XF) to final index in cellXf collection
  326. *
  327. * @var array
  328. */
  329. private $_mapCellXfIndex;
  330. /**
  331. * Mapping of XF index (that is a style XF) to final index in cellStyleXf collection
  332. *
  333. * @var array
  334. */
  335. private $_mapCellStyleXfIndex;
  336. /**
  337. * The shared formulas in a sheet. One SHAREDFMLA record contributes with one value.
  338. *
  339. * @var array
  340. */
  341. private $_sharedFormulas;
  342. /**
  343. * The shared formula parts in a sheet. One FORMULA record contributes with one value if it
  344. * refers to a shared formula.
  345. *
  346. * @var array
  347. */
  348. private $_sharedFormulaParts;
  349. /**
  350. * Read data only?
  351. *
  352. * @return boolean
  353. */
  354. public function getReadDataOnly()
  355. {
  356. return $this->_readDataOnly;
  357. }
  358. /**
  359. * Set read data only
  360. *
  361. * @param boolean $pValue
  362. * @return PHPExcel_Reader_Excel5
  363. */
  364. public function setReadDataOnly($pValue = false)
  365. {
  366. $this->_readDataOnly = $pValue;
  367. return $this;
  368. }
  369. /**
  370. * Get which sheets to load
  371. *
  372. * @return mixed
  373. */
  374. public function getLoadSheetsOnly()
  375. {
  376. return $this->_loadSheetsOnly;
  377. }
  378. /**
  379. * Set which sheets to load
  380. *
  381. * @param mixed $value
  382. * @return PHPExcel_Reader_Excel5
  383. */
  384. public function setLoadSheetsOnly($value = null)
  385. {
  386. $this->_loadSheetsOnly = is_array($value) ?
  387. $value : array($value);
  388. return $this;
  389. }
  390. /**
  391. * Set all sheets to load
  392. *
  393. * @return PHPExcel_Reader_Excel5
  394. */
  395. public function setLoadAllSheets()
  396. {
  397. $this->_loadSheetsOnly = null;
  398. return $this;
  399. }
  400. /**
  401. * Read filter
  402. *
  403. * @return PHPExcel_Reader_IReadFilter
  404. */
  405. public function getReadFilter() {
  406. return $this->_readFilter;
  407. }
  408. /**
  409. * Set read filter
  410. *
  411. * @param PHPExcel_Reader_IReadFilter $pValue
  412. * @return PHPExcel_Reader_Excel5
  413. */
  414. public function setReadFilter(PHPExcel_Reader_IReadFilter $pValue) {
  415. $this->_readFilter = $pValue;
  416. return $this;
  417. }
  418. /**
  419. * Create a new PHPExcel_Reader_Excel5 instance
  420. */
  421. public function __construct() {
  422. $this->_readFilter = new PHPExcel_Reader_DefaultReadFilter();
  423. }
  424. /**
  425. * Can the current PHPExcel_Reader_IReader read the file?
  426. *
  427. * @param string $pFileName
  428. * @return boolean
  429. */
  430. public function canRead($pFilename)
  431. {
  432. // Check if file exists
  433. if (!file_exists($pFilename)) {
  434. throw new Exception("Could not open " . $pFilename . " for reading! File does not exist.");
  435. }
  436. try {
  437. // Use ParseXL for the hard work.
  438. $this->_ole = new PHPExcel_Shared_OLERead();
  439. // get excel data
  440. $res = $this->_ole->read($pFilename);
  441. return true;
  442. } catch (Exception $e) {
  443. return false;
  444. }
  445. }
  446. /**
  447. * Loads PHPExcel from file
  448. *
  449. * @param string $pFilename
  450. * @throws Exception
  451. */
  452. public function load($pFilename)
  453. {
  454. // Initialisations
  455. $this->_phpExcel = new PHPExcel;
  456. $this->_phpExcel->removeSheetByIndex(0); // remove 1st sheet
  457. if (!$this->_readDataOnly) {
  458. $this->_phpExcel->removeCellStyleXfByIndex(0); // remove the default style
  459. $this->_phpExcel->removeCellXfByIndex(0); // remove the default style
  460. }
  461. // Use ParseXL for the hard work.
  462. $this->_ole = new PHPExcel_Shared_OLERead();
  463. // get excel data
  464. $res = $this->_ole->read($pFilename);
  465. $this->_data = $this->_ole->getWorkBook();
  466. // total byte size of Excel data (workbook global substream + sheet substreams)
  467. $this->_dataSize = strlen($this->_data);
  468. // initialize
  469. $this->_pos = 0;
  470. $this->_codepage = 'CP1252';
  471. $this->_formats = array();
  472. $this->_objFonts = array();
  473. $this->_palette = array();
  474. $this->_sheets = array();
  475. $this->_externalBooks = array();
  476. $this->_ref = array();
  477. $this->_definedname = array();
  478. $this->_sst = array();
  479. $this->_drawingGroupData = '';
  480. $this->_xfIndex = '';
  481. $this->_mapCellXfIndex = array();
  482. $this->_mapCellStyleXfIndex = array();
  483. // Parse Workbook Global Substream
  484. while ($this->_pos < $this->_dataSize) {
  485. $code = $this->_GetInt2d($this->_data, $this->_pos);
  486. switch ($code) {
  487. case self::XLS_Type_BOF:
  488. $pos = $this->_pos;
  489. $length = $this->_GetInt2d($this->_data, $pos + 2);
  490. $recordData = substr($this->_data, $pos + 4, $length);
  491. // offset: 0; size: 2; BIFF version
  492. $this->_version = $this->_GetInt2d($this->_data, $pos + 4);
  493. if (($this->_version != self::XLS_BIFF8) && ($this->_version != self::XLS_BIFF7)) {
  494. return false;
  495. }
  496. // offset: 2; size: 2; type of stream
  497. $substreamType = $this->_GetInt2d($this->_data, $pos + 6);
  498. if ($substreamType != self::XLS_WorkbookGlobals) {
  499. return false;
  500. }
  501. $this->_pos += 4 + $length;
  502. break;
  503. case self::XLS_Type_FILEPASS: $this->_readFilepass(); break;
  504. case self::XLS_Type_CODEPAGE: $this->_readCodepage(); break;
  505. case self::XLS_Type_DATEMODE: $this->_readDateMode(); break;
  506. case self::XLS_Type_FONT: $this->_readFont(); break;
  507. case self::XLS_Type_FORMAT: $this->_readFormat(); break;
  508. case self::XLS_Type_XF: $this->_readXf(); break;
  509. case self::XLS_Type_STYLE: $this->_readStyle(); break;
  510. case self::XLS_Type_PALETTE: $this->_readPalette(); break;
  511. case self::XLS_Type_SHEET: $this->_readSheet(); break;
  512. case self::XLS_Type_EXTERNALBOOK: $this->_readExternalBook(); break;
  513. case self::XLS_Type_EXTERNSHEET: $this->_readExternSheet(); break;
  514. case self::XLS_Type_DEFINEDNAME: $this->_readDefinedName(); break;
  515. case self::XLS_Type_MSODRAWINGGROUP: $this->_readMsoDrawingGroup(); break;
  516. case self::XLS_Type_SST: $this->_readSst(); break;
  517. case self::XLS_Type_EOF: $this->_readDefault(); break 2;
  518. default: $this->_readDefault(); break;
  519. }
  520. }
  521. // Resolve indexed colors for font, fill, and border colors
  522. // Cannot be resolved already in XF record, because PALETTE record comes afterwards
  523. if (!$this->_readDataOnly) {
  524. foreach ($this->_objFonts as $objFont) {
  525. $color = $this->_readColor($objFont->colorIndex);
  526. $objFont->getColor()->setRGB($color['rgb']);
  527. }
  528. foreach ($this->_phpExcel->getCellXfCollection() as $objStyle) {
  529. // fill start and end color
  530. $startColor = $this->_readColor($objStyle->getFill()->startcolorIndex);
  531. $objStyle->getFill()->getStartColor()->setRGB($startColor['rgb']);
  532. $endColor = $this->_readColor($objStyle->getFill()->endcolorIndex);
  533. $objStyle->getFill()->getEndColor()->setRGB($endColor['rgb']);
  534. // border colors
  535. $borderTopColor = $this->_readColor($objStyle->getBorders()->getTop()->colorIndex);
  536. $objStyle->getBorders()->getTop()->getColor()->setRGB($borderTopColor['rgb']);
  537. $borderRightColor = $this->_readColor($objStyle->getBorders()->getRight()->colorIndex);
  538. $objStyle->getBorders()->getRight()->getColor()->setRGB($borderRightColor['rgb']);
  539. $borderBottomColor = $this->_readColor($objStyle->getBorders()->getBottom()->colorIndex);
  540. $objStyle->getBorders()->getBottom()->getColor()->setRGB($borderBottomColor['rgb']);
  541. $borderLeftColor = $this->_readColor($objStyle->getBorders()->getLeft()->colorIndex);
  542. $objStyle->getBorders()->getLeft()->getColor()->setRGB($borderLeftColor['rgb']);
  543. }
  544. }
  545. // treat MSODRAWINGGROUP records, workbook-level Escher
  546. if (!$this->_readDataOnly && $this->_drawingGroupData) {
  547. $escherWorkbook = new PHPExcel_Shared_Escher();
  548. $reader = new PHPExcel_Reader_Excel5_Escher($escherWorkbook);
  549. $escherWorkbook = $reader->load($this->_drawingGroupData);
  550. // debug Escher stream
  551. //$debug = new Debug_Escher(new PHPExcel_Shared_Escher());
  552. //$debug->load($this->_drawingGroupData);
  553. }
  554. // Parse the individual sheets
  555. foreach ($this->_sheets as $sheet) {
  556. // check if sheet should be skipped
  557. if (isset($this->_loadSheetsOnly) && !in_array($sheet['name'], $this->_loadSheetsOnly)) {
  558. continue;
  559. }
  560. // add sheet to PHPExcel object
  561. $this->_phpSheet = $this->_phpExcel->createSheet();
  562. $this->_phpSheet->setTitle($sheet['name']);
  563. $this->_phpSheet->setSheetState($sheet['sheetState']);
  564. $this->_pos = $sheet['offset'];
  565. // Initialize isFitToPages. May change after reading SHEETPR record.
  566. $this->_isFitToPages = false;
  567. // Initialize drawingData
  568. $this->_drawingData = '';
  569. // Initialize objs
  570. $this->_objs = array();
  571. // Initialize shared formula parts
  572. $this->_sharedFormulaParts = array();
  573. // Initialize shared formulas
  574. $this->_sharedFormulas = array();
  575. while ($this->_pos < $this->_dataSize) {
  576. $code = $this->_GetInt2d($this->_data, $this->_pos);
  577. switch ($code) {
  578. case self::XLS_Type_BOF:
  579. $length = $this->_GetInt2d($this->_data, $this->_pos + 2);
  580. $recordData = substr($this->_data, $this->_pos + 4, $length);
  581. // move stream pointer to next record
  582. $this->_pos += 4 + $length;
  583. // do not use this version information for anything
  584. // it is unreliable (OpenOffice doc, 5.8), use only version information from the global stream
  585. // offset: 2; size: 2; type of the following data
  586. $substreamType = $this->_GetInt2d($recordData, 2);
  587. if ($substreamType != self::XLS_Worksheet) {
  588. break 2;
  589. }
  590. break;
  591. case self::XLS_Type_PRINTGRIDLINES: $this->_readPrintGridlines(); break;
  592. case self::XLS_Type_DEFAULTROWHEIGHT: $this->_readDefaultRowHeight(); break;
  593. case self::XLS_Type_SHEETPR: $this->_readSheetPr(); break;
  594. case self::XLS_Type_HORIZONTALPAGEBREAKS: $this->_readHorizontalPageBreaks(); break;
  595. case self::XLS_Type_VERTICALPAGEBREAKS: $this->_readVerticalPageBreaks(); break;
  596. case self::XLS_Type_HEADER: $this->_readHeader(); break;
  597. case self::XLS_Type_FOOTER: $this->_readFooter(); break;
  598. case self::XLS_Type_HCENTER: $this->_readHcenter(); break;
  599. case self::XLS_Type_VCENTER: $this->_readVcenter(); break;
  600. case self::XLS_Type_LEFTMARGIN: $this->_readLeftMargin(); break;
  601. case self::XLS_Type_RIGHTMARGIN: $this->_readRightMargin(); break;
  602. case self::XLS_Type_TOPMARGIN: $this->_readTopMargin(); break;
  603. case self::XLS_Type_BOTTOMMARGIN: $this->_readBottomMargin(); break;
  604. case self::XLS_Type_PAGESETUP: $this->_readPageSetup(); break;
  605. case self::XLS_Type_PROTECT: $this->_readProtect(); break;
  606. case self::XLS_Type_PASSWORD: $this->_readPassword(); break;
  607. case self::XLS_Type_DEFCOLWIDTH: $this->_readDefColWidth(); break;
  608. case self::XLS_Type_COLINFO: $this->_readColInfo(); break;
  609. case self::XLS_Type_DIMENSION: $this->_readDefault(); break;
  610. case self::XLS_Type_ROW: $this->_readRow(); break;
  611. case self::XLS_Type_DBCELL: $this->_readDefault(); break;
  612. case self::XLS_Type_RK: $this->_readRk(); break;
  613. case self::XLS_Type_LABELSST: $this->_readLabelSst(); break;
  614. case self::XLS_Type_MULRK: $this->_readMulRk(); break;
  615. case self::XLS_Type_NUMBER: $this->_readNumber(); break;
  616. case self::XLS_Type_FORMULA: $this->_readFormula(); break;
  617. case self::XLS_Type_SHAREDFMLA: $this->_readSharedFmla(); break;
  618. case self::XLS_Type_BOOLERR: $this->_readBoolErr(); break;
  619. case self::XLS_Type_MULBLANK: $this->_readMulBlank(); break;
  620. case self::XLS_Type_LABEL: $this->_readLabel(); break;
  621. case self::XLS_Type_BLANK: $this->_readBlank(); break;
  622. case self::XLS_Type_MSODRAWING: $this->_readMsoDrawing(); break;
  623. case self::XLS_Type_OBJ: $this->_readObj(); break;
  624. case self::XLS_Type_WINDOW2: $this->_readWindow2(); break;
  625. case self::XLS_Type_SCL: $this->_readScl(); break;
  626. case self::XLS_Type_PANE: $this->_readPane(); break;
  627. case self::XLS_Type_MERGEDCELLS: $this->_readMergedCells(); break;
  628. case self::XLS_Type_HYPERLINK: $this->_readHyperLink(); break;
  629. case self::XLS_Type_SHEETLAYOUT: $this->_readSheetLayout(); break;
  630. case self::XLS_Type_RANGEPROTECTION: $this->_readRangeProtection(); break;
  631. //case self::XLS_Type_IMDATA: $this->_readImData(); break;
  632. case self::XLS_Type_CONTINUE: $this->_readContinue(); break;
  633. case self::XLS_Type_EOF: $this->_readDefault(); break 2;
  634. default: $this->_readDefault(); break;
  635. }
  636. }
  637. // treat MSODRAWING records, sheet-level Escher
  638. if (!$this->_readDataOnly && $this->_drawingData) {
  639. $escherWorksheet = new PHPExcel_Shared_Escher();
  640. $reader = new PHPExcel_Reader_Excel5_Escher($escherWorksheet);
  641. $escherWorksheet = $reader->load($this->_drawingData);
  642. // debug Escher stream
  643. //$debug = new Debug_Escher(new PHPExcel_Shared_Escher());
  644. //$debug->load($this->_drawingData);
  645. // get all spContainers in one long array, so they can be mapped to OBJ records
  646. $allSpContainers = $escherWorksheet->getDgContainer()->getSpgrContainer()->getAllSpContainers();
  647. }
  648. // treat OBJ records
  649. foreach ($this->_objs as $n => $obj) {
  650. // the first shape container never has a corresponding OBJ record, hence $n + 1
  651. $spContainer = $allSpContainers[$n + 1];
  652. // we skip all spContainers that are a part of a group shape since we cannot yet handle those
  653. if ($spContainer->getNestingLevel() > 1) {
  654. continue;
  655. }
  656. // calculate the width and height of the shape
  657. list($startColumn, $startRow) = PHPExcel_Cell::coordinateFromString($spContainer->getStartCoordinates());
  658. list($endColumn, $endRow) = PHPExcel_Cell::coordinateFromString($spContainer->getEndCoordinates());
  659. $startOffsetX = $spContainer->getStartOffsetX();
  660. $startOffsetY = $spContainer->getStartOffsetY();
  661. $endOffsetX = $spContainer->getEndOffsetX();
  662. $endOffsetY = $spContainer->getEndOffsetY();
  663. $width = PHPExcel_Shared_Excel5::getDistanceX($this->_phpSheet, $startColumn, $startOffsetX, $endColumn, $endOffsetX);
  664. $height = PHPExcel_Shared_Excel5::getDistanceY($this->_phpSheet, $startRow, $startOffsetY, $endRow, $endOffsetY);
  665. // calculate offsetX and offsetY of the shape
  666. $offsetX = $startOffsetX * PHPExcel_Shared_Excel5::sizeCol($this->_phpSheet, $startColumn) / 1024;
  667. $offsetY = $startOffsetY * PHPExcel_Shared_Excel5::sizeRow($this->_phpSheet, $startRow) / 256;
  668. switch ($obj['type']) {
  669. case 0x08:
  670. // picture
  671. // get index to BSE entry (1-based)
  672. $BSEindex = $spContainer->getOPT(0x0104);
  673. $BSECollection = $escherWorkbook->getDggContainer()->getBstoreContainer()->getBSECollection();
  674. $BSE = $BSECollection[$BSEindex - 1];
  675. $blipType = $BSE->getBlipType();
  676. // need check because some blip types are not supported by Escher reader such as EMF
  677. if ($blip = $BSE->getBlip()) {
  678. $ih = imagecreatefromstring($blip->getData());
  679. $drawing = new PHPExcel_Worksheet_MemoryDrawing();
  680. $drawing->setImageResource($ih);
  681. // width, height, offsetX, offsetY
  682. $drawing->setResizeProportional(false);
  683. $drawing->setWidth($width);
  684. $drawing->setHeight($height);
  685. $drawing->setOffsetX($offsetX);
  686. $drawing->setOffsetY($offsetY);
  687. switch ($blipType) {
  688. case PHPExcel_Shared_Escher_DggContainer_BstoreContainer_BSE::BLIPTYPE_JPEG:
  689. $drawing->setRenderingFunction(PHPExcel_Worksheet_MemoryDrawing::RENDERING_JPEG);
  690. $drawing->setMimeType(PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_JPEG);
  691. break;
  692. case PHPExcel_Shared_Escher_DggContainer_BstoreContainer_BSE::BLIPTYPE_PNG:
  693. $drawing->setRenderingFunction(PHPExcel_Worksheet_MemoryDrawing::RENDERING_PNG);
  694. $drawing->setMimeType(PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_PNG);
  695. break;
  696. }
  697. $drawing->setWorksheet($this->_phpSheet);
  698. $drawing->setCoordinates($spContainer->getStartCoordinates());
  699. }
  700. break;
  701. default:
  702. // other object type
  703. break;
  704. }
  705. }
  706. // treat SHAREDFMLA records
  707. if ($this->_version == self::XLS_BIFF8) {
  708. foreach ($this->_sharedFormulaParts as $cell => $baseCell) {
  709. $formula = $this->_getFormulaFromStructure($this->_sharedFormulas[$baseCell], $cell);
  710. $this->_phpSheet->getCell($cell)->setValueExplicit('=' . $formula, PHPExcel_Cell_DataType::TYPE_FORMULA);
  711. }
  712. }
  713. }
  714. // add the named ranges (defined names)
  715. foreach ($this->_definedname as $definedName) {
  716. if ($definedName['isBuiltInName']) {
  717. switch ($definedName['name']) {
  718. case pack('C', 0x06):
  719. // print area
  720. // in general, formula looks like this: Foo!$C$7:$J$66,Bar!$A$1:$IV$2
  721. $ranges = explode(',', $definedName['formula']); // FIXME: what if sheetname contains comma?
  722. foreach ($ranges as $range) {
  723. // $range should look like this one of these
  724. // Foo!$C$7:$J$66
  725. // Bar!$A$1:$IV$2
  726. $explodes = explode('!', $range);
  727. if (count($explodes) == 2) {
  728. if ($docSheet = $this->_phpExcel->getSheetByName($explodes[0])) {
  729. $extractedRange = $explodes[1];
  730. $extractedRange = str_replace('$', '', $extractedRange);
  731. $docSheet->getPageSetup()->setPrintArea($extractedRange);
  732. }
  733. }
  734. }
  735. break;
  736. case pack('C', 0x07):
  737. // print titles (repeating rows)
  738. // Assuming BIFF8, there are 3 cases
  739. // 1. repeating rows
  740. // formula looks like this: Sheet!$A$1:$IV$2
  741. // rows 1-2 repeat
  742. // 2. repeating columns
  743. // formula looks like this: Sheet!$A$1:$B$65536
  744. // columns A-B repeat
  745. // 3. both repeating rows and repeating columns
  746. // formula looks like this: Sheet!$A$1:$B$65536,Sheet!$A$1:$IV$2
  747. $ranges = explode(',', $definedName['formula']); // FIXME: what if sheetname contains comma?
  748. foreach ($ranges as $range) {
  749. // $range should look like this one of these
  750. // Sheet!$A$1:$B$65536
  751. // Sheet!$A$1:$IV$2
  752. $explodes = explode('!', $range);
  753. if (count($explodes) == 2) {
  754. if ($docSheet = $this->_phpExcel->getSheetByName($explodes[0])) {
  755. $extractedRange = $explodes[1];
  756. $extractedRange = str_replace('$', '', $extractedRange);
  757. $coordinateStrings = explode(':', $extractedRange);
  758. if (count($coordinateStrings) == 2) {
  759. list($firstColumn, $firstRow) = PHPExcel_Cell::coordinateFromString($coordinateStrings[0]);
  760. list($lastColumn, $lastRow) = PHPExcel_Cell::coordinateFromString($coordinateStrings[1]);
  761. if ($firstColumn == 'A' and $lastColumn == 'IV') {
  762. // then we have repeating rows
  763. $docSheet->getPageSetup()->setRowsToRepeatAtTop(array($firstRow, $lastRow));
  764. } elseif ($firstRow == 1 and $lastRow == 65536) {
  765. // then we have repeating columns
  766. $docSheet->getPageSetup()->setColumnsToRepeatAtLeft(array($firstColumn, $lastColumn));
  767. }
  768. }
  769. }
  770. }
  771. }
  772. break;
  773. }
  774. } else {
  775. // Extract range
  776. $explodes = explode('!', $definedName['formula']);
  777. if (count($explodes) == 2) {
  778. if ($docSheet = $this->_phpExcel->getSheetByName($explodes[0])) {
  779. $extractedRange = $explodes[1];
  780. $extractedRange = str_replace('$', '', $extractedRange);
  781. $this->_phpExcel->addNamedRange( new PHPExcel_NamedRange((string)$definedName['name'], $docSheet, $extractedRange, false) );
  782. }
  783. }
  784. }
  785. }
  786. return $this->_phpExcel;
  787. }
  788. /**
  789. * Reads a general type of BIFF record. Does nothing except for moving stream pointer forward to next record.
  790. */
  791. private function _readDefault()
  792. {
  793. $length = $this->_GetInt2d($this->_data, $this->_pos + 2);
  794. $recordData = substr($this->_data, $this->_pos + 4, $length);
  795. // move stream pointer to next record
  796. $this->_pos += 4 + $length;
  797. }
  798. /**
  799. * FILEPASS
  800. *
  801. * This record is part of the File Protection Block. It
  802. * contains information about the read/write password of the
  803. * file. All record contents following this record will be
  804. * encrypted.
  805. *
  806. * -- "OpenOffice.org's Documentation of the Microsoft
  807. * Excel File Format"
  808. */
  809. private function _readFilepass()
  810. {
  811. $length = $this->_GetInt2d($this->_data, $this->_pos + 2);
  812. $recordData = substr($this->_data, $this->_pos + 4, $length);
  813. // move stream pointer to next record
  814. $this->_pos += 4 + $length;
  815. throw new Exception('Cannot read encrypted file');
  816. }
  817. /**
  818. * CODEPAGE
  819. *
  820. * This record stores the text encoding used to write byte
  821. * strings, stored as MS Windows code page identifier.
  822. *
  823. * -- "OpenOffice.org's Documentation of the Microsoft
  824. * Excel File Format"
  825. */
  826. private function _readCodepage()
  827. {
  828. $length = $this->_GetInt2d($this->_data, $this->_pos + 2);
  829. $recordData = substr($this->_data, $this->_pos + 4, $length);
  830. // move stream pointer to next record
  831. $this->_pos += 4 + $length;
  832. // offset: 0; size: 2; code page identifier
  833. $codepage = $this->_GetInt2d($recordData, 0);
  834. switch ($codepage) {
  835. case 367: // ASCII
  836. $this->_codepage ="ASCII";
  837. break;
  838. case 437: //OEM US
  839. $this->_codepage ="CP437";
  840. break;
  841. case 720: //OEM Arabic
  842. // currently not supported by libiconv
  843. $this->_codepage = "";
  844. break;
  845. case 737: //OEM Greek
  846. $this->_codepage ="CP737";
  847. break;
  848. case 775: //OEM Baltic
  849. $this->_codepage ="CP775";
  850. break;
  851. case 850: //OEM Latin I
  852. $this->_codepage ="CP850";
  853. break;
  854. case 852: //OEM Latin II (Central European)
  855. $this->_codepage ="CP852";
  856. break;
  857. case 855: //OEM Cyrillic
  858. $this->_codepage ="CP855";
  859. break;
  860. case 857: //OEM Turkish
  861. $this->_codepage ="CP857";
  862. break;
  863. case 858: //OEM Multilingual Latin I with Euro
  864. $this->_codepage ="CP858";
  865. break;
  866. case 860: //OEM Portugese
  867. $this->_codepage ="CP860";
  868. break;
  869. case 861: //OEM Icelandic
  870. $this->_codepage ="CP861";
  871. break;
  872. case 862: //OEM Hebrew
  873. $this->_codepage ="CP862";
  874. break;
  875. case 863: //OEM Canadian (French)
  876. $this->_codepage ="CP863";
  877. break;
  878. case 864: //OEM Arabic
  879. $this->_codepage ="CP864";
  880. break;
  881. case 865: //OEM Nordic
  882. $this->_codepage ="CP865";
  883. break;
  884. case 866: //OEM Cyrillic (Russian)
  885. $this->_codepage ="CP866";
  886. break;
  887. case 869: //OEM Greek (Modern)
  888. $this->_codepage ="CP869";
  889. break;
  890. case 874: //ANSI Thai
  891. $this->_codepage ="CP874";
  892. break;
  893. case 932: //ANSI Japanese Shift-JIS
  894. $this->_codepage ="CP932";
  895. break;
  896. case 936: //ANSI Chinese Simplified GBK
  897. $this->_codepage ="CP936";
  898. break;
  899. case 949: //ANSI Korean (Wansung)
  900. $this->_codepage ="CP949";
  901. break;
  902. case 950: //ANSI Chinese Traditional BIG5
  903. $this->_codepage ="CP950";
  904. break;
  905. case 1200: //UTF-16 (BIFF8)
  906. $this->_codepage ="UTF-16LE";
  907. break;
  908. case 1250:// ANSI Latin II (Central European)
  909. $this->_codepage ="CP1250";
  910. break;
  911. case 1251: //ANSI Cyrillic
  912. $this->_codepage ="CP1251";
  913. break;
  914. case 1252: //ANSI Latin I (BIFF4-BIFF7)
  915. $this->_codepage ="CP1252";
  916. break;
  917. case 1253: //ANSI Greek
  918. $this->_codepage ="CP1253";
  919. break;
  920. case 1254: //ANSI Turkish
  921. $this->_codepage ="CP1254";
  922. break;
  923. case 1255: //ANSI Hebrew
  924. $this->_codepage ="CP1255";
  925. break;
  926. case 1256: //ANSI Arabic
  927. $this->_codepage ="CP1256";
  928. break;
  929. case 1257: //ANSI Baltic
  930. $this->_codepage ="CP1257";
  931. break;
  932. case 1258: //ANSI Vietnamese
  933. $this->_codepage ="CP1258";
  934. break;
  935. case 1361: //ANSI Korean (Johab)
  936. $this->_codepage ="CP1361";
  937. break;
  938. case 10000: //Apple Roman
  939. $this->_codepage = 'MAC';
  940. break;
  941. case 32768: //Apple Roman
  942. $this->_codepage = 'MAC';
  943. break;
  944. case 32769: //ANSI Latin I (BIFF2-BIFF3)
  945. // currently not supported by libiconv
  946. $this->_codepage = "";
  947. break;
  948. }
  949. }
  950. /**
  951. * DATEMODE
  952. *
  953. * This record specifies the base date for displaying date
  954. * values. All dates are stored as count of days past this
  955. * base date. In BIFF2-BIFF4 this record is part of the
  956. * Calculation Settings Block. In BIFF5-BIFF8 it is
  957. * stored in the Workbook Globals Substream.
  958. *
  959. * -- "OpenOffice.org's Documentation of the Microsoft
  960. * Excel File Format"
  961. */
  962. private function _readDateMode()
  963. {
  964. $length = $this->_GetInt2d($this->_data, $this->_pos + 2);
  965. $recordData = substr($this->_data, $this->_pos + 4, $length);
  966. // move stream pointer to next record
  967. $this->_pos += 4 + $length;
  968. // offset: 0; size: 2; 0 = base 1900, 1 = base 1904
  969. PHPExcel_Shared_Date::setExcelCalendar(PHPExcel_Shared_Date::CALENDAR_WINDOWS_1900);
  970. if (ord($recordData{0}) == 1) {
  971. PHPExcel_Shared_Date::setExcelCalendar(PHPExcel_Shared_Date::CALENDAR_MAC_1904);
  972. }
  973. }
  974. /**
  975. * Read a FONT record
  976. */
  977. private function _readFont()
  978. {
  979. $length = $this->_GetInt2d($this->_data, $this->_pos + 2);
  980. $recordData = substr($this->_data, $this->_pos + 4, $length);
  981. // move stream pointer to next record
  982. $this->_pos += 4 + $length;
  983. if (!$this->_readDataOnly) {
  984. $objFont = new PHPExcel_Style_Font();
  985. // offset: 0; size: 2; height of the font (in twips = 1/20 of a point)
  986. $size = $this->_GetInt2d($recordData, 0);
  987. $objFont->setSize($size / 20);
  988. // offset: 2; size: 2; option flags
  989. // bit: 0; mask 0x0001; bold (redundant in BIFF5-BIFF8)
  990. // bit: 1; mask 0x0002; italic
  991. $isItalic = (0x0002 & $this->_GetInt2d($recordData, 2)) >> 1;
  992. if ($isItalic) $objFont->setItalic(true);
  993. // bit: 2; mask 0x0004; underlined (redundant in BIFF5-BIFF8)
  994. // bit: 3; mask 0x0008; strike
  995. $isStrike = (0x0008 & $this->_GetInt2d($recordData, 2)) >> 3;
  996. if ($isStrike) $objFont->setStrikethrough(true);
  997. // offset: 4; size: 2; colour index
  998. $colorIndex = $this->_GetInt2d($recordData, 4);
  999. $objFont->colorIndex = $colorIndex;
  1000. // offset: 6; size: 2; font weight
  1001. $weight = $this->_GetInt2d($recordData, 6);
  1002. switch ($weight) {
  1003. case 0x02BC:
  1004. $objFont->setBold(true);
  1005. break;
  1006. }
  1007. // offset: 8; size: 2; escapement type
  1008. $escapement = $this->_GetInt2d($recordData, 8);
  1009. switch ($escapement) {
  1010. case 0x0001:
  1011. $objFont->setSuperScript(true);
  1012. break;
  1013. case 0x0002:
  1014. $objFont->setSubScript(true);
  1015. break;
  1016. }
  1017. // offset: 10; size: 1; underline type
  1018. $underlineType = ord($recordData{10});
  1019. switch ($underlineType) {
  1020. case 0x00:
  1021. break; // no underline
  1022. case 0x01:
  1023. $objFont->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
  1024. break;
  1025. case 0x02:
  1026. $objFont->setUnderline(PHPExcel_Style_Font::UNDERLINE_DOUBLE);
  1027. break;
  1028. case 0x21:
  1029. $objFont->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLEACCOUNTING);
  1030. break;
  1031. case 0x22:
  1032. $objFont->setUnderline(PHPExcel_Style_Font::UNDERLINE_DOUBLEACCOUNTING);
  1033. break;
  1034. }
  1035. // offset: 11; size: 1; font family
  1036. // offset: 12; size: 1; character set
  1037. // offset: 13; size: 1; not used
  1038. // offset: 14; size: var; font name
  1039. if ($this->_version == self::XLS_BIFF8) {
  1040. $string = $this->_readUnicodeStringShort(substr($recordData, 14));
  1041. } else {
  1042. $string = $this->_readByteStringShort(substr($recordData, 14));
  1043. }
  1044. $objFont->setName($string['value']);
  1045. $this->_objFonts[] = $objFont;
  1046. }
  1047. }
  1048. /**
  1049. * FORMAT
  1050. *
  1051. * This record contains information about a number format.
  1052. * All FORMAT records occur together in a sequential list.
  1053. *
  1054. * In BIFF2-BIFF4 other records referencing a FORMAT record
  1055. * contain a zero-based index into this list. From BIFF5 on
  1056. * the FORMAT record contains the index itself that will be
  1057. * used by other records.
  1058. *
  1059. * -- "OpenOffice.org's Documentation of the Microsoft
  1060. * Excel File Format"
  1061. */
  1062. private function _readFormat()
  1063. {
  1064. $length = $this->_GetInt2d($this->_data, $this->_pos + 2);
  1065. $recordData = substr($this->_data, $this->_pos + 4, $length);
  1066. // move stream pointer to next record
  1067. $this->_pos += 4 + $length;
  1068. if (!$this->_readDataOnly) {
  1069. $indexCode = $this->_GetInt2d($recordData, 0);
  1070. if ($this->_version == self::XLS_BIFF8) {
  1071. $string = $this->_readUnicodeStringLong(substr($recordData, 2));
  1072. } else {
  1073. // BIFF7
  1074. $string = $this->_readByteStringShort(substr($recordData, 2));
  1075. }
  1076. $formatString = $string['value'];
  1077. $this->_formats[$indexCode] = $formatString;
  1078. }
  1079. }
  1080. /**
  1081. * XF - Extended Format
  1082. *
  1083. * This record contains formatting information for cells, rows, columns or styles.
  1084. * According to http://support.microsoft.com/kb/147732 there are always at least 15 cell style XF
  1085. * and 1 cell XF.
  1086. * Inspection of Excel files generated by MS Office Excel shows that XF records 0-14 are cell style XF
  1087. * and XF record 15 is a cell XF
  1088. * We only read the first cell style XF and skip the remaining cell style XF records
  1089. * We read all cell XF records.
  1090. *
  1091. * -- "OpenOffice.org's Documentation of the Microsoft
  1092. * Excel File Format"
  1093. */
  1094. private function _readXf()
  1095. {
  1096. $length = $this->_GetInt2d($this->_data, $this->_pos + 2);
  1097. $recordData = substr($this->_data, $this->_pos + 4, $length);
  1098. // move stream pointer to next record
  1099. $this->_pos += 4 + $length;
  1100. $objStyle = new PHPExcel_Style();
  1101. if (!$this->_readDataOnly) {
  1102. // offset: 0; size: 2; Index to FONT record
  1103. if ($this->_GetInt2d($recordData, 0) < 4) {
  1104. $fontIndex = $this->_GetInt2d($recordData, 0);
  1105. } else {
  1106. // this has to do with that index 4 is omitted in all BIFF versions for some strange reason
  1107. // check the OpenOffice documentation of the FONT record
  1108. $fontIndex = $this->_GetInt2d($recordData, 0) - 1;
  1109. }
  1110. $objStyle->setFont($this->_objFonts[$fontIndex]);
  1111. // offset: 2; size: 2; Index to FORMAT record
  1112. $numberFormatIndex = $this->_GetInt2d($recordData, 2);
  1113. if (isset($this->_formats[$numberFormatIndex])) {
  1114. // then we have user-defined format code
  1115. $numberformat = array('code' => $this->_formats[$numberFormatIndex]);
  1116. } elseif (($code = PHPExcel_Style_NumberFormat::builtInFormatCode($numberFormatIndex)) !== '') {
  1117. // then we have built-in format code
  1118. $numberformat = array('code' => $code);
  1119. } else {
  1120. // we set the general format code
  1121. $numberformat = array('code' => 'General');
  1122. }
  1123. $objStyle->getNumberFormat()->setFormatCode($numberformat['code']);
  1124. // offset: 4; size: 2; XF type, cell protection, and parent style XF
  1125. // bit 2-0; mask 0x0007; XF_TYPE_PROT
  1126. $xfTypeProt = $this->_GetInt2d($recordData, 4);
  1127. // bit 0; mask 0x01; 1 = cell is locked
  1128. $isLocked = (0x01 & $xfTypeProt) >> 0;
  1129. $objStyle->getProtection()->setLocked($isLocked ?
  1130. PHPExcel_Style_Protection::PROTECTION_INHERIT : PHPExcel_Style_Protection::PROTECTION_UNPROTECTED);
  1131. // bit 1; mask 0x02; 1 = Formula is hidden
  1132. $isHidden = (0x02 & $xfTypeProt) >> 1;
  1133. $objStyle->getProtection()->setHidden($isHidden ?
  1134. PHPExcel_Style_Protection::PROTECTION_PROTECTED : PHPExcel_Style_Protection::PROTECTION_UNPROTECTED);
  1135. // bit 2; mask 0x04; 0 = Cell XF, 1 = Cell Style XF
  1136. $isCellStyleXf = (0x04 & $xfTypeProt) >> 2;
  1137. // offset: 6; size: 1; Alignment and text break
  1138. // bit 2-0, mask 0x07; horizontal alignment
  1139. $horAlign = (0x07 & ord($recordData{6})) >> 0;
  1140. switch ($horAlign) {
  1141. case 0:
  1142. $objStyle->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_GENERAL);
  1143. break;
  1144. case 1:
  1145. $objStyle->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
  1146. break;
  1147. case 2:
  1148. $objStyle->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  1149. break;
  1150. case 3:
  1151. $objStyle->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
  1152. break;
  1153. case 5:
  1154. $objStyle->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY);
  1155. break;
  1156. case 6:
  1157. $objStyle->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER_CONTINUOUS);
  1158. break;
  1159. }
  1160. // bit 3, mask 0x08; wrap text
  1161. $wrapText = (0x08 & ord($recordData{6})) >> 3;
  1162. switch ($wrapText) {
  1163. case 0:
  1164. $objStyle->getAlignment()->setWrapText(false);
  1165. break;
  1166. case 1:
  1167. $objStyle->getAlignment()->setWrapText(true);
  1168. break;
  1169. }
  1170. // bit 6-4, mask 0x70; vertical alignment
  1171. $vertAlign = (0x70 & ord($recordData{6})) >> 4;
  1172. switch ($vertAlign) {
  1173. case 0:
  1174. $objStyle->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
  1175. break;
  1176. case 1:
  1177. $objStyle->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
  1178. break;
  1179. case 2:
  1180. $objStyle->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_BOTTOM);
  1181. break;
  1182. case 3:
  1183. $objStyle->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_JUSTIFY);
  1184. break;
  1185. }
  1186. if ($this->_version == self::XLS_BIFF8) {
  1187. // offset: 7; size: 1; XF_ROTATION: Text rotation angle
  1188. $angle = ord($recordData{7});
  1189. $rotation = 0;
  1190. if ($angle <= 90) {
  1191. $rotation = $angle;
  1192. } else if ($angle <= 180) {
  1193. $rotation = 90 - $angle;
  1194. } else if ($angle == 255) {
  1195. $rotation = -165;
  1196. }
  1197. $objStyle->getAlignment()->setTextRotation($rotation);
  1198. // offset: 8; size: 1; Indentation, shrink to cell size, and text direction
  1199. // bit: 3-0; mask: 0x0F; indent level
  1200. $indent = (0x0F & ord($recordData{8})) >> 0;
  1201. $objStyle->getAlignment()->setIndent($indent);
  1202. // bit: 4; mask: 0x10; 1 = shrink content to fit into cell
  1203. $shrinkToFit = (0x10 & ord($recordData{8})) >> 4;
  1204. switch ($shrinkToFit) {
  1205. case 0:
  1206. $objStyle->getAlignment()->setShrinkToFit(false);
  1207. break;
  1208. case 1:
  1209. $objStyle->getAlignment()->setShrinkToFit(true);
  1210. break;
  1211. }
  1212. // offset: 9; size: 1; Flags used for attribute groups
  1213. // offset: 10; size: 4; Cell border lines and background area
  1214. // bit: 3-0; mask: 0x0000000F; left style
  1215. if ($bordersLeftStyle = $this->_mapBorderStyle((0x0000000F & $this->_GetInt4d($recordData, 10)) >> 0)) {
  1216. $objStyle->getBorders()->getLeft()->setBorderStyle($bordersLeftStyle);
  1217. }
  1218. // bit: 7-4; mask: 0x000000F0; right style
  1219. if ($bordersRightStyle = $this->_mapBorderStyle((0x000000F0 & $this->_GetInt4d($recordData, 10)) >> 4)) {
  1220. $objStyle->getBorders()->getRight()->setBorderStyle($bordersRightStyle);
  1221. }
  1222. // bit: 11-8; mask: 0x00000F00; top style
  1223. if ($bordersTopStyle = $this->_mapBorderStyle((0x00000F00 & $this->_GetInt4d($recordData, 10)) >> 8)) {
  1224. $objStyle->getBorders()->getTop()->setBorderStyle($bordersTopStyle);
  1225. }
  1226. // bit: 15-12; mask: 0x0000F000; bottom style
  1227. if ($bordersBottomStyle = $this->_mapBorderStyle((0x0000F000 & $this->_GetInt4d($recordData, 10)) >> 12)) {
  1228. $objStyle->getBorders()->getBottom()->setBorderStyle($bordersBottomStyle);
  1229. }
  1230. // bit: 22-16; mask: 0x007F0000; left color
  1231. $objStyle->getBorders()->getLeft()->colorIndex = (0x007F0000 & $this->_GetInt4d($recordData, 10)) >> 16;
  1232. // bit: 29-23; mask: 0x3F800000; right color
  1233. $objStyle->getBorders()->getRight()->colorIndex = (0x3F800000 & $this->_GetInt4d($recordData, 10)) >> 23;
  1234. // offset: 14; size: 4;
  1235. // bit: 6-0; mask: 0x0000007F; top color
  1236. $objStyle->getBorders()->getTop()->colorIndex = (0x0000007F & $this->_GetInt4d($recordData, 14)) >> 0;
  1237. // bit: 13-7; mask: 0x00003F80; bottom color
  1238. $objStyle->getBorders()->getBottom()->colorIndex = (0x00003F80 & $this->_GetInt4d($recordData, 14)) >> 7;
  1239. // bit: 31-26; mask: 0xFC000000 fill pattern
  1240. if ($fillType = $this->_mapFillPattern((0xFC000000 & $this->_GetInt4d($recordData, 14)) >> 26)) {
  1241. $objStyle->getFill()->setFillType($fillType);
  1242. }
  1243. // offset: 18; size: 2; pattern and background colour
  1244. // bit: 6-0; mask: 0x007F; color index for pattern color
  1245. $objStyle->getFill()->startcolorIndex = (0x007F & $this->_GetInt2d($recordData, 18)) >> 0;
  1246. // bit: 13-7; mask: 0x3F80; color index for pattern background
  1247. $objStyle->getFill()->endcolorIndex = (0x3F80 & $this->_GetInt2d($recordData, 18)) >> 7;
  1248. } else {
  1249. // BIFF5
  1250. // offset: 7; size: 1; Text orientation and flags
  1251. $orientationAndFlags = ord($recordData{7});
  1252. // bit: 1-0; mask: 0x03; XF_ORIENTATION: Text orientation
  1253. $xfOrientation = (0x03 & $orientationAndFlags) >> 0;
  1254. switch ($xfOrientation) {
  1255. case 0:
  1256. $objStyle->getAlignment()->setTextRotation(0);
  1257. break;
  1258. case 1:
  1259. $objStyle->getAlignment()->setTextRotation(-165);
  1260. break;
  1261. case 2:
  1262. $objStyle->getAlignment()->setTextRotation(90);
  1263. break;
  1264. case 3:
  1265. $objStyle->getAlignment()->setTextRotation(-90);
  1266. break;
  1267. }
  1268. // offset: 8; size: 4; cell border lines and background area
  1269. $borderAndBackground = $this->_GetInt4d($recordData, 8);
  1270. // bit: 6-0; mask: 0x0000007F; color index for pattern color
  1271. $objStyle->getFill()->startcolorIndex = (0x0000007F & $borderAndBackground) >> 0;
  1272. // bit: 13-7; mask: 0x00003F80; color index for pattern background
  1273. $objStyle->getFill()->endcolorIndex = (0x00003F80 & $borderAndBackground) >> 7;
  1274. // bit: 21-16; mask: 0x003F0000; fill pattern
  1275. $objStyle->getFill()->setFillType($this->_mapFillPattern((0x003F0000 & $borderAndBackground) >> 16));
  1276. // bit: 24-22; mask: 0x01C00000; bottom line style
  1277. $objStyle->getBorders()->getBottom()->setBorderStyle($this->_mapBorderStyle((0x01C00000 & $borderAndBackground) >> 22));
  1278. // bit: 31-25; mask: 0xFE000000; bottom line color
  1279. $objStyle->getBorders()->getBottom()->colorIndex = (0xFE000000 & $borderAndBackground) >> 25;
  1280. // offset: 12; size: 4; cell border lines
  1281. $borderLines = $this->_GetInt4d($recordData, 12);
  1282. // bit: 2-0; mask: 0x00000007; top line style
  1283. $objStyle->getBorders()->getTop()->setBorderStyle($this->_mapBorderStyle((0x00000007 & $borderLines) >> 0));
  1284. // bit: 5-3; mask: 0x00000038; left line style
  1285. $objStyle->getBorders()->getLeft()->setBorderStyle($this->_mapBorderStyle((0x00000038 & $borderLines) >> 3));
  1286. // bit: 8-6; mask: 0x000001C0; right line style
  1287. $objStyle->getBorders()->getRight()->setBorderStyle($this->_mapBorderStyle((0x000001C0 & $borderLines) >> 6));
  1288. // bit: 15-9; mask: 0x0000FE00; top line color index
  1289. $objStyle->getBorders()->getTop()->colorIndex = (0x0000FE00 & $borderLines) >> 9;
  1290. // bit: 22-16; mask: 0x007F0000; left line color index
  1291. $objStyle->getBorders()->getLeft()->colorIndex = (0x007F0000 & $borderLines) >> 16;
  1292. // bit: 29-23; mask: 0x3F800000; right line color index
  1293. $objStyle->getBorders()->getRight()->colorIndex = (0x3F800000 & $borderLines) >> 23;
  1294. }
  1295. // add cellStyleXf or cellXf and update mapping
  1296. if ($isCellStyleXf) {
  1297. // we only read one style XF record which is always the first
  1298. if ($this->_xfIndex == 0) {
  1299. $this->_phpExcel->addCellStyleXf($objStyle);
  1300. $this->_mapCellStyleXfIndex[$this->_xfIndex] = 0;
  1301. }
  1302. } else {
  1303. // we read all cell XF records
  1304. $this->_phpExcel->addCellXf($objStyle);
  1305. $this->_mapCellXfIndex[$this->_xfIndex] = count($this->_phpExcel

Large files files are truncated, but you can click here to view the full file