Trying to draw simple bubble chart with PhpSpreadsheet but got only the data without the chart. Here is my code:
use PhpOfficePhpSpreadsheetSpreadsheet;
use PhpOfficePhpSpreadsheetWriterXlsx;
use PhpOfficePhpSpreadsheetChartChart as ExcelChart;
use PhpOfficePhpSpreadsheetChartDataSeries;
use PhpOfficePhpSpreadsheetChartDataSeriesValues;
use PhpOfficePhpSpreadsheetChartLayout;
use PhpOfficePhpSpreadsheetChartPlotArea;
$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?