[Summary view] [Print] [Text view]

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