Inserting images to an Excel sheet in Delphi

Question

I have a question here, I have an application and I need to add images to it. I have tried:

Sheet.Shapes.AddPicture(G_V.Prog_Dir+'pic.BMP',false,true, 190, 10+(15*rowcount), 100, 100 );

it works just fine, but I don't want to give parameters, I want to insert pictures to specified (and parametric) cells because I need to add picture to the last column of the page; this excel needs to be printed I must mention that. So I tried:

Sheet.Range['E'+inttostr(rowcount),'E'+inttostr(rowcount)].Select;
Sheet.Pictures.Insert(G_V.Prog_Dir+'pic.BMP');

It looks OK at first sight, however I think this code links images to the sheet. For example, I send the created Excel to another computer and these images cannot be seen (I don't recall the exact error) and when I searched it, I found out that receiving computer needs to have images at the exact path. As a solution to this, "Sheet.Shapes.AddPicture" recommended but as I stated before, I need another solution here.

I didn't see anybody experiencing this kind of problem, I hope someone helps me out.

Answer 1

You can use this code to import picture. You do not need exact path if you use AddPicture function with this parameters. it copies photo and paste it to excel.

   Set p = ActiveSheet.Shapes.AddPicture(FileName:=PictureFileName,_
   linktofile:=msoFalse, savewithdocument:=msoCTrue, _ 
   left:=TargetCell.Left, Top:=TargetCell.Top, Width:=-1,_
   Height:=TargetCell.Height)

Answer 2

The simple solution is to add the picture to the clipboard and then paste it into the sheet.

implementation

uses
  VCL.Clipbrd, VCL.Graphics, Excel2000;

procedure TForm1.Test;
var
  Bitmap: TBitmap;
  Excel: TExcelApplication;
  Worksheet: TExcelWorksheet;
begin
  Bitmap:= TBitmap.Create;
  try
    Bitmap.LoadFromFile('c:\test.bmp');
    Clipboard.Assign(Bitmap);
    Excel:= TExcelApplication.Create(self);
    Worksheet:= Excel.Worksheets[0];
    Worksheet.Range['a1','a1'].Select;
    Worksheet.Paste;
  finally
    Bitmap.Free;
  end;
end;

This destroys the previous contents of the clipboard, which is bad form. You can save/restore the clipboard data using the following routines (based on: https://www.devexpress.com/Support/Center/Question/Details/Q93874)

procedure TForm1.RestoreClipboardData(SourceDataStream: TMemoryStream);
var
  AData: THandle;
  ADataPtr: Pointer;
begin
  if SourceDataStream.Size = 0 then Exit;
  Clipboard.Open;
  try
    AData := GlobalAlloc(GMEM_MOVEABLE + GMEM_DDESHARE, SourceDataStream.Size);
    try
      ADataPtr := GlobalLock(AData);
      try
        SourceDataStream.Position := 0;
        SourceDataStream.ReadBuffer(ADataPtr^, SourceDataStream.Size);
        SetClipboardData(CF_BITMAP, AData);
      finally
        GlobalUnlock(AData);
      end;
    except
      GlobalFree(AData);
      raise;
    end;
  finally
    Clipboard.Close;
  end;
end;

procedure TForm1.SaveClipboardData(DestDataStream: TMemoryStream);
var
  AData: THandle;
  ADataPtr: Pointer;
begin
  if DestDataStream = nil then raise Exception.Create('Dest is nil');
  Clipboard.Open;
  try
    AData := GetClipboardData(CF_BITMAP);
    if AData = 0 then Exit;
    ADataPtr := GlobalLock(AData);
    try
      DestDataStream.Size := GlobalSize(AData);
      DestDataStream.Position := 0;
      DestDataStream.Write(ADataPtr^, DestDataStream.Size);
    finally
      GlobalUnlock(AData);
    end;
  finally
    Clipboard.Close;
  end;
end;