PHPExcel

daserato

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?

Could anybody help me?

mdryan

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.

MarkBaker

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;
 }

daserato

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.

MarkBaker

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

tripexito

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?

MarkBaker

Coordinator

Jun 19, 2012 at 3:54 PM

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

tripexito

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!

daserato

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?

MarkBaker

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

daserato

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?

tripexito

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!

tripexito

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! ;)

gauge

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...)?

Steaks

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');

robustamante

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.

Mononoid

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

Manish018

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

MarkBaker

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

Marie78

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.

MarkBaker

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.

Marie78

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

sese123

Feb 20 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 ?

daserato 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?

mdryan 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.

MarkBaker 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; }

daserato 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.

MarkBaker 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

tripexito 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?

MarkBaker Coordinator Jun 19, 2012 at 3:54 PM	This writeRichTextForCharts() error has been fixed in the develop branch at the new github repository

tripexito 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!

daserato 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?

MarkBaker 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

daserato 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?

tripexito 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!

tripexito 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! ;)

gauge 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...)?

Steaks 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');

robustamante 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.

Mononoid 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

Manish018 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

MarkBaker 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

Marie78 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.

MarkBaker 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.

Marie78 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

sese123 Feb 20 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 ?

Updating...

Problem with creating charts

Text Formatting

Lists

Quotes

Code

References

Updating...

Problem with creating charts

Text Formatting

Lists

Quotes

Code

References

Delete Post X

Delete Thread X