Search moodle.org's
Developer Documentation

See Release Notes

  • Bug fixes for general core bugs in 3.11.x will end 14 Nov 2022 (12 months plus 6 months extension).
  • Bug fixes for security issues in 3.11.x will end 13 Nov 2023 (18 months plus 12 months extension).
  • PHP version: minimum PHP 7.3.0 Note: minimum PHP version has increased since Moodle 3.10. PHP 7.4.x is supported too.

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 DateTime;
   6  use DateTimeZone;
   7  use DOMAttr;
   8  use DOMDocument;
   9  use DOMElement;
  10  use DOMNode;
  11  use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
  12  use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
  13  use PhpOffice\PhpSpreadsheet\Cell\DataType;
  14  use PhpOffice\PhpSpreadsheet\DefinedName;
  15  use PhpOffice\PhpSpreadsheet\Reader\Exception as ReaderException;
  16  use PhpOffice\PhpSpreadsheet\Reader\Ods\PageSettings;
  17  use PhpOffice\PhpSpreadsheet\Reader\Ods\Properties as DocumentProperties;
  18  use PhpOffice\PhpSpreadsheet\Reader\Security\XmlScanner;
  19  use PhpOffice\PhpSpreadsheet\RichText\RichText;
  20  use PhpOffice\PhpSpreadsheet\Settings;
  21  use PhpOffice\PhpSpreadsheet\Shared\Date;
  22  use PhpOffice\PhpSpreadsheet\Shared\File;
  23  use PhpOffice\PhpSpreadsheet\Spreadsheet;
  24  use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
  25  use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
  26  use XMLReader;
  27  use ZipArchive;
  28  
  29  class Ods extends BaseReader
  30  {
  31      /**
  32       * Create a new Ods Reader instance.
  33       */
  34      public function __construct()
  35      {
  36          parent::__construct();
  37          $this->securityScanner = XmlScanner::getInstance($this);
  38      }
  39  
  40      /**
  41       * Can the current IReader read the file?
  42       *
  43       * @param string $pFilename
  44       *
  45       * @return bool
  46       */
  47      public function canRead($pFilename)
  48      {
  49          File::assertFile($pFilename);
  50  
  51          $mimeType = 'UNKNOWN';
  52  
  53          // Load file
  54  
  55          $zip = new ZipArchive();
  56          if ($zip->open($pFilename) === true) {
  57              // check if it is an OOXML archive
  58              $stat = $zip->statName('mimetype');
  59              if ($stat && ($stat['size'] <= 255)) {
  60                  $mimeType = $zip->getFromName($stat['name']);
  61              } elseif ($zip->statName('META-INF/manifest.xml')) {
  62                  $xml = simplexml_load_string(
  63                      $this->securityScanner->scan($zip->getFromName('META-INF/manifest.xml')),
  64                      'SimpleXMLElement',
  65                      Settings::getLibXmlLoaderOptions()
  66                  );
  67                  $namespacesContent = $xml->getNamespaces(true);
  68                  if (isset($namespacesContent['manifest'])) {
  69                      $manifest = $xml->children($namespacesContent['manifest']);
  70                      foreach ($manifest as $manifestDataSet) {
  71                          $manifestAttributes = $manifestDataSet->attributes($namespacesContent['manifest']);
  72                          if ($manifestAttributes->{'full-path'} == '/') {
  73                              $mimeType = (string) $manifestAttributes->{'media-type'};
  74  
  75                              break;
  76                          }
  77                      }
  78                  }
  79              }
  80  
  81              $zip->close();
  82          }
  83  
  84          return $mimeType === 'application/vnd.oasis.opendocument.spreadsheet';
  85      }
  86  
  87      /**
  88       * Reads names of the worksheets from a file, without parsing the whole file to a PhpSpreadsheet object.
  89       *
  90       * @param string $pFilename
  91       *
  92       * @return string[]
  93       */
  94      public function listWorksheetNames($pFilename)
  95      {
  96          File::assertFile($pFilename);
  97  
  98          $zip = new ZipArchive();
  99          if ($zip->open($pFilename) !== true) {
 100              throw new ReaderException('Could not open ' . $pFilename . ' for reading! Error opening file.');
 101          }
 102  
 103          $worksheetNames = [];
 104  
 105          $xml = new XMLReader();
 106          $xml->xml(
 107              $this->securityScanner->scanFile('zip://' . realpath($pFilename) . '#content.xml'),
 108              null,
 109              Settings::getLibXmlLoaderOptions()
 110          );
 111          $xml->setParserProperty(2, true);
 112  
 113          // Step into the first level of content of the XML
 114          $xml->read();
 115          while ($xml->read()) {
 116              // Quickly jump through to the office:body node
 117              while ($xml->name !== 'office:body') {
 118                  if ($xml->isEmptyElement) {
 119                      $xml->read();
 120                  } else {
 121                      $xml->next();
 122                  }
 123              }
 124              // Now read each node until we find our first table:table node
 125              while ($xml->read()) {
 126                  if ($xml->name == 'table:table' && $xml->nodeType == XMLReader::ELEMENT) {
 127                      // Loop through each table:table node reading the table:name attribute for each worksheet name
 128                      do {
 129                          $worksheetNames[] = $xml->getAttribute('table:name');
 130                          $xml->next();
 131                      } while ($xml->name == 'table:table' && $xml->nodeType == XMLReader::ELEMENT);
 132                  }
 133              }
 134          }
 135  
 136          return $worksheetNames;
 137      }
 138  
 139      /**
 140       * Return worksheet info (Name, Last Column Letter, Last Column Index, Total Rows, Total Columns).
 141       *
 142       * @param string $pFilename
 143       *
 144       * @return array
 145       */
 146      public function listWorksheetInfo($pFilename)
 147      {
 148          File::assertFile($pFilename);
 149  
 150          $worksheetInfo = [];
 151  
 152          $zip = new ZipArchive();
 153          if ($zip->open($pFilename) !== true) {
 154              throw new ReaderException('Could not open ' . $pFilename . ' for reading! Error opening file.');
 155          }
 156  
 157          $xml = new XMLReader();
 158          $xml->xml(
 159              $this->securityScanner->scanFile('zip://' . realpath($pFilename) . '#content.xml'),
 160              null,
 161              Settings::getLibXmlLoaderOptions()
 162          );
 163          $xml->setParserProperty(2, true);
 164  
 165          // Step into the first level of content of the XML
 166          $xml->read();
 167          while ($xml->read()) {
 168              // Quickly jump through to the office:body node
 169              while ($xml->name !== 'office:body') {
 170                  if ($xml->isEmptyElement) {
 171                      $xml->read();
 172                  } else {
 173                      $xml->next();
 174                  }
 175              }
 176              // Now read each node until we find our first table:table node
 177              while ($xml->read()) {
 178                  if ($xml->name == 'table:table' && $xml->nodeType == XMLReader::ELEMENT) {
 179                      $worksheetNames[] = $xml->getAttribute('table:name');
 180  
 181                      $tmpInfo = [
 182                          'worksheetName' => $xml->getAttribute('table:name'),
 183                          'lastColumnLetter' => 'A',
 184                          'lastColumnIndex' => 0,
 185                          'totalRows' => 0,
 186                          'totalColumns' => 0,
 187                      ];
 188  
 189                      // Loop through each child node of the table:table element reading
 190                      $currCells = 0;
 191                      do {
 192                          $xml->read();
 193                          if ($xml->name == 'table:table-row' && $xml->nodeType == XMLReader::ELEMENT) {
 194                              $rowspan = $xml->getAttribute('table:number-rows-repeated');
 195                              $rowspan = empty($rowspan) ? 1 : $rowspan;
 196                              $tmpInfo['totalRows'] += $rowspan;
 197                              $tmpInfo['totalColumns'] = max($tmpInfo['totalColumns'], $currCells);
 198                              $currCells = 0;
 199                              // Step into the row
 200                              $xml->read();
 201                              do {
 202                                  $doread = true;
 203                                  if ($xml->name == 'table:table-cell' && $xml->nodeType == XMLReader::ELEMENT) {
 204                                      if (!$xml->isEmptyElement) {
 205                                          ++$currCells;
 206                                          $xml->next();
 207                                          $doread = false;
 208                                      }
 209                                  } elseif ($xml->name == 'table:covered-table-cell' && $xml->nodeType == XMLReader::ELEMENT) {
 210                                      $mergeSize = $xml->getAttribute('table:number-columns-repeated');
 211                                      $currCells += (int) $mergeSize;
 212                                  }
 213                                  if ($doread) {
 214                                      $xml->read();
 215                                  }
 216                              } while ($xml->name != 'table:table-row');
 217                          }
 218                      } while ($xml->name != 'table:table');
 219  
 220                      $tmpInfo['totalColumns'] = max($tmpInfo['totalColumns'], $currCells);
 221                      $tmpInfo['lastColumnIndex'] = $tmpInfo['totalColumns'] - 1;
 222                      $tmpInfo['lastColumnLetter'] = Coordinate::stringFromColumnIndex($tmpInfo['lastColumnIndex'] + 1);
 223                      $worksheetInfo[] = $tmpInfo;
 224                  }
 225              }
 226          }
 227  
 228          return $worksheetInfo;
 229      }
 230  
 231      /**
 232       * Loads PhpSpreadsheet from file.
 233       *
 234       * @param string $pFilename
 235       *
 236       * @return Spreadsheet
 237       */
 238      public function load($pFilename)
 239      {
 240          // Create new Spreadsheet
 241          $spreadsheet = new Spreadsheet();
 242  
 243          // Load into this instance
 244          return $this->loadIntoExisting($pFilename, $spreadsheet);
 245      }
 246  
 247      /**
 248       * Loads PhpSpreadsheet from file into PhpSpreadsheet instance.
 249       *
 250       * @param string $pFilename
 251       *
 252       * @return Spreadsheet
 253       */
 254      public function loadIntoExisting($pFilename, Spreadsheet $spreadsheet)
 255      {
 256          File::assertFile($pFilename);
 257  
 258          $timezoneObj = new DateTimeZone('Europe/London');
 259          $GMT = new DateTimeZone('UTC');
 260  
 261          $zip = new ZipArchive();
 262          if ($zip->open($pFilename) !== true) {
 263              throw new Exception("Could not open {$pFilename} for reading! Error opening file.");
 264          }
 265  
 266          // Meta
 267  
 268          $xml = @simplexml_load_string(
 269              $this->securityScanner->scan($zip->getFromName('meta.xml')),
 270              'SimpleXMLElement',
 271              Settings::getLibXmlLoaderOptions()
 272          );
 273          if ($xml === false) {
 274              throw new Exception('Unable to read data from {$pFilename}');
 275          }
 276  
 277          $namespacesMeta = $xml->getNamespaces(true);
 278  
 279          (new DocumentProperties($spreadsheet))->load($xml, $namespacesMeta);
 280  
 281          // Styles
 282  
 283          $dom = new DOMDocument('1.01', 'UTF-8');
 284          $dom->loadXML(
 285              $this->securityScanner->scan($zip->getFromName('styles.xml')),
 286              Settings::getLibXmlLoaderOptions()
 287          );
 288  
 289          $pageSettings = new PageSettings($dom);
 290  
 291          // Main Content
 292  
 293          $dom = new DOMDocument('1.01', 'UTF-8');
 294          $dom->loadXML(
 295              $this->securityScanner->scan($zip->getFromName('content.xml')),
 296              Settings::getLibXmlLoaderOptions()
 297          );
 298  
 299          $officeNs = $dom->lookupNamespaceUri('office');
 300          $tableNs = $dom->lookupNamespaceUri('table');
 301          $textNs = $dom->lookupNamespaceUri('text');
 302          $xlinkNs = $dom->lookupNamespaceUri('xlink');
 303  
 304          $pageSettings->readStyleCrossReferences($dom);
 305  
 306          // Content
 307  
 308          $spreadsheets = $dom->getElementsByTagNameNS($officeNs, 'body')
 309              ->item(0)
 310              ->getElementsByTagNameNS($officeNs, 'spreadsheet');
 311  
 312          foreach ($spreadsheets as $workbookData) {
 313              /** @var DOMElement $workbookData */
 314              $tables = $workbookData->getElementsByTagNameNS($tableNs, 'table');
 315  
 316              $worksheetID = 0;
 317              foreach ($tables as $worksheetDataSet) {
 318                  /** @var DOMElement $worksheetDataSet */
 319                  $worksheetName = $worksheetDataSet->getAttributeNS($tableNs, 'name');
 320  
 321                  // Check loadSheetsOnly
 322                  if (
 323                      isset($this->loadSheetsOnly)
 324                      && $worksheetName
 325                      && !in_array($worksheetName, $this->loadSheetsOnly)
 326                  ) {
 327                      continue;
 328                  }
 329  
 330                  $worksheetStyleName = $worksheetDataSet->getAttributeNS($tableNs, 'style-name');
 331  
 332                  // Create sheet
 333                  if ($worksheetID > 0) {
 334                      $spreadsheet->createSheet(); // First sheet is added by default
 335                  }
 336                  $spreadsheet->setActiveSheetIndex($worksheetID);
 337  
 338                  if ($worksheetName) {
 339                      // Use false for $updateFormulaCellReferences to prevent adjustment of worksheet references in
 340                      // formula cells... during the load, all formulae should be correct, and we're simply
 341                      // bringing the worksheet name in line with the formula, not the reverse
 342                      $spreadsheet->getActiveSheet()->setTitle((string) $worksheetName, false, false);
 343                  }
 344  
 345                  // Go through every child of table element
 346                  $rowID = 1;
 347                  foreach ($worksheetDataSet->childNodes as $childNode) {
 348                      /** @var DOMElement $childNode */
 349  
 350                      // Filter elements which are not under the "table" ns
 351                      if ($childNode->namespaceURI != $tableNs) {
 352                          continue;
 353                      }
 354  
 355                      $key = $childNode->nodeName;
 356  
 357                      // Remove ns from node name
 358                      if (strpos($key, ':') !== false) {
 359                          $keyChunks = explode(':', $key);
 360                          $key = array_pop($keyChunks);
 361                      }
 362  
 363                      switch ($key) {
 364                          case 'table-header-rows':
 365                              /// TODO :: Figure this out. This is only a partial implementation I guess.
 366                              //          ($rowData it's not used at all and I'm not sure that PHPExcel
 367                              //          has an API for this)
 368  
 369  //                            foreach ($rowData as $keyRowData => $cellData) {
 370  //                                $rowData = $cellData;
 371  //                                break;
 372  //                            }
 373                              break;
 374                          case 'table-row':
 375                              if ($childNode->hasAttributeNS($tableNs, 'number-rows-repeated')) {
 376                                  $rowRepeats = $childNode->getAttributeNS($tableNs, 'number-rows-repeated');
 377                              } else {
 378                                  $rowRepeats = 1;
 379                              }
 380  
 381                              $columnID = 'A';
 382                              foreach ($childNode->childNodes as $key => $cellData) {
 383                                  // @var \DOMElement $cellData
 384  
 385                                  if ($this->getReadFilter() !== null) {
 386                                      if (!$this->getReadFilter()->readCell($columnID, $rowID, $worksheetName)) {
 387                                          ++$columnID;
 388  
 389                                          continue;
 390                                      }
 391                                  }
 392  
 393                                  // Initialize variables
 394                                  $formatting = $hyperlink = null;
 395                                  $hasCalculatedValue = false;
 396                                  $cellDataFormula = '';
 397  
 398                                  if ($cellData->hasAttributeNS($tableNs, 'formula')) {
 399                                      $cellDataFormula = $cellData->getAttributeNS($tableNs, 'formula');
 400                                      $hasCalculatedValue = true;
 401                                  }
 402  
 403                                  // Annotations
 404                                  $annotation = $cellData->getElementsByTagNameNS($officeNs, 'annotation');
 405  
 406                                  if ($annotation->length > 0) {
 407                                      $textNode = $annotation->item(0)->getElementsByTagNameNS($textNs, 'p');
 408  
 409                                      if ($textNode->length > 0) {
 410                                          $text = $this->scanElementForText($textNode->item(0));
 411  
 412                                          $spreadsheet->getActiveSheet()
 413                                              ->getComment($columnID . $rowID)
 414                                              ->setText($this->parseRichText($text));
 415  //                                                                    ->setAuthor( $author )
 416                                      }
 417                                  }
 418  
 419                                  // Content
 420  
 421                                  /** @var DOMElement[] $paragraphs */
 422                                  $paragraphs = [];
 423  
 424                                  foreach ($cellData->childNodes as $item) {
 425                                      /** @var DOMElement $item */
 426  
 427                                      // Filter text:p elements
 428                                      if ($item->nodeName == 'text:p') {
 429                                          $paragraphs[] = $item;
 430                                      }
 431                                  }
 432  
 433                                  if (count($paragraphs) > 0) {
 434                                      // Consolidate if there are multiple p records (maybe with spans as well)
 435                                      $dataArray = [];
 436  
 437                                      // Text can have multiple text:p and within those, multiple text:span.
 438                                      // text:p newlines, but text:span does not.
 439                                      // Also, here we assume there is no text data is span fields are specified, since
 440                                      // we have no way of knowing proper positioning anyway.
 441  
 442                                      foreach ($paragraphs as $pData) {
 443                                          $dataArray[] = $this->scanElementForText($pData);
 444                                      }
 445                                      $allCellDataText = implode("\n", $dataArray);
 446  
 447                                      $type = $cellData->getAttributeNS($officeNs, 'value-type');
 448  
 449                                      switch ($type) {
 450                                          case 'string':
 451                                              $type = DataType::TYPE_STRING;
 452                                              $dataValue = $allCellDataText;
 453  
 454                                              foreach ($paragraphs as $paragraph) {
 455                                                  $link = $paragraph->getElementsByTagNameNS($textNs, 'a');
 456                                                  if ($link->length > 0) {
 457                                                      $hyperlink = $link->item(0)->getAttributeNS($xlinkNs, 'href');
 458                                                  }
 459                                              }
 460  
 461                                              break;
 462                                          case 'boolean':
 463                                              $type = DataType::TYPE_BOOL;
 464                                              $dataValue = ($allCellDataText == 'TRUE') ? true : false;
 465  
 466                                              break;
 467                                          case 'percentage':
 468                                              $type = DataType::TYPE_NUMERIC;
 469                                              $dataValue = (float) $cellData->getAttributeNS($officeNs, 'value');
 470  
 471                                              // percentage should always be float
 472                                              //if (floor($dataValue) == $dataValue) {
 473                                              //    $dataValue = (int) $dataValue;
 474                                              //}
 475                                              $formatting = NumberFormat::FORMAT_PERCENTAGE_00;
 476  
 477                                              break;
 478                                          case 'currency':
 479                                              $type = DataType::TYPE_NUMERIC;
 480                                              $dataValue = (float) $cellData->getAttributeNS($officeNs, 'value');
 481  
 482                                              if (floor($dataValue) == $dataValue) {
 483                                                  $dataValue = (int) $dataValue;
 484                                              }
 485                                              $formatting = NumberFormat::FORMAT_CURRENCY_USD_SIMPLE;
 486  
 487                                              break;
 488                                          case 'float':
 489                                              $type = DataType::TYPE_NUMERIC;
 490                                              $dataValue = (float) $cellData->getAttributeNS($officeNs, 'value');
 491  
 492                                              if (floor($dataValue) == $dataValue) {
 493                                                  if ($dataValue == (int) $dataValue) {
 494                                                      $dataValue = (int) $dataValue;
 495                                                  }
 496                                              }
 497  
 498                                              break;
 499                                          case 'date':
 500                                              $type = DataType::TYPE_NUMERIC;
 501                                              $value = $cellData->getAttributeNS($officeNs, 'date-value');
 502  
 503                                              $dateObj = new DateTime($value, $GMT);
 504                                              $dateObj->setTimeZone($timezoneObj);
 505                                              [$year, $month, $day, $hour, $minute, $second] = explode(
 506                                                  ' ',
 507                                                  $dateObj->format('Y m d H i s')
 508                                              );
 509  
 510                                              $dataValue = Date::formattedPHPToExcel(
 511                                                  (int) $year,
 512                                                  (int) $month,
 513                                                  (int) $day,
 514                                                  (int) $hour,
 515                                                  (int) $minute,
 516                                                  (int) $second
 517                                              );
 518  
 519                                              if ($dataValue != floor($dataValue)) {
 520                                                  $formatting = NumberFormat::FORMAT_DATE_XLSX15
 521                                                      . ' '
 522                                                      . NumberFormat::FORMAT_DATE_TIME4;
 523                                              } else {
 524                                                  $formatting = NumberFormat::FORMAT_DATE_XLSX15;
 525                                              }
 526  
 527                                              break;
 528                                          case 'time':
 529                                              $type = DataType::TYPE_NUMERIC;
 530  
 531                                              $timeValue = $cellData->getAttributeNS($officeNs, 'time-value');
 532  
 533                                              $dataValue = Date::PHPToExcel(
 534                                                  strtotime(
 535                                                      '01-01-1970 ' . implode(':', sscanf($timeValue, 'PT%dH%dM%dS'))
 536                                                  )
 537                                              );
 538                                              $formatting = NumberFormat::FORMAT_DATE_TIME4;
 539  
 540                                              break;
 541                                          default:
 542                                              $dataValue = null;
 543                                      }
 544                                  } else {
 545                                      $type = DataType::TYPE_NULL;
 546                                      $dataValue = null;
 547                                  }
 548  
 549                                  if ($hasCalculatedValue) {
 550                                      $type = DataType::TYPE_FORMULA;
 551                                      $cellDataFormula = substr($cellDataFormula, strpos($cellDataFormula, ':=') + 1);
 552                                      $cellDataFormula = $this->convertToExcelFormulaValue($cellDataFormula);
 553                                  }
 554  
 555                                  if ($cellData->hasAttributeNS($tableNs, 'number-columns-repeated')) {
 556                                      $colRepeats = (int) $cellData->getAttributeNS($tableNs, 'number-columns-repeated');
 557                                  } else {
 558                                      $colRepeats = 1;
 559                                  }
 560  
 561                                  if ($type !== null) {
 562                                      for ($i = 0; $i < $colRepeats; ++$i) {
 563                                          if ($i > 0) {
 564                                              ++$columnID;
 565                                          }
 566  
 567                                          if ($type !== DataType::TYPE_NULL) {
 568                                              for ($rowAdjust = 0; $rowAdjust < $rowRepeats; ++$rowAdjust) {
 569                                                  $rID = $rowID + $rowAdjust;
 570  
 571                                                  $cell = $spreadsheet->getActiveSheet()
 572                                                      ->getCell($columnID . $rID);
 573  
 574                                                  // Set value
 575                                                  if ($hasCalculatedValue) {
 576                                                      $cell->setValueExplicit($cellDataFormula, $type);
 577                                                  } else {
 578                                                      $cell->setValueExplicit($dataValue, $type);
 579                                                  }
 580  
 581                                                  if ($hasCalculatedValue) {
 582                                                      $cell->setCalculatedValue($dataValue);
 583                                                  }
 584  
 585                                                  // Set other properties
 586                                                  if ($formatting !== null) {
 587                                                      $spreadsheet->getActiveSheet()
 588                                                          ->getStyle($columnID . $rID)
 589                                                          ->getNumberFormat()
 590                                                          ->setFormatCode($formatting);
 591                                                  } else {
 592                                                      $spreadsheet->getActiveSheet()
 593                                                          ->getStyle($columnID . $rID)
 594                                                          ->getNumberFormat()
 595                                                          ->setFormatCode(NumberFormat::FORMAT_GENERAL);
 596                                                  }
 597  
 598                                                  if ($hyperlink !== null) {
 599                                                      $cell->getHyperlink()
 600                                                          ->setUrl($hyperlink);
 601                                                  }
 602                                              }
 603                                          }
 604                                      }
 605                                  }
 606  
 607                                  // Merged cells
 608                                  if (
 609                                      $cellData->hasAttributeNS($tableNs, 'number-columns-spanned')
 610                                      || $cellData->hasAttributeNS($tableNs, 'number-rows-spanned')
 611                                  ) {
 612                                      if (($type !== DataType::TYPE_NULL) || (!$this->readDataOnly)) {
 613                                          $columnTo = $columnID;
 614  
 615                                          if ($cellData->hasAttributeNS($tableNs, 'number-columns-spanned')) {
 616                                              $columnIndex = Coordinate::columnIndexFromString($columnID);
 617                                              $columnIndex += (int) $cellData->getAttributeNS($tableNs, 'number-columns-spanned');
 618                                              $columnIndex -= 2;
 619  
 620                                              $columnTo = Coordinate::stringFromColumnIndex($columnIndex + 1);
 621                                          }
 622  
 623                                          $rowTo = $rowID;
 624  
 625                                          if ($cellData->hasAttributeNS($tableNs, 'number-rows-spanned')) {
 626                                              $rowTo = $rowTo + (int) $cellData->getAttributeNS($tableNs, 'number-rows-spanned') - 1;
 627                                          }
 628  
 629                                          $cellRange = $columnID . $rowID . ':' . $columnTo . $rowTo;
 630                                          $spreadsheet->getActiveSheet()->mergeCells($cellRange);
 631                                      }
 632                                  }
 633  
 634                                  ++$columnID;
 635                              }
 636                              $rowID += $rowRepeats;
 637  
 638                              break;
 639                      }
 640                  }
 641                  $pageSettings->setPrintSettingsForWorksheet($spreadsheet->getActiveSheet(), $worksheetStyleName);
 642                  ++$worksheetID;
 643              }
 644  
 645              $this->readDefinedRanges($spreadsheet, $workbookData, $tableNs);
 646              $this->readDefinedExpressions($spreadsheet, $workbookData, $tableNs);
 647          }
 648          $spreadsheet->setActiveSheetIndex(0);
 649          // Return
 650          return $spreadsheet;
 651      }
 652  
 653      /**
 654       * Recursively scan element.
 655       *
 656       * @return string
 657       */
 658      protected function scanElementForText(DOMNode $element)
 659      {
 660          $str = '';
 661          foreach ($element->childNodes as $child) {
 662              /** @var DOMNode $child */
 663              if ($child->nodeType == XML_TEXT_NODE) {
 664                  $str .= $child->nodeValue;
 665              } elseif ($child->nodeType == XML_ELEMENT_NODE && $child->nodeName == 'text:s') {
 666                  // It's a space
 667  
 668                  // Multiple spaces?
 669                  /** @var DOMAttr $cAttr */
 670                  $cAttr = $child->attributes->getNamedItem('c');
 671                  if ($cAttr) {
 672                      $multiplier = (int) $cAttr->nodeValue;
 673                  } else {
 674                      $multiplier = 1;
 675                  }
 676  
 677                  $str .= str_repeat(' ', $multiplier);
 678              }
 679  
 680              if ($child->hasChildNodes()) {
 681                  $str .= $this->scanElementForText($child);
 682              }
 683          }
 684  
 685          return $str;
 686      }
 687  
 688      /**
 689       * @param string $is
 690       *
 691       * @return RichText
 692       */
 693      private function parseRichText($is)
 694      {
 695          $value = new RichText();
 696          $value->createText($is);
 697  
 698          return $value;
 699      }
 700  
 701      private function convertToExcelAddressValue(string $openOfficeAddress): string
 702      {
 703          $excelAddress = $openOfficeAddress;
 704  
 705          // Cell range 3-d reference
 706          // As we don't support 3-d ranges, we're just going to take a quick and dirty approach
 707          //  and assume that the second worksheet reference is the same as the first
 708          $excelAddress = preg_replace('/\$?([^\.]+)\.([^\.]+):\$?([^\.]+)\.([^\.]+)/miu', '$1!$2:$4', $excelAddress);
 709          // Cell range reference in another sheet
 710          $excelAddress = preg_replace('/\$?([^\.]+)\.([^\.]+):\.([^\.]+)/miu', '$1!$2:$3', $excelAddress);
 711          // Cell reference in another sheet
 712          $excelAddress = preg_replace('/\$?([^\.]+)\.([^\.]+)/miu', '$1!$2', $excelAddress);
 713          // Cell range reference
 714          $excelAddress = preg_replace('/\.([^\.]+):\.([^\.]+)/miu', '$1:$2', $excelAddress);
 715          // Simple cell reference
 716          $excelAddress = preg_replace('/\.([^\.]+)/miu', '$1', $excelAddress);
 717  
 718          return $excelAddress;
 719      }
 720  
 721      private function convertToExcelFormulaValue(string $openOfficeFormula): string
 722      {
 723          $temp = explode('"', $openOfficeFormula);
 724          $tKey = false;
 725          foreach ($temp as &$value) {
 726              // Only replace in alternate array entries (i.e. non-quoted blocks)
 727              if ($tKey = !$tKey) {
 728                  // Cell range reference in another sheet
 729                  $value = preg_replace('/\[\$?([^\.]+)\.([^\.]+):\.([^\.]+)\]/miu', '$1!$2:$3', $value);
 730                  // Cell reference in another sheet
 731                  $value = preg_replace('/\[\$?([^\.]+)\.([^\.]+)\]/miu', '$1!$2', $value);
 732                  // Cell range reference
 733                  $value = preg_replace('/\[\.([^\.]+):\.([^\.]+)\]/miu', '$1:$2', $value);
 734                  // Simple cell reference
 735                  $value = preg_replace('/\[\.([^\.]+)\]/miu', '$1', $value);
 736  
 737                  $value = Calculation::translateSeparator(';', ',', $value, $inBraces);
 738              }
 739          }
 740  
 741          // Then rebuild the formula string
 742          $excelFormula = implode('"', $temp);
 743  
 744          return $excelFormula;
 745      }
 746  
 747      /**
 748       * Read any Named Ranges that are defined in this spreadsheet.
 749       */
 750      private function readDefinedRanges(Spreadsheet $spreadsheet, DOMElement $workbookData, string $tableNs): void
 751      {
 752          $namedRanges = $workbookData->getElementsByTagNameNS($tableNs, 'named-range');
 753          foreach ($namedRanges as $definedNameElement) {
 754              $definedName = $definedNameElement->getAttributeNS($tableNs, 'name');
 755              $baseAddress = $definedNameElement->getAttributeNS($tableNs, 'base-cell-address');
 756              $range = $definedNameElement->getAttributeNS($tableNs, 'cell-range-address');
 757  
 758              $baseAddress = $this->convertToExcelAddressValue($baseAddress);
 759              $range = $this->convertToExcelAddressValue($range);
 760  
 761              $this->addDefinedName($spreadsheet, $baseAddress, $definedName, $range);
 762          }
 763      }
 764  
 765      /**
 766       * Read any Named Formulae that are defined in this spreadsheet.
 767       */
 768      private function readDefinedExpressions(Spreadsheet $spreadsheet, DOMElement $workbookData, string $tableNs): void
 769      {
 770          $namedExpressions = $workbookData->getElementsByTagNameNS($tableNs, 'named-expression');
 771          foreach ($namedExpressions as $definedNameElement) {
 772              $definedName = $definedNameElement->getAttributeNS($tableNs, 'name');
 773              $baseAddress = $definedNameElement->getAttributeNS($tableNs, 'base-cell-address');
 774              $expression = $definedNameElement->getAttributeNS($tableNs, 'expression');
 775  
 776              $baseAddress = $this->convertToExcelAddressValue($baseAddress);
 777              $expression = $this->convertToExcelFormulaValue($expression);
 778  
 779              $this->addDefinedName($spreadsheet, $baseAddress, $definedName, $expression);
 780          }
 781      }
 782  
 783      /**
 784       * Assess scope and store the Defined Name.
 785       */
 786      private function addDefinedName(Spreadsheet $spreadsheet, string $baseAddress, string $definedName, string $value): void
 787      {
 788          [$sheetReference] = Worksheet::extractSheetTitle($baseAddress, true);
 789          $worksheet = $spreadsheet->getSheetByName($sheetReference);
 790          // Worksheet might still be null if we're only loading selected sheets rather than the full spreadsheet
 791          if ($worksheet !== null) {
 792              $spreadsheet->addDefinedName(DefinedName::createInstance((string) $definedName, $worksheet, $value));
 793          }
 794      }
 795  }