File Source for Excel2003XML.php

PHPExcel_Reader

# Source for file Excel2003XML.php

Documentation is available at Excel2003XML.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_Excel2003XML
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_Excel2003XML implements PHPExcel_Reader_IReader
46. {
47. /**
48. * Read data only?
49. *
50. * @var boolean
51. */
52. private $_readDataOnly = false;
54. /**
55. * Restict which sheets should be loaded?
56. *
57. * @var array
58. */
59. private $_loadSheetsOnly = null;
61. /**
62. * Formats
63. *
64. * @var array
65. */
66. private $_styles = array();
68. /**
69. * PHPExcel_Reader_IReadFilter instance
70. *
71. * @var PHPExcel_Reader_IReadFilter
72. */
73. private $_readFilter = null;
76. /**
77. * Read data only?
78. *
79. * @return boolean
80. */
81. public function getReadDataOnly() {
82. return $this->_readDataOnly;
83. }
85. /**
86. * Set read data only
87. *
88. * @param boolean $pValue
89. * @return PHPExcel_Reader_Excel2003XML
90. */
91. public function setReadDataOnly($pValue = false) {
92. $this->_readDataOnly = $pValue;
93. return $this;
94. }
96. /**
97. * Get which sheets to load
98. *
99. * @return mixed
100. */
101. public function getLoadSheetsOnly()
102. {
103. return $this->_loadSheetsOnly;
104. }
106. /**
107. * Set which sheets to load
108. *
109. * @param mixed $value
110. * @return PHPExcel_Reader_Excel2003XML
111. */
112. public function setLoadSheetsOnly($value = null)
113. {
114. $this->_loadSheetsOnly = is_array($value) ?
115. $value : array($value);
116. return $this;
117. }
119. /**
120. * Set all sheets to load
121. *
122. * @return PHPExcel_Reader_Excel2003XML
123. */
124. public function setLoadAllSheets()
125. {
126. $this->_loadSheetsOnly = null;
127. return $this;
128. }
130. /**
131. * Read filter
132. *
133. * @return PHPExcel_Reader_IReadFilter
134. */
135. public function getReadFilter() {
136. return $this->_readFilter;
137. }
139. /**
140. * Set read filter
141. *
142. * @param PHPExcel_Reader_IReadFilter $pValue
143. * @return PHPExcel_Reader_Excel2003XML
144. */
145. public function setReadFilter(PHPExcel_Reader_IReadFilter $pValue) {
146. $this->_readFilter = $pValue;
147. return $this;
148. }
150. /**
151. * Create a new PHPExcel_Reader_Excel2003XML
152. */
153. public function __construct() {
154. $this->_readFilter = new PHPExcel_Reader_DefaultReadFilter();
155. }
157. /**
158. * Can the current PHPExcel_Reader_IReader read the file?
159. *
160. * @param string $pFileName
161. * @return boolean
162. */
163. public function canRead($pFilename)
164. {
166. // Office xmlns:o="urn:schemas-microsoft-com:office:office"
167. // Excel xmlns:x="urn:schemas-microsoft-com:office:excel"
168. // XML Spreadsheet xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
169. // Spreadsheet component xmlns:c="urn:schemas-microsoft-com:office:component:spreadsheet"
170. // XML schema xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882"
171. // XML data type xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882"
172. // MS-persist recordset xmlns:rs="urn:schemas-microsoft-com:rowset"
173. // Rowset xmlns:z="#RowsetSchema"
174. //
176. $signature = array(
177. '<?xml version="1.0"',
178. '<?mso-application progid="Excel.Sheet"?>'
179. );
181. // Check if file exists
182. if (! file_exists($pFilename)) {
183. throw new Exception("Could not open " . $pFilename . " for reading! File does not exist.");
184. }
186. // Read sample data (first 2 KB will do)
187. $fh = fopen($pFilename, 'r');
188. $data = fread($fh, 2048);
189. fclose($fh);
191. $valid = true;
192. foreach($signature as $match) {
193. // every part of the signature must be present
194. if ( strpos($data, $match) === false) {
195. $valid = false;
196. break;
197. }
198. }
200. return $valid;
201. }
203. /**
204. * Reads names of the worksheets from a file, without parsing the whole file to a PHPExcel object
205. *
206. * @param string $pFilename
207. * @throws Exception
208. */
209. public function listWorksheetNames($pFilename)
210. {
211. // Check if file exists
212. if (! file_exists($pFilename)) {
213. throw new Exception("Could not open " . $pFilename . " for reading! File does not exist.");
214. }
216. $worksheetNames = array();
218. $xml = simplexml_load_file($pFilename);
219. $namespaces = $xml->getNamespaces(true);
221. $xml_ss = $xml->children($namespaces['ss']);
222. foreach($xml_ss->Worksheet as $worksheet) {
223. $worksheet_ss = $worksheet->attributes($namespaces['ss']);
224. $worksheetNames[] = $worksheet_ss['Name'];
225. }
227. return $worksheetNames;
228. }
231. /**
232. * Loads PHPExcel from file
233. *
234. * @param string $pFilename
235. * @return PHPExcel
236. * @throws Exception
237. */
238. public function load($pFilename)
239. {
240. // Create new PHPExcel
241. $objPHPExcel = new PHPExcel();
243. // Load into this instance
244. return $this-> loadIntoExisting($pFilename, $objPHPExcel);
245. }
247. private static function identifyFixedStyleValue($styleList,&$styleAttributeValue) {
248. $styleAttributeValue = strtolower($styleAttributeValue);
249. foreach($styleList as $style) {
250. if ($styleAttributeValue == strtolower($style)) {
251. $styleAttributeValue = $style;
252. return true;
253. }
254. }
255. return false;
256. }
258. /**
259. * pixel units to excel width units(units of 1/256th of a character width)
260. * @param pxs
261. * @return
262. */
263. private static function _pixel2WidthUnits($pxs) {
264. $UNIT_OFFSET_MAP = array(0, 36, 73, 109, 146, 182, 219);
266. $widthUnits = 256 * ($pxs / 7);
267. $widthUnits += $UNIT_OFFSET_MAP[($pxs % 7)];
268. return $widthUnits;
269. }
271. /**
272. * excel width units(units of 1/256th of a character width) to pixel units
273. * @param widthUnits
274. * @return
275. */
276. private static function _widthUnits2Pixel($widthUnits) {
277. $pixels = ($widthUnits / 256) * 7;
278. $offsetWidthUnits = $widthUnits % 256;
279. $pixels += round($offsetWidthUnits / (256 / 7));
280. return $pixels;
281. }
284. private static function _hex2str($hex) {
285. return chr( hexdec($hex[1]));
286. }
288. /**
289. * Loads PHPExcel from file into PHPExcel instance
290. *
291. * @param string $pFilename
292. * @param PHPExcel $objPHPExcel
293. * @return PHPExcel
294. * @throws Exception
295. */
296. public function loadIntoExisting($pFilename, PHPExcel $objPHPExcel)
297. {
298. $fromFormats = array('\-', '\ ');
299. $toFormats = array('-', ' ');
301. $underlineStyles = array (
302. PHPExcel_Style_Font:: UNDERLINE_NONE,
303. PHPExcel_Style_Font:: UNDERLINE_DOUBLE,
304. PHPExcel_Style_Font:: UNDERLINE_DOUBLEACCOUNTING,
305. PHPExcel_Style_Font:: UNDERLINE_SINGLE,
306. PHPExcel_Style_Font:: UNDERLINE_SINGLEACCOUNTING
307. );
308. $verticalAlignmentStyles = array (
309. PHPExcel_Style_Alignment:: VERTICAL_BOTTOM,
310. PHPExcel_Style_Alignment:: VERTICAL_TOP,
311. PHPExcel_Style_Alignment:: VERTICAL_CENTER,
312. PHPExcel_Style_Alignment:: VERTICAL_JUSTIFY
313. );
314. $horizontalAlignmentStyles = array (
315. PHPExcel_Style_Alignment:: HORIZONTAL_GENERAL,
316. PHPExcel_Style_Alignment:: HORIZONTAL_LEFT,
317. PHPExcel_Style_Alignment:: HORIZONTAL_RIGHT,
318. PHPExcel_Style_Alignment:: HORIZONTAL_CENTER,
319. PHPExcel_Style_Alignment:: HORIZONTAL_CENTER_CONTINUOUS,
320. PHPExcel_Style_Alignment:: HORIZONTAL_JUSTIFY
321. );
323. $timezoneObj = new DateTimeZone('Europe/London');
324. $GMT = new DateTimeZone('UTC');
327. // Check if file exists
328. if (! file_exists($pFilename)) {
329. throw new Exception("Could not open " . $pFilename . " for reading! File does not exist.");
330. }
332. $xml = simplexml_load_file($pFilename);
333. $namespaces = $xml->getNamespaces(true);
335. $docProps = $objPHPExcel->getProperties();
336. if (isset($xml->DocumentProperties[0])) {
337. foreach($xml->DocumentProperties[0] as $propertyName => $propertyValue) {
338. switch ($propertyName) {
339. case 'Title' :
340. $docProps->setTitle($propertyValue);
341. break;
342. case 'Subject' :
343. $docProps->setSubject($propertyValue);
344. break;
345. case 'Author' :
346. $docProps->setCreator($propertyValue);
347. break;
348. case 'Created' :
349. $creationDate = strtotime($propertyValue);
350. $docProps->setCreated($creationDate);
351. break;
352. case 'LastAuthor' :
353. $docProps->setLastModifiedBy($propertyValue);
354. break;
355. case 'LastSaved' :
356. $lastSaveDate = strtotime($propertyValue);
357. $docProps->setModified($lastSaveDate);
358. break;
359. case 'Company' :
360. $docProps->setCompany($propertyValue);
361. break;
362. case 'Category' :
363. $docProps->setCategory($propertyValue);
364. break;
365. case 'Manager' :
366. $docProps->setManager($propertyValue);
367. break;
368. case 'Keywords' :
369. $docProps->setKeywords($propertyValue);
370. break;
371. case 'Description' :
372. $docProps->setDescription($propertyValue);
373. break;
374. }
375. }
376. }
377. if (isset($xml->CustomDocumentProperties)) {
378. foreach($xml->CustomDocumentProperties[0] as $propertyName => $propertyValue) {
379. $propertyAttributes = $propertyValue->attributes($namespaces['dt']);
380. $propertyName = preg_replace_callback('/_x([0-9a-z]{4})_/','PHPExcel_Reader_Excel2003XML::_hex2str',$propertyName);
381. $propertyType = PHPExcel_DocumentProperties:: PROPERTY_TYPE_UNKNOWN;
382. switch((string) $propertyAttributes) {
383. case 'string' :
384. $propertyType = PHPExcel_DocumentProperties:: PROPERTY_TYPE_STRING;
385. $propertyValue = trim($propertyValue);
386. break;
387. case 'boolean' :
388. $propertyType = PHPExcel_DocumentProperties:: PROPERTY_TYPE_BOOLEAN;
389. $propertyValue = (bool) $propertyValue;
390. break;
391. case 'integer' :
392. $propertyType = PHPExcel_DocumentProperties:: PROPERTY_TYPE_INTEGER;
393. $propertyValue = intval($propertyValue);
394. break;
395. case 'float' :
396. $propertyType = PHPExcel_DocumentProperties:: PROPERTY_TYPE_FLOAT;
397. $propertyValue = floatval($propertyValue);
398. break;
399. case 'dateTime.tz' :
400. $propertyType = PHPExcel_DocumentProperties:: PROPERTY_TYPE_DATE;
401. $propertyValue = strtotime( trim($propertyValue));
402. break;
403. }
404. $docProps->setCustomProperty($propertyName,$propertyValue,$propertyType);
405. }
406. }
408. foreach($xml->Styles[0] as $style) {
409. $style_ss = $style->attributes($namespaces['ss']);
410. $styleID = (string) $style_ss['ID'];
411. // echo 'Style ID = '.$styleID.'<br />';
412. if ($styleID == 'Default') {
413. $this->_styles['Default'] = array();
414. } else {
415. $this->_styles[$styleID] = $this->_styles['Default'];
416. }
417. foreach ($style as $styleType => $styleData) {
418. $styleAttributes = $styleData->attributes($namespaces['ss']);
419. // echo $styleType.'<br />';
420. switch ($styleType) {
421. case 'Alignment' :
422. foreach($styleAttributes as $styleAttributeKey => $styleAttributeValue) {
423. // echo $styleAttributeKey.' = '.$styleAttributeValue.'<br />';
424. $styleAttributeValue = (string) $styleAttributeValue;
425. switch ($styleAttributeKey) {
426. case 'Vertical' :
427. if (self:: identifyFixedStyleValue($verticalAlignmentStyles,$styleAttributeValue)) {
428. $this->_styles[$styleID]['alignment']['vertical'] = $styleAttributeValue;
429. }
430. break;
431. case 'Horizontal' :
432. if (self:: identifyFixedStyleValue($horizontalAlignmentStyles,$styleAttributeValue)) {
433. $this->_styles[$styleID]['alignment']['horizontal'] = $styleAttributeValue;
434. }
435. break;
436. case 'WrapText' :
437. $this->_styles[$styleID]['alignment']['wrap'] = true;
438. break;
439. }
440. }
441. break;
442. case 'Borders' :
443. foreach($styleData->Border as $borderStyle) {
444. $borderAttributes = $borderStyle->attributes($namespaces['ss']);
445. $thisBorder = array();
446. foreach($borderAttributes as $borderStyleKey => $borderStyleValue) {
447. // echo $borderStyleKey.' = '.$borderStyleValue.'<br />';
448. switch ($borderStyleKey) {
449. case 'LineStyle' :
450. $thisBorder['style'] = PHPExcel_Style_Border:: BORDER_MEDIUM;
451. // $thisBorder['style'] = $borderStyleValue;
452. break;
453. case 'Weight' :
454. // $thisBorder['style'] = $borderStyleValue;
455. break;
456. case 'Position' :
457. $borderPosition = strtolower($borderStyleValue);
458. break;
459. case 'Color' :
460. $borderColour = substr($borderStyleValue,1);
461. $thisBorder['color']['rgb'] = $borderColour;
462. break;
463. }
464. }
465. if ( count($thisBorder) > 0) {
466. if (($borderPosition == 'left') || ($borderPosition == 'right') || ($borderPosition == 'top') || ($borderPosition == 'bottom')) {
467. $this->_styles[$styleID]['borders'][$borderPosition] = $thisBorder;
468. }
469. }
470. }
471. break;
472. case 'Font' :
473. foreach($styleAttributes as $styleAttributeKey => $styleAttributeValue) {
474. // echo $styleAttributeKey.' = '.$styleAttributeValue.'<br />';
475. $styleAttributeValue = (string) $styleAttributeValue;
476. switch ($styleAttributeKey) {
477. case 'FontName' :
478. $this->_styles[$styleID]['font']['name'] = $styleAttributeValue;
479. break;
480. case 'Size' :
481. $this->_styles[$styleID]['font']['size'] = $styleAttributeValue;
482. break;
483. case 'Color' :
484. $this->_styles[$styleID]['font']['color']['rgb'] = substr($styleAttributeValue,1);
485. break;
486. case 'Bold' :
487. $this->_styles[$styleID]['font']['bold'] = true;
488. break;
489. case 'Italic' :
490. $this->_styles[$styleID]['font']['italic'] = true;
491. break;
492. case 'Underline' :
493. if (self:: identifyFixedStyleValue($underlineStyles,$styleAttributeValue)) {
494. $this->_styles[$styleID]['font']['underline'] = $styleAttributeValue;
495. }
496. break;
497. }
498. }
499. break;
500. case 'Interior' :
501. foreach($styleAttributes as $styleAttributeKey => $styleAttributeValue) {
502. // echo $styleAttributeKey.' = '.$styleAttributeValue.'<br />';
503. switch ($styleAttributeKey) {
504. case 'Color' :
505. $this->_styles[$styleID]['fill']['color']['rgb'] = substr($styleAttributeValue,1);
506. break;
507. }
508. }
509. break;
510. case 'NumberFormat' :
511. foreach($styleAttributes as $styleAttributeKey => $styleAttributeValue) {
512. // echo $styleAttributeKey.' = '.$styleAttributeValue.'<br />';
513. $styleAttributeValue = str_replace($fromFormats,$toFormats,$styleAttributeValue);
514. switch ($styleAttributeValue) {
515. case 'Short Date' :
516. $styleAttributeValue = 'dd/mm/yyyy';
517. break;
518. }
519. if ($styleAttributeValue > '') {
520. $this->_styles[$styleID]['numberformat']['code'] = $styleAttributeValue;
521. }
522. }
523. break;
524. case 'Protection' :
525. foreach($styleAttributes as $styleAttributeKey => $styleAttributeValue) {
526. // echo $styleAttributeKey.' = '.$styleAttributeValue.'<br />';
527. }
528. break;
529. }
530. }
531. // print_r($this->_styles[$styleID]);
532. // echo '<hr />';
533. }
534. // echo '<hr />';
536. $worksheetID = 0;
537. $xml_ss = $xml->children($namespaces['ss']);
538. foreach($xml_ss->Worksheet as $worksheet) {
539. $worksheet_ss = $worksheet->attributes($namespaces['ss']);
541. if ((isset($this->_loadSheetsOnly)) && (isset($worksheet_ss['Name'])) &&
542. (! in_array($worksheet_ss['Name'], $this->_loadSheetsOnly))) {
543. continue;
544. }
546. // echo '<h3>Worksheet: ',$worksheet_ss['Name'],'<h3>';
547. //
548. // Create new Worksheet
549. $objPHPExcel->createSheet();
550. $objPHPExcel->setActiveSheetIndex($worksheetID);
551. if (isset($worksheet_ss['Name'])) {
552. $worksheetName = (string) $worksheet_ss['Name'];
553. $objPHPExcel->getActiveSheet()->setTitle($worksheetName);
554. }
556. $columnID = 'A';
557. if (isset($worksheet->Table->Column)) {
558. foreach($worksheet->Table->Column as $columnData) {
559. $columnData_ss = $columnData->attributes($namespaces['ss']);
560. if (isset($columnData_ss['Index'])) {
561. $columnID = PHPExcel_Cell:: stringFromColumnIndex($columnData_ss['Index']-1);
562. }
563. if (isset($columnData_ss['Width'])) {
564. $columnWidth = $columnData_ss['Width'];
565. // echo '<b>Setting column width for '.$columnID.' to '.$columnWidth.'</b><br />';
566. $objPHPExcel->getActiveSheet()->getColumnDimension($columnID)->setWidth($columnWidth / 5.4);
567. }
568. ++$columnID;
569. }
570. }
572. $rowID = 1;
573. if (isset($worksheet->Table->Row)) {
574. foreach($worksheet->Table->Row as $rowData) {
575. $rowHasData = false;
576. $row_ss = $rowData->attributes($namespaces['ss']);
577. if (isset($row_ss['Index'])) {
578. $rowID = (integer) $row_ss['Index'];
579. }
580. // echo '<b>Row '.$rowID.'</b><br />';
582. $columnID = 'A';
583. foreach($rowData->Cell as $cell) {
585. $cell_ss = $cell->attributes($namespaces['ss']);
586. if (isset($cell_ss['Index'])) {
587. $columnID = PHPExcel_Cell:: stringFromColumnIndex($cell_ss['Index']-1);
588. }
589. $cellRange = $columnID.$rowID;
591. if (! is_null($this-> getReadFilter())) {
592. if (!$this-> getReadFilter()->readCell($columnID, $rowID, $worksheetName)) {
593. continue;
594. }
595. }
597. if ((isset($cell_ss['MergeAcross'])) || (isset($cell_ss['MergeDown']))) {
598. $columnTo = $columnID;
599. if (isset($cell_ss['MergeAcross'])) {
600. $columnTo = PHPExcel_Cell:: stringFromColumnIndex( PHPExcel_Cell:: columnIndexFromString($columnID) + $cell_ss['MergeAcross'] -1);
601. }
602. $rowTo = $rowID;
603. if (isset($cell_ss['MergeDown'])) {
604. $rowTo = $rowTo + $cell_ss['MergeDown'];
605. }
606. $cellRange .= ':'.$columnTo.$rowTo;
607. $objPHPExcel->getActiveSheet()->mergeCells($cellRange);
608. }
610. $cellIsSet = $hasCalculatedValue = false;
611. $cellDataFormula = '';
612. if (isset($cell_ss['Formula'])) {
613. $cellDataFormula = $cell_ss['Formula'];
614. // added this as a check for array formulas
615. if (isset($cell_ss['ArrayRange'])) {
616. $cellDataCSEFormula = $cell_ss['ArrayRange'];
617. // echo "found an array formula at ".$columnID.$rowID."<br />";
618. }
619. $hasCalculatedValue = true;
620. }
621. if (isset($cell->Data)) {
622. $cellValue = $cellData = $cell->Data;
623. $type = PHPExcel_Cell_DataType:: TYPE_NULL;
624. $cellData_ss = $cellData->attributes($namespaces['ss']);
625. if (isset($cellData_ss['Type'])) {
626. $cellDataType = $cellData_ss['Type'];
627. switch ($cellDataType) {
628. /*
629. const TYPE_STRING = 's';
630. const TYPE_FORMULA = 'f';
631. const TYPE_NUMERIC = 'n';
632. const TYPE_BOOL = 'b';
633. const TYPE_NULL = 's';
634. const TYPE_INLINE = 'inlineStr';
635. const TYPE_ERROR = 'e';
636. */
637. case 'String' :
638. $type = PHPExcel_Cell_DataType:: TYPE_STRING;
639. break;
640. case 'Number' :
641. $type = PHPExcel_Cell_DataType:: TYPE_NUMERIC;
642. $cellValue = (float) $cellValue;
643. if ( floor($cellValue) == $cellValue) {
644. $cellValue = (integer) $cellValue;
645. }
646. break;
647. case 'Boolean' :
648. $type = PHPExcel_Cell_DataType:: TYPE_BOOL;
649. $cellValue = ($cellValue != 0);
650. break;
651. case 'DateTime' :
652. $type = PHPExcel_Cell_DataType:: TYPE_NUMERIC;
653. $cellValue = PHPExcel_Shared_Date:: PHPToExcel( strtotime($cellValue));
654. break;
655. case 'Error' :
656. $type = PHPExcel_Cell_DataType:: TYPE_ERROR;
657. break;
658. }
659. }
661. if ($hasCalculatedValue) {
662. // echo 'FORMULA<br />';
663. $type = PHPExcel_Cell_DataType:: TYPE_FORMULA;
664. $columnNumber = PHPExcel_Cell:: columnIndexFromString($columnID);
665. if ( substr($cellDataFormula,0,3) == 'of:') {
666. $cellDataFormula = substr($cellDataFormula,3);
667. // echo 'Before: ',$cellDataFormula,'<br />';
668. $temp = explode('"',$cellDataFormula);
669. $key = false;
670. foreach($temp as &$value) {
671. // Only replace in alternate array entries (i.e. non-quoted blocks)
672. if ($key = !$key) {
673. $value = str_replace(array('[.','.',']'),'',$value);
674. }
675. }
676. } else {
677. // Convert R1C1 style references to A1 style references (but only when not quoted)
678. // echo 'Before: ',$cellDataFormula,'<br />';
679. $temp = explode('"',$cellDataFormula);
680. $key = false;
681. foreach($temp as &$value) {
682. // Only replace in alternate array entries (i.e. non-quoted blocks)
683. if ($key = !$key) {
684. preg_match_all('/(R(\[?-?\d*\]?))(C(\[?-?\d*\]?))/',$value, $cellReferences,PREG_SET_ORDER+PREG_OFFSET_CAPTURE);
685. // Reverse the matches array, otherwise all our offsets will become incorrect if we modify our way
686. // through the formula from left to right. Reversing means that we work right to left.through
687. // the formula
688. $cellReferences = array_reverse($cellReferences);
689. // Loop through each R1C1 style reference in turn, converting it to its A1 style equivalent,
690. // then modify the formula to use that new reference
691. foreach($cellReferences as $cellReference) {
692. $rowReference = $cellReference[2][0];
693. // Empty R reference is the current row
694. if ($rowReference == '') $rowReference = $rowID;
695. // Bracketed R references are relative to the current row
696. if ($rowReference{0} == '[') $rowReference = $rowID + trim($rowReference,'[]');
697. $columnReference = $cellReference[4][0];
698. // Empty C reference is the current column
699. if ($columnReference == '') $columnReference = $columnNumber;
700. // Bracketed C references are relative to the current column
701. if ($columnReference{0} == '[') $columnReference = $columnNumber + trim($columnReference,'[]');
702. $A1CellReference = PHPExcel_Cell:: stringFromColumnIndex($columnReference-1).$rowReference;
703. $value = substr_replace($value,$A1CellReference,$cellReference[0][1], strlen($cellReference[0][0]));
704. }
705. }
706. }
707. }
708. unset($value);
709. // Then rebuild the formula string
710. $cellDataFormula = implode('"',$temp);
711. // echo 'After: ',$cellDataFormula,'<br />';
712. }
714. // echo 'Cell '.$columnID.$rowID.' is a '.$type.' with a value of '.(($hasCalculatedValue) ? $cellDataFormula : $cellValue).'<br />';
715. //
716. $objPHPExcel->getActiveSheet()->getCell($columnID.$rowID)->setValueExplicit((($hasCalculatedValue) ? $cellDataFormula : $cellValue),$type);
717. if ($hasCalculatedValue) {
718. // echo 'Formula result is '.$cellValue.'<br />';
719. $objPHPExcel->getActiveSheet()->getCell($columnID.$rowID)->setCalculatedValue($cellValue);
720. }
721. $cellIsSet = $rowHasData = true;
722. }
724. if (isset($cell->Comment)) {
725. // echo '<b>comment found</b><br />';
726. $commentAttributes = $cell->Comment->attributes($namespaces['ss']);
727. $author = 'unknown';
728. if (isset($commentAttributes->Author)) {
729. $author = (string)$commentAttributes->Author;
730. // echo 'Author: ',$author,'<br />';
731. }
732. $node = $cell->Comment->Data->asXML();
733. // $annotation = str_replace('html:','',substr($node,49,-10));
734. // echo $annotation,'<br />';
735. $annotation = strip_tags($node);
736. // echo 'Annotation: ',$annotation,'<br />';
737. $objPHPExcel->getActiveSheet()->getComment( $columnID.$rowID )
738. ->setAuthor( $author )
739. ->setText($this->_parseRichText($annotation) );
740. }
742. if (($cellIsSet) && (isset($cell_ss['StyleID']))) {
743. $style = (string) $cell_ss['StyleID'];
744. // echo 'Cell style for '.$columnID.$rowID.' is '.$style.'<br />';
745. if ((isset($this->_styles[$style])) && ( count($this->_styles[$style]) > 0)) {
746. // echo 'Cell '.$columnID.$rowID.'<br />';
747. // print_r($this->_styles[$style]);
748. // echo '<br />';
749. if (!$objPHPExcel->getActiveSheet()->cellExists($columnID.$rowID)) {
750. $objPHPExcel->getActiveSheet()->getCell($columnID.$rowID)->setValue(NULL);
751. }
752. $objPHPExcel->getActiveSheet()->getStyle($cellRange)->applyFromArray($this->_styles[$style]);
753. }
754. }
755. ++$columnID;
756. }
758. if ($rowHasData) {
759. if (isset($row_ss['StyleID'])) {
760. $rowStyle = $row_ss['StyleID'];
761. }
762. if (isset($row_ss['Height'])) {
763. $rowHeight = $row_ss['Height'];
764. // echo '<b>Setting row height to '.$rowHeight.'</b><br />';
765. $objPHPExcel->getActiveSheet()->getRowDimension($rowID)->setRowHeight($rowHeight);
766. }
767. }
769. ++$rowID;
770. }
771. }
772. ++$worksheetID;
773. }
775. // Return
776. return $objPHPExcel;
777. }
779. private function _parseRichText($is = '') {
780. $value = new PHPExcel_RichText();
782. $value->createText($is);
784. return $value;
785. }
787. }

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