1<?php
2
3/** PHPExcel root directory */
4if (!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 */
37class 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}
479