How to group rows with pictures in excel using PHP?

Question

PHP

Here is a similar topic stackoverflow.com/questions/17920308/phpexcel-grou...
there are screenshots reflecting the essence of the problem.
When grouping lines containing an image, the image remains on top of the collapsed lines.
I saw similar files generated from 1s - the picture is wrapped there.
Moreover, if you create a file in the usual way - in libreoffice - if you place the picture in the range of folded lines - the picture is hidden (xlsx file format), and if you do the same in MS Office - the picture remains on top of the folded lines.
How to solve this problem?

Answer 1

SilverSlice Thanks for the tip! PHPExcel did not adjust much and everything worked out, but on the condition that the picture is within one cell (I needed it for the price list). If the picture climbs out of this cell, you will still need to think.
PHPExcel has a setResizeProportional method, but it is not used anywhere in PHPExcel/Writer/Excel2007/Drawing.php @version 1.7.9.
Added adjustments to the public function _writeDrawing function, I have this line 172: Before:
Now
:

$aResizeProportional = $pDrawing->getResizeProportional();

      if ($aResizeProportional) {
        $objWriter->startElement('xdr:twoCellAnchor');
      }
      else {
        $objWriter->startElement('xdr:oneCellAnchor');
      }

And one more edit just below
Was:

// xdr:ext
          $objWriter->startElement('xdr:ext');
            $objWriter->writeAttribute('cx', PHPExcel_Shared_Drawing::pixelsToEMU($pDrawing->getWidth()));
            $objWriter->writeAttribute('cy', PHPExcel_Shared_Drawing::pixelsToEMU($pDrawing->getHeight()));
          $objWriter->endElement();

It became

if ($aResizeProportional) {
          $objWriter->startElement('xdr:to');
            $objWriter->writeElement('xdr:col', $aCoordinates[0] - 1);
            $objWriter->writeElement('xdr:colOff', PHPExcel_Shared_Drawing::pixelsToEMU($pDrawing->getOffsetX()) + PHPExcel_Shared_Drawing::pixelsToEMU($pDrawing->getWidth()));
            $objWriter->writeElement('xdr:row', $aCoordinates[1] - 1);
            $objWriter->writeElement('xdr:rowOff', PHPExcel_Shared_Drawing::pixelsToEMU($pDrawing->getOffsetY()) + PHPExcel_Shared_Drawing::pixelsToEMU($pDrawing->getHeight()));
          $objWriter->endElement();
        } else {
          // xdr:ext
          $objWriter->startElement('xdr:ext');
            $objWriter->writeAttribute('cx', PHPExcel_Shared_Drawing::pixelsToEMU($pDrawing->getWidth()));
            $objWriter->writeAttribute('cy', PHPExcel_Shared_Drawing::pixelsToEMU($pDrawing->getHeight()));
          $objWriter->endElement();
                }

When we create an object we add this:

$objDrawing->setWidth($width);
$objDrawing->setHeight($height);
$objDrawing->setResizeProportional(true); 
$objDrawing->setWidthAndHeight($width,$height);

Answer 2

The xlsx file is a zip archive, unzip it using php and find the file xl/drawings/drawing1.xml . It contains information about the pictures on the sheet.
PHPExcel puts all pictures into oneCellAnchor elements (move but don't resize), you need to change them to twoCellAnchor (move and resize the object along with the cells). See the Open Office XML documentation for details.
UPD. Implemented this algorithm in the library . Try it, everything works on my test data.

Answer 3

You need to change the drawing properties. The default is underlined in red, you need to set the marked with a green checkmark.

Answer 4

Well, we figured it out in Excel itself .. is it possible to programmatically set this property for an image? In PHPExcel - judging by their forum there is no such possibility.. are there any paid solutions that can solve this problem?