Not able to draw bubble chart with PhpSpreadsheet

18 views Asked by At

Trying to draw simple bubble chart with PhpSpreadsheet but got only the data without the chart. Here is my code:

    use PhpOffice\PhpSpreadsheet\Spreadsheet;
    use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
    use PhpOffice\PhpSpreadsheet\Chart\Chart as ExcelChart;
    use PhpOffice\PhpSpreadsheet\Chart\DataSeries;
    use PhpOffice\PhpSpreadsheet\Chart\DataSeriesValues;
    use PhpOffice\PhpSpreadsheet\Chart\Layout;
    use PhpOffice\PhpSpreadsheet\Chart\PlotArea;
    
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    $sheet->setCellValue('A1', 'Category');
    $sheet->setCellValue('B1', 'Value 1');
    $sheet->setCellValue('C1', 'Value 2');
    $sheet->setCellValue('D1', 'Bubble Size');
        
    // Sample data
    $data = [
      ['Category 1', 10, 20, 30],
      ['Category 2', 15, 25, 40],
      ['Category 3', 20, 30, 50],
    ];
    foreach ($data as $rowIndex => $rowData) {
      $rowNumber = $rowIndex + 2;
      $sheet->setCellValue('A' . $rowNumber, $rowData[0]);
      $sheet->setCellValue('B' . $rowNumber, $rowData[1]);
      $sheet->setCellValue('C' . $rowNumber, $rowData[2]);
      $sheet->setCellValue('D' . $rowNumber, $rowData[3]);
    }
    $chart = new ExcelChart(
      'BubbleChart', // name
      null, // title
      null, // legend
      null, // xAxisLabel
      null, // yAxisLabel
      null // layout
    );
    $dataSeriesLabels = [
      new DataSeriesValues('String', 'Worksheet!$A$1', null, 1), // Category labels
    ];
    $xValues = [
      new DataSeriesValues('Number', 'Worksheet!$B$2:$B$4', null, 3), // X-axis values
    ];
    $yValues = [
      new DataSeriesValues('Number', 'Worksheet!$C$2:$C$4', null, 3), // Y-axis values
    ];
    $sizeValues = [
      new DataSeriesValues('Number', 'Worksheet!$D$2:$D$4', null, 3), // Bubble size values
    ];
     
    $dataSeries = new DataSeries(
      DataSeries::TYPE_BUBBLECHART, // plotType
      DataSeries::GROUPING_STANDARD, // plotGrouping
      range(0, count($xValues) - 1), // plotOrder
      $dataSeriesLabels, // dataSeriesLabels
      $xValues, // xValues
      $yValues, // yValues
      $sizeValues // sizeValues
    );
    
    $layout = new Layout(['layout' => 'blip', 'manualLayout' => ['w' => 300, 'h' => 200, 'x' => 0, 'y' => 0]]);
    $plotArea = new PlotArea($layout, [$dataSeries]);
    $chart->setPlotArea($plotArea);
        
    $sheet->addChart($chart);
    
    $writer = new Xlsx($spreadsheet);
    $writer->setIncludeCharts(true);
    $writer->save('php://output');
        
    exit();


Once again: The result in Excel file is: data appears but the chart is missing. What is wrong?
0

There are 0 answers