1<?php
2
3namespace PhpOffice\PhpSpreadsheet\Reader\Xlsx;
4
5use PhpOffice\PhpSpreadsheet\Calculation\Functions;
6use PhpOffice\PhpSpreadsheet\Chart\DataSeries;
7use PhpOffice\PhpSpreadsheet\Chart\DataSeriesValues;
8use PhpOffice\PhpSpreadsheet\Chart\Layout;
9use PhpOffice\PhpSpreadsheet\Chart\Legend;
10use PhpOffice\PhpSpreadsheet\Chart\PlotArea;
11use PhpOffice\PhpSpreadsheet\Chart\Title;
12use PhpOffice\PhpSpreadsheet\RichText\RichText;
13use PhpOffice\PhpSpreadsheet\Style\Color;
14use PhpOffice\PhpSpreadsheet\Style\Font;
15use SimpleXMLElement;
16
17class Chart
18{
19    /**
20     * @param SimpleXMLElement $component
21     * @param string $name
22     * @param string $format
23     *
24     * @return null|bool|float|int|string
25     */
26    private static function getAttribute(SimpleXMLElement $component, $name, $format)
27    {
28        $attributes = $component->attributes();
29        if (isset($attributes[$name])) {
30            if ($format == 'string') {
31                return (string) $attributes[$name];
32            } elseif ($format == 'integer') {
33                return (int) $attributes[$name];
34            } elseif ($format == 'boolean') {
35                return (bool) ($attributes[$name] === '0' || $attributes[$name] !== 'true') ? false : true;
36            }
37
38            return (float) $attributes[$name];
39        }
40
41        return null;
42    }
43
44    private static function readColor($color, $background = false)
45    {
46        if (isset($color['rgb'])) {
47            return (string) $color['rgb'];
48        } elseif (isset($color['indexed'])) {
49            return Color::indexedColor($color['indexed'] - 7, $background)->getARGB();
50        }
51    }
52
53    /**
54     * @param SimpleXMLElement $chartElements
55     * @param string $chartName
56     *
57     * @return \PhpOffice\PhpSpreadsheet\Chart\Chart
58     */
59    public static function readChart(SimpleXMLElement $chartElements, $chartName)
60    {
61        $namespacesChartMeta = $chartElements->getNamespaces(true);
62        $chartElementsC = $chartElements->children($namespacesChartMeta['c']);
63
64        $XaxisLabel = $YaxisLabel = $legend = $title = null;
65        $dispBlanksAs = $plotVisOnly = null;
66
67        foreach ($chartElementsC as $chartElementKey => $chartElement) {
68            switch ($chartElementKey) {
69                case 'chart':
70                    foreach ($chartElement as $chartDetailsKey => $chartDetails) {
71                        $chartDetailsC = $chartDetails->children($namespacesChartMeta['c']);
72                        switch ($chartDetailsKey) {
73                            case 'plotArea':
74                                $plotAreaLayout = $XaxisLable = $YaxisLable = null;
75                                $plotSeries = $plotAttributes = [];
76                                foreach ($chartDetails as $chartDetailKey => $chartDetail) {
77                                    switch ($chartDetailKey) {
78                                        case 'layout':
79                                            $plotAreaLayout = self::chartLayoutDetails($chartDetail, $namespacesChartMeta);
80
81                                            break;
82                                        case 'catAx':
83                                            if (isset($chartDetail->title)) {
84                                                $XaxisLabel = self::chartTitle($chartDetail->title->children($namespacesChartMeta['c']), $namespacesChartMeta);
85                                            }
86
87                                            break;
88                                        case 'dateAx':
89                                            if (isset($chartDetail->title)) {
90                                                $XaxisLabel = self::chartTitle($chartDetail->title->children($namespacesChartMeta['c']), $namespacesChartMeta);
91                                            }
92
93                                            break;
94                                        case 'valAx':
95                                            if (isset($chartDetail->title)) {
96                                                $YaxisLabel = self::chartTitle($chartDetail->title->children($namespacesChartMeta['c']), $namespacesChartMeta);
97                                            }
98
99                                            break;
100                                        case 'barChart':
101                                        case 'bar3DChart':
102                                            $barDirection = self::getAttribute($chartDetail->barDir, 'val', 'string');
103                                            $plotSer = self::chartDataSeries($chartDetail, $namespacesChartMeta, $chartDetailKey);
104                                            $plotSer->setPlotDirection($barDirection);
105                                            $plotSeries[] = $plotSer;
106                                            $plotAttributes = self::readChartAttributes($chartDetail);
107
108                                            break;
109                                        case 'lineChart':
110                                        case 'line3DChart':
111                                            $plotSeries[] = self::chartDataSeries($chartDetail, $namespacesChartMeta, $chartDetailKey);
112                                            $plotAttributes = self::readChartAttributes($chartDetail);
113
114                                            break;
115                                        case 'areaChart':
116                                        case 'area3DChart':
117                                            $plotSeries[] = self::chartDataSeries($chartDetail, $namespacesChartMeta, $chartDetailKey);
118                                            $plotAttributes = self::readChartAttributes($chartDetail);
119
120                                            break;
121                                        case 'doughnutChart':
122                                        case 'pieChart':
123                                        case 'pie3DChart':
124                                            $explosion = isset($chartDetail->ser->explosion);
125                                            $plotSer = self::chartDataSeries($chartDetail, $namespacesChartMeta, $chartDetailKey);
126                                            $plotSer->setPlotStyle($explosion);
127                                            $plotSeries[] = $plotSer;
128                                            $plotAttributes = self::readChartAttributes($chartDetail);
129
130                                            break;
131                                        case 'scatterChart':
132                                            $scatterStyle = self::getAttribute($chartDetail->scatterStyle, 'val', 'string');
133                                            $plotSer = self::chartDataSeries($chartDetail, $namespacesChartMeta, $chartDetailKey);
134                                            $plotSer->setPlotStyle($scatterStyle);
135                                            $plotSeries[] = $plotSer;
136                                            $plotAttributes = self::readChartAttributes($chartDetail);
137
138                                            break;
139                                        case 'bubbleChart':
140                                            $bubbleScale = self::getAttribute($chartDetail->bubbleScale, 'val', 'integer');
141                                            $plotSer = self::chartDataSeries($chartDetail, $namespacesChartMeta, $chartDetailKey);
142                                            $plotSer->setPlotStyle($bubbleScale);
143                                            $plotSeries[] = $plotSer;
144                                            $plotAttributes = self::readChartAttributes($chartDetail);
145
146                                            break;
147                                        case 'radarChart':
148                                            $radarStyle = self::getAttribute($chartDetail->radarStyle, 'val', 'string');
149                                            $plotSer = self::chartDataSeries($chartDetail, $namespacesChartMeta, $chartDetailKey);
150                                            $plotSer->setPlotStyle($radarStyle);
151                                            $plotSeries[] = $plotSer;
152                                            $plotAttributes = self::readChartAttributes($chartDetail);
153
154                                            break;
155                                        case 'surfaceChart':
156                                        case 'surface3DChart':
157                                            $wireFrame = self::getAttribute($chartDetail->wireframe, 'val', 'boolean');
158                                            $plotSer = self::chartDataSeries($chartDetail, $namespacesChartMeta, $chartDetailKey);
159                                            $plotSer->setPlotStyle($wireFrame);
160                                            $plotSeries[] = $plotSer;
161                                            $plotAttributes = self::readChartAttributes($chartDetail);
162
163                                            break;
164                                        case 'stockChart':
165                                            $plotSeries[] = self::chartDataSeries($chartDetail, $namespacesChartMeta, $chartDetailKey);
166                                            $plotAttributes = self::readChartAttributes($plotAreaLayout);
167
168                                            break;
169                                    }
170                                }
171                                if ($plotAreaLayout == null) {
172                                    $plotAreaLayout = new Layout();
173                                }
174                                $plotArea = new PlotArea($plotAreaLayout, $plotSeries);
175                                self::setChartAttributes($plotAreaLayout, $plotAttributes);
176
177                                break;
178                            case 'plotVisOnly':
179                                $plotVisOnly = self::getAttribute($chartDetails, 'val', 'string');
180
181                                break;
182                            case 'dispBlanksAs':
183                                $dispBlanksAs = self::getAttribute($chartDetails, 'val', 'string');
184
185                                break;
186                            case 'title':
187                                $title = self::chartTitle($chartDetails, $namespacesChartMeta);
188
189                                break;
190                            case 'legend':
191                                $legendPos = 'r';
192                                $legendLayout = null;
193                                $legendOverlay = false;
194                                foreach ($chartDetails as $chartDetailKey => $chartDetail) {
195                                    switch ($chartDetailKey) {
196                                        case 'legendPos':
197                                            $legendPos = self::getAttribute($chartDetail, 'val', 'string');
198
199                                            break;
200                                        case 'overlay':
201                                            $legendOverlay = self::getAttribute($chartDetail, 'val', 'boolean');
202
203                                            break;
204                                        case 'layout':
205                                            $legendLayout = self::chartLayoutDetails($chartDetail, $namespacesChartMeta);
206
207                                            break;
208                                    }
209                                }
210                                $legend = new Legend($legendPos, $legendLayout, $legendOverlay);
211
212                                break;
213                        }
214                    }
215            }
216        }
217        $chart = new \PhpOffice\PhpSpreadsheet\Chart\Chart($chartName, $title, $legend, $plotArea, $plotVisOnly, $dispBlanksAs, $XaxisLabel, $YaxisLabel);
218
219        return $chart;
220    }
221
222    private static function chartTitle(SimpleXMLElement $titleDetails, array $namespacesChartMeta)
223    {
224        $caption = [];
225        $titleLayout = null;
226        foreach ($titleDetails as $titleDetailKey => $chartDetail) {
227            switch ($titleDetailKey) {
228                case 'tx':
229                    $titleDetails = $chartDetail->rich->children($namespacesChartMeta['a']);
230                    foreach ($titleDetails as $titleKey => $titleDetail) {
231                        switch ($titleKey) {
232                            case 'p':
233                                $titleDetailPart = $titleDetail->children($namespacesChartMeta['a']);
234                                $caption[] = self::parseRichText($titleDetailPart);
235                        }
236                    }
237
238                    break;
239                case 'layout':
240                    $titleLayout = self::chartLayoutDetails($chartDetail, $namespacesChartMeta);
241
242                    break;
243            }
244        }
245
246        return new Title($caption, $titleLayout);
247    }
248
249    private static function chartLayoutDetails($chartDetail, $namespacesChartMeta)
250    {
251        if (!isset($chartDetail->manualLayout)) {
252            return null;
253        }
254        $details = $chartDetail->manualLayout->children($namespacesChartMeta['c']);
255        if ($details === null) {
256            return null;
257        }
258        $layout = [];
259        foreach ($details as $detailKey => $detail) {
260            $layout[$detailKey] = self::getAttribute($detail, 'val', 'string');
261        }
262
263        return new Layout($layout);
264    }
265
266    private static function chartDataSeries($chartDetail, $namespacesChartMeta, $plotType)
267    {
268        $multiSeriesType = null;
269        $smoothLine = false;
270        $seriesLabel = $seriesCategory = $seriesValues = $plotOrder = [];
271
272        $seriesDetailSet = $chartDetail->children($namespacesChartMeta['c']);
273        foreach ($seriesDetailSet as $seriesDetailKey => $seriesDetails) {
274            switch ($seriesDetailKey) {
275                case 'grouping':
276                    $multiSeriesType = self::getAttribute($chartDetail->grouping, 'val', 'string');
277
278                    break;
279                case 'ser':
280                    $marker = null;
281                    $seriesIndex = '';
282                    foreach ($seriesDetails as $seriesKey => $seriesDetail) {
283                        switch ($seriesKey) {
284                            case 'idx':
285                                $seriesIndex = self::getAttribute($seriesDetail, 'val', 'integer');
286
287                                break;
288                            case 'order':
289                                $seriesOrder = self::getAttribute($seriesDetail, 'val', 'integer');
290                                $plotOrder[$seriesIndex] = $seriesOrder;
291
292                                break;
293                            case 'tx':
294                                $seriesLabel[$seriesIndex] = self::chartDataSeriesValueSet($seriesDetail, $namespacesChartMeta);
295
296                                break;
297                            case 'marker':
298                                $marker = self::getAttribute($seriesDetail->symbol, 'val', 'string');
299
300                                break;
301                            case 'smooth':
302                                $smoothLine = self::getAttribute($seriesDetail, 'val', 'boolean');
303
304                                break;
305                            case 'cat':
306                                $seriesCategory[$seriesIndex] = self::chartDataSeriesValueSet($seriesDetail, $namespacesChartMeta);
307
308                                break;
309                            case 'val':
310                                $seriesValues[$seriesIndex] = self::chartDataSeriesValueSet($seriesDetail, $namespacesChartMeta, $marker);
311
312                                break;
313                            case 'xVal':
314                                $seriesCategory[$seriesIndex] = self::chartDataSeriesValueSet($seriesDetail, $namespacesChartMeta, $marker);
315
316                                break;
317                            case 'yVal':
318                                $seriesValues[$seriesIndex] = self::chartDataSeriesValueSet($seriesDetail, $namespacesChartMeta, $marker);
319
320                                break;
321                        }
322                    }
323            }
324        }
325
326        return new DataSeries($plotType, $multiSeriesType, $plotOrder, $seriesLabel, $seriesCategory, $seriesValues, $smoothLine);
327    }
328
329    private static function chartDataSeriesValueSet($seriesDetail, $namespacesChartMeta, $marker = null)
330    {
331        if (isset($seriesDetail->strRef)) {
332            $seriesSource = (string) $seriesDetail->strRef->f;
333            $seriesData = self::chartDataSeriesValues($seriesDetail->strRef->strCache->children($namespacesChartMeta['c']), 's');
334
335            return new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, $seriesSource, $seriesData['formatCode'], $seriesData['pointCount'], $seriesData['dataValues'], $marker);
336        } elseif (isset($seriesDetail->numRef)) {
337            $seriesSource = (string) $seriesDetail->numRef->f;
338            $seriesData = self::chartDataSeriesValues($seriesDetail->numRef->numCache->children($namespacesChartMeta['c']));
339
340            return new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, $seriesSource, $seriesData['formatCode'], $seriesData['pointCount'], $seriesData['dataValues'], $marker);
341        } elseif (isset($seriesDetail->multiLvlStrRef)) {
342            $seriesSource = (string) $seriesDetail->multiLvlStrRef->f;
343            $seriesData = self::chartDataSeriesValuesMultiLevel($seriesDetail->multiLvlStrRef->multiLvlStrCache->children($namespacesChartMeta['c']), 's');
344            $seriesData['pointCount'] = count($seriesData['dataValues']);
345
346            return new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, $seriesSource, $seriesData['formatCode'], $seriesData['pointCount'], $seriesData['dataValues'], $marker);
347        } elseif (isset($seriesDetail->multiLvlNumRef)) {
348            $seriesSource = (string) $seriesDetail->multiLvlNumRef->f;
349            $seriesData = self::chartDataSeriesValuesMultiLevel($seriesDetail->multiLvlNumRef->multiLvlNumCache->children($namespacesChartMeta['c']), 's');
350            $seriesData['pointCount'] = count($seriesData['dataValues']);
351
352            return new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, $seriesSource, $seriesData['formatCode'], $seriesData['pointCount'], $seriesData['dataValues'], $marker);
353        }
354
355        return null;
356    }
357
358    private static function chartDataSeriesValues($seriesValueSet, $dataType = 'n')
359    {
360        $seriesVal = [];
361        $formatCode = '';
362        $pointCount = 0;
363
364        foreach ($seriesValueSet as $seriesValueIdx => $seriesValue) {
365            switch ($seriesValueIdx) {
366                case 'ptCount':
367                    $pointCount = self::getAttribute($seriesValue, 'val', 'integer');
368
369                    break;
370                case 'formatCode':
371                    $formatCode = (string) $seriesValue;
372
373                    break;
374                case 'pt':
375                    $pointVal = self::getAttribute($seriesValue, 'idx', 'integer');
376                    if ($dataType == 's') {
377                        $seriesVal[$pointVal] = (string) $seriesValue->v;
378                    } elseif ($seriesValue->v === Functions::NA()) {
379                        $seriesVal[$pointVal] = null;
380                    } else {
381                        $seriesVal[$pointVal] = (float) $seriesValue->v;
382                    }
383
384                    break;
385            }
386        }
387
388        return [
389            'formatCode' => $formatCode,
390            'pointCount' => $pointCount,
391            'dataValues' => $seriesVal,
392        ];
393    }
394
395    private static function chartDataSeriesValuesMultiLevel($seriesValueSet, $dataType = 'n')
396    {
397        $seriesVal = [];
398        $formatCode = '';
399        $pointCount = 0;
400
401        foreach ($seriesValueSet->lvl as $seriesLevelIdx => $seriesLevel) {
402            foreach ($seriesLevel as $seriesValueIdx => $seriesValue) {
403                switch ($seriesValueIdx) {
404                    case 'ptCount':
405                        $pointCount = self::getAttribute($seriesValue, 'val', 'integer');
406
407                        break;
408                    case 'formatCode':
409                        $formatCode = (string) $seriesValue;
410
411                        break;
412                    case 'pt':
413                        $pointVal = self::getAttribute($seriesValue, 'idx', 'integer');
414                        if ($dataType == 's') {
415                            $seriesVal[$pointVal][] = (string) $seriesValue->v;
416                        } elseif ($seriesValue->v === Functions::NA()) {
417                            $seriesVal[$pointVal] = null;
418                        } else {
419                            $seriesVal[$pointVal][] = (float) $seriesValue->v;
420                        }
421
422                        break;
423                }
424            }
425        }
426
427        return [
428            'formatCode' => $formatCode,
429            'pointCount' => $pointCount,
430            'dataValues' => $seriesVal,
431        ];
432    }
433
434    private static function parseRichText(SimpleXMLElement $titleDetailPart)
435    {
436        $value = new RichText();
437        $objText = null;
438        foreach ($titleDetailPart as $titleDetailElementKey => $titleDetailElement) {
439            if (isset($titleDetailElement->t)) {
440                $objText = $value->createTextRun((string) $titleDetailElement->t);
441            }
442            if (isset($titleDetailElement->rPr)) {
443                if (isset($titleDetailElement->rPr->rFont['val'])) {
444                    $objText->getFont()->setName((string) $titleDetailElement->rPr->rFont['val']);
445                }
446
447                $fontSize = (self::getAttribute($titleDetailElement->rPr, 'sz', 'integer'));
448                if ($fontSize !== null) {
449                    $objText->getFont()->setSize(floor($fontSize / 100));
450                }
451
452                $fontColor = (self::getAttribute($titleDetailElement->rPr, 'color', 'string'));
453                if ($fontColor !== null) {
454                    $objText->getFont()->setColor(new Color(self::readColor($fontColor)));
455                }
456
457                $bold = self::getAttribute($titleDetailElement->rPr, 'b', 'boolean');
458                if ($bold !== null) {
459                    $objText->getFont()->setBold($bold);
460                }
461
462                $italic = self::getAttribute($titleDetailElement->rPr, 'i', 'boolean');
463                if ($italic !== null) {
464                    $objText->getFont()->setItalic($italic);
465                }
466
467                $baseline = self::getAttribute($titleDetailElement->rPr, 'baseline', 'integer');
468                if ($baseline !== null) {
469                    if ($baseline > 0) {
470                        $objText->getFont()->setSuperscript(true);
471                    } elseif ($baseline < 0) {
472                        $objText->getFont()->setSubscript(true);
473                    }
474                }
475
476                $underscore = (self::getAttribute($titleDetailElement->rPr, 'u', 'string'));
477                if ($underscore !== null) {
478                    if ($underscore == 'sng') {
479                        $objText->getFont()->setUnderline(Font::UNDERLINE_SINGLE);
480                    } elseif ($underscore == 'dbl') {
481                        $objText->getFont()->setUnderline(Font::UNDERLINE_DOUBLE);
482                    } else {
483                        $objText->getFont()->setUnderline(Font::UNDERLINE_NONE);
484                    }
485                }
486
487                $strikethrough = (self::getAttribute($titleDetailElement->rPr, 's', 'string'));
488                if ($strikethrough !== null) {
489                    if ($strikethrough == 'noStrike') {
490                        $objText->getFont()->setStrikethrough(false);
491                    } else {
492                        $objText->getFont()->setStrikethrough(true);
493                    }
494                }
495            }
496        }
497
498        return $value;
499    }
500
501    private static function readChartAttributes($chartDetail)
502    {
503        $plotAttributes = [];
504        if (isset($chartDetail->dLbls)) {
505            if (isset($chartDetail->dLbls->howLegendKey)) {
506                $plotAttributes['showLegendKey'] = self::getAttribute($chartDetail->dLbls->showLegendKey, 'val', 'string');
507            }
508            if (isset($chartDetail->dLbls->showVal)) {
509                $plotAttributes['showVal'] = self::getAttribute($chartDetail->dLbls->showVal, 'val', 'string');
510            }
511            if (isset($chartDetail->dLbls->showCatName)) {
512                $plotAttributes['showCatName'] = self::getAttribute($chartDetail->dLbls->showCatName, 'val', 'string');
513            }
514            if (isset($chartDetail->dLbls->showSerName)) {
515                $plotAttributes['showSerName'] = self::getAttribute($chartDetail->dLbls->showSerName, 'val', 'string');
516            }
517            if (isset($chartDetail->dLbls->showPercent)) {
518                $plotAttributes['showPercent'] = self::getAttribute($chartDetail->dLbls->showPercent, 'val', 'string');
519            }
520            if (isset($chartDetail->dLbls->showBubbleSize)) {
521                $plotAttributes['showBubbleSize'] = self::getAttribute($chartDetail->dLbls->showBubbleSize, 'val', 'string');
522            }
523            if (isset($chartDetail->dLbls->showLeaderLines)) {
524                $plotAttributes['showLeaderLines'] = self::getAttribute($chartDetail->dLbls->showLeaderLines, 'val', 'string');
525            }
526        }
527
528        return $plotAttributes;
529    }
530
531    /**
532     * @param Layout $plotArea
533     * @param mixed $plotAttributes
534     */
535    private static function setChartAttributes(Layout $plotArea, $plotAttributes)
536    {
537        foreach ($plotAttributes as $plotAttributeKey => $plotAttributeValue) {
538            switch ($plotAttributeKey) {
539                case 'showLegendKey':
540                    $plotArea->setShowLegendKey($plotAttributeValue);
541
542                    break;
543                case 'showVal':
544                    $plotArea->setShowVal($plotAttributeValue);
545
546                    break;
547                case 'showCatName':
548                    $plotArea->setShowCatName($plotAttributeValue);
549
550                    break;
551                case 'showSerName':
552                    $plotArea->setShowSerName($plotAttributeValue);
553
554                    break;
555                case 'showPercent':
556                    $plotArea->setShowPercent($plotAttributeValue);
557
558                    break;
559                case 'showBubbleSize':
560                    $plotArea->setShowBubbleSize($plotAttributeValue);
561
562                    break;
563                case 'showLeaderLines':
564                    $plotArea->setShowLeaderLines($plotAttributeValue);
565
566                    break;
567            }
568        }
569    }
570}
571