Saving IOFactory::WRITER_XLSX does not work for an Amazon Template for their CPF Program (Climate Pledge Friendly)

[xonu]

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?

The file.xlsx saved successfully.

What is the current behavior?

Saving file does not work:

Fatal error:  Uncaught Error: Object of class PhpOffice\PhpSpreadsheet\Calculation\Engine\Operands\StructuredReference could not be converted to string in /app/excel/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php:4716
Stack trace:
#0 /app/excel/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php(3761): PhpOffice\PhpSpreadsheet\Calculation\Calculation->processTokenStack()
#1 /app/excel/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php(3535): PhpOffice\PhpSpreadsheet\Calculation\Calculation->_calculateFormulaValue()
#2 /app/excel/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Cell/Cell.php(380): PhpOffice\PhpSpreadsheet\Calculation\Calculation->calculateCellValue()
#3 /app/excel/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Writer/Xlsx/Worksheet.php(1274): PhpOffice\PhpSpreadsheet\Cell\Cell->getCalculatedValue()
#4 /app/excel/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Writer/Xlsx/Worksheet.php(1343): PhpOffice\PhpSpreadsheet\Writer\Xlsx\Worksheet->writeCellFormula()
#5 /app/excel/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Writer/Xlsx/Worksheet.php(1195): PhpOffice\PhpSpreadsheet\Writer\Xlsx\Worksheet->writeCell()
#6 /app/excel/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Writer/Xlsx/Worksheet.php(87): PhpOffice\PhpSpreadsheet\Writer\Xlsx\Worksheet->writeSheetData()
#7 /app/excel/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Writer/Xlsx.php(393): PhpOffice\PhpSpreadsheet\Writer\Xlsx\Worksheet->writeWorksheet()
#8 /app/excel/index.php(23): PhpOffice\PhpSpreadsheet\Writer\Xlsx->save()
#9 {main}
  thrown in /app/excel/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php on line 4716

What are the steps to reproduce?

Nothing very special, just load the file and save it (without even changing anything) like that:

<php
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\IOFactory;

$filename = 'Amazon.CPF.empty';
$filePath = "input/$filename.xlsx";
$spreadsheet = IOFactory::load($filePath);

$xlsWriter = IOFactory::createWriter($spreadsheet, IOFactory::WRITER_XLSX);
$xlsWriter->save($filePathOutput = "output/$filename.xlsx");

If this is an issue with reading and writing a specific spreadsheet file:
Amazon CPF empty.xlsx

What features do you think are causing the issue

• 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?

XLSX IOFactory::WRITER_XLSX

Which versions of PhpSpreadsheet and PHP are affected?

1.29.0 (currently latest)

[oleibman]

The reported problem is fixed by PR #3659, which has just been merged. I am leaving the issue open because loading and saving the spreadsheet isn't quite perfect, in particular some dropdowns are lost.

[oleibman]

Interesting case. Data Validations and Conditional Styles can each be supplied in the Xml in either "external" or "internal" formats. The code for each to handle "external" assumes that each is the only "external" item on the worksheet in the Xml, but some of the worksheets in the sample spreadsheet provide both as "external" on some sheets. I will work on a solution.