Phpspreadsheet: Unreadable content when including drawings

[nicolacardi]

This is:

• [x] a bug report (I beleive)

What is the expected behavior?

Include drawings without causing any problems

What is the current behavior?

If I add drawings dynamically (via code) this causes an "unreadable content" message when opening the file. The odd thing is that this is only happening on my local webserver, not online (!).

What are the steps to reproduce?

I simply added

$drawing = new PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
$drawing->setPath(__DIR__ . '/assets/img/logodefViola.png'); 
$drawing->setHeight(40);
$drawing->setCoordinates('B1');  
$drawing->setWorksheet($spreadsheet->getActiveSheet()); 

Which versions of PhpSpreadsheet and PHP are affected?

I downloaded it one week ago then I guess it's the most up to date

I commented another issue (https://github.com/PHPOffice/PhpSpreadsheet/issues/610) but then I realized it is closed for some reason, even if it is not solved:

This is the story:
When I "clone" a worksheet that includes a drawing (I have a logo on each worksheet
) the response is an "out of memory".
Then as a workaround I decided to make a template without logo and use the

$drawing = new PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
$drawing->setPath(__DIR__ . '/assets/img/logodefViola.png'); 
$drawing->setHeight(40);
$drawing->setCoordinates('B1');  
$drawing->setWorksheet($spreadsheet->getActiveSheet()); 

to include the logo dinamically everytime I clone a worksheet. This seems to work BUT, probably linked to this, I have another issue which might be a character coding issue but can't find how to solve it: when I generate my excel file on localhost it says there is unreadable text in the file (I have tried the exit() instruction at the end of the file, I have tried to remove the '?>' chars, as I found in another thread...still this is the error I see) and then another mask appears where it says PRINTER OPTIONS are REMOVED, which is what I don't want: I need print options (on A3 paper and front/rear ) to be saved and replicated on all worksheets I generate, not removed.... I tried to publish online ad happily I have no more "unreadable character", but I need it to work locally too, to make my tests....what do you suggest me to try? Find attached screenshot: they're in italian I make some translation for you : )
(translation: ...".xlsx includes unreadable content. Recover worksheet content?)
(translation: the file could be recovered : it is possible that some parts got deleted:
Print options
Print options
Print options
...etc: most likely one per each worksheet I cloned...
Please help

If I remove the "drawings" part everything works fine.

[To give more details that might help: I have another issue in my application that might be connected to the above problem: character coding is not the same locally and on the web and even though I have tried to solve this in 1000 ways (editing httpd.conf, editing php.ini, adding meta tags on my html pages) still I see my texts locally not displayed in the right way...all accented letters are mistyped....which is not happening online.]

Please help

[konovalovk]

I have same issue and my research results are:

• create and save empty xlsx to use it as a template, add image to active sheet, save => you`ll get corrupted excel file warning
• same with xls template => no such issue
• output format in each case is xlsx

So I converted every template to xls.

[nicolacardi]

Hi konovalovk, thanks for your answer but it's not 100% clear to me...
Should I save as xls instead of xlsx?
At the moment I:

1. open a template x.xlsx
2. change content
3. add drawings
4. save file as y.xlsx
   As far as I understand your suggestion is to:
   a. open xlsx template x
   b. change content
   c. save file as y.xlsx
   d. re-open file y.xlsx
   e. add drawings
   f. save file as y.xlsx or z.xlsx?
   Did I get it right?
   Thanks again

[konovalovk]

Try to open your template x.xlsx in MS Excel, save as x.xls (excel 97-2003) and use it in PhpOffice\PhpSpreadsheet\IOFactory::load(x.xls). This could help and will allow you don`t wait bug fix.

[nicolacardi]

it works! The only drawback is that now "all" images must be included via code. I had other images in the template and following your hint they became filled rectangles. Then I had to take them out and include each of them through the $drawing = new PhpOffice\PhpSpreadsheet\Worksheet\Drawing();...procedure. Thank you very much! I hope they'll work a bit on this issue so to let me avoid this workaround, but for the moment it's ok. Thanks a lot!

[adjenks]

I too am getting this error when opening an xlsx file and then saving a new copy of it. The problem still seems to occur after deleting the image from the original file. So it's just loading and writing xlsx files.

[adjenks]

Looks possibly related to #853 ? Can anyone confirm?

[rhino-corey]

I'm also experiencing issues when copying an XLSX sheet with images. The images are not carried over into the new sheet. Confirmed that switching to XLS copies sheet correctly.

[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.

[githubjeka]

Confirmed.
Any images of template dont transfer to new XLSX.
Any new images dont insert if template XLSX have images.
This depends by this code https://github.com/PHPOffice/PhpSpreadsheet/blob/ebc0b5695912e67545e0442ee3df0c044eac066b/src/PhpSpreadsheet/Writer/Xlsx.php#L331-L340

The path xl\drawings\drawing1.xml created with your new images before this code. But this code replace path xl\drawings\drawing1.xml to original from template.
Also path xl\drawings\_rels has incorect data

[jasperverbeet]

I've read a possible quick-fix by changing the template to an XLS (instead of XLSX), which seem to work except for the fact that any transparency layers in images are removed and cell fills don't seem to support the alpha channel.

Do you have any other way around this? Or can the root cause be addressed?

[jasperverbeet]

Update: It's not the cell fill alpha channel that is broken, it's the theme colors that get converted to Excel 2003 theme colors. This can easily be overcome by setting the cell color to a custom color. Basically, before converting your template to XLS make sure to replace all cell fills that implements a theme color with a color picked color. Images can be fixed by using JPEG's instead of PNG's (somehow the alpha channel gets replaced with a black channel).

[sools]

I've read a possible quick-fix by changing the template to an XLS (instead of XLSX), which seem to work except for the fact that any transparency layers in images are removed and cell fills don't seem to support the alpha channel.

Do you have any other way around this? Or can the root cause be addressed?

@jasperverbeet I noticed this problem too if using PNG images from legacy .xls files. To workaround this, I added the following lines into Reader/Xls.php, right in front of the setRenderingFunction() and setMimeType() calls:

                                    case BSE::BLIPTYPE_PNG:
                                        imagealphablending($ih, false);
                                        imagesavealpha($ih, true);