[Summary view]

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

   1  <?php
   2  
   3  namespace PhpOffice\PhpSpreadsheet\Reader;
   4  
   5  use DateTime;
   6  use DateTimeZone;
   7  use PhpOffice\PhpSpreadsheet\Cell\AddressHelper;
   8  use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
   9  use PhpOffice\PhpSpreadsheet\Cell\DataType;
  10  use PhpOffice\PhpSpreadsheet\DefinedName;
  11  use PhpOffice\PhpSpreadsheet\Reader\Security\XmlScanner;
  12  use PhpOffice\PhpSpreadsheet\Reader\Xlsx\Namespaces;
  13  use PhpOffice\PhpSpreadsheet\Reader\Xml\PageSettings;
  14  use PhpOffice\PhpSpreadsheet\Reader\Xml\Properties;
  15  use PhpOffice\PhpSpreadsheet\Reader\Xml\Style;
  16  use PhpOffice\PhpSpreadsheet\RichText\RichText;
  17  use PhpOffice\PhpSpreadsheet\Settings;
  18  use PhpOffice\PhpSpreadsheet\Shared\Date;
  19  use PhpOffice\PhpSpreadsheet\Shared\File;
  20  use PhpOffice\PhpSpreadsheet\Shared\StringHelper;
  21  use PhpOffice\PhpSpreadsheet\Spreadsheet;
  22  use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
  23  use SimpleXMLElement;
  24  
  25  /**
  26   * Reader for SpreadsheetML, the XML schema for Microsoft Office Excel 2003.
  27   */
  28  class Xml extends BaseReader
  29  {
  30      public const NAMESPACES_SS = 'urn:schemas-microsoft-com:office:spreadsheet';
  31  
  32      /**
  33       * Formats.
  34       *
  35       * @var array
  36       */
  37      protected $styles = [];
  38  
  39      /**
  40       * Create a new Excel2003XML Reader instance.
  41       */
  42      public function __construct()
  43      {
  44          parent::__construct();
  45          $this->securityScanner = XmlScanner::getInstance($this);
  46      }
  47  
  48      /** @var string */
  49      private $fileContents = '';
  50  
  51      public static function xmlMappings(): array
  52      {
  53          return array_merge(
  54              Style\Fill::FILL_MAPPINGS,
  55              Style\Border::BORDER_MAPPINGS
  56          );
  57      }
  58  
  59      /**
  60       * Can the current IReader read the file?
  61       */
  62      public function canRead(string $filename): bool
  63      {
  64          //    Office                    xmlns:o="urn:schemas-microsoft-com:office:office"
  65          //    Excel                    xmlns:x="urn:schemas-microsoft-com:office:excel"
  66          //    XML Spreadsheet            xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
  67          //    Spreadsheet component    xmlns:c="urn:schemas-microsoft-com:office:component:spreadsheet"
  68          //    XML schema                 xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882"
  69          //    XML data type            xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882"
  70          //    MS-persist recordset    xmlns:rs="urn:schemas-microsoft-com:rowset"
  71          //    Rowset                    xmlns:z="#RowsetSchema"
  72          //
  73  
  74          $signature = [
  75              '<?xml version="1.0"',
  76              'xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet',
  77          ];
  78  
  79          // Open file
  80          $data = file_get_contents($filename) ?: '';
  81  
  82          // Why?
  83          //$data = str_replace("'", '"', $data); // fix headers with single quote
  84  
  85          $valid = true;
  86          foreach ($signature as $match) {
  87              // every part of the signature must be present
  88              if (strpos($data, $match) === false) {
  89                  $valid = false;
  90  
  91                  break;
  92              }
  93          }
  94  
  95          //    Retrieve charset encoding
  96          if (preg_match('/<?xml.*encoding=[\'"](.*?)[\'"].*?>/m', $data, $matches)) {
  97              $charSet = strtoupper($matches[1]);
  98              if (preg_match('/^ISO-8859-\d[\dL]?$/i', $charSet) === 1) {
  99                  $data = StringHelper::convertEncoding($data, 'UTF-8', $charSet);
 100                  $data = (string) preg_replace('/(<?xml.*encoding=[\'"]).*?([\'"].*?>)/um', '$1' . 'UTF-8' . '$2', $data, 1);
 101              }
 102          }
 103          $this->fileContents = $data;
 104  
 105          return $valid;
 106      }
 107  
 108      /**
 109       * Check if the file is a valid SimpleXML.
 110       *
 111       * @param string $filename
 112       *
 113       * @return false|SimpleXMLElement
 114       */
 115      public function trySimpleXMLLoadString($filename)
 116      {
 117          try {
 118              $xml = simplexml_load_string(
 119                  $this->getSecurityScannerOrThrow()->scan($this->fileContents ?: file_get_contents($filename)),
 120                  'SimpleXMLElement',
 121                  Settings::getLibXmlLoaderOptions()
 122              );
 123          } catch (\Exception $e) {
 124              throw new Exception('Cannot load invalid XML file: ' . $filename, 0, $e);
 125          }
 126          $this->fileContents = '';
 127  
 128          return $xml;
 129      }
 130  
 131      /**
 132       * Reads names of the worksheets from a file, without parsing the whole file to a Spreadsheet object.
 133       *
 134       * @param string $filename
 135       *
 136       * @return array
 137       */
 138      public function listWorksheetNames($filename)
 139      {
 140          File::assertFile($filename);
 141          if (!$this->canRead($filename)) {
 142              throw new Exception($filename . ' is an Invalid Spreadsheet file.');
 143          }
 144  
 145          $worksheetNames = [];
 146  
 147          $xml = $this->trySimpleXMLLoadString($filename);
 148          if ($xml === false) {
 149              throw new Exception("Problem reading {$filename}");
 150          }
 151  
 152          $xml_ss = $xml->children(self::NAMESPACES_SS);
 153          foreach ($xml_ss->Worksheet as $worksheet) {
 154              $worksheet_ss = self::getAttributes($worksheet, self::NAMESPACES_SS);
 155              $worksheetNames[] = (string) $worksheet_ss['Name'];
 156          }
 157  
 158          return $worksheetNames;
 159      }
 160  
 161      /**
 162       * Return worksheet info (Name, Last Column Letter, Last Column Index, Total Rows, Total Columns).
 163       *
 164       * @param string $filename
 165       *
 166       * @return array
 167       */
 168      public function listWorksheetInfo($filename)
 169      {
 170          File::assertFile($filename);
 171          if (!$this->canRead($filename)) {
 172              throw new Exception($filename . ' is an Invalid Spreadsheet file.');
 173          }
 174  
 175          $worksheetInfo = [];
 176  
 177          $xml = $this->trySimpleXMLLoadString($filename);
 178          if ($xml === false) {
 179              throw new Exception("Problem reading {$filename}");
 180          }
 181  
 182          $worksheetID = 1;
 183          $xml_ss = $xml->children(self::NAMESPACES_SS);
 184          foreach ($xml_ss->Worksheet as $worksheet) {
 185              $worksheet_ss = self::getAttributes($worksheet, self::NAMESPACES_SS);
 186  
 187              $tmpInfo = [];
 188              $tmpInfo['worksheetName'] = '';
 189              $tmpInfo['lastColumnLetter'] = 'A';
 190              $tmpInfo['lastColumnIndex'] = 0;
 191              $tmpInfo['totalRows'] = 0;
 192              $tmpInfo['totalColumns'] = 0;
 193  
 194              $tmpInfo['worksheetName'] = "Worksheet_{$worksheetID}";
 195              if (isset($worksheet_ss['Name'])) {
 196                  $tmpInfo['worksheetName'] = (string) $worksheet_ss['Name'];
 197              }
 198  
 199              if (isset($worksheet->Table->Row)) {
 200                  $rowIndex = 0;
 201  
 202                  foreach ($worksheet->Table->Row as $rowData) {
 203                      $columnIndex = 0;
 204                      $rowHasData = false;
 205  
 206                      foreach ($rowData->Cell as $cell) {
 207                          if (isset($cell->Data)) {
 208                              $tmpInfo['lastColumnIndex'] = max($tmpInfo['lastColumnIndex'], $columnIndex);
 209                              $rowHasData = true;
 210                          }
 211  
 212                          ++$columnIndex;
 213                      }
 214  
 215                      ++$rowIndex;
 216  
 217                      if ($rowHasData) {
 218                          $tmpInfo['totalRows'] = max($tmpInfo['totalRows'], $rowIndex);
 219                      }
 220                  }
 221              }
 222  
 223              $tmpInfo['lastColumnLetter'] = Coordinate::stringFromColumnIndex($tmpInfo['lastColumnIndex'] + 1);
 224              $tmpInfo['totalColumns'] = $tmpInfo['lastColumnIndex'] + 1;
 225  
 226              $worksheetInfo[] = $tmpInfo;
 227              ++$worksheetID;
 228          }
 229  
 230          return $worksheetInfo;
 231      }
 232  
 233      /**
 234       * Loads Spreadsheet from string.
 235       */
 236      public function loadSpreadsheetFromString(string $contents): Spreadsheet
 237      {
 238          // Create new Spreadsheet
 239          $spreadsheet = new Spreadsheet();
 240          $spreadsheet->removeSheetByIndex(0);
 241  
 242          // Load into this instance
 243          return $this->loadIntoExisting($contents, $spreadsheet, true);
 244      }
 245  
 246      /**
 247       * Loads Spreadsheet from file.
 248       */
 249      protected function loadSpreadsheetFromFile(string $filename): Spreadsheet
 250      {
 251          // Create new Spreadsheet
 252          $spreadsheet = new Spreadsheet();
 253          $spreadsheet->removeSheetByIndex(0);
 254  
 255          // Load into this instance
 256          return $this->loadIntoExisting($filename, $spreadsheet);
 257      }
 258  
 259      /**
 260       * Loads from file or contents into Spreadsheet instance.
 261       *
 262       * @param string $filename file name if useContents is false else file contents
 263       */
 264      public function loadIntoExisting(string $filename, Spreadsheet $spreadsheet, bool $useContents = false): Spreadsheet
 265      {
 266          if ($useContents) {
 267              $this->fileContents = $filename;
 268          } else {
 269              File::assertFile($filename);
 270              if (!$this->canRead($filename)) {
 271                  throw new Exception($filename . ' is an Invalid Spreadsheet file.');
 272              }
 273          }
 274  
 275          $xml = $this->trySimpleXMLLoadString($filename);
 276          if ($xml === false) {
 277              throw new Exception("Problem reading {$filename}");
 278          }
 279  
 280          $namespaces = $xml->getNamespaces(true);
 281  
 282          (new Properties($spreadsheet))->readProperties($xml, $namespaces);
 283  
 284          $this->styles = (new Style())->parseStyles($xml, $namespaces);
 285          if (isset($this->styles['Default'])) {
 286              $spreadsheet->getCellXfCollection()[0]->applyFromArray($this->styles['Default']);
 287          }
 288  
 289          $worksheetID = 0;
 290          $xml_ss = $xml->children(self::NAMESPACES_SS);
 291  
 292          /** @var null|SimpleXMLElement $worksheetx */
 293          foreach ($xml_ss->Worksheet as $worksheetx) {
 294              $worksheet = $worksheetx ?? new SimpleXMLElement('<xml></xml>');
 295              $worksheet_ss = self::getAttributes($worksheet, self::NAMESPACES_SS);
 296  
 297              if (
 298                  isset($this->loadSheetsOnly, $worksheet_ss['Name']) &&
 299                  (!in_array($worksheet_ss['Name'], /** @scrutinizer ignore-type */ $this->loadSheetsOnly))
 300              ) {
 301                  continue;
 302              }
 303  
 304              // Create new Worksheet
 305              $spreadsheet->createSheet();
 306              $spreadsheet->setActiveSheetIndex($worksheetID);
 307              $worksheetName = '';
 308              if (isset($worksheet_ss['Name'])) {
 309                  $worksheetName = (string) $worksheet_ss['Name'];
 310                  //    Use false for $updateFormulaCellReferences to prevent adjustment of worksheet references in
 311                  //        formula cells... during the load, all formulae should be correct, and we're simply bringing
 312                  //        the worksheet name in line with the formula, not the reverse
 313                  $spreadsheet->getActiveSheet()->setTitle($worksheetName, false, false);
 314              }
 315              if (isset($worksheet_ss['Protected'])) {
 316                  $protection = (string) $worksheet_ss['Protected'] === '1';
 317                  $spreadsheet->getActiveSheet()->getProtection()->setSheet($protection);
 318              }
 319  
 320              // locally scoped defined names
 321              if (isset($worksheet->Names[0])) {
 322                  foreach ($worksheet->Names[0] as $definedName) {
 323                      $definedName_ss = self::getAttributes($definedName, self::NAMESPACES_SS);
 324                      $name = (string) $definedName_ss['Name'];
 325                      $definedValue = (string) $definedName_ss['RefersTo'];
 326                      $convertedValue = AddressHelper::convertFormulaToA1($definedValue);
 327                      if ($convertedValue[0] === '=') {
 328                          $convertedValue = substr($convertedValue, 1);
 329                      }
 330                      $spreadsheet->addDefinedName(DefinedName::createInstance($name, $spreadsheet->getActiveSheet(), $convertedValue, true));
 331                  }
 332              }
 333  
 334              $columnID = 'A';
 335              if (isset($worksheet->Table->Column)) {
 336                  foreach ($worksheet->Table->Column as $columnData) {
 337                      $columnData_ss = self::getAttributes($columnData, self::NAMESPACES_SS);
 338                      $colspan = 0;
 339                      if (isset($columnData_ss['Span'])) {
 340                          $spanAttr = (string) $columnData_ss['Span'];
 341                          if (is_numeric($spanAttr)) {
 342                              $colspan = max(0, (int) $spanAttr);
 343                          }
 344                      }
 345                      if (isset($columnData_ss['Index'])) {
 346                          $columnID = Coordinate::stringFromColumnIndex((int) $columnData_ss['Index']);
 347                      }
 348                      $columnWidth = null;
 349                      if (isset($columnData_ss['Width'])) {
 350                          $columnWidth = $columnData_ss['Width'];
 351                      }
 352                      $columnVisible = null;
 353                      if (isset($columnData_ss['Hidden'])) {
 354                          $columnVisible = ((string) $columnData_ss['Hidden']) !== '1';
 355                      }
 356                      while ($colspan >= 0) {
 357                          if (isset($columnWidth)) {
 358                              $spreadsheet->getActiveSheet()->getColumnDimension($columnID)->setWidth($columnWidth / 5.4);
 359                          }
 360                          if (isset($columnVisible)) {
 361                              $spreadsheet->getActiveSheet()->getColumnDimension($columnID)->setVisible($columnVisible);
 362                          }
 363                          ++$columnID;
 364                          --$colspan;
 365                      }
 366                  }
 367              }
 368  
 369              $rowID = 1;
 370              if (isset($worksheet->Table->Row)) {
 371                  $additionalMergedCells = 0;
 372                  foreach ($worksheet->Table->Row as $rowData) {
 373                      $rowHasData = false;
 374                      $row_ss = self::getAttributes($rowData, self::NAMESPACES_SS);
 375                      if (isset($row_ss['Index'])) {
 376                          $rowID = (int) $row_ss['Index'];
 377                      }
 378                      if (isset($row_ss['Hidden'])) {
 379                          $rowVisible = ((string) $row_ss['Hidden']) !== '1';
 380                          $spreadsheet->getActiveSheet()->getRowDimension($rowID)->setVisible($rowVisible);
 381                      }
 382  
 383                      $columnID = 'A';
 384                      foreach ($rowData->Cell as $cell) {
 385                          $cell_ss = self::getAttributes($cell, self::NAMESPACES_SS);
 386                          if (isset($cell_ss['Index'])) {
 387                              $columnID = Coordinate::stringFromColumnIndex((int) $cell_ss['Index']);
 388                          }
 389                          $cellRange = $columnID . $rowID;
 390  
 391                          if ($this->getReadFilter() !== null) {
 392                              if (!$this->getReadFilter()->readCell($columnID, $rowID, $worksheetName)) {
 393                                  ++$columnID;
 394  
 395                                  continue;
 396                              }
 397                          }
 398  
 399                          if (isset($cell_ss['HRef'])) {
 400                              $spreadsheet->getActiveSheet()->getCell($cellRange)->getHyperlink()->setUrl((string) $cell_ss['HRef']);
 401                          }
 402  
 403                          if ((isset($cell_ss['MergeAcross'])) || (isset($cell_ss['MergeDown']))) {
 404                              $columnTo = $columnID;
 405                              if (isset($cell_ss['MergeAcross'])) {
 406                                  $additionalMergedCells += (int) $cell_ss['MergeAcross'];
 407                                  $columnTo = Coordinate::stringFromColumnIndex((int) (Coordinate::columnIndexFromString($columnID) + $cell_ss['MergeAcross']));
 408                              }
 409                              $rowTo = $rowID;
 410                              if (isset($cell_ss['MergeDown'])) {
 411                                  $rowTo = $rowTo + $cell_ss['MergeDown'];
 412                              }
 413                              $cellRange .= ':' . $columnTo . $rowTo;
 414                              $spreadsheet->getActiveSheet()->mergeCells($cellRange, Worksheet::MERGE_CELL_CONTENT_HIDE);
 415                          }
 416  
 417                          $hasCalculatedValue = false;
 418                          $cellDataFormula = '';
 419                          if (isset($cell_ss['Formula'])) {
 420                              $cellDataFormula = $cell_ss['Formula'];
 421                              $hasCalculatedValue = true;
 422                          }
 423                          if (isset($cell->Data)) {
 424                              $cellData = $cell->Data;
 425                              $cellValue = (string) $cellData;
 426                              $type = DataType::TYPE_NULL;
 427                              $cellData_ss = self::getAttributes($cellData, self::NAMESPACES_SS);
 428                              if (isset($cellData_ss['Type'])) {
 429                                  $cellDataType = $cellData_ss['Type'];
 430                                  switch ($cellDataType) {
 431                                      /*
 432                                      const TYPE_STRING        = 's';
 433                                      const TYPE_FORMULA        = 'f';
 434                                      const TYPE_NUMERIC        = 'n';
 435                                      const TYPE_BOOL            = 'b';
 436                                      const TYPE_NULL            = 'null';
 437                                      const TYPE_INLINE        = 'inlineStr';
 438                                      const TYPE_ERROR        = 'e';
 439                                      */
 440                                      case 'String':
 441                                          $type = DataType::TYPE_STRING;
 442  
 443                                          break;
 444                                      case 'Number':
 445                                          $type = DataType::TYPE_NUMERIC;
 446                                          $cellValue = (float) $cellValue;
 447                                          if (floor($cellValue) == $cellValue) {
 448                                              $cellValue = (int) $cellValue;
 449                                          }
 450  
 451                                          break;
 452                                      case 'Boolean':
 453                                          $type = DataType::TYPE_BOOL;
 454                                          $cellValue = ($cellValue != 0);
 455  
 456                                          break;
 457                                      case 'DateTime':
 458                                          $type = DataType::TYPE_NUMERIC;
 459                                          $dateTime = new DateTime($cellValue, new DateTimeZone('UTC'));
 460                                          $cellValue = Date::PHPToExcel($dateTime);
 461  
 462                                          break;
 463                                      case 'Error':
 464                                          $type = DataType::TYPE_ERROR;
 465                                          $hasCalculatedValue = false;
 466  
 467                                          break;
 468                                  }
 469                              }
 470  
 471                              if ($hasCalculatedValue) {
 472                                  $type = DataType::TYPE_FORMULA;
 473                                  $columnNumber = Coordinate::columnIndexFromString($columnID);
 474                                  $cellDataFormula = AddressHelper::convertFormulaToA1($cellDataFormula, $rowID, $columnNumber);
 475                              }
 476  
 477                              $spreadsheet->getActiveSheet()->getCell($columnID . $rowID)->setValueExplicit((($hasCalculatedValue) ? $cellDataFormula : $cellValue), $type);
 478                              if ($hasCalculatedValue) {
 479                                  $spreadsheet->getActiveSheet()->getCell($columnID . $rowID)->setCalculatedValue($cellValue);
 480                              }
 481                              $rowHasData = true;
 482                          }
 483  
 484                          if (isset($cell->Comment)) {
 485                              $this->parseCellComment($cell->Comment, $spreadsheet, $columnID, $rowID);
 486                          }
 487  
 488                          if (isset($cell_ss['StyleID'])) {
 489                              $style = (string) $cell_ss['StyleID'];
 490                              if ((isset($this->styles[$style])) && (!empty($this->styles[$style]))) {
 491                                  //if (!$spreadsheet->getActiveSheet()->cellExists($columnID . $rowID)) {
 492                                  //    $spreadsheet->getActiveSheet()->getCell($columnID . $rowID)->setValue(null);
 493                                  //}
 494                                  $spreadsheet->getActiveSheet()->getStyle($cellRange)
 495                                      ->applyFromArray($this->styles[$style]);
 496                              }
 497                          }
 498                          ++$columnID;
 499                          while ($additionalMergedCells > 0) {
 500                              ++$columnID;
 501                              --$additionalMergedCells;
 502                          }
 503                      }
 504  
 505                      if ($rowHasData) {
 506                          if (isset($row_ss['Height'])) {
 507                              $rowHeight = $row_ss['Height'];
 508                              $spreadsheet->getActiveSheet()->getRowDimension($rowID)->setRowHeight((float) $rowHeight);
 509                          }
 510                      }
 511  
 512                      ++$rowID;
 513                  }
 514              }
 515  
 516              $dataValidations = new Xml\DataValidations();
 517              $dataValidations->loadDataValidations($worksheet, $spreadsheet);
 518              $xmlX = $worksheet->children(Namespaces::URN_EXCEL);
 519              if (isset($xmlX->WorksheetOptions)) {
 520                  if (isset($xmlX->WorksheetOptions->FreezePanes)) {
 521                      $freezeRow = $freezeColumn = 1;
 522                      if (isset($xmlX->WorksheetOptions->SplitHorizontal)) {
 523                          $freezeRow = (int) $xmlX->WorksheetOptions->SplitHorizontal + 1;
 524                      }
 525                      if (isset($xmlX->WorksheetOptions->SplitVertical)) {
 526                          $freezeColumn = (int) $xmlX->WorksheetOptions->SplitVertical + 1;
 527                      }
 528                      $spreadsheet->getActiveSheet()->freezePane(Coordinate::stringFromColumnIndex($freezeColumn) . (string) $freezeRow);
 529                  }
 530                  (new PageSettings($xmlX))->loadPageSettings($spreadsheet);
 531                  if (isset($xmlX->WorksheetOptions->TopRowVisible, $xmlX->WorksheetOptions->LeftColumnVisible)) {
 532                      $leftTopRow = (string) $xmlX->WorksheetOptions->TopRowVisible;
 533                      $leftTopColumn = (string) $xmlX->WorksheetOptions->LeftColumnVisible;
 534                      if (is_numeric($leftTopRow) && is_numeric($leftTopColumn)) {
 535                          $leftTopCoordinate = Coordinate::stringFromColumnIndex((int) $leftTopColumn + 1) . (string) ($leftTopRow + 1);
 536                          $spreadsheet->getActiveSheet()->setTopLeftCell($leftTopCoordinate);
 537                      }
 538                  }
 539                  $rangeCalculated = false;
 540                  if (isset($xmlX->WorksheetOptions->Panes->Pane->RangeSelection)) {
 541                      if (1 === preg_match('/^R(\d+)C(\d+):R(\d+)C(\d+)$/', (string) $xmlX->WorksheetOptions->Panes->Pane->RangeSelection, $selectionMatches)) {
 542                          $selectedCell = Coordinate::stringFromColumnIndex((int) $selectionMatches[2])
 543                              . $selectionMatches[1]
 544                              . ':'
 545                              . Coordinate::stringFromColumnIndex((int) $selectionMatches[4])
 546                              . $selectionMatches[3];
 547                          $spreadsheet->getActiveSheet()->setSelectedCells($selectedCell);
 548                          $rangeCalculated = true;
 549                      }
 550                  }
 551                  if (!$rangeCalculated) {
 552                      if (isset($xmlX->WorksheetOptions->Panes->Pane->ActiveRow)) {
 553                          $activeRow = (string) $xmlX->WorksheetOptions->Panes->Pane->ActiveRow;
 554                      } else {
 555                          $activeRow = 0;
 556                      }
 557                      if (isset($xmlX->WorksheetOptions->Panes->Pane->ActiveCol)) {
 558                          $activeColumn = (string) $xmlX->WorksheetOptions->Panes->Pane->ActiveCol;
 559                      } else {
 560                          $activeColumn = 0;
 561                      }
 562                      if (is_numeric($activeRow) && is_numeric($activeColumn)) {
 563                          $selectedCell = Coordinate::stringFromColumnIndex((int) $activeColumn + 1) . (string) ($activeRow + 1);
 564                          $spreadsheet->getActiveSheet()->setSelectedCells($selectedCell);
 565                      }
 566                  }
 567              }
 568              ++$worksheetID;
 569          }
 570  
 571          // Globally scoped defined names
 572          $activeSheetIndex = 0;
 573          if (isset($xml->ExcelWorkbook->ActiveSheet)) {
 574              $activeSheetIndex = (int) (string) $xml->ExcelWorkbook->ActiveSheet;
 575          }
 576          $activeWorksheet = $spreadsheet->setActiveSheetIndex($activeSheetIndex);
 577          if (isset($xml->Names[0])) {
 578              foreach ($xml->Names[0] as $definedName) {
 579                  $definedName_ss = self::getAttributes($definedName, self::NAMESPACES_SS);
 580                  $name = (string) $definedName_ss['Name'];
 581                  $definedValue = (string) $definedName_ss['RefersTo'];
 582                  $convertedValue = AddressHelper::convertFormulaToA1($definedValue);
 583                  if ($convertedValue[0] === '=') {
 584                      $convertedValue = substr($convertedValue, 1);
 585                  }
 586                  $spreadsheet->addDefinedName(DefinedName::createInstance($name, $activeWorksheet, $convertedValue));
 587              }
 588          }
 589  
 590          // Return
 591          return $spreadsheet;
 592      }
 593  
 594      protected function parseCellComment(
 595          SimpleXMLElement $comment,
 596          Spreadsheet $spreadsheet,
 597          string $columnID,
 598          int $rowID
 599      ): void {
 600          $commentAttributes = $comment->attributes(self::NAMESPACES_SS);
 601          $author = 'unknown';
 602          if (isset($commentAttributes->Author)) {
 603              $author = (string) $commentAttributes->Author;
 604          }
 605  
 606          $node = $comment->Data->asXML();
 607          $annotation = strip_tags((string) $node);
 608          $spreadsheet->getActiveSheet()->getComment($columnID . $rowID)
 609              ->setAuthor($author)
 610              ->setText($this->parseRichText($annotation));
 611      }
 612  
 613      protected function parseRichText(string $annotation): RichText
 614      {
 615          $value = new RichText();
 616  
 617          $value->createText($annotation);
 618  
 619          return $value;
 620      }
 621  
 622      private static function getAttributes(?SimpleXMLElement $simple, string $node): SimpleXMLElement
 623      {
 624          return ($simple === null)
 625              ? new SimpleXMLElement('<xml></xml>')
 626              : ($simple->attributes($node) ?? new SimpleXMLElement('<xml></xml>'));
 627      }
 628  }