ILIAS  eassessment Revision 61809
 All Data Structures Namespaces Files Functions Variables Groups Pages
OOCalc.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_OOCalc implements PHPExcel_Reader_IReader
46 {
54  private $_readDataOnly = false;
55 
62  private $_loadSheetsOnly = null;
63 
69  private $_styles = array();
70 
76  private $_readFilter = null;
77 
78 
86  public function getReadDataOnly() {
87  return $this->_readDataOnly;
88  }
89 
99  public function setReadDataOnly($pValue = false) {
100  $this->_readDataOnly = $pValue;
101  return $this;
102  }
103 
111  public function getLoadSheetsOnly()
112  {
113  return $this->_loadSheetsOnly;
114  }
115 
125  public function setLoadSheetsOnly($value = null)
126  {
127  $this->_loadSheetsOnly = is_array($value) ?
128  $value : array($value);
129  return $this;
130  }
131 
138  public function setLoadAllSheets()
139  {
140  $this->_loadSheetsOnly = null;
141  return $this;
142  }
143 
149  public function getReadFilter() {
150  return $this->_readFilter;
151  }
152 
159  public function setReadFilter(PHPExcel_Reader_IReadFilter $pValue) {
160  $this->_readFilter = $pValue;
161  return $this;
162  }
163 
167  public function __construct() {
168  $this->_readFilter = new PHPExcel_Reader_DefaultReadFilter();
169  }
170 
177  public function canRead($pFilename)
178  {
179  // Check if zip class exists
180  if (!class_exists('ZipArchive')) {
181  return false;
182  }
183 
184  // Check if file exists
185  if (!file_exists($pFilename)) {
186  throw new Exception("Could not open " . $pFilename . " for reading! File does not exist.");
187  }
188 
189  // Load file
190  $zip = new ZipArchive;
191  if ($zip->open($pFilename) === true) {
192  // check if it is an OOXML archive
193  $mimeType = $zip->getFromName("mimetype");
194 
195  $zip->close();
196 
197  return ($mimeType === 'application/vnd.oasis.opendocument.spreadsheet');
198  }
199 
200  return false;
201  }
202 
209  public function listWorksheetNames($pFilename)
210  {
211  // Check if file exists
212  if (!file_exists($pFilename)) {
213  throw new Exception("Could not open " . $pFilename . " for reading! File does not exist.");
214  }
215 
216  $worksheetNames = array();
217 
218  $zip = new ZipArchive;
219  if ($zip->open($pFilename) === true) {
220 
221  $xml = simplexml_load_string($zip->getFromName("content.xml"));
222  $namespacesContent = $xml->getNamespaces(true);
223 
224  $workbook = $xml->children($namespacesContent['office']);
225  foreach($workbook->body->spreadsheet as $workbookData) {
226  $workbookData = $workbookData->children($namespacesContent['table']);
227  foreach($workbookData->table as $worksheetDataSet) {
228  $worksheetDataAttributes = $worksheetDataSet->attributes($namespacesContent['table']);
229 
230  $worksheetNames[] = $worksheetDataAttributes['name'];
231  }
232  }
233  }
234 
235  return $worksheetNames;
236  }
237 
238 
246  public function load($pFilename)
247  {
248  // Create new PHPExcel
249  $objPHPExcel = new PHPExcel();
250 
251  // Load into this instance
252  return $this->loadIntoExisting($pFilename, $objPHPExcel);
253  }
254 
255  private static function identifyFixedStyleValue($styleList,&$styleAttributeValue) {
256  $styleAttributeValue = strtolower($styleAttributeValue);
257  foreach($styleList as $style) {
258  if ($styleAttributeValue == strtolower($style)) {
259  $styleAttributeValue = $style;
260  return true;
261  }
262  }
263  return false;
264  }
265 
274  public function loadIntoExisting($pFilename, PHPExcel $objPHPExcel)
275  {
276  // Check if file exists
277  if (!file_exists($pFilename)) {
278  throw new Exception("Could not open " . $pFilename . " for reading! File does not exist.");
279  }
280 
281  $timezoneObj = new DateTimeZone('Europe/London');
282  $GMT = new DateTimeZone('UTC');
283 
284  $zip = new ZipArchive;
285  if ($zip->open($pFilename) === true) {
286 // echo '<h1>Meta Information</h1>';
287  $xml = simplexml_load_string($zip->getFromName("meta.xml"));
288  $namespacesMeta = $xml->getNamespaces(true);
289 // echo '<pre>';
290 // print_r($namespacesMeta);
291 // echo '</pre><hr />';
292 
293  $docProps = $objPHPExcel->getProperties();
294  $officeProperty = $xml->children($namespacesMeta['office']);
295  foreach($officeProperty as $officePropertyData) {
296  $officePropertyDC = array();
297  if (isset($namespacesMeta['dc'])) {
298  $officePropertyDC = $officePropertyData->children($namespacesMeta['dc']);
299  }
300  foreach($officePropertyDC as $propertyName => $propertyValue) {
301  switch ($propertyName) {
302  case 'title' :
303  $docProps->setTitle($propertyValue);
304  break;
305  case 'subject' :
306  $docProps->setSubject($propertyValue);
307  break;
308  case 'creator' :
309  $docProps->setCreator($propertyValue);
310  $docProps->setLastModifiedBy($propertyValue);
311  break;
312  case 'date' :
313  $creationDate = strtotime($propertyValue);
314  $docProps->setCreated($creationDate);
315  $docProps->setModified($creationDate);
316  break;
317  case 'description' :
318  $docProps->setDescription($propertyValue);
319  break;
320  }
321  }
322  $officePropertyMeta = array();
323  if (isset($namespacesMeta['dc'])) {
324  $officePropertyMeta = $officePropertyData->children($namespacesMeta['meta']);
325  }
326  foreach($officePropertyMeta as $propertyName => $propertyValue) {
327  $propertyValueAttributes = $propertyValue->attributes($namespacesMeta['meta']);
328  switch ($propertyName) {
329  case 'initial-creator' :
330  $docProps->setCreator($propertyValue);
331  break;
332  case 'keyword' :
333  $docProps->setKeywords($propertyValue);
334  break;
335  case 'creation-date' :
336  $creationDate = strtotime($propertyValue);
337  $docProps->setCreated($creationDate);
338  break;
339  case 'user-defined' :
340  $propertyValueType = PHPExcel_DocumentProperties::PROPERTY_TYPE_STRING;
341  foreach ($propertyValueAttributes as $key => $value) {
342  if ($key == 'name') {
343  $propertyValueName = (string) $value;
344  } elseif($key == 'value-type') {
345  switch ($value) {
346  case 'date' :
347  $propertyValue = PHPExcel_DocumentProperties::convertProperty($propertyValue,'date');
348  $propertyValueType = PHPExcel_DocumentProperties::PROPERTY_TYPE_DATE;
349  break;
350  case 'boolean' :
351  $propertyValue = PHPExcel_DocumentProperties::convertProperty($propertyValue,'bool');
352  $propertyValueType = PHPExcel_DocumentProperties::PROPERTY_TYPE_BOOLEAN;
353  break;
354  case 'float' :
355  $propertyValue = PHPExcel_DocumentProperties::convertProperty($propertyValue,'r4');
356  $propertyValueType = PHPExcel_DocumentProperties::PROPERTY_TYPE_FLOAT;
357  break;
358  default :
359  $propertyValueType = PHPExcel_DocumentProperties::PROPERTY_TYPE_STRING;
360  }
361  }
362  }
363  $docProps->setCustomProperty($propertyValueName,$propertyValue,$propertyValueType);
364  break;
365  }
366  }
367  }
368 
369 
370 // echo '<h1>Workbook Content</h1>';
371  $xml = simplexml_load_string($zip->getFromName("content.xml"));
372  $namespacesContent = $xml->getNamespaces(true);
373 // echo '<pre>';
374 // print_r($namespacesContent);
375 // echo '</pre><hr />';
376 
377  $workbook = $xml->children($namespacesContent['office']);
378  foreach($workbook->body->spreadsheet as $workbookData) {
379  $workbookData = $workbookData->children($namespacesContent['table']);
380  $worksheetID = 0;
381  foreach($workbookData->table as $worksheetDataSet) {
382  $worksheetData = $worksheetDataSet->children($namespacesContent['table']);
383 // print_r($worksheetData);
384 // echo '<br />';
385  $worksheetDataAttributes = $worksheetDataSet->attributes($namespacesContent['table']);
386 // print_r($worksheetDataAttributes);
387 // echo '<br />';
388  if ((isset($this->_loadSheetsOnly)) && (isset($worksheetDataAttributes['name'])) &&
389  (!in_array($worksheetDataAttributes['name'], $this->_loadSheetsOnly))) {
390  continue;
391  }
392 
393 // echo '<h2>Worksheet '.$worksheetDataAttributes['name'].'</h2>';
394  // Create new Worksheet
395  $objPHPExcel->createSheet();
396  $objPHPExcel->setActiveSheetIndex($worksheetID);
397  if (isset($worksheetDataAttributes['name'])) {
398  $worksheetName = (string) $worksheetDataAttributes['name'];
399  $objPHPExcel->getActiveSheet()->setTitle($worksheetName);
400  }
401 
402  $rowID = 1;
403  foreach($worksheetData as $key => $rowData) {
404 // echo '<b>'.$key.'</b><br />';
405  switch ($key) {
406  case 'table-header-rows':
407  foreach ($rowData as $key=>$cellData) {
408  $rowData = $cellData;
409  break;
410  }
411  case 'table-row' :
412  $columnID = 'A';
413  foreach($rowData as $key => $cellData) {
414  if (!is_null($this->getReadFilter())) {
415  if (!$this->getReadFilter()->readCell($columnID, $rowID, $worksheetName)) {
416  continue;
417  }
418  }
419 
420 // echo '<b>'.$columnID.$rowID.'</b><br />';
421  $cellDataText = $cellData->children($namespacesContent['text']);
422  $cellDataOffice = $cellData->children($namespacesContent['office']);
423  $cellDataOfficeAttributes = $cellData->attributes($namespacesContent['office']);
424  $cellDataTableAttributes = $cellData->attributes($namespacesContent['table']);
425 
426 // echo 'Office Attributes: ';
427 // print_r($cellDataOfficeAttributes);
428 // echo '<br />Table Attributes: ';
429 // print_r($cellDataTableAttributes);
430 // echo '<br />Cell Data Text';
431 // print_r($cellDataText);
432 // echo '<br />';
433 //
434  $type = $formatting = $hyperlink = null;
435  $hasCalculatedValue = false;
436  $cellDataFormula = '';
437  if (isset($cellDataTableAttributes['formula'])) {
438  $cellDataFormula = $cellDataTableAttributes['formula'];
439  $hasCalculatedValue = true;
440  }
441 
442  if (isset($cellDataOffice->annotation)) {
443 // echo 'Cell has comment<br />';
444  $annotationText = $cellDataOffice->annotation->children($namespacesContent['text']);
445  $textArray = array();
446  foreach($annotationText as $t) {
447  foreach($t->span as $text) {
448  $textArray[] = (string)$text;
449  }
450  }
451  $text = implode("\n",$textArray);
452 // echo $text,'<br />';
453  $objPHPExcel->getActiveSheet()->getComment( $columnID.$rowID )
454 // ->setAuthor( $author )
455  ->setText($this->_parseRichText($text) );
456  }
457 
458  if (isset($cellDataText->p)) {
459 // echo 'Value Type is '.$cellDataOfficeAttributes['value-type'].'<br />';
460  switch ($cellDataOfficeAttributes['value-type']) {
461  case 'string' :
462  $type = PHPExcel_Cell_DataType::TYPE_STRING;
463  $dataValue = $cellDataText->p;
464  if (isset($dataValue->a)) {
465  $dataValue = $dataValue->a;
466  $cellXLinkAttributes = $dataValue->attributes($namespacesContent['xlink']);
467  $hyperlink = $cellXLinkAttributes['href'];
468  }
469  break;
470  case 'boolean' :
471  $type = PHPExcel_Cell_DataType::TYPE_BOOL;
472  $dataValue = ($cellDataText->p == 'TRUE') ? True : False;
473  break;
474  case 'float' :
475  $type = PHPExcel_Cell_DataType::TYPE_NUMERIC;
476  $dataValue = (float) $cellDataOfficeAttributes['value'];
477  if (floor($dataValue) == $dataValue) {
478  $dataValue = (integer) $dataValue;
479  }
480  break;
481  case 'date' :
482  $type = PHPExcel_Cell_DataType::TYPE_NUMERIC;
483  $dateObj = new DateTime($cellDataOfficeAttributes['date-value'], $GMT);
484  $dateObj->setTimeZone($timezoneObj);
485  list($year,$month,$day,$hour,$minute,$second) = explode(' ',$dateObj->format('Y m d H i s'));
486  $dataValue = PHPExcel_Shared_Date::FormattedPHPToExcel($year,$month,$day,$hour,$minute,$second);
487  if ($dataValue != floor($dataValue)) {
488  $formatting = PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX15.' '.PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME4;
489  } else {
490  $formatting = PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX15;
491  }
492  break;
493  case 'time' :
494  $type = PHPExcel_Cell_DataType::TYPE_NUMERIC;
495  $dataValue = PHPExcel_Shared_Date::PHPToExcel(strtotime('01-01-1970 '.implode(':',sscanf($cellDataOfficeAttributes['time-value'],'PT%dH%dM%dS'))));
496  $formatting = PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME4;
497  break;
498  }
499 // echo 'Data value is '.$dataValue.'<br />';
500 // if (!is_null($hyperlink)) {
501 // echo 'Hyperlink is '.$hyperlink.'<br />';
502 // }
503  }
504 
505  if ($hasCalculatedValue) {
506  $type = PHPExcel_Cell_DataType::TYPE_FORMULA;
507 // echo 'Formula: '.$cellDataFormula.'<br />';
508  $cellDataFormula = substr($cellDataFormula,strpos($cellDataFormula,':=')+1);
509  $temp = explode('"',$cellDataFormula);
510  $tKey = false;
511  foreach($temp as &$value) {
512  // Only replace in alternate array entries (i.e. non-quoted blocks)
513  if ($tKey = !$tKey) {
514  $value = preg_replace('/\[\.(.*):\.(.*)\]/Ui','$1:$2',$value);
515  $value = preg_replace('/\[\.(.*)\]/Ui','$1',$value);
516  $value = PHPExcel_Calculation::_translateSeparator(';',',',$value,$inBraces);
517  }
518  }
519  unset($value);
520  // Then rebuild the formula string
521  $cellDataFormula = implode('"',$temp);
522 // echo 'Adjusted Formula: '.$cellDataFormula.'<br />';
523  }
524 
525  if (!is_null($type)) {
526  $objPHPExcel->getActiveSheet()->getCell($columnID.$rowID)->setValueExplicit((($hasCalculatedValue) ? $cellDataFormula : $dataValue),$type);
527  if ($hasCalculatedValue) {
528 // echo 'Forumla result is '.$dataValue.'<br />';
529  $objPHPExcel->getActiveSheet()->getCell($columnID.$rowID)->setCalculatedValue($dataValue);
530  }
531  if (($cellDataOfficeAttributes['value-type'] == 'date') ||
532  ($cellDataOfficeAttributes['value-type'] == 'time')) {
533  $objPHPExcel->getActiveSheet()->getStyle($columnID.$rowID)->getNumberFormat()->setFormatCode($formatting);
534  }
535  if (!is_null($hyperlink)) {
536  $objPHPExcel->getActiveSheet()->getCell($columnID.$rowID)->getHyperlink()->setUrl($hyperlink);
537  }
538  }
539 
540  // Merged cells
541  if ((isset($cellDataTableAttributes['number-columns-spanned'])) || (isset($cellDataTableAttributes['number-rows-spanned']))) {
542  $columnTo = $columnID;
543  if (isset($cellDataTableAttributes['number-columns-spanned'])) {
544  $columnTo = PHPExcel_Cell::stringFromColumnIndex(PHPExcel_Cell::columnIndexFromString($columnID) + $cellDataTableAttributes['number-columns-spanned'] -2);
545  }
546  $rowTo = $rowID;
547  if (isset($cellDataTableAttributes['number-rows-spanned'])) {
548  $rowTo = $rowTo + $cellDataTableAttributes['number-rows-spanned'] - 1;
549  }
550  $cellRange = $columnID.$rowID.':'.$columnTo.$rowTo;
551  $objPHPExcel->getActiveSheet()->mergeCells($cellRange);
552  }
553 
554  if (isset($cellDataTableAttributes['number-columns-repeated'])) {
555 // echo 'Repeated '.$cellDataTableAttributes['number-columns-repeated'].' times<br />';
556  $columnID = PHPExcel_Cell::stringFromColumnIndex(PHPExcel_Cell::columnIndexFromString($columnID) + $cellDataTableAttributes['number-columns-repeated'] - 2);
557  }
558  ++$columnID;
559  }
560  ++$rowID;
561  break;
562  }
563  }
564  ++$worksheetID;
565  }
566  }
567 
568  }
569 
570  // Return
571  return $objPHPExcel;
572  }
573 
574  private function _parseRichText($is = '') {
575  $value = new PHPExcel_RichText();
576 
577  $value->createText($is);
578 
579  return $value;
580  }
581 
582 }