Drag and drop from Excel to Windows Form Controls

As you know, Excel Application lets you drag cells in sheet. When you begin a drag operation, the Excel you drag from (the drop source), creates a COM object implementing the IDataObject interface, and puts some data into the object. The window you drag into (the drop target), then reads that data using IDataObject methods; that's how it knows what we are copying.

Following formats supported in .Net 3.5

• Bitmap: Specifies a Windows bitmap format
• CommaSeparatedValue: Specifies a comma-separated value (CSV) format, which is a common interchange format used by spreadsheets.
• Dib: Specifies the Windows device-independent bitmap (DIB) format
• Dif: Specifies the Windows Data Interchange Format (DIF), which Windows Forms does not directly use
• EnhancedMetafile: Specifies the Windows enhanced metafile format
• FileDrop: Specifies the Windows file drop format, which Windows Forms does not directly use
• Html: Specifies text consisting of HTML data
• Locale: Specifies the Windows culture format, which Windows Forms does not directly use
• MetafilePict: Specifies the Windows metafile format, which Windows Forms does not directly use
• OemText: Specifies the standard Windows original equipment manufacturer (OEM) text format
• Palette: Specifies the Windows palette format
• PenData: Specifies the Windows pen data format, which consists of pen strokes for handwriting software; Windows Forms does not use this format
• Riff: Specifies the Resource Interchange File Format (RIFF) audio format, which Windows Forms does not directly use
• Rtf: Specifies text consisting of Rich Text Format (RTF) data
• Serializable: Specifies a format that encapsulates any type of Windows Forms object
• StringFormat: Specifies the Windows Forms string class format, which Windows Forms uses to store string objects
• SymbolicLink: Specifies the Windows symbolic link format, which Windows Forms does not directly use
• Text: Specifies the standard ANSI text format
• Tiff: Specifies the Tagged Image File Format (TIFF), which Windows Forms does not directly use
• UnicodeText: Specifies the standard Windows Unicode text format
• WaveAudio: Specifies the wave audio format, which Windows Forms does not directly use

When we copy data from Excel and drag in our application to drop on the controls following are the events which get fired on our control. Hence we need to override the following events in our application.

• OnDragEnter(): Called when the cursor enters your window.
• OnDragOver(): Called when the cursor moves inside your window.
• OnDragLeave(): Called when the cursor leaves your window.
• OnDrop(): Called when the user drops in your window.

When we drag the data in our application Excel has already created the COM object and it contain the data with comma separated values. This data need to be formatted before we use that in our application. We can validate this data in OnDragEnter()event .
In our application, if you are using inbuilt .Net component then verify it has AllowDrag property which can be set to true. And also it has above mentioned event to override. If you are using any third party control then it should have events to override the drag and drop functionality. Here i am using inbuilt .Net component TextBox and DataGridView.

#region "Grid Events"

private
void dataGridView1_DragDrop(object sender, DragEventArgs e)
{
if (e.Effect == DragDropEffects.Copy)
{
if (e.Data.GetDataPresent(DataFormats.CommaSeparatedValue))
{
string csvText = e.Data.GetData(DataFormats.CommaSeparatedValue, false).ToString();

if (!String.IsNullOrEmpty(csvText))
{
// write data to multiline text box as it is
textBox1.Text = GetFormattedData((MemoryStream)e.Data.GetData(DataFormats.CommaSeparatedValue, false));

DataTable dtable = GetDataTable((MemoryStream)e.Data.GetData(DataFormats.CommaSeparatedValue, false));

if (dtable != null)
{
dataGridView1.DataSource = dtable;
dataGridView1.Refresh();
}
}
}
}

this.Text = appTitle + " (DragDrop)";
}



private
void dataGridView1_DragEnter(object sender, DragEventArgs e)
{
if (!e.Data.GetDataPresent(DataFormats.CommaSeparatedValue))
{
e.Effect = DragDropEffects.None;
}
else
{
e.Effect = DragDropEffects.Copy;
}
this.Text = appTitle + " (DragEnter)";
}


private
void dataGridView1_DragLeave(object sender, EventArgs e)
{
this.Text = appTitle + " (DragLeave)";
}

private
void dataGridView1_DragOver(object sender, DragEventArgs e)
{
this.Text = appTitle + " (DragOver)";
}
#endregion "Grid Events"


Note :

• If you are using Visual Studio 2010, this program will not work in debug mode. try to run the exe from bin.