Images on worksheet - exact position in pixels

Topics: Developer Forum, User Forum
Jun 16, 2008 at 9:23 AM
There are 6 photos with the same widths.
Sheet columns have different widths.
I need to position photos on the worksheet with the same distance from each other.

How can I determine widths of columns in pixels?

Jun 16, 2008 at 11:15 AM

I found:

http://processtrends.com/pg_charts_cell_size.htm

But Column Width (Characters)  = (Width  Pixels - 5)/5 doesn't always work properly.
Coordinator
Jun 16, 2008 at 11:57 AM
Edited Jun 16, 2008 at 12:01 PM
Have a look at the imagettfbbox() function in the PHP GD library. This takes as parameters the font, font size, text string (and display angle if the text is rotated) and returns an array for the positions of the top, bottom, left and right coordinates of the bounding box in pixels.
If you know the width of the box in characters, and the font used, then you can work out the pixel width for a standard character string.
 
Remember that the default PHPExcel font, Calibri, is proportionally spaced. You probably want to set the font to be a monospaced font (such as Courier New).
Jun 18, 2008 at 1:18 PM
http://www.xtremevbtalk.com/showthread.php?t=67565
Coordinator
Jun 18, 2008 at 4:31 PM
Thank's for the tip!
Jun 19, 2008 at 6:09 PM
No. This formula is wrong.

We can simple use methods _sizeCol and _sizeRow from PHPExcel_Writer_Excel5_Worksheet class (Excel 5 Writer)

/**
    * Convert the width of a cell from user's units to pixels. By interpolation
    * the relationship is: y = 7x +5. If the width hasn't been set by the user we
    * use the default value. If the col is hidden we use a value of zero.
    *
    * @access private
    * @param integer $col The column
    * @return integer The width in pixels
    */
function _sizeCol($col)
    {
        // Look up the cell value to see if it has been changed
        if (isset($this->col_sizes[$col])) {
            if ($this->col_sizes[$col] == 0) {
                return(0);
            } else {
                return(floor(7 * $this->col_sizes[$col] + 5));
            }
        } else {
            return(64);
        }
    }

    /**
    * Convert the height of a cell from user's units to pixels. By interpolation
    * the relationship is: y = 4/3x. If the height hasn't been set by the user we
    * use the default value. If the row is hidden we use a value of zero. (Not
    * possible to hide row yet).
    *
    * @access private
    * @param integer $row The row
    * @return integer The width in pixels
    */
    function _sizeRow($row)
    {
        // Look up the cell value to see if it has been changed
        if (isset($this->_row_sizes[$row])) {
            if ($this->_row_sizes[$row] == 0) {
                return(0);
            } else {
                return(floor(4/3 * $this->_row_sizes[$row]));
            }
        } else {
            return(17);
        }
    }

 _col_sizes - array of column widths in symbols.
These formulas are very precise (for Excel 5).

Tips:
1) We can not  use large pictures in  BIFF5.
Use phpThumb (http://phpthumb.sourceforge.net/) to generate BMP thumbnails:

phpThumb->resetObject();
$phpThumb->setSourceFilename($input_file);
$phpThumb->setParameter('w', $this->thumbnail_width);
$phpThumb->setParameter('config_output_format', 'bmp');
       
if ($phpThumb->GenerateThumbnail())
  $phpThumb->RenderToFile($output_file);

2) Do not use BIFF8!
3) Gnumeric can open BIFF5 with pictures. OpenOffice can not it.
4) Direct usage PHPExcel_Writer_Excel5 class is faster than working with PHPExcel class.