[Summary view]

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

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