[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\AddressHelper;
   6  use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
   7  use PhpOffice\PhpSpreadsheet\Cell\DataType;
   8  use PhpOffice\PhpSpreadsheet\DefinedName;
   9  use PhpOffice\PhpSpreadsheet\Document\Properties;
  10  use PhpOffice\PhpSpreadsheet\Reader\Security\XmlScanner;
  11  use PhpOffice\PhpSpreadsheet\Reader\Xml\PageSettings;
  12  use PhpOffice\PhpSpreadsheet\RichText\RichText;
  13  use PhpOffice\PhpSpreadsheet\Settings;
  14  use PhpOffice\PhpSpreadsheet\Shared\Date;
  15  use PhpOffice\PhpSpreadsheet\Shared\File;
  16  use PhpOffice\PhpSpreadsheet\Shared\StringHelper;
  17  use PhpOffice\PhpSpreadsheet\Spreadsheet;
  18  use PhpOffice\PhpSpreadsheet\Style\Alignment;
  19  use PhpOffice\PhpSpreadsheet\Style\Border;
  20  use PhpOffice\PhpSpreadsheet\Style\Borders;
  21  use PhpOffice\PhpSpreadsheet\Style\Fill;
  22  use PhpOffice\PhpSpreadsheet\Style\Font;
  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      /**
  31       * Formats.
  32       *
  33       * @var array
  34       */
  35      protected $styles = [];
  36  
  37      /**
  38       * Create a new Excel2003XML Reader instance.
  39       */
  40      public function __construct()
  41      {
  42          parent::__construct();
  43          $this->securityScanner = XmlScanner::getInstance($this);
  44      }
  45  
  46      private $fileContents = '';
  47  
  48      private static $mappings = [
  49          'borderStyle' => [
  50              '1continuous' => Border::BORDER_THIN,
  51              '1dash' => Border::BORDER_DASHED,
  52              '1dashdot' => Border::BORDER_DASHDOT,
  53              '1dashdotdot' => Border::BORDER_DASHDOTDOT,
  54              '1dot' => Border::BORDER_DOTTED,
  55              '1double' => Border::BORDER_DOUBLE,
  56              '2continuous' => Border::BORDER_MEDIUM,
  57              '2dash' => Border::BORDER_MEDIUMDASHED,
  58              '2dashdot' => Border::BORDER_MEDIUMDASHDOT,
  59              '2dashdotdot' => Border::BORDER_MEDIUMDASHDOTDOT,
  60              '2dot' => Border::BORDER_DOTTED,
  61              '2double' => Border::BORDER_DOUBLE,
  62              '3continuous' => Border::BORDER_THICK,
  63              '3dash' => Border::BORDER_MEDIUMDASHED,
  64              '3dashdot' => Border::BORDER_MEDIUMDASHDOT,
  65              '3dashdotdot' => Border::BORDER_MEDIUMDASHDOTDOT,
  66              '3dot' => Border::BORDER_DOTTED,
  67              '3double' => Border::BORDER_DOUBLE,
  68          ],
  69          'fillType' => [
  70              'solid' => Fill::FILL_SOLID,
  71              'gray75' => Fill::FILL_PATTERN_DARKGRAY,
  72              'gray50' => Fill::FILL_PATTERN_MEDIUMGRAY,
  73              'gray25' => Fill::FILL_PATTERN_LIGHTGRAY,
  74              'gray125' => Fill::FILL_PATTERN_GRAY125,
  75              'gray0625' => Fill::FILL_PATTERN_GRAY0625,
  76              'horzstripe' => Fill::FILL_PATTERN_DARKHORIZONTAL, // horizontal stripe
  77              'vertstripe' => Fill::FILL_PATTERN_DARKVERTICAL, // vertical stripe
  78              'reversediagstripe' => Fill::FILL_PATTERN_DARKUP, // reverse diagonal stripe
  79              'diagstripe' => Fill::FILL_PATTERN_DARKDOWN, // diagonal stripe
  80              'diagcross' => Fill::FILL_PATTERN_DARKGRID, // diagoanl crosshatch
  81              'thickdiagcross' => Fill::FILL_PATTERN_DARKTRELLIS, // thick diagonal crosshatch
  82              'thinhorzstripe' => Fill::FILL_PATTERN_LIGHTHORIZONTAL,
  83              'thinvertstripe' => Fill::FILL_PATTERN_LIGHTVERTICAL,
  84              'thinreversediagstripe' => Fill::FILL_PATTERN_LIGHTUP,
  85              'thindiagstripe' => Fill::FILL_PATTERN_LIGHTDOWN,
  86              'thinhorzcross' => Fill::FILL_PATTERN_LIGHTGRID, // thin horizontal crosshatch
  87              'thindiagcross' => Fill::FILL_PATTERN_LIGHTTRELLIS, // thin diagonal crosshatch
  88          ],
  89      ];
  90  
  91      public static function xmlMappings(): array
  92      {
  93          return self::$mappings;
  94      }
  95  
  96      /**
  97       * Can the current IReader read the file?
  98       *
  99       * @param string $pFilename
 100       *
 101       * @return bool
 102       */
 103      public function canRead($pFilename)
 104      {
 105          //    Office                    xmlns:o="urn:schemas-microsoft-com:office:office"
 106          //    Excel                    xmlns:x="urn:schemas-microsoft-com:office:excel"
 107          //    XML Spreadsheet            xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
 108          //    Spreadsheet component    xmlns:c="urn:schemas-microsoft-com:office:component:spreadsheet"
 109          //    XML schema                 xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882"
 110          //    XML data type            xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882"
 111          //    MS-persist recordset    xmlns:rs="urn:schemas-microsoft-com:rowset"
 112          //    Rowset                    xmlns:z="#RowsetSchema"
 113          //
 114  
 115          $signature = [
 116              '<?xml version="1.0"',
 117              '<?mso-application progid="Excel.Sheet"?>',
 118          ];
 119  
 120          // Open file
 121          $data = file_get_contents($pFilename);
 122  
 123          // Why?
 124          //$data = str_replace("'", '"', $data); // fix headers with single quote
 125  
 126          $valid = true;
 127          foreach ($signature as $match) {
 128              // every part of the signature must be present
 129              if (strpos($data, $match) === false) {
 130                  $valid = false;
 131  
 132                  break;
 133              }
 134          }
 135  
 136          //    Retrieve charset encoding
 137          if (preg_match('/<?xml.*encoding=[\'"](.*?)[\'"].*?>/m', $data, $matches)) {
 138              $charSet = strtoupper($matches[1]);
 139              if (1 == preg_match('/^ISO-8859-\d[\dL]?$/i', $charSet)) {
 140                  $data = StringHelper::convertEncoding($data, 'UTF-8', $charSet);
 141                  $data = preg_replace('/(<?xml.*encoding=[\'"]).*?([\'"].*?>)/um', '$1' . 'UTF-8' . '$2', $data, 1);
 142              }
 143          }
 144          $this->fileContents = $data;
 145  
 146          return $valid;
 147      }
 148  
 149      /**
 150       * Check if the file is a valid SimpleXML.
 151       *
 152       * @param string $pFilename
 153       *
 154       * @return false|SimpleXMLElement
 155       */
 156      public function trySimpleXMLLoadString($pFilename)
 157      {
 158          try {
 159              $xml = simplexml_load_string(
 160                  $this->securityScanner->scan($this->fileContents ?: file_get_contents($pFilename)),
 161                  'SimpleXMLElement',
 162                  Settings::getLibXmlLoaderOptions()
 163              );
 164          } catch (\Exception $e) {
 165              throw new Exception('Cannot load invalid XML file: ' . $pFilename, 0, $e);
 166          }
 167          $this->fileContents = '';
 168  
 169          return $xml;
 170      }
 171  
 172      /**
 173       * Reads names of the worksheets from a file, without parsing the whole file to a Spreadsheet object.
 174       *
 175       * @param string $pFilename
 176       *
 177       * @return array
 178       */
 179      public function listWorksheetNames($pFilename)
 180      {
 181          File::assertFile($pFilename);
 182          if (!$this->canRead($pFilename)) {
 183              throw new Exception($pFilename . ' is an Invalid Spreadsheet file.');
 184          }
 185  
 186          $worksheetNames = [];
 187  
 188          $xml = $this->trySimpleXMLLoadString($pFilename);
 189  
 190          $namespaces = $xml->getNamespaces(true);
 191  
 192          $xml_ss = $xml->children($namespaces['ss']);
 193          foreach ($xml_ss->Worksheet as $worksheet) {
 194              $worksheet_ss = $worksheet->attributes($namespaces['ss']);
 195              $worksheetNames[] = (string) $worksheet_ss['Name'];
 196          }
 197  
 198          return $worksheetNames;
 199      }
 200  
 201      /**
 202       * Return worksheet info (Name, Last Column Letter, Last Column Index, Total Rows, Total Columns).
 203       *
 204       * @param string $pFilename
 205       *
 206       * @return array
 207       */
 208      public function listWorksheetInfo($pFilename)
 209      {
 210          File::assertFile($pFilename);
 211          if (!$this->canRead($pFilename)) {
 212              throw new Exception($pFilename . ' is an Invalid Spreadsheet file.');
 213          }
 214  
 215          $worksheetInfo = [];
 216  
 217          $xml = $this->trySimpleXMLLoadString($pFilename);
 218  
 219          $namespaces = $xml->getNamespaces(true);
 220  
 221          $worksheetID = 1;
 222          $xml_ss = $xml->children($namespaces['ss']);
 223          foreach ($xml_ss->Worksheet as $worksheet) {
 224              $worksheet_ss = $worksheet->attributes($namespaces['ss']);
 225  
 226              $tmpInfo = [];
 227              $tmpInfo['worksheetName'] = '';
 228              $tmpInfo['lastColumnLetter'] = 'A';
 229              $tmpInfo['lastColumnIndex'] = 0;
 230              $tmpInfo['totalRows'] = 0;
 231              $tmpInfo['totalColumns'] = 0;
 232  
 233              $tmpInfo['worksheetName'] = "Worksheet_{$worksheetID}";
 234              if (isset($worksheet_ss['Name'])) {
 235                  $tmpInfo['worksheetName'] = (string) $worksheet_ss['Name'];
 236              }
 237  
 238              if (isset($worksheet->Table->Row)) {
 239                  $rowIndex = 0;
 240  
 241                  foreach ($worksheet->Table->Row as $rowData) {
 242                      $columnIndex = 0;
 243                      $rowHasData = false;
 244  
 245                      foreach ($rowData->Cell as $cell) {
 246                          if (isset($cell->Data)) {
 247                              $tmpInfo['lastColumnIndex'] = max($tmpInfo['lastColumnIndex'], $columnIndex);
 248                              $rowHasData = true;
 249                          }
 250  
 251                          ++$columnIndex;
 252                      }
 253  
 254                      ++$rowIndex;
 255  
 256                      if ($rowHasData) {
 257                          $tmpInfo['totalRows'] = max($tmpInfo['totalRows'], $rowIndex);
 258                      }
 259                  }
 260              }
 261  
 262              $tmpInfo['lastColumnLetter'] = Coordinate::stringFromColumnIndex($tmpInfo['lastColumnIndex'] + 1);
 263              $tmpInfo['totalColumns'] = $tmpInfo['lastColumnIndex'] + 1;
 264  
 265              $worksheetInfo[] = $tmpInfo;
 266              ++$worksheetID;
 267          }
 268  
 269          return $worksheetInfo;
 270      }
 271  
 272      /**
 273       * Loads Spreadsheet from file.
 274       *
 275       * @param string $pFilename
 276       *
 277       * @return Spreadsheet
 278       */
 279      public function load($pFilename)
 280      {
 281          // Create new Spreadsheet
 282          $spreadsheet = new Spreadsheet();
 283          $spreadsheet->removeSheetByIndex(0);
 284  
 285          // Load into this instance
 286          return $this->loadIntoExisting($pFilename, $spreadsheet);
 287      }
 288  
 289      private static function identifyFixedStyleValue($styleList, &$styleAttributeValue)
 290      {
 291          $returnValue = false;
 292          $styleAttributeValue = strtolower($styleAttributeValue);
 293          foreach ($styleList as $style) {
 294              if ($styleAttributeValue == strtolower($style)) {
 295                  $styleAttributeValue = $style;
 296                  $returnValue = true;
 297  
 298                  break;
 299              }
 300          }
 301  
 302          return $returnValue;
 303      }
 304  
 305      protected static function hex2str($hex)
 306      {
 307          return mb_chr((int) hexdec($hex[1]), 'UTF-8');
 308      }
 309  
 310      /**
 311       * Loads from file into Spreadsheet instance.
 312       *
 313       * @param string $pFilename
 314       *
 315       * @return Spreadsheet
 316       */
 317      public function loadIntoExisting($pFilename, Spreadsheet $spreadsheet)
 318      {
 319          File::assertFile($pFilename);
 320          if (!$this->canRead($pFilename)) {
 321              throw new Exception($pFilename . ' is an Invalid Spreadsheet file.');
 322          }
 323  
 324          $xml = $this->trySimpleXMLLoadString($pFilename);
 325  
 326          $namespaces = $xml->getNamespaces(true);
 327  
 328          $docProps = $spreadsheet->getProperties();
 329          if (isset($xml->DocumentProperties[0])) {
 330              foreach ($xml->DocumentProperties[0] as $propertyName => $propertyValue) {
 331                  $stringValue = (string) $propertyValue;
 332                  switch ($propertyName) {
 333                      case 'Title':
 334                          $docProps->setTitle($stringValue);
 335  
 336                          break;
 337                      case 'Subject':
 338                          $docProps->setSubject($stringValue);
 339  
 340                          break;
 341                      case 'Author':
 342                          $docProps->setCreator($stringValue);
 343  
 344                          break;
 345                      case 'Created':
 346                          $creationDate = strtotime($stringValue);
 347                          $docProps->setCreated($creationDate);
 348  
 349                          break;
 350                      case 'LastAuthor':
 351                          $docProps->setLastModifiedBy($stringValue);
 352  
 353                          break;
 354                      case 'LastSaved':
 355                          $lastSaveDate = strtotime($stringValue);
 356                          $docProps->setModified($lastSaveDate);
 357  
 358                          break;
 359                      case 'Company':
 360                          $docProps->setCompany($stringValue);
 361  
 362                          break;
 363                      case 'Category':
 364                          $docProps->setCategory($stringValue);
 365  
 366                          break;
 367                      case 'Manager':
 368                          $docProps->setManager($stringValue);
 369  
 370                          break;
 371                      case 'Keywords':
 372                          $docProps->setKeywords($stringValue);
 373  
 374                          break;
 375                      case 'Description':
 376                          $docProps->setDescription($stringValue);
 377  
 378                          break;
 379                  }
 380              }
 381          }
 382          if (isset($xml->CustomDocumentProperties)) {
 383              foreach ($xml->CustomDocumentProperties[0] as $propertyName => $propertyValue) {
 384                  $propertyAttributes = $propertyValue->attributes($namespaces['dt']);
 385                  $propertyName = preg_replace_callback('/_x([0-9a-f]{4})_/i', ['self', 'hex2str'], $propertyName);
 386                  $propertyType = Properties::PROPERTY_TYPE_UNKNOWN;
 387                  switch ((string) $propertyAttributes) {
 388                      case 'string':
 389                          $propertyType = Properties::PROPERTY_TYPE_STRING;
 390                          $propertyValue = trim($propertyValue);
 391  
 392                          break;
 393                      case 'boolean':
 394                          $propertyType = Properties::PROPERTY_TYPE_BOOLEAN;
 395                          $propertyValue = (bool) $propertyValue;
 396  
 397                          break;
 398                      case 'integer':
 399                          $propertyType = Properties::PROPERTY_TYPE_INTEGER;
 400                          $propertyValue = (int) $propertyValue;
 401  
 402                          break;
 403                      case 'float':
 404                          $propertyType = Properties::PROPERTY_TYPE_FLOAT;
 405                          $propertyValue = (float) $propertyValue;
 406  
 407                          break;
 408                      case 'dateTime.tz':
 409                          $propertyType = Properties::PROPERTY_TYPE_DATE;
 410                          $propertyValue = strtotime(trim($propertyValue));
 411  
 412                          break;
 413                  }
 414                  $docProps->setCustomProperty($propertyName, $propertyValue, $propertyType);
 415              }
 416          }
 417  
 418          $this->parseStyles($xml, $namespaces);
 419  
 420          $worksheetID = 0;
 421          $xml_ss = $xml->children($namespaces['ss']);
 422  
 423          foreach ($xml_ss->Worksheet as $worksheet) {
 424              $worksheet_ss = $worksheet->attributes($namespaces['ss']);
 425  
 426              if (
 427                  (isset($this->loadSheetsOnly)) && (isset($worksheet_ss['Name'])) &&
 428                  (!in_array($worksheet_ss['Name'], $this->loadSheetsOnly))
 429              ) {
 430                  continue;
 431              }
 432  
 433              // Create new Worksheet
 434              $spreadsheet->createSheet();
 435              $spreadsheet->setActiveSheetIndex($worksheetID);
 436              if (isset($worksheet_ss['Name'])) {
 437                  $worksheetName = (string) $worksheet_ss['Name'];
 438                  //    Use false for $updateFormulaCellReferences to prevent adjustment of worksheet references in
 439                  //        formula cells... during the load, all formulae should be correct, and we're simply bringing
 440                  //        the worksheet name in line with the formula, not the reverse
 441                  $spreadsheet->getActiveSheet()->setTitle($worksheetName, false, false);
 442              }
 443  
 444              // locally scoped defined names
 445              if (isset($worksheet->Names[0])) {
 446                  foreach ($worksheet->Names[0] as $definedName) {
 447                      $definedName_ss = $definedName->attributes($namespaces['ss']);
 448                      $name = (string) $definedName_ss['Name'];
 449                      $definedValue = (string) $definedName_ss['RefersTo'];
 450                      $convertedValue = AddressHelper::convertFormulaToA1($definedValue);
 451                      if ($convertedValue[0] === '=') {
 452                          $convertedValue = substr($convertedValue, 1);
 453                      }
 454                      $spreadsheet->addDefinedName(DefinedName::createInstance($name, $spreadsheet->getActiveSheet(), $convertedValue, true));
 455                  }
 456              }
 457  
 458              $columnID = 'A';
 459              if (isset($worksheet->Table->Column)) {
 460                  foreach ($worksheet->Table->Column as $columnData) {
 461                      $columnData_ss = $columnData->attributes($namespaces['ss']);
 462                      if (isset($columnData_ss['Index'])) {
 463                          $columnID = Coordinate::stringFromColumnIndex((int) $columnData_ss['Index']);
 464                      }
 465                      if (isset($columnData_ss['Width'])) {
 466                          $columnWidth = $columnData_ss['Width'];
 467                          $spreadsheet->getActiveSheet()->getColumnDimension($columnID)->setWidth($columnWidth / 5.4);
 468                      }
 469                      ++$columnID;
 470                  }
 471              }
 472  
 473              $rowID = 1;
 474              if (isset($worksheet->Table->Row)) {
 475                  $additionalMergedCells = 0;
 476                  foreach ($worksheet->Table->Row as $rowData) {
 477                      $rowHasData = false;
 478                      $row_ss = $rowData->attributes($namespaces['ss']);
 479                      if (isset($row_ss['Index'])) {
 480                          $rowID = (int) $row_ss['Index'];
 481                      }
 482  
 483                      $columnID = 'A';
 484                      foreach ($rowData->Cell as $cell) {
 485                          $cell_ss = $cell->attributes($namespaces['ss']);
 486                          if (isset($cell_ss['Index'])) {
 487                              $columnID = Coordinate::stringFromColumnIndex((int) $cell_ss['Index']);
 488                          }
 489                          $cellRange = $columnID . $rowID;
 490  
 491                          if ($this->getReadFilter() !== null) {
 492                              if (!$this->getReadFilter()->readCell($columnID, $rowID, $worksheetName)) {
 493                                  ++$columnID;
 494  
 495                                  continue;
 496                              }
 497                          }
 498  
 499                          if (isset($cell_ss['HRef'])) {
 500                              $spreadsheet->getActiveSheet()->getCell($cellRange)->getHyperlink()->setUrl((string) $cell_ss['HRef']);
 501                          }
 502  
 503                          if ((isset($cell_ss['MergeAcross'])) || (isset($cell_ss['MergeDown']))) {
 504                              $columnTo = $columnID;
 505                              if (isset($cell_ss['MergeAcross'])) {
 506                                  $additionalMergedCells += (int) $cell_ss['MergeAcross'];
 507                                  $columnTo = Coordinate::stringFromColumnIndex(Coordinate::columnIndexFromString($columnID) + $cell_ss['MergeAcross']);
 508                              }
 509                              $rowTo = $rowID;
 510                              if (isset($cell_ss['MergeDown'])) {
 511                                  $rowTo = $rowTo + $cell_ss['MergeDown'];
 512                              }
 513                              $cellRange .= ':' . $columnTo . $rowTo;
 514                              $spreadsheet->getActiveSheet()->mergeCells($cellRange);
 515                          }
 516  
 517                          $hasCalculatedValue = false;
 518                          $cellDataFormula = '';
 519                          if (isset($cell_ss['Formula'])) {
 520                              $cellDataFormula = $cell_ss['Formula'];
 521                              $hasCalculatedValue = true;
 522                          }
 523                          if (isset($cell->Data)) {
 524                              $cellData = $cell->Data;
 525                              $cellValue = (string) $cellData;
 526                              $type = DataType::TYPE_NULL;
 527                              $cellData_ss = $cellData->attributes($namespaces['ss']);
 528                              if (isset($cellData_ss['Type'])) {
 529                                  $cellDataType = $cellData_ss['Type'];
 530                                  switch ($cellDataType) {
 531                                      /*
 532                                      const TYPE_STRING        = 's';
 533                                      const TYPE_FORMULA        = 'f';
 534                                      const TYPE_NUMERIC        = 'n';
 535                                      const TYPE_BOOL            = 'b';
 536                                      const TYPE_NULL            = 'null';
 537                                      const TYPE_INLINE        = 'inlineStr';
 538                                      const TYPE_ERROR        = 'e';
 539                                      */
 540                                      case 'String':
 541                                          $type = DataType::TYPE_STRING;
 542  
 543                                          break;
 544                                      case 'Number':
 545                                          $type = DataType::TYPE_NUMERIC;
 546                                          $cellValue = (float) $cellValue;
 547                                          if (floor($cellValue) == $cellValue) {
 548                                              $cellValue = (int) $cellValue;
 549                                          }
 550  
 551                                          break;
 552                                      case 'Boolean':
 553                                          $type = DataType::TYPE_BOOL;
 554                                          $cellValue = ($cellValue != 0);
 555  
 556                                          break;
 557                                      case 'DateTime':
 558                                          $type = DataType::TYPE_NUMERIC;
 559                                          $cellValue = Date::PHPToExcel(strtotime($cellValue . ' UTC'));
 560  
 561                                          break;
 562                                      case 'Error':
 563                                          $type = DataType::TYPE_ERROR;
 564                                          $hasCalculatedValue = false;
 565  
 566                                          break;
 567                                  }
 568                              }
 569  
 570                              if ($hasCalculatedValue) {
 571                                  $type = DataType::TYPE_FORMULA;
 572                                  $columnNumber = Coordinate::columnIndexFromString($columnID);
 573                                  $cellDataFormula = AddressHelper::convertFormulaToA1($cellDataFormula, $rowID, $columnNumber);
 574                              }
 575  
 576                              $spreadsheet->getActiveSheet()->getCell($columnID . $rowID)->setValueExplicit((($hasCalculatedValue) ? $cellDataFormula : $cellValue), $type);
 577                              if ($hasCalculatedValue) {
 578                                  $spreadsheet->getActiveSheet()->getCell($columnID . $rowID)->setCalculatedValue($cellValue);
 579                              }
 580                              $rowHasData = true;
 581                          }
 582  
 583                          if (isset($cell->Comment)) {
 584                              $commentAttributes = $cell->Comment->attributes($namespaces['ss']);
 585                              $author = 'unknown';
 586                              if (isset($commentAttributes->Author)) {
 587                                  $author = (string) $commentAttributes->Author;
 588                              }
 589                              $node = $cell->Comment->Data->asXML();
 590                              $annotation = strip_tags($node);
 591                              $spreadsheet->getActiveSheet()->getComment($columnID . $rowID)->setAuthor($author)->setText($this->parseRichText($annotation));
 592                          }
 593  
 594                          if (isset($cell_ss['StyleID'])) {
 595                              $style = (string) $cell_ss['StyleID'];
 596                              if ((isset($this->styles[$style])) && (!empty($this->styles[$style]))) {
 597                                  //if (!$spreadsheet->getActiveSheet()->cellExists($columnID . $rowID)) {
 598                                  //    $spreadsheet->getActiveSheet()->getCell($columnID . $rowID)->setValue(null);
 599                                  //}
 600                                  $spreadsheet->getActiveSheet()->getStyle($cellRange)->applyFromArray($this->styles[$style]);
 601                              }
 602                          }
 603                          ++$columnID;
 604                          while ($additionalMergedCells > 0) {
 605                              ++$columnID;
 606                              --$additionalMergedCells;
 607                          }
 608                      }
 609  
 610                      if ($rowHasData) {
 611                          if (isset($row_ss['Height'])) {
 612                              $rowHeight = $row_ss['Height'];
 613                              $spreadsheet->getActiveSheet()->getRowDimension($rowID)->setRowHeight($rowHeight);
 614                          }
 615                      }
 616  
 617                      ++$rowID;
 618                  }
 619  
 620                  if (isset($namespaces['x'])) {
 621                      $xmlX = $worksheet->children($namespaces['x']);
 622                      if (isset($xmlX->WorksheetOptions)) {
 623                          (new PageSettings($xmlX, $namespaces))->loadPageSettings($spreadsheet);
 624                      }
 625                  }
 626              }
 627              ++$worksheetID;
 628          }
 629  
 630          // Globally scoped defined names
 631          $activeWorksheet = $spreadsheet->setActiveSheetIndex(0);
 632          if (isset($xml->Names[0])) {
 633              foreach ($xml->Names[0] as $definedName) {
 634                  $definedName_ss = $definedName->attributes($namespaces['ss']);
 635                  $name = (string) $definedName_ss['Name'];
 636                  $definedValue = (string) $definedName_ss['RefersTo'];
 637                  $convertedValue = AddressHelper::convertFormulaToA1($definedValue);
 638                  if ($convertedValue[0] === '=') {
 639                      $convertedValue = substr($convertedValue, 1);
 640                  }
 641                  $spreadsheet->addDefinedName(DefinedName::createInstance($name, $activeWorksheet, $convertedValue));
 642              }
 643          }
 644  
 645          // Return
 646          return $spreadsheet;
 647      }
 648  
 649      protected function parseRichText($is)
 650      {
 651          $value = new RichText();
 652  
 653          $value->createText($is);
 654  
 655          return $value;
 656      }
 657  
 658      private function parseStyles(SimpleXMLElement $xml, array $namespaces): void
 659      {
 660          if (!isset($xml->Styles)) {
 661              return;
 662          }
 663  
 664          foreach ($xml->Styles[0] as $style) {
 665              $style_ss = $style->attributes($namespaces['ss']);
 666              $styleID = (string) $style_ss['ID'];
 667              $this->styles[$styleID] = (isset($this->styles['Default'])) ? $this->styles['Default'] : [];
 668              foreach ($style as $styleType => $styleData) {
 669                  $styleAttributes = $styleData->attributes($namespaces['ss']);
 670                  switch ($styleType) {
 671                      case 'Alignment':
 672                          $this->parseStyleAlignment($styleID, $styleAttributes);
 673  
 674                          break;
 675                      case 'Borders':
 676                          $this->parseStyleBorders($styleID, $styleData, $namespaces);
 677  
 678                          break;
 679                      case 'Font':
 680                          $this->parseStyleFont($styleID, $styleAttributes);
 681  
 682                          break;
 683                      case 'Interior':
 684                          $this->parseStyleInterior($styleID, $styleAttributes);
 685  
 686                          break;
 687                      case 'NumberFormat':
 688                          $this->parseStyleNumberFormat($styleID, $styleAttributes);
 689  
 690                          break;
 691                  }
 692              }
 693          }
 694      }
 695  
 696      /**
 697       * @param string $styleID
 698       */
 699      private function parseStyleAlignment($styleID, SimpleXMLElement $styleAttributes): void
 700      {
 701          $verticalAlignmentStyles = [
 702              Alignment::VERTICAL_BOTTOM,
 703              Alignment::VERTICAL_TOP,
 704              Alignment::VERTICAL_CENTER,
 705              Alignment::VERTICAL_JUSTIFY,
 706          ];
 707          $horizontalAlignmentStyles = [
 708              Alignment::HORIZONTAL_GENERAL,
 709              Alignment::HORIZONTAL_LEFT,
 710              Alignment::HORIZONTAL_RIGHT,
 711              Alignment::HORIZONTAL_CENTER,
 712              Alignment::HORIZONTAL_CENTER_CONTINUOUS,
 713              Alignment::HORIZONTAL_JUSTIFY,
 714          ];
 715  
 716          foreach ($styleAttributes as $styleAttributeKey => $styleAttributeValue) {
 717              $styleAttributeValue = (string) $styleAttributeValue;
 718              switch ($styleAttributeKey) {
 719                  case 'Vertical':
 720                      if (self::identifyFixedStyleValue($verticalAlignmentStyles, $styleAttributeValue)) {
 721                          $this->styles[$styleID]['alignment']['vertical'] = $styleAttributeValue;
 722                      }
 723  
 724                      break;
 725                  case 'Horizontal':
 726                      if (self::identifyFixedStyleValue($horizontalAlignmentStyles, $styleAttributeValue)) {
 727                          $this->styles[$styleID]['alignment']['horizontal'] = $styleAttributeValue;
 728                      }
 729  
 730                      break;
 731                  case 'WrapText':
 732                      $this->styles[$styleID]['alignment']['wrapText'] = true;
 733  
 734                      break;
 735                  case 'Rotate':
 736                      $this->styles[$styleID]['alignment']['textRotation'] = $styleAttributeValue;
 737  
 738                      break;
 739              }
 740          }
 741      }
 742  
 743      private static $borderPositions = ['top', 'left', 'bottom', 'right'];
 744  
 745      /**
 746       * @param $styleID
 747       */
 748      private function parseStyleBorders($styleID, SimpleXMLElement $styleData, array $namespaces): void
 749      {
 750          $diagonalDirection = '';
 751          $borderPosition = '';
 752          foreach ($styleData->Border as $borderStyle) {
 753              $borderAttributes = $borderStyle->attributes($namespaces['ss']);
 754              $thisBorder = [];
 755              $style = (string) $borderAttributes->Weight;
 756              $style .= strtolower((string) $borderAttributes->LineStyle);
 757              $thisBorder['borderStyle'] = self::$mappings['borderStyle'][$style] ?? Border::BORDER_NONE;
 758              foreach ($borderAttributes as $borderStyleKey => $borderStyleValue) {
 759                  switch ($borderStyleKey) {
 760                      case 'Position':
 761                          $borderStyleValue = strtolower((string) $borderStyleValue);
 762                          if (in_array($borderStyleValue, self::$borderPositions)) {
 763                              $borderPosition = $borderStyleValue;
 764                          } elseif ($borderStyleValue == 'diagonalleft') {
 765                              $diagonalDirection = $diagonalDirection ? Borders::DIAGONAL_BOTH : Borders::DIAGONAL_DOWN;
 766                          } elseif ($borderStyleValue == 'diagonalright') {
 767                              $diagonalDirection = $diagonalDirection ? Borders::DIAGONAL_BOTH : Borders::DIAGONAL_UP;
 768                          }
 769  
 770                          break;
 771                      case 'Color':
 772                          $borderColour = substr($borderStyleValue, 1);
 773                          $thisBorder['color']['rgb'] = $borderColour;
 774  
 775                          break;
 776                  }
 777              }
 778              if ($borderPosition) {
 779                  $this->styles[$styleID]['borders'][$borderPosition] = $thisBorder;
 780              } elseif ($diagonalDirection) {
 781                  $this->styles[$styleID]['borders']['diagonalDirection'] = $diagonalDirection;
 782                  $this->styles[$styleID]['borders']['diagonal'] = $thisBorder;
 783              }
 784          }
 785      }
 786  
 787      private static $underlineStyles = [
 788          Font::UNDERLINE_NONE,
 789          Font::UNDERLINE_DOUBLE,
 790          Font::UNDERLINE_DOUBLEACCOUNTING,
 791          Font::UNDERLINE_SINGLE,
 792          Font::UNDERLINE_SINGLEACCOUNTING,
 793      ];
 794  
 795      private function parseStyleFontUnderline(string $styleID, string $styleAttributeValue): void
 796      {
 797          if (self::identifyFixedStyleValue(self::$underlineStyles, $styleAttributeValue)) {
 798              $this->styles[$styleID]['font']['underline'] = $styleAttributeValue;
 799          }
 800      }
 801  
 802      private function parseStyleFontVerticalAlign(string $styleID, string $styleAttributeValue): void
 803      {
 804          if ($styleAttributeValue == 'Superscript') {
 805              $this->styles[$styleID]['font']['superscript'] = true;
 806          }
 807          if ($styleAttributeValue == 'Subscript') {
 808              $this->styles[$styleID]['font']['subscript'] = true;
 809          }
 810      }
 811  
 812      /**
 813       * @param $styleID
 814       */
 815      private function parseStyleFont(string $styleID, SimpleXMLElement $styleAttributes): void
 816      {
 817          foreach ($styleAttributes as $styleAttributeKey => $styleAttributeValue) {
 818              $styleAttributeValue = (string) $styleAttributeValue;
 819              switch ($styleAttributeKey) {
 820                  case 'FontName':
 821                      $this->styles[$styleID]['font']['name'] = $styleAttributeValue;
 822  
 823                      break;
 824                  case 'Size':
 825                      $this->styles[$styleID]['font']['size'] = $styleAttributeValue;
 826  
 827                      break;
 828                  case 'Color':
 829                      $this->styles[$styleID]['font']['color']['rgb'] = substr($styleAttributeValue, 1);
 830  
 831                      break;
 832                  case 'Bold':
 833                      $this->styles[$styleID]['font']['bold'] = true;
 834  
 835                      break;
 836                  case 'Italic':
 837                      $this->styles[$styleID]['font']['italic'] = true;
 838  
 839                      break;
 840                  case 'Underline':
 841                      $this->parseStyleFontUnderline($styleID, $styleAttributeValue);
 842  
 843                      break;
 844                  case 'VerticalAlign':
 845                      $this->parseStyleFontVerticalAlign($styleID, $styleAttributeValue);
 846  
 847                      break;
 848              }
 849          }
 850      }
 851  
 852      /**
 853       * @param $styleID
 854       */
 855      private function parseStyleInterior($styleID, SimpleXMLElement $styleAttributes): void
 856      {
 857          foreach ($styleAttributes as $styleAttributeKey => $styleAttributeValue) {
 858              switch ($styleAttributeKey) {
 859                  case 'Color':
 860                      $this->styles[$styleID]['fill']['endColor']['rgb'] = substr($styleAttributeValue, 1);
 861                      $this->styles[$styleID]['fill']['startColor']['rgb'] = substr($styleAttributeValue, 1);
 862  
 863                      break;
 864                  case 'PatternColor':
 865                      $this->styles[$styleID]['fill']['startColor']['rgb'] = substr($styleAttributeValue, 1);
 866  
 867                      break;
 868                  case 'Pattern':
 869                      $lcStyleAttributeValue = strtolower((string) $styleAttributeValue);
 870                      $this->styles[$styleID]['fill']['fillType'] = self::$mappings['fillType'][$lcStyleAttributeValue] ?? Fill::FILL_NONE;
 871  
 872                      break;
 873              }
 874          }
 875      }
 876  
 877      /**
 878       * @param $styleID
 879       */
 880      private function parseStyleNumberFormat($styleID, SimpleXMLElement $styleAttributes): void
 881      {
 882          $fromFormats = ['\-', '\ '];
 883          $toFormats = ['-', ' '];
 884  
 885          foreach ($styleAttributes as $styleAttributeKey => $styleAttributeValue) {
 886              $styleAttributeValue = str_replace($fromFormats, $toFormats, $styleAttributeValue);
 887              switch ($styleAttributeValue) {
 888                  case 'Short Date':
 889                      $styleAttributeValue = 'dd/mm/yyyy';
 890  
 891                      break;
 892              }
 893  
 894              if ($styleAttributeValue > '') {
 895                  $this->styles[$styleID]['numberFormat']['formatCode'] = $styleAttributeValue;
 896              }
 897          }
 898      }
 899  }