File Source for Gnumeric.php

PHPExcel_Reader

# Source for file Gnumeric.php

Documentation is available at Gnumeric.php

1. <?php
2. /**
3. * PHPExcel
4. *
5. * Copyright (c) 2006 - 2011 PHPExcel
6. *
7. * This library is free software; you can redistribute it and/or
8. * modify it under the terms of the GNU Lesser General Public
9. * License as published by the Free Software Foundation; either
10. * version 2.1 of the License, or (at your option) any later version.
11. *
12. * This library is distributed in the hope that it will be useful,
13. * but WITHOUT ANY WARRANTY; without even the implied warranty of
14. * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
15. * Lesser General Public License for more details.
16. *
17. * You should have received a copy of the GNU Lesser General Public
18. * License along with this library; if not, write to the Free Software
19. * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
20. *
21. * @category PHPExcel
22. * @package PHPExcel_Reader
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. */
29. /** PHPExcel root directory */
30. if (! defined('PHPEXCEL_ROOT')) {
31. /**
32. * @ignore
33. */
34. define('PHPEXCEL_ROOT', dirname(__FILE__) . '/../../');
35. require(PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php');
36. }
38. /**
39. * PHPExcel_Reader_Gnumeric
40. *
41. * @category PHPExcel
42. * @package PHPExcel_Reader
43. * @copyright Copyright (c) 2006 - 2011 PHPExcel (http://www.codeplex.com/PHPExcel)
44. */
45. class PHPExcel_Reader_Gnumeric implements PHPExcel_Reader_IReader
46. {
47. /**
48. * Read data only?
49. * Identifies whether the Reader should only read data values for cells, and ignore any formatting information;
50. * or whether it should read both data and formatting
51. *
52. * @var boolean
53. */
54. private $_readDataOnly = false;
56. /**
57. * Restrict which sheets should be loaded?
58. * This property holds an array of worksheet names to be loaded. If null, then all worksheets will be loaded.
59. *
60. * @var array of string
61. */
62. private $_loadSheetsOnly = null;
64. /**
65. * Formats
66. *
67. * @var array
68. */
69. private $_styles = array();
71. /**
72. * Shared Expressions
73. *
74. * @var array
75. */
76. private $_expressions = array();
78. private $_referenceHelper = null;
80. /**
81. * PHPExcel_Reader_IReadFilter instance
82. *
83. * @var PHPExcel_Reader_IReadFilter
84. */
85. private $_readFilter = null;
88. /**
89. * Read data only?
90. * If this is true, then the Reader will only read data values for cells, it will not read any formatting information.
91. * If false (the default) it will read data and formatting.
92. *
93. * @return boolean
94. */
95. public function getReadDataOnly() {
96. return $this->_readDataOnly;
97. }
99. /**
100. * Set read data only
101. * Set to true, to advise the Reader only to read data values for cells, and to ignore any formatting information.
102. * Set to false (the default) to advise the Reader to read both data and formatting for cells.
103. *
104. * @param boolean $pValue
105. *
106. * @return PHPExcel_Reader_Gnumeric
107. */
108. public function setReadDataOnly($pValue = false) {
109. $this->_readDataOnly = $pValue;
110. return $this;
111. }
113. /**
114. * Get which sheets to load
115. * Returns either an array of worksheet names (the list of worksheets that should be loaded), or a null
116. * indicating that all worksheets in the workbook should be loaded.
117. *
118. * @return mixed
119. */
120. public function getLoadSheetsOnly()
121. {
122. return $this->_loadSheetsOnly;
123. }
125. /**
126. * Set which sheets to load
127. *
128. * @param mixed $value
129. * This should be either an array of worksheet names to be loaded, or a string containing a single worksheet name.
130. * If NULL, then it tells the Reader to read all worksheets in the workbook
131. *
132. * @return PHPExcel_Reader_Gnumeric
133. */
134. public function setLoadSheetsOnly($value = null)
135. {
136. $this->_loadSheetsOnly = is_array($value) ?
137. $value : array($value);
138. return $this;
139. }
141. /**
142. * Set all sheets to load
143. * Tells the Reader to load all worksheets from the workbook.
144. *
145. * @return PHPExcel_Reader_Gnumeric
146. */
147. public function setLoadAllSheets()
148. {
149. $this->_loadSheetsOnly = null;
150. return $this;
151. }
153. /**
154. * Read filter
155. *
156. * @return PHPExcel_Reader_IReadFilter
157. */
158. public function getReadFilter() {
159. return $this->_readFilter;
160. }
162. /**
163. * Set read filter
164. *
165. * @param PHPExcel_Reader_IReadFilter $pValue
166. * @return PHPExcel_Reader_Gnumeric
167. */
168. public function setReadFilter(PHPExcel_Reader_IReadFilter $pValue) {
169. $this->_readFilter = $pValue;
170. return $this;
171. }
173. /**
174. * Create a new PHPExcel_Reader_Gnumeric
175. */
176. public function __construct() {
177. $this->_readFilter = new PHPExcel_Reader_DefaultReadFilter();
178. $this->_referenceHelper = PHPExcel_ReferenceHelper:: getInstance();
179. }
181. /**
182. * Can the current PHPExcel_Reader_IReader read the file?
183. *
184. * @param string $pFileName
185. * @return boolean
186. */
187. public function canRead($pFilename)
188. {
189. // Check if gzlib functions are available
190. if (! function_exists('gzread')) {
191. return false;
192. }
194. // Check if file exists
195. if (! file_exists($pFilename)) {
196. throw new Exception("Could not open " . $pFilename . " for reading! File does not exist.");
197. }
199. // Read signature data (first 3 bytes)
200. $fh = fopen($pFilename, 'r');
201. $data = fread($fh, 2);
202. fclose($fh);
204. if ($data != chr(0x1F).chr(0x8B)) {
205. return false;
206. }
208. return true;
209. }
211. /**
212. * Loads PHPExcel from file
213. *
214. * @param string $pFilename
215. * @return PHPExcel
216. * @throws Exception
217. */
218. public function load($pFilename)
219. {
220. // Create new PHPExcel
221. $objPHPExcel = new PHPExcel();
223. // Load into this instance
224. return $this-> loadIntoExisting($pFilename, $objPHPExcel);
225. }
227. private function _gzfileGetContents($filename) {
228. $file = @ gzopen($filename, 'rb');
229. if ($file !== false) {
230. $data = '';
231. while (! gzeof($file)) {
232. $data .= gzread($file, 1024);
233. }
234. gzclose($file);
235. }
236. return $data;
237. }
239. /**
240. * Reads names of the worksheets from a file, without parsing the whole file to a PHPExcel object
241. *
242. * @param string $pFilename
243. * @throws Exception
244. */
245. public function listWorksheetNames($pFilename)
246. {
247. // Check if file exists
248. if (! file_exists($pFilename)) {
249. throw new Exception("Could not open " . $pFilename . " for reading! File does not exist.");
250. }
252. $gFileData = $this->_gzfileGetContents($pFilename);
254. $xml = simplexml_load_string($gFileData);
255. $namespacesMeta = $xml->getNamespaces(true);
257. $gnmXML = $xml->children($namespacesMeta['gnm']);
259. $worksheetNames = array();
261. foreach($gnmXML->Sheets->Sheet as $sheet) {
262. $worksheetNames[] = (string) $sheet->Name;
263. }
265. return $worksheetNames;
266. }
269. /**
270. * Loads PHPExcel from file into PHPExcel instance
271. *
272. * @param string $pFilename
273. * @param PHPExcel $objPHPExcel
274. * @return PHPExcel
275. * @throws Exception
276. */
277. public function loadIntoExisting($pFilename, PHPExcel $objPHPExcel)
278. {
279. // Check if file exists
280. if (! file_exists($pFilename)) {
281. throw new Exception("Could not open " . $pFilename . " for reading! File does not exist.");
282. }
284. $timezoneObj = new DateTimeZone('Europe/London');
285. $GMT = new DateTimeZone('UTC');
287. $gFileData = $this->_gzfileGetContents($pFilename);
289. // echo '<pre>';
290. // echo htmlentities($gFileData,ENT_QUOTES,'UTF-8');
291. // echo '</pre><hr />';
292. //
293. $xml = simplexml_load_string($gFileData);
294. $namespacesMeta = $xml->getNamespaces(true);
296. // var_dump($namespacesMeta);
297. //
298. $gnmXML = $xml->children($namespacesMeta['gnm']);
300. $docProps = $objPHPExcel->getProperties();
301. // Document Properties are held differently, depending on the version of Gnumeric
302. if (isset($namespacesMeta['office'])) {
303. $officeXML = $xml->children($namespacesMeta['office']);
304. $officeDocXML = $officeXML->{'document-meta'};
305. $officeDocMetaXML = $officeDocXML->meta;
307. foreach($officeDocMetaXML as $officePropertyData) {
309. $officePropertyDC = array();
310. if (isset($namespacesMeta['dc'])) {
311. $officePropertyDC = $officePropertyData->children($namespacesMeta['dc']);
312. }
313. foreach($officePropertyDC as $propertyName => $propertyValue) {
314. $propertyValue = (string) $propertyValue;
315. switch ($propertyName) {
316. case 'title' :
317. $docProps->setTitle( trim($propertyValue));
318. break;
319. case 'subject' :
320. $docProps->setSubject( trim($propertyValue));
321. break;
322. case 'creator' :
323. $docProps->setCreator( trim($propertyValue));
324. $docProps->setLastModifiedBy( trim($propertyValue));
325. break;
326. case 'date' :
327. $creationDate = strtotime( trim($propertyValue));
328. $docProps->setCreated($creationDate);
329. $docProps->setModified($creationDate);
330. break;
331. case 'description' :
332. $docProps->setDescription( trim($propertyValue));
333. break;
334. }
335. }
336. $officePropertyMeta = array();
337. if (isset($namespacesMeta['meta'])) {
338. $officePropertyMeta = $officePropertyData->children($namespacesMeta['meta']);
339. }
340. foreach($officePropertyMeta as $propertyName => $propertyValue) {
341. $attributes = $propertyValue->attributes($namespacesMeta['meta']);
342. $propertyValue = (string) $propertyValue;
343. switch ($propertyName) {
344. case 'keyword' :
345. $docProps->setKeywords( trim($propertyValue));
346. break;
347. case 'initial-creator' :
348. $docProps->setCreator( trim($propertyValue));
349. $docProps->setLastModifiedBy( trim($propertyValue));
350. break;
351. case 'creation-date' :
352. $creationDate = strtotime( trim($propertyValue));
353. $docProps->setCreated($creationDate);
354. $docProps->setModified($creationDate);
355. break;
356. case 'user-defined' :
357. list(,$attrName) = explode(':',$attributes['name']);
358. switch ($attrName) {
359. case 'publisher' :
360. $docProps->setCompany( trim($propertyValue));
361. break;
362. case 'category' :
363. $docProps->setCategory( trim($propertyValue));
364. break;
365. case 'manager' :
366. $docProps->setManager( trim($propertyValue));
367. break;
368. }
369. break;
370. }
371. }
372. }
373. } elseif (isset($gnmXML->Summary)) {
374. foreach($gnmXML->Summary->Item as $summaryItem) {
375. $propertyName = $summaryItem->name;
376. $propertyValue = $summaryItem->{'val-string'};
377. switch ($propertyName) {
378. case 'title' :
379. $docProps->setTitle( trim($propertyValue));
380. break;
381. case 'comments' :
382. $docProps->setDescription( trim($propertyValue));
383. break;
384. case 'keywords' :
385. $docProps->setKeywords( trim($propertyValue));
386. break;
387. case 'category' :
388. $docProps->setCategory( trim($propertyValue));
389. break;
390. case 'manager' :
391. $docProps->setManager( trim($propertyValue));
392. break;
393. case 'author' :
394. $docProps->setCreator( trim($propertyValue));
395. $docProps->setLastModifiedBy( trim($propertyValue));
396. break;
397. case 'company' :
398. $docProps->setCompany( trim($propertyValue));
399. break;
400. }
401. }
402. }
404. $worksheetID = 0;
405. foreach($gnmXML->Sheets->Sheet as $sheet) {
406. $worksheetName = (string) $sheet->Name;
407. // echo '<b>Worksheet: ',$worksheetName,'</b><br />';
408. if ((isset($this->_loadSheetsOnly)) && (! in_array($worksheetName, $this->_loadSheetsOnly))) {
409. continue;
410. }
412. $maxRow = $maxCol = 0;
414. // Create new Worksheet
415. $objPHPExcel->createSheet();
416. $objPHPExcel->setActiveSheetIndex($worksheetID);
417. $objPHPExcel->getActiveSheet()->setTitle($worksheetName);
419. if ((!$this->_readDataOnly) && (isset($sheet->PrintInformation))) {
420. if (isset($sheet->PrintInformation->Margins)) {
421. foreach($sheet->PrintInformation->Margins->children('gnm',TRUE) as $key => $margin) {
422. $marginAttributes = $margin->attributes();
423. $marginSize = 72 / 100; // Default
424. switch($marginAttributes['PrefUnit']) {
425. case 'mm' :
426. $marginSize = intval($marginAttributes['Points']) / 100;
427. break;
428. }
429. switch($key) {
430. case 'top' :
431. $objPHPExcel->getActiveSheet()->getPageMargins()->setTop($marginSize);
432. break;
433. case 'bottom' :
434. $objPHPExcel->getActiveSheet()->getPageMargins()->setBottom($marginSize);
435. break;
436. case 'left' :
437. $objPHPExcel->getActiveSheet()->getPageMargins()->setLeft($marginSize);
438. break;
439. case 'right' :
440. $objPHPExcel->getActiveSheet()->getPageMargins()->setRight($marginSize);
441. break;
442. case 'header' :
443. $objPHPExcel->getActiveSheet()->getPageMargins()->setHeader($marginSize);
444. break;
445. case 'footer' :
446. $objPHPExcel->getActiveSheet()->getPageMargins()->setFooter($marginSize);
447. break;
448. }
449. }
450. }
451. }
453. foreach($sheet->Cells->Cell as $cell) {
454. $cellAttributes = $cell->attributes();
455. $row = (int) $cellAttributes->Row + 1;
456. $column = (int) $cellAttributes->Col;
458. if ($row > $maxRow) $maxRow = $row;
459. if ($column > $maxCol) $maxCol = $column;
461. $column = PHPExcel_Cell:: stringFromColumnIndex($column);
463. // Read cell?
464. if (! is_null($this-> getReadFilter())) {
465. if (!$this-> getReadFilter()->readCell($column, $row, $worksheetName)) {
466. continue;
467. }
468. }
470. $ValueType = $cellAttributes->ValueType;
471. $ExprID = (string) $cellAttributes->ExprID;
472. // echo 'Cell ',$column,$row,'<br />';
473. // echo 'Type is ',$ValueType,'<br />';
474. // echo 'Value is ',$cell,'<br />';
475. $type = PHPExcel_Cell_DataType:: TYPE_FORMULA;
476. if ($ExprID > '') {
477. if (((string) $cell) > '') {
479. $this->_expressions[$ExprID] = array( 'column' => $cellAttributes->Col,
480. 'row' => $cellAttributes->Row,
481. 'formula' => (string) $cell
482. );
483. // echo 'NEW EXPRESSION ',$ExprID,'<br />';
484. } else {
485. $expression = $this->_expressions[$ExprID];
487. $cell = $this->_referenceHelper->updateFormulaReferences( $expression['formula'],
488. 'A1',
489. $cellAttributes->Col - $expression['column'],
490. $cellAttributes->Row - $expression['row'],
491. $worksheetName
492. );
493. // echo 'SHARED EXPRESSION ',$ExprID,'<br />';
494. // echo 'New Value is ',$cell,'<br />';
495. }
496. $type = PHPExcel_Cell_DataType:: TYPE_FORMULA;
497. } else {
498. switch($ValueType) {
499. case '10' : // NULL
500. $type = PHPExcel_Cell_DataType:: TYPE_NULL;
501. break;
502. case '20' : // Boolean
503. $type = PHPExcel_Cell_DataType:: TYPE_BOOL;
504. $cell = ($cell == 'TRUE') ? True : False;
505. break;
506. case '30' : // Integer
507. $cell = intval($cell);
508. case '40' : // Float
509. $type = PHPExcel_Cell_DataType:: TYPE_NUMERIC;
510. break;
511. case '50' : // Error
512. $type = PHPExcel_Cell_DataType:: TYPE_ERROR;
513. break;
514. case '60' : // String
515. $type = PHPExcel_Cell_DataType:: TYPE_STRING;
516. break;
517. case '70' : // Cell Range
518. case '80' : // Array
519. }
520. }
521. $objPHPExcel->getActiveSheet()->getCell($column.$row)->setValueExplicit($cell,$type);
522. }
524. if ((!$this->_readDataOnly) && (isset($sheet->Objects))) {
525. foreach($sheet->Objects->children('gnm',TRUE) as $key => $comment) {
526. $commentAttributes = $comment->attributes();
527. // Only comment objects are handled at the moment
528. if ($commentAttributes->Text) {
529. $objPHPExcel->getActiveSheet()->getComment( (string)$commentAttributes->ObjectBound )
530. ->setAuthor( (string)$commentAttributes->Author )
531. ->setText($this->_parseRichText((string)$commentAttributes->Text) );
532. }
533. }
534. }
535. // echo '$maxCol=',$maxCol,'; $maxRow=',$maxRow,'<br />';
536. //
537. foreach($sheet->Styles->StyleRegion as $styleRegion) {
538. $styleAttributes = $styleRegion->attributes();
539. // var_dump($styleAttributes);
540. // echo '<br />';
542. if (($styleAttributes['startRow'] <= $maxRow) &&
543. ($styleAttributes['startCol'] <= $maxCol)) {
545. $startColumn = PHPExcel_Cell:: stringFromColumnIndex($styleAttributes['startCol']);
546. $startRow = $styleAttributes['startRow'] + 1;
548. $endColumn = ($styleAttributes['endCol'] > $maxCol) ? $maxCol : $styleAttributes['endCol'];
549. $endColumn = PHPExcel_Cell:: stringFromColumnIndex($endColumn);
550. $endRow = ($styleAttributes['endRow'] > $maxRow) ? $maxRow : $styleAttributes['endRow'];
551. $endRow += 1;
552. $cellRange = $startColumn.$startRow.':'.$endColumn.$endRow;
553. // echo $cellRange,'<br />';
555. $styleAttributes = $styleRegion->Style->attributes();
556. // var_dump($styleAttributes);
557. // echo '<br />';
559. // We still set the number format mask for date/time values, even if _readDataOnly is true
560. if ((!$this->_readDataOnly) ||
561. ( PHPExcel_Shared_Date:: isDateTimeFormatCode($styleArray['numberformat']['code']))) {
562. $styleArray = array();
563. $styleArray['numberformat']['code'] = (string) $styleAttributes['Format'];
564. // If _readDataOnly is false, we set all formatting information
565. if (!$this->_readDataOnly) {
566. switch($styleAttributes['HAlign']) {
567. case '1' :
568. $styleArray['alignment']['horizontal'] = PHPExcel_Style_Alignment:: HORIZONTAL_GENERAL;
569. break;
570. case '2' :
571. $styleArray['alignment']['horizontal'] = PHPExcel_Style_Alignment:: HORIZONTAL_LEFT;
572. break;
573. case '4' :
574. $styleArray['alignment']['horizontal'] = PHPExcel_Style_Alignment:: HORIZONTAL_RIGHT;
575. break;
576. case '8' :
577. $styleArray['alignment']['horizontal'] = PHPExcel_Style_Alignment:: HORIZONTAL_CENTER;
578. break;
579. case '16' :
580. case '64' :
581. $styleArray['alignment']['horizontal'] = PHPExcel_Style_Alignment:: HORIZONTAL_CENTER_CONTINUOUS;
582. break;
583. case '32' :
584. $styleArray['alignment']['horizontal'] = PHPExcel_Style_Alignment:: HORIZONTAL_JUSTIFY;
585. break;
586. }
588. switch($styleAttributes['VAlign']) {
589. case '1' :
590. $styleArray['alignment']['vertical'] = PHPExcel_Style_Alignment:: VERTICAL_TOP;
591. break;
592. case '2' :
593. $styleArray['alignment']['vertical'] = PHPExcel_Style_Alignment:: VERTICAL_BOTTOM;
594. break;
595. case '4' :
596. $styleArray['alignment']['vertical'] = PHPExcel_Style_Alignment:: VERTICAL_CENTER;
597. break;
598. case '8' :
599. $styleArray['alignment']['vertical'] = PHPExcel_Style_Alignment:: VERTICAL_JUSTIFY;
600. break;
601. }
603. $styleArray['alignment']['wrap'] = ($styleAttributes['WrapText'] == '1') ? True : False;
604. $styleArray['alignment']['shrinkToFit'] = ($styleAttributes['ShrinkToFit'] == '1') ? True : False;
605. $styleArray['alignment']['indent'] = ( intval($styleAttributes["Indent"]) > 0) ? $styleAttributes["indent"] : 0;
607. $RGB = self::_parseGnumericColour($styleAttributes["Fore"]);
608. $styleArray['font']['color']['rgb'] = $RGB;
609. $RGB = self::_parseGnumericColour($styleAttributes["Back"]);
610. $shade = $styleAttributes["Shade"];
611. if (($RGB != '000000') || ($shade != '0')) {
612. $styleArray['fill']['color']['rgb'] = $styleArray['fill']['startcolor']['rgb'] = $RGB;
613. $RGB2 = self::_parseGnumericColour($styleAttributes["PatternColor"]);
614. $styleArray['fill']['endcolor']['rgb'] = $RGB2;
615. switch($shade) {
616. case '1' :
617. $styleArray['fill']['type'] = PHPExcel_Style_Fill:: FILL_SOLID;
618. break;
619. case '2' :
620. $styleArray['fill']['type'] = PHPExcel_Style_Fill:: FILL_GRADIENT_LINEAR;
621. break;
622. case '3' :
623. $styleArray['fill']['type'] = PHPExcel_Style_Fill:: FILL_GRADIENT_PATH;
624. break;
625. case '4' :
626. $styleArray['fill']['type'] = PHPExcel_Style_Fill:: FILL_PATTERN_DARKDOWN;
627. break;
628. case '5' :
629. $styleArray['fill']['type'] = PHPExcel_Style_Fill:: FILL_PATTERN_DARKGRAY;
630. break;
631. case '6' :
632. $styleArray['fill']['type'] = PHPExcel_Style_Fill:: FILL_PATTERN_DARKGRID;
633. break;
634. case '7' :
635. $styleArray['fill']['type'] = PHPExcel_Style_Fill:: FILL_PATTERN_DARKHORIZONTAL;
636. break;
637. case '8' :
638. $styleArray['fill']['type'] = PHPExcel_Style_Fill:: FILL_PATTERN_DARKTRELLIS;
639. break;
640. case '9' :
641. $styleArray['fill']['type'] = PHPExcel_Style_Fill:: FILL_PATTERN_DARKUP;
642. break;
643. case '10' :
644. $styleArray['fill']['type'] = PHPExcel_Style_Fill:: FILL_PATTERN_DARKVERTICAL;
645. break;
646. case '11' :
647. $styleArray['fill']['type'] = PHPExcel_Style_Fill:: FILL_PATTERN_GRAY0625;
648. break;
649. case '12' :
650. $styleArray['fill']['type'] = PHPExcel_Style_Fill:: FILL_PATTERN_GRAY125;
651. break;
652. case '13' :
653. $styleArray['fill']['type'] = PHPExcel_Style_Fill:: FILL_PATTERN_LIGHTDOWN;
654. break;
655. case '14' :
656. $styleArray['fill']['type'] = PHPExcel_Style_Fill:: FILL_PATTERN_LIGHTGRAY;
657. break;
658. case '15' :
659. $styleArray['fill']['type'] = PHPExcel_Style_Fill:: FILL_PATTERN_LIGHTGRID;
660. break;
661. case '16' :
662. $styleArray['fill']['type'] = PHPExcel_Style_Fill:: FILL_PATTERN_LIGHTHORIZONTAL;
663. break;
664. case '17' :
665. $styleArray['fill']['type'] = PHPExcel_Style_Fill:: FILL_PATTERN_LIGHTTRELLIS;
666. break;
667. case '18' :
668. $styleArray['fill']['type'] = PHPExcel_Style_Fill:: FILL_PATTERN_LIGHTUP;
669. break;
670. case '19' :
671. $styleArray['fill']['type'] = PHPExcel_Style_Fill:: FILL_PATTERN_LIGHTVERTICAL;
672. break;
673. case '20' :
674. $styleArray['fill']['type'] = PHPExcel_Style_Fill:: FILL_PATTERN_MEDIUMGRAY;
675. break;
676. }
677. }
679. $fontAttributes = $styleRegion->Style->Font->attributes();
680. // var_dump($fontAttributes);
681. // echo '<br />';
682. $styleArray['font']['name'] = (string) $styleRegion->Style->Font;
683. $styleArray['font']['size'] = intval($fontAttributes['Unit']);
684. $styleArray['font']['bold'] = ($fontAttributes['Bold'] == '1') ? True : False;
685. $styleArray['font']['italic'] = ($fontAttributes['Italic'] == '1') ? True : False;
686. $styleArray['font']['strike'] = ($fontAttributes['StrikeThrough'] == '1') ? True : False;
687. switch($fontAttributes['Underline']) {
688. case '1' :
689. $styleArray['font']['underline'] = PHPExcel_Style_Font:: UNDERLINE_SINGLE;
690. break;
691. case '2' :
692. $styleArray['font']['underline'] = PHPExcel_Style_Font:: UNDERLINE_DOUBLE;
693. break;
694. case '3' :
695. $styleArray['font']['underline'] = PHPExcel_Style_Font:: UNDERLINE_SINGLEACCOUNTING;
696. break;
697. case '4' :
698. $styleArray['font']['underline'] = PHPExcel_Style_Font:: UNDERLINE_DOUBLEACCOUNTING;
699. break;
700. default :
701. $styleArray['font']['underline'] = PHPExcel_Style_Font:: UNDERLINE_NONE;
702. break;
703. }
704. switch($fontAttributes['Script']) {
705. case '1' :
706. $styleArray['font']['superScript'] = True;
707. break;
708. case '-1' :
709. $styleArray['font']['subScript'] = True;
710. break;
711. }
713. if (isset($styleRegion->Style->StyleBorder)) {
714. if (isset($styleRegion->Style->StyleBorder->Top)) {
715. $styleArray['borders']['top'] = self::_parseBorderAttributes($styleRegion->Style->StyleBorder->Top->attributes());
716. }
717. if (isset($styleRegion->Style->StyleBorder->Bottom)) {
718. $styleArray['borders']['bottom'] = self::_parseBorderAttributes($styleRegion->Style->StyleBorder->Bottom->attributes());
719. }
720. if (isset($styleRegion->Style->StyleBorder->Left)) {
721. $styleArray['borders']['left'] = self::_parseBorderAttributes($styleRegion->Style->StyleBorder->Left->attributes());
722. }
723. if (isset($styleRegion->Style->StyleBorder->Right)) {
724. $styleArray['borders']['right'] = self::_parseBorderAttributes($styleRegion->Style->StyleBorder->Right->attributes());
725. }
726. if ((isset($styleRegion->Style->StyleBorder->Diagonal)) && (isset($styleRegion->Style->StyleBorder->{'Rev-Diagonal'}))) {
727. $styleArray['borders']['diagonal'] = self::_parseBorderAttributes($styleRegion->Style->StyleBorder->Diagonal->attributes());
728. $styleArray['borders']['diagonaldirection'] = PHPExcel_Style_Borders:: DIAGONAL_BOTH;
729. } elseif (isset($styleRegion->Style->StyleBorder->Diagonal)) {
730. $styleArray['borders']['diagonal'] = self::_parseBorderAttributes($styleRegion->Style->StyleBorder->Diagonal->attributes());
731. $styleArray['borders']['diagonaldirection'] = PHPExcel_Style_Borders:: DIAGONAL_UP;
732. } elseif (isset($styleRegion->Style->StyleBorder->{'Rev-Diagonal'})) {
733. $styleArray['borders']['diagonal'] = self::_parseBorderAttributes($styleRegion->Style->StyleBorder->{'Rev-Diagonal'}->attributes());
734. $styleArray['borders']['diagonaldirection'] = PHPExcel_Style_Borders:: DIAGONAL_DOWN;
735. }
736. }
737. if (isset($styleRegion->Style->HyperLink)) {
738. // TO DO
739. $hyperlink = $styleRegion->Style->HyperLink->attributes();
740. }
741. }
742. // var_dump($styleArray);
743. // echo '<br />';
744. $objPHPExcel->getActiveSheet()->getStyle($cellRange)->applyFromArray($styleArray);
745. }
746. }
747. }
749. if ((!$this->_readDataOnly) && (isset($sheet->Cols))) {
750. // Column Widths
751. $columnAttributes = $sheet->Cols->attributes();
752. $defaultWidth = $columnAttributes['DefaultSizePts'] / 5.4;
753. $c = 0;
754. foreach($sheet->Cols->ColInfo as $columnOverride) {
755. $columnAttributes = $columnOverride->attributes();
756. $column = $columnAttributes['No'];
757. $columnWidth = $columnAttributes['Unit'] / 5.4;
758. $hidden = ((isset($columnAttributes['Hidden'])) && ($columnAttributes['Hidden'] == '1')) ? true : false;
759. $columnCount = (isset($columnAttributes['Count'])) ? $columnAttributes['Count'] : 1;
760. while ($c < $column) {
761. $objPHPExcel->getActiveSheet()->getColumnDimension( PHPExcel_Cell:: stringFromColumnIndex($c))->setWidth($defaultWidth);
762. ++$c;
763. }
764. while (($c < ($column+$columnCount)) && ($c <= $maxCol)) {
765. $objPHPExcel->getActiveSheet()->getColumnDimension( PHPExcel_Cell:: stringFromColumnIndex($c))->setWidth($columnWidth);
766. if ($hidden) {
767. $objPHPExcel->getActiveSheet()->getColumnDimension( PHPExcel_Cell:: stringFromColumnIndex($c))->setVisible(false);
768. }
769. ++$c;
770. }
771. }
772. while ($c <= $maxCol) {
773. $objPHPExcel->getActiveSheet()->getColumnDimension( PHPExcel_Cell:: stringFromColumnIndex($c))->setWidth($defaultWidth);
774. ++$c;
775. }
776. }
778. if ((!$this->_readDataOnly) && (isset($sheet->Rows))) {
779. // Row Heights
780. $rowAttributes = $sheet->Rows->attributes();
781. $defaultHeight = $rowAttributes['DefaultSizePts'];
782. $r = 0;
784. foreach($sheet->Rows->RowInfo as $rowOverride) {
785. $rowAttributes = $rowOverride->attributes();
786. $row = $rowAttributes['No'];
787. $rowHeight = $rowAttributes['Unit'];
788. $hidden = ((isset($rowAttributes['Hidden'])) && ($rowAttributes['Hidden'] == '1')) ? true : false;
789. $rowCount = (isset($rowAttributes['Count'])) ? $rowAttributes['Count'] : 1;
790. while ($r < $row) {
791. ++$r;
792. $objPHPExcel->getActiveSheet()->getRowDimension($r)->setRowHeight($defaultHeight);
793. }
794. while (($r < ($row+$rowCount)) && ($r < $maxRow)) {
795. ++$r;
796. $objPHPExcel->getActiveSheet()->getRowDimension($r)->setRowHeight($rowHeight);
797. if ($hidden) {
798. $objPHPExcel->getActiveSheet()->getRowDimension($r)->setVisible(false);
799. }
800. }
801. }
802. while ($r < $maxRow) {
803. ++$r;
804. $objPHPExcel->getActiveSheet()->getRowDimension($r)->setRowHeight($defaultHeight);
805. }
806. }
808. // Handle Merged Cells in this worksheet
809. if (isset($sheet->MergedRegions)) {
810. foreach($sheet->MergedRegions->Merge as $mergeCells) {
811. $objPHPExcel->getActiveSheet()->mergeCells($mergeCells);
812. }
813. }
815. $worksheetID++;
816. }
818. // Loop through definedNames (global named ranges)
819. if (isset($gnmXML->Names)) {
820. foreach($gnmXML->Names->Name as $namedRange) {
821. $name = (string) $namedRange->name;
822. $range = (string) $namedRange->value;
823. if ( stripos($range, '#REF!') !== false) {
824. continue;
825. }
827. $range = explode('!',$range);
828. $range[0] = trim($range[0],"'");;
829. if ($worksheet = $objPHPExcel->getSheetByName($range[0])) {
830. $extractedRange = str_replace('$', '', $range[1]);
831. $objPHPExcel->addNamedRange( new PHPExcel_NamedRange($name, $worksheet, $extractedRange) );
832. }
833. }
834. }
837. // Return
838. return $objPHPExcel;
839. }
841. private static function _parseBorderAttributes($borderAttributes) {
842. $styleArray = array();
844. if (isset($borderAttributes["Color"])) {
845. $RGB = self::_parseGnumericColour($borderAttributes["Color"]);
846. $styleArray['color']['rgb'] = $RGB;
847. }
849. switch ($borderAttributes["Style"]) {
850. case '0' :
851. $styleArray['style'] = PHPExcel_Style_Border:: BORDER_NONE;
852. break;
853. case '1' :
854. $styleArray['style'] = PHPExcel_Style_Border:: BORDER_THIN;
855. break;
856. case '2' :
857. $styleArray['style'] = PHPExcel_Style_Border:: BORDER_MEDIUM;
858. break;
859. case '4' :
860. $styleArray['style'] = PHPExcel_Style_Border:: BORDER_DASHED;
861. break;
862. case '5' :
863. $styleArray['style'] = PHPExcel_Style_Border:: BORDER_THICK;
864. break;
865. case '6' :
866. $styleArray['style'] = PHPExcel_Style_Border:: BORDER_DOUBLE;
867. break;
868. case '7' :
869. $styleArray['style'] = PHPExcel_Style_Border:: BORDER_DOTTED;
870. break;
871. case '9' :
872. $styleArray['style'] = PHPExcel_Style_Border:: BORDER_DASHDOT;
873. break;
874. case '10' :
875. $styleArray['style'] = PHPExcel_Style_Border:: BORDER_MEDIUMDASHDOT;
876. break;
877. case '11' :
878. $styleArray['style'] = PHPExcel_Style_Border:: BORDER_DASHDOTDOT;
879. break;
880. case '12' :
881. $styleArray['style'] = PHPExcel_Style_Border:: BORDER_MEDIUMDASHDOTDOT;
882. break;
883. case '13' :
884. $styleArray['style'] = PHPExcel_Style_Border:: BORDER_MEDIUMDASHDOTDOT;
885. break;
886. case '3' :
887. $styleArray['style'] = PHPExcel_Style_Border:: BORDER_SLANTDASHDOT;
888. break;
889. case '8' :
890. $styleArray['style'] = PHPExcel_Style_Border:: BORDER_MEDIUMDASHED;
891. break;
892. }
893. return $styleArray;
894. }
896. private function _parseRichText($is = '') {
897. $value = new PHPExcel_RichText();
899. $value->createText($is);
901. return $value;
902. }
904. private static function _parseGnumericColour($gnmColour) {
905. list($gnmR,$gnmG,$gnmB) = explode(':',$gnmColour);
906. $gnmR = substr( str_pad($gnmR,4,'0',STR_PAD_RIGHT),0,2);
907. $gnmG = substr( str_pad($gnmG,4,'0',STR_PAD_RIGHT),0,2);
908. $gnmB = substr( str_pad($gnmB,4,'0',STR_PAD_RIGHT),0,2);
909. $RGB = $gnmR.$gnmG.$gnmB;
910. // echo 'Excel Colour: ',$RGB,'<br />';
911. return $RGB;
912. }
914. }

Documentation generated on Sun, 27 Feb 2011 16:32:13 -0800 by phpDocumentor 1.4.3