inserting images in sheets

I am currently using a different tool than FlexCel, and we have issues. I would like to learn as quickly as possible whether the move to FlexCel will allow us to resolve these concerns.


It is essential that we put a customer provided logo in the sheet. This would be in the upper left of each sheet, and we need to be able to preserve the aspect ratio of the image. We also need to discover what column is the first completely to the right of the inserted image, to that cells can be filled there with data. Similarly, we need to discover in code which row would be the first completely below the image as it has been inserted. 

In other words, the image will be larger than a cell, not contained in one.

Thanks!
Hi,
Yes, this is fully supported.

As a starting point, we always recommend to use APIMate. Create the file you want in Excel, then open it with ApiMate (start menu->tms flexcel->tools)and look at the code.

As an example, I tried with a file with an image here, and I got:


 //Images
  fs := TFileStream.Create('imagename.png', fmOpenRead or fmShareDenyNone);
  try
    ImgProps := TImageProperties_Create();
    ImgProps.Anchor := TClientAnchor.Create(TFlxAnchorType.MoveAndDontResize, 2, 0, 2, 0, 5, 204, 9, 416);
    ImgProps.ShapeName := 'Picture 1';
    xls.AddImage(fs, ImgProps);
  finally
    fs.Free;
  end;


This would add an image from row 2, column 2, to row 5, column 9.
And well, once we have this, we need to get deeper in how to specify the coordinates of the image.
In Excel, images are always saved with their position and size relative to cells.

So for example, you might have an image going form row 1, col 3, to row 7, col 8. And then you specify the offset from the start of the cell. For example, the image might go from row 1, with an offset of 128, and that would mean it starts at the middle of row 1. (offsets go from 0 to 255 in rows, and 0 to 1024 in columns, so a row offset of 255 would mean the next row).

And well, APIMate shows you the TClientAnchor constructor that specifies starting and ending (row, col) and their offsets:

TClientAnchor.Create(TFlxAnchorType.MoveAndDontResize, 2, 0, 2, 0, 5, 204, 9, 416);


But you normally don't really know the end row,col, as this depends on the image, aspect ratio and column/row sizes.

So we have an overload that you might use instead of what APIMate suggests:


   /// <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;


With this overload, you specify the height and width of the image, in "pixels". But note that "pixel" here is a loose term, because while images are measured in pixels, Excel uses inches. For example, if your monitor has 96 dots per inch, then an image with 96 pixels would be 1inch. But when you print it in a 600 dpi printer, it would be 96/600 inches. And in a high res monitor, it would be another size. So Excel will always consider the absolute (inches) size of the image, and resize it so it looks the same. 

When you specify a height and width in pixels for the anchor, FlexCel will use 96dpi to calculate the ending row, col and offsets that it needs to write in the xls/x files.

You don't need to be so exact with the pixel dimensions, it doesn't really matter as the image will most likely be resized anyway, depending in the monitor resolution. But you need to keep the aspect ratio.

You might already know the pixel dimensions of the image, or you might use a TPicture object to get them, or a FlexCel's TUIImage object.

And once you create the anchor using the width and height, you can get the end (row, col) of the anchor, so you can fill the data after that.

Here is a little code snippet that would do all the stuff described. It will open an image to see its dimensions, then insert it at row 2, col 3, and then write "Hello from FlexCel" at the row completely below the image, and column completely to the right of the image



procedure CreateFile;
var
  xls: TExcelFile;
  fs: TFileStream;
  ImgProps: IImageProperties;
  img: TUIImage;


begin
  xls := TXlsFile.Create(true);
  xls.NewFile(1, TExcelFileFormat.v2010);  //Create a new Excel file with 3 sheets.


  //Set the names of the sheets
  xls.ActiveSheet := 1;


  //Images
  fs := TFileStream.Create('r:\test.png', fmOpenRead or fmShareDenyNone);
  try
    img := TUIImage.FromStream(fs);
    try
      ImgProps := TImageProperties_Create();
      ImgProps.Anchor := TClientAnchor.Create(TFlxAnchorType.MoveAndDontResize,
          2, 0, 3, 0, Trunc(img.Height), Trunc(img.Width), xls);
      ImgProps.ShapeName := 'Picture 1';
    finally
      img.Free;
    end;


    fs.Position := 0;
    xls.AddImage(fs, ImgProps);
  finally
    fs.Free;
  end;


  xls.SetCellValue(ImgProps.Anchor.Row2 + 1, ImgProps.Anchor.Col2 + 1, 'Hello from FlexCel');
  xls.Save('r:\test.xlsx');
end;

Adrian, thanks, that's a great load of information!


However, unless I am simply not seeing it, what is missing is how I can learn what the column widths are. I will play with ApiMate, and see what I can learn that way. But in the nature of things, the column widths will be adjusted based on the content in them, and as a consequence, I do not have the knowledge until then of what columns the graphic will span. 

So as I see it, I would reserve the rows needed for my nominal page header content, then populate the columns for the primary data area and adjust their widths to fit the content of the columns. Once that has been done, I can insert the logo. But at that point, in order to pass the correct arguments to TClientAnchor.Create(), I would need to discover the widths to which the columns had been adjusted. I can then make the call to TClientAnchor.Create(), and would also then know the index of the first column to the right of the image.

Does that make sense? 

What I cannot do is insert the image, then adjust the column widths and suffer a change in size of the inserted logo.
Hi,

In the example above, FlexCel calculated the landing row/col for you.

When you do:

ImgProps.Anchor := TClientAnchor.Create(TFlxAnchorType.MoveAndDontResize,
          2, 0, 3, 0, Trunc(img.Height), Trunc(img.Width), xls);


internally the method will look at the current widths in the xls file you pass (in the last parameter), and calculate row2, col2 correctly.

Once you created the anchor that way, you can know the row2, col2, with:
ImgProps.Anchor.Row2
ImgProps.Anchor.Col2

Also note that once you insert the image, if you modify column widths, imagesize will change or not depending on the first parameter of the anchor. In this case we used TFlxAnchorType.MoveAndDontResize, so the image won't be resized when you resize columns. Internally what will happen is that when you resize a column, both FlexCel and Excel will calculate the new row2, col2 so the image stays the same size.

So you can do both: 
1) You can insert the image when you start creating the file and as long as it is "MoveButDontResize" it will stay the same width even if you resize rows or columns after that.

or 2) You can insert the data first, then insert the image and insert  columns/rows so the data moves to the right and down.
But in your case, I think it makes sense to just add the image at the start. Don't worry' when you resize the rows and columns FlexCel will adapt the ending row/col and the image size won't change. 

Wonderful! Thanks again!

When I try to use the example in Delphi Seattle I get an error:

: [dcc32 Error] ExportPDFxls.pas(128): E2251 Ambiguous overloaded call to 'TImageProperties_Create'
  FMX.FlexCel.Core.pas(1370): Related method: function TImageProperties_Create: IImageProperties;
  VCL.FlexCel.Core.pas(1375): Related method: function TImageProperties_Create: IImageProperties;

I copied following from APIMATE in my application

  //Images
  fs := TFileStream.Create('imagename.jpg', fmOpenRead or fmShareDenyNone);
  try
    ImgProps := TImageProperties_Create();
    ImgProps.Anchor := TClientAnchor.Create(TFlxAnchorType.MoveAndDontResize, 1, 0, 1, 277, 2, 97, 3, 128);
    ImgProps.ShapeName := 'Picture 1';
    xls.AddImage(fs, ImgProps);
  finally
    fs.Free;
  end;


The line ImgProps := TImageProperties_Create(); gives the error.
I do not know how to solve this.
Please help

Hi,
It seems like you are using both FMX.FlexCel.Core and VCL.FlexCel.Core.
You should use only one: If it is a VCL application use VCL.FlexCel.Core and remove all FMX.FlexCel.Core references. If it is a FireMonkey application, use only FMX.FlexCel.Core.

Note also that you need to use VCL.FlexCel.Core just once in your app. For most units (which might be used from Firemonkey or VCL apps) just use FlexCel.Core.
Then once in your VCL/FMX only code use VCL/FMX.FlexCel.Core. For the rest, use FlexCel.Core.

Thanks for quick reply. Yes indeed I had accidentally Flexcore double in my uses clause.