Inserting Multiple Images in Worksheet

Topics: Developer Forum, User Forum
Mar 16, 2010 at 5:24 AM

Hi Guys,

I am trying to insert multiple images in a worksheet. I was trying to place them one below another. setOffsetX is working fine but setOffsetY seems to have no affect on moving any image down. Here is what I am using:

I am using PHPExcel v1.7.2, XAMPP on WIN with PHP 5.3, Excel5 Writer

$excel = new PHPExcel();



$sheet = $excel->getActiveSheet();

$objDrawing = new PHPExcel_Worksheet_Drawing();




$objDrawing->setOffsetX(30);    // setOffsetX works properly

$objDrawing->setOffsetY(300);  //setOffsetY has no effect



$writer = new PHPExcel_Writer_Excel5($excel);



What am I doing wrong here? I even tried setting the font to Arial 10 but it still didn't work. Kind of stuck at the moment. :)

- Ananth

$objDrawing = new PHPExcel_Worksheet_Drawing();



Mar 16, 2010 at 5:45 AM

I just noticed  that if I increase the OffsetX value beyond 65 it ignores shifting the picture  with respect to the B1 cell and places it exactly at the starting of the B1 cell.

So now my problem is how do I calculate how many cells does a picture span across width wise/ height wise and then place the next one? Ideally I wanted to place pictures one below another.

Mar 16, 2010 at 5:54 AM

Excel5 writer still has problems if offsets exceed pixel dimensions of the anchor cell.

The unique position of a drawing in a worksheet is determined as follows:

1) Coordinates: The anchor cell which the upper left corner of the drawing falls into e.g. 'A1'
2) OffsetX: Pixel offset to the right
3) OffsetY: Pixel offset downwards

Instead of this:


Use the anchor cell which the upper-left corner of the drawing falls into. Maybe something like this:




Mar 16, 2010 at 6:23 AM

Hi Koyama,

Thanks for your explanation. I understood that OffsetX and OffsetY are specific to the cell. So in my case I won't be using those. The trouble is that once I insert the picture (Height: 200pixels x  Width: 300pixels) in an anchor cell e.g. B1, then I need to calculate the anchor cell to be used for the next picture in this case lets say B11 (assuming all cells have same height of 20pixels).

I know it is tough for you to calculate what the cell height will be, as it changes with the font and font size used.

Is there any way of returning the cell height if there is a default font and default font size being used for the whole sheet? 






Mar 16, 2010 at 6:44 AM
ananthdeodhar wrote:

Is there any way of returning the cell height if there is a default font and default font size being used for the whole sheet? 

If you already now know that you are going to use Arial 10 for the whole workbook, then the default row height is 17px as you see in Excel by clicking between to rows.

If you are asking about arbitrary fonts and sizes, then it is complicated. We have a function that returns the default row height in points for some of the common Excel fonts Arial (up to 10pt), Calibri (up to 11), Verdana (up to 10).


$pt = PHPExcel_Shared_Font::getDefaultRowHeightByFont(PHPExcel_Style_Font $font); // default row height in pt

This value can then be converted from points to pixels

$px = PHPExcel_Shared_Drawing::pointsToPixels($pt); // convert to pixels


Mar 16, 2010 at 7:58 AM

Thanks Koyama, I was thinking along the same lines. I had figured out the height difference the same way. So, will have to write out a function to figure this out.

I came across the row height explanation at