Subversion Repositories eFlore/Applications.cel

Rev

Go to most recent revision | Details | Last modification | View Log | RSS feed

Rev Author Line No. Line
2388 jpm 1
<?php
2
/**
3
 * PHPExcel
4
 *
5
 * Copyright (c) 2006 - 2013 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 - 2013 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 */
30
if (!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 - 2013 PHPExcel (http://www.codeplex.com/PHPExcel)
44
 */
45
class 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)
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)
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);
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($styleArray['numberformat']['code']))) {
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
}