Creating chart with PhpExcel in existing file

Topics: User Forum
Jan 8, 2015 at 2:52 PM
Hello, and first of all, thanks for this. It's really an amazing work.

I am very new with php and phpexcel, and I'm having some trouble creating a chart.
I have a function in one file that only creates the excel file, and then another function that reads the created file and then writes data from an array and creates a chart in the same excel file. I run all of them without any error but when I look at the sheet the chart it's created but it's empy though the data is in the table correctly.
I have another function that does all of it in the same file (practically the same code in both of them) and works perfectly.
No idea what's the problem here.
Well, all the help is welcome.


I'll leave my code here below:
    $workbook = PHPExcel_IOFactory::createReader("Excel2007");
    $workbook = PHPExcel_IOFactory::load('Excel.xlsx');


        $sheet = $workbook->getActiveSheet();
        $sheet = $workbook->setActiveSheetIndex();

$sheet->fromArray($array, null, 'A4');

$workbook ->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
$workbook ->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
$workbook ->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);

$cell = count($array, COUNT_RECURSIVE);
$cell = (($cell/3)-3);
echo ("$cell \n");
$num = ceil($cell);
echo ("$num \n");



/*-----------------------------Full Graph-------------------------------*/
$labels = array(
  new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$B$4', null, 1),
  new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$C$4', null, 1),
);
$categories = array(
  new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$A$5:$A'.($num + 5), null, ($num - 1)),
  new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$A$5:$A'.($num + 5), null, ($num - 1)),
);
$values = array(
  new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$B$5:$B'.($num + 5), null, ($num - 1)),
  new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$C$5:$C'.($num + 5), null, ($num - 1)),
);
$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
);

$title1 = new PHPExcel_Chart_Title('Total y ' .($dep));

$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(
  'chart',                                       // name
  $title1,                                           // title
  $legend,                                        // legend
  $plotarea,                                      // plotArea
  true,                                           // plotVisibleOnly
  0,                                              // displayBlanksAs
  null,                                           // xAxisLabel
  null                                            // yAxisLabel
);



/*-----------------------------Full Graph-------------------------------*/

/*-----------------------------Small Graph-------------------------------*/
$label = array(
  new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$B$4', null, 1),
  
);
$category = array(
  new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$A$5:$A'.($num + 5), null, ($num - 1)),
);
$values2 = array(
  new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$B$5:$B'.($num + 5), null, ($num - 1)),

);
$series2 = new PHPExcel_Chart_DataSeries(
  PHPExcel_Chart_DataSeries::TYPE_BARCHART,       // plotType
  PHPExcel_Chart_DataSeries::GROUPING_CLUSTERED,  // plotGrouping
  array(0),                                    // plotOrder
  $label,                                        // plotLabel
  $category,                                    // plotCategory
  $values2                                         // plotValues
);

$title2 = new PHPExcel_Chart_Title('Total  ' .($dep));

$series2->setPlotDirection(PHPExcel_Chart_DataSeries::DIRECTION_COL);
$plotarea2 = new PHPExcel_Chart_PlotArea(null, array($series2));
$legend2 = new PHPExcel_Chart_Legend(PHPExcel_Chart_Legend::POSITION_RIGHT, null, false);
$chart2 = new PHPExcel_Chart(
  'chart2',                                       // name
   $title2,                                           // title
  $legend2,                                        // legend
  $plotarea2,                                      // plotArea
  true,                                           // plotVisibleOnly
  0,                                              // displayBlanksAs
  null,                                           // xAxisLabel
  null                                            // yAxisLabel
);




/*-----------------------------Small Graph-------------------------------*/

$chart->setTopLeftPosition('A'.($num+10));
$chart2->setTopLeftPosition('K'.($num+10));
$chart->setBottomRightPosition('I30');
$chart2->setBottomRightPosition('W30');
$sheet->addChart($chart);
$sheet->addChart($chart2);
$writer = PHPExcel_IOFactory::createWriter($workbook, 'Excel2007');
$writer->setIncludeCharts(TRUE);
$writer->save('Excel.xlsx');