PageRenderTime 27ms CodeModel.GetById 19ms RepoModel.GetById 0ms app.codeStats 1ms

/include/PHPExcel/PHPExcel/Reader/Excel2007.php

https://bitbucket.org/thomashii/vtigercrm-6-for-postgresql
PHP | 1998 lines | 1433 code | 267 blank | 298 comment | 512 complexity | 1514ac137d78307a96031cf596ab8425 MD5 | raw file
Possible License(s): Apache-2.0, LGPL-3.0, LGPL-2.1, GPL-2.0, GPL-3.0

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

  1. <?php
  2. /**
  3. * PHPExcel
  4. *
  5. * Copyright (c) 2006 - 2012 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 - 2012 PHPExcel (http://www.codeplex.com/PHPExcel)
  24. * @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL
  25. * @version 1.7.7, 2012-05-19
  26. */
  27. /** PHPExcel root directory */
  28. if (!defined('PHPEXCEL_ROOT')) {
  29. /**
  30. * @ignore
  31. */
  32. define('PHPEXCEL_ROOT', dirname(__FILE__) . '/../../');
  33. require(PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php');
  34. }
  35. /**
  36. * PHPExcel_Reader_Excel2007
  37. *
  38. * @category PHPExcel
  39. * @package PHPExcel_Reader
  40. * @copyright Copyright (c) 2006 - 2012 PHPExcel (http://www.codeplex.com/PHPExcel)
  41. */
  42. class PHPExcel_Reader_Excel2007 implements PHPExcel_Reader_IReader
  43. {
  44. /**
  45. * Read data only?
  46. * Identifies whether the Reader should only read data values for cells, and ignore any formatting information;
  47. * or whether it should read both data and formatting
  48. *
  49. * @var boolean
  50. */
  51. private $_readDataOnly = false;
  52. /**
  53. * Read charts that are defined in the workbook?
  54. * Identifies whether the Reader should read the definitions for any charts that exist in the workbook;
  55. *
  56. * @var boolean
  57. */
  58. private $_includeCharts = false;
  59. /**
  60. * Restrict which sheets should be loaded?
  61. * This property holds an array of worksheet names to be loaded. If null, then all worksheets will be loaded.
  62. *
  63. * @var array of string
  64. */
  65. private $_loadSheetsOnly = null;
  66. /**
  67. * PHPExcel_Reader_IReadFilter instance
  68. *
  69. * @var PHPExcel_Reader_IReadFilter
  70. */
  71. private $_readFilter = null;
  72. /**
  73. * PHPExcel_ReferenceHelper instance
  74. *
  75. * @var PHPExcel_ReferenceHelper
  76. */
  77. private $_referenceHelper = null;
  78. /**
  79. * PHPExcel_Reader_Excel2007_Theme instance
  80. *
  81. * @var PHPExcel_Reader_Excel2007_Theme
  82. */
  83. private static $_theme = null;
  84. /**
  85. * Create a new PHPExcel_Reader_Excel2007 instance
  86. */
  87. public function __construct() {
  88. $this->_readFilter = new PHPExcel_Reader_DefaultReadFilter();
  89. $this->_referenceHelper = PHPExcel_ReferenceHelper::getInstance();
  90. }
  91. /**
  92. * Read data only?
  93. * If this is true, then the Reader will only read data values for cells, it will not read any formatting information.
  94. * If false (the default) it will read data and formatting.
  95. *
  96. * @return boolean
  97. */
  98. public function getReadDataOnly() {
  99. return $this->_readDataOnly;
  100. }
  101. /**
  102. * Set read data only
  103. * Set to true, to advise the Reader only to read data values for cells, and to ignore any formatting information.
  104. * Set to false (the default) to advise the Reader to read both data and formatting for cells.
  105. *
  106. * @param boolean $pValue
  107. *
  108. * @return PHPExcel_Reader_Excel2007
  109. */
  110. public function setReadDataOnly($pValue = false) {
  111. $this->_readDataOnly = $pValue;
  112. return $this;
  113. }
  114. /**
  115. * Read charts in workbook?
  116. * If this is true, then the Reader will include any charts that exist in the workbook.
  117. * Note that a ReadDataOnly value of false overrides, and charts won't be read regardless of the IncludeCharts value.
  118. * If false (the default) it will ignore any charts defined in the workbook file.
  119. *
  120. * @return boolean
  121. */
  122. public function getIncludeCharts() {
  123. return $this->_includeCharts;
  124. }
  125. /**
  126. * Set read charts in workbook
  127. * Set to true, to advise the Reader to include any charts that exist in the workbook.
  128. * Note that a ReadDataOnly value of false overrides, and charts won't be read regardless of the IncludeCharts value.
  129. * Set to false (the default) to discard charts.
  130. *
  131. * @param boolean $pValue
  132. *
  133. * @return PHPExcel_Reader_Excel2007
  134. */
  135. public function setIncludeCharts($pValue = false) {
  136. $this->_includeCharts = (boolean) $pValue;
  137. return $this;
  138. }
  139. /**
  140. * Get which sheets to load
  141. * Returns either an array of worksheet names (the list of worksheets that should be loaded), or a null
  142. * indicating that all worksheets in the workbook should be loaded.
  143. *
  144. * @return mixed
  145. */
  146. public function getLoadSheetsOnly()
  147. {
  148. return $this->_loadSheetsOnly;
  149. }
  150. /**
  151. * Set which sheets to load
  152. *
  153. * @param mixed $value
  154. * This should be either an array of worksheet names to be loaded, or a string containing a single worksheet name.
  155. * If NULL, then it tells the Reader to read all worksheets in the workbook
  156. *
  157. * @return PHPExcel_Reader_Excel2007
  158. */
  159. public function setLoadSheetsOnly($value = null)
  160. {
  161. $this->_loadSheetsOnly = is_array($value) ?
  162. $value : array($value);
  163. return $this;
  164. }
  165. /**
  166. * Set all sheets to load
  167. * Tells the Reader to load all worksheets from the workbook.
  168. *
  169. * @return PHPExcel_Reader_Excel2007
  170. */
  171. public function setLoadAllSheets()
  172. {
  173. $this->_loadSheetsOnly = null;
  174. return $this;
  175. }
  176. /**
  177. * Read filter
  178. *
  179. * @return PHPExcel_Reader_IReadFilter
  180. */
  181. public function getReadFilter() {
  182. return $this->_readFilter;
  183. }
  184. /**
  185. * Set read filter
  186. *
  187. * @param PHPExcel_Reader_IReadFilter $pValue
  188. * @return PHPExcel_Reader_Excel2007
  189. */
  190. public function setReadFilter(PHPExcel_Reader_IReadFilter $pValue) {
  191. $this->_readFilter = $pValue;
  192. return $this;
  193. }
  194. /**
  195. * Can the current PHPExcel_Reader_IReader read the file?
  196. *
  197. * @param string $pFileName
  198. * @return boolean
  199. * @throws Exception
  200. */
  201. public function canRead($pFilename)
  202. {
  203. // Check if file exists
  204. if (!file_exists($pFilename)) {
  205. throw new Exception("Could not open " . $pFilename . " for reading! File does not exist.");
  206. }
  207. // Check if zip class exists
  208. if (!class_exists('ZipArchive')) {
  209. throw new Exception("ZipArchive library is not enabled");
  210. }
  211. $xl = false;
  212. // Load file
  213. $zip = new ZipArchive;
  214. if ($zip->open($pFilename) === true) {
  215. // check if it is an OOXML archive
  216. $rels = simplexml_load_string($this->_getFromZipArchive($zip, "_rels/.rels"));
  217. if ($rels !== false) {
  218. foreach ($rels->Relationship as $rel) {
  219. switch ($rel["Type"]) {
  220. case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument":
  221. if (basename($rel["Target"]) == 'workbook.xml') {
  222. $xl = true;
  223. }
  224. break;
  225. }
  226. }
  227. }
  228. $zip->close();
  229. }
  230. return $xl;
  231. }
  232. /**
  233. * Return worksheet info (Name, Last Column Letter, Last Column Index, Total Rows, Total Columns)
  234. *
  235. * @param string $pFilename
  236. * @throws Exception
  237. */
  238. public function listWorksheetInfo($pFilename)
  239. {
  240. // Check if file exists
  241. if (!file_exists($pFilename)) {
  242. throw new Exception("Could not open " . $pFilename . " for reading! File does not exist.");
  243. }
  244. $worksheetInfo = array();
  245. $zip = new ZipArchive;
  246. $zip->open($pFilename);
  247. $rels = simplexml_load_string($this->_getFromZipArchive($zip, "_rels/.rels")); //~ http://schemas.openxmlformats.org/package/2006/relationships");
  248. foreach ($rels->Relationship as $rel) {
  249. if ($rel["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument") {
  250. $dir = dirname($rel["Target"]);
  251. $relsWorkbook = simplexml_load_string($this->_getFromZipArchive($zip, "$dir/_rels/" . basename($rel["Target"]) . ".rels")); //~ http://schemas.openxmlformats.org/package/2006/relationships");
  252. $relsWorkbook->registerXPathNamespace("rel", "http://schemas.openxmlformats.org/package/2006/relationships");
  253. $worksheets = array();
  254. foreach ($relsWorkbook->Relationship as $ele) {
  255. if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet") {
  256. $worksheets[(string) $ele["Id"]] = $ele["Target"];
  257. }
  258. }
  259. $xmlWorkbook = simplexml_load_string($this->_getFromZipArchive($zip, "{$rel['Target']}")); //~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");
  260. if ($xmlWorkbook->sheets) {
  261. $dir = dirname($rel["Target"]);
  262. foreach ($xmlWorkbook->sheets->sheet as $eleSheet) {
  263. $tmpInfo = array();
  264. $tmpInfo['worksheetName'] = (string) $eleSheet["name"];
  265. $tmpInfo['lastColumnLetter'] = 'A';
  266. $tmpInfo['lastColumnIndex'] = 0;
  267. $tmpInfo['totalRows'] = 0;
  268. $tmpInfo['totalColumns'] = 0;
  269. $fileWorksheet = $worksheets[(string) self::array_item($eleSheet->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "id")];
  270. $xmlSheet = simplexml_load_string($this->_getFromZipArchive($zip, "$dir/$fileWorksheet")); //~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");
  271. if ($xmlSheet && $xmlSheet->sheetData && $xmlSheet->sheetData->row) {
  272. foreach ($xmlSheet->sheetData->row as $row) {
  273. foreach ($row->c as $c) {
  274. $r = (string) $c["r"];
  275. $coordinates = PHPExcel_Cell::coordinateFromString($r);
  276. $rowIndex = $coordinates[1];
  277. $columnIndex = PHPExcel_Cell::columnIndexFromString($coordinates[0]) - 1;
  278. $tmpInfo['totalRows'] = max($tmpInfo['totalRows'], $rowIndex);
  279. $tmpInfo['lastColumnIndex'] = max($tmpInfo['lastColumnIndex'], $columnIndex);
  280. }
  281. }
  282. }
  283. $tmpInfo['lastColumnLetter'] = PHPExcel_Cell::stringFromColumnIndex($tmpInfo['lastColumnIndex']);
  284. $tmpInfo['totalColumns'] = $tmpInfo['lastColumnIndex'] + 1;
  285. $worksheetInfo[] = $tmpInfo;
  286. }
  287. }
  288. }
  289. }
  290. $zip->close();
  291. return $worksheetInfo;
  292. }
  293. private static function _castToBool($c) {
  294. // echo 'Initial Cast to Boolean<br />';
  295. $value = isset($c->v) ? (string) $c->v : null;
  296. if ($value == '0') {
  297. return false;
  298. } elseif ($value == '1') {
  299. return true;
  300. } else {
  301. return (bool)$c->v;
  302. }
  303. return $value;
  304. } // function _castToBool()
  305. private static function _castToError($c) {
  306. // echo 'Initial Cast to Error<br />';
  307. return isset($c->v) ? (string) $c->v : null;;
  308. } // function _castToError()
  309. private static function _castToString($c) {
  310. // echo 'Initial Cast to String<br />';
  311. return isset($c->v) ? (string) $c->v : null;;
  312. } // function _castToString()
  313. private function _castToFormula($c,$r,&$cellDataType,&$value,&$calculatedValue,&$sharedFormulas,$castBaseType) {
  314. // echo '<font color="darkgreen">Formula</font><br />';
  315. // echo '$c->f is '.$c->f.'<br />';
  316. $cellDataType = 'f';
  317. $value = "={$c->f}";
  318. $calculatedValue = self::$castBaseType($c);
  319. // Shared formula?
  320. if (isset($c->f['t']) && strtolower((string)$c->f['t']) == 'shared') {
  321. // echo '<font color="darkgreen">SHARED FORMULA</font><br />';
  322. $instance = (string)$c->f['si'];
  323. // echo 'Instance ID = '.$instance.'<br />';
  324. //
  325. // echo 'Shared Formula Array:<pre>';
  326. // print_r($sharedFormulas);
  327. // echo '</pre>';
  328. if (!isset($sharedFormulas[(string)$c->f['si']])) {
  329. // echo '<font color="darkgreen">SETTING NEW SHARED FORMULA</font><br />';
  330. // echo 'Master is '.$r.'<br />';
  331. // echo 'Formula is '.$value.'<br />';
  332. $sharedFormulas[$instance] = array( 'master' => $r,
  333. 'formula' => $value
  334. );
  335. // echo 'New Shared Formula Array:<pre>';
  336. // print_r($sharedFormulas);
  337. // echo '</pre>';
  338. } else {
  339. // echo '<font color="darkgreen">GETTING SHARED FORMULA</font><br />';
  340. // echo 'Master is '.$sharedFormulas[$instance]['master'].'<br />';
  341. // echo 'Formula is '.$sharedFormulas[$instance]['formula'].'<br />';
  342. $master = PHPExcel_Cell::coordinateFromString($sharedFormulas[$instance]['master']);
  343. $current = PHPExcel_Cell::coordinateFromString($r);
  344. $difference = array(0, 0);
  345. $difference[0] = PHPExcel_Cell::columnIndexFromString($current[0]) - PHPExcel_Cell::columnIndexFromString($master[0]);
  346. $difference[1] = $current[1] - $master[1];
  347. $value = $this->_referenceHelper->updateFormulaReferences( $sharedFormulas[$instance]['formula'],
  348. 'A1',
  349. $difference[0],
  350. $difference[1]
  351. );
  352. // echo 'Adjusted Formula is '.$value.'<br />';
  353. }
  354. }
  355. }
  356. public function _getFromZipArchive(ZipArchive $archive, $fileName = '')
  357. {
  358. // Root-relative paths
  359. if (strpos($fileName, '//') !== false)
  360. {
  361. $fileName = substr($fileName, strpos($fileName, '//') + 1);
  362. }
  363. $fileName = PHPExcel_Shared_File::realpath($fileName);
  364. // Apache POI fixes
  365. $contents = $archive->getFromName($fileName);
  366. if ($contents === false)
  367. {
  368. $contents = $archive->getFromName(substr($fileName, 1));
  369. }
  370. return $contents;
  371. }
  372. /**
  373. * Reads names of the worksheets from a file, without parsing the whole file to a PHPExcel object
  374. *
  375. * @param string $pFilename
  376. * @throws Exception
  377. */
  378. public function listWorksheetNames($pFilename)
  379. {
  380. // Check if file exists
  381. if (!file_exists($pFilename)) {
  382. throw new Exception("Could not open " . $pFilename . " for reading! File does not exist.");
  383. }
  384. $worksheetNames = array();
  385. $zip = new ZipArchive;
  386. $zip->open($pFilename);
  387. $rels = simplexml_load_string($this->_getFromZipArchive($zip, "_rels/.rels")); //~ http://schemas.openxmlformats.org/package/2006/relationships");
  388. foreach ($rels->Relationship as $rel) {
  389. switch ($rel["Type"]) {
  390. case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument":
  391. $xmlWorkbook = simplexml_load_string($this->_getFromZipArchive($zip, "{$rel['Target']}")); //~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");
  392. if ($xmlWorkbook->sheets) {
  393. foreach ($xmlWorkbook->sheets->sheet as $eleSheet) {
  394. // Check if sheet should be skipped
  395. $worksheetNames[] = (string) $eleSheet["name"];
  396. }
  397. }
  398. }
  399. }
  400. $zip->close();
  401. return $worksheetNames;
  402. }
  403. /**
  404. * Loads PHPExcel from file
  405. *
  406. * @param string $pFilename
  407. * @throws Exception
  408. */
  409. public function load($pFilename)
  410. {
  411. // Check if file exists
  412. if (!file_exists($pFilename)) {
  413. throw new Exception("Could not open " . $pFilename . " for reading! File does not exist.");
  414. }
  415. // Initialisations
  416. $excel = new PHPExcel;
  417. $excel->removeSheetByIndex(0);
  418. if (!$this->_readDataOnly) {
  419. $excel->removeCellStyleXfByIndex(0); // remove the default style
  420. $excel->removeCellXfByIndex(0); // remove the default style
  421. }
  422. $zip = new ZipArchive;
  423. $zip->open($pFilename);
  424. // Read the theme first, because we need the colour scheme when reading the styles
  425. $wbRels = simplexml_load_string($this->_getFromZipArchive($zip, "xl/_rels/workbook.xml.rels")); //~ http://schemas.openxmlformats.org/package/2006/relationships");
  426. foreach ($wbRels->Relationship as $rel) {
  427. switch ($rel["Type"]) {
  428. case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/theme":
  429. $themeOrderArray = array('lt1','dk1','lt2','dk2');
  430. $themeOrderAdditional = count($themeOrderArray);
  431. $xmlTheme = simplexml_load_string($this->_getFromZipArchive($zip, "xl/{$rel['Target']}"));
  432. if (is_object($xmlTheme)) {
  433. $xmlThemeName = $xmlTheme->attributes();
  434. $xmlTheme = $xmlTheme->children("http://schemas.openxmlformats.org/drawingml/2006/main");
  435. $themeName = (string)$xmlThemeName['name'];
  436. $colourScheme = $xmlTheme->themeElements->clrScheme->attributes();
  437. $colourSchemeName = (string)$colourScheme['name'];
  438. $colourScheme = $xmlTheme->themeElements->clrScheme->children("http://schemas.openxmlformats.org/drawingml/2006/main");
  439. $themeColours = array();
  440. foreach ($colourScheme as $k => $xmlColour) {
  441. $themePos = array_search($k,$themeOrderArray);
  442. if ($themePos === false) {
  443. $themePos = $themeOrderAdditional++;
  444. }
  445. if (isset($xmlColour->sysClr)) {
  446. $xmlColourData = $xmlColour->sysClr->attributes();
  447. $themeColours[$themePos] = $xmlColourData['lastClr'];
  448. } elseif (isset($xmlColour->srgbClr)) {
  449. $xmlColourData = $xmlColour->srgbClr->attributes();
  450. $themeColours[$themePos] = $xmlColourData['val'];
  451. }
  452. }
  453. self::$_theme = new PHPExcel_Reader_Excel2007_Theme($themeName,$colourSchemeName,$themeColours);
  454. }
  455. break;
  456. }
  457. }
  458. $rels = simplexml_load_string($this->_getFromZipArchive($zip, "_rels/.rels")); //~ http://schemas.openxmlformats.org/package/2006/relationships");
  459. foreach ($rels->Relationship as $rel) {
  460. switch ($rel["Type"]) {
  461. case "http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties":
  462. $xmlCore = simplexml_load_string($this->_getFromZipArchive($zip, "{$rel['Target']}"));
  463. if (is_object($xmlCore)) {
  464. $xmlCore->registerXPathNamespace("dc", "http://purl.org/dc/elements/1.1/");
  465. $xmlCore->registerXPathNamespace("dcterms", "http://purl.org/dc/terms/");
  466. $xmlCore->registerXPathNamespace("cp", "http://schemas.openxmlformats.org/package/2006/metadata/core-properties");
  467. $docProps = $excel->getProperties();
  468. $docProps->setCreator((string) self::array_item($xmlCore->xpath("dc:creator")));
  469. $docProps->setLastModifiedBy((string) self::array_item($xmlCore->xpath("cp:lastModifiedBy")));
  470. $docProps->setCreated(strtotime(self::array_item($xmlCore->xpath("dcterms:created")))); //! respect xsi:type
  471. $docProps->setModified(strtotime(self::array_item($xmlCore->xpath("dcterms:modified")))); //! respect xsi:type
  472. $docProps->setTitle((string) self::array_item($xmlCore->xpath("dc:title")));
  473. $docProps->setDescription((string) self::array_item($xmlCore->xpath("dc:description")));
  474. $docProps->setSubject((string) self::array_item($xmlCore->xpath("dc:subject")));
  475. $docProps->setKeywords((string) self::array_item($xmlCore->xpath("cp:keywords")));
  476. $docProps->setCategory((string) self::array_item($xmlCore->xpath("cp:category")));
  477. }
  478. break;
  479. case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties":
  480. $xmlCore = simplexml_load_string($this->_getFromZipArchive($zip, "{$rel['Target']}"));
  481. if (is_object($xmlCore)) {
  482. $docProps = $excel->getProperties();
  483. if (isset($xmlCore->Company))
  484. $docProps->setCompany((string) $xmlCore->Company);
  485. if (isset($xmlCore->Manager))
  486. $docProps->setManager((string) $xmlCore->Manager);
  487. }
  488. break;
  489. case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/custom-properties":
  490. $xmlCore = simplexml_load_string($this->_getFromZipArchive($zip, "{$rel['Target']}"));
  491. if (is_object($xmlCore)) {
  492. $docProps = $excel->getProperties();
  493. foreach ($xmlCore as $xmlProperty) {
  494. $cellDataOfficeAttributes = $xmlProperty->attributes();
  495. if (isset($cellDataOfficeAttributes['name'])) {
  496. $propertyName = (string) $cellDataOfficeAttributes['name'];
  497. $cellDataOfficeChildren = $xmlProperty->children('http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes');
  498. $attributeType = $cellDataOfficeChildren->getName();
  499. $attributeValue = (string) $cellDataOfficeChildren->{$attributeType};
  500. $attributeValue = PHPExcel_DocumentProperties::convertProperty($attributeValue,$attributeType);
  501. $attributeType = PHPExcel_DocumentProperties::convertPropertyType($attributeType);
  502. $docProps->setCustomProperty($propertyName,$attributeValue,$attributeType);
  503. }
  504. }
  505. }
  506. break;
  507. case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument":
  508. $dir = dirname($rel["Target"]);
  509. $relsWorkbook = simplexml_load_string($this->_getFromZipArchive($zip, "$dir/_rels/" . basename($rel["Target"]) . ".rels")); //~ http://schemas.openxmlformats.org/package/2006/relationships");
  510. $relsWorkbook->registerXPathNamespace("rel", "http://schemas.openxmlformats.org/package/2006/relationships");
  511. $sharedStrings = array();
  512. $xpath = self::array_item($relsWorkbook->xpath("rel:Relationship[@Type='http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings']"));
  513. $xmlStrings = simplexml_load_string($this->_getFromZipArchive($zip, "$dir/$xpath[Target]")); //~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");
  514. if (isset($xmlStrings) && isset($xmlStrings->si)) {
  515. foreach ($xmlStrings->si as $val) {
  516. if (isset($val->t)) {
  517. $sharedStrings[] = PHPExcel_Shared_String::ControlCharacterOOXML2PHP( (string) $val->t );
  518. } elseif (isset($val->r)) {
  519. $sharedStrings[] = $this->_parseRichText($val);
  520. }
  521. }
  522. }
  523. $worksheets = array();
  524. foreach ($relsWorkbook->Relationship as $ele) {
  525. if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet") {
  526. $worksheets[(string) $ele["Id"]] = $ele["Target"];
  527. }
  528. }
  529. $styles = array();
  530. $cellStyles = array();
  531. $xpath = self::array_item($relsWorkbook->xpath("rel:Relationship[@Type='http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles']"));
  532. $xmlStyles = simplexml_load_string($this->_getFromZipArchive($zip, "$dir/$xpath[Target]")); //~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");
  533. $numFmts = null;
  534. if ($xmlStyles && $xmlStyles->numFmts[0]) {
  535. $numFmts = $xmlStyles->numFmts[0];
  536. }
  537. if (isset($numFmts) && ($numFmts !== NULL)) {
  538. $numFmts->registerXPathNamespace("sml", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
  539. }
  540. if (!$this->_readDataOnly && $xmlStyles) {
  541. foreach ($xmlStyles->cellXfs->xf as $xf) {
  542. $numFmt = PHPExcel_Style_NumberFormat::FORMAT_GENERAL;
  543. if ($xf["numFmtId"]) {
  544. if (isset($numFmts)) {
  545. $tmpNumFmt = self::array_item($numFmts->xpath("sml:numFmt[@numFmtId=$xf[numFmtId]]"));
  546. if (isset($tmpNumFmt["formatCode"])) {
  547. $numFmt = (string) $tmpNumFmt["formatCode"];
  548. }
  549. }
  550. if ((int)$xf["numFmtId"] < 164) {
  551. $numFmt = PHPExcel_Style_NumberFormat::builtInFormatCode((int)$xf["numFmtId"]);
  552. }
  553. }
  554. //$numFmt = str_replace('mm', 'i', $numFmt);
  555. //$numFmt = str_replace('h', 'H', $numFmt);
  556. $style = (object) array(
  557. "numFmt" => $numFmt,
  558. "font" => $xmlStyles->fonts->font[intval($xf["fontId"])],
  559. "fill" => $xmlStyles->fills->fill[intval($xf["fillId"])],
  560. "border" => $xmlStyles->borders->border[intval($xf["borderId"])],
  561. "alignment" => $xf->alignment,
  562. "protection" => $xf->protection,
  563. );
  564. $styles[] = $style;
  565. // add style to cellXf collection
  566. $objStyle = new PHPExcel_Style;
  567. self::_readStyle($objStyle, $style);
  568. $excel->addCellXf($objStyle);
  569. }
  570. foreach ($xmlStyles->cellStyleXfs->xf as $xf) {
  571. $numFmt = PHPExcel_Style_NumberFormat::FORMAT_GENERAL;
  572. if ($numFmts && $xf["numFmtId"]) {
  573. $tmpNumFmt = self::array_item($numFmts->xpath("sml:numFmt[@numFmtId=$xf[numFmtId]]"));
  574. if (isset($tmpNumFmt["formatCode"])) {
  575. $numFmt = (string) $tmpNumFmt["formatCode"];
  576. } else if ((int)$xf["numFmtId"] < 165) {
  577. $numFmt = PHPExcel_Style_NumberFormat::builtInFormatCode((int)$xf["numFmtId"]);
  578. }
  579. }
  580. $cellStyle = (object) array(
  581. "numFmt" => $numFmt,
  582. "font" => $xmlStyles->fonts->font[intval($xf["fontId"])],
  583. "fill" => $xmlStyles->fills->fill[intval($xf["fillId"])],
  584. "border" => $xmlStyles->borders->border[intval($xf["borderId"])],
  585. "alignment" => $xf->alignment,
  586. "protection" => $xf->protection,
  587. );
  588. $cellStyles[] = $cellStyle;
  589. // add style to cellStyleXf collection
  590. $objStyle = new PHPExcel_Style;
  591. self::_readStyle($objStyle, $cellStyle);
  592. $excel->addCellStyleXf($objStyle);
  593. }
  594. }
  595. $dxfs = array();
  596. if (!$this->_readDataOnly && $xmlStyles) {
  597. if ($xmlStyles->dxfs) {
  598. foreach ($xmlStyles->dxfs->dxf as $dxf) {
  599. $style = new PHPExcel_Style;
  600. self::_readStyle($style, $dxf);
  601. $dxfs[] = $style;
  602. }
  603. }
  604. if ($xmlStyles->cellStyles)
  605. {
  606. foreach ($xmlStyles->cellStyles->cellStyle as $cellStyle) {
  607. if (intval($cellStyle['builtinId']) == 0) {
  608. if (isset($cellStyles[intval($cellStyle['xfId'])])) {
  609. // Set default style
  610. $style = new PHPExcel_Style;
  611. self::_readStyle($style, $cellStyles[intval($cellStyle['xfId'])]);
  612. // normal style, currently not using it for anything
  613. }
  614. }
  615. }
  616. }
  617. }
  618. $xmlWorkbook = simplexml_load_string($this->_getFromZipArchive($zip, "{$rel['Target']}")); //~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");
  619. // Set base date
  620. if ($xmlWorkbook->workbookPr) {
  621. PHPExcel_Shared_Date::setExcelCalendar(PHPExcel_Shared_Date::CALENDAR_WINDOWS_1900);
  622. if (isset($xmlWorkbook->workbookPr['date1904'])) {
  623. $date1904 = (string)$xmlWorkbook->workbookPr['date1904'];
  624. if ($date1904 == "true" || $date1904 == "1") {
  625. PHPExcel_Shared_Date::setExcelCalendar(PHPExcel_Shared_Date::CALENDAR_MAC_1904);
  626. }
  627. }
  628. }
  629. $sheetId = 0; // keep track of new sheet id in final workbook
  630. $oldSheetId = -1; // keep track of old sheet id in final workbook
  631. $countSkippedSheets = 0; // keep track of number of skipped sheets
  632. $mapSheetId = array(); // mapping of sheet ids from old to new
  633. $charts = $chartDetails = array();
  634. if ($xmlWorkbook->sheets) {
  635. foreach ($xmlWorkbook->sheets->sheet as $eleSheet) {
  636. ++$oldSheetId;
  637. // Check if sheet should be skipped
  638. if (isset($this->_loadSheetsOnly) && !in_array((string) $eleSheet["name"], $this->_loadSheetsOnly)) {
  639. ++$countSkippedSheets;
  640. $mapSheetId[$oldSheetId] = null;
  641. continue;
  642. }
  643. // Map old sheet id in original workbook to new sheet id.
  644. // They will differ if loadSheetsOnly() is being used
  645. $mapSheetId[$oldSheetId] = $oldSheetId - $countSkippedSheets;
  646. // Load sheet
  647. $docSheet = $excel->createSheet();
  648. // Use false for $updateFormulaCellReferences to prevent adjustment of worksheet
  649. // references in formula cells... during the load, all formulae should be correct,
  650. // and we're simply bringing the worksheet name in line with the formula, not the
  651. // reverse
  652. $docSheet->setTitle((string) $eleSheet["name"],false);
  653. $fileWorksheet = $worksheets[(string) self::array_item($eleSheet->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "id")];
  654. $xmlSheet = simplexml_load_string($this->_getFromZipArchive($zip, "$dir/$fileWorksheet")); //~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");
  655. $sharedFormulas = array();
  656. if (isset($eleSheet["state"]) && (string) $eleSheet["state"] != '') {
  657. $docSheet->setSheetState( (string) $eleSheet["state"] );
  658. }
  659. if (isset($xmlSheet->sheetViews) && isset($xmlSheet->sheetViews->sheetView)) {
  660. if (isset($xmlSheet->sheetViews->sheetView['zoomScale'])) {
  661. $docSheet->getSheetView()->setZoomScale( intval($xmlSheet->sheetViews->sheetView['zoomScale']) );
  662. }
  663. if (isset($xmlSheet->sheetViews->sheetView['zoomScaleNormal'])) {
  664. $docSheet->getSheetView()->setZoomScaleNormal( intval($xmlSheet->sheetViews->sheetView['zoomScaleNormal']) );
  665. }
  666. if (isset($xmlSheet->sheetViews->sheetView['showGridLines'])) {
  667. $docSheet->setShowGridLines((string)$xmlSheet->sheetViews->sheetView['showGridLines'] ? true : false);
  668. }
  669. if (isset($xmlSheet->sheetViews->sheetView['showRowColHeaders'])) {
  670. $docSheet->setShowRowColHeaders((string)$xmlSheet->sheetViews->sheetView['showRowColHeaders'] ? true : false);
  671. }
  672. if (isset($xmlSheet->sheetViews->sheetView['rightToLeft'])) {
  673. $docSheet->setRightToLeft((string)$xmlSheet->sheetViews->sheetView['rightToLeft'] ? true : false);
  674. }
  675. if (isset($xmlSheet->sheetViews->sheetView->pane)) {
  676. if (isset($xmlSheet->sheetViews->sheetView->pane['topLeftCell'])) {
  677. $docSheet->freezePane( (string)$xmlSheet->sheetViews->sheetView->pane['topLeftCell'] );
  678. } else {
  679. $xSplit = 0;
  680. $ySplit = 0;
  681. if (isset($xmlSheet->sheetViews->sheetView->pane['xSplit'])) {
  682. $xSplit = 1 + intval($xmlSheet->sheetViews->sheetView->pane['xSplit']);
  683. }
  684. if (isset($xmlSheet->sheetViews->sheetView->pane['ySplit'])) {
  685. $ySplit = 1 + intval($xmlSheet->sheetViews->sheetView->pane['ySplit']);
  686. }
  687. $docSheet->freezePaneByColumnAndRow($xSplit, $ySplit);
  688. }
  689. }
  690. if (isset($xmlSheet->sheetViews->sheetView->selection)) {
  691. if (isset($xmlSheet->sheetViews->sheetView->selection['sqref'])) {
  692. $sqref = (string)$xmlSheet->sheetViews->sheetView->selection['sqref'];
  693. $sqref = explode(' ', $sqref);
  694. $sqref = $sqref[0];
  695. $docSheet->setSelectedCells($sqref);
  696. }
  697. }
  698. }
  699. if (isset($xmlSheet->sheetPr) && isset($xmlSheet->sheetPr->tabColor)) {
  700. if (isset($xmlSheet->sheetPr->tabColor['rgb'])) {
  701. $docSheet->getTabColor()->setARGB( (string)$xmlSheet->sheetPr->tabColor['rgb'] );
  702. }
  703. }
  704. if (isset($xmlSheet->sheetPr) && isset($xmlSheet->sheetPr->outlinePr)) {
  705. if (isset($xmlSheet->sheetPr->outlinePr['summaryRight']) && $xmlSheet->sheetPr->outlinePr['summaryRight'] == false) {
  706. $docSheet->setShowSummaryRight(false);
  707. } else {
  708. $docSheet->setShowSummaryRight(true);
  709. }
  710. if (isset($xmlSheet->sheetPr->outlinePr['summaryBelow']) && $xmlSheet->sheetPr->outlinePr['summaryBelow'] == false) {
  711. $docSheet->setShowSummaryBelow(false);
  712. } else {
  713. $docSheet->setShowSummaryBelow(true);
  714. }
  715. }
  716. if (isset($xmlSheet->sheetPr) && isset($xmlSheet->sheetPr->pageSetUpPr)) {
  717. if (isset($xmlSheet->sheetPr->pageSetUpPr['fitToPage']) && $xmlSheet->sheetPr->pageSetUpPr['fitToPage'] == false) {
  718. $docSheet->getPageSetup()->setFitToPage(false);
  719. } else {
  720. $docSheet->getPageSetup()->setFitToPage(true);
  721. }
  722. }
  723. if (isset($xmlSheet->sheetFormatPr)) {
  724. if (isset($xmlSheet->sheetFormatPr['customHeight']) && ((string)$xmlSheet->sheetFormatPr['customHeight'] == '1' || strtolower((string)$xmlSheet->sheetFormatPr['customHeight']) == 'true') && isset($xmlSheet->sheetFormatPr['defaultRowHeight'])) {
  725. $docSheet->getDefaultRowDimension()->setRowHeight( (float)$xmlSheet->sheetFormatPr['defaultRowHeight'] );
  726. }
  727. if (isset($xmlSheet->sheetFormatPr['defaultColWidth'])) {
  728. $docSheet->getDefaultColumnDimension()->setWidth( (float)$xmlSheet->sheetFormatPr['defaultColWidth'] );
  729. }
  730. }
  731. if (isset($xmlSheet->cols) && !$this->_readDataOnly) {
  732. foreach ($xmlSheet->cols->col as $col) {
  733. for ($i = intval($col["min"]) - 1; $i < intval($col["max"]); ++$i) {
  734. if ($col["style"] && !$this->_readDataOnly) {
  735. $docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setXfIndex(intval($col["style"]));
  736. }
  737. if ($col["bestFit"]) {
  738. //$docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setAutoSize(true);
  739. }
  740. if ($col["hidden"]) {
  741. $docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setVisible(false);
  742. }
  743. if ($col["collapsed"]) {
  744. $docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setCollapsed(true);
  745. }
  746. if ($col["outlineLevel"] > 0) {
  747. $docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setOutlineLevel(intval($col["outlineLevel"]));
  748. }
  749. $docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setWidth(floatval($col["width"]));
  750. if (intval($col["max"]) == 16384) {
  751. break;
  752. }
  753. }
  754. }
  755. }
  756. if (isset($xmlSheet->printOptions) && !$this->_readDataOnly) {
  757. if ($xmlSheet->printOptions['gridLinesSet'] == 'true' && $xmlSheet->printOptions['gridLinesSet'] == '1') {
  758. $docSheet->setShowGridlines(true);
  759. }
  760. if ($xmlSheet->printOptions['gridLines'] == 'true' || $xmlSheet->printOptions['gridLines'] == '1') {
  761. $docSheet->setPrintGridlines(true);
  762. }
  763. if ($xmlSheet->printOptions['horizontalCentered']) {
  764. $docSheet->getPageSetup()->setHorizontalCentered(true);
  765. }
  766. if ($xmlSheet->printOptions['verticalCentered']) {
  767. $docSheet->getPageSetup()->setVerticalCentered(true);
  768. }
  769. }
  770. if ($xmlSheet && $xmlSheet->sheetData && $xmlSheet->sheetData->row) {
  771. foreach ($xmlSheet->sheetData->row as $row) {
  772. if ($row["ht"] && !$this->_readDataOnly) {
  773. $docSheet->getRowDimension(intval($row["r"]))->setRowHeight(floatval($row["ht"]));
  774. }
  775. if ($row["hidden"] && !$this->_readDataOnly) {
  776. $docSheet->getRowDimension(intval($row["r"]))->setVisible(false);
  777. }
  778. if ($row["collapsed"]) {
  779. $docSheet->getRowDimension(intval($row["r"]))->setCollapsed(true);
  780. }
  781. if ($row["outlineLevel"] > 0) {
  782. $docSheet->getRowDimension(intval($row["r"]))->setOutlineLevel(intval($row["outlineLevel"]));
  783. }
  784. if ($row["s"] && !$this->_readDataOnly) {
  785. $docSheet->getRowDimension(intval($row["r"]))->setXfIndex(intval($row["s"]));
  786. }
  787. foreach ($row->c as $c) {
  788. $r = (string) $c["r"];
  789. $cellDataType = (string) $c["t"];
  790. $value = null;
  791. $calculatedValue = null;
  792. // Read cell?
  793. if ($this->getReadFilter() !== NULL) {
  794. $coordinates = PHPExcel_Cell::coordinateFromString($r);
  795. if (!$this->getReadFilter()->readCell($coordinates[0], $coordinates[1], $docSheet->getTitle())) {
  796. continue;
  797. }
  798. }
  799. // echo '<b>Reading cell '.$coordinates[0].$coordinates[1].'</b><br />';
  800. // print_r($c);
  801. // echo '<br />';
  802. // echo 'Cell Data Type is '.$cellDataType.': ';
  803. //
  804. // Read cell!
  805. switch ($cellDataType) {
  806. case "s":
  807. // echo 'String<br />';
  808. if ((string)$c->v != '') {
  809. $value = $sharedStrings[intval($c->v)];
  810. if ($value instanceof PHPExcel_RichText) {
  811. $value = clone $value;
  812. }
  813. } else {
  814. $value = '';
  815. }
  816. break;
  817. case "b":
  818. // echo 'Boolean<br />';
  819. if (!isset($c->f)) {
  820. $value = self::_castToBool($c);
  821. } else {
  822. // Formula
  823. $this->_castToFormula($c,$r,$cellDataType,$value,$calculatedValue,$sharedFormulas,'_castToBool');
  824. if (isset($c->f['t'])) {
  825. $att = array();
  826. $att = $c->f;
  827. $docSheet->getCell($r)->setFormulaAttributes($att);
  828. }
  829. // echo '$calculatedValue = '.$calculatedValue.'<br />';
  830. }
  831. break;
  832. case "inlineStr":
  833. // echo 'Inline String<br />';
  834. $value = $this->_parseRichText($c->is);
  835. break;
  836. case "e":
  837. // echo 'Error<br />';
  838. if (!isset($c->f)) {
  839. $value = self::_castToError($c);
  840. } else {
  841. // Formula
  842. $this->_castToFormula($c,$r,$cellDataType,$value,$calculatedValue,$sharedFormulas,'_castToError');
  843. // echo '$calculatedValue = '.$calculatedValue.'<br />';
  844. }
  845. break;
  846. default:
  847. // echo 'Default<br />';
  848. if (!isset($c->f)) {
  849. // echo 'Not a Formula<br />';
  850. $value = self::_castToString($c);
  851. } else {
  852. // echo 'Treat as Formula<br />';
  853. // Formula
  854. $this->_castToFormula($c,$r,$cellDataType,$value,$calculatedValue,$sharedFormulas,'_castToString');
  855. // echo '$calculatedValue = '.$calculatedValue.'<br />';
  856. }
  857. break;
  858. }
  859. // echo 'Value is '.$value.'<br />';
  860. // Check for numeric values
  861. if (is_numeric($value) && $cellDataType != 's') {
  862. if ($value == (int)$value) $value = (int)$value;
  863. elseif ($value == (float)$value) $value = (float)$value;
  864. elseif ($value == (double)$value) $value = (double)$value;
  865. }
  866. // Rich text?
  867. if ($value instanceof PHPExcel_RichText && $this->_readDataOnly) {
  868. $value = $value->getPlainText();
  869. }
  870. $cell = $docSheet->getCell($r);
  871. // Assign value
  872. if ($cellDataType != '') {
  873. $cell->setValueExplicit($value, $cellDataType);
  874. } else {
  875. $cell->setValue($value);
  876. }
  877. if ($calculatedValue !== NULL) {
  878. $cell->setCalculatedValue($calculatedValue);
  879. }
  880. // Style information?
  881. if ($c["s"] && !$this->_readDataOnly) {
  882. // no style index means 0, it seems
  883. $cell->setXfIndex(isset($styles[intval($c["s"])]) ?
  884. intval($c["s"]) : 0);
  885. }
  886. }
  887. }
  888. }
  889. $conditionals = array();
  890. if (!$this->_readDataOnly && $xmlSheet && $xmlSheet->conditionalFormatting) {
  891. foreach ($xmlSheet->conditionalFormatting as $conditional) {
  892. foreach ($conditional->cfRule as $cfRule) {
  893. if (
  894. (
  895. (string)$cfRule["type"] == PHPExcel_Style_Conditional::CONDITION_NONE ||
  896. (string)$cfRule["type"] == PHPExcel_Style_Conditional::CONDITION_CELLIS ||
  897. (string)$cfRule["type"] == PHPExcel_Style_Conditional::CONDITION_CONTAINSTEXT ||
  898. (string)$cfRule["type"] == PHPExcel_Style_Conditional::CONDITION_EXPRESSION
  899. ) && isset($dxfs[intval($cfRule["dxfId"])])
  900. ) {
  901. $conditionals[(string) $conditional["sqref"]][intval($cfRule["priority"])] = $cfRule;
  902. }
  903. }
  904. }
  905. foreach ($conditionals as $ref => $cfRules) {
  906. ksort($cfRules);
  907. $conditionalStyles = array();
  908. foreach ($cfRules as $cfRule) {
  909. $objConditional = new PHPExcel_Style_Conditional();
  910. $objConditional->setConditionType((string)$cfRule["type"]);
  911. $objConditional->setOperatorType((string)$cfRule["operator"]);
  912. if ((string)$cfRule["text"] != '') {
  913. $objConditional->setText((string)$cfRule["text"]);
  914. }
  915. if (count($cfRule->formula) > 1) {
  916. foreach ($cfRule->formula as $formula) {
  917. $objConditional->addCondition((string)$formula);
  918. }
  919. } else {
  920. $objConditional->addCondition((string)$cfRule->formula);
  921. }
  922. $objConditional->setStyle(clone $dxfs[intval($cfRule["dxfId"])]);
  923. $conditionalStyles[] = $objConditional;
  924. }
  925. // Extract all cell references in $ref
  926. $aReferences = PHPExcel_Cell::extractAllCellReferencesInRange($ref);
  927. foreach ($aReferences as $reference) {
  928. $docSheet->getStyle($reference)->setConditionalStyles($conditionalStyles);
  929. }
  930. }
  931. }
  932. $aKeys = array("sheet", "objects", "scenarios", "formatCells", "formatColumns", "formatRows", "insertColumns", "insertRows", "insertHyperlinks", "deleteColumns", "deleteRows", "selectLockedCells", "sort", "autoFilter", "pivotTables", "selectUnlockedCells");
  933. if (!$this->_readDataOnly && $xmlSheet && $xmlSheet->sheetProtection) {
  934. foreach ($aKeys as $key) {
  935. $method = "set" . ucfirst($key);
  936. $docSheet->getProtection()->$method($xmlSheet->sheetProtection[$key] == "true");
  937. }
  938. }
  939. if (!$this->_readDataOnly && $xmlSheet && $xmlSheet->sheetProtection) {
  940. $docSheet->getProtection()->setPassword((string) $xmlSheet->sheetProtection["password"], true);
  941. if ($xmlSheet->protectedRanges->protectedRange) {
  942. foreach ($xmlSheet->protectedRanges->protectedRange as $protectedRange) {
  943. $docSheet->protectCells((string) $protectedRange["sqref"], (string) $protectedRange["password"], true);
  944. }
  945. }
  946. }
  947. if ($xmlSheet && $xmlSheet->autoFilter && !$this->_readDataOnly) {
  948. $docSheet->setAutoFilter((string) $xmlSheet->autoFilter["ref"]);
  949. }
  950. if ($xmlSheet && $xmlSheet->mergeCells && $xmlSheet->mergeCells->mergeCell && !$this->_readDataOnly) {
  951. foreach ($xmlSheet->mergeCells->mergeCell as $mergeCell) {
  952. $docSheet->mergeCells((string) $mergeCell["ref"]);
  953. }
  954. }
  955. if ($xmlSheet && $xmlSheet->pageMargins && !$this->_readDataOnly) {
  956. $docPageMargins = $docSheet->getPageMargins();
  957. $docPageMargins->setLeft(floatval($xmlSheet->pageMargins["left"]));
  958. $docPageMargins->setRight(floatval($xmlSheet->pageMargins["right"]));
  959. $docPageMargins->setTop(floatval($xmlSheet->pageMargins["top"]));
  960. $docPageMargins->setBottom(floatval($xmlSheet->pageMargins["bottom"]));
  961. $docPageMargins->setHeader(floatval($xmlSheet->pageMargins["header"]));
  962. $docPageMargins->setFooter(floatval($xmlSheet->pageMargins["footer"]));
  963. }
  964. if ($xmlSheet && $xmlSheet->pageSetup && !$this->_readDataOnly) {
  965. $docPageSetup = $docSheet->getPageSetup();
  966. if (isset($xmlSheet->pageSetup["orientation"])) {
  967. $docPageSetup->setOrientation((string) $xmlSheet->pageSetup["orientation"]);
  968. }
  969. if (isset($xmlSheet->pageSetup["paperSize"])) {
  970. $docPageSetup->setPaperSize(intval($xmlSheet->pageSetup["paperSize"]));
  971. }
  972. if (isset($xmlSheet->pageSetup["scale"])) {
  973. $docPageSetup->setScale(intval($xmlSheet->pageSetup["scale"]), false);
  974. }
  975. if (isset($xmlSheet->pageSetup["fitToHeight"]) && intval($xmlSheet->pageSetup["fitToHeight"]) >= 0) {
  976. $docPageSetup->setFitToHeight(intval($xmlSheet->pageSetup["fitToHeight"]), false);
  977. }
  978. if (isset($xmlSheet->pageSetup["fitToWidth"]) && intval($xmlSheet->pageSetup["fitToWidth"]) >= 0) {
  979. $docPageSetup->setFitToWidth(intval($xmlSheet->pageSetup["fitToWidth"]), false);
  980. }
  981. if (isset($xmlSheet->pageSetup["firstPageNumber"]) && isset($xmlSheet->pageSetup["useFirstPageNumber"]) &&
  982. ((string)$xmlSheet->pageSetup["useFirstPageNumber"] == 'true' || (string)$xmlSheet->pageSetup["useFirstPageNumber"] == '1')) {
  983. $docPageSetup->setFirstPageNumber(intval($xmlSheet->pageSetup["firstPageNumber"]));
  984. }
  985. }
  986. if ($xmlSheet && $xmlSheet->headerFooter && !$this->_readDataOnly) {
  987. $docHeaderFooter = $docSheet->getHeaderFooter();
  988. if (isset($xmlSheet->headerFooter["differentOddEven"]) &&
  989. ((string)$xmlSheet->headerFooter["differentOddEven"] == 'true' || (string)$xmlSheet->headerFooter["differentOddEven"] == '1')) {
  990. $docHeaderFooter->setDifferentOddEven(true);
  991. } else {
  992. $docHeaderFooter->setDifferentOddEven(false);
  993. }
  994. if (isset($xmlSheet->headerFooter["differentFirst"]) &&
  995. ((string)$xmlSheet->headerFooter["differentFirst"] == 'true' || (string)$xmlSheet->headerFooter["differentFirst"] == '1')) {
  996. $docHeaderFooter->setDifferentFirst(true);
  997. } else {
  998. $docHeaderFooter->setDifferentFirst(false);
  999. }
  1000. if (isset($xmlSheet->headerFooter["scaleWithDoc"]) &&
  1001. ((string)$xmlSheet->headerFooter["scaleWithDoc"] == 'false' || (string)$xmlSheet->headerFooter["scaleWithDoc"] == '0')) {
  1002. $docHeaderFooter->setScaleWithDocument(false);
  1003. } else {
  1004. $docHeaderFooter->setScaleWithDocument(true);
  1005. }
  1006. if (isset($xmlSheet->headerFooter["alignWithMargins"]) &&
  1007. ((string)$xmlSheet->headerFooter["alignWithMargins"] == 'false' || (string)$xmlSheet->headerFooter["alignWithMargins"] == '0')) {
  1008. $docHeaderFooter->setAlignWithMargins(false);
  1009. } else {
  1010. $docHeaderFooter->setAlignWithMargins(true);
  1011. }
  1012. $docHeaderFooter->setOddHeader((string) $xmlSheet->headerFooter->oddHeader);
  1013. $docHeaderFooter->setOddFooter((string) $xmlSheet->headerFooter->oddFooter);
  1014. $docHeaderFooter->setEvenHeader((string) $xmlSheet->headerFooter->evenHeader);
  1015. $docHeaderFooter->setEvenFooter((string) $xmlSheet->headerFooter->evenFooter);
  1016. $docHeaderFooter->setFirstHeader((string) $xmlSheet->headerFooter->firstHeader);
  1017. $docHeaderFooter->setFirstFooter((string) $xmlSheet->headerFooter->firstFooter);
  1018. }
  1019. if ($xmlSheet && $xmlSheet->rowBreaks && $xmlSheet->rowBreaks->brk && !$this->_readDataOnly) {
  1020. foreach ($xmlSheet->rowBreaks->brk as $brk) {
  1021. if ($brk["man"]) {
  1022. $docSheet->setBreak("A$brk[id]", PHPExcel_Worksheet::BREAK_ROW);
  1023. }
  1024. }
  1025. }
  1026. if ($xmlSheet && $xmlSheet->colBreaks && $xmlSheet->colBreaks->brk && !$this->_readDataOnly) {
  1027. foreach ($xmlSheet->colBreaks->brk as $brk) {
  1028. if ($brk["man"]) {
  1029. $docSheet->setBreak(PHPExcel_Cell::stringFromColumnIndex($brk["id"]) . "1", PHPExcel_Worksheet::BREAK_COLUMN);
  1030. }
  1031. }
  1032. }
  1033. if ($xmlSheet && $xmlSheet->dataValidations && !$this->_readDataOnly) {
  1034. foreach ($xmlSheet->dataValidations->dataValidation as $dataValidation) {
  1035. // Uppercase coordinate
  1036. $range = strtoupper($dataValidation["sqref"]);
  1037. $rangeSet = explode(' ',$range);
  1038. foreach($rangeSet as $range) {
  1039. $stRange = $docSheet->shrinkRangeToFit($range);
  1040. // Extract all cell references in $range
  1041. $aReferences = PHPExcel_Cell::extractAllCellReferencesInRange($stRange);
  1042. foreach ($aReferences as $reference) {
  1043. // Create validation
  1044. $docValidation = $docSheet->getCell($reference)->getDataValidation();
  1045. $docValidation->setType((string) $dataValidation["type"]);
  1046. $docValidation->setErrorStyle((string) $dataValidation["errorStyle"]);
  1047. $docValidation->setOperator((string) $dataValidation["operator"]);
  1048. $docValidation->setAllowBlank($dataValidation["allowBlank"] != 0);
  1049. $docValidation->setShowDropDown($dataValidation["showDropDown"] == 0);
  1050. $docValidation->setShowInputMessage($dataValidation["showInputMessage"] != 0);
  1051. $docValidation->setShowErrorMessage($dataValidation["showErrorMessage"] != 0);
  1052. $docValidation->setErrorTitle((string) $dataValidation["errorTitle"]);
  1053. $docValidation->setError((string) $dataValidation["error"]);
  1054. $docValidation->setPromptTitle((string) $dataValidation["promptTitle"]);
  1055. $docValidation->setPrompt((string) $dataValidation["prompt"]);
  1056. $docValidation->setFormula1((string) $dataValidation->formula1);
  1057. $docValidation->setFormula2((string) $dataValidation->formula2);
  1058. }
  1059. }
  1060. }
  1061. }
  1062. // Add hyperlinks
  1063. $hyperlinks = array();
  1064. if (!$this->_readDataOnly) {
  1065. // Locate hyperlink relations
  1066. if ($zip->locateName(dirname("$dir/$fileWorksheet") . "/_rels/" . basename($fileWorksheet) . ".rels")) {
  1067. $relsWorksheet = simplexml_load_string($this->_getFromZipArchive($zip, dirname("$dir/$fileWorksheet") . "/_rels/" . basename($fileWorksheet) . ".rels") ); //~ http://schemas.openxmlformats.org/package/2006/relationships");
  1068. foreach ($relsWorksheet->Relationship as $ele) {
  1069. if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/hyperlink") {
  1070. $hyperlinks[(string)$ele["Id"]] = (string)$ele["Target"];
  1071. }
  1072. }
  1073. }
  1074. // Loop through hyperlinks
  1075. if ($xmlSheet && $xmlSheet->hyperlinks) {
  1076. foreach ($xmlSheet->hyperlinks->hyperlink as $hyperlink) {
  1077. // Link url
  1078. $linkRel = $hyperlink->attributes('http://schemas.openxmlformats.org/officeDocument/2006/relationships');
  1079. foreach (PHPExcel_Cell::extractAllCellReferencesInRange($hyperlink['ref']) as $cellReference) {
  1080. $cell = $docSheet->getCell( $cellReference );
  1081. if (isset($linkRel['id'])) {
  1082. $cell->getHyperlink()->setUrl( $hyperlinks[ (string)$linkRel['id'] ] );
  1083. }
  1084. if (isset($hyperlink['location'])) {
  1085. $cell->getHyperlink()->setUrl( 'sheet://' . (string)$hyperlink['location'] );
  1086. }
  1087. // Tooltip
  1088. if (isset($hyperlink['tooltip'])) {
  1089. $cell->getHyperlink()->setTooltip( (string)$hyperlink['tooltip'] );
  1090. }
  1091. }
  1092. }
  1093. }
  1094. }
  1095. // Add comments
  1096. $comments = array();
  1097. $vmlComments = array();
  1098. if (!$this->_readDataOnly) {
  1099. // Locate comment relations
  1100. if ($zip->locateName(dirname("$dir/$fileWorksheet") . "/_rels/" . basename($fileWorksheet) . ".rels")) {
  1101. $relsWorksheet = simplexml_load_string($this->_getFromZipArchive($zip, dirname("$dir/$fileWorksheet") . "/_rels/" . basename($fileWorksheet) . ".rels") ); //~ http://schemas.openxmlformats.org…

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