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 1.8.0, 2014-03-02
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: }
451: