In a basic Office 365 Excel spreadsheet, I can enter '=Image("url-to-an-image")' and the image shows in the cell. (documented here: https://support.microsoft.com/en-gb/office/image-function-7e112975-5e52-4f2a-b9da-1d913d51f5d5)
Now in PhpSpreadsheet if I use:
$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
$activeWorksheet = $spreadsheet->getActiveSheet();
$url = "URL-to-image-file";
$activeWorksheet->setCellValue("A1", "=Image(\"$url\")");
$writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
$writer->save('test.xlsx');
The resulting text/formula in cell A1 of text.xlsx is: =@Image("URL-to-image-file") and the cell shows #NAME. If, from within excel I remove the '@' then the expected image shows.
I have also tried:
setCellValueExplicit("A1", "=Image(\"$url\")", \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_FORMULA);
which gives the same result
I also tried some code for array formulas, that did not work either.
Edit: Some additional info: I see that if, in Excel, I enter "=@Image('URL')" the image is displayed as expected. I also see that if created by PhpSpreadsheet, and then opened in Excel it shows #NAME. But if in Excel I edit the cell and just hit with no change to the formula, the image shows.
How I can create this formula and when opened in excel the image is retrieved and shown?
Any suggestions would be appreciated. Thanks.