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