ILIAS  eassessment Revision 61809
 All Data Structures Namespaces Files Functions Variables Groups Pages
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 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 
80  private $_readFilter = null;
81 
85  public function __construct() {
86  $this->_readFilter = new PHPExcel_Reader_DefaultReadFilter();
87  }
88 
95  public function canRead($pFilename)
96  {
97  // Check if file exists
98  if (!file_exists($pFilename)) {
99  throw new Exception("Could not open " . $pFilename . " for reading! File does not exist.");
100  }
101 
102  // Read sample data (first 2 KB will do)
103  $fh = fopen($pFilename, 'r');
104  $data = fread($fh, 2048);
105  fclose($fh);
106 
107  // Count delimiters in file
108  $delimiterCount = substr_count($data, ';');
109  if ($delimiterCount < 1) {
110  return false;
111  }
112 
113  // Analyze first line looking for ID; signature
114  $lines = explode("\n", $data);
115  if (substr($lines[0],0,4) != 'ID;P') {
116  return false;
117  }
118 
119  return true;
120  }
121 
129  public function load($pFilename)
130  {
131  // Create new PHPExcel
132  $objPHPExcel = new PHPExcel();
133 
134  // Load into this instance
135  return $this->loadIntoExisting($pFilename, $objPHPExcel);
136  }
137 
143  public function getReadFilter() {
144  return $this->_readFilter;
145  }
146 
152  public function setReadFilter(PHPExcel_Reader_IReadFilter $pValue) {
153  $this->_readFilter = $pValue;
154  return $this;
155  }
156 
162  public function setInputEncoding($pValue = 'ANSI')
163  {
164  $this->_inputEncoding = $pValue;
165  return $this;
166  }
167 
173  public function getInputEncoding()
174  {
175  return $this->_inputEncoding;
176  }
177 
186  public function loadIntoExisting($pFilename, PHPExcel $objPHPExcel)
187  {
188  // Check if file exists
189  if (!file_exists($pFilename)) {
190  throw new Exception("Could not open " . $pFilename . " for reading! File does not exist.");
191  }
192 
193  // Create new PHPExcel
194  while ($objPHPExcel->getSheetCount() <= $this->_sheetIndex) {
195  $objPHPExcel->createSheet();
196  }
197  $objPHPExcel->setActiveSheetIndex( $this->_sheetIndex );
198 
199  $fromFormats = array('\-', '\ ');
200  $toFormats = array('-', ' ');
201 
202  // Open file
203  $fileHandle = fopen($pFilename, 'r');
204  if ($fileHandle === false) {
205  throw new Exception("Could not open file $pFilename for reading.");
206  }
207 
208  // Loop through file
209  $rowData = array();
210  $column = $row = '';
211 
212  // loop through one row (line) at a time in the file
213  while (($rowData = fgets($fileHandle)) !== FALSE) {
214 
215  // convert SYLK encoded $rowData to UTF-8
216  $rowData = PHPExcel_Shared_String::SYLKtoUTF8($rowData);
217 
218  // explode each row at semicolons while taking into account that literal semicolon (;)
219  // is escaped like this (;;)
220  $rowData = explode("\t",str_replace('¤',';',str_replace(';',"\t",str_replace(';;','¤',rtrim($rowData)))));
221 
222  $dataType = array_shift($rowData);
223  // Read shared styles
224  if ($dataType == 'P') {
225  $formatArray = array();
226  foreach($rowData as $rowDatum) {
227  switch($rowDatum{0}) {
228  case 'P' : $formatArray['numberformat']['code'] = str_replace($fromFormats,$toFormats,substr($rowDatum,1));
229  break;
230  case 'E' :
231  case 'F' : $formatArray['font']['name'] = substr($rowDatum,1);
232  break;
233  case 'L' : $formatArray['font']['size'] = substr($rowDatum,1);
234  break;
235  case 'S' : $styleSettings = substr($rowDatum,1);
236  for ($i=0;$i<strlen($styleSettings);++$i) {
237  switch ($styleSettings{$i}) {
238  case 'I' : $formatArray['font']['italic'] = true;
239  break;
240  case 'D' : $formatArray['font']['bold'] = true;
241  break;
242  case 'T' : $formatArray['borders']['top']['style'] = PHPExcel_Style_Border::BORDER_THIN;
243  break;
244  case 'B' : $formatArray['borders']['bottom']['style'] = PHPExcel_Style_Border::BORDER_THIN;
245  break;
246  case 'L' : $formatArray['borders']['left']['style'] = PHPExcel_Style_Border::BORDER_THIN;
247  break;
248  case 'R' : $formatArray['borders']['right']['style'] = PHPExcel_Style_Border::BORDER_THIN;
249  break;
250  }
251  }
252  break;
253  }
254  }
255  $this->_formats['P'.$this->_format++] = $formatArray;
256  // Read cell value data
257  } elseif ($dataType == 'C') {
258  $hasCalculatedValue = false;
259  $cellData = $cellDataFormula = '';
260  foreach($rowData as $rowDatum) {
261  switch($rowDatum{0}) {
262  case 'C' :
263  case 'X' : $column = substr($rowDatum,1);
264  break;
265  case 'R' :
266  case 'Y' : $row = substr($rowDatum,1);
267  break;
268  case 'K' : $cellData = substr($rowDatum,1);
269  break;
270  case 'E' : $cellDataFormula = '='.substr($rowDatum,1);
271  // Convert R1C1 style references to A1 style references (but only when not quoted)
272  $temp = explode('"',$cellDataFormula);
273  $key = false;
274  foreach($temp as &$value) {
275  // Only count/replace in alternate array entries
276  if ($key = !$key) {
277  preg_match_all('/(R(\[?-?\d*\]?))(C(\[?-?\d*\]?))/',$value, $cellReferences,PREG_SET_ORDER+PREG_OFFSET_CAPTURE);
278  // Reverse the matches array, otherwise all our offsets will become incorrect if we modify our way
279  // through the formula from left to right. Reversing means that we work right to left.through
280  // the formula
281  $cellReferences = array_reverse($cellReferences);
282  // Loop through each R1C1 style reference in turn, converting it to its A1 style equivalent,
283  // then modify the formula to use that new reference
284  foreach($cellReferences as $cellReference) {
285  $rowReference = $cellReference[2][0];
286  // Empty R reference is the current row
287  if ($rowReference == '') $rowReference = $row;
288  // Bracketed R references are relative to the current row
289  if ($rowReference{0} == '[') $rowReference = $row + trim($rowReference,'[]');
290  $columnReference = $cellReference[4][0];
291  // Empty C reference is the current column
292  if ($columnReference == '') $columnReference = $column;
293  // Bracketed C references are relative to the current column
294  if ($columnReference{0} == '[') $columnReference = $column + trim($columnReference,'[]');
295  $A1CellReference = PHPExcel_Cell::stringFromColumnIndex($columnReference-1).$rowReference;
296 
297  $value = substr_replace($value,$A1CellReference,$cellReference[0][1],strlen($cellReference[0][0]));
298  }
299  }
300  }
301  unset($value);
302  // Then rebuild the formula string
303  $cellDataFormula = implode('"',$temp);
304  $hasCalculatedValue = true;
305  break;
306  }
307  }
308  $columnLetter = PHPExcel_Cell::stringFromColumnIndex($column-1);
309  $cellData = PHPExcel_Calculation::_unwrapResult($cellData);
310 
311  // Set cell value
312  $objPHPExcel->getActiveSheet()->getCell($columnLetter.$row)->setValue(($hasCalculatedValue) ? $cellDataFormula : $cellData);
313  if ($hasCalculatedValue) {
314  $cellData = PHPExcel_Calculation::_unwrapResult($cellData);
315  $objPHPExcel->getActiveSheet()->getCell($columnLetter.$row)->setCalculatedValue($cellData);
316  }
317  // Read cell formatting
318  } elseif ($dataType == 'F') {
319  $formatStyle = $columnWidth = $styleSettings = '';
320  $styleData = array();
321  foreach($rowData as $rowDatum) {
322  switch($rowDatum{0}) {
323  case 'C' :
324  case 'X' : $column = substr($rowDatum,1);
325  break;
326  case 'R' :
327  case 'Y' : $row = substr($rowDatum,1);
328  break;
329  case 'P' : $formatStyle = $rowDatum;
330  break;
331  case 'W' : list($startCol,$endCol,$columnWidth) = explode(' ',substr($rowDatum,1));
332  break;
333  case 'S' : $styleSettings = substr($rowDatum,1);
334  for ($i=0;$i<strlen($styleSettings);++$i) {
335  switch ($styleSettings{$i}) {
336  case 'I' : $styleData['font']['italic'] = true;
337  break;
338  case 'D' : $styleData['font']['bold'] = true;
339  break;
340  case 'T' : $styleData['borders']['top']['style'] = PHPExcel_Style_Border::BORDER_THIN;
341  break;
342  case 'B' : $styleData['borders']['bottom']['style'] = PHPExcel_Style_Border::BORDER_THIN;
343  break;
344  case 'L' : $styleData['borders']['left']['style'] = PHPExcel_Style_Border::BORDER_THIN;
345  break;
346  case 'R' : $styleData['borders']['right']['style'] = PHPExcel_Style_Border::BORDER_THIN;
347  break;
348  }
349  }
350  break;
351  }
352  }
353  if (($formatStyle > '') && ($column > '') && ($row > '')) {
354  $columnLetter = PHPExcel_Cell::stringFromColumnIndex($column-1);
355  $objPHPExcel->getActiveSheet()->getStyle($columnLetter.$row)->applyFromArray($this->_formats[$formatStyle]);
356  }
357  if ((count($styleData) > 0) && ($column > '') && ($row > '')) {
358  $columnLetter = PHPExcel_Cell::stringFromColumnIndex($column-1);
359  $objPHPExcel->getActiveSheet()->getStyle($columnLetter.$row)->applyFromArray($styleData);
360  }
361  if ($columnWidth > '') {
362  if ($startCol == $endCol) {
363  $startCol = PHPExcel_Cell::stringFromColumnIndex($startCol-1);
364  $objPHPExcel->getActiveSheet()->getColumnDimension($startCol)->setWidth($columnWidth);
365  } else {
366  $startCol = PHPExcel_Cell::stringFromColumnIndex($startCol-1);
367  $endCol = PHPExcel_Cell::stringFromColumnIndex($endCol-1);
368  $objPHPExcel->getActiveSheet()->getColumnDimension($startCol)->setWidth($columnWidth);
369  do {
370  $objPHPExcel->getActiveSheet()->getColumnDimension(++$startCol)->setWidth($columnWidth);
371  } while ($startCol != $endCol);
372  }
373  }
374  } else {
375  foreach($rowData as $rowDatum) {
376  switch($rowDatum{0}) {
377  case 'C' :
378  case 'X' : $column = substr($rowDatum,1);
379  break;
380  case 'R' :
381  case 'Y' : $row = substr($rowDatum,1);
382  break;
383  }
384  }
385  }
386  }
387 
388  // Close file
389  fclose($fileHandle);
390 
391  // Return
392  return $objPHPExcel;
393  }
394 
400  public function getSheetIndex() {
401  return $this->_sheetIndex;
402  }
403 
410  public function setSheetIndex($pValue = 0) {
411  $this->_sheetIndex = $pValue;
412  return $this;
413  }
414 
415 }