PHPExcel export xlsx with drawing got bug in width and height

Topics: User Forum
Sep 5, 2012 at 2:09 AM

Hi guys, i'm currently working with a project that need to export data to excel with drawing in it. I'm using PHPExcel 1.7.7 and PHP 5.3.8 on Window XP SP3

After export to xlsx, the content of file look just fine in the normal view, but when i print preview, width and height of the drawing has change

Here is the code snippet i used:

       $file_path = dirname(__FILE__) . '\template.xlsx';
        $objReader = new PHPExcel_Reader_Excel2007();
        $objPHPExcel = $objReader->load($file_path);
       
        $objPHPExcel->setActiveSheetIndex(0);
       
        $objDrawing = new PHPExcel_Worksheet_Drawing();
       
        $objDrawing->setPath('C:/Documents and Settings/All Users/Documents/My Pictures/Sample Pictures/Sunset.jpg');
        $objDrawing->setWidthAndHeight(360, 252);
        $objDrawing->setCoordinates('B3');
        $objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
       
        $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
        $objWriter->save('C:/test.xlsx');

 

Template file: http://www.mediafire.com/view/?cq24o5ma20kesgf

Result file: http://www.mediafire.com/view/?x8c50bpdteywx20

Coordinator
Sep 5, 2012 at 9:15 PM

I can see the image in the result file but not in the template. Can you upload the files to http://phpexcel.codeplex.com/workitem/10749?ProjectName=phpexcel

Sep 6, 2012 at 1:04 AM
Edited Sep 6, 2012 at 9:21 AM

there is no image in the template file, all i do is read the template, add the drawing and save to result file. In normal mode view, the drawing fit perfectly in the border that i make, but in print preview mode, the drawing has been scaled and if you go to Size and Properties of the drawing, change the option in object positioning at properties tab, the drawing will display properly in print preview mode. Can you point out what is wrong with the drawing?

Link to the file: http://phpexcel.codeplex.com/workitem/18596

 

Sep 6, 2012 at 9:23 AM
Edited Sep 6, 2012 at 9:31 AM

I tried to create an excel file with a image that look perfectly in normal view and print preview and use it as a template. In code, i loaded the template then save it without changing anything, and the result file still have the bug as i describe above. You can take a look at my templateWithImage.xlsx in http://phpexcel.codeplex.com/workitem/18596

Code snippet:

        $filename = dirname(__FILE__).'\template.xlsx';
        $objReader = new PHPExcel_Reader_Excel2007();
       
        $objPHPExcel = $objReader->load($filename);

        $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
        $objWriter->save('C:/test.xlsx');

Sep 6, 2012 at 11:01 PM

I had the same problem. Every time I have to open the generated excel file, and change the properties of the image object to 'Move and size with cells' to solve the issue. It's really inconvenient. I am glad to know a better solution here.

Sep 11, 2012 at 1:24 AM
Edited Sep 12, 2012 at 2:02 AM

does anyone have any ideas to solve this problem?

I found the solution, changed the source code of Drawing.php under Writer/Excel2007 folder (start from line 176 and end at line 204, hope this will help you with your project

$objWriter->startElement('xdr:twoCellAnchor');
                // Image location
                $aCoordinates         = PHPExcel_Cell::coordinateFromString($pDrawing->getCoordinates());
                $aCoordinates[0]     = PHPExcel_Cell::columnIndexFromString($aCoordinates[0]);
                               
                                $aCoordinatesTO         = PHPExcel_Cell::coordinateFromString($pDrawing->getCoordinatesTO());
                                $aCoordinatesTO[0]      = PHPExcel_Cell::columnIndexFromString($aCoordinatesTO[0]);
                               
                // xdr:from
                $objWriter->startElement('xdr:from');
                    $objWriter->writeElement('xdr:col', $aCoordinates[0] - 1);
                    $objWriter->writeElement('xdr:colOff', PHPExcel_Shared_Drawing::pixelsToEMU($pDrawing->getOffsetX()));
                    $objWriter->writeElement('xdr:row', $aCoordinates[1] - 1);
                    $objWriter->writeElement('xdr:rowOff', PHPExcel_Shared_Drawing::pixelsToEMU($pDrawing->getOffsetY()));
                $objWriter->endElement();
                               
                                // xdr:to
                                $objWriter->startElement('xdr:to');
                    $objWriter->writeElement('xdr:col', $aCoordinatesTO[0] - 1);
                    $objWriter->writeElement('xdr:colOff', PHPExcel_Shared_Drawing::pixelsToEMU($pDrawing->getOffsetX() * -1));
                    $objWriter->writeElement('xdr:row', $aCoordinatesTO[1] - 1);
                    $objWriter->writeElement('xdr:rowOff', PHPExcel_Shared_Drawing::pixelsToEMU($pDrawing->getOffsetY() * -1));
                $objWriter->endElement();

                // 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();

May 18, 2013 at 12:20 AM
@trongcuong1710

The move to the the twoCellAnchor certainly looks like a move in the right direction. The image degradation I have seen in my tests are accompanied by by a change from a twoCellAnchor in the template document to a oneCellAnchor in the document persisted by the 2007 writer.

Looks to me like your solution must be accompanied by additional supporting code in the drawing hierarchy not posted above. In particular I don't see an implementation of $pDrawing->getCoordinatesTO() in PHPExcel 1.7.8, 1.7.7 or 1.7.3 ... I stopped looking assuming you created those.

The getCoordinateTO() implementation would be greatly appreciated.
May 18, 2013 at 1:17 AM
never mind ...

added some minimal hacks to persist the 'TO' coordinate in a new base drawing member to get your code to run. While the result is somewhat better than what I got out of the box, the simple negation on colOff and rowOff in the xdr:to element are bogus. Seems to me this would only work on extremely well placed images - those offset equidistantly from the 'from' and 'to' coordinates.
Apr 21, 2015 at 4:23 PM
This is helpfull patch for PHPExcel 1.8.0. The problem resolved in it:
diff -rub PHPExcel_1.8.0.orig/Classes/PHPExcel/Reader/Excel2007.php phpexcel/Classes/PHPExcel/Reader/Excel2007.php
--- PHPExcel_1.8.0.orig/Classes/PHPExcel/Reader/Excel2007.php   2015-04-21 18:14:03.542709000 +0400
+++ phpexcel/Classes/PHPExcel/Reader/Excel2007.php  2015-04-21 18:29:53.707014100 +0400
@@ -1502,10 +1502,20 @@
                                                    $objDrawing->setCoordinates(PHPExcel_Cell::stringFromColumnIndex((string) $twoCellAnchor->from->col) . ($twoCellAnchor->from->row + 1));
                                                    $objDrawing->setOffsetX(PHPExcel_Shared_Drawing::EMUToPixels($twoCellAnchor->from->colOff));
                                                    $objDrawing->setOffsetY(PHPExcel_Shared_Drawing::EMUToPixels($twoCellAnchor->from->rowOff));
+
+                                                   $objDrawing->setToCoordinates(PHPExcel_Cell::stringFromColumnIndex((string) $twoCellAnchor->to->col) . ($twoCellAnchor->to->row + 1));
+                                                   $objDrawing->setToOffsetX(PHPExcel_Shared_Drawing::EMUToPixels($twoCellAnchor->to->colOff));
+                                                   $objDrawing->setToOffsetY(PHPExcel_Shared_Drawing::EMUToPixels($twoCellAnchor->to->rowOff));
+
                                                    $objDrawing->setResizeProportional(false);
 
-                                                   $objDrawing->setWidth(PHPExcel_Shared_Drawing::EMUToPixels(self::array_item($xfrm->ext->attributes(), "cx")));
-                                                   $objDrawing->setHeight(PHPExcel_Shared_Drawing::EMUToPixels(self::array_item($xfrm->ext->attributes(), "cy")));
+//                                                 if ($xfrm->ext) {
+//                                                     $objDrawing->setWidth(PHPExcel_Shared_Drawing::EMUToPixels(self::array_item($xfrm->ext->attributes(), "cx")));
+//                                                     $objDrawing->setHeight(PHPExcel_Shared_Drawing::EMUToPixels(self::array_item($xfrm->ext->attributes(), "cy")));
+//                                                 }
+
+                                                   $objDrawing->setWidth(PHPExcel_Shared_Drawing::EMUToPixels(self::array_item($twoCellAnchor->ext->attributes(), "cx")));
+                                                   $objDrawing->setHeight(PHPExcel_Shared_Drawing::EMUToPixels(self::array_item($twoCellAnchor->ext->attributes(), "cy")));
 
                                                    if ($xfrm) {
                                                        $objDrawing->setRotation(PHPExcel_Shared_Drawing::angleToDegrees(self::array_item($xfrm->attributes(), "rot")));
diff -rub PHPExcel_1.8.0.orig/Classes/PHPExcel/Worksheet/BaseDrawing.php phpexcel/Classes/PHPExcel/Worksheet/BaseDrawing.php
--- PHPExcel_1.8.0.orig/Classes/PHPExcel/Worksheet/BaseDrawing.php  2015-04-21 18:14:04.212710000 +0400
+++ phpexcel/Classes/PHPExcel/Worksheet/BaseDrawing.php 2015-04-21 17:57:39.647282800 +0400
@@ -253,6 +253,22 @@
     }
 
     /**
+    * Coordinates
+    *
+    * @var string
+    */
+   private $_toCoordinates;
+
+    /**
+     * Get "TO" Coordinates
+     *
+     * @return string
+     */
+    public function getToCoordinates() {
+       return $this->_toCoordinates;
+    }
+
+    /**
      * Set Coordinates
      *
      * @param string $pValue
@@ -264,6 +280,17 @@
     }
 
     /**
+     * Set "TO" Coordinates
+     *
+     * @param string $pValue
+     * @return PHPExcel_Worksheet_BaseDrawing
+     */
+    public function setToCoordinates($pValue = 'A1') {
+       $this->_toCoordinates = $pValue;
+       return $this;
+    }
+
+    /**
      * Get OffsetX
      *
      * @return int
@@ -284,6 +311,47 @@
     }
 
     /**
+    * @var int
+    */
+   private $_toOffsetX;
+   /**
+    * @var int
+    */
+   private $_toOffsetY;
+
+    /**
+     * @return int
+     */
+    public function getToOffsetX() {
+       return $this->_toOffsetX;
+    }
+
+    /**
+     * @param int $pValue
+     * @return PHPExcel_Worksheet_BaseDrawing
+     */
+    public function setToOffsetX($pValue = 0) {
+       $this->_toOffsetX = $pValue;
+       return $this;
+    }
+
+    /**
+     * @return int
+     */
+    public function getToOffsetY() {
+       return $this->_toOffsetY;
+    }
+
+    /**
+     * @param int $pValue
+     * @return PHPExcel_Worksheet_BaseDrawing
+     */
+    public function setToOffsetY($pValue = 0) {
+       $this->_toOffsetY = $pValue;
+       return $this;
+    }
+
+    /**
      * Get OffsetY
      *
      * @return int
diff -rub PHPExcel_1.8.0.orig/Classes/PHPExcel/Writer/Excel2007/Drawing.php phpexcel/Classes/PHPExcel/Writer/Excel2007/Drawing.php
--- PHPExcel_1.8.0.orig/Classes/PHPExcel/Writer/Excel2007/Drawing.php   2015-04-21 18:14:04.392710200 +0400
+++ phpexcel/Classes/PHPExcel/Writer/Excel2007/Drawing.php  2015-04-21 20:02:13.711107600 +0400
@@ -172,6 +172,32 @@
    public function _writeDrawing(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet_BaseDrawing $pDrawing = null, $pRelationId = -1)
    {
        if ($pRelationId >= 0) {
+           if ($pDrawing->getToCoordinates()) {
+                $objWriter->startElement('xdr:twoCellAnchor');
+                // Image location
+                $aCoordinates         = PHPExcel_Cell::coordinateFromString($pDrawing->getCoordinates());
+                $aCoordinates[0]     = PHPExcel_Cell::columnIndexFromString($aCoordinates[0]);
+
+                $aCoordinatesTO         = PHPExcel_Cell::coordinateFromString($pDrawing->getToCoordinates());
+                $aCoordinatesTO[0]      = PHPExcel_Cell::columnIndexFromString($aCoordinatesTO[0]);
+
+                // xdr:from
+                $objWriter->startElement('xdr:from');
+                    $objWriter->writeElement('xdr:col', $aCoordinates[0] - 1);
+                    $objWriter->writeElement('xdr:colOff', PHPExcel_Shared_Drawing::pixelsToEMU($pDrawing->getOffsetX()));
+                    $objWriter->writeElement('xdr:row', $aCoordinates[1] - 1);
+                    $objWriter->writeElement('xdr:rowOff', PHPExcel_Shared_Drawing::pixelsToEMU($pDrawing->getOffsetY()));
+                $objWriter->endElement();
+
+                // xdr:to
+                $objWriter->startElement('xdr:to');
+                    $objWriter->writeElement('xdr:col', $aCoordinatesTO[0] - 1);
+                    $objWriter->writeElement('xdr:colOff', PHPExcel_Shared_Drawing::pixelsToEMU($pDrawing->getToOffsetX()));
+                    $objWriter->writeElement('xdr:row', $aCoordinatesTO[1] - 1);
+                    $objWriter->writeElement('xdr:rowOff', PHPExcel_Shared_Drawing::pixelsToEMU($pDrawing->getToOffsetY()));
+                $objWriter->endElement();
+           }
+           else {
            // xdr:oneCellAnchor
            $objWriter->startElement('xdr:oneCellAnchor');
                // Image location
@@ -191,6 +217,7 @@
                    $objWriter->writeAttribute('cx', PHPExcel_Shared_Drawing::pixelsToEMU($pDrawing->getWidth()));
                    $objWriter->writeAttribute('cy', PHPExcel_Shared_Drawing::pixelsToEMU($pDrawing->getHeight()));
                $objWriter->endElement();
+           }
 
                // xdr:pic
                $objWriter->startElement('xdr:pic');