I am trying to render multiple barcharts on a single worksheet in PHPSpreadsheet. I am using the code from the documented samples as my starting point. No errors are (now) being thrown, but the charts are not rendering.
The initial dataset is as follows:
[
[0] =>
[
[title] => How did you find Session 1?
[total] => 49
[options] =>
[
[very helpful] => 31
[helpful] => 16
[not helpful] => 2
]
]
[1] =>
[
[title] => How did you find Session 2 (if applicable)?
[total] => 49
[options] =>
[
[very helpful] => 26
[helpful] => 18
[not helpful] => 5
]
]
[2] =>
[
[title] => How did you find Session 3?
[total] => 49
[options] =>
[
[very helpful] => 27
[helpful] => 19
[not helpful] => 3
]
]
]
I have an initial sheet, where I merely list some attending data, and then I move on to the next sheet for the barcharts.
if(!empty($barcharts))
{
$alphabet = Helper::alphabet(true, 2); // gives me an uppercase alphabet, iterated twice: ...X,Y,Z,AA,AB,AC, etc...
$chart_left_col_index = 0;
$chart_col_width = 8;
// blue, orange, red
$colors = [
'00abb8', 'eb8500', 'b8292f',
];
$sheet = $excel_obj->createSheet();
$sheet->setTitle('Charts');
$barcharts = array_values($barcharts);
foreach($barcharts as $i => $barchart)
{
$bars = [];
foreach($barchart['options'] as $key => $num)
$bars[] = [$key, $num];
$num_points = count($bars);
$start_row = 1; $end_row = $num_points;
$chart_keys_col = $alphabet[$chart_left_col_index];
$chart_values_col = $alphabet[$chart_left_col_index + 1];
$chart_right_col = $alphabet[$chart_left_col_index + $chart_col_width];
$row_count = $start_row;
foreach($bars as $row)
{
$sheet->setCellValue($chart_keys_col . $row_count, $row[0]);
$sheet->setCellValue($chart_values_col . $row_count, $row[1]);
$row_count++;
}
$series_labels = [
//new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$C$1', null, 1), // not applicable
];
$x_axis_tick_keys = [
new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING,
"Worksheet!\${$chart_keys_col}\${$start_row}:\${$chart_keys_col}\${$end_row}",
null,
$num_points),
];
$series_values = [
new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER,
"Worksheet!\${$chart_values_col}\${$start_row}:\${$chart_values_col}\${$end_row}",
null,
$num_points,
[],
null,
$colors),
];
// Build the dataseries
$series = new DataSeries(
DataSeries::TYPE_BARCHART, // plotType
null,
range(0, count($series_values) - 1), // plotOrder
$series_labels, // plotLabel
$x_axis_tick_keys, // plotCategory
$series_values // plotValues
);
// Set up a layout object for the chart
$layout = new Layout();
$layout->setShowVal(true);
$layout->setShowPercent(true);
// Set the series in the plot area
// Set the chart legend
$plot_area = new PlotArea($layout, [$series]);
$legend = new Legend(Legend::POSITION_RIGHT, null, false);
$title = new Title($barchart['title']);
$chart = new Chart(
"chart{$i}", // name
$title, // title
$legend, // legend
$plot_area, // plotArea
true, // plotVisibleOnly
DataSeries::EMPTY_AS_GAP, // displayBlanksAs
null, // xAxisLabel
null // yAxisLabel
);
// Set the position where the chart should appear in the worksheet
$chart->setTopLeftPosition($chart_keys_col . '10');
$chart->setBottomRightPosition($chart_right_col . '30');
// Add the chart to the worksheet
$sheet->addChart($chart);
$chart_left_col_index = $chart_left_col_index + $chart_col_width + 1;
}
}
The datasets are being written to the sheet correctly:
And the Worksheet coordinate strings, when echoed, are correct. I save the file like so:
$objWriter = new Xlsx($excel_obj);
$objWriter->save($exports_path . O_DE . $file_name);
$url = O_URL_APPS . 'dashboard/assets/exports/' . $file_name;
$return = ['url'=>$url];
What corrections must I make to have the charts render?
The PhpOffice\PhpSpreadsheet\Writer\Xlsx writer exposes a setIncludeCharts method to configure it to include charts in the generated spreadsheet.
$objWriter->setIncludeCharts(true);
$objWriter->save($exports_path . O_DE . $file_name);