PHPExcel_Chart_DataSeriesValues problem

Topics: Developer Forum
Oct 4, 2012 at 1:00 PM

Hi there, 

I have developped a program which consists of creating new Excel worksheets containing different cells. The next step would be to generate a vertical bar chart to display various data retrieved from data cells.

All the chart examples work fine, I don't encounter any specific problem, however whenever I try to create my own chart, by calling the rangeToArray() and fromArray() methods, nothing appears in the worksheet, see the subset of my code below to clarify this issue:

The first step consists of creating a new worksheet containing two cells with 40 data per cell, after that, I retrieve the data from those cells by calling rangeToArray() method, then the final step is to call the fromArray() method to use these data to create a graph but it does not work, the graph remains empty, I can only display the title and the yAxisLabel,

I suspect a problem with the fromArray() method but I can't figure out why, could you please help me by giving a similar working example by using this approach? Thanks in advance

$retrieve_data = array();

$retrieve_data = $worksheet->rangetoArray("D8:E48",null,null,null);

$worksheet = $objPHPExcel->getActiveSheet();   

$objWorksheet = $worksheet;   

$objWorksheet->fromArray($retrieve_data, NULL, 'D8', false);

$dataseriesLabels = array( new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$D$8', null, 1), new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$E$8', null, 1),);

$xAxisTickValues = array( new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$D$8:$D$27', null, 20),);

$dataSeriesValues = array( new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$D$8:$D$27', null, 20), new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$E$8:$E$27', null, 20),);

$series = new PHPExcel_Chart_DataSeries( PHPExcel_Chart_DataSeries::TYPE_BARCHART, plotType PHPExcel_Chart_DataSeries::GROUPING_STANDARD, range(0, count($dataSeriesValues)-1), $dataseriesLabels, $xAxisTickValues, $dataSeriesValues);

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

$title = new PHPExcel_Chart_Title('Test Column Chart');

$yAxisLabel = new PHPExcel_Chart_Title('Value');

$chart = new PHPExcel_Chart( 'chart1', $title, $legend, $plotarea, true, 0, null, $yAxisLabel);

$chart->setTopLeftPosition('G2');

$chart->setBottomRightPosition('S20');

$objWorksheet->addChart($chart);

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');   header('Content-Disposition: attachment;filename="BAL_Delhaize2012.xlsx"');   header('Cache-Control: max-age=0');
  $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); 

$objWriter->setIncludeCharts(TRUE); 

$objWriter->save('php://output');

Oct 5, 2012 at 4:31 AM

Hi there, 

I made some typo in my code, here's the little change. The cells data are retrieved with the rangeToArray() method like this:

$retrieve_data = array();

$retrieve_data = $worksheet->rangetoArray("D8:E48",null,null,null);

 

Then, the data series labels is as follows:

$dataseriesLabels = array( new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$D$8', null, 1), new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$E$8', null, 1),);

and the x-Axis tick values as follows:

$xAxisTickValues = array( new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$D$9:$D$29', null, 20),);

 

If I use the following array with the same code it works fine:

$objWorksheet->fromArray( array( array('', 2010, 2011, 2012), array('1',   12,   15, 21), array('2',   56,   73, 86), array('3',   52,   61, 69), ... ....)),

On the other hand, if I call the rangeToArray() to display the same data in a bar graph, it doesn't work, there is no error message, what's wrong?

By the way, does anybody know the best way to debug chart problems when we are faced with such a similar problem? Thanks in advance for your help

 

Yves Hardy