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