File Source for Excel2007.php

PHPExcel_Reader

# Source for file Excel2007.php

Documentation is available at Excel2007.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_Excel2007
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_Excel2007 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. * PHPExcel_Reader_IReadFilter instance
66. *
67. * @var PHPExcel_Reader_IReadFilter
68. */
69. private $_readFilter = null;
71. /**
72. * PHPExcel_ReferenceHelper instance
73. *
74. * @var PHPExcel_ReferenceHelper
75. */
76. private $_referenceHelper = null;
78. /**
79. * PHPExcel_Reader_Excel2007_Theme instance
80. *
81. * @var PHPExcel_Reader_Excel2007_Theme
82. */
83. private static $_theme = null;
86. /**
87. * Read data only?
88. * If this is true, then the Reader will only read data values for cells, it will not read any formatting information.
89. * If false (the default) it will read data and formatting.
90. *
91. * @return boolean
92. */
93. public function getReadDataOnly() {
94. return $this->_readDataOnly;
95. }
97. /**
98. * Set read data only
99. * Set to true, to advise the Reader only to read data values for cells, and to ignore any formatting information.
100. * Set to false (the default) to advise the Reader to read both data and formatting for cells.
101. *
102. * @param boolean $pValue
103. *
104. * @return PHPExcel_Reader_Excel2007
105. */
106. public function setReadDataOnly($pValue = false) {
107. $this->_readDataOnly = $pValue;
108. return $this;
109. }
111. /**
112. * Get which sheets to load
113. * Returns either an array of worksheet names (the list of worksheets that should be loaded), or a null
114. * indicating that all worksheets in the workbook should be loaded.
115. *
116. * @return mixed
117. */
118. public function getLoadSheetsOnly()
119. {
120. return $this->_loadSheetsOnly;
121. }
123. /**
124. * Set which sheets to load
125. *
126. * @param mixed $value
127. * This should be either an array of worksheet names to be loaded, or a string containing a single worksheet name.
128. * If NULL, then it tells the Reader to read all worksheets in the workbook
129. *
130. * @return PHPExcel_Reader_Excel2007
131. */
132. public function setLoadSheetsOnly($value = null)
133. {
134. $this->_loadSheetsOnly = is_array($value) ?
135. $value : array($value);
136. return $this;
137. }
139. /**
140. * Set all sheets to load
141. * Tells the Reader to load all worksheets from the workbook.
142. *
143. * @return PHPExcel_Reader_Excel2007
144. */
145. public function setLoadAllSheets()
146. {
147. $this->_loadSheetsOnly = null;
148. return $this;
149. }
151. /**
152. * Read filter
153. *
154. * @return PHPExcel_Reader_IReadFilter
155. */
156. public function getReadFilter() {
157. return $this->_readFilter;
158. }
160. /**
161. * Set read filter
162. *
163. * @param PHPExcel_Reader_IReadFilter $pValue
164. * @return PHPExcel_Reader_Excel2007
165. */
166. public function setReadFilter(PHPExcel_Reader_IReadFilter $pValue) {
167. $this->_readFilter = $pValue;
168. return $this;
169. }
171. /**
172. * Create a new PHPExcel_Reader_Excel2007 instance
173. */
174. public function __construct() {
175. $this->_readFilter = new PHPExcel_Reader_DefaultReadFilter();
176. $this->_referenceHelper = PHPExcel_ReferenceHelper:: getInstance();
177. }
179. /**
180. * Can the current PHPExcel_Reader_IReader read the file?
181. *
182. * @param string $pFileName
183. * @return boolean
184. */
185. public function canRead($pFilename)
186. {
187. // Check if zip class exists
188. if (! class_exists('ZipArchive')) {
189. return false;
190. }
192. // Check if file exists
193. if (! file_exists($pFilename)) {
194. throw new Exception("Could not open " . $pFilename . " for reading! File does not exist.");
195. }
197. $xl = false;
198. // Load file
199. $zip = new ZipArchive;
200. if ($zip->open($pFilename) === true) {
201. // check if it is an OOXML archive
202. $rels = simplexml_load_string($this->_getFromZipArchive($zip, "_rels/.rels"));
203. foreach ($rels->Relationship as $rel) {
204. switch ($rel["Type"]) {
205. case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument":
206. if ( basename($rel["Target"]) == 'workbook.xml') {
207. $xl = true;
208. }
209. break;
211. }
212. }
213. $zip->close();
214. }
216. return $xl;
217. }
219. private static function _castToBool($c) {
220. // echo 'Initial Cast to Boolean<br />';
221. $value = isset($c->v) ? (string) $c->v : null;
222. if ($value == '0') {
223. return false;
224. } elseif ($value == '1') {
225. return true;
226. } else {
227. return (bool)$c->v;
228. }
229. return $value;
230. } // function _castToBool()
232. private static function _castToError($c) {
233. // echo 'Initial Cast to Error<br />';
234. return isset($c->v) ? (string) $c->v : null;;
235. } // function _castToError()
237. private static function _castToString($c) {
238. // echo 'Initial Cast to String<br />';
239. return isset($c->v) ? (string) $c->v : null;;
240. } // function _castToString()
242. private function _castToFormula($c,$r,&$cellDataType,&$value,&$calculatedValue,&$sharedFormulas,$castBaseType) {
243. // echo '<font color="darkgreen">Formula</font><br />';
244. // echo '$c->f is '.$c->f.'<br />';
245. $cellDataType = 'f';
246. $value = "={$c->f}";
247. $calculatedValue = self::$castBaseType($c);
249. // Shared formula?
250. if (isset($c->f['t']) && strtolower((string)$c->f['t']) == 'shared') {
251. // echo '<font color="darkgreen">SHARED FORMULA</font><br />';
252. $instance = (string)$c->f['si'];
254. // echo 'Instance ID = '.$instance.'<br />';
255. //
256. // echo 'Shared Formula Array:<pre>';
257. // print_r($sharedFormulas);
258. // echo '</pre>';
259. if (!isset($sharedFormulas[(string)$c->f['si']])) {
260. // echo '<font color="darkgreen">SETTING NEW SHARED FORMULA</font><br />';
261. // echo 'Master is '.$r.'<br />';
262. // echo 'Formula is '.$value.'<br />';
263. $sharedFormulas[$instance] = array( 'master' => $r,
264. 'formula' => $value
265. );
266. // echo 'New Shared Formula Array:<pre>';
267. // print_r($sharedFormulas);
268. // echo '</pre>';
269. } else {
270. // echo '<font color="darkgreen">GETTING SHARED FORMULA</font><br />';
271. // echo 'Master is '.$sharedFormulas[$instance]['master'].'<br />';
272. // echo 'Formula is '.$sharedFormulas[$instance]['formula'].'<br />';
273. $master = PHPExcel_Cell:: coordinateFromString($sharedFormulas[$instance]['master']);
274. $current = PHPExcel_Cell:: coordinateFromString($r);
276. $difference = array(0, 0);
277. $difference[0] = PHPExcel_Cell:: columnIndexFromString($current[0]) - PHPExcel_Cell:: columnIndexFromString($master[0]);
278. $difference[1] = $current[1] - $master[1];
280. $value = $this->_referenceHelper->updateFormulaReferences( $sharedFormulas[$instance]['formula'],
281. 'A1',
282. $difference[0],
283. $difference[1]
284. );
285. // echo 'Adjusted Formula is '.$value.'<br />';
286. }
287. }
288. }
290. public function _getFromZipArchive(ZipArchive $archive, $fileName = '')
291. {
292. // Root-relative paths
293. if ( strpos($fileName, '//') !== false)
294. {
295. $fileName = substr($fileName, strpos($fileName, '//') + 1);
296. }
297. $fileName = PHPExcel_Shared_File:: realpath($fileName);
299. // Apache POI fixes
300. $contents = $archive->getFromName($fileName);
301. if ($contents === false)
302. {
303. $contents = $archive->getFromName( substr($fileName, 1));
304. }
306. return $contents;
307. }
309. /**
310. * Reads names of the worksheets from a file, without parsing the whole file to a PHPExcel object
311. *
312. * @param string $pFilename
313. * @throws Exception
314. */
315. public function listWorksheetNames($pFilename)
316. {
317. // Check if file exists
318. if (! file_exists($pFilename)) {
319. throw new Exception("Could not open " . $pFilename . " for reading! File does not exist.");
320. }
322. $worksheetNames = array();
324. $zip = new ZipArchive;
325. $zip->open($pFilename);
327. $rels = simplexml_load_string($this->_getFromZipArchive($zip, "_rels/.rels")); //~ http://schemas.openxmlformats.org/package/2006/relationships");
328. foreach ($rels->Relationship as $rel) {
329. switch ($rel["Type"]) {
330. case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument":
331. $xmlWorkbook = simplexml_load_string($this->_getFromZipArchive($zip, "{$rel['Target']}")); //~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");
333. if ($xmlWorkbook->sheets) {
334. foreach ($xmlWorkbook->sheets->sheet as $eleSheet) {
335. // Check if sheet should be skipped
336. $worksheetNames[] = (string) $eleSheet["name"];
337. }
338. }
339. }
340. }
342. $zip->close();
344. return $worksheetNames;
345. }
348. /**
349. * Loads PHPExcel from file
350. *
351. * @param string $pFilename
352. * @throws Exception
353. */
354. public function load($pFilename)
355. {
356. // Check if file exists
357. if (! file_exists($pFilename)) {
358. throw new Exception("Could not open " . $pFilename . " for reading! File does not exist.");
359. }
361. // Initialisations
362. $excel = new PHPExcel;
363. $excel->removeSheetByIndex(0);
364. if (!$this->_readDataOnly) {
365. $excel->removeCellStyleXfByIndex(0); // remove the default style
366. $excel->removeCellXfByIndex(0); // remove the default style
367. }
368. $zip = new ZipArchive;
369. $zip->open($pFilename);
371. // Read the theme first, because we need the colour scheme when reading the styles
372. $wbRels = simplexml_load_string($this->_getFromZipArchive($zip, "xl/_rels/workbook.xml.rels")); //~ http://schemas.openxmlformats.org/package/2006/relationships");
373. foreach ($wbRels->Relationship as $rel) {
374. switch ($rel["Type"]) {
375. case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/theme":
376. $themeOrderArray = array('lt1','dk1','lt2','dk2');
377. $themeOrderAdditional = count($themeOrderArray);
379. $xmlTheme = simplexml_load_string($this->_getFromZipArchive($zip, "xl/{$rel['Target']}"));
380. if ( is_object($xmlTheme)) {
381. $xmlThemeName = $xmlTheme->attributes();
382. $xmlTheme = $xmlTheme->children("http://schemas.openxmlformats.org/drawingml/2006/main");
383. $themeName = (string)$xmlThemeName['name'];
385. $colourScheme = $xmlTheme->themeElements->clrScheme->attributes();
386. $colourSchemeName = (string)$colourScheme['name'];
387. $colourScheme = $xmlTheme->themeElements->clrScheme->children("http://schemas.openxmlformats.org/drawingml/2006/main");
389. $themeColours = array();
390. foreach ($colourScheme as $k => $xmlColour) {
391. $themePos = array_search($k,$themeOrderArray);
392. if ($themePos === false) {
393. $themePos = $themeOrderAdditional++;
394. }
395. if (isset($xmlColour->sysClr)) {
396. $xmlColourData = $xmlColour->sysClr->attributes();
397. $themeColours[$themePos] = $xmlColourData['lastClr'];
398. } elseif (isset($xmlColour->srgbClr)) {
399. $xmlColourData = $xmlColour->srgbClr->attributes();
400. $themeColours[$themePos] = $xmlColourData['val'];
401. }
402. }
403. self::$_theme = new PHPExcel_Reader_Excel2007_Theme($themeName,$colourSchemeName,$themeColours);
404. }
405. break;
406. }
407. }
409. $rels = simplexml_load_string($this->_getFromZipArchive($zip, "_rels/.rels")); //~ http://schemas.openxmlformats.org/package/2006/relationships");
410. foreach ($rels->Relationship as $rel) {
411. switch ($rel["Type"]) {
412. case "http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties":
413. $xmlCore = simplexml_load_string($this->_getFromZipArchive($zip, "{$rel['Target']}"));
414. if ( is_object($xmlCore)) {
415. $xmlCore->registerXPathNamespace("dc", "http://purl.org/dc/elements/1.1/");
416. $xmlCore->registerXPathNamespace("dcterms", "http://purl.org/dc/terms/");
417. $xmlCore->registerXPathNamespace("cp", "http://schemas.openxmlformats.org/package/2006/metadata/core-properties");
418. $docProps = $excel->getProperties();
419. $docProps->setCreator((string) self:: array_item($xmlCore->xpath("dc:creator")));
420. $docProps->setLastModifiedBy((string) self:: array_item($xmlCore->xpath("cp:lastModifiedBy")));
421. $docProps->setCreated( strtotime(self:: array_item($xmlCore->xpath("dcterms:created")))); //! respect xsi:type
422. $docProps->setModified( strtotime(self:: array_item($xmlCore->xpath("dcterms:modified")))); //! respect xsi:type
423. $docProps->setTitle((string) self:: array_item($xmlCore->xpath("dc:title")));
424. $docProps->setDescription((string) self:: array_item($xmlCore->xpath("dc:description")));
425. $docProps->setSubject((string) self:: array_item($xmlCore->xpath("dc:subject")));
426. $docProps->setKeywords((string) self:: array_item($xmlCore->xpath("cp:keywords")));
427. $docProps->setCategory((string) self:: array_item($xmlCore->xpath("cp:category")));
428. }
429. break;
431. case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties":
432. $xmlCore = simplexml_load_string($this->_getFromZipArchive($zip, "{$rel['Target']}"));
433. if ( is_object($xmlCore)) {
434. $docProps = $excel->getProperties();
435. if (isset($xmlCore->Company))
436. $docProps->setCompany((string) $xmlCore->Company);
437. if (isset($xmlCore->Manager))
438. $docProps->setManager((string) $xmlCore->Manager);
439. }
440. break;
442. case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/custom-properties":
443. $xmlCore = simplexml_load_string($this->_getFromZipArchive($zip, "{$rel['Target']}"));
444. if ( is_object($xmlCore)) {
445. $docProps = $excel->getProperties();
446. foreach ($xmlCore as $xmlProperty) {
447. $cellDataOfficeAttributes = $xmlProperty->attributes();
448. if (isset($cellDataOfficeAttributes['name'])) {
449. $propertyName = (string) $cellDataOfficeAttributes['name'];
450. $cellDataOfficeChildren = $xmlProperty->children('http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes');
451. $attributeType = $cellDataOfficeChildren->getName();
452. $attributeValue = (string) $cellDataOfficeChildren->{$attributeType};
453. $attributeValue = PHPExcel_DocumentProperties:: convertProperty($attributeValue,$attributeType);
454. $attributeType = PHPExcel_DocumentProperties:: convertPropertyType($attributeType);
455. $docProps->setCustomProperty($propertyName,$attributeValue,$attributeType);
456. }
457. }
458. }
459. break;
461. case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument":
462. $dir = dirname($rel["Target"]);
463. $relsWorkbook = simplexml_load_string($this->_getFromZipArchive($zip, "$dir/_rels/" . basename($rel["Target"]) . ".rels")); //~ http://schemas.openxmlformats.org/package/2006/relationships");
464. $relsWorkbook->registerXPathNamespace("rel", "http://schemas.openxmlformats.org/package/2006/relationships");
466. $sharedStrings = array();
467. $xpath = self:: array_item($relsWorkbook->xpath("rel:Relationship[@Type='http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings']"));
468. $xmlStrings = simplexml_load_string($this->_getFromZipArchive($zip, "$dir/$xpath[Target]")); //~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");
469. if (isset($xmlStrings) && isset($xmlStrings->si)) {
470. foreach ($xmlStrings->si as $val) {
471. if (isset($val->t)) {
472. $sharedStrings[] = PHPExcel_Shared_String:: ControlCharacterOOXML2PHP( (string) $val->t );
473. } elseif (isset($val->r)) {
474. $sharedStrings[] = $this->_parseRichText($val);
475. }
476. }
477. }
479. $worksheets = array();
480. foreach ($relsWorkbook->Relationship as $ele) {
481. if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet") {
482. $worksheets[(string) $ele["Id"]] = $ele["Target"];
483. }
484. }
486. $styles = array();
487. $cellStyles = array();
488. $xpath = self:: array_item($relsWorkbook->xpath("rel:Relationship[@Type='http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles']"));
489. $xmlStyles = simplexml_load_string($this->_getFromZipArchive($zip, "$dir/$xpath[Target]")); //~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");
490. $numFmts = null;
491. if ($xmlStyles && $xmlStyles->numFmts[0]) {
492. $numFmts = $xmlStyles->numFmts[0];
493. }
494. if (isset($numFmts) && ! is_null($numFmts)) {
495. $numFmts->registerXPathNamespace("sml", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
496. }
497. if (!$this->_readDataOnly && $xmlStyles) {
498. foreach ($xmlStyles->cellXfs->xf as $xf) {
499. $numFmt = PHPExcel_Style_NumberFormat:: FORMAT_GENERAL;
501. if ($xf["numFmtId"]) {
502. if (isset($numFmts)) {
503. $tmpNumFmt = self:: array_item($numFmts->xpath("sml:numFmt[@numFmtId=$xf[numFmtId]]"));
505. if (isset($tmpNumFmt["formatCode"])) {
506. $numFmt = (string) $tmpNumFmt["formatCode"];
507. }
508. }
510. if ((int)$xf["numFmtId"] < 164) {
511. $numFmt = PHPExcel_Style_NumberFormat:: builtInFormatCode((int)$xf["numFmtId"]);
512. }
513. }
514. //$numFmt = str_replace('mm', 'i', $numFmt);
515. //$numFmt = str_replace('h', 'H', $numFmt);
517. $style = (object) array(
518. "numFmt" => $numFmt,
519. "font" => $xmlStyles->fonts->font[ intval($xf["fontId"])],
520. "fill" => $xmlStyles->fills->fill[ intval($xf["fillId"])],
521. "border" => $xmlStyles->borders->border[ intval($xf["borderId"])],
522. "alignment" => $xf->alignment,
523. "protection" => $xf->protection,
524. );
525. $styles[] = $style;
527. // add style to cellXf collection
528. $objStyle = new PHPExcel_Style;
529. self::_readStyle($objStyle, $style);
530. $excel->addCellXf($objStyle);
531. }
533. foreach ($xmlStyles->cellStyleXfs->xf as $xf) {
534. $numFmt = PHPExcel_Style_NumberFormat:: FORMAT_GENERAL;
535. if ($numFmts && $xf["numFmtId"]) {
536. $tmpNumFmt = self:: array_item($numFmts->xpath("sml:numFmt[@numFmtId=$xf[numFmtId]]"));
537. if (isset($tmpNumFmt["formatCode"])) {
538. $numFmt = (string) $tmpNumFmt["formatCode"];
539. } else if ((int)$xf["numFmtId"] < 165) {
540. $numFmt = PHPExcel_Style_NumberFormat:: builtInFormatCode((int)$xf["numFmtId"]);
541. }
542. }
544. $cellStyle = (object) array(
545. "numFmt" => $numFmt,
546. "font" => $xmlStyles->fonts->font[ intval($xf["fontId"])],
547. "fill" => $xmlStyles->fills->fill[ intval($xf["fillId"])],
548. "border" => $xmlStyles->borders->border[ intval($xf["borderId"])],
549. "alignment" => $xf->alignment,
550. "protection" => $xf->protection,
551. );
552. $cellStyles[] = $cellStyle;
554. // add style to cellStyleXf collection
555. $objStyle = new PHPExcel_Style;
556. self::_readStyle($objStyle, $cellStyle);
557. $excel->addCellStyleXf($objStyle);
558. }
559. }
561. $dxfs = array();
562. if (!$this->_readDataOnly && $xmlStyles) {
563. if ($xmlStyles->dxfs) {
564. foreach ($xmlStyles->dxfs->dxf as $dxf) {
565. $style = new PHPExcel_Style;
566. self::_readStyle($style, $dxf);
567. $dxfs[] = $style;
568. }
569. }
571. if ($xmlStyles->cellStyles)
572. {
573. foreach ($xmlStyles->cellStyles->cellStyle as $cellStyle) {
574. if ( intval($cellStyle['builtinId']) == 0) {
575. if (isset($cellStyles[ intval($cellStyle['xfId'])])) {
576. // Set default style
577. $style = new PHPExcel_Style;
578. self::_readStyle($style, $cellStyles[ intval($cellStyle['xfId'])]);
580. // normal style, currently not using it for anything
581. }
582. }
583. }
584. }
585. }
587. $xmlWorkbook = simplexml_load_string($this->_getFromZipArchive($zip, "{$rel['Target']}")); //~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");
589. // Set base date
590. if ($xmlWorkbook->workbookPr) {
591. PHPExcel_Shared_Date:: setExcelCalendar( PHPExcel_Shared_Date:: CALENDAR_WINDOWS_1900);
592. if (isset($xmlWorkbook->workbookPr['date1904'])) {
593. $date1904 = (string)$xmlWorkbook->workbookPr['date1904'];
594. if ($date1904 == "true" || $date1904 == "1") {
595. PHPExcel_Shared_Date:: setExcelCalendar( PHPExcel_Shared_Date:: CALENDAR_MAC_1904);
596. }
597. }
598. }
600. $sheetId = 0; // keep track of new sheet id in final workbook
601. $oldSheetId = -1; // keep track of old sheet id in final workbook
602. $countSkippedSheets = 0; // keep track of number of skipped sheets
603. $mapSheetId = array(); // mapping of sheet ids from old to new
605. if ($xmlWorkbook->sheets)
606. {
607. foreach ($xmlWorkbook->sheets->sheet as $eleSheet) {
608. ++$oldSheetId;
610. // Check if sheet should be skipped
611. if (isset($this->_loadSheetsOnly) && ! in_array((string) $eleSheet["name"], $this->_loadSheetsOnly)) {
612. ++$countSkippedSheets;
613. $mapSheetId[$oldSheetId] = null;
614. continue;
615. }
617. // Map old sheet id in original workbook to new sheet id.
618. // They will differ if loadSheetsOnly() is being used
619. $mapSheetId[$oldSheetId] = $oldSheetId - $countSkippedSheets;
621. // Load sheet
622. $docSheet = $excel->createSheet();
623. $docSheet->setTitle((string) $eleSheet["name"]);
624. $fileWorksheet = $worksheets[(string) self:: array_item($eleSheet->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "id")];
625. $xmlSheet = simplexml_load_string($this->_getFromZipArchive($zip, "$dir/$fileWorksheet")); //~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");
627. $sharedFormulas = array();
629. if (isset($eleSheet["state"]) && (string) $eleSheet["state"] != '') {
630. $docSheet->setSheetState( (string) $eleSheet["state"] );
631. }
633. if (isset($xmlSheet->sheetViews) && isset($xmlSheet->sheetViews->sheetView)) {
634. if (isset($xmlSheet->sheetViews->sheetView['zoomScale'])) {
635. $docSheet->getSheetView()->setZoomScale( intval($xmlSheet->sheetViews->sheetView['zoomScale']) );
636. }
638. if (isset($xmlSheet->sheetViews->sheetView['zoomScaleNormal'])) {
639. $docSheet->getSheetView()->setZoomScaleNormal( intval($xmlSheet->sheetViews->sheetView['zoomScaleNormal']) );
640. }
642. if (isset($xmlSheet->sheetViews->sheetView['showGridLines'])) {
643. $docSheet->setShowGridLines((string)$xmlSheet->sheetViews->sheetView['showGridLines'] ? true : false);
644. }
646. if (isset($xmlSheet->sheetViews->sheetView['showRowColHeaders'])) {
647. $docSheet->setShowRowColHeaders((string)$xmlSheet->sheetViews->sheetView['showRowColHeaders'] ? true : false);
648. }
650. if (isset($xmlSheet->sheetViews->sheetView['rightToLeft'])) {
651. $docSheet->setRightToLeft((string)$xmlSheet->sheetViews->sheetView['rightToLeft'] ? true : false);
652. }
654. if (isset($xmlSheet->sheetViews->sheetView->pane)) {
655. if (isset($xmlSheet->sheetViews->sheetView->pane['topLeftCell'])) {
656. $docSheet->freezePane( (string)$xmlSheet->sheetViews->sheetView->pane['topLeftCell'] );
657. } else {
658. $xSplit = 0;
659. $ySplit = 0;
661. if (isset($xmlSheet->sheetViews->sheetView->pane['xSplit'])) {
662. $xSplit = 1 + intval($xmlSheet->sheetViews->sheetView->pane['xSplit']);
663. }
665. if (isset($xmlSheet->sheetViews->sheetView->pane['ySplit'])) {
666. $ySplit = 1 + intval($xmlSheet->sheetViews->sheetView->pane['ySplit']);
667. }
669. $docSheet->freezePaneByColumnAndRow($xSplit, $ySplit);
670. }
671. }
673. if (isset($xmlSheet->sheetViews->sheetView->selection)) {
674. if (isset($xmlSheet->sheetViews->sheetView->selection['sqref'])) {
675. $sqref = (string)$xmlSheet->sheetViews->sheetView->selection['sqref'];
676. $sqref = explode(' ', $sqref);
677. $sqref = $sqref[0];
678. $docSheet->setSelectedCells($sqref);
679. }
680. }
682. }
684. if (isset($xmlSheet->sheetPr) && isset($xmlSheet->sheetPr->tabColor)) {
685. if (isset($xmlSheet->sheetPr->tabColor['rgb'])) {
686. $docSheet->getTabColor()->setARGB( (string)$xmlSheet->sheetPr->tabColor['rgb'] );
687. }
688. }
690. if (isset($xmlSheet->sheetPr) && isset($xmlSheet->sheetPr->outlinePr)) {
691. if (isset($xmlSheet->sheetPr->outlinePr['summaryRight']) && $xmlSheet->sheetPr->outlinePr['summaryRight'] == false) {
692. $docSheet->setShowSummaryRight(false);
693. } else {
694. $docSheet->setShowSummaryRight(true);
695. }
697. if (isset($xmlSheet->sheetPr->outlinePr['summaryBelow']) && $xmlSheet->sheetPr->outlinePr['summaryBelow'] == false) {
698. $docSheet->setShowSummaryBelow(false);
699. } else {
700. $docSheet->setShowSummaryBelow(true);
701. }
702. }
704. if (isset($xmlSheet->sheetPr) && isset($xmlSheet->sheetPr->pageSetUpPr)) {
705. if (isset($xmlSheet->sheetPr->pageSetUpPr['fitToPage']) && $xmlSheet->sheetPr->pageSetUpPr['fitToPage'] == false) {
706. $docSheet->getPageSetup()->setFitToPage(false);
707. } else {
708. $docSheet->getPageSetup()->setFitToPage(true);
709. }
710. }
712. if (isset($xmlSheet->sheetFormatPr)) {
713. if (isset($xmlSheet->sheetFormatPr['customHeight']) && ((string)$xmlSheet->sheetFormatPr['customHeight'] == '1' || strtolower((string)$xmlSheet->sheetFormatPr['customHeight']) == 'true') && isset($xmlSheet->sheetFormatPr['defaultRowHeight'])) {
714. $docSheet->getDefaultRowDimension()->setRowHeight( (float)$xmlSheet->sheetFormatPr['defaultRowHeight'] );
715. }
716. if (isset($xmlSheet->sheetFormatPr['defaultColWidth'])) {
717. $docSheet->getDefaultColumnDimension()->setWidth( (float)$xmlSheet->sheetFormatPr['defaultColWidth'] );
718. }
719. }
721. if (isset($xmlSheet->cols) && !$this->_readDataOnly) {
722. foreach ($xmlSheet->cols->col as $col) {
723. for ($i = intval($col["min"]) - 1; $i < intval($col["max"]); ++$i) {
724. if ($col["style"] && !$this->_readDataOnly) {
725. $docSheet->getColumnDimension( PHPExcel_Cell:: stringFromColumnIndex($i))->setXfIndex( intval($col["style"]));
726. }
727. if ($col["bestFit"]) {
728. //$docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setAutoSize(true);
729. }
730. if ($col["hidden"]) {
731. $docSheet->getColumnDimension( PHPExcel_Cell:: stringFromColumnIndex($i))->setVisible(false);
732. }
733. if ($col["collapsed"]) {
734. $docSheet->getColumnDimension( PHPExcel_Cell:: stringFromColumnIndex($i))->setCollapsed(true);
735. }
736. if ($col["outlineLevel"] > 0) {
737. $docSheet->getColumnDimension( PHPExcel_Cell:: stringFromColumnIndex($i))->setOutlineLevel( intval($col["outlineLevel"]));
738. }
739. $docSheet->getColumnDimension( PHPExcel_Cell:: stringFromColumnIndex($i))->setWidth( floatval($col["width"]));
741. if ( intval($col["max"]) == 16384) {
742. break;
743. }
744. }
745. }
746. }
748. if (isset($xmlSheet->printOptions) && !$this->_readDataOnly) {
749. if ($xmlSheet->printOptions['gridLinesSet'] == 'true' && $xmlSheet->printOptions['gridLinesSet'] == '1') {
750. $docSheet->setShowGridlines(true);
751. }
753. if ($xmlSheet->printOptions['gridLines'] == 'true' || $xmlSheet->printOptions['gridLines'] == '1') {
754. $docSheet->setPrintGridlines(true);
755. }
757. if ($xmlSheet->printOptions['horizontalCentered']) {
758. $docSheet->getPageSetup()->setHorizontalCentered(true);
759. }
760. if ($xmlSheet->printOptions['verticalCentered']) {
761. $docSheet->getPageSetup()->setVerticalCentered(true);
762. }
763. }
765. if ($xmlSheet && $xmlSheet->sheetData && $xmlSheet->sheetData->row) {
766. foreach ($xmlSheet->sheetData->row as $row) {
767. if ($row["ht"] && !$this->_readDataOnly) {
768. $docSheet->getRowDimension( intval($row["r"]))->setRowHeight( floatval($row["ht"]));
769. }
770. if ($row["hidden"] && !$this->_readDataOnly) {
771. $docSheet->getRowDimension( intval($row["r"]))->setVisible(false);
772. }
773. if ($row["collapsed"]) {
774. $docSheet->getRowDimension( intval($row["r"]))->setCollapsed(true);
775. }
776. if ($row["outlineLevel"] > 0) {
777. $docSheet->getRowDimension( intval($row["r"]))->setOutlineLevel( intval($row["outlineLevel"]));
778. }
779. if ($row["s"] && !$this->_readDataOnly) {
780. $docSheet->getRowDimension( intval($row["r"]))->setXfIndex( intval($row["s"]));
781. }
783. foreach ($row->c as $c) {
784. $r = (string) $c["r"];
785. $cellDataType = (string) $c["t"];
786. $value = null;
787. $calculatedValue = null;
789. // Read cell?
790. if (! is_null($this-> getReadFilter())) {
791. $coordinates = PHPExcel_Cell:: coordinateFromString($r);
793. if (!$this-> getReadFilter()->readCell($coordinates[0], $coordinates[1], $docSheet->getTitle())) {
794. continue;
795. }
796. }
798. // echo '<b>Reading cell '.$coordinates[0].$coordinates[1].'</b><br />';
799. // print_r($c);
800. // echo '<br />';
801. // echo 'Cell Data Type is '.$cellDataType.': ';
802. //
803. // Read cell!
804. switch ($cellDataType) {
805. case "s":
806. // echo 'String<br />';
807. if ((string)$c->v != '') {
808. $value = $sharedStrings[ intval($c->v)];
810. if ($value instanceof PHPExcel_RichText) {
811. $value = clone $value;
812. }
813. } else {
814. $value = '';
815. }
817. break;
818. case "b":
819. // echo 'Boolean<br />';
820. if (!isset($c->f)) {
821. $value = self::_castToBool($c);
822. } else {
823. // Formula
824. $this->_castToFormula($c,$r,$cellDataType,$value,$calculatedValue,$sharedFormulas,'_castToBool');
825. if (isset($c->f['t'])) {
826. $att = array();
827. $att = $c->f;
828. $docSheet->getCell($r)->setFormulaAttributes($att);
829. }
830. // echo '$calculatedValue = '.$calculatedValue.'<br />';
831. }
832. break;
833. case "inlineStr":
834. // echo 'Inline String<br />';
835. $value = $this->_parseRichText($c->is);
837. break;
838. case "e":
839. // echo 'Error<br />';
840. if (!isset($c->f)) {
841. $value = self::_castToError($c);
842. } else {
843. // Formula
844. $this->_castToFormula($c,$r,$cellDataType,$value,$calculatedValue,$sharedFormulas,'_castToError');
845. // echo '$calculatedValue = '.$calculatedValue.'<br />';
846. }
848. break;
850. default:
851. // echo 'Default<br />';
852. if (!isset($c->f)) {
853. // echo 'Not a Formula<br />';
854. $value = self::_castToString($c);
855. } else {
856. // echo 'Treat as Formula<br />';
857. // Formula
858. $this->_castToFormula($c,$r,$cellDataType,$value,$calculatedValue,$sharedFormulas,'_castToString');
859. // echo '$calculatedValue = '.$calculatedValue.'<br />';
860. }
862. break;
863. }
864. // echo 'Value is '.$value.'<br />';
866. // Check for numeric values
867. if ( is_numeric($value) && $cellDataType != 's') {
868. if ($value == (int)$value) $value = (int)$value;
869. elseif ($value == (float)$value) $value = (float)$value;
870. elseif ($value == (double)$value) $value = (double)$value;
871. }
873. // Rich text?
874. if ($value instanceof PHPExcel_RichText && $this->_readDataOnly) {
875. $value = $value->getPlainText();
876. }
878. $cell = $docSheet->getCell($r);
879. // Assign value
880. if ($cellDataType != '') {
881. $cell->setValueExplicit($value, $cellDataType);
882. } else {
883. $cell->setValue($value);
884. }
885. if (! is_null($calculatedValue)) {
886. $cell->setCalculatedValue($calculatedValue);
887. }
889. // Style information?
890. if ($c["s"] && !$this->_readDataOnly) {
891. // no style index means 0, it seems
892. $cell->setXfIndex(isset($styles[ intval($c["s"])]) ?
893. intval($c["s"]) : 0);
894. }
895. }
896. }
897. }
899. $conditionals = array();
900. if (!$this->_readDataOnly && $xmlSheet && $xmlSheet->conditionalFormatting) {
901. foreach ($xmlSheet->conditionalFormatting as $conditional) {
902. foreach ($conditional->cfRule as $cfRule) {
903. if (
904. (
905. (string)$cfRule["type"] == PHPExcel_Style_Conditional:: CONDITION_NONE ||
906. (string)$cfRule["type"] == PHPExcel_Style_Conditional:: CONDITION_CELLIS ||
907. (string)$cfRule["type"] == PHPExcel_Style_Conditional:: CONDITION_CONTAINSTEXT ||
908. (string)$cfRule["type"] == PHPExcel_Style_Conditional:: CONDITION_EXPRESSION
909. ) && isset($dxfs[ intval($cfRule["dxfId"])])
910. ) {
911. $conditionals[(string) $conditional["sqref"]][ intval($cfRule["priority"])] = $cfRule;
912. }
913. }
914. }
916. foreach ($conditionals as $ref => $cfRules) {
917. ksort($cfRules);
918. $conditionalStyles = array();
919. foreach ($cfRules as $cfRule) {
920. $objConditional = new PHPExcel_Style_Conditional();
921. $objConditional->setConditionType((string)$cfRule["type"]);
922. $objConditional->setOperatorType((string)$cfRule["operator"]);
924. if ((string)$cfRule["text"] != '') {
925. $objConditional->setText((string)$cfRule["text"]);
926. }
928. if ( count($cfRule->formula) > 1) {
929. foreach ($cfRule->formula as $formula) {
930. $objConditional->addCondition((string)$formula);
931. }
932. } else {
933. $objConditional->addCondition((string)$cfRule->formula);
934. }
935. $objConditional->setStyle(clone $dxfs[ intval($cfRule["dxfId"])]);
936. $conditionalStyles[] = $objConditional;
937. }
939. // Extract all cell references in $ref
940. $aReferences = PHPExcel_Cell:: extractAllCellReferencesInRange($ref);
941. foreach ($aReferences as $reference) {
942. $docSheet->getStyle($reference)->setConditionalStyles($conditionalStyles);
943. }
944. }
945. }
947. $aKeys = array("sheet", "objects", "scenarios", "formatCells", "formatColumns", "formatRows", "insertColumns", "insertRows", "insertHyperlinks", "deleteColumns", "deleteRows", "selectLockedCells", "sort", "autoFilter", "pivotTables", "selectUnlockedCells");
948. if (!$this->_readDataOnly && $xmlSheet && $xmlSheet->sheetProtection) {
949. foreach ($aKeys as $key) {
950. $method = "set" . ucfirst($key);
951. $docSheet->getProtection()->$method($xmlSheet->sheetProtection[$key] == "true");
952. }
953. }
955. if (!$this->_readDataOnly && $xmlSheet && $xmlSheet->sheetProtection) {
956. $docSheet->getProtection()->setPassword((string) $xmlSheet->sheetProtection["password"], true);
957. if ($xmlSheet->protectedRanges->protectedRange) {
958. foreach ($xmlSheet->protectedRanges->protectedRange as $protectedRange) {
959. $docSheet->protectCells((string) $protectedRange["sqref"], (string) $protectedRange["password"], true);
960. }
961. }
962. }
964. if ($xmlSheet && $xmlSheet->autoFilter && !$this->_readDataOnly) {
965. $docSheet->setAutoFilter((string) $xmlSheet->autoFilter["ref"]);
966. }
968. if ($xmlSheet && $xmlSheet->mergeCells && $xmlSheet->mergeCells->mergeCell && !$this->_readDataOnly) {
969. foreach ($xmlSheet->mergeCells->mergeCell as $mergeCell) {
970. $docSheet->mergeCells((string) $mergeCell["ref"]);
971. }
972. }
974. if ($xmlSheet && $xmlSheet->pageMargins && !$this->_readDataOnly) {
975. $docPageMargins = $docSheet->getPageMargins();
976. $docPageMargins->setLeft( floatval($xmlSheet->pageMargins["left"]));
977. $docPageMargins->setRight( floatval($xmlSheet->pageMargins["right"]));
978. $docPageMargins->setTop( floatval($xmlSheet->pageMargins["top"]));
979. $docPageMargins->setBottom( floatval($xmlSheet->pageMargins["bottom"]));
980. $docPageMargins->setHeader( floatval($xmlSheet->pageMargins["header"]));
981. $docPageMargins->setFooter( floatval($xmlSheet->pageMargins["footer"]));
982. }
984. if ($xmlSheet && $xmlSheet->pageSetup && !$this->_readDataOnly) {
985. $docPageSetup = $docSheet->getPageSetup();
987. if (isset($xmlSheet->pageSetup["orientation"])) {
988. $docPageSetup->setOrientation((string) $xmlSheet->pageSetup["orientation"]);
989. }
990. if (isset($xmlSheet->pageSetup["paperSize"])) {
991. $docPageSetup->setPaperSize( intval($xmlSheet->pageSetup["paperSize"]));
992. }
993. if (isset($xmlSheet->pageSetup["scale"])) {
994. $docPageSetup->setScale( intval($xmlSheet->pageSetup["scale"]), false);
995. }
996. if (isset($xmlSheet->pageSetup["fitToHeight"]) && intval($xmlSheet->pageSetup["fitToHeight"]) >= 0) {
997. $docPageSetup->setFitToHeight( intval($xmlSheet->pageSetup["fitToHeight"]), false);
998. }
999. if (isset($xmlSheet->pageSetup["fitToWidth"]) && intval($xmlSheet->pageSetup["fitToWidth"]) >= 0) {
1000. $docPageSetup->setFitToWidth( intval($xmlSheet->pageSetup["fitToWidth"]), false);
1001. }
1002. if (isset($xmlSheet->pageSetup["firstPageNumber"]) && isset($xmlSheet->pageSetup["useFirstPageNumber"]) &&
1003. ((string)$xmlSheet->pageSetup["useFirstPageNumber"] == 'true' || (string)$xmlSheet->pageSetup["useFirstPageNumber"] == '1')) {
1004. $docPageSetup->setFirstPageNumber( intval($xmlSheet->pageSetup["firstPageNumber"]));
1005. }
1006. }
1008. if ($xmlSheet && $xmlSheet->headerFooter && !$this->_readDataOnly) {
1009. $docHeaderFooter = $docSheet->getHeaderFooter();
1011. if (isset($xmlSheet->headerFooter["differentOddEven"]) &&
1012. ((string)$xmlSheet->headerFooter["differentOddEven"] == 'true' || (string)$xmlSheet->headerFooter["differentOddEven"] == '1')) {
1013. $docHeaderFooter->setDifferentOddEven(true);
1014. } else {
1015. $docHeaderFooter->setDifferentOddEven(false);
1016. }
1017. if (isset($xmlSheet->headerFooter["differentFirst"]) &&
1018. ((string)$xmlSheet->headerFooter["differentFirst"] == 'true' || (string)$xmlSheet->headerFooter["differentFirst"] == '1')) {
1019. $docHeaderFooter->setDifferentFirst(true);
1020. } else {
1021. $docHeaderFooter->setDifferentFirst(false);
1022. }
1023. if (isset($xmlSheet->headerFooter["scaleWithDoc"]) &&
1024. ((string)$xmlSheet->headerFooter["scaleWithDoc"] == 'false' || (string)$xmlSheet->headerFooter["scaleWithDoc"] == '0')) {
1025. $docHeaderFooter->setScaleWithDocument(false);
1026. } else {
1027. $docHeaderFooter->setScaleWithDocument(true);
1028. }
1029. if (isset($xmlSheet->headerFooter["alignWithMargins"]) &&
1030. ((string)$xmlSheet->headerFooter["alignWithMargins"] == 'false' || (string)$xmlSheet->headerFooter["alignWithMargins"] == '0')) {
1031. $docHeaderFooter->setAlignWithMargins(false);
1032. } else {
1033. $docHeaderFooter->setAlignWithMargins(true);
1034. }
1036. $docHeaderFooter->setOddHeader((string) $xmlSheet->headerFooter->oddHeader);
1037. $docHeaderFooter->setOddFooter((string) $xmlSheet->headerFooter->oddFooter);
1038. $docHeaderFooter->setEvenHeader((string) $xmlSheet->headerFooter->evenHeader);
1039. $docHeaderFooter->setEvenFooter((string) $xmlSheet->headerFooter->evenFooter);
1040. $docHeaderFooter->setFirstHeader((string) $xmlSheet->headerFooter->firstHeader);
1041. $docHeaderFooter->setFirstFooter((string) $xmlSheet->headerFooter->firstFooter);
1042. }
1044. if ($xmlSheet && $xmlSheet->rowBreaks && $xmlSheet->rowBreaks->brk && !$this->_readDataOnly) {
1045. foreach ($xmlSheet->rowBreaks->brk as $brk) {
1046. if ($brk["man"]) {
1047. $docSheet->setBreak("A$brk[id]", PHPExcel_Worksheet:: BREAK_ROW);
1048. }
1049. }
1050. }
1051. if ($xmlSheet && $xmlSheet->colBreaks && $xmlSheet->colBreaks->brk && !$this->_readDataOnly) {
1052. foreach ($xmlSheet->colBreaks->brk as $brk) {
1053. if ($brk["man"]) {
1054. $docSheet->setBreak( PHPExcel_Cell:: stringFromColumnIndex($brk["id"]) . "1", PHPExcel_Worksheet:: BREAK_COLUMN);
1055. }
1056. }
1057. }
1059. if ($xmlSheet && $xmlSheet->dataValidations && !$this->_readDataOnly) {
1060. foreach ($xmlSheet->dataValidations->dataValidation as $dataValidation) {
1061. // Uppercase coordinate
1062. $range = strtoupper($dataValidation["sqref"]);
1063. $rangeSet = explode(' ',$range);
1064. foreach($rangeSet as $range) {
1065. $stRange = $docSheet->shrinkRangeToFit($range);
1067. // Extract all cell references in $range
1068. $aReferences = PHPExcel_Cell:: extractAllCellReferencesInRange($stRange);
1069. foreach ($aReferences as $reference) {
1070. // Create validation
1071. $docValidation = $docSheet->getCell($reference)->getDataValidation();
1072. $docValidation->setType((string) $dataValidation["type"]);
1073. $docValidation->setErrorStyle((string) $dataValidation["errorStyle"]);
1074. $docValidation->setOperator((string) $dataValidation["operator"]);
1075. $docValidation->setAllowBlank($dataValidation["allowBlank"] != 0);
1076. $docValidation->setShowDropDown($dataValidation["showDropDown"] == 0);
1077. $docValidation->setShowInputMessage($dataValidation["showInputMessage"] != 0);
1078. $docValidation->setShowErrorMessage($dataValidation["showErrorMessage"] != 0);
1079. $docValidation->setErrorTitle((string) $dataValidation["errorTitle"]);
1080. $docValidation->setError((string) $dataValidation["error"]);
1081. $docValidation->setPromptTitle((string) $dataValidation["promptTitle"]);
1082. $docValidation->setPrompt((string) $dataValidation["prompt"]);
1083. $docValidation->setFormula1((string) $dataValidation->formula1);
1084. $docValidation->setFormula2((string) $dataValidation->formula2);
1085. }
1086. }
1087. }
1088. }
1090. // Add hyperlinks
1091. $hyperlinks = array();
1092. if (!$this->_readDataOnly) {
1093. // Locate hyperlink relations
1094. if ($zip->locateName( dirname("$dir/$fileWorksheet") . "/_rels/" . basename($fileWorksheet) . ".rels")) {
1095. $relsWorksheet = simplexml_load_string($this->_getFromZipArchive($zip, dirname("$dir/$fileWorksheet") . "/_rels/" . basename($fileWorksheet) . ".rels") ); //~ http://schemas.openxmlformats.org/package/2006/relationships");
1096. foreach ($relsWorksheet->Relationship as $ele) {
1097. if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/hyperlink") {
1098. $hyperlinks[(string)$ele["Id"]] = (string)$ele["Target"];
1099. }
1100. }
1101. }
1103. // Loop through hyperlinks
1104. if ($xmlSheet && $xmlSheet->hyperlinks) {
1105. foreach ($xmlSheet->hyperlinks->hyperlink as $hyperlink) {
1106. // Link url
1107. $linkRel = $hyperlink->attributes('http://schemas.openxmlformats.org/officeDocument/2006/relationships');
1109. foreach ( PHPExcel_Cell:: extractAllCellReferencesInRange($hyperlink['ref']) as $cellReference) {
1110. $cell = $docSheet->getCell( $cellReference );
1111. if (isset($linkRel['id'])) {
1112. $cell->getHyperlink()->setUrl( $hyperlinks[ (string)$linkRel['id'] ] );
1113. }
1114. if (isset($hyperlink['location'])) {
1115. $cell->getHyperlink()->setUrl( 'sheet://' . (string)$hyperlink['location'] );
1116. }
1118. // Tooltip
1119. if (isset($hyperlink['tooltip'])) {
1120. $cell->getHyperlink()->setTooltip( (string)$hyperlink['tooltip'] );
1121. }
1122. }
1123. }
1124. }
1125. }
1127. // Add comments
1128. $comments = array();
1129. $vmlComments = array();
1130. if (!$this->_readDataOnly) {
1131. // Locate comment relations
1132. if ($zip->locateName( dirname("$dir/$fileWorksheet") . "/_rels/" . basename($fileWorksheet) . ".rels")) {
1133. $relsWorksheet = simplexml_load_string($this->_getFromZipArchive($zip, dirname("$dir/$fileWorksheet") . "/_rels/" . basename($fileWorksheet) . ".rels") ); //~ http://schemas.openxmlformats.org/package/2006/relationships");
1134. foreach ($relsWorksheet->Relationship as $ele) {
1135. if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/comments") {
1136. $comments[(string)$ele["Id"]] = (string)$ele["Target"];
1137. }
1138. if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/vmlDrawing") {
1139. $vmlComments[(string)$ele["Id"]] = (string)$ele["Target"];
1140. }
1141. }
1142. }
1144. // Loop through comments
1145. foreach ($comments as $relName => $relPath) {
1146. // Load comments file
1147. $relPath = PHPExcel_Shared_File:: realpath( dirname("$dir/$fileWorksheet") . "/" . $relPath);
1148. $commentsFile = simplexml_load_string($this->_getFromZipArchive($zip, $relPath) );
1150. // Utility variables
1151. $authors = array();
1153. // Loop through authors
1154. foreach ($commentsFile->authors->author as $author) {
1155. $authors[] = (string)$author;
1156. }
1158. // Loop through contents
1159. foreach ($commentsFile->commentList->comment as $comment) {
1160. $docSheet->getComment( (string)$comment['ref'] )->setAuthor( $authors[(string)$comment['authorId']] );
1161. $docSheet->getComment( (string)$comment['ref'] )->setText( $this->_parseRichText($comment->text) );
1162. }
1163. }
1165. // Loop through VML comments
1166. foreach ($vmlComments as $relName => $relPath) {
1167. // Load VML comments file
1168. $relPath = PHPExcel_Shared_File:: realpath( dirname("$dir/$fileWorksheet") . "/" . $relPath);
1169. $vmlCommentsFile = simplexml_load_string( $this->_getFromZipArchive($zip, $relPath) );
1170. $vmlCommentsFile->registerXPathNamespace('v', 'urn:schemas-microsoft-com:vml');
1172. $shapes = $vmlCommentsFile->xpath('//v:shape');
1173. foreach ($shapes as $shape) {
1174. $shape->registerXPathNamespace('v', 'urn:schemas-microsoft-com:vml');
1176. if (isset($shape['style'])) {
1177. $style = (string)$shape['style'];
1178. $fillColor = strtoupper( substr( (string)$shape['fillcolor'], 1 ) );
1179. $column = null;
1180. $row = null;
1182. $clientData = $shape->xpath('.//x:ClientData');
1183. if ( is_array($clientData) && count($clientData) > 0) {
1184. $clientData = $clientData[0];
1186. if ( isset($clientData['ObjectType']) && (string)$clientData['ObjectType'] == 'Note' ) {
1187. $temp = $clientData->xpath('.//x:Row');
1188. if ( is_array($temp)) $row = $temp[0];
1190. $temp = $clientData->xpath('.//x:Column');
1191. if ( is_array($temp)) $column = $temp[0];
1192. }
1193. }
1195. if (! is_null($column) && ! is_null($row)) {
1196. // Set comment properties
1197. $comment = $docSheet->getCommentByColumnAndRow($column, $row + 1);
1198. $comment->getFillColor()->setRGB( $fillColor );
1200. // Parse style
1201. $styleArray = explode(';', str_replace(' ', '', $style));
1202. foreach ($styleArray as $stylePair) {
1203. $stylePair = explode(':', $stylePair);
1205. if ($stylePair[0] == 'margin-left') $comment->setMarginLeft($stylePair[1]);
1206. if ($stylePair[0] == 'margin-top') $comment->setMarginTop($stylePair[1]);
1207. if ($stylePair[0] == 'width') $comment->setWidth($stylePair[1]);
1208. if ($stylePair[0] == 'height') $comment->setHeight($stylePair[1]);
1209. if ($stylePair[0] == 'visibility') $comment->setVisible( $stylePair[1] == 'visible' );
1211. }
1212. }
1213. }
1214. }
1215. }
1217. // Header/footer images
1218. if ($xmlSheet && $xmlSheet->legacyDrawingHF && !$this->_readDataOnly) {
1219. if ($zip->locateName( dirname("$dir/$fileWorksheet") . "/_rels/" . basename($fileWorksheet) . ".rels")) {
1220. $relsWorksheet = simplexml_load_string($this->_getFromZipArchive($zip, dirname("$dir/$fileWorksheet") . "/_rels/" . basename($fileWorksheet) . ".rels") ); //~ http://schemas.openxmlformats.org/package/2006/relationships");
1221. $vmlRelationship = '';
1223. foreach ($relsWorksheet->Relationship as $ele) {
1224. if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/vmlDrawing") {
1225. $vmlRelationship = self:: dir_add("$dir/$fileWorksheet", $ele["Target"]);
1226. }
1227. }
1229. if ($vmlRelationship != '') {
1230. // Fetch linked images
1231. $relsVML = simplexml_load_string($this->_getFromZipArchive($zip, dirname($vmlRelationship) . '/_rels/' . basename($vmlRelationship) . '.rels' )); //~ http://schemas.openxmlformats.org/package/2006/relationships");
1232. $drawings = array();
1233. foreach ($relsVML->Relationship as $ele) {
1234. if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/image") {
1235. $drawings[(string) $ele["Id"]] = self:: dir_add($vmlRelationship, $ele["Target"]);
1236. }
1237. }
1239. // Fetch VML document
1240. $vmlDrawing = simplexml_load_string($this->_getFromZipArchive($zip, $vmlRelationship));
1241. $vmlDrawing->registerXPathNamespace('v', 'urn:schemas-microsoft-com:vml');
1243. $hfImages = array();
1245. $shapes = $vmlDrawing->xpath('//v:shape');
1246. foreach ($shapes as $shape) {
1247. $shape->registerXPathNamespace('v', 'urn:schemas-microsoft-com:vml');
1248. $imageData = $shape->xpath('//v:imagedata');
1249. $imageData = $imageData[0];
1251. $imageData = $imageData->attributes('urn:schemas-microsoft-com:office:office');
1252. $style = self:: toCSSArray( (string)$shape['style'] );
1254. $hfImages[ (string)$shape['id'] ] = new PHPExcel_Worksheet_HeaderFooterDrawing();
1255. if (isset($imageData['title'])) {
1256. $hfImages[ (string)$shape['id'] ]->setName( (string)$imageData['title'] );
1257. }
1259. $hfImages[ (string)$shape['id'] ]->setPath("zip://$pFilename#" . $drawings[(string)$imageData['relid']], false);
1260. $hfImages[ (string)$shape['id'] ]->setResizeProportional(false);
1261. $hfImages[ (string)$shape['id'] ]->setWidth($style['width']);
1262. $hfImages[ (string)$shape['id'] ]->setHeight($style['height']);
1263. $hfImages[ (string)$shape['id'] ]->setOffsetX($style['margin-left']);
1264. $hfImages[ (string)$shape['id'] ]->setOffsetY($style['margin-top']);
1265. $hfImages[ (string)$shape['id'] ]->setResizeProportional(true);
1266. }
1268. $docSheet->getHeaderFooter()->setImages($hfImages);
1269. }
1270. }
1271. }
1273. }
1275. // TODO: Make sure drawings and graph are loaded differently!
1276. if ($zip->locateName( dirname("$dir/$fileWorksheet") . "/_rels/" . basename($fileWorksheet) . ".rels")) {
1277. $relsWorksheet = simplexml_load_string($this->_getFromZipArchive($zip, dirname("$dir/$fileWorksheet") . "/_rels/" . basename($fileWorksheet) . ".rels") ); //~ http://schemas.openxmlformats.org/package/2006/relationships");
1278. $drawings = array();
1279. foreach ($relsWorksheet->Relationship as $ele) {
1280. if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/drawing") {
1281. $drawings[(string) $ele["Id"]] = self:: dir_add("$dir/$fileWorksheet", $ele["Target"]);
1282. }
1283. }
1284. if ($xmlSheet->drawing && !$this->_readDataOnly) {
1285. foreach ($xmlSheet->drawing as $drawing) {
1286. $fileDrawing = $drawings[(string) self:: array_item($drawing->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "id")];
1287. $relsDrawing = simplexml_load_string($this->_getFromZipArchive($zip, dirname($fileDrawing) . "/_rels/" . basename($fileDrawing) . ".rels") ); //~ http://schemas.openxmlformats.org/package/2006/relationships");
1288. $images = array();
1290. if ($relsDrawing && $relsDrawing->Relationship) {
1291. foreach ($relsDrawing->Relationship as $ele) {
1292. if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/image") {
1293. $images[(string) $ele["Id"]] = self:: dir_add($fileDrawing, $ele["Target"]);
1294. }
1295. }
1296. }
1297. $xmlDrawing = simplexml_load_string($this->_getFromZipArchive($zip, $fileDrawing))->children("http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing");
1299. if ($xmlDrawing->oneCellAnchor) {
1300. foreach ($xmlDrawing->oneCellAnchor as $oneCellAnchor) {
1301. if ($oneCellAnchor->pic->blipFill) {
1302. $blip = $oneCellAnchor->pic->blipFill->children("http://schemas.openxmlformats.org/drawingml/2006/main")->blip;
1303. $xfrm = $oneCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->xfrm;
1304. $outerShdw = $oneCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->effectLst->outerShdw;
1305. $objDrawing = new PHPExcel_Worksheet_Drawing;
1306. $objDrawing->setName((string) self:: array_item($oneCellAnchor->pic->nvPicPr->cNvPr->attributes(), "name"));
1307. $objDrawing->setDescription((string) self:: array_item($oneCellAnchor->pic->nvPicPr->cNvPr->attributes(), "descr"));
1308. $objDrawing->setPath("zip://$pFilename#" . $images[(string) self:: array_item($blip->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "embed")], false);
1309. $objDrawing->setCoordinates( PHPExcel_Cell:: stringFromColumnIndex($oneCellAnchor->from->col) . ($oneCellAnchor->from->row + 1));
1310. $objDrawing->setOffsetX( PHPExcel_Shared_Drawing:: EMUToPixels($oneCellAnchor->from->colOff));
1311. $objDrawing->setOffsetY( PHPExcel_Shared_Drawing:: EMUToPixels($oneCellAnchor->from->rowOff));
1312. $objDrawing->setResizeProportional(false);
1313. $objDrawing->setWidth( PHPExcel_Shared_Drawing:: EMUToPixels(self:: array_item($oneCellAnchor->ext->attributes(), "cx")));
1314. $objDrawing->setHeight( PHPExcel_Shared_Drawing:: EMUToPixels(self:: array_item($oneCellAnchor->ext->attributes(), "cy")));
1315. if ($xfrm) {
1316. $objDrawing->setRotation( PHPExcel_Shared_Drawing:: angleToDegrees(self:: array_item($xfrm->attributes(), "rot")));
1317. }
1318. if ($outerShdw) {
1319. $shadow = $objDrawing->getShadow();
1320. $shadow->setVisible(true);
1321. $shadow->setBlurRadius( PHPExcel_Shared_Drawing:: EMUTopixels(self:: array_item($outerShdw->attributes(), "blurRad")));
1322. $shadow->setDistance( PHPExcel_Shared_Drawing:: EMUTopixels(self:: array_item($outerShdw->attributes(), "dist")));
1323. $shadow->setDirection( PHPExcel_Shared_Drawing:: angleToDegrees(self:: array_item($outerShdw->attributes(), "dir")));
1324. $shadow->setAlignment((string) self:: array_item($outerShdw->attributes(), "algn"));
1325. $shadow->getColor()->setRGB(self:: array_item($outerShdw->srgbClr->attributes(), "val"));
1326. $shadow->setAlpha(self:: array_item($outerShdw->srgbClr->alpha->attributes(), "val") / 1000);
1327. }
1328. $objDrawing->setWorksheet($docSheet);
1329. }
1330. }
1331. }
1332. if ($xmlDrawing->twoCellAnchor) {
1333. foreach ($xmlDrawing->twoCellAnchor as $twoCellAnchor) {
1334. if ($twoCellAnchor->pic->blipFill) {
1335. $blip = $twoCellAnchor->pic->blipFill->children("http://schemas.openxmlformats.org/drawingml/2006/main")->blip;
1336. $xfrm = $twoCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->xfrm;
1337. $outerShdw = $twoCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->effectLst->outerShdw;
1338. $objDrawing = new PHPExcel_Worksheet_Drawing;
1339. $objDrawing->setName((string) self:: array_item($twoCellAnchor->pic->nvPicPr->cNvPr->attributes(), "name"));
1340. $objDrawing->setDescription((string) self:: array_item($twoCellAnchor->pic->nvPicPr->cNvPr->attributes(), "descr"));
1341. $objDrawing->setPath("zip://$pFilename#" . $images[(string) self:: array_item($blip->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "embed")], false);
1342. $objDrawing->setCoordinates( PHPExcel_Cell:: stringFromColumnIndex($twoCellAnchor->from->col) . ($twoCellAnchor->from->row + 1));
1343. $objDrawing->setOffsetX( PHPExcel_Shared_Drawing:: EMUToPixels($twoCellAnchor->from->colOff));
1344. $objDrawing->setOffsetY( PHPExcel_Shared_Drawing:: EMUToPixels($twoCellAnchor->from->rowOff));
1345. $objDrawing->setResizeProportional(false);
1347. $objDrawing->setWidth( PHPExcel_Shared_Drawing:: EMUToPixels(self:: array_item($xfrm->ext->attributes(), "cx")));
1348. $objDrawing->setHeight( PHPExcel_Shared_Drawing:: EMUToPixels(self:: array_item($xfrm->ext->attributes(), "cy")));
1350. if ($xfrm) {
1351. $objDrawing->setRotation( PHPExcel_Shared_Drawing:: angleToDegrees(self:: array_item($xfrm->attributes(), "rot")));
1352. }
1353. if ($outerShdw) {
1354. $shadow = $objDrawing->getShadow();
1355. $shadow->setVisible(true);
1356. $shadow->setBlurRadius( PHPExcel_Shared_Drawing:: EMUTopixels(self:: array_item($outerShdw->attributes(), "blurRad")));
1357. $shadow->setDistance( PHPExcel_Shared_Drawing:: EMUTopixels(self:: array_item($outerShdw->attributes(), "dist")));
1358. $shadow->setDirection( PHPExcel_Shared_Drawing:: angleToDegrees(self:: array_item($outerShdw->attributes(), "dir")));
1359. $shadow->setAlignment((string) self:: array_item($outerShdw->attributes(), "algn"));
1360. $shadow->getColor()->setRGB(self:: array_item($outerShdw->srgbClr->attributes(), "val"));
1361. $shadow->setAlpha(self:: array_item($outerShdw->srgbClr->alpha->attributes(), "val") / 1000);
1362. }
1363. $objDrawing->setWorksheet($docSheet);
1364. }
1365. }
1366. }
1368. }
1369. }
1370. }
1372. // Loop through definedNames
1373. if ($xmlWorkbook->definedNames) {
1374. foreach ($xmlWorkbook->definedNames->definedName as $definedName) {
1375. // Extract range
1376. $extractedRange = (string)$definedName;
1377. $extractedRange = preg_replace('/\'(\w+)\'\!/', '', $extractedRange);
1378. if (($spos = strpos($extractedRange,'!')) !== false) {
1379. $extractedRange = substr($extractedRange,0,$spos).str_replace('$', '', substr($extractedRange,$spos));
1380. } else {
1381. $extractedRange = str_replace('$', '', $extractedRange);
1382. }
1384. // Valid range?
1385. if ( stripos((string)$definedName, '#REF!') !== false || $extractedRange == '') {
1386. continue;
1387. }
1389. // Some definedNames are only applicable if we are on the same sheet...
1390. if ((string)$definedName['localSheetId'] != '' && (string)$definedName['localSheetId'] == $sheetId) {
1391. // Switch on type
1392. switch ((string)$definedName['name']) {
1394. case '_xlnm._FilterDatabase':
1395. $docSheet->setAutoFilter($extractedRange);
1396. break;
1398. case '_xlnm.Print_Titles':
1399. // Split $extractedRange
1400. $extractedRange = explode(',', $extractedRange);
1402. // Set print titles
1403. foreach ($extractedRange as $range) {
1404. $matches = array();
1406. // check for repeating columns, e g. 'A:A' or 'A:D'
1407. if ( preg_match('/^([A-Z]+)\:([A-Z]+)$/', $range, $matches)) {
1408. $docSheet->getPageSetup()->setColumnsToRepeatAtLeft(array($matches[1], $matches[2]));
1409. }
1410. // check for repeating rows, e.g. '1:1' or '1:5'
1411. elseif ( preg_match('/^(\d+)\:(\d+)$/', $range, $matches)) {
1412. $docSheet->getPageSetup()->setRowsToRepeatAtTop(array($matches[1], $matches[2]));
1413. }
1414. }
1415. break;
1417. case '_xlnm.Print_Area':
1418. $rangeSets = explode(',', $extractedRange); // FIXME: what if sheetname contains comma?
1419. $newRangeSets = array();
1420. foreach($rangeSets as $rangeSet) {
1421. $range = explode('!', $rangeSet); // FIXME: what if sheetname contains exclamation mark?
1422. $rangeSet = isset($range[1]) ? $range[1] : $range[0];
1423. $newRangeSets[] = str_replace('$', '', $rangeSet);
1424. }
1425. $docSheet->getPageSetup()->setPrintArea( implode(',',$newRangeSets));
1426. break;
1428. default:
1429. break;
1430. }
1431. }
1432. }
1433. }
1435. // Next sheet id
1436. ++$sheetId;
1437. }
1439. // Loop through definedNames
1440. if ($xmlWorkbook->definedNames) {
1441. foreach ($xmlWorkbook->definedNames->definedName as $definedName) {
1442. // Extract range
1443. $extractedRange = (string)$definedName;
1444. $extractedRange = preg_replace('/\'(\w+)\'\!/', '', $extractedRange);
1445. if (($spos = strpos($extractedRange,'!')) !== false) {
1446. $extractedRange = substr($extractedRange,0,$spos).str_replace('$', '', substr($extractedRange,$spos));
1447. } else {
1448. $extractedRange = str_replace('$', '', $extractedRange);
1449. }
1451. // Valid range?
1452. if ( stripos((string)$definedName, '#REF!') !== false || $extractedRange == '') {
1453. continue;
1454. }
1456. // Some definedNames are only applicable if we are on the same sheet...
1457. if ((string)$definedName['localSheetId'] != '') {
1458. // Local defined name
1459. // Switch on type
1460. switch ((string)$definedName['name']) {
1462. case '_xlnm._FilterDatabase':
1463. case '_xlnm.Print_Titles':
1464. case '_xlnm.Print_Area':
1465. break;
1467. default:
1468. $range = explode('!', (string)$definedName);
1469. if ( count($range) == 2) {
1470. $range[0] = str_replace("''", "'", $range[0]);
1471. $range[0] = str_replace("'", "", $range[0]);
1472. if ($worksheet = $docSheet->getParent()->getSheetByName($range[0])) {
1473. $extractedRange = str_replace('$', '', $range[1]);
1474. $scope = $docSheet->getParent()->getSheet((string)$definedName['localSheetId']);
1476. $excel->addNamedRange( new PHPExcel_NamedRange((string)$definedName['name'], $worksheet, $extractedRange, true, $scope) );
1477. }
1478. }
1479. break;
1480. }
1481. } else if (!isset($definedName['localSheetId'])) {
1482. // "Global" definedNames
1483. $locatedSheet = null;
1484. $extractedSheetName = '';
1485. if ( strpos( (string)$definedName, '!' ) !== false) {
1486. // Extract sheet name
1487. $extractedSheetName = PHPExcel_Worksheet:: extractSheetTitle( (string)$definedName, true );
1488. $extractedSheetName = $extractedSheetName[0];
1490. // Locate sheet
1491. $locatedSheet = $excel->getSheetByName($extractedSheetName);
1493. // Modify range
1494. $range = explode('!', $extractedRange);
1495. $extractedRange = isset($range[1]) ? $range[1] : $range[0];
1496. }
1498. if (! is_null($locatedSheet)) {
1499. $excel->addNamedRange( new PHPExcel_NamedRange((string)$definedName['name'], $locatedSheet, $extractedRange, false) );
1500. }
1501. }
1502. }
1503. }
1504. }
1506. if (!$this->_readDataOnly) {
1507. // active sheet index
1508. $activeTab = intval($xmlWorkbook->bookViews->workbookView["activeTab"]); // refers to old sheet index
1510. // keep active sheet index if sheet is still loaded, else first sheet is set as the active
1511. if (isset($mapSheetId[$activeTab]) && $mapSheetId[$activeTab] !== null) {
1512. $excel->setActiveSheetIndex($mapSheetId[$activeTab]);
1513. } else {
1514. if ($excel->getSheetCount() == 0)
1515. {
1516. $excel->createSheet();
1517. }
1518. $excel->setActiveSheetIndex(0);
1519. }
1520. }
1521. break;
1522. }
1524. }
1526. $zip->close();
1528. return $excel;
1529. }
1531. private static function _readColor($color, $background=false) {
1532. if (isset($color["rgb"])) {
1533. return (string)$color["rgb"];
1534. } else if (isset($color["indexed"])) {
1535. return PHPExcel_Style_Color:: indexedColor($color["indexed"],$background)->getARGB();
1536. } else if (isset($color["theme"])) {
1537. if (! is_null(self::$_theme)) {
1538. $returnColour = self::$_theme->getColourByIndex((int)$color["theme"]);
1539. if (isset($color["tint"])) {
1540. $tintAdjust = (float) $color["tint"];
1541. $returnColour = PHPExcel_Style_Color:: changeBrightness($returnColour, $tintAdjust);
1542. }
1543. return 'FF'.$returnColour;
1544. }
1545. }
1547. if ($background) {
1548. return 'FFFFFFFF';
1549. }
1550. return 'FF000000';
1551. }
1553. private static function _readStyle($docStyle, $style) {
1554. // format code
1555. if (isset($style->numFmt)) {
1556. $docStyle->getNumberFormat()->setFormatCode($style->numFmt);
1557. }
1559. // font
1560. if (isset($style->font)) {
1561. $docStyle->getFont()->setName((string) $style->font->name["val"]);
1562. $docStyle->getFont()->setSize((string) $style->font->sz["val"]);
1563. if (isset($style->font->b)) {
1564. $docStyle->getFont()->setBold(!isset($style->font->b["val"]) || $style->font->b["val"] == 'true' || $style->font->b["val"] == '1');
1565. }
1566. if (isset($style->font->i)) {
1567. $docStyle->getFont()->setItalic(!isset($style->font->i["val"]) || $style->font->i["val"] == 'true' || $style->font->i["val"] == '1');
1568. }
1569. if (isset($style->font->strike)) {
1570. $docStyle->getFont()->setStrikethrough(!isset($style->font->strike["val"]) || $style->font->strike["val"] == 'true' || $style->font->strike["val"] == '1');
1571. }
1572. $docStyle->getFont()->getColor()->setARGB(self::_readColor($style->font->color));
1574. if (isset($style->font->u) && !isset($style->font->u["val"])) {
1575. $docStyle->getFont()->setUnderline( PHPExcel_Style_Font:: UNDERLINE_SINGLE);
1576. } else if (isset($style->font->u) && isset($style->font->u["val"])) {
1577. $docStyle->getFont()->setUnderline((string)$style->font->u["val"]);
1578. }
1580. if (isset($style->font->vertAlign) && isset($style->font->vertAlign["val"])) {
1581. $vertAlign = strtolower((string)$style->font->vertAlign["val"]);
1582. if ($vertAlign == 'superscript') {
1583. $docStyle->getFont()->setSuperScript(true);
1584. }
1585. if ($vertAlign == 'subscript') {
1586. $docStyle->getFont()->setSubScript(true);
1587. }
1588. }
1589. }
1591. // fill
1592. if (isset($style->fill)) {
1593. if ($style->fill->gradientFill) {
1594. $gradientFill = $style->fill->gradientFill[0];
1595. if(! empty($gradientFill["type"])) {
1596. $docStyle->getFill()->setFillType((string) $gradientFill["type"]);
1597. }
1598. $docStyle->getFill()->setRotation( floatval($gradientFill["degree"]));
1599. $gradientFill->registerXPathNamespace("sml", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
1600. $docStyle->getFill()->getStartColor()->setARGB(self::_readColor( self:: array_item($gradientFill->xpath("sml:stop[@position=0]"))->color) );
1601. $docStyle->getFill()->getEndColor()->setARGB(self::_readColor( self:: array_item($gradientFill->xpath("sml:stop[@position=1]"))->color) );
1602. } elseif ($style->fill->patternFill) {
1603. $patternType = (string)$style->fill->patternFill["patternType"] != '' ? (string)$style->fill->patternFill["patternType"] : 'solid';
1604. $docStyle->getFill()->setFillType($patternType);
1605. if ($style->fill->patternFill->fgColor) {
1606. $docStyle->getFill()->getStartColor()->setARGB(self::_readColor($style->fill->patternFill->fgColor,true));
1607. } else {
1608. $docStyle->getFill()->getStartColor()->setARGB('FF000000');
1609. }
1610. if ($style->fill->patternFill->bgColor) {
1611. $docStyle->getFill()->getEndColor()->setARGB(self::_readColor($style->fill->patternFill->bgColor,true));
1612. }
1613. }
1614. }
1616. // border
1617. if (isset($style->border)) {
1618. $diagonalUp = false;
1619. $diagonalDown = false;
1620. if ($style->border["diagonalUp"] == 'true' || $style->border["diagonalUp"] == 1) {
1621. $diagonalUp = true;
1622. }
1623. if ($style->border["diagonalDown"] == 'true' || $style->border["diagonalDown"] == 1) {
1624. $diagonalDown = true;
1625. }
1626. if ($diagonalUp == false && $diagonalDown == false) {
1627. $docStyle->getBorders()->setDiagonalDirection( PHPExcel_Style_Borders:: DIAGONAL_NONE);
1628. } elseif ($diagonalUp == true && $diagonalDown == false) {
1629. $docStyle->getBorders()->setDiagonalDirection( PHPExcel_Style_Borders:: DIAGONAL_UP);
1630. } elseif ($diagonalUp == false && $diagonalDown == true) {
1631. $docStyle->getBorders()->setDiagonalDirection( PHPExcel_Style_Borders:: DIAGONAL_DOWN);
1632. } elseif ($diagonalUp == true && $diagonalDown == true) {
1633. $docStyle->getBorders()->setDiagonalDirection( PHPExcel_Style_Borders:: DIAGONAL_BOTH);
1634. }
1635. self::_readBorder($docStyle->getBorders()->getLeft(), $style->border->left);
1636. self::_readBorder($docStyle->getBorders()->getRight(), $style->border->right);
1637. self::_readBorder($docStyle->getBorders()->getTop(), $style->border->top);
1638. self::_readBorder($docStyle->getBorders()->getBottom(), $style->border->bottom);
1639. self::_readBorder($docStyle->getBorders()->getDiagonal(), $style->border->diagonal);
1640. }
1642. // alignment
1643. if (isset($style->alignment)) {
1644. $docStyle->getAlignment()->setHorizontal((string) $style->alignment["horizontal"]);
1645. $docStyle->getAlignment()->setVertical((string) $style->alignment["vertical"]);
1647. $textRotation = 0;
1648. if ((int)$style->alignment["textRotation"] <= 90) {
1649. $textRotation = (int)$style->alignment["textRotation"];
1650. } else if ((int)$style->alignment["textRotation"] > 90) {
1651. $textRotation = 90 - (int)$style->alignment["textRotation"];
1652. }
1654. $docStyle->getAlignment()->setTextRotation( intval($textRotation));
1655. $docStyle->getAlignment()->setWrapText( (string)$style->alignment["wrapText"] == "true" || (string)$style->alignment["wrapText"] == "1" );
1656. $docStyle->getAlignment()->setShrinkToFit( (string)$style->alignment["shrinkToFit"] == "true" || (string)$style->alignment["shrinkToFit"] == "1" );
1657. $docStyle->getAlignment()->setIndent( intval((string)$style->alignment["indent"]) > 0 ? intval((string)$style->alignment["indent"]) : 0 );
1658. }
1660. // protection
1661. if (isset($style->protection)) {
1662. if (isset($style->protection['locked'])) {
1663. if ((string)$style->protection['locked'] == 'true') {
1664. $docStyle->getProtection()->setLocked( PHPExcel_Style_Protection:: PROTECTION_PROTECTED);
1665. } else {
1666. $docStyle->getProtection()->setLocked( PHPExcel_Style_Protection:: PROTECTION_UNPROTECTED);
1667. }
1668. }
1670. if (isset($style->protection['hidden'])) {
1671. if ((string)$style->protection['hidden'] == 'true') {
1672. $docStyle->getProtection()->setHidden( PHPExcel_Style_Protection:: PROTECTION_PROTECTED);
1673. } else {
1674. $docStyle->getProtection()->setHidden( PHPExcel_Style_Protection:: PROTECTION_UNPROTECTED);
1675. }
1676. }
1677. }
1678. }
1680. private static function _readBorder($docBorder, $eleBorder) {
1681. if (isset($eleBorder["style"])) {
1682. $docBorder->setBorderStyle((string) $eleBorder["style"]);
1683. }
1684. if (isset($eleBorder->color)) {
1685. $docBorder->getColor()->setARGB(self::_readColor($eleBorder->color));
1686. }
1687. }
1689. private function _parseRichText($is = null) {
1690. $value = new PHPExcel_RichText();
1692. if (isset($is->t)) {
1693. $value->createText( PHPExcel_Shared_String:: ControlCharacterOOXML2PHP( (string) $is->t ) );
1694. } else {
1695. foreach ($is->r as $run) {
1696. if (!isset($run->rPr)) {
1697. $objText = $value->createText( PHPExcel_Shared_String:: ControlCharacterOOXML2PHP( (string) $run->t ) );
1699. } else {
1700. $objText = $value->createTextRun( PHPExcel_Shared_String:: ControlCharacterOOXML2PHP( (string) $run->t ) );
1702. if (isset($run->rPr->rFont["val"])) {
1703. $objText->getFont()->setName((string) $run->rPr->rFont["val"]);
1704. }
1706. if (isset($run->rPr->sz["val"])) {
1707. $objText->getFont()->setSize((string) $run->rPr->sz["val"]);
1708. }
1710. if (isset($run->rPr->color)) {
1711. $objText->getFont()->setColor( new PHPExcel_Style_Color( self::_readColor($run->rPr->color) ) );
1712. }
1714. if ( (isset($run->rPr->b["val"]) && ((string) $run->rPr->b["val"] == 'true' || (string) $run->rPr->b["val"] == '1'))
1715. || (isset($run->rPr->b) && !isset($run->rPr->b["val"])) ) {
1716. $objText->getFont()->setBold(true);
1717. }
1719. if ( (isset($run->rPr->i["val"]) && ((string) $run->rPr->i["val"] == 'true' || (string) $run->rPr->i["val"] == '1'))
1720. || (isset($run->rPr->i) && !isset($run->rPr->i["val"])) ) {
1721. $objText->getFont()->setItalic(true);
1722. }
1724. if (isset($run->rPr->vertAlign) && isset($run->rPr->vertAlign["val"])) {
1725. $vertAlign = strtolower((string)$run->rPr->vertAlign["val"]);
1726. if ($vertAlign == 'superscript') {
1727. $objText->getFont()->setSuperScript(true);
1728. }
1729. if ($vertAlign == 'subscript') {
1730. $objText->getFont()->setSubScript(true);
1731. }
1732. }
1734. if (isset($run->rPr->u) && !isset($run->rPr->u["val"])) {
1735. $objText->getFont()->setUnderline( PHPExcel_Style_Font:: UNDERLINE_SINGLE);
1736. } else if (isset($run->rPr->u) && isset($run->rPr->u["val"])) {
1737. $objText->getFont()->setUnderline((string)$run->rPr->u["val"]);
1738. }
1740. if ( (isset($run->rPr->strike["val"]) && ((string) $run->rPr->strike["val"] == 'true' || (string) $run->rPr->strike["val"] == '1'))
1741. || (isset($run->rPr->strike) && !isset($run->rPr->strike["val"])) ) {
1742. $objText->getFont()->setStrikethrough(true);
1743. }
1744. }
1745. }
1746. }
1748. return $value;
1749. }
1751. private static function array_item($array, $key = 0) {
1752. return (isset($array[$key]) ? $array[$key] : null);
1753. }
1755. private static function dir_add($base, $add) {
1756. return preg_replace('~[^/]+/\.\./~', '', dirname($base) . "/$add");
1757. }
1759. private static function toCSSArray($style) {
1760. $style = str_replace(array("\r","\n"), "", $style);
1762. $temp = explode(';', $style);
1763. $style = array();
1764. foreach ($temp as $item) {
1765. $item = explode(':', $item);
1767. if ( strpos($item[1], 'px') !== false) {
1768. $item[1] = str_replace('px', '', $item[1]);
1769. }
1770. if ( strpos($item[1], 'pt') !== false) {
1771. $item[1] = str_replace('pt', '', $item[1]);
1772. $item[1] = PHPExcel_Shared_Font:: fontSizeToPixels($item[1]);
1773. }
1774. if ( strpos($item[1], 'in') !== false) {
1775. $item[1] = str_replace('in', '', $item[1]);
1776. $item[1] = PHPExcel_Shared_Font:: inchSizeToPixels($item[1]);
1777. }
1778. if ( strpos($item[1], 'cm') !== false) {
1779. $item[1] = str_replace('cm', '', $item[1]);
1780. $item[1] = PHPExcel_Shared_Font:: centimeterSizeToPixels($item[1]);
1781. }
1783. $style[$item[0]] = $item[1];
1784. }
1786. return $style;
1787. }
1788. }

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