1<?php
2
3/** PHPExcel root directory */
4if (!defined('PHPEXCEL_ROOT')) {
5    /**
6     * @ignore
7     */
8    define('PHPEXCEL_ROOT', dirname(__FILE__) . '/../../');
9    require(PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php');
10}
11
12/**
13 * PHPExcel_Reader_Gnumeric
14 *
15 * Copyright (c) 2006 - 2015 PHPExcel
16 *
17 * This library is free software; you can redistribute it and/or
18 * modify it under the terms of the GNU Lesser General Public
19 * License as published by the Free Software Foundation; either
20 * version 2.1 of the License, or (at your option) any later version.
21 *
22 * This library is distributed in the hope that it will be useful,
23 * but WITHOUT ANY WARRANTY; without even the implied warranty of
24 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
25 * Lesser General Public License for more details.
26 *
27 * You should have received a copy of the GNU Lesser General Public
28 * License along with this library; if not, write to the Free Software
29 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
30 *
31 * @category   PHPExcel
32 * @package    PHPExcel_Reader
33 * @copyright  Copyright (c) 2006 - 2015 PHPExcel (http://www.codeplex.com/PHPExcel)
34 * @license    http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt    LGPL
35 * @version    ##VERSION##, ##DATE##
36 */
37class PHPExcel_Reader_Gnumeric extends PHPExcel_Reader_Abstract implements PHPExcel_Reader_IReader
38{
39    /**
40     * Formats
41     *
42     * @var array
43     */
44    private $styles = array();
45
46    /**
47     * Shared Expressions
48     *
49     * @var array
50     */
51    private $expressions = array();
52
53    private $referenceHelper = null;
54
55    /**
56     * Create a new PHPExcel_Reader_Gnumeric
57     */
58    public function __construct()
59    {
60        $this->readFilter     = new PHPExcel_Reader_DefaultReadFilter();
61        $this->referenceHelper = PHPExcel_ReferenceHelper::getInstance();
62    }
63
64    /**
65     * Can the current PHPExcel_Reader_IReader read the file?
66     *
67     * @param     string         $pFilename
68     * @return     boolean
69     * @throws PHPExcel_Reader_Exception
70     */
71    public function canRead($pFilename)
72    {
73        // Check if file exists
74        if (!file_exists($pFilename)) {
75            throw new PHPExcel_Reader_Exception("Could not open " . $pFilename . " for reading! File does not exist.");
76        }
77
78        // Check if gzlib functions are available
79        if (!function_exists('gzread')) {
80            throw new PHPExcel_Reader_Exception("gzlib library is not enabled");
81        }
82
83        // Read signature data (first 3 bytes)
84        $fh = fopen($pFilename, 'r');
85        $data = fread($fh, 2);
86        fclose($fh);
87
88        if ($data != chr(0x1F).chr(0x8B)) {
89            return false;
90        }
91
92        return true;
93    }
94
95    /**
96     * Reads names of the worksheets from a file, without parsing the whole file to a PHPExcel object
97     *
98     * @param     string         $pFilename
99     * @throws     PHPExcel_Reader_Exception
100     */
101    public function listWorksheetNames($pFilename)
102    {
103        // Check if file exists
104        if (!file_exists($pFilename)) {
105            throw new PHPExcel_Reader_Exception("Could not open " . $pFilename . " for reading! File does not exist.");
106        }
107
108        $xml = new XMLReader();
109        $xml->xml($this->securityScanFile('compress.zlib://'.realpath($pFilename)), null, PHPExcel_Settings::getLibXmlLoaderOptions());
110        $xml->setParserProperty(2, true);
111
112        $worksheetNames = array();
113        while ($xml->read()) {
114            if ($xml->name == 'gnm:SheetName' && $xml->nodeType == XMLReader::ELEMENT) {
115                $xml->read();    //    Move onto the value node
116                $worksheetNames[] = (string) $xml->value;
117            } elseif ($xml->name == 'gnm:Sheets') {
118                //    break out of the loop once we've got our sheet names rather than parse the entire file
119                break;
120            }
121        }
122
123        return $worksheetNames;
124    }
125
126    /**
127     * Return worksheet info (Name, Last Column Letter, Last Column Index, Total Rows, Total Columns)
128     *
129     * @param   string     $pFilename
130     * @throws   PHPExcel_Reader_Exception
131     */
132    public function listWorksheetInfo($pFilename)
133    {
134        // Check if file exists
135        if (!file_exists($pFilename)) {
136            throw new PHPExcel_Reader_Exception("Could not open " . $pFilename . " for reading! File does not exist.");
137        }
138
139        $xml = new XMLReader();
140        $xml->xml($this->securityScanFile('compress.zlib://'.realpath($pFilename)), null, PHPExcel_Settings::getLibXmlLoaderOptions());
141        $xml->setParserProperty(2, true);
142
143        $worksheetInfo = array();
144        while ($xml->read()) {
145            if ($xml->name == 'gnm:Sheet' && $xml->nodeType == XMLReader::ELEMENT) {
146                $tmpInfo = array(
147                    'worksheetName' => '',
148                    'lastColumnLetter' => 'A',
149                    'lastColumnIndex' => 0,
150                    'totalRows' => 0,
151                    'totalColumns' => 0,
152                );
153
154                while ($xml->read()) {
155                    if ($xml->name == 'gnm:Name' && $xml->nodeType == XMLReader::ELEMENT) {
156                        $xml->read();    //    Move onto the value node
157                        $tmpInfo['worksheetName'] = (string) $xml->value;
158                    } elseif ($xml->name == 'gnm:MaxCol' && $xml->nodeType == XMLReader::ELEMENT) {
159                        $xml->read();    //    Move onto the value node
160                        $tmpInfo['lastColumnIndex'] = (int) $xml->value;
161                        $tmpInfo['totalColumns'] = (int) $xml->value + 1;
162                    } elseif ($xml->name == 'gnm:MaxRow' && $xml->nodeType == XMLReader::ELEMENT) {
163                        $xml->read();    //    Move onto the value node
164                        $tmpInfo['totalRows'] = (int) $xml->value + 1;
165                        break;
166                    }
167                }
168                $tmpInfo['lastColumnLetter'] = PHPExcel_Cell::stringFromColumnIndex($tmpInfo['lastColumnIndex']);
169                $worksheetInfo[] = $tmpInfo;
170            }
171        }
172
173        return $worksheetInfo;
174    }
175
176    private function gzfileGetContents($filename)
177    {
178        $file = @gzopen($filename, 'rb');
179        if ($file !== false) {
180            $data = '';
181            while (!gzeof($file)) {
182                $data .= gzread($file, 1024);
183            }
184            gzclose($file);
185        }
186        return $data;
187    }
188
189    /**
190     * Loads PHPExcel from file
191     *
192     * @param     string         $pFilename
193     * @return     PHPExcel
194     * @throws     PHPExcel_Reader_Exception
195     */
196    public function load($pFilename)
197    {
198        // Create new PHPExcel
199        $objPHPExcel = new PHPExcel();
200
201        // Load into this instance
202        return $this->loadIntoExisting($pFilename, $objPHPExcel);
203    }
204
205    /**
206     * Loads PHPExcel from file into PHPExcel instance
207     *
208     * @param     string         $pFilename
209     * @param    PHPExcel    $objPHPExcel
210     * @return     PHPExcel
211     * @throws     PHPExcel_Reader_Exception
212     */
213    public function loadIntoExisting($pFilename, PHPExcel $objPHPExcel)
214    {
215        // Check if file exists
216        if (!file_exists($pFilename)) {
217            throw new PHPExcel_Reader_Exception("Could not open " . $pFilename . " for reading! File does not exist.");
218        }
219
220        $timezoneObj = new DateTimeZone('Europe/London');
221        $GMT = new DateTimeZone('UTC');
222
223        $gFileData = $this->gzfileGetContents($pFilename);
224
225//        echo '<pre>';
226//        echo htmlentities($gFileData,ENT_QUOTES,'UTF-8');
227//        echo '</pre><hr />';
228//
229        $xml = simplexml_load_string($this->securityScan($gFileData), 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions());
230        $namespacesMeta = $xml->getNamespaces(true);
231
232//        var_dump($namespacesMeta);
233//
234        $gnmXML = $xml->children($namespacesMeta['gnm']);
235
236        $docProps = $objPHPExcel->getProperties();
237        //    Document Properties are held differently, depending on the version of Gnumeric
238        if (isset($namespacesMeta['office'])) {
239            $officeXML = $xml->children($namespacesMeta['office']);
240            $officeDocXML = $officeXML->{'document-meta'};
241            $officeDocMetaXML = $officeDocXML->meta;
242
243            foreach ($officeDocMetaXML as $officePropertyData) {
244                $officePropertyDC = array();
245                if (isset($namespacesMeta['dc'])) {
246                    $officePropertyDC = $officePropertyData->children($namespacesMeta['dc']);
247                }
248                foreach ($officePropertyDC as $propertyName => $propertyValue) {
249                    $propertyValue = (string) $propertyValue;
250                    switch ($propertyName) {
251                        case 'title':
252                            $docProps->setTitle(trim($propertyValue));
253                            break;
254                        case 'subject':
255                            $docProps->setSubject(trim($propertyValue));
256                            break;
257                        case 'creator':
258                            $docProps->setCreator(trim($propertyValue));
259                            $docProps->setLastModifiedBy(trim($propertyValue));
260                            break;
261                        case 'date':
262                            $creationDate = strtotime(trim($propertyValue));
263                            $docProps->setCreated($creationDate);
264                            $docProps->setModified($creationDate);
265                            break;
266                        case 'description':
267                            $docProps->setDescription(trim($propertyValue));
268                            break;
269                    }
270                }
271                $officePropertyMeta = array();
272                if (isset($namespacesMeta['meta'])) {
273                    $officePropertyMeta = $officePropertyData->children($namespacesMeta['meta']);
274                }
275                foreach ($officePropertyMeta as $propertyName => $propertyValue) {
276                    $attributes = $propertyValue->attributes($namespacesMeta['meta']);
277                    $propertyValue = (string) $propertyValue;
278                    switch ($propertyName) {
279                        case 'keyword':
280                            $docProps->setKeywords(trim($propertyValue));
281                            break;
282                        case 'initial-creator':
283                            $docProps->setCreator(trim($propertyValue));
284                            $docProps->setLastModifiedBy(trim($propertyValue));
285                            break;
286                        case 'creation-date':
287                            $creationDate = strtotime(trim($propertyValue));
288                            $docProps->setCreated($creationDate);
289                            $docProps->setModified($creationDate);
290                            break;
291                        case 'user-defined':
292                            list(, $attrName) = explode(':', $attributes['name']);
293                            switch ($attrName) {
294                                case 'publisher':
295                                    $docProps->setCompany(trim($propertyValue));
296                                    break;
297                                case 'category':
298                                    $docProps->setCategory(trim($propertyValue));
299                                    break;
300                                case 'manager':
301                                    $docProps->setManager(trim($propertyValue));
302                                    break;
303                            }
304                            break;
305                    }
306                }
307            }
308        } elseif (isset($gnmXML->Summary)) {
309            foreach ($gnmXML->Summary->Item as $summaryItem) {
310                $propertyName = $summaryItem->name;
311                $propertyValue = $summaryItem->{'val-string'};
312                switch ($propertyName) {
313                    case 'title':
314                        $docProps->setTitle(trim($propertyValue));
315                        break;
316                    case 'comments':
317                        $docProps->setDescription(trim($propertyValue));
318                        break;
319                    case 'keywords':
320                        $docProps->setKeywords(trim($propertyValue));
321                        break;
322                    case 'category':
323                        $docProps->setCategory(trim($propertyValue));
324                        break;
325                    case 'manager':
326                        $docProps->setManager(trim($propertyValue));
327                        break;
328                    case 'author':
329                        $docProps->setCreator(trim($propertyValue));
330                        $docProps->setLastModifiedBy(trim($propertyValue));
331                        break;
332                    case 'company':
333                        $docProps->setCompany(trim($propertyValue));
334                        break;
335                }
336            }
337        }
338
339        $worksheetID = 0;
340        foreach ($gnmXML->Sheets->Sheet as $sheet) {
341            $worksheetName = (string) $sheet->Name;
342//            echo '<b>Worksheet: ', $worksheetName,'</b><br />';
343            if ((isset($this->loadSheetsOnly)) && (!in_array($worksheetName, $this->loadSheetsOnly))) {
344                continue;
345            }
346
347            $maxRow = $maxCol = 0;
348
349            // Create new Worksheet
350            $objPHPExcel->createSheet();
351            $objPHPExcel->setActiveSheetIndex($worksheetID);
352            //    Use false for $updateFormulaCellReferences to prevent adjustment of worksheet references in formula
353            //        cells... during the load, all formulae should be correct, and we're simply bringing the worksheet
354            //        name in line with the formula, not the reverse
355            $objPHPExcel->getActiveSheet()->setTitle($worksheetName, false);
356
357            if ((!$this->readDataOnly) && (isset($sheet->PrintInformation))) {
358                if (isset($sheet->PrintInformation->Margins)) {
359                    foreach ($sheet->PrintInformation->Margins->children('gnm', true) as $key => $margin) {
360                        $marginAttributes = $margin->attributes();
361                        $marginSize = 72 / 100;    //    Default
362                        switch ($marginAttributes['PrefUnit']) {
363                            case 'mm':
364                                $marginSize = intval($marginAttributes['Points']) / 100;
365                                break;
366                        }
367                        switch ($key) {
368                            case 'top':
369                                $objPHPExcel->getActiveSheet()->getPageMargins()->setTop($marginSize);
370                                break;
371                            case 'bottom':
372                                $objPHPExcel->getActiveSheet()->getPageMargins()->setBottom($marginSize);
373                                break;
374                            case 'left':
375                                $objPHPExcel->getActiveSheet()->getPageMargins()->setLeft($marginSize);
376                                break;
377                            case 'right':
378                                $objPHPExcel->getActiveSheet()->getPageMargins()->setRight($marginSize);
379                                break;
380                            case 'header':
381                                $objPHPExcel->getActiveSheet()->getPageMargins()->setHeader($marginSize);
382                                break;
383                            case 'footer':
384                                $objPHPExcel->getActiveSheet()->getPageMargins()->setFooter($marginSize);
385                                break;
386                        }
387                    }
388                }
389            }
390
391            foreach ($sheet->Cells->Cell as $cell) {
392                $cellAttributes = $cell->attributes();
393                $row = (int) $cellAttributes->Row + 1;
394                $column = (int) $cellAttributes->Col;
395
396                if ($row > $maxRow) {
397                    $maxRow = $row;
398                }
399                if ($column > $maxCol) {
400                    $maxCol = $column;
401                }
402
403                $column = PHPExcel_Cell::stringFromColumnIndex($column);
404
405                // Read cell?
406                if ($this->getReadFilter() !== null) {
407                    if (!$this->getReadFilter()->readCell($column, $row, $worksheetName)) {
408                        continue;
409                    }
410                }
411
412                $ValueType = $cellAttributes->ValueType;
413                $ExprID = (string) $cellAttributes->ExprID;
414//                echo 'Cell ', $column, $row,'<br />';
415//                echo 'Type is ', $ValueType,'<br />';
416//                echo 'Value is ', $cell,'<br />';
417                $type = PHPExcel_Cell_DataType::TYPE_FORMULA;
418                if ($ExprID > '') {
419                    if (((string) $cell) > '') {
420                        $this->expressions[$ExprID] = array(
421                            'column'    => $cellAttributes->Col,
422                            'row'        => $cellAttributes->Row,
423                            'formula'    => (string) $cell
424                        );
425//                        echo 'NEW EXPRESSION ', $ExprID,'<br />';
426                    } else {
427                        $expression = $this->expressions[$ExprID];
428
429                        $cell = $this->referenceHelper->updateFormulaReferences(
430                            $expression['formula'],
431                            'A1',
432                            $cellAttributes->Col - $expression['column'],
433                            $cellAttributes->Row - $expression['row'],
434                            $worksheetName
435                        );
436//                        echo 'SHARED EXPRESSION ', $ExprID,'<br />';
437//                        echo 'New Value is ', $cell,'<br />';
438                    }
439                    $type = PHPExcel_Cell_DataType::TYPE_FORMULA;
440                } else {
441                    switch ($ValueType) {
442                        case '10':        //    NULL
443                            $type = PHPExcel_Cell_DataType::TYPE_NULL;
444                            break;
445                        case '20':        //    Boolean
446                            $type = PHPExcel_Cell_DataType::TYPE_BOOL;
447                            $cell = ($cell == 'TRUE') ? true: false;
448                            break;
449                        case '30':        //    Integer
450                            $cell = intval($cell);
451                            // Excel 2007+ doesn't differentiate between integer and float, so set the value and dropthru to the next (numeric) case
452                        case '40':        //    Float
453                            $type = PHPExcel_Cell_DataType::TYPE_NUMERIC;
454                            break;
455                        case '50':        //    Error
456                            $type = PHPExcel_Cell_DataType::TYPE_ERROR;
457                            break;
458                        case '60':        //    String
459                            $type = PHPExcel_Cell_DataType::TYPE_STRING;
460                            break;
461                        case '70':        //    Cell Range
462                        case '80':        //    Array
463                    }
464                }
465                $objPHPExcel->getActiveSheet()->getCell($column.$row)->setValueExplicit($cell, $type);
466            }
467
468            if ((!$this->readDataOnly) && (isset($sheet->Objects))) {
469                foreach ($sheet->Objects->children('gnm', true) as $key => $comment) {
470                    $commentAttributes = $comment->attributes();
471                    //    Only comment objects are handled at the moment
472                    if ($commentAttributes->Text) {
473                        $objPHPExcel->getActiveSheet()->getComment((string)$commentAttributes->ObjectBound)->setAuthor((string)$commentAttributes->Author)->setText($this->parseRichText((string)$commentAttributes->Text));
474                    }
475                }
476            }
477//            echo '$maxCol=', $maxCol,'; $maxRow=', $maxRow,'<br />';
478//
479            foreach ($sheet->Styles->StyleRegion as $styleRegion) {
480                $styleAttributes = $styleRegion->attributes();
481                if (($styleAttributes['startRow'] <= $maxRow) &&
482                    ($styleAttributes['startCol'] <= $maxCol)) {
483                    $startColumn = PHPExcel_Cell::stringFromColumnIndex((int) $styleAttributes['startCol']);
484                    $startRow = $styleAttributes['startRow'] + 1;
485
486                    $endColumn = ($styleAttributes['endCol'] > $maxCol) ? $maxCol : (int) $styleAttributes['endCol'];
487                    $endColumn = PHPExcel_Cell::stringFromColumnIndex($endColumn);
488                    $endRow = ($styleAttributes['endRow'] > $maxRow) ? $maxRow : $styleAttributes['endRow'];
489                    $endRow += 1;
490                    $cellRange = $startColumn.$startRow.':'.$endColumn.$endRow;
491//                    echo $cellRange,'<br />';
492
493                    $styleAttributes = $styleRegion->Style->attributes();
494//                    var_dump($styleAttributes);
495//                    echo '<br />';
496
497                    //    We still set the number format mask for date/time values, even if readDataOnly is true
498                    if ((!$this->readDataOnly) ||
499                        (PHPExcel_Shared_Date::isDateTimeFormatCode((string) $styleAttributes['Format']))) {
500                        $styleArray = array();
501                        $styleArray['numberformat']['code'] = (string) $styleAttributes['Format'];
502                        //    If readDataOnly is false, we set all formatting information
503                        if (!$this->readDataOnly) {
504                            switch ($styleAttributes['HAlign']) {
505                                case '1':
506                                    $styleArray['alignment']['horizontal'] = PHPExcel_Style_Alignment::HORIZONTAL_GENERAL;
507                                    break;
508                                case '2':
509                                    $styleArray['alignment']['horizontal'] = PHPExcel_Style_Alignment::HORIZONTAL_LEFT;
510                                    break;
511                                case '4':
512                                    $styleArray['alignment']['horizontal'] = PHPExcel_Style_Alignment::HORIZONTAL_RIGHT;
513                                    break;
514                                case '8':
515                                    $styleArray['alignment']['horizontal'] = PHPExcel_Style_Alignment::HORIZONTAL_CENTER;
516                                    break;
517                                case '16':
518                                case '64':
519                                    $styleArray['alignment']['horizontal'] = PHPExcel_Style_Alignment::HORIZONTAL_CENTER_CONTINUOUS;
520                                    break;
521                                case '32':
522                                    $styleArray['alignment']['horizontal'] = PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY;
523                                    break;
524                            }
525
526                            switch ($styleAttributes['VAlign']) {
527                                case '1':
528                                    $styleArray['alignment']['vertical'] = PHPExcel_Style_Alignment::VERTICAL_TOP;
529                                    break;
530                                case '2':
531                                    $styleArray['alignment']['vertical'] = PHPExcel_Style_Alignment::VERTICAL_BOTTOM;
532                                    break;
533                                case '4':
534                                    $styleArray['alignment']['vertical'] = PHPExcel_Style_Alignment::VERTICAL_CENTER;
535                                    break;
536                                case '8':
537                                    $styleArray['alignment']['vertical'] = PHPExcel_Style_Alignment::VERTICAL_JUSTIFY;
538                                    break;
539                            }
540
541                            $styleArray['alignment']['wrap'] = ($styleAttributes['WrapText'] == '1') ? true : false;
542                            $styleArray['alignment']['shrinkToFit'] = ($styleAttributes['ShrinkToFit'] == '1') ? true : false;
543                            $styleArray['alignment']['indent'] = (intval($styleAttributes["Indent"]) > 0) ? $styleAttributes["indent"] : 0;
544
545                            $RGB = self::parseGnumericColour($styleAttributes["Fore"]);
546                            $styleArray['font']['color']['rgb'] = $RGB;
547                            $RGB = self::parseGnumericColour($styleAttributes["Back"]);
548                            $shade = $styleAttributes["Shade"];
549                            if (($RGB != '000000') || ($shade != '0')) {
550                                $styleArray['fill']['color']['rgb'] = $styleArray['fill']['startcolor']['rgb'] = $RGB;
551                                $RGB2 = self::parseGnumericColour($styleAttributes["PatternColor"]);
552                                $styleArray['fill']['endcolor']['rgb'] = $RGB2;
553                                switch ($shade) {
554                                    case '1':
555                                        $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_SOLID;
556                                        break;
557                                    case '2':
558                                        $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR;
559                                        break;
560                                    case '3':
561                                        $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_GRADIENT_PATH;
562                                        break;
563                                    case '4':
564                                        $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_DARKDOWN;
565                                        break;
566                                    case '5':
567                                        $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_DARKGRAY;
568                                        break;
569                                    case '6':
570                                        $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_DARKGRID;
571                                        break;
572                                    case '7':
573                                        $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_DARKHORIZONTAL;
574                                        break;
575                                    case '8':
576                                        $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_DARKTRELLIS;
577                                        break;
578                                    case '9':
579                                        $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_DARKUP;
580                                        break;
581                                    case '10':
582                                        $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_DARKVERTICAL;
583                                        break;
584                                    case '11':
585                                        $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_GRAY0625;
586                                        break;
587                                    case '12':
588                                        $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_GRAY125;
589                                        break;
590                                    case '13':
591                                        $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_LIGHTDOWN;
592                                        break;
593                                    case '14':
594                                        $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_LIGHTGRAY;
595                                        break;
596                                    case '15':
597                                        $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_LIGHTGRID;
598                                        break;
599                                    case '16':
600                                        $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_LIGHTHORIZONTAL;
601                                        break;
602                                    case '17':
603                                        $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_LIGHTTRELLIS;
604                                        break;
605                                    case '18':
606                                        $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_LIGHTUP;
607                                        break;
608                                    case '19':
609                                        $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_LIGHTVERTICAL;
610                                        break;
611                                    case '20':
612                                        $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_MEDIUMGRAY;
613                                        break;
614                                }
615                            }
616
617                            $fontAttributes = $styleRegion->Style->Font->attributes();
618//                            var_dump($fontAttributes);
619//                            echo '<br />';
620                            $styleArray['font']['name'] = (string) $styleRegion->Style->Font;
621                            $styleArray['font']['size'] = intval($fontAttributes['Unit']);
622                            $styleArray['font']['bold'] = ($fontAttributes['Bold'] == '1') ? true : false;
623                            $styleArray['font']['italic'] = ($fontAttributes['Italic'] == '1') ? true : false;
624                            $styleArray['font']['strike'] = ($fontAttributes['StrikeThrough'] == '1') ? true : false;
625                            switch ($fontAttributes['Underline']) {
626                                case '1':
627                                    $styleArray['font']['underline'] = PHPExcel_Style_Font::UNDERLINE_SINGLE;
628                                    break;
629                                case '2':
630                                    $styleArray['font']['underline'] = PHPExcel_Style_Font::UNDERLINE_DOUBLE;
631                                    break;
632                                case '3':
633                                    $styleArray['font']['underline'] = PHPExcel_Style_Font::UNDERLINE_SINGLEACCOUNTING;
634                                    break;
635                                case '4':
636                                    $styleArray['font']['underline'] = PHPExcel_Style_Font::UNDERLINE_DOUBLEACCOUNTING;
637                                    break;
638                                default:
639                                    $styleArray['font']['underline'] = PHPExcel_Style_Font::UNDERLINE_NONE;
640                                    break;
641                            }
642                            switch ($fontAttributes['Script']) {
643                                case '1':
644                                    $styleArray['font']['superScript'] = true;
645                                    break;
646                                case '-1':
647                                    $styleArray['font']['subScript'] = true;
648                                    break;
649                            }
650
651                            if (isset($styleRegion->Style->StyleBorder)) {
652                                if (isset($styleRegion->Style->StyleBorder->Top)) {
653                                    $styleArray['borders']['top'] = self::parseBorderAttributes($styleRegion->Style->StyleBorder->Top->attributes());
654                                }
655                                if (isset($styleRegion->Style->StyleBorder->Bottom)) {
656                                    $styleArray['borders']['bottom'] = self::parseBorderAttributes($styleRegion->Style->StyleBorder->Bottom->attributes());
657                                }
658                                if (isset($styleRegion->Style->StyleBorder->Left)) {
659                                    $styleArray['borders']['left'] = self::parseBorderAttributes($styleRegion->Style->StyleBorder->Left->attributes());
660                                }
661                                if (isset($styleRegion->Style->StyleBorder->Right)) {
662                                    $styleArray['borders']['right'] = self::parseBorderAttributes($styleRegion->Style->StyleBorder->Right->attributes());
663                                }
664                                if ((isset($styleRegion->Style->StyleBorder->Diagonal)) && (isset($styleRegion->Style->StyleBorder->{'Rev-Diagonal'}))) {
665                                    $styleArray['borders']['diagonal'] = self::parseBorderAttributes($styleRegion->Style->StyleBorder->Diagonal->attributes());
666                                    $styleArray['borders']['diagonaldirection'] = PHPExcel_Style_Borders::DIAGONAL_BOTH;
667                                } elseif (isset($styleRegion->Style->StyleBorder->Diagonal)) {
668                                    $styleArray['borders']['diagonal'] = self::parseBorderAttributes($styleRegion->Style->StyleBorder->Diagonal->attributes());
669                                    $styleArray['borders']['diagonaldirection'] = PHPExcel_Style_Borders::DIAGONAL_UP;
670                                } elseif (isset($styleRegion->Style->StyleBorder->{'Rev-Diagonal'})) {
671                                    $styleArray['borders']['diagonal'] = self::parseBorderAttributes($styleRegion->Style->StyleBorder->{'Rev-Diagonal'}->attributes());
672                                    $styleArray['borders']['diagonaldirection'] = PHPExcel_Style_Borders::DIAGONAL_DOWN;
673                                }
674                            }
675                            if (isset($styleRegion->Style->HyperLink)) {
676                                //    TO DO
677                                $hyperlink = $styleRegion->Style->HyperLink->attributes();
678                            }
679                        }
680//                        var_dump($styleArray);
681//                        echo '<br />';
682                        $objPHPExcel->getActiveSheet()->getStyle($cellRange)->applyFromArray($styleArray);
683                    }
684                }
685            }
686
687            if ((!$this->readDataOnly) && (isset($sheet->Cols))) {
688                //    Column Widths
689                $columnAttributes = $sheet->Cols->attributes();
690                $defaultWidth = $columnAttributes['DefaultSizePts']  / 5.4;
691                $c = 0;
692                foreach ($sheet->Cols->ColInfo as $columnOverride) {
693                    $columnAttributes = $columnOverride->attributes();
694                    $column = $columnAttributes['No'];
695                    $columnWidth = $columnAttributes['Unit']  / 5.4;
696                    $hidden = ((isset($columnAttributes['Hidden'])) && ($columnAttributes['Hidden'] == '1')) ? true : false;
697                    $columnCount = (isset($columnAttributes['Count'])) ? $columnAttributes['Count'] : 1;
698                    while ($c < $column) {
699                        $objPHPExcel->getActiveSheet()->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($c))->setWidth($defaultWidth);
700                        ++$c;
701                    }
702                    while (($c < ($column+$columnCount)) && ($c <= $maxCol)) {
703                        $objPHPExcel->getActiveSheet()->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($c))->setWidth($columnWidth);
704                        if ($hidden) {
705                            $objPHPExcel->getActiveSheet()->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($c))->setVisible(false);
706                        }
707                        ++$c;
708                    }
709                }
710                while ($c <= $maxCol) {
711                    $objPHPExcel->getActiveSheet()->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($c))->setWidth($defaultWidth);
712                    ++$c;
713                }
714            }
715
716            if ((!$this->readDataOnly) && (isset($sheet->Rows))) {
717                //    Row Heights
718                $rowAttributes = $sheet->Rows->attributes();
719                $defaultHeight = $rowAttributes['DefaultSizePts'];
720                $r = 0;
721
722                foreach ($sheet->Rows->RowInfo as $rowOverride) {
723                    $rowAttributes = $rowOverride->attributes();
724                    $row = $rowAttributes['No'];
725                    $rowHeight = $rowAttributes['Unit'];
726                    $hidden = ((isset($rowAttributes['Hidden'])) && ($rowAttributes['Hidden'] == '1')) ? true : false;
727                    $rowCount = (isset($rowAttributes['Count'])) ? $rowAttributes['Count'] : 1;
728                    while ($r < $row) {
729                        ++$r;
730                        $objPHPExcel->getActiveSheet()->getRowDimension($r)->setRowHeight($defaultHeight);
731                    }
732                    while (($r < ($row+$rowCount)) && ($r < $maxRow)) {
733                        ++$r;
734                        $objPHPExcel->getActiveSheet()->getRowDimension($r)->setRowHeight($rowHeight);
735                        if ($hidden) {
736                            $objPHPExcel->getActiveSheet()->getRowDimension($r)->setVisible(false);
737                        }
738                    }
739                }
740                while ($r < $maxRow) {
741                    ++$r;
742                    $objPHPExcel->getActiveSheet()->getRowDimension($r)->setRowHeight($defaultHeight);
743                }
744            }
745
746            //    Handle Merged Cells in this worksheet
747            if (isset($sheet->MergedRegions)) {
748                foreach ($sheet->MergedRegions->Merge as $mergeCells) {
749                    if (strpos($mergeCells, ':') !== false) {
750                        $objPHPExcel->getActiveSheet()->mergeCells($mergeCells);
751                    }
752                }
753            }
754
755            $worksheetID++;
756        }
757
758        //    Loop through definedNames (global named ranges)
759        if (isset($gnmXML->Names)) {
760            foreach ($gnmXML->Names->Name as $namedRange) {
761                $name = (string) $namedRange->name;
762                $range = (string) $namedRange->value;
763                if (stripos($range, '#REF!') !== false) {
764                    continue;
765                }
766
767                $range = explode('!', $range);
768                $range[0] = trim($range[0], "'");
769                if ($worksheet = $objPHPExcel->getSheetByName($range[0])) {
770                    $extractedRange = str_replace('$', '', $range[1]);
771                    $objPHPExcel->addNamedRange(new PHPExcel_NamedRange($name, $worksheet, $extractedRange));
772                }
773            }
774        }
775
776        // Return
777        return $objPHPExcel;
778    }
779
780    private static function parseBorderAttributes($borderAttributes)
781    {
782        $styleArray = array();
783        if (isset($borderAttributes["Color"])) {
784            $styleArray['color']['rgb'] = self::parseGnumericColour($borderAttributes["Color"]);
785        }
786
787        switch ($borderAttributes["Style"]) {
788            case '0':
789                $styleArray['style'] = PHPExcel_Style_Border::BORDER_NONE;
790                break;
791            case '1':
792                $styleArray['style'] = PHPExcel_Style_Border::BORDER_THIN;
793                break;
794            case '2':
795                $styleArray['style'] = PHPExcel_Style_Border::BORDER_MEDIUM;
796                break;
797            case '3':
798                $styleArray['style'] = PHPExcel_Style_Border::BORDER_SLANTDASHDOT;
799                break;
800            case '4':
801                $styleArray['style'] = PHPExcel_Style_Border::BORDER_DASHED;
802                break;
803            case '5':
804                $styleArray['style'] = PHPExcel_Style_Border::BORDER_THICK;
805                break;
806            case '6':
807                $styleArray['style'] = PHPExcel_Style_Border::BORDER_DOUBLE;
808                break;
809            case '7':
810                $styleArray['style'] = PHPExcel_Style_Border::BORDER_DOTTED;
811                break;
812            case '8':
813                $styleArray['style'] = PHPExcel_Style_Border::BORDER_MEDIUMDASHED;
814                break;
815            case '9':
816                $styleArray['style'] = PHPExcel_Style_Border::BORDER_DASHDOT;
817                break;
818            case '10':
819                $styleArray['style'] = PHPExcel_Style_Border::BORDER_MEDIUMDASHDOT;
820                break;
821            case '11':
822                $styleArray['style'] = PHPExcel_Style_Border::BORDER_DASHDOTDOT;
823                break;
824            case '12':
825                $styleArray['style'] = PHPExcel_Style_Border::BORDER_MEDIUMDASHDOTDOT;
826                break;
827            case '13':
828                $styleArray['style'] = PHPExcel_Style_Border::BORDER_MEDIUMDASHDOTDOT;
829                break;
830        }
831        return $styleArray;
832    }
833
834    private function parseRichText($is = '')
835    {
836        $value = new PHPExcel_RichText();
837        $value->createText($is);
838
839        return $value;
840    }
841
842    private static function parseGnumericColour($gnmColour)
843    {
844        list($gnmR, $gnmG, $gnmB) = explode(':', $gnmColour);
845        $gnmR = substr(str_pad($gnmR, 4, '0', STR_PAD_RIGHT), 0, 2);
846        $gnmG = substr(str_pad($gnmG, 4, '0', STR_PAD_RIGHT), 0, 2);
847        $gnmB = substr(str_pad($gnmB, 4, '0', STR_PAD_RIGHT), 0, 2);
848        return $gnmR . $gnmG . $gnmB;
849    }
850}
851