phpspreadsheet how to replace an image in a template

Question

Is it possible to swap out images in existing template file?

In my excel xlsx template file are three images. Each image has its own unique name/title (IE: "imageBanner_1"). Each image is in a location in reference to where certain charts are on the worksheet - but not necessarily bound to a specific cell.

Based on using the image name, is it possible to replace the image with another so that the new image is in the same location as the original and retains all of the original image properties (shadow, width/length, name, etc)?

This is the method I have tried but has not worked:

  $fImages = $speadsheet->getSheetByName("Chart Data")->getDrawingCollection();
  foreach ($fImages as $fImage) {
    echo "\n".$fImage->getName() ;
    if ($fImage->getName() == "imageBanner_1") {
      $fImage->setPath("path/to/new/file/fileName.png") ;
    }
  }

How can I get all the properties of $fImage? Maybe one of them is the correct name that will match on imageBanner_1

In my excel templates, I have bound the names (in Formulas -> Name manager) as:

Name             Value         Refers To
imageBanner_1    Picture 9     =*Picture 9*
imageBanner_2    Picture 18    =*Picture 18*
imageBanner_3    Picture 21    =*Picture 21*

Doing the echo prints out:

Picture 9
Picture 18
Picture 21

Which tells me the getName() is either a reference to the value OR getName() is actually Picture X. Either way, how can I use the imageBanner_X names as the identifier? I don't know if my code to simply replace the image with a setPath will work, because at least for now I can't actually get to the image because of the getName() issue.

======== UPDATE ======

Well, my above code works IF I change:

if ($fImage->getName() == "imageBanner_1") {

to

if ($fImage->getName() == "Picture 13") {

...but the problem with this is I have to know the exact picture number I am replacing. This is not ideal as the picture number is not nessissarily a static number all the time. In some cases I am replicating charts or images, in others I am replacing...esp when it comes to replicating it will be quite challenging to know the exact picture number each time I run the script with different data sets.

score 0 · Answer 1 · answered Jul 29 '22 at 21:04

Well, this took some time, but I finally got it work.

$mainTempName = "$baseDir/reports/MainReportTemplate.xlsx" ;
$mainTempReader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader("Xlsx") ;
$mainTempFile = $mainTempReader->load($mainTempName, \PhpOffice\PhpSpreadsheet\Reader\IReader::LOAD_WITH_CHARTS) ;

$mainTempFile = swapImage($mainTempFile,$localFilePath,$excelImageName,$localFileName) ;

function swapImage($inFile,$iPath,$iName,$iFile) {
  foreach ($inFile->getDefinedNames() as $name) {
    if ($name->getName() == $iName) {
      $picID = str_replace("\"","",$name->getValue()) ;
    }
  }

  foreach ($inFile->getSheetByName("Chart Data")->getDrawingCollection() as $fImage) {
    if ($fImage->getName() == $picID) {
      $fImage->setPath($iPath.$iFile) ;
    }
  }
  return $inFile ;
}

$name->getValue() outputs value in quotes "Picture 9", thus the need to use str_replace to strip out the "s as the $fImage->getName() value does not have quotes.

phpspreadsheet how to replace an image in a template

1 Answers1