merrymonk has asked for the wisdom of the Perl Monks concerning the following question:

I want to add an image to an Excel spreadsheet, find its size and then resize it to give a required size.
The Perl code below does most of this.
However, when I gave new sizes for the width and height but only the second of these ‘worked’ since the aspect ratio of the image was maintained.
This can be seen in the output from the Perl code which is given next.
original width <94.5> height <115.5> aspect ratio <1.22222222222222>
new width <81.8181915283203> height <100> aspect ratio <1.22222207716955>
I did try the ‘traditional’ route of recoding a macro in Excel (for example to add a border) but I could not seem to get any code for any of the functions in the Picture Tool -> Format tab.
Therefore my three questions are:
1. How do I ‘unset’ the keep the aspect ratio constant attribute of an image in Excel;
2. How do I get recorded macro instructions for the actions in the Format tab in Excel;
3. Is there a list somewhere of the attributes that are associated with an Excel picture (my attempts to find these on the Internet sadly have failed)?
use strict ;
use OLE;
use Win32::OLE::Const "Microsoft Excel";
my ($excel, $workbook, $sheet, $j, $row, $range, $cell_str, $content, 
+$contentb, , $contentc, $width_found, $id, $test_cell_idg );
my ($worktable_name, $jwk, $jr, $sheets_total, $new_wk); 
my ($cell_tg, $v_pos, $h_pos, $pic_cur, $image_file_full, $cell_id, $c
+ur_width, $cur_height, $aspect_ratio);
#___ DEFINE EXCEL
$excel = CreateObject OLE "Excel.Application";
#___ MAKE EXCEL VISIBLE
$excel -> {Visible} = 1;
#___ ADD NEW WORKBOOK
$workbook = $excel    -> Workbooks -> Add;
$sheet    = $workbook -> Worksheets("Sheet1");
$sheet                -> Activate;
$image_file_full =  “the full directory path and file name of an image
+ is written here”
$cell_id = "B3";
$cell_tg = $sheet->Range($cell_id);
$v_pos = $cell_tg->Top;
$h_pos = $cell_tg->Left;
# insert picture with top left hand corner at cell_id
$pic_cur = $sheet->Pictures->Insert($image_file_full);
$pic_cur ->{Top}  = $cell_tg->Top;
$pic_cur ->{Left} = $cell_tg->Left;
# get the size and aspect ratio of the original image
$cur_width    = $pic_cur ->{Width};
$cur_height    = $pic_cur ->{Height};
$aspect_ratio  = $cur_height / $cur_width;
print "original width <$cur_width> height <$cur_height> aspect ratio <
+$aspect_ratio>\n";
# change the width and size of the image
$pic_cur ->{Width}  = 200;
$pic_cur ->{Height} = 100;
# get the size and aspect ratio of the resized image
$cur_width    = $pic_cur ->{Width};
$cur_height    = $pic_cur ->{Height};
$aspect_ratio  = $cur_height / $cur_width;
print "new width <$cur_width> height <$cur_height> aspect
 ratio <$aspect_ratio>\n";

[download]

Replies are listed 'Best First'.
Re: Excel Picture Size Changes & other actions
by bart (Canon) on Nov 02, 2010 at 12:07 UTC
    Just an aside regarding your formatting: I see the site stripped out a bunch of your "</br>" tags (because I set my preferences to show me what was modified by the server from what you entered). That is quite understandable, because that kind of tag doesn't legally exist in HTML!

    You should be using either "<br />" (slash at the back, space not required) or plain old "<br>".

    As for keeping the aspect ratio: why don't you keep it in the proper ratio yourself? Try to reduce either the width or the height, so the other value has its maximum allowed value, and the current aspect ratio is respected. You can do that like this (untested): 

    my $width = $pic_cur ->{Width};
my $height = $pic_cur ->{Height};
my $aspect = $height / $width;

my $max_height = 100;
my $max_width = 200;

my $new_width = $max_width;
my $new_height = $max_width * $aspect;
if($new_height > $max_height) {
    # too high, so reduce width < max_width
    $new_height = $max_height;
    $new_width = $new_height / $aspect;
}
# else: height <= max_height

    [download]
[reply]
[d/l]
[select]
      Thanks for that. However, I want to be able to change the aspect ratio so that I can distort the original image.
      I have tried the following 2 lines
      $pic_cur ->{AspectRatio}  = "False";
$pic_cur ->{LockAspectRatio} = "False";

      [download]
      but neither had the desired effect.
[reply]
[d/l]
        Well, as I calculate the aspect ratio from the picture dimensions from the original image as it is loaded, you can simply ignore that value and set the desired aspect ratio yourself — or at least, modify it at will. For example, inserting 
        $aspect *= 2;

        [download]
        before using the value, will make the picture twice as high as it should be, although it might be scaled back to fit the maximum allowed size.
[reply]
[d/l]
      To be pedantic, <br /> isn't actually legal in HTML either. (If you think it is, I challenge you to give the relevant SGML/HTML-DTD rules and the derivation)
[reply]
        Go to http://validator.w3.org/ and enter 
        <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html><HEAD><TITLE>Br</TITLE></HEAD><BODY><br/></body></html>

        [download]
        compare with 
        <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0//EN" "http://www.w3.org/TR
+/html4/strict.dtd">
<html><HEAD><TITLE>Br</TITLE></HEAD><BODY><br/></body></html>

        [download]
[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom