joi, 7 septembrie 2017

Multiple Series Line Chart with PHPExcel

Below is a sample piece of script which creates a multiple lines (multi - series) chart with the PHPExcel library:


  $dataSeriesLabels = array();
  $xAxisTickValues = array();
  $dataSeriesValues = array();
  $series = array();
  $dataPointCount =  8 /* the count of datapoints: $by-2 to $by+5 inclusive */;
  for ($i=0; $i<=4; $i++)
  {
   $r = 35+$i;
   $dataSeriesLabels[$i] = new \PHPExcel_Chart_DataSeriesValues('String', $sheetTitle.'!$B$'.$r, NULL, 1);
   $xAxisTickValues[$i] = new \PHPExcel_Chart_DataSeriesValues('Number', $sheetTitle.'!$C$34:$J$34', NULL, $dataPointCount);
   $dataSeriesValues[$i] = new \PHPExcel_Chart_DataSeriesValues('Number', $sheetTitle.'!$C$'.$r.':$J$'.$r, NULL, $dataPointCount);
  }
  $series[0] = new \PHPExcel_Chart_DataSeries(
   \PHPExcel_Chart_DataSeries::TYPE_LINECHART,  // plotType
   \PHPExcel_Chart_DataSeries::GROUPING_STANDARD,  // plotGrouping
   range(0, count($dataSeriesValues) - 1),   // plotOrder
   $dataSeriesLabels,        // plotLabel
   $xAxisTickValues,        // plotCategory
   $dataSeriesValues         // plotValues
  );
  $series[0]->setPlotDirection(\PHPExcel_Chart_DataSeries::DIRECTION_HORIZONTAL);

  //  Set the series in the plot area
  $plotarea = new \PHPExcel_Chart_PlotArea(NULL, $series);
  //  Set the chart legend
  $legend = new \PHPExcel_Chart_Legend(\PHPExcel_Chart_Legend::POSITION_RIGHT, NULL, false);
  $title = new \PHPExcel_Chart_Title('');

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

  $chart->setTopLeftPosition('L31');
  $chart->setBottomRightPosition('AC43');
  $objWorksheet->addChart($chart);
  header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
  header('Content-Disposition: attachment;filename="'.$fname.'"');
  header('Cache-Control: max-age=0');

  // Do your stuff here
  $writer = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
  //PHPExcel_Writer_Excel2007
  $writer->setIncludeCharts(TRUE);


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