ILIAS  release_5-3 Revision v5.3.23-19-g915713cf615
SYLK.php
Go to the documentation of this file.
1<?php
30if (!defined('PHPEXCEL_ROOT')) {
34 define('PHPEXCEL_ROOT', dirname(__FILE__) . '/../../');
35 require(PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php');
36}
37
45class PHPExcel_Reader_SYLK extends PHPExcel_Reader_Abstract implements PHPExcel_Reader_IReader
46{
52 private $_inputEncoding = 'ANSI';
53
59 private $_sheetIndex = 0;
60
66 private $_formats = array();
67
73 private $_format = 0;
74
78 public function __construct() {
79 $this->_readFilter = new PHPExcel_Reader_DefaultReadFilter();
80 }
81
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
112 public function setInputEncoding($pValue = 'ANSI')
113 {
114 $this->_inputEncoding = $pValue;
115 return $this;
116 }
117
123 public function getInputEncoding()
124 {
125 return $this->_inputEncoding;
126 }
127
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
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
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
435 public function getSheetIndex() {
436 return $this->_sheetIndex;
437 }
438
445 public function setSheetIndex($pValue = 0) {
446 $this->_sheetIndex = $pValue;
447 return $this;
448 }
449
450}
$objPHPExcel
$objPHPExcel
Definition: 01pharSimple.php:42
$column
$column
Definition: 39dropdown.php:62
php
An exception for terminatinating execution or to throw for unit testing.
PHPExcel_Calculation\_unwrapResult
static _unwrapResult($value)
Remove quotes used as a wrapper to identify string values.
Definition: Calculation.php:2187
PHPExcel_Cell\stringFromColumnIndex
static stringFromColumnIndex($pColumnIndex=0)
String from columnindex.
Definition: Cell.php:825
PHPExcel_Reader_Abstract
Definition: Abstract.php:37
PHPExcel_Reader_Abstract\$_fileHandle
$_fileHandle
Definition: Abstract.php:70
PHPExcel_Reader_Abstract\_openFile
_openFile($pFilename)
Open file for reading.
Definition: Abstract.php:195
PHPExcel_Reader_DefaultReadFilter
Definition: DefaultReadFilter.php:46
PHPExcel_Reader_Exception
Definition: Exception.php:36
PHPExcel_Reader_SYLK
Definition: SYLK.php:46
PHPExcel_Reader_SYLK\__construct
__construct()
Create a new PHPExcel_Reader_SYLK.
Definition: SYLK.php:78
PHPExcel_Reader_SYLK\setSheetIndex
setSheetIndex($pValue=0)
Set sheet index.
Definition: SYLK.php:445
PHPExcel_Reader_SYLK\listWorksheetInfo
listWorksheetInfo($pFilename)
Return worksheet info (Name, Last Column Letter, Last Column Index, Total Rows, Total Columns)
Definition: SYLK.php:134
PHPExcel_Reader_SYLK\load
load($pFilename)
Loads PHPExcel from file.
Definition: SYLK.php:204
PHPExcel_Reader_SYLK\$_format
$_format
Definition: SYLK.php:73
PHPExcel_Reader_SYLK\getInputEncoding
getInputEncoding()
Get input encoding.
Definition: SYLK.php:123
PHPExcel_Reader_SYLK\setInputEncoding
setInputEncoding($pValue='ANSI')
Set input encoding.
Definition: SYLK.php:112
PHPExcel_Reader_SYLK\getSheetIndex
getSheetIndex()
Get sheet index.
Definition: SYLK.php:435
PHPExcel_Reader_SYLK\$_inputEncoding
$_inputEncoding
Definition: SYLK.php:52
PHPExcel_Reader_SYLK\_isValidFormat
_isValidFormat()
Validate that the current file is a SYLK file.
Definition: SYLK.php:87
PHPExcel_Reader_SYLK\$_sheetIndex
$_sheetIndex
Definition: SYLK.php:59
PHPExcel_Reader_SYLK\$_formats
$_formats
Definition: SYLK.php:66
PHPExcel_Reader_SYLK\loadIntoExisting
loadIntoExisting($pFilename, PHPExcel $objPHPExcel)
Loads PHPExcel from file into PHPExcel instance.
Definition: SYLK.php:221
PHPExcel_Shared_String\SYLKtoUTF8
static SYLKtoUTF8($pValue='')
Convert SYLK encoded string to UTF-8.
Definition: String.php:783
PHPExcel_Style_Border\BORDER_THIN
const BORDER_THIN
Definition: Border.php:52
$key
$key
Definition: croninfo.php:18
$i
$i
Definition: disco.tpl.php:19
PHPExcel_Reader_IReader
Definition: IReader.php:37
defined
defined( 'APPLICATION_ENV')||define( 'APPLICATION_ENV'
Definition: bootstrap.php:27