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