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