Hi, here's a question: I want to insert an image and want to resize it to fit in a certain area. However I need to keep the original height/width ratio in tact. How do I do that?
Regards, Mark Ekkers
Hi,
Sorry for the delay, the machine crashed yesterday when I was writing the answer, and when I had it back working I forgot it.
Basically, inside the Excel file, images are always stored with coordinates: TopLeft-Cell, Bottom-Right cell, and the offset from the start of the cell.
For example, an image going from the middle of A3 to the start of B5, would be stored as A3:B5, with an x and y offset of 50% for A3, and offset of 0% for B5.
This means that in order to maintain aspect ratios, you need to calculate how many rows and columns it would take to keep it. TClientAnchor already contains a method that will do the calculation for you.
Besides the standard constructor taking the cells and offsets:
/// <summary>
/// Creates a new ClientAnchor object, based on cell coords. Does not take in count actual image size.
/// </summary>
/// <param name="aAnchorType">How the image behaves when copying/inserting cells.</param>
/// <param name="aCol1">First column of object</param>
/// <param name="aDx1">Delta x of image, on 1/1024 of a cell. 0 means totally at the left, 512 on half of the cell, 1024 means at the left of next cell.</param>
/// <param name="aRow1">First Row of object.</param>
/// <param name="aDy1">Delta y of image on 1/255 of a cell. 0 means totally at the top, 128 on half of the cell, 255 means at the top of next cell.</param>
/// <param name="aCol2">Last column of object.</param>
/// <param name="aDx2">Delta x of image, on 1/1024 of a cell. 0 means totally at the left, 512 on half of the cell, 1024 means at the left of next cell.</param>
/// <param name="aRow2">Last row of object.</param>
/// <param name="aDy2">Delta y of image on 1/255 of a cell. 0 means totally at the top, 128 on half of the cell, 255 means at the top of next cell.</param>
class function Create(const aAnchorType: TFlxAnchorType; const aRow1: Int32; const aDy1: Int32; const aCol1: Int32; const aDx1: Int32; const aRow2: Int32; const aDy2: Int32; const aCol2: Int32; const aDx2: Int32): TClientAnchor; overload; static;
There is another that takes widths and heights in pixels, and converts them to cells and offsets:
/// <summary>
/// Creates a new image based on the image size.
/// </summary>
/// <param name="aAnchorType">How the image behaves when copying/inserting cells.</param>
/// <param name="aRow1">Row where to insert the image.</param>
/// <param name="aCol1">Column where to insert the image.</param>
/// <param name="aPixDy1">Delta in pixels that the image is moved from aRow1.</param>
/// <param name="aPixDx1">Delta in pixels that the image is moved from aCol1.</param>
/// <param name="height">Height in pixels.</param>
/// <param name="width">Width in pixels.</param>
/// <param name="Workbook">ExcelFile with the workbook, used to calculate the cells.</param>
class function Create(const aAnchorType: TFlxAnchorType; const aRow1: Int32; const aPixDy1: Int32; const aCol1: Int32; const aPixDx1: Int32; const height: Int32; const width: Int32; const Workbook: IRowColSize): TClientAnchor; overload; static;
This second constructor is the one you want to use. It takes the width and height of the image. If your original image was (100x 75), and you want a width of 150, then the new heigth you need to specify is 150/100*75 or width_in_excel / original_width * original_height;
Of course internally all will be converted to cells and offsets, so this isn't "pixel perfect". In fact, "pixels" in Excel depend on a lot of factors like the resolution (you will see that a print preview has wider columns than what is shown in the screen). So it is a "kind of pixels" thing. But even with that, it will allow you to insert images with the correct dimensions and aspect ratio.