Chart title loses reference to cell

[roland-jungwirth]

This is:

- [x] a bug report

What is the expected behavior?

If I have a graph title that relates to a cell, this should stay as is. I.e. the graph title's value is "=A1", deriving the value from Cell A1.

What is the current behavior?

When the graph has gone through the reader and writer, the output does have the correct data, i.e. the value of A1, but it is not a reference to A1 anymore.

What are the steps to reproduce?

<?php

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

$template_file = 'test-rotated_xlabel.xlsx';
$filename = 'test_' . date( 'Y-m-d_H-i-s' ) . '.xlsx';

// Load the template file
$reader = IOFactory::createReader('Xlsx');
$reader->setIncludeCharts(TRUE);

$spreadsheet = $reader->load($template_file);

// Set the headers to force a download of the Excel file
header( 'Content-Type: application/vnd.ms-excel' );
header( 'Content-Disposition: attachment; filename="' . $filename . '"' );
header( 'Cache-Control: max-age=0' );

// Create a writer and send the Excel file to the browser
$writer = new Xlsx($spreadsheet);

ob_end_clean();
$writer->setIncludeCharts(TRUE);
$writer->save( 'php://output' );
exit;

Here is the sample Excel file that I use:
test-rotated_xlabel.xlsx

What features do you think are causing the issue

• Reader
• Writer
• Styles
• Data Validations
• Formula Calculations
• Charts
• AutoFilter
• Form Elements

Does an issue affect all spreadsheet file formats? If not, which formats are affected?

Which versions of PhpSpreadsheet and PHP are affected?

PHP 8.1

[oleibman]

Thank you for the sample file. PhpSpreadsheet does not support using a formula for a title. I will investigate whether it can be supported.

[roland-jungwirth]

Excel doesn't either - that's why I have the formula in another cell and reference that. Excel does support that.

[oleibman]

Excel stores the cell references as if they were formulas. I agree that, at least at present, Excel restricts the formulas to references to a single cell (and sheet name must be part of the reference). I'm not sure that's a safe long-term assumption (but it probably is).

Your example sheet pointed out something else missing from PhpSpreadsheet. Your title font specifies all-caps, which is not an attribute that you can apply to a font for a cell. I'll have to see if it can be supported.

[roland-jungwirth]

So is there - currently - a way of setting a chart's title using PhpSpreadsheet?

[oleibman]

Yes, the chart has a Title object, which has a caption property. Most of the 33* members in samples/Chart do this. What you can't do yet is make the title dynamic, which is the issue you raised.