Winform Drag Drop into excel

Question

I would like to perform drag drop from my winform into Excel. For example, holding a button (or a line in DataGridView) in Winform and drag and drop it into an Excel cell will perfom some action or write some data into the Excel cell.

I am talking about drag drop between winform and Excel, not between winform components.

I remember seeing an example once but could not find it again on Google.

Update: Following answer by Avantol13,

How do we extract the object being sent over to Excel ? In the example, it's kind of a hack to let the string be written into the cell, then check that cell's content to perform further action. Perhaps something similar to DragDrop event where we can extract data being sent from e.Data.GetData(e.Data.GetFormats()[0])

Besides, using Application_SheetChange to know the drop event will cause it to loop as Application_SheetChange -> code that modifies the cell, as in the example (delete the cell and write something new) -> trigger again Application_SheetChange, etc.

Thanks in advance.

Answer 1

Sean Sexton provides an excellent example on how to Drag&Drop data from a WPF window to Excel. I have modified his example so that it works with WinForms. Try this:

private void control_MouseDown(object sender, MouseEventArgs e)
{
    List<string> stringItems = new List<string>() { "Value1", "Value2", "Value3" };

    //Separate all values with tabs
    string someValues = string.Join("\t", stringItems);

    DataObject data = new DataObject(DataFormats.Text, someValues);
    DoDragDrop(data, DragDropEffects.Copy);
}

Answer 2

I figured out a way to do this based on the recommendation by Avantol13.

Hook Excel_Drop event when Excel's sheet is modified

Application.SheetChange += new AppEvents_SheetChangeEventHandler(Excel_Drop);

For the button where the drag initiated:

private void _Button1_MouseDown(object sender, MouseEventArgs e)
        {
            if (e.Button == MouseButtons.Left)
            {
                DragDropEffects dde1 = DoDragDrop("Action1", DragDropEffects.All);
            }
        }

Another button will send "Action2" as flag.

private void Excel_Drop(object dropObj, Range target)
    {
        String dragData = Convert.ToString(target.Value2);

        if (dragData.Equals("Action1"))
        {
            Application.SheetChange -= Excel_Drop;
            DoAction1();
            Application.SheetChange += Excel_Drop;
            return;
        }
        if (dragData.Equals("Action2"))
        {
            Application.SheetChange -= Excel_Drop;
            DoAction2();
            Application.SheetChange += Excel_Drop;
            return;
        }
    }

To solve the looping problem :dropping event fires Excel_drop->which calls DoActionx() that modifies the sheet again->in turns re-fire Excel_drop, I block the event.

There are however still several drawbacks base on this method of using a text flag:

1. I cannot send anything except text as drag object like here when dragdrop between winform components. Because of that, I need to let the text flag be sent anyway, delete it and do my action. Beside being a hack, I cannot verify "do action only if the cell is empty" because the drop will overwrite my cell with the flag anyway. Any recommendation is welcome.

2. How can we send anything as drag object?