Fatal error in charts using comma-separated cell references

[StevenRichards]

This is:

- [*] a bug report

What is the expected behavior?

Create an XLSX file with a chart that uses a comma-separated list of cell references for its series data and/or axis labels, e.g. =(Worksheet!$B$2,Worksheet!$B$5)

This format is generated in Excel 2007 when Ctrl+clicking to select non-contiguous cells to be used for chart data or labels, as seen here:

What is the current behavior?

Program crashes:
PHP Fatal error: Uncaught exception 'PhpOffice\PhpSpreadsheet\Calculation\Exception' with message 'Formula Error: Unexpected ,' in /vagrant/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php:4078

Speculation: PhpSpreadsheet appears to be interpreting the cell reference list as a function call (comma-separated list within parentheses) missing the function name.

See sample code below; problem line is:
new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, '(Worksheet!$B$2,Worksheet!$B$5)', null, 4),

Sample code is based on the 33_Chart_create_bar example. Error also occurs with naturally generated XLSX files using the built-in Excel chart builder.

What are the steps to reproduce?

Please provide a Minimal, Complete, and Verifiable example of code that exhibits the issue without relying on an external Excel file or a web server:

<?php

require __DIR__ . '/vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Chart\Chart;
use PhpOffice\PhpSpreadsheet\Chart\DataSeries;
use PhpOffice\PhpSpreadsheet\Chart\DataSeriesValues;
use PhpOffice\PhpSpreadsheet\Chart\Legend;
use PhpOffice\PhpSpreadsheet\Chart\PlotArea;
use PhpOffice\PhpSpreadsheet\Chart\Title;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\IOFactory;

$spreadsheet = new Spreadsheet();
$worksheet = $spreadsheet->getActiveSheet();
$worksheet->fromArray([
    ['', 2010, 2011, 2012],
    ['Q1', 12, 15, 21],
    ['Q2', 56, 73, 86],
    ['Q3', 52, 61, 69],
    ['Q4', 30, 32, 0],
]);

$dataSeriesLabels = [
    new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$B$1', null, 1), //	2010
    new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$C$1', null, 1), //	2011
    new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$D$1', null, 1), //	2012
];

$xAxisTickValues = [
    new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$A$2:$A$5', null, 4), //	Q1 to Q4
];

$dataSeriesValues = [
    new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, '(Worksheet!$B$2,Worksheet!$B$5)', null, 4),
    new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, 'Worksheet!$C$2:$C$5', null, 4),
    new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, 'Worksheet!$D$2:$D$5', null, 4),
];

//	Build the dataseries
$series = new DataSeries(
    DataSeries::TYPE_BARCHART, // plotType
    DataSeries::GROUPING_CLUSTERED, // plotGrouping
    range(0, count($dataSeriesValues) - 1), // plotOrder
    $dataSeriesLabels, // plotLabel
    $xAxisTickValues, // plotCategory
    $dataSeriesValues        // plotValues
);

$plotArea = new PlotArea(null, [$series]);
$legend = new Legend(Legend::POSITION_RIGHT, null, false);

$title = new Title('Test Bar Chart');
$yAxisLabel = new Title('Value ($k)');

//	Create the chart
$chart = new Chart(
    'chart1', // name
    $title, // title
    $legend, // legend
    $plotArea, // plotArea
    true, // plotVisibleOnly
    0, // displayBlanksAs
    null, // xAxisLabel
    $yAxisLabel  // yAxisLabel
);

$chart->setTopLeftPosition('A7');
$chart->setBottomRightPosition('H20');

$worksheet->addChart($chart);

$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->setIncludeCharts(TRUE);
$writer->save('/tmp/test.xlsx');

Which versions of PhpSpreadsheet and PHP are affected?

PhpSpreadsheet dev-master (27d83b4), PHP 5.6

[PowerKiKi]

I can confirm the bug with given code.

As a workaround $writer->setPreCalculateFormulas(false); can be added right before the save() and the file will be shown correctly in Excel (with chart and everything).

[StevenRichards]

@PowerKiKi Thanks for the workaround. Can confirm that works in our use case.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs.
If this is still an issue for you, please try to help by debugging it further and sharing your results.
Thank you for your contributions.

[sebauribe]

With the workaround i loose the labels in the others charts in the same worksheet.

[u01jmg3]

@oleibman: sorry for dragging up this old issue (happy to raise a new one) but I wanted to ask whether there was any merit in addressing this yet to be resolved problem which I stumbled across today. I realise there is a workaround but I wondered whether you knew what was preventing PHPSpreadsheet from supporting comma-separated cell references as described above?

[MarkBaker]

The issue is in the Calculation Engine itself, and in how it handles values inside braces when not in the context of a function call, or an arithmetic expression.

I've looked at how it can be addressed a couple of times now, but haven't yet come up with a satisfactory answer that doesn't break other working expressions