phpspreadsheet

PHPSpreadsheet multiple barcharts in worksheeet


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:

enter image description here

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?


Solution

  • 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);