ILIAS  release_5-3 Revision v5.3.23-19-g915713cf615
Excel2003XML.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
46{
52 protected $_styles = array();
53
59 protected $_charSet = 'UTF-8';
60
61
65 public function __construct() {
66 $this->_readFilter = new PHPExcel_Reader_DefaultReadFilter();
67 }
68
69
77 public function canRead($pFilename)
78 {
79
80 // Office xmlns:o="urn:schemas-microsoft-com:office:office"
81 // Excel xmlns:x="urn:schemas-microsoft-com:office:excel"
82 // XML Spreadsheet xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
83 // Spreadsheet component xmlns:c="urn:schemas-microsoft-com:office:component:spreadsheet"
84 // XML schema xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882"
85 // XML data type xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882"
86 // MS-persist recordset xmlns:rs="urn:schemas-microsoft-com:rowset"
87 // Rowset xmlns:z="#RowsetSchema"
88 //
89
90 $signature = array(
91 '<?xml version="1.0"',
92 '<?mso-application progid="Excel.Sheet"?>'
93 );
94
95 // Open file
96 $this->_openFile($pFilename);
97 $fileHandle = $this->_fileHandle;
98
99 // Read sample data (first 2 KB will do)
100 $data = fread($fileHandle, 2048);
101 fclose($fileHandle);
102
103 $valid = true;
104 foreach($signature as $match) {
105 // every part of the signature must be present
106 if (strpos($data, $match) === false) {
107 $valid = false;
108 break;
109 }
110 }
111
112 // Retrieve charset encoding
113 if(preg_match('/<?xml.*encoding=[\'"](.*?)[\'"].*?>/um',$data,$matches)) {
114 $this->_charSet = strtoupper($matches[1]);
115 }
116// echo 'Character Set is ',$this->_charSet,'<br />';
117
118 return $valid;
119 }
120
121
128 public function listWorksheetNames($pFilename)
129 {
130 // Check if file exists
131 if (!file_exists($pFilename)) {
132 throw new PHPExcel_Reader_Exception("Could not open " . $pFilename . " for reading! File does not exist.");
133 }
134 if (!$this->canRead($pFilename)) {
135 throw new PHPExcel_Reader_Exception($pFilename . " is an Invalid Spreadsheet file.");
136 }
137
138 $worksheetNames = array();
139
140 $xml = simplexml_load_string($this->securityScan(file_get_contents($pFilename)), 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions());
141 $namespaces = $xml->getNamespaces(true);
142
143 $xml_ss = $xml->children($namespaces['ss']);
144 foreach($xml_ss->Worksheet as $worksheet) {
145 $worksheet_ss = $worksheet->attributes($namespaces['ss']);
146 $worksheetNames[] = self::_convertStringEncoding((string) $worksheet_ss['Name'],$this->_charSet);
147 }
148
149 return $worksheetNames;
150 }
151
152
159 public function listWorksheetInfo($pFilename)
160 {
161 // Check if file exists
162 if (!file_exists($pFilename)) {
163 throw new PHPExcel_Reader_Exception("Could not open " . $pFilename . " for reading! File does not exist.");
164 }
165
166 $worksheetInfo = array();
167
168 $xml = simplexml_load_string($this->securityScan(file_get_contents($pFilename)), 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions());
169 $namespaces = $xml->getNamespaces(true);
170
171 $worksheetID = 1;
172 $xml_ss = $xml->children($namespaces['ss']);
173 foreach($xml_ss->Worksheet as $worksheet) {
174 $worksheet_ss = $worksheet->attributes($namespaces['ss']);
175
176 $tmpInfo = array();
177 $tmpInfo['worksheetName'] = '';
178 $tmpInfo['lastColumnLetter'] = 'A';
179 $tmpInfo['lastColumnIndex'] = 0;
180 $tmpInfo['totalRows'] = 0;
181 $tmpInfo['totalColumns'] = 0;
182
183 if (isset($worksheet_ss['Name'])) {
184 $tmpInfo['worksheetName'] = (string) $worksheet_ss['Name'];
185 } else {
186 $tmpInfo['worksheetName'] = "Worksheet_{$worksheetID}";
187 }
188
189 if (isset($worksheet->Table->Row)) {
190 $rowIndex = 0;
191
192 foreach($worksheet->Table->Row as $rowData) {
193 $columnIndex = 0;
194 $rowHasData = false;
195
196 foreach($rowData->Cell as $cell) {
197 if (isset($cell->Data)) {
198 $tmpInfo['lastColumnIndex'] = max($tmpInfo['lastColumnIndex'], $columnIndex);
199 $rowHasData = true;
200 }
201
202 ++$columnIndex;
203 }
204
205 ++$rowIndex;
206
207 if ($rowHasData) {
208 $tmpInfo['totalRows'] = max($tmpInfo['totalRows'], $rowIndex);
209 }
210 }
211 }
212
213 $tmpInfo['lastColumnLetter'] = PHPExcel_Cell::stringFromColumnIndex($tmpInfo['lastColumnIndex']);
214 $tmpInfo['totalColumns'] = $tmpInfo['lastColumnIndex'] + 1;
215
216 $worksheetInfo[] = $tmpInfo;
217 ++$worksheetID;
218 }
219
220 return $worksheetInfo;
221 }
222
223
231 public function load($pFilename)
232 {
233 // Create new PHPExcel
234 $objPHPExcel = new PHPExcel();
235 $objPHPExcel->removeSheetByIndex(0);
236
237 // Load into this instance
238 return $this->loadIntoExisting($pFilename, $objPHPExcel);
239 }
240
241
242 protected static function identifyFixedStyleValue($styleList,&$styleAttributeValue) {
243 $styleAttributeValue = strtolower($styleAttributeValue);
244 foreach($styleList as $style) {
245 if ($styleAttributeValue == strtolower($style)) {
246 $styleAttributeValue = $style;
247 return true;
248 }
249 }
250 return false;
251 }
252
253
259 protected static function _pixel2WidthUnits($pxs) {
260 $UNIT_OFFSET_MAP = array(0, 36, 73, 109, 146, 182, 219);
261
262 $widthUnits = 256 * ($pxs / 7);
263 $widthUnits += $UNIT_OFFSET_MAP[($pxs % 7)];
264 return $widthUnits;
265 }
266
267
273 protected static function _widthUnits2Pixel($widthUnits) {
274 $pixels = ($widthUnits / 256) * 7;
275 $offsetWidthUnits = $widthUnits % 256;
276 $pixels += round($offsetWidthUnits / (256 / 7));
277 return $pixels;
278 }
279
280
281 protected static function _hex2str($hex) {
282 return chr(hexdec($hex[1]));
283 }
284
285
294 public function loadIntoExisting($pFilename, PHPExcel $objPHPExcel)
295 {
296 $fromFormats = array('\-', '\ ');
297 $toFormats = array('-', ' ');
298
299 $underlineStyles = array (
305 );
306 $verticalAlignmentStyles = array (
311 );
312 $horizontalAlignmentStyles = array (
319 );
320
321 $timezoneObj = new DateTimeZone('Europe/London');
322 $GMT = new DateTimeZone('UTC');
323
324
325 // Check if file exists
326 if (!file_exists($pFilename)) {
327 throw new PHPExcel_Reader_Exception("Could not open " . $pFilename . " for reading! File does not exist.");
328 }
329
330 if (!$this->canRead($pFilename)) {
331 throw new PHPExcel_Reader_Exception($pFilename . " is an Invalid Spreadsheet file.");
332 }
333
334 $xml = simplexml_load_string($this->securityScan(file_get_contents($pFilename)), 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions());
335 $namespaces = $xml->getNamespaces(true);
336
337 $docProps = $objPHPExcel->getProperties();
338 if (isset($xml->DocumentProperties[0])) {
339 foreach($xml->DocumentProperties[0] as $propertyName => $propertyValue) {
340 switch ($propertyName) {
341 case 'Title' :
342 $docProps->setTitle(self::_convertStringEncoding($propertyValue,$this->_charSet));
343 break;
344 case 'Subject' :
345 $docProps->setSubject(self::_convertStringEncoding($propertyValue,$this->_charSet));
346 break;
347 case 'Author' :
348 $docProps->setCreator(self::_convertStringEncoding($propertyValue,$this->_charSet));
349 break;
350 case 'Created' :
351 $creationDate = strtotime($propertyValue);
352 $docProps->setCreated($creationDate);
353 break;
354 case 'LastAuthor' :
355 $docProps->setLastModifiedBy(self::_convertStringEncoding($propertyValue,$this->_charSet));
356 break;
357 case 'LastSaved' :
358 $lastSaveDate = strtotime($propertyValue);
359 $docProps->setModified($lastSaveDate);
360 break;
361 case 'Company' :
362 $docProps->setCompany(self::_convertStringEncoding($propertyValue,$this->_charSet));
363 break;
364 case 'Category' :
365 $docProps->setCategory(self::_convertStringEncoding($propertyValue,$this->_charSet));
366 break;
367 case 'Manager' :
368 $docProps->setManager(self::_convertStringEncoding($propertyValue,$this->_charSet));
369 break;
370 case 'Keywords' :
371 $docProps->setKeywords(self::_convertStringEncoding($propertyValue,$this->_charSet));
372 break;
373 case 'Description' :
374 $docProps->setDescription(self::_convertStringEncoding($propertyValue,$this->_charSet));
375 break;
376 }
377 }
378 }
379 if (isset($xml->CustomDocumentProperties)) {
380 foreach($xml->CustomDocumentProperties[0] as $propertyName => $propertyValue) {
381 $propertyAttributes = $propertyValue->attributes($namespaces['dt']);
382 $propertyName = preg_replace_callback('/_x([0-9a-z]{4})_/','PHPExcel_Reader_Excel2003XML::_hex2str',$propertyName);
384 switch((string) $propertyAttributes) {
385 case 'string' :
387 $propertyValue = trim($propertyValue);
388 break;
389 case 'boolean' :
391 $propertyValue = (bool) $propertyValue;
392 break;
393 case 'integer' :
395 $propertyValue = intval($propertyValue);
396 break;
397 case 'float' :
399 $propertyValue = floatval($propertyValue);
400 break;
401 case 'dateTime.tz' :
403 $propertyValue = strtotime(trim($propertyValue));
404 break;
405 }
406 $docProps->setCustomProperty($propertyName,$propertyValue,$propertyType);
407 }
408 }
409
410 foreach($xml->Styles[0] as $style) {
411 $style_ss = $style->attributes($namespaces['ss']);
412 $styleID = (string) $style_ss['ID'];
413// echo 'Style ID = '.$styleID.'<br />';
414 if ($styleID == 'Default') {
415 $this->_styles['Default'] = array();
416 } else {
417 $this->_styles[$styleID] = $this->_styles['Default'];
418 }
419 foreach ($style as $styleType => $styleData) {
420 $styleAttributes = $styleData->attributes($namespaces['ss']);
421// echo $styleType.'<br />';
422 switch ($styleType) {
423 case 'Alignment' :
424 foreach($styleAttributes as $styleAttributeKey => $styleAttributeValue) {
425// echo $styleAttributeKey.' = '.$styleAttributeValue.'<br />';
426 $styleAttributeValue = (string) $styleAttributeValue;
427 switch ($styleAttributeKey) {
428 case 'Vertical' :
429 if (self::identifyFixedStyleValue($verticalAlignmentStyles,$styleAttributeValue)) {
430 $this->_styles[$styleID]['alignment']['vertical'] = $styleAttributeValue;
431 }
432 break;
433 case 'Horizontal' :
434 if (self::identifyFixedStyleValue($horizontalAlignmentStyles,$styleAttributeValue)) {
435 $this->_styles[$styleID]['alignment']['horizontal'] = $styleAttributeValue;
436 }
437 break;
438 case 'WrapText' :
439 $this->_styles[$styleID]['alignment']['wrap'] = true;
440 break;
441 }
442 }
443 break;
444 case 'Borders' :
445 foreach($styleData->Border as $borderStyle) {
446 $borderAttributes = $borderStyle->attributes($namespaces['ss']);
447 $thisBorder = array();
448 foreach($borderAttributes as $borderStyleKey => $borderStyleValue) {
449// echo $borderStyleKey.' = '.$borderStyleValue.'<br />';
450 switch ($borderStyleKey) {
451 case 'LineStyle' :
452 $thisBorder['style'] = PHPExcel_Style_Border::BORDER_MEDIUM;
453// $thisBorder['style'] = $borderStyleValue;
454 break;
455 case 'Weight' :
456// $thisBorder['style'] = $borderStyleValue;
457 break;
458 case 'Position' :
459 $borderPosition = strtolower($borderStyleValue);
460 break;
461 case 'Color' :
462 $borderColour = substr($borderStyleValue,1);
463 $thisBorder['color']['rgb'] = $borderColour;
464 break;
465 }
466 }
467 if (!empty($thisBorder)) {
468 if (($borderPosition == 'left') || ($borderPosition == 'right') || ($borderPosition == 'top') || ($borderPosition == 'bottom')) {
469 $this->_styles[$styleID]['borders'][$borderPosition] = $thisBorder;
470 }
471 }
472 }
473 break;
474 case 'Font' :
475 foreach($styleAttributes as $styleAttributeKey => $styleAttributeValue) {
476// echo $styleAttributeKey.' = '.$styleAttributeValue.'<br />';
477 $styleAttributeValue = (string) $styleAttributeValue;
478 switch ($styleAttributeKey) {
479 case 'FontName' :
480 $this->_styles[$styleID]['font']['name'] = $styleAttributeValue;
481 break;
482 case 'Size' :
483 $this->_styles[$styleID]['font']['size'] = $styleAttributeValue;
484 break;
485 case 'Color' :
486 $this->_styles[$styleID]['font']['color']['rgb'] = substr($styleAttributeValue,1);
487 break;
488 case 'Bold' :
489 $this->_styles[$styleID]['font']['bold'] = true;
490 break;
491 case 'Italic' :
492 $this->_styles[$styleID]['font']['italic'] = true;
493 break;
494 case 'Underline' :
495 if (self::identifyFixedStyleValue($underlineStyles,$styleAttributeValue)) {
496 $this->_styles[$styleID]['font']['underline'] = $styleAttributeValue;
497 }
498 break;
499 }
500 }
501 break;
502 case 'Interior' :
503 foreach($styleAttributes as $styleAttributeKey => $styleAttributeValue) {
504// echo $styleAttributeKey.' = '.$styleAttributeValue.'<br />';
505 switch ($styleAttributeKey) {
506 case 'Color' :
507 $this->_styles[$styleID]['fill']['color']['rgb'] = substr($styleAttributeValue,1);
508 break;
509 }
510 }
511 break;
512 case 'NumberFormat' :
513 foreach($styleAttributes as $styleAttributeKey => $styleAttributeValue) {
514// echo $styleAttributeKey.' = '.$styleAttributeValue.'<br />';
515 $styleAttributeValue = str_replace($fromFormats,$toFormats,$styleAttributeValue);
516 switch ($styleAttributeValue) {
517 case 'Short Date' :
518 $styleAttributeValue = 'dd/mm/yyyy';
519 break;
520 }
521 if ($styleAttributeValue > '') {
522 $this->_styles[$styleID]['numberformat']['code'] = $styleAttributeValue;
523 }
524 }
525 break;
526 case 'Protection' :
527 foreach($styleAttributes as $styleAttributeKey => $styleAttributeValue) {
528// echo $styleAttributeKey.' = '.$styleAttributeValue.'<br />';
529 }
530 break;
531 }
532 }
533// print_r($this->_styles[$styleID]);
534// echo '<hr />';
535 }
536// echo '<hr />';
537
538 $worksheetID = 0;
539 $xml_ss = $xml->children($namespaces['ss']);
540
541 foreach($xml_ss->Worksheet as $worksheet) {
542 $worksheet_ss = $worksheet->attributes($namespaces['ss']);
543
544 if ((isset($this->_loadSheetsOnly)) && (isset($worksheet_ss['Name'])) &&
545 (!in_array($worksheet_ss['Name'], $this->_loadSheetsOnly))) {
546 continue;
547 }
548
549// echo '<h3>Worksheet: ',$worksheet_ss['Name'],'<h3>';
550//
551 // Create new Worksheet
552 $objPHPExcel->createSheet();
553 $objPHPExcel->setActiveSheetIndex($worksheetID);
554 if (isset($worksheet_ss['Name'])) {
555 $worksheetName = self::_convertStringEncoding((string) $worksheet_ss['Name'],$this->_charSet);
556 // Use false for $updateFormulaCellReferences to prevent adjustment of worksheet references in
557 // formula cells... during the load, all formulae should be correct, and we're simply bringing
558 // the worksheet name in line with the formula, not the reverse
559 $objPHPExcel->getActiveSheet()->setTitle($worksheetName,false);
560 }
561
562 $columnID = 'A';
563 if (isset($worksheet->Table->Column)) {
564 foreach($worksheet->Table->Column as $columnData) {
565 $columnData_ss = $columnData->attributes($namespaces['ss']);
566 if (isset($columnData_ss['Index'])) {
567 $columnID = PHPExcel_Cell::stringFromColumnIndex($columnData_ss['Index']-1);
568 }
569 if (isset($columnData_ss['Width'])) {
570 $columnWidth = $columnData_ss['Width'];
571// echo '<b>Setting column width for '.$columnID.' to '.$columnWidth.'</b><br />';
572 $objPHPExcel->getActiveSheet()->getColumnDimension($columnID)->setWidth($columnWidth / 5.4);
573 }
574 ++$columnID;
575 }
576 }
577
578 $rowID = 1;
579 if (isset($worksheet->Table->Row)) {
580 $additionalMergedCells = 0;
581 foreach($worksheet->Table->Row as $rowData) {
582 $rowHasData = false;
583 $row_ss = $rowData->attributes($namespaces['ss']);
584 if (isset($row_ss['Index'])) {
585 $rowID = (integer) $row_ss['Index'];
586 }
587// echo '<b>Row '.$rowID.'</b><br />';
588
589 $columnID = 'A';
590 foreach($rowData->Cell as $cell) {
591
592 $cell_ss = $cell->attributes($namespaces['ss']);
593 if (isset($cell_ss['Index'])) {
594 $columnID = PHPExcel_Cell::stringFromColumnIndex($cell_ss['Index']-1);
595 }
596 $cellRange = $columnID.$rowID;
597
598 if ($this->getReadFilter() !== NULL) {
599 if (!$this->getReadFilter()->readCell($columnID, $rowID, $worksheetName)) {
600 continue;
601 }
602 }
603
604 if ((isset($cell_ss['MergeAcross'])) || (isset($cell_ss['MergeDown']))) {
605 $columnTo = $columnID;
606 if (isset($cell_ss['MergeAcross'])) {
607 $additionalMergedCells += (int)$cell_ss['MergeAcross'];
608 $columnTo = PHPExcel_Cell::stringFromColumnIndex(PHPExcel_Cell::columnIndexFromString($columnID) + $cell_ss['MergeAcross'] -1);
609 }
610 $rowTo = $rowID;
611 if (isset($cell_ss['MergeDown'])) {
612 $rowTo = $rowTo + $cell_ss['MergeDown'];
613 }
614 $cellRange .= ':'.$columnTo.$rowTo;
615 $objPHPExcel->getActiveSheet()->mergeCells($cellRange);
616 }
617
618 $cellIsSet = $hasCalculatedValue = false;
619 $cellDataFormula = '';
620 if (isset($cell_ss['Formula'])) {
621 $cellDataFormula = $cell_ss['Formula'];
622 // added this as a check for array formulas
623 if (isset($cell_ss['ArrayRange'])) {
624 $cellDataCSEFormula = $cell_ss['ArrayRange'];
625// echo "found an array formula at ".$columnID.$rowID."<br />";
626 }
627 $hasCalculatedValue = true;
628 }
629 if (isset($cell->Data)) {
630 $cellValue = $cellData = $cell->Data;
632 $cellData_ss = $cellData->attributes($namespaces['ss']);
633 if (isset($cellData_ss['Type'])) {
634 $cellDataType = $cellData_ss['Type'];
635 switch ($cellDataType) {
636 /*
637 const TYPE_STRING = 's';
638 const TYPE_FORMULA = 'f';
639 const TYPE_NUMERIC = 'n';
640 const TYPE_BOOL = 'b';
641 const TYPE_NULL = 'null';
642 const TYPE_INLINE = 'inlineStr';
643 const TYPE_ERROR = 'e';
644 */
645 case 'String' :
646 $cellValue = self::_convertStringEncoding($cellValue,$this->_charSet);
648 break;
649 case 'Number' :
651 $cellValue = (float) $cellValue;
652 if (floor($cellValue) == $cellValue) {
653 $cellValue = (integer) $cellValue;
654 }
655 break;
656 case 'Boolean' :
658 $cellValue = ($cellValue != 0);
659 break;
660 case 'DateTime' :
662 $cellValue = PHPExcel_Shared_Date::PHPToExcel(strtotime($cellValue));
663 break;
664 case 'Error' :
666 break;
667 }
668 }
669
670 if ($hasCalculatedValue) {
671// echo 'FORMULA<br />';
673 $columnNumber = PHPExcel_Cell::columnIndexFromString($columnID);
674 if (substr($cellDataFormula,0,3) == 'of:') {
675 $cellDataFormula = substr($cellDataFormula,3);
676// echo 'Before: ',$cellDataFormula,'<br />';
677 $temp = explode('"',$cellDataFormula);
678 $key = false;
679 foreach($temp as &$value) {
680 // Only replace in alternate array entries (i.e. non-quoted blocks)
681 if ($key = !$key) {
682 $value = str_replace(array('[.','.',']'),'',$value);
683 }
684 }
685 } else {
686 // Convert R1C1 style references to A1 style references (but only when not quoted)
687// echo 'Before: ',$cellDataFormula,'<br />';
688 $temp = explode('"',$cellDataFormula);
689 $key = false;
690 foreach($temp as &$value) {
691 // Only replace in alternate array entries (i.e. non-quoted blocks)
692 if ($key = !$key) {
693 preg_match_all('/(R(\[?-?\d*\]?))(C(\[?-?\d*\]?))/',$value, $cellReferences,PREG_SET_ORDER+PREG_OFFSET_CAPTURE);
694 // Reverse the matches array, otherwise all our offsets will become incorrect if we modify our way
695 // through the formula from left to right. Reversing means that we work right to left.through
696 // the formula
697 $cellReferences = array_reverse($cellReferences);
698 // Loop through each R1C1 style reference in turn, converting it to its A1 style equivalent,
699 // then modify the formula to use that new reference
700 foreach($cellReferences as $cellReference) {
701 $rowReference = $cellReference[2][0];
702 // Empty R reference is the current row
703 if ($rowReference == '') $rowReference = $rowID;
704 // Bracketed R references are relative to the current row
705 if ($rowReference{0} == '[') $rowReference = $rowID + trim($rowReference,'[]');
706 $columnReference = $cellReference[4][0];
707 // Empty C reference is the current column
708 if ($columnReference == '') $columnReference = $columnNumber;
709 // Bracketed C references are relative to the current column
710 if ($columnReference{0} == '[') $columnReference = $columnNumber + trim($columnReference,'[]');
711 $A1CellReference = PHPExcel_Cell::stringFromColumnIndex($columnReference-1).$rowReference;
712 $value = substr_replace($value,$A1CellReference,$cellReference[0][1],strlen($cellReference[0][0]));
713 }
714 }
715 }
716 }
717 unset($value);
718 // Then rebuild the formula string
719 $cellDataFormula = implode('"',$temp);
720// echo 'After: ',$cellDataFormula,'<br />';
721 }
722
723// echo 'Cell '.$columnID.$rowID.' is a '.$type.' with a value of '.(($hasCalculatedValue) ? $cellDataFormula : $cellValue).'<br />';
724//
725 $objPHPExcel->getActiveSheet()->getCell($columnID.$rowID)->setValueExplicit((($hasCalculatedValue) ? $cellDataFormula : $cellValue),$type);
726 if ($hasCalculatedValue) {
727// echo 'Formula result is '.$cellValue.'<br />';
728 $objPHPExcel->getActiveSheet()->getCell($columnID.$rowID)->setCalculatedValue($cellValue);
729 }
730 $cellIsSet = $rowHasData = true;
731 }
732
733 if (isset($cell->Comment)) {
734// echo '<b>comment found</b><br />';
735 $commentAttributes = $cell->Comment->attributes($namespaces['ss']);
736 $author = 'unknown';
737 if (isset($commentAttributes->Author)) {
738 $author = (string)$commentAttributes->Author;
739// echo 'Author: ',$author,'<br />';
740 }
741 $node = $cell->Comment->Data->asXML();
742// $annotation = str_replace('html:','',substr($node,49,-10));
743// echo $annotation,'<br />';
744 $annotation = strip_tags($node);
745// echo 'Annotation: ',$annotation,'<br />';
746 $objPHPExcel->getActiveSheet()->getComment( $columnID.$rowID )
747 ->setAuthor(self::_convertStringEncoding($author ,$this->_charSet))
748 ->setText($this->_parseRichText($annotation) );
749 }
750
751 if (($cellIsSet) && (isset($cell_ss['StyleID']))) {
752 $style = (string) $cell_ss['StyleID'];
753// echo 'Cell style for '.$columnID.$rowID.' is '.$style.'<br />';
754 if ((isset($this->_styles[$style])) && (!empty($this->_styles[$style]))) {
755// echo 'Cell '.$columnID.$rowID.'<br />';
756// print_r($this->_styles[$style]);
757// echo '<br />';
758 if (!$objPHPExcel->getActiveSheet()->cellExists($columnID.$rowID)) {
759 $objPHPExcel->getActiveSheet()->getCell($columnID.$rowID)->setValue(NULL);
760 }
761 $objPHPExcel->getActiveSheet()->getStyle($cellRange)->applyFromArray($this->_styles[$style]);
762 }
763 }
764 ++$columnID;
765 while ($additionalMergedCells > 0) {
766 ++$columnID;
767 $additionalMergedCells--;
768 }
769 }
770
771 if ($rowHasData) {
772 if (isset($row_ss['StyleID'])) {
773 $rowStyle = $row_ss['StyleID'];
774 }
775 if (isset($row_ss['Height'])) {
776 $rowHeight = $row_ss['Height'];
777// echo '<b>Setting row height to '.$rowHeight.'</b><br />';
778 $objPHPExcel->getActiveSheet()->getRowDimension($rowID)->setRowHeight($rowHeight);
779 }
780 }
781
782 ++$rowID;
783 }
784 }
785 ++$worksheetID;
786 }
787
788 // Return
789 return $objPHPExcel;
790 }
791
792
793 protected static function _convertStringEncoding($string,$charset) {
794 if ($charset != 'UTF-8') {
795 return PHPExcel_Shared_String::ConvertEncoding($string,'UTF-8',$charset);
796 }
797 return $string;
798 }
799
800
801 protected function _parseRichText($is = '') {
802 $value = new PHPExcel_RichText();
803
804 $value->createText(self::_convertStringEncoding($is,$this->_charSet));
805
806 return $value;
807 }
808
809}
$objPHPExcel
$worksheet
An exception for terminatinating execution or to throw for unit testing.
static stringFromColumnIndex($pColumnIndex=0)
String from columnindex.
Definition: Cell.php:825
static columnIndexFromString($pString='A')
Column index from string.
Definition: Cell.php:782
securityScan($xml)
Scan theXML for use of <!ENTITY to prevent XXE/XEE attacks.
Definition: Abstract.php:236
getReadFilter()
Read filter.
Definition: Abstract.php:173
_openFile($pFilename)
Open file for reading.
Definition: Abstract.php:195
static _convertStringEncoding($string, $charset)
static _pixel2WidthUnits($pxs)
pixel units to excel width units(units of 1/256th of a character width)
static _widthUnits2Pixel($widthUnits)
excel width units(units of 1/256th of a character width) to pixel units
__construct()
Create a new PHPExcel_Reader_Excel2003XML.
static identifyFixedStyleValue($styleList, &$styleAttributeValue)
listWorksheetNames($pFilename)
Reads names of the worksheets from a file, without parsing the whole file to a PHPExcel object.
loadIntoExisting($pFilename, PHPExcel $objPHPExcel)
Loads PHPExcel from file into PHPExcel instance.
listWorksheetInfo($pFilename)
Return worksheet info (Name, Last Column Letter, Last Column Index, Total Rows, Total Columns)
canRead($pFilename)
Can the current PHPExcel_Reader_IReader read the file?
load($pFilename)
Loads PHPExcel from file.
static getLibXmlLoaderOptions()
Get default options for libxml loader.
Definition: Settings.php:381
static PHPToExcel($dateValue=0, $adjustToTimezone=FALSE, $timezone=NULL)
Convert a date from PHP to Excel.
Definition: Date.php:185
static ConvertEncoding($value, $to, $from)
Convert string from one encoding to another.
Definition: String.php:493
const HORIZONTAL_CENTER_CONTINUOUS
Definition: Alignment.php:43
const UNDERLINE_DOUBLE
Definition: Font.php:40
const UNDERLINE_SINGLEACCOUNTING
Definition: Font.php:43
const UNDERLINE_DOUBLEACCOUNTING
Definition: Font.php:41
const UNDERLINE_SINGLE
Definition: Font.php:42
const UNDERLINE_NONE
Definition: Font.php:39
$key
Definition: croninfo.php:18
$valid
$style
Definition: example_012.php:70
$xml
Definition: metadata.php:240
defined( 'APPLICATION_ENV')||define( 'APPLICATION_ENV'
Definition: bootstrap.php:27
$type