Native Excel copy and pasting is something I've wanted to do for ages. I think FlexCel may be the only component which supports this - great stuff. However, I can't seem to find any examples, although one is alluded to here:
Indeed there was a demo (in both FlexCel 3 and FlexCel .NET), but I wasn't too sure if it was worth porting to v5 VCL. I just want to avoid having "too much" demos (as it is the case in FlexCel.NET, where we have over 50 demos, and it gets really hard for a beginner to find what he is looking for).
But well, in any case, I 've ported the demo to v5, and while again it won't make it to 5.6.5 (because we've just released it!), it will be there in the next version.
And I will be keeping the file there until we release a new version that has it included.
In short, FlexCel takes care of 3/4 of the work (saving the an xlsfile to a clipboard stream that Excel understands, and reading a clipboard stream into a TXlsFile object).
But you still need to read/write those streams from/to the clipboard, giving priority to the native xls stream and if not available, using the alternate text stream instead.
This demo shows how to do that other 1/4 part with a Clipboard object. FlexCel doesn't do this 1/4 part anymore (it did in v3) because how you write or read to a clipboard is very platform dependent code, and would be completely different in for example firemonkey, or OSX. But well, you can get the code from this sample.
Also, you could also support drag and drop the same way (the .NET example shows how to do it), but I can't show it in this demo because you need some 3rd party drag and drop support like http://melander.dk/delphi/dragdrop/ because the "native" drag and drop in delphi is just for VCL objects. But if you have support for COM drag and drop, you can use the same streams FlexCel generates for the clipboard in the drag and drop.
This is weird, I followed the same steps from your screencast (Excel 2010/win8/XE3) and here the data is pasted.
So I'd like to ask some questions to see if we can find what's happening:
1) If you paste the "Text" or "unicode text", do you get something? This demo copies the data both as native excel and as text, so both formats should be present. You aren't seeing the "Microsoft Excel 8.0 format" in paste special as I do here, but I wonder if the text was copied right or it wasn't either.
2)It would be good to verify that the paste has gone well. So, change the pasteclick event to be like this:
procedure TForm2.btnPasteClick(Sender: TObject);
begin
DoPaste;
Xls.AllowOverwritingFiles := true;
Xls.Save('r:\test.xls');
end;
Then paste something from Excel, and open the file that was created. Does it contain the pasted data?
3)If the file pasted in 2) was ok, then we need to check if the file copied to the clipboard is fine. Change the copyToClipboard method to be:
procedure TForm2.btnCopyClick(Sender: TObject);
var
TextString: TStringBuilder;
XlsStream: TStream;
begin
if (Xls = nil) then
begin
ShowMessage('Please push the New File button before copying');
exit;
end;
try
TextString := TStringBuilder.Create;
try
XlsStream := TMemoryStream.Create;
try
Xls.CopyToClipboard(TextString, XlsStream);
TMemoryStream(xlsStream).SaveToFile('r:\test1.xls');
CopyToClipboard(TextString.ToString, XlsStream);
finally
FreeAndNil(XlsStream)
end;
finally
FreeAndNil(TextString);
end;
except on ex: Exception do
begin
ShowMessage(ex.Message);
end;
end;
end;
And again, repeat the procedure and copy somehting to excel. Then open the file test1.xls that was saved in the copy event in Excel. Does it open fine?
Once we know the answer to this questions the problem is reduced in half, or it is when creating the formatted text (in which case there will be something wrong when we do the things above), or it is when we put this code in the clipboard.
Strangely after making those changes everything works. The only slightly odd thing is the cells, when pasted back to Excel, do not contain any formulas. They are just the values. I would have thought the formulas should have survived.
Thanks for the help and I'll ping you if I have any further questions.
Of course changing those lines shouldn't affect how the copy/paste works (and you should revert those changes or you will be saving a temp file every time the user copies/pastes a file). Could it be that maybe something in the clipboard state was wrong, and resetting the machine fixed it?
About formulas, I thought they were preserved, but I've tried it here and indeed they aren't.
Investigating further, I've tried with Excel only (no FlexCel involved) and it didn't copy them either. So this seems to be the way Excel copy/paste works.
The way to test it with Excel only is a little complex, you can't just copy and paste in Excel, because if you do, Excel just skips the clipboard and copies directly (They might call InsertAndCopyRows :)
Another way to try it would be to copy, then close Excel, open it again, and paste, but if you do this, Excel only leaves the text data in the clipboard.
So the way to test it is: Open 2 different Excel instances. To do this, middle-click in the Excel icon in the taskbar. (don't go to "File new" in Excel, since this won't create a new instance). Now copy in one instance and paste in the other. You will see formulas are lost here too. In fact, if you do this in Excel 2010, you will get this warning:
So I guess they are aware of this too. Strangely, you don't get this warning when pasting from FlexCel, but this might be because we are copying in xls format, and Excel copies in both xls and xlsx, and pastes by default xlsx.