PageRenderTime 67ms CodeModel.GetById 29ms RepoModel.GetById 0ms app.codeStats 0ms

/include/PHPExcel/Reader/Excel2003XML.php

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