[Summary view]

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

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