All Charts Are Removed From Sheet - Removed Part: Drawing shape.

[roland-jungwirth]

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?

When I use an existing Excel file, load it, and then save it, I expect all the graphs to be in the document.

What is the current behavior?

When I use an existing Excel file, load it using IOFactory::load( $filename ), the write it to php://output using the Xlsx writer, then open the file, Excel returns the following error (from Xcode):

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><logFileName>Repair Result to test.xml</logFileName><summary>Errors were detected in file ’~/Downloads/test.xlsx’</summary><removedParts summary="Following is a list of removed parts:"><removedPart>Removed Part: Drawing shape.</removedPart></removedParts></recoveryLog>

and the repaired file does not have any graphs.

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

// Create new Spreadsheet object
$spreadsheet = IOFactory::load( 'test.xlsx' );

// I've also tried:
// 1. application/ms-excel
// 2. application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
header( 'Content-Type: application/vnd.ms-excel' );
header( 'Content-Disposition: attachment; filename="test-output.xlsx"' );
header( 'Cache-Control: max-age=0' );

$writer = new Xlsx($spreadsheet);

// added this, as this claimed to fix this.
ob_end_clean();
// I've found this open as well.
$writer->setIncludeCharts(TRUE);
$writer->save( 'php://output' );
exit;

The attached Excel sheet is just a sample with a - simple - graph. When I pass that to the above workflow, the graph is stripped. I tried opening this up in both Excel, LibreOffice and Google Sheets. The graph is missing in all of these.

What features do you think are causing the issue

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

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

test.xlsx

Which versions of PhpSpreadsheet and PHP are affected?

PHP version: 8.1
PhpSpreadsheet: 1.29

[oleibman]

Thank you for the sample file. This turns out to hit at least 3 different problems, so may take a while to fix.

First, although you are specifying includeCharts for write, you are not doing so for read. That's an easy problem for you to fix. So, the expected outcome is that your output file will not contain charts (because they were ignored on read).

Second, it is, of course, not an expected outcome that Excel will have to repair the output file given your code. That's problem 2, and it's our problem, and I will need to research what needs to be done to avoid that.

Finally, if you do specify includeCharts for read, PhpSpreadsheet will throw a TypeError during the save. Something appears to be wrong with the way it reads the Chart Title, which should a RichText item containing a string but which instead unexpectedly contains an array. That's problem 3, and it's again our problem, and I will need to research ...

[roland-jungwirth]

@oleibman , thank you for your answer. Adding the includeCharts to the reader definitely helped. I have replaced this line

$spreadsheet = IOFactory::load( 'test.xlsx' );

with this

$reader = IOFactory::createReader('Xlsx');
$reader->setIncludeCharts(TRUE);
$spreadsheet = $reader->load( 'test.xlsx' );

and the graphs are there.

I do get the following error when opening the excel file

Excel completed file level validation and repair. Some parts of this workbook may have been repaired or discarded.

but I cannot see which part this pertains to, the file opens and the graphs seem to be correct.

I am not sure whether you want to close this, so I left it open for now.

[oleibman]

Please leave it open, I am working on a solution to the other problems.

Interesting that you avoided the TypeError for save; see next paragraph. At any rate, I am guessing that the part of the graph which Excel is having problems with is the title, which is set in the original sheet to what must be a default value 'Graph Title'. You'll notice that it's missing after Excel repairs it, but you probably wouldn't notice unless you were looking for it. I don't see anything in the Xml which sets that value in the original.

My results don't entirely match yours. I have been running my tests against master, which is where the TypeError shows up. However, it does not show up when I run against 1.29. Unlike you, I also don't get a corrupt file when I run against 1.29. At any rate, it seems the TypeError was introduced after 1.29 and is in the pending changes for whenever we issue a new release. So fixing it becomes a higher priority. I'm guessing it happened with the elimination of Php7.4 and the subsequent use of typing using Php declarations rather than doc-blocks. I don't know if I'll be able to put my finger on the smoking gun.

[roland-jungwirth]

Ok, no problem. Just ping me should you want me to test something.

[oleibman]

@roland-jungwirth Since your test results didn't completely match mine, please test against 3771 if you can, including both cases (specify or omit includeCharts on read). If everything looks okay on your end, l will merge that change.

[roland-jungwirth]

@oleibman, thank you - can I do this directly using composer, or do I need to pull it using git?

[oleibman]

@roland-jungwirth
There may be an easier way, but the following should work:

git clone -b issue3767 --single-branch https://github.com/oleibman/PhpSpreadsheet.git directory_name

Then go to directory_name and use composer to install any dependencies. Point your "require autoload" statement to directory_name, and you can run your tests.

[flyke]

Hi,
I have the same issue.
I tested manually applying these changes into vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Writer/Xlsx.php and vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Writer/Xlsx/Chart.php.
Still no charts are generated.

See attached .xlsx test file that has 3 charts that are not being generated.
demo-excel.xlsx

use PhpOffice\PhpSpreadsheet\IOFactory as SpreadsheetIOFactory;
use PhpOffice\PhpSpreadsheet\Writer\Pdf\Mpdf as ExcelMPDF;

      // Load the document
      $reader = SpreadsheetIOFactory::createReader('Xlsx');
      $reader->setIncludeCharts(true);
      $document = $reader->load('demo-excel.xlsx);
      // Save as html for testing
      $writer = new \PhpOffice\PhpSpreadsheet\Writer\Html($document);
      $writer->setIncludeCharts(true);
      $writer->writeAllSheets();
      $writer->save( 'test.html');
      // Save as pdf for testing
      $writer = new ExcelMPDF($document);
      $writer->setIncludeCharts(true);
      $writer->save('demo-excel.pdf');

[oleibman]

@flyke Your problem is different. You need to specify a renderer when generating charts for Html or Pdf. If you have used Composer to install the suggested package mitoteam/jpgraph:

\PhpOffice\PhpSpreadsheet\Settings::setChartRenderer(
    \PhpOffice\PhpSpreadsheet\Chart\Renderer\MtJpGraphRenderer::class
);

You will now get charts in your Html/Pdf. Even so, I don't think that the rendering for this sheet is good enough. Please open a new issue and I will start looking into it once I see it.

[roland-jungwirth]

@oleibman , sorry for the delay. This branch fixes my issue with the resulting Excel having an error.

For reference, this is the code that I tested it with:

$template_file = 'test.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;

and this is the test.xlsx file:
test.xlsx