Inserting a drawing / chart causes print options to break in version 1.3.1

[rf1234]

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?

In version 1.2.1 there was no issue with print options: I don't set any and the template I use is issue checked so that there shouldn't be any problems.

What is the current behavior?

In version 1.3.1 I get an issue with Print Options. Excel repairs the file and removes print options.

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 = new \PhpOffice\PhpSpreadsheet\Spreadsheet();

// add code that show the issue here...

Which versions of PhpSpreadsheet and PHP are affected?

[fyrye]

@rf1234 what template are you using?
How do we reproduce the issue you are experiencing?
What do you mean by issue checked so that there shouldn't be any problems.?

[rf1234]

I guess I found the problem now: If I insert a drawing into one of the sheets (it can be a jpeg or an Excel chart) the problem occurs:

This rather simple code which does almost nothing causes the print option problem:

$templateFile = "Darlehensuebersicht_template.xlsx";
    //we have xlsx and xls input files the input file types are hence Xlsx or Xls
    $readerType = ucfirst( substr($templateFile, strrpos($templateFile, '.') + 1) );
    if ( $readerType === 'Xlsm' ) {
        $readerType = 'Xlsx';
    }
    
    //open spreadsheet template
    $templateFilePath = __DIR__ . '/reporting/templates/' . $templateFile;
    
    $reader = IOFactory::createReader($readerType);
    $reader->setIncludeCharts(false); //we don't want to load charts
    $spreadsheet = $reader->load($templateFilePath);
    
    $outputFile = "Darlehensuebersicht.xlsx";
    
    $outputFileType = substr($outputFile, strrpos($outputFile, '.') + 1);
    $writerType = ucfirst( $outputFileType );
    if ( $writerType === 'Xlsm' ) {
        $writerType = 'Xlsx';
    }

    //change $outputFile by removing xls or xlsx
    $outputFile = substr($outputFile, 0, strrpos( $outputFile, '.') );
    $reportId = "1.3.1";
    
    $outputFileName = $outputFile . '_' . $reportId . '.' . $outputFileType;
    //set fields to update the file table and to write the file
    $webPath = '/lgfuploads/reporting/' . $outputFileName;
    $systemPath = $_SERVER['DOCUMENT_ROOT'] . $webPath;
    
    $spreadsheet->setActiveSheetIndex(0);
   
    $col = 'K';
    $ws = $spreadsheet->getActiveSheet(); 
    $logoPath = __DIR__ . '/reporting/templates/SmallLogo.jpg';
    $drawing = new Drawing();
    $drawing->setPath($logoPath);
    $drawing->setCoordinates($col.'2');
    $drawing->setHeight(45); //pixels
    $colWidth = $ws->getColumnDimension($col)->getWidth();
    if ($colWidth == -1) { //not defined which means we have the standard width
        $colWidthPixels = 64; //pixels, this is the standard width of an Excel cell in pixels = 9.140625 char units outer size
    } else {                  //innner width is 8.43 char units
        $colWidthPixels = $colWidth * 7.0017094; //colwidht in Char Units * Pixels per CharUnit
    }
    $offsetX = $colWidthPixels - $drawing->getWidth(); //pixels
    $drawing->setOffsetX($offsetX); //pixels
    $drawing->setWorksheet($ws);
    
//write Excel file based on template
    $writer = IOFactory::createWriter($spreadsheet, $writerType);    
    $writer->setIncludeCharts(true);
    $writer->setPreCalculateFormulas(true);
    $writer->save($systemPath);

"Issue checked" means I open "file/check for issues" and have Excel fix any issues in the template.

If I remove this code I have no problem:

$col = 'K';
    $ws = $spreadsheet->getActiveSheet(); 
    $logoPath = __DIR__ . '/reporting/templates/SmallLogo.jpg';
    $drawing = new Drawing();
    $drawing->setPath($logoPath);
    $drawing->setCoordinates($col.'2');
    $drawing->setHeight(45); //pixels
    $colWidth = $ws->getColumnDimension($col)->getWidth();
    if ($colWidth == -1) { //not defined which means we have the standard width
        $colWidthPixels = 64; //pixels, this is the standard width of an Excel cell in pixels = 9.140625 char units outer size
    } else {                  //innner width is 8.43 char units
        $colWidthPixels = $colWidth * 7.0017094; //colwidht in Char Units * Pixels per CharUnit
    }
    $offsetX = $colWidthPixels - $drawing->getWidth(); //pixels
    $drawing->setOffsetX($offsetX); //pixels
    $drawing->setWorksheet($ws);
    

All of this used to work perfectly with version 1.2.1! Hence I moved back to 1.2.1.

I attached four files: the template file, the resulting file without drawing which is ok, the resulting file with drawing which is broken and the picture file that I insert as a drawing. I am actually surprised that I seem to be the first user facing the issue. I thought many people insert charts or pictures programmatically. All of them should be facing the issue once migrating to 1.3.1. Please help!

Darlehensuebersicht_1.3.1_without_drawing.xlsx
Darlehensuebersicht_1.3.1_with_drawing.xlsx
Darlehensuebersicht_Template.xlsx

[rf1234]

Found a work around in the meantime: I use versions 1.3.1 and 1.2.1 in parallel depending on the report type. I am lucky that the xlsm files I write do not contain drawings (no logos, no charts). Hence I can use 1.3.1 for xlsm and have the benefit of not losing a button that is contained in the template I read.

For all other files (almost all of them have drawings) I use version 1.2.1 and hence avoid the print option issue mentioned above.

Hope this gets fixed soon; I think it is a pretty serious bug because version 1.3.1 is unusable for files containing drawings / pictures / charts if you don't want to bother the user with files that need repair.

[PowerKiKi]

@rf1234 are you able to create a Minimal, Complete, and Verifiable example of code that exhibits this issue without relying on an external Excel file ?

[rf1234]

Sorry, no I can't. I never use PHPSpreadsheet without a template. Wouldn't know any reason for using it without a template to be honest. You can find my template attached above and also the full code I used.

[rf1234]

@PowerKiKi
can you help me with this please. I know it seems to work without using a template. But that doesn't help because many people will need to use a template. And according to my experience it doesn't work with ANY template. I tried with a lot of them. Made sure they are all checked for integrity etc. But doesn't help. Just tried with version 1.4.0. Same issue with the print options.

[PowerKiKi]

To maximise the chance of getting help, you should submit the minimal code to reproduce the issue. What you submitted so far is clearly not the minimum. It should be a only a few line of code. You should be able to create a brand new spreadsheet, set printing options, add a drawing and save the file to demonstrate the bug.

[rf1234]

That's not working. The error does not occur if you do not use a template. I have submitted everything I have. If this still isn't good enough then I can't help it.

[stale]

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.

[JParkerCFI]

Stale or not the problem still persists.

[rf1234]

@JParkerCFI , you are right. All of the issues I have brought up so far have become "stale" after a while. Virtually nothing got resolved! I am deeply frustrated that I obviously just wasted my time.

[PowerKiKi]

This project heavily relies on community contributions, because we don't have the resources to do it ourselves. This unfortunately means that if a bug is not resolved in a timely fashion, then your best course of action is to debug it yourself and ideally share back the fix you found.

I understand this can be frustrating, especially for people who are less experienced. But there really is nothing else we can do. For the record we are only 2 "active" people on this project and only in our spare time. I'll do my best to merge incoming PRs, but I personally don't have enough time to spend a whole day debugging a single issue.

I suggest you guys, @rf1234 and @JParkerCFI work together to resolve this particular issue.

[ger82]

Hello, in which branch can I commit fixes?

[rf1234]

Pull requests!

"First time contributing to PHPOffice/PhpSpreadsheet?
If you know how to fix an issue, consider opening a pull request for it.

You can read this repository’s contributing guidelines or GitHub Help to learn how to open a good pull request."

[PowerKiKi]

Pull requests should be made against master branch.

[nicolacardi]

Hi.
I have a similar problem: I am using the most recent version of PHPspreadsheet (donwloaded yesterday). 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" message BUT printer options are removed anyway....I guess we are talking about the same story...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...

Everything I described happens only if drawings are dynamically included: if I take them out the file, or if drawings are simply present in the template and not added via code, everything is fine.
[To give more details: all the above happens ONLY in localhost, NOT online...I connect all the above with a matter of character coding because I have another asymmetric behaviour between localhost and web that I can't solve: all texts I get from database are mistyped when I do it locally, not online...this is something I have been struggling with for a few weeks now.]

Please help