[Summary view]

Differences Between: [Versions 310 and 311] [Versions 310 and 400] [Versions 310 and 401] [Versions 310 and 402] [Versions 310 and 403]

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