Push buttons are not preserved when loading and saving a file.

[ChrisPHL]

This is: I'm working on an old club project which used to utilize PhpExcel... Now I got hands on because there was something to do and I recognized some errors which I think were causes by an update of the PHP version from the pages provider side. I got things running with PhpSpreadsheet thanks to a good documentation and most of the things are running fine again but...

The project needs to alter some data of an old Excel spreadsheet file which contains some buttons which are running VBA scripts. Unfortunately the buttons are gone after altering some cells an saving the file back to disc. I'm not sure if this is related to #2904.

To demonstrate the behaviour I coded a MCVE which you can checkout at: https://www.moellner-rc.com/ls_bugdemo/

I'm happy to help with further information about the issue.

- [*] 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 behaviour?

Push Button is still there after altering the document as it was when using the outdated PHPExcel (v1.8.1)

What is the current behaviour?

The push button has disappeared (.xls and .ods) and the Basic-Script code has disappeared as well (*.ods only).

What are the steps to reproduce?

https://www.moellner-rc.com/ls_bugdemo/

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';
$inputFileName = dirname(__FILE__)."/PHPSpreadsheet_bugdemo_original.ods";
$spreadsheetReader = new PhpOffice\PhpSpreadsheet\Reader\Ods();
$objSpreadsheet = ($inputFileName);
$objSpreadsheet->setActiveSheetIndex(0)->setCellValue('A1', 'Where has my button gone?');
$objSpreadsheet->setActiveSheetIndex(0)->setCellValue('A2', 'Please open Tools --> Macros --> Edit Macros to prove the code is still there. (*.xls files only, *.ods will loose their scripts and cell formating inspite of using "$reader->setReadDataOnly(true);".');

// Redirect output to a client’s web browser (Excel5)
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="PHPSpreadsheet_bugdemo_edited.ods"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');

// If you're serving to IE over SSL, then the following may be needed
header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header ('Pragma: public'); // HTTP/1.0

$objWriter = PhpOffice\PhpSpreadsheet\IOFactory::createWriter($objSpreadsheet, 'Ods');
$objWriter->save('php://output');
?>

If this is an issue with reading a specific spreadsheet file, then it may be appropriate to provide a sample file that demonstrates the problem; but please keep it as small as possible, and sanitize any confidential information before uploading.

What features do you think are causing the issue

I'm pretty unsure. :-/

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

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

I doubt yes.

Which versions of PhpSpreadsheet and PHP are affected?

1.25.1

[oleibman]

This is probably solved by PR #3130. Please test with it if you can. It is still in draft status, but I think I am getting close to readying it.

[ChrisPHL]

I temporarily exchanged my installation with the code of your PR. Unfortunately it is not fixing it. :-( I really would appreciate if you would test your WIP with my example file "PHPSpreadsheet_bugdemo_original.ods". (Download at https://www.moellner-rc.com/ls_bugdemo/) Thank you!

[oleibman]

Sorry, there are many aspects of ods which are currently unimplemented in PhpSpreadsheet, and this is one of them. My PR applies only to Excel xlsx/xlsxm files.

[ChrisPHL]

Ah, I tried with an xls file too. To be exact I opened an xls file and saved as xlsx. What do you think ,should I retry with an xlsx file AS source? (Would be not that easy for me since LibreOffice (7.2) is not able to save xls (not sure if xlsx also) files including Basic/VBA script.)

[oleibman]

You may be out of luck, then. My PR won't handle xls either. I did try an online converter on your sample file - it didn't work, which isn't terribly surprising.

[oleibman]

LibreOffice 7.3.6.2 allowed me to save your ods worksheet as xlsx (not xlsm). When I open that xlsx file, the button is there, and you can click it, but Excel will say it can't find the macros, which is reasonable. I am happy to report that loading and saving that file using my PR does create a good copy (still has the button, you can still push it, but ...). So perhaps you can do the same and manually add the macros. I don't know if that's good enough for you, but it's probably as far as I can go for now.

[B3none]

I also have a spreadsheet which, opening and saving with PHPSpreadsheet, is clearing the buttons.