[Summary view]

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

   1  <?php
   2  
   3  namespace PhpOffice\PhpSpreadsheet\Reader;
   4  
   5  use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
   6  use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
   7  use PhpOffice\PhpSpreadsheet\Reader\Exception as ReaderException;
   8  use PhpOffice\PhpSpreadsheet\Shared\StringHelper;
   9  use PhpOffice\PhpSpreadsheet\Spreadsheet;
  10  use PhpOffice\PhpSpreadsheet\Style\Border;
  11  use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
  12  
  13  class Slk extends BaseReader
  14  {
  15      /**
  16       * Input encoding.
  17       *
  18       * @var string
  19       */
  20      private $inputEncoding = 'ANSI';
  21  
  22      /**
  23       * Sheet index to read.
  24       *
  25       * @var int
  26       */
  27      private $sheetIndex = 0;
  28  
  29      /**
  30       * Formats.
  31       *
  32       * @var array
  33       */
  34      private $formats = [];
  35  
  36      /**
  37       * Format Count.
  38       *
  39       * @var int
  40       */
  41      private $format = 0;
  42  
  43      /**
  44       * Fonts.
  45       *
  46       * @var array
  47       */
  48      private $fonts = [];
  49  
  50      /**
  51       * Font Count.
  52       *
  53       * @var int
  54       */
  55      private $fontcount = 0;
  56  
  57      /**
  58       * Create a new SYLK Reader instance.
  59       */
  60      public function __construct()
  61      {
  62          parent::__construct();
  63      }
  64  
  65      /**
  66       * Validate that the current file is a SYLK file.
  67       */
  68      public function canRead(string $filename): bool
  69      {
  70          try {
  71              $this->openFile($filename);
  72          } catch (ReaderException $e) {
  73              return false;
  74          }
  75  
  76          // Read sample data (first 2 KB will do)
  77          $data = (string) fread($this->fileHandle, 2048);
  78  
  79          // Count delimiters in file
  80          $delimiterCount = substr_count($data, ';');
  81          $hasDelimiter = $delimiterCount > 0;
  82  
  83          // Analyze first line looking for ID; signature
  84          $lines = explode("\n", $data);
  85          $hasId = substr($lines[0], 0, 4) === 'ID;P';
  86  
  87          fclose($this->fileHandle);
  88  
  89          return $hasDelimiter && $hasId;
  90      }
  91  
  92      private function canReadOrBust(string $filename): void
  93      {
  94          if (!$this->canRead($filename)) {
  95              throw new ReaderException($filename . ' is an Invalid SYLK file.');
  96          }
  97          $this->openFile($filename);
  98      }
  99  
 100      /**
 101       * Set input encoding.
 102       *
 103       * @deprecated no use is made of this property
 104       *
 105       * @param string $inputEncoding Input encoding, eg: 'ANSI'
 106       *
 107       * @return $this
 108       *
 109       * @codeCoverageIgnore
 110       */
 111      public function setInputEncoding($inputEncoding)
 112      {
 113          $this->inputEncoding = $inputEncoding;
 114  
 115          return $this;
 116      }
 117  
 118      /**
 119       * Get input encoding.
 120       *
 121       * @deprecated no use is made of this property
 122       *
 123       * @return string
 124       *
 125       * @codeCoverageIgnore
 126       */
 127      public function getInputEncoding()
 128      {
 129          return $this->inputEncoding;
 130      }
 131  
 132      /**
 133       * Return worksheet info (Name, Last Column Letter, Last Column Index, Total Rows, Total Columns).
 134       *
 135       * @param string $filename
 136       *
 137       * @return array
 138       */
 139      public function listWorksheetInfo($filename)
 140      {
 141          // Open file
 142          $this->canReadOrBust($filename);
 143          $fileHandle = $this->fileHandle;
 144          rewind($fileHandle);
 145  
 146          $worksheetInfo = [];
 147          $worksheetInfo[0]['worksheetName'] = basename($filename, '.slk');
 148  
 149          // loop through one row (line) at a time in the file
 150          $rowIndex = 0;
 151          $columnIndex = 0;
 152          while (($rowData = fgets($fileHandle)) !== false) {
 153              $columnIndex = 0;
 154  
 155              // convert SYLK encoded $rowData to UTF-8
 156              $rowData = StringHelper::SYLKtoUTF8($rowData);
 157  
 158              // explode each row at semicolons while taking into account that literal semicolon (;)
 159              // is escaped like this (;;)
 160              $rowData = explode("\t", str_replace('¤', ';', str_replace(';', "\t", str_replace(';;', '¤', rtrim($rowData)))));
 161  
 162              $dataType = array_shift($rowData);
 163              if ($dataType == 'B') {
 164                  foreach ($rowData as $rowDatum) {
 165                      switch ($rowDatum[0]) {
 166                          case 'X':
 167                              $columnIndex = (int) substr($rowDatum, 1) - 1;
 168  
 169                              break;
 170                          case 'Y':
 171                              $rowIndex = substr($rowDatum, 1);
 172  
 173                              break;
 174                      }
 175                  }
 176  
 177                  break;
 178              }
 179          }
 180  
 181          $worksheetInfo[0]['lastColumnIndex'] = $columnIndex;
 182          $worksheetInfo[0]['totalRows'] = $rowIndex;
 183          $worksheetInfo[0]['lastColumnLetter'] = Coordinate::stringFromColumnIndex($worksheetInfo[0]['lastColumnIndex'] + 1);
 184          $worksheetInfo[0]['totalColumns'] = $worksheetInfo[0]['lastColumnIndex'] + 1;
 185  
 186          // Close file
 187          fclose($fileHandle);
 188  
 189          return $worksheetInfo;
 190      }
 191  
 192      /**
 193       * Loads PhpSpreadsheet from file.
 194       */
 195      protected function loadSpreadsheetFromFile(string $filename): Spreadsheet
 196      {
 197          // Create new Spreadsheet
 198          $spreadsheet = new Spreadsheet();
 199  
 200          // Load into this instance
 201          return $this->loadIntoExisting($filename, $spreadsheet);
 202      }
 203  
 204      private const COLOR_ARRAY = [
 205          'FF00FFFF', // 0 - cyan
 206          'FF000000', // 1 - black
 207          'FFFFFFFF', // 2 - white
 208          'FFFF0000', // 3 - red
 209          'FF00FF00', // 4 - green
 210          'FF0000FF', // 5 - blue
 211          'FFFFFF00', // 6 - yellow
 212          'FFFF00FF', // 7 - magenta
 213      ];
 214  
 215      private const FONT_STYLE_MAPPINGS = [
 216          'B' => 'bold',
 217          'I' => 'italic',
 218          'U' => 'underline',
 219      ];
 220  
 221      private function processFormula(string $rowDatum, bool &$hasCalculatedValue, string &$cellDataFormula, string $row, string $column): void
 222      {
 223          $cellDataFormula = '=' . substr($rowDatum, 1);
 224          //    Convert R1C1 style references to A1 style references (but only when not quoted)
 225          $temp = explode('"', $cellDataFormula);
 226          $key = false;
 227          foreach ($temp as &$value) {
 228              //    Only count/replace in alternate array entries
 229              $key = !$key;
 230              if ($key) {
 231                  preg_match_all('/(R(\[?-?\d*\]?))(C(\[?-?\d*\]?))/', $value, $cellReferences, PREG_SET_ORDER + PREG_OFFSET_CAPTURE);
 232                  //    Reverse the matches array, otherwise all our offsets will become incorrect if we modify our way
 233                  //        through the formula from left to right. Reversing means that we work right to left.through
 234                  //        the formula
 235                  $cellReferences = array_reverse($cellReferences);
 236                  //    Loop through each R1C1 style reference in turn, converting it to its A1 style equivalent,
 237                  //        then modify the formula to use that new reference
 238                  foreach ($cellReferences as $cellReference) {
 239                      $rowReference = $cellReference[2][0];
 240                      //    Empty R reference is the current row
 241                      if ($rowReference == '') {
 242                          $rowReference = $row;
 243                      }
 244                      //    Bracketed R references are relative to the current row
 245                      if ($rowReference[0] == '[') {
 246                          $rowReference = (int) $row + (int) trim($rowReference, '[]');
 247                      }
 248                      $columnReference = $cellReference[4][0];
 249                      //    Empty C reference is the current column
 250                      if ($columnReference == '') {
 251                          $columnReference = $column;
 252                      }
 253                      //    Bracketed C references are relative to the current column
 254                      if ($columnReference[0] == '[') {
 255                          $columnReference = (int) $column + (int) trim($columnReference, '[]');
 256                      }
 257                      $A1CellReference = Coordinate::stringFromColumnIndex($columnReference) . $rowReference;
 258  
 259                      $value = substr_replace($value, $A1CellReference, $cellReference[0][1], strlen($cellReference[0][0]));
 260                  }
 261              }
 262          }
 263          unset($value);
 264          //    Then rebuild the formula string
 265          $cellDataFormula = implode('"', $temp);
 266          $hasCalculatedValue = true;
 267      }
 268  
 269      private function processCRecord(array $rowData, Spreadsheet &$spreadsheet, string &$row, string &$column): void
 270      {
 271          //    Read cell value data
 272          $hasCalculatedValue = false;
 273          $cellDataFormula = $cellData = '';
 274          foreach ($rowData as $rowDatum) {
 275              switch ($rowDatum[0]) {
 276                  case 'C':
 277                  case 'X':
 278                      $column = substr($rowDatum, 1);
 279  
 280                      break;
 281                  case 'R':
 282                  case 'Y':
 283                      $row = substr($rowDatum, 1);
 284  
 285                      break;
 286                  case 'K':
 287                      $cellData = substr($rowDatum, 1);
 288  
 289                      break;
 290                  case 'E':
 291                      $this->processFormula($rowDatum, $hasCalculatedValue, $cellDataFormula, $row, $column);
 292  
 293                      break;
 294                  case 'A':
 295                      $comment = substr($rowDatum, 1);
 296                      $columnLetter = Coordinate::stringFromColumnIndex((int) $column);
 297                      $spreadsheet->getActiveSheet()
 298                          ->getComment("$columnLetter$row")
 299                          ->getText()
 300                          ->createText($comment);
 301  
 302                      break;
 303              }
 304          }
 305          $columnLetter = Coordinate::stringFromColumnIndex((int) $column);
 306          $cellData = Calculation::unwrapResult($cellData);
 307  
 308          // Set cell value
 309          $this->processCFinal($spreadsheet, $hasCalculatedValue, $cellDataFormula, $cellData, "$columnLetter$row");
 310      }
 311  
 312      private function processCFinal(Spreadsheet &$spreadsheet, bool $hasCalculatedValue, string $cellDataFormula, string $cellData, string $coordinate): void
 313      {
 314          // Set cell value
 315          $spreadsheet->getActiveSheet()->getCell($coordinate)->setValue(($hasCalculatedValue) ? $cellDataFormula : $cellData);
 316          if ($hasCalculatedValue) {
 317              $cellData = Calculation::unwrapResult($cellData);
 318              $spreadsheet->getActiveSheet()->getCell($coordinate)->setCalculatedValue($cellData);
 319          }
 320      }
 321  
 322      private function processFRecord(array $rowData, Spreadsheet &$spreadsheet, string &$row, string &$column): void
 323      {
 324          //    Read cell formatting
 325          $formatStyle = $columnWidth = '';
 326          $startCol = $endCol = '';
 327          $fontStyle = '';
 328          $styleData = [];
 329          foreach ($rowData as $rowDatum) {
 330              switch ($rowDatum[0]) {
 331                  case 'C':
 332                  case 'X':
 333                      $column = substr($rowDatum, 1);
 334  
 335                      break;
 336                  case 'R':
 337                  case 'Y':
 338                      $row = substr($rowDatum, 1);
 339  
 340                      break;
 341                  case 'P':
 342                      $formatStyle = $rowDatum;
 343  
 344                      break;
 345                  case 'W':
 346                      [$startCol, $endCol, $columnWidth] = explode(' ', substr($rowDatum, 1));
 347  
 348                      break;
 349                  case 'S':
 350                      $this->styleSettings($rowDatum, $styleData, $fontStyle);
 351  
 352                      break;
 353              }
 354          }
 355          $this->addFormats($spreadsheet, $formatStyle, $row, $column);
 356          $this->addFonts($spreadsheet, $fontStyle, $row, $column);
 357          $this->addStyle($spreadsheet, $styleData, $row, $column);
 358          $this->addWidth($spreadsheet, $columnWidth, $startCol, $endCol);
 359      }
 360  
 361      private const STYLE_SETTINGS_FONT = ['D' => 'bold', 'I' => 'italic'];
 362  
 363      private const STYLE_SETTINGS_BORDER = [
 364          'B' => 'bottom',
 365          'L' => 'left',
 366          'R' => 'right',
 367          'T' => 'top',
 368      ];
 369  
 370      private function styleSettings(string $rowDatum, array &$styleData, string &$fontStyle): void
 371      {
 372          $styleSettings = substr($rowDatum, 1);
 373          $iMax = strlen($styleSettings);
 374          for ($i = 0; $i < $iMax; ++$i) {
 375              $char = $styleSettings[$i];
 376              if (array_key_exists($char, self::STYLE_SETTINGS_FONT)) {
 377                  $styleData['font'][self::STYLE_SETTINGS_FONT[$char]] = true;
 378              } elseif (array_key_exists($char, self::STYLE_SETTINGS_BORDER)) {
 379                  $styleData['borders'][self::STYLE_SETTINGS_BORDER[$char]]['borderStyle'] = Border::BORDER_THIN;
 380              } elseif ($char == 'S') {
 381                  $styleData['fill']['fillType'] = \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_PATTERN_GRAY125;
 382              } elseif ($char == 'M') {
 383                  if (preg_match('/M([1-9]\\d*)/', $styleSettings, $matches)) {
 384                      $fontStyle = $matches[1];
 385                  }
 386              }
 387          }
 388      }
 389  
 390      private function addFormats(Spreadsheet &$spreadsheet, string $formatStyle, string $row, string $column): void
 391      {
 392          if ($formatStyle && $column > '' && $row > '') {
 393              $columnLetter = Coordinate::stringFromColumnIndex((int) $column);
 394              if (isset($this->formats[$formatStyle])) {
 395                  $spreadsheet->getActiveSheet()->getStyle($columnLetter . $row)->applyFromArray($this->formats[$formatStyle]);
 396              }
 397          }
 398      }
 399  
 400      private function addFonts(Spreadsheet &$spreadsheet, string $fontStyle, string $row, string $column): void
 401      {
 402          if ($fontStyle && $column > '' && $row > '') {
 403              $columnLetter = Coordinate::stringFromColumnIndex((int) $column);
 404              if (isset($this->fonts[$fontStyle])) {
 405                  $spreadsheet->getActiveSheet()->getStyle($columnLetter . $row)->applyFromArray($this->fonts[$fontStyle]);
 406              }
 407          }
 408      }
 409  
 410      private function addStyle(Spreadsheet &$spreadsheet, array $styleData, string $row, string $column): void
 411      {
 412          if ((!empty($styleData)) && $column > '' && $row > '') {
 413              $columnLetter = Coordinate::stringFromColumnIndex((int) $column);
 414              $spreadsheet->getActiveSheet()->getStyle($columnLetter . $row)->applyFromArray($styleData);
 415          }
 416      }
 417  
 418      private function addWidth(Spreadsheet $spreadsheet, string $columnWidth, string $startCol, string $endCol): void
 419      {
 420          if ($columnWidth > '') {
 421              if ($startCol == $endCol) {
 422                  $startCol = Coordinate::stringFromColumnIndex((int) $startCol);
 423                  $spreadsheet->getActiveSheet()->getColumnDimension($startCol)->setWidth((float) $columnWidth);
 424              } else {
 425                  $startCol = Coordinate::stringFromColumnIndex((int) $startCol);
 426                  $endCol = Coordinate::stringFromColumnIndex((int) $endCol);
 427                  $spreadsheet->getActiveSheet()->getColumnDimension($startCol)->setWidth((float) $columnWidth);
 428                  do {
 429                      $spreadsheet->getActiveSheet()->getColumnDimension(++$startCol)->setWidth((float) $columnWidth);
 430                  } while ($startCol !== $endCol);
 431              }
 432          }
 433      }
 434  
 435      private function processPRecord(array $rowData, Spreadsheet &$spreadsheet): void
 436      {
 437          //    Read shared styles
 438          $formatArray = [];
 439          $fromFormats = ['\-', '\ '];
 440          $toFormats = ['-', ' '];
 441          foreach ($rowData as $rowDatum) {
 442              switch ($rowDatum[0]) {
 443                  case 'P':
 444                      $formatArray['numberFormat']['formatCode'] = str_replace($fromFormats, $toFormats, substr($rowDatum, 1));
 445  
 446                      break;
 447                  case 'E':
 448                  case 'F':
 449                      $formatArray['font']['name'] = substr($rowDatum, 1);
 450  
 451                      break;
 452                  case 'M':
 453                      $formatArray['font']['size'] = ((float) substr($rowDatum, 1)) / 20;
 454  
 455                      break;
 456                  case 'L':
 457                      $this->processPColors($rowDatum, $formatArray);
 458  
 459                      break;
 460                  case 'S':
 461                      $this->processPFontStyles($rowDatum, $formatArray);
 462  
 463                      break;
 464              }
 465          }
 466          $this->processPFinal($spreadsheet, $formatArray);
 467      }
 468  
 469      private function processPColors(string $rowDatum, array &$formatArray): void
 470      {
 471          if (preg_match('/L([1-9]\\d*)/', $rowDatum, $matches)) {
 472              $fontColor = $matches[1] % 8;
 473              $formatArray['font']['color']['argb'] = self::COLOR_ARRAY[$fontColor];
 474          }
 475      }
 476  
 477      private function processPFontStyles(string $rowDatum, array &$formatArray): void
 478      {
 479          $styleSettings = substr($rowDatum, 1);
 480          $iMax = strlen($styleSettings);
 481          for ($i = 0; $i < $iMax; ++$i) {
 482              if (array_key_exists($styleSettings[$i], self::FONT_STYLE_MAPPINGS)) {
 483                  $formatArray['font'][self::FONT_STYLE_MAPPINGS[$styleSettings[$i]]] = true;
 484              }
 485          }
 486      }
 487  
 488      private function processPFinal(Spreadsheet &$spreadsheet, array $formatArray): void
 489      {
 490          if (array_key_exists('numberFormat', $formatArray)) {
 491              $this->formats['P' . $this->format] = $formatArray;
 492              ++$this->format;
 493          } elseif (array_key_exists('font', $formatArray)) {
 494              ++$this->fontcount;
 495              $this->fonts[$this->fontcount] = $formatArray;
 496              if ($this->fontcount === 1) {
 497                  $spreadsheet->getDefaultStyle()->applyFromArray($formatArray);
 498              }
 499          }
 500      }
 501  
 502      /**
 503       * Loads PhpSpreadsheet from file into PhpSpreadsheet instance.
 504       *
 505       * @param string $filename
 506       *
 507       * @return Spreadsheet
 508       */
 509      public function loadIntoExisting($filename, Spreadsheet $spreadsheet)
 510      {
 511          // Open file
 512          $this->canReadOrBust($filename);
 513          $fileHandle = $this->fileHandle;
 514          rewind($fileHandle);
 515  
 516          // Create new Worksheets
 517          while ($spreadsheet->getSheetCount() <= $this->sheetIndex) {
 518              $spreadsheet->createSheet();
 519          }
 520          $spreadsheet->setActiveSheetIndex($this->sheetIndex);
 521          $spreadsheet->getActiveSheet()->setTitle(substr(basename($filename, '.slk'), 0, Worksheet::SHEET_TITLE_MAXIMUM_LENGTH));
 522  
 523          // Loop through file
 524          $column = $row = '';
 525  
 526          // loop through one row (line) at a time in the file
 527          while (($rowDataTxt = fgets($fileHandle)) !== false) {
 528              // convert SYLK encoded $rowData to UTF-8
 529              $rowDataTxt = StringHelper::SYLKtoUTF8($rowDataTxt);
 530  
 531              // explode each row at semicolons while taking into account that literal semicolon (;)
 532              // is escaped like this (;;)
 533              $rowData = explode("\t", str_replace('¤', ';', str_replace(';', "\t", str_replace(';;', '¤', rtrim($rowDataTxt)))));
 534  
 535              $dataType = array_shift($rowData);
 536              if ($dataType == 'P') {
 537                  //    Read shared styles
 538                  $this->processPRecord($rowData, $spreadsheet);
 539              } elseif ($dataType == 'C') {
 540                  //    Read cell value data
 541                  $this->processCRecord($rowData, $spreadsheet, $row, $column);
 542              } elseif ($dataType == 'F') {
 543                  //    Read cell formatting
 544                  $this->processFRecord($rowData, $spreadsheet, $row, $column);
 545              } else {
 546                  $this->columnRowFromRowData($rowData, $column, $row);
 547              }
 548          }
 549  
 550          // Close file
 551          fclose($fileHandle);
 552  
 553          // Return
 554          return $spreadsheet;
 555      }
 556  
 557      private function columnRowFromRowData(array $rowData, string &$column, string &$row): void
 558      {
 559          foreach ($rowData as $rowDatum) {
 560              $char0 = $rowDatum[0];
 561              if ($char0 === 'X' || $char0 == 'C') {
 562                  $column = substr($rowDatum, 1);
 563              } elseif ($char0 === 'Y' || $char0 == 'R') {
 564                  $row = substr($rowDatum, 1);
 565              }
 566          }
 567      }
 568  
 569      /**
 570       * Get sheet index.
 571       *
 572       * @return int
 573       */
 574      public function getSheetIndex()
 575      {
 576          return $this->sheetIndex;
 577      }
 578  
 579      /**
 580       * Set sheet index.
 581       *
 582       * @param int $sheetIndex Sheet index
 583       *
 584       * @return $this
 585       */
 586      public function setSheetIndex($sheetIndex)
 587      {
 588          $this->sheetIndex = $sheetIndex;
 589  
 590          return $this;
 591      }
 592  }