PhpSpreadsheet: File containing a chart can not be opened by Excel 2003/2013/2019

This is:

- [x] a bug report
- [ ] a feature request
- [x] **not** a usage question (ask them on https://stackoverflow.com/questions/tagged/phpspreadsheet or https://gitter.im/PHPOffice/PhpSpreadsheet)

What is the expected behavior?

MS Office Excel should open an exported xlsx without any issues.

What is the current behavior?

Excel 2003, Excel 2013 and Excel 2019 (probably others, too) complain that the file is corrupted and offer to try to repair the sheet. If allowed to do so, all charts will be removed.

What are the steps to reproduce?

Run 33_Chart_create_stock.php (or probably any other chart example) and open the resulting file in Excel 2013.

Which versions of PhpSpreadsheet and PHP are affected?

PHPSpreadsheet 1.10.1 PHP 7.3.11

Additional comments

Exported files can be opened without any warning as long as they don’t contain a chart. Also, files can be opened with LibreOffice Calc without any issues.

About this issue

Original URL
State: closed
Created 4 years ago
Reactions: 7
Comments: 17 (1 by maintainers)

Commits related to this issue

File containing a chart can be opened by Excel 2003/2013/2019 All chart examples passed the displayBlanksAs parameter as 0 instead of 'gap'. I added a constants EMPTY_AS_GAP, EMPTY_AS_ZERO and EMPTY_... — committed to PHPOffice/PhpSpreadsheet by cwolcott 4 years ago

Most upvoted comments

@parthkharecha : Yes, it’s written in comment 578692963 (the second in this thread), too. This ticket is just open in order to track if the examples were adjusted (Edit: or if they should be, at all).

xrm on Apr 22, 2020

displayBlanksAs Repace 0 to gap

   $chart = new Chart(
                    'PROCESSED LEADS BREAKDOWN', // name
                    $title, // title
                    $legend, // legend
                    $plotArea, // plotArea
                    true, // plotVisibleOnly
                    'gap', // displayBlanksAs //Repace 0 to gap
                    null, // xAxisLabel
                    null   // yAxisLabel- Like Pie charts, Donut charts don't have a Y-Axis
                );

https://github.com/PHPOffice/PhpSpreadsheet/issues/942#issuecomment-605392953

parthkharecha on Apr 22, 2020

It seems to be the same effect, yes, although I suspect #1294 to have found an issue in the reader while this is probably just an issue with the examples. If you replace L95 with ‘gap’ (instead of 0), the exported excel file is valid.

This goes back to this commit https://github.com/PHPOffice/PhpSpreadsheet/commit/144a0cabbc572780d0f49db50243f15b104ce26e#diff-0fa257b790aee274c5c82d3156288e94

I assume that the reader does not correctly set this value in ticket #1294 and that this might cause problems when writing the file again.

Not sure if I should close this ticket and open a new one for the adjustment of the examples or if I should just change this one’s title … ?

xrm on Jan 27, 2020