How to specify data types when draging content to Excel

Question

I am initiating drag and drop from my WinForms application using this simple

IDataObject data = new DataObject();
string textToExcel = "Hello\tWorld\t1\t2\nHello\tWorld\t1\t2\n"
data.SetData(DataFormats.Text, textToExcel);

I works fine when dropped on Excel, it ends up nicely in columns and rows. Problem is that Excel does not know that the cells with values 1 and 2 are numerics and it gets worse when dropping a date value.

How can I tell Excel the data types of the individiual cells, is there some richer type that Excel accepts when content is being dropped into it.

Answer 1

All you are able to communicate with Drag-Drop to Excel are strings, which Excel will automatically convert to a date or numeric type if it can. If you don't want it to convert identifable types to a data type then you should begin the value with an appostrophe (') character, such as '100, instead of 100.

If you wish to have full control, you should subscribe to the Excel.Worksheet.Change event which will be triggered at the end of the Drag-Drop action. At that point you can do a custom conversion of your own data. This would be facilitated if you transmit your data as a custom format to begin with, one that would not be automatically converted by Excel. For example, instead of sending a 2x2 block of values such as:

100 Hello
$1.25 10/9/2010

You could send it through as:

<DragDrop:Double>100</Double> <DragDrop:String>Hello</String>
<DragDrop:Currency>1.25</Currency> <DragDrop:Date>2010.10.9</Date>

These values would be received by the cells as strings. But when the Worksheet.Change event fires, it will tell you which cells have been changed, and your routine could process the strings, looking for anything that begins with "<DragDrop:". You could then convert these to the required data types as specified in the strings themselves.

For a detailed example, see the article Adding Drag-and-Drop Functionality using the .NET Framework and Visual Studio 2005 Tools for Office Second Edition. In that article, they use an example that is much more unique than "<DragDrop:" -- they use a GUID at the front of the string to really make sure that the string is unique and identifiable. But the basic strategy is as I described, above.

Hope this helps,

Mike