Hi,
/// <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;
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.
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?
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.
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?
Hi,