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