[Summary view] [Print] [Text view]

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