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

[xrm]

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.

[YanSt]

Look at this: https://github.com/PHPOffice/PhpSpreadsheet/issues/1294 it seems to be the same

[xrm]

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 … ?

[pyatnitsev]

@xrm, thanks for your comment. You're save a lot of my time

in addition i would to say one important thing.

I faced this issue after migrating from PHPExcel 1.8 and this issue is coused by change in

\PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex function.

with PhpSpreadsheet previous value need to be increased by 1.

[ChrisSantiago82]

You have saved me a lot of time. But why is this issue closed? The examples are not changed or the bug isn't resolved either. If you have an example code it should run without errors. And many other people are having the same issues...

[pyatnitsev]

So... I can create a PR for Examples for charts if it's needed.

Solution is here:

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.

Just replace small part of code

[ChrisSantiago82]

Well, the same error occurs in other situations. I wanted to import an existing excel sheet that already has an Chart and just change data on another sheet. But like this it's impossible. Or this bug exists in other classes as well that build on phpspreadsheet. For example in LaravelExcel, that was downloaded more than a million time, now it's not possible to create charts.

But at least with your workaround I can get it working. But if that is the correct way to do this, we should at least adapt the example files...

[cwolcott]

Wow, thank you so much for the ticket. I wasted 8 hours yesterday trying to figure out if this was an Excel for MacOS issue, Office O365 issue or just my stupid issue. Went to bed at midnight and this morning found this ticket and within 5 seconds solved the problem. Thank you.

[ChrisSantiago82]

Yes, and still nobody is going to work on this problem. PHPSpreadsheet is pretty much dead. Just a question of time until this issue is closed by the robot...

[cwolcott]

Why do you say it is dead. Version 1.11.0 was released March 2, 2020. I think it is a great library. I have only submitted a Pull Request once, about 3 years ago for dc.js. Maybe if I find sometime I will try to remember how to do it again. I would love to contribute.

[ChrisSantiago82]

Because this is not a new issue. It exists for a while. I have lost also many hours with this errors. But instead solving on the issues that exists, all the issues are getting closed: https://github.com/PHPOffice/PhpSpreadsheet/issues/942

[parthkharecha]

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

[xrm]

@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).

[tonycesar]

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

#942 (comment)

This solve for me, with sample 33_Chart_create_pie_custom_colors.php only change line 93 and 165 from 0 to 'gap' in displayBlanksAs; with xlsx like the base example.

[xrm]

Wow, thank you so much for the ticket. I wasted 8 hours yesterday trying to figure out if this was an Excel for MacOS issue, Office O365 issue or just my stupid issue. Went to bed at midnight and this morning found this ticket and within 5 seconds solved the problem. Thank you.

@cwolcott I just saw that you also commited a PR back then - thank you :-)

For those that are wondering what the current state of this issue is, please look at PR https://github.com/PHPOffice/PhpSpreadsheet/pull/1448/

I will close this ticket now, since the PR is underway.

[PowerKiKi]

Thank you all for your patience. The PR was merged.

And the project is not dead. It is going at a slow pace, but it still is alive. The best thing you can do to help the project is to have constructive discussion around issues like you did here, and then create a well crafted PR like @cwolcott did.

[petehenshall]

Thank you for this - changing 0 for 'gap' fixed my problem.

[nitin-usualsmart]

I have tried putting the 'gap' but it does not work for me. Can anyone help me to sort this out? I am using line and area charts, if I comment the area chart code it works fine but throwing error of Repaired Records: Drawing from /xl/drawings/drawing1.xml part (Drawing shape) phpspreadsheet when I use the area chart code. I am adding multiple series in line and area charts. Thanks in advance.

$charts = [];

        foreach ($data as $index => $seriesData) {
            $name = $seriesData['name'];
            $chartValues = $seriesData['data'];
            $xAxisTickValues = [
                new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, null, null, count($categories), $categories),
            ];

            $dataSeriesLabels = [
                new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, null, null, count($chartValues), [$name]),
            ];
            $dataSeriesValues = [
                new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, null, null, count($chartValues), $chartValues),
            ];
            // $layout2 = new Layout();
            // $layout2->setShowVal(true);
            // $layout2->setShowPercent(true);

            $series = new DataSeries(
                DataSeries::TYPE_AREACHART,
                null,
                range(0, count($dataSeriesValues) - 1),
                $dataSeriesLabels,
                $xAxisTickValues,
                $dataSeriesValues
            );
            $charts[] = $series;
        }
 $areaChart = new ChartChart(
            'Areachart',
            $title_area,
            $legend,
            $plotArea,
            true,
            'gap',
            null,
            $yAxisLabel_area
        );