Hi,
It is sadly not possible (not by us, and not by anyone, including Excel itself) to include images when copying cells to paste in Excel. Yes, I know, You can ctrl-c some cells with images in Excel, then open a new file also in Excel, ctrl-v and the image will be copied. But this is because Excel bypasses the clipboard and copies the stuff directly.
If you do the following:
-
Create a file with an image:
-
Copy the cells from A1 to D10 in Excel
-
Close Excel completely. (This is important. If Excel remains in memory, it will bypass the clipboard and paste the image)
-
Open a new file, and ctrl-V:
The image won't be there anymore. We can't bypass the clipboard as Excel does, so we are subject to the limitations the clipboard has. Besides the image thing, the other limitation is that strings will be cut at 256 characters: FlexCel API Developer Guide | FlexCel Studio for VCL and FireMonkey documentation
Now, if those aren't deal breakers, some other stuff like merged cells, formulas and such can be copied, and you might be able to improve a little over what you have (but not much).
Using the code in this example: Copy and paste (Delphi) | FlexCel Studio for VCL and FireMonkey documentation
unit Unit1;
interface
uses
Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
Vcl.Controls, Vcl.Forms, Vcl.Dialogs, UAdvGridExcelExport, FlexCel.Core, FlexCel.XlsAdapter,
AdvUtil, Vcl.Grids, AdvObj, BaseGrid, AdvGrid;
type
TForm1 = class(TForm)
AdvGridExcelExport1: TAdvGridExcelExport;
AdvStringGrid1: TAdvStringGrid;
procedure FormCreate(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;
var
Form1: TForm1;
implementation
uses Clipbrd;
{$R *.dfm}
procedure CopyToClipboard(const DataFormat: TFlexCelClipboardFormat; const DataStream: TStream);
var
MyHandle: THandle;
BiffPtr: pointer;
FreeHandle: boolean;
Df: string;
begin
DataStream.Position := 0;
FreeHandle := true;
MyHandle := GlobalAlloc(GMEM_MOVEABLE, DataStream.Size);
try
BiffPtr := GlobalLock(MyHandle);
try
DataStream.ReadBuffer(BiffPtr^, DataStream.Size);
finally
GlobalUnlock(MyHandle);
end; //finally
Clipboard.Open;
try
//Text format is standard, must be handled differently.
if DataFormat = TFlexCelClipboardFormat.Text then
begin
//Setting CF_UNICODE_TEXT will also set CF_TEXT and CF_OEMTEXT
Clipboard.SetAsHandle(CF_UNICODETEXT, MyHandle);
end else
begin
//Other formats than TEXT must be registered with RegisterClipboardFormat.
Df := TFlexCelDataFormats.GetString(DataFormat);
Clipboard.SetAsHandle(RegisterClipboardFormat(PChar(Df)), MyHandle);
end;
FreeHandle := false; //Note that we dont have to free MyHandle if the clipboard takes care of it
finally
Clipboard.Close;
end; //Finally
except
if FreeHandle then GlobalFree(MyHandle);
raise
end; //except
end;
procedure CopyXlsToClipboard(const Xls: TExcelFile);
begin
try
Clipboard.Clear;
Clipboard.Open; //The other open calls in CopyToCliboard will be ignored.
try
for var cf := Low(TFlexCelClipboardFormat) to High(TFlexCelClipboardFormat) do
begin
var DataStream := TMemoryStream.Create;
try
Xls.CopyToClipboardFormat(TFlexCelClipboardFormat.Xls, TXlsCellRange.Create(1, 1, 10, 10), DataStream);
// You could also coy the full sheet with:
//Xls.CopyToClipboard(cf, DataStream);
//Copying the full sheet allows for copying column widths/row heights and column/row formats too.
CopyToClipboard(cf, DataStream);
finally
FreeAndNil(DataStream)
end;
end;
finally
Clipboard.Close;
end;
except on ex: Exception do
begin
ShowMessage(ex.Message);
end;
end;
end;
procedure TForm1.FormCreate(Sender: TObject);
begin
AdvStringGrid1.Cells[1,1] := 'hello';
var Xls := TXlsFile.Create(1, TExcelFileFormat.v2023, true);
try
AdvGridExcelExport1.Export(Xls);
var Data := TMemoryStream.Create;
try
CopyXlsToClipboard(Xls);
finally
Data.Free;
end;
finally
Xls.Free;
end;
end;
end.
