ILIAS  release_5-2 Revision v5.2.25-18-g3f80b828510
SYLK.php
Go to the documentation of this file.
1 <?php
30 if (!defined('PHPEXCEL_ROOT')) {
34  define('PHPEXCEL_ROOT', dirname(__FILE__) . '/../../');
35  require(PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php');
36 }
37 
45 class 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 }
PHPExcel_Reader_SYLK\__construct
__construct()
Create a new PHPExcel_Reader_SYLK.
Definition: SYLK.php:78
PHPExcel_Style_Border\BORDER_THIN
const BORDER_THIN
Definition: Border.php:52
PHPExcel\getSheetCount
getSheetCount()
Get sheet count.
Definition: PHPExcel.php:661
PHPExcel_Reader_Abstract\$_fileHandle
$_fileHandle
Definition: Abstract.php:70
PHPExcel_Reader_SYLK\$_format
$_format
Definition: SYLK.php:73
PHPExcel_Reader_Abstract
Definition: Abstract.php:36
PHPExcel_Reader_SYLK\loadIntoExisting
loadIntoExisting($pFilename, PHPExcel $objPHPExcel)
Loads PHPExcel from file into PHPExcel instance.
Definition: SYLK.php:221
$objPHPExcel
$objPHPExcel
Definition: 01pharSimple.php:42
PHPExcel_Reader_SYLK\$_inputEncoding
$_inputEncoding
Definition: SYLK.php:52
PHPExcel_Reader_SYLK\setSheetIndex
setSheetIndex($pValue=0)
Set sheet index.
Definition: SYLK.php:445
PHPExcel_Reader_SYLK\$_sheetIndex
$_sheetIndex
Definition: SYLK.php:59
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_DefaultReadFilter
Definition: DefaultReadFilter.php:45
PHPExcel\createSheet
createSheet($iSheetIndex=NULL)
Create sheet and add it to this workbook.
Definition: PHPExcel.php:479
PHPExcel_Reader_SYLK
Definition: SYLK.php:45
PHPExcel_Reader_SYLK\_isValidFormat
_isValidFormat()
Validate that the current file is a SYLK file.
Definition: SYLK.php:87
$column
$column
Definition: 39dropdown.php:62
PHPExcel_Reader_SYLK\setInputEncoding
setInputEncoding($pValue='ANSI')
Set input encoding.
Definition: SYLK.php:112
PHPExcel_Reader_SYLK\getInputEncoding
getInputEncoding()
Get input encoding.
Definition: SYLK.php:123
PHPExcel_Reader_IReader
Definition: IReader.php:36
array
Create styles array
The data for the language used.
Definition: 40duplicateStyle.php:19
PHPExcel_Reader_SYLK\getSheetIndex
getSheetIndex()
Get sheet index.
Definition: SYLK.php:435
PHPExcel\getActiveSheet
getActiveSheet()
Get active sheet.
Definition: PHPExcel.php:467
PHPExcel_Reader_Exception
Definition: Exception.php:36
PHPExcel_Cell\stringFromColumnIndex
static stringFromColumnIndex($pColumnIndex=0)
String from columnindex.
Definition: Cell.php:825
PHPExcel\setActiveSheetIndex
setActiveSheetIndex($pIndex=0)
Set active sheet index.
Definition: PHPExcel.php:683
PHPExcel_Calculation\_unwrapResult
static _unwrapResult($value)
Remove quotes used as a wrapper to identify string values.
Definition: Calculation.php:2187
PHPExcel_Reader_SYLK\$_formats
$_formats
Definition: SYLK.php:66
PHPExcel_Reader_Abstract\_openFile
_openFile($pFilename)
Open file for reading.
Definition: Abstract.php:195
defined
defined( 'APPLICATION_ENV')||define( 'APPLICATION_ENV'
Definition: bootstrap.php:27
PHPExcel_Shared_String\SYLKtoUTF8
static SYLKtoUTF8($pValue='')
Convert SYLK encoded string to UTF-8.
Definition: String.php:783
PHPExcel_Reader_SYLK\load
load($pFilename)
Loads PHPExcel from file.
Definition: SYLK.php:204