[Summary view] [Print] [Text view]

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