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