PageRenderTime 59ms CodeModel.GetById 21ms RepoModel.GetById 0ms app.codeStats 1ms

/common/libraries/plugin/phpexcel/PHPExcel/Reader/Excel5.php

https://bitbucket.org/ywarnier/chamilo-dev
PHP | 8247 lines | 5256 code | 1111 blank | 1880 comment | 454 complexity | d4a0c02ad14d08ad33754fe3624e1cd6 MD5 | raw file
Possible License(s): GPL-2.0, BSD-3-Clause, LGPL-2.1, LGPL-3.0, GPL-3.0, MIT

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 - 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. */
  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. /**
  60. * @ignore
  61. */
  62. define('PHPEXCEL_ROOT', dirname(__FILE__) . '/../../');
  63. require (PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php');
  64. }
  65. /**
  66. * PHPExcel_Reader_Excel5
  67. *
  68. * This class uses {@link http://sourceforge.net/projects/phpexcelreader/parseXL}
  69. *
  70. * @category PHPExcel
  71. * @package PHPExcel_Reader_Excel5
  72. * @copyright Copyright (c) 2006 - 2011 PHPExcel (http://www.codeplex.com/PHPExcel)
  73. */
  74. class PHPExcel_Reader_Excel5 implements PHPExcel_Reader_IReader
  75. {
  76. // ParseXL definitions
  77. const XLS_BIFF8 = 0x0600;
  78. const XLS_BIFF7 = 0x0500;
  79. const XLS_WorkbookGlobals = 0x0005;
  80. const XLS_Worksheet = 0x0010;
  81. // record identifiers
  82. const XLS_Type_FORMULA = 0x0006;
  83. const XLS_Type_EOF = 0x000a;
  84. const XLS_Type_PROTECT = 0x0012;
  85. const XLS_Type_OBJECTPROTECT = 0x0063;
  86. const XLS_Type_SCENPROTECT = 0x00dd;
  87. const XLS_Type_PASSWORD = 0x0013;
  88. const XLS_Type_HEADER = 0x0014;
  89. const XLS_Type_FOOTER = 0x0015;
  90. const XLS_Type_EXTERNSHEET = 0x0017;
  91. const XLS_Type_DEFINEDNAME = 0x0018;
  92. const XLS_Type_VERTICALPAGEBREAKS = 0x001a;
  93. const XLS_Type_HORIZONTALPAGEBREAKS = 0x001b;
  94. const XLS_Type_NOTE = 0x001c;
  95. const XLS_Type_SELECTION = 0x001d;
  96. const XLS_Type_DATEMODE = 0x0022;
  97. const XLS_Type_EXTERNNAME = 0x0023;
  98. const XLS_Type_LEFTMARGIN = 0x0026;
  99. const XLS_Type_RIGHTMARGIN = 0x0027;
  100. const XLS_Type_TOPMARGIN = 0x0028;
  101. const XLS_Type_BOTTOMMARGIN = 0x0029;
  102. const XLS_Type_PRINTGRIDLINES = 0x002b;
  103. const XLS_Type_FILEPASS = 0x002f;
  104. const XLS_Type_FONT = 0x0031;
  105. const XLS_Type_CONTINUE = 0x003c;
  106. const XLS_Type_PANE = 0x0041;
  107. const XLS_Type_CODEPAGE = 0x0042;
  108. const XLS_Type_DEFCOLWIDTH = 0x0055;
  109. const XLS_Type_OBJ = 0x005d;
  110. const XLS_Type_COLINFO = 0x007d;
  111. const XLS_Type_IMDATA = 0x007f;
  112. const XLS_Type_SHEETPR = 0x0081;
  113. const XLS_Type_HCENTER = 0x0083;
  114. const XLS_Type_VCENTER = 0x0084;
  115. const XLS_Type_SHEET = 0x0085;
  116. const XLS_Type_PALETTE = 0x0092;
  117. const XLS_Type_SCL = 0x00a0;
  118. const XLS_Type_PAGESETUP = 0x00a1;
  119. const XLS_Type_MULRK = 0x00bd;
  120. const XLS_Type_MULBLANK = 0x00be;
  121. const XLS_Type_DBCELL = 0x00d7;
  122. const XLS_Type_XF = 0x00e0;
  123. const XLS_Type_MERGEDCELLS = 0x00e5;
  124. const XLS_Type_MSODRAWINGGROUP = 0x00eb;
  125. const XLS_Type_MSODRAWING = 0x00ec;
  126. const XLS_Type_SST = 0x00fc;
  127. const XLS_Type_LABELSST = 0x00fd;
  128. const XLS_Type_EXTSST = 0x00ff;
  129. const XLS_Type_EXTERNALBOOK = 0x01ae;
  130. const XLS_Type_DATAVALIDATIONS = 0x01b2;
  131. const XLS_Type_TXO = 0x01b6;
  132. const XLS_Type_HYPERLINK = 0x01b8;
  133. const XLS_Type_DATAVALIDATION = 0x01be;
  134. const XLS_Type_DIMENSION = 0x0200;
  135. const XLS_Type_BLANK = 0x0201;
  136. const XLS_Type_NUMBER = 0x0203;
  137. const XLS_Type_LABEL = 0x0204;
  138. const XLS_Type_BOOLERR = 0x0205;
  139. const XLS_Type_STRING = 0x0207;
  140. const XLS_Type_ROW = 0x0208;
  141. const XLS_Type_INDEX = 0x020b;
  142. const XLS_Type_ARRAY = 0x0221;
  143. const XLS_Type_DEFAULTROWHEIGHT = 0x0225;
  144. const XLS_Type_WINDOW2 = 0x023e;
  145. const XLS_Type_RK = 0x027e;
  146. const XLS_Type_STYLE = 0x0293;
  147. const XLS_Type_FORMAT = 0x041e;
  148. const XLS_Type_SHAREDFMLA = 0x04bc;
  149. const XLS_Type_BOF = 0x0809;
  150. const XLS_Type_SHEETPROTECTION = 0x0867;
  151. const XLS_Type_RANGEPROTECTION = 0x0868;
  152. const XLS_Type_SHEETLAYOUT = 0x0862;
  153. const XLS_Type_XFEXT = 0x087d;
  154. const XLS_Type_UNKNOWN = 0xffff;
  155. /**
  156. * Read data only?
  157. * Identifies whether the Reader should only read data values for cells, and ignore any formatting information;
  158. * or whether it should read both data and formatting
  159. *
  160. * @var boolean
  161. */
  162. private $_readDataOnly = false;
  163. /**
  164. * Restrict which sheets should be loaded?
  165. * This property holds an array of worksheet names to be loaded. If null, then all worksheets will be loaded.
  166. *
  167. * @var array of string
  168. */
  169. private $_loadSheetsOnly = null;
  170. /**
  171. * PHPExcel_Reader_IReadFilter instance
  172. *
  173. * @var PHPExcel_Reader_IReadFilter
  174. */
  175. private $_readFilter = null;
  176. /**
  177. * Summary Information stream data.
  178. *
  179. * @var string
  180. */
  181. private $_summaryInformation;
  182. /**
  183. * Extended Summary Information stream data.
  184. *
  185. * @var string
  186. */
  187. private $_documentSummaryInformation;
  188. /**
  189. * User-Defined Properties stream data.
  190. *
  191. * @var string
  192. */
  193. private $_userDefinedProperties;
  194. /**
  195. * Workbook stream data. (Includes workbook globals substream as well as sheet substreams)
  196. *
  197. * @var string
  198. */
  199. private $_data;
  200. /**
  201. * Size in bytes of $this->_data
  202. *
  203. * @var int
  204. */
  205. private $_dataSize;
  206. /**
  207. * Current position in stream
  208. *
  209. * @var integer
  210. */
  211. private $_pos;
  212. /**
  213. * Workbook to be returned by the reader.
  214. *
  215. * @var PHPExcel
  216. */
  217. private $_phpExcel;
  218. /**
  219. * Worksheet that is currently being built by the reader.
  220. *
  221. * @var PHPExcel_Worksheet
  222. */
  223. private $_phpSheet;
  224. /**
  225. * BIFF version
  226. *
  227. * @var int
  228. */
  229. private $_version;
  230. /**
  231. * Codepage set in the Excel file being read. Only important for BIFF5 (Excel 5.0 - Excel 95)
  232. * For BIFF8 (Excel 97 - Excel 2003) this will always have the value 'UTF-16LE'
  233. *
  234. * @var string
  235. */
  236. private $_codepage;
  237. /**
  238. * Shared formats
  239. *
  240. * @var array
  241. */
  242. private $_formats;
  243. /**
  244. * Shared fonts
  245. *
  246. * @var array
  247. */
  248. private $_objFonts;
  249. /**
  250. * Color palette
  251. *
  252. * @var array
  253. */
  254. private $_palette;
  255. /**
  256. * Worksheets
  257. *
  258. * @var array
  259. */
  260. private $_sheets;
  261. /**
  262. * External books
  263. *
  264. * @var array
  265. */
  266. private $_externalBooks;
  267. /**
  268. * REF structures. Only applies to BIFF8.
  269. *
  270. * @var array
  271. */
  272. private $_ref;
  273. /**
  274. * External names
  275. *
  276. * @var array
  277. */
  278. private $_externalNames;
  279. /**
  280. * Defined names
  281. *
  282. * @var array
  283. */
  284. private $_definedname;
  285. /**
  286. * Shared strings. Only applies to BIFF8.
  287. *
  288. * @var array
  289. */
  290. private $_sst;
  291. /**
  292. * Panes are frozen? (in sheet currently being read). See WINDOW2 record.
  293. *
  294. * @var boolean
  295. */
  296. private $_frozen;
  297. /**
  298. * Fit printout to number of pages? (in sheet currently being read). See SHEETPR record.
  299. *
  300. * @var boolean
  301. */
  302. private $_isFitToPages;
  303. /**
  304. * Objects. One OBJ record contributes with one entry.
  305. *
  306. * @var array
  307. */
  308. private $_objs;
  309. /**
  310. * Text Objects. One TXO record corresponds with one entry.
  311. *
  312. * @var array
  313. */
  314. private $_textObjects;
  315. /**
  316. * Cell Annotations (BIFF8)
  317. *
  318. * @var array
  319. */
  320. private $_cellNotes;
  321. /**
  322. * The combined MSODRAWINGGROUP data
  323. *
  324. * @var string
  325. */
  326. private $_drawingGroupData;
  327. /**
  328. * The combined MSODRAWING data (per sheet)
  329. *
  330. * @var string
  331. */
  332. private $_drawingData;
  333. /**
  334. * Keep track of XF index
  335. *
  336. * @var int
  337. */
  338. private $_xfIndex;
  339. /**
  340. * Mapping of XF index (that is a cell XF) to final index in cellXf collection
  341. *
  342. * @var array
  343. */
  344. private $_mapCellXfIndex;
  345. /**
  346. * Mapping of XF index (that is a style XF) to final index in cellStyleXf collection
  347. *
  348. * @var array
  349. */
  350. private $_mapCellStyleXfIndex;
  351. /**
  352. * The shared formulas in a sheet. One SHAREDFMLA record contributes with one value.
  353. *
  354. * @var array
  355. */
  356. private $_sharedFormulas;
  357. /**
  358. * The shared formula parts in a sheet. One FORMULA record contributes with one value if it
  359. * refers to a shared formula.
  360. *
  361. * @var array
  362. */
  363. private $_sharedFormulaParts;
  364. /**
  365. * Read data only?
  366. * If this is true, then the Reader will only read data values for cells, it will not read any formatting information.
  367. * If false (the default) it will read data and formatting.
  368. *
  369. * @return boolean
  370. */
  371. public function getReadDataOnly()
  372. {
  373. return $this->_readDataOnly;
  374. }
  375. /**
  376. * Set read data only
  377. * Set to true, to advise the Reader only to read data values for cells, and to ignore any formatting information.
  378. * Set to false (the default) to advise the Reader to read both data and formatting for cells.
  379. *
  380. * @param boolean $pValue
  381. *
  382. * @return PHPExcel_Reader_Excel5
  383. */
  384. public function setReadDataOnly($pValue = false)
  385. {
  386. $this->_readDataOnly = $pValue;
  387. return $this;
  388. }
  389. /**
  390. * Get which sheets to load
  391. * Returns either an array of worksheet names (the list of worksheets that should be loaded), or a null
  392. * indicating that all worksheets in the workbook should be loaded.
  393. *
  394. * @return mixed
  395. */
  396. public function getLoadSheetsOnly()
  397. {
  398. return $this->_loadSheetsOnly;
  399. }
  400. /**
  401. * Set which sheets to load
  402. *
  403. * @param mixed $value
  404. * This should be either an array of worksheet names to be loaded, or a string containing a single worksheet name.
  405. * If NULL, then it tells the Reader to read all worksheets in the workbook
  406. *
  407. * @return PHPExcel_Reader_Excel5
  408. */
  409. public function setLoadSheetsOnly($value = null)
  410. {
  411. $this->_loadSheetsOnly = is_array($value) ? $value : array($value);
  412. return $this;
  413. }
  414. /**
  415. * Set all sheets to load
  416. * Tells the Reader to load all worksheets from the workbook.
  417. *
  418. * @return PHPExcel_Reader_Excel5
  419. */
  420. public function setLoadAllSheets()
  421. {
  422. $this->_loadSheetsOnly = null;
  423. return $this;
  424. }
  425. /**
  426. * Read filter
  427. *
  428. * @return PHPExcel_Reader_IReadFilter
  429. */
  430. public function getReadFilter()
  431. {
  432. return $this->_readFilter;
  433. }
  434. /**
  435. * Set read filter
  436. *
  437. * @param PHPExcel_Reader_IReadFilter $pValue
  438. * @return PHPExcel_Reader_Excel5
  439. */
  440. public function setReadFilter(PHPExcel_Reader_IReadFilter $pValue)
  441. {
  442. $this->_readFilter = $pValue;
  443. return $this;
  444. }
  445. /**
  446. * Create a new PHPExcel_Reader_Excel5 instance
  447. */
  448. public function __construct()
  449. {
  450. $this->_readFilter = new PHPExcel_Reader_DefaultReadFilter();
  451. }
  452. /**
  453. * Can the current PHPExcel_Reader_IReader read the file?
  454. *
  455. * @param string $pFileName
  456. * @return boolean
  457. */
  458. public function canRead($pFilename)
  459. {
  460. // Check if file exists
  461. if (! file_exists($pFilename))
  462. {
  463. throw new Exception("Could not open " . $pFilename . " for reading! File does not exist.");
  464. }
  465. try
  466. {
  467. // Use ParseXL for the hard work.
  468. $ole = new PHPExcel_Shared_OLERead();
  469. // get excel data
  470. $res = $ole->read($pFilename);
  471. return true;
  472. }
  473. catch (Exception $e)
  474. {
  475. return false;
  476. }
  477. }
  478. /**
  479. * Reads names of the worksheets from a file, without parsing the whole file to a PHPExcel object
  480. *
  481. * @param string $pFilename
  482. * @throws Exception
  483. */
  484. public function listWorksheetNames($pFilename)
  485. {
  486. // Check if file exists
  487. if (! file_exists($pFilename))
  488. {
  489. throw new Exception("Could not open " . $pFilename . " for reading! File does not exist.");
  490. }
  491. $worksheetNames = array();
  492. // Read the OLE file
  493. $this->_loadOLE($pFilename);
  494. // total byte size of Excel data (workbook global substream + sheet substreams)
  495. $this->_dataSize = strlen($this->_data);
  496. $this->_pos = 0;
  497. $this->_sheets = array();
  498. // Parse Workbook Global Substream
  499. while ($this->_pos < $this->_dataSize)
  500. {
  501. $code = self :: _GetInt2d($this->_data, $this->_pos);
  502. switch ($code)
  503. {
  504. case self :: XLS_Type_BOF :
  505. $this->_readBof();
  506. break;
  507. case self :: XLS_Type_SHEET :
  508. $this->_readSheet();
  509. break;
  510. case self :: XLS_Type_EOF :
  511. $this->_readDefault();
  512. break 2;
  513. default :
  514. $this->_readDefault();
  515. break;
  516. }
  517. }
  518. foreach ($this->_sheets as $sheet)
  519. {
  520. if ($sheet['sheetType'] != 0x00)
  521. {
  522. // 0x00: Worksheet, 0x02: Chart, 0x06: Visual Basic module
  523. continue;
  524. }
  525. $worksheetNames[] = $sheet['name'];
  526. }
  527. return $worksheetNames;
  528. }
  529. /**
  530. * Loads PHPExcel from file
  531. *
  532. * @param string $pFilename
  533. * @return PHPExcel
  534. * @throws Exception
  535. */
  536. public function load($pFilename)
  537. {
  538. // Read the OLE file
  539. $this->_loadOLE($pFilename);
  540. // Initialisations
  541. $this->_phpExcel = new PHPExcel();
  542. $this->_phpExcel->removeSheetByIndex(0); // remove 1st sheet
  543. if (! $this->_readDataOnly)
  544. {
  545. $this->_phpExcel->removeCellStyleXfByIndex(0); // remove the default style
  546. $this->_phpExcel->removeCellXfByIndex(0); // remove the default style
  547. }
  548. // Read the summary information stream (containing meta data)
  549. $this->_readSummaryInformation();
  550. // Read the Additional document summary information stream (containing application-specific meta data)
  551. $this->_readDocumentSummaryInformation();
  552. // total byte size of Excel data (workbook global substream + sheet substreams)
  553. $this->_dataSize = strlen($this->_data);
  554. // initialize
  555. $this->_pos = 0;
  556. $this->_codepage = 'CP1252';
  557. $this->_formats = array();
  558. $this->_objFonts = array();
  559. $this->_palette = array();
  560. $this->_sheets = array();
  561. $this->_externalBooks = array();
  562. $this->_ref = array();
  563. $this->_definedname = array();
  564. $this->_sst = array();
  565. $this->_drawingGroupData = '';
  566. $this->_xfIndex = '';
  567. $this->_mapCellXfIndex = array();
  568. $this->_mapCellStyleXfIndex = array();
  569. // Parse Workbook Global Substream
  570. while ($this->_pos < $this->_dataSize)
  571. {
  572. $code = self :: _GetInt2d($this->_data, $this->_pos);
  573. switch ($code)
  574. {
  575. case self :: XLS_Type_BOF :
  576. $this->_readBof();
  577. break;
  578. case self :: XLS_Type_FILEPASS :
  579. $this->_readFilepass();
  580. break;
  581. case self :: XLS_Type_CODEPAGE :
  582. $this->_readCodepage();
  583. break;
  584. case self :: XLS_Type_DATEMODE :
  585. $this->_readDateMode();
  586. break;
  587. case self :: XLS_Type_FONT :
  588. $this->_readFont();
  589. break;
  590. case self :: XLS_Type_FORMAT :
  591. $this->_readFormat();
  592. break;
  593. case self :: XLS_Type_XF :
  594. $this->_readXf();
  595. break;
  596. case self :: XLS_Type_XFEXT :
  597. $this->_readXfExt();
  598. break;
  599. case self :: XLS_Type_STYLE :
  600. $this->_readStyle();
  601. break;
  602. case self :: XLS_Type_PALETTE :
  603. $this->_readPalette();
  604. break;
  605. case self :: XLS_Type_SHEET :
  606. $this->_readSheet();
  607. break;
  608. case self :: XLS_Type_EXTERNALBOOK :
  609. $this->_readExternalBook();
  610. break;
  611. case self :: XLS_Type_EXTERNNAME :
  612. $this->_readExternName();
  613. break;
  614. case self :: XLS_Type_EXTERNSHEET :
  615. $this->_readExternSheet();
  616. break;
  617. case self :: XLS_Type_DEFINEDNAME :
  618. $this->_readDefinedName();
  619. break;
  620. case self :: XLS_Type_MSODRAWINGGROUP :
  621. $this->_readMsoDrawingGroup();
  622. break;
  623. case self :: XLS_Type_SST :
  624. $this->_readSst();
  625. break;
  626. case self :: XLS_Type_EOF :
  627. $this->_readDefault();
  628. break 2;
  629. default :
  630. $this->_readDefault();
  631. break;
  632. }
  633. }
  634. // Resolve indexed colors for font, fill, and border colors
  635. // Cannot be resolved already in XF record, because PALETTE record comes afterwards
  636. if (! $this->_readDataOnly)
  637. {
  638. foreach ($this->_objFonts as $objFont)
  639. {
  640. if (isset($objFont->colorIndex))
  641. {
  642. $color = self :: _readColor($objFont->colorIndex, $this->_palette, $this->_version);
  643. $objFont->getColor()->setRGB($color['rgb']);
  644. }
  645. }
  646. foreach ($this->_phpExcel->getCellXfCollection() as $objStyle)
  647. {
  648. // fill start and end color
  649. $fill = $objStyle->getFill();
  650. if (isset($fill->startcolorIndex))
  651. {
  652. $startColor = self :: _readColor($fill->startcolorIndex, $this->_palette, $this->_version);
  653. $fill->getStartColor()->setRGB($startColor['rgb']);
  654. }
  655. if (isset($fill->endcolorIndex))
  656. {
  657. $endColor = self :: _readColor($fill->endcolorIndex, $this->_palette, $this->_version);
  658. $fill->getEndColor()->setRGB($endColor['rgb']);
  659. }
  660. // border colors
  661. $top = $objStyle->getBorders()->getTop();
  662. $right = $objStyle->getBorders()->getRight();
  663. $bottom = $objStyle->getBorders()->getBottom();
  664. $left = $objStyle->getBorders()->getLeft();
  665. $diagonal = $objStyle->getBorders()->getDiagonal();
  666. if (isset($top->colorIndex))
  667. {
  668. $borderTopColor = self :: _readColor($top->colorIndex, $this->_palette, $this->_version);
  669. $top->getColor()->setRGB($borderTopColor['rgb']);
  670. }
  671. if (isset($right->colorIndex))
  672. {
  673. $borderRightColor = self :: _readColor($right->colorIndex, $this->_palette, $this->_version);
  674. $right->getColor()->setRGB($borderRightColor['rgb']);
  675. }
  676. if (isset($bottom->colorIndex))
  677. {
  678. $borderBottomColor = self :: _readColor($bottom->colorIndex, $this->_palette, $this->_version);
  679. $bottom->getColor()->setRGB($borderBottomColor['rgb']);
  680. }
  681. if (isset($left->colorIndex))
  682. {
  683. $borderLeftColor = self :: _readColor($left->colorIndex, $this->_palette, $this->_version);
  684. $left->getColor()->setRGB($borderLeftColor['rgb']);
  685. }
  686. if (isset($diagonal->colorIndex))
  687. {
  688. $borderDiagonalColor = self :: _readColor($diagonal->colorIndex, $this->_palette, $this->_version);
  689. $diagonal->getColor()->setRGB($borderDiagonalColor['rgb']);
  690. }
  691. }
  692. }
  693. // treat MSODRAWINGGROUP records, workbook-level Escher
  694. if (! $this->_readDataOnly && $this->_drawingGroupData)
  695. {
  696. $escherWorkbook = new PHPExcel_Shared_Escher();
  697. $reader = new PHPExcel_Reader_Excel5_Escher($escherWorkbook);
  698. $escherWorkbook = $reader->load($this->_drawingGroupData);
  699. // debug Escher stream
  700. //$debug = new Debug_Escher(new PHPExcel_Shared_Escher());
  701. //$debug->load($this->_drawingGroupData);
  702. }
  703. // Parse the individual sheets
  704. foreach ($this->_sheets as $sheet)
  705. {
  706. if ($sheet['sheetType'] != 0x00)
  707. {
  708. // 0x00: Worksheet, 0x02: Chart, 0x06: Visual Basic module
  709. continue;
  710. }
  711. // check if sheet should be skipped
  712. if (isset($this->_loadSheetsOnly) && ! in_array($sheet['name'], $this->_loadSheetsOnly))
  713. {
  714. continue;
  715. }
  716. // add sheet to PHPExcel object
  717. $this->_phpSheet = $this->_phpExcel->createSheet();
  718. $this->_phpSheet->setTitle($sheet['name']);
  719. $this->_phpSheet->setSheetState($sheet['sheetState']);
  720. $this->_pos = $sheet['offset'];
  721. // Initialize isFitToPages. May change after reading SHEETPR record.
  722. $this->_isFitToPages = false;
  723. // Initialize drawingData
  724. $this->_drawingData = '';
  725. // Initialize objs
  726. $this->_objs = array();
  727. // Initialize shared formula parts
  728. $this->_sharedFormulaParts = array();
  729. // Initialize shared formulas
  730. $this->_sharedFormulas = array();
  731. // Initialize text objs
  732. $this->_textObjects = array();
  733. // Initialize cell annotations
  734. $this->_cellNotes = array();
  735. $this->textObjRef = - 1;
  736. while ($this->_pos <= $this->_dataSize - 4)
  737. {
  738. $code = self :: _GetInt2d($this->_data, $this->_pos);
  739. switch ($code)
  740. {
  741. case self :: XLS_Type_BOF :
  742. $this->_readBof();
  743. break;
  744. case self :: XLS_Type_PRINTGRIDLINES :
  745. $this->_readPrintGridlines();
  746. break;
  747. case self :: XLS_Type_DEFAULTROWHEIGHT :
  748. $this->_readDefaultRowHeight();
  749. break;
  750. case self :: XLS_Type_SHEETPR :
  751. $this->_readSheetPr();
  752. break;
  753. case self :: XLS_Type_HORIZONTALPAGEBREAKS :
  754. $this->_readHorizontalPageBreaks();
  755. break;
  756. case self :: XLS_Type_VERTICALPAGEBREAKS :
  757. $this->_readVerticalPageBreaks();
  758. break;
  759. case self :: XLS_Type_HEADER :
  760. $this->_readHeader();
  761. break;
  762. case self :: XLS_Type_FOOTER :
  763. $this->_readFooter();
  764. break;
  765. case self :: XLS_Type_HCENTER :
  766. $this->_readHcenter();
  767. break;
  768. case self :: XLS_Type_VCENTER :
  769. $this->_readVcenter();
  770. break;
  771. case self :: XLS_Type_LEFTMARGIN :
  772. $this->_readLeftMargin();
  773. break;
  774. case self :: XLS_Type_RIGHTMARGIN :
  775. $this->_readRightMargin();
  776. break;
  777. case self :: XLS_Type_TOPMARGIN :
  778. $this->_readTopMargin();
  779. break;
  780. case self :: XLS_Type_BOTTOMMARGIN :
  781. $this->_readBottomMargin();
  782. break;
  783. case self :: XLS_Type_PAGESETUP :
  784. $this->_readPageSetup();
  785. break;
  786. case self :: XLS_Type_PROTECT :
  787. $this->_readProtect();
  788. break;
  789. case self :: XLS_Type_SCENPROTECT :
  790. $this->_readScenProtect();
  791. break;
  792. case self :: XLS_Type_OBJECTPROTECT :
  793. $this->_readObjectProtect();
  794. break;
  795. case self :: XLS_Type_PASSWORD :
  796. $this->_readPassword();
  797. break;
  798. case self :: XLS_Type_DEFCOLWIDTH :
  799. $this->_readDefColWidth();
  800. break;
  801. case self :: XLS_Type_COLINFO :
  802. $this->_readColInfo();
  803. break;
  804. case self :: XLS_Type_DIMENSION :
  805. $this->_readDefault();
  806. break;
  807. case self :: XLS_Type_ROW :
  808. $this->_readRow();
  809. break;
  810. case self :: XLS_Type_DBCELL :
  811. $this->_readDefault();
  812. break;
  813. case self :: XLS_Type_RK :
  814. $this->_readRk();
  815. break;
  816. case self :: XLS_Type_LABELSST :
  817. $this->_readLabelSst();
  818. break;
  819. case self :: XLS_Type_MULRK :
  820. $this->_readMulRk();
  821. break;
  822. case self :: XLS_Type_NUMBER :
  823. $this->_readNumber();
  824. break;
  825. case self :: XLS_Type_FORMULA :
  826. $this->_readFormula();
  827. break;
  828. case self :: XLS_Type_SHAREDFMLA :
  829. $this->_readSharedFmla();
  830. break;
  831. case self :: XLS_Type_BOOLERR :
  832. $this->_readBoolErr();
  833. break;
  834. case self :: XLS_Type_MULBLANK :
  835. $this->_readMulBlank();
  836. break;
  837. case self :: XLS_Type_LABEL :
  838. $this->_readLabel();
  839. break;
  840. case self :: XLS_Type_BLANK :
  841. $this->_readBlank();
  842. break;
  843. case self :: XLS_Type_MSODRAWING :
  844. $this->_readMsoDrawing();
  845. break;
  846. case self :: XLS_Type_OBJ :
  847. $this->_readObj();
  848. break;
  849. case self :: XLS_Type_WINDOW2 :
  850. $this->_readWindow2();
  851. break;
  852. case self :: XLS_Type_SCL :
  853. $this->_readScl();
  854. break;
  855. case self :: XLS_Type_PANE :
  856. $this->_readPane();
  857. break;
  858. case self :: XLS_Type_SELECTION :
  859. $this->_readSelection();
  860. break;
  861. case self :: XLS_Type_MERGEDCELLS :
  862. $this->_readMergedCells();
  863. break;
  864. case self :: XLS_Type_HYPERLINK :
  865. $this->_readHyperLink();
  866. break;
  867. case self :: XLS_Type_DATAVALIDATIONS :
  868. $this->_readDataValidations();
  869. break;
  870. case self :: XLS_Type_DATAVALIDATION :
  871. $this->_readDataValidation();
  872. break;
  873. case self :: XLS_Type_SHEETLAYOUT :
  874. $this->_readSheetLayout();
  875. break;
  876. case self :: XLS_Type_SHEETPROTECTION :
  877. $this->_readSheetProtection();
  878. break;
  879. case self :: XLS_Type_RANGEPROTECTION :
  880. $this->_readRangeProtection();
  881. break;
  882. case self :: XLS_Type_NOTE :
  883. $this->_readNote();
  884. break;
  885. //case self::XLS_Type_IMDATA: $this->_readImData(); break;
  886. case self :: XLS_Type_TXO :
  887. $this->_readTextObject();
  888. break;
  889. case self :: XLS_Type_CONTINUE :
  890. $this->_readContinue();
  891. break;
  892. case self :: XLS_Type_EOF :
  893. $this->_readDefault();
  894. break 2;
  895. default :
  896. $this->_readDefault();
  897. break;
  898. }
  899. }
  900. // treat MSODRAWING records, sheet-level Escher
  901. if (! $this->_readDataOnly && $this->_drawingData)
  902. {
  903. $escherWorksheet = new PHPExcel_Shared_Escher();
  904. $reader = new PHPExcel_Reader_Excel5_Escher($escherWorksheet);
  905. $escherWorksheet = $reader->load($this->_drawingData);
  906. // debug Escher stream
  907. //$debug = new Debug_Escher(new PHPExcel_Shared_Escher());
  908. //$debug->load($this->_drawingData);
  909. // get all spContainers in one long array, so they can be mapped to OBJ records
  910. $allSpContainers = $escherWorksheet->getDgContainer()->getSpgrContainer()->getAllSpContainers();
  911. }
  912. // treat OBJ records
  913. foreach ($this->_objs as $n => $obj)
  914. {
  915. // echo '<hr /><b>Object</b> reference is ',$n,'<br />';
  916. // var_dump($obj);
  917. // echo '<br />';
  918. // the first shape container never has a corresponding OBJ record, hence $n + 1
  919. $spContainer = $allSpContainers[$n + 1];
  920. // we skip all spContainers that are a part of a group shape since we cannot yet handle those
  921. if ($spContainer->getNestingLevel() > 1)
  922. {
  923. continue;
  924. }
  925. // calculate the width and height of the shape
  926. list($startColumn, $startRow) = PHPExcel_Cell :: coordinateFromString($spContainer->getStartCoordinates());
  927. list($endColumn, $endRow) = PHPExcel_Cell :: coordinateFromString($spContainer->getEndCoordinates());
  928. $startOffsetX = $spContainer->getStartOffsetX();
  929. $startOffsetY = $spContainer->getStartOffsetY();
  930. $endOffsetX = $spContainer->getEndOffsetX();
  931. $endOffsetY = $spContainer->getEndOffsetY();
  932. $width = PHPExcel_Shared_Excel5 :: getDistanceX($this->_phpSheet, $startColumn, $startOffsetX, $endColumn, $endOffsetX);
  933. $height = PHPExcel_Shared_Excel5 :: getDistanceY($this->_phpSheet, $startRow, $startOffsetY, $endRow, $endOffsetY);
  934. // calculate offsetX and offsetY of the shape
  935. $offsetX = $startOffsetX * PHPExcel_Shared_Excel5 :: sizeCol($this->_phpSheet, $startColumn) / 1024;
  936. $offsetY = $startOffsetY * PHPExcel_Shared_Excel5 :: sizeRow($this->_phpSheet, $startRow) / 256;
  937. switch ($obj['otObjType'])
  938. {
  939. case 0x19 :
  940. // Note
  941. // echo 'Cell Annotation Object<br />';
  942. // echo 'Object ID is ',$obj['idObjID'],'<br />';
  943. //
  944. if (isset($this->_cellNotes[$obj['idObjID']]))
  945. {
  946. $cellNote = $this->_cellNotes[$obj['idObjID']];
  947. // echo '_cellNotes[',$obj['idObjID'],']: ';
  948. // var_dump($cellNote);
  949. // echo '<br />';
  950. //
  951. if (isset($this->_textObjects[$obj['idObjID']]))
  952. {
  953. $textObject = $this->_textObjects[$obj['idObjID']];
  954. // echo '_textObject: ';
  955. // var_dump($textObject);
  956. // echo '<br />';
  957. //
  958. $this->_cellNotes[$obj['idObjID']]['objTextData'] = $textObject;
  959. $text = $textObject['text'];
  960. }
  961. // echo $text,'<br />';
  962. }
  963. break;
  964. case 0x08 :
  965. // echo 'Picture Object<br />';
  966. // picture
  967. // get index to BSE entry (1-based)
  968. $BSEindex = $spContainer->getOPT(0x0104);
  969. $BSECollection = $escherWorkbook->getDggContainer()->getBstoreContainer()->getBSECollection();
  970. $BSE = $BSECollection[$BSEindex - 1];
  971. $blipType = $BSE->getBlipType();
  972. // need check because some blip types are not supported by Escher reader such as EMF
  973. if ($blip = $BSE->getBlip())
  974. {
  975. $ih = imagecreatefromstring($blip->getData());
  976. $drawing = new PHPExcel_Worksheet_MemoryDrawing();
  977. $drawing->setImageResource($ih);
  978. // width, height, offsetX, offsetY
  979. $drawing->setResizeProportional(false);
  980. $drawing->setWidth($width);
  981. $drawing->setHeight($height);
  982. $drawing->setOffsetX($offsetX);
  983. $drawing->setOffsetY($offsetY);
  984. switch ($blipType)
  985. {
  986. case PHPExcel_Shared_Escher_DggContainer_BstoreContainer_BSE :: BLIPTYPE_JPEG :
  987. $drawing->setRenderingFunction(PHPExcel_Worksheet_MemoryDrawing :: RENDERING_JPEG);
  988. $drawing->setMimeType(PHPExcel_Worksheet_MemoryDrawing :: MIMETYPE_JPEG);
  989. break;
  990. case PHPExcel_Shared_Escher_DggContainer_BstoreContainer_BSE :: BLIPTYPE_PNG :
  991. $drawing->setRenderingFunction(PHPExcel_Worksheet_MemoryDrawing :: RENDERING_PNG);
  992. $drawing->setMimeType(PHPExcel_Worksheet_MemoryDrawing :: MIMETYPE_PNG);
  993. break;
  994. }
  995. $drawing->setWorksheet($this->_phpSheet);
  996. $drawing->setCoordinates($spContainer->getStartCoordinates());
  997. }
  998. break;
  999. default :
  1000. // other object type
  1001. break;
  1002. }
  1003. }
  1004. // treat SHAREDFMLA records
  1005. if ($this->_version == self :: XLS_BIFF8)
  1006. {
  1007. foreach ($this->_sharedFormulaParts as $cell => $baseCell)
  1008. {
  1009. list($column, $row) = PHPExcel_Cell :: coordinateFromString($cell);
  1010. if (! is_null($this->getReadFilter()) && $this->getReadFilter()->readCell($column, $row, $this->_phpSheet->getTitle()))
  1011. {
  1012. $formula = $this->_getFormulaFromStructure($this->_sharedFormulas[$baseCell], $cell);
  1013. $this->_phpSheet->getCell($cell)->setValueExplicit('=' . $formula, PHPExcel_Cell_DataType :: TYPE_FORMULA);
  1014. }
  1015. }
  1016. }
  1017. if (count($this->_cellNotes) > 0)
  1018. {
  1019. foreach ($this->_cellNotes as $note => $noteDetails)
  1020. {
  1021. // echo '<b>Cell annotation ',$note,'</b><br />';
  1022. // var_dump($noteDetails);
  1023. // echo '<br />';
  1024. $cellAddress = str_replace('$', '', $noteDetails['cellRef']);
  1025. $this->_phpSheet->getComment($cellAddress)->setAuthor($noteDetails['author'])->setText($this->_parseRichText($noteDetails['objTextData']['text']));
  1026. }
  1027. }
  1028. }
  1029. // add the named ranges (defined names)
  1030. foreach ($this->_definedname as $definedName)
  1031. {
  1032. if ($definedName['isBuiltInName'])
  1033. {
  1034. switch ($definedName['name'])
  1035. {
  1036. case pack('C', 0x06) :
  1037. // print area
  1038. // in general, formula looks like this: Foo!$C$7:$J$66,Bar!$A$1:$IV$2
  1039. $ranges = explode(',', $definedName['formula']); // FIXME: what if sheetname contains comma?
  1040. $extractedRanges = array();
  1041. foreach ($ranges as $range)
  1042. {
  1043. // $range should look like one of these
  1044. // Foo!$C$7:$J$66
  1045. // Bar!$A$1:$IV$2
  1046. $explodes = explode('!', $range); // FIXME: what if sheetname contains exclamation mark?
  1047. $sheetName = $explodes[0];
  1048. if (count($explodes) == 2)
  1049. {
  1050. $extractedRanges[] = str_replace('$', '', $explodes[1]); // C7:J66
  1051. }
  1052. }
  1053. if ($docSheet = $this->_phpExcel->getSheetByName($sheetName))
  1054. {
  1055. $docSheet->getPageSetup()->setPrintArea(implode(',', $extractedRanges)); // C7:J66,A1:IV2
  1056. }
  1057. break;
  1058. case pack('C', 0x07) :
  1059. // print titles (repeating rows)
  1060. // Assuming BIFF8, there are 3 cases
  1061. // 1. repeating rows
  1062. // formula looks like this: Sheet!$A$1:$IV$2
  1063. // rows 1-2 repeat
  1064. // 2. repeating columns
  1065. // formula looks like this: Sheet!$A$1:$B$65536
  1066. // columns A-B repeat
  1067. // 3. both repeating rows and repeating columns
  1068. // formula looks like this: Sheet!$A$1:$B$65536,Sheet!$A$1:$IV$2
  1069. $ranges = explode(',', $definedName['formula']); // FIXME: what if sheetname contains comma?
  1070. foreach ($ranges as $range)
  1071. {
  1072. // $range should look like this one of these
  1073. // Sheet!$A$1:$B$65536
  1074. // Sheet!$A$1:$IV$2
  1075. $explodes = explode('!', $range);
  1076. if (count($explodes) == 2)
  1077. {
  1078. if ($docSheet = $this->_phpExcel->getSheetByName($explodes[0]))
  1079. {
  1080. $extractedRange = $explodes[1];
  1081. $extractedRange = str_replace('$', '', $extractedRange);
  1082. $coordinateStrings = explode(':', $extractedRange);
  1083. if (count($coordinateStrings) == 2)
  1084. {
  1085. list($firstColumn, $firstRow) = PHPExcel_Cell :: coordinateFromString($coordinateStrings[0]);
  1086. list($lastColumn, $lastRow) = PHPExcel_Cell :: coordinateFromString($coordinateStrings[1]);
  1087. if ($firstColumn == 'A' and $lastColumn == 'IV')
  1088. {
  1089. // then we have repeating rows
  1090. $docSheet->getPageSetup()->setRowsToRepeatAtTop(array(
  1091. $firstRow, $lastRow));
  1092. }
  1093. elseif ($firstRow == 1 and $lastRow == 65536)
  1094. {
  1095. // then we have repeating columns
  1096. $docSheet->getPageSetup()->setColumnsToRepeatAtLeft(array(
  1097. $firstColumn, $lastColumn));
  1098. }
  1099. }
  1100. }
  1101. }
  1102. }
  1103. break;
  1104. }
  1105. }
  1106. else
  1107. {
  1108. // Extract range
  1109. $explodes = explode('!', $definedName['formula']);
  1110. if (count($explodes) == 2)
  1111. {
  1112. if ($docSheet = $this->_phpExcel->getSheetByName($explodes[0]))
  1113. {
  1114. $extractedRange = $explodes[1];
  1115. $extractedRange = str_replace('$', '', $extractedRange);
  1116. $localOnly = ($definedName['scope'] == 0) ? false : true;
  1117. $scope = ($definedName['scope'] == 0) ? null : $this->_phpExcel->getSheetByName($this->_sheets[$definedName['scope'] - 1]['name']);
  1118. $this->_phpExcel->addNamedRange(new PHPExcel_NamedRange((string) $definedName['name'], $docSheet, $extractedRange, $localOnly, $scope));
  1119. }
  1120. }
  1121. }
  1122. }
  1123. return $this->_phpExcel;
  1124. }
  1125. /**
  1126. * Use OLE reader to extract the relevant data streams from the OLE file
  1127. *
  1128. * @param string $pFilename
  1129. */
  1130. private function _loadOLE($pFilename)
  1131. {
  1132. // OLE reader
  1133. $ole = new PHPExcel_Shared_OLERead();
  1134. // get excel data,
  1135. $res = $ole->read($pFilename);
  1136. // Get workbook data: workbook stream + sheet streams
  1137. $this->_data = $ole->getStream($ole->wrkbook);
  1138. // Get summary information data
  1139. $this->_summaryInformation = $ole->getStream($ole->summaryInformation);
  1140. // Get additional document summary information data
  1141. $this->_documentSummaryInformation = $ole->getStream($ole->documentSummaryInformation);
  1142. // Get user-defined property data
  1143. // $this->_userDefinedProperties = $ole->getUserDefinedProperties();
  1144. }
  1145. /**
  1146. * Read summary information
  1147. */
  1148. private function _readSummaryInformation()
  1149. {
  1150. if (! isset($this->_summaryInformation))
  1151. {
  1152. return;
  1153. }
  1154. // offset: 0; size: 2; must be 0xFE 0xFF (UTF-16 LE byte order mark)
  1155. // offset: 2; size: 2;
  1156. // offset: 4; size: 2; OS version
  1157. // offset: 6; size: 2; OS indicator
  1158. // offset: 8; size: 16
  1159. // offset: 24; size: 4; section count
  1160. $secCount = self :: _GetInt4d($this->_summaryInformation, 24);
  1161. // 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
  1162. // offset: 44; size: 4
  1163. $secOffset = self :: _GetInt4d($this->_summaryInformation, 44);
  1164. // section header
  1165. // offset: $secOffset; size: 4; section length
  1166. $secLength = self :: _GetInt4d($this->_summaryInformation, $secOffset);
  1167. // offset: $secOffset+4; size: 4; property count
  1168. $countProperties = self :: _GetInt4d($this->_summaryInformation, $secOffset + 4);
  1169. // initialize code page (used to resolve string values)

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