CODEWITHSUNDEEP
phpexcel
yves
yves

Reputation: 11

PHPExcel_Chart_DataSeriesValues

I have developed 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');

Upvotes: 1

Views: 7682

Answers (2)

Yves
Yves

Reputation: 1

Mark,

Oops, I forgot to answer your question. I made a mistake in my example, here's the code about the data series values and the x-Axis tick values:

$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$9:$D$27', null, 20),);

The label is located in $D$8 and $E$8 cells, while the data as from $D$9 to $D$27

By the way what is the easiest way to debug a problem with this class when you get no error message?

Upvotes: 0

Mark Baker
Mark Baker

Reputation: 212452

I can see two obvious problems in the chart definition:

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

Should the first set of data series values really be the same as your x-Axis tick values?

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

the word "plotType" should throw a parser error here.

But if you believe that the problem is with the fromArray() method, test that without the chart to confirm that it's doing what you'd expect

Upvotes: 1

Related Questions