1: <?php
  2: /**
  3:  * PHPExcel
  4:  *
  5:  * Copyright (c) 2006 - 2014 PHPExcel
  6:  *
  7:  * This library is free software; you can redistribute it and/or
  8:  * modify it under the terms of the GNU Lesser General Public
  9:  * License as published by the Free Software Foundation; either
 10:  * version 2.1 of the License, or (at your option) any later version.
 11:  *
 12:  * This library is distributed in the hope that it will be useful,
 13:  * but WITHOUT ANY WARRANTY; without even the implied warranty of
 14:  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 15:  * Lesser General Public License for more details.
 16:  *
 17:  * You should have received a copy of the GNU Lesser General Public
 18:  * License along with this library; if not, write to the Free Software
 19:  * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
 20:  *
 21:  * @category   PHPExcel
 22:  * @package    PHPExcel_Reader
 23:  * @copyright  Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel)
 24:  * @license    http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt    LGPL
 25:  * @version    1.8.0, 2014-03-02
 26:  */
 27: 
 28: 
 29: /** PHPExcel root directory */
 30: if (!defined('PHPEXCEL_ROOT')) {
 31:     /**
 32:      * @ignore
 33:      */
 34:     define('PHPEXCEL_ROOT', dirname(__FILE__) . '/../../');
 35:     require(PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php');
 36: }
 37: 
 38: /**
 39:  * PHPExcel_Reader_Excel2003XML
 40:  *
 41:  * @category   PHPExcel
 42:  * @package    PHPExcel_Reader
 43:  * @copyright  Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel)
 44:  */
 45: class PHPExcel_Reader_Excel2003XML extends PHPExcel_Reader_Abstract implements PHPExcel_Reader_IReader
 46: {
 47:     /**
 48:      * Formats
 49:      *
 50:      * @var array
 51:      */
 52:     private $_styles = array();
 53: 
 54:     /**
 55:      * Character set used in the file
 56:      *
 57:      * @var string
 58:      */
 59:     private $_charSet = 'UTF-8';
 60: 
 61: 
 62:     /**
 63:      * Create a new PHPExcel_Reader_Excel2003XML
 64:      */
 65:     public function __construct() {
 66:         $this->_readFilter  = new PHPExcel_Reader_DefaultReadFilter();
 67:     }
 68: 
 69: 
 70:     /**
 71:      * Can the current PHPExcel_Reader_IReader read the file?
 72:      *
 73:      * @param   string      $pFilename
 74:      * @return  boolean
 75:      * @throws PHPExcel_Reader_Exception
 76:      */
 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: 
122:     /**
123:      * Reads names of the worksheets from a file, without parsing the whole file to a PHPExcel object
124:      *
125:      * @param   string      $pFilename
126:      * @throws  PHPExcel_Reader_Exception
127:      */
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_file($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: 
153:     /**
154:      * Return worksheet info (Name, Last Column Letter, Last Column Index, Total Rows, Total Columns)
155:      *
156:      * @param   string     $pFilename
157:      * @throws   PHPExcel_Reader_Exception
158:      */
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_file($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: 
224:     /**
225:      * Loads PHPExcel from file
226:      *
227:      * @param   string      $pFilename
228:      * @return  PHPExcel
229:      * @throws  PHPExcel_Reader_Exception
230:      */
231:     public function load($pFilename)
232:     {
233:         // Create new PHPExcel
234:         $objPHPExcel = new PHPExcel();
235: 
236:         // Load into this instance
237:         return $this->loadIntoExisting($pFilename, $objPHPExcel);
238:     }
239: 
240: 
241:     private static function identifyFixedStyleValue($styleList,&$styleAttributeValue) {
242:         $styleAttributeValue = strtolower($styleAttributeValue);
243:         foreach($styleList as $style) {
244:             if ($styleAttributeValue == strtolower($style)) {
245:                 $styleAttributeValue = $style;
246:                 return true;
247:             }
248:         }
249:         return false;
250:     }
251: 
252: 
253:     /**
254:      * pixel units to excel width units(units of 1/256th of a character width)
255:      * @param pxs
256:      * @return
257:      */
258:     private static function _pixel2WidthUnits($pxs) {
259:         $UNIT_OFFSET_MAP = array(0, 36, 73, 109, 146, 182, 219);
260: 
261:         $widthUnits = 256 * ($pxs / 7);
262:         $widthUnits += $UNIT_OFFSET_MAP[($pxs % 7)];
263:         return $widthUnits;
264:     }
265: 
266: 
267:     /**
268:      * excel width units(units of 1/256th of a character width) to pixel units
269:      * @param widthUnits
270:      * @return
271:      */
272:     private static function _widthUnits2Pixel($widthUnits) {
273:         $pixels = ($widthUnits / 256) * 7;
274:         $offsetWidthUnits = $widthUnits % 256;
275:         $pixels += round($offsetWidthUnits / (256 / 7));
276:         return $pixels;
277:     }
278: 
279: 
280:     private static function _hex2str($hex) {
281:         return chr(hexdec($hex[1]));
282:     }
283: 
284: 
285:     /**
286:      * Loads PHPExcel from file into PHPExcel instance
287:      *
288:      * @param   string      $pFilename
289:      * @param   PHPExcel    $objPHPExcel
290:      * @return  PHPExcel
291:      * @throws  PHPExcel_Reader_Exception
292:      */
293:     public function loadIntoExisting($pFilename, PHPExcel $objPHPExcel)
294:     {
295:         $fromFormats    = array('\-',   '\ ');
296:         $toFormats      = array('-',    ' ');
297: 
298:         $underlineStyles = array (
299:                 PHPExcel_Style_Font::UNDERLINE_NONE,
300:                 PHPExcel_Style_Font::UNDERLINE_DOUBLE,
301:                 PHPExcel_Style_Font::UNDERLINE_DOUBLEACCOUNTING,
302:                 PHPExcel_Style_Font::UNDERLINE_SINGLE,
303:                 PHPExcel_Style_Font::UNDERLINE_SINGLEACCOUNTING
304:             );
305:         $verticalAlignmentStyles = array (
306:                 PHPExcel_Style_Alignment::VERTICAL_BOTTOM,
307:                 PHPExcel_Style_Alignment::VERTICAL_TOP,
308:                 PHPExcel_Style_Alignment::VERTICAL_CENTER,
309:                 PHPExcel_Style_Alignment::VERTICAL_JUSTIFY
310:             );
311:         $horizontalAlignmentStyles = array (
312:                 PHPExcel_Style_Alignment::HORIZONTAL_GENERAL,
313:                 PHPExcel_Style_Alignment::HORIZONTAL_LEFT,
314:                 PHPExcel_Style_Alignment::HORIZONTAL_RIGHT,
315:                 PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
316:                 PHPExcel_Style_Alignment::HORIZONTAL_CENTER_CONTINUOUS,
317:                 PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY
318:             );
319: 
320:         $timezoneObj = new DateTimeZone('Europe/London');
321:         $GMT = new DateTimeZone('UTC');
322: 
323: 
324:         // Check if file exists
325:         if (!file_exists($pFilename)) {
326:             throw new PHPExcel_Reader_Exception("Could not open " . $pFilename . " for reading! File does not exist.");
327:         }
328: 
329:         if (!$this->canRead($pFilename)) {
330:             throw new PHPExcel_Reader_Exception($pFilename . " is an Invalid Spreadsheet file.");
331:         }
332: 
333:         $xml = simplexml_load_file($pFilename, 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions());
334:         $namespaces = $xml->getNamespaces(true);
335: 
336:         $docProps = $objPHPExcel->getProperties();
337:         if (isset($xml->DocumentProperties[0])) {
338:             foreach($xml->DocumentProperties[0] as $propertyName => $propertyValue) {
339:                 switch ($propertyName) {
340:                     case 'Title' :
341:                             $docProps->setTitle(self::_convertStringEncoding($propertyValue,$this->_charSet));
342:                             break;
343:                     case 'Subject' :
344:                             $docProps->setSubject(self::_convertStringEncoding($propertyValue,$this->_charSet));
345:                             break;
346:                     case 'Author' :
347:                             $docProps->setCreator(self::_convertStringEncoding($propertyValue,$this->_charSet));
348:                             break;
349:                     case 'Created' :
350:                             $creationDate = strtotime($propertyValue);
351:                             $docProps->setCreated($creationDate);
352:                             break;
353:                     case 'LastAuthor' :
354:                             $docProps->setLastModifiedBy(self::_convertStringEncoding($propertyValue,$this->_charSet));
355:                             break;
356:                     case 'LastSaved' :
357:                             $lastSaveDate = strtotime($propertyValue);
358:                             $docProps->setModified($lastSaveDate);
359:                             break;
360:                     case 'Company' :
361:                             $docProps->setCompany(self::_convertStringEncoding($propertyValue,$this->_charSet));
362:                             break;
363:                     case 'Category' :
364:                             $docProps->setCategory(self::_convertStringEncoding($propertyValue,$this->_charSet));
365:                             break;
366:                     case 'Manager' :
367:                             $docProps->setManager(self::_convertStringEncoding($propertyValue,$this->_charSet));
368:                             break;
369:                     case 'Keywords' :
370:                             $docProps->setKeywords(self::_convertStringEncoding($propertyValue,$this->_charSet));
371:                             break;
372:                     case 'Description' :
373:                             $docProps->setDescription(self::_convertStringEncoding($propertyValue,$this->_charSet));
374:                             break;
375:                 }
376:             }
377:         }
378:         if (isset($xml->CustomDocumentProperties)) {
379:             foreach($xml->CustomDocumentProperties[0] as $propertyName => $propertyValue) {
380:                 $propertyAttributes = $propertyValue->attributes($namespaces['dt']);
381:                 $propertyName = preg_replace_callback('/_x([0-9a-z]{4})_/','PHPExcel_Reader_Excel2003XML::_hex2str',$propertyName);
382:                 $propertyType = PHPExcel_DocumentProperties::PROPERTY_TYPE_UNKNOWN;
383:                 switch((string) $propertyAttributes) {
384:                     case 'string' :
385:                         $propertyType = PHPExcel_DocumentProperties::PROPERTY_TYPE_STRING;
386:                         $propertyValue = trim($propertyValue);
387:                         break;
388:                     case 'boolean' :
389:                         $propertyType = PHPExcel_DocumentProperties::PROPERTY_TYPE_BOOLEAN;
390:                         $propertyValue = (bool) $propertyValue;
391:                         break;
392:                     case 'integer' :
393:                         $propertyType = PHPExcel_DocumentProperties::PROPERTY_TYPE_INTEGER;
394:                         $propertyValue = intval($propertyValue);
395:                         break;
396:                     case 'float' :
397:                         $propertyType = PHPExcel_DocumentProperties::PROPERTY_TYPE_FLOAT;
398:                         $propertyValue = floatval($propertyValue);
399:                         break;
400:                     case 'dateTime.tz' :
401:                         $propertyType = PHPExcel_DocumentProperties::PROPERTY_TYPE_DATE;
402:                         $propertyValue = strtotime(trim($propertyValue));
403:                         break;
404:                 }
405:                 $docProps->setCustomProperty($propertyName,$propertyValue,$propertyType);
406:             }
407:         }
408: 
409:         foreach($xml->Styles[0] as $style) {
410:             $style_ss = $style->attributes($namespaces['ss']);
411:             $styleID = (string) $style_ss['ID'];
412: //          echo 'Style ID = '.$styleID.'<br />';
413:             if ($styleID == 'Default') {
414:                 $this->_styles['Default'] = array();
415:             } else {
416:                 $this->_styles[$styleID] = $this->_styles['Default'];
417:             }
418:             foreach ($style as $styleType => $styleData) {
419:                 $styleAttributes = $styleData->attributes($namespaces['ss']);
420: //              echo $styleType.'<br />';
421:                 switch ($styleType) {
422:                     case 'Alignment' :
423:                             foreach($styleAttributes as $styleAttributeKey => $styleAttributeValue) {
424: //                              echo $styleAttributeKey.' = '.$styleAttributeValue.'<br />';
425:                                 $styleAttributeValue = (string) $styleAttributeValue;
426:                                 switch ($styleAttributeKey) {
427:                                     case 'Vertical' :
428:                                             if (self::identifyFixedStyleValue($verticalAlignmentStyles,$styleAttributeValue)) {
429:                                                 $this->_styles[$styleID]['alignment']['vertical'] = $styleAttributeValue;
430:                                             }
431:                                             break;
432:                                     case 'Horizontal' :
433:                                             if (self::identifyFixedStyleValue($horizontalAlignmentStyles,$styleAttributeValue)) {
434:                                                 $this->_styles[$styleID]['alignment']['horizontal'] = $styleAttributeValue;
435:                                             }
436:                                             break;
437:                                     case 'WrapText' :
438:                                             $this->_styles[$styleID]['alignment']['wrap'] = true;
439:                                             break;
440:                                 }
441:                             }
442:                             break;
443:                     case 'Borders' :
444:                             foreach($styleData->Border as $borderStyle) {
445:                                 $borderAttributes = $borderStyle->attributes($namespaces['ss']);
446:                                 $thisBorder = array();
447:                                 foreach($borderAttributes as $borderStyleKey => $borderStyleValue) {
448: //                                  echo $borderStyleKey.' = '.$borderStyleValue.'<br />';
449:                                     switch ($borderStyleKey) {
450:                                         case 'LineStyle' :
451:                                                 $thisBorder['style'] = PHPExcel_Style_Border::BORDER_MEDIUM;
452: //                                              $thisBorder['style'] = $borderStyleValue;
453:                                                 break;
454:                                         case 'Weight' :
455: //                                              $thisBorder['style'] = $borderStyleValue;
456:                                                 break;
457:                                         case 'Position' :
458:                                                 $borderPosition = strtolower($borderStyleValue);
459:                                                 break;
460:                                         case 'Color' :
461:                                                 $borderColour = substr($borderStyleValue,1);
462:                                                 $thisBorder['color']['rgb'] = $borderColour;
463:                                                 break;
464:                                     }
465:                                 }
466:                                 if (!empty($thisBorder)) {
467:                                     if (($borderPosition == 'left') || ($borderPosition == 'right') || ($borderPosition == 'top') || ($borderPosition == 'bottom')) {
468:                                         $this->_styles[$styleID]['borders'][$borderPosition] = $thisBorder;
469:                                     }
470:                                 }
471:                             }
472:                             break;
473:                     case 'Font' :
474:                             foreach($styleAttributes as $styleAttributeKey => $styleAttributeValue) {
475: //                              echo $styleAttributeKey.' = '.$styleAttributeValue.'<br />';
476:                                 $styleAttributeValue = (string) $styleAttributeValue;
477:                                 switch ($styleAttributeKey) {
478:                                     case 'FontName' :
479:                                             $this->_styles[$styleID]['font']['name'] = $styleAttributeValue;
480:                                             break;
481:                                     case 'Size' :
482:                                             $this->_styles[$styleID]['font']['size'] = $styleAttributeValue;
483:                                             break;
484:                                     case 'Color' :
485:                                             $this->_styles[$styleID]['font']['color']['rgb'] = substr($styleAttributeValue,1);
486:                                             break;
487:                                     case 'Bold' :
488:                                             $this->_styles[$styleID]['font']['bold'] = true;
489:                                             break;
490:                                     case 'Italic' :
491:                                             $this->_styles[$styleID]['font']['italic'] = true;
492:                                             break;
493:                                     case 'Underline' :
494:                                             if (self::identifyFixedStyleValue($underlineStyles,$styleAttributeValue)) {
495:                                                 $this->_styles[$styleID]['font']['underline'] = $styleAttributeValue;
496:                                             }
497:                                             break;
498:                                 }
499:                             }
500:                             break;
501:                     case 'Interior' :
502:                             foreach($styleAttributes as $styleAttributeKey => $styleAttributeValue) {
503: //                              echo $styleAttributeKey.' = '.$styleAttributeValue.'<br />';
504:                                 switch ($styleAttributeKey) {
505:                                     case 'Color' :
506:                                             $this->_styles[$styleID]['fill']['color']['rgb'] = substr($styleAttributeValue,1);
507:                                             break;
508:                                 }
509:                             }
510:                             break;
511:                     case 'NumberFormat' :
512:                             foreach($styleAttributes as $styleAttributeKey => $styleAttributeValue) {
513: //                              echo $styleAttributeKey.' = '.$styleAttributeValue.'<br />';
514:                                 $styleAttributeValue = str_replace($fromFormats,$toFormats,$styleAttributeValue);
515:                                 switch ($styleAttributeValue) {
516:                                     case 'Short Date' :
517:                                             $styleAttributeValue = 'dd/mm/yyyy';
518:                                             break;
519:                                 }
520:                                 if ($styleAttributeValue > '') {
521:                                     $this->_styles[$styleID]['numberformat']['code'] = $styleAttributeValue;
522:                                 }
523:                             }
524:                             break;
525:                     case 'Protection' :
526:                             foreach($styleAttributes as $styleAttributeKey => $styleAttributeValue) {
527: //                              echo $styleAttributeKey.' = '.$styleAttributeValue.'<br />';
528:                             }
529:                             break;
530:                 }
531:             }
532: //          print_r($this->_styles[$styleID]);
533: //          echo '<hr />';
534:         }
535: //      echo '<hr />';
536: 
537:         $worksheetID = 0;
538:         $xml_ss = $xml->children($namespaces['ss']);
539: 
540:         foreach($xml_ss->Worksheet as $worksheet) {
541:             $worksheet_ss = $worksheet->attributes($namespaces['ss']);
542: 
543:             if ((isset($this->_loadSheetsOnly)) && (isset($worksheet_ss['Name'])) &&
544:                 (!in_array($worksheet_ss['Name'], $this->_loadSheetsOnly))) {
545:                 continue;
546:             }
547: 
548: //          echo '<h3>Worksheet: ',$worksheet_ss['Name'],'<h3>';
549: //
550:             // Create new Worksheet
551:             $objPHPExcel->createSheet();
552:             $objPHPExcel->setActiveSheetIndex($worksheetID);
553:             if (isset($worksheet_ss['Name'])) {
554:                 $worksheetName = self::_convertStringEncoding((string) $worksheet_ss['Name'],$this->_charSet);
555:                 //  Use false for $updateFormulaCellReferences to prevent adjustment of worksheet references in
556:                 //      formula cells... during the load, all formulae should be correct, and we're simply bringing
557:                 //      the worksheet name in line with the formula, not the reverse
558:                 $objPHPExcel->getActiveSheet()->setTitle($worksheetName,false);
559:             }
560: 
561:             $columnID = 'A';
562:             if (isset($worksheet->Table->Column)) {
563:                 foreach($worksheet->Table->Column as $columnData) {
564:                     $columnData_ss = $columnData->attributes($namespaces['ss']);
565:                     if (isset($columnData_ss['Index'])) {
566:                         $columnID = PHPExcel_Cell::stringFromColumnIndex($columnData_ss['Index']-1);
567:                     }
568:                     if (isset($columnData_ss['Width'])) {
569:                         $columnWidth = $columnData_ss['Width'];
570: //                      echo '<b>Setting column width for '.$columnID.' to '.$columnWidth.'</b><br />';
571:                         $objPHPExcel->getActiveSheet()->getColumnDimension($columnID)->setWidth($columnWidth / 5.4);
572:                     }
573:                     ++$columnID;
574:                 }
575:             }
576: 
577:             $rowID = 1;
578:             if (isset($worksheet->Table->Row)) {
579:                 foreach($worksheet->Table->Row as $rowData) {
580:                     $rowHasData = false;
581:                     $row_ss = $rowData->attributes($namespaces['ss']);
582:                     if (isset($row_ss['Index'])) {
583:                         $rowID = (integer) $row_ss['Index'];
584:                     }
585: //                  echo '<b>Row '.$rowID.'</b><br />';
586: 
587:                     $columnID = 'A';
588:                     foreach($rowData->Cell as $cell) {
589: 
590:                         $cell_ss = $cell->attributes($namespaces['ss']);
591:                         if (isset($cell_ss['Index'])) {
592:                             $columnID = PHPExcel_Cell::stringFromColumnIndex($cell_ss['Index']-1);
593:                         }
594:                         $cellRange = $columnID.$rowID;
595: 
596:                         if ($this->getReadFilter() !== NULL) {
597:                             if (!$this->getReadFilter()->readCell($columnID, $rowID, $worksheetName)) {
598:                                 continue;
599:                             }
600:                         }
601: 
602:                         if ((isset($cell_ss['MergeAcross'])) || (isset($cell_ss['MergeDown']))) {
603:                             $columnTo = $columnID;
604:                             if (isset($cell_ss['MergeAcross'])) {
605:                                 $columnTo = PHPExcel_Cell::stringFromColumnIndex(PHPExcel_Cell::columnIndexFromString($columnID) + $cell_ss['MergeAcross'] -1);
606:                             }
607:                             $rowTo = $rowID;
608:                             if (isset($cell_ss['MergeDown'])) {
609:                                 $rowTo = $rowTo + $cell_ss['MergeDown'];
610:                             }
611:                             $cellRange .= ':'.$columnTo.$rowTo;
612:                             $objPHPExcel->getActiveSheet()->mergeCells($cellRange);
613:                         }
614: 
615:                         $cellIsSet = $hasCalculatedValue = false;
616:                         $cellDataFormula = '';
617:                         if (isset($cell_ss['Formula'])) {
618:                             $cellDataFormula = $cell_ss['Formula'];
619:                             // added this as a check for array formulas
620:                             if (isset($cell_ss['ArrayRange'])) {
621:                                 $cellDataCSEFormula = $cell_ss['ArrayRange'];
622: //                              echo "found an array formula at ".$columnID.$rowID."<br />";
623:                             }
624:                             $hasCalculatedValue = true;
625:                         }
626:                         if (isset($cell->Data)) {
627:                             $cellValue = $cellData = $cell->Data;
628:                             $type = PHPExcel_Cell_DataType::TYPE_NULL;
629:                             $cellData_ss = $cellData->attributes($namespaces['ss']);
630:                             if (isset($cellData_ss['Type'])) {
631:                                 $cellDataType = $cellData_ss['Type'];
632:                                 switch ($cellDataType) {
633:                                     /*
634:                                     const TYPE_STRING       = 's';
635:                                     const TYPE_FORMULA      = 'f';
636:                                     const TYPE_NUMERIC      = 'n';
637:                                     const TYPE_BOOL         = 'b';
638:                                     const TYPE_NULL         = 'null';
639:                                     const TYPE_INLINE       = 'inlineStr';
640:                                     const TYPE_ERROR        = 'e';
641:                                     */
642:                                     case 'String' :
643:                                             $cellValue = self::_convertStringEncoding($cellValue,$this->_charSet);
644:                                             $type = PHPExcel_Cell_DataType::TYPE_STRING;
645:                                             break;
646:                                     case 'Number' :
647:                                             $type = PHPExcel_Cell_DataType::TYPE_NUMERIC;
648:                                             $cellValue = (float) $cellValue;
649:                                             if (floor($cellValue) == $cellValue) {
650:                                                 $cellValue = (integer) $cellValue;
651:                                             }
652:                                             break;
653:                                     case 'Boolean' :
654:                                             $type = PHPExcel_Cell_DataType::TYPE_BOOL;
655:                                             $cellValue = ($cellValue != 0);
656:                                             break;
657:                                     case 'DateTime' :
658:                                             $type = PHPExcel_Cell_DataType::TYPE_NUMERIC;
659:                                             $cellValue = PHPExcel_Shared_Date::PHPToExcel(strtotime($cellValue));
660:                                             break;
661:                                     case 'Error' :
662:                                             $type = PHPExcel_Cell_DataType::TYPE_ERROR;
663:                                             break;
664:                                 }
665:                             }
666: 
667:                             if ($hasCalculatedValue) {
668: //                              echo 'FORMULA<br />';
669:                                 $type = PHPExcel_Cell_DataType::TYPE_FORMULA;
670:                                 $columnNumber = PHPExcel_Cell::columnIndexFromString($columnID);
671:                                 if (substr($cellDataFormula,0,3) == 'of:') {
672:                                     $cellDataFormula = substr($cellDataFormula,3);
673: //                                  echo 'Before: ',$cellDataFormula,'<br />';
674:                                     $temp = explode('"',$cellDataFormula);
675:                                     $key = false;
676:                                     foreach($temp as &$value) {
677:                                         //  Only replace in alternate array entries (i.e. non-quoted blocks)
678:                                         if ($key = !$key) {
679:                                             $value = str_replace(array('[.','.',']'),'',$value);
680:                                         }
681:                                     }
682:                                 } else {
683:                                     //  Convert R1C1 style references to A1 style references (but only when not quoted)
684: //                                  echo 'Before: ',$cellDataFormula,'<br />';
685:                                     $temp = explode('"',$cellDataFormula);
686:                                     $key = false;
687:                                     foreach($temp as &$value) {
688:                                         //  Only replace in alternate array entries (i.e. non-quoted blocks)
689:                                         if ($key = !$key) {
690:                                             preg_match_all('/(R(\[?-?\d*\]?))(C(\[?-?\d*\]?))/',$value, $cellReferences,PREG_SET_ORDER+PREG_OFFSET_CAPTURE);
691:                                             //  Reverse the matches array, otherwise all our offsets will become incorrect if we modify our way
692:                                             //      through the formula from left to right. Reversing means that we work right to left.through
693:                                             //      the formula
694:                                             $cellReferences = array_reverse($cellReferences);
695:                                             //  Loop through each R1C1 style reference in turn, converting it to its A1 style equivalent,
696:                                             //      then modify the formula to use that new reference
697:                                             foreach($cellReferences as $cellReference) {
698:                                                 $rowReference = $cellReference[2][0];
699:                                                 //  Empty R reference is the current row
700:                                                 if ($rowReference == '') $rowReference = $rowID;
701:                                                 //  Bracketed R references are relative to the current row
702:                                                 if ($rowReference{0} == '[') $rowReference = $rowID + trim($rowReference,'[]');
703:                                                 $columnReference = $cellReference[4][0];
704:                                                 //  Empty C reference is the current column
705:                                                 if ($columnReference == '') $columnReference = $columnNumber;
706:                                                 //  Bracketed C references are relative to the current column
707:                                                 if ($columnReference{0} == '[') $columnReference = $columnNumber + trim($columnReference,'[]');
708:                                                 $A1CellReference = PHPExcel_Cell::stringFromColumnIndex($columnReference-1).$rowReference;
709:                                                     $value = substr_replace($value,$A1CellReference,$cellReference[0][1],strlen($cellReference[0][0]));
710:                                             }
711:                                         }
712:                                     }
713:                                 }
714:                                 unset($value);
715:                                 //  Then rebuild the formula string
716:                                 $cellDataFormula = implode('"',$temp);
717: //                              echo 'After: ',$cellDataFormula,'<br />';
718:                             }
719: 
720: //                          echo 'Cell '.$columnID.$rowID.' is a '.$type.' with a value of '.(($hasCalculatedValue) ? $cellDataFormula : $cellValue).'<br />';
721: //
722:                             $objPHPExcel->getActiveSheet()->getCell($columnID.$rowID)->setValueExplicit((($hasCalculatedValue) ? $cellDataFormula : $cellValue),$type);
723:                             if ($hasCalculatedValue) {
724: //                              echo 'Formula result is '.$cellValue.'<br />';
725:                                 $objPHPExcel->getActiveSheet()->getCell($columnID.$rowID)->setCalculatedValue($cellValue);
726:                             }
727:                             $cellIsSet = $rowHasData = true;
728:                         }
729: 
730:                         if (isset($cell->Comment)) {
731: //                          echo '<b>comment found</b><br />';
732:                             $commentAttributes = $cell->Comment->attributes($namespaces['ss']);
733:                             $author = 'unknown';
734:                             if (isset($commentAttributes->Author)) {
735:                                 $author = (string)$commentAttributes->Author;
736: //                              echo 'Author: ',$author,'<br />';
737:                             }
738:                             $node = $cell->Comment->Data->asXML();
739: //                          $annotation = str_replace('html:','',substr($node,49,-10));
740: //                          echo $annotation,'<br />';
741:                             $annotation = strip_tags($node);
742: //                          echo 'Annotation: ',$annotation,'<br />';
743:                             $objPHPExcel->getActiveSheet()->getComment( $columnID.$rowID )
744:                                                             ->setAuthor(self::_convertStringEncoding($author ,$this->_charSet))
745:                                                             ->setText($this->_parseRichText($annotation) );
746:                         }
747: 
748:                         if (($cellIsSet) && (isset($cell_ss['StyleID']))) {
749:                             $style = (string) $cell_ss['StyleID'];
750: //                          echo 'Cell style for '.$columnID.$rowID.' is '.$style.'<br />';
751:                             if ((isset($this->_styles[$style])) && (!empty($this->_styles[$style]))) {
752: //                              echo 'Cell '.$columnID.$rowID.'<br />';
753: //                              print_r($this->_styles[$style]);
754: //                              echo '<br />';
755:                                 if (!$objPHPExcel->getActiveSheet()->cellExists($columnID.$rowID)) {
756:                                     $objPHPExcel->getActiveSheet()->getCell($columnID.$rowID)->setValue(NULL);
757:                                 }
758:                                 $objPHPExcel->getActiveSheet()->getStyle($cellRange)->applyFromArray($this->_styles[$style]);
759:                             }
760:                         }
761:                         ++$columnID;
762:                     }
763: 
764:                     if ($rowHasData) {
765:                         if (isset($row_ss['StyleID'])) {
766:                             $rowStyle = $row_ss['StyleID'];
767:                         }
768:                         if (isset($row_ss['Height'])) {
769:                             $rowHeight = $row_ss['Height'];
770: //                          echo '<b>Setting row height to '.$rowHeight.'</b><br />';
771:                             $objPHPExcel->getActiveSheet()->getRowDimension($rowID)->setRowHeight($rowHeight);
772:                         }
773:                     }
774: 
775:                     ++$rowID;
776:                 }
777:             }
778:             ++$worksheetID;
779:         }
780: 
781:         // Return
782:         return $objPHPExcel;
783:     }
784: 
785: 
786:     private static function _convertStringEncoding($string,$charset) {
787:         if ($charset != 'UTF-8') {
788:             return PHPExcel_Shared_String::ConvertEncoding($string,'UTF-8',$charset);
789:         }
790:         return $string;
791:     }
792: 
793: 
794:     private function _parseRichText($is = '') {
795:         $value = new PHPExcel_RichText();
796: 
797:         $value->createText(self::_convertStringEncoding($is,$this->_charSet));
798: 
799:         return $value;
800:     }
801: 
802: }
803: