How to insert a image at height100% and width100%

FLX TMS FlexCel for VCL & FMX
1
Hi,
I would like to insert the image at 100% height magnification and 100% width magnification.
Is that possible? If impossible, what kind of method is there?

Below is the ApiMate code.
It specifies the width and height with the number of cells with the dx or the dy.
It is necessary to convert height and width to cell height and width, which is difficult.

//------
  //Images
  fs := TFileStream.Create('imagename.png', fmOpenRead or fmShareDenyNone);
  try
    ImgProps := TImageProperties_Create();
    ImgProps.Anchor := TClientAnchor.Create(TFlxAnchorType.MoveAndDontResize, 2, 0, 2, 0, 14, 0, 7, 569);
    ImgProps.ShapeName := 'Image1';
    xls.AddImage(fs, ImgProps);
  finally
    fs.Free;
  end;
//------

Best regards,
Taro
2

Hi,

Internally, Excel stores the coordinates of images as cells/dx/dy, so indeed, we need to convert image width and image height into coordinates before inserting the image.

But TClientAnchor comes with a method that will do all the calculations for you:
 
  
   /// <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="aPixDy1">Delta in pixels that the image is moved from aRow1.</param>
    /// <param name="aCol1">Column where to insert the image.</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;

The method above will take the height and width of the image, and create an anchor for it.

Here is a complete code example:


program Project37;


{$APPTYPE CONSOLE}


{$R *.res}


uses
  Classes, System.SysUtils, VCL.FlexCel.Core, FlexCel.XlsAdapter;


var
  ImgProps: IImageProperties;
  Image: TUIImage;
  xls: TExcelFile;
begin
 xls := TXlsFile.Create(1, TExcelFileFormat.v2016, true);
 try
    Image := TUIImage.FromFile('r:\test.png');
    try
      ImgProps := TImageProperties_Create();
      ImgProps.Anchor := TClientAnchor.Create(TFlxAnchorType.MoveAndDontResize, 2, 0, 2, 0,
                         Round(Image.HeightInPoints * 96.0 / 72.0), Round(Image.WidthInPoints * 96.0 / 72.0), xls);
      ImgProps.ShapeName := 'Image1';
      xls.AddImage(Image, ImgProps);
    finally
      Image.Free;
    end;
    xls.Save('r:\test.xlsx');
 finally
   xls.Free;
 end;
end.

One interesting thing to note is that I used Round(Image.HeightInPoints * 96.0 / 72.0) instead of just
Round(Image.Height)

For images at 96 dpi, you can use Round(Image.Height) and it will be the same. But for higher resolutions, Image.Height will always return the image size in physical pixels, and won't compensate for the fact that a virtual pixel is smaller. So instead I am getting the image in points (where a point is 1/72 of an inch) and converting it to virtual pixels (where a virtual pixel is always 1/96 of an inch, no matter the dpi of the image).
3

Hello,
Thank you for your wonderful answer. I am impressed with the wonderful function. Thank you very much ,
Taro

4

Hello!
I've got the same issue. Can you help please?
Unlike the previous question I'm trying to insert two copies of the same image at cells (2, 5) and (18, 5). It works perfect with your code example until I'm not inserting text with WordWrap format at any cell at row 2 (where the first copy of image is inserted).
For example, if I do insert text with WordWrap format at cell (2, 1) the first copy of image becomes stretched vertically and as I guess it depends on height of row 2.

Here is my code example:

program Project17;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  System.SysUtils,
  VCL.FlexCel.Core,
  FlexCel.XlsAdapter;

function CalcImgProps(const pXls: TExcelFile; const vRow, vCol: Integer; const pFileName: string): IImageProperties;
var
  vImg:      TUIImage;
  vAnchor:   TClientAnchor;
begin
  vImg := TUIImage.FromFile(pFileName);
  try
    vAnchor := TClientAnchor.Create(TFlxAnchorType.MoveAndDontResize,
                                    vRow, 0, vCol, 0,
                                    ROUND(vImg.HeightInPoints * 96.0 / 72.0),
                                    ROUND(vImg.WidthInPoints  * 96.0 / 72.0),
                                    pXls);
    Result := TImageProperties_Create(vAnchor, '');
  finally
    vImg.Free;
  end;
end;


var
  vXls:       TExcelFile;
  vFlxFormat: TFlxFormat;
  vXF:        Integer;
const
  vFileName = '..\..\1.jpg';
begin
  vXls := TXlsFile.Create(1, TExcelFileFormat.v2016, True);
  try
    // printing text 'ABC/DEF/...' at Cell(2, 1) with WrapText format:
    vXls.SetCellValue(2, 1, 'ABC'+#10+'DEF'+#10+'GHI'+#10+'JKL');
    vFlxFormat := vXLS.getDefaultFormat;
    vFlxFormat.WrapText := True;
    vXF := vXLS.AddFormat(vFlxFormat);
    vXLS.SetCellFormat(2, 1, vXF); // - this increases height of row 2:

    // printing Image at Cell(2, 5):
    vXls.AddImage(vFileName, CalcImgProps(vXls, 2, 5, vFileName)); // it becomes stretched vertically
                                                                   // depending on height of row 2

    // printing the same Image at Cell(18, 5):
    vXls.AddImage(vFileName, CalcImgProps(vXls, 18, 5, vFileName));  // it looks allright
                                                                     // since we didn't change height of row 18

    vXls.Save('..\..\test.xlsx');
  finally
    vXls.Free;
  end;
end.



What am I doing wrong?

5

Hi,
To get this to work you will need to autofit the rows with FlexCel. What is happening here is just that in Excel the anchors of the images are always stored as (cell1, cell2), so the code above calculates the cell2 needed for 100% height based in the row height without autofitting. When you modify a row height and the anchor is "dontresize" both FlexCel and Excel will automatically update all the cell2 of all the anchor images to give the illusion of the image not resizing. But when Excel does the original autofit when opening the file, it doesn't do adapt cell2.

So the solution is to have cell2 calculated with the correct row height. In the code above:

1)Add FlexCel.Render to the uses clause.

2)Add this line in red:


    vXLS.SetCellFormat(2, 1, vXF); // - this increases height of row 2:

    vXLS.AutofitRowsOnWorkbook(false, false, 1.1);

    // printing Image at Cell(2, 5):
    vXls.AddImage(vFileName, CalcImgProps(vXls, 2, 5, vFileName)); // it becomes stretched vertically



And it should work. Note: As mentioned above, when you resize a row, FlexCel will automatically change the anchors in the images, so you could do the autofit after adding the images too if needed and it will work the same. But by autofitting before adding the images, you save a little of work, because FlexCel doesn't have to loop over the existing images to adapt the anchors. So if possible it is better to autofit after all cells have been filled, but before adding the images. But if for whatever reason this is complex to do, you can autofi the rows at the end too.

6

Hello Adrian!

Thank you for help. Line "AutofitRowsOnWorkbook" let us to solve problem with stretching images, but it causes another problem:
I have a template file "in.xlsx" that has some data in it:


I open template, insert text "ABC" at cell(1,1) and save file as "out.xlsx":

program Project17;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  System.SysUtils,
  VCL.FlexCel.Core,
  FlexCel.XlsAdapter,
  FlexCel.Render;

var
  vXls: TExcelFile;
begin
  vXls := TXlsFile.Create('..\..\in.xlsx');
  try
    vXls.SetCellValue(1, 1, 'ABC');

    vXLS.AutofitRowsOnWorkbook(false, false, 1.1);

    if FileExists('..\..\out.xlsx') then
      DeleteFile('..\..\out.xlsx');

    vXls.Save('..\..\out.xlsx');
  finally
    vXls.Free;
  end;
end.



After opening file "out.xlsx" we see that height of some rows has changed even if I didn't insert there any data:


Is it possible to use "AutofitRowsOnWorkbook" and not lose original row heights? Or I can only have one: original image sizes or original row heights?
Maybe there is another way to solve problem with stretching images?


7

Hi,

There is sadly no such thing as "original heights". A row either has a fixed height, or the height is calculated by Excel automatically, and it will change depending in the resolution. In general pixel perfect layouts are impossible in Excel, and you need to take that in account when calculating the widths and heights. (and that is why we use a 1.1 adjustment factor in the call to AutoFitRowsInWorkbook).

Don't take my word for it: Do a print preview of the sheet, select 100% zoom and print gridlines and headers, and you will see that the rectangles are different. (they are bigger in print preview). Change the printer, the rectangles will change again. And if you ever open the file in Excel for macOS or iphone, well, it will look very different.

So, the AutofitRows in FlexCel just calculates the pixel width for the average size of a 600 dpi laser printer (I personally measured dozens of different 600 dpi laser printers and took the average). In fact, you can affect how the row height is calculated by changing the WidthAdjust and HeightAdjust properties in the TXlsFile, but that won't be worth.

The fact that it is impossible to autofit "like Excel" because Excel autofits different things at different resolutions is the reason why we don't automatically call AutoFitAllRows before saving.
There is more about this here: http://www.tmssoftware.biz/flexcel/doc/vcl/guides/api-developer-guide.html#autofitting-rows-and-columns

Now, about the solutions:
1)You can keep row heights automatically. I called 
 but you could call
Setting the second parameter to true will keep the row heights automatic in Excel, so it will recalculate them when opening, and in your machine and at your resolution they will go back from 20 px to 21. But they will stay automatic, so they will change when opened in other machines, specially not Windows machines. (and he aspect ratio of the images might be a little affected too). By setting the row height to fixed you ensure they will look more the same everywhere, that is why I used false in my example. But if you prefer to keep the row heights automatic, then that is your choice, and in fact it is better if the users are going to edit the file and would like the row heights to change when they add text.

2)From what I see in your image, most values are reasonably near, but there is one value that stands out and it is the 56px instead of 75. I imagine this is related to the merged cell, because Excel doesn't autofit merged cells and FlexCel does ( http://www.tmssoftware.biz/flexcel/doc/vcl/guides/api-developer-guide.html#autofitting-merged-cells )

I'll investigate it, but something is wrong with that autofit. If I can't reproduce it here I will ask you for the specific file, but I can try to reproduce it based in the image.