How to fetch the images present in the excel cell?

user_3888958 0
Viewed 656

I need to read the images present in the cell, I know how to read the drawing present the sheet but the getDrawingCollection() only works on sheet. is there any function that will give the images present in the cell while getting the data. The below code is used to fetch the data of the sheet.

$spreadsheet = IOFactory::load($inputFileName);
  $worksheet = $spreadsheet->setActiveSheetIndex(2);
  foreach ($worksheet->getRowIterator() as $row) {      
      $cellIterator = $row->getCellIterator();
      $cellIterator->setIterateOnlyExistingCells(FALSE); 
      foreach ($cellIterator as $cell) {
          echo $cell->getValue();
      }          
  }

If the question is duplicate I am happy to remove it.

phpspreadsheet import-from-excel php
2 Answers

I don't think it has any function for that, but you can do something like this:

foreach ($worksheet->getDrawingCollection() as $drawing) {
    $drawings[$drawing->getCoordinates()] = $drawing;
}
// then find your image using your cell coordinates
$image = $drawings[$cell->getCoordinates()];

// or
foreach ($worksheet->getDrawingCollection() as $drawing) {
    if($drawing->getCoordinates() == $cell->getCoordinates()){
        // or do your stuff here
    }
}
user_99811370

I suspect this is a deliberate design decision by the PHPSpreadsheet team, since you cannot really insert an image into a cell in Excel - you can lock an image over a cell (such that as you resize rows/columns the image stays over that cell). Anyway, for your purpose, that's academic.

Ok, so we can only access drawings at the spreadsheet level. Fortunately, we can get the coordinates of the drawing.. so we can build an index on top of this data to give you the access you prefer.

class WorksheetDrawingExt {
    private array $idx=[];

    /**
     * Upon construction will build an index drawings and their locations
     */
    public function __construct(PhpOffice\PhpSpreadsheet\Worksheet\Worksheet $ws) {
        $this->createDrawingIndex($ws);
    }

    private function addDrawingToCell($coord,$drawing) {
        if (array_key_exists($coord,$this->idx)) {
            //There's already one image here - append a new
            $this->idx[$coord][]=$drawing;
        } else {
            //No images so far, setup the base array
            $this->idx[$coord]=[$drawing];
        }
    }

    private function createDrawingIndex($ws) {
        $drawings=$ws->getDrawingCollection();
        foreach ($drawings as $drawing) {
            $coord=$drawing->getCoordinates();//Inconsistent plural!
            $this->addDrawingToCell($coord,$drawing);
        }
    }

    /**
     * Get all drawings for a cell (always returns an array even if there's 1 or less images)
     */
    public function drawingsForCell(PhpOffice\PhpSpreadsheet\Cell\Cell $cell):array {
        return $this->drawingsForCoordinate($cell->getCoordinate());
    }

    /**
     * Get all drawings at the given coordinate (always returns an array even if there's 1 or less images)
     */
    public function drawingsForCoordinate(string $coordinate):array {
        if (array_key_exists($coordinate,$this->idx)) {
            return $this->idx[$coordinate];
        } else {
            return [];
        }
    }
}

You'll need to create one of these objects for each worksheet you access, and then the drawingsForCoordinate or drawingsForCell are the methods you care about. So how do you use it? Well as you iterate over a spreadsheet, you can ask this object for drawings related to a cell:

$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($file);
$ws = $spreadsheet->getActiveSheet();
//Build the new index of drawings:
$wsExt=new WorksheetDrawingExt($ws);

foreach ($ws->getRowIterator() as $row) {      
    $cellIterator = $row->getCellIterator();
    $cellIterator->setIterateOnlyExistingCells(FALSE); 
    foreach ($cellIterator as $cell) {
        //Report the coord:
        $coord=$cell->getCoordinate();
        echo $coord.': ';
        //Report the content 
        echo $cell->getValue();
        //Report images - our new feature in action
        $ds=$wsExt->drawingsForCoordinate($coord);
        //$ds=$wsExt->drawingsForCell($cell);
        foreach($ds as $d) echo $d->getPath().',';
        echo "\n";
    }
}

I've changed your code slightly to indicate the cell-coordinate we're currently on. And for the purposes of display, I output the drawing path (which only exists when it's not a \PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawing). You can use either the $wsExt->drawingsForCoordinate($coord); (active) or $wsExt->drawingsForCell($cell); (commented out) to access all drawings for the cell.

Because, for your purpose, you wish to access the drawings - I chose to build the index at WorksheetDrawingExt creation. However if drawings may not always be used (but the new class is always built), it might be better to late-bind the index creation to the first use of drawingsFor* - I'll leave that as an exercise for the reader.

user_756990
Related