Phpspreadsheet: Corrupted Xlsx file when generating a chart inside a Sheet

[chartz]

This is:

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

What is the expected behavior?

I want to generated a Chart in an Excel Sheet.

What is the current behavior?

When adding a chart into a sheet, the Xlsx file is generated with success but it show's an error message while opening it in Excel.
"Désolé... Nous avons trouvé un problème dans le contenu de [Nom Fichier], mais nous pouvons récupérer le maximum de contenu. Si la source de ce classeur est fiable, cliquez sur Oui."
After clicking Yes, the Sheet is recovered but the chart is not present into the Sheet.

The error occurs depending on the server operating system.

• The file has no error while generated on Windows through a WAMP instance (PHP 5.6).
• The file has an error while generated on Unix Debian 9.8 with Apache 2.4 (PHP 5.6).

The same PHP code has been deployed on the 2 server.

What are the steps to reproduce?

Here is the PHP code I use

When adding a chart into a sheet, the Xlsx file is generated with success but it show's an error message while opening it in Excel.
"Désolé... Nous avons trouvé un problème dans le contenu de [Nom Fichier], mais nous pouvons récupérer le maximum de contenu. Si la source de ce classeur est fiable, cliquez sur Oui."

The error occurs depending on the server operating system.

• The file has no error while generated on Windows through a WAMP instance (PHP 5.6).
• The file has an error while generated on Unix Debian 9.8 with Apache 2.4 (PHP 5.6).

The same PHP code has been deployed on the 2 server.

Comparison of the two files

Into de Xlsx files, just one file is really different. It's the file "xl\chart\chart1.xml".
At the end of the file, there is a difference in the "c:printSettings" at the end.
In the working file, the attributes value for the "c:pageMargins" are number with a point as decimal separator "." separtor (footer="0.3"...).
In the working file, the attributes value for the "c:pageMargins" are number with a comma as decimal separator "." separtor (footer="0.3"...).

It seems the file generator is Os dependant.

Workaround found

I check the \PhpSpreadsheet\Writer\Xlsx\Chart.php file.
The function printSettings has margin attributes hardcoded to float values.

    private function writePrintSettings($objWriter)
    {
        $objWriter->startElement('c:printSettings');

        $objWriter->startElement('c:headerFooter');
        $objWriter->endElement();

        $objWriter->startElement('c:pageMargins');
        $objWriter->writeAttribute('footer', 0.3);
        $objWriter->writeAttribute('header', 0.3);
        $objWriter->writeAttribute('r', 0.7);
        $objWriter->writeAttribute('l', 0.7);
        $objWriter->writeAttribute('t', 0.75);
        $objWriter->writeAttribute('b', 0.75);
        $objWriter->endElement();

        $objWriter->startElement('c:pageSetup');
        $objWriter->writeAttribute('orientation', 'portrait');
        $objWriter->endElement();

        $objWriter->endElement();
    }

I solved my issues when replacing the float values with string values for the six attributes.
$objWriter->writeAttribute('footer', '0.3');

I'm not sure it's the right solution as I've found the usage of StringHelper::formatNumber() into the \PhpSpreadsheet\Writer\Xlsx\Worksheet.php

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:

        $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        $sheet->setCellValue('A1', 1);
        $sheet->setCellValue('A2', 2);
        $sheet->setCellValue('A3', 3);

        $area = 'Worksheet!$A$1:$A$3';
        $dataSeriesValues = $dataseriesLabels = $xAxisTickValues = [
            new \PhpOffice\PhpSpreadsheet\Chart\DataSeriesValues('Number', $area),
        ];

        $series = new \PhpOffice\PhpSpreadsheet\Chart\DataSeries(
            \PhpOffice\PhpSpreadsheet\Chart\DataSeries::TYPE_BARCHART,
            \PhpOffice\PhpSpreadsheet\Chart\DataSeries::GROUPING_STANDARD,
            range(0, count($dataSeriesValues) - 1),
            $dataseriesLabels,
            $xAxisTickValues,
            $dataSeriesValues
        );

        $plotarea = new \PhpOffice\PhpSpreadsheet\Chart\PlotArea(null, [$series]);
        $chart = new \PhpOffice\PhpSpreadsheet\Chart\Chart(
          'chart1',   // name
          null,       // title
          null,       // legend
          $plotarea,  // plotArea
          true,       // plotVisibleOnly
          0,          // displayBlanksAs
          NULL,       // xAxisLabel
          NULL        // yAxisLabel
        );

        $chart->setTopLeftPosition('A5');
        $chart->setBottomRightPosition('H20');
        $sheet->addChart($chart);

        // save XLSX
        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
        $writer->setIncludeCharts(true);
        $writer->save('template.xlsx'); // saved with the chart

        // re-use template
        $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');
        $reader->setIncludeCharts(true);
        $spreadsheetTpl = $reader->load('template.xlsx');
        $worksheetTpl = $spreadsheetTpl->getActiveSheet();
        $worksheetTpl->getCell('A1')->setValue(123);
        $writerTpl = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheetTpl, 'Xlsx');
        $writerTpl->setIncludeCharts(true); // useless since no charts were loaded
        $writerTpl->save('template2.xlsx'); // cell A1 has value 123, but the chart is gone

Which versions of PhpSpreadsheet and PHP are affected?

Spreadhseet v1.5.1 / Spreadsheet v1.6.0 (I tried with the two versions).
PHP 5.6

[stale[bot]]

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.

[ghost]

Can you open this issue?
I used the examples and also get a Corrupted file.

https://github.com/PHPOffice/PhpSpreadsheet/blob/master/samples/Chart/33_Chart_create_line.php

Which versions of PhpSpreadsheet and PHP are affected?
Spreadhseet v1.10.1 and v1.11.0
PHP 7.3

[Mongkol-j]

Problem is displayBlanksAs property. I have 2 solution.

1. Set it to 'gap' on Create the chart. change 0 to 'gap'
   // Create the chart
   $chart = new Chart(
   'chart1', // name
   $title, // title
   $legend, // legend
   $plotArea, // plotArea
   true, // plotVisibleOnly
   'gap', // displayBlanksAs
   $xAxisLabel, // xAxisLabel
   $yAxisLabel // yAxisLabel
   );
   or
2. Cancel write this property to xlsx file in function
   writeChart in "vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Writer/Xlsx/Chart.php by comment it out (line 90~93)

    //$objWriter->startElement('c:dispBlanksAs');
    //$objWriter->writeAttribute('val', $pChart->getDisplayBlanksAs());
    //$objWriter->endElement();

[ghost]

I checked Solution 1 with the examples from this repository and it works.
Shoud the examples be changed?

[parthkharecha]

@Mongkol-j Thank you your example is worked fine
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
                );

[Mongkol-j]

I google and found possible values of displayBlanksAs are
gap,span,zero