Problem with creating charts

Topics: User Forum
Jun 12, 2012 at 9:46 AM
Edited Jun 12, 2012 at 9:58 AM

Hello,

 

I am fighting with charts in PHPExcel 1.7.7. I can create charts as:

$objPHPExcel->setActiveSheetIndex(0);

$zakres_number = "'PARETO FOR'!D$16:D$22";
$zakres_string = "'PARETO FOR'!B$16:B$22";
  $values = new PHPExcel_Chart_DataSeriesValues('Number', $zakres_number);
  $categories = new PHPExcel_Chart_DataSeriesValues('String', $zakres_string);

  $series = new PHPExcel_Chart_DataSeries(
    PHPExcel_Chart_DataSeries::TYPE_BARCHART,       // plotType
    PHPExcel_Chart_DataSeries::GROUPING_CLUSTERED,  // plotGrouping
    array(0),                                       // plotOrder
    array(),                                        // plotLabel
    array($categories),                             // plotCategory
    array($values)                                  // plotValues
  );
  $series->setPlotDirection(PHPExcel_Chart_DataSeries::DIRECTION_COL);
   
  $layout = new PHPExcel_Chart_Layout();
  $plotarea = new PHPExcel_Chart_PlotArea($layout, array($series));
   
  $chart = new PHPExcel_Chart('Wykres 1', $title, null, $plotarea);

  $chart->setTopLeftPosition('J2');
  $chart->setBottomRightPosition('R17');
$chart->$objPHPExcel->getActiveSheet()->addChart($chart);

 

A piece of code above generates a chart, but I have few problems:

1. How to add a Title?    

$title = new PHPExcel_Chart_Title(null, $layout);
$title->setCaption('Blady');

It doesn't work?

2. Categories' names don't work. It displays only column's numbers. How to fix it?

3. How to add another data serie with type LINETYPE to the existing chart? Is it possible to add another scale on the right side?

4. How to add a legend and a serie's name?

<Legend works $legend = new PHPExcel_Chart_Legend(-4152, $layout, false);>

Could anybody help me?

Jun 16, 2012 at 9:00 PM

Join the club. I've been fighting with charts too. So far I have this new improved demo:

 

require_once(dirname(__FILE__) . 'PHPExcel/Classes/PHPExcel.php');

$workbook = new PHPExcel();
$sheet = $workbook->getActiveSheet();
$sheet->fromArray(array(
  array(  '', 2010, 2011),
  array('Q1',   12,   15),
  array('Q2',   56,   73),
  array('Q3',   52,   61),
  array('Q4',   30,   32),
));
$labels = array(
  new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$B$1', null, 1),
  new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$C$1', null, 1),
);
$categories = array(
  new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$A$2:$A$5', null, 4),
  new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$A$2:$A$5', null, 4),
);
$values = array(
  new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$B$2:$B$5', null, 4),
  new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$C$2:$C$5', null, 4),
);
$series = new PHPExcel_Chart_DataSeries(
  PHPExcel_Chart_DataSeries::TYPE_BARCHART,       // plotType
  PHPExcel_Chart_DataSeries::GROUPING_CLUSTERED,  // plotGrouping
  array(0, 1),                                    // plotOrder
  $labels,                                        // plotLabel
  $categories,                                    // plotCategory
  $values                                         // plotValues
);
$series->setPlotDirection(PHPExcel_Chart_DataSeries::DIRECTION_COL);
$plotarea = new PHPExcel_Chart_PlotArea(null, array($series));
$legend = new PHPExcel_Chart_Legend(PHPExcel_Chart_Legend::POSITION_RIGHT, null, false);
$chart = new PHPExcel_Chart(
  'chart1',                                       // name
  null,                                           // title
  $legend,                                        // legend
  $plotarea,                                      // plotArea
  true,                                           // plotVisibleOnly
  0,                                              // displayBlanksAs
  null,                                           // xAxisLabel
  null                                            // yAxisLabel
);
$chart->setTopLeftPosition('A7');
$chart->setBottomRightPosition('H20');
$sheet->addChart($chart);
$writer = PHPExcel_IOFactory::createWriter($workbook, 'Excel2007');
$writer->setIncludeCharts(TRUE);
$writer->save('test.xlsx');

 

To answer your questions:

1. How to add a title

I wish I knew. I'll keep working on it more for the sake of axis labels (which appear to have the same problems)

2. Category names don't work

This tripped me up as well. You need to specify the number of points in the data series for the writer to include the category names

3. Multiple series / axis

Not sure, not something I've needed to do, although see this if you haven't already: http://phpexcel.codeplex.com/workitem/18228. Here I have one DataSeries object with multiple DataSeriesValues which is more than sufficient for my needs.

4. Legend / series names

Sounds like you partly worked this out for yourself, although see also my example.

 

For my immediate project I really need to work out how to label the axis. It seems like this should be possible from the existing code, but I can't make it work. Has anyone succeeded in doing this?

Separately I will also want to set min and max values on one of the axis, plus leave no gap between bars. These settings are currently hard coded in the writer and it doesn't look like too much work to extend it to cope with these extra parameters. I'm reluctant to start until I work out what's wrong with the captions though.

Coordinator
Jun 17, 2012 at 10:32 AM

Adding the following to the PHPExcel_Chart class may help:

 /**
  * Set Title
  *
  * @param PHPExcel_Chart_Title $title
  * @return PHPExcel_Chart
  */
 public function setTitle(PHPExcel_Chart_Title $title) {
     $this->_title = $title;
     return $this;
 }
  /**
  * Set Legend
  *
  * @param PHPExcel_Chart_Legend $legend
  * @return PHPExcel_Chart
  */
 public function setLegend(PHPExcel_Chart_Legend $legend) {
     $this->_legend = $legend;
     return $this;
 }
 /**
  * Set X-Axis Label
  *
  * @param PHPExcel_Chart_Title $label
  * @return PHPExcel_Chart
  */
 public function setXAxisLabel(PHPExcel_Chart_Legend $label) {
     $this->_xAxisLabel = $label;
     return $this;
 }
 /**
  * Set Y-Axis Label
  *
  * @param PHPExcel_Chart_Title $label
  * @return PHPExcel_Chart
  */
 public function setYAxisLabel(PHPExcel_Chart_Legend $label) {
     $this->_yAxisLabel = $label;
     return $this;
 }
Jun 18, 2012 at 7:15 AM
Edited Jun 18, 2012 at 9:55 AM

Thanks a lot for Your answers. I will check it ASAP.

I have checked the code above. When I want to add a title:

$title = new PHPExcel_Chart_Title('Title', null);
$chart->setTitle($title);

it fails. I do not know exactly but I think there is something wrong in the constructor of PHPExcel_Chart_Title.

 

Setting axis labels with the code (I have changed PHPExcel_Chart_Legend into PHPExcel_Chart_Title):

public function setYAxisLabel(PHPExcel_Chart_Title $label) {
     $this->_yAxisLabel = $label;
     return $this;
 }

doesn't work.

 

To @mdryan Thanks a lot. Unfortunately, I still need another right scale. My first values are numbers, second are percentages (yes, I can multiply by the max value, but this isn't the point). I still cannot add title and axis names and I will be grateful if You will share Your solution with us when You will have it.

Coordinator
Jun 19, 2012 at 6:49 AM

It should now be possible to set titles and axis labels using the latest version of the develop branch of the code from github https://github.com/PHPOffice/PHPExcel and I've added 33chartcreate.php (based heavily on mdryans example) as an example in /Tests

Jun 19, 2012 at 1:28 PM

I've created an example to prove PHPExcel chart functionality based on mdryans example, with multiple series (barchart and linechart).

 

/** Include path * */
set_include_path(get_include_path() . PATH_SEPARATOR . '../Classes/');

/** PHPExcel_IOFactory */
include 'PHPExcel/IOFactory.php';

$workbook = new PHPExcel();
$sheet = $workbook->getActiveSheet();
$sheet->fromArray(array(
    array('', 2010, 2011, 'Porc'),
    array('Q1', 12, 15, 1.25),
    array('Q2', 56, 73, 1.30),
    array('Q3', 52, 61, 1.17),
    array('Q4', 30, 32, 1.07),
));

$sheet->getStyle('D2:D5')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE);

$labels = array(
    new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$B$1', null, 1),
    new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$C$1', null, 1),
    new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$D$1', null, 1)
);
$categories = array(
    new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$A$2:$A$5', null, 4),
    new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$A$2:$A$5', null, 4),
    new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$A$2:$A$5', null, 4)
);
$values = array(
    new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$B$2:$B$5', null, 4),
    new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$C$2:$C$5', null, 4)
);

$series = new PHPExcel_Chart_DataSeries(
                PHPExcel_Chart_DataSeries::TYPE_BARCHART, // plotType
                PHPExcel_Chart_DataSeries::GROUPING_CLUSTERED, // plotGrouping
                array(0, 1), // plotOrder
                $labels, // plotLabel
                $categories, // plotCategory
                $values // plotValues
);

$values2 = array(
    null, null,
    new PHPExcel_Chart_DataSeriesValues('Percentage', 'Worksheet!$D$2:$D$5', null, 4)
);

$series2 = new PHPExcel_Chart_DataSeries(
                PHPExcel_Chart_DataSeries::TYPE_LINECHART, // plotType
                PHPExcel_Chart_DataSeries::GROUPING_STANDARD, // plotGrouping
                array(2), // plotOrder
                $labels, // plotLabel
                $categories, // plotCategory
                $values2, // plotValues
                true,
                PHPExcel_Chart_DataSeries::STYLE_FILLED
);

$series->setPlotDirection(PHPExcel_Chart_DataSeries::DIRECTION_COL);
$layout = new PHPExcel_Chart_Layout();
$plotarea = new PHPExcel_Chart_PlotArea($layout, array($series, $series2));
$legend = new PHPExcel_Chart_Legend(PHPExcel_Chart_Legend::POSITION_RIGHT, null, false);
$plotTitle = new PHPExcel_Chart_Title('Test Multiple Series / Types PHPExcel Chart');
//$plotTitle = null;
$xTitle = new PHPExcel_Chart_Title('xAxisLabel');
$yTitle = new PHPExcel_Chart_Title('yAxisLabel');
//new PHPExcel_Chart($name, $title, $legend, $plotArea, $plotVisibleOnly, $displayBlanksAs, $xAxisLabel, $yAxisLabel)
$chart = new PHPExcel_Chart(
                'chart1', // name
                $plotTitle, // title
                $legend, // legend
                $plotarea, // plotArea
                true, // plotVisibleOnly
                0, // displayBlanksAs
                $xTitle, // xAxisLabel
                $yTitle // yAxisLabel
);
$chart->setTopLeftPosition('A7');
$chart->setBottomRightPosition('H20');
$sheet->addChart($chart);
$writer = PHPExcel_IOFactory::createWriter($workbook, 'Excel2007');
$writer->setIncludeCharts(TRUE);
$outputFileName = str_replace('.php', '.xlsx', __FILE__);
$writer->save($outputFileName);

 

 

In the example, it's possible to set xAxis and yAxis titles, but I'm still getting trouble with chart main title. I got this error message. please help!

 

PHP Catchable fatal error:  Argument 2 passed to PHPExcel_Writer_Excel2007_StringTable::writeRichTextForCharts() must be an instance of PHPExcel_RichText, string given, called in D:\SERVIDORES\Apache2.2\htdocs\PHPExcel\1.7.7\Classes\PHPExcel\Writer\Excel2007\Chart.php on line 141 and defined in D:\SERVIDORES\Apache2.2\htdocs\PHPExcel\1.7.7\Classes\PHPExcel\W
riter\Excel2007\StringTable.php on line 232
PHP Stack trace:
PHP   1. {main}() D:\SERVIDORES\Apache2.2\htdocs\PHPExcel\1.7.7\Tests\00CrearGrafico.php:0
PHP   2. PHPExcel_Writer_Excel2007->save() D:\SERVIDORES\Apache2.2\htdocs\PHPExcel\1.7.7\Tests\00CrearGrafico.php:86
PHP   3. PHPExcel_Writer_Excel2007_Chart->writeChart() D:\SERVIDORES\Apache2.2\htdocs\PHPExcel\1.7.7\Classes\PHPExcel\Writer\Excel2007.php:287
PHP   4. PHPExcel_Writer_Excel2007_Chart->_writeTitle() D:\SERVIDORES\Apache2.2\htdocs\PHPExcel\1.7.7\Classes\PHPExcel\Writer\Excel2007\Chart.php:80
PHP   5. PHPExcel_Writer_Excel2007_StringTable->writeRichTextForCharts() D:\SERVIDORES\Apache2.2\htdocs\PHPExcel\1.7.7\Classes\PHPExcel\Writer\Excel2007\Chart.php:141

 

PS: Is there any possibility to set linechart to secondary yAxis to create a "pareto" chart type?

Coordinator
Jun 19, 2012 at 3:54 PM

This writeRichTextForCharts() error has been fixed in the develop branch at the new github repository

Jun 19, 2012 at 6:13 PM

Hi Mark, thanks for your reply. ;)

I've checked the github repository, but I'm still getting same kind of warnings. Please help! regards from Chile!

Jun 20, 2012 at 7:15 AM
Edited Jun 20, 2012 at 7:21 AM

Hi,

I am getting this problem, too. I don't know what I am doing wrong?!

With this line $title = new PHPExcel_Chart_Title('Test Chart'); and having $title in the constructor of PHPExcel_Chart, I am getting no chart, only problem with a shape of the chart sent by MS Excel.

 

tripexito,

My intention is to create a pareto chart type and a second yAxis is my need.

To Your example, I am still getting few problems: I have only one serie name in legend multiplied by 3 (2010) and none axis labels. I am sending a file to a browser, maybe this is why I have these problems?

Coordinator
Jun 20, 2012 at 7:56 AM
Edited Jun 20, 2012 at 8:00 AM

Did you pull the develop branch rather than the master branch? master is intended only for releases, develop should be the latest working code; the charts branch is for additional development on charts, and should also include this fix.

Have you tested the new /Tests/33createchart.php script?

 

Secondary Y-axes are not an option at present

Jun 20, 2012 at 10:16 AM
Edited Jun 20, 2012 at 10:17 AM

Yes, I did pull the develop branch and tested /Tests/33createchart.php script. When I change in PHPExcel_Chart constructor $title to null it generates a file without a chart. When I change axis labels to null, it generates a file with a chart, legend is ok.

/Tests/33createchart.php script with changes below works:

$chart = new PHPExcel_Chart(
'chart1',    // name
null,    // title
$legend,    // legend
$plotarea,    // plotArea
true,    // plotVisibleOnly
0,    // displayBlanksAs
null,   // xAxisLabel
null    // yAxisLabel
);

 

Where did I make a mistake?

Jun 20, 2012 at 12:53 PM
Edited Jun 20, 2012 at 1:06 PM

@daserato, I haven't tried my example sending a file to browser (file download), just by command line ;) and it's working fine!

Jun 29, 2012 at 2:14 PM

Hi, I've tested it by sending a file to browser, and it works too ;)

Now, MarkBaker, Is there any possibilities to implement multiple series (bars and lines chart together, like a pareto chart), with line chart on secondary Y-Axis ASAP?

Or how may I implement it? Please help! Thanks! ;)

Jun 29, 2012 at 2:37 PM

Hi all,

just another question: is it possible to remove the outer border of the chart (or make it white...)?

Jul 2, 2012 at 2:23 PM
Edited Jul 2, 2012 at 2:45 PM

Hello, I am trying to create a pie chart.  Everything seems to work, but no matter what I can't get the legend to display by default.  Going into the Layout ribbon and turning on the legend there makes it appear fine.  I am using the code from the developers branch of GitHub.  Here is the code based off mdryan that I am using.

Edit: Nevermind, setting the plotGrouping attribute in the Dataseries to null seems to fix it in case anyone else was wondering.

	$workbook = new PHPExcel();
	$sheet = $workbook->getActiveSheet();
	$sheet->fromArray(array(
		array(  '', ''),
		array('Q1',   12),
		array('Q2',   56),
		array('Q3',   52),
		array('Q4',   30),
	));
	$categories = array(
		new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$A$2:$A$5', null, 4)
	);
	$values = array(
		new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$B$2:$B$5', null, 4)
	);
	$series = new PHPExcel_Chart_DataSeries(
		PHPExcel_Chart_DataSeries::TYPE_PIECHART,       // plotType
		PHPExcel_Chart_DataSeries::GROUPING_CLUSTERED,  // plotGrouping
		array(0),                                    	// plotOrder
		null,                                        	// plotLabel
		$categories,                                    // plotCategory
		$values                                         // plotValues
	);
	$title = new PHPExcel_Chart_Title('Test Pie Chart');
	$plotarea = new PHPExcel_Chart_PlotArea(null, array($series));
	$legend = new PHPExcel_Chart_Legend(PHPExcel_Chart_Legend::POSITION_RIGHT, null, false);
	$chart = new PHPExcel_Chart(
		'chart1',                                       // name
		$title,                                         // title
		$legend,                                        // legend
		$plotarea,                                      // plotArea
		true,                                           // plotVisibleOnly
		0,                                              // displayBlanksAs
		null,                                           // xAxisLabel
		null                                            // yAxisLabel
	);
	$chart->setTopLeftPosition('A7');
	$chart->setBottomRightPosition('H20');
	$sheet->addChart($chart);
	$writer = PHPExcel_IOFactory::createWriter($workbook, 'Excel2007');
	$writer->setIncludeCharts(TRUE);
	$writer->save('test.xlsx');
Jul 20, 2012 at 6:34 PM

Hi.

How I can display the categories and values in a PIE CHART as labels? Like the template example "32readwritePieChart3.xlsx".

Thanks you.

Regards.

Jul 24, 2012 at 8:13 AM
Edited Jul 24, 2012 at 8:17 AM

Just use below. It's work for me

$title = new PHPExcel_Chart_Title(new PHPExcel_RichText(new PHPExcel_Cell('A','1','Ttile Chart',PHPExcel_Cell_DataType::TYPE_STRING,$sheet)));

 

Mononoid

Aug 30, 2012 at 7:27 AM

hi there ,

i want to implement some charts like bubble,stock,scatter,radar etc. but i am not getting any solution from anywhere and also there is not mentioning about the charts in the documentation of phpexcel...so just help me in this ..m stuck in this from many days..

please help

Coordinator
Sep 1, 2012 at 11:26 AM

I'm putting together some examples for creating the different chart types in the /Tests folder on github at the moment

Sep 3, 2012 at 9:12 PM
Edited Sep 3, 2012 at 9:12 PM

Hi

PHPExcel is a nice product.

I try to create graph.

With the version 1.7.7 downloaded and MarkBaker examples (33chartcreate-area and others) I succeed to create various type of graphs.

The only problem is that they are perfect in Excel 2010 but cannot be open in Excel 2007.

I also tried to modify a workbook containing a graph just adding the line
$writer->setIncludeCharts(TRUE);
but it does not work.

I tried the example 32ReadWrite with graph but I have this error message : Warning: Invalid argument supplied for foreach() in /classes/PHPExcel/Writer/Excel2007/Chart.php on line 956

I downloaded some new files : with the new version of /classes/PHPExcel/Writer/Excel2007/Chart.php, the 32ReadWrite script works well but the scripts used to create graphs (33chartcreate-area and others) do not work with this new Chart.php version.
I got this error : Catchable fatal error: Argument 5 passed to PHPExcel_Writer_Excel2007_Chart::_writePlotArea() must be an instance of PHPExcel_Worksheet, null given, called in /classes/PHPExcel/Writer/Excel2007/Chart.php on line 91 and defined in /classes/PHPExcel/Writer/Excel2007/Chart.php on line 226.

I also tried to change other files (/classes/PHPExcel/Worksheet.php - /classes/PHPExcel/Writer/Excel2007/Worksheet.php - classes/PHPExcel/Writer/Excel2007/Workbook.php) but I also get error messages about each of these files.

What are the files I have to change to be able to use the new /classes/PHPExcel/Writer/Excel2007/Chart.php  with the scripts used to created graphs ( 33chartcreate-area and others).

Thanks.

Coordinator
Sep 4, 2012 at 6:44 AM

If you try working with the latest github code, you should find that these issues have been resolved.

Sep 4, 2012 at 9:46 PM
Edited Sep 4, 2012 at 10:00 PM

Hi Mark

I downloaded more files from github and now everything is working !

Thanks

Feb 20, 2013 at 6:32 AM
Hello,
 I use the exemple "33chartcreate-pie.php" with PHPExcel 1.7.8 and the legend doesn't appear on my graph.
Have you found a solution ?